mysql多个表增删改查_MySQL 增删改查（单表）

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 isNULL;+----+------+------+------+-------+

| id | name | age | sex | class |

+----+------+------+------+-------+

| 4 | lisi | 22 | girl | NULL |

+----+------+------+------+-------+

查询字段不为空的数据

select * from + 表名 where 字段 is not null

mysql> select * from t_student where class is notNULL;+----+----------+------+------+-------+

| 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 * fromt_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 * fromt_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(*) fromt_student;+----------+

| count(*) |

+----------+

| 4 |

+----------+

查询某个字段求和

select sum(字段) from + 表名

#求出所有学员年纪之和

mysql> select sum(age) fromt_student;+----------+

| sum(age) |

+----------+

| 79 |

+----------+

查询某个字段进行平均值

select avg(字段) from + 表名

#求出平均年纪

mysql> select avg(age) fromt_student;+----------+

| avg(age) |

+----------+

| 19.7500 |

+----------+

查询某个字段最大值

select max(字段) from + 表名

#求出最大年纪

mysql> select max(age) fromt_student;+----------+

| max(age) |

+----------+

| 22 |

+----------+

查询某个字段最小值

select min(字段) from + 表名

#求出最小年纪

mysql> select min(age) fromt_student;+----------+

| min(age) |

+----------+

| 18 |

+----------+

对某个字段进行去重

select distinct(字段) from + 表名

#对 class 字段去重

mysql> select distinct(class) fromt_student;+-------+

| class |

+-------+

| 1 |

| 2 |

| NULL |

+-------+