MySQL 增刪改查（單表）

1、sql 新增陳述句

• 表中插入資料
  • insert into + 表名 values(欄位1value1，欄位2value1，欄位3value1)，(欄位1value2，欄位2value2，欄位3value2)

mysql> insert into t_student values(1,"zhangsan",18,"boy"),(2,"wangwu",20,"girl");

mysql> select * from t_student;

+----+----------+------+------+
| id | name     | age  | sex  |
+----+----------+------+------+
|  1 | zhangsan |   18 | boy  |
|  2 | wangwu   |   20 | girl |
+----+----------+------+------+

• 對表中指定欄位插入資料
  • insert into +表名(欄位1，欄位2) values (欄位1值，欄位2值)

mysql> insert into t_student(id,name,age,sex)values(3,"zhaoliu",19,"boy");

mysql> select * from t_student;

+----+----------+------+------+
| id | name     | age  | sex  |
+----+----------+------+------+
|  1 | zhangsan |   18 | boy  |
|  2 | wangwu   |   20 | girl |
|  3 | zhaoliu  |   19 | boy  |
+----+----------+------+------+

• 洗掉表中指定資料
  • delete from + 表名 where 條件　　
  • delete from + 表名　　--->     清空表

# 洗掉 t_student 表中 id = 4 的資料
mysql> delete from t_student where id = 4

# 清空 t_student 表
mysql> delete from t_student

• 更新表中指定欄位資料
  • update + 表名 set 欄位名=值 where 條件　　

# 修改 t_student 表中 wangwu 的性別為 boy
mysql> update t_student set sex = "boy" where name = "wangwu";

mysql> select * from t_student;

+----+----------+------+------+
| id | name     | age  | sex  |
+----+----------+------+------+
|  1 | zhangsan |   18 | boy  |
|  2 | wangwu   |   20 | boy  |
|  3 | zhaoliu  |   19 | boy  |
+----+----------+------+------+

4、sql 單表查詢陳述句

• 查詢表中所有資料
  • select * from + 表名
    • * 代表所有

# 查詢 t_student 表中所有資料
mysql> select * from t_student;

+----+----------+------+------+
| id | name     | age  | sex  |
+----+----------+------+------+
|  1 | zhangsan |   18 | boy  |
|  2 | wangwu   |   20 | boy  |
|  3 | zhaoliu  |   19 | boy  |
+----+----------+------+------+

•  查詢某個欄位的資料
  • select 欄位 from + 表名

# 查詢出 t_student 表中 name 欄位的所有資料
mysql> select name from t_student;

+----------+
| name     |
+----------+
| zhangsan |
| wangwu   |
| zhaoliu  |
+----------+

• 查詢多個欄位的資料
  • select 欄位1，欄位2 from + 表名

# 查詢出 t_student 表中 name，age 兩個欄位的所有資料
mysql> select name,age from t_student;

+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| wangwu   |   20 |
| zhaoliu  |   19 |
+----------+------+

• 查詢滿足某個條件的所有資料
  • select * from + 表名 where 欄位=值
    
    • where 后面接滿足的條件

mysql> select age,sex from t_student where name = "zhaoliu";

+------+------+
| age  | sex  |
+------+------+
|   19 | boy  |
+------+------+

• 查詢不滿足某個條件的所有資料
  • select * from + 表名 where 欄位 != value
    • != 代表不等于，也可以用符號 <> 代表不等于　　

mysql> select age,sex from t_student where name != "zhaoliu";
+------+------+
| age  | sex  |
+------+------+
|   18 | boy  |
|   20 | boy  |
+------+------+

mysql> select age,sex from t_student where name <> "zhaoliu";
+------+------+
| age  | sex  |
+------+------+
|   18 | boy  |
|   20 | boy  |
+------+------+

• 查詢同時滿足多個條件資料
  • select * from + 表名 where 條件1 and 條件2
    • and 關鍵字左右的兩個條件必須同時滿足

# 查詢出性別為 boy 且班級為 2 班的學生資訊
mysql> select * from t_student where sex = "boy" and class = 2;

+----+---------+------+------+-------+
| id | name    | age  | sex  | class |
+----+---------+------+------+-------+
|  3 | zhaoliu |   19 | boy  | 2     |
+----+---------+------+------+-------+

• 查詢滿足至少 1 個條件的資料
  • select * from + 表名 where 條件1 or 條件2
    • or 關鍵字左右的兩個條件至少滿足 1 個，否則回傳空

# 查詢出年紀為 18 歲 或者班級為 2 班的學生資訊
mysql> select * from t_student where age = 18 or class = 2;

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
+----+----------+------+------+-------+

• 查詢一個條件范圍內的資料
  • select * from + 表名 where 欄位 between m and n
    
    • between...and ... 指定一個范圍

# 查詢出年紀在 19 - 20 之間的學生資訊
mysql> select * from t_student where age between 19 and 20;

+----+---------+------+------+-------+
| id | name    | age  | sex  | class |
+----+---------+------+------+-------+
|  2 | wangwu  |   20 | boy  | 1     |
|  3 | zhaoliu |   19 | boy  | 2     |
+----+---------+------+------+-------+

• 查詢欄位滿足在指定的集合中的資料
  • select * from + 表名 where 欄位 in（值1，值2，值3）

mysql> select * from t_student where age in (18,19);

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
+----+----------+------+------+-------+

