碎碎念

一入Mysql深似海，只见树叶不见森林😓

关系型数据库

关系型数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系(即二维表格形式)。关系型数据库以行和列的形式存储数据，以便于用户理解。这一系列的行和列被称为表，一组表则成了一个库。表于表之间的数据记录有关系。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库

  • 复杂查询: 可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询
  • 事物支持: 使得对于安全性能很高的数据访问要求得以实现

非关系型数据库

非关系型数据库可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步提高性能。目前基本上大部分主流的非关系型数据库都是免费的

关系型数据库设计规则

  • 关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的(Structured)
  • 将数据放到表中，表再放到库中
  • 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表明具有唯一性
  • 表具有一些特征，这些特征定义了数据在表中如何存储，类似Java和Python中类的设计

SQL分类

SQL语言在功能上主要氛围如下3大类:

  • DDL(Data Definition Languages，数据定义语言): 这些语言定义了不同的数据库，表，视图，索引等数据库对象，还可以用来创建，删除，修改数据库的数据表结构。主要的语句。主要的语句关键字包括CREATE，DROP，ALTER
  • DML(Data Manipulation Language，数据操作语言): 用于添加删除，更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括INSERT，DELETE，UPDATE，SELECT等
  • DCL(Data Control Language，数据控制语言): 用于定义数据库，表，字段，用户访问权限和安全级别，主要语句关键字包括GRANT，REVOKE，ROLLBACK，SAVEPOINT

因为查询语句使用的非常频繁，所以很多人把查询语句单伶出来一类: DQL(数据查询语句)。还有单独将COMMIT，ROLLBACK取出来成为TCL(Transaction Control Language 事物控制语言)

SQL语言的规则和规范

基本规则

  • SQL可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用锁进
  • 每条命令以;或\g或\G结束
  • 关键字不能被缩写也不能分行
  • 必须保证所有的()，单引号，双引号是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串和日期时间类型的数据可以使用单引号表示
  • 列的别名，尽量使用双引号，而且不建议省略as

SQL大小写规范

  • MySQL在window环境下是大小写不敏感的，在Linux环境下是大小写敏感的
  • 数据库名，表名，表的别名，变量名是严格区分大小写的
  • 关键字，函数名，列名，列的别名是忽略大小写的
  • 推荐采用统一的书写规范
  • 数据库名，表名，表别名，字段名，字段别名等都小写
  • SQL关键字，函数名，绑定变量等都大写

注释

单行注释: #注释文字(MySQL特有的方式)
单行注释: — 注释文字(— 后面必须包含一个空格)
多行注释: /* 注释文字 */

命名规则

  • 数据库，表名不得超过30个字符，变量名限制为29个
  • 必须只能包含A-Z，a-z，0-9，_共63个字符
  • 数据库名，表名，字段名等对象名中间不要包含空格
  • 同一个MySQL软件中，数据库不能同名;同一个库中，表不能重名;同一个表中，字段不能重名
  • 必须保证你的字段没有和保留字，数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用``引起来
  • 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就变成别的字符型了

SELECT

在生产环境下，不推荐直接使用SELECT * 进行查询。MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写，数据列和表名小写，读者也应该养成一个良好的变成习惯，这样写出来的代码更容易阅读和维护

列的别名

AS全称alias(别名)，可以省略，列的别名可以使用一对双引号引起来，不要使用单引号

1
2
SELECT employee_id emp_id, last_name AS lname, department_id "部门id", salary * 12 AS "annual sal"
FROM employees;

去除重复行

默认情况下，会查询全部行，包括重复行。所以我们可以用DISTINCT关键字去重，这里有亮点需要注意: 1.DISTINCT需要放到所有列名的签名，如果写成SELECT salary, DISTINCTROW department_id会报错。2. DISTINCT其实是对后面所有列名的组合进行去重，你能看到最后的结果是74条，因为这74个部门id不同，都有salary这个属性值。如果你想要看都有哪些不同的部门，只需要写DISTINCT department_id即可，后面不需要再加其他的列名了

1
2
SELECT DISTINCTROW department_id
FROM employees;

空值参与运算

所有运算符或列值遇到null值，运算的结果都为null

1
2
SELECT employee_id, salary, commission_pct, 12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;

这里一定要注意，在MySQL里面，空值不等于空字符串，一个空字符串的长度是0，而一个空值的长度为空。而且，在Mysql里面，空值是占空间的

着重号

1
SELECT * FROM `order`;

查询常数

SELECT查询还可以对常数进行查询。对的，就是在SELECT查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。SQL中的SELECT语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。不如说，我们想对employees数据表中的员工姓名进行查询，同时增加一列字段corporation，这个字段固定值为wxz，可以这样写

1
2
SELECT 'wxz' as corporation, last_name 
FROM employees;

显示表结构

使用DESCRIBE或DESC命令，表示表结构

1
DESCRIBE employees;

WHERE

使用WHERE子句，将不满足条件的行过滤掉

