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 | +----+----------+------+------+
2、sql 删除语句
- 删除表中指定数据
- delete from + 表名 where 条件
- delete from + 表名 ---> 清空表
# 删除 t_student 表中 id = 4 的数据mysql> delete from t_student where id = 4# 清空 t_student 表mysql> delete from t_student
3、sql 修改语句
- 更新表中指定字段数据
- update + 表名 set 字段名=值 where 条件
# 修改 t_student 表中 wangwu 的性别为 boymysql> 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 | +----------+------+
- 查询满足某个条件的所有数据
- where 后面接满足的条件
- select * from + 表名 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 | +------+------+
- 查询同时满足多个条件数据
- and 关键字左右的两个条件必须同时满足
- select * from + 表名 where 条件1 and 条件2
# 查询出性别为 boy 且班级为 2 班的学生信息mysql> select * from t_student where sex = "boy" and class = 2;+----+---------+------+------+-------+ | id | name | age | sex | class | +----+---------+------+------+-------+ | 3 | zhaoliu | 19 | boy | 2 | +----+---------+------+------+-------+
- 查询满足至少 1 个条件的数据
- or 关键字左右的两个条件至少满足 1 个，否则返回空
- select * from + 表名 where 条件1 or 条件2
# 查询出年纪为 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 | +----+----------+------+------+-------+
- 查询一个条件范围内的数据
- between...and ... 指定一个范围
- select * from + 表名 where 字段 between m and n
# 查询出年纪在 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 | +----+--------+------+------+-------+
- 查询字段值为空的数据
- 注意：字段是空不能写成 字段 = null
- select * from + 表名 where 字段 is 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 | +----+----------+------+------+-------+
- 查询限定的数量的数据
- m 指下标，n 指限定的数量，下标为 m 的开始的 n 条数据
- select * from + 表名 limit 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 | +----+---------+------+------+-------+
- 查询的数据根据某个字段从小到大排序
- order by ...asc 从小到大排序
- select * from + 表名 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 | +----+----------+------+------+-------+
- 查询的数据根据某个字段从大到小排序
- order by ... desc 从大到小排序
- select * from + 表名 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 | +----+----------+------+------+-------+
- 查询的数据根据某个字段进行分组
- group by ... 根据条件分组
- select * from + 表名 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 | +----+----------+------+------+-------+
- 查询的数据根据某个字段进行分组再条件过滤
- having 跟在 group by 后面，作用相当于 where
- select * from + 表名 group by 字段 having 条件
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 | +-------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