• 查詢欄位不滿足在指定集合中的資料
  • select * from + 表名 where 欄位 not in （值1，值2，值3）

mysql> select * from t_student where age not in (18,19);

+----+--------+------+------+-------+
| id | name   | age  | sex  | class |
+----+--------+------+------+-------+
|  2 | wangwu |   20 | boy  | 1     |
+----+--------+------+------+-------+

• 查詢欄位值為空的資料
  • select * from + 表名 where 欄位 is null　　
    • 注意：欄位是空不能寫成 欄位 = null

mysql> select * from t_student where class is NULL;

+----+------+------+------+-------+
| id | name | age  | sex  | class |
+----+------+------+------+-------+
|  4 | lisi |   22 | girl | NULL  |
+----+------+------+------+-------+

• 查詢欄位不為空的資料
  • select * from + 表名 where 欄位 is not null

mysql> select * from t_student where class is not NULL;

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
|  2 | wangwu   |   20 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
+----+----------+------+------+-------+

• 查詢某個欄位模糊匹配成功的資料　　
  • select * from +表名 where 欄位 like "%值%"
    • % 用于匹配欄位開頭和結尾

# 查詢出表中姓 zhang 的學生資訊
mysql> select * from t_student where name like "zhang%";

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
+----+----------+------+------+-------+

# 查詢出表中姓名中帶有 ng 的學生資訊
mysql> select * from t_student where name like "%ng%";

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
|  2 | wangwu   |   20 | boy  | 1     |
+----+----------+------+------+-------+

• 查詢限定的數量的資料
  • select * from + 表名 limit m，n　　　　
    • m 指下標，n 指限定的數量，下標為 m 的開始的 n 條資料

# 查詢出表中 第 2，3 兩行學生資訊
mysql> select * from t_student limit 1,2;

+----+---------+------+------+-------+
| id | name    | age  | sex  | class |
+----+---------+------+------+-------+
|  2 | wangwu  |   20 | boy  | 1     |
|  3 | zhaoliu |   19 | boy  | 2     |
+----+---------+------+------+-------+

• 查詢的資料根據某個欄位從小到大排序
  • select * from + 表名 order by 欄位 asc　　
    • order by ...asc 從小到大排序

# 查詢表資料，按照 age 欄位升序排序
mysql> select * from t_student order by age asc;

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  1 | zhangsan |   18 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
|  2 | wangwu   |   20 | boy  | 1     |
|  4 | lisi     |   22 | girl | NULL  |
+----+----------+------+------+-------+

• 查詢的資料根據某個欄位從大到小排序
  • select * from + 表名 order by 欄位 desc　　
    • order by ... desc 從大到小排序

# 查詢表資料，按照 age 欄位降序排序
mysql> select * from t_student order by age desc;

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  4 | lisi     |   22 | girl | NULL  |
|  2 | wangwu   |   20 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
|  1 | zhangsan |   18 | boy  | 1     |
+----+----------+------+------+-------+

• 查詢的資料根據某個欄位進行分組
  • select * from + 表名 group by 欄位
    • group by ... 根據條件分組

mysql> select * from t_student group by class;

+----+----------+------+------+-------+
| id | name     | age  | sex  | class |
+----+----------+------+------+-------+
|  4 | lisi     |   22 | girl | NULL  |
|  1 | zhangsan |   18 | boy  | 1     |
|  3 | zhaoliu  |   19 | boy  | 2     |
+----+----------+------+------+-------+

• 查詢的資料根據某個欄位進行分組再條件過濾
  • select * from + 表名 group by 欄位 having 條件
    • having 跟在 group by 后面，作用相當于 where

mysql> select * from t_student group by class having sex = "girl";

+----+------+------+------+-------+
| id | name | age  | sex  | class |
+----+------+------+------+-------+
|  4 | lisi |   22 | girl | NULL  |
+----+------+------+------+-------+

• 聚合函式
  • 統計查詢資料的數量　　
    • select count(*) from + 表名

# 統計表中有多少行資料
mysql> select count(*) from t_student;

+----------+
| count(*) |
+----------+
|        4 |
+----------+

• • 查詢某個欄位求和
    
    • select sum(欄位) from + 表名

# 求出所有學員年紀之和
mysql> select sum(age) from t_student;

+----------+
| sum(age) |
+----------+
|       79 |
+----------+

• • 查詢某個欄位進行平均值
    
    • select avg(欄位) from + 表名

# 求出平均年紀
mysql> select avg(age) from t_student;

+----------+
| avg(age) |
+----------+
|  19.7500 |
+----------+

• • 查詢某個欄位最大值
    
    • select max(欄位) from + 表名

# 求出最大年紀
mysql> select max(age) from t_student;

+----------+
| max(age) |
+----------+
|       22 |
+----------+

• • 查詢某個欄位最小值
    
    • select min(欄位) from + 表名

# 求出最小年紀
mysql> select min(age) from t_student;

+----------+
| min(age) |
+----------+
|       18 |
+----------+

• • 對某個欄位進行去重
    
    • select distinct(欄位) from + 表名

# 對 class 欄位去重
mysql> select distinct(class) from t_student;

+-------+
| class |
+-------+
| 1     |
| 2     |
| NULL  |
+-------+

標籤：MySQL

上一篇：MySQL 表操作

下一篇：MySQL 多表查詢