1
2
3
SELECT *
FROM employees
WHERE department_id = 90

算数运算符

算数运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加，减，乘，除和取模运算。注意: 在SQL中没有连接的作用，就表示加法运算，会将字符串转换为数值(隐式转换)

1
2
SELECT 100, 100 + 0, 100 + 50, 100 + 50 - 30, 100 + 35.5, 100 - 35.5
FROM DUAL;

一个整数类型的值对整数进行加法和减法操作，结果还是整数
一个整数类型的值对浮点数进行加法和减法操作，结果还是一个浮点数
加法和减法的优先级相同，进行先加后减操作与先减后加操作的结果是一样的
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果失败，就按0算(MySQL中字符串拼接要使用CONCAT()函数实现)

1
2
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 DIV 0
FROM DUAL;

一个数乘以整数1和除以整数1仍得原数
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相同
一个数除以整数后，不管是否能除尽，结果都为一个浮点数
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后四位
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同
在数学运算中，0不能作除数，在MySQL中，一个数除以0为NULL

比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。比较运算符经常用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录

运算符 名称 作用 示例
= 等于运算符 判断两个值，字符串或表达式是否相等 SELECT C FROM TABLE WHERE A = B
<=> 安全等于运算符 安全的判断两个值，字符串或表达式是否相等 SELECT C FROM TABLE WHERE A <=> B
<>(!=) 不等于运算符 判断两个值，字符串或表达式是否相等 SELECT C FROM TABLE WHERE A <> B
< 小于运算符 判断前面的值，字符串或表达式是否小于 SELECT C FROM TABLE WHERE A < B
<= 小于等于运算符 判断前面的值，字符串或表达式是否小于等于 SELECT C FROM TABLE WHERE A <= B
> 大于运算符 判断前面的值，字符串或表达式是否大于 SELECT C FROM TABLE WHERE A > B
>= 大于等于运算符 判断前面的值，字符串或表达式是否大于等于 SELECT C FROM WHERE A >= B

此外，还有非符号类型的运算符

运算符 名称 作用 示例
IS NULL 为空 判断是否为空 SELECT B FROM TABLE WHERE A IS NULL
IS NOT NULL 不为空 判断是否不为空 SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST 最小值 在多个值中返回最小值 SELECT D FROM TABLE WHERE C LEAST(A, B)
GREATEST 最大值 在多个值中返回最大值 SELECT D FROM TABLE WHERE C GREATEST(A, B
BETWEEN 两值之间 判断是否在两个值之间 SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL 为空 判断是否为空 SELECT B FROM TABLE WHERE ISNULL(A)
IN 属于 判断是否在列表里 SELECT D FROM TABLE WHERE C IN (A, B)
NOT IN 不属于 判断是否不再列表里 SELECT D FROM TABLE WHERE C NOT IN (A, B)
LIKE 模糊匹配 判断是否符合匹配 SELECT C FROM TABLE WHERE A LIKE B
REGEXP 正则 判断是否符合正则 SELECT C FROM TABLE WHERE A REGEXP B

^匹配以该字符后面的字符开头的字符串
$匹配以该字符前面的字符结尾的字符串
.匹配任何一个单字符
[...]匹配在方括号内的任何字符。例如，[abc]匹配a或b或c。为了命名字符的范围，使用一个-。[a-z]匹配任何字母
*匹配零个或多个在它前面的字符。例如，x*匹配任何数量的x字符，[0-9]*匹配任何数量的数字

1
SELECT 'wxz' REGEXP 'w'

逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1，0或者NULL

运算符 作用 示例
NOT 或 ! 逻辑非 SELECT NOT A
AND 或 && 逻辑与 SELECT A AND B
OR 逻辑或 SELECT A OR B
XOR 逻辑异或 SELECT A XOR B

OR可以和AND一起使用，但是在使用时注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合

位运算符

位运算是在二进制数上进行计算的运算符。位运算符先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制

运算符 作用 示例
& 按位与（位AND） SELECT A & B
｜ 按位或（位OR） SELECT A ｜ B
^ 按位异或（位XOR） SELECT A ^ B
~ 按位取反 SELECT ~A
>> 按位右移 SELECT A >> 2
<< 按位左移 SELECT B << 2

运算符优先级

优先级 运算符
1 :=， =
2 OR，XOR
3 &&，AND
4 NOT
5 BETWEEN，CASE，WHEN，THEN和ELSE
6 =，<=>，>=，>，<=，<，<>，!=，IS，LIKE，REGEXP和IN
7 位运算
8 &
9 <<与>>
10 -和+
11 *，/，DIV，%和MOD
12 ^
13 -（负号）和～（按位取反）
14 !
15 ()

数字编号越大，优先级越高，优先级高的运算符先进性计算。可以看到，赋值运算符的优先级最低，使用()括起来的表达式的优先级最高

ORDER BY

1
2
3
SELECT employee_id, salary, department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

可以使用不在SELECT列表中的列排序。在对多列进行排序的时候，首先排序的第一列必须有相同地列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序

分页

1
2
3
SELECT employee_id, salary, department_id
FROM employees
LIMIT 10;

MySQL8.0中可以使用LIMIT 3 OFFSET 4，意思是获取从第5条记录开始后面的3条记录，和LIMIT 4,3返回的结果相同。注意: LIMIT 子句必须放在整个SELECT语句的最后

多表查询

1
2
3
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id

非等值连接

1
2
3
SELECT last_name, salary, grade_level
FROM employees, job_grades
WHERE salary BETWEEN lowest_sal AND highest_sal

自连接

1
2
3
SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM employees emp, employees mgr
WHERE emp.manager_id = mgr.employee_id

内连接 VS 外连接

  • 内连接: 合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 了；两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接成为左（或右）外连接，没有匹配的行时，结果表中相应的列为空
1
2
3
SELECT employee_id, department_name
FROM employees e
LEFT JOIN departments d on e.department_id = d.department_id

UNION

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION操作符返回两个查询结果集的并集，去除重复记录。UNION ALL操作符返回两个查询结果集的并集，对于两个结果集的重复部分，不去重

执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高查询效率

NATURAL JOIN

SQL99在SQL92的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接

1
2
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

USING

与自然连接不同的是，USING制定了具体的相同的字段名称，你需要在USING的括号中填入要指定同名字段，同时使用JOIN USING可以简化JOIN ON的等值连接

1
2
3
SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d 
USING(department_id)

单行函数

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。MySQL提供的内置函数从实现的功能角度可以分为数值函数，字符串函数，日期和时间函数，流程控制函数，加密与解密函数，获取MySQL信息函数，聚合函数等。这里我将这些丰富的函数再分为两类: 单行函数，聚合函数

数值函数

函数 用法
ABS(x) 返回x的绝对值
SIGN(x) 返回x的符号。整数返回1，负数返回-1，0返回0
PI() 返回圆周率的值
CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数
FLOOR(x) 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3) 返回列表中的最小值
GREATEST(e1,e2,e3) 返回列表中的最大值
MOD(x,y) 返回x除以y后的余数
RAND() 返回0~1的随机值
RAND(x) 返回0~1的随机值，其中x的值作为种子值，相同的x值会产生相同的随机数
ROUND(x) 返回一个对x的值进行四舍五入后最接近x的整数
ROUND(x,y) 返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面y位
TRUNCATE(x,y) 返回数字x截断y位小数的结果
SQRT(x) 返回x的平方根。当x位负数时，返回NULL

