MySQL总结（上）

一、SQL语句

1.1、DDL（数据库定义语言）

1.1.1、定义数据库

--创建库
create database 库名;
--创建库时判断库是否存在，不存在则创建
create database if not exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;

1.1.2、定义数据表

--创建表
create table 表名 (
        字段1 字段1类型[comment 字段1注释],
        字段2 字段2类型[comment 字段2注释],
        字段3 字段3类型[comment 字段3注释],
        ......
        字段n 字段n类型[comment 字段n注释]
)[comment 表注释];
-- 查看表结构
desc 表名;
-- 查看创建表的SQL语句
show create table 表名;
-- 修改表名
alter table 表名 rename to 新的表名;
--添加一个新的字段
alter table 表名 add 字段; 字段类型;
--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
--删除一个字段
alter table 表名 drop 字段名;
--删除表
drop table 表名;
--删除表时判断表是否存在,若存在则删除
drop table if exists 表名;

数据类型：数值类型、字符串类型、日期时间类型

• 数值类型
  
• 字符串类型
  
• 日期时间类型
  

1.2、DML（数据库操作语言）

1.2.1、增加 insert into

-- 写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
-- 不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);
-- 插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);

#insert into Persons values(1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

1.2.2、删除 delete

-- 删除表中数据
delete from 表名 where 列名  = 值;
-- 删除表中所有数据
delete from 表名;
-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

1.2.3、修改 update

-- 不带条件的修改(会修改所有行,更新整张表)
update 表名 set 字段名1=值1,字段名2=值2;
-- 带条件的修改
update 表名 set 字段名1=值1,字段名2=值2 where 列名=值;

1.3、DQL（数据库查询语言）

1.3.1、基本查询和条件查询（where)

--查询多个字段
select 字段1,字段2,字段3…… from 表名;
select * from 表名;

--设置别名
select 字段1 [as 别名1],字段2 [as 别名2]…… from 表名;
#select workadderss as '工作地址' from emp;
#select workadderss '工作地址' from emp;

--去除重复记录
select distinct 字段列表 from 表名;
#select distinct workadderss '工作地址' from emp;

条件查询

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

BETWEEN…AND （在什么之间）和 IN( 集合)：

-- 查询年龄大于等于20 小于等于30				
SELECT * FROM student WHERE age >= 20 &&  age <=30;
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
				
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);

is not null(不为null值) 与 like（模糊查询）、distinct（去除重复值）

• _:单个任意字符
• %：多个任意字符

-- 查询英语成绩不为null
SELECT * FROM student WHERE english  IS NOT NULL;
	

-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的人			
SELECT * FROM student WHERE NAME LIKE "_化%";				
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';					
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

1.3.2、聚合函数

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

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

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

# 聚合函数
# 统计企业的员工数量
select count(*) from emp;

# 统计该企业员工的平均年龄
select avg(age) from emp;

# 统计该企业员工的最大年龄
select max(age) from emp;

# 统计该企业员工的最小年龄
select min(age) from emp;

# 统计西安地区员工的年龄之和
select sum(age) from emp where workadderss = '西安';

1.3.3、分组查询（group by、having）

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

# 分组查询
# 根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;

# 根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;

# 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workadderss ,count(*) from emp where age < 45 group by workadderss having count(*) > 3;

where和having的区别:

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

注意：

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

1.3.4、排序查询（order by）

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

1.3.5、分页查询（limit）

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

# 分页查询
# 查询第1页员工数据，每页展示10条记录
select * from emp limit 0,10;

select * from emp limit 10;

# 查询第2页员工数据，每页展示10条记录 ------>（页码 - 1）* 页展示记录数
select * from emp limit 10, 10;

注意：

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

1.4、DCL（数据库控制语言）

1.4.1、管理用户

---查询用户
use mysql;
select * from user;
---添加用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
---删除用户
DROP USER '用户名'@'主机名';

1.4.2、权限管理

-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'lisi'@'%';

-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT ALL ON *.* TO 'zhangsan'@'localhost';

-- 撤销权限：
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

二、函数、约束、事务

2.1、函数

2.1.1、字符串函数

# concat
select concat('Hello', ' MySql');
# lower
select lower('Hello');
# upper
select upper('Hello');
# lpad
select lpad('01', 5, '-');
# rpad
select rpad('01', 5, '-');
# trim
select trim(' Hello MySql ');
# substring
select substring('Hello MySql', 1, 5);

eg：用于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0,比如：1号员工的工号应该为00001.

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

2.1.2、数值函数

# ceil
select ceil(1.1);
# floor
select floor(1.9);
# mod
select mod(2,4);
select mod(6,4);
# rand
select rand();
# round
select round(2.345, 2);
select round(2.344, 2);

