MySQL数据查询语言

最新推荐文章于 2024-05-08 09:36:01 发布

开化者

最新推荐文章于 2024-05-08 09:36:01 发布

阅读量402

点赞数

分类专栏： MySQL 文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/li175325/article/details/132452828

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

1. DQL 介绍

select
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．列出所有员工的年工资,按年薪从低到高排序。