SQL：結構化查詢語言

讀操作

重點是where查詢，即高級查詢部分

分類

DML ：數據操縱語言，寫操作，增insert 刪delete 改update
DQL： 數據查詢語言 ：select
DDL:數據定義語言 create alter drop 創建庫，創建表，創建函數，創建存儲過程
DCL：數據控制語言。用戶，角色，權限

重點是學習前兩個部分，三四部分在面試不問，但是筆試有可能遇到

基礎查詢語句示例

-- 1.查詢全部數據。*代表所有列.一般不要直接使用*,因為查詢量太大影響性。
SELECT * from t_student;-- 2.限定列查詢：
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student;-- 3.列起別名,使用as  別名也可以省略as
SELECT  t_student.id as 編號,stu_id AS 學號 ,`name` 姓名,pinyin 拼音,sex 性別,birthday 出生日期  from t_student;-- 4.表起別名:...from t_student as t 在沒有歧義的情況下，表名前綴可以省略
SELECT  t.id as 編號,t.stu_id AS 學號,`name` 姓名,pinyin 拼音,sex 性別,birthday 出生日期 from t_student as t;-- 5.列運算
select id,stu_id,`name`，pinyin,sex,height+10 修正身高,weight-10 修正體重 from t_student;-- 6.限定行查詢，可以用于分頁等操作（重點）
-- 返回查詢結果的前 10 行數據
SELECT id,stu_id,`name`,pinyin,sex,birthday FROM t_student LIMIT 10;
-- 第一個參數表示起始行（從0開始），第二個參數表示查詢行數
-- 跳過前 10 條，顯示第 11 到 20 條數據
SELECT id,stu_id,`name`,pinyin,sex,birthday FROM t_student  LIMIT 10,10;
SELECT id,stu_id,`name`,pinyin,sex,birthday FROM t_student  LIMIT 10 OFFSET 10;-- 7.指定查詢條件where。where后面指定查詢表達式。支持算術運算符、比較運算符、邏輯運算符 、其它的。
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student WHERE id=5;
-- 比較運算符 不等于用的是<>、！
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student WHERE id<>5 LIMIT 10;
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student WHERE id!=5 LIMIT 10;
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student WHERE id<=5;
-- 僅sql支持：
SELECT  id,stu_id,`name`，pinyin,sex,birthday from t_student WHERE birthday > '2000-1-1';-- 8.算術運算符,也可以用在查詢條件。支持小括號提高優先級
SELECT  id,stu_id,`name`，pinyin,weight,height from t_student WHERE  weight/((height/100)*(height/100))>24;-- 9.邏輯運算符：and or not；
SELECT  id,stu_id,`name`，sex,pinyin,weight,height from t_student WHERE height>=175 AND sex='女';SELECT  id,stu_id,`name`，sex,pinyin,weight,height from t_student WHERE  NOT sex='女';

