MySQL基础

该文章持续更新中…

环境声明

• 系统: Windows10家庭中文版
• 硬件: 16G内存、8核CPU
• 集成环境: 小皮面板8.1.1.3
• Mysql版本: 5.7.26
• 强烈推荐学习视频: B站-Java酱

概念

数据库

数据库

连接数据库

本地数据库

# 连接数据库
mysql -u 数据库用户名 -p
mysql -u 数据库用户名 -p密码		// -p后面别空格

C:\Users\king>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

远程数据库

# 语法
mysql -h IP地址 -u 数据库用户名 -p

C:\Users\king>mysql -h 192.168.2.100 -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

远程连接失败时

本地登录到数据库后，查询需要登录的用户是否允许远程登录，host为%时允许远程登录

mysql> select host,user from mysql.user where user='root';
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |	
+-----------+------+

C:\Users\king>mysql -h 192.168.2.100 -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'Y7000.lan' is not allowed to connect to this MySQL server

将需要登录的用户host改为%，并且使用flush privileges刷新权限后再登录

mysql> update mysql.user set Host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

创建数据库

语法

-> 关于Mysql数据库字符集字符集 <-

-- 创建数据库
create database 数据库名;		-- 普通创建
create database if not exists 数据库名;		-- 数据库不存在时创建
create database 数据库名 character set 字符集名		-- 创建数据库时指定字符集

-- 查询数据库信息
show databases;		-- 查看所有数据库
show create databases 数据库名		-- 查看某个数据库的字符集

创建示例

-- 创建new_sql数据库，判断是否存在，并指定字符集为utf8
mysql> create database if not exists new_sql character set utf8;
Query OK, 1 row affected (0.00 sec)

-- 查看所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new_sql            |
| performance_schema |
| sys                |
+--------------------+

