【史上最全MYSQL总结】MySQL超详细笔记

8.2.4 、列的别名

列 AS ‘列名’

#查询员工表中所有员工的编号、姓名、日薪(列的运算 / 22)，列名均为中文

SELECT EMPLOYEE_ID AS ‘编号’,FIRST_NAME AS ‘姓’,LAST_NAME AS ‘名’,SALARY / 22 AS’日薪’ FROM t_employees;

#起别名，没有对原表的列名发生影响

8.2.5、 查询结果去重

distinct 列名

#查询员工表中，所有经理的ID编号

SELECT DISTINCT MANAGER_ID AS ‘经理编号’ FROM t_employees;

#查询员工表中，所有的工资 (去掉重复的)

SELECT DISTINCT SALARY FROM t_employees;

8.3 、排序查询

---

语法： SELECT 列名 FROM 表名 ORDER BY 排序列名 [排序规则]

| 排序规则 | 描述 |

| — | — |

| ASC | 做升序排序 |

| DESC | 做降序排序 |

8.3.1、依据单列进行排序

#查询员工的编号，名字，薪资，按照工资进行升序排序

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY salary ASC;

#查询员工的编号，名字，薪资，按照姓名进行升序排序

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY FIRST_NAME ASC;

#查询员工的编号，名字，薪资，按照工资进行降序排序

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees ORDER BY salary DESC;

• 当进行升序排序时，排序规则可以不显示声明。默认为升序排序规则

8.3.2 、依据多列进行排序

#查询员工编号，名字，薪资，按照工资进行升序排序，如果工资相等，按照编号降序排序

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees

ORDER BY SALARY ASC,EMPLOYEE_ID DESC

#查询员工编号，名字，薪资，按照工资进行升序排序，如果工资相等，按照姓名降序排序

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees

ORDER BY SALARY ASC,FIRST_NAME DESC

8.4 、条件查询

---

语法： SELECT 列名 FROM 表名 WHERE 条件

| 关键字 | 描述 |

| :-: | :-: |

| WHERE | 在查询结果中，筛选符合条件的查询结果。条件为布尔表达式 |

8.4.1 、等值判断(=)

#查询工资为2500的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE salary = 2500;

#查询姓为Steven的

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE FIRST_NAME=‘Steven’;

• 注意：与Java不同(==),MySQL中等值判断用 =

8.4.2、不等值判断(>、<、>=、<=、!=、<>)

#查询员工工资大于6000的员工的信息

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary>=6000;

#查询员工工资不等于2500的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary<>2500;(!=同理)

8.4.3、逻辑判断(and、or、not)

#查询员工工资在6000~10000的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary>=6000 AND salary<=10000;

#查询工资是10000的或者是9000的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary =10000 OR salary = 9000;

#查询除了工资是10000的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE NOT salary =10000;

8.4.4 、区间判断(between and)

#区间判断 包含区间边界的两个值

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary BETWEEN 6000 AND 10000;

• 注意：between and要遵循 between 小值 and 大值；

8.4.5 、NULL值判断(IS NULL、IS NOT NULL)

IS NULL

• 列名 IS NULL

IS NOT NULL

• 列名 IS NOT NULL

#查询出 没有经理编号的员工 IS NULL

SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees

WHERE MANAGER_ID IS NULL;

#查询出 没有经理编号以外的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees

WHERE MANAGER_ID IS NOT NULL;

#查询出 没有经理编号以外的员工信息(此处NOT为取反。两个结果)

SELECT EMPLOYEE_ID,FIRST_NAME,MANAGER_ID FROM t_employees

WHERE NOT MANAGER_ID IS NULL;

8.4.6 、枚举查询(IN (值1,值2,值n…))

#枚举查询 IN (值1,值2,值n…)

#查询部门编号为70,80,90的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,DEPARTMENT_ID FROM t_employees

WHERE DEPARTMENT_ID IN(70,80,90);

#枚举查询 查询经理编号为 124 和100的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID FROM t_employees

WHERE MANAGER_ID IN(124,100);

8.4.7、 模糊查询(_、%)

LIKE

• LIKE _(单个任意字符)

*   列名 LIKE ‘S\_’

• LIKE %(任意长度的任意字符 0~n个)

*   列名 LIKE ‘S%’

#模糊查询，查询姓氏以S开头且长度为6的员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees

WHERE FIRST_NAME LIKE ‘S_____’;

#模糊查询，查询姓氏以S开头任意长度的所有员工信息

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees

WHERE FIRST_NAME LIKE ‘S%’;

8.4.8 、分支结构查询

CASE

WHEN 条件1 THEN 结果1

WHEN 条件2 THEN 结果2

WHEN 条件3 THEN 结果3

WHEN 条件4 THEN 结果4

