3. MySQL笔记

MySQL基础

1 数据库相关概念

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

1.1 数据库

• 存储和管理数据的仓库，数据是有组织的进行存储。
• 数据库英文名是 DataBase，简称DB。

1.2 数据库管理系统(DBMS)

• 管理数据库的大型软件
• 英文：DataBase Management System，简称 DBMS

在电脑上安装了数据库管理系统后，就可以通过数据库管理系统创建数据库来存储数据，也可以通过该系统对数据库中的数据进行数据的增删改查相关的操作。我们平时说的MySQL数据库其实是MySQL数据库管理系统。

1.3 常见的数据库管理系统

• Oracle：收费的大型数据库，Oracle 公司的产品
• MySQL： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购
• SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
• PostgreSQL：开源免费中小型的数据库
• DB2：IBM 公司的大型收费数据库产品
• SQLite：嵌入式的微型数据库。如：作为 Android 内置数据库
• MariaDB：开源免费中小型的数据库

1.4 SQL

• 英文：Structured Query Language，简称 SQL，结构化查询语言
• 操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作。

2 MySQL的安装

2.1 Windows下MySQL的安装

2.1.1 下载

下载地址：https://downloads.mysql.com/archives/community/

2.1.2 安装(解压)

解压即可，可以放在你想安装的位置。

2.2 MySQL卸载

如果你想卸载MySQL，也很简单。

右键开始菜单，选择命令提示符(管理员)，打开黑框。

1. 敲入net stop mysql，回车。

net stop mysql

2. 再敲入mysqld -remove mysql，回车。

mysqld -remove mysql

3. 最后删除MySQL目录及相关的环境变量。

至此，MySQL卸载完成！

2.3 MySQL配置

2.3.1 添加环境变量

注意：建议安装路径不要包含中文或者空格等特殊字符（使用纯英文目录）。

下面变量设置参数如下：

变量名：MYSQL_HOME
变量值：C:\devtools\mysql-8.0.27-winx64// 要根据自己的实际路径配置
变量名：Path
变量值：%MYSQL_HOME%\bin

解压完成后，需要我们手动配置的MySQL环境变量，此电脑右键->点击属性->往下滑找到高级系统设置->点击高级->点击环境变量

在系统变量部分，点击新建填写变量，确定。

点击新建填写相关变量，确定

注意：路径不能出错，建议到对应的安装路径复制。

验证是否配置成功，Win+R输入cmd 再输入

mysql --version

配置成功

2.3.2 新建配置文件

创建my.ini文件，记事本或者相关编辑软件，写入

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录,要根据自己的实际路径配置
basedir=C:\\devtools\mysql-8.0.27-winx64
# 设置mysql数据库的数据的存放目录,要根据自己的实际路径配置
datadir=C:\\devtools\mysql-8.0.27-winx64\data
# 允许最大连接数
max_connections=200
# 允许连接失败次数
max_connect_errors=10
# 服务端使用的字符集
character-set-server=utf8mb4
# 创建新表时使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用"mysql_native_password"插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

2.3.3 初始化MySQL

mysqld --initialize --console

root@localhost：，它后面接着的是随机密码，复制下来保存好。（冒号后面，也就是随机密码前面，有一个空格，不用复制）

2.3.4 安装mysql服务

接下来，执行

mysqld --install

安装成功会提示：Service successfully installed。

如果出现问题在下面可能出现的问题中查看。

2.3.5 启动、连接mysql、退出连接、停止MySQL服务

net start mysql  // 先启动mysql服务

mysql -uroot -p  // 再连接mysql

quit  或  exit     // 退出连接

net stop mysql  // 停止mysql服务

2.3.6 修改默认账户密码

修改密码的命令是 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘新密码’;。（这其实是一条sql语句）

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

或者

SET PASSWORD = PASSWORD('新密码');

成功后提示：Query OK, 0 rows affected。

下一次连接就需要用新密码连接了

2.3.7 可能出现的问题

（1）初始化mysqld --initialize --console时，出现 Install/Remove of the Service Denied，原因：没有以管理员身份运行，解决：以管理员身份运行。

（2）注册mysqld -install时，出现 The Service already exits ，原因：之前有安装过mysql服务，也可能时卸载残留，解决：先删除之前的，再安装目前的。

先输入，回车

sc query mysql

再输入，回车

sc delete mysql

再mysqld --install即可成功。

2.4 MySQL数据模型

关系型数据库：

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库

数据库的优点：

• 都是使用表结构，格式一致，易于维护。
• 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
  • 关系型数据库都可以通过SQL进行操作，所以使用方便。
  • 复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。
• 数据存储在磁盘中，安全。

输入：

create database db1;

小结：

• MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
• 在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件
• 每张表可以存储多条数据，数据会被存储到磁盘中 MYD 文件中

2.5 Linux下MySQL的安装

MySQL8.0.26-Linux版安装

1. 准备一台Linux服务器

云服务器或者虚拟机都可以;

Linux的版本为 CentOS7;

2. 下载Linux版MySQL安装包

https://downloads.mysql.com/archives/community/

3. 上传MySQL安装包

4. 创建目录,并解压

mkdir mysql

tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C mysql

5. 安装mysql的安装包

cd mysql

rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm 

rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm

yum install openssl-devel

rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm

rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm

如果此安装过程中出现报错

error: Failed dependencies:
	mariadb-libs is obsoleted by mysql-community-libs-8.0.28-1.el7.x86_64

解决方法:删除mysql-libs

yum remove mysql-libs

6. 启动MySQL服务

systemctl start mysqld

systemctl restart mysqld

systemctl stop mysqld

systemctl status mysqld

7. 查询自动生成的root用户密码

查看临时密码

grep 'temporary password' /var/log/mysqld.log

命令行执行指令 :

mysql -u root -p

然后输入上述查询到的自动生成的密码, 完成登录 .

8. 修改root用户密码

登录到MySQL之后，需要将自动生成的不便记忆的密码修改了，修改成自己熟悉的便于记忆的密码。

ALTER  USER  'root'@'localhost'  IDENTIFIED BY '1234';

执行上述的SQL会报错，原因是因为设置的密码太简单，密码复杂度不够。我们可以设置密码的复杂度为简单类型，密码长度为4。

set global validate_password.policy = 0;
set global validate_password.length = 4;

降低密码的校验规则之后，再次执行上述修改密码的指令。

9. 创建用户

默认的root用户只能当前节点localhost访问，是无法远程访问的，我们还需要创建一个root账户，用户远程访问

create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

10. 并给root用户分配权限

grant all on *.* to 'root'@'%';

11. 重新连接MySQL

mysql -u root -p

然后输入密码

2.6 卸载linux版mysql

停止MySQL服务

systemctl stop mysqld

查询MySQL的安装文件

rpm -qa | grep -i mysql

卸载上述查询出来的所有的MySQL安装包

rpm -e mysql-community-client-plugins-8.0.26-1.el7.x86_64 --nodeps

rpm -e mysql-community-server-8.0.26-1.el7.x86_64 --nodeps

rpm -e mysql-community-common-8.0.26-1.el7.x86_64 --nodeps

rpm -e mysql-community-libs-8.0.26-1.el7.x86_64 --nodeps

rpm -e mysql-community-client-8.0.26-1.el7.x86_64 --nodeps

rpm -e mysql-community-libs-compat-8.0.26-1.el7.x86_64 --nodeps

删除MySQL的数据存放目录

rm -rf /var/lib/mysql/

删除MySQL的配置文件备份

rm -rf /etc/my.cnf.rpmsave

3 SQL概述

3.1 SQL简介

• 英文：Structured Query Language，简称 SQL
• 结构化查询语言，一门操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准
• 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

3.2 通用语法

• SQL 语句可以单行或多行书写，以分号结尾。

• MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

• 注释

  • 单行注释: – 注释内容 或 #注释内容(MySQL 特有)

    注意：使用-- 添加单行注释时，--后面一定要加空格，而#没有要求。

  • 多行注释: /* 注释 */

3.3 SQL分类

3.3.1 数据定义语言（DDL）

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言用来定义数据库对象：数据库，表，列等，DDL简单理解就是用来操作数据库和操作表等，由create、alter与drop三个语法所组成，truncate清空表数据,并重置id自增。

