第六章 DQL 数据查询语言

一、select 简单查询命令

#1.查询表中所有的数据
mysql> select * from test.student;

#2.查看所有数据之前，先查看数据量
mysql> select count(*) from test.student;

#3.查询指定列
mysql> select user,host from mysql.user;

#4.按条件查询
mysql> select * from test.student where id='8';
mysql> select id,name from test.student where id='8';

2.查询数据测试

1）将sql导入数据库

#上传sql文件到服务器
[root@db01 ~]# rz world.sql

#导入sql到数据库
mysql> source /root/world.sql;
mysql> . /root/world.sql

2）查询的操作

#1.查看库下面的表
mysql> show tables from world;

mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

#2.查看表结构
mysql> desc city;

#3.查询所有数据
mysql> select count(*) from city;
mysql> select * from city;

#4.查询指定列数据
mysql> select name,population from city;

#5.按照人口数量排序
#升序
mysql> select name,population from city order by population;
#降序
mysql> select name,population from city order by population desc;

#6.查看人口数量最多排名前十的城市
mysql> select name,population from city order by population desc limit 10;

#7.按照步长查询数据
#查询数据从10后面开始计算，展示20条数据，20就是步长
mysql> select id,name,population from city limit 10,20;

mysql> select id,name,population from city limit 0,60;
mysql> select id,name,population from city limit 60,60;
mysql> select id,name,population from city limit 120,60;

3.按条件查询

#1.条件查询where的符号
where的条件符号： = < > >= <= != <>
where的连接符：and or like in

#2.查看中国城市的人口数量
mysql> select CountryCode,name,population from city where CountryCode='CHN';

#3.查看黑龙江省城市的人口数量
mysql> select CountryCode,District,name,population from city where CountryCode='CHN' and District='heilongjiang';

#4.查询中国人口数量小于10万的城市
mysql> select CountryCode,population,name from city where CountryCode='CHN' and population<'100000';

#5.查看国家代码以H开头的
mysql> select * from city where CountryCode like 'H%';

#6.查看国家代码以H结尾的
mysql> select * from city where CountryCode like '%H';

#7.查看国家代码包含H的
mysql> select * from city where CountryCode like '%H%';

#8.查询中国城市和美国城市的人口数量
mysql> select CountryCode,name,population from city where CountryCode='CHN' or CountryCode='USA';
mysql> select CountryCode,name,population from city where CountryCode in ('CHN','USA');

#9.联合查询
mysql> select CountryCode,name,population from city where CountryCode='CHN' union all select CountryCode,name,population from city where CountryCode='USA';

二、select 高级用法（多表联查，连表查询）

1.传统连接

1）数据

[qiudao,zengdao,qiandao]
[80,90,100]

#建表
id:[1,2,3]
name:[qiudao,zengdao,qiandao]

#建表
id:[1,2,3]
mark:[80,90,100]

2）建表

#建立学生表
mysql> create table student1(id int,name varchar(20));

#建立成绩表
mysql> create table score(id int,mark int);

3）插入数据

#插入学生表数据
mysql> insert student1 values('1','qiudao'),('2','zengdao'),('3','qiandao');

#插入成绩表数据
mysql> insert score values('1','80'),('2','90'),('3','100');

4）查看数据

#查看学生表
mysql> select * from student1;
+------+---------+
| id   | name    |
+------+---------+
|    1 | qiudao  |
|    2 | zengdao |
|    3 | qiandao |
+------+---------+
3 rows in set (0.00 sec)

#查看成绩表
mysql> select * from score;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+
3 rows in set (0.00 sec)

5）数据查询

#查看qiudao的成绩

1.方式一：
mysql> select student1.name,score.mark from student1,score where student1.id='1' and score.id='1';

2.方式二：
mysql> select student1.name,score.mark from student1,score where student1.id=score.id and name='qiudao';

6）查询题1：

#查询世界上小于100人的城市是哪个国家的？

#1.审题：查看需要查询哪些数据
城市名字   城市人口数量   国家名字

#2.找到查询内容的字段在哪个表
城市名字        城市人口数量          国家名字
city.name      city.population		country.name

#3.找出两个表中关联的列
city.countrycode
country.code

#4.编写语句
select city.name,city.population,country.name from city,country where city.countrycode=country.code  and city.population < '100';

select city.name,city.population,country.name from city natural join country where city.population < '100';

7）多表联查练习题2：

#查询世界上小于100人的城市是哪个国家的，使用什么语言？

#1.审题：查看需要查询哪些数据
城市名字   城市人口数量   国家名字   国家的语言

#2.找到查询内容的字段在哪个表
城市名字        城市人口数量          国家名字		国家的语言
city.name      city.population		country.name    countrylanguage.language

#3.找出三个表相关联的列
city.countrycode
country.code
countrylanguage.CountryCode

#4.编写语句
select city.name,city.population,country.name,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.CountryCode and city.population < '100';

2.自连接

#自连接会自动关联两个表中数据相同的字段，自连接的两个表必须有相同的字段和数据

1）自连接查询

#查询人口数量大于100万的城市，列出他们的国家代码和国家语言

1.传统连接：
select city.name,city.population,countrylanguage.CountryCode,countrylanguage.language from city,countrylanguage where countrylanguage.CountryCode=city.CountryCode and city.population > '1000000';

2.自连接：
select city.name,city.population,countrylanguage.CountryCode,countrylanguage.language from city natural join countrylanguage where city.population > '1000000';

#注意：
	1.自连接会自动去获取两个表之间的关联列和数据，所以自连接的两个表必须有相同的字段和数据

3.内连接

1）语法

select * from 表1 join 表2 on 关联条件 where 条件

#注意：
	表 1 是小表
	表 2 是大表

2）例子：

#查询世界上小于100人的城市是哪个国家的，国家代码是什么

1.传统链接：
select city.population,city.name,country.name,country.code from city,country where country.code=city.countrycode and city.population < '100';

2.内连接：
select city.population,city.name,country.name,country.code from country join city on country.code=city.countrycode where city.population < '100';

3）内连接三表联查

#查询世界上小于100人的城市是哪个国家的，用什么语言？
select city.population,city.name,country.name,countrylanguage.language from country join city on city.countrycode=country.code join countrylanguage on country.code=countrylanguage.countrycode where city.population < '100';

4.外连接

1）左外连接

select city.name,city.countrycode,country.name 
from city left join country 
on city.countrycode=country.code 
and city.population<100;

2）右外连接

select city.name,city.countrycode,country.name 
from city right join country 
on city.countrycode=country.code 
and city.population<100;

5.UNION（合并查询）

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';

#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');

#替换为：
mysql> select * from city where countrycode='CHN' 
union all
select * from city where countrycode='USA' limit 10;

三、字符集

1.字符集介绍

字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等

#最早的字符集：ASCII码
中国的字符集：gbk，utf8，utf8mb4，gbk2312，....
日本：shift-JIS
韩国：Euc-kr
万国编码：Unicode字符集

#数据库常用的字符集
	gbk: 一个汉字占用2个字节
	utf8: 一个汉字占用3个字节
	utf8mb4: 一个汉字占用4个字节
	
#字符集修改
	字符集有一个包含关系，修改时要注意小的范围可以修改为大范围的字符集

#数据库查看字符集
	mysql> show charset;

2.校验规则

#查看校验规则
mysql> show collation;

| latin1_bin               |
| latin1_general_ci        |
| latin1_general_cs        |

#校验规则区别
1.ci结尾的校验规则不区分大小写
2.bin和cs结尾的校验规则区分大小写