MySQL基礎 [五] - 表的增刪查改

Create（insert）

Retrieve（select）

where條件

?編輯?NULL的查詢

?結果排序(order by)

篩選分頁結果 (limit)

Update

Delete?

刪除表

?截斷表（truncate）

插入查詢結果（insert+select）

聚合函數

分組聚合統計（group by）

CRUD : Create(創建), Retrieve(讀取)，Update(更新)，Delete（刪除）

Create（insert）

語法：

[ ]內的是可以省略的

INSERT [INTO] table_name[(column [, column] ...)]VALUES (value_list) [, (value_list)] ...value_list: value, [, value] ...

使用：創建一個學生表?

單行數據 + 指定列插入

value_list 數量必須和定義表的列的數量及順序一致。value的左右兩邊必須值對應，類型也對應

可以不用指定id，因為mysql會用默認的值進行自增?

單行數據 + 全列插入

全列插入可以省略values左側的列屬性

?多行數據 + 全列插入

多行數據用逗號隔開?

多行數據 + 指定列插入

插入是否更新

可能會出現由于主鍵或者唯一鍵對應的值已經存在而導致插入失敗的情況

這時候我們希望能夠進行可以選擇性的進行同步更新操作????而不是直接報錯

INSERT ... ON DUPLICATE KEY UPDATE column = value [, column = value] ...

舉個例子

第一個錯誤是因為主鍵沖突，第二個錯誤是因為我們嘗試更新的數據和其他行數據也沖突了

相當于是多做一次嘗試，如果語句沖突了，就把insert操作改成updata操作

需要注意的是你也要保證更新的數據不要和其他行數據的主鍵發生沖突！！

0 row affected: 表中有沖突數據，但沖突數據的值和 update 的值相等
1 row affected: 表中沒有沖突數據，數據被插入
2 row affected: 表中有沖突數據，并且數據已經被更新

也可通過 MySQL ? ?row_count()函數獲取受到影響的數據行數 ?(-1表示沒有)

replace

主鍵或者唯一鍵沒有沖突，則直接插入；
主鍵或者唯一鍵如果沖突，則刪除后再插入

1 row affected: 表中沒有沖突數據，數據被插入

2 row affected: 表中有沖突數據，刪除后重新插入

Retrieve（select）

語法

SELECT [DISTINCT]//去重 {* | {column [, column] ...} [FROM table_name] //從某個表里去提取[WHERE ...] //篩選條件[ORDER BY column [ASC | DESC], ...] //排序LIMIT ...    //限定篩選出來的結果條數

?創建表結構并插入數據

 CREATE TABLE exam_result ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL COMMENT '同學姓名', chinese float DEFAULT 0.0 COMMENT '語文成績', math float DEFAULT 0.0 COMMENT '數學成績', english float DEFAULT 0.0 COMMENT '英語成績' 
);

INSERT INTO exam_result (name, chinese, math, english) VALUES ('唐三藏', 67, 98, 56), ('孫悟空', 87, 78, 77), ('豬悟能', 88, 98, 90), ('曹孟德', 82, 84, 67), ('劉玄德', 55, 85, 45), ('孫權', 70, 73, 78), ('宋公明', 75, 65, 30); 
Query OK, 7 rows affected (0.00 sec) 
Records: 7 Duplicates: 0 Warnings: 0

全列查詢（*）

通常情況下不建議使用*進行全列查詢

查詢的列越多，意味著需要傳輸的數據量越大（線性遍歷）；
可能會影響到索引的使用。??

指定列查詢

是將表中所有的數據拿出來，然后要什么再顯示什么，指定列的順序不需要按定義表的順序來?

查詢字段為表達式

?表達式不包含字段

表達式包含一個字段?

?表達式包含多個字段 ?

為查詢結果指定別名(as)?

SELECT column [AS] alias_name [...] FROM table_name;

起多個別名

結果篩選并去重

??我們發現98分重復了

SELECT DISTINCT column  [...] FROM table_name;

where條件

比較運算符

?邏輯運算符

英語不及格的同學即英語成績 ( < 60 )?

SELECT name, english FROM exam_result WHERE english < 60;

語文成績在 [80, 90] 分的同學及語文成績

使用 AND 進行條件連接?

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;

?使用 BETWEEN ... AND ... 條件?

SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

數學成績是 58 或者 59 或者 98 或者 99 分的同學及數學成績?

使用 OR 進行條件連接

SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;

