MySQL笔记

SQL命令的使用规则

- SQL命令不区分字母大小写（密码、变量值除外）
- 每条SQL·命令以；结束
- 默认命令不支持Tab键自动补齐
- \c 终止SQL命令

管理数据库使用SQL（结构化查询语言）

• DDL 数据定义语言 如：create、alter、drop
• DML 数据操作语言 如：insert update delete
• DCL 数据控制语言 如：grant、revoke
• **DTL **数据事物语言 如：commit、rollback、savepoint

连接方式

1. 命令行
   使用mysql命令连接
   - mysql -u用户名 -p密码 [数据库名]
- quit或exit退出

2. web页面

   - phpmyadmin

3. 安装图形页面软件

   - SQL yang

4. 编写脚本（java python，php）

数据类型

字符类型

char

定长：char(字符个数)

• 最大字符个数255
• 不够指定字符个数时在右边用空格补全
• 字符个数超出时，无法写入数据

varchar

变长：varchar(字符个数)，最多可以存65532个字符

• 根据数据实际大小分配存储空间
• 字符个数超出时无法写入数据

text/blob

大文本类型

• 字符数大于65535存储时使用

数值类型

整数类型

浮点型

日期时间类型

日期：date

范围: 0001-01-01~9999-12-31

格式: yyyymmdd(如：19491001)

年：year

范围：901~2155

格式：yyyy(如：1998)

时间：time

格式：HHMMSS(如:221828)

日期时间：datetime（存储范围大）

范围:1000-01-01 00:00:00~9999-12-31 23:59:59

格式：yyyymmddhhmmss

日期时间：timestamp

范围：1970-01-01 00:00:00~2038-01-19 00:00:00

格式：yyyymmddhhmmss

类型关键字注意

• 关于日期时间字段

  • 当未给timestamp字段赋值时，自动以当前系统时间赋值，而datetime值为NULL（空）

• year类型

  • 要求使用4位数赋值
  • 当使用2位数赋值时:01~99
  • 01-69视为 2001~2069
  • 7099视为 19701999

枚举类型

字段的值必须在指定的范围内选择。

enum 单选

• 格式：字段名 enum(值1，值2,值N)
• 仅能在列表里选择一个值

set多选

• 格式：字段名 set(值1，值2，值N)
• 在列表里选择一个或多个值

数据库基本管理

相关杂命令

记录类似文件中的行
- select * from 库名.表名;				   #查看表记录
- insert into 库名.表名 values(值列表)		#插入表记录
# 此命令插入对应的是表里面有多少列都需要有多少值
# 如何指定字段添加插入多条数据
- insert into 表名(id,name.dizhi) values(id,name,dizhi),(id,name,dizhi);
# 指定插入对应字段，并批量插入多行数据
- delete from 表名 where id=102;
# 删除指定ID为102的行
- delete from 表名 where id=102 and name='ccc';
# 删除指定ID为102并且name为ccc的行，如果有一个不符合将不会执行删除
- delete from 表名 where id =102 or name='ccc';
# 删除过户指定ID为102或者是name等于ccc的行数据只要满足一个就会被删除
- delete from 表名;
# 删除所有数据
- update t1 set name='ccc';
# 更新表数据
# 将t1表里面所有name的字段改为ccc
- update t1 set name='ccc',dizhi='beijing'  where id=102;
# 指定id为102的行把name改为ccc dizhi改为beijing
- truncate table 表名;					#清空表数据
- drop table 表名;						#删除表

DDL-库管理操作

DDL语言用于对数据库和表结构的字段

select use();                             #显示连接的用户  
use   库名;							     #切换库
select database();                         #获取当前所在的库
show databases;                            #查看所有数据库
create database if not exists 库名;        #建新库，如果已存在不会报错
show tables;                              #显示已有的表
drop database if exists 库名;             #删除库，如果不存在不会报错

DDL-表结构修改

添加一个字段

alter table 表名 add 字段名 类型(长度)[comment 注释][约束]; #关键字段add表示添加的意思

在创建时如果需要调整顺序可以使用

alter table 表名 add 字段名 列名 first; # 添加到表第一列

alter table 表名 add 字段 列名 after 列名2; # 添加到列名2的后面

first 第一

after 在......之后

修改列顺序

alter table 表明 modify column 列名1 数据类型 first|after 列名2;

格式说明： 列名1为所要调整顺序的列名，first表示将列调整到表的第一个位置，after列名2表示将列调整到列名2之后。

修改数据类型

alter table 表名 modify 字段名 新数据类型（长度）; #关键词modify

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型 长度 #关键词change

删除字段

alter table 表名 drop 字段名; #关键词drop

修改表名字

alter table 表名 rename to 新表名; #关键词 rename to

删除表

drop table 表名; 

删除表,并重新创建该表

truncate table 表名; 

DML-添加表数据

