【MySQL系列】操作手册

MySQL操作手册

分类	描述	关键字
DDL	Data Definition Language 【数据定义语言】 定义数据库对象：数据库，表，列	create，drop，alter
DML	Data Manipulation Language【数据操作语言】 操作表中数据：增，删，改	insert，delete，update
DQL	Data Query Language【数据查询语言】 查询表中数据，对数据库的操作最多就是查询	select…from…
DCL	Data Control Language【数据控制语言】 定义数据库的访问权限和安全级别，及创建用户	grant，revoke
TCL	Transaction Control Language【事务控制语言】 控制数据库的事务操作	commit，rollback，savepoint

一个数据库 可以有 多张表，一张表 可以有 多条记录，记录 = 表结构 + 表数据

操作	增	删	改	查
数据库（database）	create	drop	alter	show
表（table）	create	drop	alter	show
表结构（字段）	alter…add	alter…drop	alter…modify/change	/
表数据（数据）	insert into	delete from	update…set…	select…from

一、Database 操作

1、创建数据库

-- 直接创建数据库db1
create database db1;

-- 判断如果不存在则创建数据库db2
create database if not exists db2;

-- 创建数据库db3并指定字符集为gbk
create database db3 character set gbk;

-- 关键字用 `` 
create database `create`;

2、使用数据库

-- 查看正在使用的数据库
select database();

-- 使用/切换数据库
use db2;

3、查看数据库

-- 查看正在使用的数据库
select database();

-- 查看所有的数据库
show databases;

-- 查看某个数据库的定义信息
show create database db3;

4、修改数据库（了解）

-- 将db3数据库的字符集改成utf8
alter database db3 character set utf8;

Tips：sql语句不识别-，utf-8 要写成 utf8，iso-8859-1 要写成 latin1

二、Table 操作

1、创建表

-- 创建student表包含id，name，sex，birthday字段
create table student(
    id int,
    name varchar(20),
    sex char(1),
    birthday date    
);

-- 创建一个student2表，结构与student相同
create table student2 like student;

2、查看表

-- 显示所有表
show tables;

-- 查看数据表表结构
desc student;

-- 查看数据表的创建信息
show create table student;

3、修改表结构（了解）

语法：ALTER TABLE 表名 (ADD、MODIFY、change、DROP) 字段名 数据类型(长度)

-- 新增字段
alter table student add remark varchar(20);

-- 修改字段
alter table student modify remark varchar(100);

-- 修改字段名（remark -> intro）
alter table student change remark intro varchar(20);

-- 删除字段
alter table student drop intro; 

-- 修改表名
rename student to student2;

-- 修改表的编码
alter table student character set gbk;

4、删除表 - drop

-- 直接删除表s1表
drop table s1;

-- 判断表如果存在就删除s1表
drop table if exists s1;

三、Data 操作 - 增删改

1、新增 - insert

-- 准备表
CREATE TABLE student (
    id INT,
    sex CHAR(2),
    NAME VARCHAR(20), 
    birthday DATE
);

-- 全写形式：插入所有数据，所有的字段名都写出来
insert into stu (id,sex,name,birthday) values (1,'男','tom','2020-05-20');

-- 省略全部字段：插入所有数据，可以不写字段名
insert into stu values (2,'男','汤姆','2020-05-21');

-- 省略部分字段：插入部分数据，需要指定字段名
insert into stu (name,sex) values ('柳岩','女');

-- 批量插入
insert into student(id,name,birthday,sex) values
(4,'ml','1998-01-01','男'),
(5,'qq','1999-01-01','女');

表的备份 & 蠕虫复制

-- 表结构的复制
create table s2 like student;
-- 表数据的复制
insert into s2 select * from student;
-- 蠕虫复制（不断复制自身）
insert into s2 select * from s2;

2、修改 - update

-- 修改表中的性别为'女'
update stu set sex='女'; 

-- 将表中id为1的性别改为男
update stu set sex='男' where id=1;

-- 修改多个列，用逗号分隔
update student set name='hehe', address = '北京' where id=3;

3、删除 - delete/truncate

-- 删除id为2的学生信息
delete from stu where id=2;

-- 删除整张表中的所有数据
delete from stu;
truncate table stu;

delete 和 truncate 删除的区别