3.3.2 数据操纵语言（DML）

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，用来对数据库中表的数据进行增删改，DML简单理解就对表中数据进行增删改，以insert、update、delete三种指令为核心，分别代表插入、更新与删除。

3.3.3 数据查询语言（DQL）

数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，用来查询数据库中表的记录(数据)，DQL简单理解就是对数据进行查询操作。从数据库表中查询到我们想要的数据。保留字select是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有from，where，group by，Having和order by。这些DQL保留字常与其他类型的SQL语句一起使用。

3.3.4 数据控制语言（DCL）

数据控制语言 (Data Control Language, DCL) 在SQL语言中，用来定义数据库的访问权限和安全级别，及创建用户，DML简单理解就是对数据库进行权限控制，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 Grant 和 revoke 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

3.3.5 事务控制语言（TCL）

事务控制语言（Trasactional Control Languag, TCL），经常被用于快速原型开发、脚本编程、GUI和测试等方面，代表关键字为commit、rollback。

4 数据定义语言DDL

4.1数据定义语言DDL:操作数据库

DDL来操作数据库。而操作数据库主要就是对数据库的增删查操作。

4.1.2 查询

查询所有的数据库

show databases;

4.1.2 创建数据库

• 创建数据库：

create database 数据库名称;

而在创建数据库的时候，我并不知道db1数据库有没有创建，直接再次创建名为db1的数据库就会出现错误。

• 指定字符集创建数据库

create database 数据库名 charset=utf8/gbk;

创建的数据库如果已经存在，将会报错，避免错误，在创建数据库的时候先做判断，如果不存在再创建。

• 创建数据库(判断，如果不存在则创建)：

create database if not exists 数据库名称;

从上面的效果可以看到虽然db1数据库已经存在，再创建db1也没有报错，而创建db2数据库则创建成功。

4.1.3 删除数据库

• 删除数据库

drop database 数据库名称;

• 删除数据库(判断，如果存在则删除)

drop database if exists 数据库名称;

4.1.4 使用数据库

数据库创建好了，要在数据库中创建表，得先明确在哪儿个数据库中操作，此时就需要使用数据库。

• 使用数据库

use 数据库名称;

• 查看当前使用的数据库

select database();

4.1.5 查看数据库信息

格式:

show create database 数据库名;

4.1.6 清空表数据,并重置id自增

--清空表数据,并重置id自增
truncate ams_admin;

4.2 数据定义语言DDL:操作表

操作表也就是对表进行增、删、改、查。

4.2.1 查询表

• 查询当前数据库下所有表名称

show tables;

• 查询表信息

show create table 表名;

• 查询表结构

desc 表名称;

4.2.2 创建表

create table 表名 (
	字段名1  数据类型1,
	字段名2  数据类型2,
	…
	字段名n  数据类型n
);

注意：最后一行末尾，不能加逗号

创建一个tb_user表

create table tb_user (
	id int,
    username varchar(20),
    password varchar(32)
);

4.2.3 数据类型

MySQL 支持多种类型，可以分为三类：

• 数值

  tinyint : 从 -2^7 (-128) 到 2^7 - 1 (123) 的整型数据。存储大小为 1 个字节。小整数型，占1个字节
  smallint : 从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。
  int ： 大整数类型，占4个字节
  	eg ： age int
  bigint : 大大整数类型，存储大小为 8 个字节。
  double ： 浮点类型
  	使用格式： 字段名 double(总长度(不包括小数点),小数点后保留的位数)
  	eg ： score double(5,2) 
  decimal(m,d):DECIMAL 类型用于存储精确的小数
	m是数字的最大位数，他的范围是从1-65；
	d是小数点后的位数，他的范围是0-30，并且不能大于m。
	如果m被省略了，那么m的值默认为10，
	如果d被省略了，那么d的值默认为0.
举例表示：
1、decimal(5,2) 所指代的范围是-999.99~999.99 数字的最大位数是5位，小数点右侧是2位，即有两位小数。
2、decimal(7,6)所指代的范围是-9.999999~9.999999 数字的最大位数是7位，小数点右侧是6位，即有六位小数。

int(m):m指最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。

• 日期

  date ： 日期值。只包含年月日
  	eg ：birthday date ： 
  datetime ： 混合日期和时间值。包含年月日时分秒
  time : 只能保存时分秒
  timestamp(时间戳,举例1970年1月1日的毫秒数),保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19 

• 字符串

  char ： 定长字符串。
  	优点：存储性能高
  	缺点：浪费空间
  	eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
  varchar ： 变长字符串。
  	优点：节约空间
  	缺点：存储性能底
  	eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间	
  text(m),可变长度, 最大值65535,建议保存长度大于255的

案例：

需求：设计一张学生表，请注重数据类型、长度的合理性
	1. 编号
	2. 姓名，姓名最长不超过10个汉字
	3. 性别，因为取值只有两种可能，因此最多一个汉字
	4. 生日，取值为年月日
	5. 入学成绩，小数点后保留两位
	6. 邮件地址，最大长度不超过 64
	7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
	8. 学生状态（用数字表示，正常、休学、毕业...）

语句设计如下：

create table student (
	id int,
    name varchar(10),
    gender char(1),
    birthday date,
    score double(5,2),
    email varchar(15),
    tel varchar(15),
    status tinyint
);

4.2.4 删除表

• 删除表

drop tables 表名;

• 删除表时判断表是否存在

drop table if exists 表名;

4.2.5 修改表

• 修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

-- 方式一：将表名student修改为stu
alter table student rename to stu;

rename table 原名 to 新名;

-- 方式一：将表名student修改为stu
rename table student to stu;

• 添加一列

最后面添加格式:ALTER TABLE 表名 ADD 列名 数据类型;

-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);

最前面添加格式: alter table 表名 add 字段名 类型 first;

-- 给emp1表在最前面添加一列id，该字段类型是int
alter table emp1 add id int first;

在xxx字段后面添加: alter table 表名 add 字段名 类型 after xxx;

-- 给emp1表在salary后添加一列dept，该字段类型是varchar(50)
alter table emp1 add dept varchar(50) after salary;

• 修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);

• 修改列名和数据类型

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

-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);

• 删除列

alter table 表名 drop 列名;

-- 将stu表中的addr字段 删除
alter table stu drop addr;

5 navicat使用

5.1 navicat概述

• Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。
• 这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。
• 官网： http://www.navicat.com.cn

5.2 navicat安装

详见草稿Navicat安装

5.3 navicat使用

5.3.1 建立和mysql服务的连接

进入软件后点击连接，选择MySQL

5.3.2 连接可能出现的问题

问题如下图：

原因：

没有启动MySQL服务

解决办法：
此电脑右键，选择管理，找到下面位置，选中MySQL右键启动，即可连接成功

或
在命令行输入

net start mysql    //启动服务

5.3.3 操作

• 修改表结构

通过选中要修改的表右键，选中设计表
点击了设计表后即出现如下图所示界面，在图中红框中直接修改字段名，类型等信息：

• 编写SQL语句并执行

按照如下图所示进行操作即可书写SQL语句并执行sql语句。

5.3.4 常用Navcat快捷键

快捷键	说明
鼠标左键三击	选中当前行
F6	弹出一个命令窗口
Ctrl + R	执行所有sql语句
Ctrl + Shift + R	只运行选中的sql语句
Ctrl + /	注释选中sql语句
Ctrl + Shift + /	解除选中sql语句
Ctrl + N	(在查询窗口)打开一个新的查询窗口, (在表窗口)在最后插入一行null数据
Ctrl + D	(在查询页面)复制当前行并粘贴到下一行
Ctrl + L	删除选中行内容
Ctrl + W	关闭当前查询窗口

6 数据操纵语言DML

DML主要是对数据进行增（insert）删（delete）改（update）操作。

6.1 添加数据

• 给指定列添加数据

insert into 表名(列名1,列名2,…) values(值1,值2,…);

• 给全部列添加数据

insert into 表名 values(值1,值2,…);

• 批量添加数据

insert into 表名(列名1,列名2,…) values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
insert into 表名 values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

• 练习

select * from student;