ELSE 结果

END

• 通过使用CASE END进行条件判断，每条数据对应生成一个值

• 类似Java中的分支结构

#查询员工信息(编号、名字、薪资、薪资级别<条件表达式>)

SELECT EMPLOYEE_ID,FIRST_NAME,salary,

CASE

WHEN salary >=10000 THEN ‘A’

WHEN salary >=8000 AND salary<10000 THEN ‘B’

WHEN salary >=6000 AND salary<8000 THEN ‘C’

WHEN salary >=4000 AND salary <6000 THEN ‘D’

ELSE ‘E’

END AS ‘薪资级别’

FROM t_employees;

• 注意：case分支结构产生一个新的列

8.5、 时间查询

---

语法： SELECT 时间函数([参数列表]);

• 执行时间函数查询，会生成一张虚拟表(一行一列)

| 时间函数 | 描述 |

| :-: | :-: |

| SYSDATE() | 当前系统时间(年、月、日、时、分、秒) |

| CURDATE() | 获得当前日期 |

| CURTIME() | 获得当前时间 |

| WEEK(DATE) | 获得指定日期是一年中第几周 |

| YEAR(DATE) | 获得指定日期的年份 |

| MONTH(DATE) | 获得指定日期的月份 |

| DAY(DATE) | 获得指定日期的天 |

| HOUR(DATE) | 获得指定时间的小时值 |

| MINUTE(DATE) | 获得指定时间的分钟值 |

| SECOND(DATE) | 获得指定日期的秒值 |

| DATEDIFF(DATE1,DATE2) | 获得DATE1和DATE2之间相隔的天数 |

| ADDDATE(DATE,N) | 在指定日期加上N天后的日期 |

#1.当前系统时间

SELECT SYSDATE();

#2.获得当前日期

SELECT CURDATE();

#3.获得当前时间

SELECT CURTIME();

#4.获得指定日期在一年中为第几周

SELECT WEEK(CURDATE());

#5.获取指定日期中的年份

SELECT YEAR(CURDATE());

#6.获取指定日期中的月份

SELECT MONTH(CURDATE());

#7.获取指定日期中的日

SELECT DAY(CURDATE());

#8.获取指定日期中的时

SELECT HOUR(SYSDATE());

#9.获取指定日期中的分

SELECT MINUTE(SYSDATE());

#10.获取指定日期中的秒

SELECT SECOND(SYSDATE());

#11.获取Date1和Date2之间相隔的天数

SELECT DATEDIFF(SYSDATE(),‘2019-3-26’);

#12.在指定日期之上加N天后的日期

SELECT ADDDATE(SYSDATE(),6);

8.6 、字符串查询

---

语法：SELECT 字符串函数([参数列表]);

• 执行字符串函数，产生一张虚拟表,（一行一列）

| 字符串函数 | 说明 |

| :-: | :-: |

| CONCAT(str1,str2,str3…) | 将多个字符串进行拼接 |

| INSERT(str,pos,len,newStr) | 将str中指定pos位置开始len长度的内容替换为newStr |

| LOWER(str) | 将指定字符串转换为小写 |

| UPPER(str) | 将指定字符串转换为大写 |

| SUBSTRING(str,pos,len) | 将str字符串指定pos位置开始截取len个内容 |

#1.连接将多个字符串连接在一起

SELECT CONCAT(‘My’,‘S’,‘QL’);

#2.插入替换（下标从1开始）

SELECT INSERT(‘这是MySQL数据库’,3,5,‘Oracle’);

#3.转小写

SELECT LOWER(‘MYSQL’);

#4.转大写

SELECT UPPER(‘mysql’);

#5.截取

SELECT SUBSTRING(‘kak真的太帅了！我的天呀’,3,4);

8.7、聚合函数

---

语法：SELECT 聚合函数(列名) FROM 表名;

• 聚合函数式对多条数据的单列进行统计，返回统计后的一行结果

| 聚合函数 | 说明 |

| :-: | :-: |

| COUNT() | 求总行数 |

| SUM() | 求单列中所有行的总和 |

| AVG() | 求单列中所有行的平均值 |

| MAX() | 求单列中所有行的最大值 |

| MIN() | 求单列中所有行的最小值 |

8.7.1 、求总行数

#1.查询员工一共多少人

SELECT COUNT(EMPLOYEE_ID) AS ‘员工总数’ FROM t_employees;

SELECT COUNT(MANAGER_ID) AS ‘经理总数’ FROM t_employees;

SELECT COUNT(*) FROM t_employees;

• 注意：聚合函数中，自动忽略null值。不进行统计

8.7.2、单列总和

#2.查询员工每个月工资的总和

SELECT SUM(salary) FROM t_employees;

8.7.3、单列平均值

#3.查询员工每个月工资的平均工资

