MYSQL<三>

標籤：max   varchar   default   tab   順序   匯總   符號   劃線   取出   

-- ########## 01、查詢的排序 ##########-- 需求：對班級的所有男生的年齡進行排序-- 思路：-- 思路1、對全部的資料先排序，再進行篩選-- 思路2、對全部的資料線篩選，再進行排序-- 顯然，思路2這種形式效率比較高，語義上和實現上都符合要求，因為排序的資料越多，耗時越多，所以先通過篩選減少需要排序的資料量再進行排序-- 次序：升序（順序）ASC 和 降序（逆序）DESC-- 特點：-- A：沒有指明使用哪個欄位作為排序次序時，預設的顯示次序是按照表的主鍵的欄位來進行排序（升序）的-- B：升序時，NULL值得順序排在非空值之前-- C：不顯式指明次序，預設的順序為升序（順序）ASCDESC student;SELECT * FROM student;-- 1、單一欄位的排序SELECT * FROM student ORDER BY phone ASC;SELECT * FROM student ORDER BY phone DESC;SELECT * FROM student ORDER BY phone;-- 2、多個欄位的排序INSERT INTO student VALUES(NULL, ‘郭嘉‘, ‘男‘, ‘666‘), (NULL, ‘郭嘉‘, ‘男‘, ‘111‘);-- 需求：按studentname升序，同時按phone降序SELECT * FROM student ORDER BY studentname ASC, phone DESC;INSERT INTO student VALUES(NULL, ‘郭嘉‘, ‘男‘, ‘222‘);-- 注意：下句也涉及到多個欄位的排序：先按studentname升序，再按主鍵studentid升序--         即首先考慮ORDER BY子句中顯式指明的排序次序，其次考慮隱式的主鍵升序SELECT * FROM student ORDER BY studentname ASC;-- ########## 02、基於列的邏輯 ##########-- 商品表CREATE TABLE product(    -- 商品編號    productid INT AUTO_INCREMENT PRIMARY KEY,    -- 商品名稱    productname VARCHAR(20),    -- 分類編碼    categorycode ENUM(‘F‘, ‘C‘));INSERT INTO product VALUES(NULL, ‘蘋果‘, ‘F‘), (NULL, ‘梨子‘, ‘F‘), (NULL, ‘香蕉‘, ‘F‘),(NULL, ‘Nike‘, ‘C‘), (NULL, ‘Kappa‘, ‘C‘);SELECT * FROM product;-- 需求：分類編碼categorycode顯示的F、C不夠清晰，在查詢結果中希望能夠看到清晰的含義-- 分析：這就是對於列的邏輯，對列的內容如果是F就顯示為水果，如果是C就顯示為衣服--          使用關鍵字CASE、WHEN、THEN、ELSE、END的結合使用-- case運算式的格式1：-- select-- case 欄位或運算式-- when 值1 then 結果1-- WHEN 值2 THEN 結果2-- ...-- else 預設結果-- endSELECT     productid AS 商品編號,    productname AS 商品名稱,    categorycode AS 分類編碼（不清晰）,    CASE categorycode    WHEN ‘F‘ THEN ‘水果‘    WHEN ‘C‘ THEN ‘衣服‘    ELSE ‘其他‘    END AS 分類編碼（清晰）FROM product;-- case運算式的格式2：-- select-- case-- when 條件1 then 結果1-- WHEN 條件2 THEN 結果2-- ...-- else 預設結果-- endSELECT    productid AS 商品編號,    productname AS 商品名稱,    categorycode AS 分類編碼（不清晰）,    CASE    WHEN categorycode = ‘F‘ THEN ‘水果‘    WHEN categorycode = ‘C‘ THEN ‘衣服‘    ELSE ‘其他‘    END AS 分類編碼（清晰）FROM product;-- ########## 03、基於行的邏輯 ##########SELECT * FROM student;DESC student;-- 1、使用WHERE子句，對集合進行條件式篩選SELECT * FROM student WHERE gender = ‘女‘;SELECT * FROM student WHERE gender = ‘男‘;-- 錯誤碼： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘LIMIT 0, 1000‘ at line 1SELECT * FROM student WHERE gender = DEFAULT;-- 2、在WHERE子句中使用AND，表示邏輯與的關係（篩選出WHERE子句中所有條件滿足的結果）SELECT * FROM student WHERE gender = ‘女‘ AND phone = ‘123‘;SELECT * FROM student WHERE gender = ‘女‘ AND phone = ‘114‘;-- 3、在WHERE子句中使用OR，表示邏輯或的關係（WHERE子句中只要有條件滿足的結果就篩選出來）SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘123‘;SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘114‘;SELECT * FROM student WHERE gender = ‘女‘ OR phone = ‘119‘;-- 4、在WHERE子句中使用!= 或者 <>，表示不等於的關係SELECT * FROM student WHERE gender != ‘女‘;SELECT * FROM student WHERE gender <> ‘女‘;-- 錯誤碼： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘>< ‘女‘ LIMIT 0, 1000‘ at line 1SELECT * FROM student WHERE gender >< ‘女‘;-- 5、* 萬用字元：星號萬用字元，用來代替所有的欄位名稱SELECT * FROM student;-- 6、_ 萬用字元：底線萬用字元（佔位萬用字元），結合LIKE關鍵字使用-- 注意：_ 萬用字元佔一個位置，這個位置上可以是任一字元，但是一定有一個字元INSERT INTO student VALUES(NULL, ‘曹丕‘, ‘男‘, ‘110‘), (NULL, ‘曹植‘, ‘男‘, ‘120‘), (NULL, ‘曹孟德‘, ‘男‘, ‘130‘), (NULL, ‘曹‘, ‘男‘, ‘140‘);-- 需求：查詢以‘曹‘開頭的，後面接一個任一字元的記錄SELECT * FROM student WHERE studentname LIKE ‘曹_‘;-- 需求：查詢以‘曹‘開頭的，後面接兩個任一字元的記錄SELECT * FROM student WHERE studentname LIKE ‘曹__‘;-- 需求：查詢以一個任一字元開頭，中間有一個‘孟‘字，後面接一個任一字元的記錄SELECT * FROM student WHERE studentname LIKE ‘_孟_‘;-- 需求：查詢以一個任一字元開頭，中間有一個‘曹‘字，後面接一個任一字元的記錄SELECT * FROM student WHERE studentname LIKE ‘_曹_‘;-- 注意：體會佔位的含義，下句插入空格+曹彰，會被查出INSERT INTO student VALUES(NULL, ‘ 曹彰‘, ‘男‘, 150);SELECT * FROM student WHERE studentname LIKE ‘_曹_‘;-- 7、% 萬用字元：百分比符號萬用字元（任意匹配萬用字元），結合LIKE關鍵字使用-- 任意匹配的含義：不論匹配一個字元，還是匹配多個字元，甚至沒有字元，均匹配SELECT * FROM student WHERE studentname LIKE ‘曹%‘;-- 下句中的‘曹‘字元後有兩個%百分比符號的效果 和 有一個%百分比符號的效果是一致的，都是在‘曹‘字元後任意匹配SELECT * FROM student WHERE studentname LIKE ‘曹%%‘;-- 下句表示在‘曹‘字元前後任意匹配SELECT * FROM student WHERE studentname LIKE ‘%曹%‘;-- 匹配所有的記錄（非空記錄）SELECT * FROM student WHERE studentname LIKE ‘%‘;-- 匹配所有聯絡電話以1開頭的記錄SELECT * FROM student WHERE phone LIKE ‘1%‘;-- 匹配所有聯絡電話非空的記錄SELECT * FROM student WHERE phone LIKE ‘%‘;-- 8、使用LIKE進行模糊查詢時，取反操作NOT LIKE，不會對NULL值進行匹配的SELECT * FROM student WHERE phone NOT LIKE ‘1%‘;-- 9、在WHERE子句中使用IN關鍵字描述處於某一個小的範圍的集合-- IN對應的小範圍集合的值在表中均有的情況：匹配值的記錄被取出SELECT * FROM student WHERE phone IN (‘110‘, ‘120‘, ‘666‘);-- IN對應的小範圍集合的值在表中部分有的情況：匹配值的記錄被取出SELECT * FROM student WHERE phone IN (‘110‘, ‘120‘, ‘777‘);-- 注意：上述IN的使用可以理解為：SELECT * FROM student WHERE phone = ‘110‘ OR phone = ‘120‘ OR phone = ‘777‘;-- 10、在WHERE子句中使用NOT IN關鍵字描述不處於某一個小的範圍的集合SELECT * FROM student WHERE phone NOT IN (‘110‘, ‘120‘, ‘666‘);SELECT * FROM student WHERE phone NOT IN (‘110‘, ‘120‘, ‘777‘);-- 注意：IN 某一個小的範圍 + NOT IN 某一個小的範圍     不等於  全部範圍，因為忽略了NULL值-- 11、使用IS NULL篩選欄位的內容為NULL的記錄SELECT * FROM student WHERE phone IS NULL;-- 12、使用IS NOT NULL篩選欄位的內容不為NULL的記錄SELECT * FROM student WHERE phone IS NOT NULL;-- 注意：IS NULL 篩選出來的記錄  +  IS NOT NULL 篩選出來的記錄   等於  所有記錄-- 13、使用大於、小於、大於等於、小於等於符號進行範圍篩選（同樣不包含NULL值對應的記錄）SELECT * FROM student WHERE phone > ‘150‘;        -- 2條記錄SELECT * FROM student WHERE phone < ‘150‘;        -- 7條記錄SELECT * FROM student WHERE phone >= ‘150‘;        -- 3條記錄SELECT * FROM student WHERE phone <= ‘150‘;        -- 8條記錄-- 14、使用BETWEEN...AND...做範圍篩選（考慮邊界值是否包含？答：兩個邊界值均包含在內）SELECT * FROM student WHERE studentid BETWEEN 3 AND 8;-- 下句查詢的結果：沒有滿足條件的記錄SELECT * FROM student WHERE studentid BETWEEN 8 AND 3;-- 注意：BETWEEN 值1 AND 值2  相當於  大於等於 值1  且 小於等於 值2-- 198行可以理解為下句：SELECT * FROM student WHERE studentid >= 3 AND studentid <= 8;-- 201行可以理解為下句：SELECT * FROM student WHERE studentid >= 8 AND studentid <= 3;-- ########## 04、摘要資料 ##########-- 1、使用DISTINCT關鍵字去除重複的值-- 需求：擷取學生的性別SELECT gender FROM student;-- 上句從文法上看沒有問題，但是從語義上，會取出很多重複的資料，而這些重複的資料無意義SELECT DISTINCT gender FROM student;-- *****************************************************-- 彙總函式：SUM()、AVG()、MAX()、MIN()、COUNT()SELECT * FROM student;-- 2、單獨使用彙總函式-- COUNT(*)統計的記錄針對非空欄位進行的統計SELECT COUNT(*) AS 記錄條數 FROM student;-- 對欄位的內容中沒有NULL值的欄位（主鍵欄位）進行COUNT()，結果和COUNT(*)的記錄數一致SELECT COUNT(studentid) AS 記錄條數 FROM student;-- 對欄位的內容中沒有NULL值的欄位（非空欄位）進行COUNT()，結果和COUNT(*)的記錄數一致SELECT COUNT(studentname) AS 記錄條數 FROM student;-- 對欄位的內容中有NULL值的欄位（可空欄位）進行COUNT()，結果和COUNT(*)的記錄數相差了NULL值的個數SELECT COUNT(phone) AS 非空記錄條數 FROM student;-- 注意：欄位內容中的NULL值不會被COUNT()這個彙總函式統計在內--         COUNT(*)函數中的* 並不是匹配所有的欄位，而是那些非空的欄位SELECT MAX(studentid) AS 最大學號 FROM student;SELECT MIN(studentid) AS 最小學號 FROM student;SELECT MAX(phone) AS 最大聯絡號碼 FROM student;SELECT MIN(phone) AS 最小聯絡號碼 FROM student;-- 注意：欄位內容中的NULL值不會被MAX()、MIN()這兩個彙總函式統計在內ALTER TABLE student ADD score INT AFTER phone;DESC student;SELECT * FROM student;-- 此時，新增了score成績欄位（可空），預設值均為NULL，對其進行SUM() 和 AVG()，均為NULL值SELECT SUM(score) AS 總分 FROM student;        -- NULLSELECT AVG(score) AS 平均分 FROM student;    -- NULL-- 更新隨機分數，測試一下SELECT ROUND(RAND() * 100);SELECT SUBSTRING(RAND() * 100, 1, 2); -- 這種形式可能會出現個位元字加小數點點號的結果-- 更新表中的資料UPDATE student SET score = ROUND(RAND() * 100) WHERE studentid IN (1, 2, 8);SELECT SUM(score) AS 總分 FROM student;SELECT AVG(score) AS 平均分 FROM student;-- 注意：欄位內容中的NULL值不會被SUM()、AVG()這兩個彙總函式統計在內-- AVG(某個欄位) = SUM(某個欄位) / COUNT(某個欄位)SELECT SUM(score) / COUNT(score) FROM student;-- 彙總函式小結：彙總函式均無視NULL值

 

MYSQL<三>