-- 查看指定数据库的创建语句(字符集)
mysql> show create database new_sql;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| new_sql  | CREATE DATABASE `new_sql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+

修改数据库

语法

-- 修改数据库的字符集
alter database 数据库名 character set 新字符集;

修改示例

-- 将new_sql数据库的字符集改为gbk
mysql> alter database new_sql character set gbk;
Query OK, 1 row affected (0.00 sec)

mysql> show create database new_sql;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| new_sql  | CREATE DATABASE `new_sql` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-----------------------------------------------------------------+

选择数据库

语法

-- 选择需要操作的数据库
use 数据库名;

-- 查看当前选择的数据库
select database();

选择示例

-- 选择mysql数据库
mysql> use mysql;
Database changed

-- 查看当前选择的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+

-- 查看选择的数据库有哪些数据表
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
......

删除数据库

语法

-- 删除数据库
drop database 数据库名;
drop database if exists 数据库名;		-- 数据库存在时再删除

删除示例

mysql> drop database new_sql;
Query OK, 0 rows affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

数据类型

下方表格来源: 菜鸟教程-Mysql教程

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串(动态分配)
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

数据表

创建数据表

语法

-- 创建数据表
create table 表名(
	字段名 数据类型 其他可选参数,
	字段名 数据类型 其他可选参数，
    PRIMARY KEY (主键字段名)
);

-- 表不存在时创建
create table if not exists 表名(
	字段名 数据类型 其他可选参数,
	字段名 数据类型 其他可选参数，
    PRIMARY KEY (主键字段名)
);

-- 查看数据表
show tables;

-- 查看表结构
desc 表名;

创建示例

1. id字段(列)类型为int，not null表示值不能为NULL空值，AUTO_INCREMENT表示字段自增长
2. username字段类型为varchar(20)，最大字符个数为20，not null表示值不能为NULL空值
3. email字段类型为varchar(20)，最大字符个数为20，值可以为空
4. PRIMARY KEY (id)表示该表的主键为字段id

-- 创建一个名为new_sql的数据库
mysql> create database new_sql;
Query OK, 1 row affected (0.02 sec)

-- 选择new_sql数据库
mysql> use new_sql;
Database changed

-- 创建名为users的数据表
mysql>create table users(
	id int not null AUTO_INCREMENT,
	username varchar(20) not null,
	password varchar(20) not null,
	email varchar(20),
	PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.08 sec)	-- 执行成功

-- 查看选择的数据库的所有数据表
mysql> show tables;
+-------------------+
| Tables_in_new_sql |
+-------------------+
| users             |
+-------------------+

-- 查看数据表结构
mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

修改数据表

语法

-- 修改表名
alter table 旧表名 rename to 新表名;

-- 修改表字符集
alter table 表名 character set 新字符集;

-- 添加一列
alter table 表名 add 字段名 数据类型;

-- 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型;

-- 修改字段数据类型
alter tables 表名 modify 字段名 新数据类型;

-- 删除字段
alter table 表名 drop 字段名;

修改表名

-- 将users表名修改为user_info
mysql> alter table users rename to user_info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_new_sql |
+-------------------+
| user_info         |
+-------------------+

修改表字符集

-- 将user_info表的字符集修改为utf8
mysql> alter table user_info character set utf8;

-- 查询指定表的创建信息(包括字符集)
mysql> show create table user_info;

添加字段(列)

-- 为user_info表添加一个字段，字段名为age，数据类型为int
mysql> alter table user_info add age int;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改前user_info表结构信息
mysql> desc user_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

-- 修改后user_info表结构信息(增加了age字段)
mysql> desc user_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
| age      | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

修改字段名

-- 将user_info表的age字段名修改为sex，且数据类型修改为char(2)
mysql> alter table user_info change age sex char(2);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改后user_info表结构信息(age变成sex,int变成char)
mysql> desc user_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
| sex      | char(2)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

修改字段数据类型

-- 将user_info表的sex字段数据类型修改为int
mysql> alter table user_info modify sex int;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改后user_info表结构信息(sex -> char变成sex -> int)
mysql> desc user_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
| sex      | int(11)     | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

删除字段

# 删除user_info表的sex字段
mysql> alter table user_info drop sex;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改后user_info表结构信息(sex字段被删除)
mysql> desc user_info;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(20) | NO   |     | NULL    |                |
| password | varchar(20) | NO   |     | NULL    |                |
| email    | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

删除数据表

语法

-- 删除数据表
drop table 表名;
drop table if exists 表名;		-- 表存在时再删除

删除示例

-- 删除user_info数据表
mysql> drop table user_info;
Query OK, 0 rows affected (0.00 sec)

-- 查询当前数据库的数据表
mysql> show tables;
Empty set (0.00 sec)	-- 无表

插入数据

注意

1. 字段和值要一一对应
2. 表名后不指定字段则表示给所有列添加值
3. 除了数字类型，其他类型需要使用引号包裹数据(标准使用单引号)
4. 当有自增长的字段时，可以不用插入数据
5. 字段为not null时不允许不插入数据

语法

-- 插入数据
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
insert into 表名 values(值1,值2,值3);

插入示例

-- users表信息
mysql> desc users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |		-- id自增长，主键
| username | varchar(20) | NO   |     | NULL    |                |		-- 不允许为空
| password | varchar(20) | NO   |     | NULL    |                |		-- 不允许为空
| email    | varchar(20) | YES  |     | NULL    |                |		-- 允许为空
+----------+-------------+------+-----+---------+----------------+

-- 插入一行数据，每个字段与值一一对应
mysql> insert into users(id,username,password,email) values(1,"admin","123456","1@qq.com");
Query OK, 1 row affected (0.00 sec)

-- 插入一行数据，不指定字段时每个值都需要有
mysql> insert into users values(2,"guest","654321","2@qq.com");
Query OK, 1 row affected (0.00 sec)

-- 插入一行数据，指定需要插入的字段(字段为not null时不允许没数据)
mysql> insert into users(id,username,password) values(3,"test","123654");
Query OK, 1 row affected (0.00 sec)

-- 插入一行数据，id字段已经有自增长了，所以可以不用自己定义序号
mysql> insert into users(username,password,email) values("user1","abcdef","");

-- 查询users表中所有字段的数据
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

简单查询

语法

select 字段1,字段2 from 数据库名.表名		-- 未选择数据库或查询其他数据库时使用
select 字段1,字段2 from 表名		-- 查询选中数据库的指定表的指定字段
select * from 表名				-- 查询指定表的所有字段(*表示全部)

查询示例

-- 只查询users表的id与username字段的数据
mysql> select id,username from users;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | guest    |
|  3 | test     |
|  4 | user1    |
+----+----------+

-- 查询users表的所有字段
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

修改数据

语法

-- 将指定字段的所有值都改成指定值(不指定条件则修改全部)
update 表名 set 字段1=值1,字段2=值2

-- 将指定条件的指定字段都改成指定值
update 表名 set 字段1=值1,字段2=值2 where 条件

修改示例

-- 创建销售记录表并插入数据
create table sales_records(
	id int not null AUTO_INCREMENT,
	name varchar(20) not null,		-- 销售员
	commodity varchar(20) not null,		-- 商品名
	PRIMARY KEY (id)
);
insert into sales_records(name,commodity) values("小王","牛奶"),("小陈","牛奶"),("小王","西瓜"),("小王","冬瓜");

-- 查询sales_records表所有字段数据
mysql> select * from sales_records;
+----+--------+-----------+
| id | name   | commodity |
+----+--------+-----------+
|  1 | 小王   | 牛奶      |
|  2 | 小陈   | 牛奶      |
|  3 | 小王   | 西瓜      |
|  4 | 小王   | 冬瓜      |
+----+--------+-----------+

-- 将name字段的小陈改成小张
mysql> update sales_records set name='小张' where name='小陈';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 修改后的sales_records表 (小陈变小张)
mysql> select * from sales_records;
+----+--------+-----------+
| id | name   | commodity |
+----+--------+-----------+
|  1 | 小王   | 牛奶      |
|  2 | 小张   | 牛奶      |
|  3 | 小王   | 西瓜      |
|  4 | 小王   | 冬瓜      |
+----+--------+-----------+

-- 将name字段的所有数据都改成小红
mysql> update sales_records set name="小红";
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

# 修改后的sales_records表 (全部变小红)
mysql> select * from sales_records;
+----+--------+-----------+
| id | name   | commodity |
+----+--------+-----------+
|  1 | 小红   | 牛奶      |
|  2 | 小红   | 牛奶      |
|  3 | 小红   | 西瓜      |
|  4 | 小红   | 冬瓜      |
+----+--------+-----------+

删除数据

语法

-- 删除所有值(清空表)
delete from 表名;			-- 不推荐: 一条一条删除记录，有多少删多少
truncate table 表名;		-- 推荐: 删除表后再创建一张一样的表

-- 删除指定条件下的所有值
delete from 表名 where 条件

删除示例

-- 删除sales_records表下id字段为1的一行数据
mysql> delete from sales_records where id=1;
Query OK, 1 row affected (0.02 sec)

mysql> select * from sales_records;
+----+--------+-----------+
| id | name   | commodity |
+----+--------+-----------+
|  2 | 小红   | 牛奶      |
|  3 | 小红   | 西瓜      |
|  4 | 小红   | 冬瓜      |
+----+--------+-----------+

-- 删除sales_records表的所有行 (删除全部数据)
mysql> delete from sales_records;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from sales_records;
Empty set (0.00 sec)

条件查询

指定别名

语法

1. 当表名很长或执行特殊查询时，为方便操作可以为表或字段指定别名，用别名代替原来的表或字段名
2. 表的别名不能与该数据库其他表同名，字段的别名不能与该表其他字段同名
3. 在条件表达式中不能使用字段的别名，否则会报错
4. 表的别名只在执行查询时使用，并不在返回结果中显示，而字段别名会在客户端显示
5. 别名包含空格时使用引号包裹别名

-- 为字段指定别名
select 字段1 as 别名,字段2 as 别名 from 表名;
select 字段1 别名,字段2 别名 from 表名;

-- 为表指定别名(一般在多表查询时使用)
select 表1.字段1,表1.字段2 from 表名 as 别名;
select 表1.字段1,表1.字段2 from 表名 别名;

别名示例

-- 创建实验数据表
drop table if exists users;
create table users(
	id int(11) primary key auto_increment,
	username varchar(20) not null,
	password varchar(20) not null,
	email varchar(20)
);
insert into users(id,username,password,email) values(1,"admin","123456","1@qq.com");
insert into users values(2,"guest","654321","2@qq.com");
insert into users(id,username,password) values(3,"test","123654");
insert into users(username,password,email) values("user1","abcdef","");

-- 指定字段别名
mysql> select id,username as '用户名' from users;	-- 一样的效果
mysql> select id,username '用户名' from users;
+----+-----------+
| id | 用户名    |
+----+-----------+
|  1 | admin     |
|  2 | guest     |
|  3 | test      |
|  4 | user1     |
+----+-----------+

-- 指定表别名
mysql> select u.id,u.username from users as u;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | guest    |
|  3 | test     |
|  4 | user1    |
+----+----------+

指定条件

运算符

运算符	描述
>,<,>=,<=,=,<>	大于、小于、大于等于、小于等于、不等于
IN(元素)	包含集合中某个元素
IS NULL	值为NULL
and、&&	与，多个条件都要成立
or、双管道符	或，只要条件成立一个
not、!	不为，取反值

语法

select 字段1,字段2 from 表名 where 条件

-- 查询某字段数据是否为指定值
select 字段1,字段2 from 表名 where 字段名 = 值		-- 查询数据为指定值
select 字段1,字段2 from 表名 where 字段名 != 值		-- 查询数据不为指定值
select 字段1,字段2 from 表名 where 字段名 <> 值		-- 查询数据不为指定值

-- 多条件同时满足
select 字段1,字段2 from 表名 where 条件1 and 条件2 and 条件n
select 字段1,字段2 from 表名 where 条件1 && 条件2 && 条件n

-- 多条件只需满足一个或以上
select 字段1,字段2 from 表名 where 条件1 or 条件2 or 条件n
select 字段1,字段2 from 表名 where 条件1 || 条件2 || 条件n

-- 查询某列数据是否包含在指定集合中
select 字段1,字段2 from 表名 where 字段名 IN(元素1,元素2,元素n)			-- 查询包含集合元素的数据
select 字段1,字段2 from 表名 where 字段名 not IN(元素1,元素2,元素n)		-- 查询不包含集合元素的数据

查询指定值

-- 原始数据
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 查询username字段中数据为admin的所有数据
mysql> select * from users where username="admin";
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
+----+----------+----------+----------+

-- 查询username字段中数据不为admin的所有数据
mysql> select * from users where username<>"admin";
mysql> select * from users where username!="admin";  -- 一样的效果
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 根据id字段查询前面两个用户数据
mysql> select * from users where id<=2;
mysql> select * from users where id<3;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
+----+----------+----------+----------+

满足多条件

-- 查询username字段数据为admin且password字段数据为123的所有数据
mysql> select * from users where username="admin" && password="123";
mysql> select * from users where username="admin" and password="123";
Empty set (0.00 sec)		-- 没有查询到数据

满足一个或以上条件

-- 查询username字段数据为admin或password字段数据为123的所有数据
mysql> select * from users where username="admin" || password="123";
mysql> select * from users where username="admin" or password="123";
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
+----+----------+----------+----------+

集合元素

-- 查询username字段数据为admin或guest或aaa的所有数据
mysql> select * from users where username="admin" or username="guest" or username="aaa";
mysql> select * from users where username in ("admin","guest","aaa");	-- 推荐写法
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
+----+----------+----------+----------+

查询NULL值

-- 查询email字段数据为null的所有数据(不能使用=)
mysql> select * from users where email is null;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  3 | test     | 123654   | NULL  |
+----+----------+----------+-------+

-- 查询email字段数据不为null的所有数据(不能使用!=)
mysql> select * from users where email is not null;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

模糊查询

占位符

占位符	描述
_	单个字符
%	多个字符

语法

select 字段1,字段2 from 表名 where 字段名 like 字符

查询示例

-- 原始数据
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 查询username字段数据中以a开头的所有数据
mysql> select * from users where username like "a%";
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
+----+----------+----------+----------+

-- 查询username字段数据中第二个字符为e的所有数据
mysql> select * from users where username like "_e%";
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  3 | test     | 123654   | NULL  |
+----+----------+----------+-------+

-- 查询username字段数据中包含e的所有数据
mysql> select * from users where username like "%e%";
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 查询username字段数据中数据长度为4个字符的所有数据(下面有四个_)
mysql> select * from users where username like "____";
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  3 | test     | 123654   | NULL  |
+----+----------+----------+-------+

排序查询

语法

select 字段1,字段2 from 表名 order by 字段名 ASC;		-- 根据指定字段进行升序排序(默认)
select 字段1,字段2 from 表名 order by 字段名 DESC;		-- 根据指定字段进行降序排序

查询示例

-- 根据id字段对查询的数据进行升序排序
mysql> select * from users order by id ASC;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+
4 rows in set (0.00 sec)

-- 根据id字段对查询的数据进行降序排序
mysql> select * from users order by id DESC;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  4 | user1    | abcdef   |          |
|  3 | test     | 123654   | NULL     |
|  2 | guest    | 654321   | 2@qq.com |
|  1 | admin    | 123456   | 1@qq.com |
+----+----------+----------+----------+
4 rows in set (0.00 sec)

分组函数

函数

1. 分组函数的计算自动排除NULL值
2. 分组函数不能直接使用在where语句中
3. 分组函数需要分组之后才能使用，默认一整张表为一组

函数	描述
count()	计算个数
max()	计算最大值
min()	计算最小值
sum()	计算和
avg()	计算平均值

查询示例

-- users表数据
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 统计users表的email字段共有几条数据(自动排除NULL)
mysql> select count(email) from users;
+--------------+
| count(email) |
+--------------+
|            3 |
+--------------+

-- 统计users表共有几行数据
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

-- 查询users表的id字段数据最大值是哪个
mysql> select max(id) from users;
+---------+
| max(id) |
+---------+
|       4 |
+---------+

-- 查询users表的id字段数据最小值是哪个
mysql> select min(id) from users;
+---------+
| min(id) |
+---------+
|       1 |
+---------+

-- 计算前两个id数据之和(1+2)
mysql> select sum(id) from users where id<3;
+---------+
| sum(id) |
+---------+
|       3 |
+---------+

-- 计算前两个id数据平均值((1+2)/2)
mysql> select avg(id) from users where id<3;
+---------+
| avg(id) |
+---------+
|  1.5000 |
+---------+

分组查询

语法

1. select语句中如果使用了group by，则select后面只能跟参加分组的字段与分组函数，其他一律不能跟！！！

-- 对数据表按照指定字段进行分组
select 分组字段,分组函数(字段) from 表名 group by 分组字段1,分组字段2;

-- 对数据表按照指定字段进行分组，并指定条件
select 分组字段,分组函数(字段) from 表名 group by 分组字段 having 条件;

执行顺序

-- 语法(只能按这个顺序写，不可颠倒)
select ... from ... where ... group by ... having ... order by ...

-- 执行顺序
from -> where -> group by -> having -> select -> order by

where与having区别

1. where在分组查询前进行限定，不满足条件则不参与分组，不能使用聚合函数
2. having在分组之后进行限定，不满足条件则不会被查询出来，可以使用聚合函数
3. 优化策略: 能用where实现的就使用where，实现不了就使用having

查询示例

-- 创建销售记录表并插入数据
drop table if exists sales_records;
create table sales_records(
	id int not null AUTO_INCREMENT,
	name varchar(20) not null,		-- 销售员
	commodity varchar(20) not null,		--  商品名
	PRIMARY KEY (id)
);

insert into sales_records(name,commodity) values("小王","牛奶"),("小陈","牛奶"),("小王","西瓜"),("小王","冬瓜");

-- sales_records表数据
+----+--------+-----------+
| id | name   | commodity |
+----+--------+-----------+
|  1 | 小王   | 牛奶      |
|  2 | 小陈   | 牛奶      |
|  3 | 小王   | 西瓜      |
|  4 | 小王   | 冬瓜      |
+----+--------+-----------+

-- 查询销售员共卖了多少产品(根据name字段分组)
-- 先从sales_records表查询数据，再根据name字段进行分组，再对每组的数据进行统计
mysql> select name,count(commodity) from sales_records group by name;
+--------+------------------+
| name   | count(commodity) |
+--------+------------------+
| 小陈   |                1 |
| 小王   |                3 |
+--------+------------------+

-- 查询销售员共卖了多少牛奶(根据name字段分组)
-- 先从sales_records表查询数据，在处理商品为牛奶的数据，再根据name字段进行分组，再对每组的数据进行统计
mysql> select name,count(commodity) from sales_records where commodity="牛奶" group by name;
+--------+------------------+
| name   | count(commodity) |
+--------+------------------+
| 小陈   |                1 |
| 小王   |                1 |
+--------+------------------+

-- 查询销售员共卖了多少产品，小于2就不显示
-- 先从sales_records表查询数据，再根据name字段进行分组，在使用having筛选，再对每组的数据进行统计
mysql> select name,count(commodity) from sales_records where count(commodity)>1 group by name;	-- 错误示范(where后不能跟分组函数)
ERROR 1111 (HY000): Invalid use of group function
mysql> select name,count(commodity) from sales_records group by name having count(commodity)>1;	-- having后可以跟聚合函数
+--------+------------------+
| name   | count(commodity) |
+--------+------------------+
| 小王   |                3 |
+--------+------------------+

分页查询

语法

1. 索引从0开始
2. 不指定起始索引时，起始索引默认为0

select 字段1,字段2 from 表名 limit 起始索引,查询条数;

查询示例

-- users表数据
mysql> select * from users;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
|  3 | test     | 123654   | NULL     |
|  4 | user1    | abcdef   |          |
+----+----------+----------+----------+

-- 从索引0开始，查询两条数据(0~1)
mysql> select * from users limit 2;
+----+----------+----------+----------+
| id | username | password | email    |
+----+----------+----------+----------+
|  1 | admin    | 123456   | 1@qq.com |
|  2 | guest    | 654321   | 2@qq.com |
+----+----------+----------+----------+

-- 从索引2开始，查询两条数据(2~3)
mysql> select * from users limit 2,2;
+----+----------+----------+-------+
| id | username | password | email |
+----+----------+----------+-------+
|  3 | test     | 123654   | NULL  |
|  4 | user1    | abcdef   |       |
+----+----------+----------+-------+

-- 从索引0开始，每页查询两条数据
mysql> select * from users limit 0,2;	-- 第一页
mysql> select * from users limit 2,2;	-- 第二页
mysql> select * from users limit 4,2;	-- 第三页

联合查询

语法

1. union将多个查询的结果拼接在一起
2. union在进行合并接结果集时，要求两个结果集的字段数相同
3. 使用union比join效率高

-- 不包括重复数据
select 字段 from 表 union select 字段 from 表;

-- 包括重复数据
select 字段 from 表 union all select 字段 from 表;

查询示例

-- 创建实验数据
create table student(
	id int not null AUTO_INCREMENT,
	name varchar(20) not nulL,
  PRIMARY KEY (id)
);
create table teacher(
	id int not null AUTO_INCREMENT,
	name varchar(20) not null,
  PRIMARY KEY (id)
);
insert into student(name) values('张三'),('李四'),('王五');
insert into teacher(name) values('张老师'),('李老师'),('王老师'),('赵老师');


-- 表数据
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王五   |
+----+--------+

mysql> select * from teacher;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张老师    |
|  2 | 李老师    |
|  3 | 王老师    |
|  4 | 赵老师    |
+----+-----------+

-- 联合查询结果(将student表与teacher表的查询结果放一起)
mysql> select name from student union select name from teacher;
+-----------+
| name      |
+-----------+
| 张三      |
| 李四      |
| 王五      |
| 张老师    |
| 李老师    |
| 王老师    |
| 赵老师    |
+-----------+

多表连接查询

概念

多表连接

1. 内连接获取多表的重合部分
2. 左连接获取左表全部，右表与左表重合部分
3. 右连接获取右表全部，左右与右表重合部分
4. 任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法

连表方式	描述
inner join	内连接，获取两个表中字段匹配关系的记录，可以省略inner关键词
left join	左连接，获取左表的所有记录，即使右表没有对应匹配的记录
right join	右连接，获取右表的所有记录，即使左表没有对应匹配的记录

笛卡尔积现象

1. 当两张表进行连接查询，没有任何条件限制的时候，最终查询到的结果条数为两张表条数的乘积，该现象被称为笛卡尔积现象
2. 在连接时加上条件，满足条件的记录会被筛选出来，就可以避免笛卡尔积现象

创建数据

-- 学生表
drop table if extsis student;
create table student(
	stu_id int(10) not null primary key,	-- 学号
	stu_name varchar(20) not null,		-- 姓名
	stu_sex int(10) not null,		-- 性别: 0女1男
	stu_age int(10) not null		-- 年龄
);

-- 课程表
drop table if extsis class;
create table class(
	class_id int(10) not null primary key,		-- 课程id
	class_name varchar(20) not null		-- 课程名
);

-- 选课表
drop table if extsis class_order;
create table class_order(
	stu_id int(10) not null primary key,		-- 学号
	class_id int(10) not null,		-- 课程id
	score int(10) not null		-- 分数
);


-- 插入学生信息
insert into student values
(1001,"张三",1,18),
(1002,"李四",0,18),
(1003,"王五",1,19),
(1004,"老六",1,19);

-- 插入课程信息
insert into class values
(2001,"语文"),
(2002,"数学"),
(2003,"英语"),
(2004,"体育");

-- 插入选课信息
insert into class_order values
(1001,2001,90),
(1002,2003,91),
(1003,2002,92);


-- 查询
select * from student;
select * from class;
select * from class_order;

内连接

1. 得到左表与右表的共同信息

语法

-- 两个表
select 表.字段,表.字段 from 左表 join 右表 on 条件;

-- 三个表
select 表.字段,表.字段 from (左表 join 右表 on 条件) join 右表2 on (条件);

两表查询

1. 将student表别名设置为s，class_order表别名设置为co
2. 将s表的stu_id字段与co表的stu_id字段关联
3. 查询的结果为两表的共同信息，s表的stu_id字段与co表的stu_id字段没有同时拥有1004，所以没有1004行结果

-- 查询左表、右表的共同信息
mysql> select * from student s join class_order co on s.stu_id=co.stu_id;
+--------+----------+---------+---------+--------+----------+-------+
| stu_id | stu_name | stu_sex | stu_age | stu_id | class_id | score |
+--------+----------+---------+---------+--------+----------+-------+
|   1001 | 张三     |       1 |      18 |   1001 |     2001 |    90 |
|   1002 | 李四     |       0 |      18 |   1002 |     2003 |    91 |
|   1003 | 王五     |       1 |      19 |   1003 |     2002 |    92 |
+--------+----------+---------+---------+--------+----------+-------+

-- 查询指定字段
mysql> select s.stu_name,co.score from student s join class_order co on s.stu_id=co.stu_id;
+----------+-------+
| stu_name | score |
+----------+-------+
| 张三     |    90 |
| 李四     |    91 |
| 王五     |    92 |
+----------+-------+

三表查询

1. 查询学生姓名、科目、成绩
2. 第一次内查询将student表与class_order表通过stu_id字段关联
3. 将得到的查询结果再与class表的class_id关联起来

-- 第一次内连接查询结果
mysql> select * from student s join class_order co on s.stu_id=co.stu_id;
+--------+----------+---------+---------+--------+----------+-------+
| stu_id | stu_name | stu_sex | stu_age | stu_id | class_id | score |
+--------+----------+---------+---------+--------+----------+-------+
|   1001 | 张三     |       1 |      18 |   1001 |     2001 |    90 |
|   1002 | 李四     |       0 |      18 |   1002 |     2003 |    91 |
|   1003 | 王五     |       1 |      19 |   1003 |     2002 |    92 |
+--------+----------+---------+---------+--------+----------+-------+

-- class表内容(右表)
mysql> select * from class;
+----------+------------+
| class_id | class_name |
+----------+------------+
|     2001 | 语文       |
|     2002 | 数学       |
|     2003 | 英语       |
|     2004 | 体育       |
+----------+------------+

-- 第二次内连接(第一次内连接的结果与class表再进行内连接)
mysql> select s.stu_name 姓名,c.class_name 科目,co.score from (student s join class_order co on s.stu_id=co.stu_id) join class c on c.class_id=co.class_id;
+--------+--------+-------+
| 姓名   | 科目   | score |
+--------+--------+-------+
| 张三   | 语文   |    90 |
| 李四   | 英语   |    91 |
| 王五   | 数学   |    92 |
+--------+--------+-------+

左连接

1. 得到左表的所有信息与右表的共同信息

语法

-- 两个表
select 表.字段,表.字段 from 左表 left join 右表 on 条件;

-- 三个表
select 表.字段,表.字段 from (左表 left join 右表 on 条件) left join 右表2 on (条件);

两表查询

1. 左表student设置别名为s，右表class_order设置别名为co
2. 左表与右表的共同字段为stu_id，所以条件指定s.stu_id=co.stu_id
3. 左查询结果为左表的全部字段，右表与左表共同信息字段
4. 右表没有数据时查询结果为null

-- 查询左表所有字段，右表与左表共同信息
mysql> SELECT * FROM student as s left join class_order as co on s.stu_id=co.stu_id;
+--------+----------+---------+---------+--------+----------+-------+
| stu_id | stu_name | stu_sex | stu_age | stu_id | class_id | score |
+--------+----------+---------+---------+--------+----------+-------+
|   1001 | 张三     |       1 |      18 |   1001 |     2001 |    90 |
|   1002 | 李四     |       0 |      18 |   1002 |     2003 |    91 |
|   1003 | 王五     |       1 |      19 |   1003 |     2002 |    92 |
|   1004 | 老六     |       1 |      19 |   NULL |     NULL |  NULL |
+--------+----------+---------+---------+--------+----------+-------+

-- 查询指定字段
mysql> SELECT s.stu_name,co.score FROM student as s left join class_order as co on s.stu_id=co.stu_id;
+----------+-------+
| stu_name | score |
+----------+-------+
| 张三     |    90 |
| 李四     |    91 |
| 王五     |    92 |
| 老六     |  NULL |
+----------+-------+

-- 指定字段别名且去除空值
mysql> SELECT s.stu_name as 姓名,co.score as 成绩 FROM student as s left join class_order as co on s.stu_id=co.stu_id where co.score is not null;
+--------+--------+
| 姓名   | 成绩   |
+--------+--------+
| 张三   |     90 |
| 李四   |     91 |
| 王五   |     92 |
+--------+--------+

三表查询

1. 查询学生姓名、科目、成绩
2. 第一个左查询将左表student与右表class_order通过stu_id关联起来
3. 第二个左查询将第一个左查询的结果作为左表，与右表class通过class_id关联起来

-- 第一次左查询结果(通过stu_id关联)
mysql> SELECT * FROM student as s left join class_order as co on s.stu_id=co.stu_id;
+--------+----------+---------+---------+--------+----------+-------+
| stu_id | stu_name | stu_sex | stu_age | stu_id | class_id | score |
+--------+----------+---------+---------+--------+----------+-------+
|   1001 | 张三     |       1 |      18 |   1001 |     2001 |    90 |
|   1002 | 李四     |       0 |      18 |   1002 |     2003 |    91 |
|   1003 | 王五     |       1 |      19 |   1003 |     2002 |    92 |
|   1004 | 老六     |       1 |      19 |   NULL |     NULL |  NULL |
+--------+----------+---------+---------+--------+----------+-------+

-- class表内容(右表)
mysql> select * from class;
+----------+------------+
| class_id | class_name |
+----------+------------+
|     2001 | 语文       |
|     2002 | 数学       |
|     2003 | 英语       |
|     2004 | 体育       |
+----------+------------+

-- 第二次左查询(三表查询)，将第一次的查询结果与class表做左查询
mysql> select s.stu_name 姓名,c.class_name 科目,co.score 成绩
    from (student s left join class_order co on s.stu_id=co.stu_id)
    left join class c on c.class_id=co.class_id;
+--------+--------+--------+
| 姓名   | 科目   | 成绩   |
+--------+--------+--------+
| 张三   | 语文   |     90 |
| 李四   | 英语   |     91 |
| 王五   | 数学   |     92 |
| 老六   | NULL   |   NULL |
+--------+--------+--------+

右连接

1. 与左连接相反，得到右表的所有信息与左表的共同信息

语法

-- 两个表
select 表.字段,表.字段 from 左表 right join 右表 on 条件;

-- 三个表
select 表.字段,表.字段 from (左表 right join 右表 on 条件) right join 右表2 on (条件);

两表查询

1. 左表class_order设置别名为co，右表class设置别名为c
2. 左表与右表的共同字段为class_id，所以条件指定co.class_id=c.class_id
3. 右查询结果为右表的全部字段，左表与右表共同信息字段
4. 左表没有数据时查询结果为null

-- 查询右表所有字段，左表与右表共同信息
mysql> select * from class_order co right join class c on co.class_id=c.class_id;
+--------+----------+-------+----------+------------+
| stu_id | class_id | score | class_id | class_name |
+--------+----------+-------+----------+------------+
|   1001 |     2001 |    90 |     2001 | 语文       |
|   1002 |     2003 |    91 |     2003 | 英语       |
|   1003 |     2002 |    92 |     2002 | 数学       |
|   NULL |     NULL |  NULL |     2004 | 体育       |
+--------+----------+-------+----------+------------+

-- 查询指定字段(体育没有人报)
mysql> select c.class_name 科目,co.stu_id 学号 from class_order co right join class c on co.class_id=c.class_id;
+--------+--------+
| 科目   | 学号   |
+--------+--------+
| 语文   |   1001 |
| 英语   |   1002 |
| 数学   |   1003 |
| 体育   |   NULL |
+--------+--------+

三表查询

1. 查询学生姓名、科目、成绩
2. 第一个右查询将左表class_order与右表class通过class_id关联起来
3. 第二个右查询将第一个右查询的结果作为左表，与右表student通过stu_id关联起来

-- 第一次右查询结果
mysql> select c.class_name 科目,co.score 成绩 from class_order co right join class c on co.class_id=c.class_id;
+--------+--------+
| 科目   | 成绩   |
+--------+--------+
| 语文   |     90 |
| 英语   |     91 |
| 数学   |     92 |
| 体育   |   NULL |
+--------+--------+

-- 第二次右查询(三表查询)，将第一次的查询结果与student表做右查询
mysql> select s.stu_name 姓名,c.class_name 科目,co.score 成绩 from (class_order co right join class c on co.class_id=c.class_id) right join student s on s.stu_id=co.stu_id;
+--------+--------+--------+
| 姓名   | 科目   | 成绩   |
+--------+--------+--------+
| 张三   | 语文   |     90 |
| 李四   | 英语   |     91 |
| 王五   | 数学   |     92 |
| 老六   | NULL   |   NULL |
+--------+--------+--------+

子查询

概念

1. 子查询是指一个查询语句里面嵌套另一个查询语句内部的查询
2. 在执行查询语句时，会先执行子查询中的语句，再将返回结果作为外层查询的过滤条件

语法

-- where后的子查询
select 字段,字段 from 表 where 字段 in (子查询);
select 字段,字段 from 表 where 字段 = (子查询);
select 字段,字段 from 表 where exists (子查询);
select 字段,字段 from 表 where 字段 = any (子查询);
select 字段,字段 from 表 where 字段 = all (子查询);

-- from后的子查询(把子查询的结果当作一个临时表，再与其他表进行多表查询)
select 表1.字段,表2.字段 from (子查询) as 表别名 join 表 on 条件;

查询示例

创建实验表

-- 学生表
create table student(
	stu_id int(10) not null primary key,	-- 学号
	stu_name varchar(20) not null,		-- 姓名
	stu_sex int(10) not null,		-- 性别: 0女1男
	stu_age int(10) not null		-- 年龄
);


-- 课程表
create table class(
	stu_id int(10) not null,		-- 学号
	class_name varchar(20) not null,		-- 课程名
	score int(10) not null		-- 成绩
);

-- 插入学生表数据
insert into student values
(1001,"张三",1,18),
(1002,"李四",0,18),
(1003,"王五",1,19),
(1004,"老六",1,19);

-- 插入课程表数据
insert into class values
(1001,"语文",90),
(1002,"语文",92),
(1003,"语文",83),
(1001,"英语",95),
(1002,"英语",92),
(1003,"英语",85),
(1001,"数学",83),
(1002,"数学",92),
(1003,"数学",86);

带比较符的子查询

-- 查询谁没有成绩
mysql> select stu_name from student where stu_id not in (select stu_id from class);
+----------+
| stu_name |
+----------+
| 老六     |
+----------+

-- 查询语文成绩大于90的
mysql> select stu_name from student where stu_id = (select stu_id from class where class_name="语文" and score>90);
+----------+
| stu_name |
+----------+
| 李四     |
+----------+

-- 查询张三的英语成绩
mysql> select score from class where stu_id = (select stu_id from student where stu_name="张三" and class_name="英语");
+-------+
| score |
+-------+
|    95 |
+-------+

带EXISTS的子查询

1. ESISTS关键词后面的参数可以是任意的子查询，执行后有值返回TRUE，没值返回FALSE
2. 当返回值为TRUE时才会执行外层的查询

-- 如果张三有成绩就返回所有人的信息
mysql> select * from student where exists (select * from class where stu_id='1001');
+--------+----------+---------+---------+
| stu_id | stu_name | stu_sex | stu_age |
+--------+----------+---------+---------+
|   1001 | 张三     |       1 |      18 |
|   1002 | 李四     |       0 |      18 |
|   1003 | 王五     |       1 |      19 |
|   1004 | 老六     |       1 |      19 |
+--------+----------+---------+---------+

-- 如果张三没成绩就返回所有人的信息
mysql> select * from student where not exists (select * from class where stu_id='1001');
Empty set (0.00 sec)

带ANY的子查询

1. ANY表示满足其中一个条件就返回一个结果作为外层查询条件

-- 查询
select stu_name from student where stu_id > any (select stu_id from class);

带ALL的子查询

约束

约束

约束	描述
primary key	主键约束
not null	非空约束
unique	唯一约束
foreign key	外键约束

主键约束

1. 非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识

创建表时添加主键约束

-- 语法
create table 表名(
	-- 字段 数据类型 primary key;
    id int primary key,
    name varchar(20)
);


-- 创建test表时指定id字段为主键，使用desc查询表信息时可以看到id行的Key有PRI
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除主键

-- 语法
alter table 表名 drop primary key;


-- 删除test表的主键，使用desc查询表信息时可以看到id行的Key为空
mysql> alter table test drop primary key;
Query OK, 0 rows affected (0.09 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

创建完成表后指定主键

-- 语法
alter table 表名 modify 字段 数据类型 primary key;

-- 指定test表的id字段为主键，使用desc查询表信息时可以看到id行的Key有PRI
mysql> alter table test modify id int primary key;
Query OK, 0 rows affected (0.06 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

自增长

1. 如果某字段为数值类型时，可以使用auto_increment指定该列的值自动增长

-- 创建表时指定某字段数值为自增长
create table 表名(
	id int primary key auto_increment,	-- 主键且自增长
	name varchar(20)
);

-- 表创建后指定某字段数值为自增长
alter table 表名 modify 字段 数据类型 auto_increment;

-- 删除自增长
alter table 表名 modify 字段 int;

非空约束

1. 值不能为null值，但可以为空格
2. 创建完成表后添加非空字段时，该字段下的数据不能为null值

创建表时添加非空约束

-- 语法
create table 表名(
    -- 字段 数据类型 not null,
	id int not null,
	name varchar(20)
);


-- 示例: Null列值为NO时表示该字段不能为NULL
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into test(id) values(2);	-- 只插入id时，正常
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(name) values('c');	-- 只插入name时，异常
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into test(id,name) values(,'a');

删除非空约束

-- 语法
alter table 表名 modify 字段 数据类型;

-- 示例: 删除test表的id字段非空约束时，只插入name字段不会异常
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into test(name) values('c');
Query OK, 1 row affected (0.00 sec)

创建完表后添加非空约束

-- 语法
delete from 表名 where 字段 is null;		-- 删除值为null的行
alter table 表名 modify 字段名 数据类型 not null;	-- 添加非空约束

唯一约束

1. 值不能重复，但可以有多个null值

创建表时添加唯一约束

-- 语法
create table 表名(
    -- 字段 数据类型 unique,
	id int,
	name varchar(20) unique
);


-- 示例: name字段不允许出现重复值
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into test values(1,"a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2,"b");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3,"b");	-- 重复值不允许插入
ERROR 1062 (23000): Duplicate entry 'b' for key 'name'

删除唯一约束

-- 语法
alter table 表名 drop index 字段;

-- 示例
mysql> alter table test drop index name;
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

创建表后添加唯一约束

-- 语法
alter table 表名 modify 字段名 数据类型 unique;

-- 示例
mysql> alter table test modify name varchar(20) unique;
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

外键约束

介绍

1. 外键: 外键是表的一个特殊字段，被参照的表是主表，外键所在的字段的表为子表
2. 原则: 外键的原则是依赖于数据库中已存在的表的主键
3. 作用: 外键作用是建立子表与父表的关联关系，父表中对记录做出操作时，子表对应的信息也应有相应的改变，从而保持数据的一致性与完整性

注意事项

1. 父表和子表必须使用相同的存储引擎，并且禁止使用临时表
2. 数据表的存储引擎只能是InnoDB
3. 外键列和参照列必须具有相似的数据类型。其中数字长度或是否有符号必须相同，而字符的长度可以不同
4. 外键列和参照列必须创建索引，如果外键列不存在索引时，MySQL将自动创建索引
5. 子表外键引用父表的某个字段，被引用的字段不一定要为主键，但一定要具有唯一性(unique约束)

创建删除顺序

1. 创建表: 先创建父表再创建子表
2. 删除表: 先删除子表再删除父表
3. 删除数据: 先删除子表再删父表
4. 插入数据: 先插父表再插入子表

外键约束

关键词	描述
CASCADE	父表删除或更新，子表也会删除或更新匹配的行
SET NULL	父表删除或更新，子表的外键值会设置为NULL (子表字段不能指定not null)
RESTRICT	拒绝对父表的删除或更新

语法

-- 创建表时添加外键
create table 表名(
	外键 数据类型,
	constraint 外键名 foreign key(外键字段名) references 主表名(主表字段名)
)ENGINE=INNODB;

-- 删除外键
alter table 表名 drop foreign key 外键名;

-- 创建表后添加外键
alter table 表名 add constraint 外键名 foreign key(外键字段名) references 主表名(主表列名称)

创建表时指定外键

-- 创建数据表
drop table if exists student;
drop table if exists class;
create table class(
	c_id int primary key,
	c_name varchar(20)
)ENGINE=INNODB;
create table student(
	s_id int primary key auto_increment,
	s_name varchar(20),
	c_id int,
	foreign key(c_id) REFERENCES class(c_id)	-- 指定外键
)ENGINE=INNODB;

-- 插入父表class数据
insert into class values(1001,"三年二班");
insert into class values(1002,"三年三班");

-- 插入子表student数据
insert into student(s_name,c_id) values("张三",1001),("李四",1001),("王五",1002),("老六",1002);

-- 查看表数据
mysql> select * from class;
+------+--------------+
| c_id | c_name       |
+------+--------------+
| 1001 | 三年二班     |
| 1002 | 三年三班     |
+------+--------------+

mysql> select * from student;
+------+--------+------+
| s_id | s_name | c_id |
+------+--------+------+
|    1 | 张三   | 1001 |
|    2 | 李四   | 1001 |
|    3 | 王五   | 1002 |
|    4 | 老六   | 1002 |
+------+--------+------+

使用外键约束后，想插入或修改外键约束字段的内容时，修改或插入的值只能来源于父表class的c_id字段的内容，这就保证了数据的安全性与统一性

-- 值1002在父表的c_id字段中，允许添加或更新
mysql> insert into student(s_name,c_id) values("赵七",1002);
Query OK, 1 row affected (0.91 sec)

-- 值1003不在父表的c_id字段中，不允许添加或更新
mysql> insert into student(s_name,c_id) values("老八",1003);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`new_sql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`c_id`))