-- 10.空判斷
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE class_id IS NULL;
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE class_id IS NOT NULL;
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE  Not class_id IS NULL;--  11.通配符。模糊查詢  %代表0或者多個字符  _代表一個字符 
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` LIKE '張%';
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` LIKE '%曉%';
-- _代表一個字符 
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` LIKE '張_';
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` LIKE '張__';-- 12.正則查詢。正則表達式是用于字符串匹配用
-- 下面是大于等于3
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` REGEXP '\\w{3}';
SELECT  id,stu_id,`name`,sex,pinyin,weight,height from t_student WHERE `name` RLIKE '\\w{3}';
--  比如匹配11位數字的微信號
SELECT  id,stu_id,`name`,sex,pinyin,weight,height,wechat from t_student WHERE `wechat` RLIKE '\\d{11}';
--  比如匹配不是11位數字的微信號
SELECT  id,stu_id,`name`,sex,pinyin,weight,height,wechat from t_student WHERE `wechat` NOT RLIKE '\\d{11}';-- 13. 結果集去重 DISTINCT 慎用  影響性能 
SELECT DISTINCT sex FROM t_student LIMIT 20;

-- 14.查詢結果排序: order by，mysql默認按照數字插入的自然順序排序
SELECT  id,stu_id,`name`，pinyin,sex,birthday,weight,height from t_student  LIMIT 20;
-- 身高升序。asc升序，desc降序，asc可以省略，默認就是升序
SELECT  id,stu_id,`name`，pinyin,sex,birthday,weight,height from t_student ORDER BY height ASC LIMIT 100;
-- desc降序查詢：
SELECT  id,stu_id,`name`，pinyin,sex,birthday,weight,height from t_student ORDER BY weight DESC LIMIT 100;-- 多列查詢 當排序條件出現"矛盾"時，在 ORDER BY 子句中排在第一位的優先級更高。
SELECT  id,stu_id,`name`，pinyin,sex,birthday,weight,height from t_student ORDER BY height ASC, weight DESC LIMIT 100;

高級查詢–分組查詢、子查詢、表連接、聯合查詢

分組查詢：

-- 二、分組查詢
-- 1.SELECT后面只能是分組列和聚合函數列:聚合函數共五個：avg、sum、max、min、count，對數字進行 聚合運算
-- 分組列：sex,聚合函數：avg。聚合函數可以寫多個
SELECT sex,AVG(height) FROM t_student GROUP BY sex;
-- 聚合函數可以寫多個,也可以進行運算，。其中COUNT().()里面寫啥都行
-- COUNT() 里面可以是任何列，因為它的作用是計算非空行的數量。如果用 *，就表示計算所有行的數量。
SELECT sex,MAX(weight),MIN(weight),COUNT(*) FROM t_student GROUP BY sex;
SELECT class_id,MAX(height),MAX(weight) FROM t_student GROUP BY class_id;-- 2.多次分組
-- 第一次分組：按 class_id 分組，把相同班級的學生分到一組。
-- 第二次分組：在每個 class_id 組內，再按 sex（性別）分組，比如男生一組，女生一組。
SELECT class_id,sex,MAX(height),MAX(weight) FROM  t_student GROUP BY class_id,sex ORDER BY class_id,sex;-- 3.無分組（也叫單分組） count中的參數表示根據哪一列來統計行數。0：常數列，和表的總行數一樣的 *：全部。下面三種執行都不一樣，什么時候有區別？注意：count不統計空值。
SELECT  COUNT(0) FROM t_student;   -- SELECT 0 from t_student；常數列 
SELECT  COUNT(*) FROM t_student;
SELECT  COUNT(id) FROM t_student;
SELECT  COUNT(sex) FROM t_student;
-- 注意：count不統計空值-- 如果對count進行去重，那么結果就不是全部的了。常量去重就是一條記錄。
SELECT  COUNT(DISTINCT class_id) FROM t_student;
SELECT  COUNT(DISTINCT 0) FROM t_student;-- 常量去重就是一條記錄。
SELECT sex,AVG(DISTINCT height) FROM t_student GROUP BY sex;-- 4.分組之后的條件篩選（聚合之后的數據再次篩選：WHERE）
-- 區分 ：SELECT是分組之前的篩選，WHERE是分組之后的篩選
SELECT class_id,sex,MAX(height) mh,MAX(weight) mwFROM  t_student GROUP BY class_id,sex HAVING mh >183 AND mw>95ORDER BY class_id,sex ;

子查詢（嵌套查詢）

-- 三、子查詢（嵌套查詢）
-- 1.列子查詢（不常用）
SELECT id,stu_id,`name`,(SELECT `class_name`FROM t_class WHERE id = class_id ) class_name,class_id,sex,birthday FROM t_student LIMIT 10;--  2.表子查詢，必須起別名。將查詢結果作為一個表：
SELECT  id,stu_id,`name`，pinyin,sex,birthday from (SELECT * FROM t_student WHERE sex='女') t1;--  3.在where中，比較運算符子查詢
--  等號（不等號）子查詢，要求子查詢結果必須是一行一列。
SELECT  id,stu_id,`name`，pinyin,sex,birthday 
from  t_student WHERE class_id=(SELECT id FROM t_class  WHERE class_name = '080503-JAVA');
-- 大于號子查詢  小于號子查詢 
SELECT  id,stu_id,`name`，pinyin,sex,birthday 
from  t_student WHERE class_id>(SELECT id FROM t_class  WHERE class_name = '080503-JAVA');
--  ALL:
SELECT  id,stu_id,`name`，pinyin,sex,birthday 
from  t_student WHERE class_id>ALL(SELECT id FROM t_class  WHERE class_name = '%JAVA%');
-- ANY：
SELECT id,stu_id,`name`,pinyin,sex,birthday,class_id  FROM t_student 
WHERE class_id > ANY(SELECT id FROM t_class WHERE class_name = '080503-JAVA');
-- 4.in 和not in 子查詢
-- in:集合中的任意一個
SELECT id,stu_id,`name`,pinyin,sex,birthday from t_student  WHERE id in(1,2,3);
SELECT id,stu_id,`name`,pinyin,sex,birthday from t_student 
WHERE id in (SELECT id FROM  t_class where class_name LIKE '%JAVA%');-- 5.EXISTS 和not EXISTS子查詢，唯一的斷言類查詢
SELECT id,stu_id,`name`,pinyin,sex,birthday 
from t_student WHERE EXISTS (SELECT  id from  t_class where class_name LIKE '%JAVA%');-- 6.相關子查詢：
-- 查詢出比所在班平均身高要高的學生
SELECT id,stu_id,`name`,pinyin,sex,birthday 
from t_student t1 
WHERE height > (SELECT AVG(height)FROM t_student t2 WHERE t2.class_id = t1.class_id);

表連接

-- 四、表連接:將 兩張表中的數據，顯示到一個結果集中。
--  1.內連接（最常使用），連接的時候必須使用on指定連接條件。特點：連接條件的列在左右兩側都必須存在才能連接
SELECT t1.id,stu_id,`name`,pinyin,sex,birthday,class_id,t2.class_name,t2.begin_time
FROM t_student t1
INNER JOIN  t_class t2
ON t1.class_id = t2.id ORDER BY t1.id;-- 2.左外連接，簡稱左連接:LEFT JOIN 。outer通常省略。
-- 和內連接的區別：左表數據一定全部顯示,右邊連接不上顯示Null 
SELECT t1.id,stu_id,`name`,pinyin,sex,birthday,class_id,t2.class_name,t2.begin_time
FROM t_student t1
LEFT JOIN t_class t2
ON t1.class_id = t2.id ORDER BY t1.id;-- 3.右外連接，簡稱右連接.RIGHT JOIN。outer通常省略。
--  和內連接的區別：右表數據一定全部顯示,左邊連接不上顯示Null 
SELECT t1.id,stu_id,`name`,pinyin,sex,birthday,class_id,t2.class_name,t2.begin_time
FROM t_student t1
right JOIN t_class t2
ON t1.class_id = t2.id ORDER BY t1.id;-- 4.全外連接。mysql不支持---解決方案：聯合查詢。
-- 左表和右表全顯示 ，連接不上的顯示Null。
-- 雖然不支持，但是使用union將左外連接和右外連接連接在一起就實現了全外連接
SELECT  * from (SELECT t1.id,stu_id,`name`,pinyin,sex,birthday,class_id,t2.class_name,t2.begin_time
FROM t_student t1
LEFT JOIN t_class t2
ON t1.class_id = t2.id ORDER BY t1.id) t1UNIONSELECT  * from (SELECT t1.id,stu_id,`name`,pinyin,sex,birthday,class_id,t2.class_name,t2.begin_time
FROM t_student t1
right JOIN t_class t2
ON t1.class_id = t2.id ORDER BY t1.id) t2;

聯合查詢

-- 五、聯合查詢：UNION關鍵字
--  注意事項：
-- 1.無需是同一張表
-- 2.列數必須一致
-- 3.數據類型基本匹配
-- 4.UNION會自動去重 UNTONN ALL不會去重-- 1.將兩個結果集合并成一個：合并結果集
SELECT id,stu_id,`name`,pinyin,sex,birthday from t_student  WHERE sex ='男'
UNION
SELECT id,stu_id,`name`,pinyin,sex,birthday from t_student  WHERE sex ='女';

寫操作

語法固定

-- 寫操作。增刪改查--->CURD
-- 1.最常見：注意字段值不為空的字段必須給他賦值，且值要和參數個數、順序 一致
INSERT into t_student (stu_id,name,sex) VALUES ('st_001','孫小美','女');-- 2.插入全部列：不建議用，不常用INSERT INTO `t_student` VALUES (1, 'st_0000', 3, '伍華欣', 'wǔ huá xīn', 'wuhuaxin', '華欣', '伍', '女', '1989-10-13', 171, 47.1, 91, '1038132', '13270787041', '13270787041', 'wuhuaxin@yahoo.com', 32580, NULL, '漢族', NULL, '中國', NULL, NULL, NULL);-- 3.插入結果集(不常用）：只要是合法集就行.如果是常量沒有from表，就是直接直接插入一行。要求：列數必須一致，數據類型基本匹配
-- 	一次性插入結果集 ： 
INSERT INTO t_class
SELECT 21, '080203-JAVA', '2008-02-03', '2008-06-24', 1, 0, 1, NULL 
UNION
SELECT 22, '091219-UI', '2009-12-19', '2010-05-02', 3, 0, 1, NULL-- 4.指定列
INSERT INTO t_class (class_name,begin_time,end_time)
SELECT '080203-JAVA', '2008-02-03', '2008-06-24' 
UNION
SELECT  '091219-UI', '2009-12-19', '2010-05-02'-- 5.修改:無論怎么改不能違反已經制定的約束：比如非空約束...
update t_student set 
stu_id='st_2000',
name='錢夫人',
sex='女'
where id=1;-- 6.刪除：
DELETE FROM t_student
where id=12;

視圖

視圖就相當于子查詢的結果集起個別名，是一個假的表：

sql語句創建視圖：

語法 ：

create view 視圖名 as 子查詢

示例：

create view v_salary as 
SELECT emp_id,base_salary+pension+allowance+bonus-deduct-tax from t_salary;

或者直接在navicate里面直接創建視圖，視圖里面寫的就是子查詢的sql語。

創建好之后：

如上，視圖里面的數據是不能改動的 ，因為數據來自實際表里面，視圖就是一張假表，方便我們查詢的。