MySQL基础学习笔记

朝凌yyds

已于 2022-04-16 15:06:51 修改

阅读量1.1k

点赞数

文章标签： sql mysql

于 2022-04-03 14:38:00 首次发布

本文链接：https://blog.csdn.net/weixin_46598345/article/details/123813817

版权

文章目录

一、学习目标及任务
一、数据库概述
二、SQL
三、函数
四、约束
五、多表查询
六、事务

一、学习目标及任务

目标及任务

一、数据库概述

（一）、数据库相关概念

1.

                     数据库：存储数据的仓库。数据是有组织的进行存储，简称DataBase(DB)；
                     数据库管理系统：操纵和管理数据库的大型软件，Database Management System（DBMS）
                     SQL：操作关系性数据库的编程软件，定义了一套操作关系型数据库统一标准，Structured QUer Language（SQL）

2. 主流的关系型数据库管理系统

关系型数据库管理系统

（二）、MySQL数据库

1.下载安装、配置检查及启动

2.MySQL数据模型

（1）关系型数据库（RDBMS）

                   概念：建立在关系模型基础上，有多涨相互链接的二维表组合成的数据库。
                   特点：使用表存储数据，格式统一便于维护，使用SQL语言操作，标准统一，使用方便。

（2）数据模型

数据模型
客户端连接数据库管理系统，使用SQL语句通过数据库管理系统来创建数据库，也可以使用SQL语句通过数据库管理系统中的数据库来创建表。一个数据库服务器中可以创建多个服务器，在一时数据库中可以创建多个表，再一张表中可以创建多条记录。

二、SQL

（一）、SQL通用语法

                  1.SQL语句可以单行或多行书写，以分号结尾。
                  2.SQL语句可以使用空格/所欧锦来增强语句可读性。
                  3.MySQL数据库的SQL语句不分大小写，关键字建议使用大写。
                  4.注释：
                    &emsp;&emsp;单行注释：--注释内容 或 #注释内容（MySQL特有）
                    &emsp;&emsp; 多行注释：/* 注释内容 */

（二）、SQL分类

SQL分类

（三）、DDL

1.DDL-数据库操作

（1）查询

查询所有数据库

SHOW DATABASES;

查询所有数据库

查询当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSE 字符集] [COLLATE 排序规则];

创建且添加字符集

删除

DROP DATABASE [IF EXISTS]数据库名;

判断存在再删除

使用

USE 数据库名;

2.DDL-表操作

（1）查询

①查询当前数据库所有表

SHOW TABLES;

查询当前数据库所有表

②查询表结构

DESC 表名;

查询表结构

③查询指定表的建表语句

SHOW CREATE TABLE 表名;

查询指定表建表语句

（2）创建

CREATE TABLE 表名（
       字段1  字段1类型[COMMENT 字段1注释],
       字段2  字段2类型[COMMENT 字段2注释],
       字段3  字段3类型[COMMENT 字段13注释],
       ......
       字段n  字段n类型[COMMENT 字段n注释]
   )[COMMENT 表注释];

创建表

（3）数据类型

MySQL中的数据类型有很多种，主要分为三类：数值类型、字符串类型、日期时间类型。
①数值类型：

精度与标度的区别：对于123.45的精度为：5，标度为2 score double(5,2)
精度：整个数字的长度，标度：小数点后的数值长度。
使用合适的数值类型可以解决存储空间。
②字符串类型

带BLOB的是用来描写二进制数据（视频、音频、安装包），带TEXT时用来描述文本数据。
定长字符串：
例：char(10),参数10 是指当前字符串能够存储的最大长度，即使你使用了一个字符，剩下的空间也会被空格补位。性能高。
变长字符串：
例：varchar（10），参数时指当前字符串能够存储的最大长度，但是varchar会根据字符串内容来计算当前字符串的长度。相对于char性能较差。
练习：对于：用户名 username 50 使用char还是varchar更好一些？
答：varchar（50）。
练习：对于：性别 gender 使用哪个比较好？
答：char(1).

③日期时间类型

练习题

代码展示：

演示结果
在这里插入图片描述

（3）修改

①添加字段

ALTER TABLE 表名 ADD 字段名 类型（长度） [COMMENT注释] [约束];

案例：为EMP表添加一个心的字段‘昵称’为nickname 类型为varchar(10)
结果展示
②修改字段
修改数据类型：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT注释] [约束];

案例：将EMP表中的nickname字段修改为username,类型为varchar(30);
结果演示
③删除字段

ALTER TABLE 表名 DROP 字段名;

案例：将EMP表中的字段username删除。
演示结果
④修改表名

ALTER TABLE 表名 RENAME TO 新表名;

结果演示
⑤删除表
删除表：

DROP TABLE[IF EXISTS] 表名;

结果演示

删除指定表，并重新创建该表：

TRUNCATE TABLE 表名;

结果演示
删除表之后再创建一个全新的表，表内没有数据,只有表结构。

图形化界面工具DataGrip

        下载安装配置，链接MySQL。

（四）、DML

                  英文全称：Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改。
                  增加数据（INSERT）、修改数据（UPDATE）、删除数据（DELETE）。

1.DML-添加数据

①给指定的字段添加数据

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

INSERT INTO employee(ID, WORKNUM, NAME, GENDER, AGE, IDCARD, ENTRYDATE) VALUE(1,'1','Itcast','男',25,'1234567894585858','2000-01-01');
INSERT INTO employee(ID, WORKNUM, NAME, GENDER, AGE, IDCARD, ENTRYDATE) VALUE(2,'2','Ityou','女',23,'1234567894589589','2000-02-01');

在这里插入图片描述
②给全部字段添加数据

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

INSERT INTO EMPLOYEE VALUES (3,'3','张无忌','男',15,'123456789458525852','2022-03-29');

结果演示
③批量添加数据

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

INSERT INTO EMPLOYEE (ID,WORKNUM,NAME) VALUES (6,'6','栗子'),(7,'7','孙悟空'),(8,'8','八戒'),(9,'9','沙僧');

演示结果

INSERT INTO EMPLOYEE VALUES (4,'5','韦一笑','男',16,'123456959458525852','2022-03-19'),(5,'5','萧峰','男',35,'123456789988525852','2022-04-29'),(3,'3','张无忌','男',15,'123456789458525852','2022-03-29');

在这里插入图片描述
注：插入数据时，指定的字段顺序需要与值的顺序一一对应。
字符串和日期型数据应该包含在引号里。
插入的数据大小，应该在字段规定范围内。