字符串函数

函数 用法
ASCII(S) 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s) 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2) 连接s1，s2位一个字符串
CONCAT_WS(x,s1,s2) 同CONCAT函数，但是每个字符串之间要加上x
INSERT(str,idx,len,replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str,a,b) 用字符串b代替str中所出现的字符串a
UPPER(s)或UCASE(s) 将字符串s的所有字母转成大写
LOWER(s)或LCASE(s) 将字符串s的所有字母转成小写
LEFT(str,n) 返回字符串str最左边的n个字符
RIGHT(str,n) 返回字符串最右边的n个字符
LPAD(str,len,pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str,len,pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符

日期和时间函数

获取日期时间

函数 用法
CURDATE()，CURRENT_DATE() 返回当前日期，只包含年月日
CURTIME()，CURRENT_TIME() 返回当前时间，只包含时分秒
NOW()/SYSDATE()/CURRENT_TIMESTAMP()/LOCALTIME()/LOCALTIMESTAMP() 返回当前系统日期时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间

日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() -> 1677764526
UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp) 将UNIX时间戳转换为普通格式的时间

获取月份星期等函数

函数 用法
YEAR(date)/MONTH(date)/DAY(date) 返回具体日期值
HOUR(time)/MINUTE(time)/SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份: January
DAYNAME(date) 返回星期几: MONDAY
WEEKDAY(date) 返回周几，注意: 周一是0，周二是1
QUARTER(date) 返回日期对应的季度，范围为1~4
WEEK(date)，WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，注意: 周日是1，周一是2

日期的操作函数

EXTRACT(type FROM date)返回指定日期中特定的部分，type指定返回的值。EXTRACT(type FROM date)函数中type 的取值与含义:

type取值 含义
MICROSECOND 返回毫秒数
SECOND 返回秒数
MINUTE 返回分钟数
HOUR 返回小时数
DAY 返回天数
WEEK 返回日期在一年中的第几个星期
MONTH 返回日期在一年中的第几个月
QUARTER 返回日期在一年中的第几个季度
YEAR 返回日期的年份

时间和秒钟转换的函数

函数 用法
TIME_TO_SEC(time) 将time转化为秒并返回结果。转化的公式为: 小时*3600+分钟*60+秒
SEC_TO_TIME(seconds) 将seconds描述转化为包含小时，分钟和秒的时间

计算日期和时间的函数