• delete
  • 将表中的数据一条一条删除，实际上没有删除表中数据的所有信息
  • 数据可能恢复（可以理解成隐藏）
  • 效率比较低
  • 属于DML语句
• truncate
  • 将整个表摧毁，重新创建一个新的表，新的表结构和原来表结构一模一样
  • 数据不可恢复
  • 效率比较高
  • 属于DDL语句

四、Data 操作 - 查询

1、基本查询

-- 创建商品表
CREATE TABLE product(
	pid INT,
	pname VARCHAR(20),
	price DOUBLE,
	category_id VARCHAR(32)
);

INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

1）基本语法 - select

-- 查询所有商品（*表示查询所有列，不推荐，推荐一一列举）
select * from product;

-- 查询商品的名称和价格
select pname, price from product;

2）别名查询 - as

【表别名】

-- 表名.字段名: 某张表的某个字段
select product.pname, product.price from product;
-- 如果表只有一张，表名可以省略
select pname, price from product;
-- 使用关键字as取别名
select p.pname, p.price from product as p;
-- as可以省略
select p.pname, p.price from product p;

-- 注意: 一旦取了别名，原名不可用
select product.pname, product.price from product as p; 	-- error

【列别名】

-- 完整写法
select product.pname as '商品名', product.price as '商品价格' from product;
-- as 后的字符串可以不加引号
select product.pname as 商品名, product.price as 商品价格 from product;
-- as可以省略
select product.pname 商品名, product.price 商品价格 from product; 

3）去重查询 - distinct

-- 查看商品表中有哪些价格
select price from product; 			 -- 有重复的, 结果20条
select distinct price from product;  -- 过滤重复, 结果10条

4）计算查询

-- 将所有商品的价格+10元进行显示
select pname, price+10 newprice from product;
-- 通过ifnull，将null值转化为0再参与运算
select pname, ifnull(price,0) + 10 newprice from product;	

2、条件查询

-- 创建学生表
CREATE TABLE student (
    id INT,
    NAME VARCHAR(20),
    age INT,
    sex VARCHAR(5),
    address VARCHAR(100),
    math INT,
    english INT
);

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

1）比较运算符

数值: 
	小于<   大于>   等于=   小于等于<=   大于等于>=    不等于<>，!= 
null:
	is null 或 is not null
函数:
	ifnull(字段, 默认值)  如果字段值为null，则取默认值，否则取字段值

-- 数学成绩大于80分的学生
select * from student where math > 80;

-- 英语成绩小于等于80分的学生（不计null）
select name, english from student where english <= 80;

-- 英语成绩小于或等于80分的学生（null缺考，记作0）
select name, ifnull(english, 0) eng from student where ifnull(english, 0) <= 80;

-- age等于20岁的学生
select * from student where age = 20;

-- age不等于20岁的学生
select * from student where age != 20;
select * from student where age <> 20;

-- 英语成绩为null的同学（缺考）
select * from student where english is null;	
select * from student where english is not null;	

-- english + 10
select name, english+10 from student;	
select name, ifnull(english, 0) + 10 newEng from student;

2）逻辑运算符

与或非：
	and  or  not
in：
	多个or时使用 (in里面的每个数据都会作为一次条件，只要满足条件的就会显示)
between：
	在一个范围内，between a and b （包含a和b，b>a）

-- age大于35 且 性别为男（两个条件同时满足）
select * from student where age > 35 and sex = '男';

-- age大于35 或 性别为男（两个条件其中一个满足）
select * from student where age > 35 or sex = '男';

-- id是1或3或5
select * from student where id=1 or id=3 or id=5;
select * from student where id in (1, 3, 5);

-- 英语成绩 大于等于75 且 小于等于90
select * from student where english >= 75 and english <= 90;
select * from student where english between 75 and 90;

3）模糊查询

like：
	%  表示0个或多个
	_  表示1个

-- 姓马的学生
select * from student where name like '马%';
-- 姓名中包含'德'的学生
select * from student where name like '%德%';
-- 姓马 且 姓名有三个字 的学生
select * from student where name like '马__';

3、排序查询 - sort

order by
	desc - descending 降序
    asc  - ascending  升序（默认）

