MySQL数据库部分 DQL语句

DQL语句：

对表中的数据进行查询操作的语句

1）格式:select 查询列表

from 表名 where[各种条件语句]

where 条件查询语句

group by 分组查询

order by 排序

limit

having

2）查询语句条件顺序

select *from 表名

[where 条件查询语句]

[group by]

[order by]

[limit]

[having]

eg:

1.查询全部记录 '*'表示查询表中所有记录

select *from emp;

2.查询员工姓名 可以指定查询字段

select name from emp;

3.查询员工name,age,salary并取别名为姓名,年龄,工资

取别名:字段 as 别名 字段 别名

select name 姓名,age'年龄',salary as '工资' from emp;

4.查询有哪些部门 distinct 对查询的内容进行去重

select distinct dept as '部门' from emp;

5.查询研发部年龄大于20的员工所有信息

select *from emp where age > 20 and dept = '研发部';

6.查询不是研发部的员工

select *from emp where dept !='研发部';

-- or

select *from emp where not dept = '研发部';

7.查询2018年以后入职的员工名称和入职时间

select name 姓名,entry_time 入职时间 from emp where entry_time >= '2018-01-01';

-- or

select name 姓名,entry_time 入职时间 from emp where date_format(entry_time,'%Y') >= 2018;

-- or

select name 姓名,entry_time 入职时间 from emp where year(entry_time) >= 2018;

查询每个员工的入职了多少年

select name 姓名,year(now())-year(entry_time) 入职年长 from emp;

-- or 建议 未满一年不会算上

select name,timestampdiff(day,entry_time,now()) 入职年长 from emp;

8.查询工资大于等于2000并且小于等于5000员工姓名

格式:between 值1 and 值2 等价于 >=值1 and <=值2

select name from emp where salary >=2000 and salary <=5000;

--or

select name from emp where salary between 2000 and 5000;

9.查询工资为2000或者为5000或者为7000的员工

in(值1,值2) 等价于 = 值1 or = 值2

select *from emp where salary = 2000 or salary = 5000 or salary = 7000;

-- or

select *from emp where salary in(2000,5000,7000);

10.查询销售部的年龄为20或者研发部年龄为24的员工

select *from emp where (dept = '销售部' and age = 20) or (dept = '研发部' and age = 24);

-- or

select *from emp where (dept,age) in (('销售部',20),('研发部',24));

3）模糊查询

关键字: like

通配符:

(1)_:匹配单个字符

(2)%:匹配多个字符

eg:

1.查询姓张的员工

select *from emp where name like '%王%';

2.查询名字为两位数的姓张的员工

select *from emp where name like '张_';

3.查询名字为三位数的姓张的员工

select *from emp where name like '张__';

4.查询名字中含有"五"字的员工

select *from emp where name like '%五%';

-- or

select *from emp where name like concat('%','五','%'); 拼接成'%五%';

4) 排序查询

将指定的字段按照升序降序进行查询

关键字:order by

格式1:order by 字段 [desc/asc] 默认升序 desc 降序

格式2:order by 字段1 [desc/asc],字段2 [desc/asc] 按照字段1进行升序或者降序排序，如果相等就按照字段2进行升序或者降序排序

eg:

1.查询所有员工，按照工资从低到高排序

select *from emp order by salary,age;

2.查询所有员工，按照入职年份从高到低排序

select *from emp order by entry_time desc;

3.查询所有员工，按照工资从低到高排序，如果工资相等按照年龄从高到底排序

select *from emp order by salary,age desc;

5）分组查询

关键字:group by

格式:group by 字段 以该字段来进行分组，一般都和聚合函数搭配查询，以什么字段分组，查询列表除了聚合函数之外，只能查询该分组的字段

group by dept 以部门来进行分组

eg:

1.查询每个部门的人数

select dept 部门,count(*) 各部门人数 from emp group by dept; #先分组再进行聚合函数

select count(*)>2 from emp group by dept;

2.查询每个部门的员工平均工资,并从高到底排序

select dept,avg(salary) from emp group by dept order by avg(salary) desc;

3.查询人数大于2的部门

select dept 部门,count(*) 各部门人数 from emp group by dept having 各部门人数>2; #先分组再筛选

4.查询每个部门年龄大于20的人数

select dept 部门,count(*) 人数 from emp where age > 20 group by dept; #先筛选再分组

where 和 having的区别

(1)where一般用于分组前的数据筛选,先筛选再分组，having一般用于分组后的数据筛选,先分组再筛选.

(2)where后面不能跟聚合函数，having可以

6）多表查询

create table people(

id int primary key auto_increment,

name varchar(5),

age smallint(1),

sex char(3),

province int(5)

);

create table province(

id int primary key auto_increment,

pro_name char(5)

);

insert into province values(null,"四川省"),(null,"云南省"),(null,"贵州省"),(null,"湖南省");

insert into people values

(null,"王老五",18,'男',1),

(null,"张翠花",19,'女',2),