使用 IN 條件?

SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

?_ 匹配嚴格的一個任意字符

SELECT name FROM exam_result WHERE name LIKE '孫_';

語文成績好于英語成績的同學?

WHERE 條件中比較運算符兩側都是字段?

SELECT name, chinese, english FROM exam_result WHERE chinese > english;

總分在 200 分以下的同學?

SELECT name, chinese + math + english 總分 FROM exam_result WHERE chinese + math + english < 200;

能用別名進行查找嗎？?

上述代碼的執行順序：先?from exam_result? ?再?where total < 200? 最后?chinese+english+math total;?

根據上圖我們會發現如果直接在篩選條件那里重命名也是不可以的！！因為對列做重命名已經是屬于顯示范疇了，相當于是已經把數據拿完了，然后在最后把列名字起別名，這一步是最后一步了！所以語法上不允許的！！?

孫某同學，否則要求總成績 > 200 并且語文成績 < 數學成績并且英語成績 > 80?

綜合性查詢?

SELECT name, chinese, math, english, chinese + math + english 總分 
FROM exam_result 
WHERE name LIKE '孫_' OR ( chinese + math + english > 200 AND chinese < math AND english > 80 
);

?NULL的查詢

‘ ’ 和NULL沒有關系！！?

-- NULL 和 NULL 的比較，= 和的區別 ??

?結果排序(order by)

語法:

SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...]; //依據哪一列做排序

ASC 為升序（從小到大） //ascending order
DESC 為降序（從大到小） //descending order
默認為 ASC?

注意：沒有 ORDER BY 子句的查詢，返回的順序是未定義的，永遠不要依賴這個順序

同學及數學成績，按數學成績升序顯示??

同學及qq號，按姓名排序顯示?

NULL視為比任何值都小，升序出現在最上面。降序在最下面

默認是按照升序來排序?

查詢同學各門成績，依次按數學降序，英語升序，語文升序的方式顯示?

多字段排序，排序優先級隨書寫順序 ?

也就是說先比較第一個順序，然后當兩個或者多個數據第一個順序相同時，再比較第二個數據，第三個數據，以此類推

查詢同學及總分，由高到低??

ORDER BY中可以使用表達式
ORDER BY子句中可以使用列別名

為什么這里用別名進行排序呢？上次我們將where的時候不是不能用別名嗎？

?因為這個排序是第四步，也就是晚于下面的任何一步

所以也就是起別名之后再進行order by操作，故而是可以用別名來進行排序的。能不能用起的別名，完全取決與使用的順序，如果你使用的時候別名還沒起呢，那肯定用不了，如果已經起過了，那就可以用了

查詢姓孫的同學或者姓曹的同學數學成績，結果按數學成績由高到低顯示

結合 WHERE 子句和 ORDER BY 子句?

篩選分頁結果 (limit)

對于limit而言，也可以使用別名的原因?

排序是需要現有數據的

只有數據準備好了，你才能顯示，而limit的本質功能就是"顯示" ,而不是篩選，所以limit的執行順序會更靠后，比排序還靠后。所以也可以使用別名。

Update

語法

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]

對查詢到的結果進行列值更新（一般要加where條件否則會全部被更新）

?將孫悟空同學的數學成績變更為80分 ?

更新值為具體值

將曹孟德同學的數學成績變更為60分，語文成績變更為70分??

?一次更新多個列 ?

將總成績倒數前三的3位同學的數學成績加上30分??

數據更新，不支持math+=30這種語法? ?要用math = math+30這種寫法

update exam_result set math=math+30 order by chinese+english+math asc limit 3;

將所有同學的語文成績更新為原來的2倍

注意：更新全表的語句慎用！--沒有WHERE子句，則更新全表

update exam_result set chinese=chinese*2;

Delete?

刪除表

語法

DELETE FROM  table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

刪除孫悟空同學的考試成績?

delete from exam_result where name='孫悟空'；

去掉班級的倒數第一名

delete from  exam_result order by english+math+chinese asc limit 1;

刪除整張表數據

?delete只是刪表數據，不刪表結構。表結構是由alter來管理的

先新建表，并插入一點數據?

再查表的結構，然后再進行刪除操作?

?我們會發現我們只是把表數據給刪除了，但是表的結構還在！！計數器沒有變

?截斷表（truncate）

語法

TRUNCATE [TABLE] table_name

我們還按照剛才的例子重新測試下

