mysql刷題題後感

程序计算机人發表於2024-05-03

MySql

　　刷題的時候會將一些題目收藏，也會收藏錯題，做完了整合一下，會有一些補充知識點。

函式篇（和長篇大論的查詢有交集）

Q1：

查詢語句select stuff('lo ina',3, 1, 've ch')結果為？love

love china

china love

china
答：stuff，刪除並加入字元，STUFF(原字元, 開始位置, 刪除長度, 插入字元)
從指定的起點處開始刪除指定長度的字元，並在此處插入另一組字元，所以這個題的答案應該是 love china

Q2：

1.A中SQL語句查詢不出列值為NULL的欄位，此時需對欄位為NULL的情況另外處理

2.null與任何欄位相比都會返回false，為此，oracle提供了一個is null片語判斷null。空字串不是null
查詢null的值:select * from student_table where name is null ;
3.查詢非null的值，就需要使用is not null片語判斷

A的結果只有1001，1005兩條記錄，<>無法對null做篩選；

C的結果只有1001，1004，1005兩條記錄，length無法對null做篩選；

D的結果只有1001，1005兩條記錄，length無法對null做篩選；

B的結果才是正確的！

Q3：

　　解析：RANK() OVER(PRITITION xxx ORDER BYxxx)作用已經在長篇大論的查詢裡面講了。以及over是視窗函式。

補充：

Q4：

　　解析：正確答案是DF。

　　主要是，score不是主鍵，所以score可能為空。

Q5：

delete刪除資料：

delete from <表名> [where條件]

Q6：

　　解析：

ROUND() 函式用於把數值欄位舍入為指定的小數位數；

TRUNCATE() 函式是按照小數位數進行數值擷取，沒有四捨五入。

　　嗯，就是，D選項是2，C選項是1.9，A選項是2，B選項是2.0，所以C不行。

　　也就是說TRUNCATE的引數是從左到右數，比如2.83 後面的0，其實就是不要小數位了。

Q7：

　　（這一道題好難分類啊），D選項是錯的，欄位 = 別名僅限 SQL Server 。

Q8：

　　解析：連結：

1. drop是完全刪除表，包括表結構

2. delete是刪除表資料，保留表的結構，而且可以加where,只刪除一行或者多行

3. truncate 只能刪除表資料，會保留表結構，而且不能加where

Q9：

　　解析：

考察IN 與BETWEEN AND的區分（數字型）

1.in (xx,xx,xx,...) 通常是不連續的

2.BETWEEN AND 注意是閉區間比方說 BETWEEN 1 AND 199，是1-199，含1和199

連線篇（有一些連線題目我歸到長篇大論的查詢裡面了）

Q1：

