MySQL之DQL

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)]

在排序后会出现中间结果:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k8CNsrX9-1592143746322)(9. DQL/image-20200614144326805.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)]

  • 外连接
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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值