【MySQL篇】MySQL基本查詢詳解

前言：?

1，Create

1.1，單行數據+全列插入?

1.2，單行數據+指定列插入?

?1.3，多行數據+全列插入

1.4，多行數據+指定列插入?

1.5，插入否則更新?

1.6，替換

2，Retrieve （讀取）

2.1，全列查詢

2.2，指定列查詢

2.3，查詢字段為表達式

?2.4，為查詢結果指定別名

2.5，結果去重?

2.6，where條件?

2.7，結果排序?

?2.8，篩選分頁結果

3，Update?

4，Delete?

4.1，刪除數據

?4.2，刪除整張表

?4.3，截斷表

5，插入查詢結果

6，聚合函數?

7，group by子句的使用?

?結語：

前言：?

本篇主要講述對于表內容的增刪查改及相關查詢語句:

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

1，Create

語法:

insert into 表名? (指定列)?values? (values1,values2,...)

示例：

?創建一張學生表

mysql> create table students(
? ? -> id int unsigned primary key auto_increment,
? ? -> sn int not null unique comment '學號',
? ? -> name varchar(20) not null,
? ? -> qq varchar(20) unique);

1.1，單行數據+全列插入?

插入數據

注意在這里插入的時候也可以不指明id，因為我們定義了auto_increment屬性，mysql會使用默認的值自增。

mysql> insert into students values (1,101,'張三',1111);

mysql> insert into students values (2,102,'李四',11222);

查看插入結果?

select * from students;

1.2，單行數據+指定列插入?

?指定列插入數據

mysql> insert into students ?(sn,name,qq) values (103,'李四',11333);
mysql> insert into students (id,sn,name) values (10,104,'王五');

?1.3，多行數據+全列插入

mysql> insert into students values (20,105,'趙六',2222) ,(21,106,'田七',3333);

1.4，多行數據+指定列插入?

mysql> insert into students (sn,name) values (116,'北京'),(117,'上海');

1.5，插入否則更新?

在插入數據的時候，由于主鍵和唯一鍵的存在，可能導致插入的數據已經存在而插入失敗。

如果需要更新，可以進行更新操作。也就是在插入數據時，如果不存在就直接插入，如果存在，發生沖突了，就將原來的數據更新為插入的數據。這個和STL中的unordered_map類似，在使用[ ]的時候，如果存在就更新，如果不存在就插入。

語法：

insert? ......? on duplicate key update? column=value[......]

示例：

mysql> insert into students values (1,101,'張三',55555) on duplicate key update id=1,sn=101,name='張三',qq=55555;

在更新的時候也要保證不和其他值沖突。?

1.6，替換

替換replace與上面的插入更新類似，如果數據存在就替換，如果數據不存在就直接插入。

mysql> replace into students (sn,name) values (120,'廣州');//不存在直接插入

mysql> replace into students (id,sn,name,qq) ?values (1,101,'蘇州',99999);//數據已經存在，則替換掉原數據

2，Retrieve （讀取）

首先創建一張學生成績表，作為示例

mysql> create table exam_result(
? ? -> id int unsigned primary key auto_increment,
? ? -> name varchar(20),
? ? -> chinese int,
? ? -> math int,
? ? -> english int);

向表中插入一些數據：

?mysql> insert into exam_result (name,chinese,math,english) values ('孫悟空',87,78,77);
?

mysql> insert into exam_result (name,chinese,math,english) values ('豬悟能',88,98,90);
?

mysql> insert into exam_result (name,chinese,math,english) values ('曹孟德',82,84,67);
?

mysql> insert into exam_result (name,chinese,math,english) values ('劉玄德',55,85,45);
?

mysql> insert into exam_result (name,chinese,math,english) values ('孫權',70,73,78);
?

mysql> insert into exam_result (name,chinese,math,english) values ('宋公明',75,65,30);?

2.1，全列查詢

?mysql> select * from exam_result;

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

