系列文章目錄

前言

一、Create

1.首先創建一張學生表

create table students(id int unsigned primary key auto_increment,sn int not null unique comment ‘學號’,name varchar(20) not null,qq varchar(20));

1.單行資料 + 全列插入

插入兩條記錄，value_list 數量必須和定義表的列的數量及順序一致:

insert into students value(100,1000,‘唐僧’ ,null);

insert into students value(101,10001,‘孫猴子’,‘11111’);

2. 多行資料 + 指定列插入

插入兩條記錄，value_list 數量必須和指定列數量及順序一致，

insert into students (id,sn,name) values(102,2001,‘曹孟德’),(103,2002,‘孫仲謀’),(104,2003,‘劉備’),(105,2004,‘諸葛亮’);

3.插入否則更新

由于 主鍵 或者 唯一鍵 對應的值已經存在而導致插入失敗：

1.主鍵沖突

INSERT INTO students (id, sn, name) VALUES (100, 10010, ‘唐大師’);

2.唯一鍵沖突

INSERT INTO students (sn, name) VALUES (2001, ‘曹阿瞞’);

4.替換

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

replace into students(sn,name) values(10001,‘趙云’);

主鍵 或者 唯一鍵 如果沖突，則洗掉后再插入，
replace into students(sn,name) values(2001,‘曹孟德’);

二、Retrieve

1.創建一張exam_result的表

-> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL COMMENT ‘同學姓名’,
-> yuwen float DEFAULT 0.0 COMMENT ‘語文成績’,
-> shuxue float DEFAULT 0.0 COMMENT ‘數學成績’,
-> yingyu float DEFAULT 0.0 COMMENT ‘英語成績’
-> );

2.向exam_result表中插入資料

MariaDB [user2]> INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES
-> (‘唐三藏’, 67, 98, 56),
-> (‘孫悟空’, 87, 78, 77),
-> (‘豬悟能’, 88, 98, 90),
-> (‘曹孟德’, 82, 84, 67),
-> (‘劉玄德’, 55, 85, 45),
-> (‘孫權’, 70, 73, 78),
-> (‘宋公明’, 75, 65, 30);

3.select列

1.全列查詢

通常情況下不建議使用 * 進行全列查詢：
– 1. 查詢的列越多，意味著需要傳輸的資料量越大；
– 2. 可能會影響到索引的使用，（索引待后面博客講解）

select * from exam_result;

2.指定列查詢

select name ,shuxue from exam_result;

3.查詢欄位為運算式

①運算式不包含欄位

select id,name,10 from exam_result;

②運算式包含一個欄位

select id,name ,yingyu+10 from exam_result;

③運算式包含多個欄位:

select id, name,yingyu+shuxue+yuwen from exam_result;

4.為查詢結果指定別名

語法:

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

select id,name,yingyu+shuxue+yuwen 總分 from exam_result;

5.查詢結果去重

select distinct shuxue from exam_result;

4.where約束條件

比較運算子：

邏輯運算子：

1.英語不及格的同學及英語成績 ( < 60 )

select name ,yingyu from exam_result where yingyu<60;

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

select name,yuwen from exam_result where yuwen>=80 and yuwen<=90;

select name,yuwen from exam_result where yuwen between 80 and 90;

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

select name,shuxue from exam_result where shuxue=58 or shuxue=59 or shuxue=98 or shuxue=99;

4.姓孫的同學 及 孫某同學

% 匹配任意多個（包括 0 個）任意字符:

select name from exam_result where name like ‘孫%’;

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

select name from exam_result where name like ‘孫_’;

5.語文成績好于英語成績的同學

select name ,yuwen,yingyu from exam_result where yuwen>yingyu;

6.總分在 200 分以下的同學

WHERE 條件中使用運算式-- 別名不能用在 WHERE 條件中

select name ,yuwen+shuxue+yingyu as 總分 from exam_result where yuwen+shuxue+yingyu<200;

7.語文成績 > 80 并且不姓孫的同學

select name,yuwen from exam_result where yuwen>80 and name not like ‘孫%’;

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

select name,yingyu,shuxue,yuwen,yingyu+shuxue+yuwen from exam_result where name like ‘孫%’ or(yingyu+shuxue+yuwen>200 and yuwen80);

9.NULL 的查詢

select name,qq from students where qq is not null;

5.結果排序

語法：

– ASC 為升序（從小到大）
– DESC 為降序（從大到小）
– 默認為 ASC
SELECT … FROM table_name [WHERE …]
ORDER BY column [ASC|DESC], […];

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

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

select name,shuxue from exam_result order by shuxue;

2.同學及 qq 號，按 qq 號排序顯示

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