-- 查询所有数据，按年龄排序
select * from student order by age desc; -- 降序
select * from student order by age asc;  -- 升序
select * from student order by age;   	 -- 默认升序

-- 查询所有数据，按年龄降序排列，如果年龄相同，以数学成绩降序排列（多个排序条件以逗号分隔）
select * from student order by age desc, math desc;

4、分页查询 - limit

1）limit语法

limit：
	-- limit offset(index), count;
        -- offset : 偏移量/索引 (从0开始)
        -- count  : 最大的查询数量	

-- 查询学生表中数据，跳过前面2条，显示6条
select * from student limit 2, 6;

-- 查询前5条数据
select * from student limit 0,5;
select * from student limit 5; 	   -- 当offset=0时,可以省略

-- 共14条数据
select * from student limit 10, 7;  -- 只返回4条（不会溢出）
select * from student limit 20, 7;  -- 没有就返回null（不会索引越界）

-- 查询第1条数据
select * from student limit 1;

2）分页查询

分页查询：每次只查询某一页数据，每页显示特定的数量

# 查询第page页，每页显示pageSize条数据
select * from student (page-1) * pageSize, pageSize;

-- 第一页: 获取5条（1~5）
select * from student limit 0, 5;
-- 第二页: 获取5条（6~10）
select * from student limit 5, 5;
-- 第三页: 获取5条（11~15）
select * from student limit 10, 5;

5、聚合函数

聚合：
	一组值进行运算，最终得出一个结果
函数：
	就是方法（sql、javascript中叫函数，java中叫方法）
语法：
	SELECT 聚合函数(字段) FROM 表名;

常见聚合函数：
    -- 求和  sum(字段参数)
    -- 平均  avg(字段参数) 
    -- 最值  max/min(字段参数) 
    -- 个数  count(字段参数) 
    
注意事项：
	a. null不是值，不会被列入运算
	b. where先于聚合执行  having后于聚合执行
	c. 聚合函数不能用在where之中，可以用在聚合之后的sql语句中

-- 查询学生总数
select count(*) from student;		-- 8  所有字段中，只要有一个有值，就会被统计 	
select count(english) from student; -- 7  有一个null，没有计入内
select count(id) from student; 		-- 8  写非空的字段也可以

-- 查询年龄大于40的总数
select count(*) from student where age > 40;

-- 查询数学成绩总分、最低分、最高分
select sum(math) from student;
select max(math) from student;
select min(math) from student;

-- 查询英语成绩平均分（不计null）
select sum(english)/count(english) 英语平均分 from student;  -- 81.4286
select avg(english) 英语平均分 from student; 				-- 81.4286

-- 查询英语成绩平均分（包含null）
select sum(english)/count(id) 英语平均分 from student; -- 71.2500
select avg(ifnull(english, 0)) 英语平均分 from student; -- 71.2500

6、分组查询 - group by

group by：
	1）被分组字段有几类，最终结果集中就有几条（每一类只会显示首条结果）
		-- 如sex有男，女两类，结果集就只有2条
	2）分组查询中，select之后跟【与分组字段一对一关系的字段】和【聚合函数】
		-- 因为只会取首条结果，如果不是一对一的关系，没有意义
	3）和聚合函数一起使用：
		-- 无分组，聚合函数将表中所有符合条件的数据当成一组。
		-- 有分组，聚合在分组之后执行，对每一组数据进行聚合

-- 查询男女各多少人
select sex, count(*) from student group by sex;

-- 查询年龄大于25岁的男女各多少人
select sex, count(*) from student where age > 25 group by sex;	

-- 查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于1的数据，并降序输出
select sex, count(*) from student
where age > 25      
group by sex
having count(*) > 1			-- 聚合函数不能用在where中，只能用在having中
order by count(*) desc;	

五、Data 操作 - 关联查询

-- 创建部门表
CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES 
('开发部'),
('市场部'),
('财务部');

-- 创建员工表
CREATE TABLE emp ( 
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    gender CHAR(1),
    salary DOUBLE,
    join_date DATE,
    dept_id INT
);
INSERT INTO emp(NAME, gender, salary, join_date, dept_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);

1、笛卡尔积

# 多表查询
	select * from A表, B表...;
# 交叉查询：会产生笛卡尔积
	select * from emp cross join dept; 
