MySQL數據的管理 · 數據庫講義新編2020

# MySQL的數據管理 [TOC] ## 導學在之前的學習中，我們已經會建庫了，也會建表了，還了解了一些相關的數據庫知識，那么我們今天進入MySQL的重中之重，對數據的管理。 ## 修改數據操作針對于數據的操作，其實我們可以分為兩種，一種是針對數據的查詢，還有就是針對于數據的修改。那么，在數據的修改中，又分為數據增加操作，數據刪除操作和數據修改操作。 ### 添加記錄語法： ~~~ INSERT INTO table_name[(field1, field2,...)] VALUES(value1, value2, value3) 或 INSERT tbl_name SET 字段名稱=值,字段名稱=值,...; ~~~ 注意： 1. field可以省略不寫，但是如果不寫，實際會對所有字段設置數據。而且后面VALUES中的順序是要和你定義表時的順序保持一致的； 2. field如果寫了，那么field里面的順序要和values后面的數值保持一致。 >[warning]在實際的開發中，一般都要給定字段進行插入，不建議省略fields的定義。 **批量插入數據** 語法： ~~~ INSERT tbl_name (字段名稱,...) VALUES (值,...), (值,...), (值,...); ~~~ 或者利用查詢，將查詢結果插入到表中語法： ~~~ INSERT INTO table_name(field1, field2,...) SELECT field1, field2, ... FROM new_talbe ~~~ 示例： ~~~ INSERT INTO s_v1(code, name, birthday) SELECT code, name, birthday FROM s ~~~ ### 修改記錄 >[danger]在程序開發中，修改數據一般是非常關鍵的操作，所以，只要是寫更新數據的語句的時候，一定要想好**條件**。語法： ~~~ UPDATE table_name SET field1=value1, field2=value2, ... WHERE condition ~~~ 示例： ~~~ UPDATE s SET code='ss6',birthday='1990-10-10' WHERE name='zhangsan' ~~~ ### 刪除記錄 >[danger]同樣的和修改數據操作一樣，在刪除數據的時候，一定要加上刪除數據的**條件**。 ~~~ 刪除記錄： DELETE FROM tbl_name [WHERE條件] -- DELETE 清空數據的時候不會重置AUTO_INCREMENT的值 ~~~ 補充： ~~~ TRUNCATE [TABLE] tbl_name; -- 清空表中所有記錄，會重置AUTO_INCREMENT的值 ~~~ ## 檢索數據操作完整語法： ~~~ SELECT select_expr,... FROM tb_name [WHERE 條件] [GROP BY{col_name|position} HAVING 二次篩選] [ORDER BY {col_name|position|expr}[ASC|DESC]] [LIMIT 限制結果采集的顯示條數] ~~~ ### 查詢表中所有的記錄語法： ~~~ SELECT * FROM table_name [WHERE 條件] ~~~ 示例： ~~~ select * from user ~~~ >[info]此處遍歷出來的數據的順序是創建表中字段的順序。 ### 指定字段信息查詢如果某張表的字段較多，但是在具體的某個場景中，只需要用到部分字段的信息，可以使用該查詢。語法： ~~~ SELECT field1, field2,... FROM table_name [WHERE 條件] ~~~ 示例： ~~~ select id,user_name,password from user ~~~ ### 起別名查詢語法： ~~~ 給字段起別名： SELECT 字段名稱[AS] 別名名稱,... FROM db_name.tb_name; 給數據表起別名： SELECT 字段名稱,... FROM tb_name AS 別名; ~~~ ### 避免重復數據的查詢使用關鍵字：DISTINCT >[warning] 例如要查詢有在校生的班級編號，就可以使用 DISTINCT 關鍵字，查詢 stu\_info 表。 > > 在使用 DISTINCT 查詢時要注意：其效率較低的。 ~~~ SELECT DISTINCT clazz FROM stu_info ~~~ ### 查詢時進行數學運算數學運算的數據結果處理：+ - \* / % 示例： ~~~ SELECT name, price AS '人民幣', price/6 AS '美元' FROM t_menu ~~~ ## where篩選條件在where后面，我們可以跟上所要設置的查詢條件。那么如何描述查詢條件呢？ * 帶關系運算符和邏輯運算符的表達式； * 帶 BETWEEN AND 關鍵字的條件查詢； * 帶 IS NULL 關鍵字的條件查詢； * 帶 IN 關鍵字的條件查詢； * 帶 LIKE 關鍵字的條件查詢。 ### 關系運算符和邏輯運算符關系運算符：>、 >=、 <、 <=、=、<=>；邏輯運算符：AND(&&)、OR(||)、NOT(!) 示例： ~~~ SELECT * FROM stu_info WHERE age >= 18 AND clazz = 'C1' AND code = '01' 對于null值的判斷： select * from user where user_name=null;(x) 不可以使用=進行null值的判斷 select * from user where user_name<=>null (ok) ~~~ ### BETWEEN ADN >[info] 一般用在對數值或者日期的區間判斷條件中，而且是可以被替代的。 ~~~ SELECT * FROM stu_info WHERE age BETWEEN 16 AND 20 SELECT * FROM stu_info WHERE age >= 16 AND age <=20 -- 使用 NOT 取反 SELECT * FROM stu_info WHERE NOT (age >= 16 AND age <=20) ~~~ ### IS NULL >[info] 判斷數據結果集中非空元素，要注意的是：NULL 和空字符串是兩個概念，使用的查詢條件不盡相同 ~~~ SELECT * FROM stu_info WHERE name IS NULL; SELECT * FROM stu_info WHERE name = ''; -- 判斷空字符串 ~~~ **使用非空判斷是要注意** ~~~ SELECT * FROM stu_info WHERE name IS NOT NULL ~~~ ### IN >[info] 條件在某些離散的數據范圍內 ~~~ SELECT * FROM stu_info WHERE clazz IN ('C1', 'C2'); -- 替代方案 SELECT * FROM stu_info WHERE clazz = 'C1' OR clazz = 'C2' ~~~ ### LIKE >[info] 模糊查詢：用的較多，一般用到的是全匹配`%搜索字%`，尾部匹配`搜索字%` > > 其他還有單個字匹配`_`和首部匹配`%搜索字` ~~~ SELECT * FROM stu_info WHERE name LIKE '李_強'; SELECT * FROM stu_info WHERE name LIKE '李%'; SELECT * FROM stu_info WHERE name LIKE '%李%'; ~~~ ## 分組 GROUP BY分組：把值相同放到一個組中，最終查詢出的結果只會顯示組中一條記錄。示例： ~~~ SELECT id,username,age,sex FROM user ~~~ 只會分別顯示男女2組里的各一條信息代表。 **分組拼接信息-group_concat()** ~~~ SELECT GROUP_CONTACT(username),age,sex,addr FROM user ~~~ ### 聚合函數 * count 總數 * sum 求和 * avg 平均值 * max 最大值 * min 最小值示例： ~~~ SELECT COUNT(*) AS totalUsers, SUM(age) AS sum_age, MAX(age) AS max_age, MIN(age) AS min_age, AVG(age) AS avg_age, FROM user ~~~ **注意：**需要注意count(*)與count(字段)的區別。 ### HAVING對分組后的數據進行篩選我們使用where對分組前的數據進行篩選，使用having對分組后的數據進行篩選。示例： ~~~ select subject,avg(score) as avg_score from student group by subject having (avg_score < 60); ~~~ ## 排序 >[info] 數據的排序方式：順序 ASC、逆序 DESC。 > 在排序中是可以多字段排序的，即會有第一排序條件和第二、三...次排序條件 ~~~ SELECT * FROM stu_info ORDER BY clazz ASC, code DESC ~~~ >[warning]隨機順序`ORDER BY RAND();` ## 限制查詢數量使用 LIMIT 關鍵字限制查詢的條數，后面跟兩個參數，第一個參數是從第幾條開始，第二個是一共顯示多少條記錄 ~~~ SELECT * FROM stu_info ORDER BY clazz ASC, code DESC LIMIT 9, 3 -- 顯示 page 頁，每頁顯示 num 條記錄 -- page = 2; num = 3; -- x = (page - 1) * num -- y = num ~~~ LIMIT同樣也可以用于更新和刪除操作，但是使用的時候只能添加一個參數 ~~~sql -- 更新user1表中的前3條記錄，將age加5 UPDATE user1 SET age=age+5 LIMIT 3; -- 將user1表中id字段降序排列，更新前三條記錄，將age減10 UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3; -- 刪除user1表中前三條記錄 DELETE FROM user1 LIMIT 3; -- 刪除user1表中id字段降序排列的前三條記錄 DELETE FROM user1 ORDER BY id DESC LIMIT 3; ~~~ ## 查詢順序解釋 >[warning] 對于一個較完整的 SQL 語句執行的解釋 ~~~ SELECT clazz,MAX(age) AS '最大年齡', COUNT(*) AS '多少人' FROM stu_info WHERE id > 2 GROUP BY clazz HAVING count(*) > 1 ORDER BY MAX(age) DESC ~~~ 執行順序 1. 篩選整個表找那個`id > 2`的數據； 2. 把篩選出的記錄按照`clazz`字段進行分組； 3. 把分組完的結果，篩選出每組數據總數量 > 1的數據`count(*) > 1`； 4. 按照分組后的字段進行排序`MAX(age) DESC`； 5. 按照 SELECT 中要求顯示的字段輸出結果集。 ## 高級查詢 ### 表之間的關系 * 一對多（多對一）在多的一方加入一的一方的外鍵。 * 多對多通過一個中間表將兩個表之間建立關系。 * 一對一在所謂的子表中加入所謂主表的外鍵，并加上唯一性約束。 ### 笛卡爾積查詢-自然連接查詢自然連接查詢是用的比較少的，同學們只需要了解即可。示例： ~~~ SELECT t.id, t.name, t.age, ts.depName FROM teachar t, teach_species ts ~~~ 要注意的是，如果不加條件，直接查詢，會把兩個表進行笛卡爾積的操作，查詢出來的數據是有問題。 ~~~ SELECT t.id, t.name, t.age, ts.depName FROM teachar t, teach_species ts where t.dep_id=ts.id ~~~ ### 內連接查詢 >[info] 內連接查詢是可以使用自然連接查詢替代的，但是效率方面，內連接會高 ~~~ -- 內連接查詢 SELECT s.`code`, s.`name`, c.`name` FROM student s INNER JOIN clazz c ON s.clazz_id = c.id ~~~ >[warning]內連接會查詢出兩個表中符合條件的交集部分 ### 外連接查詢左外連接： ~~~ SELECT 字段名稱,... FROM tb_name1 LEFT [OUTER] JOIN tb_name2 ON 條件 ~~~ 先顯示左表中的全部記錄，再去右表中查詢符合條件的記錄，不符合的以NULL代替右外連接： ~~~ SELECT 字段名稱,... FROM tb_name1 RIGHT [OUTER] JOIN tb_name2 ON 條件 ~~~ 先顯示右表中的全部記錄，再去左表中查詢符合條件的記錄，不符合的以NULL代替關鍵字LEFT左邊的那個表為主表，右邊的表為從表；關鍵字RIGHT右邊的那個表為主表，左邊的表為從表。 **左外連接查詢** >[info] 以左表為主表，左表中的數據都會被顯示出來，關聯的右表中，如果存在符合條件的數據，那么會被關聯出并顯示，如果沒有，則會顯示 NULL。 ~~~ -- 左外連接 SELECT s.`code`, s.`name`, c.`name` FROM student s LEFT JOIN clazz c ON s.clazz_id = c.id ~~~ ## 特殊查詢 ### 子查詢內層語句的查詢結果可以作為外層語句的查詢條件。語法： ~~~ 子查詢（必須要放在括號里） SELECT 字段名稱 FROM tbl_name WHERE col_name =（SELECT col_name FROM tbl_name) ~~~ 示例： ~~~ 1、由IN引發的子查詢 SELECT * FROM emp WHERE depId IN (SELECT id FROM dep); 2、由NOT IN 引發的子查詢 SELECT * FROM emp WHERE depId NOT IN (SELECT id FROM dep); 3、由比較運算符引發的子查詢 SELECT id,username,score,FROM stu WHERE score>=(SELECT score FROM level WHERE id=1); 4、由EXISTS引發的子查詢 EXISTS后面的SELECT語句返回一個布爾類型，若后面的值不存在前面的語句不執行，若存在，則執行（EXISTS 后面的語句返回的BOOLEAN值,是是否執行前面語句的條件） SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10); ~~~ ### 帶有 ANY SOME ALL的子查詢 ![](https://img.kancloud.cn/92/4e/924e5040d205578cf07e8ed07453392d_391x90.png) **ANY 和 SOME 理解為與或 | 相似** 例如:表達式: A > SOME(1,2,3,4,5,6) 只要A的值大于SOME括號中的任意一個值, 整個表達式返回TURE, 否則返回FALSE **ALL 則理解為與并且 & 相似** 例如:表達式: A > ALL(1,2,3,4,5,6) 只有A的值大于ALL括號中的所有值, 整個表達式返回TURE, 否則返回FALSE 示例： ~~~ 帶有ANY SOME ALL 關鍵字查詢 SELECT * FROM stu WHERE score>=ANY(SELECT score FROM level); ANY跟SOME意義相同 SELECT * FROM stu WHERE score>=SOME(SELECT score FROM level); SELECT * FROM stu WHERE score>=ALL(SELECT score FROM level); ~~~ ### 聯合查詢使用的比較少。通過多個 select 語句查詢，將查詢的結果合并在一起，并返回一個新的結果集。 union 關鍵字用于合并數據，默認選擇不同的數據，若允許重復的數據，可以使用 union all。請注意，UNION 內部的每個 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。 ~~~ UNION： SELECT 字段名稱,... FROM tb_name1 UNION SELECT 字段名稱,... FROM tb_name2；會去掉兩個表中的重復項 UNION ALL： SELECT 字段名稱,... FROM tb_name1 UNION ALL SELECT 字段名稱,... FROM tb_name2；直接合并兩個表中的記錄 ~~~