函数 用法
DATE_ADD(datetime,INTERVAL expr type)，
ADDDATE(date,INTERVAL expr type)		 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type),
SUBDATE(date,INTERVAL expr type)		 返回与date相差INTERVAL时间间隔的日期

日期格式化与解析

函数 用法
DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
STR_TO_DATE(str,fmt) 按照字符串fmt对str进行解析，解析为一个日期

流程控制函数

流程控制函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()，IFNULL()和CASE()函数

函数 用法
IF(value,value1,value2) 如果value的值为TRUE，返回value1,否则返回value2
IFNULL(value1,value2) 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN结果1 WHEN 条件2 THEN 结果2 相当于Java的 if else
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 相当于Java的switch case 
1
2
3
4
5
SELECT CASE department_id 
  WHEN 10 THEN salary * 1.1 
  WHEN 20 THEN salary * 1.2 
  WHEN 30 THEN salary * 1.3 ELSE salary * 1.4	END "details" 
FROM employees;

加密与解密函数

加密与解密函数主要用于数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用

函数 用法
PASSWORD(str) [8.0弃用] 返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户密码加密
MD5(str) 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str) 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全
ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value [8.0弃用]
DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value [8.0弃用]

聚合函数

聚合函数作用域一组数据，并对一组数据返回一个值

AVG / SUM

1
2
SELECT AVG(salary), SUM(salary), AVG(salary) * 107
FROM employees;

MAX / MIN

1
2
SELECT MAX(salary), MIN(salary)
FROM employees;

COUNT

计算指定字段中在查询结果中出现的个数(不包含空值)

1
SELECT COUNT(*) FROM employees;

用count(*)，count(1)，count(列名)哪个更好

其实对于MuISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(*)，count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。不要使用count(列名)来代替count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关

GROUP BY

1
2
3
SELECT department_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY department_id;

SELECT出现的非组函数的字段必须声明在GROUP BY中，反之，GROUP BY中声明的字段可以不出现在SELECT中。GROUP BY出现在FROM后面，WHERE后面，ORDER BY前面，LIMIT前面。当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY 是互斥排斥的

HAVING

如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE，否则报错；当过滤条件中没有聚合函数时，则此过滤过滤条件声明在WHERE或HAVING中都可以，但是建议大家声明在WHERE。HAVING必须是声明在GROUP BY 的后面。开发中HAVING不不能单独使用，必须跟GROUP BY一起使用

1
2
3
4
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000

WHERE与HAVING对比

从使用范围上来讲，HAVING的适用范围更广。如果过滤条件中没有聚合函数，这种情况下WHERE的执行效率要高于HAVING

SQL底层执行原理

SQL92 SELECT语句的完整结构: 

1
2
3
4
5
6
7
SELECT ...，...，...（存在聚合函数）
FROM ...，...，...
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...，...，...，
HAVING 包含聚合函数的过滤条件
ORDER BY ...，...（ASC / DESC）
LIMIT ...，...

SQL99 SELECT语句的完整结构:

1
2
3
4
5
6
7
SELECT ...，...，...（存在聚合函数）
FROM ... （LEFT / RIGHT）JOIN ... ON 多表的连接条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ...，...，...，
HAVING 包含聚合函数的过滤条件
ORDER BY ...，...（ASC / DESC）
LIMIT ...，...

SQL语句执行过程

  • FROM
  • ON
  • LEFT / RIGHT JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • LIMIT

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。SQL查询中子查询的使用大大增加了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果进行比较。子查询要包含在括号内；将子查询放在比较条件的右侧；单行操作符对应单行子查询，多行操作符对应多行子查询

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询，多行子查询。我们按内查询是否被执行多次，将子查询划分为相关子查询和不相关子查询。子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。同样，如果子查询需要被执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询

单行子查询

1
2
3
4
5
6
SELECT employee_id, last_name, salary
FROM employees 
WHERE salary > (
  SELECT salary 
  FROM employees 
  WHERE employee_id = 149 );

多行子查询

1
2
3
4
5
6
7
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG');

相关子查询

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了关联，因此没执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一回子查询

1
2
3
4
5
6
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id)

创建和管理数据库

创建数据库

  • 创建数据库 CREATE DATABASE 数据库名;
  • 创建数据库并指定字符集 CREATE DATABASE SET 字符集;
  • 判断数据库是否已经存在，不存在则创建数据库（推荐）CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。注意: DATABASE不能改名。一些可视化工具可以改名，它是新建库，把所有表复制到新库，再删除旧库完成的

使用数据库

  • 查看当前所有的数据库 SHOW DATABASE; 有一个S，代表多个数据库
  • 查看当前正在使用的数据库 SELECT DATABASE(); 使用一个MySQL中的全局函数
  • 查看指定库下所有的表 SHOW TABLES FROM 数据库名;
  • 查看数据库的创建信息 SHOW CREATE DATABASE 数据库名; 或者 SHOW CREATE DATABASE 数据库名\G
  • 使用 / 切换数据库 USE 数据库名;

修改数据库

  • ALTER DATABASE 数据库名 CHARACTER SET 字符集; 比如: gbk，utf8等

