mysql資料庫實戰第25課,mysql實訓5答案

本文目錄一覽：

• 1、提高MySQL資料庫查詢效率有哪些技巧？
• 2、mysql資料庫
• 3、mysql資料庫面試題（學生表_課程表_成績表_教師表）
• 4、如何利用MySQL資料庫鎖定和解鎖資料庫表
• 5、mysql怎麼查看錶結構和注釋
• 6、在MYSQL資料庫里如何建立兩個表的關聯

提高MySQL資料庫查詢效率有哪些技巧？

1．合理使用索引

索引是資料庫中重要的數據結構，它的根本目的就是為了提高查詢效率。現在大多數的資料庫產品都採用IBM最先提出的ISAM索引結構。索引的使用要恰到好處，其使用原則如下：

●在經常進行連接，但是沒有指定為外鍵的列上建立索引，而不經常連接的欄位則由優化器自動生成索引。

●在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引。

●在條件表達式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在僱員表的「性別」列上只有「男」與「女」兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。

●如果待排序的列有多個，可以在這些列上建立複合索引（compound index）。

●使用系統工具。如Informix資料庫有一個tbcheck工具，可以在可疑的索引上進行檢查。在一些資料庫伺服器上，索引可能失效或者因為頻繁操作而使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進行修復。另外，當資料庫表更新大量數據後，刪除並重建索引可以提高查詢速度。

2．避免或簡化排序

應當簡化或避免對大型表進行重複的排序。當能夠利用索引自動以適當的次序產生輸出時，優化器就避免了排序的步驟。以下是一些影響因素：

●索引中不包括一個或幾個待排序的列；

●group by或order by子句中列的次序與索引的次序不一樣；

●排序的列來自不同的表。

為了避免不必要的排序，就要正確地增建索引，合理地合併資料庫表（儘管有時可能影響表的規範化，但相對於效率的提高是值得的）。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的列的範圍等。

3．消除對大型錶行數據的順序存取

在嵌套查詢中，對錶的順序存取對查詢效率可能產生致命的影響。比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢10億行數據。避免這種情況的主要方法就是對連接的列進行索引。例如，兩個表：學生表（學號、姓名、年齡……）和選課表（學號、課程號、成績）。如果兩個表要做連接，就要在「學號」這個連接欄位上建立索引。

還可以使用並集來避免順序存取。儘管在所有的檢查列上都有索引，但某些形式的where子句強迫優化器使用順序存取。下面的查詢將強迫對orders表執行順序操作：

SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num;1001) OR order_num=1008

雖然在customer_num和order_num上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句：

SELECT ＊ FROM orders WHERE customer_num=104 AND order_num;1001

UNION

SELECT ＊ FROM orders WHERE order_num=1008

這樣就能利用索引路徑處理查詢。

4．避免相關子查詢

一個列的標籤同時在主查詢和where子句中的查詢中出現，那麼很可能當主查詢中的列值改變之後，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉儘可能多的行。

5．避免困難的正規表達式

MATCHES和LIKE關鍵字支持通配符匹配，技術上叫正規表達式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE 「98_ _ _」

即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode ;「98000」，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。

另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] ;「80」，在where子句中採用了非開始子串，因而這個語句也不會使用索引。

6．使用臨時表加速查詢

7．用排序來取代非順序存取

mysql資料庫

怎麼可能呢 你要注意添加數據的類型和建表的一致 舉例如下： //建立一個表 create table tab_nam( age int, name char(10) ) //往裡插入數據 insert into tab_nam(age,name) values(11,’aaa’) insert into tab_nam(age,name) values(22,’bbb’) ….

mysql資料庫面試題（學生表_課程表_成績表_教師表）

Student(Sid,Sname,Sage,Ssex)學生表

Sid：學號

Sname：學生姓名

Sage：學生年齡

Ssex：學生性別

Course(Cid,Cname,Tid)課程表

Cid：課程編號

Cname：課程名稱

Tid：教師編號

SC(Sid,Cid,score)成績表

Sid：學號

Cid：課程編號

score：成績

Teacher(Tid,Tname)教師表

Tid：教師編號：

Tname：教師名字

1、插入數據

2、刪除課程表所有數據

3、將學生表中的姓名 張三修改為張大山

或者

4、查詢姓』李』的老師的個數：

5、查詢所有課程成績小於60的同學的學號、姓名：

6、查詢沒有學全所有課的同學的學號、姓名

7、查詢平均成績大於60分的同學的學號和平均成績

8、查詢學過「100」並且也學過編號「101」課程的同學的學號、姓名

9、查詢「100」課程比「101」課程成績高的所有學生的學號

10、查詢課程編號「100」的成績比課程編號「101」課程高的所有同學的學號、姓名

11、查詢學過「魯迅」老師所教的所有課的同學的學號、姓名

12、查詢所有同學的學號、姓名、選課數、總成績