查詢的列越多意味著需要傳輸的數據量越大；
可能會影響到索引的作用。（索引在后面更新）

2.2，指定列查詢

select 篩選的列名稱 from 表名

示例：

mysql> select id,name,math from exam_result;
mysql> select id,name,chinese ?from exam_result;?

2.3，查詢字段為表達式

?表達式不包含字段

mysql> select id,name,math,10 from exam_result;
mysql> select id,name,math,10+10 from exam_result;

表達式中包含一個字段?

mysql> select id,name,math+10 from exam_result;

表達式中包含多個字段?

mysql> select id,name,math+chinese+english from exam_result;

?2.4，為查詢結果指定別名

select? column? [as] 別名 from 表名;

示例：?

mysql> select id,name,math+chinese+english as total from exam_result;
mysql> select id,name,math+chinese+english ?'總分'? from exam_result;

2.5，結果去重?

math列中的數據98重復了。

?去重，需要使用distinct

mysql> select distinct math from exam_result;

2.6，where條件?

前面講的select 用法，是用來篩選出哪些列，而接下來的這部分內容where條件，是篩選出滿足條件的行。?

比較運算符

>,>=,<,<=：大于，大于等于，小于，小于等于
=：等于，比較null值時不安全，比如null=null返回的結果是null
<=>：等于，比較null值時是安全的，比如null=null返回的結果是1
!=,<>：不等于
between a0? and? a1：范圍匹配，[a0,a1]，如果a0<=values<=a1，返回true(1)
in(option...)：如果是option中的任意一個，返回true(1)
is null：是空
is not null：不是空
like：模糊匹配。%表示任意多個字符（包括0個），_表示任意一個字符

邏輯運算符?

and：多個條件必須都為true，結果才為true
or：只要有一個條件為true，結果就為true
not：如果條件為true(1)，結果就為false(0)?

null和null的比較，=和<=>的比較：

使用案例：?

英語成績不及格的同學及英語成績（<60）

mysql> select name,english from exam_result where english<60;

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

mysql> select name,chinese from exam_result where chinese>=80 and chinese<=90;
mysql> select name,chinese from exam_result where chinese between 80 and 90;

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

mysql> select name,math from exam_result where math=58 or math=59 or math=98 or math=99;
mysql> select name,math from exam_result where math in (58,59,98,99);

姓孫的同學

mysql> select name from exam_result where name like '孫%';

孫某同學?

mysql> select name from exam_result where name like '孫_';

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

//where條件中比較運算符兩側都是字段

mysql> select name,chinese,english from exam_result where chinese>english;

總分在200分以下的同學?

mysql> select name,math+chinese+english from exam_result where math+chinese+english<200;

前面在select內容部分，我們可以為查詢結果math+chinese+english取別名。但在where子句中是否可以使用別名呢？

可以看到是不能使用的。這和mysql的執行順序有關。?

?總結：先確定操作哪張表，再根據where子句的條件篩選出我們想要的數據，最后再顯示出來。

?所以在where子句中不能使用別名，但在select中仍然可以使用。

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

mysql> select name,chinese from exam_result where chinese>80 and name not like '孫%';

孫某同學，或者總成績>200并且語文成績<數學成績并且英語成績>80

mysql> 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);

2.7，結果排序?

語法：

---ASC?升序（從小到大）

---DESC 降序（從大到小）

---默認為ASC

select ...... from table_name [where...]? order by column [ASC|DESC]；

注意：null視為比任何值都小?

案例：

查詢同學及數學成績，數學成績按照升序顯示

mysql> select name,math from exam_result order by math;

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

mysql> select name,math,english,chinese from exam_result order by math desc,english,chinese;

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

mysql> select name,math+chinese+english from exam_result order by chinese+math+english desc;

order by 子句可以使用別名?

mysql> select name,math+chinese+english '總分' from exam_result order by 總分?desc;

在這里為什么可以使用別名了呢？同樣這和mysql的子句執行順序有關。