数据操作语言，用来对数据库中表的数据记录进行增删改操作

• 给指定字段添加数据

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,.....);

注意

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
• 字符串和日期类型数据应该包含到引号中
• 插入数据的大小，应该是在字段的规定范围内

DML-修改表数据

update 表名 set 字段名1=值1,字段名2=值2,......[where条件] #关键字update和set

注意

• 修改语句的条件可以有，也可以没有，如果没有则会修改整张表的所有数据。
• 如需修改多个字段可以用逗号隔开

DML-删除表数据

delete from 表名 [where 条件] #关键字delete和from

 注意

• delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete语句不能删除某一个字段的值(可以使用update)

DQL-常用查询

数据查询语言，用来查询数据库中的表的记录 查询关键字：select

字段可以使用数学表达式

select
	字段列表
from
	表名列表
where
	条件列表
group by 
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

1.查询指定字段

select 字段1,字段2 from 表名; #查询指定字段

1.1.查询全部字段

select *from 表名;

2.设置别名

select 字段1 as 别名1 from 表名; #取别名时as可以省略 别名是中文用单引号阔起来

演示：

mysql> select name '名字' from  t2; # 查询name字段设置别名:名字
+--------+
| 名字   |
+--------+
| yyt    |
| hlk    |
+--------+
2 rows in set (0.00 sec)

3.去除重复记录

select distinct 字段列表 from 表名;

DQL-条件查询

select 字段列表 from 表名 where 条件列表;

比较运算符	功能
>	大于
>=	大于等于
>	小于
<=	小于等于
=	等于
<>或!=	不等于
between…and…	在某个范围内含(最小,最大值)
in(……)	在in后面的列表里的值多选一
like 占位符	模糊匹配（_匹配单个字符,%匹配任意个字符）
is null	是null
is not null	不是null

逻辑运算符	功能
and或&&	并且（多个条件同时成立）
or或||	或者（多个条件任意一个成立）
not或！	非，不是

查询age（年龄）大于等于19的数据

mysql> select *from t2 where age>=19; # 注意这里的>=其实是分开的两个字符，因为本笔记软件的显示异常
+------+------+------------+
| name | age  | sfz        |
+------+------+------------+
| hlk  |   19 | 1234567890 |
| wgh  |   19 |        123 |
| dzy  |   19 |      12345 |
| xxx  |   20 |     123456 |
+------+------+------------+
4 rows in set (0.00 sec)

查询age年龄不等于18并且sfz等于123的数据

mysql> select *from t2 where age!=18 && sfz=123; # 这里&&表示的等同于and
+------+------+------+
| name | age  | sfz  |
+------+------+------+
| wgh  |   19 |  123 |
+------+------+------+
1 row in set (0.00 sec)

查询年龄等于19或者是年龄等于20

mysql> select *from t2 where age=19 or age=20;	#这里查出的年龄等于19或者是年龄等于20的所有人
+------+------+------------+
| name | age  | sfz        |
+------+------+------------+
| hlk  |   19 | 1234567890 |
| wgh  |   19 |        123 |
| dzy  |   19 |      12345 |
| xxx  |   20 |     123456 |
+------+------+------------+
4 rows in set (0.00 sec)

查询年龄小于19的数据

mysql> select age '年龄' from t2  where age<19; #取了个别名，年龄小于19的只有一位
+--------+
| 年龄   |
+--------+
|     18 |
+--------+
1 row in set (0.00 sec)

查询年龄最小在15岁，最大在20岁之间的数据

select *from 表名 where age between 15 and 20；

查询年龄等于15或者20或者30的信息

select *from 表名 where age = 15 or age = 20 or age = 30；

为了避免以上的多个or可以使用in(….)

select *from 表名 where age in(15,20,30);

查询名字为两个字的信息

select *from 表名 where name like '__';

查询身份证号最后一位为X的员工信息

select *from 表名 where sfz like '%X'

DQL-聚合函数查询

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

常见的聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法

select 聚合函数(字段列表) from 表名;

统计表全部数量

select count(*) from 表名 #统计所有

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

演示：

mysql> select count(*) from t2; #这里用统计函数count而不是用sum
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

求年龄的平均值

mysql> select *from t2; # 查看一下表的所有内容
+------+------+------------+
| name | age  | sfz        |
+------+------+------------+
| yyt  |   18 |        291 |
| hlk  |   19 | 1234567890 |
| wgh  |   19 |        123 |
| dzy  |   19 |      12345 |
| xxx  |   20 |     123456 |
+------+------+------------+
5 rows in set (0.00 sec)

mysql> select avg(age) from t2;		#统计年龄字段的平均值，这里用了avg函数
+----------+
| avg(age) |
+----------+
|  19.0000 |
+----------+
1 row in set (0.00 sec)

求所有人年龄当中的最大值；