(null,"史珍香",22,'女',1),

(null,"关阴",20,'女',3),

(null,"朱逸群",20,'男',2),

(null,"杨伟",23,'男',3),

(null,"蔡坤",25,'男',4),

(null,"吴凡",27,'男',null)

查询用户所有信息以及所在省份名称

select *from people,province;

出现了笛卡尔积现象 只有people.province = province.id能够匹配的数据才是我们想要的

解决 加一个people.province = province.id条件

1.内连接:用左表去匹配右表，查询满足条件的数据

(1)隐式内连接 格式:select 查询列表 from 左表，右表 where 关联条件

select peo.name,pro.pro_name from people peo,province pro where peo.province_id = pro.id;

(2)显式内连接 格式:select 查询列表 from 左表 [inner] join 右表 on 关联条件[where其他条件]

select p1.*,p2.pro_name from people p1

inner join province p2

on p1.province_id = p2.id;

2.外连接

(1)左连接:保证左表的记录全部显示,右边记录显示为null,哪怕不满足匹配条件。

格式：select 查询列表 from 左表 left [outer] join 右表 on 关联条件 [where 其他条件]

select p1.*,p2.pro_name from people p1

left join province p2

on p1.province_id = p2.id;

(2)右连接:保证右表的记录全部显示,左边记录显示为null,哪怕不满足匹配条件。

格式：select 查询列表 from 左表 right [outer] join 右表 on 关联条件 [where 其他条件]

select p1.*,p2.pro_name from people p1

right join province p2

on p1.province_id = p2.id;

7）子查询

把一个查询结果作为另一个查询的条件，称为子查询又称之为查询的嵌套

若查询结果是单行单列，看成一个值

1.查询年龄最大的人员信息

步骤1:找最大年龄

select max(age) from emp;

步骤2:

select *from people where age = (select max(age) from emp);

2.查询年龄大于20的人来自于哪些省

select p1.pro_name,p2.pro_name from province p2 where name in (select name from people where age>20) ;

若查询结果是多行单列，看成一个数组

步骤1:找到年龄大于20的人所在的省份id

select province_id from people where age>20;

步骤2:将步骤1所查询到的结果作为province查询条件得到省份名称

select pro_name from province where id in(select province_id from people where age>20);

若查询结果是多行多列，看成一张表，这张表必须取别名

3.查询年龄大于22的人员所有信息包括省份

步骤1:通过people去找到年龄大于22的人员信息

select from people where age>22;#步骤2:将步骤1查询到的结果和province进行多表联查select p1.,p2.pro_name from (select *from people where age>22) p1,province p2 where p1.province_id = p2.id;

8）一对多与多对多

多对多 多个用户对应多个角色(用户,管理员) 用户表(id,name)-权限中间表(id,user_id,role_id)-权限表(id,name)

查询张三对应的权限名称

隐式内连接

select u.NAME,r.role_name from user u,role r,user_role u2

where u.id = u2.user_id

and r.id = u2.role_id

and u.name = '张三';

显示内连接

select u.NAME,r.role_name from user u

连接中间表

inner join user_role ur

on u.id = ur.user_id

连接权限表

inner join role r

on r.id = ur.role_id

where u.NAME = '张三';

一对多 一个班级对应多个学生 学生只能属于一个班级 一个班级会包含多个学生 学生表(id,name,class_id) 班级表(id,name)

select *from

student2 s inner join

class c on

s.class_id = c.id

where s.name='张三';

常用函数:

1.date_format(time,format); 将日期按照指定格式进行转换

%Y:年

%m:月

%d:日

2.分别获取年、月、日的函数

year(时间)获取年份

month(时间)获取月份

day(时间)获取天数

yyyy MM dd HH mm ss

select date_format(entry_time,'%Y %m %d') from emp;

select date_format(now(),'%Y %m %d %H %M %s');

select year(now()) 年,month(now()) 月,day(now()) 日;

3.获取时间差函数

timestampdiff(unit,时间1，时间2)

unit表示时间单位 year month day

4.concat(str1,str2,str3);

将str1和str2和str3进行拼接 一般用于拼接模糊查询 %关键字%

5.聚合函数

(1)count():返回查询到数据的条数

(2)min(字段):返回指定字段在所有数据中的最小值

(3)max(字段):返回指定字段在所有数据中的最大值

(4)avg(字段):返回指定字段在所有数据中的平均值

(5)sum(字段):返回指定字段在数据中的总和

eg:

1.查询员工总人数

select count(*) from emp;

2.查询最低工资

select min(salary) 最低工资 from emp;

3.查询研发部员工最大年龄

select max(age) from emp where dept = '研发部';

4.查询员工的平均年龄

select avg(age) from emp;

select avg(salary) from emp;

limit:限制查询的条数

格式:limit 起始下标,偏移量(条数)

使用于分页功能

查询研发部工资从高到低前两名员工信息

select *from emp where dept = '研发部' order by salary desc limit 1,2;