1.聚合函数
2.分组查询
3.排序查询
4.分页查询
5.SQL执行顺序
6.函数
7.字符串函数
8.数值函数
9.流程控制函数
10.约束
11.多表查询
create table student( id int auto_increment primary key comment '主键ID', name varchar(10) comment '姓名', no varchar(10) comment '学号' ) comment '学生表';insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');create table course( id int auto_increment primary key comment '主键ID', name varchar(10) comment '课程名称' ) comment '课程表';insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');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 student (id) )comment '学生课程中间表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2), (null,2,3),(null,3,4);
create table tb_user(id int auto_increment primary key comment ' 主键 ID' ,name varchar ( 10 ) comment ' 姓名 ' ,age int comment ' 年龄 ' ,gender char ( 1 ) comment '1: 男 , 2: 女 ' ,phone char ( 11 ) comment ' 手机号 ') comment ' 用户基本信息表 ' ;create table tb_user_edu(id int auto_increment primary key comment ' 主键 ID' ,degree varchar ( 20 ) comment ' 学历 ' ,major varchar ( 50 ) comment ' 专业 ' ,primaryschool varchar ( 50 ) comment ' 小学 ' ,middleschool varchar ( 50 ) comment ' 中学 ' ,university varchar ( 50 ) comment ' 大学 ' ,userid int unique comment ' 用户 ID' ,constraint fk_userid foreign key (userid) references tb_user(id)) comment ' 用户教育信息表 ' ;insert into tb_user(id, name, age, gender, phone) values( null , ' 黄渤 ' , 45 , '1' , '18800001111' ),( null , ' 冰冰 ' , 35 , '2' , '18800002222' ),( null , ' 码云 ' , 55 , '1' , '18800008888' ),( null , ' 李彦宏 ' , 50 , '1' , '18800009999' );insert into tb_user_edu(id, degree, major, primaryschool, middleschool,university, userid) values( null , ' 本科 ' , ' 舞蹈 ' , ' 静安区第一小学 ' , ' 静安区第一中学 ' , ' 北京舞蹈学院 ' , 1 ),( null , ' 硕士 ' , ' 表演 ' , ' 朝阳区第一小学 ' , ' 朝阳区第一中学 ' , ' 北京电影学院 ' , 2 ),( null , ' 本科 ' , ' 英语 ' , ' 杭州市第一小学 ' , ' 杭州市第一中学 ' , ' 杭州师范大学 ' , 3 ),( null , ' 本科 ' , ' 应用数学 ' , ' 阳泉第一小学 ' , ' 阳泉区第一中学 ' , ' 清华大学 ' , 4 );
-- 创建 dept 表,并插入数据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 , ' 总经办 ' ), ( 6 , ' 人事部 ' );-- 创建 emp 表,并插入数据create table emp(id int auto_increment comment 'ID' primary key,name varchar ( 50 ) not null comment ' 姓名 ' ,age int comment ' 年龄 ' ,job varchar ( 20 ) comment ' 职位 ' ,salary int comment ' 薪资 ' ,entrydate date comment ' 入职时间 ' ,managerid int comment ' 直属领导 ID' ,dept_id int comment ' 部门 ID')comment ' 员工表 ' ;-- 添加外键alter table emp add constraint fk_emp_dept_id foreign key (dept_id) referencesdept(id);INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)VALUES( 1 , ' 金庸 ' , 66 , ' 总裁 ' , 20000 , '2000-01-01' , null , 5 ),( 2 , ' 张无忌 ' , 20 , ' 项目经理 ' , 12500 , '2005-12-05' , 1 , 1 ),( 3 , ' 杨逍 ' , 33 , ' 开发 ' , 8400 , '2000-11-03' , 2 , 1 ),( 4 , ' 韦一笑 ' , 48 , ' 开发 ' , 11000 , '2002-02-05' , 2 , 1 ),( 5 , ' 常遇春 ' , 43 , ' 开发 ' , 10500 , '2004-09-07' , 3 , 1 ),( 6 , ' 小昭 ' , 19 , ' 程序员鼓励师 ' , 6600 , '2004-10-12' , 2 , 1 ),( 7 , ' 灭绝 ' , 60 , ' 财务总监 ' , 8500 , '2002-09-12' , 1 , 3 ),( 8 , ' 周芷若 ' , 19 , ' 会计 ' , 48000 , '2006-06-02' , 7 , 3 ),( 9 , ' 丁敏君 ' , 23 , ' 出纳 ' , 5250 , '2009-05-13' , 7 , 3 ),( 10 , ' 赵敏 ' , 20 , ' 市场部总监 ' , 12500 , '2004-10-12' , 1 , 2 ),( 11 , ' 鹿杖客 ' , 56 , ' 职员 ' , 3750 , '2006-10-03' , 10 , 2 ),( 12 , ' 鹤笔翁 ' , 19 , ' 职员 ' , 3750 , '2007-05-09' , 10 , 2 ),( 13 , ' 方东白 ' , 19 , ' 职员 ' , 5500 , '2009-02-12' , 10 , 2 ),( 14 , ' 张三丰 ' , 88 , ' 销售总监 ' , 14000 , '2004-10-12' , 1 , 4 ),( 15 , ' 俞莲舟 ' , 38 , ' 销售 ' , 4600 , '2004-10-12' , 14 , 4 ),( 16 , ' 宋远桥 ' , 40 , ' 销售 ' , 4600 , '2004-10-12' , 14 , 4 ),( 17 , ' 陈友谅 ' , 42 , null , 2000 , '2011-10-12' , 1 , null );
笛卡尔积
select * from emp , dept where emp.dept_id = dept.id;
而由于 id 为 17 的员工,没有 dept_id 字段值,所以在多表查询时,根据连接查询的条件并没有查询到。
分类
内连接
1). 隐式内连接
SELECT 字段列表 FROM 表 1 , 表 2 WHERE 条件 ... ;
2). 显式内连接
SELECT 字段列表 FROM 表 1 [ INNER ] JOIN 表 2 ON 连接条件 ... ;
select emp .name , dept .name from emp , dept where emp .dept_id = dept .id ;-- 为每一张表起别名 , 简化 SQL 编写select e .name ,d .name from emp e , dept d where e .dept_id = d .id ;
select e .name , d .name from emp e inner join dept d on e .dept_id = d .id ;-- 为每一张表起别名 , 简化 SQL 编写select e .name , d .name from emp e join dept d on e .dept_id = d .id ;
外连接
1). 左外连接
SELECT 字段列表 FROM 表 1 LEFT [ OUTER ] JOIN 表 2 ON 条件 ... ;
2). 右外连接
SELECT 字段列表 FROM 表 1 RIGHT [ OUTER ] JOIN 表 2 ON 条件 ... ;
select e.*, d .name from emp e left outer join dept d on e .dept_id = d .id ;select e.*, d .name from emp e left join dept d on e .dept_id = d .id ;
select d.*, e.* from emp e right outer join dept d on e .dept_id = d .id ;select d.*, e.* from dept d left outer join emp e on e .dept_id = d .id ;
注意事项:左外连接和右外连接是可以相互替换的,只需要调整在连接查询时 SQL 中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。
自连接
自连接查询
SELECT 字段列表 FROM 表 A 别名 A JOIN 表 A 别名 B ON 条件 ... ;
select a .name , b .name from emp a , emp b where a .managerid = b .id ;
select a .name ' 员工 ' , b .name ' 领导 ' from emp a left join emp b on a .managerid =b .id ;
注意事项 :在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底是哪一张表的字段。
联合查询
SELECT 字段列表 FROM 表 A ...UNION [ ALL ]SELECT 字段列表 FROM 表 B ....;
select * from emp where salary < 5000union allselect * from emp where age > 50 ;
select * from emp where salary < 5000unionselect * from emp where age > 50 ;
子查询
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
标量子查询
select id from dept where name = ' 销售部 ' ;1select * from emp where dept_id = ( select id from dept where name = ' 销售部 ' );
列子查询
select entrydate from emp where name = ' 方东白 ' ;1select * from emp where entrydate > ( select entrydate from emp where name = ' 方东白 ' );
行子查询
select salary, managerid from emp where name = ' 张无忌 ' ;
select * from emp where (salary,managerid) = ( select salary, managerid from empwhere name = ' 张无忌 ' );
多表查询案例
create table salgrade(grade int ,losal int ,hisal int) comment ' 薪资等级表 ' ;insert into salgrade values ( 1 , 0 , 3000 );insert into salgrade values ( 2 , 3001 , 5000 );insert into salgrade values ( 3 , 5001 , 8000 );insert into salgrade values ( 4 , 8001 , 10000 );insert into salgrade values ( 5 , 10001 , 15000 );insert into salgrade values ( 6 , 15001 , 20000 );insert into salgrade values ( 7 , 20001 , 25000 );insert into salgrade values ( 8 , 25001 , 30000 );
select e .name , e .age , e .job , d .name from emp e , dept d where e .dept_id = d .id ;
select e .name , e .age , e .job , d .name from emp e inner join dept d on e .dept_id =d .id where e .age < 30 ;
select distinct d .id , d .name from emp e , dept d where e .dept_id = d .id ;
select e.*, d .name from emp e left join dept d on e .dept_id = d .id where e .age >40 ;
-- 方式一select e.* , s .grade , s .losal , s .hisal from emp e , salgrade s where e .salary >=s .losal and e .salary <= s .hisal ;-- 方式二select e.* , s .grade , s .losal , s .hisal from emp e , salgrade s where e .salarybetween s .losal and s .hisal ;
select e.* , s .grade from emp e , dept d , salgrade s where e .dept_id = d .id and (e .salary between s .losal and s .hisal ) and d .name = ' 研发部 ' ;
select avg(e .salary ) from emp e, dept d where e .dept_id = d .id and d .name = ' 研发部 ' ;
select salary from emp where name = ' 灭绝 ' ;
select * from emp where salary > ( select salary from emp where name = ' 灭绝 ' );
select avg(salary) from emp;
select * from emp where salary > ( select avg(salary) from emp );
select avg(e1 .salary ) from emp e1 where e1 .dept_id = 1 ;select avg(e1 .salary ) from emp e1 where e1 .dept_id = 2 ;
select * from emp e2 where e2 .salary < ( select avg(e1 .salary ) from emp e1 wheree1 .dept_id = e2 .dept_id );
select d .id , d .name , ( select count (*) from emp e where e .dept_id = d .id ) ' 人数 'from dept d;
select s .name , s .no , c .name from student s , student_course sc , course c wheres .id = sc .studentid and sc .courseid = c .id ;
12.存储引擎
13.为什么存储引擎使用B+Tree
14.索引分类
15.索引语法
create table tb_users(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
-- INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO mybatis.tb_users (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');
表索引查看
SHOW INDEX FROM tb_users;
按照下面要求,完成索引的创建
CREATE INDEX idx_users_name ON tb_users(name);#idx_users_name :索引名称# tb_users(name):哪张表的哪个字段
CREATE UNIQUE INDEX idx_users_phone ON tb_users(phone);
#和常规索引唯一不同的就是加上了unique关键字
CREATE INDEX idx_users_pro_age_sta ON tb_users(profession,age,status);
CREATE INDEX idx_users_email ON tb_users(email);
删除索引
DROP INDEX idx_users_email ON tb_users;
16.性能分析-SQL性能工具
SQL执行频率
-- session 是查看当前会话 ;-- global 是查询全局数据 ;SHOW GLOBAL STATUS LIKE 'Com_______' ;->七个下划线
性能分析-慢查询日志
SHOW VARIABLES like 'slow_query_log'
# 开启 MySQL 慢日志查询开关slow_query_log = 1# 设置慢日志的时间为 2 秒, SQL 语句执行时间超过 2 秒,就会视为慢查询,记录慢查询日志long_query_time = 2
性能分析-profile
SELECT @@have_profiling ;
SET profiling = 1;
select * from tb_user;select * from tb_user where id = 1 ;select * from tb_user where name = ' 白起 ' ;select count (*) from tb_sku;
-- 查看每一条 SQL 的耗时基本情况show profiles;-- 查看指定 query_id 的 SQL 语句各个阶段的耗时情况show profile for query query_id;-- 查看指定 query_id 的 SQL 语句 CPU 的使用情况show profile cpu for query query_id;
性能分析-explain
-- 直接在 select 语句之前加上关键字 explain / descEXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;
17.索引使用-验证索引效率
select * from tb_sku where id = 1\G;#1/G:单纯的为了数据展示好看一点
SELECT * FROM tb_sku WHERE sn = '100000003145001' ;
create index idx_sku_sn on tb_sku(sn) ;
SELECT * FROM tb_sku WHERE sn = '100000003145001' ;
18.最左前缀法则
explain select * from tb_user where profession = ' 软件工程 ' and age = 31 and status= '0';
explain select * from tb_user where profession = ' 软件工程 ' and age = 31;
explain select * from tb_user where profession = ' 软件工程 ';
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';
explain select * from tb_user where profession = ' 软件工程 ' and status = '0';
思考题:当执行 SQL 语句 : explain select * from tb_user where age = 31 andstatus = '0' and profession = ' 软件工程 ' ; 时,是否满足最左前缀法则,走不走上述的联合索引,索引长度?可以看到,是完全满足最左前缀法则的,索引长度 54 ,联合索引是生效的。注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段 ( 即是第一个字段 ) 必须存在,与我们编写 SQL 时,条件编写的先后顺序无关。
19.索引失效
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
explain select * from tb_user where profession = ' 软件工程 ' and age > 30 and status= '0';
explain select * from tb_user where profession = ' 软件工程 ' and age >= 30 andstatus = '0';
索引列运算
explain select * from tb_user where phone = '17799990015' ;
explain select * from tb_user where substring(phone, 10 , 2 ) = '15' ;
explain select * from tb_user where profession = ' 软件工程 ' and age = 31 and status= '0' ;explain select * from tb_user where profession = ' 软件工程 ' and age = 31 and status= 0 ;
explain select * from tb_user where phone = '17799990015';explain select * from tb_user where phone = 17799990015;
模糊查询
explain select * from tb_user where profession like ' 软件 %' ;explain select * from tb_user where profession like '% 工程 ' ;explain select * from tb_user where profession like '% 工 %' ;
or连接条件
explain select * from tb_user where id = 10 or age = 23 ;explain select * from tb_user where phone = '17799990017' or age = 23 ;
create index idx_user_age on tb_user(age);
数据分布影响
select * from tb_user where phone >= '17799990005' ;select * from tb_user where phone >= '17799990015' ;
explain select * from tb_user where profession is null ;explain select * from tb_user where profession is not null ;
20.SQL提示(使用或者不用哪个索引)
drop index idx_user_age on tb_user;drop index idx_email on tb_user;
explain select * from tb_user use index(idx_user_pro) where profession = ' 软件工程' ;
explain select * from tb_user ignore index(idx_user_pro) where profession = ' 软件工程' ;
explain select * from tb_user force index(idx_user_pro) where profession = ' 软件工程' ;
explain select * from tb_user use index(idx_user_pro) where profession = ' 软件工程' ;
explain select * from tb_user ignore index(idx_user_pro) where profession = ' 软件工程' ;
explain select * from tb_user force index(idx_user_pro_age_sta) where profession =' 软件工程 ' ;
21.覆盖索引
explain select id, profession from tb_user where profession = ' 软件工程 ' and age =31 and status = '0' ;explain select id,profession,age, status from tb_user where profession = ' 软件工程 'and age = 31 and status = '0' ;explain select id,profession,age, status, name from tb_user where profession = ' 软件工程 ' and age = 31 and status = '0' ;explain select * from tb_user where profession = ' 软件工程 ' and age = 31 and status= '0' ;
思考题:一张表 , 有四个字段 (id, username, password, status), 由于数据量大 , 需要对以下 SQL 语句进行优化 , 该如何进行才是最优方案 :select id,username,password from tb_user where username ='itcast';答案 : 针对于 username, password 建立联合索引 , sql 为 : create indexidx_user_name_pass on tb_user(username,password);这样可以避免上述的 SQL 语句,在查询的过程中,出现回表查询。
22.前缀索引
create index idx_xxxx on table_name(column(n)) ;
create index idx_email_5 on tb_user(email( 5 ));
select count ( distinct email) / count (*) from tb_user ;select count ( distinct substring(email, 1 , 5 )) / count (*) from tb_user ;