mysql> select max(age) '年龄' from t2; #可以对聚合函数上使用别名，使用求最大值的函数max
+--------+
| 年龄   |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

求所有人年龄最小的那一个带名字

mysql> select min(age) '年龄' from t2; #求最小值的函数是min
+--------+
| 年龄   |
+--------+
|     18 |
+--------+
1 row in set (0.00 sec)

DQL-分组查询

语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组过滤后的条件]

where不能对聚合函数进行判断，having支持使用聚合函数

执行时机不同where是对分组之前进行过滤，having是对分组之后进行过滤

group by 关键词

注意事项

• 执行顺序：where>聚合函数>having
• 分组之后查询的字段一般为聚合函数和分组字段，查询其他字段毫无意义

根据学生信息表bstudent查找出有几个女生几个男生

mysql> select stud_sex '性别',count(*) from studentscore.bstudent group by stud_sex;
+--------+----------+
| 性别   | count(*) |
+--------+----------+
| 女     |        7 |
| 男     |       10 |
+--------+----------+
2 rows in set (0.00 sec)

DQL-排序查询

语法

select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;

排序方式

• asc代表升序
• desc代表降序

注意：如果是多字段进行排序，当第一个字段值相同时，才会根据第二个字段进行排序 
  默认按照asc升序进行排序所以asc默认可以不写

DQL-分页查询

语法

select 字段列表 from 表名 limit 起始索引，查询记录数

注意

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

DCL-数据控制语言

DCL数据控制语言，用来管理数据库，用户，控制数据库的访问，权限。

DCL-管理用户

查询用户

• mysql数据库中用户的信息都是存放在“mysql”数据库中user表中

select *from mysql.user;


# 示例
mysql> select *from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *E148ACD30EACBC5E1BC84CD068AEB31EB6C8C749 | N                | 2022-09-17 00:18:35   |              NULL | N              |
| localhost | mysql.session | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-09-17 00:12:27   |              NULL | Y              |
| localhost | mysql.sys     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-09-17 00:12:27   |              NULL | Y              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.10 sec)

创建用户

create user '用户名'@'主机名' identified by '密码';
创建用户yyt
create user 'yyt'@'localhost' identified by '123456';
# 需要可以在任意主机上访问
把localhost改为%号

• 创建一个用户yyt，只能够在localhost访问，密码是123456

create user 'yyt'@'localhost' identified by '123456'；

# 示例
mysql> create user 'yyt'@'localhost' identified by '123456'
    -> ;
Query OK, 0 rows affected (0.00 sec)

• 创建一个用户dzy，可以在任何一台机器上访问我们的mysql数据库

create user 'dzy'@'%' identified by '123456' # %表示通配，可以在任意主机上面访问

# 示例
mysql> select count(*) from mysql.user; #	首先通过我我们前面学的聚合函数count求数据库总用户数量
+----------+
| count(*) |
+----------+
|        3 |					# 得到的结果是三个用户，此结果是在创建yyt用户前面执行的，所以创建dzy就会显示五个用户
+----------+
1 row in set (0.00 sec)

mysql> create user 'dzy'@'%' identified by '123456'; #创建一个可以在任何机器上访问的用户
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from mysql.user; # 我们可以看到目前的结果的确是五个用户
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

修改用户可以在任意主机上访问

update mysql.user set Host='%' where User='用户名';

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '1234';

• 修改用户dzy的用户为000000

alter user 'dzy'@'%' identified with mysql_native_password by '000000'

# 示例
mysql> alter user 'dzy'@'%' identified with mysql_native_password by '000000';
Query OK, 0 rows affected (0.00 sec)

# 另一个方法，在mysql命令行外面执行
mysqladmin -uroot -p旧密码 password 新密码;

设置当前用户密码

set password = password('密码');

修改指定用户密码

set password for 用户名 = password('密码');

给用户重命名

rename user 原用户名 to 新用户名;

删除用户

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

注意

• 主机名可以使用百分号通配
• 这类SQL开放人员操作的较少，主要是DBA数据库管理员使用

DCL-权限控制

mysql中定义了很多权限

以下是我们经常,常用的几种

权限	说明
alL,ALL PRIVILEGES	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表,修改字段
drop	删除数据库/表/视图
create	创建数据库/表

• 其他权限及描述的含义，需要去参考官方文档

1. 查询权限

show grants for '用户名'@'主机名'；

# 演示
mysql> show grants for 'yyt'@'localhost'; #查询上面创建的用户yyt的权限
+-----------------------------------------+
| Grants for yyt@localhost                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'yyt'@'localhost' |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> 

2. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

# 如何给所有数据库表赋予权限？

grant 权限列表 on *.* to '用户名'@'主机名'；

# 如何给数据库所有表赋予权限

grant 权限列表 on 指定库名.* to '用户名'@'主机名'；