如果还是要插入该字段的内容则需要在父表中先插入对应数据，再插入子表数据

-- 插入父表数据
mysql> insert into class values(1003,"二年三班");
Query OK, 1 row affected (0.40 sec)

-- 插入子表数据
mysql> insert into student(s_name,c_id) values("老八",1003);
Query OK, 1 row affected (0.03 sec)

-- 查询子表数据
mysql> select * from student;
+------+--------+------+
| s_id | s_name | c_id |
+------+--------+------+
|    1 | 张三   | 1001 |
|    2 | 李四   | 1001 |
|    3 | 王五   | 1002 |
|    4 | 老六   | 1002 |
|    5 | 赵七   | 1002 |
|    7 | 老八   | 1003 |
+------+--------+------+

-- 多表查询
mysql> select s.s_name,c.c_name from student s join class c on s.c_id=c.c_id;
+--------+--------------+
| s_name | c_name       |
+--------+--------------+
| 张三   | 三年二班     |
| 李四   | 三年二班     |
| 王五   | 三年三班     |
| 老六   | 三年三班     |
| 赵七   | 三年三班     |
| 老八   | 二年三班     |
+--------+--------------+

删除父表数据时需要先删除子表外键对应的数据

-- 直接删除父表数据，失败
mysql> delete from class where c_name="二年三班";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`new_sql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id                   `) REFERENCES `class` (`c_id`))