2.DML-修改数据

UPDATE 表名 SET 字段名1 = 值1,字段名2=值2,...[WHERE 条件];

注：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

# 修改id为1 的数据，将name修改为Itjuan。
UPDATE EMPLOYEE SET NAME = 'Itjuan' WHERE ID = 1;

# ID为3的数据，将name修改为小昭，gender修改为女。
UPDATE EMPLOYEE SET NAME='小昭',gender = '女' WHERE ID=3;

# 将所有员工入职日期修改为2008-01-01.
UPDATE EMPLOYEE SET ENTRYDATE='2008-01-01';

演示结果

3.DML-删除数据

DELETE FROM 表名[WHERE 条件];

注：delete语句的条件可以有，也可可以没有，如果没有条件，则会删除整张表的所有数据。
delete语句不能删除某一个字段的值（可以使用UPDATE）。

# 删除gender为女的员工。
DELETE FROM  EMPLOYEE WHERE GENDER='女';

演示结果

# 删除所有员工。
DELETE FROM EMPLOYEE;

演示结果

（五）、DQL

DQL全称Data Query Language(数据查询语言)，用于查询数据库中表的记录。查询关键字:SELECT.

1.DQL-语法（先涉及单表查询）

语法结构

（1）基本查询

①查询多个字段、查询所有字段。

SELECT 字段1,字段2,字段3,...FROM 表名;
SELECT *FROM 表名;

②设置别名

SELECT 字段1 [AS 别名1],字段2[AS 别名2]...FROM 表名;

③去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

2）案例：
EMP表格：
EMP表格详情

# 基本查询
# 1.查询指定字段name，worknum,age返回。
SELECT NAME,WORKNUM,AGE FROM EMP;
# 2.查询所有字段返回。
SELECT *FROM EMP;
SELECT ID,WORKNUM,NAME,GENDER,AGE,IDCARD,WORKADDRESS,ENTRYDATE FROM EMP;
# 3.查询所有员工的工作地址，起别名。
SELECT WORKADDRESS AS '工作地址' FROM EMP;
SELECT WORKADDRESS '工作地址' FROM EMP;
# 4.查询公司员工的上班地址不要重复
SELECT DISTINCT WORKADDRESS FROM EMP;

（2）条件查询（WHERE）

1).语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2).条件

3）案例：

# 条件查询
# 1.查询年龄等于25的员工
SELECT *FROM EMP WHERE AGE = 25;
# 2.查询年龄小于27的员工信息。
SELECT  *FROM EMP WHERE AGE < 27;
# 3.拆查询年龄小于等于26的员工信息。
SELECT  *FROM EMP WHERE AGE <= 27;
# 4.查询没有身份证号的员工信息。
SELECT *FROM EMP WHERE IDCARD IS NULL;
# 5.查询有身份证号的员工信息。
SELECT *FROM EMP WHERE IDCARD IS NOT  NULL;
# 6.查询年龄不等于25的员工信息
SELECT *FROM EMP WHERE AGE != 25;
SELECT *FROM EMP WHERE AGE <> 25;
# 7.查询年龄在10岁（包含）到28岁（包含）之间的员工信息
SELECT *FROM EMP WHERE AGE BETWEEN 10 AND 28;
SELECT *FROM EMP WHERE AGE >= 10 && AGE <=28;
SELECT *FROM EMP WHERE AGE >= 10 AND AGE <=28;
# 8.查询性别为女且年龄小于28岁的员工信息
SELECT *FROM EMP WHERE GENDER='女'AND AGE <= 28;
# 9.查询年龄等于18或21或25的员工的信息
SELECT *FROM EMP WHERE AGE=18 OR AGE=21 OR AGE=25;
SELECT *FROM EMP WHERE AGE=18 || AGE=21 || AGE=25;
SELECT *FROM EMP WHERE AGE IN(18,21,25);
# 10.查询姓名为两个字的员工信息。  _    %
SELECT *FROM EMP WHERE NAME LIKE '__';
# 11.查询身份证号最后一位为是9的员工信息。
SELECT *FROM EMP WHERE IDCARD LIKE '%9';
# 11.查询身份证号第五位为是5的员工信息。
SELECT *FROM EMP WHERE IDCARD LIKE '____5____';

（3）聚合函数查询(count,max,min,avg,sum)

 &emsp;1）介绍：将一列数据作为一个整体，进行纵向计算。
 &emsp;2）常见聚合函数：

常见聚合函数
3）语法

SELECT 聚合函数(字段列表) FROM 表名;

注：所有的null值不参与任何聚合函数运算。
4）案例：

# 聚合函数
# 1.统计该公司员工数量
SELECT COUNT(ID) FROM EMP;
SELECT COUNT(*) FROM EMP;
SELECT COUNT(1) FROM EMP;
# 2.统计该公司员工的平均年龄
SELECT AVG(AGE) FROM EMP;
# 3.统计该公司员工的最大年龄
SELECT MAX(AGE) FROM EMP;
# 4.统计该公司员工最小年龄
SELECT MIN(AGE) FROM EMP;
# 5.统计同福客栈地区员工的年龄之和.
SELECT SUM(AGE) FROM EMP WHERE WORKADDRESS='同福客栈';

（4）分组查询(GROUP BY)

1）语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

2）where和having之间的区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，二having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。
注：执行顺序：where>聚合函数>having;
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
3）案例

# 分组查询
# 1.根据性别分组,统计男性员工和女性员工的数量.
SELECT GENDER,COUNT(*) FROM EMP GROUP BY GENDER;
# 2.根据性别分组,统计男性员工和女性员工的平均年龄.
SELECT GENDER,AVG(AGE) FROM EMP GROUP BY GENDER;
# 3.查询年龄小于25岁的员工,饼干据工作地址进行分组,获取员工数量大于三的工作地址.
SELECT WORKADDRESS,COUNT(*) FROM EMP WHERE AGE < 25 GROUP BY WORKADDRESS HAVING COUNT(*)>=3;
                            # 起别名:
SELECT WORKADDRESS,COUNT(*) ADDRESS_COUNT FROM EMP WHERE AGE < 25 GROUP BY WORKADDRESS HAVING ADDRESS_COUNT>=3;

（5）排序查询(ORDER BY)

1）语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

2）排序方式
①ASC：升序（默认值）
②DESC：降序
注：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
3）案例：