13、查詢至少有一門課與學號為「1」同學所學相同的同學的學號和姓名

14、把「SC」表中「魯迅」老師教的課的成績都更改為此課程的平均成績，

錯誤

15、查詢和「2」學號的同學學習的課程完全相同的其他同學學號和姓名

16、刪除學習「魯迅」老師課的SC表記錄

17、向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號「003」課程的同學學號、002號課的平均成績

18、查詢各科成績最高和最低的分：以如下的形式顯示：課程ID，最高分，最低分

19、按各科平均成績從低到高和及格率的百分數從高到低順序

20、查詢如下課程平均成績和及格率的百分數(用」1行」顯示): 數學（100），語文（101），英語（102）

22、查詢不同老師所教不同課程平均分從高到低顯示

23、查詢如下課程成績第3名到第6名的學生成績單：數學（100），語文（101），英語（102）

23、統計下列各科成績，各分數段人數：課程ID，課程名稱，[100-85],[85-70],[70-60],[ 小於60]

24、查詢學生平均成績及其名次

25、查詢各科成績前三名的記錄（不考慮成績並列情況）

26、查詢每門課程被選修的學生數

27、查詢出只選修一門課程的全部學生的學號和姓名

28、查詢男生、女生人數

29、查詢姓「張」的學生名單

30、查詢同名同姓的學生名單，並統計同名人數

31、1981年出生的學生名單（註：student表中sage列的類型是datetime）

32、查詢平均成績大於85的所有學生的學號、姓名和平均成績

33、查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列

34、查詢課程名稱為「英語」，且分數低於60的學生名字和分數

35、查詢所有學生的選課情況

36、查詢任何一門課程成績在70分以上的姓名、課程名稱和分數

37、查詢不及格的課程，並按課程號從大到小的排列

38、查詢課程編號為「101」且課程成績在80分以上的學生的學號和姓名

39、求選了課程的學生人數：

40、查詢選修「魯迅」老師所授課程的學生中，成績最高的學生姓名及其成績

41、檢索至少選修兩門課程的學生學號

42、查詢全部學生都選修的課程的課程號和課程名(1.一個課程被全部的學生選修，2.所有的學生選擇的所有課程)

43、查詢沒學過「魯迅」老師講授的任一門課程的學生姓名

44、查詢兩門以上不及格課程的同學的學號及其平均成績

45、檢索「101」課程分數小於60，按分數降序排列的同學學號

46、刪除「2」同學的「101」課程的成績

如何利用MySQL資料庫鎖定和解鎖資料庫表

第一步，創建資料庫表writer和查看錶結構，利用SQL語句：

create table writer(

wid int(10),

wno int(10),

wname varchar(20),

wsex varchar(2),

wage int(2)

);

desc writer;

如下圖所示：

第二步，向資料庫表writer插入五條數據，插入後查看錶里數據，如下圖所示：

第三步，利用鎖定語句鎖定資料庫表writer，利用SQL語句：

lock table writer read;

讓資料庫表只讀不能進行寫

如下圖所示：

第四步，為了驗證鎖定效果，可以查看資料庫表數據，利用SQL語句：

select * from writer;

如下圖所示：

第五步，利用update語句對id=5進行更新，SQL語句為：

update writer set wname = ‘胡思思’ where id = 5;

如下圖所示：

6

第六步，利用unlock進行解鎖，SQL語句為：

unlock tables;

如下圖所示：

mysql怎麼查看錶結構和注釋

MySQL 查看錶結構簡單命令。

一、簡單描述表結構，欄位類型desc tabl_name;

顯示錶結構，欄位類型，主鍵，是否為空等屬性，但不顯示外鍵。

二、查詢表中列的注釋信息

select * from information_schema.columns where table_schema = ‘db’ #表所在資料庫

and table_name = ‘tablename’ ; #你要查的表

三、只查詢列名和注釋

select column_name,

column_comment from information_schema.columns where table_schema =’db’ and

table_name = ‘tablename’ ;

四、#查看錶的注釋

select table_name,table_comment from information_schema.tables where table_schema = ‘db’ and table_name =’tablename’

ps：二～四是在元數據表中查看，我在實際操作中，常常不靈光，不知為什麼，有了解的大俠請留印。

五、查看錶生成的DDL show create table table_name;

在MYSQL資料庫里如何建立兩個表的關聯

1、首先我們打開Workbench創一個建資料庫（這裡都使用閃電1執行選定命令行)。

2、先創建Student學生表。

3、再創建course課程表。

4、然後就可以創建sc關聯表了我們先寫上Student的主鍵和course的主鍵,並寫上sc自己的屬性成績。

5、再寫上主鍵約束，以及把sc表的學號屬性和Studnet的學號關聯、課程號屬性和course的課程號關聯。

6、再次運行就可以看到我們成功創建了學生表和課程表的關聯表sc。