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; - 查询name和population信息 (awk取列)
SELECT NAME,population FROM city;
- 查询name和population信息 (awk取列)
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;
