DQL——数据查询语言

2020-01-11 本文已影响0人静如止水yw

1. select

select单独使用

查询数据库的参数

mysql> select @@port;   查询端口
mysql> select @@datadir;   查询mysql的存放路径
mysql> select @@basedir;   查询mysql的安装路径
mysql> select @@innodb_flush_log_at_trx_commit;   查询参数
mysql> show variables like '%trx%';   相当于grep过滤

调用内置函数

mysql> select database();  查询库名
mysql> select now();  查询系统时间
mysql> selete concat(user,"@",host) from mysql.user;  ##相当于sed替换

简易计算器

SELECT 4*5;
SELECT SUM(xid) FROM student;

2. 配合其他字句使用

（1）子句列表介绍

FROM     -- 查询对象（表，视图）
WHERE    -- 过滤子句（grep）
GROUP BY -- 分组子句（统计分析类）
HAVING   -- 后过滤子句
ORDER BY -- 排序子句
LIMIT    -- 限制子句

（2）配合FROM应用

world模板库介绍

##英文单词介绍
-- city        -- 城市
-- id          --序号ID主键
-- NAME        --城市名
--Countrycode  -- 国家代码（CHA,USA）
-- Population  -- 城市人口数

##举例说明
1. 插叙表中所有数据
select * from city;
2. 查询name,population信息
select name,population from city;

select + from + where(grep)使用

#where配合等值查询
## 举例
1. 查询中国所有城市信息
select * from city where countrycode='CHN ';
2. 查询id为100的城市信息
select * from where id=100;
3. 查询中国河北省的城市信息
select * from city where countrycode='CHN' and district='hebei';
4. 查询中国或美国的城市
select * from city where  countrycode='CHN' or countrycode='USA';
或者 
select * from city where countrycode=('CHN','USA');
或者
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

where 配合范围查询

#举例
1. 查询人口数量100w-200w之间的
select * from city where population >=1000000 and population <=2000000
或者
select * from city where population between 1000000 and 2000000;
2. 查询人口数量小于100人的城市
select * from city where population <100;
3. 查询国家代号是CH开头的城市信息
select * from city where countrycode like 'CH%';  ## %一般放在字符后面

（5）group_by分组子句+聚合函数应用

1. -- 执行过程：
   -- 排序
   -- 去重复
   -- 函数聚合
2. -- SQL_MODE(5.7+)=ONLY_FULL_GROUPBY
   
##聚合函数
count()     -- 计数
sum()       -- 求和
avg()       -- 求平均值
max()       -- 求最大值
min()       -- 最小值
group_concat()   -- 聚合列值

## 结果集显示特点：必须1v1，不能是1vn
##举例
1. 统计每个国家的人口总数
select countrycode,sum(population) from city group by countrycode;
2. 统计中国每个省的人口总数
select district,sum(population) from city where countrycode='CHN' group by district;
##金典错误##
mysql> SELECT name,district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
        ERROR 1055 (42000): Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with `sql_mode=only_full_group_by`
        原因: a. select后的列，不在 group by子句后
                 b. 也不在聚合函数中出现.
        修改: mysql> SELECT group_concat(name),district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district;
3. 统计下中国每个省的城市个数及城市名
select countrycode,count(name) from city group by countrycode;

group_by是怎么用的
       from——>where——>sum——>group_by
       聚合函数不一定配合group_by，但group_by一定配合聚合函数。

having后判断

##举例
统计中国每个省的人口总数，只显示总人口数大于500w的省的信息
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>=5000000 order by sum(population) desc;

order by 排序子句

##例子
1. 查询中国所有城市信息，人口数从小到大排序输出.
select * from city where countrycode='CHN' order by population desc;
2. 查询中国所有城市信息，按城市名排序
SELECT * FROM city 
WHERE countrycode='CHN' 
ORDER BY NAME;
3. 查询中国所有省的总人口，并按总人口数从大到小排序输出
SELECT district,SUM(population) FROM city 
WHERE countrycode='CHN' 
GROUP BY district 
ORDER BY SUM(population) DESC;

limit 分页限制子句

##例子
查询中国所有省的总人口,并按总人口数从大到小排序输出.
SELECT  district,SUM(population) FROM city 
WHERE countrycode='CHN' 
GROUP BY district 
ORDER BY SUM(population) DESC 
limit 5;   ##前5行

SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 5 OFFSET 1;  ##跳过第一行(偏移一行)

SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 10;   ##前十行

SELECT  district,SUM(population)  FROM  city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC
LIMIT 1,5;  ##2到6名，跳过第一行, 显示之后的5行
注意
LIMIT谨慎使用，如果超过500w+的表，使用时性能极差，一般会使用明确范围查找，使用between或者大于或小于等符号。

3. 多表连接的语法

（1）介绍
内连接：inner join

from a inner join b on A.x=B.y    -- 内连接的语法

外连接：left join right join

from A right join B on A.x=B.y

笛卡尔

from A join B

（2）作用
聚合多张表数据，实现查询需求
（3）多表连接示例