#排序查询
#  1.根据年龄对公司的员工进行升序排序.
SELECT *FROM EMP ORDER BY AGE ASC ;
SELECT *FROM EMP ORDER BY AGE ;
#  2.根据入职时间,对员工进行派降序.
SELECT *FROM EMP ORDER BY ENTRYDATE DESC;
#  3.根据年龄对公司员工进行升序排序,年龄相同,再按照入职时间进行降序排序.
SELECT *FROM EMP ORDER BY AGE ASC,ENTRYDATE DESC ;

（6）分页查询(LIMIT)

分页查询
1）语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注：起始索引时从0开始的，*起始索引=（查询页面-1）每页显示记录数。
分页查询是数据库的方言，不同数据库有不同的实现，MySQL中是LIMIT.
如果查询的时第一页数据，起始索引可以省略，直接简写为limit 10.
2)案例

# 分页查询
# 1.查询第1页的员工数据,每页展示10条记录.
SELECT *FROM EMP LIMIT 0,10;
# 2.查询第2页员工数据,每页展示10条数据.
SELECT *FROM EMP LIMIT 10,10;

2.练习题

# DQL语句练习                                                                                    
# 1.查询年龄为20,21,22,23岁的女性员工信息                                                                 
SELECT *FROM EMP WHERE AGE IN(20,21,22,23) AND GENDER='女';                                   
# 2.查询性别为男,并且年龄在20-30(含)以内的姓名为三个字的员工.                                                        
SELECT *FROM EMP WHERE GENDER='男' && AGE BETWEEN 20 AND 30 && NAME LIKE '___';               
# 3.统计员工表中年龄小于25岁的男性员工与女性员工的人数.                                                              
SELECT GENDER,COUNT(*)FROM EMP WHERE AGE<25 GROUP BY GENDER;                                 
# 4.查询所有年龄小于28岁的员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序.                                      
SELECT NAME,AGE,ENTRYDATE FROM EMP WHERE AGE<28 ORDER BY AGE ASC ,ENTRYDATE DESC ;           
# 5.查询性别为男,且年龄在20-35岁以内(含)的前五个员工信息,对查询结果按年龄升序排序年龄相同按入职时间升序排序.                                
SELECT *FROM EMP WHERE AGE BETWEEN 20 AND 35 && GENDER='男' ORDER BY AGE ASC ,ENTRYDATE ASC LIMIT 0,5;

（7）执行顺序
执行数据图解

# 查询年龄大于15岁的员工的姓名年龄并且根据年龄进行升序排序.                                               
SELECT NAME AGE FROM EMP WHERE AGE>15 ORDER BY AGE ASC;                        
# 验证FROM在WHERE之前执行                                                             
SELECT NAME AGE FROM EMP E WHERE E.AGE>15 ORDER BY AGE ASC;                    
# 验证SELECT在WHERE之前执行                                                           
SELECT E.NAME ,E.AGE FROM EMP E WHERE E.AGE>15 ORDER BY AGE ASC;               
# 验证SELECT在WHERE之前执行（反向验证，代码时错的）                                                           
SELECT E.NAME ENAME ,E.AGE EAGE FROM EMP E WHERE EAGE>15 ORDER BY AGE ASC;     
# 验证SELECT在WHRERE　，　ORDER　BY　之前执行                                              
SELECT E.NAME ENAME ,E.AGE EAGE FROM EMP E WHERE E.AGE>15 ORDER BY EAGE ASC;

（六）、DCL

DCL英文全称Data Control Language(数据控制语言)，用来管理数据库用户、控制数据量的访问权限。

1.查询用户

USE mysql;
SELECT *FROM user;

2.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

示例：

# 创建用户Itcast,只能够在当前主机localhost访问,密码123456;
CREATE USER  'Itcast'@'localhost' IDENTIFIED BY '123456';

通过cmd访问：

# 创建用户xiaojuan,可在任意主机访问该数据库,密码:123456;
CREATE USER  'xiaojuan'@'%' IDENTIFIED BY '123456';

3.修改用户密码

语法：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

示例：

# 修改用户xiaojuan 的访问密码为1234;
ALTER USER 'xiaojuan'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

4.删除用户

DROP USER '用户名'@'主机名';

示例：

# 删除Itcast@localhost用户;
DROP USER 'Itcast'@'localhost';

5.权限控制

MySQL中定义了很多种权限，但是常用的就一下几种：

（1）查询权限

SHOW GRANTS FOR '用户名'@'主机名';

（2）授予权限

GRANT 权限列表  ON 数据库名.表名  TO  '用户名'@'主机名';

（3）撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM  '用户名'@'主机名';

（4）案例

# 查询权限
SHOW GRANTS FOR 'xiaojuan'@'%';
# 授予权限
GRANT ALL ON ITCAST.* TO 'xiaojuan'@'%';
# 授予所有库的所有表的权限给xiaojuan.
GRANT ALL ON *.* TO 'xiaojuan'@'%';
# 撤销权限
REVOKE ALL ON ITCAST.* FROM 'xiaojuan'@'%';

cmd中演示授予权限

cmd中演示撤销权限

注：多个权限之间，可以用逗号分隔开来，all代表所有权限。授权时，数据库名和表名可以使用*进行通配，代表所有。

三、函数

函数是指一段可以被另一段程序调用的程序或者代码。

（一）字符串函数

MySQL中内置了很多函数，常用的几个如下：
常用字符串函数

1.函数详解

①CONCAT函数

SELECT CONCAT('HELLO','MYSQL');

在这里插入图片描述
②LOWER函数

SELECT LOWER('HELLO');

在这里插入图片描述
③UPPER函数

SELECT UPPER('hello');

在这里插入图片描述
④LPAD函数和RPAD函数

SELECT LPAD('01',5,'_');
SELECT RPAD('01',5,'_');

在这里插入图片描述

⑤TRIM函数

SELECT TRIM('  HELLO MYSQL ');

在这里插入图片描述
⑥SUBSTRING函数

SELECT SUBSTRING('HELLO MYSQL',3,6);

在这里插入图片描述

2.案例

由于业务需求变更，公司员工的工号，统一为5位数，目前不足5位数的全部在前面补0，比如：1好员工的工号是00001.

UPDATE EMP SET WORKNUM = LPAD(WORKNUM,5,'0');

运行结果

（二）数值函数

常见的数值函数如下：
在这里插入图片描述

1.函数语法详解

①CEL()函数

SELECT CEIL(1.5);

在这里插入图片描述
②FLOOR函数

SELECT FLOOR(1.5);

在这里插入图片描述
③MOD函数（取余）

SELECT  MOD(5,6);