SELECT AVG(salary) FROM t_employees;

8.7.4、单列最大值

#4.查询月薪最高的

SELECT MAX(salary) FROM t_employees;

8.7.5 、单列最小值

#5.查询月薪最低的

SELECT MIN(salary) FROM t_employees;

8.8、分组查询

---

语法： SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列名)

| 关键字 | 说明 |

| :-: | :-: |

| GROUP BY | 分组依据。如果有WHERE,在WHERE之后生效 |

8.8.1、查询各部门的总人数

#思路：

#1.先按照部门编号分组(分组依据是：department_id)

#2.再针对各部门的人数进行统计(count)

SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID)

FROM t_employees

GROUP BY DEPARTMENT_ID;

8.8.2、查询各部门的平均工资

#思路：

#1.先按照部门编号分组(分组依据是：department_id)

#2.再针对各部门的工资进行平均计算(AVG())

SELECT DEPARTMENT_ID,AVG(salary) AS ‘平均工资’,COUNT(EMPLOYEE_ID) AS’人数’

FROM t_employees

GROUP BY DEPARTMENT_ID;

8.8.3、查询各个部门、各个岗位的人数

#思路

#1.按照部门编号进行分组(department_id)

#2.按照岗位名称进行分组(job_id)

#3.针对每个部门中各个岗位的人数进行统计

SELECT DEPARTMENT_ID AS’部门’,JOB_ID AS’岗位’,COUNT(EMPLOYEE_ID) AS’人数’

FROM t_employees

GROUP BY DEPARTMENT_ID,JOB_ID;

8.8.4、常见问题

#查询各个部门的id、总人数、first_name

SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID),FIRST_NAME

FROM t_employees

GROUP BY DEPARTMENT_ID;

• 注意：分组查询中，select显示的列只能是分组依据的列或者是聚合函数列，不能出现其他列。

8.9 、分组过滤查询

---

语法： SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列名) HAVING 过滤规则

| 关键字 | 说明 |

| — | — |

| HAVING | 过滤规则是对分组后的数据进行过滤 |

8.9.1、 统计部门中的最高工资

#统计部门编号为60、70、80的部门最高工资

#思路：

#1.确定分组依据 department_id

#2.对分组后的数据进行过滤 过滤规则为 60 70 80部门编号

#3.分组过滤后的数据，做max()函数处理

SELECT DEPARTMENT_ID,MAX(salary)

FROM t_employees

GROUP BY DEPARTMENT_ID

HAVING DEPARTMENT_ID IN (60,70,80);

#HAVING是在分组之后的数据进行过滤

8.10、限定查询

---

语法：SELECT 列名 FROM 表名 LIMIT 起始行,查询行

| 关键字 | 描述 |

| — | — |

| LIMIT offset_start,row_count | 限定查询结果的起始行和总行数 |

8.10.1、查询前5行记录

#查询表中前五名员工的信息

SELECT * FROM t_employees LIMIT 0,5;

• 注意：起始行是从0开始，代表了第一行。第二个参数代表的是从指定行开始查询几行

8.10.2 、查询范围记录

#查询表中的第二页数据和第三页数据

SELECT * FROM t_employees LIMIT 5,5;

SELECT * FROM t_employees LIMIT 10,5;

• 在分页的应用场景中，起始行是跟随页数变化的，但是一页显示的条数是不变得

8.11、查询总结

---

8.11.1 、SQL语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 LIMIT 起始行,总条数

8.11.2、SQL语句执行顺序

1. 执行 FROM ： 指定数据来源表

2. 执行WHERE ： 对查询的数据做第一次过滤

3. 执行GROUP BY ：分组

4. 执行HAVING : 对分组后的数据做第二次过滤

5. 执行SELECT : 查询各个字段的值

6. 执行ORDER BY : 排序

7. 执行LIMIT : 限定查询结果

8.12、子查询（作为条件判断）

---

语法：SELECT 列名 FROM 表名 WHERE 条件(子查询结果)

8.12.1、查询工资大于Bruce的员工信息

#思路

#1.先查询到Bruce的工资（一行一列）

SELECT SALARY FROM t_employees WHERE first_name = ‘Bruce’;#6000

#2.查询大于Bruce工资的员工信息

SELECT * FROM t_employees WHERE SALARY > 6000;

#3.将1 、2 整合为一条语句

SELECT * FROM t_employees WHERE salary >(SELECT SALARY FROM t_employees WHERE first_name = ‘Bruce’);

• 注意：将子查询"一行一列"的结果作为外部查询的条件。做第二次查询

• 子查询得到的是一行一列的结果才能作为外部条件的等值或不等值判断条件

8.13、子查询(作为枚举查询的条件)

---

语法：SELECT 列名 FROM 表名 WHERE 列名 IN(子查询结果)