# 笛卡尔积：
	两张表数据的乘积
# 笛卡尔积的消除
	从表.外键 = 主表.主键

-- 查询员工表和部门表 【产生笛卡尔积】
select * from emp, dept;

-- 查询员工表和部门表 【消除笛卡尔积】
select * from emp e, dept d where e.dept_id = d.id;	

-- 如果多表查询的结果中字段唯一，可以不指定表名（推荐指定）
select `salary` from emp e, dept d where e.dept_id = d.id;	

-- 如果多表查询的结果中有多个同名字段，必须指定表名（不然会报错）
select `NAME` from emp e, dept d where e.dept_id = d.id;	

2、内连接 - inner join

-- 效果: 
	求两张表的交集（消除笛卡尔积 -> 从表.外键 = 主表.主键）
	
-- 隐式内连接：
	select 列名 from 左表, 右表 where 从表.外键 = 主表.主键

-- 显示内连接：（on + 连接条件） 【推荐】
	select 列名 from 左表 inner join 右表 on 从表.外键 = 主表.主键

-- 隐式内连接
select e.name, e.salary, d.name 
from emp e, dept d
where e.dept_id = d.id and e.name = '唐僧';

-- 显式内连接【推荐】 
select e.name, e.salary, d.name 
from emp e inner join dept d
on e.dept_id = d.id
where e.name = '唐僧';

3、左外连接 - left join

-- 效果
	左表不动，并上右表与之交集的部分, 如果右表没有对应的数据，使用NULL填充。
	
-- 语法
	select 列名 from 左表 left join 右表 on 从表.外键 = 主表.主键

-- 添加一个销售部，暂时还没有员工
insert into dept (name) values ('销售部');

-- 使用内连接查询，缺少销售部
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用左连接查询，有销售部，销售部员工为null
select * from dept d left join emp e on d.id = e.dept_id;

4、右外连接 - right join

-- 效果
	右表不动，并上左表与之交集的部分, 如果右表没有对应的数据，使用NULL填充。
	
-- 语法
	select 列名 from 左表 right join 右表 on 从表.外键 = 主表.主键

-- 在员工表中增加一个员工，还没分配部门
insert into emp values(null,'沙僧','男',6666,'2013-02-24',null);

-- 使用内连接查询，缺少沙僧
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用右外连接查询，有沙僧，沙僧的部门为null
select * from dept d right join emp e on d.id = e.dept_id;

5、全连接 - union

-- 效果：
	左右表的数据都不动, 并上交集数据。

-- oracle语法（mysql不支持）
	select 列名 from 左表 full outer join 右表 on 条件

-- mysql语法 - union（去重）
	select * from dept d left join emp e on e.dept_id = d.id
	union
	select * from dept d right join emp e on e.dept_id = d.id;

-- mysql语法 - union all（不去重）
	select * from dept d left join emp e on e.dept_id = d.id
	union all
	select * from dept d right join emp e on e.dept_id = d.id;

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，尽量使用UNION ALL语句，以提高数据查询的效率。

六、Data 操作 - 子查询

1、子查询定义

# 子查询定义
	a语句是b语句的一部分

# 主查询/外层查询 main query
	可以是select、update、delete等语句

# 子查询/内层查询 sub query
	可以写在 select、from、where、having、exists 之后，最常见在where之后

2、子查询分类

# 按结果集的行列数不同
1. 标量子查询
		返回的结果是一条数据，搭配 > < >= <= = <> 使用
		select * from article where uid = (select uid from blog where bid = 1);
2. 列子查询
		返回的结果是一列（N行1列）  搭配 in、any、some、all 使用
		select * from article where uid in (select uid from blog where bid in(1,2));
3. 行子查询
		返回的结果是一行（1行N列）
		select * from article where (title, content) = (select title, content from blog where bid = 1);
4. 表子查询
		返回的结果是一张表（N行N列）
		select * from article where (title, content) in (SELECT title, content from blog);	

# 按子查询出现的位置
1. select之后（少见）
		只支持标量子查询，子查询的结果直接出现在结果集中
2. from之后（有用）
		支持表子查询
3. where、having之后（重要）
		a. 标量子查询 - 常见
		b. 列子查询 - 常见
		c. 行子查询
4. exists之后（有用）
    	都支持，一般是表子查询

