MySQL查询实例讲解

一、单表查询

#前期例子表准备：
 create table teacher(
 	id int primary key auto_increment,
 	name varchar(16),
 	age int,
 	sex enum('男','女') default '女',
 	xueke varchar(16),
 	slary int
 );
 insert into teacher(name,age,sex,xueke,slary) value('alex',28,'男','计算机',8000);
insert into teacher(name,age,sex,xueke,slary) value('egon',23,'男','数学',9000);
insert into teacher(name,age,sex,xueke,slary) value('hello',24,'女','语文',9500);
insert into teacher(name,age,sex,xueke,slary) value('world',26,'男','计算机',10000);
insert into teacher(name,age,sex,xueke,slary) value('python',22,'女','英语',7000);
insert into teacher(name,age,sex,xueke,slary) value('json',21,'男','化学',6000);
insert into teacher(name,age,sex,xueke,slary) value('tank',38,'男','计算机',7000);
insert into teacher(name,age,sex,xueke,slary) value('AK',28,'男','数学',7500);
insert into teacher(name,age,sex,xueke,slary) value('jeck',18,'女','语文',8000);
insert into teacher(name,age,sex,xueke,slary) value('lusi',18,'女','化学',8500);
insert into teacher(name,age,sex,xueke,slary) value('haozi',33,'男','计算机',9000);
insert into teacher(name,age,sex,xueke,slary) value('xuefei',28,'女','英语',9900)；
insert into teacher(name,age,sex,xueke,slary) value('耗子',25,'男','计算机',8000);
insert into teacher(name,age,sex,xueke,slary) value('爱跟',24,'男','数学',9000);
insert into teacher(name,age,sex,xueke,slary) value('你好',23,'女','语文',9500);
insert into teacher(name,age,sex,xueke,slary) value('世界',27,'男','计算机',10000);
insert into teacher(name,age,sex,xueke,slary) value('小七',21,'女','英语',7000);
insert into teacher(name,age,sex,xueke,slary) value('合理',31,'男','化学',6000);
insert into teacher(name,age,sex,xueke,slary) value('test',48,'男','计算机',NULL);

1. where
   模糊查询：like
   % 匹配任意多个字符
   _ 匹配任意单个字符

#(1)查询名字有n的教师

select * from teacher where name like '%n%';

#（2）查询名字是4个字符的教师

select * from teacher where name like '____';
select * from teacher where char_length(name)=4;

#（3）查询年龄在22到28之间的教师

select * from teacher where age between 22 and 28;

（4）查询age小于23或者id大于28的教师

select * from teacher where age not between 22 and 28;

（5）查询age不在23,28,33范围内的教师

select * from teacher where age not in(23,28,33);

(6)查询age 是23,21的教师
select * from teacher where age in(23,21);

(7)查询工资为空的老师
查询工资为非空的老师

select * from teacher where slary is NULL;
select * from teacher where slary is not NULL;

2. group by 分组
四个聚合函数：max、min、avg 、count
(1)查询各个学科最高工资

select xueke,max(slary) from teacher group by xueke;

(2)查询各个学科最低工资

select xueke,min(slary) from teacher group by xueke;

(3)查询各个学科的平均工资

 select xueke,avg(slary) from teacher group by xueke;

(4)查询各个学科的老师人数

 select xueke,count(slary) from teacher group by xueke;
 

group_concat 可以获取分组后的其他字段值，还支持拼接操作。
（1）查看各科老师的名字

select xueke,group_concat(name) from teacher group by xueke;

（2）查看各科老师的名字和工资

select xueke,group_concat(name,':',slary) from teacher group by xueke;

cnocat是在不分组情况下使用的

select concat('名字：',name),concat('工资：',slary) from teacher;

as 可以临时改别名

select teacher.name,teacher.slary from teacher where id=1;
等价于
select t.name,t.slary from teacher as t where id=1;

group by 分组总结：

关键字where 和 group by 同时出现的时候group by 必须在where 的后面
where先对整体数据进行过滤之后再分组操作
聚合函数只能再分组之后使用
where条件中不能出现聚合函数，否则会报错。

3. having（是分组之后使用的）
   having可以直接使用聚合函数。
   （1）查询各学科年龄在20以上的教师平均工资并且保留平均工资在8000元以上的学科

select xueke,avg(slary) from teacher where age >20 group by xueke having avg(slary)>8000;

4. distinct 去重
注意：去重必须是完全一样才可以去重，如果去重的数据中存有主键数据，则不可能去重成功，因为主键是唯一的。
（1）对工资进行去重

select distinct slary from teacher;

5. order by 排序

默认是升序 asc
	降序是desc 
(1)对计算机学科的老师工资进行降序排列。
select * from teacher where xueke='计算机' order by slary desc;
(2)对计算机学科的老师工资进行升序排列。
select * from teacher where xueke='计算机' order by slary asc;#asc可加可不加

7. limit 限制

（1）取五条数据
select * from teacher limit 5;
（2）从第五条开始取五条数据
select * from teacher limit 5,5; #第一个参数5表示从第五条开始，第二个参数5表示取的数据条数。

8. 正则

查询名字以j开头，k\n结束的老师
select * from teacher where name regexp '^j.*(n|k)$';

二、多表查询

1. 连表操作

（1）inner join 内连接
	只拼接两张表都有的数据
	select * from right_b inner join left_b on right_b.l_id=left_b.id;
（2）left join 左连接
	左表所有的数据都展示出来，没有对应的项就用NULL代替
	#在join左边的为左表，右边为右表
	select * from right_b left join left_b on right_b.l_id=left_b.id;
（3）right join 右连接
	右表所有的数据都展示出来，没有对应的项就用NULL代替
	select * from right_b right join left_b on right_b.l_id=left_b.id;
（4）union
	两张表的数据都展现出来
	select * from right_b left join left_b on right_b.l_id=left_b.id
	union
	select * from right_b right join left_b on right_b.l_id=left_b.id;

2. 子查询
   本质：就将第一个查询的结果作为查询条件去使用
   （1）查询技术部门、销售部门员工的信息
   select * from right_b where l_id in (select id from left_b where name=‘技术’ or name=‘销售’);