-- 先删除子表中与父表要删除的对应信息的内容，再删除父表信息
mysql> delete from student where c_id=1003;
Query OK, 1 row affected (0.13 sec)

mysql> delete from class where c_name="二年三班";
Query OK, 1 row affected (0.03 sec)

mysql> select s.s_name,c.c_name from student s join class c on s.c_id=c.c_id;
+--------+--------------+
| s_name | c_name       |
+--------+--------------+
| 张三   | 三年二班     |
| 李四   | 三年二班     |
| 王五   | 三年三班     |
| 老六   | 三年三班     |
| 赵七   | 三年三班     |
+--------+--------------+

常用函数

日期转换

创建实验表

drop tables if exists users;
create table users(
	id int,
	name varchar(10),
    birth date
);

str_to_date

将字符串类型转换为date类型，通常使用在insert插入数据

-- 语法
str_to_date('字符串日期','日期格式')

-- 日期格式
%Y    年(2022)
%y    年(22)
%m    月
%d    日
%h    时
%i    分
%s    秒

-- 示例
insert into users values(1,'张三',str_to_date('2022-01-01','%Y-%m-%d'));
insert into users values(2,'李四',str_to_date('22-01-01','%y-%m-%d'));
insert into users values(3,'王五','2022-01-01');		-- 如果格式为%Y-%m-%d时，可以不用转换
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    1 | 张三   | 2022-01-01 |
|    2 | 李四   | 2022-01-01 |
|    3 | 王五   | 2022-01-01 |
+------+--------+------------+