# 按关联性
1. 非相关子查询
	a. 含义: 独立于外部查询的子查询 (子查询可以独立运行)
	b. 执行: 子查询优先于主查询执行，并且只执行一次，执行完将结果传递给外部查询
	c. 效率: 较高
	d. 举例: 
			select * from A where A.id in (select  id  from  B)

2. 相关子查询
	a. 含义: 依赖于外部查询的数据的子查询
	b. 执行: 子查询和外部查询交叉执行，外部查询每执行一行，子查询执行一次
	c. 解释: 子查询中查询条件依赖于外层查询中的某个值，子查询要反复求值，以供外层查询使用。
	d. 效率: 较低
	e. 举例: 
			select * from emp e1 
			where exists (select * from emp e2 where e1.empno=e2.mgr);

3、select之后

-- 查询每个部门id、name和对应的员工数
select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) as 员工数
from dept d;

# select之后（相关子查询）
	1. 主查询执行一次 -> select id, name from dept;
	2. 子查询执行一次 -> select count(*) from emp where e.dept_id = 1；
	3. 后续执行同理，主查询和子查询交叉进行	

4、from之后

-- 查询每个部门id、name和对应的员工数
select d.id, d.name, temp.员工数
from (select dept_id, count(*) 员工数 from emp group by dept_id) as temp
inner join dept d
on temp.dept_id = d.id;

# from之后（表子查询）
1. 特点：
		将子查询的结果作为一张临时表，和另一张表连接，再进行连接查询
2. 要求：
		1. 子查询必须起别名（一般为temp，表示临时表）
		2. 若子查询中使用了聚合函数，必须取别名，否则外部语句引用时会报错
		   例如：不能直接写 temp.count(*)，语法不允许

5、where/having之后

【标量子查询】

-- 查询工资最高的员工是谁？ 
select * from emp where salary = (select max(salary) from emp);

-- 查询工资小于平均工资的员工有哪些？
select * from emp where salary < (select avg(salary) from emp);

-- 查询 部门平均工资 超过 全公司平均工资的 部门id和部门平均工资
select dept_id, avg(salary) from emp 
group by dept_id
having avg(salary) > (select avg(salary) from emp);

【列子查询】

-- 查询工资最高的员工是谁？ 
	-- all关键字
	select * from emp where salary >= all (select salary from emp);

-- 查询工资大于5000的员工，来自于哪些部门
	-- in关键字
	select name from dept where id in (select dept_id from emp where salary > 5000);
	-- any关键字
	select name from dept where id any (select dept_id from emp where salary > 5000);
	-- some关键字
	select name from dept where id some (select dept_id from emp where salary > 5000);
	
-- 查询开发部与财务部所有的员工信息
select * from emp where dept_id in (select id from dept where name in ('开发部','财务部'));

6、exist之后

# exists之后（相关子查询）
1. 语法：
		exists(完整的查询语句);
2. 结果：
		0/1 (false/true)
3. 理解：
		当成if来看, exists小括号中的内容是null，返回0，否则返回1

-- select之后，返回0或1
select exists (select * from emp where salary > 1000); 	 -- 返回1
select exists (select * from emp where salary > 10000);  -- 返回0

-- where之后，控制结果的显示
select * from emp where 1; -- 条件为1，显示结果
select * from emp where exists (select * from emp where salary > 1000);

select * from emp where 0;	-- 条件为0，不显示结果
select * from emp where exists (select * from emp where salary > 10000);

-- 查询工资大于5000的员工，来自于哪些部门
select name from dept d
where exists (select * from emp e where e.salary > 5000 and e.dept_id = d.id);

-- 执行顺序
	-- 主查询结果: 1,2,3
	-- 第一次执行:
		-- a. 先执行主查询: id=1, name=开发部
		-- b. 接着执行子查询: 满足条件的数据不为null，返回1
		-- c. 子查询返回1，主查询就保留当前行记录
	-- 第二次执行: 返回1，会保留 name=市场部
	-- 第三次执行: 返回0，不保留 name=财务部

7、exist 和 in

-- exists和in的区别
	-- in 后面一般直接跟 非相关子查询（子查询可单独运行。子查询执行完毕，再执行主查询）
	-- exists 后面一般都要跟 相关子查询 (子查询和主查询交叉执行，主查询查询一条，子查询执行一次)
	
