MySQL笔记，偷偷收藏下

这是我参与11月更文挑战的第9天，活动详情查看：2021最后一次更文挑战

1 定义

数据库中的表：一行叫一条记录。每一列叫一个属性，或一个字段。主键：表中的某个特殊字段，具有唯一的确定的值，可以根据该字段唯一的确定一条记录外键：表中的某个字段的值为另一张表（也可以是自身）中的某个字段的值。

2 MySQL 的数据类型

2.1 数值

TINYINT 小整型
INT/INTEGER 整型
BIGINT 长整型
FLOAT 小数
DOUBLIE 双精度小数

2.2 日期

DATE YYYY-MM-DD
TIME HH:MM:SS
YEAT YYYY
DATETIME 混合
TIMESTAMP 高精度

2.3 字符串

CHAR 定长字符串
VARCHAR 不定长
BLOB 二进制文件
LONGBLOB 长二进制文件
TEXT 长文本数据
LONGTEXT 长文本数据 注：以上仅总结了常用类型，并不是所有类型

3 SQL语句

3.1 数据库的创建

CREATE DATABASE 数据库名 [参数]

例如创建一个名为test的数据库，设置字符集为utf-8

CREATE DATABASE test CHARACTER SET utf8;

出现下面的内容即说明创建成功。

Query OK, 0 rows affected (0.00 sec)

你可以使用下面的语句查看新创建的数据库

SHOW DATABASES;

选择需要操作的数据库

USE test;

执行完上面的语句后出现Database changed，你所有的操作都将在test数据库中进行。

3.2 创建表

CREATE TABLE 表名 (
	列名 列数据属性 [约束]，
	列名 列数据属性，
	... ...
);

例如创建一个users表id是主键，自增长。 PRIMARY KEY：约束主键约束 AUTO_INCREMENT：约束自增长

CREATE TABLE users(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
account VARCHAR(50),
pwd VARCHAR(50),
);

3.3 插入数据

INSERT INTO 表名 VALUES (值1, 值2,...,NULL，...);

这种插入方式一定要将所有的值都写上，如果为空的话写NULL。值与列要一一对应 主键如果设置为自增的话写NULL

也可以指定所要插入数据的列，向下面这样。

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....);

按回车后出现

Query Ok, 1 row affected (0.05 sec)

说明成功。否则请检查SQL语句。

例如向刚刚创建的users表中插入一条数据

INSERT INTO users VALUES(NULL,'admin','admin');

第一列是id，主键自增，不需要插入。

3.4 查询数据

SELECT * FROM 表名 [WHERE 条件]  [参数];

例如查询users表里的所有数据的 account 列。

SELECT account FROM users;

3.4.1 WHERE 查询

可以给FROM后面的表起一个别名。例如:

SELECT tn.id,tn.account FROM tablename AS tn
WHERE tn.id = 1;

3.4.2 WHERE 后的条件语句

WHERE后面可以写多种条件语句。
1. 关系语句: =,>,<,!=,>=,<=
2. 逻辑语句: AND,OR,NOT
3. IS NULL: 是否为空
4. BETWEEN:在两者之间 
	WHERE u.id BETWEEN 11 AND 15;
	id 在11-15之间。
5. IN:在..之中
	WHERE u.account IN (值1,值2,...);
	查询 account 在(值1,值2,...)之中的数据
5. LIKE:模糊匹配 
	WHERE  u.id LIKE '通配符';
	
	通配符
	'%':替代零个或多个字符
	'_':替代一个字符
		WHERE u.account LIKE '%A%';查询account字段中含有'A'的数据。

 	[charlist]:字符列中的任何单一字符 
		WHERE u.account LIKE '[ASD]%'; 查询以A S D 开头的数据。
	[!charlist]:不在字符列中的任何单一字符
		WHERE u.account LIKE '[!ASD]%'; 查询不以A S D 开头的数据

3.4.3 聚合函数

SELECT AVG(列名) FROM 表名

AVG(列名):返回某一列的平均值
COUNT(列名):返回匹配指定条件的行数
FIRST(列名):返回指定的字段中第一个记录的值。
LAST(列名):返回指定的字段中最后一个记录的值。
MAX(列名):返回一列中的最大值。NULL 值不包括在计算中。
MIN(列名):返回一列中的最小值。NULL 值不包括在计算中。
SUM(列名):返回数值列的总数（总额）。
UCASE(列名):把字段的值转换为大写。
LCASE(列名):把字段的值转换为小写。
MID(列名,开始位置，截取长度):用于从文本字段中提取字符。

SELECT MID(account,1,3) as uname FROM users;

LEN(列名):返回文本字段中值的长度。
ROUND(列名,小数位数):用于把数值字段舍入为指定的小数位数。
CURDATE():返回当前日期。YYYY-MM-DD。
CURTIME():返回当前时间。HH:MM:SS。
NOW():返回当前时间。YYYY-MM-DD HH:MM:SS。
YEAR(d)，MONTH(d),DAY(d):分别返回参数的年，月，日。
ADDDATE(d,n):在d的时间上加n天。
SUBDATE(d,n):在d的时间上减n天。

19. DATE_FORMAT(d,f):格式化时间d。
%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I(大写的i) 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l(小写的L) 小时 (1-12)
%M 月名
%m 月，数值(00-12)
%p AM 或 PM
%r 时间，12-小时（hh:mm:ss AM 或 PM）
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位

3.4.4 分组查询

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT 列名, 聚合函数
FROM 表名
WHERE 条件
GROUP BY 列名

例如根据用户表中的role属性分组统计每组的人数

SELECT role, COUNT(id)
FROM users
GROUP BY role;

3.4.5 分组过滤

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。例如根据用户表中的role属性分组统计每组的人数，查找人数大于10的role

SELECT role, COUNT(id)
FROM users
GROUP BY role
HAVING COUNT(id)>10;

3.4.6 结果排序

ORDER BY 语句用于根据指定的列对结果集进行排序。默认为升序，降序使用关键字DESC。例如根据用户表中的role属性分组统计每组的人数，按照人数降序排列

SELECT role, COUNT(id) count
FROM users
GROUP BY role
ORDER BY count DESC;

3.5 更新数据

UPDATE 表名 SET 列名 = 新值 WHERE 条件

WHERE的修改条件是可选的，不写的话修改表的所有数据

例如修改users表里的数据，将admin用户的密码改为root

UPDATE users SET pwd = 'root' WHERE account = 'admin';

3.6 删除数据

DELETE FROM 表名 WHERE 条件

WHERE的修改条件是可选的，不写的话删除表的所有数据

4 表属性的修改

4.1 表重命名

ALTER TABLE 表名 RENAME 新表名;

4.2 添加新的一列

ALTER TABLE 表名 ADD 列名 列数据类型 [AFTER 插入位置]

例如在users表的pwd字段后面添加一个role字段类型是INTEGER

ALTER TABLE users ADD role INTEGER AFTER pwd;

4.3 修改列

ALTER TABLE 表名 CHANGE 列名称 新列名称 新数据类型;

例如修改users表里的pwd字段改为password，类型是varchar(20)

ALTER TABLE users CHANGE pwd password varchar(20);