mysql查询例_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 分组总结：

1、关键字where 和 group by 同时出现的时候group by 必须在where 的后面

2、where先对整体数据进行过滤之后再分组操作

3、聚合函数只能再分组之后使用

4、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=‘销售’);