8.13.1、查询与King同一部门员工信息

#思路

#1.查询King所在的部门编号（多行单列）

SELECT DEPARTMENT_ID FROM t_employees WHERE last_name=‘King’;

#2.将 80、90作为枚举查询的条件

SELECT EMPLOYEE_ID,FIRST_NAME,salary

FROM t_employees

WHERE DEPARTMENT_ID IN (80,90)

#3.整合

SELECT EMPLOYEE_ID,FIRST_NAME,salary

FROM t_employees

WHERE DEPARTMENT_ID

IN

(SELECT DEPARTMENT_ID FROM t_employees WHERE last_name=‘King’)

• 将子查询得到的"多行一列"的结果作为外部查询的枚举查询条件，做第二次查询

8.13.2、工资高于部门编号为60的所有人的信息

#1.查询部门编号为60的工资

SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60;

#2.查询高于60部门所有人的工资的员工信息(高于所有人！)

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary > ALL

(SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60);

#3.查询高于60部门所有人的工资的员工信息(高于部分人！)

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees

WHERE salary > ANY

(SELECT salary FROM t_employees WHERE DEPARTMENT_ID = 60);

• 注意：当子查询结果集为多行单列时，也可以使用ALL匹配所有或者ANY匹配部分

8.14、子查询(作为一张表)

---

语法：SELECT 列名 FROM (子查询结果集) WHERE 条件；

8.14.1 、查询表中部分列的信息，获得工资大于15000的

#思路

#1.先查询部分列的信息作为一张临时表

SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees;

#2.将子查询得到的临时表作为外部查询的表

SELECT EMPLOYEE_ID ,FIRST_NAME ,salary

FROM

(SELECT EMPLOYEE_ID,FIRST_NAME,salary FROM t_employees)AS temp

WHERE salary > 15000;

• 经验：将子查询得到的"多行多列"的结果作为外部查询的一张临时表，做第二次查询

8.15、合并查询

---

语法：

• SELECT 列名 FROM 表名 1 UNION SELECT 列名 FROM 表名2

• SELECT 列名 FROM 表名 1 UNION ALL SELECT 列名 FROM 表名2

8.15.1、合并两张表的结果（去除重复记录）

#合并 t1 和t2两张表的结果。纵向合并,去除重复的记录

SELECT * FROM t1

UNION

SELECT * FROM t2

8.15.2 、合并两张表的结果(保留重复记录)

#合并结果集，不去除重复记录

SELECT * FROM t1

UNION ALL

SELECT * FROM t2

• 注意：合并的两个结果集，列数必须相同，列类型、列名可以不同

8.16、表连接查询

---

语法：SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件;

8.16.1、内连接查询(INNER JOIN ON)

#查询所有有部门的员工信息,显示部门名称(不包括没有部门的员工) SQL标准

SELECT * FROM t_employees

INNER JOIN t_departments

ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#MYSQL标准

SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME FROM

t_employees,t_departments

WHERE t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

#1.两张表连接查询，要有关联条件。但是关联条件的列重复了。需要明确查询的是哪个表的列

#2.表名比较长,表名多次重复出现。容易混淆.可以给别名

SELECT EMPLOYEE_ID,FIRST_NAME,d.DEPARTMENT_ID,DEPARTMENT_NAME FROM t_employees AS e

INNER JOIN t_departments AS d

ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

• 在MySQL中，可以使用第二种方式，不符合SQL标准

• 第一种，属于SQL标准，与其他关系型数据库通用

8.16.2 、内连接查询

#查询所有岗位的员工信息，显示岗位名称

SELECT EMPLOYEE_ID,FIRST_NAME,JOB_TITLE

FROM t_employees AS e

INNER JOIN t_jobs AS j

ON e.JOB_ID = j.JOB_ID;

8.16.3、三表连接查询

#查询所有员工工号、名字、部门名称、部门所在城市的名称

SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY

FROM t_employees AS e

INNER JOIN t_departments AS d

ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

INNER JOIN t_locations AS l

ON d.LOCATION_ID = l.LOCATION_ID;

8.16.4、多表连接查询

#查询所有员工工号、名字、部门名称、部门城市名称、所在城市的国家

SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME,CITY,COUNTRY_NAME

FROM t_employees AS e

INNER JOIN t_departments AS d

ON e.DEPARTMENT_ID = d.DEPARTMENT_ID

INNER JOIN t_locations AS l

ON d.LOCATION_ID = l.LOCATION_ID

INNER JOIN t_countries AS c

ON l.COUNTRY_ID = c.COUNTRY_ID;

• 经验：多表查询时，要明确哪一张表和连接的表有关系。

8.16.5、左外连接查询(LEFT JOIN ON)

#查询所有员工信息，以及对应的部门名称（没有部门的员工，也在查询结果中，但是部门名称以NULL填充）

SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME

FROM t_employees AS e

LEFT JOIN t_departments AS d

ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

• 注意：左外连接，是以左表为主表，依次向右表匹配，匹配到，则返回正确结果

• 匹配不到，则返回NULL值，填充显示

8.16.6、右外连接查询(RIGHT JOIN ON)

#查询所有部门信息，以及部门中的员工信息

#(没有员工的部门，也在查询结果中，员工信息以NULL填充)

SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME

FROM t_employees AS e

RIGHT JOIN t_departments AS d

ON e.DEPARTMENT_ID = d.DEPARTMENT_ID;

• 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回正确结果

• 匹配不到，则返回NULL填充

9、 DML操作（增、删、改）

==================================================================================

9.1、新增(INSERT)

---

INSERT INTO 表名 (列1,列2,列3…) VALUES(值1,值2,值3…)

#添加一条员工信息

INSERT INTO t_employees

(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)

VALUES(‘209’,‘Ya’,‘Suo’,‘123@happy.com’,‘515.123.6666’,‘2010-03-18’,‘Center’,900,NULL,‘123’,‘50’)

#多行添加，在值列表外边追加,再写一个值列表

,(‘208’,‘Ya’,‘Suo’,‘YaSuo@happy.com’,‘515.123.6666’,‘2010-03-18’,‘Center’,900,NULL,‘123’,‘50’);

#添加一条城市信息

INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME)VALUES(‘AL’,‘阿尔巴尼亚’);

SELECT * FROM t_departments;

#添加一条部门信息

INSERT INTO t_departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)

VALUES(‘280’,‘Teach’,‘111’,‘1500’)

• 注意：表名后的列名列表以及VALUES里的值列表要一一对应(个数、顺序、类型)

9.2、修改(UPDATE)

---

UPDATE 表名 SET 列名1=新值1,列名2 = 新值2… WHERE 条件

#修改员工编号为208的员工名字为TOM Jackson

UPDATE t_employees SET FIRST_NAME=‘TOM’, LAST_NAME = ‘Jackson’ WHERE EMPLOYEE_ID = ‘208’;

• 注意：SET后跟着多个列 = 值，绝大多数情况下，都要加WHERE条件，指定修改的目标，否则为整表更新

9.3、删除

---

DELETE FROM 表名 WHERE 条件

#删除一条员工，编号为207的

DELETE FROM t_employees WHERE EMPLOYEE_ID = ‘207’

• 注意：删除时，如若不加WHERE条件，删除的是整张表的数据。结构不变

9.4、清空(TRUNCATE)

---

清空整张表：TRUNCATE TABLE 表名；

#清空t2整张表

TRUNCATE TABLE t2;

注意：TRUNCATE与DELETE不加WHERE，删除整张表数据不同：

• DELETE仅仅删除数据，结构不变

• TRUNCATE是把整张表销毁，再按照原表的格式、结构创建一张新表

10、库表操作

==========================================================================

10.1、数据库创建(CREATE)

---

CREATE DATABASE 库名

#创建默认字符集的数据库

CREATE DATABASE MYDB1;

#创建指定字符集的数据库

CREATE DATABASE MYDB1 CHARACTER SET UTF8;

10.2、修改数据库

---

ALTER DATABASE 库名 操作

10.2.1、修改数据库的字符集

#修改mydb1的字符集给gbk

ALTER DATABASE MYDB1 CHARACTER SET GBK;

10.3 删除数据库

---

删除数据库：DROP DATABASE 库名

#删除mydb1数据库

DROP DATABASE MYDB1;

10.4、数据类型

---

MySQL大致可以分为三类：数值、日期/时间、字符串(字符)类型。对于我们建表，约束列的类型有很大的帮助；

10.4.1 、数值类型

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |

| — | — | — | — | — |

| INT | 4字节 | (-2147483648,2147483647) | (0,4294967295) | 整数值 |

| DOUBLE | 8字节 | (-1.797E+308,-2.22E-308) | (0,2.22E-308,1.797E+308) | 双精度浮点值 |

| DOUBLE(M,D) | 8字节，M表示长度，D表示小数位数 | 同上，受M和D的约束。DOUBLE(5,2)-999.99-999.99 | 同上，受M和D的约束 | 双精度浮点值 |

| DECIMAL(M,D) | 保存精确值 | 依赖M和D。 | 依赖M和D | 小数值 |

10.4.2 、日期类型

| 类型 | 大小 | 范围 | 格式 | 用途 |

| — | — | — | — | — |

| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |

| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值 |

| YEAR | 1 | 1901/2155 | YYYY | 年分值 |

| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM–DD HH:MM:SS | 混合日期时间值 |

10.4.3、字符串类型

| 类型 | 大小 | 用途 |

| — | — | — |