eg:# 通过数据库的数据，生成一个六位数的随机验证码

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

2.1.3、日期函数

# curdate
select curdate();
# curtime
select curtime();
# now
select now();
# year(date)
select year(curtime());
# month(date)
select month(curtime());
# day(date)
select day(curtime());
# data_add(date, interval expr type)
select date_add(curtime(), interval 70 year);
select date_add(curtime(), interval 70 month);
select date_add(curtime(), interval 70 day);

# datediff(date1, date2)
select datediff('2020-12-01','2020-11-01');

eg：查询所有员工的入职天数，并根据入职天数倒叙排序

select name ,datediff(curdate(), entrydate) as entryday from emp order by entryday desc;

2.1.4、流程函数

# if
select if(true, 'ok', 'error');
select if(false, 'ok', 'error');

# ifnull
select ifnull('ok', 'default');
select ifnull('', 'default');
select ifnull(null, 'default');

# case when then else end
# 查询emp表的员工姓名和工作地址(北京/上海--->一线城市，其他 ----> 二线城市)
select
    name,
    (case workadderss when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

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

2.2、约束

1. 概念：约束是作用于表字段上的规则，用于限制存储在表中的数据。

2. 目的：保证数据库中数据的正确、有效性和完整性。

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

举例：

create table user(
    id int primary key auto_increment comment '主键',
    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) comment '性别'
)comment '用户表';

2.2.1、外键约束

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

---添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表（主表列名）;

#alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);


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

#alter table emp drop foreign key fk_emp_dept_id;

具有外键的称为子表，外键关联的表称为父表

2.2.2、外键删除、更新行为

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
---举例（级联更新）
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;

2.3、事务

一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
默认MySQL的事务时自动提交的，也就是说，当执行一条 DML语句马，MySQL会立即隐式的提交事务。


此时执行完语句，数据没有发生变化。需要执行commit语句

执行错误，执行回滚事务。（rollback）

事务的四大特性

并发事务问题

事务的隔离级别

三、多表查询

多表关系

从A表找B表内容，就在A中设置外键

多表查询

select * from emp, dept;    # 笛卡尔积
select * from emp, dept where emp.dept_id = dept.id;

3.1、连接查询

3.1.1、内连接（A、B交集）（join）

相当于查询A、B交集部分数据
1.隐式内连接：使用where条件消除无用数据

-- 语法： 
select 字段列表 from 表名1,表名2 where 条件;
-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id= dept.id;
 

2.显式内连接

-- 语法： 
select 字段列表 from 表名1 [inner] join 表名2 on 连接条件;
-- 例如：
SELECT * FROM emp INNER JOIN dept ON emp.dept_id= dept.id;   
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id; 

3.1.2、外连接（[outer] join）

左外连接：查询左表所有数据，以及两张表交集部分数据
相当于查询表1的所有数据包含表1和表2交际部分的数据

-- 语法：
select 字段列表 from 表1 left [outer] join 表2 on 条件；
-- 例子：
-- 查询员工表的所有数据，和对应的部门信息
SELECT  e.*,d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;

右外连接：查询右表所有数据，以及两张表交集部分数据
相当于查询表2的所有数据包含表1和表2交际部分的数据

-- 语法：
select 字段列表 from 表1 right [outer] join 表2 on 条件；
-- 例子：
-- 查询部门表的所有数据，和对应的员工信息
SELECT  d.*,e.*FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

3.1.3、自连接和联合查询（union）

1、自连接

--语法:
select 字段列表 from 表A 别名A join 表名A 别名B on 条件……;
--例子
select a.name '员工', b.name '领导' from emp a join emp b on a.managerid = b.id;

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

--语法：
select 字段列表 from 表A …… 
union [all]
select 字段列表 from 表B ……;

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

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

3.2、子查询(嵌套select语句)

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

--语法：
select * from t1 where column1 = (select column1 from t2);

1、标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单地形式，这种子查询称为标量子查询
常用的操作符：= <> > >= < <=

# a. 查询”销售部“部门ID
select id from dept where name = '销售部';

# b. 根据销售部门ID，查询员工信息
select * from emp where dept_id = 4;
select * from emp where dept_id = (select id from dept where name = '销售部');

2、列子查询
子查询返回的结果是一列（可以是多行），这种查询称为列子查询

常用的操作符： in、 not in、 any 、some 、all

# 查询 销售部 和 市场部 的部门ID
select id from dept where name = '销售部' or name = '市场部';
# 根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '市场部' or name = '销售部');

3、行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、 <> 、 in、 not in

# 行子查询
# 查询 张无忌 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name = '张无忌';

# 查询与 张无忌 的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

4、表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：in

# 表子查询
# 查询 鹿杖客、宋远桥 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

# 查询与 鹿杖客、宋远桥 的职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');