MySQL的增删改查语句总结

MySQL的增删改查语句总结

很多同学刚开始学习数据库的时候也和我一样比较迷茫。根本无从下手。只会简单的创建和删除表。不会对表里的数据进行进行改变。下面我结合作业的方式将对MySQL中的增删改查进行举例说明。欢迎大家和勇勇一起来学习

添加数据

 

格式：insert into 表名[(字段列表)] values(值列表...); 注：[]中为可省略的数据

-- 标准添加（指定所有字段，给定所有的值）
insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'男','1');

-- 指定部分字段添加值
insert into stu(name,classid) values('yyx','1');

-- 不指定字段添加值
insert into stu values(null,'lj','20','男','2');

-- 批量添加值
insert into stu values
(null,'zhaoliu',25,'女','1'),
(null,'uu01',26,'男','2'),
(null,'uu02',28,'男','1'),
(null,'qq02',24,'男','2'),
(null,'uu03',32,'女','1'),
(null,'qq03',23,'女','2'),
(null,'aa',19,'男','1');

修改数据

格式：update 表名 set 字段1=值1,字段值2=值2,字段n=值n ...where 条件

-- 将id为2的age改为22，sex改为男
update stu set age=22,sex='男' where id=2;

-- 将id值为1-5的classid改为1,6-10的classid改为2
update stu set classid=1 where id between 1 and 5;
update stu set classid=2 where id between 6 and 10;

删除数据

格式：delete from 表名 [where 条件]

-- 删除stu表中id值为11的数据
delete from stu where id=11;

-- 删除stu表中id值为12到17的数据
delete from stu where id between 12 and 17;

-- 删除stu表中id值大于10的数据
delete from stu where id>10;

查询语句

语法格式：

select 字段列表|*  from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]

基础查询

# 查询表中所有列 所有数据
select * from stu;

# 指定字段列表进行查询
select id,name,age,sex from stu;

Where 条件查询

• 可以在where子句中指定任何条件
• 可以使用 and 或者 or 指定一个或多个条件
• where条件也可以运用在update和delete语句的后面
• where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

示例：

-- 查询stu表中 age > 22的数据
select * from stu where age>22;

-- 查询 stu 表中 name=某个条件值 的数据
select * from stu where name='yyx';

-- 查询 stu 表中 年龄在22到25之间的数据
select * from stu where age between 22 and 25;
select * from stu where age >= 22 and age<= 25;

-- 查询 stu 表中 年龄不在22到25之间的数据
select * from stu where age not between 22 and 25;

-- 查询 stu 表中 年龄在22到25之间的女生信息
select * from stu where age between 22 and 25 and sex='女';

and和or 使用时注意 (and 优先级大于 or)

假设要求 查询 users 表中 年龄为22或者25 的女生信息

select * from users where age=22 or age = 25 and sex = '女';

思考上面的语句能否返回符合条件的数据？

实际查询结果并不符合要求？

select * from users where age=22 or age = 25 and sex = '女';
+------+--------+------+-------+-------+------+------+
| id   | name   | age  | phone | email | sex  | mm   |
+------+--------+------+-------+-------+------+------+
|    1 | 章三   |   22 |       | NULL  | 男   |    0 |
| 1002 | cc     |   25 | 123   | NULL  | 女   | NULL |
+------+--------+------+-------+-------+------+------+
2 rows in set (0.00 sec)

-- 上面的查询结果并不符合 查询条件的要求。
-- 问题出在 sql 计算的顺序上，sql会优先处理and条件，所以上面的sql语句就变成了
-- 查询变成了为年龄22的不管性别，或者年龄为 25的女生

-- 如何改造sql符合我们的查询条件呢？
-- 使用小括号来关联相同的条件
select * from users where (age=22 or age = 25) and sex = '女';
+------+------+------+-------+-------+------+------+
| id   | name | age  | phone | email | sex  | mm   |
+------+------+------+-------+-------+------+------+
| 1002 | cc   |   25 | 123   | NULL  | 女   | NULL |
+------+------+------+-------+-------+------+------+
1 row in set (0.00 sec)

Like 子句

我们可以在where条件中使用=,<,> 等符合进行条件的过滤，但是当想查询某个字段是否包含时如何过滤？
可以使用like语句进行某个字段的模糊搜索，
例如： 查询 name字段中包含五的数据

-- like 语句  like某个确定的值 和。where name = '王五' 是一样
select * from stu where name like 'yyx';

-- 使用 % 模糊搜索。%代表任意个任意字符
    -- 查询name字段中包含x的
    select * from stu where name like '%x%';
    -- 查询name字段中最后一个字符 为 u的
    select * from stu where name like '%u';
    -- 查询name字段中第一个字符 为 q 的
    select * from stu where name like 'q%';
-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似
    -- 查询表中 name 字段为两个字符的数据
    select * from users where name like '__';

    -- 查询 name 字段最后为j，的两个字符的数据
    select * from users where name like '_j';

注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意：