-- 给指定列添加数据
insert into student (id, NAME) values(1, '张三');
-- 给所有列添加数据，列名的列表可以省略的
insert into student (id,NAME,gender,birthday,score,email,tel,STATUS) values(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

insert into student values(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

-- 批量添加数据
insert into student values
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
	(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

6.2 修改数据

• 改表数据**

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

注意：

1. 修改语句中如果不加条件，则将所有数据都修改！
2. 像上面的语句中的中括号，表示在写sql语句中可以省略这部分

• 练习

  • 将李四的性别改为女

update student set gender='女' where name ='李四';

• 将张三的生日改为 2022-12-12 分数改为100

update student  set birthday = '2022-12-12', score = 100 where name = '李四';

• 注意：如果update语句没有加where条件，则会将表中所有数据全部修改！

update student set email='123@qq.com';

上面语句的执行完后查询到的结果是：

6.3 删除数据

• 删除数据

DELETE FROM 表名 [WHERE 条件] ;

• 练习

-- 删除张三记录
delete from student where name = '张三';

-- 删除student表中所有的数据
delete from student;

7 数据查询语言DQL

DQL查询语句，语法结构如下：

SELECT 
    字段列表
FROM 
    表名列表 
WHERE 
    条件列表
GROUP BY
    分组字段
HAVING
    分组后条件
ORDER BY
    排序字段
LIMIT
    分页限定

将上面的完整语法进行拆分，分为以下几个部分：

• 基本查询（不带任何条件）
• 条件查询（WHERE）
• 排序查询（order by）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（group by）
• 分页查询（limit）

-- 删除stu表
drop table if exists stu;


-- 创建stu表
CREATE TABLE stu (
 id int, -- 编号
 name varchar(20), -- 姓名
 age int, -- 年龄
 sex varchar(5), -- 性别
 address varchar(100), -- 地址
 math double(5,2), -- 数学成绩
 english double(5,2), -- 英语成绩
 hire_date date -- 入学时间
);

-- 添加数据
INSERT INTO stu(id,name,age,sex,address,math,english,hire_date) 
VALUES 
(1,'唐三',23,'男','天斗帝国',99,98,'1998-09-01'),
(2,'小舞',22,'女','星斗大森林',98,87,'1999-09-01'),
(3,'戴沐白',24,'男','星罗帝国',56,77,'1997-09-02'),
(4,'朱竹清',20,'女','星罗帝国',76,65,'2002-09-05'),
(5,'奥斯卡',20,'男','天斗帝国',86,NULL,'1998-09-01'),
(6,'宁荣荣',18,'男','天斗帝国',99,99,'2004-09-01'),
(7,'胡列娜',22,'女','武魂帝国',99,99,'1998-09-01'),
(8,'千仞雪',18,'女','武魂帝国',56,65,'2004-09-02');

7.1 基本查询

7.1.1 语法

• 查询多个字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

• 去除重复记录

select distinct  字段列表 from 表名;

• 起别名

AS: AS 也可以省略

7.1.2 练习

• 查询name、age两列

  select name,age from stu;

• 查询所有列的数据，列名的列表可以使用*替代

  select * from stu;

• 查询地址信息

  select address from stu;

执行上面语句结果如下：

从上面的结果我们可以看到有重复的数据，我们也可以使用 distinct 关键字去重重复数据。

• 去除重复记录

  select distinct address from stu;

• 查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）

  select name,math as 数学成绩,english as 英文成绩 from stu;
  select name,math 数学成绩,english 英文成绩 from stu;

7.2 条件查询

7.2.1 语法

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

• 条件
  条件列表可以使用以下运算符

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
!= 或 <>	不等于
between x and y	在x到y的范围之内(都包含)
in(x,y,z)	多选一, name in(x,y,z),选择name等于x或y或z的信息, name not in(x,y,z)不选择name等于x或y或z的信息
like占位符	模糊查询_单个任意字符%多个任意字符
is null	是null
is not null	不是null
and 或 &&	并且
or 或 ||	或者
not 或 !	非，不是

7.2.2 条件查询练习

• 查询年龄大于20岁的学员信息

  select * from stu where age > 20;

• 查询年龄大于等于20岁的学员信息

  select * from stu where age >= 20;

• 查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息

  select * from stu where age >= 20 &&  age <= 22;
  select * from stu where age >= 20 and  age <= 22;

上面语句中 && 和 and 都表示并且的意思。建议使用 and 。

也可以使用 between … and 来实现上面需求

  select * from stu where age BETWEEN 20 and 30;

• 查询入学日期在’1998-09-01’ 到 ‘1999-09-01’ 之间的学员信息

  select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';

• 查询年龄等于18岁的学员信息

  select * from stu where age = 18;

• 查询年龄不等于18岁的学员信息

  select * from stu where age != 18;
  select * from stu where age <> 18;

• 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息

  select * from stu where age = 18 or age = 20 or age = 22;
  select * from stu where age in (18,20 ,22);

• 查询英语成绩为 null的学员信息

  null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not

  select * from stu where english = null; -- 这个语句是不行的
  select * from stu where english is null;
  select * from stu where english is not null;

• 查询数学分数在2000到3000之间的员工信息

select * from stu where math=>50 and math<=90;

select * from emp where sal between 50 and 90;

7.2.3 模糊查询练习

模糊查询使用like关键字，可以使用通配符进行占位:

（1）_ : 代表单个任意字符

（2）% : 代表任意个数字符

• 查询姓’唐’的学员信息

  select * from stu where name like '唐%';

• 查询第二个字是’列’的学员信息

  select * from stu where name like '_列%';

• 查询名字中包含 ‘雪’ 的学员信息

  select * from stu where name like '%雪%';

7.3 排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

7.3.1 语法

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

上述语句中的排序方式有两种，分别是：

• ASC ： 升序排列 （默认值）
• DESC ： 降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

注意事项：
• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

7.3.2 练习

• 查询学生信息，按照年龄升序排列

  select * from stu order by age ;

• 查询学生信息，按照数学成绩降序排列

  select * from stu order by math desc ;

• 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列

  select * from stu order by math desc , english asc ;

• 查询学生信息，查询天斗帝国的学生，按照数学成绩降序排列，

select * from stu where address='天斗帝国' order by math desc 

7.4 聚合函数

7.4.1 概念

将一列数据作为一个整体，进行纵向计算 。

7.4.2 常见的聚合函数分类

函数名	功能
count(列名)	统计数量（一般选用不为null的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

7.4.3 聚合函数语法

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

注意：null 值不参与所有聚合函数运算

7.4.4 练习

• 统计班级一共有多少个学生

  select count(id) from stu;
  select count(english) from stu;

上面语句根据某个字段进行统计，如果该字段某一行的值为null的话，将不会被统计。所以可以在count(*) 来实现。* 表示所有字段数据，一行中也不可能所有的数据都为null，所以建议使用 count(*)

  select count(*) from stu;

• 查询数学成绩的最高分

  select max(math) from stu;

• 查询数学成绩的最低分

  select min(math) from stu;

• 查询数学成绩的总分

  select sum(math) from stu;

• 查询数学成绩的平均分

  select avg(math) from stu;

• 查询英语成绩的最低分

  select min(english) from stu;

7.4.5 数值计算 + - * / %

查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)

select name,sal,sal*5 年终奖 from emp;

给3号部门的员工每人涨薪5块钱

update emp set sal=sal+5 where dept_id=3;

7.5 分组查询

7.5.1 语法

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

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

注意事项:
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
执行顺序: where > 聚合函数 >having 。
where后面只能写普通字段的条件,不能包含聚合函数
having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面
支持多字段分组, 具体语法为 : group by columnA,columnB

where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

7.3.2 练习

• 查询男同学和女同学各自的数学平均分

select sex, avg(math) from stu group by sex;

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义

• 查询男同学和女同学各自的数学平均分，以及各自人数

select sex, avg(math),count(*) from stu group by sex;

• 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组

select sex, avg(math),count(*) from stu where math > 70 group by sex;

• 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的

select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;

7.6 分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台 都需要借助于数据库的分页操作。

7.6.1 语法

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

注意事项:

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

7.6.2 练习

• 从0开始查询，查询3条数据

  select * from stu limit 0 , 3;

• 每页显示3条数据，查询第1页数据

  select * from stu limit 0 , 3;

• 每页显示3条数据，查询第2页数据

  select * from stu limit 3 , 3;

• 每页显示3条数据，查询第3页数据

  select * from stu limit 6 , 3;

从上面的练习推导出起始索引计算公式：

起始索引 = (当前页码 - 1) * 每页显示的条数

7.7 各个关键字的书写顺序

select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

8 数据控制语言DCL

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

8.1 管理用户

8.1.1 查询用户

select * from mysql.user;

查询的结果如下:

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以 远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一 个用户。

8.1.2 创建用户

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

8.1.3 修改用户密码

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

8.1.4 删除用户

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

注意事项:

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 % 通配。
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。

8.1.5 练习

1. 创建用户tangsan, 只能够在当前主机localhost访问, 密码123456;

create user 'tangsan'@'localhost' identified by '123456';

2. 创建用户tangsan, 可以在任意主机访问该数据库, 密码123456;

create user 'tangsan'@'%' identified by '123456';

3. 修改用户tangsan的访问密码为1234;

alter user 'tangsan'@'%' identified with mysql_native_password by '1234';

4. 删除 tangsan@localhost 用户

drop user 'itcast'@'localhost';

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

alter user ‘heima’@‘%’ identified with mysql_native_password by ‘1234’;
drop user ‘itcast’@‘localhost’;

8.2 权限控制

MySQL中定义了很多种权限，常用权限：

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

更多详见官方文档：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

8.2.1 查询权限

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

8.2.2 授予权限

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

8.2.3 撤销权限

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

注意事项：

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

练习:

1. 查询 ‘tangsan’@‘%’ 用户的权限

show grants for 'tangsan'@'%';

2. 授予 ‘tangsan’@‘%’ 用户itcast数据库所有表的所有操作权限

grant all on itcast.* to 'tangsan'@'%';

3. 撤销 ‘tangsan’@‘%’ 用户的itcast数据库的所有权限

revoke all on itcast.* from 'tangsan'@'%';

9 函数

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

9.1 字符串函数

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

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接，将S1，S2，… Sn拼接成一个字符串,如果有任何一个参数为null,则concat返回值为null
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符 串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符 串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

1. concat : 字符串拼接

select concat('Hello' , ' MySQL');

2. lower : 全部转小写

select lower('HELLO');

3. upper : 全部转大写

select upper('Hello');

4. lpad : 左填充

select lpad('01', 5, '-');

5. rpad : 右填充

select rpad('01', 5, '-');

6. trim : 去除空格

select trim(' Hello  MySQL ');

7. substring : 截取子字符串

select substring('Hello MySQL',1,5);

案例：将stu学号统一为3位数，目前不足5位的全部在前面补0，比如1变为001。

 update emp set workno = lpad(stuno, 3, '0');

9.2 数值函数

常见的数值函数如下：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

1. ceil：向上取整

select ceil(1.1);

2. floor：向下取整

select floor(1.9);

3. mod：取模

select mod(7,4);

4. rand：获取随机数

select rand();

5. round：四舍五入

select round(2.344,2);

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

思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础 上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

select lpad(round(rand()*1000000 , 0), 6, '0');

9.3 日期函数

常见的日期函数如下：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1 和 结束时间date2之间的天数
now()	获取当前datetime
from_unixtime(时间戳)	将时间戳转换为datetime
unix_timestamp(now())	将datetime转换为时间戳
unix_timestamp()	获取当前时间戳

1. curdate：当前日期

select curdate();

2. curtime：当前时间

select curtime();

3. now：当前日期和时间

select now();

4. YEAR , MONTH , DAY：当前年、月、日

select YEAR(now()); 
select MONTH(now());
 select DAY(now());

5. date_add：增加指定的时间间隔

select date_add(now(), INTERVAL 70 YEAR );

6. datediff：获取两个日期相差的天数

select datediff('2021-10-01', '2021-12-01');

7. from_unixtime()将时间戳转换为datetime

# 将时间戳转换为datetime
select from_unixtime(dt.gy_time) from date_test1 dt;

8. unix_timestamp()将datetime转换为时间戳

# 将datetime转换为时间戳
select unix_timestamp(dt.begin_time) from date_test1 dt;
select now(),unix_timestamp(now());
select unix_timestamp();

9. 格式化时间

-- 格式化时间
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s') from dual;
str_to_date(
	'2016-12-15 16:48:40',
	'%Y-%m-%d %H:%i:%S'
)

date_format( ) 转换格式

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%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 位

案例：
查询所有stu学生的入学天数，并根据入学天数升序排序。
思路： 入学天数，就是通过当前日期 - 入学日期，所以需要使用datediff函数来完成。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays ASC;

9.4 流程函数。

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
IF(value , t , f)	如果value为true，则返回t，否则返回 f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则 返回value2
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END	如果val1为true，返回res1，… 否 则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END	如果expr的值等于val1，返回 res1，… 否则返回default默认值

1. if

select if(false, 'Ok', 'Error');
select if(true, 'Ok', 'Error');

2. ifnull

select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

3. case when then else end
   需求: 查询stu表的学生姓名和地址 (天斗帝国/武魂帝国 ----> 一线城市 , 其他 ----> 二线城市)

select
    name,
        ( case address when '天斗帝国' then '一线城市' when '武魂帝国' then '一线城市' else
       '二线城市' end ) as '地址'
from stu;

案例:将stu表的成绩分级
具体的SQL语句如下:

select
		id,
		name,
    (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学',
    (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语'
from stu;

10 约束

10.1 概念

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
   例如：我们可以给id列加约束，让其值不能重复，不能为null值。
2. 目的：保证数据库中数据的正确、有效性和完整性
   添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性。

10.2 分类

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本 之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致 性和完整性	FOREIGN KEY

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

9.3 非空约束

• 概念

  非空约束用于保证列中所有数据不能有NULL值

• 语法

  • 添加约束

    -- 创建表时添加非空约束
    CREATE TABLE 表名(
       列名 数据类型 NOT NULL,
       …
    ); 
    

    -- 建完表后添加非空约束
    ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
    

  • 删除约束

    ALTER TABLE 表名 MODIFY 字段名 数据类型;
    

9.4 唯—约束

• 概念

  唯一约束用于保证列中所有数据各不相同

• 语法

  • 添加约束

    -- 创建表时添加唯一约束
    CREATE TABLE 表名(
       列名 数据类型 UNIQUE [AUTO_INCREMENT],
       -- AUTO_INCREMENT: 当不指定值时自动增长
       …
    ); 
    CREATE TABLE 表名(
       列名 数据类型,
       …
       [CONSTRAINT] [约束名称] UNIQUE(列名)
    ); 
    

    -- 建完表后添加唯一约束
    ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
    

  • 删除约束

    ALTER TABLE 表名 DROP INDEX 字段名;
    

9.5 主键约束

• 概念

  主键是一行数据的唯一标识，要求非空且唯一

  一张表只能有一个主键

• 语法

  • 添加约束

    -- 创建表时添加主键约束
    CREATE TABLE 表名(
       列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
       …
    ); 
    CREATE TABLE 表名(
       列名 数据类型,
       [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
    ); 
    

    -- 建完表后添加主键约束
    ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
    

  • 删除约束

    ALTER TABLE 表名 DROP PRIMARY KEY;
    

9.6 默认约束

• 概念

  保存数据时，未指定值则采用默认值

• 语法

  • 添加约束

    -- 创建表时添加默认约束
    CREATE TABLE 表名(
       列名 数据类型 DEFAULT 默认值,
       …
    ); 
    

    -- 建完表后添加默认约束
    ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
    

  • 删除约束

    ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
    

9.7 约束练习

1. 根据需求，为表添加合适的约束

-- 员工表
CREATE TABLE emp (
	id INT,  -- 员工id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且唯一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'唐三','1999-11-11',8800,5000);

2. 验证主键约束，非空且唯一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'小舞','1999-11-11',8800,5000);

从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'宁荣荣','1999-11-11',8800,5000);

从上面结果可以看到，1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'小舞','1999-11-11',8800,5000);

添加成功。

3. 验证非空约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的非空约束生效了。

4. 验证唯一约束

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'宁荣荣','1999-11-11',8800,5000);

执行结果如下：

从上面结果可以看到，ename 字段的唯一约束生效了。

5. 验证默认约束

INSERT INTO emp(id,ename,joindate,salary) values(3,'宁荣荣','1999-11-11',8800);

执行完上面语句后查询表中数据，如下图可以看到王五这条数据的bonus列就有了默认值0。

注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。

6. 验证自动增长： auto_increment 当列是数字类型 并且唯一约束**

重新创建 emp 表，并给id列添加自动增长

CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('唐三','1999-11-11',8800,5000);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'小舞','1999-11-11',8800,5000);
INSERT INTO emp(id,ename,joindate,salary) values(null,'宁荣荣','1999-11-11',8800);