删除数据库

  • 删除指定数据库 DROP DATABASE 数据库名;
  • 数据指定的数据库（推荐）DROP DATABASE IF EXISTS 数据库名

MySQL中的数据类型

类型 类型举例
整数类型 TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
浮点类型 FLOAT，DOUBLE
定点数类型 DECIMAL
位；诶行 BIT
日期时间类型 YEAR，TIME，DATE，DATETIME，TIMESTAMP
文本字符串类型 CHAR，VARCHAR，TINYTEXT，MEDIUMTEXT，LONGTEXT
枚举类型 ENUM
集合类型 SET
二进制字符串类型 BINARY，VARBINARY，TINYBLOB，BLOB，MEDIUMBLOB，LONGBLOB
JSON类型 JSON对象，JSON数组
空间数据类型 单值: GEOMETRY，POINT，LINESTRING，POLYGON
集合: MULTIPOINT，MULTILINESTRING，MULTIPOLYGON，GEOMETRYCOLLECTION

其中，常用的几类类型介绍如下

数据类型 描述
INT 从-2^31到2^31的整形数据。存储大小为4个字节
CHAR(size) 定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size) 可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M.D) 单精度，占用4个字节，M = 整数位 + 小数位，D = 小数位。D <= M <= 255， 0 <= D <= 30，M + D <= 6
DOUBLE(M,D) 双精度，占用8个字节，D <= M <= 255，0 <= D <= 30，默认M + D <= 15
DECIMAL(M, D) 高精度小数，占用M + 2个字节，D <= M <= 65，0 <= D <= 30，最大取值范围与DOUBLE相同
DATE 日期型数据，格式YYYY-MM-DD
BLOB 二进制形式的长文本数据，最大可达4G
TEXT 长文本数据，最大可达4G

整数类型

类型介绍

整数类型一共包括5种，包括TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。它们的区别如下表所示：

整数类型 字节 有符号数取值范围 无符号数取值范围
TINYINT 1 -128~127 0~255
SMALLINT 2 -32768~32767 0~65535
MEDIUMINT 3 -8388608~8388607 0~16777215
INT 4 -2147483648~2147483647 0~4294967295
BIGINT 8 -9223372036854775808~9223372036854775807 0~18446744073709551615

可选属性

整数类型的可选属性有三个：

  • M

表示显示宽度，M的取值范围是（0, 255）例如，int(5)：当数据宽度小于5位的时候在数字前面需要用字符填满宽度，该项功能需要配合ZEROFILL使用，表示用0填满宽度，否则指定显示宽度无效

思考：如果设置了显示宽度，那么插入的数据超过显示宽度限制，会不会截断或插入失败？

不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。整形数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值

  • UNSIGNED

无符号数据类型（非负）所有的整数类型都有一个可选的属性UNSIGNED（无符号属性）无符号整数类型的最小值取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置成无符号类型。int类型默认显示宽度为int(11)，无符号类型默认显示宽度为int(10)

  • ZEROFILL

0填充（如果某列时ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性）如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。原来，在int(M)中，M的值跟int(M)所占多少存储空间并无任何关系，int(3)，int(4)，int(5)在磁盘上都是占用4bytes的存储空间。也就是说，int(M)必须和UNSIGNED ZEROFILL一起使用才有意义。如果整数超过M位，就按照实际位数填充，只是无需再用字符0填充

适用场景

  • TINYINT：一般用于枚举数据，比如系统设定取值范围很小且固定的场景
  • SMALLINT：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量
  • MEDIUMINT：用于较大整数的计算，比如车站每日客流量等
  • INT：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号
  • BIGINT：只有当你处理特别巨大的整数时才会用到。比如双11的交易量，大型门户网站的点击量，证劵公司衍生持仓等

浮点类型

类型介绍

浮点数和定点数类型的特点时可以处理小数，你可以把整数看成小数的一个特例，因此，浮点数和定点数的使用场景比整数大多了。MySQL支持的浮点数类型，分别是FLOAT，DOUBLE，REAL。FLOAT表示单精度浮点数（4个字节）DOUBLE（8个字节）表示双精度浮点数；REAL默认就是DOUBLE。如果你把SQL模式设定为启用REAL_AS_FLOAT，那么MySQL就会认为REAL是FLOAT。如果要启用REAL_AS_FLOAT，可以通过SQL语句实现：SET sql_mode = REAL_AS_FLOAT

FLOAT和DOUBLE这两种数据类型有什么区别？

FLOAT占用字节少，取值范围小；DOUBLE占用字节数多，取值范围大

为什么浮点数的无符号数取值范围只相当于有符号数取值范围的一半？

MySQL存储浮点数的格式为：符号(S)，尾数(M)和阶码(E)。因此，无论有没有符号，MySQL的浮点数都会存储表示符号的部分。因此，所谓的无符号数取值范围，其实就是有符号取值范围大于等于0的部分

数据精度说明

