數據庫服務基礎應用操作
數據庫語句應用實踐
1）DCL和數據庫安全有關的操作
grant/revoke # 和授權有關操作
create use/alter user/drop user # 和用户相關操作
commit 可以確保DML語句操作後，產生的數據信息合理存儲到磁盤中
rollback 可以實現DML語句操作後，將改動調整的數據做回退操作
DML- insert update delete 可以保證存儲語句的執行成功
2）DQL可以實現查詢數據庫數據信息
查詢情況1
DQL 可以實現查詢數據庫數據信息
show select
查詢情況1：數據庫中變量信息查詢（變量--配置項信息/監控信息）######################################################
show variables like 'XXX'; 查看配置項變量信息
show global status like 'xxx'; 查看監控項變量信息
select @@max_connections; -- 查看具體某個變量信息
擴展説明：數據庫配置項變量如何改動
兩種方式可以改動配置項
方式一：永久改動
max_connections 設置數據庫最大併發連接數
在vim /etc/my.cnf中更改
[mysqld]
max_connections 永久修改配置需要重啓數據庫服務
方式二：臨時改動
set global max_connextions=3; 配置會影響全部用户功能/當會話斷開重新連接配置不會失效（全局改動）
set session sql_log_bin='off'; 配置只會影響當前用户/當會話斷開重新連接配置會失效（會話改動）
查詢情況2
查詢情況2：可以查看具體數據或對象信息########################################################################
show 查看錶或庫信息或用户或權限或索引...
select 查看具體數據信息
單表數據信息查看
-- 創建單表查看測試數據：
https://dev.mysql.com/doc/index-other.html
加載測試數據-導入數據表到數據庫中進行操作
mysql -uroot -p66666 -S /tmp/mysql.sock <world.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| world |
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
附加：如何導入數據表到mysql中
1.把world.sql移動到數據庫安裝目錄下
[root@mysql local]#ll # 當前在/usr/local
total 603964
............
-rw-r--r-- 1 root root 398629 Mar 1 07:05 world.sql⭐
............
2.使用命令導入world.sql數據表
[root@mysql local]#mysql -uroot -p666666 -S /tmp/mysql.sock <world.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
# 用管理員身份導入world.sql
3.進入數據庫查看
[root@mysql local]#mysql -p666666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; # 展示所有數據庫，發現world數據庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
mysql> use world; # 使用world數據庫
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> show tables; # 查看所有數據表
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
world.sql導入成功
可以進行操作來查看具體內容，比如數據表結構、數據表內容
mysql> desc course; # 查看數據表結構
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(20) | NO | | NULL | |
| tno | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from course; # 查看數據表內容
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | mysql | 103 |
| 1004 | go | 105 |
+------+--------+-----+
4 rows in set (0.00 sec)
單表數據查看練習（基礎查看）
1.單表所有數據查看
1.單表所有數據查看
select * from 庫名.表名;
select 列名01，列表02.. from 庫名.表名;
備註：以上表中全部數據查看操作，儘量少用
2.單表數據過濾查看 （where）⭐
2.單表數據過濾查看 （where）
1）等值條件查看數據信息####################################################################################
查看北京城市人口情況；
mysql> show databases; # 查看所有數據庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| school |
| sys |
| world |
+--------------------+
7 rows in set (0.02 sec)
mysql> use world; # 使用我們需要的數據庫
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> show tables; # 展示所有數據表
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql> desc city; # 查看city數據表的結構
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from city; # 查看city數據表的內容
+------+------------------------------------+-------------+------------------------+------------+
| 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 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
............
#可以得出name是城市名，countrycode是國家，district是省份，population是人口
mysql> select name,population from city where name='peking';
+--------+------------+
| name | population |
+--------+------------+
| Peking | 7472000 |
+--------+------------+
1 row in set (0.01 sec)
2）範圍條件查看數據信息####################################################################################
①查看人口數量大於1000w城市和國家信息
mysql> select name,countrycode,population from city where population>10000000;
+-----------------+-------------+------------+
| name | countrycode | population |
+-----------------+-------------+------------+
| Mumbai (Bombay) | IND | 10500000 |
+-----------------+-------------+------------+
# 數值做範圍查看 > < >= <= <>/!= ⭐⭐⭐
②查看北京上海深圳重慶等一線城市人口數量
mysql> select name,population from city where name in ('peking','shanghai','shenzhen','chongqing');
+-----------+------------+
| name | population |
+-----------+------------+
| Shanghai | 9696300 |
| Peking | 7472000 |
| Chongqing | 6351600 |
| Shenzhen | 950500 |
+-----------+------------+
# 字符信息做範圍查看 in () / not in () ⭐⭐⭐
3) 多個條件列的邏輯關聯查詢################################################################################
多個條件邏輯關聯方式
and --- 將多個條件做交集處理（將過濾條件做逐步處理 得到需要的查詢結果）
or --- 將多個條件做並集處理（將過濾條件做分別處理 交結果進行合併顯示）
①查看中國和美國所有城市以及人口數量情況
mysql> select name,population from city where countrycode='chn' or countrycode='usa';
②查看城市人口數量大於100w並小於300w城市信息
mysql> select name,population from city where population>1000000 and population<3000000;
# 或者另一種表達方式--> and=between and
mysql> select name,population from city where population between 1000000 and 3000000;
4) 條件列做模糊過濾查詢####################################################################################
查詢廣州城市人口信息
mysql> select * from city where countrycode='chn' and district like '%guang%' and name like '%guang%';
+------+--------------------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------------------+-------------+-----------+------------+
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
+------+--------------------+-------------+-----------+------------+
5) 取重查詢數據信息#######################################################################################
獲取中國有多少個省份
select distinct District from city where countrycode='chn';
select count(district) from city where countrycode='chn';
# 統計省份一共有多少
6) 查看空值列信息#########################################################################################
mysql> select * from city where population is null;
查看非空列信息
mysql> select * from city where population is not null;
單表數據查看練習（進階查看）
1.分組聚合查看數據信息
①聚合查看數據
①聚合查看數據 # 查詢數據需要做統計分析 實現聚合查看數據需要應用聚合函數
1.查看中國人口總數
select countrycode,sum(population) from city where countrycode='chn';
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
+-------------+-----------------+
2.查看中國城市數量
select countrycode,count(name) from city where countrycode='chn';
+-------------+-------------+
| countrycode | count(name) |
+-------------+-------------+
| CHN | 364 |
+-------------+-------------+
3.查看中國人口最多的城市信息/查看中國人口最少的城市信息
mysql> select countrycode,max(population) from city where countrycode='chn';
+-------------+-----------------+
| countrycode | max(population) |
+-------------+-----------------+
| CHN | 9696300 |
+-------------+-----------------+
1 row in set (0.05 sec)
mysql> select countrycode,min(population) from city where countrycode='chn';
+-------------+-----------------+
| countrycode | min(population) |
+-------------+-----------------+
| CHN | 89288 |
+-------------+-----------------+
avg() -- 取某個數值列的平均數
max() -- 取某個數值列的最大值
min() -- 取某個數值列的最小值
-- 直接查看列的最大值或最小值
額外：顯示查看中國人口最多的城市信息，顯示人口信息與城市名字
SELECT name,countrycode,population
FROM city
WHERE countrycode = 'chn'
ORDER BY population DESC
LIMIT 1;
+----------+-------------+------------+
| name | countrycode | population |
+----------+-------------+------------+
| Shanghai | CHN | 9696300 |
+----------+-------------+------------+
執行過程（精講版）：
我們可以把它拆解成五個簡單的步驟來理解：
1. FROM city
第一步：去哪個表裏找？
告訴數據庫：“請打開名為 city 的那張表（裏面存着所有城市的數據）。”
2. WHERE countrycode = 'chn'
第二步：篩選哪些行？
告訴數據庫：“在這張表裏，我只關心 countrycode（國家代碼） 等於 'chn' （中國）的那些行。其他的國家（如 'usa', 'jpn'）全部忽略。”
此時，手裏剩下的全是中國的城市數據。
3. ORDER BY population DESC
第三步：怎麼排隊？
告訴數據庫：“把剛才篩選出來的中國城市，按照 population（人口） 這一列進行排序。”
DESC 是 Descend（下降）的縮寫，意思是從大到小排。
此時，人口最多的城市排在了第 1 位，人口最少的排在了最後。
# 升序用ORDER BY population，降序用ORDER BY population DESC
4. LIMIT 1
第四步：只要幾個？
告訴數據庫：“排好隊後，我只要第 1 個（也就是排在最前面、人口最多的那一個），後面的都不要了。”
如果沒有這句話，數據庫會把所有中國城市按人口從大到小全部列出來。
5. SELECT name, countrycode, population
第五步：給我看什麼？
告訴數據庫：“對於最後留下的這唯一一行數據，我只需要看這三列信息：name（城市名）、countrycode（國家代碼） 和 population（人口數）。其他的信息（比如所屬省份 District、城市 ID 等）不用顯示給我。”
②分組查看數據⭐
②分組查看數據 # 表示將某個列相同信息分為一組，然後將分組後數據分別做分析處理
1.查看全球範圍內，每個國家的人口總數
select countrycode,sum(population) from city group by countrycode;
2.查看中國境內，每個省份的人口總數
select district,sum(population) from city where countrycode='CHN' group by district;
知識擴展説明：分組聚合查看數據常見問題
3.查看中國境內，每個省份的城市數量，以及顯示省份的城市信息
select district,count(name),name from city where countrycode='CHN' group by district;
ERROR 1140 (42000):
In aggregated query without GROUP BY,
expression #2 of SELECT list contains nonaggregated column 'world.city.Name';
this is incompatible with sql_mode=only_full_group_by
在聚合查看數據時，需要使用group by做分組操作
在分組聚合處理輸出信息時，某些非聚合處理列，無法正常顯示內容
sql_mode=only_full_group_by 無法實現數據表中一行信息對多行信息關聯顯示
分組聚合查詢原理：
1）會先根據分組列信息，做排序處理
2）會將分組後的相同數據列信息合併成一行
3）會將需要分析統計列做對應聚合處理
解決以上分組聚合輸出問題，可以將多行信息轉換成一行
select district,count(name),group_concat(name) from city where countrycode='CHN' group by district;
命令的執行過程：
1. GROUP BY district —— 先分類
動作：把全中國的城市，按照 district（省份/地區）分成不同的堆。
結果：比如“廣東”一堆，“江蘇”一堆，“北京”一堆……
2. COUNT(name) —— 數個數
動作：在每一堆裏，數一數有多少個城市名字。
結果：得到該地區的城市數量。
例如：廣東 -> 50個，江蘇 -> 40個。
3. GROUP_CONCAT(name) —— 連名字
動作：把每一堆裏的所有城市名字，用逗號 , 連接成一個長長的字符串。
結果：得到該地區的城市名單列表。
例如：廣東 -> "廣州,深圳,珠海,佛山..."
# 分行顯示
select district,count(name),group_concat(name) from city where countrycode='CHN' group by district\G;
2.分組聚合過濾查看數據信息 -- having⭐
2）分組聚合過濾查看數據信息 -- having
查看全球範圍內，每個國家的人口總數，將人口總數大於5000w國家信息顯示出來
mysql> select countrycode,sum(population)
-> from city
-> group by countrycode
-> having sum(population)>50000000
-> ;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| BRA | 85876862 |
| CHN | 175953614 |
| IND | 123298526 |
| JPN | 77965107 |
| MEX | 59752521 |
| RUS | 69150700 |
| USA | 78625774 |
+-------------+-----------------+
having -- 會將聚合列作為條件過濾 （統計分析的數據做過濾）
where -- 會將現有數據列作為條件過濾 （表中有的數據做過濾）
3.數據信息排序查詢
3）數據信息排序查詢
查看全球範圍內，每個國家的人口總數，將人口總數大於5000w國家信息顯示出來，並排序顯示輸出
mysql> select countrycode,sum(population)
-> from city
-> group by countrycode
-> having sum(population)>50000000
-> order by sum(population)
-> ;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| MEX | 59752521 |
| RUS | 69150700 |
| JPN | 77965107 |
| USA | 78625774 |
| BRA | 85876862 |
| IND | 123298526 |
| CHN | 175953614 |
+-------------+-----------------+
-- order by默認排序為升序排序⭐
查看全球範圍內，每個國家的人口總數，將人口總數大於5000w國家信息顯示出來，並按降序顯示輸出
mysql> select countrycode,sum(population)
-> from city
-> group by countrycode
-> having sum(population)>50000000
-> order by sum(population) DESC
-> ;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
| USA | 78625774 |
| JPN | 77965107 |
| RUS | 69150700 |
| MEX | 59752521 |
+-------------+-----------------+
4.截取部分輸出信息顯示
4) 截取部分輸出信息顯示
顯示全球人口總數大於5000w的前3名
mysql> select countrycode,sum(population)
-> from city
-> group by countrycode
-> having sum(population)>50000000
-> order by sum(population) DESC
-> limit 3
-> ;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
+-------------+-----------------+
單表查詢數據常用語句：
select 輸出列信息 from 庫.表 where+group by+having+order by[desc]+limit;
小知識點總結❗️
1.order by排序的升序和降序
升序用ORDER BY population
降序用ORDER BY population DESC
即order by默認就是升序，降序只需要加一個DESC
####################################################################
2.sum()和count()的區別
簡單一句話總結：
COUNT() 是數個數（有多少行？有多少個非空值？）。
SUM() 是加總值（這些數字加起來是多少？）。
問 “多少個？” (How many?) -> 用 COUNT。
問 “總共多少？” (How much total?) -> 用 SUM。
####################################################################
3.where 和 having
having -- 會將聚合列作為條件過濾 （統計分析的數據做過濾）
where -- 會將現有數據列作為條件過濾 （表中有的數據做過濾）
使用where的時候要後group by分組，使用having的時候要先group by分組❗️
where不能使用聚合函數，having可以使用聚合函數，如HAVING COUNT(*) > 5的表達方式
####################################################################
4.SQL的邏輯執行順序
①FROM (找表)
②WHERE (🔴 先過濾原始數據行) -> 此時還沒有分組，沒有聚合值
③GROUP BY (🟢 再分組) -> 此時產生了“組”
④聚合函數計算 (COUNT, SUM, AVG...) -> 此時算出了每組的統計值
⑤HAVING (🔵 最後過濾分組後的結果) -> 此時才能對統計值進行判斷
⑥SELECT (顯示結果)
⑦ORDER BY (排序)
⑧LIMIT (取多少行)
多表數據信息查看（連表信息查看）
多表數據信息查看（連表信息查看）
加載多表查詢測試數據
mysql -uroot -p123456 -S /tmp/mysql.sock <school.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| school
mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| sc |
| student |
| teacher |
+------------------+
相同列可以進行關聯
sc和student兩個表格有相同的列，可以進行關聯
再看一下表的信息teacher和student,沒有相同的列，所以不能直接關聯在一起,可以間接的關聯在一起
數據表連表操作
數據庫連表操作
如果想實現連表操作，必須將有相同列的表進行連接，沒有相同列的表不能做連表操作
方法一：內連接
mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
mysql> select * from teacher join course on teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux | 101 |
| 102 | xiaoQ | 1002 | python | 102 |
| 103 | xiaoA | 1003 | mysql | 103 |
+-----+--------+------+--------+-----+
擴展：內連接語句寫法
select * from 表01,表02,表03,表04 where 表01.列=表02.列 and 表02.列=表03.列 and 表03.列=表04.列;
select * from 表01
join 表02
on 表01.列=表02.列
join 表03
on 表02.列=表03.列
join 表04
on 表03.列=表04.列;
方法二：外連接
select * from teacher left join course on teacher.tno=course.tno;
-- 左外連接，會把左邊的表作為主表，右邊的表為子表
主表數據信息在連接時都會加載顯示，子表中只會加載顯示和主表有關聯信息，沒有關聯的信息用null表示
select * from teacher right join course on teacher.tno=course.tno;
-- 右外連接，會把右表的表作為主表，左邊的表作為子表
主表數據信息在連接時都會加載顯示，子表中只會加載顯示和主表有關聯信息，沒有關聯的信息用null表示
連表查詢數據解題步驟
連表查詢數據有解題步驟：
1）確認庫中表的關聯關係（畫圖顯示錶中關聯關係）
teacher.tno=course.tno course.cno=sc.cno sc.sno=student.sno
2) 根據題目需求，確認需要的數據表信息
3）將多個數據表信息進行連接整合為一張大表
4) 根據整合後的單表做數據查詢調取
練習題
練習題：
01 統計zhang3，學習了幾門課？
需要加載數據表： student course
將數據表進行連接： student.sno=sc.sno sc.cno=course.cno student+sc+course
select * from student join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno;
查詢調取數據信息：
select student.sname,count(course.cname) from student join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
select sname,count(cname) from tea_cou_sc_stu where sname='zhang3';
02 查詢zhang3，學習的課程名稱有哪些？
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';
查詢zhang3，學習課程信息，以及數量信息；
select student.sname,count(course.cname),group_concat(course.cname) from student join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
where student.sname='zhang3';
+--------+---------------------+----------------------------+
| sname | count(course.cname) | group_concat(course.cname) |
+--------+---------------------+----------------------------+
| zhang3 | 2 | linux,python |
+--------+---------------------+----------------------------+
03 查詢xiaoA老師教的學生名？
需要加載表： teacher course sc student
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno;
單表查詢數據：
select teacher.tname,student.sname from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA';
如果有老師重名情況如何統計每個老師授課學生情況
select teacher.tno,teacher.tname,count(student.sname) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA'
group by teacher.tno;
04 查詢xiaoA老師教課程的平均分數？
需要加載表： teacher course sc
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno;
單表查詢數據：
select teacher.tname,avg(sc.score) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
where teacher.tname='xiaoA';
如果有老師重名情況 一個老師可能教授多門課程
select teacher.tno,teacher.tname,avg(sc.score) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
where teacher.tname='xiaoA'
group by teacher.tno,course.cname;
05 每位老師所教課程的平均分，並按平均分排序？
需要加載表： teacher course sc
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno;
單表查詢數據：
select teacher.tno,course.cname,avg(sc.score) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
group by teacher.tno,course.cname
order by avg(sc.score);
06 查詢xiaoA老師教的不及格的學生姓名？
需要加載表： teacher course sc stdent
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno;
單表查詢數據：
select teacher.tname,student.sname,sc.score,course.cname from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA' and sc.score<60;
07 查詢所有老師所教學生不及格的信息？
select teacher.tname,student.sname,sc.score,course.cname from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where sc.score<60;
select 查詢的擴展操作
擴展01：別名查詢數據信息
表別名查詢數據：
select a.tname,d.sname,c.score,b.cname from teacher as a
join course b
on a.tno=b.tno
join sc c
on b.cno=c.cno
join student d
on c.sno=d.sno
where c.score<60;
列別名查詢數據：
select a.tname as '講師名',d.sname as '學生名',c.score,b.cname from teacher as a
join course b
on a.tno=b.tno
join sc c
on b.cno=c.cno
join student d
on c.sno=d.sno
where c.score<60;
擴展02：多表拼接可以實現縱向拼接
mysql> select * from teacher union all select * from teacher02;
+-----+--------+
| tno | tname |
+-----+--------+
| 101 | oldboy |
| 102 | xiaoQ |
| 103 | xiaoA |
| 104 | xiaoB |
| 101 | oldboy |
| 105 | xiaoE |
+-----+--------+
-- 當使用union all和union時，都可以實現縱向拼接兩張表的數據
union all -- 不會將兩張表相同行數據做取重輸出
union -- 會將兩張表相同行數據做取重輸出
擴展03：數據表視圖操作/視圖管理應用
什麼是視圖：
視圖等價於將複雜查詢表的語句做別名設置；
t1 視圖 = select xxx
如果有老師重名情況 一個老師可能教授多門課程
select teacher.tno,teacher.tname,avg(sc.score) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
where teacher.tname='xiaoA'
group by teacher.tno,course.cname;
select * from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno;
create view tea_cou_sc_stu as
select teacher.tno,teacher.tname,course.cno,course.cname,sc.sno,sc.score,student.sname,student.sage,student.ssex from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno;
select sname,count(cname) from tea_cou_sc_stu where sname='zhang3';
-- 利用視圖可以簡化連表語句的應用
總結
數據庫語句應用
1）DCL語句應用 commit/rollback
2）DQL語句應用
查看數據庫變量信息（配置變量/監控變量） show variables like / show global status like/ select @@變量名
數據變量信息如何調整 永久修改 -- vim /etc/my.cnf 臨時修改 -- set global 變量名=值;/set session 變量名=值
查看數據庫對象信息
查看數據庫數據表信息 show
查看數據表數據信息 select
單表查看數據 -- select+from+where+group by+having+order by+limit
多表查看數據 -- 內連接查詢/外連接查詢
內連接查詢：select * from 表01 join 表02 on 表01.列=表02.列
外連接查詢：select * from 表01 left/right join 表02 on 表01.列=表02.列
左外連接/右外連接
連表查詢擴展説明：
-- 連表的別名設置 表別名/列別名
-- 連表的縱向拼接 union all/union
-- 連表後視圖應用 create view 視圖名稱 as 連表語句
作業
課程作業：
01 查詢平均成績大於60分的同學的學號和平均成績
02 查詢所有同學的學號，姓名。選課數，總成績
03 查詢各科成績最高和最低的分，以如下形式顯示：課程ID，最高分，最低分
04 查詢每門課程被選修的學生數
05 查詢出只選修了一門課程的全部學生的學號和姓名
06 查詢平均成績大於85的所有學生的學號、姓名和平均成績
