经管之家App
让优质教育人人可得
立即打开
注：为了便于阅读，所有代码均采用小写字母，MySQL不区分大小写。
MySQL的语句在结束时需加上分号，本文档中的所有示例代码均可执行。
本文是作者在学习过程中整理的期末复习笔记，希望能帮助到需要快速掌握命令的同学，如有错误或遗漏，请指出！
全部版块
我的主页
收藏
create database (if not exists) 你的数据库名称;create database delta_soldier;show databases;select database();use 你的数据库名称;use delta_soldier;drop database (if exists) 数据库名称;drop database delta_soldier;注意，从这里开始，每个操作都在一个特定的数据库内进行，即已使用use命令进入该数据库。
创建表
在创建表时，规范格式下，每行定义一个属性，每个属性后跟逗号，除了最后一个属性外，最后一个属性后不加逗号。
注意，你需要先进入一个数据库才能创建表，使用上文提到的use命令进入数据库。
所有的comment ‘你的备注’ 都是可以省略的。
create table 你想创建的表的名称(
变量1 变量类型 comment '你的备注',
变量2 变量类型 comment '你的备注',
变量3 变量类型 comment '你的备注',
变量4 变量类型 comment '你的备注' /*最后一个属性无需加逗号*/
)comment '你的备注';例如，创建一个包含姓名、性别、代号、职业、年龄的表：
create table delta_soldiers(
name char(50) comment '姓名',
gender char(1) comment '性别',
codes char(20) comment '代号',
occ char(20) comment '职业',
age int comment '年龄'
)comment '人员信息表';这样，一个表就创建完成了。现在解释一下各个部分的含义：
create table 名称( ); 是固定的格式，（）内定义属性。
第一个属性name char(50) comment ‘姓名’，name是你自定义的变量名，char是变量类型，表示该变量最多可存储50个字符。我个人更倾向于使用varchar(50)，这种类型的特性是根据实际输入的数据大小来调整占用的空间。例如，char(50)，如果你输入的数据是4个字符，它仍然占用50个字符的空间；而使用varchar(50)，输入4个字符则仅占用4个字符的空间。comment 后面的‘姓名’ 是对该属性的备注。
再次强调，每个属性定义后都要加逗号换行，但最后一个属性不加逗号。
show tables;desc 你的表名称;show create table 你的表名称;类型 有符号
tinyint -128-127
smallint -32768-32767
mediumint -8388608-8388607
int或integer
bigint -2^63-2^63-1
float
double
decimal 依赖于M和精度D的值类型 大小 描述
char 0-255 bytes 定长字符串
varchar 0-65535 bytes 变长字符串
tinyblob 0-255 bytes 不超过255个字符的二进制数据
tinytext 0-255 bytes 短文本字符串
blob 0-65535 bytes 二进制形式的长文本数据
text 0-65535 bytes 长文本数据
mediumblob 0-16777215 bytes 二进制形式的中等长文本数据
mediumtext 0-16777215 bytes 中等长文本数据
longblob 0-4294967295 bytes 二进制形式的极大文本数据
longtext 0-4294967295 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：01至2038-01-19 03：14：07 YYYY-MM-DD HH-MM-SS 混合日期和时间值，时间戳alter table 表名 add 字段名 类型（长度） comment '注释';alter table delta_soldiers add address varchar(50) comment '地址';alter table 表名 modify 字段名 新数据类型（长度）;alter table delta_soldiers modify age tinyint;alter table 表名 change 旧字段名 新字段名 类型（长度） comment '备注';用于调整已建立的属性，以delta_soldiers中的occ（职业）为例，将其名称从occ改为occupation，类型从char改为varchar，代码如下：
alter table delta_soldiers change occ occupation varchar(30) comment '职业';完成之后，可以使用desc命令检查效果。
alter table 表名 drop 字段名;删除特定字段
示例，删除delta_soldiers中的address（地址）字段
alter table delta_soldiers drop address;alter table 表名 rename to 新表名;更改指定表的名称
比如，将表delta_soldiers的名称更改为delta
alter table delta_soldiers rename to delta;这样，表delta_soldiers就变更为表delta，你可以通过使用show tables;命令查看delta_soldiers是否依然存在。
drop table (if exists) 表名;删除指定表，例如，删除表delta，代码如下：
drop table delta;truncate table 表名;依旧以delta为例
truncate table delta;执行此操作后，在使用show tables;命令时，你仍然可以看到这个表，但其内部数据已被清空。
insert into 表名 （字段名1，字段名2……） values (值1，值2……);该指令用于向一个表的部分字段插入数据，即，假设这个表一个单元有5个属性，你可以只写入3个属性（至少目前如此）
示例，向delta中插入name、code、gender三个属性，为了方便，我再次列出delta表的创建代码
create table delta(
name char(50) comment '姓名',
gender char(1) comment '性别',
codes char(20) comment '代号',
occ char(20) comment '职业',
age int comment '年龄'
)comment '人员信息表';现在开始插入name、code、gender的数据
insert into delta (name,gender,age) values ('测试','男','22');两个括号内的数据需相互对应，字符类型需要使用单引号，这样，我们就部分地插入了数据。
insert into 表名 values (值1，值2……);实际上是去掉前面的括号，直接默认修改整个字段，后面的括号需要包含完整的数据。
示例，向delta中插入完整数据
insert into delta values('阿列克谢·彼得罗夫','男','深蓝','工程',45);这样，一条数据就完整地插入表中了。
insert into 表名 （字段名1，字段名2……） values (值1，值2……), (值1，值2……), (值1，值2……);
insert into 表名 values (值1，值2……),(值1，值2……),(值1，值2……);可以一次性添加大量数据，数据之间用逗号分隔即可
例如，继续向delta中添加数据
insert into delta values ('大卫·费莱尔','男','乌鲁鲁','工程',40),('泰瑞·缪萨','男','牧羊人','工程',34);如何查看已插入的数据呢？这里会用到后续将介绍的一条语句，大家无需理解，学习到后面自然明白。
select *from delta;update 表名 set 字段名1=值1,字段名2=值2,…… where 条件;该语句用于修改某条数据的某些属性，where用于精确定位，若不加where，那么你的操作将会修改整个表的数据而非单独某条数据。
例如，我们先前在delta中插入了一条测试数据
insert into delta (name,gender,age) values ('测试','男','22');现在我们来修改这条数据
update delta set name='金卢娜',gender='女',codes='露娜',occ='侦察',age=30 where name='测试';这样，原先的测试数据就变为现在新增的数据了。
delete from 表名 where 条件;用于删除特定的数据，若不加where，将导致整个表数据被删除！
此外，delete语句不可用于删除某个字段的值，如有此需求，应使用update
先再增加一条数据吧
insert into delta values('格赫罗斯','男','典狱长','HAAVK',null);（别问为何是null，我也无从得知）
开始删除
delete from delta where codes='典狱长';这样就成功删除了一条数据。
那么大家猜测一下，如果where后面是gender='男'，执行完毕后表里剩余多少条数据呢？
答案是仅剩一条四道具侦察
在此再次强调，如果你不写where，整张表都将被清空！
select 字段1,字段2,字段3,…… from 表名;可以查询到指定部分的数据
例如，我们在表delta中，我们只想知道人员的名字和代号，而不需要了解其他数据
select name,codes from delta;运行后发现，列出的表格里确实只有姓名和代号，其他的信息均未显示
select *from 表名;可查询到全部数据，你会看到完整的列表
select 字段1 （as） '别名',字段2 （as） '别名'…… from 表名;这个命令的作用以delta为例，当你直接查询，即未使用别名查询信息时，系统将以delta内元素的名称作为表头，例如，select name, codes from delta; 这个命令将以name 和codes为表头，下方则是你查询的数据。当使用别名查询时，代码如下：
select name as '姓名',codes as '代号' from delta;这样系统将以姓名和代号作为表头，而不是原本的name和codes，其中，as可加可不加，效果相同。
select distinct 字段1,字段2…… from 表名;使用该命令后，系统展示的记录均为唯一，例如，在delta中，对occ进行去除重复记录查询，代码如下：
select distinct occ from delta;执行命令后发现，输出结果里只有一个工程师，一个侦察员，一个HAVVK共三条记录，其余的则未显示，注意，这里仍属于查询部分字段。
那么，如果同时查询occ和codes呢？
select distinct occ,codes from delta;你会发现所有的occ和codes属性都出现了，因为每个codes（代号）都是独一无二的。即如果你查询的字段中有不会重复的字段，那么所有记录都将出现。
select 字段列表 from 表名 where 条件列表;字段列表即“字段1,字段2……”，即你可以只输入一个字段，也可以输入多个字段，条件列表也可以只有一个，也可以多个。
在此前，先概述一下有哪些条件运算符
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<>或!= 不等于
between……and…… 在某个范围之内，包含最大值和最小值
in(……) 在in之后的列表中的值，多选一
like 占位符 模糊匹配（_匹配单个字符，%匹配任意个字符）
is null 为空
逻辑运算符 功能
and 或 && 并且，两个条件都成立
or 或 || 或者，任意条件成立
not 或 ! 非，不是以表delta为例，筛选出年龄超过40岁的个人并展示所有详情，代码如下：
select *from delta where age>40;如此一来，就能获取完整且符合标准的个人资料。
之前我们曾添加过一位年龄为空的典狱长（若已删除则重新添加，或自行添加一个无年龄的数据），现在我们将依据年龄进行查询。
select *from delta where age is null;如此便可通过查找年龄为空的数据，获得完整的典狱长资料。
查询年龄不为45岁的个人的所有详情，代码如下：
select *from delta where age!=45;
select *from delta where age<>45;上述两行代码等效，均能查找到所有年龄非45岁的个人的详情。
查询年龄介于25至30岁之间（含25和30）的个人资料，代码如下：
select *from delta where age>=30 && age<=35;
select *from delta where age>=30 and age <=35;
select *from delta where age between 30 and 35;上述代码等效，查找到的都是相同的信息。
查询性别为女性且年龄低于35岁的个人全部信息，代码如下：
select *from delta where gender='女' and age<35;查询年龄为30、45、34岁的个人全部信息，代码如下：
select *from delta where age=30 or age=45 or age=34;
select *from delta where age in(30,45,34);上述两行代码等效，请注意，in无法用于查询空值，对于所有空值，in都将忽略它们，查询空值 唯一的方式 就是使用is null
查询代号为三个字符的个人全部信息，代码如下：
select *from delta where codes like '___';like后面是三个"_"，在这个文件里三个下划线看起来全部合并了，同学们在数据库中输入时会非常清晰地看到三条下划线。
查询姓名最后一个字为“夫”的个人的全部信息，代码如下：
select *from delta where name like '%夫';‘%夫’ 表示前面可以有任何字符，只要最后一个是“夫”即可匹配成功，输入后发现阿列克谢·彼得罗夫的全部信息被显示出来。
| 函数 | 功能 |
|---|---|
| count | 计数 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
select 聚合函数(字段列表) from 表名;例1：统计delta中个人的数量，代码如下：
select count(*) from delta;表中有5条数据，统计结果是5
例2：统计有年龄数据的个人数量
select count(age) from delta;你会发现统计结果是4，因为典狱长的年龄记录为空，空值不参与任何聚合函数计算。
例3：统计个人的平均年龄
select avg(age) from delta;请注意，这里的统计结果不包括典狱长，因为典狱长的年龄为空。
例4：求最小的员工年龄
select min(age) from delta;例5：求职业为工程师的全部个人年龄总和
select sum(age) from delta where occ='工程';select 字段列表 from 表名 ( where 条件) group by 分组字段名 (having 分组后的过滤条件);select gender,count(*) from delta group by gender;select occ,count(occ) from delta where age<=45 group by occ having count(*)>=3;select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;select *from delta order by age desc;select *from delta order by occ,age desc;select 字段列表 from 表名 limit 起始索引,查询记录数;注意：
起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数；
如果查询的是第一页的数据，起始索引可以省略，直接简写为limit 10
select *from delta limit 0,3;
select *from delta limit 3;select *from delta limit 3,3;在此前，回顾一下先前学习的内容
alter table delta add birth date comment '出生日期';update delta set birth='1990-07-22' where name='阿列克谢·彼得罗夫';
update delta set birth='1995-04-25' where name='大卫·费莱尔';
update delta set birth='2001-09-02' where name='泰瑞·缪萨';
update delta set birth='2005-09-12' where name='金卢娜';3.再增加多个数据
insert into delta values('麦晓雯','女','骇爪','侦察',22,'2013-11-16');
insert into delta values('罗伊·斯米','男','蜂医','支援',27,'2008-02-23');
insert into delta values('王宇昊','男','威龙','突击',27,'2008-05-11');1.示例
示例1：查询年龄为27岁的突击、支援人员的信息
select *from delta where age=27 and occ in('突击','支援');select *from delta where gender='男' and age between 20 and 40 and name like '___';select gender,count(*) from delta where age<=40 group by gender;示例4：查询所有年龄小于40岁（含40岁）的人员，按年龄升序排列，若年龄相同，则按职业降序排列
select *from delta where age<=40 order by age,occ desc;select *from delta where age<50 and gender='男' order by age,occ desc limit 4;1.编写顺序
select 字段列表
from 表名
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数from 表名
where 条件列表
group by 分组字段列表
having 分组后条件列表
select 字段列表
order by 排序字段列表
limit 分页参数DCL-用户管理
1.查询用户
use mysql;
select *from user;create user '用户名'@'主机名' identified by '密码';create user 'test'@'localhost' identified by '001001';mysql -u test -palter user '用户名'@'主机名' identified with mysql_native_password by '新密码';alter user 'test'@'localhost' identified with mysql_native_password by '111111';drop user '用户名'@'主机名';drop user 'test'@'localhost';1.常用权限
mysql的常见权限如下：
权限 说明
all,all privileges 所有权限
select 查询数据
insert 插入数据
update 修改数据
delete 删除数据
alter 修改表
drop 删除数据库/表/视图
create 创建数据库/表show grants for '用户名'@'主机名';grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';grant all on mydata.delta to 'test'@'localhost';revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';revoke all on mydata.delta from 'test'@'localhost';字符串函数
1.常用函数
函数 功能
concat(s1,s2,..sn) 字符串拼接，将s1，s2，... sn拼接成一个字符串
lower(str) 将字符串str全部转为小写
upper(str) 将字符串str全部转为大写
lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str) 去掉字符串头部和尾部的空格
substring(str,start,len) 返回从字符串str从start位置起的len个长度的字符串select concat('hello',' mysql');数值函数
函数 功能
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0~1内的随机数
round(x,y) 求参数x的四舍五入的值，保留y位小数select lpad(round(rand()*1000000,0),6,'0');日期函数
函数 功能
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定date的年份
month(date) 获取指定date的月份
day(date) 获取指定date的日期
date_add(date, interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1, date2) 返回起始时间date1 和 结束时间date2之间的天数select name,datediff(curdate(),birth) as dates from delta order by dates desc;流程函数
函数
if(value, t, f)
如果value为true，则返回t，否则返回f
ifnull(value1, value2)
如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1]……else[default] end
如果val1为true，返回res1，... 否则返回default默认值
case[expr] when [val1] then [res1]……else[default] end
如果expr的值等于val1，返回res1，... 否则返回default默认值select
name,
(case codes when '深蓝' then '浮力肘击王（已废）' when '乌鲁鲁' then 'CS' when '牧羊人' then '无名严父' else '暂无文本' end) as '称号'
from delta;1.概念
约束是在表字段上设置的规则，用于限制存储在表中的数据。
2.目的
确保数据库中数据的准确性、有效性和完整性。
3.分类
约束 描述 关键字
非空约束 限制该字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一、不重复的 unique
主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key
默认约束 保存数据时，如果未指定该字段的值，则采用默认值 default
检查约束(8.0.16版本之后) 保证字段值满足某一个条件 check
外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 foreign key字段名 字段含义 字段类型 约束条件 约束关键字
id id唯一标识 int 主键，并且自动增长 primary key, auto_increment
name 姓名 varchar(10) 不为空，并且唯一 not null，unique
age 年龄 int 大于0，并且小于等于120 check
status 状态 char(1) 如果没有指定该值，默认为1 default
gender 性别 char(1) 男或女 checkcreate table users(
id int primary key auto_increment comment 'id主键',
name varchar(10) not null unique comment '姓名',
age int check ( age>0 and age<=120 ) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) check ( gender='男' or gender='女' ) comment '性别'
)comment '用户表';insert into users (name, age, status, gender)values ('test',20,'1','男');create table dept(
id int auto_increment comment 'id' primary key,
name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into dept (id, name) values (1, '战斗部'), (2, '医疗部'), (3, '战术部'), (4, '后勤部'), (5, '研发部');
create table emp(
id int auto_increment comment 'id' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位',
entrydate date comment '入职时间',
managerial int comment '直属领导id',
dept_id int comment '部门id'
)comment '员工表';
insert into emp (id, name, age, job,entrydate, managerial, dept_id) values
(1,'泰瑞·缪萨',34,'工程','2035-01-01',1,1),
(2,'阿列克谢·彼得罗夫’,45,'工程','2035-01-01',1,1),
(3,'大卫·费莱尔',40,'工程','2035-01-01',1,1),
(4,'罗伊·斯米',27,'支援','2035-01-01',2,2),
(5,'金卢娜',30,'侦察','2035-01-01',4,4);create table 表名（
………………
constraint 外键名称 foreign key(外键字段名) references 主表（主表列名）
）;alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表（主表列名）;alter table emp add constraint kf_emp_dept_id foreign key(dept_id) references dept（id）;delete from dept where id=1;alter table 表名 drop foreign key 外键名称;alter table emp drop foreign key kf_emp_dept_id;no action
当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 restrict 一致）
restrict
当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与 no action 一致）
cascade
当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则删除/更新外键在子表中的记录。
set null
当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
set default
父表有变更时，子表将外键列设置成一个默认的值 (innodb不支持)级联删除/更新外键语法如下，与前文大致相同，仅在末尾增加了on update cascade on delete cascade：
alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名)
on update cascade on delete cascade;接下来我们重新构建前文提及的外键
alter table emp add constraint kf_emp_dept_id foreign key(dept_id) references dept(id) on update cascade on delete cascade;目前外键已成功创建，此外键允许修改dept表中的数据
update dept set id=6 where id=1;我们将战斗部（1号部门）的标识更改为6，之后检查子表是否有变化
select *from emp;可以看到，原先属于1号部门的所有成员现在都归类到6号部门
假设我直接从部门表中移除6号部门，会发生什么情况？
答案是子表中6号部门的数据也会一同被删除
设置为null的删除/更新外键语法如下：
alter table emp add constraint kf_emp_dept_id foreign key(dept_id) references dept(id) on update set null on delete set null;其实就是在cascade的位置替换成set null
创建完成后，在部门表（即父表）中移除1号部门。注：此前我已经将部门编号恢复了
delete from dept where id=1;可以看到1号部门已被成功删除，而emp表中原来属于1号部门的数据依然存在，只是部门字段变为null
一对多：一名学生只能归属于一个班级，但一个班级可以容纳多名学生，这与前文讨论的部门类似
在这种情形下，多的一方会设立外键，指向单一的一方的主键
多对多：一名学生可以选择多门课程，同时一门课程也可被多名学生选修
在这种情况下，需要建立一个中介表，该表至少包含两个外键，分别连接双方的主键。如此便可在中介表中管理双方的关系
一对一：用户与其详细资料之间的关系
在此情境下，任一方均可设立外键，连接另一方的主键，并设定外键唯一性
这里，通过一个多对多的例子来帮助理解
create table student(
id int primary key auto_increment comment '主键ID',
name varchar(20) not null comment '姓名',
num varchar(20) comment '学号'
)comment '学生信息表';
insert into student (name, num) VALUES ('test1','01001'),('test2','001002'),('test1','001003');
create table course(
id int auto_increment primary key comment '主键ID',
name varchar(10) not null comment '课程名称'
)comment '课程表';
insert into course (name) values ('JAVA'),('Python'),('C++');
create table connect(
id int auto_increment primary key comment '主键ID',
student_name int not null comment '学生ID',
course_name int not null comment '课程ID',
constraint connect_student foreign key (student_name) references student(id),
constraint connect_course foreign key (course_name) references course(id)
)comment '中间表';
insert into connect (student_name, course_name) VALUES (1,1),(1,2),(2,1),
(2,3),(3,2),(3,3);connect表负责维护student和course两表的数据，当这两表的主键发生更改时，connect表会同步更新。
在以往的学习过程中，我们构建了emp和dept表，但所进行的查询均为单表查询。那么，多表查询又是怎样的呢？
select *from emp,dept;如同我们学习过的许多命令，只需用逗号分隔即可实现多表查询。不过，你会注意到一个问题，emp表中有5条记录，dept表中也有5条记录，当你执行多表查询时，会得到5*5=25个结果，这种现象被称为笛卡尔积。
筛选有效数据的方法很简单，只需添加where条件即可。
select *from emp,dept where emp.dept_id=dept.id;这样一来，就能同时获取两表的信息，并确保它们一一对应。你可能会疑惑其他部门去哪了，答案是它们被过滤掉了，我们查询的是两个表能相互匹配的记录。
连接查询
内连接：相当于查询A、B两表交集的数据
外连接：
左外连接：查询左侧表的所有数据，以及两表交集的数据
右外连接：查询右侧表的所有数据，以及两表交集的数据
自连接：当前表与自身进行连接查询，自连接必须使用表别名
子查询
内连接查询语法
注意：内连接查询的是两个表的交集
1. 隐式内连接
select 字段列表 from 表1,表2 where 条件;示例：查询每个员工的姓名及关联的部门名称（隐式内连接）
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;2. 显式内连接
select 字段列表 from 表1 (inner) join 表2 on 连接条件;示例：查询每个员工的姓名及关联的部门名称（显式内连接）
select emp.name,dept.name from emp inner join dept on emp.dept_id=dept.id;以上两种方法的查询结果是一致的
外连接查询语法
1. 左外连接
select 字段列表 from 表1 left(outer) join 表2 on 条件;左外连接查询的是左侧表的数据加上两表交集的部分
示例：查询emp表的所有数据及其对应的部门信息（左外连接）
select emp.*,dept.name from emp left outer join dept on emp.dept_id=dept.id;2. 右外连接
select 字段列表 from 表1 right(outer) join 表2 on 条件;右外连接查询的是右侧表的数据加上两表交集的部分
示例：查询emp表的所有数据及其对应的部门信息（右外连接）
select emp.*,dept.name from emp right outer join dept on emp.dept_id=dept.id;观察右外连接的查询结果，你会发现有些差异。某些部门没有对应的员工，但右外连接会展示右侧表的所有信息，因此这些部门在左侧的对应数据全为null。
自连接语法如下：
select 字段列表 from 表A 别名A join 表A 别名B on 条件;自连接查询可以是内连接，也可以是外连接
例1：查询员工及其直属上司的姓名
预期结果，emp中泰瑞的1号部门上司，大卫和阿列克谢向他汇报（这里简化了他们的实际隶属关系），罗伊则向阿列克谢汇报
select a.name,b.name from emp a join emp b on a.managerial=b.id;查询结果与预期相符。
例2：查询所有员工及其上司的姓名，即使没有上司也要显示出来
标准的左外连接，因为需要列出所有员工的数据
select a.name,b.name from emp a left join emp b on a.managerial=b.id;由于样本数据有限，同学们可以自行增加更多的数据进行测试。
联合查询是指将多次查询的结果合并成一个新的查询结果集。
语法如下：
select 字段列表 from 表A ……
union (all)
select 字段列表 from 表B ……;案例：列出年龄超过40岁（含40）且兵种为侦察的人员
select *from emp where age>=40
union all
select *from emp where job='侦察';其中 all的存在与否是有区别的
如果某人既符合第一个查询条件，又符合第二个查询条件，那么在使用union all的情况下，此人会出现两次，因为union all只是简单地合并结果。若不希望出现重复，可省略all。此外，上下两个查询的字段列表必须一致，否则会导致错误。
1. 概念：在SQL语句中嵌套select语句，称为嵌套查询或子查询
语法如下：
select *from t1 where column1=(select column1 from t2);子查询外部的语句可以是insert/update/delete/select中的任意一种
依据子查询结果的不同，可分为：
根据子查询的位置，可分为：where之后、from之后、select之后。
子查询返回的结果是一个单独的值（数字、文本、日期等），这是最基础的形式，此类子查询被称为标量子查询。
常用的运算符：＝ <> > >= < <=
示例：我们要查找战斗部门的ID，然后获取该部门所有成员的信息
select *from emp where dept_id=(select id from dept where name='战斗部');实际上就是将两个查询合并，顺序执行。首先在括号内查询出战斗部门的ID，然后将结果作为外部查询的条件，外部查询根据返回的条件进行筛选。
子查询返回的结果是一列（可以是多行），这种子查询称作列子查询。
常用的运算符：in 、not in 、any 、some 、all
操作符 描述
in 在指定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回列表中，有任意一个满足即可
some 与any等同，使用some的地方都可以使用any
all 子查询返回列表的所有值都必须满足示例1：查询医疗部门和后勤部门的人员信息
select *from emp where dept_id in(select id from dept where name='医疗部'or name='后勤部');代码解释：首先，括号内的字段会返回医疗和后勤部门的编号，即2和4，这两个编号会被传递给外部主查询，在此，in函数会检查emp表是否有匹配的内容，只要符合条件就会列出。
示例2：查询比战斗部门年龄小的所有人员的年龄
先分解问题
select id from dept where name='战斗部';
select age from emp where dept_id='战斗部id'；
select * from emp where age<all(战斗部的年龄);
开始套娃
第一行和第二行合并
select age from emp where dept_id=(select id from dept where name='战斗部');
再和第三行合并，继续套娃
select *from emp where age<all(select age from emp where dept_id=(select id from dept where name='战斗部'));子查询返回的结果是一行（可以是多列），这类子查询称为行子查询。
常用的运算符：=、<>、in、not in
示例：查询与阿列克谢职业相同且领导为泰瑞的人员信息。（虽然有些奇怪，但我懒得更改表结构或添加新条目了）
依旧先拆分问题
select job, managerial from emp where name='阿列克谢·彼得罗夫';
select *from emp where job='工程'and managerial=1;
其中，第二行可以改写成这样
select *from emp where (job,managerial)=('工程',1);
所以两行合并
select *from emp where (job,managerial)=(select job, managerial from emp where name='阿列克谢·彼得罗夫');子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的运算符：in
示例：查询年龄大于等于30岁的员工及其部门的信息
select *from emp where age>=30;
select *from (年龄>30的员工信息) left join dept on 员工信息.dept_id=dept.id;
开始套娃
select a.*,dept.*from (select*from emp where age>=30) a left join dept on a.dept_id=dept.id;这里给(select*from emp where age>=30)取了个别名a，由于from是最先执行的，因此其他部分可以识别这个别名。
索引（index）是一种辅助MySQL高效获取数据的数据结构（有序）。除了数据本身，数据库系统还维护着用于特定查找算法的数据结构，这些结构通过某种方式指向数据，从而可以在这些结构上实现高效的查找算法，这就是索引。
1. 创建索引
create [unique | fulltext] index 索引名称 on 表名 (字段1，字段2,...);[unique | fulltext]二者选其一，当然也可以不加，unique表示该索引唯一，不允许重复，即索引指向的字段绝不允许有重复项。
fulltext表示创建一个全文索引。
如果不加上述任一选项，则表示创建的是一个普通索引。
一个索引可以关联多个字段名。
示例：
在数据库university中，给学生表的学生号添加一个普通索引
create index sno_index on student(Sno);2. 查看索引
show index from 表名;示例：
在university中的student表中，查看表中已有的索引
show index from student;3. 删除索引
drop index 索引名称 on 表名;示例：
删除刚刚创建的索引sno_index
drop index son_index on student;再次使用show index from student;查看索引，会发现我们创建的索引已被移除。
视图（View）是一种虚拟表。视图中的数据不在数据库中实际存储，行和列数据来自定义视图的查询所使用的表，并且在使用视图时动态生成。
简单来说，视图仅保存查询的SQL逻辑，而不保存查询结果。因此，在创建视图时，主要工作集中在构建这条SQL查询语句上。
1. 创建视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]or replace可以省略，如果要替换一个已存在的视图，就需要加上。
示例：
在university的student表中创建学号——姓名视图stu_v_1
create or replace view stu_v_1 as select Sno,Sname from student;stu_v_1即视图名称，as后面的语句表明视图内需封装的内容，这里选择了学号和姓名。
2. 查询
视图与表类似，查询方式也相同。
查看创建视图语句：show create view 视图名称；
查看视图数据：select * from 视图名称……；示例：
查询我们创建的视图语句，并查看视图内的数据
show create view stu_v_1;
select *from stu_v_1;和表查询几乎一样。
3. 修改视图
方式一：create [or replace] view 视图名称[列名列表] as select语句 [with [cascaded | local] check option]
方式二：alter view 视图名称[列名列表] as select语句 [with [cascaded | local] check option]方法一实际上是创建视图，此处or replace不可省略（如果你想用它来修改已存在的视图的话）
示例：
修改视图stu_v_1的字段，增加性别
方式一：create or replace view stu_v_1 as select Sno,Sname,Ssex from student;
方式二：alter view stu_v_1 as select Sno,Sname,Ssex from student;4. 删除视图
drop view [if exists] 视图名称 [视图名称] …if exists可加可不加
示例：
删除我们创建的视图
drop view if exists stu_v_1;5. 视图添加数据
和表一样，视图也可以添加数据，但视图本身不存储数据，它的所有数据都来自表。向视图内插入数据等同于向表中插入数据。插入数据的语法和表完全相同，不再赘述。
示例：向stu_v_1视图插入数据（如果已删除请自行创建）
insert into stu_v_1 values (20180008,'zhangsan','male');此时如果你查询student表，会发现表中新增了一条记录。
6. 检查选项cascaded
视图在建立时，能够加入限定条件，比如，为stu_v_1添加性别为男性的限制（最初打算使用学号，后来发现university里的学号是文本而非数值）
create or replace view stu_v_1 as select Sno,Sname,Ssex from student where Ssex='male';因此，生成的视图仅包含性别为男性的数据。
那么，如果尝试插入一个性别为女性的数据，能否成功呢？
答案是可以的，尽管视图中不会显示这条数据，但实际表中确实成功插入了。
如何防止这种情况？就是在创建视图时增加检查选项with cascaded check option。
当利用with check option子句创建视图时，MySQL会验证每条正在更改的记录，如插入、更新、删除，确保其符合视图的定义。MySQL支持基于其他视图创建视图，同时也会检查依赖视图中的规则以维持一致性。为了确定检查的范围，MySQL提供了两种选项：cascaded和local，默认设置为cascaded。
create or replace view stu_v_1 as select Sno,Sname,Ssex from student where Ssex='male' with cascaded check option;添加检查选项后，再次尝试插入性别为女性的数据时，系统将报告错误。
此外，我们也可以基于一个视图创建另一个视图。
实例：在stu_v_1基础上，创建stu_v_2视图，但选择特定的学号。
create or replace view stu_v_2 as select Sno,Sname,Ssex from stu_v_1 where Sno in('20180001','20180004','20180006','20180009') with cascaded check option;我们选择了3个特定的学号，创建了新的视图stu_v_2。
接下来，我们试着插入学号为20180009的学生信息，性别为女性，会怎样呢？
insert into stu_v_2 values ('20180009','ceshi1','female');答案是会出错。
那么输入性别为男性试试看。
insert into stu_v_2 values ('20180009','测试','male');发现插入成功，由此得出结论，当一个视图基于另一个视图创建时，会继承其限制条件。
另外，cascaded还有一个特性，如果当前视图基于上一个视图创建，而上一个视图虽然有where条件，但没有添加检查选项。
如果当前视图设置了cascaded，就相当于给它及其依赖的所有视图都强制加上了cascaded检查选项。
实例：创建两个视图，第二个视图基于第一个创建，第一个视图不加检查选项。
create or replace view stu_v_3 as select Sno,Sname,Ssex from student where Ssex='male';
create or replace view stu_v_4 as select Sno,Sname,Ssex from stu_v_3 where Sno in('20180001','20180004','20180006','20180009','20180010') with cascaded check option ;可以看到，尽管stu_v_3筛选性别为男性，但不阻止添加性别为女性的数据，stu_v_4也不限制性别。
然而，当你试图向stu_v_4插入性别为女性的数据时，会出现错误。
也就是说，cascaded会强制检查当前视图及其所有依赖视图的筛选条件，并全部加上检查选项。
7. 检查选项local
语法与cascaded基本相同，只需将with cascaded check option中的cascaded替换为local即可，用法一致。
这里仅阐述它与cascaded的不同之处。
local仅对视图有条件地进行检查，如果视图或被依赖的视图有检查选项local，就会进行检查，否则不检查。
将上述stu_v_4的检查选项更改为local，添加性别为女性的数据就不会出错。
介绍
触发器是与表相关的数据库对象，在执行insert/update/delete操作之前或之后，触发并执行触发器中定义的一系列SQL语句。触发器的这一特性有助于在数据库层面确保数据的完整性、日志记录、数据验证等操作。
使用别名old和new来引用触发器中发生变化的记录内容，这一点与其他数据库系统相似。目前触发器仅支持行级触发，不支持语句级触发。
触发器语法
1. 创建触发器
create trigger 触发器名称
before/after insert/update/delete
on 表名称 for each row
begin
触发器的具体逻辑实现 ;
end;其中after/before分别表示该触发器是在操作完成之后还是之前被触发。
2. 查看触发器
show triggers;3. 删除触发器
drop trigger 触发器名称;4. 插入触发器
示例：创建用于存储记录的日志表user_logs，再创建针对student表的insert触发器。
日志表user_logs的创建代码如下：
create table user_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作的ID',
operate_params varchar(500) comment '操作参数',
primary key('id')
)engine=innodb default charset=utf8;插入触发器tb_user_insert_trigger的代码如下：
create trigger tb_user_insert_trigger
after insert on student for each row
begin
insert into user_logs values
(null,'insert',now(),new.Sno,concat('插入数据内容为：Sno=',new.Sno
,' Sname=',new.Sname,' Ssex=',new.Ssex,' Sbirthday=',new.Sbirthdate,' Smanjor=',new.Smajor));
end;完成后，向student表中插入一条数据：
insert into student values ('20180011','test2','female','2005-01-01','JAVA');插入成功后，我们会看到user_logs表中出现了记录，其中详细记录了操作信息。
5. 修改触发器
示例：创建student表的修改触发器。
触发器代码如下：
create trigger tb_user_update_trigger
after update on student for each row
begin
insert into user_logs values(null,'update',now(),new.Sno,
concat('修改前的数据： Sno=',old.Sno,
' Sname=',old.Sname,
' Ssex=',old.Ssex,
' Sbirthdate=',old.Sbirthdate,
' Smajor=',old.Smajor,
'|修改后的数据： Sno=',new.Sno,
' Sname=',new.Sname,
' Ssex=',new.Ssex,
' Sbirthdate=',new.Sbirthdate,
' Smajor=',new.Smajor ));
end;完成后，当你修改数据时，触发器会记录下修改前后的详细信息。
注意，如果你修改的数据中有任何一项为空，则最后一项记录也会为空，若要避免此问题，可以使用ifnull()函数处理。
例如，将old.Smajor改写为ifnull(old.Smajor)即可避免出现空值的问题。
6. 删除触发器
注意，这里指的是检测删除操作的触发器，不是删除触发器本身。
示例：创建student表的删除触发器。
create trigger tb_user_delete_trigger
after delete on student for each row
begin
insert into user_logs values(null,'delete',now(),old.Sno,
concat('被删除的数据： Sno=',old.Sno,
' Sname=',old.Sname,
' Ssex=',old.Ssex,
' Sbirthdate=',old.Sbirthdate,
' Smajor=',old.Smajor));
end;创建完成后，随意删除student表中的一条数据，会发现日志表也记录了操作信息。需要注意的是，如果删除的数据中有空值，仍然会导致最后一项操作信息为空。
触发器可以被视为一种监听设备，当检测到被监听的表发生变化时，按照预设的代码执行记录日志的操作。
begin和end之间的内容实际上是一系列常规的表操作，触发器检测到变化后会执行这些操作。
7. case—when触发器
语法如下：
create trigger 触发器名称
before/after insert/update/delete
on 表名称 for each row
begin
case
when(限制条件)then
逻辑实现1
else
begin
逻辑实现2
end;
end case;
end;含义是，若符合限定条件，则实施逻辑实现1；反之，则实施逻辑实现2。其中，逻辑实现均为标准表操作。
示例：添加insert触发器，当主修课程为CPP时，记录信息；当主修课程非CPP时，则不记录此操作。
create trigger stu_tr_course
after insert on student for each row
begin
case
when(new.Smajor='CPP')then
insert into user_logs values (null,'insert',now(),new.Sno,
concat('选择插入数据内容为：Sno=',new.Sno,
' Sname=',new.Sname,
' Ssex=',new.Ssex,
' Sbirthday=',new.Sbirthdate,
' Smanjor=',new.Smajor));
else
begin
end;
end case;
end;向student表中添加数据，若课程非cpp，则仅tb_user_insert_trigger会记录数据，而stu_tr_course不会记录数据。
8.if——then触发器
语法如下：
create trigger 触发器名称
before/after insert/update/delete
on 表名称 for each row
begin
if(限制条件)then
逻辑实现
end if;
end;逻辑实现依旧是常规表操作，不再举例说明。
扫码加好友，拉您进群