对于浮点类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。MySQL允许使用非标准语法（其他数据库未必支持，因此涉及到数据迁移，则最好不要这么用）FLOAT(M,D)或DOUBLE(M,D)，这里M称为精度，D称为标度。(M,D)中M = 整数位 + 小数位，D = 小数位。D <= M <= 255，0 <= D <= 30。例如，定义为FLOAT(5,2)的一个列可以显示为-999.99~999.99，超过这个范围会报错。FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。浮点类型也可以加UNSIGNED，但是不会改变数据范围，例如FLOAT(3,2)UNSIGNED仍然只能表示0～9.99的范围。不管是否显示设置了精度(M,D)，这里MySQL的处理方案如下：

  • 如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值
  • 如果存储时，小数点部分若超出范围，就分以下情况：
  • 若四舍五入后，整数部分没有超出范围，则只是警告，但能成功操作并四舍五入删除多余的小数位后保存。例如在FLOAT(5,2)列内插入999.009，近似结果是999.01
  • 若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理，如FLOAT(5,2)列内插入999.995和-999.995都会报错

精度误差说明

浮点数类型有个缺陷，就是不精准。比如我们设计一个表，有f1这个字段，插入值分别为0.47，0.44，0.19，我们期待的运行结果：0.47 + 0.44 + 0.19 = 1.1，而使用sun之后查询是1.099999999999999999,虽然误差很小，但确实有误差

MySQL用4个字节存储FLOATL诶行数据，用8个字节存储DOUBLE类型数据，无论哪个，都是采用二进制的方式来进行存储，比如9.625，用二进制来表达就是1001.101，或者表打成1.001101 x 2^3。如果尾数不是0或5（比如9.624）你就无法用一个二进制数来精确表达，进而就只好在取值允许的范围内进行四舍五入。在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用 = 来判断两个数是否相等。同时，在一些对精准度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL有没有精准的数据类型呢？当然有，这就是定点数类型：DECIMAL

定点数 类型

类型介绍

MySQL中定点数类型只有DECIMALZ一种类型

数据类型 字节数 含义
DECIMAL(M,D)，DEC，NUMERIC M + 2字节 有效范围由M和D决定

使用DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M + 2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。当DECIMAL不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQLT同样会进行四舍五入处理

浮点数和定点数区别和使用场景？

浮点数对于定点数的优点是在长度一定的情况下，浮点数取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学，分子建模，流体动力）

定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及到金额计算的场景）

由于DECIMAL数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差

位类型

BIT类型中存储的是二进制值，类似010110

为紧致字符串类型 长度 长度范围 占用空间
BIT(M) M 1 <= M <= 64 约为(M + 7) / 8个字节

BIT类型，如果没有指定(M)，默认就是1位。这个1位，表示只能存1位的二进制值。这里(M)表示二进制的位数，位数最小值为1，最大值为64

日期与时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。MySQL有多种表示日期和时间的数据类型，不同的版本有可能有所差异，MySQL 8.0 B版本支持的日期和时间类型主要有：YEAR类型，TIME类型，DATE类型，DATETIME类型和TIMESTAMP类型

  • YEAR类型通常用来表示年
  • DATE类型通常用来表示年，月，日
  • TIME类型通常用来表示时，分，秒
  • DATETIME类型通常用来表示年，月，日，时，分，秒
  • TIMESTAMP类型通常用来表示带时区的年，月，日，时，分，秒
类型 名称 字节 日期格式 最小值 最大值
YEAR 年 1 YYYY或YY 1901 2155
TIME 时间 3 HH:MM:SS -838:59:59 838:59:59
DATE 日期 3 YYYY-MM-DD 1000-01-01 9999-12-03
DATETIME 日期时间 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP 日期时间 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:00 UTC 2038-01019 03:14:07 UTC

为什么时间类型TIME的取值范围不是-23:59:59 ~ 23:59:59呢？

原因是MySQL设计的TIME类型。不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过24小时

YEAR

YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间。在MySQL中，YEAR有以下集中存储格式：

  • 以4位字符串或数字格式表示YEAR类型，其格式位YYYY，最小值位1901，最大值位2155
  • 以2位字符串格式表示YEAR类型，最小值为00，最大值为99
  • 当取值为01到69时，表示2001到2069
  • 当取值为70到99时，表示1970到1999
  • 当取值整数的0或00添加的话，那么是0000年
  • 当取值是日期/字符串的0添加的话，是2000年

从MySQL 5.5.27开始，2位格式的YEARyI经不推荐使用。YEAR默认格式就是YYYY，没必要写成YEAR(4)，从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型

DATE

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示年份，DD表示日期。需要3个字节的存储空间，在向DATE类型的字段插入数据时，同样需要满足一定的格式条件：

  • 以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，其最小值取值为1000-01-01，最大值取值为9999-12-01。YYYYMMDD格式会被转化为YYYY-MM-DD格式
  • 以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被传话为2000到2069；当年份取值为70到99时，会被转化为1970到1999
  • 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期

TIME