| CHAR | 0-255字符 | 定长字符串 CHAR(10) 10个字符 |

| VARCHAR | 0-65535 | 可变长字符串 VARCHAR(10) 10个字符 |

| BLOB(binary large object) | 0-65535 | 二进制形式的长文本数据 |

| TEXT | 0-65535 | 长文本 |

10.5、数据表的创建(CREATE)

---

CREATE TABLE 表名(

​ 列名 数据类型 [约束],

​ 列名 数据类型 [约束],

​ …

​ 列名 数据类型 [约束] //最后一列的创建，末尾不需要加逗号

)[charset=utf8]; //根据需要指定表的字符编码集

#创建科目表

#科目编号、科目名称、科目学时

#Subject

CREATE TABLE Subject(

subjectId INT,

subjectName VARCHAR(20),

subjectHours INT

)CHARSET=utf8;

INSERT INTO subject(subjectid,subjectname,subjecthours)

VALUES(1,‘Java’,10);

INSERT INTO subject(subjectid,subjectname,subjecthours)

VALUES(2,‘HTML5’,20);

INSERT INTO subject(subjectid,subjectname,subjecthours)

VALUES(3,‘BIGDATA’,5);

11、约束

========================================================================

11.1、实体完整性约束

---

表中一行数据代表一个实体(entity)，实体完整性约束是标识每一行数据不重复、实体唯一。

11.1.1 、主键约束

PRIMARY KEY 唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL

#创建表中，选择适合做主键的列，添加主键约束

CREATE TABLE Student(

stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL

stuName VARCHAR(20),

phone VARCHAR(11)

)CHARSET=utf8;

11.1.2、唯一约束

UNIQUE 唯一，标识表中的一行数据，不可重复，可以为NULL

#表中的手机号列，添加唯一约束！不能重复，但是可以为NULL

CREATE TABLE Student(

stuid INT PRIMARY KEY,#标识每一个学生的编号是唯一的，不能为NULL

stuName VARCHAR(20),

phone VARCHAR(11) UNIQUE

)CHARSET=utf8;

11.1.3、自动增长列

AUTO_INCREMENT 自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键搭配

#为表中的主键列添加自动增长，避免ID重复，也容易忘记

CREATE TABLE Student(

stuid INT PRIMARY KEY AUTO_INCREMENT,#会从1开始，根据添加数据的顺序依次+1

stuName VARCHAR(20),

phone VARCHAR(11) UNIQUE

)CHARSET=utf8;

11.2、域完整性约束

---

限制列的每一个单元格的数据正确性

11.2.1、非空约束

NOT NULL 非空，约束此列的每一个单元格不允许有NULL

#加了NOT NULL的约束列，必须有值

CREATE TABLE emp(

id INT PRIMARY KEY AUTO_INCREMENT,

empName VARCHAR(20)NOT NULL,#约束名字这一列必须有值

address VARCHAR(50) NOT NULL

)CHARSET=utf8;

INSERT INTO emp(empName,address) VALUES(null,‘北京市海淀区’);#error,课程名称必须有值

11.2.2、默认值约束

DEFAULT 为列赋予默认值，当新增的数据不指定值时，可以书写DEFAULT，以定义好的默认值进行填充

#默认值约束，如果没有指定值，填充DEFAULT，默认值。

CREATE TABLE emp(

id INT PRIMARY KEY AUTO_INCREMENT,

empName VARCHAR(20)NOT NULL,#约束名字这一列必须有值

address VARCHAR(50) NOT NULL,

sex CHAR(1) DEFAULT ‘女’

)CHARSET=utf8;

11.2.3 、引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

• FOREIGN KEY 引用外部表的某5个列的值，新增数据时，约束此列的值必须是被引用表中存在的值

#专业表

CREATE TABLE Speciality(

id INT PRIMARY KEY AUTO_INCREMENT,

SpecialName VARCHAR(20) UNIQUE NOT NULL#唯一，且不能为空

)CHARSET=utf8;

#课程表

CREATE TABLE subject(

subjectid INT PRIMARY KEY AUTO_INCREMENT,

subjecname VARCHAR(20) UNIQUE NOT NULL,

subjecthours INT DEFAULT 20,

specialid INT NOT NULL,

CONSTRAINT fk_subject_specialid

FOREIGN KEY(specialid)

REFERENCES Speciality(id)

)CHARSET=utf8;

SELECT * FROM SUBJECT;

#存在引用关系的表。要先添加被引用的表数据(主键表).再添加引用表的数据(外键表)

INSERT INTO Speciality (SpecialName) VALUES(‘Java’);

INSERT INTO Speciality (SpecialName) VALUES(‘HTML5’);

INSERT INTO subject(subjecname,subjecthours,specialid)

VALUES(‘JavaSE’,10,1);