?然后開始截斷，并再次查看表數據和表結構

注意：這個操作慎用

只能對整表操作，不能像DELETE一樣針對部分數據操作；

實際上MySQL 不對數據操作，所以比DELETE更快，但是TRUNCATE在刪除數據的時候，并不經過真正的事務（不會被記錄到日志里），所以無法回滾

會重置AUTO_INCREMENT項

三種日志：

bin log：?歷史上操作過的sql語句優化之后保留下來——方便主從同步、備份、恢復

redo log：確保宕機、斷電的時候數據不丟失（因為數據可能在內存中存著）——保證崩潰安全

undo log：做事務回滾、事務的隔離性

插入查詢結果（insert+select）

INSERT INTO table_name [(column [, column ...])] SELECT ...

刪除表中的重復記錄，重復的數據只能有一份?

建表

?插入測試數據

不能用distinct去重，因為它不影響原表的數據，而是修改的顯示的數據

但是我們可以將insert和select結合起來用，將distinct篩選出來的數據插入到空表中！！然后再改一下表的名字！！

第一步：create table no_duplicate_table like duplicate_table;建立一張和原表結構相同的空表

第二步：insert into?no_duplicate_table select distinct * from duplicate_table; 查詢原表去重后的結果然后插入到新表中

第三步：rename table duplicate_table to old_duplicate_table,no_duplicate_table to duplicate_table;將原表重命名備份一下，然后再把新表的名字改成原表的名字

為什么最后是通過rename的方式進行的？

創建一個數據庫其實就是創建一個文件夾,創建一張表其實就是創建一個文件，對應的系統調用就是mkdir和touch，而rename背后的也是類似rename這樣的系統調用，平時我們用的move指令重命名也是類似的，如果我今天想把一個文件上傳到linux下，可能上傳得很慢，我想等這個文件上傳好之后，把這個文件放到某個目錄下，我希望他放入的過程是原子的，所以我們一定不能直接把這個文件直接上傳到對應的目錄下，因為上傳的過程一直在寫入，一定不是原子的，所以一般我們喜歡把這個要上傳的文件上傳到一個臨時的目錄下，等全都上傳完成之后，再把整個文件move到特定的目錄下，這個move是原子的。

所以總的來說，用rename單純就是相等一切都就緒了，然后統一放入、更新、生效等。因為我們的move操作和重命名操作實際上就是在文件系統里就是改這個文件所在的目錄里面文件名和inode的映射關系，他相較于冗長地向表中插入和冗長的上傳行為比起來非常輕。很有可能我這個目錄有很多文件包括正在操作的這個文件正在被外部的網站或者各種語言正在訪問，所以我們不能著急動這個表而是應該先把這個表先傳到臨時目錄然后再統一move過去，這是一種比較推薦的做法

聚合函數

統計班級共有多少同學?

使用 * 做統計，不受 NULL 影響

統計本次考試的數學成績分數個數

NULL 不會計入結果?

COUNT(math) 統計的是全部成績 ?

?COUNT(DISTINCT math) 統計的是去重成績數量 ?

統計數學成績總分?

統計數學的平均分

數學不及格的人有多少

統計平均總分?

返回英語最高分?

返回 > 70 分以上的數學最低分

聚合函數：1、在應用層上更多的是在未來進行某種程度上的數據統計，是有自己的現實需求的2、大部分聚合都是簡單的場景，還有一部分場景需要對信息做完分組之后做聚合?

分組聚合統計（group by）

分組的目的是為了方便后面的聚合統計?（比如說分成男生女生然后分別做統計）

在select中使用group by 子句可以對指定列進行分組查詢 ?

select column1, column2, .. from table group by column;

案例：準備工作，創建一個雇員信息表（來自oracle 9i的經典測試表）

1、EMP員工表

2、DEPT部門表

3、SALGRADE工資等級表