在这里插入图片描述
④RAND函数

SELECT RAND();

在这里插入图片描述
⑤ROUND函数.

SELECT ROUND(3.1415926,4);

在这里插入图片描述

2.案例

通过数据库的函数，生成一个六位数的随机验证码。

SELECT LPAD(ROUND(RAND()*1000000,0),6,'0');

在这里插入图片描述

（三）日期函数

常见的日期函数如下：
在这里插入图片描述

1.函数语法详解

①CURDATE函数

SELECT CURDATE();

②CURTIME函数

SELECT CURTIME();

③NOW函数

SELECT NOW();

④YEAR函数

SELECT YEAR(NOW());

在这里插入图片描述
⑤MONTH函数

SELECT MONTH(NOW());

⑥DAY函数

SELECT DAY(NOW());

⑦DATE_ADD函数

#往后推70天。
SELECT DATE_ADD(NOW(),INTERVAL 70 DAY);

在这里插入图片描述
⑧DATEDIFF函数

SELECT DATEDIFF('2015-12-01','2025-04-05');

在这里插入图片描述

2.案例

查询所有员工的入职天数，并根据入职天数倒序排序。

SELECT NAME,DATEDIFF(CURDATE(),ENTRYDATE) AS ENTRYDAYS FROM EMP ORDER BY ENTRYDAYS DESC;

（四）流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
在这里插入图片描述 ①IF函数

SELECT IF(TRUE,'OK','ERROR');
SELECT IF(FALSE,'OK','ERROR');

②IFNULL函数

SELECT  IFNULL('OK','DEFAULT');
SELECT  IFNULL(NULL,'DEFAULT');

③CASE函数

# 需求：查询EMP员工表员工姓名和工作地址（同福客栈/十里桃林-->一线城市，其他-->二线城市）
SELECT
       NAME,
       (CASE WORKADDRESS WHEN '同福客栈'THEN '一线城市' WHEN '十里桃林'THEN '一线城市' ELSE '二线城市' END) AS '工作地址'
FROM EMP;

# 案例:统计班级各个学院的成绩,展示的规则如下:
# >= 85,展示优秀.
# >= 60,展示及格，
# 否则展示不及格。
SELECT ID,
       NAME,
       (CASE WHEN MATH>=85 THEN '优秀' WHEN MATH BETWEEN 84 AND 65 THEN '及格' ELSE '不及格'END) AS '数学成绩',
       (CASE WHEN ENGLISH>=85 THEN '优秀' WHEN ENGLISH BETWEEN 84 AND 65 THEN '及格' ELSE '不及格'END) AS '英语成绩',
       (CASE WHEN CHINESE>=85 THEN '优秀' WHEN CHINESE BETWEEN 84 AND 65 THEN '及格' ELSE '不及格'END) AS '语文成绩'

FROM  SCORE;

学习成绩

四、约束

（一）约束概述

1.概念

约束数作用域表中字段上的规则，用于显示存储在表中的数据。

2.目的

保证数据库中数据的正确性、有效性和完整性。

3.分类

（二）约束演示

案例演示

根据需求完成表结构的创建。
在这里插入图片描述

CREATE TABLE USER(
    ID INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    NAME VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
    AGE INT CHECK ( AGE BETWEEN 1 AND 120) COMMENT '年龄',
    STATUS VARCHAR(1) DEFAULT '1' COMMENT '情况',
    GENDER CHAR(1) COMMENT '性别'
)COMMENT '用户表';

INSERT INTO USER( NAME, AGE, STATUS, GENDER)
VALUES ('TOM1',19,'1','男'),
       ('TOM2',25,'0','男');

注：黄色钥匙图案为主键，ID字段的约束AUTO_INCREMENT 使ID自动生成序号，无需插入ID。

①情况一；

注：验证非空，name的约束条件为 NOT NULL，所以当name 为NULL时，报错。

②情况二
验证唯一
注：name的UNIQUE约束，使TOM2不能重复出现。

③情况三
插入一段正常数据：

INSERT INTO USER( NAME, AGE, STATUS, GENDER)
VALUES ('TOM3',80,'1','男');

在这里插入图片描述
原因：虽然上次传入数据没有成功，但是已经向数据库申请过主键3了，所以这次自动往后申请，就是4.

④情况四
CHACK
⑤情况五
在这里插入图片描述
⑥情况六

INSERT INTO USER( NAME, AGE, GENDER)
VALUES ('TOM5',120,'男');

在这里插入图片描述
注：不传入status值时，根据约束DEFAULT ,默认为1.

（三）外键约束

1.概念

外键用来让两张表的数据之间建立链接从而保证数据的一致性和完整性。

2.语法

建立两张表备用，代码如下：

CREATE TABLE DEPT(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
    NAME VARCHAR(50)NOT NULL COMMENT '部门名称'
)COMMENT '部门表';

INSERT INTO DEPT(ID, NAME) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

CREATE TABLE EMP(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
    NAME VARCHAR(50) NOT NULL COMMENT '姓名',
    AGE INT COMMENT '年龄',
    JOB VARCHAR(20) COMMENT '职位',
    SALARY INT COMMENT '薪资',
    ENTRYDATE DATE COMMENT '入职时间',
    MANAGERID INT COMMENT '直属领导ID',
    DEPT_ID INT COMMENT '部门ID'
)COMMENT '员工表';