INSERT INTO subject(subjecname,subjecthours,specialid)

VALUES(‘JavaScript’,20,2);

INSERT INTO subject(subjecname,subjecthours,specialid)

VALUES(‘BIGDATA’,20,3);#error 约束：主键表不存在3.所以外键表不能插入3

• 注意：两张表存在引用关系时，执行删除操作需要注意，先删除从表(引用表、外键表)，再删除主表(被引用表、主键表)

11.3、约束创建整合

---

创建带有约束的表

11.3.1 、创建Grade表

| 列名 | 数据类型 | 约束 | 说明 |

| — | — | — | — |

| GradeId | INT | 主键、自动增长 | 班级编号 |

| GradeName | VARCHAR(20) | 唯一、非空 | 班级名称 |

#创建Grade表

CREATE TABLE Grade(

GradeId INT PRIMARY KEY AUTO_INCREMENT,

GradeName VARCHAR(20) UNIQUE NOT NULL

)CHARSET=utf8;

SELECT * FROM grade;

INSERT INTO Grade(GradeName) VALUES(‘001’);

INSERT INTO Grade(GradeName) VALUES(‘002’);

INSERT INTO Grade(GradeName) VALUES(‘003’);

11.3.2、创建Student表

| 列名 | 数据类型 | 约束 | 说明 |

| — | — | — | — |

| student_id | VARCHAR(50) | 主键 | 学号 |

| student_name | VARCHAR(50) | 非空 | 姓名 |

| sex | CHAR(2) | 默认值。男 | 性别 |

| borndate | DATE | 非空 | 生日 |

| phone | VARCHAR(11) | 无 | 电话 |

| GradeId | INT | 非空，外键约束：引用班级表的GradeId | 班级编号 |

#创建Student表

CREATE TABLE Student(

student_id VARCHAR(50) PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

sex CHAR(2) DEFAULT ‘男’,

borndate DATE NOT NULL,

phone VARCHAR(11),

GradeId INT NOT NULL,

CONSTRAINT fk_student_gradeId FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)

)CHARSET=utf8;

SELECT * FROM student;

INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)

VALUES(‘S1001’,‘kak1’,DEFAULT,‘2001-06-01’,NULL,2);

INSERT INTO student(student_id,student_name,sex,borndate,phone,GradeId)

VALUES(‘S1002’,‘kak2’,DEFAULT,‘1999-06-01’,NULL,3);

• 注意：在创建有关系关联表时，要先创建主表（主键），再创建从表（外键表）

11.4、数据表的修改（ALTER）

---

语法：ALTER TABLE 表名 修改操作;

11.4.1、向现有表中添加列

#向现有表中添加列

ALTER TABLE Student ADD image BLOB;

#ADD 新列名 数据类型 [约束]

11.4.2 、修改表中的列

ALTER TABLE student MODIFY phone VARCHAR(14) NOT NULL

• 注意：修改表中的某列时，需要写全列的名字、数据类型、约束

11.4.3、删除表中的列

ALTER TABLE student DROP image;

11.4.4、改变列名

ALTER TABLE student CHANGE borndate birthday DATE NOT NULL;

• 注意：改变列名时，在给定新列名的同时，要指定列的数据类型和约束

11.4.5、修改表名

ALTER TABLE student RENAME stu;

11.5、删除表(DROP)

---

删除学生表：DROP TABLE 表名

DROP TABLE stu

12、事务

========================================================================

12.1、模拟转账

---

生活中转账是转账方扣钱，收钱方账户价钱。用数据库操作来模拟现实转账。

12.1.1、 模拟账户转钱

#1账号转钱给2账户1000元

#1账户扣钱

UPDATE account SET money = money - 1000 WHERE id = 1;

#2账户加钱

UPDATE account SET money = money + 1000 WHERE id = 2;

12.1.2 、模拟转账错误

#1账号转钱给2账户1000元

#1账户扣钱

UPDATE account SET money = money - 1000 WHERE id = 1;

#断电、异常、出错

#2账户加钱

UPDATE account SET money = money + 1000 WHERE id = 2;

• 上述代码在减钱操作过程中出现了异常或语句出错，会发现，减钱仍旧成功，而加钱失败了

• 每条SQL语句都是一个独立的操作！任何一个操作执行完对数据库是永久性的影响

12.2、事务的概念

---

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务中，所有的SQL语句都成功执行时，整个事务成功！有一个SQL语句执行失败，整个事务都执行失败！

12.3、事务的边界

---

开始：连接到数据库，执行一条DML语句。 上一个事务结束后，又输入了一条DML语句，即事务的开始

结束：

• 提交：

*   显示提交：[COMMIT]( );

*   隐式提交：一条DML语句。正常退出(客户端退出链接)

• 回滚：

*   显示回滚：[ROLLBACK]( );