date_format

将date类型转换为字符串类型，通常使用在select查询日期

-- 语法
date_format(日期类型数据,'日期格式')

-- 原始默认格式 %Y-%m-%d
mysql> select * from users;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    1 | 张三   | 2022-01-01 |
|    2 | 李四   | 2022-01-01 |
|    3 | 王五   | 2022-01-01 |
+------+--------+------------+

-- 指定格式后
mysql> select id,name,date_format(birth,'%Y年%m月%d日') as birth from users;
+------+--------+-------------------+
| id   | name   | birth             |
+------+--------+-------------------+
|    1 | 张三   | 2022年01月01日    |
|    2 | 李四   | 2022年01月01日    |
|    3 | 王五   | 2022年01月01日    |
+------+--------+-------------------+

日期时间

创建实验表

drop tables if exists users;
create table users(
	id int,
	name varchar(10),
	create_time datetime
);

插入日期时间

-- 默认格式: %Y-%m-%d %h:%i:%s
insert into users values(1,'张三','2022-01-01 01:02:03');
+------+--------+---------------------+
| id   | name   | create_time         |
+------+--------+---------------------+
|    1 | 张三   | 2022-01-01 01:02:03 |
+------+--------+---------------------+

now

获取系统当前日期时间

-- 格式
%Y-%m-%d %h:%i:%s