1. 查询人口数小于100人城市名，国家名，国土面积？
## 思路：找关联表，找关系列
select city.name country.name,countrycode.surfacearea,city.population from city join country on city.countrycode=countrycode.code where city.population <100;
2. 统计查询每位学员的平均分
select student.xid,student.xname,avg(score.score) from student join score on student.xid=score.xid group by student.xid,student.xname;
3. 统计每位学员学习了几门课
select student.xid,student.xname,count(score.score) from student join score on student.xid=score.xid group by student.xid,student.xname;
4. 查询每位老师教的课程名和价格
思路：teacher和course
select teacher.tname,course.cname,course.cprice from teacher join course on teacher.tid=course.tid;
5. 每位老师教的学生的个数和姓名列表
select teacher.tname,count(student.xid), group_concat(student.xname) from student join score on student.xid=score.xid join course on score.cid=course.cid join teacher on course.tid=teacher.tid group by teacher.tid,teacher.tname;

内连接综合练习题

1. 查询oldguo老师教的学生名
select concat (teacher.tname,'_',teacher.tid) as "教师名",group_concat(student.xname) as "学生列表" from student join score on student.xid=score.xid join course on score.cid=course.cid join teacher on course.tid=teacher.tid where teacher.tname='oldguo' group_by teacher.tid,teacher.tname;
2. 查询oldguo所教课程的平均分数
select concat (teacher.tname,'_',teacher.tid) as "教师名"，course.cname as "课程名"，avg(score.score) as "平均分" from teacher join course on teacher.tid=course.tid join score on course.cid=score.cid where teacher.tname='oldguo' group by teacher.tid,teacher.tname,course.cname;
3. 每位老师所教课程的平均分，并按平均分排序
select teacher.tname,count(student.xid),avg(score.score) from student join score on score.cid=course.cid join teacher on course.tid=teacher.tid group by teacher.tid,teacher.tname desc;
4. 查询白龙马，学习的课程名称有哪些?
select student.xname as "学生姓名"，group_concat(course.cname) as "课程列表" from student join score on student.xid = score.xid join course on score.cid = course.cid where student.xname='白龙马' group by student.xid,student.xname;
5. 统计每位老师总收入
select teacher.tname,count(student.xid),sum(course.cprice) from teacher join course on teacher.tid = course.tid join score on course.cid = score.cid join student on score.xid = student.xid group by teacher.r=tid,teacher.tname
-- 5.1 统计每位老师,每门课程,分别的收入情况
SELECT 
CONCAT(teacher.`tname`,"_",teacher.`tid`) AS "讲师名",
course.`cprice`*COUNT(score.xid) AS "课程收入"
FROM teacher
JOIN course
ON teacher.`tid` = course.`tid`
JOIN score
ON course.`cid` = score.`cid`
GROUP BY teacher.`tid`,teacher.`tname` , course.`cid`;

-- 6. 统计每门课程学习的人数.
SELECT course.`cname`,COUNT(score.xid)
FROM course
JOIN score
ON course.`cid`=score.`cid`
GROUP BY course.`cname`;

-- 7. 查询oldboy老师教的学生不及格的学生名单 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.tid,teacher.tname;

-- 8. 统计每位老师不及格学生名单 
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名"
,GROUP_CONCAT(CONCAT(student.xname,":",score.score))
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
JOIN student 
ON score.`xid`=student.`xid`
WHERE score.`score`<60
GROUP BY teacher.tid,teacher.tname;

-- 9. 每位老师所教课程的平均分,并按平均分排序.
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
AVG(score.`score`)
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

-- 10. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分.
SELECT course.`cid`,MAX(score.`score`),MIN(score.`score`)
FROM course
JOIN score
ON course.`cid`=score.`cid` 
GROUP BY course.`cid`;

-- 11. 查询平均成绩大于60分的同学的学号和平均成绩.
SELECT 
CONCAT(student.`xname`,"_",student.`xid`),
AVG(score.`score`)
FROM student
JOIN score
ON student.`xid`=score.`xid`
GROUP BY student.`xname`,student.`xid`
HAVING AVG(score.`score`)>60
ORDER BY AVG(score.`score`) DESC ;

扩展:
-- 12. 统计各位老师,所教课程的及格率
SELECT 
CONCAT(teacher.tname,"_",teacher.tid) AS "教师名",
course.`cname`,
CONCAT(COUNT(CASE WHEN score.score>60 THEN 1  END)/COUNT(score.xid)*100,"%")
FROM teacher
JOIN course
ON teacher.`tid`=course.`tid`
JOIN score
ON course.`cid`=score.`cid`  
GROUP BY teacher.tid,teacher.tname,course.`cid`;