*   隐式回滚：非正常退出(断电、死机)，执行了创建、删除的语句，但是失败了！会为这个无效的SQL语句执行回滚。

12.4、事务的原理

---

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有的SQL语句均正常结束(COMMIT),才会将回滚段中的数据同步到数据库。否则无论因为任何原因失败了，则整个事务回滚(ROLLBACK);

12.5、事务的特性

---

• Atomicity(原子性)：表示的是一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。

• Consistency(一致性)：表示一个事务内有一个操作失败时，所有的更改过得数据都必须回滚到修改前状态。

• Isolation(隔离性)：事务查看数据操作时数据所处的状态，要么是另一个并发事务修改数据之前的状态，要么是另一个并发事务修改它之后的状态。事务不会查看中间状态的数据

• Durability(持久性)：事务完成之后，对于数据库的影响是永久性的。

12.6、事务的应用
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

总结

面试难免让人焦虑不安。经历过的人都懂的。但是如果你提前预测面试官要问你的问题并想出得体的回答方式，就会容易很多。

此外，都说“面试造火箭，工作拧螺丝”，那对于准备面试的朋友，你只需懂一个字：刷！

给我刷刷刷刷，使劲儿刷刷刷刷刷！今天既是来谈面试的，那就必须得来整点面试真题，这不花了我整28天，做了份“Java一线大厂高岗面试题解析合集：JAVA基础-中级-高级面试+SSM框架+分布式+性能调优+微服务+并发编程+网络+设计模式+数据结构与算法等”

且除了单纯的刷题，也得需准备一本【JAVA进阶核心知识手册】：JVM、JAVA集合、JAVA多线程并发、JAVA基础、Spring 原理、微服务、Netty与RPC、网络、日志、Zookeeper、Kafka、RabbitMQ、Hbase、MongoDB、Cassandra、设计模式、负载均衡、数据库、一致性算法、JAVA算法、数据结构、加密算法、分布式缓存、Hadoop、Spark、Storm、YARN、机器学习、云计算，用来查漏补缺最好不过。

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
)12.3、事务的边界

---

开始：连接到数据库，执行一条DML语句。 上一个事务结束后，又输入了一条DML语句，即事务的开始

结束：

• 提交：

*   显示提交：[COMMIT]( );

*   隐式提交：一条DML语句。正常退出(客户端退出链接)

• 回滚：

*   显示回滚：[ROLLBACK]( );

*   隐式回滚：非正常退出(断电、死机)，执行了创建、删除的语句，但是失败了！会为这个无效的SQL语句执行回滚。

12.4、事务的原理

---

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有的SQL语句均正常结束(COMMIT),才会将回滚段中的数据同步到数据库。否则无论因为任何原因失败了，则整个事务回滚(ROLLBACK);

12.5、事务的特性

---

• Atomicity(原子性)：表示的是一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。

• Consistency(一致性)：表示一个事务内有一个操作失败时，所有的更改过得数据都必须回滚到修改前状态。

• Isolation(隔离性)：事务查看数据操作时数据所处的状态，要么是另一个并发事务修改数据之前的状态，要么是另一个并发事务修改它之后的状态。事务不会查看中间状态的数据

• Durability(持久性)：事务完成之后，对于数据库的影响是永久性的。

12.6、事务的应用
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。[外链图片转存中…(img-U8tH15Az-1713545421020)]

[外链图片转存中…(img-MdOfseXj-1713545421024)]

[外链图片转存中…(img-uAgFzVAe-1713545421026)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

总结

面试难免让人焦虑不安。经历过的人都懂的。但是如果你提前预测面试官要问你的问题并想出得体的回答方式，就会容易很多。

此外，都说“面试造火箭，工作拧螺丝”，那对于准备面试的朋友，你只需懂一个字：刷！

给我刷刷刷刷，使劲儿刷刷刷刷刷！今天既是来谈面试的，那就必须得来整点面试真题，这不花了我整28天，做了份“Java一线大厂高岗面试题解析合集：JAVA基础-中级-高级面试+SSM框架+分布式+性能调优+微服务+并发编程+网络+设计模式+数据结构与算法等”

[外链图片转存中…(img-MmWjgBvZ-1713545421028)]

且除了单纯的刷题，也得需准备一本【JAVA进阶核心知识手册】：JVM、JAVA集合、JAVA多线程并发、JAVA基础、Spring 原理、微服务、Netty与RPC、网络、日志、Zookeeper、Kafka、RabbitMQ、Hbase、MongoDB、Cassandra、设计模式、负载均衡、数据库、一致性算法、JAVA算法、数据结构、加密算法、分布式缓存、Hadoop、Spark、Storm、YARN、机器学习、云计算，用来查漏补缺最好不过。

[外链图片转存中…(img-kTW9qPMl-1713545421029)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！