INSERT INTO EMP(ID, NAME, AGE, JOB, SALARY, ENTRYDATE, MANAGERID, DEPT_ID)
VALUES (1,'金庸',66,'总裁',20000,'2000-01-01',NULL,5),
       (2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
       (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
       (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
       (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
       (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

（1）添加外键

CREATE  TABLE EMP(
    字段名 数据类型
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

（2）删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

ALTER TABLE EMP DROP FOREIGN KEY FK_EMP_ID;

3.外键删除更新行为

在这里插入图片描述

ALTER TABLE EMP ADD CONSTRAINT  FK_EMP_DEPT_ID FOREIGN KEY  (DEPT_ID) REFERENCES  DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE ;

删除或更新DEPT表中的值时，EMP 表格中的记录也会被更新或删除。

ALTER TABLE EMP ADD CONSTRAINT  FK_EMP_DEPT_ID FOREIGN KEY  (DEPT_ID) REFERENCES  DEPT(ID) ON UPDATE SET NULL ON DELETE SET NULL ;

删除或更新DEPT表中D的记录时，EMP 表格中的记录也会默认为NULL。

五、多表查询

（一）多表关系

1.概述

项目开发中，在进行多数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以哥哥表结构之间也存在各种联系，基本上分为3种：
一对多（多对一）
多对多
一对一

2.一对多（多对一）

            案例：部门与员工的关系。
            关系：一个部门对应多个员工，一个员工对应一个部门。
            实现：在多的一方简历外键，指向一的一方的主键。

一对多

CREATE TABLE DEPT(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
    NAME VARCHAR(50)NOT NULL COMMENT '部门名称'
)COMMENT '部门表';

INSERT INTO DEPT(ID, NAME) VALUES (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办'),(6,'人事部');
;

CREATE TABLE EMP(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
    NAME VARCHAR(50) NOT NULL COMMENT '姓名',
    AGE INT COMMENT '年龄',
    JOB VARCHAR(20) COMMENT '职位',
    SALARY INT COMMENT '薪资',
    ENTRYDATE DATE COMMENT '入职时间',
    MANAGERID INT COMMENT '直属领导ID',
    DEPT_ID INT COMMENT '部门ID'
)COMMENT '员工表';

INSERT INTO EMP(ID, NAME, AGE, JOB, SALARY, ENTRYDATE, MANAGERID, DEPT_ID)
VALUES (1,'金庸',66,'总裁',20000,'2000-01-01',NULL,5),
       (2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
       (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),
       (4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
       (5,'常遇春',43,'开发',10500,'2004-09-07',3,1),
       (6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);
       (7,'灭绝',60,'财务总监',8500,'2002-09-12',1,3),
       (8,'周芷若',19,'会计',48000,'2006-06-02',7,3),
       (9,'丁敏君',23,'出纳',5250,'2009-05-13',7,3),
       (10,'赵敏',20,'市场部总监',12500,'2004-10-03',1,2),
       (11,'鹿杖客',56,'职员',3750,'2006-05-01',10,2),
       (12,'鹤笔翁',19,'职员',3750,'2007-10-01',10,2),
       (13,'方东白',19,'职员',5500,'2009-10-01',10,2),
       (14,'张三丰',88,'销售总监',14000,'2004-10-01',1,2),
       (15,'俞莲舟',38,'销售',4600,'2004-10-01',14,4),
       (16,'宋远桥',40,'销售',4600,'2004-10-01',14,4),
       (17,'陈友谅',42,NULL,2000,'2011-10-01',1,NULL);

3.多对多

            案例：学生与课程的关系
            关系：一个学生可以选择多门课程进修，一门课程也可以供多个学生选择。
            实现：建立第三张中间表，中间至少包含两个外键，分别关联两方主键。

多对多
建两张表：

CREATE TABLE STUDENT(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT 'ID',
    NAME VARCHAR(10) COMMENT '姓名',
    NO VARCHAR(10) COMMENT '学号'
)COMMENT '学生表';
INSERT INTO STUDENT(ID,NAME, NO)
 VALUES (NULL,'熊黛丝','2000100101'),
        (NULL,'谢逊','2000100102'),
        (NULL,'殷天正','2000100103'),
        (NULL,'韦一笑','2000100104');
CREATE TABLE COURSE(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    NAME VARCHAR(10) COMMENT '课程名'
)COMMENT '课程表';
INSERT INTO COURSE(ID, NAME) VALUES (NULL,'Java'),(NULL,'PHP'),(NULL,'MySQL'),(NULL,'Hadoop');

创建中间表：

CREATE TABLE STUDENT_COURSE(
    ID INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY ,
    STUDENT_ID INT NOT NULL COMMENT '学生ID',
    COURSE_ID INT NOT NULL  COMMENT '课程ID',
    CONSTRAINT FK_COURSE_ID FOREIGN KEY (COURSE_ID) REFERENCES COURSE(ID),
    CONSTRAINT  FK_STUDENT_ID FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(ID)
)COMMENT '学生选课表';
INSERT INTO STUDENT_COURSE(ID, STUDENT_ID, COURSE_ID)
VALUES (NULL,1,1),
       (NULL,1,2),
       (NULL,1,3),
       (NULL,2,2),
       (NULL,2,3),
       (NULL,3,4);

图形可视化

4.一对一

            案例：用户与用户详情的关系。
            关系：一对一关系，多用于单表拆分，将其中一张表的基础字段放在一张表中，其详情字段放在另一张表中，以提升操作效率。
            实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

在这里插入图片描述
拆分：

建两张表并建立一对一关系：

CREATE  TABLE TB_USER(
    ID INT AUTO_INCREMENT PRIMARY KEY  COMMENT '主键ID',
    NAME VARCHAR(10) COMMENT '姓名',
    AGE INT COMMENT '年龄',
    GENDER CHAR(1) COMMENT '性别',
    PHONE CHAR(11) COMMENT '手机号'
)COMMENT '用户基本信息表';
CREATE TABLE TB_USER_EDU(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    DEGREE VARCHAR(20) COMMENT '学历',
    MAJOR VARCHAR(50) COMMENT '专业',
    PRIMARY_SCHOOL VARCHAR(50) COMMENT '小学',
    MIDDLE_SHOOL VARCHAR(50) COMMENT '中学',
    UNIVERSITY VARCHAR(50) COMMENT '大学',
    USERID INT UNIQUE COMMENT '用户ID',
    CONSTRAINT FK_USER_ID FOREIGN KEY (USERID) REFERENCES TB_USER(ID)

)COMMENT '用户教育信息表';

INSERT INTO TB_USER(ID, NAME, AGE, GENDER, PHONE)
VALUES (null,'黄渤',45,'1','18800001111'),
       (null,'冰冰',35,'2','18800002222'),
       (null,'码云',55,'1','18800008888'),
       (null,'李彦宏',50,'1','18800009999 ');

INSERT INTO TB_USER_EDU(ID, DEGREE, MAJOR, PRIMARY_SCHOOL, MIDDLE_SHOOL, UNIVERSITY, USERID)
VALUES (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
       (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
       (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
       (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

（二）多表查询概述

1.概述

多表查询：指从多张表中查询数据。

#多表查询--笛卡尔积
SELECT *FROM EMP,DEPT;

笛卡尔积现象
笛卡尔积：在数学中两个集合，A集合与B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积。）

消除无效的笛卡尔积:

SELECT *FROM EMP,DEPT WHERE EMP.DEPT_ID=DEPT.ID;

在这里插入图片描述

2.多表查询的分类

（1）连接查询
①内连接：相当于查询A、B交际部分数据。
②外连接：
a.左外连接：查询左表所有数据，及两张表交集部分数据。
b.右外连接：查询右表所有数据，及两张表交集部分数据。
③自连接：当前表与自身的连接查询，以及两张表必须使用表别名。
（2）子查询

（三）内连接

1.内连接查询语法

（1）隐式内连接

SELECT 字段列表 FROM 表1,表2, WHERE 条件...;

（2）显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

2.内连接查询演示

# 内连接演示
# 查询每一个员工的姓名，及关联的部门的名称（隐式内连接）
# 表结构：EMP,DEPT
# 连接条件：EMP.DEPT_ID=DEPT.ID
SELECT EMP.NAME,DEPT.NAME FROM EMP,DEPT WHERE EMP.DEPT_ID=DEPT.ID;
                          # 起别名                         
SELECT E.NAME,D.NAME FROM EMP E, DEPT D WHERE E.DEPT_ID=D.ID;
# 查询每一个员工的姓名，及关联的部门的名称（显式内连接）
SELECT E.NAME,D.NAME FROM EMP E INNER JOIN DEPT D ON E.DEPT_ID = D.ID;
                              # 省略INNER 
SELECT E.NAME,D.NAME FROM EMP E JOIN DEPT D ON E.DEPT_ID = D.ID;

内连接

（四）外连接

1.外连接查询语法

（1）左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JION 表2 ON 条件...;

（2）右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JION 表2 ON 条件...;

2.外连接查询演示

# 外连接演示
# 查询EMP表中的所有数据,和对应的信息部门,(左外连接)
# 表结构:EMP,DEPT
# 连接条件:EMP.DEPT_ID=DEPT.ID
SELECT  * , D.NAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPT_ID = D.ID;
SELECT  * , D.NAME FROM EMP E LEFT JOIN DEPT D ON E.DEPT_ID = D.ID;

# 查询DEPT表中的所有数据,和对应的员工信息,(右外链接)
SELECT D.* ,E.* FROM DEPT D RIGHT OUTER JOIN EMP E ON E.DEPT_ID=D.ID;
# 左外连接
SELECT D.* ,E.* FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPT_ID=D.ID;

（五）自连接

1.自连接的查询语法

SELECT 字段列表 FROM 表A 别名 A JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询。

2.自连接查询演

# 自连接
# 1.查询员工及其领导的名字。
#内连接查询形式的自连接查询
SELECT E1.NAME,E2.NAME FROM EMP E1,EMP E2 WHERE E1.MANAGERID=E2.ID;
SELECT E1.NAME,E2.NAME FROM EMP E1 JOIN EMP E2 ON E1.MANAGERID=E2.ID;
# 2.查询所有员工EMP及其领导的名字EMP,如果员工没有领导也要查询出来。
#外连接查询形式的自连接查询
SELECT E1.NAME '员工',E2.NAME '领导' FROM EMP E1 LEFT JOIN EMP E2 ON E1.MANAGERID=E2.ID;

在这里插入图片描述

（六）联合查询

1.union, union all 联合查询语法

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果。

SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

2.自连接查询演示

# UNION ALL,UNION
# 1.将薪资第一5000的员工，和年龄大于50岁的员工全部查询出来。
SELECT * FROM EMP WHERE SALARY < 5000
UNION ALL
SELECT * FROM EMP WHERE AGE > 50;

会发现有重复。
在这里插入图片描述
将关键字ALL去掉即可。

# UNION ALL,UNION
# 1.将薪资第一5000的员工，和年龄大于50岁的员工全部查询出来。
SELECT * FROM EMP WHERE SALARY < 5000
UNION 
SELECT * FROM EMP WHERE AGE > 50;

3.总结

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接和并在一起，union会对合并后之后的数据去重。

（七）子查询

1.概念及语法

**（1）概念:**SQL语句中嵌套SELECT语句，称为嵌套语句，又称为子查询。

SELECT * FROM t1 WHERE column1 (SELECT column1 FROM t2);

子查询外部语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个。
（2）根据子查询结果的不同，分为：
①标量子查询
子查询结果为单个值(数字，字符串，日期等)，最简单的形式。
常用的操作符为：= 、<> 、 > 、 >= 、< 、<=
演示如下：

# 标量子查询
# 1.查询‘销售部’的所有员工信息。
SELECT ID FROM DEPT WHERE NAME ='销售部';
SELECT * FROM EMP WHERE DEPT_ID ='4';
SELECT * FROM EMP WHERE DEPT_ID =(SELECT ID FROM DEPT WHERE NAME ='销售部');
# 2.查询在方东白入职之后的员工信息
SELECT ENTRYDATE FROM EMP WHERE NAME='方东白';
SELECT * FROM EMP WHERE ENTRYDATE>'2009-10-01';
SELECT * FROM EMP WHERE ENTRYDATE>(SELECT ENTRYDATE FROM EMP WHERE NAME='方东白');

②列查询
子查询结果为一列。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、ALL
操作符描述

演示如下：

# 列子查询
# 1.查询‘销售部’和‘市场部’的所有员工信息。
SELECT ID FROM DEPT WHERE NAME='市场部' OR NAME = '销售部';
SELECT  * FROM EMP WHERE DEPT_ID IN(2,4);

SELECT  * FROM EMP WHERE DEPT_ID IN(SELECT ID FROM DEPT WHERE NAME='市场部' OR NAME = '销售部');
# 2.查询比财务部所有人工资都高的员工信息。
# 我写的  WHERE后面不应该跟聚合函数，但是在子查询中进行聚合函数计算，然后再WHERE中判断是可行的。
SELECT ID FROM DEPT WHERE NAME='财务部';
SELECT MAX(SALARY) FROM EMP WHERE DEPT_ID=3;
SELECT * FROM EMP WHERE SALARY>48000;
SELECT * FROM EMP WHERE SALARY>(SELECT MAX(SALARY) FROM EMP WHERE DEPT_ID=(SELECT ID FROM DEPT WHERE NAME='财务部'));
#老师写的 
SELECT * FROM EMP WHERE SALARY>ALL(SELECT SALARY FROM EMP WHERE DEPT_ID=(SELECT ID FROM DEPT WHERE NAME='财务部'));
# 3.查询比研发部其中任意一人工资高的员工信息。
# 我写的
SELECT ID FROM DEPT WHERE NAME='研发部';
SELECT MIN(SALARY) FROM EMP WHERE DEPT_ID=1;
SELECT * FROM EMP WHERE SALARY>6600;
SELECT * FROM EMP WHERE SALARY>(SELECT MIN(SALARY) FROM EMP WHERE DEPT_ID=(SELECT ID FROM DEPT WHERE NAME='研发部'));
# 老师写的
SELECT ID FROM DEPT WHERE NAME='研发部';
SELECT SALARY FROM EMP WHERE DEPT_ID=1;
SELECT * FROM EMP WHERE SALARY>6600;
SELECT * FROM EMP WHERE SALARY>ANY(SELECT SALARY FROM EMP WHERE DEPT_ID=(SELECT ID FROM DEPT WHERE NAME='研发部'));

③行子查询
返回的结果是一行（可以是多列）
常用的操作符：= 、 <> 、 IN、 NOT IN
演示如下：

# 1.查询与‘张无忌’的薪资及直属领导相同的员工信息；
SELECT SALARY,MANAGERID FROM EMP WHERE NAME='张无忌';
SELECT * FROM EMP WHERE MANAGERID=1 AND SALARY=12500;

SELECT * FROM EMP WHERE (SALARY,MANAGERID)=(12500,1);
SELECT * FROM EMP WHERE (SALARY,MANAGERID)=(SELECT SALARY,MANAGERID FROM EMP WHERE NAME='张无忌');

④表子查询
子查询结果为多行多列
常用的操作符：IN
演示如下：

# 表子查询
# 1.查询与“鹿杖客””宋远桥“的职位和薪资都相同的员工信息
# a.查询“鹿杖客””宋远桥“的职位和薪资
SELECT SALARY,JOB FROM EMP WHERE NAME='鹿杖客' OR NAME='宋远桥';
# b.查询“鹿杖客””宋远桥“的职位和薪资都相同的员工信息
SELECT * FROM EMP WHERE (JOB,SALARY) IN (SELECT JOB,SALARY FROM EMP WHERE NAME='鹿杖客' OR NAME='宋远桥');
# 2.查询入职日期是’2006-01-01‘之后的员工信息，及其部门信息
# a.查询入职日期是’2006-01-01‘之后的员工信息
SELECT * FROM EMP WHERE ENTRYDATE>'2006-01-01';
# b.查询入职日期是’2006-01-01‘之后的员工信息对应的部门信息
SELECT  E.*,D.* FROM (SELECT * FROM EMP WHERE ENTRYDATE>'2006-01-01') E LEFT JOIN DEPT D ON E.DEPT_ID=D.ID;

（3）根据子查询位置的不同，分为：
WHERE之后，FROM之后，SELECT之后。

（八）多表查询案例

题目：

演示代码：

#再创建一个SALGRADE表格
CREATE TABLE SALGRADE(
    GRADE INT,
    LOW_SAL INT,
    HIGH_SAL INT

)COMMENT '薪资等级表';
INSERT INTO SALGRADE
VALUES (1,0,3000),
       (2,3001,5000),
       (3,5001,8000),
       (4,8001,10000),
       (5,10001,15000),
       (6,15001,20000),
       (7,20001,25000),
       (8,25001,30000);

# 1.查询员工的姓名、年龄、职位、部门信息（隐式内连接）
SELECT E.NAME,E.AGE ,E.JOB ,D.NAME FROM EMP E, DEPT D WHERE E.DEPT_ID=D.ID;
#2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
SELECT E.NAME,E.AGE,E.JOB,D.NAME FROM EMP E INNER JOIN DEPT D on E.DEPT_ID = D.ID WHERE AGE<30;
# 3.查询拥有员工的部门ID、部门名称。
SELECT DISTINCT D.ID,D.NAME FROM EMP E ,DEPT D WHERE E.DEPT_ID=D.ID;
# 4.查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有给分配部门，也要展示出来。
SELECT E.*,D.NAME FROM EMP E LEFT JOIN DEPT D on D.ID = E.DEPT_ID WHERE AGE>40;
# 5.查询所有员工的工资等级。
   # 连接条件：EMP.SALARY>=SALGRADE.LOW_SAL AND EMP.SALARY<=SALGRADE.HIGH_SAL  也可以写成 EMP.SALARY BETWEEN SALGRADE.LOW_SAL AND SALGRADE.HIGH_SAL
SELECT E.NAME, S.GRADE, E.SALARY, S.LOW_SAL, S.HIGH_SAL
FROM EMP E,
     SALGRADE S
WHERE E.SALARY >= S.LOW_SAL
  AND E.SALARY <= S.HIGH_SAL;

# 6.查询’研发部‘所有员工的信息及工资等级;
# 连接条件：n张表有n-1个连接条件。 ①EMP.SALARY BETWEEN SALGRADE.LOW_SAL AND SALGRADE.HIGH_SAL  ②E.DEPT_ID = D.ID
SELECT E.*, D.NAME, S.GRADE
FROM EMP E,
     DEPT D,
     SALGRADE S
WHERE E.DEPT_ID = D.ID
  AND (E.SALARY BETWEEN S.LOW_SAL AND S.HIGH_SAL)
  AND D.NAME = '研发部';

# 7.查询’研发部‘员工的平均工资
SELECT AVG(E.SALARY) FROM EMP E,DEPT D WHERE E.DEPT_ID=D.ID AND D.NAME='研发部';

# 8.查询工资比’灭绝‘高的员工信息。
SELECT  SALARY FROM EMP WHERE NAME='灭绝';
SELECT *FROM EMP WHERE SALARY>(SELECT  SALARY FROM EMP WHERE NAME='灭绝');

# 9.查询工资比平均薪资高的员工信息
SELECT AVG(SALARY) FROM EMP;
SELECT * FROM EMP WHERE SALARY>(SELECT AVG(SALARY) FROM EMP );

# 10.查询低于本部门平均薪资的员工信息；
# a.查询指定部门的平均工资
SELECT AVG(E1.SALARY) FROM EMP E1 WHERE E1.DEPT_ID='1';
SELECT AVG(E1.SALARY) FROM EMP E1 WHERE E1.DEPT_ID='2';
# b.查询低于本部门平均工资的员工信息。
SELECT E2.*, (SELECT AVG(E1.SALARY) FROM EMP E1 WHERE E1.DEPT_ID = E2.DEPT_ID) AS '平均工资'
FROM EMP E2
WHERE E2.SALARY < (SELECT AVG(E1.SALARY) FROM EMP E1 WHERE E1.DEPT_ID = E2.DEPT_ID);
# 11.查询所有的部门信息,并统计部门的员工人数.
SELECT ID,NAME,ID AS '部门人数' FROM DEPT;
SELECT COUNT(*) FROM EMP E WHERE E.DEPT_ID='1';

SELECT D.ID,D.NAME,(SELECT COUNT(*) FROM EMP E WHERE E.DEPT_ID=D.ID) AS '部门人数' FROM DEPT D;

# 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称.
 #涉及到的表:STUDENT COURSE STUDENT_COURSE;
 #连接条件:STUDENT.ID=STUDENT_CORSE.STUDEN_ID   COURSE.ID=STUDENT_COURSE.COURSE_ID
SELECT S.NAME,S.ID,C.NAME FROM STUDENT S,STUDENT_COURSE SC,COURSE C WHERE S.ID=SC.STUDENT_ID AND C.ID=SC.COURSE_ID;

六、事务

（一）事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式提交事务。

（二）事务操作

1.事务操作演示

创建表，并按正常转账操作顺序操作；

CREATE TABLE ACCOUNT(
    ID INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    NAME VARCHAR(10) COMMENT '姓名',
    MONEY INT COMMENT '余额'
)COMMENT '账户表';

INSERT INTO ACCOUNT(ID, NAME, MONEY) VALUES (NULL,'张三',2000),(NULL,'李四',2000);

# 转账操作（张三给李四转账1000）
  #1.先查询张三账户余额
SELECT * FROM ACCOUNT WHERE NAME='张三';

  #2.将张三账户余额-1000
UPDATE ACCOUNT SET MONEY=MONEY-1000 WHERE NAME='张三';

  #3.将李四账户余+1000
UPDATE ACCOUNT SET MONEY=MONEY+1000 WHERE NAME='李四';

转帐前
转账后
模拟程序在第2步之后出现异常：

# 模拟程序在第2步之后出现异常
# 先恢复数据
UPDATE ACCOUNT SET MONEY=2000 WHERE NAME='张三' OR NAME='李四';
#1.先查询张三账户余额
SELECT * FROM ACCOUNT WHERE NAME='张三';
  #2.将张三账户余额-1000
UPDATE ACCOUNT SET MONEY=MONEY-1000 WHERE NAME='张三';
程序抛出异常...
 #3.将李四账户余+1000
UPDATE ACCOUNT SET MONEY=MONEY+1000 WHERE NAME='李四';

编译时出现错误
在这里插入图片描述
转账失败后的账户余额
转账异常

2.事务控制方式一

查看/设置事务提交方式

# 查看：为1，属自动提交，为0，属手动提交。
SELECT @@autocommit;
# 设置：
SET @@autocommit = 0;

在这里插入图片描述

提交事务

COMMIT;

演示：

SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 0;
SELECT @@AUTOCOMMIT;
# 转账操作（张三给李四转账1000）
  #1.先查询张三账户余额
SELECT * FROM ACCOUNT WHERE NAME='张三';

  #2.将张三账户余额-1000
UPDATE ACCOUNT SET MONEY=MONEY-1000 WHERE NAME='张三';

  #3.将李四账户余+1000
UPDATE ACCOUNT SET MONEY=MONEY+1000 WHERE NAME='李四';
# 提交事务
COMMIT ;

回滚事务

保持事务正确性完整性。

ROLLBACK;

代码演示：

# 模拟程序在第2步之后出现异常
# 先恢复数据
UPDATE ACCOUNT SET MONEY=2000 WHERE NAME='张三' OR NAME='李四';
# 提交事务
COMMIT ;
# #1.先查询张三账户余额
SELECT * FROM ACCOUNT WHERE NAME='张三';
#   #2.将张三账户余额-1000
UPDATE ACCOUNT SET MONEY=MONEY-1000 WHERE NAME='张三';
程序抛出异常...
#  #3.将李四账户余+1000
 UPDATE ACCOUNT SET MONEY=MONEY+1000 WHERE NAME='李四';
# 出现错误，回滚事务
ROLLBACK ;

2.事务控制方式二

开启事务

START TRANSACTION 或 BEGIN;

提交事务

COMMIT;

回滚事务

ROLLBACK;

代码演示：

SET @@AUTOCOMMIT = 1;
SELECT @@AUTOCOMMIT;
# 开启事务
START TRANSACTION ;

# 1.先查询张三账户余额
SELECT * FROM ACCOUNT WHERE NAME='张三';
#  2.将张三账户余额-1000
UPDATE ACCOUNT SET MONEY=MONEY-1000 WHERE NAME='张三';
程序抛出异常...
#  3.将李四账户余+1000
 UPDATE ACCOUNT SET MONEY=MONEY+1000 WHERE NAME='李四';

# 提交事务
COMMIT ;

# 回滚事务
ROLLBACK ;

（三）事务四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库的改变就是永久的。

（四）并发事务问题

脏读

一个事务读到另一个事务还没提交的数据。

不可重复读

一个事务先后读取同一条记录，但是两次读取的数据不同，称之为不可重复读。

幻读

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了‘幻影’

（五）事务隔离级别

隔离级别

在这里插入图片描述
隔离级别：从上到下，数据安全性越来越高，但是性能越来越低。

查看隔离级别

SELECT @@TRANSACTION_ISOLATION;

在这里插入图片描述

设置事务隔离级别

SET [SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED|REPEATABLE READ |SERIALIZABLE}

在这里插入图片描述

READ UNCOMMITTED–演示

打开两个cmd模拟两个进程，操作顺序及演示结果如下：
READ UNCOMMITTED

READ COMMITTED–演示

解决了脏读问题。
READ COMMITTED
未解决不可重复读（第7步与第9步读取的数据不同）和幻读现象。

在这里插入图片描述

REPEATABLE READ–演示

解决了不可重复读的问题：第10步和第11步读取的数据相同，这一事务结束后再进行第12步就读取到更新的数据了。
在这里插入图片描述
不解决幻读：第3步查询显示没有，但是第六步插入显示已存在，然后再第7步查询还是显示没有。

SERIALIZABLE–演示

出现闪动之后，需要在左侧事务中添加数据之后并且COMMIT，右边事务才能添加数据，但是会显示数据已存在。
在这里插入图片描述

朝凌yyds

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础学习笔记

一、学习目标及任务一、数据库概述（一）、数据库相关概念1. 数据库：存储数据的仓库。数据是有组织的进行存储，简称DataBase(DB)；数据库管理系统：操纵和管理数据库的大型软件，Database Management System（DBMS） SQL：操作关系性数据库的编程软件，定义了一套操作关系型数据库统一标准，Structured QUer Language（SQL
复制链接

扫一扫