//利用source將該備份文件恢復到數據庫中
DROP database IF EXISTS `scott`;//如果曾經有這個名字是數據庫就刪掉
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;//創建這個數據庫USE `scott`;//使用這個數據庫DROP TABLE IF EXISTS `dept`;//如果有這個名字的部門表叫把他刪掉
CREATE TABLE `dept` (//創建部門表`deptno` int(2) unsigned zerofill NOT NULL COMMENT '部門編號',`dname` varchar(14) DEFAULT NULL COMMENT '部門名稱',`loc` varchar(13) DEFAULT NULL COMMENT '部門所在地點'
);DROP TABLE IF EXISTS `emp`;//如果有這個名字的部門表叫把他刪掉
CREATE TABLE `emp` (//創建員工表`empno` int(6) unsigned zerofill NOT NULL COMMENT '雇員編號',`ename` varchar(10) DEFAULT NULL COMMENT '雇員姓名',`job` varchar(9) DEFAULT NULL COMMENT '雇員職位',`mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇員領導編號',`hiredate` datetime DEFAULT NULL COMMENT '雇傭時間',`sal` decimal(7,2) DEFAULT NULL COMMENT '工資月薪',//外鍵`comm` decimal(7,2) DEFAULT NULL COMMENT '獎金',`deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部門編號'//外鍵
);DROP TABLE IF EXISTS `salgrade`;//如果有這個名字的部門表叫把他刪掉
CREATE TABLE `salgrade` (//薪資表  可以客觀反應這個員工在公司的重要程度`grade` int(11) DEFAULT NULL COMMENT '等級',`losal` int(11) DEFAULT NULL COMMENT '此等級最低工資',`hisal` int(11) DEFAULT NULL COMMENT '此等級最高工資'
);//插入部門
insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');//核算部門
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');//搜索部門
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');//銷售部門
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');//運營部門//插入員工
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);//插入不同等級的薪資
insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

該數據庫的表結構?

表emp?

表 dept?

表salgrade?

顯示每個部分的平均工資和最高工資?

select deptno,max(sal) 最高,avg(sal) 平均 from EMP group by deptno;

from emp：
- 從 emp（員工）表中查詢數據
group by deptno：
- 按部門編號（deptno）將員工數據分組
- 意味著所有具有相同部門編號的員工會被歸類到同一個組
select 部分：
- deptno：顯示部門編號
- max(sal) 最高：計算每個部門的最高工資
- avg(sal) 平均：計算每個部門的平均工資

這個查詢的具體含義是：

按部門編號（deptno）將員工表（emp）分組
對每個部門計算：
- 最高工資（max(sal)）
- 平均工資（avg(sal)）

groupby的宏觀理解

讓我們進行分組聚合統計的。分組指定列名，實際分組是用該列的不同的行數據

比如說你按照deptno進行分組，那它就會把deptno列中，相同的數據成為一組，也就是可以被聚合壓縮。

分組，就是把一組按照條件拆成了多個組，然后進行各自組內的統計。

分組也就是把一張表按照條件在邏輯上拆成了多個子表，然后分別對各自的子表進行聚合統計

顯示每個部門的每種崗位的平均工資和最低工資

也就是說不僅要按照部門分組，也要按照崗位分組

用,來進行區分不同的組

select deptno,job from EMP group by deptno, job;

再聚合，也就是進行計算所需要的數據

select deptno,job,avg(sal) 平均,min(sal) 最低 from EMP group by deptno, job;

為啥進行員工名字ename分組的時候不行呢？

原因是ename沒有在分組條件中出現，不屬于分組條件，所以無法進行聚合和壓縮。也就是說，select后面要想能出現，必須在group by后進行添加。

顯示平均工資低于2000的部門和它的平均工資

第一步，先統計出來每一個部門的平均工資，然后在進行對比，也就是說先把結果聚合出來

select deptno,avg(sal) deptavg from EMP group by deptno;

第二步，再進行判斷。就是對聚合的結果進行判斷

select deptno,avg(sal) deptavg from EMP group by deptno having deptavg<2000;

那怎么對聚合的結果進行判斷呢？

having和group by配合使用，對group by結果進行過濾

having經常和group by搭配使用，作用是對聚合后的統計數據進行條件篩選，作用有些像where。?

hvaing和where的區別理解是什么呢？?

執行順序不同：
- WHERE在分組和聚合函數計算之前執行
- HAVING在分組和聚合函數計算之后執行
作用對象不同：
- WHERE作用于表中的列/字段，篩選原始數據
- HAVING作用于分組后的結果集，可以使用聚合函數

不要單純的認為，只有磁盤上表結構導入到mysql，真實存在的表才叫做表。中間篩選出來的，包括最終結果，全部都是邏輯上的表！“MySQL一切皆表”。也就是說未來只要我們處理好單表的CURD，所有的sql場景我們全部都能用統一的方式進行

面試題：SQL查詢中各個關鍵字的執行先后順序 from > on> join > where > group by > with > having > select > distinct > order by > limit?