-- 13. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
SELECT 
course.`cname` AS 课程名称,
GROUP_CONCAT(CASE WHEN score.`score` >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.`score` >=70 AND score.`score` < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.`score` >=60  AND score.`score` <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.`score` <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.`cid`=course.`cid`
GROUP BY course.`cid`;

as  -- 表示别名关键字

left/right join 外连接应用

一般应用在强制驱动表时，强制小结果集驱动大表。
① 生产中可以使用left join 强制驱动表，尽量减少next loop的出现
② 为什么要强制？inner join 优化器，自动选择，按照索引选择的几率较大

别名的应用

（1）列别名
SELECT 
course.cname AS 课程名称,
GROUP_CONCAT(CASE WHEN score.score >= 85 THEN student.xname END )  AS "优秀",
GROUP_CONCAT(CASE WHEN score.score >=70 AND score.score < 85  THEN student.xname  END) AS "良好",
GROUP_CONCAT(CASE WHEN score.score >=60  AND score.score <70 THEN  student.xname  END  )AS "一般",
GROUP_CONCAT(CASE WHEN score.score <60  THEN  student.xname END  ) AS "不及格"
FROM student 
JOIN score
ON student.xid = score.xid 
JOIN course 
ON score.cid=course.cid
GROUP BY course.cid;
说明:  1. 为了显示的好看.  2. 可以在 having 或 order by 子句中调用

（2）表别名

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名",
GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher as te 
JOIN course as  co 
ON te.tid=co.tid
JOIN score as sc 
ON co.cid=sc.cid 
JOIN student  as st
ON sc.xid=st.xid
WHERE sc.score<60
GROUP BY te.tid,te.tname;

SELECT 
CONCAT(te.tname,"_",te.tid) AS "教师名",
GROUP_CONCAT(CONCAT(st.xname,":",sc.score))
FROM teacher AS te 
JOIN course AS  co 
ON te.tid=co.tid
JOIN score AS sc 
ON co.cid=sc.cid 
JOIN student  AS st
ON sc.xid=st.xid
WHERE sc.score<60
GROUP BY te.tid,te.tname;

show语句的应用

——查看所有库
show databases;
——查看当前库下的所有表
show tables;
show tables from world;
——查看当前并发会话信息
show processlist;
show full processlist;
——查看数据库支持的权限
show privileges;
——查看数据库参数信息
show variables
show variables like '%trx%';
——查看字符集&校对规则
show charset;
show collation;
——查看建库&建表语句
show create database world;
show create table world.city;
——查看用户权限
show grants for root@'localhost';
——查看支持的存储引擎
show engines;
——查询表中索引信息
show index from world.city;
——查看数据库当前状态信息
show status;
show status like '%lock%';
——查看InnoDB引擎相关的状态信息（内存，事务，锁，线程……）
show engine innodb status\G;
——查看二进制日志相关信息
show binary logs;
show master status;
show binlog events in 'xxxx';
——查看主从复制相关信息  show relaylog events in 'xxxx';
show slave status \G
——查看show相关的所有命令
mysql> help show ;

4.统计信息库(information_schema)

视图的介绍：
① 安全：只允许查询，不知道操作的是什么对象。
② 方便：只需要简单的select语句即可使用。
information_schema的作用：
① 方便做数据库资产统计(库\表：个数；数据量；核心业务表的数据行的变化[大小，行数]；每张表的数据字典信息).
② 获取到Server层状态信息.
③ 获取到InnoDB引擎层的状态信息.
应用举例：

TABLES : 
TABLE_SCHEMA        : 表所在的库 
TABLE_NAME            : 表名
ENGINE                      : 表的引擎   
TABLE_ROWS            : 表的行数
AVG_ROW_LENGTH : 平均行长度(字节) 
INDEX_LENGTH        : 索引占用长度(字节) 
TABLE_COMMENT    : 表注释 

-- 1. 简单查询体验TABLES信息
SELECT * FROM TABLES;

-- 2. 所有业务库和表的名字.
SELECT table_schema , table_name 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

-- 3. 统计每个业务库,表的个数和列表
SELECT table_schema , COUNT(table_name),GROUP_CONCAT(table_name) 
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;

-- 4. 统计业务数据库的总数据量
SELECT SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

-- 5. 每个业务库分别统计数据量
SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS total_KB
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema
ORDER  BY total_KB DESC ;

-- 6. top 3 数据量大的表
SELECT table_schema,table_name,(table_rows * AVG_ROW_LENGTH+index_length)/1024 AS table_kb
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
ORDER BY  table_kb DESC 
LIMIT 3;

-- 运维需求
-- 7. 查询所有非INNODB的表
SELECT table_schema,table_name ,ENGINE FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 8. 查询所有非INNODB的表 , 并且提出修改建议
SELECT 
table_schema,
table_name ,
ENGINE ,
CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE <> 'innodb';

-- 9. 所有业务库和表的名字,并且生成备份语句.
SELECT 
table_schema , 
table_name ,
CONCAT("mysqldump ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql") AS "备份"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql');

-- 10. 统计 数据库所有业务表的字典信息
mysql> select table_schema,
table_name,column_name ,data_type,Column_key ,COLUMN_COMMENT 
from columns 
WHERE table_schema 
NOT IN ('sys','informatiion_schema','performance_schema','mysql');