04-DQL数据查询语言

2019-12-20  本文已影响0人  UncleZ_strive

1.DQL单表数据查询

--------------------------------------------------------------------------------------------old guo

1.SELECT

1.SELECT 单独使用

SELECT @@port;
SELECT @@datadir;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES  LIKE '%trx%';
SELECT DATABASE();     查询当前所在数据库
SELECT NOW();       查看时间
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;      合并
SELECT GROUP_CONCAT(xid) FROM student;      将列合并
SELECT SUM(xid) FROM student;    xid列求和

2.select 配合其他子句使用

子句列表介绍

FROM     -- 查询对象(表,视图)
WHERE    -- 过滤子句(grep)
GROUP BY -- 分组子句(统计分析类)
ORDER BY -- 排序子句
HAVING   -- 后过滤子句
LIMIT    -- 限制子句(分页子句)
1.配合FROM应用
2.select+ from + where(grep)使用
3.group by 分组子句+聚合函数应用

聚合函数?

COUNT()  -- 计数
SUM()    -- 求和
AVG()    -- 求平均值
MAX()    -- 求最大值
MIN()    -- 最小值
GROUP_CONCAT() -- 聚合列值 
4.having 后判断

1.统计中国每个省的人口总数,只显示总人口数大于500w的省信息.
SELECT district,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population) >=5000000;

6.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;

5.limit 分页限制子句

limit m offset n 跳过n行显示m行

查询中国所有省的总人口,并按总人口数从大到小排序输出.

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;

注意: LIMIT 谨慎使用, 500w+的表.
LIMIT 5000000,100
一般会改为明确查找范围

2.DQL多表数据查询

1.介绍
内连接  inner join
外连接  left  join right join
笛卡尔  
2.作用

聚合多张表数据,实现查询需求

3.多表连接的语法
FROM A 
INNER JOIN B 
ON  A.x=B.y
FROM A 
LEFT JOIN B 
ON  A.x=B.y

FROM A 
RIGHT JOIN B 
ON  A.x=B.y

1.3.3 笛卡尔乘积

FROM A 
JOIN B 
4.多表连接例子

套路1. 找关联表 2. 找关系列

SELECT 
  city.name,
  country.name,
  country.surfacearea,
  city.population 
FROM
  city 
  JOIN country 
    ON city.countrycode = country.code 
WHERE city.population < 100  
SELECT student.xid ,student.xname,AVG(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname
SELECT student.xid ,student.xname,COUNT(score.score)   
FROM  student
JOIN score 
ON student.xid=score.xid
GROUP BY student.xid,student.xname 
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;
5.left/right join 外连接应用
6 补充 别名的应用 .
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 子句中调用

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;
上一篇下一篇

猜你喜欢

热点阅读