TIME类型用来表示时间，不包含日期部分，在MySQL中，需要3个字节的存储空间来存储TIME类型的数据，可以使用HH:MM:SS格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。在MySQL中，向TIME类型的字段插入数据时，也可以使用集中不同的格式

  • 可以使用带有冒号的字符串，比如D HH:MM:SS，HH:MM:SS，HH:MM，D HH:MM，D HH或SS格式，都能够被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D * 24 + HH，当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10
  • 可以使用不带有冒号的字符串或者数字，格式为HHMMSS。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒表示00:12:10，而不是12:10:00
  • 使用CURRENT_TIME()或者NOW()，会插入当前系统的时间

DATETIME

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为DATE类型和TIME的组合，可以表示为YYYY-MM-DD HH:MM:SS，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件：

  • 以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59，以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DD HH:MM:SS格式
  • 以YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999
  • 使用函数CURRENT_TIMESTAMP()和NOW()，会插入当前系统的时间

TIMESTAMP

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM;SS，需要4个字节的存储空间，但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC之间的时间。其中，YTC表示世界统一时间，也叫做世界标准时间

  • 存储数据的时候需要对当前时间所在时区进行转换，查询数据的时候再将时间转换回当前的时区。因此使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间

向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。如果向TIMESTAMP类型的字段插入的时间超过了TIMESTAMP类型的范围，则MySQL会抛出错误信息

TIMESTAMP和DATETIME的区别

  • TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
  • 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，举例1970-1-1 0:0:0 0毫秒的毫秒
  • 两个日期比较大小或日期计算时，TIMESTAMP更方便，更快
  • TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果，而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差

文本字符串类型

在实际项目中。我们还经常遇到一种数据，就是字符串数据。MySQL中，文本字符串总体上分为CHAR，VARCHAR，TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT，ENUM，SET等类型

文本字符串类型 值的长度 长度范围 占用的存储空间
CHAR(M) M 0 <= M <= 255 M个字节
VARCHAR(M) M 0 <= M <= 65535 M + 1个字节
TINYTEXT L 0 <= M <= 255 L + 2个字节
TEXT L 0 <= L <= 65535 L + 2个字节
MEDIUMTEXT L 0 <= L <= 16777215 L + 3个字节
LONGTEXT L 0 <= L <= 4294967295 L + 4个字节
ENUM L 1 <= L <= 65535 1或2个字节
SET L 0 <= L <= 64 1,2,3,4或8个字节

CHAR和VARCHAR类型

CHAR和VARCHAR都可以存储比较短的字符串

  • CHAR(M)类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是一个字符
  • 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格
  • 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数
  • VARCHAR(M)定义时，必须指定长度M，否则报错
  • MySQL 4.0版本以上，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）MySQL 5.0以上版本，varchar(20)：指的是20字符
  • 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节

哪些情况下使用CHAR或VARCHAR更好？

类型 特点 空间 时间上 适用场景
CHAR(M) 固定长度 浪费存储空间 效率高 存储不大，速度要求高
VARCHAR(M) 可变长度 节省存储空间 效率低 非CHAR的情况

TEXT

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT类型。在向TEXT类型的字段保存和查询数据时，系统会自动按照实际长度存储，不需要预先定义长度，这一点和VARCHAR类型相同。每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

文本字符串类型 特点 长度 长度范围 占用的存储空间
TINYTEXT 小文本，可变长度 L 0 <= L <= 255 L + 2个字节
TEXT 文本，可变长度 L 0 <= L <= 65535 L + 2个字节
MEDIUMTEXT 中等文本，可变长度 L 0 <= L <= 16777215 L + 3个字节
LONGTEXT 大文本，可变长度 L 0 <= L <= 4294967295(相当于4GB) L + 4个字节

由于实际存储的长度不确定，MySQL不允许TEXT类型的字段做主键。遇到这种情况，你只能采用CHAR或者VARCHAR

阿里巴巴开发手册之MySQL数据库

  • 任何字段如果为非负数，必须使用UNSIGNED
  • 小数类型为DECIMAL，禁止使用FLOAT和DOUBLE。在存储的时候，FLOAT和DOUBLE都存在精度损失的问题，很有可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过DECIMAL范围，建议将数据拆成整数和小数并分开存储
  • 如果存储的字符串长度几乎相等，使用CHAR定长字符串类型
  • VARCHAR是可变长字符串，不预先分配存储空间，长度不要超过5000。如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键对应，避免影响其他字段索引效率

创建表

1
2
3
4
5
6
7
CREATE TABLE [IF NOT EXISTS] 表名 {
  字段1，数据类型 [约束条件] [默认值]，
  字段2，数据类型 [约束条件] [默认值]，
  字段3，数据类型 [约束条件] [默认值]，
  ......
  [表约束条件]
};
1
2
3
4
CREATE TABLE myemp2
AS
SELECT employee_id, last_name, salary
FROM employees;

加上了IF NOT EXISTS关键字，则表示: 如果当前数据库中不存在要创建的数据表，则创建数据表；如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表

