1. DQL 介绍
show
2. select 语句的应用
2.1 select单独使用的情况***
mysql> select @@basedir; #mysql安装目录 mysql> select @@port; #mysql端口号 mysql> select @@innodb_flush_log_at_trx_commit; #日志刷新策略 mysql> show variables like 'innodb%'; #模糊查看innodb开头的配置 mysql> select database(); #查看当前库名 mysql> select now(); #查看当前系统时间 mysql> select @@server_id; #查看本实例id号，群集中不能重复
2.2 select 通用语法(单表) *****
select 显示的列名（多列逗号分开）
from 表名（多个表逗号分开）
where 过滤条件的列
group by 分组的列
having 分组后的过滤聚合函数
order by 排序的列
limit 显示前几行
2.3 学习环境的说明
world数据库
city 城市表
country 国家表
countrylanguage 国家的语言
city表结构
mysql> desc city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
ID : 城市序号(1-...)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 洲
population : 人口数
如何熟悉数据库业务?
快速和研发人员打好关系
找到领导要ER图
DESC ,show create table
select * from city limit 5;
2.4 SELECT 配合 FROM 子句使用
-- select 列,列,列 from 表
--- 例子:
(1) 查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ; SELECT id,NAME ,countrycode ,district,population FROM city; 或者: SELECT * FROM city;
(2) 查询表中 name和population的值
SELECT NAME ,population FROM city;
2.5 SELECT 配合 WHERE 子句使用
-- select 列,列,列 from 表 where 过滤条件
-- where等值条件查询 *****
例子:
查询中国所有的城市名和人口数
select name,population from city where countrycode='CHN';
-- where 配合比较判断查询(> < >= <=) *****
例子:
世界上小于100人的城市名和人口数
select name,population from city where population<100;
-- where 配合 逻辑连接符(and or)
例子:
(1) 查询中国人口数量大于800w的城市名和人口
select name,population from city where countrycode='CHN' and population>8000000;
(2) 查询中国或美国的城市名和人口数
select name,population from city where countrycode='CHN' or countrycode='USA';
(3) 查询人口数量在500w到600w之间的城市名和人口数
select name,population from city where population>=5000000 and population<=6000000;
或者:
select name,population from city where population between 5000000 and 6000000;
-- where 配合 like 子句 模糊查询 *****
例子:
查询一下contrycode中带有CH开头,城市信息
select name,countrycode from city where countrycode like 'CH%';
注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"Elasticsearch"来替代
-- where 配合 in 语句
例子:
查询中国或美国的城市信息.
select name,population from city where countrycode in ('CHN','USA');
2.5.2 GROUP BY
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数(sum,avg,count,max,min)操作.
例子:
(1) 统计每个国家,城市的个数
select countrycode,count(name) from city group by countrycode;
(2) 统计每个国家的总人口数.
select countrycode,sum(population) from city group by countrycode;
(3) 统计每个 国家 省 的个数(distinct 去除重复)
select countrycode,count(distinct district) from city group by countrycode;
(4) 统计中国 每个省的总人口数
select district as 省,sum(population) as 总人口 from city where countrycode='CHN' group by district;
(5) 统计中国 每个省城市的个数
select district as 省,count(name) as 城市个数 from city where countrycode='CHN' group by district;
(6) 统计中国 每个省城市的名字列表GROUP_CONCAT() #列转行
select district,group_concat(name) from city where countrycode='CHN' group by district;
(7) 小扩展(拼接，自定义分隔符)
anhui : hefei,huaian ....
SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city
WHERE countrycode='CHN'
GROUP BY district ;
2.7 SELECT 配合 ORDER BY 子句
例子:
统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列
select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc;
注：默认为升序，asc ; 降序为desc
2.8 SELECT 配合 LIMIT 子句
例子:
统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列,只显示前三名
select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;
LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行
2.9 练习题:
(1) 统计中国每个省的总人口数，只打印总人口数小于100w的
select district,sum(population) from city where countrycode='CHN'
group by district having sum(population)<1000000;
(2) 查看中国所有的城市，并按人口数进行排序(从大到小)
select name,population from city where countrycode='CHN' order by population desc;
(3) 统计中国各个省的总人口数量，按照总人口从大到小排序
select district,sum(population) from city where countrycode='CHN'
group by district order by sum(population) desc;
(4) 统计中国,每个省的总人口,找出总人口大于500w的,
并按总人口从大到小排序,只显示前三名
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 3 ;
2.10 把中国每个省城市个数大于10的列出前3名
select district,count(name) from city where countrycode='CHN'
group by district having count(name)>10 order by count(name) desc limit 3;
2.11 union 和 union all
作用: 多个结果集合并查询的功能
需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
改写为:
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
面试题: union 和 union all 的区别 ?
union all 不做去重复
union 会做去重操作
3. 多表连接查询(内连接)
分类：
inner join 内连接，企业普遍使用，inner可以省略
left join 左外连接
right join 右外连接
full join 完整外连接
cross join 求笛卡尔积
3.1 多表连接基本语法
student ：学生表
===============
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
================
tno： 教师编号
tname：教师名字
course ：课程表
===============
cno： 课程编号
cname：课程名字
tno： 教师编号
sc ：成绩表
==============
sno： 学号
cno： 课程编号
score：成绩
3.2 多表连接例子
create database bdqn; use bdqn; drop table if exists emp; drop table if exists dept; drop table if exists salgrade; -- 部门表 CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, -- 部门编号 DNAME VARCHAR(14), -- 部门名称 LOC VARCHAR(13) -- 部门地址 ); INSERT INTO DEPT VALUES (10,"ACCOUNTING","NEW YORK"); INSERT INTO DEPT VALUES (20,"RESEARCH","DALLAS"); INSERT INTO DEPT VALUES (30,"SALES","CHICAGO"); INSERT INTO DEPT VALUES (40,"OPERATIONS","BOSTON"); -- 员工表 CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工名称 JOB VARCHAR(9), -- 工作 MGR DOUBLE, -- 直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 部门号 FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)); INSERT INTO EMP VALUES (7369,"SMITH","CLERK",7902,"1980-12-17",800,NULL,20); INSERT INTO EMP VALUES (7499,"ALLEN","SALESMAN",7698,"1981-02-20",1600,300,30); INSERT INTO EMP VALUES (7521,"WARD","SALESMAN",7698,"1981-02-22",1250,500,30); INSERT INTO EMP VALUES (7566,"JONES","MANAGER",7839,"1981-04-02",2975,NULL,20); INSERT INTO EMP VALUES (7654,"MARTIN","SALESMAN",7698,"1981-09-28",1250,1400,30); INSERT INTO EMP VALUES (7698,"BLAKE","MANAGER",7839,"1981-05-01",2850,NULL,30); INSERT INTO EMP VALUES (7782,"CLARK","MANAGER",7839,"1981-06-09",2450,NULL,10); INSERT INTO EMP VALUES (7788,"SCOTT","ANALYST",7566,"1987-07-13",3000,NULL,20); INSERT INTO EMP VALUES (7839,"KING","PRESIDENT",NULL,"1981-11-17",5000,NULL,10); INSERT INTO EMP VALUES (7844,"TURNER","SALESMAN",7698,"1981-09-08",1500,0,30); INSERT INTO EMP VALUES (7876,"ADAMS","CLERK",7788,"1987-07-13",1100,NULL,20); INSERT INTO EMP VALUES (7900,"JAMES","CLERK",7698,"1981-12-03",950,NULL,30); INSERT INTO EMP VALUES (7902,"FORD","ANALYST",7566,"1981-12-03",3000,NULL,20); INSERT INTO EMP VALUES (7934,"MILLER","CLERK",7782,"1982-01-23",1300,NULL,10); -- 薪资表 CREATE TABLE SALGRADE ( GRADE INT, -- 工资等级 LOSAL DOUBLE, -- 最低工资 HISAL DOUBLE -- 最高工资 ); INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1201,1400); INSERT INTO SALGRADE VALUES (3,1401,2000); INSERT INTO SALGRADE VALUES (4,2001,3000); INSERT INTO SALGRADE VALUES (5,3001,9999);
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)工资=薪金＋佣金
1．列出至少有一个员工的所有部门。
select DEPT.DNAME from DEPT JOIN EMP ON DEPT.DEPTNO=EMP.DEPTNO group by DEPT.DNAME;
2．列出工资比"SMITH”多的所有员工。
select ENAME,sum(IFNULL(SAL,0) + IFNULL(COMM,0)) as gongzi from EMP GROUP by ENAME
having sum(IFNULL(SAL,0) + IFNULL(COMM,0)) > (select sum(IFNULL(SAL,0) + IFNULL(COMM,0))
as gongzi from EMP where EMP.ENAME='SMITH' GROUP by ENAME);
3．列出所有员工的姓名及其直接上级的姓名。
select a.ENAME,b.ENAME from EMP as a join EMP as b on a.MGR=b.EMPNO;
4．列出受雇日期早于其直接上级的所有员工。
select a.ENAME,b.ENAME from EMP as a join EMP as b on a.MGR=b.EMPNO where b.HIREDATE>a.HIREDATE;
5．列出部门名称和这些部门的员工.信息，同时列出那些没有员工的部门。
select DEPT.DNAME,EMP.ENAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO
UNION
select DEPT.DNAME,EMP.ENAME from DEPT left join EMP on DEPT.DEPTNO=EMP.DEPTNO;
6．列出所有“CLERK”(办事员）的姓名及其部门名称。
select EMP.ENAME,DEPT.DNAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO where EMP.JOB='CLERK';
7．列出最低薪金大于1500的各种工作。
select JOB,SAL from EMP where SAL>1500;
8．列出在部门"SALES”（销售部）工.作的员工.的姓名，假定不知道销售部的部门编号。
select DEPT.DNAME,EMP.ENAME from DEPT join EMP on DEPT.DEPTNO=EMP.DEPTNO
where DEPT.DNAME='SALES';
9.列出薪金高于公司平均薪金的所有员T.。
select ENAME,SAL from EMP where SAL > (select avg(SAL) from EMP);
10．列出与“SCOTT”从事相同T.作的所有员工。
select EMP.ENAME,b.job from EMP join (select JOB from EMP where ENAME='SCOTT') as b on EMP.JOB=b.JOB;=b.JOB;
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select a.ENAME,b.SAL from EMP as a join (select ENAME,SAL FROM EMP WHERE DEPTNO=30) as b
on a.SAL=b.SAL where a.DEPTNO != 30;
12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select distinct a.ENAME,a.SAL from EMP as a,(select ENAME,SAL FROM EMP WHERE DEPTNO=30) as b
where here a.SAL>b.SAL;
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
14．列出所有员工的姓名、部门名称和工资。
15．列出所有部门的详细信息和部门人数。
16．列出各种工作的最低工资。
17．列出各个部门的MANAGER（经理)的最低薪金。
18．列出所有员工的年工资,按年薪从低到高排序。