3. 撤销权限

revoke 权限 on 数据库名.表名 from '用户名'@'主机名'

---

函数

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

字符串函数

语法

select 函数(参数);

• 字符串拼接
  • concat

select concat('hello','mysql');			# 把hello和mysql这个字符串拼接

]

• 填字符串str全部转为小写
  • lower（str）

select lower('HELLO'); # 把括号内HELLO字符传全部转为小写

• 将所有的字符转为大写
  • upper

select upper('hello');

• 向左填充
  • lpad

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

• 向右填充
  • rpad

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

• 去除空格
  • trim

select trim(' hello mysql '); #trim 是去除左右两边的空格不去除中间的空格

时间函数

函数服务内置的命令，可以使用时间函数给字段赋值

类型	用途
curtime()	获取当前的系统时间
curdate()	获取当前的系统日期
now()	获取当前系统日期和时间
year()	获取年
month()	获取月
day()	获取日
date()	获取日期
time()	获取时间

---

约束

1. 概念：约束是作用于表中字段上面的规则，用于限制存储在表中的数据
2. 目的：保证数据库中数据的正确、有效性和完整性
3. 分类

约束	描述	关键字
非空约束	限制字段的数值不能为null	not null
唯一约束	保证该字段所有的数据都是唯一不重复的	unique
主键约束	主键是一行数据的唯一标识要求非空且唯一	primary by
默认约束	保存数据是如果未指定该字段的值，则采用默认值	default
检查约束（8.0.16版本之后）	保证字段值满足某一个或多个条件	check
外键约束	用来让两张表的数据之间建立链接，保证数据的一致性和完整性常见的约束型	foreign key

注意：

检查约束至mysql 8.0.16版本后才支持

约束演示

对应的建表语句

主键约束

通过修改表建立主键约束

alter table 表名 add constraint 自定义约束名 primary by （列名）;

删除主键约束

alter table 表名 drop primary key;

如果希望在向表中加入新行时能够由系统自动生成一个唯一标识红表中表中每行数据的序列值，则可以通过主键添加auto_increment关键字来实现

使用自增列时，需要注意如下三点，1 每张表只允许有一个自增列，2 该列的数据类型只能为整数类型，通常取int，3 该列不允许为null值，也不能有默认值，一般情况下不允许人为插入该列数值，也不允许修改该列的值

外键约束

通过修改表建立外键约束

alter table 表名 add constraint 约束名 foreign key (列名) references 被参照表名 (被参照列名);

删除外键约束

alter table 表名 drop foreign key 约束名;

实现级联更新，级联删除

on delete cascade # 级联删除
on update cascade # 级联更新

唯一约束

通过修改表建立唯一约束

alter table 表名 add constraint 约束名 unique (列名);

删除唯一约束

alter table 表名 drop index 约束名

非空约束

通过修改表建立非空约束

alter table 表名 modify 列名 数据类型 not null;

删除非空约束

alter table 表名 modify 列名 数据类型 null;

检查约束

通过修改表建立检查约束、

alter table 表名 add constraint 约束名 check (条件表达式);

删除检查约束

alter table 表名 drop check (条件表达式);

默认约束

通过修改表建立默认约束

alter table 表名 alter 列名 set default 默认值;

删除唯一约束

alter table 表名 alter 列名 drop default;

多表查询

一个关系型数据库中的多个表之间一般都存在着某种内在的联系（如外键约束），它们共同提供有用的信息，所以在实际的查询中，用户往往需要从多个表中查询相关数据。若一个查询同时涉及两个以上的表并通过连接条件实现，则称为连接查询。

笛卡尔积:笛卡尔乘积是指在数学中，两个集合，A集合和B集合的所有组合情况(在多表查询时需要消除无效的笛卡尔积)

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件限制，最终的查询结果条数是两张表记录条数的乘积

避免了笛卡尔积现象，但是并不会减少匹配次数，只不过显示的结果是有效记录

多表关系

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

---

• 一对多（多对一）

部门是1的地方，员工是多的地方，因为一个部门可以有多个员工

---

• 多对多

---

• 一对一

一对一经常用于拆分

---

多表查询概述

指从多张表中查询数据

连接查询-内连接

内连接查询的是两张表中字段交集的部分

内连接的特点就是完全能匹配上这个条件的数据查询出来

隐式内连接:

select 字段列表 from 表1，表2 where 条件...;

• 在进行多表查询的时候如果表名字比较长，我们在字段前面就需要加上比较长的表名，这个时候就需要给表设置别名，通过别名可以在表的后面空格加别名

select *from emp e,emp2 d;

对于一个DQL语句来说先执行的是from，就可以给表起别名继续后面的操作

在显示内连接中inner这个关键字是可以省略的

显示内连接:

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

外连接查询

外连接和内连接的区别

左外连接语法：

