-- 语法 update 表名 set 列名 = 值; -- 将所有人年龄修改成20岁 update student set stu_age = 20;
单个修改
1 2 3 4 5 6
-- 语法 update 表名 set 列1=值1, 列2=值2... where 匹配条件 -- 给id是3的用户年龄修改成50岁，爱好修改为 唱跳rap篮球 update student set stu_age = 50, stu_hobby = '唱、跳、rap、篮球' where id = 3; -- 给name是阿拉斯加的学生姓名修改成张三 update student set stu_name='张三' where stu_name='阿拉斯加';
删除数据
删除单个
1 2 3 4 5 6
-- 语法 delete from 表名 where 条件; -- 删除id是1的学生 delete from student where id = 1; -- 删除所有性别是男的学生 delete from student where student_sex = '男';
-- 有时候查询出来的结果有重复数据，此时可以使用去重 select distinct 查询字段 from 表名; -- sql演示 select distinct manager_id from emp;
排序查询
1 2 3 4 5
-- 语法 正常的查询语法 order by 排序字段 asc/desc; -- 演示，根据工资排序 select * from emp order by sal desc; 多个用逗号隔开
分页查询
1 2
-- 语法 select * from 表名 limit n1, n2;
n1：表示偏移量，即跳过前面多少条数据开始查询
n2：表示查询多少行
n1=(页数-1) * 每页条数
1 2 3 4 5 6
-- 查询第1页，每页3条 select * from emp limit 0, 3; -- 查询第2也，每页3条 select * from emp limit 3, 3; -- 查询工资最高的三个员工 select * from emp order by sal desc limit 3;
合并查询
将多个select语句的查询结果合并成一个查询结果
union all：不去重合并
union：去重合并
1 2 3 4
-- 演示 select * from emp where sal > 2000 union select * from emp where sal < 2500;
where子句
作用：指定条件匹配对应的数据，可以应用于select、update、delete
select * from emp where sal > 2000;
where 中使用比较运算符
MySQL中支持以下比较运算符 >、>=、<、<=、!=、=（没有双等号）、<>（不等于）
此外还支持以下的关键字
运算符
含义
between A and B
查询A和B之间的数据（包含边界）
in(collecton)
查询出现在collection集合中的数据
like
模糊匹配
is null
为空
not like
模糊匹配取反
not in(collection)
查询没有出现在集合中的数据
is not null
不为空
1 2 3 4 5 6 7 8
-- 查询工资大于3000的数据 select * from emp where sal > 3000; -- 查询没有领导的数据 select * from emp where manager_id is null; -- 查询有奖金的员工，并根据奖金排序 select * from emp where comm is not null order by comm desc; -- 查询领导id是7902、7698、7782的员工信息 select * from emp where manager_id in (7902,7698,7782);
使用like关键字进行模糊匹配
1 2
-- 语法 like '_或%模糊匹配的字符_或%'
_的作用是匹配一个字符，%的作用是匹配0-多个字符
1 2 3 4 5 6
-- 查询所有名字是A开头的员工 select * from emp where emp_name like 'A%'; -- 查询名字中含有T的员工 select * from emp where emp_name like '%T%'; -- 查询名字S开头，且名字是5个字母的员工 select * from emp where emp_name like 'S____';
_可以精确匹配制定个数的字符，%则只能模糊匹配，符合条件就匹配到
查询使用逻辑运算符
与：and，或：or，非：not
1 2 3 4
-- 查询dept_no等于10且工资大于2000的数据 select * from emp where dept_no = 10 and sal > 2000; -- 查询有奖金或者工资大于等于3000的员工 select * from emp where comm is not null or sal >= 3000;
and和or在使用时有一些小细节
and：把检索结果较少的条件放到后面
or：把检索结果较多的条件放到后面
到这里为止，总结一下目前学习到的sql查询语法
1 2
select 查询字段 from 表名 [where 查询子句] [order by 排序字段] [limit n1, n2];
date_add(date, interval expr type)、date_add()，返回将起始时间加上 expr type 之后的时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
-- 查询当前日期 select curdate(); -- 查询当前时期时间 select now(); -- 查询每个员工入职的月份 select emp_name, month(workdate) from emp; -- 查询每个员工入职的周 select emp_name, week(workdate) from emp; -- 查询每个员工入职的天 select emp_name, dayofyear(workdate) from emp; -- 演示extract select emp_name, extract(year_month from workdate) from emp; -- 查询每个员工入职了多少年 select emp_name, workdate, timestampdiff(year, workdate, now()) as d from emp; -- 将每个员工入职时间加3年 select emp_name, workdate, date_add(workdate, interval 3 year) as d from emp;
条件判断函数
if(expr, v1, v2) 如果表达式expr为true，则返回v1，否则返回v2
ifnull(v1, v2) 如果v1的值不为空，就返回v1，否则返回v2
case expr when v1 then r1 when v2 then r2 else rn end，类似于if…else if …else
1 2 3 4 5 6 7 8 9 10 11 12 13
-- 如果奖金是空，就返回0 select *, ifnull(comm, 0) from emp; -- 如果工资大于等于3000，有一个半月的年终奖 select *, if(sal >= 3000, sal*1.5, 0) as m from emp; -- 判断dept_no，如果是10，则为技术部，如果是20，则为运营部，否则则为销售部 select *, (case dept_no when 10 then '技术部' when 20 then '运营部' else '销售部' end ) as dept_name from emp;
其他函数
version() 查看当前MySQL版本号
password(s) 将s加密成一个密码，这个加密是不可逆的
多行函数
对一些数据进行运算，最终只返回一个结果
sum()：求和
avg()：求平均
count()：求总数
max()：最大值
min()：最小值
1 2 3 4 5 6 7 8 9
-- 统计平均工资、总工资、总数据条数、最大工资、最小工资 select sum(sal) as 总工资, count(sal) as 总数, max(sal) as 最大工资, min(sal) as 最小工资, -- 总工资 / 总数 as 平均工资 注意：别名不能在select中参与计算 avg(sal) as 平均工资 from emp;
-- 语法 select 查询字段 from 表名 [where 查询子句] [group by 分组字段1, 字段2] [having 查询子句] [order by 排序字段1, 字段2] [limit n1, n2]; -- 查询所有部门的平均工资，并筛选出平均工资在2000以上的部门 select dept_no, avg(sal) as avg_sal from emp group by dept_no having avg_sal > 2000 order by avg_sal;
-- 常规的连接查询 select * from emp, dept where emp.dept_no=dept.dept_no; -- 查询的每一张表都可以设置别名 select * from emp as e, dept as d where e.dept_no=d.dept_no; -- 连表查询也可以指定查询字段 select e.emp_no, e.emp_name, e.sal, d.dept_no, d.dept_name from emp as e, dept as d where e.dept_no=d.dept_no; -- 连表查询的where条件中依然可以加其他的查询条件，同样也可以使用order by、group by、having、聚合函数等 select e.emp_no, e.emp_name, e.sal, d.dept_no, d.dept_name from emp as e, dept as d where e.dept_no=d.dept_no and e.sal >= 3000 order by e.sal desc;
非等值查询
查询员工的工资等级
1 2 3
select e.emp_no, e.emp_name, e.sal, s.grade from emp e, salgrade s where e.sal < s.hi_sal and e.sal > lo_sal;
连接查询可以同时连两张以上的表
1 2 3 4
select e.emp_no, e.emp_name, e.sal, s.grade, d.dept_no, d.dept_name from emp as e, dept as d, salgrade s where e.dept_no=d.dept_no and e.sal < s.hi_sal and e.sal > lo_sal;
自连接
自己连自己
1 2 3
select e1.emp_no, e1.emp_name, e1.manager_id, e2.emp_no, e2.emp_name as 领导名称 from emp e1, emp e2 where e1.manager_id = e2.emp_no;
select * from table1 as t1 [cross join table2 as t2] -- 交叉连接 [natural join table2 as t2] -- 自然连接 [join table2 using(字段名)] -- using子句 [join table2 on (连表条件)] -- on 子句 [(left | right | full outer) join table2 on (连表条件) ] -- 左/右/满外连接 where 条件 group by 排序字段 having 过滤条件 order by 排序字段;
select * from emp as e join dept d using(dept_no) where e.sal >= 3000;
on子句
自然连接和using子句条件是基于两张表中存在同名字段的前提下，可以使用on子句
on子句能够更加灵活的指定两张表之间使用哪两个字段进行关联
1 2 3 4
select * from emp as e inner join dept as d on e.deptno=d.dept_no where e.deptno = 20;
外连接
内连接
内连接使用 inner join 关键字进行连接，只会返回匹配到的所有数据
1 2 3
select * from emp e inner join dept d on e.deptno = d.dept_no;
左外连接
左外连接特点是除了返回满足条件的行以外，还返回左表中不满足条件的行
1 2 3
select * from emp e left join dept d on e.deptno = d.dept_no;
右外连接
右外连接除了返回满足条件的行以外，还返回右表中不满足条件的行
1 2 3
select * from emp e right join dept d on e.deptno = d.dept_no;
满外连接
除了返回满足条件的行以外，还会返回两张表中不满足条件的行
1 2 3
select * from emp e full join dept d on e.deptno = d.dept_no;
MySQL5.7中不支持满外连接
子查询
问题引入
如何查询所有比CLARK 工资高的员工信息
此时我们可以使用子查询，第一步先查询CLARK的工资，第二步将上一步查询的工资作为筛选条件进行查询
语法
1 2 3 4 5
select 字段列表 from table1 where 字段名 运算符 (select 字段列表 from table2); -- 实现上面的需求 select * from emp where sal > (select sal from emp where emp_name = 'CLARK');
子查询的特点
子查询会在主查询前执行一次，主查询使用子查询的查询结果
在查询条件是基于未知数据时考虑使用子查询
子查询必须放到括号内部
建议将子查询放到条件末尾
一般不建议在子查询中使用order by 子句
单行子查询
单行子查询只返回一行记录，因此对于单行子查询可以使用比较运算符
1 2 3 4 5 6 7 8 9
-- 查询职务与SCOTT相同，但是雇佣时间比他早的员工信息 select * from emp where job = (select job from emp where emp_name='SCOTT') and workdate < (select workdate from emp where emp_name='SCOTT'); -- 查询工资比SCOTT高或者雇佣时间比SCOTT早的雇员的员工编号、名字、工资，并根据工资倒序排序 select emp_no, emp_name, sal from emp where sal > (select job from emp where emp_name='SCOTT') or workdate < (select workdate from emp where emp_name='SCOTT') order by sal desc;
-- 查询工资低于其中一个岗位CLERK的工资的雇员信息 select * from emp where sal < any(select sal from emp where job='CLERK') and job != 'CLERK'; -- 查询工资比所有的 SALESMAN 都高的员工的信息 select * from emp where sal > all(select sal from emp where job='SALESMAN'); -- 查询部门20中职务同部门10的员工一样的员工信息 select * from emp where deptno=20 and job in (select job from emp where deptno=10); -- 查询员工中那些人员是领导 select * from emp where emp_no in ( select distinct manager_id from emp ); -- 找出部门编号为20的所有员工收入最高的职员 select * from emp where sal >= all(select sal from emp where deptno=20) and deptno=20; -- 查询每个部门平均薪水的等级 -- 首先求出每个部门的平均薪水和部门信息 select deptno, avg(sal) as avg_sal from emp group by deptno; -- 查询结果可以作为临时表使用 select * from (select deptno, avg(sal) as avg_sal from emp group by deptno) t; -- 将查询结果作为一张临时的平均工资表，连工资等级表进行查询 select * from salgrade s inner join (select deptno, avg(sal) as avg_sal from emp group by deptno) as t on t.avg_sal between s.lo_sal and s.hi_sal;
-- 语法 create view 视图名 as 查询语句; -- 将emp创建成视图 create view myview1 as select * from emp;
修改或创建视图
1 2 3 4
-- 语法 create or replace view 视图名称 as 查询语句; -- 创建myview2 create or replace view myview2 as select emp_no, emp_name, job, deptno from emp where deptno > 10;
create or replace view myview3 as select d.dept_no as 部门编号, d.dept_name as 部门名称, avg(e.sal) as 平均工资, max(e.sal) as 最高工资, count(*) as 员工数 from emp e inner join dept d on e.deptno=d.dept_no group by d.dept_no, d.dept_name;
视图还可以基于其他视图
1 2
create or replace view myview4 as select * from myview3 where 员工数 > 3;
create trigger 触发器名称 before or after -- 触发时间，before表示在SQL语句执行前触发，此时会拿到变更之前的数据，after表示在执行之后触发，会拿到执行之后新的数据 insert or update or delete -- 指定触发的事件 on 表名 -- 给哪张表定义的触发器 for each row -- 触发级别，此时表示行级别的触发器，插入一行、删除一行、更新一行触发一次 begin -- 自定义触发器规则 end;
使用触发器
需求：为emp表创建一个备份表，当向emp表插入数据时，同时向备份表插入一条相同的数据
创建备份表
1
create table emp_bak like emp;
创建触发器
1 2 3 4 5 6 7 8 9 10 11
delimiter // create trigger trigger_emp_before_insert before insert on emp for each row begin insert into emp_bak(emp_no, emp_name, manager_name, manager_id, workdate, job, sal, comm, deptno) values (new.emp_no, new.emp_name, new.manager_name, new.manager_id, new.workdate, new.job, new.sal, new.comm, new.deptno); end // delimiter ;
delimiter // create trigger trigger_emp_after_delete after delete on emp for each row begin delete from emp_bak where emp_no=old.emp_no; end // delimiter ;
自定义函数
语法
1 2 3 4 5
create function 函数名(形参列表) returns 返回值类型 begin 函数体 -- 可以定义变量，变量的形式 set @x=1 返回值 end
定义一个函数完成加法运算
1 2 3 4 5 6
delimiter // create function myadd(a int, b int) returns int -- 形参列表为 变量名 数据类型，不要写反了 begin return a + b; end // delimiter ;
查看函数
1 2
show function status like '匹配符'; show create function 函数名;
删除函数
1
drop function 函数名;
自定义函数实现计算1-指定数据的和
使用全局变量
1 2 3 4 5 6 7 8 9 10 11 12
delimiter // create function mysum1(x int) returns int begin set @i=1; set @sum=0; while @i <= x do set @sum = @sum + @i; set @i = @i + 1; end while; return @sum; end // delimiter ;
使用局部变量，且5的倍数跳过
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
delimiter // create function mysum2(x int) returns int begin declare i int default 1; -- 声明局部变量 declare sum int default 0; sumwhile:while i <= x do if i % 5 = 0 then set i = i + 1; iterate sumwhile; -- iterate代表结束本次循环，使用时要指定一个标签，结束本次循环后从指定的标签位置开始运行 end if; set sum = sum + i; set i = i + 1; end while; return sum; end // delimiter ;
自定义函数给定生成一定长度的随机字符串
1 2 3 4 5 6 7 8 9 10 11 12 13
delimiter // create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXY'; declare result varchar(255) default ''; declare i int default 0; while i < n do set result = concat(result, substring(chars_str,floor(1+rand()*52),1)); set i = i + 1; end while; return result; end // delimiter ;
drop procedure if exists 存储过程名称; create procedure 存储过程名称(形参列表) begin 过程体 end
形参编写方式：in 输入参数 out 输出参数 inout 输入输出参数
编写存储过程
案例1：编写存储过程，输入员工id得到员工对应的名称和工资
1 2 3 4 5 6 7 8 9
delimiter // drop procedure if exists proc_emp; create procedure proc_emp(in empno int) begin select emp_no, emp_name, sal from emp where emp_no=empno; end // delimiter ;
-- 建表 DROP TABLE IF EXISTS person; CREATE TABLE person ( PID int(11) NOT NULL AUTO_INCREMENT COMMENT '编号', PNAME varchar(50) NOT NULL COMMENT '姓名', PSEX varchar(10) NULL DEFAULT NULL COMMENT '性别', PAGE int(11) NOT NULL COMMENT '年龄', SAL decimal(7, 2) NULL DEFAULT NULL COMMENT '工资', PRIMARY KEY (PID) ) ENGINE = InnoDB AUTO_INCREMENT = 7001 CHARACTER SET = utf8mb4 COMMENT = '人员表';
索引
普通索引
创建语法
1
create index 索引名称 on 表名称(字段名称);
给person表的pname字段创建普通索引
1
create index index_person_pname on person(pname);
使用 explain 关键字可以分析SQL是否使用到了索引
1
explain select * from person where pname='张三';
主键也是索引，叫做主键索引
验证模糊查询是否使用到了索引
1 2 3 4 5 6
-- 前后模糊中间精确，不会用到索引 explain select * from person where pname like '%ee%'; -- 前面模糊后面精确，不会用到索引 explain select * from person where pname like '%ee'; -- 前面精确后面模糊，会用到索引 explain select * from person where pname like 'ee%';
索引压力测试
1 2
-- 首先删除索引 alter table person drop index index_person_pname;
插入500w条数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
delimiter // create procedure insert_person(in max_num int(10)) begin declare i int default 0; set autocommit = 0; -- 把autocommit设置成0，这样可以只提交一次，否则。。。。。 repeat set i = i +1; insert into person (PID,PNAME,PSEX,PAGE,SAL) values (i,rand_string(5),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000)); until i = max_num end repeat; commit; end // delimiter ;
-- 调用 call insert_person(5000000);
测试
1 2 3 4 5 6 7
-- 首先没有索引测试 select * from person where pname='adsa'; -- 创建索引 create index index_person_pname on person(pname); -- 之后在测试
-- 语法 create unique index 索引名称 on 表名(列名); -- 查询 select * from person where mobile = '547454245'; -- 创建唯一索引 create unique index uni_idx_person_mobile on person(mobile);