已自动增长：

9.8 外键约束

9.8.1 概述

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

9.8.2 语法

1. 添加外键约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

2. 删除外键约束

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

9.8.3 练习

根据上述语法创建员工表和部门表，并添加上外键约束：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

10 取消外键约束和设置外键约束

1 取消外键约束

SET FOREIGN_KEY_CHECKS = 0;

2 设置外键约束

SET FOREIGN_KEY_CHECKS = 1;

作用:个人认为最大对的作用便是删除存在约束的数据数据时无法删除,通过运行取消外键约束语句之后就可以删除,删除完成后再运行设置外键约束设置回来

10 数据库设计

10.1 数据库设计简介

• 软件的研发步骤

• 数据库设计概念

  • 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
  • 建立数据库中的表结构以及表与表之间的关联关系的过程。
  • 有哪些表？表里有哪些字段？表和表之间有什么关系？

• 数据库设计的步骤

  • 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

  • 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

  • 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

  • 维护设计（1.对新的需求进行建表；2.表优化）

• 表关系

  • 一对一
  • 一对多
  • 多对多

10.2 表关系(一对多)

• 一对多

  • 如：部门 和 员工
  • 一个部门对应多个员工，一个员工对应一个部门。

• 实现方式

  在多的一方建立外键，指向一的一方的主键