總結：首先我們需要確定操作哪張表，如果第二步就執行排序，其實表中有一部分數據是不需要進行排序的，?這樣做太浪費時間和空間了。所以先確定操作哪張表，再根據where子句條件篩選出來我們要的數據，這時的數據一定是被篩選出來的有價值的數據，這時我們只是選出來數據了，不給顯示出來，最后經過排序，再將數據顯示出來。

因此執行ordere by時，別名已經定義了，所以可以使用。

?2.8，篩選分頁結果

語法：

----起始下標從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 s;

注意：limit的執行順序比order by還要靠后?

?按id進行分頁，分別顯示第1，2，3頁

3，Update?

語法：

update table_name set column=expr [column=expr...]? [where...] [order by...] [limit...]

案例：?

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

mysql> update exam_result set math=80 where name='孫悟空';

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

mysql> update exam_result set math=60,chinese=70 where name='曹孟德';

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

mysql> select name,math,chinese+math+english 總分 from exam_result order by 總分 limit 3;

4，Delete?

4.1，刪除數據

語法：

delete from table_name [where...] [order by...] [limit ...]

注意：如果delete from 表名，會將表的內容清空?

案例：

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

mysql> delete from exam_result where name='孫悟空';

?4.2，刪除整張表

準備測試表

mysql> create table for_delete(
? ? -> id int primary key auto_increment,
? ? -> name varchar(20));

其中id有自增屬性。

插入測試數據?

mysql> insert into for_delete values (1,'A'),(2,'B'),(3,'C');

查看測試數據?

刪除整張表，查看刪除結果

mysql> delete from for_delete;

mysql> select * from for_delete;

刪除表之前id自增到3，現在插入一條新數據。?

mysql> insert into for_delete (name) values ('D');

可以發現在清空表后，id的自增值沒有變為0。可以通過show查看表結構。

mysql> show create table for_delete\G

?4.3，截斷表

語法：

truncate [table] table_name

注意：這個操作慎用

1，這個操作只能對整張表使用，不能向delete一樣針對部分數據操作

2，不對數據操作，所以比delete快，但是truncate 在刪除數據的時候，并不經過正真的事務，所以無法回滾?

3，該操作與delete相比，它會重置auto_increment項

案例：

準備測試表（和for_delete表屬性一樣）

mysql> create table for_truncate like for_delete;

插入測試數據，查看測試數據?

mysql> insert into for_truncate values (1,'A'),(2,'B'),(3,'C');

mysql> select * from for_truncate;

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

mysql> truncate table for_truncate;

?查看刪除結果

mysql> select * from for_truncate;

再插入一條數據，自增id重新開始增長??

mysql> insert into for_truncate (name) values ('D');

查看表結構?

mysql> show create table for_truncate \G

5，插入查詢結果

語法：

insert into table_name [column...]? select ...

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

?創建原數據表

mysql> create table duplicate_table(
? ? -> id int,
? ? -> name varchar(20));

插入測試數據?

mysql> insert into duplicate_table values ?(100,'aaa'), (100,'aaa'), (200,'bbb'), (200,'bbb'), (200,'bbb'), (300,'ccc');

思路：?

創建一張空表no_duplicate_table，結構和duplicate_table一樣

mysql> create table no_duplicate_table like duplicate_table;

將 duplicate_table數據去重插入到no_duolicate_table表中?

mysql> insert into no_duplicate_table select distinct * from duplicate_table;?

通過重命名，做到原子的去重操作?

mysql> rename table duplicate_table to old_duplicate_table;

mysql> rename table no_duplicate_table to duplicate_table;

查看最終結果?

6，聚合函數?

count()：返回查詢到的數據的數量

sum()：返回查詢到的數據的總和，不是數字沒有意義

avg()：返回查詢到的數據的平均值，不是數字沒有意義

max()：返回查詢到的數據的最大值，不是數字沒有意義

min()：返回查詢到的數據的最小值，不是數字沒有意義

案例：