-- 示例
mysql> insert into users values(2,'李四',now());
+------+--------+---------------------+
| id   | name   | create_time         |
+------+--------+---------------------+
|    1 | 张三   | 2022-01-01 01:02:03 |
|    2 | 李四   | 2022-03-15 20:32:02 |
+------+--------+---------------------+

current_time

获取当前系统时间

-- 格式
%h:%i:%s

-- 示例
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 20:35:44     |
+--------------+

current_date

获取当前系统日期

-- 格式
%Y-%m-%d

-- 示例
mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2022-03-15   |
+--------------+

unix_timestamp

将日期转换为时间戳

-- 语法
unix_timestamp(日期时间);

-- 示例
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1647347925 |
+-----------------------+

mysql> select unix_timestamp('2022-01-01');
+------------------------------+
| unix_timestamp('2022-01-01') |
+------------------------------+
|                   1640966400 |
+------------------------------+

mysql> select unix_timestamp('2022-01-01 01:02:03');
+---------------------------------------+
| unix_timestamp('2022-01-01 01:02:03') |
+---------------------------------------+
|                            1640970123 |
+---------------------------------------+

时间差

语法

-- 语法
timestampdiff(单位,起始时间,结束时间)

-- 单位
YEAR		年
MONTH		月
WEEK		周
DAY			日
HOUR		时
MINUTI		分
SECOND		秒
MICROSECOND	毫秒

示例

-- 查询2000-01-01到2022-01-01相差几年
select timestampdiff(year,'2000-01-01','2022-01-01') 年;
+------+
| 年   |
+------+
|   22 |
+------+

-- 查询2022-01-01到2022-06-01相差几月
select timestampdiff(month,'2022-01-01','2022-06-01') 月;
+------+
| 月   |
+------+
|    5 |
+------+

-- 查询2022-01-01到2022-06-01相差几日
select timestampdiff(day,'2022-01-01','2022-06-01') 日;
+------+
| 日   |
+------+
|  151 |
+------+

-- 查询2022-01-01 00:00:00到2022-01-01 06:30:30相差几小时
select timestampdiff(hour,'2022-01-01 00:00:00','2022-01-01 06:30:30') 时;
+------+
| 时   |
+------+
|    6 |
+------+

-- 查询2000-06-01年出生的现在几岁了(当前日期2022-03-18)
select timestampdiff(year,'2000-06-01',now()) 年;
+------+
| 年   |
+------+
|   21 |
+------+

事务

概念

概念

1. 一个事务就是一个完整的业务逻辑，是一个最小的工作单元
2. MySQL事务主要用于处理操作量大、复杂度高的数据
3. 事务只支持存储引擎为InnoDB的库或表
4. 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行要么全部不执行
5. 事务可以用来管理insert、update、delete语句(DML操作)
6. 事务在执行过程中每条DML操作都会记录到事务性活动的日志文件中
7. 提交事务会清空事务性活动的日志文件，将数据全部持久化到数据库表中，事务全部成功结束
8. 回滚事务将之前的DML操作全部撤销且清空事务性活动日志，事务全部失败结束
9. MySQL默认每执行一条DML语句则自动提交事务

事务的特征

1. 原子性: 事务时不可分割的最小操作单位，要么同时成功，要么同时失败
2. 持久性: 当事务提交或回滚后，数据库会持久化存储该数据
3. 隔离性: 多个事务之间互相独立
4. 一致性: 事务操作前后数据总量不变

提交与回滚

语法

-- 打开事务(关闭自动提交机制)
start transaction;

-- 提交事务
commit

-- 回滚事务
rollback

默认提交事务

MySQL默认每执行一条DML语句则会自动提交一次事务，所以回滚后还是提交后的数据

-- 创建数据表后插入数据
mysql> create table users(
    id int,
    name varchar(20)
    )ENNGINE=INNODB;
Query OK, 0 rows affected (1.72 sec)

mysql> insert into users values(1,"张三");
Query OK, 1 row affected (0.00 sec)
mysql> insert into users values(2,"李四");
Query OK, 1 row affected (0.00 sec)

-- 事务回滚前数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+ 
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

-- 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.10 sec)

-- 事务回滚后数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

开启事务(关闭自动提交)

当开启事务后，对数据进行增、删、改，在未提交数据前进行回滚，都能回到上一步

-- 开启事务(关闭自动提交)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 删除数据
mysql> delete from users where id=2;
Query OK, 1 row affected (0.00 sec)

-- 删除数据后的users表(未提交事务)
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.13 sec)

-- 数据回到删除之前(未提交事务之前)
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

对数据进行修改后提交事务，数据无法恢复

-- 再次删除数据
mysql> delete from users where id=2;
Query OK, 1 row affected (0.69 sec)

-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- 提交事务后数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

-- 事务回滚后数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

事务隔离

事务的四个隔离级别

1. 读未提交: 事务A可以读到事务B未提交的数据，该隔离级别存在脏读现象(读到脏数据)，该隔离级别一般都是理论上的，不常用
2. 读已提交: 事务A只能读到事务B提交后的数据，该隔离级别解决了脏读现象，但不可重复读取数据
3. 可重复读: 事务A开启后不管多久，在每一次事务A中读取到的数据都是一致的。即使事务B将数据修改并提交了，事务A读取到的数据还是没有发生改变。该隔离级别解决了不可重复读的问题，但可能出现幻影读(每次读到的数据都是幻象，不够真实)
4. 序列化读: 该隔离级别表示事务排队，不能并发(效率低)。但解决了上方的所有问题，每次读取到的数据都是最真实的

隔离	描述
read uncommitted	读未提交(最低隔离级别)
read committed	读已提交
repeatable read	可重复读(MySQL默认)
serializable	序列化/串行化(最高隔离级别)

设置事务隔离级别