• 案例

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;

-- 部门表
CREATE TABLE tb_dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

10.3 表关系(多对多)

• 多对多

  • 如：商品 和 订单
  • 一个商品对应多个订单，一个订单包含多个商品

• 实现方式
  建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 案例

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;

-- 订单表
CREATE TABLE tb_order(
	id int primary key auto_increment,
	payment double(10,2),
	payment_type TINYINT,
	status TINYINT
);

-- 商品表
CREATE TABLE tb_goods(
	id int primary key auto_increment,
	title varchar(100),
	price double(10,2)
);

-- 订单商品中间表
CREATE TABLE tb_order_goods(
	id int primary key auto_increment,
	order_id int,
	goods_id int,
	count int
);

-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

10.4 表关系(一对一)

• 一对一

  • 如：用户 和 用户详情
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式

  在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

• 案例

create table tb_user_desc (
	id int primary key auto_increment,
	city varchar(20),
	edu varchar(10),
	income int,
	status char(2),
	des varchar(100)
);

create table tb_user (
	id int primary key auto_increment,
	photo varchar(100),
	nickname varchar(50),
	age int,
	gender char(1),
	desc_id int unique,
	-- 添加外键
	CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

10.5 数据库设计案例

某瓣音乐

分析

音乐专辑表名：Music

字段名	数据类型	说明
id	int	编号（唯一）
name	varchar(32)	歌曲名
image	varchar(64)	封面图片
style	varchar(8)	流派（如经典、流行、民谣、电子等）
type	varchar(4)	专辑类型（专辑、单曲等）
medium	varchar(4)	介质（CD、黑胶、数字等）
publish_time	date	发行时间
publisher	varchar(16)	出版者
summary	varchar(1024)	简介
performer	varchar(16)	表演者

实体图:

曲目表名： Song

字段名	数据类型	说明
id	int	编号（唯一）
name	varchar(32)	歌曲名
serial_number	tinyint	歌曲序号

实体图:

评论表名：Review

字段名	数据类型	说明
id	int	编号（唯一）
content	varchar(256)	评论内容
rating	tinyint	评分（1~5）
review_time	datetime	评论时间

实体图:

用户表名：User

字段名	数据类型	说明
id	int	用户编号(主键)
username	varchar(16)	用户名（唯一）
image	varchar(64)	用户头像图片地址
signature	varchar(64)	个人签名，例如（万般各所是 一切皆圆满）
nickname	varchar(16)	用户昵称

实体图:

一个专辑可以有多个曲目，一个曲目只能属于某一张专辑，所以专辑表和曲目表的关系是一对多。

一个专辑可以被多个用户进行评论，一个用户可以对多个专辑进行评论，所以专辑表和用户表的关系是 多对多。

一个用户可以发多个短评，一个短评只能是某一个人发的，所以用户表和短评表的关系是 一对多。

e-r图为:

10.6 表设计之权限管理

1. 权限管理表

任何网站中只要是存在权限管理的需求,表格都是设计成5张表,其中三张主表(用户表,角色表,权限表) 两张关系表(用户-角色关系表, 角色权限关系表)
创建三张主表

create database lastdb charset=utf8;
use lastdb;
create table user(id int primary key auto_increment,name varchar(50))charset=utf8;
create table role(id int primary key auto_increment,name varchar(50))charset=utf8;
create table module(id int primary key auto_increment,name varchar(50))charset=utf8;

创建两张关系表
用户角色关系表:

create table u_r(uid int,rid int);

角色权限关系表:

create table r_m(rid int,mid int);

准备数据

insert into user values(null,'刘德华'),(null,'王菲');
insert into role values(null,'男游客'),(null,'女会员');
insert into module values(null,'男浏览'),(null,'男发帖'),(null,'女浏览'),(null,'女发帖');

建立关系
刘德华->男游客和女会员 王菲->女会员
男游客->男浏览, 女会员->女浏览,女发帖

insert into u_r values(1,1),(1,2),(2,2);
insert into r_m values(1,1),(2,3),(2,4);

2. 权限管理关联查询相关练习题

1. 查询刘德华有哪些角色?

select r.name
from user u join u_r ur on u.id=ur.uid 
join role r on r.id=ur.rid where u.name="刘德华";

2. 查询女会员这个角色对应的用户都有谁?

select u.name
from user u join u_r ur on u.id=ur.uid 
join role r on r.id=ur.rid where r.name="女会员";

3. 查询男游客这个角色拥有什么权限?

select m.name
from role r join r_m rm on r.id=rm.rid 
join module m on rm.mid=m.id where r.name="男游客";

4. 查询女会员这个角色拥有什么权限?

select m.name
from role r join r_m rm on r.id=rm.rid 
join module m on rm.mid=m.id where r.name="女会员";

5. 查询刘德华这个用户拥有什么权限?

select m.name
from user u join u_r ur on u.id=ur.uid
join r_m rm on ur.rid=rm.rid
join module m on m.id=rm.mid
where u.name="刘德华";

6. 查询拥有女发帖这个权限的用户都有谁?

select u.name
from user u join u_r ur on u.id=ur.uid
join r_m rm on ur.rid=rm.rid
join module m on m.id=rm.mid
where m.name="女发帖";

10.7 三大范式

1. 第一范式：
   保存原子性
   第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2. 第二范式：
   完全依赖于主键，消除部分依赖
   某个属性既依赖于主键又依赖于其他属性，不能缺失一个第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

3. 第三范式：
   消除传递依赖：
   某个属性既对应了主键又对应了其他属性，可以缺失其中一个满足第三范式，必须先满足第二范式。简而言之，第三范式要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。

11 DQL多表查询

use db2;
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


# 创建部门表
	CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

	# 创建员工表
	CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
    );
	-- 添加部门数据
	INSERT INTO dept (dNAME) VALUES ('炎盟'),('蛇人族'),('云岚宗'),('米特尔家族');
	-- 添加员工数据
	INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
	('萧炎','男',7200,'2019-02-24',1),
	('美杜莎','女',3600,'2022-12-02',2),
	('海波东','男',9000,'2018-08-08',4),
	('云韵','女',5800,'2015-10-07',3),
	('小医仙','女',4500,'2022-03-14',null),
	('药老','男',5400,'2022-03-14',1),
	('雅妃','女',3000,'2020-03-14',4),
	('纳兰嫣然','女',2500,'2011-02-14',3);

执行下面的多表查询语句

select * from emp,dept;  -- 从emp和dept表中查询所有的字段数据

结果如下：