• 尽可能的不去使用%或者_
• 如果需要使用，也尽可能不要把通配符放在开头处

Mysql中的统计函数（聚合函数）

可以使用like语句进行某个字段的模糊搜索，

例如： 查询 name字段中包含五的数据

count(),max(),min(),sum(),avg()

# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄
select max(age),min(age),sum(age),avg(age) from stu;
-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化
select max(age) as max_age,min(age) as min_age,
sum(age) as sum_age,avg(age) as avg_age from stu;
-- 统计 stu 表中的数据量
select count(*) from stu;
select count(id) from stu;
-- 上面的两个统计，分别使用了 count(*) 和 count(id),结果目前都一样，有什么区别？
-- count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算
-- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，
-- 但是注意，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计
-- 假设有下面这样的一张表需要统计
+----+----------+-----+------+---------+
| id | name     | age | sex  | classid |
+----+----------+-----+------+---------+
|  1 | zhangsan |  20 | 男   |       1 |
|  2 | yyx      |  22 | 男   |       1 |
|  3 | lj       |  20 | 男   |       1 |
|  4 | zhaoliu  |  25 | 女   |       1 |
|  5 | uu01     |  26 | 男   |       1 |
|  6 | uu02     |  28 | 男   |       2 |
|  7 | qq02     |  24 | 男   |       2 |
|  8 | uu03     |  32 | 女   |       2 |
|  9 | qq03     |  23 | 女   |       2 |
| 10 | aa       |  19 | 男   |       2 |
| 25 | bb       |  20 | NULL |       2 |
+----+----------+-----+------+---------+

-- 如果按照sex这一列进行统计，结果就是10个而不是11个，因为sex这一列中有NULL值存在
select count(sex) from stu;
+------------+
| count(sex) |
+------------+
|         10 |
+------------+

聚合函数除了以上简单的使用以外，通常情况下都是配合着分组进行数据的统计和计算

group by分组

group by 语句根据一个或多个列对结果集进行分组
一般情况下，是用与数据的统计或计算，配合聚合函数使用

-- 统计 stu 表中 男女生人数,
-- 很明显按照上面的需要，可以写出两个语句进行分别统计
select count(*) from stu where sex='女';
select count(*) from stu where sex='男';
-- 可以使用分组进行统计，更方便
select sex,count(*) from stu group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| NULL |        1 |
| 男   |        7 |
| 女   |        3 |
+------+----------+
-- 统计1班和2班的人数
select classid as '班级',count(*) from stu group by classid;
+--------+----------+
| 班级   | count(*) |
+--------+----------+
|      1 |        5 |
|      2 |        6 |
+--------+----------+
-- 分别统计每个班级的男女生人数
select classid,sex,count(*) from stu group by classid,sex;
+---------+------+----------+
| classid | sex  | count(*) |
+---------+------+----------+
|       1 | 男   |        4 |
|       1 | 女   |        1 |
|       2 | NULL |        1 |
|       2 | 男   |        3 |
|       2 | 女   |        2 |
+---------+------+----------+
# 注意，在使用。group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后面

having子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where，
where过滤的是行数据，having过滤的是分组数据

-- 要统计班级人数
select classid,count(*) from stu group by classid;

-- 统计班级人数，并且要人数达到5人及以上
select classid,count(*) as num from stu group by classid having num >=5;

orded by

ORDER BY 语句用于根据指定的列对结果集进行排序。ORDER BY 语句默认按照升序对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 ASC（升序）、DESC(降序) 关键字。

limit

limit子句用于限制查询结果返回的数量，常用于分页查询

# 查询10条数据，索引从0到9，第1条记录到第10条记录
select * from t_user limit 10;
select * from t_user limit 0,10;

# 查询8条数据，索引从5到12，第6条记录到第13条记录
select * from t_user limit 5,8;

课后练习题

-- 1. 统计班级 classid为2的男女生人数？
select sex,count(*) where classid=2 group by sex;
-- 2. 获取每个班级的 平均年龄，并按照平均年龄从大到小排序
select classid,avg(age) as avg_age from stu group by classid order by avg_age desc;
-- 3. 统计每个班级的人数，按照从大到小排序
select classid,count(*) as '人数' from stu group by classid order by count(*) desc;
-- 4. 获取班级人数最多的 班级id信息
select classid,count(*) as '人数' from stu group by classid order by count(*) desc limit 1;

总结：

mysql中的查询语句比较灵活多样，所以需要多加练习，
并且在使用查询语句时，一定要注意sql的正确性和顺序

| 子句 | 说明 | 是否必须 | | -------- | -------------------------------- | ------------------ | | select | 要返回的列或表达式，字段列表\| * | 是 | | from | 查询的数据表 | 需要在表中查询时 | | Where | 数据行的过滤 | 否 | | group by | 分组 | 仅在分组聚合计算时 | | having | 分组后的数据过滤 | 否 | | order by | 输出排序 | 否 | | limit | 要提取的结果行数 | 否 |