Data Query Language
1. select
1.1 功能
获取表中的数据
1.2 select单独使用（MySQL独家功能）
1.2.1 select配合内置函数使用
-- 查看当前时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-13 19:16:47 |
+---------------------+
1 row in set (0.00 sec)
-- 查看当前所使用的数据库
mysql> use mysql
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
-- 打印字符串
mysql> select concat('hello world');
+-----------------------+
| concat('hello world') |
+-----------------------+
| hello world |
+-----------------------+
1 row in set (0.00 sec)
-- 格式化输出
mysql> select concat(user,'@',host) from user;
+-------------------------+
| concat(user,'@',host) |
+-------------------------+
| app@192.168.159.% |
| awei@192.168.159.% |
| mysql.session@localhost |
| mysql.sys@localhost |
| root@localhost |
+-------------------------+
5 rows in set (0.00 sec)
-- 查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20 |
+-----------+
1 row in set (0.00 sec)
-- 查看当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
1.2.2 计算
mysql> select 10*100;
+--------+
| 10*100 |
+--------+
| 1000 |
+--------+
1 row in set (0.01 sec)
1.2.3 查询数据库的参数
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec)
mysql> select @@datadir;
+-------------+
| @@datadir |
+-------------+
| /data/3306/ |
+-------------+
1 row in set (0.00 sec)
mysql> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)
-- 显示全部参数
mysql> show variables;
mysql> show variables like '%trx%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_api_trx_level | 0 |
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
2 rows in set (0.01 sec)
1.3 标准用法
1.3.1 单表查询
执行顺序：select --> from --> where --> group by --> select_list --> having --> order by --> limit
- from 子句的使用
-- 查询表中所有的数据，类似于 cat /etc/passwd
mysql> mysql> select *from world.city limit 5;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)
-- 查询表中的部分列值，类似于 awk 取列
mysql> select ID,Name,Population from world.city limit 5;
+----+----------------+------------+
| ID | Name | Population |
+----+----------------+------------+
| 1 | Kabul | 1780000 |
| 2 | Qandahar | 237500 |
| 3 | Herat | 186800 |
| 4 | Mazar-e-Sharif | 127800 |
| 5 | Amsterdam | 731200 |
+----+----------------+------------+
5 rows in set (0.00 sec)
- where 子句的使用
-- 配合比较判断符号 =，>，<，>=，<=，!=
mysql> select * from world.city where population<1000;
+------+-----------------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------------+-------------+-------------+------------+
| 61 | South Hill | AIA | – | 961 |
| 62 | The Valley | AIA | – | 595 |
| 1791 | Flying Fish Cove | CXR | – | 700 |
| 2316 | Bantam | CCK | Home Island | 503 |
| 2317 | West Island | CCK | West Island | 167 |
| 2728 | Yaren | NRU | – | 559 |
| 2805 | Alofi | NIU | – | 682 |
| 2806 | Kingston | NFK | – | 800 |
| 2912 | Adamstown | PCN | – | 42 |
| 3333 | Fakaofo | TKL | Fakaofo | 300 |
| 3538 | Città del Vaticano | VAT | – | 455 |
+------+-----------------------+-------------+-------------+------------+
11 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------------------------------------
-- 配合 like 语句进行模糊查询
-- 国家代号是 CH 开头的城市信息
mysql> select * from world.city where countrycode like 'CH%' limit 5;
+------+-----------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-------------+------------+
| 3245 | Zürich | CHE | Zürich | 336800 |
| 3246 | Geneve | CHE | Geneve | 173500 |
| 3247 | Basel | CHE | Basel-Stadt | 166700 |
| 3248 | Bern | CHE | Bern | 122700 |
| 3249 | Lausanne | CHE | Vaud | 114500 |
+------+-----------+-------------+-------------+------------+
5 rows in set (0.00 sec)
-- 注意：like语句在使用时，切记不要出现开头带%的模糊查询，不走索引
mysql> explain select * from world.city where countrycode like 'CH%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | city | NULL | range | CountryCode | CountryCode | 3 | NULL | 397 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from world.city where countrycode like '%CH%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
----------------------------------------------------------------------------------------------------------------------------
-- 配合逻辑连接符 AND OR XOR
-- 查询中国人口数大于500w的城市
mysql> select * from world.city where countrycode='CHN' and population>5000000;
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
+------+-----------+-------------+-----------+------------+
4 rows in set (0.00 sec)
-- 查询中国或美国人口数都大于500w的城市信息
mysql> select * from world.city where countrycode in ('CHN','USA') and population>5000000;
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 3793 | New York | USA | New York | 8008278 |
+------+-----------+-------------+-----------+------------+
5 rows in set (0.00 sec)
mysql> select * from world.city where countrycode='CHN' and population>5000000 or countrycode='USA' and population>5000000;
+------+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+-----------+------------+
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1893 | Tianjin | CHN | Tianjin | 5286800 |
| 3793 | New York | USA | New York | 8008278 |
+------+-----------+-------------+-----------+------------+
5 rows in set (0.00 sec)
----------------------------------------------------------------------------------------------------------------------------
-- 配合BETWEEN AND使用（闭区间）
-- 查询城市人口数在800w到1000w之间的城市信息
mysql> select * from world.city where population=>8000000 and population<=10000000;
+------+---------------------+-------------+------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+------------------+------------+
| 206 | São Paulo | BRA | São Paulo | 9968485 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 2331 | Seoul | KOR | Seoul | 9981619 |
| 2515 | Ciudad de México | MEX | Distrito Federal | 8591309 |
| 2822 | Karachi | PAK | Sindh | 9269265 |
| 3357 | Istanbul | TUR | Istanbul | 8787958 |
| 3580 | Moscow | RUS | Moscow (City) | 8389200 |
| 3793 | New York | USA | New York | 8008278 |
+------+---------------------+-------------+------------------+------------+
9 rows in set (0.00 sec)
mysql> select * from world.city where population between 8000000 and 10000000;
+------+---------------------+-------------+------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------------------+-------------+------------------+------------+
| 206 | São Paulo | BRA | São Paulo | 9968485 |
| 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| 1890 | Shanghai | CHN | Shanghai | 9696300 |
| 2331 | Seoul | KOR | Seoul | 9981619 |
| 2515 | Ciudad de México | MEX | Distrito Federal | 8591309 |
| 2822 | Karachi | PAK | Sindh | 9269265 |
| 3357 | Istanbul | TUR | Istanbul | 8787958 |
| 3580 | Moscow | RUS | Moscow (City) | 8389200 |
| 3793 | New York | USA | New York | 8008278 |
+------+---------------------+-------------+------------------+------------+
9 rows in set (0.00 sec)
- group by 子句的使用
-- 配合聚合函数（max(), min(), avg(), count(), sum(), group_concat()）使用
-- 聚合函数：取数据 --> 排序 --> 去重 --> 聚合
-- 核心步骤：
-- 1. 根据需求，找出分组条件
-- 2. 根据需要，使用合适的聚合函数
-- 统计每个国家的城市个数（前5）
mysql> select countrycode,count(id) from world.city group by countrycode order by count(name) desc limit 5;
+-------------+-----------+
| countrycode | count(id) |
+-------------+-----------+
| CHN | 363 |
| IND | 341 |
| USA | 274 |
| BRA | 250 |
| JPN | 248 |
+-------------+-----------+
5 rows in set (0.00 sec)
-- 统计中国每个省的城市个数（前5）
mysql> select district,count(id) from world.city where countrycode='CHN' group by district order by count(id) desc limit 5;
+--------------+-----------+
| district | count(id) |
+--------------+-----------+
| Shandong | 32 |
| Jiangsu | 25 |
| Hubei | 22 |
| Heilongjiang | 21 |
| Liaoning | 21 |
+--------------+-----------+
5 rows in set (0.00 sec)
-- 统计每个国家的总人口（前5）
mysql> select countrycode,sum(population) from world.city group by countrycode order by sum(population) desc limit 5;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
| USA | 78625774 |
| JPN | 77965107 |
+-------------+-----------------+
5 rows in set (0.00 sec)
-- 统计中国，每个省的总人口（前5）
mysql> select district,sum(population) from world.city
-> where countrycode='CHN' group by district
-> order by sum(population) desc limit 5;
+--------------+-----------------+
| district | sum(population) |
+--------------+-----------------+
| Liaoning | 15079174 |
| Shandong | 12114416 |
| Heilongjiang | 11628057 |
| Jiangsu | 9719860 |
| Shanghai | 9696300 |
+--------------+-----------------+
5 rows in set (0.00 sec)
-- 统计中国每个省的总人口，城市个数，城市名列表
mysql> select district,sum(population),count(id),name
-> from world.city
-> where countrycode='CHN'
-> group by district;
ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-- MySQL5.7以后的特性，sql_mode限制，原因看后面的图（出现一列对多行的情况）
-- 解决方法：使用group_concat()函数，列转行
mysql> select district,sum(population),count(id),group_concat(name) from world.city where countrycode='CHN' group by district;
+----------------+-----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| district | sum(population) | count(id) | group_concat(name) |
+----------------+-----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Anhui | 5141136 | 16 | Hefei,Huainan,Bengbu,Wuhu,Huaibei,Ma´anshan,Anqing,Tongling,Fuyang,Suzhou,Liu´an,Chuzhou,Chaohu,Xuangzhou,Bozhou,Huangshan |
| Chongqing | 6351600 | 1 | Chongqing |
| Fujian | 3575650 | 12 | Fuzhou,Amoy [Xiamen],Nanping,Quanzhou,Zhangzhou,Sanming,Longyan,Yong´an,Fu´an,Fuqing,Putian,Shaowu |
| Gansu | 2462631 | 7 | Lanzhou,Tianshui,Baiyin,Wuwei,Yumen,Jinchang,Pingliang |
| Guangdong | 9510263 | 20 | Kanton [Guangzhou],Shenzhen,Shantou,Zhangjiang,Shaoguan,Chaozhou,Dongwan,Foshan,Zhongshan,Jiangmen,Yangjiang,Zhaoqing,Maoming,Zhuhai,Qingyuan,Huizhou,Meixian,Heyuan,Shanwei,Jieyang |
| Guangxi | 2925142 | 9 | Nanning,Liuzhou,Guilin,Wuzhou,Yulin,Qinzhou,Guigang,Beihai,Bose |
| Guizhou | 2512087 | 6 | Guiyang,Liupanshui,Zunyi,Anshun,Duyun,Kaili |
| Hainan | 557120 | 2 | Haikou,Sanya |
| Hebei | 6458553 | 12 | Shijiazhuang,Tangshan,Handan,Zhangjiakou,Baoding,Qinhuangdao,Xingtai,Chengde,Cangzhou,Langfang,Renqiu,Hengshui |
| Heilongjiang | 11628057 | 21 | Harbin,Qiqihar,Yichun,Jixi,Daqing,Mudanjiang,Hegang,Jiamusi,Shuangyashan,Tieli,Suihua,Shangzi,Qitaihe,Bei´an,Acheng,Zhaodong,Shuangcheng,Anda,Hailun,Mishan,Fujin |
| Henan | 6899010 | 18 | Zhengzhou,Luoyang,Kaifeng,Xinxiang,Anyang,Pingdingshan,Jiaozuo,Nanyang,Hebi,Xuchang,Xinyang,Puyang,Shangqiu,Zhoukou,Luohe,Zhumadian,Sanmenxia,Yuzhou |
| Hubei | 8547585 | 22 | Wuhan,Huangshi,Xiangfan,Yichang,Shashi,Shiyan,Xiantao,Qianjiang,Honghu,Ezhou,Tianmen,Xiaogan,Zaoyang,Jinmen,Suizhou,Xianning,Laohekou,Puqi,Shishou,Danjiangkou,Guangshui,Enshi |
| Hunan | 5439275 | 18 | Changsha,Hengyang,Xiangtan,Zhuzhou,Yueyang,Changde,Shaoyang,Yiyang,Chenzhou,Lengshuijiang,Leiyang,Loudi,Huaihua,Lianyuan,Hongjiang,Zixing,Liling,Yuanjiang |
| Inner Mongolia | 4121479 | 13 | Baotou,Hohhot,Yakeshi,Chifeng,Wuhai,Tongliao,Hailar,Jining,Ulanhot,Linhe,Zalantun,Manzhouli,Xilin Hot |
| Jiangsu | 9719860 | 25 | Nanking [Nanjing],Wuxi,Xuzhou,Suzhou,Changzhou,Zhenjiang,Lianyungang,Nantong,Yangzhou,Yancheng,Huaiyin,Jiangyin,Yixing,Dongtai,Changshu,Danyang,Xinghua,Taizhou,Huai´an,Qidong,Liyang,Yizheng,Suqian,Kunshan,Zhangjiagang |
| Jiangxi | 3831558 | 11 | Nanchang,Pingxiang,Jiujiang,Jingdezhen,Ganzhou,Fengcheng,Xinyu,Yichun,Ji´an,Shangrao,Linchuan |
| Jilin | 7826824 | 20 | Changchun,Jilin,Hunjiang,Liaoyuan,Tonghua,Siping,Dunhua,Yanji,Gongziling,Baicheng,Meihekou,Fuyu,Jiutai,Jiaohe,Huadian,Taonan,Longjing,Da´an,Yushu,Tumen |
| Liaoning | 15079174 | 21 | Shenyang,Dalian,Anshan,Fushun,Benxi,Fuxin,Jinzhou,Dandong,Liaoyang,Yingkou,Panjin,Jinxi,Tieling,Wafangdian,Chaoyang,Haicheng,Beipiao,Tiefa,Kaiyuan,Xingcheng,Jinzhou |
| Ningxia | 802362 | 2 | Yinchuan,Shizuishan |
| Peking | 7569168 | 2 | Peking,Tong Xian |
| Qinghai | 700200 | 1 | Xining |
| Shaanxi | 4297493 | 8 | Xi´an,Xianyang,Baoji,Tongchuan,Hanzhong,Ankang,Weinan,Yan´an |
| Shandong | 12114416 | 32 | Qingdao,Jinan,Zibo,Yantai,Weifang,Zaozhuang,Tai´an,Linyi,Tengzhou,Dongying,Xintai,Jining,Laiwu,Liaocheng,Laizhou,Dezhou,Heze,Rizhao,Liangcheng,Jiaozhou,Pingdu,Longkou,Laiyang,Wendeng,Binzhou,Weihai,Qingzhou,Linqing,Jiaonan,Zhucheng,Junan,Pingyi |
| Shanghai | 9696300 | 1 | Shanghai |
| Shanxi | 4169899 | 9 | Taiyuan,Datong,Yangquan,Changzhi,Yuci,Linfen,Jincheng,Yuncheng,Xinzhou |
| Sichuan | 7456867 | 21 | Chengdu,Panzhihua,Zigong,Leshan,Mianyang,Luzhou,Neijiang,Yibin,Daxian,Deyang,Guangyuan,Nanchong,Jiangyou,Fuling,Wanxian,Suining,Xichang,Dujiangyan,Ya´an,Emeishan,Huaying |
| Tianjin | 5286800 | 1 | Tianjin |
| Tibet | 120000 | 1 | Lhasa |
| Xinxiang | 2894705 | 10 | Urumtši [Ürümqi],Shihezi,Qaramay,Ghulja,Qashqar,Aqsu,Hami,Korla,Changji,Kuytun |
| Yunnan | 2451016 | 5 | Kunming,Gejiu,Qujing,Dali,Kaiyuan |
| Zhejiang | 5807384 | 16 | Hangzhou,Ningbo,Wenzhou,Huzhou,Jiaxing,Shaoxing,Xiaoshan,Rui´an,Zhoushan,Jinhua,Yuyao,Quzhou,Cixi,Haining,Linhai,Huangyan |
+----------------+-----------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31 rows in set (0.00 sec)
group by 的过程：
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l15x6DB7-1592143746318)(9. DQL/image-20200614144242868.png)]](https://img-blog.csdnimg.cn/20200614220945249.png)
在排序后会出现中间结果：
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k8CNsrX9-1592143746322)(9. DQL/image-20200614144326805.png)]](https://img-blog.csdnimg.cn/20200614221000703.png)
在MySQL5.6中会取第一个值，在MySQL5.7后因为规定了 sql_mode=only_full_group_by，select_list 中的列都需要出现在 group_by 中，这种一对多的情况会不被允许
所以 group_by 一定要配合聚合函数使用
- having 子句的使用
-- 与where子句类似，having属于后过滤，在group by后过滤（在使用聚合函数统计后过滤）
-- 统计中国，每个省的总人口数，只显示总人口数大于500w的信息（前5）
mysql> select district,sum(population)
-> from world.city
-> where countrycode='CHN'
-> group by district
-> having sum(population)>5000000
-> order by sum(population) desc
-> limit 5;
+--------------+-----------------+
| district | sum(population) |
+--------------+-----------------+
| Liaoning | 15079174 |
| Shandong | 12114416 |
| Heilongjiang | 11628057 |
| Jiangsu | 9719860 |
| Shanghai | 9696300 |
+--------------+-----------------+
5 rows in set (0.00 sec)
1.3.2 多表查询
-- 数据准备
use school
===============================
student: 学生表
-------------------------------
sno: 学号
sname: 学生姓名
sage: 学生年龄
ssex: 学生性别
===============================
teacher: 教师表
-------------------------------
tno: 教师编号
tname: 教师名字
===============================
course: 课程表
-------------------------------
cno: 课程编号
cname: 课程名称
tno: 教师编号
===============================
score: 成绩表
-------------------------------
sno: 学号
cno: 课程编号
score: 成绩
===============================
drop database school;
create database school charset utf8mb4;
use school
create table student(
sno int not null primary key auto_increment comment '学号',
sname varchar(20) not null comment '姓名',
sage tinyint unsigned not null comment '年龄',
ssex enum('f','m') not null default 'm' comment '性别'
)engine=innodb charset=utf8mb4;
create table course(
cno int not null primary key comment '课程编号',
cname varchar(20) not null comment '课程名字',
tno int not null comment '教师编号'
)engine=innodb charset=utf8mb4;
create table sc(
sno int not null comment '学号',
cno int not null comment '课程编号',
score int not null default 0 comment '成绩'
)engine=innodb charset=utf8mb4;
create table teacher(
tno int not null primary key comment '教师编号',
tname varchar(20) not null comment '教师名字'
)engine=innodb charset=utf8mb4;
insert into student(sno,sname,sage,ssex)
values (1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'awei',20,'m'),
(9,'ellen',20,'f'),
(10,'ae',25,'m');
insert into teacher(tno,tname)
values (101, 'oldboy'),
(102,'hesw'),
(103,'oldguo');
insert into course(cno,cname,tno)
values
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
insert into sc(sno,cno,score)
values (1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
- 笛卡尔积
-- 一般无意义
mysql> select * from teacher,course;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1001 | linux | 101 |
| 103 | oldguo | 1001 | linux | 101 |
| 101 | oldboy | 1002 | python | 102 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1002 | python | 102 |
| 101 | oldboy | 1003 | mysql | 103 |
| 102 | hesw | 1003 | mysql | 103 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
9 rows in set (0.00 sec)
mysql> select * from teacher join course;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1001 | linux | 101 |
| 103 | oldguo | 1001 | linux | 101 |
| 101 | oldboy | 1002 | python | 102 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1002 | python | 102 |
| 101 | oldboy | 1003 | mysql | 103 |
| 102 | hesw | 1003 | mysql | 103 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
9 rows in set (0.00 sec)
- 内连接
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)
mysql> select * from teacher join course on teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | hesw | 1002 | python | 102 |
| 103 | oldguo | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zSh8uTqY-1592143746323)(9. DQL/16956686-764e2f0b3601f6ea.png)]](https://img-blog.csdnimg.cn/20200614221020679.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjUxMTMyMA==,size_16,color_FFFFFF,t_70)
- 外连接
use world
-- 左外连接：左表的所有数据 + 右表满足条件的数据
mysql> select city.name,country.name,city.population
-> from city left join country on city.countrycode=country.code
-> and city.population < 100
-> order by city.population limit 5;
+-----------------------+----------+------------+
| name | name | population |
+-----------------------+----------+------------+
| Adamstown | Pitcairn | 42 |
| West Island | NULL | 167 |
| Fakaofo | NULL | 300 |
| Citt?? del Vaticano | NULL | 455 |
| Bantam | NULL | 503 |
+-----------------------+----------+------------+
5 rows in set (0.01 sec)
-- 作用：强制指定驱动表
-- 驱动表：在多表查询中，执行过程相当于用一张表逐行取出与另外一张表逐行比较，依次找到外循环和内循环中关联的值
-- 驱动表就相当于外循环，将小表作为驱动表，能降低next loop的次数
-- 在内连接中，驱动表是由MySQL的优化层自行决定将哪张表作为驱动表的
-- left join 强制左表为驱动表；right join 强制右表作为驱动表
-- 例：
select city.name,city.population,country.name,country.sufacearea
from city join country
on city.countrycode=country.code
where city.name='wuhan';
-- 改写为：强制指定city为驱动表的left join
-- 因为where优先级大于join，相当于
-- select city.name,city.population from city where city.name='wuhan'
-- 得出只有一行数据的表与country表做关联
select city.name,city.population,country.name,country.sufacearea
from city left join country
on city.countrycode=country.code
where city.name='wuhan';
- 多表连接查询例子
-- 查询wuhan这个城市：国家名，城市名，城市人口数，国土面积
mysql> select city.name,city.population,country.name,country.surfacearea
-> from city join country
-> on city.countrycode=country.code
-> where city.name='wuhan';
+-------+------------+-------+-------------+
| name | population | name | surfacearea |
+-------+------------+-------+-------------+
| Wuhan | 4344600 | China | 9572900.00 |
+-------+------------+-------+-------------+
1 row in set (0.00 sec)
-- 统计zhang3,学习了几门课
mysql> select student.sname,count(sc.cno)
-> from student join sc on student.sno=sc.sno
-> where student.sname='zhang3';
+--------+---------------+
| sname | count(sc.cno) |
+--------+---------------+
| zhang3 | 2 |
+--------+---------------+
1 row in set (0.00 sec)
-- 统计zhang3,学习了几门课，处理同名问题
mysql> select student.sno,student.sname,count(sc.cno)
-> from student join sc on student.sno=sc.sno
-> where student.sname='zhang3'
-> group by student.sno,student.sname;
+-----+--------+---------------+
| sno | sname | count(sc.cno) |
+-----+--------+---------------+
| 1 | zhang3 | 2 |
+-----+--------+---------------+
1 row in set (0.01 sec)
-- 查询zhang3,学习的课程名称有哪些
mysql> select student.sname,course.cname
-> from student join sc
-> on student.sno=sc.sno
-> join course
-> on sc.cno=course.cno
-> where student.sname='zhang3';
+--------+--------+
| sname | cname |
+--------+--------+
| zhang3 | linux |
| zhang3 | python |
+--------+--------+
2 rows in set (0.00 sec)
-- 查询zhang3,学习的课程名称有哪些，处理同名问题
mysql> select student.sno,student.sname,group_concat(course.cname)
-> from student join sc
-> on student.sno=sc.sno
-> join course
-> on sc.cno=course.cno
-> where student.sname='zhang3'
-> group by student.sno,student.sname;
+-----+--------+----------------------------+
| sno | sname | group_concat(course.cname) |
+-----+--------+----------------------------+
| 1 | zhang3 | python,linux |
+-----+--------+----------------------------+
1 row in set (0.00 sec)
-- 查询oldguo老师教的学生名
-- 查询oldguo所教课程的平均分数
-- 每位老师所教课程的平均分,并按平均分排序
-- 查询oldguo所教的不及格的学生姓名
-- 查询所有老师所教学生不及格的信息
-- 查询平均成绩大于60分的同学的学号和平均成绩；
-- 查询所有同学的学号、姓名、选课数、总成绩；
-- 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
-- 统计各位老师,所教课程的及格率
-- 查询每门课程被选修的学生数
-- 查询出只选修了一门课程的全部学生的学号和姓名
-- 查询选修课程门数超过1门的学生信息
-- 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1.3.3 其他功能
- 列别名
-- 用于定制显示的列名
-- 可以在having及之后中的子句中调用
-- 因为select_list的执行顺序在group by之后
mysql> select student.sno,student.sname,group_concat(course.cname)
-> from student join sc on student.sno=sc.sno
-> join course on sc.cno=course.cno
-> where student.sname='zhang3'
-> group by student.sno,student.sname;
+-----+--------+----------------------------+
| sno | sname | group_concat(course.cname) |
+-----+--------+----------------------------+
| 1 | zhang3 | python,linux |
+-----+--------+----------------------------+
1 row in set (0.00 sec)
-- 可以改写为
mysql> select student.sno as '学号',student.sname as '姓名',group_concat(course.cname) as '课程列表'
-> from student join sc on student.sno=sc.sno
-> join course on sc.cno=course.cno
-> where student.sname='zhang3'
-> group by student.sno,student.sname;
+--------+--------+--------------+
| 学号 | 姓名 | 课程列表 |
+--------+--------+--------------+
| 1 | zhang3 | python,linux |
+--------+--------+--------------+
1 row in set (0.00 sec)
mysql> select district as 省,sum(population) as 总人口
-> from world.city
-> where countrycode='CHN'
-> group by district
-> having 总人口 > 5000000
-> order by 总人口
-> limit 5 offset 0;
+-----------+-----------+
| 省 | 总人口 |
+-----------+-----------+
| Anhui | 5141136 |
| Tianjin | 5286800 |
| Hunan | 5439275 |
| Zhejiang | 5807384 |
| Chongqing | 6351600 |
+-----------+-----------+
5 rows in set (0.00 sec)
- 表别名
-- 方便全局调用的别名
-- 因为定位表的执行顺序最先
mysql> select a.sno as '学号',a.sname as '姓名',group_concat(c.cname) as '课程列表'
-> from student as a join sc as b on a.sno=b.sno
-> join course as c on b.cno=c.cno
-> where a.sname='zhang3'
-> group by a.sno,a.sname;
+--------+--------+--------------+
| 学号 | 姓名 | 课程列表 |
+--------+--------+--------------+
| 1 | zhang3 | python,linux |
+--------+--------+--------------+
1 row in set (0.00 sec)
- distinct 应用
-- 作用：去重
mysql> select count(countrycode) from world.city;
+--------------------+
| count(countrycode) |
+--------------------+
| 4079 |
+--------------------+
1 row in set (0.00 sec)
mysql> select count(distinct(countrycode)) from world.city;
+------------------------------+
| count(distinct(countrycode)) |
+------------------------------+
| 232 |
+------------------------------+
1 row in set (0.00 sec)
- union 和 union all
-- 作用：将两张表的结果合并起来，必须是同类型的表
-- 查询中国或美国的城市信息
mysql> select * from world.city where countrycode='CHN' or countrycode='USA';
mysql> select * from world.city where countrycode in ('CHN','USA');
mysql> select * from world.city where countrycode='CHN'
-> union
-> select * from world city where countrycode='USA';
-- union 和 union all 的区别：
-- union：聚合两个结果集，会自动对结果集进行去重
-- union all：聚合两个结果据，不会对结果集进行去重
2. show
-- 查询所有库
show database;
-- 查询use的库中所有的表
show tables;
-- 查询world库下所有的表
show tables from world;
-- 查询所有用户连接情况
show processlist;
-- 查询所有用户连接情况，详细显示info信息
show full processlist;
-- 查看支持的字符集
show charset;
-- 查看支持的校对规则
show collationl
-- 查看支持的引擎信息
show engines;
-- 查看支持的权限信息
show privileges;
-- 查看某用户的权限
show grants for;
-- 查看建库语句
show create database;
-- 查看建表语句
====================================
show create table;
-- 查看表的索引信息
show index;
-- 查询innodb引擎状态
show engine innodb status;
-- 查看数据库状态信息
show status;
-- 模糊查询数据库状态
show status like '%%';
-- 查看所有数据库参数
show variables;
-- 模糊查询部分参数
show variables like '%%';
-- 查询所有二进制日志文件信息
show binary logs
-- 查询二进制日志事件
show binary evnets in
-- 查询二进制日志的位置点信息
show master status;
-- 查询从库状态信息
show slave status;
-- 查看中继日志事件
show relaylog events in
3427
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