从上面的结果我们看到有一些无效的数据，如 萧炎 这个员工属于1号部门，但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，使用以下内连接查询。

select * from emp,dept where emp.dep_id = dept.did;

11.1 内连接查询

• 语法

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询 A B 交集数据

• 案例

  • 隐式内连接

select * from emp,dept where emp.dep_id = dept.did;

执行上述语句结果如下：

• 查询 emp的 name， gender，dept表的dname

select emp.name,emp.gender,dept.dname from emp,dept where emp.dep_id = dept.did;

执行语句结果如下：

上面语句中使用表名指定字段所属有点麻烦，sql也支持给表指别名，上述语句可以改进为

select t1.name,t1.gender,t2.dname from emp t1,dept t2 where t1.dep_id = t2.did;

• 显式内连接

select * from emp inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
select * from emp join dept on emp.dep_id = dept.did;

执行结果如下：

11.2 外连接查询

• 语法

  -- 左外连接
  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  
  -- 右外连接
  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

左外连接：相当于查询A表所有数据和交集部分数据

右外连接：相当于查询B表所有数据和交集部分数据

• 案例

  • 查询emp表所有数据和对应的部门信息（左外连接）

select * from emp left join dept on emp.dep_id = dept.did;

执行语句结果如下：

结果显示查询到了左表（emp）中所有的数据及两张表能关联的数据。

• 查询dept表所有数据和对应的员工信息（右外连接）

select * from emp right join dept on emp.dep_id = dept.did;

执行语句结果如下：

结果显示查询到了右表（dept）中所有的数据及两张表能关联的数据。

要查询出部门表中所有的数据，也可以通过左外连接实现，只需要将两个表的位置进行互换：

select * from dept left join emp on emp.dep_id = dept.did;

执行语句结果如下：

11.3 子查询

• 概念

  查询中嵌套查询，称嵌套查询为子查询。

  需求：查询工资高于美杜莎的员工信息。

  来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

select salary from emp where name = '美杜莎'

第二步：查询工资高于美杜莎的员工信息

select * from emp where salary > 3600;

第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

select * from emp where salary > (select salary from emp where name = '美杜莎');

• 子查询根据查询结果不同，作用不同

  • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
  • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
  • 子查询语句结果是多行多列，子查询语句作为虚拟表

• 案例

1. 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

-- 查询 '云岚宗' 或者 '炎盟' 所有的员工的部门did
select did from dept where dname = '云岚宗' or dname = '炎盟';

select * from emp where dep_id in (select did from dept where dname = '云岚宗' or dname = '炎盟');

2. 查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息

-- 查询入职日期是 '2019-11-11' 之后的员工信息
select * from emp where join_date > '2019-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2019-11-11' ) t1, dept where t1.dep_id = dept.did;

3. 查询拿最低工资员工的同事们的信息(同事指同一部门)

select min(salary) from emp;
select dep_id from emp where salary=(select min(salary) from emp);
select * from emp where dep_id=(select dep_id from emp where salary=(select min(salary) from emp)) and salary!=(select min(salary) from emp);

11.4 案例

• 环境准备：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
				
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);


-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

• 需求

  1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

     /*
     	分析：
     		1. 员工编号，员工姓名，工资 信息在emp 员工表中
     		2. 职务名称，职务描述 信息在 job 职务表中
     		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
     */
     -- 方式一 ：隐式内连接
     SELECT
     	emp.id,
     	emp.ename,
     	emp.salary,
     	job.jname,
     	job.description
     FROM
     	emp,
     	job
     WHERE
     	emp.job_id = job.id;
     
     -- 方式二 ：显式内连接
     SELECT
     	emp.id,
     	emp.ename,
     	emp.salary,
     	job.jname,
     	job.description
     FROM
     	emp
     INNER JOIN job ON emp.job_id = job.id;

2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

     /*
     	分析：
     		1. 员工编号，员工姓名，工资 信息在emp 员工表中
     		2. 职务名称，职务描述 信息在 job 职务表中
     		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
     
     		4. 部门名称，部门位置 来自于 部门表 dept
     		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
     */
     
     -- 方式一 ：隐式内连接
     SELECT
     	emp.id,
     	emp.ename,
     	emp.salary,
     	job.jname,
     	job.description,
     	dept.dname,
     	dept.loc
     FROM
     	emp,
     	job,
     	dept
     WHERE
     	emp.job_id = job.id
     	and dept.id = emp.dept_id
     ;
     
     -- 方式二 ：显式内连接
     SELECT
     	emp.id,
     	emp.ename,
     	emp.salary,
     	job.jname,
     	job.description,
     	dept.dname,
     	dept.loc
     FROM
     	emp
     INNER JOIN job ON emp.job_id = job.id
     INNER JOIN dept ON dept.id = emp.dept_id

3. 查询员工姓名，工资，工资等级

     /*
     	分析：
     		1. 员工姓名，工资 信息在emp 员工表中
     		2. 工资等级 信息在 salarygrade 工资等级表中
     		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
     */
     SELECT
     	emp.ename,
     	emp.salary,
     	t2.*
     FROM
     	emp,
     	salarygrade t2
     WHERE
     	emp.salary >= t2.losalary
     AND emp.salary <= t2.hisalary

4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

     /*
     	分析：
     		1. 员工编号，员工姓名，工资 信息在emp 员工表中
     		2. 职务名称，职务描述 信息在 job 职务表中
     		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
     
     		4. 部门名称，部门位置 来自于 部门表 dept
     		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
     		6. 工资等级 信息在 salarygrade 工资等级表中
     		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
     */
     SELECT
     	emp.id,
     	emp.ename,
     	emp.salary,
     	job.jname,
     	job.description,
     	dept.dname,
     	dept.loc,
     	t2.grade
     FROM
     	emp
     INNER JOIN job ON emp.job_id = job.id
     INNER JOIN dept ON dept.id = emp.dept_id
     INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

5. 查询出部门编号、部门名称、部门位置、部门人数

     /*
     	分析：
     		1. 部门编号、部门名称、部门位置 来自于部门 dept 表
     		2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
     		3. 使用子查询，让部门表和分组后的表进行内连接
     */
     -- 根据部门id分组查询每一个部门id和员工数
     select dept_id, count(*) from emp group by dept_id;
     
     SELECT
     	dept.id,
     	dept.dname,
     	dept.loc,
     	t1.count
     FROM
     	dept,
     	(
     		SELECT
     			dept_id,
     			count(*) count
     		FROM
     			emp
     		GROUP BY
     			dept_id
     	) t1
     WHERE
     	dept.id = t1.dept_id

12 事务

12.1 概述

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

12.2 语法

1. 开启事务

START TRANSACTION;
或者  
BEGIN;

2. 提交事务

commit;

3. 回滚事务

rollback;

12.3 代码验证

1. 环境准备

DROP TABLE IF EXISTS account;
  
  -- 创建账户表
CREATE TABLE account(
  id int PRIMARY KEY auto_increment,
  name varchar(10),
  money double(10,2)
);
  
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);

2. 不加事务演示问题

  -- 转账操作
  -- 1. 查询李四账户金额是否大于500
  
  -- 2. 李四账户 -500
  UPDATE account set money = money - 500 where name = '李四';
  
  出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
  -- 3. 张三账户 +500
  UPDATE account set money = money + 500 where name = '张三';

整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。

3. 添加事务sql如下：

  -- 开启事务
  BEGIN;
  -- 转账操作
  -- 1. 查询李四账户金额是否大于500
  
  -- 2. 李四账户 -500
  UPDATE account set money = money - 500 where name = '李四';
  
  出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
  -- 3. 张三账户 +500
  UPDATE account set money = money + 500 where name = '张三';
  
  -- 提交事务
  COMMIT;
  
  -- 回滚事务
  ROLLBACK;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

说明：
mysql中事务是自动提交的。
也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;

12.4 事务的四大特征

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

上述就是事务的四大特性，简称ACID。

12.5 并发事务问题

赃读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 “幻影”。

12.6 事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

隔离级别	脏读	不可重复读	幻读
Read uncommitted读未提交	√	√	√
Read committed读已提交	×	√	√
Repeatable Read可重复读(默认)	×	×	√
Serializable可串行化	×	×	×

1. 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

2. 设置事务隔离级别

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