Mysql中表student_table(id,name,birth,sex)，插入如下記錄：
('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '張三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');
('1006' , '張三' , '2001-12-02' , '女');
執行
select t1.name from
(select * from student_table where sex = '女')t1
inner join
(select * from student_table where sex = '男')t2
on t1.name = t2.name;
的結果行數是（）？
4
3
2
1
答：這裡的連線是inner join ，內連線，就是左右外連線刪掉null的行數。主表是女表，所以說至少有兩行，null是不參加配對的，所以應該是1行。
這裡是官方解析：inner join時只會對非NULL的記錄做join,並且2邊都有的才會匹配上，所以結果只有'張三'，是1行，選D。

Q2：

具體的解析在長篇大論的查詢裡面有講啦，主表是男，所以至少4條，然後由於null是沒有辦法匹配的（嗯，null和任何值都是false，是比較獨立的值，length和<>也不能識別null，嗯，所以有了is NULL 語句），張三那一條又只有一條匹配的，所以最多就是4條。

Q3：

（不放題目了） join 和inner join是等同的。

長篇大論的查詢

Q1:

student_table(id,name,birth,sex)，查詢男生、女生人數分別最多的3個姓氏及人數。
select *
from (select sex,substr(name,1,1) AS first_name , count(*) AS c1
from student_table where length(name) >= 1 AND sex = '男'
GROUP BY first_name
ORDER BY sex , c1 desc LIMIT 3
)t1
UNION ALL
select *
from(select sex , substr(name , 1,1) AS first_name , count(*) AS c1
from student_table where length(name) >= 1 AND sex = '女'
GROUP BY first_name
ORDER BY sex , c1 desc LIMIT 3
)t2

解析：length是為了避免連姓名都沒有。這裡GROUP BY不用連線sex，是因為where已經挑出了sex的條件，所以group by就沒有必要了，試想一下都是男性沒必要透過sex分組啊，這裡ORDER BY也加上了sex，其實可以不用加的。還有一個需要注意的，新建的表必須加一下別名。

我這裡貼一下其他的錯誤選項：

　　one.
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '男'
group by first_name order by sex ,c1 desc limit 3
union all
SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1 and sex = '女'
group by first_name order by sex ,c1 desc limit 3 ;

　　解析：這選項的錯誤原因是order by 和 union的優先順序問題，union的優先順序高過order by。

　　　　　建議對比一下正確選項，記一下正確選項的union寫法。

在Mysql的參考手冊中,並沒有對union和order by的優先順序進行說明,它建議的方法是,對SQL語句加上（）,這樣能使SQL的語義更清晰.

　　second.

SELECT sex ,substr(name,1,1) as first_name ,count(*) as c1
from student_table where length(name) >=1
group by first_name , sex order by sex ,c1 desc limit 3 ;

　　　解析：該題目是三條記錄，男生女生的人數是不確定。

Q2:

這裡寫一下正確選項：

SELECT player_id, device_id

FROM（SELECT * ,rank（） over (partition by player_id order by event_date ) as rank_date from gamelist ）t

WHERE t.rank_date=1

　　解析：連結：

Rank()函式：為結果集分割槽中每一行分配一個排名，行等級由一加上前面的等級指定。

RANK（） OVER（

PARTITION BY　表示式 ##將結果集劃分為分割槽

ORDER BY　表示式　［ASC｜DESC］ ##對分割槽內的進行排序

）

這裡是圖示：

全部輸出的話：

單純將rank輸出的話：

　　可以看出來，RANK () OVER 就是對那列進行分組，對每一列的event_date排序，然後按從上到下的順序標等級（rank），講到這裡就應該懂了。

Q3：

很熟悉對吧，這個專項練習就喜歡考這個。

　　full join，在mysql(至少版本8之前包括版本8）都是不支援full join，所以替代語句是left join + right join （語句的實現中間還要加一個union all），接下來是正確選項：

select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
union all
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
where t1.name is null ;

　　同樣的，這裡也要先select * ，然後加上括號（原因前幾題講過）。

　　下面的連結本來是想借一下圖的，但是發現寫的很好，建議去看下面的連結。

借了一下該連結的資料：MySQL: 圖解 inner join、left join、right join、full outer join、union、union all的區別_mysql中union和inner join區別-CSDN部落格

left join:　

　　其實就是和left join連著的表是主表，所以直接將表的左邊換成主表（也就是說至少是主表的行數），有關係的將右表的相關記錄連上，如果沒有的就記上null。

right join:

　　和left joiin 類似的，嗯，比如說table b RIGHT JOIN table a，其實就可以轉換成table a LEFT JOIN table b。然後就和left join 一樣了。

　　看，只是位置不一樣，但是主表是一樣的。

full join:

　　我們看一下結果，前四行是a left join b，後四行原本是a right join b，然後刪去了幾行重複的，在下面的例子就是刪掉了重複的| 1 | Pirate | 2 | Pirate |和| 3 | Ninja | 4 | Ninja |

所以right join那裡要選擇null的。

+------+-----------+------+--------+
| id | name | id | name |
+------+-----------+------+--------+
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |

| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vade |
| 3 | Ninja | 4 | Ninja |
+------+--------+------+------------+

　　inner join：

　　inner join就是兩者都有的記錄。

我本以為我學會了，我就不會錯了，但是之後我還是載了，因為我不看題目（真的嗎）

哦對了，full outer join 和full join 是一樣的。

Q4：

　　收藏的題目，額，應該是想要記錄一個點。

　　這裡是使用min，然後記錄一下錯誤選項：

SELECT player_id, min(event_date) as first_login FROM gameList WHERE min(event_date) GROUP BY player_id

　　這個就是語法錯誤，我想要記錄的是各個語句的書寫順序。

　　書寫順序：

　　select[distinct]
　　from
　　join（如left join）
　　on
　　where
　　group by
　　having
　　union
　　order by
　　limit

　　執行順序：　　

　　from
　　on
　　join
　　where
　　group by
　　having
　　select
　　distinct
　　union
　　order by

　　（對，沒錯，order by比select 後，選出條件後的表然後再排序，好像是這麼一個邏輯，但是也可以先order ，不過這樣的資料可能會處理得多。當然這個順序不要硬記，記幾個就可以了，比如order 比 select 慢，union 比 order by 快，遇到了再記。

Q5：

　　解析：

MySQL 中使用 REGEXP 來操作正規表示式的匹配。

其中

^ 該符號表示匹配輸入字串的開始位置；
$表示匹配輸入字串的末尾位置；
[...] 表示匹配所包含的任意一個字元；
[^...]表示不能匹配括號內的任意單個字元；
x|y 這條豎線表示匹配x 或匹配y。

　　所以這道題選c。

Q6：

　　解析：

注意MySQL的儲存過程和sql server寫法不一樣。

由題目知道student表中的資料列名為Stu_ID，因此排除C；

變數名定義為s_no，因此排除D；

A和B的差異在於定義s_no時是否有AS，在MySQL中AS是可省略的，因此AB均正確

　　上面的官方解析講了跟沒有講似的。這是推薦參考的連結：儲存過程_w3cschool 是mysql的

　　題目那個更像是sql server 的格式：　　詳細例項全面解析SQL儲存過程-SQL 教程-w3cschool

　　先記一下，之後要寫的話寫熟悉就行了。

Q7：

（這種收藏就是為了自己寫一遍，很簡單的，邏輯是成績表和學號表的sno聯絡好找到cid，然後找cid在sno 為1909 的學生選的課程，這樣子就是至少）

Q8：

（寫這種的時候，真的好痛苦）

建立檢視語句如下 create view 檢視名 as select * from 表名 where 條件

先說一下，這一道題選B，B是錯的，因為where語句語法不對。

A和C的選項，一個是表示qcontent(題目內容），一個是qcid（題目id），然後都是建立檢視的語句，很簡單。

D選項
首先select語句中，view_teacher透過as起了別名question_2，所以在select時是找不到view_teacher.tname和view_teacher.v_tid的，因此會報錯“Unknown column 'view_teacher.tname' in 'field list'”；其次，在create view語句中，question_2這個別名是找不到的，因此會報錯“Table 'test_db.question_2' doesn't exist”。

這一個選項可以看成是先select然後再create（仔細看，select有分號），當然你要是問我為什麼要這麼做，我只能說我也沒見過，不應該是這種寫法，select是選出列形成一張表，但是create view確實從view_teacher(question_2)中create，沒有邏輯啊。

Q9：

　　解析：看了官方解析很久，看不太懂突然想了一下有評論區啊。

（遮蔽的那個是DEFAULT）

Q10:

　　解析：alter是改類名的，update是改資料的，在前面還是後面的題講過，update是資料操縱語言，ALTER確實資料定義語言。

正確的UPDATE更新語法為：

UPDATE table_name SET field1 = new-value1, field2 = new-value2 [WHERE Clause];

所以D選項才是正確的；

A錯在沒有新增WHERE指定篩選行，所以會更新全部資料！

B錯在更新多列資料的間隔符號是逗號，不是AND語句；（注意，這裡是逗號，不是AND）

C錯在REPLACE函式雖然可以批次修改資料，但是REPLACE的正確語法應該是：

REPLACE INTO tab_name(field1, field2...) VALUES (value1, value2)...;（和INSERT 是同語法）

因此此題用不到REPLACE批次修改的方法。

Q11：

已知員工表如下圖所示，員工編號依次遞增，現需改變相鄰員工的編號，當員工總人數為奇數是，不需要改變最後一個員工的編號。下列SQL語句不正確的是（）

表employee：

+-----+-----------+

| eno | ename |

+-----+-----------+

| 1 | 小李 |

| 2 | 小王 |

| 3 | 小剛 |

| 4 | 小虎 |

+----+------------+

　　解析：評論區解析

首先理解什麼叫做改變相鄰員工編號：

比如 | 1 | 小李 | | 2 | 小王 | | 3 | 小剛 | | 4 | 小虎 |更改後就是 | 2 | 小李 | | 1 | 小王 | | 4 | 小剛 | | 3 | 小虎 |

所以，如果原來的eno 是偶數，應該減1；(如果原來的eno是奇數，而且不是最後一個員工的話，應該加1;如果原來的eno 是奇數而且是最後一個，eno不改變)

其次是流程控制函式IF()。IF(expr1,expr2,expr3) 如果expr1為真，則返回expr2，否則返回expr3

eno=(SELECT COUNT(DISTINCT eno) FROM employee) 就是eno 恰好等於員工人數（而員工編號其實沒有重複的，所以distinct 意義不大）的情況

　　（那個row_NUMBER()有講過。這個解釋是真的好，所以搬過來了）

Q12：

　　解析：DATEDIFF(d1,d2)，計算日期 d1->d2 之間相隔的天數，d1-d2，負值說明前一個日期比後一個小，所以D的意思就是d2是前一天，d1是後一天，d1的kilometer小於d2的了

DATE_ADD()函式
1、定義：函式向日期新增指定的時間間隔。
2、語法：DATE_ADD(date,INTERVAL expr type)

date 引數是合法的日期表示式。

expr 引數是希望新增的時間間隔。

　　（其實c選項也是錯的）

Q13：

　　其實就是希望可以寫一下，D選項其實也是對的，官方出的答案是C。

　　case語句可以在select中出現（在經典題裡面也出現了）

Q14：

　　解析：其實就是，行轉列，那麼就要按照sno分組，然後用select來選出列。

資料庫原理篇：（原理嘛，記一下）

Q1：

　　官方解析：
資料控制語言DCL，主要用於對使用者許可權的授權和回收；A選項，DDL主要的命令有CREATE、ALTER、DROP等，大多在建立表時使用；B選項，DML包括SELECT、UPDATE等，對資料庫裡的資料進行操作；D選項，資料庫事務包括COMMIT、ROLLBACK等，主要用於對事務的提交、回收和設定儲存點。

補充：DDL ，資料定義語言。DML，資料操縱語言。TCL，事務控制語言，TCL包括COMMIT（提交）命令、ROLLBACK（回滾）命令、SAVEPOINT（儲存點）命令：

Q2：

下列關於檢視的相關概念描述不正確的是（)

檢視可以解決檢索資料時一個表中得不到一個實體所有資訊的問題
檢視是一種資料庫物件，是從資料庫的表或其他檢視中匯出的基表
若基表的資料發生變化，則變化也會自動反映到檢視中
資料庫儲存的是檢視的定義，不存放檢視對應的資料

解析：檢視是虛擬表，檢視所引用的表被稱為檢視的基表。這裡選的是B

補充知識點：資料庫中為什麼要建立檢視，它有什麼好處？-CSDN部落格

只是為了查詢某一個資訊然後去建立一個和其他表資料高度冗餘的表，是不好的，所以就選需要檢視。

建立檢視的語句：建立檢視語句如下 create view 檢視名 as select * from 表名 where 條件

比如：CREATE VIEW view_s AS SELECT * FROM STU WHERE 性別='男'

Q3：

解析是沒有什麼用的，要看評論區大哥。

Q4：

A選項，不能定義一個check約束後，立即在同一個批處理中使用；C選項，Create default，Create rule，Create trigger，Create procedure，Create view等語句同一個批處理中只能提交一個；D選項，不能把規則和預設值繫結到表欄位或自定義欄位上之後，立即在同一個批處理中使用。

所以B選項正確。