目录
一、SQL分类
1.DDL
数据定义语言，用来定义数据库对象（数据库，表，字段）
1.数据库操作：
1)查询
①查询所有数据库：show databases;
②查询当前数据库：select database();
2)创建
create database[if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
3)删除
drop database[if exists] 数据库名;
4）使用
use 数据库名;
2.表操作---查询
1）查询当前数据库所有表：show tables;
2）查询表结构：desc 表名;
3）查询指定表的建表语句：show create table 表名;
3.表操作--创建
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
.......
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
4.表操作--数据类型
①数值类型：tinyint,smallint,mediumint,int/integer,bigint,float,double（使用时要指定两个参数--整体长度与小数位数）,decimal(若声明数值无符号，在类型后加UNSIGNED),
eg. age TINGINT UNSINGED， score double(4,1)(假定分数最大为100.0，此时长度为4，小数保留一位，故后面参数值为1)
②字符串类型：char(定长字符串,即无论输入几个字符占用空间都为最大字符数占用的空间，缺失的由空格补齐，性能比varchar好),varchar(变长字符串，即在最大数量字符数内，输入几个字符就占几个字符空间),tinyblob,tinytext(短文本字符串),blob(二进制形式长文本数据),text(长文本数据),mediumblob,mediumtext,longblob(二进制形式的极大文本数据),longtext(极大文本数据)
注意：char与varchar使用时要标明最大字符长度，例如char(10),varchar(10)
③日期类型：date(YYYY-MM-DD，日期值),time(HH-MM-ss，时间值或持续时间),year(YYYY，年份值),datetime(YYYY-MM-DD HH-MM-ss，混合日期和时间值),timestamp(YYYY-MM-DD HH-MM-ss，混合日期和时间值，时间戳)
5.表操作--修改
①添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
②修改数据类型：alter table 表名 modify 字段名 新数据类型（长度）;
③修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];
④删除字段：alter table 表名 drop 字段名;
⑤修改表名：alter table 表名 rename to 新表名;
6.表操作--删除
①删除表：drop table [if exists] 表名;
②删除指定表(表中数据全部删除），并重新创建该表：truncate table 表名;
2.DML
数据操作语言，用来对数据库表中的数据进行增删改
1.添加数据：insert
①给指定字段添加数据：insert into 表名(字段名1，字段名2......) values(值1，值2 ....)
②给全部字段添加数据：insert into 表名 values(值1，值2...)
③批量添加数据：
insert into 表名(字段名1，字段名2......) values (值1，值2 ....)，(值1，值2 ....);
insert into 表名 values (值1，值2...), (值1，值2...);
注意：字符串与日期型数据包含在引号中，字段与值要一 一对应
2.修改数据：update
update 表名 set 字段名1=值1，字段名2=值2，....[where 条件];
注意：修改的条件可以有也可以没有，若没有，则会修改整张表该字段的所有数据
3.删除数据：delete
delete from 表名[where 条件]
注意：若没有条件，会删除表中所有数据，但delete不能删除某一个字段的值（可以用update）
3.DQL
全称为Data Query Language,数据查询语言，用来查询数据库中表的记录
编写顺序：select>from>where>group by>having>order by>limit
执行顺序：from>where>group by>having>select>order by>limit
①基本查询
1）查询多个字段
select 字段1，字段2，...from 表名;
select * from 表名;--------查询所有字段
2）设置别名
select 字段1[as 别名1]，字段2[as 别名2]....from 表名;
3）去除重复记录
select distinct 字段列表 from 表名；
②条件查询（where）：
1)语法：select 字段列表 from 表名 where 条件列表；
2)条件：
比较运算符：> ,>= ,< ,<= ,= ,<>或!= ,between...and...(在某个范围内，含最小、最大值),in(...)(在in之后的列表中的值，多选一),like 占位符（模糊匹配，_匹配单个字符，%匹配任意个字符，无论什么数据类型都要用单引号），is null(判断字段是否为null)
逻辑运算符：and或&&，or或||，not或！
eg1.统计年龄为18或25或30的人数：select * from emp where age in(18,25,30);
eg2. 统计年龄为两个字的人数：select * from emp where name like ‘__’;(两个下划线)
eg3.统计身份证号(假设为18位)最后一位为x的人数：select * from emp where idcard like ‘%x’;或select * from emp where idcard like ‘_________________x’;(17个下划线)
eg4.统计年龄个位为0的人数:select * from emp where age like ‘%0’;
③聚合查询（count,max,min,avg,sum）:
1)语法：select 聚合函数（字段列表） from 表名;(null值不参与计算)
eg1. select count(*) from emp;
eg2.select avg(age) from emp;
eg3.求西安地区员工年龄之和：select sum(age) from emp where workaddress=’西安’;
2)聚合函数：将一列数据作为一个整体进行纵向运算
常见聚合函数：count:统计数量，max最大值，min最小值，avg平均值，sum求和
④分组查询（group by）
1)语法：select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];
select后一般为分组字段与聚合函数，若还有其他字段。则显示分组字段的第一个对应的
2)where与having 区别：
执行时间不同：where是对分组之前进行过滤，不满足where条件，不参与分组；而having是分组以后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，having可以
eg1.根据性别分组，统计男性员工和女性员工数量：select gender,count(*) from emp group by gender;
eg2.根据性别分组，统计男性员工和女性员工的平均年龄：
select gender,avg(age) from emp group by gender;
eg3.查询年龄小于25岁的员工数量，并根据工作地址分组：select workaddress,count(*) from emp where age<25 group by workaddress;
eg4.查询年龄小于25岁的员工数量，并根据工作地址分组，获取员工数量大于3的工作地址：
select workaddress,count(*) from emp where age<25 group by workaddress having count(*)>3;
⑤排序查询（order by）
1）语法：select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；
2）排序方式：asc:升序（默认值，使用是可省略不写），desc:降序
eg1.根据年龄升序排序：select * from emp order by age asc;(由于升序是默认的，asc可省略不写)
eg2.根据年龄升序排序,若年龄相同，按照入职时间降序排序：select * from emp order by age asc,entrydate desc;
eg3.查询所有年龄小于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，若年龄相同，按入职时间降序排序：
select name,age from emp where age <35 order by age,entrydate desc;
⑥分页查询（limit）
1）语法：select 字段列表 from 表名 limit 起始索引，查询记录数;
2）起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
3）若查询的是第一页数据，起始索引可省略，直接写为limit 10（10为查询记录数）；
eg1.查询年龄在20到40岁（含）之间的前5位男性员工信息，并按年龄升序排序，若年龄相同，按入职时间升序排序：
select * from emp where gender=’男’ and age between 20 and 40 order by age asc,entrydate asc limit 5;
4.DCL
数据控制语言，用来创建数据库用户、控制数据库的访问权限
1.用户管理
①查询用户:
use mysql;
select * from user;
②创建用户
create user ‘用户名’ @ ‘主机名’ identified by ‘密码’;
若主机名为%表示任意主机均可访问
③修改用户密码
alter user ‘用户名’ @ ‘主机名’ identified with mysql_native_password by ‘新密码’;
④删除用户
drop user ‘用户名’ @ ‘主机名’;
2.权限控制
all或all privileges(所有权限)，select(查询数据)，insert(插入数据)，update(修改数据)，delete(删除数据)，alter(修改表)，drop(删除数据库/表/视图)，create(创建数据库/表)
①查询权限：show grants for ‘用户名’@’主机名’;
②授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’ @ ‘主机名’;
③撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’ @’主机名’;
注意：多个权限之间用逗号分隔；授权时，数据库名和表名可以用*进行通配，代表所有
eg. grant all on itcast.* to ‘heima’ @ ‘%’;
二、函数
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对右边进行填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str,start,len) | 返回从字符串str位置起的len个长度的字符串(索引从1开始) |
语法：select 函数(参数)
eg. select concat(‘hello’,’MySQL’); select lower(‘HELLO’); select lpad(‘01’,5,’-’);
select trim(‘ 0 ha ‘); select substring(‘Hello MySQL’,2,6);
eg.将表peo中的worknum用0左填充至长度为5：update peo set worknum=lpad(worknum,5,’0’);
注意：lpad和rpad只能修改表中的字符型数据
2.数值函数
| 函数 | 功能 |
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回x/y的余数 |
| rand() | 返回0~1内的随机数 |
| round(x,y) | 求参数x四舍五入的值，保留y位小数 |
语法：select 函数(参数)
eg.随机生成六位数：select rpad(round(rand()*1000000,0),6,'0');
3.日期函数
| 函数 | 功能 |
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取指定date的年份 |
| month(date) | 获取指定date的月份 |
| day(date) | 获取指定date的日期 |
| date_add(date,interval expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| datediff(date1,date2) | 返回起始时间date2和结束时间date1之间的天数 |
语法：select 函数(参数)
eg.select year(now()); select date_add(now(),interval 70 day);
select date_add(now(),interval 70 month);
select datediff(‘2021-12-01’,’2021-11-12’);
按入职时间降序查询员工信息：select *,datediff(curdate(),entrydate) from emp order by datediff(curdate(),entrydate) desc;
4.流程函数
| 函数 | 功能 |
| if(value,t,f) | 若valued的值为true，则返回t，否则返回f |
| ifnull(value1,value2) | 若value1不为null，返回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,...否则返回defaul默认值 |
语法：select 函数(参数)
eg1. select if(2=3,’yes’,’no’); select ifnull(‘’,’default);(返回的为value1的值)
eg2. select ifnull(‘null’,’default’);(返回null) select ifnull(null,’default’);(返回default)
eg3. 查询emp表的员工姓名和工作地址，若为上海/北京，则展示为一线城市，其他的为二线城市：select name,case workaddress when '上海' then '一线城市' when '北京' then '一线城市' else '二线城市' end from emp ;
eg4. 查询学生成绩：若大于85分为优秀，大于60为及格，其他为不及格
select id,name,
( case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end)'语文',
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学',
(case when eng>=85 then '优秀' when eng>=60 then '及格' else '不及格' end)'英语' from stu;
三、约束
是作用于表中字段上的规则，用于限制存储在表中的数据（保证数据库中的数据正确、有效性、完整性）
1.分类
| 约束 | 描述 | 关键字 |
| 非空约束 | 限制该字段的数据不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，若未指定该字段的值，则采用默认值 | default |
| 检查约束 | 保证字段值满足某个条件 | check |
| 外键约束 | 用来让两张表的数据之间建立联系，保证数据的一致性和完整性 | foreign key |
注意：约束时作用于表中字段上的，可以在创建/修改表时添加约束，约束之间用空格分开
eg1.根据需求创建表的结构
字段名
字段含义
字段类型
约束条件
约束关键字
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)
无
create table user(
id int primary key auto_increment,
name varchar(10) not null unique,
age int check(age>0 &&age<=120),
status char(1) default '1',
gender char(1)
);
insert into user (name, age,status, gender) values('Tom1',19,'1','男'),('Tom2',25,'0','女');
2.外键约束
（1）添加外键：
create table 表名(
字段名 数据类型,
.......
[constraint][外键名称] foreign key(外键字段名)references 主表(主表列名)
);
或者：
alter table 从表表名 add constraint 外键名称 foreign key(外键字段名)references 主表(主表列名);
eg. alter table emp add constraint fk_emp_dept_id foreign key(dept_id)references dept(id);
（2）删除外键
alter table 从表表名 drop foreign key 外键名称;
eg. alter table emp drop foreign key fk_emp_dept_id;
（3）删除/更新行为
| 行为 | 说明 |
| no action | 当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则不允许该操作（与restrict一致） |
| restrict | 当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则不允许该操作（与no action一致） |
| cascade | 当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则也删除/更新外键在子表中的记录 |
| set null | 当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则设置子表中该外键值为null（这要求该外键允许为null） |
| set default | 父键有变更时，子健将外键列设置成一个默认值（innodb不支持） |
cascade语法：alter table 表名 add constraint 外键名 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
set null 语法：alter table 表名 add constraint 外键名 foreign key(外键字段) references 主表名(主表字段名) on update set null on delete set null;
四、多表查询
1.多表关系
（1）一对多（多对一）：在多的一方建立外键，指向一的一方的主键，
如一个部门有多个员工
alter table emp add constraint fk_emp_dept_id foreign key(dept_id)references dept(id);
（2）多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，如一个学生可选修多门课程，一门课程可以被多个学生选择
create table student_course(
id int auto_increment comment'主键' primary key,
studentid int not null comment '学生ID',
courseid int not null comment '课程ID',
constraint fk_courseid foreign key (courseid) references course(id),
constraint fk_studentid foreign key (studentid) references course(id)
)comment '学生课程中间表';
id name 1 Java 2 PHP
（3）一对一：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的（unique），如用户与用户详情
id
name
gender
phone
1
天天
男
1234
2
向上
女
5678
用户信息表
id
degree
major
address
university
userid
1
本科
英语
1
2
硕士
舞蹈
2
用户教育信息表
2.多表查询
从多张表中查询数据
语法：select (字段1，字段2，..)from 表1，表2，..where 表1.外键名=表2.主键名
eg. select * from emp,dept where emp.dept_id=dept.id;（顺序按照dept.id的顺序）
3.多表查询分类
（1）连接查询
1）内连接：查询两表交集部分的数据
①隐式内连接：select 字段列表 from 表1，表2，.. where 条件...;
eg.查询所有员工所在部门
select emp.name,dept.name fron emp,dept where emp.dept_id=dept.id;
给表起别名进行查询（起了别名就不能再用表名，只能用别名）：
select e.name,d.name from emp e,dept d where e.dept_id=d.id;
②显式内连接：select 字段列表 from 表1[inner] join on表2 on连接条件...;
select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;(inner可略不写)
2）外连接
①左外连接：查询左表所有数据，以及两表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件..;
eg.查询emp表所有数据及对应的部门信息
select e.*,d.name from emp e left join dept d on e.dept_id=d.id;
②右外连接：查询右表所有数据，以及两表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件..;
eg.查询dept表 所有数据及对应员工的信息
select d.*,e.* from dept d right join emp e on e.dept_id=d.id;
3）自连接：当前表与自身的连接查询，自连接必须使用表别名(自连接查询可以是内连接也可以是外连接)
①内连接：select 字段列表 from 表1 别名A join 表1 别名B where 条件..;
eg.一张emp表包含结构为id,name,managerid(员工表，包含员工id，姓名，员工的上司，员工上司也属于员工，故可以用id将表自连接)
select a.name,b.name from emp a join emp b where a.managerid=b.id;
②外连接：select
上述例子中，老板没有上司，故查询时没有查询到老板，先也要把老板查询到可使用外连接
左外连接：select 字段列表 from 表1 别名A left join 表1 别名B on 条件
select a.name,b.name from emp a left join emp b on a.managerid=b.id;
（2）子查询
SQL语句中嵌套select语句称为嵌套查询，又叫子查询
语法：select * from t1 where column1 =(select column from t2);
子查询外部语句可以是insert/update/delete/select中的任一按照查询结果分类：
1）标量子查询：子查询结果为单个值（数字、字符串、日期等）
常用操作符：>,<,<>,<=,>=,!=,=
eg1.查询销售部所有员工信息
分为两步：a.查询销售部id（为4）：selsect id from dept where name=’销售部’;
b.根据销售部id查询员工信息：select * from emp where dept_id=4;
c.合并：select * from emp where dept_id=(selsect id from dept where name=’销售部’);
eg2.查询在李欣欣之后入职的员工的信息
select * from emp where entrydate>(select entrydate from emp where name=’李欣欣’);
2）列子查询：子查询结果为一列（可以是多行）
常用操作符：in,not in,any(子查询返回列表中有任意一个满足即可),some(与any等同，可以用some的地方也可以用any),all(子查询返回列表的所有值都必须满足)
3）行子查询：子查询结果为一行
eg.查询与李欣欣的工资和直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary,managerid from emp where name=’李欣欣’);
4）表子查询：子查询结果为多行多列
eg1.查询与李欣欣，数学书的职位和工资相同的员工信息
select * from emp where (job,salary)in(select job,salary from emp where name=’李欣欣’ or name=’数学书’);
eg2.查询入职日期是”2020-9-1”之后的员工信息及其部门
a.查询入职日期是其之后的员工信息：
select * from emp where entrydate>’2020-09-01’;
b.查询在步骤a中查询后所得表上的员工，对应的部门信息（因为有可能有员工没有部门，在查询时会查询不到，所以采用左外连接查询，将该特殊与员工也包含在最后的查询列表中）
select e.*,d.* from(select * from emp where entrydate>’2020-09-01’) e left join dept d on e.dept_id=d,id;
（3）联合查询
把多次查询的结果合并起来，形成一个新的查询结果集：union,union all
语法：select 字段列表 from 表1...
union [all]
select 字段列表 from 表2....;
注意：若使用union all，相当与将两次查询结果直接合并，若两次查询均有同一个人，则该人出现两次，但若使用union可以去重
对于联合查询的多张表的列数和字段类型要保持一致
eg.查询薪资大于5000的人和年龄小于30岁的人
select * from emp where salary>5000
union
select * from emp where age<30;
若员工A薪资即大于5000年龄又小于30，使用union all时他会出现两次，若使用union会出现一次
四、事务
（1）事务操作
1.方法1
1）查看/设置事务提交方式
select @@autocommit;------自动提交，autocommit默认值为1
set @@autocommit=0;-------手动提交
2）提交事务
commit;
3）回滚事务
rollback;
2.方法2
1）开启事务：start transaction或begin
2）提交事务：commit;
3）回滚事务：rollback;
（2）事务隔离
1）查看事务隔离级别：select @@transaction_isolation
2）设置事务隔离级别：
select [session | global] transaction isolation level {read uncommitted |read committed |repeatable read |serializable}
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