注意：事务隔离级别越高，数据越安全，但是性能越低。

13 练习

练习一

-- 建表
-- 学生表
CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);
-- 课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);
-- 教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);
-- --成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);

-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , '80');
insert into Score values('01' , '02' , '90');
insert into Score values('01' , '03' , '99');
insert into Score values('02' , '01' , '70');
insert into Score values('02' , '02' , '60');
insert into Score values('02' , '03' , '80');
insert into Score values('03' , '01' , '80');
insert into Score values('03' , '02' , '80');
insert into Score values('03' , '03' , '80');
insert into Score values('04' , '01' , '50');
insert into Score values('04' , '02' , '30');
insert into Score values('04' , '03' , '20');
insert into Score values('05' , '01' , '76');
insert into Score values('05' , '02' , '87');
insert into Score values('06' , '01' , '31');
insert into Score values('06' , '03' , '34');
insert into Score values('07' , '02' , '89');
insert into Score values('07' , '03' , '98');

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.*,sc.s_score  ,sc2.s_score  from Student s 
left join Score sc on sc.s_id =s.s_id and sc.c_id ='01'
left join Score sc2 on sc2.s_id =s.s_id and sc2.c_id ='02'
left join Course c on c.c_id  =sc.c_id
where sc.s_score >sc2.s_score 

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select st.*,sc.s_score '语文',sc2.s_score '数学' from student st
left join score sc on sc.s_id=st.s_id and sc.c_id='01'
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
where sc.s_score<sc2.s_score

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
	-- ROUND(字段,保留几位小数)
SELECT s.*,AVG(sc.s_score) 平均成绩 from Student s 
left join Score sc on sc.s_id =s.s_id 
GROUP BY s.s_id HAVING AVG(sc.s_score)>=60

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select st.s_id,st.s_name,(case when ROUND(AVG(sc.s_score),2) is null then 0 else ROUND(AVG(sc.s_score)) end ) cjScore from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having AVG(sc.s_score)<60 or AVG(sc.s_score) is NULL

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select st.s_id,st.s_name,count(c.c_id),( case when SUM(sc.s_score) is null or sum(sc.s_score)='' then 0 else SUM(sc.s_score) end) from student st
left join score sc on sc.s_id =st.s_id
left join course c on c.c_id=sc.c_id
group by st.s_id

-- 6、查询"李"姓老师的数量
select t.t_name,count(t.t_id) from teacher t
group by t.t_id having t.t_name like '李%';

-- 7、查询学过"张三"老师授课的同学的信息
select st.* from student st
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
left join teacher t on t.t_id=c.t_id
where t.t_name='张三'

-- 8、查询没学过"张三"老师授课的同学的信息
-- 张三老师教的课
select c.* from course c left join teacher t on t.t_id=c.t_id where t.t_name='张三'
-- 有张三老师课成绩的st.s_id
select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name='张三')
-- 不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
select st.* from student st where st.s_id not in(
select sc.s_id from score sc where sc.c_id in (select c.c_id from course c left join teacher t on t.t_id=c.t_id where t.t_name='张三')
)

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select st.* from student st
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='01'
where st.s_id in (
select st2.s_id from student st2
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id='02'
)

网友提供的思路(厉害呦~):
SELECT st.*
FROM student st
INNER JOIN score sc ON sc.s_id=st.s_id
GROUP BY st.s_id
HAVING SUM(IF(sc.c_id='01' OR sc.c_id='02' ,1,0))>1

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select st.* from student st
inner join score sc on sc.s_id = st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='01'
where st.s_id not in (
select st2.s_id from student st2
inner join score sc2 on sc2.s_id = st2.s_id
inner join course c2 on c2.c_id=sc2.c_id and c2.c_id='02'
)

-- 11、查询没有学全所有课程的同学的信息
-- 太复杂,下次换一种思路,看有没有简单点方法
-- 此处思路为查学全所有课程的学生id,再内联取反面
select * from student where s_id not in (
select st.s_id from student st
inner join score sc on sc.s_id = st.s_id and sc.c_id='01'
where st.s_id in (
select st2.s_id from student st2
inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id='02'
) and st.s_id in (
select st2.s_id from student st2
inner join score sc2 on sc2.s_id = st2.s_id and sc2.c_id='03'
))
-- 来自一楼网友的思路，左连接，根据学生id分组过滤掉 数量小于 课程表中总课程数量的结果(show me his code),简洁不少。
select st.* from Student st
left join Score S
on st.s_id = S.s_id
group by st.s_id
having count(c_id)<(select count(c_id) from Course)

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct st.* from student st
left join score sc on sc.s_id=st.s_id
where sc.c_id in (
select sc2.c_id from student st2
left join score sc2 on sc2.s_id=st2.s_id
where st2.s_id ='01'
)

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select st.* from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id
having group_concat(sc.c_id) =
(
select group_concat(sc2.c_id) from student st2
left join score sc2 on sc2.s_id=st2.s_id
where st2.s_id ='01'
)

-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select st.s_name from student st
where st.s_id not in (
select sc.s_id from score sc
inner join course c on c.c_id=sc.c_id
inner join teacher t on t.t_id=c.t_id and t.t_name='张三'
)

-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select st.s_id,st.s_name,avg(sc.s_score) from student st
left join score sc on sc.s_id=st.s_id
where sc.s_id in (
select sc.s_id from score sc
where sc.s_score<60 or sc.s_score is NULL
group by sc.s_id having COUNT(sc.s_id)>=2
)
group by st.s_id

-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select st.*,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.c_id='01' and sc.s_score<60
order by sc.s_score desc

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 可加round,case when then else end 使显示更完美
select st.s_id,st.s_name,avg(sc4.s_score) '平均分',sc.s_score '语文',sc2.s_score '数学',sc3.s_score '英语' from student st
left join score sc on sc.s_id=st.s_id and sc.c_id='01'
left join score sc2 on sc2.s_id=st.s_id and sc2.c_id='02'
left join score sc3 on sc3.s_id=st.s_id and sc3.c_id='03'
left join score sc4 on sc4.s_id=st.s_id
group by st.s_id
order by SUM(sc4.s_score) desc

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select c.c_id,c.c_name,max(sc.s_score) '最高分',MIN(sc2.s_score) '最低分',avg(sc3.s_score) '平均分'
,((select count(s_id) from score where s_score>=60 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) '及格率'
,((select count(s_id) from score where s_score>=70 and s_score<80 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) '中等率'
,((select count(s_id) from score where s_score>=80 and s_score<90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) '优良率'
,((select count(s_id) from score where s_score>=90 and c_id=c.c_id )/(select count(s_id) from score where c_id=c.c_id)) '优秀率'
from course c
left join score sc on sc.c_id=c.c_id
left join score sc2 on sc2.c_id=c.c_id
left join score sc3 on sc3.c_id=c.c_id
group by c.c_id

-- 19、按各科成绩进行排序，并显示排名(实现不完全)
-- mysql没有rank函数
-- 加@score是为了防止用union all 后打乱了顺序
select c1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1 from (select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id='01' order by sc.s_score desc) c1 ,
(select @i:=0) a
union all
select c2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1 from (select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id='02' order by sc.s_score desc) c2 ,
(select @ii:=0) aa
union all
select c3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1 from (select c.c_name,sc.* from course c
left join score sc on sc.c_id=c.c_id
where c.c_id='03' order by sc.s_score desc) c3;
set @iii=0;

-- 20、查询学生的总成绩并进行排名
select st.s_id,st.s_name
,(case when sum(sc.s_score) is null then 0 else sum(sc.s_score) end)
from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id order by sum(sc.s_score) desc

-- 21、查询不同老师所教不同课程平均分从高到低显示
select t.t_id,t.t_name,c.c_name,avg(sc.s_score) from teacher t
left join course c on c.t_id=t.t_id
left join score sc on sc.c_id =c.c_id
group by t.t_id
order by avg(sc.s_score) desc

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select a.* from (
select st.,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id='01'
order by sc.s_score desc LIMIT 1,2 ) a
union all
select b. from (
select st.,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id='02'
order by sc.s_score desc LIMIT 1,2) b
union all
select c. from (
select st.*,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id =sc.c_id and c.c_id='03'
order by sc.s_score desc LIMIT 1,2) c

-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select c.c_id,c.c_name
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=100 and sc.s_score>80)/(select count(1) from score sc where sc.c_id=c.c_id )) '100-85'
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=85 and sc.s_score>70)/(select count(1) from score sc where sc.c_id=c.c_id )) '85-70'
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=70 and sc.s_score>60)/(select count(1) from score sc where sc.c_id=c.c_id )) '70-60'
,((select count(1) from score sc where sc.c_id=c.c_id and sc.s_score<=60 and sc.s_score>=0)/(select count(1) from score sc where sc.c_id=c.c_id )) '60-0'
from course c order by c.c_id

