1.Create
Create(創建):
添加新數據到數據庫中
#基礎語法
insert into table_name (column1,column2,column3, ...)
values (value1,value2,value3, ...);
1.1 單行全列插入
value中值的數量和順序必須和column?致
describe demo1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#插入(id=1,name='張三',age=10)的記錄
mysql> insert into demo1 values (1,'張三',10);
Query OK, 1 row affected (0.01 sec)
#插入結果如下
mysql> select * from demo1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 張三 | 10 |
+------+------+------+
1 row in set (0.00 sec)
1.2 單行指定列插入
demo1:
指定(id,name,age)三列插入,相當于全列插入
insert into demo1 (id,name,age) values (2,'李四',11);
Query OK, 1 row affected (0.00 sec)mysql> select * from demo1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 張三 | 10 |
| 2 | 李四 | 11 |
+------+------+------+
2 rows in set (0.00 sec)
demo2:
指定(id,name)兩列插入
insert into demo1 (id,name) values (3,'王五');
Query OK, 1 row affected (0.00 sec)mysql> select * from demo1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 張三 | 10 |
| 2 | 李四 | 11 |
| 3 | 王五 | NULL |
+------+------+------+
3 rows in set (0.00 sec)
1.3 多行插入
在?條insert語句中也可以一次插入多行數據
insert into demo1 values (4,'趙六',12),(5,'田七',13);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from demo1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 張三 | 10 |
| 2 | 李四 | 11 |
| 3 | 王五 | NULL |
| 4 | 趙六 | 12 |
| 5 | 田七 | 13 |
+------+------+------+
5 rows in set (0.00 sec)
2.Read
Read(讀取):
查詢或獲取現有數據
#基礎語法
select 通配符/列名 from 表名
2.1 全列查詢
#(*)通配符
#使?(*)可以查詢表中(所有列)的值
select * from demo1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | 張三 | 10 |
| 2 | 李四 | 11 |
| 3 | 王五 | NULL |
| 4 | 趙六 | 12 |
| 5 | 田七 | 13 |
+------+------+------+
5 rows in set (0.00 sec)
2.1 指定列查詢
指定(id,name)兩列查詢
select id,name from demo1;
+------+------+
| id | name |
+------+------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 趙六 |
| 5 | 田七 |
+------+------+
5 rows in set (0.00 sec)
#注意1:可以指定多列查詢,也可以指定單列查詢
#注意2:查詢的順序和指定的順序有關
select name,id from demo1;
+------+------+
| name | id |
+------+------+
| 張三 | 1 |
| 李四 | 2 |
| 王五 | 3 |
| 趙六 | 4 |
| 田七 | 5 |
+------+------+
5 rows in set (0.00 sec)
2.3 表達式作為查詢條件
select * from exam;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+------+--------+---------+------+---------+
7 rows in set (0.00 sec)
#將表達式(english + 10)作為查詢條件
mysql> select id,name,chinese,math,(english+10) from exam;
+------+--------+---------+------+--------------+
| id | name | chinese | math | (english+10) |
+------+--------+---------+------+--------------+
| 1 | 唐三藏 | 67 | 98 | 66 |
| 2 | 孫悟空 | 87 | 78 | 87 |
| 3 | 豬悟能 | 88 | 98 | 100 |
| 4 | 曹孟德 | 82 | 84 | 77 |
| 5 | 劉玄德 | 55 | 85 | 55 |
| 6 | 孫權 | 70 | 73 | 88 |
| 7 | 宋公明 | 75 | 65 | 40 |
+------+--------+---------+------+--------------+
7 rows in set (0.00 sec)
2.4 為查詢結果指定別名
關鍵字:
as
#為(chinese+math+english)指定別名為(總分)
select id,name,(chinese+math+english) as '總分' from exam;
+------+--------+------+
| id | name | 總分 |
+------+--------+------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 劉玄德 | 185 |
| 6 | 孫權 | 221 |
| 7 | 宋公明 | 170 |
+------+--------+------+
7 rows in set (0.00 sec)
2.5 去重查詢
關鍵字:
distinct
2.5.1 去重查詢(單列)
select * from exam;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 關云長 | 70 | 80 | 90 |
+------+--------+---------+------+---------+
8 rows in set (0.00 sec)mysql> select english from exam;
+---------+
| english |
+---------+
| 56 |
| 77 |
| 90 |#第一個90分
| 67 |
| 45 |
| 78 |
| 30 |
| 90 |#第二個90分
+---------+
8 rows in set (0.00 sec)
#對(english)這一列進行去重
mysql> select distinct english from exam;
+---------+
| english |
+---------+
| 56 |
| 77 |
| 90 |#第一個90分
| 67 |
| 45 |
| 78 |
| 30 |
+---------+
7 rows in set (0.00 sec)
注意:
2.5.2 去重查詢(多列)
select * from exam;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
+------+--------+---------+------+---------+
9 rows in set (0.00 sec)mysql> select math,english from exam;
+------+---------+
| math | english |
+------+---------+
| 98 | 56 |
| 78 | 77 |
| 98 | 90 |
| 84 | 67 |
| 85 | 45 |
| 73 | 78 |
| 65 | 30 |
| 80 | 90 |#(1)math=80,english=90
| 80 | 90 |#(2)math=80,english=90
+------+---------+
9 rows in set (0.00 sec)
#同時對(math,english)兩列同時進行去重查詢
#要保證兩行中math和english的分數要分別相同
mysql> select distinct math,english from exam;
+------+---------+
| math | english |
+------+---------+
| 98 | 56 |
| 78 | 77 |
| 98 | 90 |
| 84 | 67 |
| 85 | 45 |
| 73 | 78 |
| 65 | 30 |
| 80 | 90 |
+------+---------+
8 rows in set (0.00 sec)
注意:
2.6 條件查詢
關鍵字:
where
#基礎語法
select 通配符/列名 from 表名 where (條件)
2.6.1 比較運算符
運算符 | 說明 |
---|---|
>,<,>=,<= | 大于,小于,大于等于,小于等于 |
= | 等于(MySQL中不存在==) |
<=> | 用于null的比較。例如:null <=> null 的結果是true,null = null 的結果還是null |
!=,<> | 不等于 |
value between A and B | 范圍匹配,如果value在[A,B]之間返回true |
value not between A and B | 范圍匹配,如果(value < A并且 value > B)返回true |
value in (option1,option2…) | 如果value與某一option匹配則返回true,not in表示取反 |
is null/is not null | 是null/不是null |
_ | 模糊匹配,表示(一個)任意字符 |
like | 模糊匹配,表示(任意個)任意字符 |
2.6.2 邏輯運算符
運算符 | 說明 |
---|---|
and | 邏輯與,全true為true,有false為false |
or | 邏輯或,全false為false,有true為true |
not | 邏輯非,條件為true,結果為false |
2.6.3 比較條件查詢
demo1:查詢語文成績比數學高的記錄
select * from exam where chinese > math;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 2 | 孫悟空 | 87 | 78 | 77 |
| 7 | 宋公明 | 75 | 65 | 30 |
+------+--------+---------+------+---------+
2 rows in set (0.01 sec)
demo2:查詢英語大于60的記錄
select * from exam where english > 60;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 6 | 孫權 | 70 | 73 | 78 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
+------+--------+---------+------+---------+
6 rows in set (0.00 sec)
demo3:查詢總分大于200的記錄
select id,name,(chinese + math + english) as '總分' from exam where (chinese + math + english) > 200;
+------+--------+------+
| id | name | 總分 |
+------+--------+------+
| 1 | 唐三藏 | 221 |
| 2 | 孫悟空 | 242 |
| 3 | 豬悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 6 | 孫權 | 221 |
| 8 | 關云長 | 240 |
| 8 | 張翼德 | 240 |
+------+--------+------+
7 rows in set (0.00 sec)
注意:
不能在where條件中進行取別名的操作
2.6.4 邏輯條件查詢
demo1:查詢語文成績大于80分并且數學成績大于80分的同學
select * from exam where chinese > 80 and math > 80;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)
demo2:查詢語文成績大于80或者數學成績大于80分的同學
select * from exam where chinese > 80 or math > 80;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
+------+--------+---------+------+---------+
5 rows in set (0.00 sec)
注意:
and的優先級大于or
2.6.5 范圍查詢
demo1:查詢語文成績在[80,90]分的記錄
select * from exam where chinese between 80 and 90;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
demo2:查詢數學成績是78或者79或者98或者99分的記錄
#使用or實現
select * from exam where math = 78 or math = 79 or math = 98 or math = 99;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
#使用in實現
mysql> select * from exam where math in (78,79,98,99);
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
2.6.6 模糊查詢
demo1:查詢姓孫的記錄
select * from exam where name like '孫%';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 2 | 孫悟空 | 87 | 78 | 77 |
| 6 | 孫權 | 70 | 73 | 78 |
+------+--------+---------+------+---------+
2 rows in set (0.00 sec)
demo2:查詢姓孫并且名字只有兩個字的記錄
select * from exam where name like '孫_';
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 6 | 孫權 | 70 | 73 | 78 |
+------+------+---------+------+---------+
1 row in set (0.00 sec)
2.6.7 null(空)值查詢
demo1:查詢英語成績為null的記錄
select * from exam where english is null;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 11 | 黃漢升 | 70 | 85 | NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)mysql> select * from exam where english <=> null;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 11 | 黃漢升 | 70 | 85 | NULL |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
demo2:查詢英語成績不為null的記錄
select * from exam where english != null;
Empty set (0.00 sec)mysql> select * from exam where english <> null;
Empty set (0.00 sec)mysql> select * from exam where english is not null;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
| 10 | 趙子龍 | 70 | 85 | 85 |
+------+--------+---------+------+---------+
10 rows in set (0.00 sec)
2.7 排序
asc:
升序desc:
降序,不是查看表結構的desc(describe)
#基礎語法
#默認asc
select 通配符/列名 from 表名 (where...) order by 列名 (asc/desc);
demo1:按照語文成績升序
select * from exam order by chinese asc;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 5 | 劉玄德 | 55 | 85 | 45 |
| 1 | 唐三藏 | 67 | 98 | 56 |
| 6 | 孫權 | 70 | 73 | 78 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
| 10 | 趙子龍 | 70 | 85 | 85 |
| 11 | 黃漢升 | 70 | 85 | NULL |
| 7 | 宋公明 | 75 | 65 | 30 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)
demo2:按照語文成績降序
select * from exam order by chinese desc;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 3 | 豬悟能 | 88 | 98 | 90 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 6 | 孫權 | 70 | 73 | 78 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
| 10 | 趙子龍 | 70 | 85 | 85 |
| 11 | 黃漢升 | 70 | 85 | NULL |
| 1 | 唐三藏 | 67 | 98 | 56 |
| 5 | 劉玄德 | 55 | 85 | 45 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)
demo3:按照數學降序,英語升序,語?升序
select * from exam order by math desc,english asc,chinese asc;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 11 | 黃漢升 | 70 | 85 | NULL |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 10 | 趙子龍 | 70 | 85 | 85 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+------+--------+---------+------+---------+
11 rows in set (0.00 sec)
2.8 分頁查詢
#基礎語法1:默認從0開始,篩選num條記錄
select 通配符/列名 from 表名 (where...) (order by ...) limit num;
#基礎語法2:從start開始,篩選num條記錄
select 通配符/列名 from 表名 (where...) (order by ...) limit start,num;
#基礎語法3(建議):從start開始,篩選num條記錄
select 通配符/列名 from 表名 (where...) (order by ...) limit num offset start;
demo1:假設一頁有三條記錄,查詢第一頁的記錄
#建議搭配(order by)使用
select * from exam order by id asc limit 3 offset 0;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 78 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
demo2:假設一頁有三條記錄,查詢第二頁的記錄
select * from exam order by id asc limit 3 offset 3;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)
3.Update
Update(更新):
修改數據庫或系統中已存在的記錄
#基礎語法
update 表名 (要修改的數據) (where...) (order by...) (limit...);
demo1:
將孫悟空的數學成績變更為80分
update exam set math = 80 where name = '孫悟空';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from exam where name = '孫悟空';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 2 | 孫悟空 | 87 | 80 | 77 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
demo2:
將曹孟德的數學成績變更為60分,語文成績變更為70
update exam set math = 60,chinese = 70 where name = '曹孟德';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from exam where name = '曹孟德';
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 4 | 曹孟德 | 70 | 60 | 67 |
+------+--------+---------+------+---------+
1 row in set (0.00 sec)
注意:
當update語句缺少where條件時,全表的記錄都將被更新
4.Delete
Delete(刪除):
從數據庫或系統中移除記錄
#基礎語法
delete from 表名 (where...) (order by...) (limit...);
demo1:刪除姓名為黃漢升的記錄
delete from exam where name = '黃漢升';
Query OK, 1 row affected (0.01 sec)mysql> select * from exam;
+------+--------+---------+------+---------+
| id | name | chinese | math | english |
+------+--------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孫悟空 | 87 | 80 | 77 |
| 3 | 豬悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 70 | 60 | 67 |
| 5 | 劉玄德 | 55 | 85 | 45 |
| 6 | 孫權 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
| 8 | 關云長 | 70 | 80 | 90 |
| 8 | 張翼德 | 70 | 80 | 90 |
| 10 | 趙子龍 | 70 | 85 | 85 |
+------+--------+---------+------+---------+
10 rows in set (0.00 sec)
demo2:刪除整張表的記錄
delete from exam;
Query OK, 10 rows affected (0.01 sec)mysql> select * from exam;
Empty set (0.00 sec)