使用VARCHAR来定义字符串，必须在使用VARCHAR时指定其长度；如果创建表时没有指明使用的字符集，则默认使用表所在的数据库的字符集；查询语句中的别名可以作为新创建的表的字段的名称

修改表

添加一个字段

  • ALTER TABLE myemp1 ADD salary DOUBLE(10,2); 默认添加到表中的最后一个字段的位置
  • ALTER TABLE myemp1 ADD phone_number VARCHAR(20) FIRST;
  • ALTER TABLE myemp1 ADD email VARCHAR(45) AFTER emp_name;

修改一个字段

  • ALTER TABLE myemp1 MODIFY emp_name VARCHAR(25);
  • ALTER TABLE myemp1 MODIFY emp_name VARCHAR(25) DEFAULT aaa;

重命名一个字段

  • ALTER TABLE myemp1 CHANGE salary month_salary DOUBLE(10,2);
  • ALTER TABLE myemp1 CHANGE email my_email VARCHAR(50);

删除一个字段

  • ALTER TABLE myemp1 DROP COLUMN my_email;

重命名表

  • RENAME TABLE emp TO myemp;
  • ALTER TABLE emp RENAME TO emp2;

删除表

  • DROP TABLE IF EXISTS emp; 不能回滚

清空表

  • TRUNCATE TABLE emp; 删除表中所有数据；释放表中的存储空间，TRUNCATE不能回滚，而DELETE语句可以回滚

TRUNCATE TABLE 和 DELETE TABLE

相同点：都可以实现对表中所有数据的删除，同时保留表结构
不同点：TRUNCATE TABLE一旦执行此操作，表数据全部清除。同时数据不可以回滚；DELETE TABLE一旦执行此操作，表数据可以全部清除（不带WHERE）。同时数据可以实现回滚

DDL 和 DML

DDL的操作一旦执行，就不可以回滚。因为在执行完DDL操作之后一定会执行一次COMMIT，而此操作不受SET autocommit = FALSE影响的
DML的操作默认情况下，一旦执行，也是不可以回滚的，但是，如果在执行DML之前，执行了SET autocommit = FALSE，则执行的DML操作就可以回滚

阿里开发规范：TRUNCATE TABLE 比 DELETE TABLE速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，又肯呢个造成事故，故不建议在开发中使用此语句。说明：TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同

COMMIT和ROLLBACK

  • COMMIT：提交数据。一旦执行COMMIT，则数据就被永久的保存在了数据库中，意味着不能回滚
  • ROLLBACK：回滚数据。一旦执行ROLLBACK，则可以实现数据的回滚。回滚到最近的一次COMMIT之后

DML之添加数据（INSERT）

一条一条的添加数据：INSERT INTO emp(id, hire_date, salary, name) VALUES(1, Tom, 2021-12-21, 3400)。INSERT语句可以同时向数据库插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法如下：

1
2
3
4
5
6
INSERT INTO table_name(column1, [, column2, ..., columnn])
VALUES
(value1 [value2, ..., valuen]),
(value1 [value2, ..., valuen]),
...
(value1 [value2, ..., valuen])

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

  • Records：表名插入的记录条数
  • Duplicates：表名插入时被忽略的记录，原因可能是这些记录包含了重复的主键值
  • Warning：表名有问题的数据值，例如发生数据类型转换

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高，因为在MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在多条记录时最好选择使用单条INSERT语句的方式插入

注意: VALUES也可以写成VALUE，但是VALUES是标准写法；字符和日期类型数据应包含在单引号中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速的从一个或多个表中插入多行，基本语法如下：

1
2
3
4
5
INSERT INTO table_name(column1, [, column2, ..., columnn])
SELECT
(column1, [, column2, ..., columnn])
FROM 源表名
[WHERE <condition>]
  • 在INSERT子句中假如子查询
  • 不必书写VALUES子句
  • 子查询中的值列表应与INSERT子句中的列名对应
1
2
3
4
INSERT INTO emp2
SELECT * 
FROM employees
WHERE department_id = 90;

DML之更新数据

使用UPDATE语句更新数据。语法如下：

1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, column3 = value3
[WHERE <condition>]

可以一次更新多条数据；如果数据需要回滚，需要保证在DML之前，进行设置：SET AUTOCOMMIT = FALSE;

DML之删除数据

使用DELETE语句从表中删除数据。语法如下：

1
DELETE FROM table_name [WHERE <ondition>];

table_name指定要删除的表；[WHERE ]为可选条件，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录

MySQL8新特性：计算列

什么叫计算列？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1，b列值为2，c列不需要手动插入，定义a + b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。在MySQL8.0中，CREATE TABLE和ALTER TABLE中都支持增加计算列。下面以CREATE TABLE为例：

1
2
3
4
5
6
CREATE TABLE tb (
  id INT,
  a INT,
  b INT,
  c INT GENERATEN ALWAYS AS (a + b) VIRTUAL
);