select name ,qq from students order by qq;

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

select name ,qq from students order by qq desc;

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

select name , shuxue,yingyu ,yuwen from exam_result order by shuxue desc,yingyu,yuwen;

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

select name ,shuxue,yingyu,yuwen ,shuxue+yingyu+yuwen from exam_result order by shuxue+yingyu+yuwen desc;

ORDER BY 子句中可以使用列別名:

select name ,shuxue,yingyu,yuwen ,shuxue+yingyu+yuwen as 總分 from exam_result order by 總分 desc;

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

select name,shuxue from exam_result where name like ‘孫%’ or name like ‘曹%’ order by shuxue desc;

6.篩選分頁結果

語法：

– 起始下標為 0
– 從 0 開始，篩選 n 條結果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n;
– 從 s 開始，篩選 n 條結果
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT s, n;
– 從 s 開始，篩選 n 條結果，比第二種用法更明確，建議使用
SELECT … FROM table_name [WHERE …] [ORDER BY …] LIMIT n OFFSET n;

建議：對未知表進行查詢時，最好加一條 LIMIT 1，避免因為表中資料過大，查詢全表資料導致資料庫卡死按 id 進行分頁，每頁 3 條記錄，分別顯示 第 1、2、3 頁.，

select * from exam_result limit 2;

select * from exam_result limit 1,2;

select * from exam_result limit 3 offset 0;

三、Update

語法：

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

對查詢到的結果進行列值更新:

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

select name,shuxue from exam_result where name =‘孫悟空’;

select name,shuxue from exam_result where name =‘孫悟空’;

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

update exam_result set shuxue=60 , yuwen=70 where name=‘曹孟德’;

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

select name,shuxue+yingyu+yuwen from exam_result order by shuxue+yingyu+yuwen limit 3;

update exam_result set shuxue=shuxue+30 order by shuxue+yingyu+yuwen limit 3;

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

update exam_result set yuwen=yuwen*2;

四、Delete

1.洗掉資料

語法：

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

2.洗掉孫悟空同學的考試成績

delete from exam_result where name=‘孫悟空’;

3. 洗掉整張表資料（慎用）

4.截斷表

語法：

TRUNCATE [TABLE] table_name

• 注意：這個操作慎用
• 只能對整表操作，不能像 DELETE 一樣針對部分資料操作，
• 實際上 MySQL 不對資料操作，所以比 DELETE 更快，
• 會重置 AUTO_INCREMENT 項，

1.首先建立一張for_truncate表

create table for_truncate(id int primary key auto_increment,name varchar(20));

2.插入一些資料到for_truncate表

insert into for_truncate(name) values(‘A’),(‘B’),(‘C’);

3.截斷表資料

截斷整表資料，注意影響行數是 0，所以實際上沒有對資料真正操作，

truncate for_truncate;

再次插入資料時自增系列重置，

五、插入查詢結果

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

洗掉表中的的重復復記錄，重復的資料只能有一份，

六、聚合函式

1.統計班級共有多少同學

select count(*) from students;

2.統計班級收集的 qq 號有多少

select count(qq) from students;

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

COUNT(shuxue) 統計的是全部成績，

select count(shuxue) from exam_result;

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

select count(distinct shuxue) from exam_result;

4.統計數學成績總分

select sum(shuxue) from exam_result;

5.統計平均總分

select avg(shuxue+yuwen+yingyu) from exam_result;

6.回傳英語最高分

select max(yingyu) from exam_result;

7.回傳 > 70 分以上的數學最低分

select min(shuxue) from exam_result where shuxue>70;

七、group by子句的使用

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

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

1.先備份一張scott_data.sql資料庫

2.準備作業，創建一個雇員資訊表

• EMP員工表，
• DEPT部門表，
• SALGRADE工資等級表，
  

3.如何顯示每個部門的平均工資和最高工資

select deptno,avg(sal),max(sal) from emp group by deptno;

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

select avg(sal),min(sal),deptno from emp group by deptno,job;

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

1.統計各個部門的平均工資

select deptno,avg(sal) from emp group by deptno;

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

select deptno,avg(sal) as myavg from emp group by deptno having myavg<2000;

總結

以上就是今天要講的內容，本文詳細介紹了資料庫表的查詢，像表的增、刪、查、改，資料庫提供了大量的方法供我們使用，非常的便捷，我們務必掌握，希望大家多多支持！另外如果上述有任何問題，請懂哥指教，不過沒關系，主要是自己能堅持，更希望有一起學習的同學可以幫我指正，但是如果可以請溫柔一點跟我講，愛與和平是永遠的主題，愛各位了，加油啊！