-- exists和in的效率哪个高? 视情况而定
	-- 其他条件相同，看结果集数量 (有索引)
	-- 1. 主查询 > 子查询，用in
	-- 2. 主查询 < 子查询，用exists	

七、函数操作

01、字符串函数

注意：MySQL中，字符串的位置是从1开始的。

函数	描述
char_length(s)	返回字符串 s 的字符数
concat(s1,s2...sn)	字符串 s1, s2 等多个字符串合并为一个字符串
lower(s)	将字符串 s 的所有字母变成小写字母
upper(s)	将字符串转换为大写
substr(s, start,length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
trim(s)	去掉字符串 s 开始和结尾处的空格

示例:

select char_length('love') as '长度';
--执行结果为: 4

select concat('i','love','you');
--执行结果为: iloveyou

select lower('LOVE');
--执行结果为: love

select upper("love");
--执行结果为: LOVE

select substr("love", 1, 2);
--执行结果为: lo

select trim(' love ');
--执行结果为: 'love'

02、数字函数

函数	描述
rand()	返回 0 到 1 的随机数
round(小数, 保留位数)	四舍五入保留几位小数
least(expr1, expr2, expr3, ...)	返回列表中的最小值
greatest(expr1, expr2, expr3, ...)	返回列表中的最大值

示例:

select rand();	
-- 返回0-1之间的随机数 0.21809973867433122

select round(3.1415926, 2) ; 
-- 执行结果: 3.14

select least(13, 14, 521, 74, 1);	
-- 执行结果: 1

select greatest(13, 14, 521, 74, 1); 
-- 执行结果: 521

03、日期函数

函数名	描述
now() 和 sysdate()	返回系统的当前日期和时间
curdate()	返回当前日期
curtime()	返回当前系统时间
year(d)	返回d的中的年份
month(d)	返回d的中的月份
day(d)	返回d中的日
date_format(日期，格式)	日期格式化

示例:

select now(); 			-- 返回系统的当前时间: 年-月-日 时:分:秒 
select sysdate(); 		-- 返回系统的当前时间: 年-月-日 时:分:秒 
select curdate(); 		-- 返回系统当前日期: 年-月-日
select curtime(); 		-- 返回系统当前时间: 时:分:秒
select year(now()); 	-- 返回当前日期中的年份
select month(now()); 	-- 返回当前日期中的月份
select day(now()); 		-- 返回当前日期中的日

日期格式化函数：date_format(date, format) 的使用

-- 字符串转为日期格式
select date_format('2021-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
-- 日期转为字符串格式
select date_format(now(), '%Y-%m-%d %H:%i:%s')

MySQL中，date_format(date, format) 函数的 format 格式如下

%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 数字, 4 位 
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat) 
%d 月份中的天数, 数字(00……31) 
%e 月份中的天数, 数字(0……31) 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%j 一年中的天数(001……366) 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 这里星期天是星期的第一天 
%u 星期(0……52), 这里星期一是星期的第一天 
%% 一个文字“%”。

04、流程控制函数

函数名	描述
ifnull(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。
isnull(expr)	如果 expr 的值不为 NULL，则返回 0，否则返回 1。
if(expr,v1,v2)	如果 expr 是 true 则返回 v1，否则返回 v2

示例:

select ifnull(null, 'Hello Word')
-- 将null值替换为 Hello Word

select isnull(NULL);
-- 执行结果: null返回1，非null返回0

Case函数：只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

-- 简单Case函数（相当于 switch...case...）
CASE sex
	WHEN '1' THEN '男'
	WHEN '2' THEN '女'
ELSE '其他' END

-- Case搜索函数（相当于 if...else...）
CASE 
	WHEN sex = '1' THEN '男'
    WHEN sex = '2' THEN '女'
ELSE '其他' END

05、cast()

函数名	描述
cast(expression AS data_type)	将某种数据类型的表达式显式转换为另一种数据类型。 用AS关键字分隔的源值和目标数据类型。

• expression：任何有效的SQServer表达式。
• AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
• data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

SIGNED 	 : 整数 
UNSIGNED : 无符号整数
DECIMAL	 : 浮点数

CHAR() : 字符型，可带参数
BINARY : 二进制，同带binary前缀的效果

DATE     : 日期 
TIME     : 时间
DATETIME : 日期时间型

示例:

select cast('9.0' as decimal);
-- 执行结果: 9

select cast('9.5' as decimal(10,2))
-- 执行结果: 9.50

select now()
-- 执行结果: 2021-04-17 12:46:59

select cast(now() as date)
-- 执行结果: 2021-04-17 

select cast(now() as time)
-- 执行结果: 12:46:59

select cast(now() as datetime)
-- 执行结果: 2021-04-17 12:46:59

06、concat 相关函数

1）concat()

• 功能：将多个字符串拼接成一个字符串。
  • 拼接结果为连接参数产生的字符串，如果有任何一个参数为null，则拼接结果为null。
• 语法：concat(str1, str2,...)

select concat(id, username, sex) from `user`;
select concat(id, ',', username, ',', sex) from `user`;

2）concat_ws()

• 功能：将多个字符串连接成一个字符串，但是可以指定分隔符（concat with separator）
• 语法：concat_ws(separator, str1, str2, ...)
  • 第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则拼接结果为null。

select concat_ws(',', id, username, sex) from `user`;

3）group_concat()

• 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
• 语法：group_concat ( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
  • 使用distinct可以排除重复值；
  • 使用order by可以对一个分组中的值进行排序
  • separator是一个字符串值，缺省为一个逗号。

select age, group_concat(id) from `user` group by age;
select age, group_concat(id order by id desc separator '_') from `user` group by age;

-- 先拼接同一分组中的每行数据的多个字段，再拼接同一分组
select age, group_concat(concat_ws('-',id,sex) order by sex) from `user` group by age;

-- 在同一分组中的每行数据的拼接结果后加上&
select age, group_concat(concat_ws('-',id,sex), '&') from `user` group by age;

八、索引操作

1、创建索引

【直接创建】

-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2);
-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2);

【修改表时指定】

-- 添加主键索引（唯一且不为Null）
alter table 表名 add primary key(字段);
-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique 索引名(字段);
-- 添加普通索引
alter table 表名 add index 索引名(字段); 
-- 添加全文索引
alter table 表名 add fulltext 索引名(字段);

【创建表时指定】

CREATE TABLE teacher (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
    `name` VARCHAR(32),
    telephone VARCHAR(11) UNIQUE, 		-- 唯一索引
    sex VARCHAR(5),
    birthday DATE,
    student_id INT,
    INDEX(`name`), 						-- 普通索引
    CONSTRAINT fk_01 FOREIGN KEY (student_id) REFERENCES student(id) -- 外键索引
);

-- 备注: 如果不指定索引名称，默认为建立索引的字段名

2、删除索引

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

-- 修改表时删除 【掌握】
alter table 表名 drop index 索引名;

3、查看索引

show index from 表名; 

# 查询结果说明
1. table : 表
2. Non_unique: 是否唯一（0表示true，1表示false）
3. Key_name : 索引的名字
4. Column_name : 索引所在的列
5. Null : 是否允许为空（YES表示允许，空表示不允许）
6. Index_type: 索引的数据结构类型

九、Json操作

create table test_json (
    id INT,
    json_field JSON
);

1、Json对象操作

-- 对象
insert into test_json (id, json_field) values (1, '{"name": "John", "age": 18}');

-- 嵌套对象
insert into test_json (id, json_field) values 
(2, '{"age": 18, "name": "John", "address": {"city": "上海", "country": "中国"}}');

-- 数组
insert into test_json (id, json_field) values 
(3, '{"age": 18, "name": "John", "friend": ["Lucy", "Jack", "Tom"]}');

1）添加元素

使用 json_set 或 json_insert 函数来 添加新元素 到 JSON 对象或数组中。

update test_json set json_field = json_set(json_field, '$.city', '上海');
update test_json set json_field = json_insert(json_field, '$.sex', '男');

2）删除元素

使用 json_remove 函数来 删除 JSON 对象或数组中的元素。

update test_json set json_field = json_remove(json_field, '$.city');

3）修改元素

使用 json_set 或 json_replace 函数来 更新 JSON 对象或数组中的现有值。

update test_json set json_field = json_set(json_field, '$.sex', '女');
update test_json set json_field = json_replace(json_field, '$.age', 28);

4）查询元素

使用 json_extract 函数从 JSON 数据中提取指定路径的值

# "John"
select json_extract(json_field, '$.name') from test_json;
# 18
select json_extract(json_field, '$.age') from test_json;

使用 -> 或 ->> 操作符从 JSON 字符串中提取值。

• -> 返回 JSON 对象或数组中的属性。
• ->> 返回 JSON 对象或数组中的属性的字符串值。

select json_field->'$.name' as name from test_json;		# "John"
select json_field->>'$.name' as name from test_json;	# John

select json_field->'$.age' as name from test_json;		# 18
select json_field->>'$.age' as name from test_json;		# 18

总之，-> 和 ->> 操作符用于不同的用途，前者返回 JSON 类型，后者返回字符串类型。你可以根据需要来选择使用哪个操作符。如果你需要进一步操作 JSON 数据的子元素，通常使用 ->，如果你只需要获取值作为字符串，可以使用 ->>。

2、Json嵌套对象操作

使用 json_array 在 JSON对象 中添加 嵌套对象

update test_json set json_field = 
json_set(json_field, '$.address', json_object('country', '中国', 'city', '上海'));

1）添加元素

update test_json set json_field = json_set(json_field, '$.address.province', '江苏省');

3）删除元素

update test_json set json_field = json_remove(json_field, '$.address.country');

4）修改元素

update test_json set json_field = json_set(json_field, '$.address.city', '南京');

5）查询元素

select json_extract(json_field, '$.address') from test_json;
select json_extract(json_field, '$.address.city') from test_json;

3、Json数组操作

使用 json_array 在 JSON对象 中添加 数组

update test_json set json_field = json_set(json_field, '$.friend', json_array('Jack', 'Tom'));

1）添加元素

使用 json_array_append 函数将一个元素添加到 JSON 数组的末尾。

update test_json set json_field = json_array_append(json_field, '$.friend', 'Roce');

使用 json_array_insert 函数将一个元素插入到 JSON 数组的指定位置（索引从0开始）

update test_json set json_field = json_array_insert(json_field, '$.friend[0]', 'Mark');

2）删除元素

-- 索引从0开始
update test_json set json_field = json_remove(json_field, '$.friend[0]');

3）修改元素

-- 索引从0开始
update test_json set json_field = json_set(json_field, '$.friend[0]', 'Lucy');

4）查询元素

-- 索引从0开始
select json_extract(json_field, '$.friend[0]') from test_json;

4、其他操作

1）json_keys

返回 JSON 对象中的所有键作为 JSON 数组

select json_keys(json_field) from test_json;	# ["age", "name", "address"]

2）json_length

使用 json_length 函数判断 JSON 对象 键的数量 或 数组的长度

-- key的数量
select json_length(json_field) from test_json;

-- 数组长度
select json_length(json_field, '$.friend') from test_json;

3）json_contains

使用 json_contains 函数判断 JSON 对象 是否包含指定的 JSON 值

-- 是否包含特定值
select json_contains(json_field, '"John"', '$.name') from test_json;
select json_contains(json_field, '"上海"', '$.address.city') from test_json;

-- 是否包含特定json子集
select json_contains(json_field, '{"name": "John"}', '$') from test_json;
select json_contains(json_field, '{"city": "上海", "country": "中国"}', '$.address') from test_json;

-- 是否包含特定键和值的组合
select json_contains(json_field, '{"name": "John", "age": 18}', '$') from test_json;

4）json_contains_path

使用 json_contains_path 函数判断 JSON 对象 是否包含指定的 JSON 路径

• 'one'：表示只需要找到一个匹配的路径，如果找到任何一个匹配的路径，函数就会返回 1。
• 'all'：表示需要找到所有匹配的路径，只有当所有匹配的路径都被找到时，函数才会返回 1。

select json_contains_path(json_field, 'one', '$.age') from test_json;
select json_contains_path(json_field, 'all', '$.name', '$.age') from test_json;

十、其他操作

# 查询mysql版本
select version();

# 查询缓存相关配置
show variables like '%cache%';

# 不走缓存查询
select sql_no_cache count(*) from users; 

# 走缓存查询
select sql_cache count(*) from users;