-- 查看当前事务隔离级别
select @@tx_isolation;
select @@transaction_isolation;		-- MySQL8

-- 设置全局事务隔离级别
set global transaction isolation level 隔离级别

读未提交

1. 默认没有提交时事务A做修改但未提交，则事务B是读取不到事务A未提交的内容
2. 开启read uncommitted事务隔离时，事务A做修改但未提交，则事务B可以看到事务A未提交的内容

-- 窗口0 (设置全局事务隔离级别)
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

-- 查看当前全局事务隔离级别(如果未刷新就退出数据库重新登录)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

-- 窗口1 (事务A)
mysql> use new_sql;
Database changed
-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 写入数据前
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 写入数据
mysql> insert into users values(2,'李四');
Query OK, 1 row affected (0.00 sec)

-- 窗口2 (事务B)
mysql> use new_sql;
Database changed
-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 可以读取到事务A未提交的数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

读已提交

1. 当事务A修改数据后未提交，事务B看不到事务A未提交的值
2. 当事务A提交数据后，事务B才能看到事务A提交的值

-- 窗口0 (设置全局事务隔离级别)
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

-- 查看当前全局事务隔离级别(如果未刷新就退出数据库重新登录)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

-- 窗口1 (事务A)
mysql> use new_sql;
Database changed
-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 写入前
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 写入数据(未提交数据)
mysql> insert into users values(2,"李四");
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

-- 窗口2 (事务B)
mysql> use new_sql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 查询不到事务A未提交的数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 窗口1 (事务A)
-- 提交数据
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

-- 窗口2 (事务B)
-- 查看修改
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

可重复读

1. 事务A修改数据并提交后，事务B看到的数据还是事务A修改前的旧数据
2. 事务B退出重新登录后，看到的是事务A修改提交后的新数据
3. 再开启一个新的终端看到的也是事务A修改提交后的新数据

-- 窗口0 (设置全局事务隔离级别)
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

-- 查看当前全局事务隔离级别(如果未刷新就退出数据库重新登录)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

-- 窗口1 (事务A)
mysql> use new_Sql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 修改前数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

mysql> delete from users where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.14 sec)

-- 修改数据后并提交的数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 窗口2 (事务B)
mysql> use new_sql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 查到的还是事务A修改前的数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

-- 退出重新登录(结束事务B)
mysql> use new_sql;
Database changed
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

序列化读

1. 当事务A对users表进行修改且未提交时，事务B对users表进行查询或其他操作时都会卡在等待界面
2. 当事务A提交事务后，事务B才可以对事务A操作的表进行查询或修改

-- 窗口0 (设置全局事务隔离级别)
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

-- 查看当前全局事务隔离级别(如果未刷新就退出数据库重新登录)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+

-- 窗口1 (事务A)
mysql> use new_sql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 修改数据前
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+
2 rows in set (0.00 sec)

-- 修改数据后(未提交事务)
mysql> delete from users where id=2;
Query OK, 1 row affected (0.00 sec)

-- 窗口2 (事务B)
mysql> use new_sql;
Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 查询users表的所有字段数据(卡住无回显)
mysql> select * from users;

-- 窗口1 (事务A)
-- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

-- 窗口2 (事务B)
-- 当事务A提交事务后，事务B的查询语句正确返回数据
mysql> select * from users;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
+------+--------+

索引

概念

概念

1. 索引是在数据库表的字段上添加的，是为了提高查询效率而存在的一种机制
2. 一张表的一个字段可以添加一个索引，多个字段联合也可以添加索引
3. 索引相当于书的目录，是为了缩小扫描范围而存在的一种机制
4. 如果没有添加索引，MySQL会进行全扫描，效率较低。添加索引后通过索引定位到大概位置，再进行局域性扫描，效率较高
5. 任何数据库中主键上都会自动添加索引对象，在MySQL数据库表字段中有unique约束也会自动创建索引

索引类型

1. 单一索引: 在一个字段上添加索引
2. 复合索引: 在多个字段上添加索引
3. 主键索引: 在主键上添加索引
4. 唯一索引: 在unique约束字段添加索引，字段内容越唯一效率越高

实现原理

1. 在MySQL中索引是一个单独的对象，不同的存储引擎以不同的形式存在
2. 索引在MySQL中都是以一棵树的形式存在(自平衡二叉树B-Tree)
3. 索引字段的每行数据在硬盘上都有物理存储编号

-- users表，假设存储编号如下(存储编号存储在物理磁盘)
id(PK)       name      (存储编号)
111          张三       0x1111
102          李四       0x2222
90           王五       0x6666
115          老六       0x3333
99           杰克       0x4444
89           汤姆       0x7777

-- 使用索引
当查询语句为select * from users where id=99时，MySQL发现字段id上存在索引对象时，就会通过索引对象idIndex进行查找
将99与111对比，比111小的值就往左边走，比111大的值就往右边走，以此类推定位到111 -> 102 -> 99
再通过99得出物理编号0x4444，再通过物理编号定位到数据的位置，从而取得数据

-- 无索引时
当查询语句为select * from users where id=99时，MySQL会从id字段一个一个匹配下来
如111 -> 102 -> 90 -> 115 -> 99 定位到后取得数据，数据量庞大时匹配时间就会大大增加

何时添加索引

1. 数据量庞大(具体看硬件环境来定义数据量)
2. 索引字段经常出现在where后，以条件的形式存在(该字段经常被扫描)
3. 该字段很少有增、删、改操作，因为DML之后索引需要重新排序
4. 不要随意添加索引，因为索引需要维护，太多反而会降低系统性能，建议通过主键或unique约束的字段进行查询

创建删除索引

语法

-- 创建索引
create index 索引名 on 表名(字段名);

-- 创建复合索引
create index 索引名 on 表名(字段1,字段2);

-- 删除索引
drop index 索引名 on 表名;

-- 查看查询语句是否使用索引
explain 查询语句;

创建索引

1. 当type=ALL时表示全表扫描，rows=4表示扫描了4次才拿到数据
2. 当type=ref时表示使用索引，rows=1表示扫描了1次就拿到数据

-- 创建实验表
drop table if exists users;
create table users(
	id int(20) primary key,
	name varchar(20) not null,
	email varchar(20)
);
insert into users values(1,"张三","1@qq.com");
insert into users values(2,"李四","2@qq.com");
insert into users values(3,"王五","3@qq.com");
insert into users values(4,"老六","4@qq.com");

-- 未使用索引时 (type=ALL)
mysql> explain select * from users where name="老六";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.12 sec)

-- 使用索引 (type=ref)
mysql> create index users_name_index on users(name);
Query OK, 4 rows affected (1.81 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from users where name="老六";
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | users_name_index | users_name_index | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.10 sec)

删除索引

