DQL数据查询语言
目录
一、select高级用法
总结
# 查询方式：
· 1. 传统连接查询：（了解）
select 表名.显示字段 from 表1 表2 表3 where 表1.关联字段=表2.关联字段 and 表3.关联字段=表1.关联字段 and 判断语句
· 2. 自连接查询（这里的表1和表2必须有相同字段名，并且字段内内容一致，使用较少）
select 表名.显示字段 from 表1 NATURAL JOIN 表2 where 判断语句
· 3. 内连接查询 （常用，超级无敌大重点）
select 表名.显示字段名 from 表1 inner join 表2 on 表1.关联条件=表2.关联条件 where 条件
· 4. 外连接查询
1). 左外链接
select 表名.显示字段 from 表1 left join 表2 on 表1.关联条件=表2.关联条件 where 条件
2). 右外链接
select 表名.显示字段 from 表1 right join 表2 on 表1.关联条件=表2.关联条件 where 条件
# 语句顺序
select开始 ---》
from子句---》
where子句---》
group by子句---》
having子句---》
order by子句---》
limit
1.select获取简单参数信息
# 快捷查询
select @@prot; # 查看my.cnf定义端口
select @@basedir; # 查看安装路径
select @@datadir; # 查看数据路径
select @@socket; # 查看socket文件
select @@server_id; # 查看id
select @@log_error; # 查看错误日志；
select now(); # 获取当前时间
select user(); # 查看当前登录用户
select concat("hello world") # 和echo一样输出hello world
select count(*) from 表名; # 查看当前表的行数
select databases; # 查看当前所在库
select version; # 查看数据库版本
SELECT @@default_storage_engine; # 查看当前数据库配置的存储引擎
2.select查询语句
1）查询表中所有的数据
#很危险，数据量过大，容易导致down机
mysql> select * from student;
#先查询数据总量，然后决定是否可以查询所有数据
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.01 sec)
2）查看指定列的数据
mysql> select user,host from mysql.user;
+--------+------------+
| user | host |
+--------+------------+
| root | % |
| root | 127.0.0.1 |
| lhd | 172.16.1.% |
| qiudao | 172.16.1.% |
| root | 172.16.1.% |
| root | ::1 |
| | db03 |
| root | db03 |
| | localhost |
| root | localhost |
+--------+------------+
10 rows in set (0.01 sec)
3）按条件查询
mysql> select name,gender from student where name='邱导';
+--------+--------+
| name | gender |
+--------+--------+
| 邱导 | f |
+--------+--------+
1 row in set (0.00 sec)
3）查询练习
#1.查看表结构
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)
#2.查看所有数据
mysql> select * from city;
#3.查看指定列的数据
mysql> select Name,Population from city;
#4.查看数据时排序（按照人口数量）
#升序
mysql> select Name,Population from city order by Population;
#降序
mysql> select Name,Population from city order by Population desc;
#5.查询部分数据
#查看前十条数据
mysql> select Name,Population from city order by Population desc limit 10;
#6.按照步长查询数据
mysql> select id,Name,Population from city limit 50,50;
#50起始位置 50步长
3.条件查询
#1.条件查询就是使用where语句，where语句可以使用的符号
条件符号：= < > <= >= != <> or and like
精确匹配：=
范围匹配：< > <= >= != <>
模糊匹配：like
连接语句：or and
#2.查询中国的城市人口
mysql> select name,population from city where CountryCode='CHN';
#3.查询黑龙江人口数量
mysql> select name,population from city where countrycode='CHN' and District='heilongjiang';
#4.查询中国人口数量小于100000的城市
mysql> select name,population from city where countrycode='CHN' and population < 100000;
#5.模糊匹配
--- 注意：like语句在MySQL中，不要出现%在前的情况。因为效率很低，不走索引。
#匹配以N结尾的数据
mysql> select name,countrycode from city where countrycode like '%N';
#匹配以N开头的数据
mysql> select name,countrycode from city where countrycode like 'N%';
#匹配包含N的数据
mysql> select name,countrycode from city where countrycode like '%N%';
#6.查询中国或美国的人口数量
#使用or
mysql> select name,population from city where countrycode = 'CHN' or countrycode = 'USA';
#使用in
mysql> select name,population from city where countrycode in ('CHN','USA');
#使用union all
mysql> select name,population from city where countrycode = 'CHN' union all select name,population from city where countrycode = 'USA';
4.传统连接
连表查询：世界上小于100人的城市在哪个国家？请列出城市名字，国家名字与人口数量
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 小于100人
#2.确认在哪个表
country.name city.name city.population
#3.找出两个表相关联的字段
city.countrycode country.code
#4.编写语句
mysql> select country.name,city.name,city.population from country,city where city.countrycode=country.code and city.population < 100;
+----------+-----------+------------+
| name | name | population |
+----------+-----------+------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+------------+
1 row in set (0.01 sec)
连表查询：世界上小于100人的城市在哪个国家，是用什么语言？请列出城市名字，国家名字与人口数量和国家语言
#1.确认我要查哪些内容
国家名字 城市名字 城市人口数量 国家使用的语言 小于100人
#2.确认在哪个表
country.name city.name city.population countrylanguage.language
#3.找出三个表相关联的字段
country.code city.countrycode countrylanguage.countrycode
#4.写sql语句
mysql> select country.name,city.name,city.population,countrylanguage.language from country,city,countrylanguage where country.code=city.countrycode and city.countrycode=countrylanguage.countrycode and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.04 sec)
5.自连接
#自己查找相同字段，使用自连接，两个关联的表必须有相同字段和相同数据
SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage
WHERE population > 1000000
ORDER BY population;
#两个表中没有相同字段不行，字段相同值不同不行
SELECT country.name,city.name,city.population FROM city NATURAL JOIN country WHERE population < 100;
#注意：
1.自连接必须有相同字段和相同值
2.两个表中的数据必须完全相同
6.内连接（划重点）
1）语法格式
select * from 表1 join 表2 on 相关联的条件 where 条件;
#注意：命中率（驱动的概念）
表1 小表
表2 大表
select * from 表1 inner join 表2 on 相关联的条件 where 条件;
2）例子1：两表联查
#小于100人的城市在哪个国家，国家代码是什么？
select city.name,city.population,city.countrycode,country.name
from city join country on city.countrycode=country.code
where city.population < 100;
3）例子2：三表联查
#世界上小于100人的城市在哪个国家？是用什么语言？
select country.name,city.name,city.population,countrylanguage.language
from city join country on city.countrycode=country.code
join countrylanguage on country.code=countrylanguage.countrycode
where city.population < 100;
7.外连接
0) 左右连接翻译
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
1）左外连接
select city.name,city.countrycode,country.name,city.population
from city left join country
on city.countrycode=country.code
and city.population < 100;
2）右外连接
select city.name,city.countrycode,country.name,city.population
from city right join country
on city.countrycode=country.code
and city.population < 100;
group by(分组条件) 配合聚合函数应用
1）常用聚合函数
avg()：平均值
count()：计算
sum()：总数
max()：最大
min()：最小
group_concat()：列转行
2) 语句总结
# 统计全世界每个国家的总人口数 (group by 可以理解成站队)
select countrycode,sum(population) from city group by countrycode;
# 统计每个国家的城市个数
解题思路：
1. 拿什么站队
国家（countrycode）--->goup by countrycode
2. 拿什么统计
城市ID或城市名name
3. 统计的是什么
count(id)
select countrycode,count(id) from city group by countrycode;
# 统计并显示每个国家省的名字列表
select countrycode,group_concat(district) from city group by countrycode;
# 统计中国每个省的总人口数
select district,sum(population) from city where countrycode='CHN' group by district;
# 每位老师所教课程的平均分,并按平均分排序
select teacher.tname,avg(sc.score)
from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno
order by avg(sc.score) desc;
# 查询所有老师所教学生不及格的信息
SELECT teacher.tname,student.sname
FROM teacher
JOIN course
ON teacher.tno = course.tno
JOIN sc
ON course.cno = sc.cno
JOIN student
ON sc.sno = student.sno
WHERE sc.score<60;
having（二级过滤）
# having说明
having和where性质差不多，都是做筛选使用，但是having语句需要在group by之后，也就是做了分组之后再做筛选，所以having一般结合group by使用
# 统计中国每个省的总人口数大于1千万的省人口数
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>10000000;
结论：having后的条件是不走索引的，可以进行一些优化手段，比如用limit做限制
