1. 多表联查心德
# 1. 分析需求
# 2. 确定表与表的关联做为判断条件
# 3. 确定每表要获取的数据
# 4. 查找所需的表内容加上条件
2. 查询练习
#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.模糊匹配
#匹配以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';
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 students(id int,name varchar(10));
Query OK, 0 rows affected (0.08 sec)
mysql> create table score(id int,mark int);
Query OK, 0 rows affected (0.05 sec)
3)插入数据
mysql> insert into students values(1,'qiudao'),(2,'qiandao'),(3,'zengdao');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into score values(1,80),(2,90),(3,100);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
4)数据查询
#查看两个表的数据
mysql> select * from students;
+------+---------+
| id | name |
+------+---------+
| 1 | qiudao |
| 2 | qiandao |
| 3 | zengdao |
+------+---------+
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)
#查看邱导的分数
mysql> select students.name,score.mark from students,score where students.id=1 and score.id=1;
mysql> select students.name,score.mark from students,score where students.id=score.id and name='qiudao';
+--------+------+
| name | mark |
+--------+------+
| qiudao | 80 |
+--------+------+
1 row in set (0.01 sec)
#查询所有学生成绩
mysql> select students.name,score.mark from students,score where students.id=score.id
5)练习题一:
连表查询:世界上小于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)
6)练习题二:
连表查询:世界上小于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)