-- 删除字段索引(type=ALL)
mysql> drop index users_name_index on users;
Query OK, 4 rows affected (1.77 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select * from users where name="老六";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引的失效

失效原因

1. 使用模糊查询时尽量不要以%开始，否则索引会失效
2. 使用or时两边条件字段都要有索引，否则索引失效
3. 可以使用union来替换or进行查询，索引不会失效
4. 在while中索引字段参加了运算，索引失效
5. 在while中索引字段使用了函数，索引失效
6. 使用复合索引时未使用左侧列查找(最左原则)，索引失效

代码示例

-- 创建索引
mysql> create index users_name_index on users(name);
Query OK, 4 rows affected (1.81 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 模糊查询以%开始，并未使用到索引 (type=ALL)
mysql> explain select * from users where name like "%六";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- or两边字段需要都有索引(email没有)，并未使用到索引 (type=ALL)
mysql> explain select * from users where name="老六" or email="4@qq.com";
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | users_name_index | NULL | NULL    | NULL |    4 |    43.75 | Using where |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 索引字段使用了函数，索引失效(type=ALL)
mysql> explain select * from users where lower(name)="老六";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- 复合索引测试
mysql> drop index users_name_index on users;
Query OK, 4 rows affected (1.84 sec)
Records: 4  Duplicates: 0  Warnings: 0
-- 创建复合索引(左侧列=name)
mysql> create index users_name_email_index on users(name,email);
Query OK, 4 rows affected (0.14 sec)
Records: 4  Duplicates: 0  Warnings: 0
-- 使用左侧列作为条件时，索引生效(type=ref)
mysql> explain select * from users where name="老六";
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys          | key                    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | users_name_email_index | users_name_email_index | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
-- 使用右侧列作为条件时，索引失效(type=ALL)
mysql> explain select * from users where email="4@qq.com";
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

视图

概念

1. 站在不同的角度操作同一份数据
2. 对视图进行增删改查时，会导致原表的数据被操作(视图的特性)
3. 视图在开发中起到方便、简化开发、利于维护的作用
4. 对于需要经常使用且非常复杂的SQL语句，可以使用视图对象来简化开发
5. 操作视图就像操作表一样，视图存储在硬盘当中
6. 创建视图时对应的语句只能是查询语句，视图创建后可以对视图进行增删改查

使用视图

语法

-- 创建视图
create view 视图名 as 查询语句;

-- 删除视图对象
drop view 视图名;

代码示例

-- 原表数据
mysql> select * from users;
+----+--------+----------+
| id | name   | email    |
+----+--------+----------+
|  1 | 张三   | 1@qq.com |
|  2 | 李四   | 2@qq.com |
|  3 | 王五   | 3@qq.com |
|  4 | 老六   | 4@qq.com |
+----+--------+----------+

-- 创建视图
mysql> create view users_view as select * from users;
Query OK, 0 rows affected (1.85 sec)

-- 删除视图数据
mysql> delete from users_view where id=4;
Query OK, 1 row affected (1.68 sec)

-- 原表数据(原表数据被影响)
mysql> select * from users;
+----+--------+----------+
| id | name   | email    |
+----+--------+----------+
|  1 | 张三   | 1@qq.com |
|  2 | 李四   | 2@qq.com |
|  3 | 王五   | 3@qq.com |
+----+--------+----------+

数据库设计

概念

设计三范式

1. 任何一张表必须有主键，每一个字段原子性不可再分
2. 所有非主键字段完全依赖主键 (建立在第一范式基础之上)
3. 所有非主键字段直接依赖主键 (建立在第二范式基础之上)

按照三范式进行设计的数据库，可以避免表中数据冗余，浪费空间

总结

1. 数据库设计三段式是理论上的，实践与理论有偏差
2. 有时候会拿冗余换执行速度，因为表与表连接次数越多，效率越低
3. 空间足够可以不用考虑冗余，减少表与表的连接次数
4. 对于开发人员来说，sql语句的编写难度也会降低

第一范式

未满足第一范式的数据表

1. 该数据表没有主键
2. 该数据表联系方式字段还可以再分割

用户编号	用户姓名			联系方式
------------------------------------------------------
1001          张三        1@qq.com,132xxxxxxx0
1002          李四        2@qq.com,132xxxxxxx1
1001          王五        3@qq.com,132xxxxxxx2

修改后

1. 为用户编号创建主键
2. 将联系方式分割成多个字段(原子性不可再分割)

用户编号(pk)	用户姓名	邮箱		电话
------------------------------------------------------
1001          	张三      1@qq.com   132xxxxxxx0
1002          	李四      2@qq.com   132xxxxxxx1
1003          	王五      3@qq.com   132xxxxxxx2

第二范式

未满足第二范式的数据表

1. 学生编号与教师编号两个字段做复合主键
2. 但学生姓名依赖学生编号，教师姓名依赖教师编号，所以并没有完全依赖主键(部分依赖)
3. 部分依赖导致了数据冗余与空间浪费

-- 不满足第一范式
学生编号	学生姓名	教师编号	教师姓名
-----------------------------------------------------
1001		张三		  001		  王老师
1002		李四		  002		  赵老师
1003		王五		  001		  王老师
1001		张三		  002		  赵老师

-- 修改后满足第一范式，但不满足第二范式
学生编号+教师编号(pk)	学生姓名	教师姓名
--------------------------------------------------
1001	  001			张三		 王老师
1002	  002			李四		 赵老师
1003	  001			王五		 王老师
1001	  002			张三		 赵老师

修改后

1. 使用三张表来表示多对多关系(学生表、教师表、学生教师关系表)
2. 将编号与id字段设置为主键，将关系表的字段设置为外键连接到教师表与学生表

口诀: 多对多，三张表，关系表两个外键

-- 学生表
学生编号(pk)	学生姓名
-----------------------------
1001			张三
1002			李四
1003			王五

-- 教师表
教师编号(pk)	教师姓名
-----------------------------
001				王老师
002				赵老师

-- 学生教师关系表
id(pk)		学生编号(fk)		教师编号(fk)
---------------------------------------------
1			 1001				 001
2			 1002				 002
3			 1003				 001
4			 1001				 002

第三范式

未满足第三范式的数据表

1. 班级名称依赖班级编号，班级编号依赖学生编号，产生传递依赖所以不满足第三范式

-- 满足第二范式，但不满足第三范式
学生编号(pk)	学生姓名	班级编号	班级名称
----------------------------------------------------
1001			张三		   01		三年一班
1002			李四		   02		三年二班
1003			王五		   03		三年三班
1004			老六		   03		三年三班

修改后

1. 将数据表拆分为两个表(班级表、学生表)
2. 将学生表的班级编号外键到班级表的班级编号

口诀: 一对多，两张表，多的表加外键

-- 班级表
班级编号(pk)	班级名称
-------------------------------
01				三年一班
02				三年二班
03				三年三班

-- 学生表
学生编号(pk)	学生姓名	班级编号(fk)
-----------------------------------------
1001			张三			01
1002			李四			02
1003			王五			03
1004			老六			03

权限系统

注意点

1. 下方数据库版本为5.7，其他版本语法可能不一样！！！
2. 对用户进行操作时最好使用root用户
3. 修改用户权限时一定要刷新系统表
4. 修改用户权限后要退出重新登录再测试权限

创建用户

语法

-- 主机为%时表示允许任何主机登录
-- 主机位localhost时表示值允许本地登录

create user '用户名'@'主机' identified by '密码';

示例

• 创建用户名为test密码为123456的用户，该用户允许从任何主机登录

mysql> create user 'test'@'%' identified by '123456';
Query OK, 0 rows affected (0.44 sec)

mysql> quit
Bye

C:\Users\king>mysql -u test -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

授权用户

语法

1. 如果允许用户对指定数据库的所有表进行操作就写 数据库.*
2. 如果允许用户对所有数据库的所有表进行操作就写 *.*
3. 修改权限后要退出重新登录才会生效

-- 查看用户权限
show grants for 用户名;

-- 创建用户且同时授权
grant 权限 on 数据库名.表名 to '用户名'@'主机' identified by '密码';
flush privileges; 	-- 刷新系统权限表(必须！)

-- 创建用户后授权
grant 权限 on 数据库名.表名 to '用户名'@'主机' with grant option;
flush privileges;

-- 权限
all privileges		全部权限

alter	修改表
create	创建库或表
delete	删除表
drop	删除库或表
index	创建删除索引
select	查询表数据
update	更新表数据
insert	插入数据
usage	只允许登录
......

示例

• 创建user1用户，密码为123456，允许所有主机登录，允许该用户对所有数据库进行任何操作

grant all privileges on *.* to 'user1'@'%' identified by '123456';
flush privileges;

• 将test用户权限改为对所有数据库仅有select、update权限

grant select,update on *.* to 'test'@'%' with grant option;
flush privileges;

权限回收

语法

-- 权限同授权时的权限
revoke 权限 on 数据库名.表名 to '用户名'@'主机';
flush privileges;

示例

• 回收user1用户的全部权限

revoke all privileges on *.* from 'user1'@'%';
flush privileges;

• 回收test用户对test数据库的update权限

-- 就取走update权限，其他还在
revoke update on test.* from 'test'@'%';
flush privileges;

修改密码

语法

set password for '用户名'@'主机' = password('新密码');

示例

• 将test用户密码改为12321

mysql> set password for 'test'@'%' = PASSWORD('123321');
Query OK, 0 rows affected, 1 warning (0.00 sec)

删除用户

语法

drop user '用户名'@'主机'

示例

• 删除test用户

-- 删除test用户
mysql> drop user 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

-- 查询所有用户
mysql> select user from mysql.user;
+---------------+
| user          |
+---------------+
| root          |
| test2         |
| user1         |
| mysql.session |
| mysql.sys     |
+---------------+

导入导出

导出数据库

语法

# 导出整个数据库
mysqldump -u 账号 -p 数据库名 > 导出文件名

# 导出数据库的一个表
mysqldump -u 账号 -p 数据库名 表名 > 导出文件名

# 导出一个数据的结构(不包含数据)
mysqldump -u 账号 -p -d 数据库名 > 导出文件名

创建实验数据库

create database output_input;
use output_input;
drop table if exists users;
create table users(
	uid int(10) primary key,
	name varchar(10)
);
create table info(
	id int(10) primary key,
	username varchar(10),
	password varchar(10)
);

insert into users values(1,'u1'),(2,'u2'),(3,'u3');
insert into info values(1,'admin','123'),(2,'test','123'),(3,'guest','123');

导出整个数据库

# 包含数据，导出到当前工作目录下
C:\Users\king>mysqldump -u root -p output_input > 111.sql
Enter password: ****	# 输入密码

导出数据库的一个表

# 包含数据，导出到当前工作目录下
C:\Users\king>mysqldump -u root -p output_input users > 222.sql
Enter password: ****

导入数据库结构

# 不包含数据，导出到当前工作目录下
C:\Users\king>mysqldump -u root -p -d output_input > 333.sql
Enter password: ****

导入数据库

语法

# 要提交创建数据库
mysql -u 用户名 -p 数据库名 < 数据库文件

-- 要提交创建数据库，使用source指定sql文件
use 数据库名
source 数据库文件

示例

-- 使用方法1导入
mysql> create database output_input;
Query OK, 1 row affected (0.00 sec)

C:\Users\king>mysql -u root -p output_input < 111.sql
Enter password: ****

-- 使用方法2导入
mysql> create database output_input;
Query OK, 1 row affected (0.00 sec)
mysql> use output_input
Database changed
mysql> source C:\Users\king\111.sql