select 字段列表 from 表名 left [outer] join 表2 on 条件...;

左外连接查询的是，完全包含左表，表1的数据也包含了表1和表2交集部分的数据

右外连接语法：

right代表：将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接中两张表存在主次关系。

select 字段列表 from 表1 right [outer] join 表2 on 条件；

相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

查询bstudent表的所有数据及每个学生对应的班级

自连接语法：

对一张表连续查询多次

select 字段列表 from 表A 别名A join 表A 别名 B on 条件表达式

• 在自连接查询中一定要给表起别名

联合查询

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

关键字 union,union all

union all是直接将查询的结果合并

union 是将查询的结果进行去重

select 字段列表 from 表A union [all] select 字段列表 from 表B;

示例：查询学生表bstudent中性别是女 和 男生是团员的信息

select *from bstudent where stud_sex='女' union select *from bstudent where stud_sex='男' && members='团员';

对于联合查询的多张表列数必须保持一致，字段类型也需要保持一致

对于union all 会将全部的数据直接合并到一起，union会对合并之后的数据去重

子查询

在SQL语句中嵌套的select语句成为嵌套查询，又称子查询

select *from t1 where column1=(select column1 from t2);

• 根据子查询结果不同，分为
  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询(子查询结果为一行）
  • 表子查询（子查询的结果为多行多列）

• 根据子查询位置，分为：where之后，from之后，select之后。

where子句中的子查询可以看做是一个条件用括号包含

from子句中的子查询，可以将子查询的查询结果当做成一张临时表（看待

select后面出现的子查询，只能返回一条结果，多余一条就报错(这个内容不需要记住，了解即可)

合并查询结果union

合并查询结果集合

select查询语句之间用union进行合并

使用union的效率要高一些，对于表连接来说，每连接一次新表则满足笛卡尔积，成倍的翻。。。。

但是union可以减少匹配的次数，在减少匹配次数的情况下还可以完成两个结果集的拼接。

union把乘法变成加法进行运算。union在使用时候注意，在进行结果集合并的时候要求两个结果集的列数相同，

事务

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

查看事务提交方式

select @@autocommit;  # 查询结果显示1表示默认事物提交方式为自动提交

设置事务的提交方式-改为手动提交

set @@autocommit=0; 			# 设置为0默认为手动提交

提交事务

commit;

回滚事务

rollback;

事务的四大特性:

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

并发事务问题：

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

事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
read uncommitted	✔	✔	✔
read committed	❌	✔	✔
repeatable read(默认)	❌	❌	✔
serializable	❌	❌	❌

查看事务的隔离级别

select @@transaction_isolation;

设置事务隔离级别

set session transaction isolation level read uncommitted;

存储引擎

存储引擎就是存储数据建立索引、更新/查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以存储引擎也可以被成为表类型

mysql默认存储引擎是InnoDB>

2. 查看当前数据库支持的存储引擎

show engines;

• InnoDB

介绍InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5之后，innodb是默认的mysql存储引擎

特点

DML操作遵循ACID模型，支持事务

行级锁，提高并发访问性能

支持外键Foreign key 约束，保证数据的完整性和正确性

文件

支持数据库崩溃后自动恢复机制

xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应一个这样的表空间，存储该表的结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table

查看系统变量

show variables like 'innodb_file_per_table';

engine=innodb

---

索引

索引实在数据库表上面的字段添加的，是为了提高查询效率存才的一种机制一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引，索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

在mysql当中主键上以及unique(唯一)字段上都会自动添加索引！

什么条件下我们会考虑给字段添加索引？

条件1：数据量庞大（到底有多么庞大，这个需要测试，因为，每一个硬件环境不同）

条件2： 该字段经常出现在where后面以条件的形式存在，也就是说这个字段总是被扫描

条件3： 该字段很少的dml操作（因为dml之后索引需要重新排序）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。建议通过主键查询，建议通过unique约束字段进行查询，效率是比较高的

索引的优缺点

创建索引

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引也是需要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗	索引大大提高了查询效率，同时却降低了更新表的速度，如对表进行insert，updatedelete时，效率降低。

为什么innodb存储引擎选择使用b+tree索引结构

1. 相对于二叉树层级更少，搜索效率高
2. 对于b-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样会导致一页中存储的键值减少，指针随着减少，要同样保存大量的数据只能增加树的高度，导致性能降低。
3. 相对hash索引，b+tree支持匹配范围及排序操作；

create index emp_ename index on emp(ename)

# emp_ename起名

删除索引

drop index emp_ename index on emp;

查看sql语句是否使用了索引进行检索

explain select *from emp where ename='bane';

索引的分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

在innodb存储引擎中，根据索引的存储形式，又可以分为以下两种。

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只能有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

注意：

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一索引作为聚集索引
3. 如果表没有主键，或没有合适的唯一索引，则innodb自动生成一个rowid作为隐藏的聚集索引

索引的语法

• 创建索引

create [unique|fulltext] index 索引名称 on 表名(字段名...);

# 一个索引可以包含多个字段，如果一个索引只有一个字段被称为单列索引，多个字段称为联合索引或称为组合索引

• 查看索引

show index from 表名;

• 删除索引

drop index 索引名称 on 表名;

SQL性能分析

对于我们做sql优化的时候我们经常优化的是select查询语句

• SQL执行频率

mysql连接客户端成功后，通过show [session|global] status 命令提供服务器状态信息，可以查询到insert、update、delete、select的访问频次：

show global status like 'Com_______';			# 共七个下滑线

• 慢查询日志

慢查询日志记录了所有执行时间超过指定参数(秒，默认10秒)的所有sql语句

mysql中的慢查询日志默认没有开启，需要在mysql的配置文件(/etc/my.cnf)中配置：

# 开启mysql满日志查询开关
slow_query_log=on
#设置慢查询日志文件存放路径
slow_query_log_file=/var/log/mariadb/localhost_slow.log
# 设置慢查询日志的时间为1秒，sql语句执行时间超过两秒就会被视为满查询，会被记录到慢查询日志中
long_query_time=1

在mysql查看mysql慢查询日志是否开启

show variables like 'slow_query%';

show variables like 'long_query%';

查看慢查询日志文件

cat /var/log/mariadb/localhost_slow.log

实时输出慢查询日志文件尾部的信息，可以使用Linux的tail命令

tail -f 文件名

• profile详情-检测每一条查询sql语句的耗时情况

show profiles能够在做sql优化时帮助我们了解时间都耗费到哪里去了，通过have_profiling参数，能够看到当前mysql是否支持profile操作

select @@profiling; #查看mysql是否支持profile;

默认profiling是关闭的，可以通过set语句在session/global级别开启prifiling

set profiling=1;	# 为0是关闭,1为开启

show profiles;

查询指定query_id的sql语句各个阶段的耗时情况

show profile for query id；

show profile cpu for query id；

• explain执行计划

explain执行计划各自段含义：

• id

​ select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从 上到下，id不同，值越大越先执行）

• select_type

​ 表示select查询类型

• type

​ 表示连接类型，性能由好到差连接类型为null，const，eq_ref,range,index,all

• possible_key

​ 显示可能应用在这张表的索引，一个或多个

• key

​ 实际用到的索引，为null则表示没有用到索引

• key_len

​ 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

• filtered

​ 表示返回结果的行数站需读取行数的百分比，filtered的值越大越好。

索引使用

不要在索引列上进行运算，否则索引将会失效，字符串不添加单引号索引也会失效，模糊查询如果只是尾部模糊匹配索引不会失效，如果头部模糊匹配，索引失效。

索引使用

如果索引了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引最左列开始，并且不跳过索引中的列，如果跳跃了某一列，索引将部分失效（后面的索引失效）。

最左前缀法则，select语句跟你列放的位置没有关系，但是最左边查询的字段必须存在

• 范围查询

联合索引中出现范围查询（>,<）范围查询，右侧的索引列索引失效

在业务中如果业务允许尽量使用大于等于的运算符保证索引生效

• 索引列运算

不要在索引列上进行运算操作，索引将失效

• 字符串不加引号索引失效，因为会出现隐式类型转换
• 模糊查询

如果仅仅是尾部进行模糊匹配，索引不会失效，如果是头部模糊匹配索引失效

• or连接条件

用or分割开的条件，如果or前的条件中的列有索引，那么涉及的索引都不会被用到。or所连接的字段，任意一侧没有索引那么索引将全部失效

• 数据分布影响

如果mysql评估使用索引比全表还慢，会走全表查询

• SQL提示

sql提示是优化数据库的重要手段简单来说就是在sql中添加一些人为的提示来达到优化操作的目的

use index(索引名) #告诉数据库建议用哪个索引

ignore index(索引名) #告诉数据库不要用哪个索引

force index(索引名字)  #必须使用指定索引

语法格式

# 三个的语法格式都是
select *from 表名 use index或ignore index或force index  where 条件表达式
# 都是在from表名后添加

use index是建议mysql使用指定的索引，mysql有权不使用

force index 必须使用指定索引

数据的导入导出

导入

检索目录

MySQL要求数据导入导出调用的文件在MySQL的检索目录下才可以

安装mysql服务软件时，会自动创建检索目录

show variables like "secure file priv"; # 查看

把系统文件的内容存储到数据库的表里。默认只有root用户拥有数据导入权限

数据导入步骤

• 建表
• 拷贝文件到检索目录下
• 导入数据

load data infile "/目录名/文件名"
into table 库名.表名
fields terminated by "符号" # 列间隔符号
lines terminated by "\n"; # 行结束符号 \n换行

导出

一共有三种命令格式分别是

格式1：
select into outfile "/目录名/文件名";

格式2：
select into outfile "/目录名/文件名"
terminated by "分隔符";
# 默认会以换行符号\n当做每一行的结束符

格式3：
select into outfile "/目录名/文件名";
terminated by "分隔符号"; # 如 ：
lines terminated "结束符号";# 想使用其他符号当做每一行的结束符号 如 ###

第一种格式演示图

第二种格式演示图

第三种格式演示图

总结

总结如下：

• 导出数据的行数有SQL查询决定
• 导出的是表的记录，不包括字段名
• 自动创建存储数据的文件
• 存储数据文件，具有唯一性

备份

mysqldump -h服务器 -u 用户名 -p 密码 数据库名 >备份文件.sql

# 选项
-A 备份所有的库
-B 备份多个数据库
-F 备份之前刷新日志
-E 备份时间调度器的代码
-R 备份存储过程和存储函数

视图

视图就是一个虚拟的表，没有真实数据，他是由select查询语句定义，同真实的表数据一致，但是视图并不在数据库中以存储的数据形式存在

视图的作用：

1. 安全原因，视图可以隐藏一些敏感的信息
2. 简化查询，对查询语句简化

语法格式

create  [or replace ] view 定义的视图名 AS select查询语句;
# or replace 是可以创建并且也可以对视图进行修改

例如

create view myuser as select user,host from mysql.user;

select *from my.user;
# 简化查询步骤

查询视图

查看创建视图的语句

show create view 视图名称;

查看视图数据

select *from 视图名称;

修改视图

第一种：直接使用创建视图的语句添加select查询的字段前提需要添加or replace

创建视图是如果存在已同名的视图则重新创建

create or replace view 原视图名称 as 新的查询语句;

第二种：使用alter语句修改视图

alter view 视图名称 as 新的查询语句;

删除视图

drop view 视图名称;

视图的检查选项

当使用with check option子句创建视图时，mysql会通过视图检查正在更改的每一个行，例如插入，更新，，删除，以使其符合视图的定义，mysql允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致为了确定检查的范围，mysql提供了两个选项cascaded和local默认是cascaded

cascaded和之前创建外键实现级联删除和级联更新的cascade一样

alter table 表名 add constraint 约束名 foreign key (字段名) references 被参照的表 (被参照的表的字段) on update cascade 级联更新|on delete cascade 级联删除;

local会递归去找当前视图所依赖的视图的检查选项

视图更新

要使视图可更新，视图中的行与基础视图中的行之间必须要存在一对一的关系，如果视图包含以下任何一项则该视图不可更新

1. 聚合函数或窗口函数（sum，min，max，avg，count等）
2. distinct 去重
3. group by
4. having
5. union或union all

存储过程

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库sql语言里面的代码封装与重用。

• 特点

封装，复用

可以接收参数，也可以返回数据

减少网络交互，效率提升

语法

• 创建

create procedure 存储过程名称（【参数列表】）
begin
	sql语句
end；

• 调用

call 名称 （【参数】）

• 查看

show create procedure 存储过程名称；

• 删除

drop procedure if exists 存储过程名；

注意：在命令行中，执行创建存储过程的sql时，需要通过关键字delimiter指定sql语句的结束符。

delimiter $$

变量

系统变量是mysql服务器提供，不是用户定义的，属于服务器层面，分为全局变量（global），会话变量（session）

系统变量

查看系统变量

show global|session variables; # 后面可以加上like模糊匹配
select @@session|global 系统变量名；# 查看指定变量值

默认不写全局或会话变量，默认就是会话变量

注意两个@表示的是系统变量一个@表示用户自定义变量

用户自定义变量

用户根据自己定义的变量，用户变量不用提前声明，在用的时候直接用@变量名使用就可以，其作用域为当前连接。

赋值

set @变量名=值；

set @变量名:=值;

select @变量名:=值;

select 字段名 into @变量名 from 表名; #将表查询的结果赋值给一个变量

推荐使用:=来给定义变量

使用

select @变量名;

用户自定义的变量无需赋值，默认是个空值null

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前需要declare声明，可用作存储过内的局部变量和输入参数，局部变量的范围是在其内声明的begin…end块。

# 声明
declare 变量名 变量类型 [default...]

变量类型就是数据的字段类型，int，varchar，char，date。enum，time等

赋值

set 变量名=值;
set 变量名:=值;
select 字段名 into 变量名 from 表名;

条件判断if

if 条件1 then
	sql语句;
elseif 条件2 then
	sql语句;
else
	不满足条件1和条件2执行;
end if;

参数

条件判断case

• 语法格式1

case 表达式
	when 1 then sql语句;
	when 2 then sql语句;
	else sql语句;
end case;

# 如果说表达式的值为1的话执行when 1 then后面的语句，为2同理

• 语法格式2

case
	when a=1 then sql语句;
	when a=2 then sql语句;
	else sql语句;
end case;

循环while

• 先判定条件如果条件为true则执行逻辑，否则，不执行逻辑

while 条件 do
	sql语句;
end while;

循环repeat

• repeat是有条件的循环控制语句，当满足条件的时候退出循环，具体语法为：

# 先执行一次逻辑然后判定逻辑是否满足，如果满足则退出如果不满足则继续下一次循环
repeat
	sql语句;
	until 条件
end repeat

循环loop

• lopp实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用来实现简单的死循环，loop可以配合一下两个语句使用;
• leave ：配合循环使用，退出循环
• iterate ： 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[标记;] loop
	sql逻辑
end loop [标记;]

leave label; 指定标记的循环体
iterate label; 直接进入下一次循环

游标

条件处理程序

存储函数

• 存储函数是有返回值的存储过程，存储函数的参数只能是in（输入类型）具体语法如下：

create function 存储函数名称(参数列表)
returns type [characteristic...]
begin
	sql语句;
	return...;
end;

characteristic说明
- deterministic: 相同的输入参数总是产生相同的结果
- no sql：不包含sql语句
- reads sql data 包含读取数据的语句，但不包含写入数据的语句。

触发器

• 触发器是与表相关的数据对象，是指在insert/update/delete之前或者是之后，触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
• 使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器还只支持行级触发，不支持语句级别触发。

• 语法

创建

create trigger 触发器名称
before/after insert/update/delete on 表名 for each row
begin
	sql语句;
end;

查看

show triggers;

删除

drop trigger [数据库.]触发器名字 # 如果不指定数据库名字则表示当前数据库

锁

• 介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源的争用外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据并发访问性能的一个重要因素，从这个角度来说，锁对数据库而显得尤其重要，也更加复杂。

按照锁的粒度分，分为三类，全局锁（锁定数据库中的所有表），表级锁（每次操作锁住整张表），行级锁（每次操作锁住对应的行数据）。

全局锁

• 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的dml的写语句，ddl语句，已经更新操作的事物提交语句都将被阻塞。

通常的使用场景事做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

加锁

flush tables  with read lock;

解锁

unlock tables;

在innodb引擎中，我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份

mysqldump --single-transaction  -u root -p123456 数据库名>自定义命名.sql

表级锁

• 介绍

表级锁，每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低，应用在myisam，innodb，bdb等存储引擎中。

对于表级锁主要分为以下三类：

1. 表锁

2. 元数据锁（meta data lock，mdl）

3. 意向锁

表锁

• 表锁分为两类：

1. 表共享读锁（read lock）
2. 表独占写锁（write lock）

语法：

1. 加锁：lock tables 表名... read/write。

2. 释放锁：unlock tables/客户端断开连接。

加了写锁后当前客户端可以读取和写入这个表，其他客户端不能读取和写入。会处于阻塞状态

元数据锁

mdl加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。mdl锁主要作用事维护表元数据的一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

查看元数据锁

意向锁

为了避免DML语句在执行时，加的行锁，与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

1. 意向共享锁（IS）：与表锁共享锁(read)兼容，与表锁

排它锁(write)互斥。

2. 意向排他锁(IX)：与表共享锁(read)及排它锁(write)都互斥，意向锁之间不会互斥

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度小，发生锁冲突的概率最低，并发度最高，应用在innodb存储引擎中。

innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类。

行锁(record lock): 锁定单个行记录的锁，防止其他事物对此进行update，delete。在rc，rr隔离级别下都支持。

间隙锁（GAP lock）：锁定索引记录间隙(不含该记录)，保证索引记录的间隙不变，并锁住数据前面的间隙gap，在rr隔离级别下支持。

临键锁(Next-key lock):行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙gap，在rr隔离级别下支持。

运维篇

日志

• 错误日志
• 二进制日志
• 查询日志
• 慢查询日志

错误日志

错误日志是mysql中最重要的日志之一，它记录了mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置：

show variables like "%log_error%"

二进制日志

二进制日志，记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（select，show）语句。

作用:灾难时的数据恢复。mysql的主从复制。在mysql8版本中默认的二进制日志是开启着的，涉及到的参数如下：

show variables like "%log_bin%"

查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句，默认情况下摩擦互相你日志是未开启的。如果需要开启查询日志，可以设置以下配置：

show variables like "%general%"

修改MySQL的配置文件/etc/my.cnf文件，添加如下内容：

# 该选项用来开启慢查询日志，可选值0H或1 0关闭 1开启
general_log=1
#设置日志的文件名，如果没有指定默认为host_name.log
general_log_file=mysql.query.log

慢查询日志

慢查询日子记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微秒。

#慢查询日志
slow_query_log=1
#执行时间参数
long_query_time=2