day06--DQL数据查询语言

目录

• 一、select高级用法
  • 总结
  • 1.select获取简单参数信息
  • 2.select查询语句
    • 1）查询表中所有的数据
    • 2）查看指定列的数据
    • 3）按条件查询
    • 3）查询练习
  • 3.条件查询
  • 4.传统连接
  • 5.自连接
  • 6.内连接（划重点）
    • 1）语法格式
    • 2）例子1：两表联查
    • 3）例子2：三表联查
  • 7.外连接
    • 0) 左右连接翻译
    • 1）左外连接
    • 2）右外连接
  • group by(分组条件) 配合聚合函数应用
    • 1）常用聚合函数
    • 2) 语句总结
  • having（二级过滤）

一、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做限制