1.select
1.1作用
獲取MySQL中的數(shù)據(jù)行
1.2單獨使用select
select variables;
查變量(內(nèi)置變量)
mysql> show variables like '%inno%';
模糊匹配
1.2.1select @@xxx
獲取參數(shù)信息
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
1.2.2select 函數(shù)（）
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-06-18 09:26:55 |
+---------------------+
1 row in set (0.00 sec)
mysql> select database();
+------------+
| database() |
+------------+
| school |
+------------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
1.3SQL92標準的使用語法
1.3.1select 語法執(zhí)行順序（單表）
select 開始----->from 字句------>where字句------>group by字句
------->執(zhí)行select后寫的執(zhí)行條件--------->having字句------->order by ------->limit
1.3.2from
查看world表全部內(nèi)容
mysql> use world;
mysql> select * from city;
此操作適合數(shù)據(jù)行少的表
select Name,population from city;
select Name,population from world.city;
查name和population列，可以加絕對路徑
1.3.3單表查詢練習環(huán)境，world數(shù)據(jù)庫下表的介紹
show tables from world;
desc city;
id:自增的無關列，數(shù)據(jù)行的需要
name：城市名
countrycode城市所在國家的編號
district：中國是省的意思，在美國是州的意思
population：人口數(shù)量
剛入職時，DBA的任務
1.搞清楚架構
通過公司架構圖，搞清楚數(shù)據(jù)庫的物理架構
1-2天
邏輯結構：
（1）生產(chǎn)庫的個數(shù)和信息（容易）
（2）庫下表的信息（非常復雜）
1.開發(fā)和業(yè)務人員打好關系
2.搞到ER圖（設計關系圖）（PD）powerDesigner
3. 啥都沒有怎么辦
（1）找到建表語句，如果有注釋，讀懂注釋，如果沒有，只能根據(jù)列名翻譯
（2） 找到表中部分數(shù)據(jù)，分析數(shù)據(jù)特點，達到了解功能的目錄
1.3.2where
例子：
1、where配合等值查詢
查詢city表中，中國城市信息
mysql> select * from city where CountryCode='CHN';
| 1898 | Chengdu | CHN | Sichuan | 3361500 |
| 1899 | Nanking [Nanjing] | CHN | Jiangsu | 2870300 |
| 1900 | Changchun | CHN | Jilin | 2812000 |
| 1901 | Xi′an | CHN | Shaanxi | 2761400 |
| 1902 | Dalian | CHN | Liaoning | 2697000 |
| 1903 | Qingdao | CHN | Shandong | 2596000 |
| 1904 | Jinan | CHN | Shandong | 2278100 |
| 1905 | Hangzhou | CHN | Zhejiang | 2190500 |
| 1906 | Zhengzhou | CHN | Henan | 2107200 |
| 1907 | Shijiazhuang | CHN | Hebei | 2041500 |
| 1908 | Taiyuan | CHN | Shanxi | 1968400 |
查詢美國所有城市
mysql> select * from city where CountryCode='USA';
+------+-------------------------+-------------+----------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-------------------------+-------------+----------------------+------------+
| 3793 | New York | USA | New York | 8008278 |
| 3794 | Los Angeles | USA | California | 3694820 |
| 3795 | Chicago | USA | Illinois | 2896016 |
| 3796 | Houston | USA | Texas | 1953631 |
| 3797 | Philadelphia | USA | Pennsylvania | 15175
2、where配合不等值(>= <= <> > <)
mysql> select * from city where population<'100';
+------+-----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown | PCN | – | 42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> select * from city where population>'10000000';
+------+-----------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------+------------+
| 1024 | Mumbai (Bombay) | IND | Maharashtra | 10500000 |
+------+-----------------+-------------+-------------+------------+
1 row in set (0.00 sec)
3、where配合模糊(like)
mysql> select * from city where countrycode like 'c%';
+------+-----------------------+-------------+---------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------------+-------------+---------------------+------------+
| 1889 | Bangui | CAF | Bangui | 524000 |
| 1810 | Montréal | CAN | Québec | 1016376 |
| 1811 | Calgary | CAN | Alberta | 768082 |
| 1812 | Toronto | CAN | Ontario | 688275
----注意like語句在MySQL中%不能寫到前面，如%C。這樣效率很低，因為不走索引
4、where配合邏輯連接符(and or )
查詢城市人口在2萬到5萬之間的
mysql> select * from city where population>2000 and population<5000;
+------+-----------------+-------------+-------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------------+------------+
| 33 | Willemstad | ANT | Cura?ao | 2345 |
| 54 | Fagatogo | ASM | Tutuila | 2323 |
| 916 | Saint George′s | GRD | St George | 4621 |
| 2256 | Bairiki | KIR | South Tarawa | 2226 |
| 2727 | Yangor | NRU | – | 4050 |
| 3065 | Castries | LCA | Castries | 2301 |
| 3170 | Serravalle | SMR | Serravalle/Dogano | 4802 |
| 3171 | San Marino | SMR | San Marino | 2294 |
| 3423 | Cockburn Town | TCA | Grand Turk | 4800 |
| 3424 | Funafuti | TUV | Funafuti | 4600 |
+------+-----------------+-------------+-------------------+------------+
10 rows in set (0.00 sec)
mysql> select * from city where population between 2000 and 5000;
+------+-----------------+-------------+-------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------------+------------+
| 33 | Willemstad | ANT | Cura?ao | 2345 |
| 54 | Fagatogo | ASM | Tutuila | 2323 |
| 916 | Saint George′s | GRD | St George | 4621 |
| 2256 | Bairiki | KIR | South Tarawa | 2226 |
| 2697 | Plymouth | MSR | Plymouth | 2000 |
| 2727 | Yangor | NRU | – | 4050 |
| 3065 | Castries | LCA | Castries | 2301 |
| 3170 | Serravalle | SMR | Serravalle/Dogano | 4802 |
| 3171 | San Marino | SMR | San Marino | 2294 |
| 3423 | Cockburn Town | TCA | Grand Turk | 4800 |
| 3424 | Funafuti | TUV | Funafuti | 4600 |
+------+-----------------+-------------+-------------------+------------+
11 rows in set (0.01 sec)
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
以上語句改為UNION ALL
UNION
UNION ALL
如果有重復行，UNION會自動去重
UNION ALL
不會去重
mysql> select * from city where countrycode='USA' union all select * from city where countrycode='CHN';
1.3.2group by配合聚合函數(shù)
常用聚合函數(shù)：
**max()** ：最大值
**min()** ：最小值
**avg()** ：平均值
**sum()** ：總和
**count()** ：個數(shù)
group_concat() : 列轉行
應用
顯示每個國家總人口數(shù)：
mysql> select countrycode,sum(population) from city group by countrycode;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| ABW | 29034 |
| AFG | 2332100 |
| AGO | 2561600 |
| AIA | 1556 |
| ALB | 270000 |
| AND | 21189 |
| ANT | 2345 |
| ARE | 1728336 |
| ARG | 19996563 |
| ARM | 1633100 |
| ASM | 7523 |
| ATG | 24000 |
顯示每個國家的總城市個數(shù)
mysql> select countrycode,count(Name) from city group by countrycode;
顯示中國每個省的城市名，列表形式
mysql> select district,group_concat(name) from city where countrycode='CHN' group by district;
運行結果如下圖
顯示中國每個省的總人口數(shù)
select district,sum(population)
排隊，隊后跟的內(nèi)容
from city
從哪個表
where countrycode='CHN'
條件
group by district;
通過什么站隊
image.png
1.3.2having
后過濾，跟where一樣
統(tǒng)計中國的每個省的總人口數(shù)大于999999的省人口數(shù)
mysql> select district,sum(population) from city where countrycode='CHN' group by district having sum(population)>9999999;
+--------------+-----------------+
| district | sum(population) |
+--------------+-----------------+
| Heilongjiang | 11628057 |
| Liaoning | 15079174 |
| Shandong | 12114416 |
+--------------+-----------------+
3 rows in set (0.00 sec)
說明：Having語句后的條件不走索引，如果數(shù)據(jù)量大，則需要優(yōu)化
1.3.2order by
從小到大
mysql> select district,sum(population) from city where countrycode='CHN' group by district order by sum(population);
從大到小
mysql> select district,sum(population) from city where countrycode='CHN' group by district order by sum(population) DESC;
顯示中國城市信息和人口數(shù)
select *
from city
where countrycode='CHN'
order by population DESC;
1.3.2limit
limit m,n 跳過M行顯示N行
limit x offset y 跳過y行顯示x行
顯示前幾名：
select *
from city
where countrycode='CHN'
order by population DESC
LIMIT 5;
跳過五個開始顯示后5個。
select *
from city
where countrycode='CHN'
order by population DESC
LIMIT 5,5;
同上
select *
from city
where countrycode='CHN'
order by population DESC
LIMIT 5 offset 5;
拓展：
統(tǒng)計行數(shù)：
select count(*) xxxx;
select count(1) xxxx;
1.4多表連接查詢
1.4.1表結構關系（略）
1.4.2什么時候用
需要查詢的數(shù)據(jù)來自于多張表時
1.4.3怎么多表連接查詢
傳統(tǒng)連接：基于where條件
1.找表之間的關系列
2.排列查詢條件
SELECT
city. NAME,
country. NAME,
country.surfacearea
FROM
city,
country
WHERE
city.countrycode = country. CODE
AND city.population < 100;
自連接（自己了解）
內(nèi)連接join on******
1.找關系列
2.將2表放在join左右
3.將關聯(lián)條件放在On后面
4.將所有查詢條件進行羅列
a.x b.y
a join b on a.x=b.y
例子1：
查詢世界上人口數(shù)量小于100人的國家的名城市名和國土面積
SELECT
city. NAME,
country. NAME,
country.surfacearea
FROM
city JOIN country
ON city.countrycode = country. CODE
WHERE
city.population < '100';
例子2：
查詢oldguo老師所教課程，及教了幾門課和課程名稱
SELECT course.cno,course.cname,SUM(score.score)
FROM course
JOIN score
ON course.cno = score.cno
GROUP BY course.cname;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'school.course.cno' which is not functionally dependent on
columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by
mysql>
原因1:在 select后面出現(xiàn)的列，不是分組的條件，并且，沒有在函數(shù)內(nèi)包裹
原因2:如果group by后是主鍵列，或者唯一列，則不會報錯
---6.查詢oldboy老師教的學生的不及格的成績的學生名字
SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.`sname`,":",score.`score`))
FROM teacher
JOIN course
ON teacher.tno=course.`tno`
JOIN score
ON course.`cno`=score.`cno`
JOIN student
ON score.`sno`=student.`sno`
WHERE teacher.`tname`='oldguo'
AND score.`score`<60
GROUP BY teacher.`tno`;
---7. 8.統(tǒng)計zhang3,學習了幾門課
SELECT
student.sname,
course.cname
from
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
WHERE student.sname='zhang3'
--9查詢oldguo老師教的學生名
SELECT
teacher.tname,student.sname
from
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
WHERE teacher.tname='oldboy'
--- 10.查詢oldguo所教課程的平均分數(shù)
SELECT
teacher.tname,course.cname,AVG(score.score)
FROM
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
GROUP BY course.cno
HAVING teacher.tname='oldguo';
--- 11.每位老師所教課程的平均分,并按平均分排序
SELECT
teacher.tname,course.cname,AVG(score.score)
FROM
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
GROUP BY course.cno
--- 12.查詢oldguo所教的不及格的學生姓名
SELECT
teacher.tname,
student.sname,
score.score
FROM
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
WHERE
teacher.tname = 'oldguo'
HAVING
score.score<60;
--- 13.查詢所有老師所教學生不及格的信息
SELECT
teacher.tname,
student.sname,
score.score
FROM
teacher
JOIN course ON teacher.tno = course.tno
JOIN score ON course.cno = score.cno
JOIN student ON score.sno = student.sno
WHERE
score.score<60;
外連接*****
