select應用場景:
1單獨使用;
檢視端口号:select @@port;
檢視使用者和主機:select user,host from mysql.user;
格式化檢視使用者和主機:select user,host from mysql.user\G;
模糊查詢含有innodb的指令:show variables like '%innodb%'
2配合函數使用:
查時間:select now();
查詢目前所在的庫: select database();
查詢庫名:
mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 47
Current database: *** NONE ***
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tangTest |
| testku |
+--------------------+
6 rows in set (0.04 sec)
使用mysql這個庫:
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
檢視目前的庫名:
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
檢視目前資料庫版本:
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
mysql>
SQL92标準的使用文法
select文法執行順序:
select開始--->from子句--->where子句--->group by子句--->select後執行條件--->having子句--->order by --->limit
--- select語句應用
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiI0gTMx81dsQWZ4lmZf1GLlpXazVmcvwFciV2dsQXYtJ3bm9CX9s2RkBnVHFmb1clWvB3MaVnRtp1XlBXe0xCMy81dvRWYoNHLwEzX5xCMx8FesU2cfdGLwMzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5iN5AjMzEjYlNDO5kjYjJ2YyYzX3UDNwUTMxEzLcdDMyIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjLyM3Lc9CX6MHc0RHaiojIsJye.png)
1進入world這個庫,查詢city表的是以資料:
相對路徑寫法:
use world;
select * from city;
絕對路徑寫法:
select * from world.city;
2查詢name和populaton這兩個列的所有值:
相對路徑寫法:
use world
select name, population from city;
絕對路徑寫法:
select name, population from world.city;
3查詢庫裡所有的表:
show tables from world;
4列出表中的資訊:
DSC city;
---where語句用法:
1等值查詢(=)
查詢中國城市資訊
select * from world.city where countrycode='CHN';
2不等值查詢(>,<,>=,<=,<>)
查詢世界人口小于100人的城市
select * from world.city where population<100;
3模糊查詢(需配合like)
查詢國家代号是c開頭的城市:
select * from world.city where countrycode like 'c%';
統計國家代碼以c開頭的城市一共有多少行?
select count(*) from world.city where countrycode like 'c%';
4配合邏輯連接配接符查詢(and ,or)
統計大于等于1萬小于等于3萬的城市
select * from world.city where population >= 10000 and population <= 20000;
select * from world.city where population between 10000 and 20000;
5查詢一下中國或美國的城市資訊
select * from world.city where countrycode='CHN' or countrycode='USA';
select * from world.city where countrycode in ('CHN','USA');
6查詢一下中國或美國的城市資訊(推薦):
union和union all的意思是聚合;但union可以去重,union all不去重。
select * from world where countrycode='CHN' union all select * from world.city where countrycode='USA'
-- group by 配合聚合函數應用
常用的聚合函數:
avg()
count()
max()
min()
group_concat()
1統計世界上每個國家的人口數:
解釋:以國家代碼進行分組,以國家代碼進行聚合運算
select countrycode,sum(population) from world.city group by countrycode;
2統計每個國家的城市個數
字段name是城市名的字段
select countrycode, count(name) from world.city group by countrycode;
3統計每個國家的省名清單
count()函數表示将多個字元串連接配接成一個字元串,而group_concat()則是以group by為條件拼接字元串
這裡的district是省的字段名
select coutrycode,group_concat(district) from world.city group_by countrycode;
4統計中國每個省的城市名清單
select district, group_concat(name) from worls.city where countrycode='CHN' group by district;
5統計中國每個省的總人口數
select discrit, sum(population) from world.city where countrycode='CHN' gropy by district;
6統計一下中國每個省的平均人口數
select dictrict, avg(population) from world.city where countrycode='CHN' group by district;
-- having:對處理的結果進行再過濾(後過濾):
統計中國的每個省的人口數量大于1000萬的省人口數
select district,sum(population) from world.city where countrycode='CHN' group by district having sum(population)>10000000;
統計中國每個省的總人口數并從小到大排序
select district sum(population) from world.city where countrycode='CHN' group by district order by sum(population);
從大到小排列中國每個省的人口數
select district sum(population) from world.city where countrycode='CHN' group by district order by dum(population)
DESC;
查詢中國所有城市,并以人口數降序排列
select * from world.city where countrycode='CHN' order by popelation DESC;
-- limit:分頁顯示(一般都配合order by一起使用):
查詢中國所有城市,以人口數量升序排列,隻顯示前五
select * from world.city where countrycode='CHN' order by population limit 5;
查詢中國所有城市,以人口數量升序排列,顯示6-8行
limit 5,5:第一個5表示跳過前5行,第二個3表示顯示3行,其實就是顯示6-8行。
select * from world.city where countrycode='CHN' order by population limit 5,3;
查詢中國所有城市,以人口數量升序排列,顯示6-10行
limit 5 offset 5:第一個5表示顯示5行,第二個5表示偏移5行
select * from world.city where countrycode='CHN' order by population limit 5 offset 5;