04-DQL数据查询语言

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列求和

• 简易计算器
  SELECT 4*5;

2.select 配合其他子句使用

子句列表介绍

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

1.配合FROM应用

• 1.查询表中所有数据(cat)
  SELECT * FROM city;
• 2. 查询name和population信息 (awk取列)
     SELECT NAME,population FROM city;

2.select+ from + where(grep)使用

• where 配合等值查询
  1.查询中国所有的城市信息
  SELECT * FROM city WHERE countrycode='CHN';
  2.查询ID为100的城市信息
  SELECT * FROM city 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 IN ('CHN','USA');
  或者:
  SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' ;
• where 配合范围查询
  1.查询人口数量小于100人的城市
  SELECT * FROM city WHERE population<100;
  2.查询人口数量100w-200w之间的
  SELECT * FROM city WHERE population>=1000000 AND population<=2000000 ;
  或者:
  SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000 ;
  3.查询国家代号是CH开头的城市信息
  SELECT * FROM city WHERE countrycode LIKE 'CH%';

3.group by 分组子句+聚合函数应用

聚合函数?

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;
  3.统计下中国每个省的城市个数及城市名.
  SELECT district,COUNT(NAME),GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district;
  4.统计每个国家城市个数
  SELECT countrycode ,COUNT(NAME) FROM city GROUP BY countrycode;

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. 找关系列

• 查询人口数小于100人城市名,国家名,国土面积?

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 外连接应用

• 一般应用在强制驱动表时,强制小结果集驱动大表
• 生产中可以使用left join 强制 驱动表.尽量减少next loop的出现.
• 为什么要强制? inner 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;