-- 24、查询学生平均成绩及其名次
set @i=0;
select a.*,@i:=@i+1 from (
select st.s_id,st.s_name,round((case when avg(sc.s_score) is null then 0 else avg(sc.s_score) end),2) '平均分' from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id order by sc.s_score desc) a

-- 25、查询各科成绩前三名的记录
select a.* from (
select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='01'
order by sc.s_score desc LIMIT 0,3) a
union all
select b.* from (
select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='02'
order by sc.s_score desc LIMIT 0,3) b
union all
select c.* from (
select st.s_id,st.s_name,c.c_id,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='03'
order by sc.s_score desc LIMIT 0,3) c

-- 26、查询每门课程被选修的学生数
select c.c_id,c.c_name,count(1) from course c
left join score sc on sc.c_id=c.c_id
inner join student st on st.s_id=c.c_id
group by st.s_id

-- 27、查询出只有两门课程的全部学生的学号和姓名
select st.s_id,st.s_name from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id
group by st.s_id having count(1)=2

-- 28、查询男生、女生人数
select st.s_sex,count(1) from student st group by st.s_sex

-- 29、查询名字中含有"风"字的学生信息
select st.* from student st where st.s_name like '%风%';

-- 30、查询同名同性学生名单，并统计同名人数
select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1)>1

-- 31、查询1990年出生的学生名单
select st.* from student st where st.s_birth like '1990%';

-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c.c_id,c.c_name,avg(sc.s_score) from course c
inner join score sc on sc.c_id=c.c_id
group by c.c_id order by avg(sc.s_score) desc,c.c_id asc

-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select st.s_id,st.s_name,avg(sc.s_score) from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having avg(sc.s_score)>=85

-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select st.s_id,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.s_score<60
inner join course c on c.c_id=sc.c_id and c.c_name ='数学'

-- 35、查询所有学生的课程及分数情况；
select st.s_id,st.s_name,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id =sc.c_id
order by st.s_id,c.c_name

-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select st2.s_id,st2.s_name,c2.c_name,sc2.s_score from student st2
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where st2.s_id in(
select st.s_id from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having min(sc.s_score)>=70)
order by s_id

-- 37、查询不及格的课程
select st.s_id,c.c_name,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.s_score<60
inner join course c on c.c_id=sc.c_id

-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select st.s_id,st.s_name,sc.s_score from student st
inner join score sc on sc.s_id=st.s_id and sc.c_id='01' and sc.s_score>=80

-- 39、求每门课程的学生人数
select c.c_id,c.c_name,count(1) from course c
inner join score sc on sc.c_id=c.c_id
group by c.c_id

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select st.*,c.c_name,sc.s_score,t.t_name from student st
inner join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id
inner join teacher t on t.t_id=c.t_id and t.t_name='张三'
order by sc.s_score desc
limit 0,1

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select st.s_id,st.s_name,sc.c_id,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
left join course c on c.c_id=sc.c_id
where (
select count(1) from student st2
left join score sc2 on sc2.s_id=st2.s_id
left join course c2 on c2.c_id=sc2.c_id
where sc.s_score=sc2.s_score and c.c_id!=c2.c_id
)>1

-- 42、查询每门功成绩最好的前两名
select a.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='01'
order by sc.s_score desc limit 0,2) a
union all
select b.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='02'
order by sc.s_score desc limit 0,2) b
union all
select c.* from (select st.s_id,st.s_name,c.c_name,sc.s_score from student st
left join score sc on sc.s_id=st.s_id
inner join course c on c.c_id=sc.c_id and c.c_id='03'
order by sc.s_score desc limit 0,2) c

-- 借鉴(更准确,漂亮):
select a.s_id,a.c_id,a.s_score from score a
where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 order by a.c_id

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
-- 若人数相同，按课程号升序排列
select sc.c_id,count(1) from score sc
left join course c on c.c_id=sc.c_id
group by c.c_id having count(1)>5
order by count(1) desc,sc.c_id asc

-- 44、检索至少选修两门课程的学生学号
select st.s_id from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having count(1)>=2

-- 45、查询选修了全部课程的学生信息
select st.* from student st
left join score sc on sc.s_id=st.s_id
group by st.s_id having count(1)=(select count(1) from course)

-- 46、查询各学生的年龄
select st.*,timestampdiff(year,st.s_birth,now()) from student st

-- 47、查询本周过生日的学生
-- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
-- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
select st.* from student st
where week(now())=week(date_format(st.s_birth,'%Y%m%d'))

-- 48、查询下周过生日的学生
select st.* from student st
where week(now())+1=week(date_format(st.s_birth,'%Y%m%d'))

-- 49、查询本月过生日的学生
select st.* from student st
where month(now())=month(date_format(st.s_birth,'%Y%m%d'))

-- 50、查询下月过生日的学生
-- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
select st.* from student st
where month(timestampadd(month,1,now()))=month(date_format(st.s_birth,'%Y%m%d'))
-- 或
select st.* from student st where (month(now()) + 1) mod 12 = month(date_format(st.s_birth,'%Y%m%d'))

练习二

查询重复字段

SELECT bank_bnkcode,COUNT(*) as count
FROM pub_pay_banka
GROUP BY bank_bnkcode
HAVING COUNT(*) > 1;

14 通过sql备份表(结构+数据)

1. mysql

1. 思路:MySQL将sql查询的结果保存为一张新的表
2. sql

# new_table_name 备份后的表名,
# 需要备份的表old_table_name,
# condition为条件,不写条件则为全部
# *为所有字段
create table new_table_name (select * from old_table_name where condition)
或
create table ms_mc_member_20220928 like ms_mc_member;
insert into ms_mc_member_20220928 select * from ms_mc_member;

15 导出数据(备份)

1. 可视化工具就不说了

2. linux系统

mysqldump -u账号 -p密码 mall(数据库名称) ums_menu(表名称) > uaa.sql

2. SQLServer

1. sql

# *代表所有的列
# schema sqlserver中的schema
# new_table_name 要创建的表名称
# old_table_name 要被复制的表
create table new_table_name (select * from old_table_name where condition)

3. Oracle

1. sql

# SCHEMA1 ORACLE中的schema，复制到那个schema下
# SCHEMA2 ORACLE中的schema，被复制的那个schema
# new_table_name 要创建的表名称
# old_table_name 要被复制的表
# *代表所有的列
create table SCHEMA1.new_table_name    as select * from SCHEMA2.old_table_name    

15 快速记忆

16 MySQL常用单词

数据定义语言 (Data Definition Language, DDL)

数据操纵语言（Data Manipulation Language, DML）

数据查询语言（Data Query Language, DQL）

数据控制语言 (Data Control Language, DCL)

事务控制语言（Trasactional Control Languag, TCL）

database数据库

database system数据库系统

database management system 数据库管理系统

entity实体

attribute属性

key关键字

type类型

primary key主键

foreign key外键

rule规则

select查询

between介于……之间

join连接

divide除

union并

intersection交

difference差

insert增加

update更新

delete删除

create创建

table表

drop删除

alter修改

view视图

index索引

primary file主要数据文件

transaction log事务日志文件

null空

check约束

column列

add添加

values值

constraint约束

count统计记录个数

sum总和

avg平局值

max最大值

min最小值

distinct去除重复

where=…条件

having=…条件

group by分组

compute by显示详细记录

unique唯一约束

unique index唯一索引

clustered index聚集索引

nonclustered index非聚集索引

with encryption加密

default默认

begin…end语句块

select @a=10赋值语句（可同时对多个变量赋值）

set @a=10赋值语句

break跳出循环

continue结束本次循环，开始下一条语句

procedure存储过程

restore还原

backup备份