MySQL的DML操作表记录

一、DML操作表记录——增删改查

1.1 插入记录

• 方式一：插入指定列，如果没有把这个列列出来，以null进行自动赋值了。
• 方式二：插入所有的列，如果哪列不想插入值，则需要赋值为null。

-- 1. 往商品表中插入数据
-- 1.1 第一种形式：指定插入哪些列的数据，没有指定的列那个就插入null
-- 更加直观
INSERT INTO product (pname,price,num) VALUES('华为5G手机',6980.0,1500);


-- 1.2 第二种形式：不指定插入哪些列的数据，也就是说要对所有列都插入数据，就算某一列不想插入数据，需要赋值为null
INSERT INTO product VALUES (NULL,'苹果手机',8999.0,2000);

1.2 更新记录

-- 2. 更新数据
-- update 表名 set 字段名=新值，字段名=新值 where 条件
-- 如果没有修改条件的话，那么会将整张表的该字段的值都修改掉

-- 2.1 将所有商品的价格修改为5000元
UPDATE product SET price = 5000;

-- 2.2 将商品名是苹果手机的价格修改为18000元
UPDATE product SET price=18000 WHERE pname='苹果手机';

-- 2.3 将商品名为苹果手机的价格修改为17000，数量修改为5
UPDATE product SET price=17000,num=5 WHERE pname = '苹果手机';

-- 2.4 将商品名为华为手机的价格在原有基础上增加2元
UPDATE product SET price = price+2 WHERE pname='华为手机';

1.3 删除记录

1.3.1 delete

根据条件，一条一条数据进行删除

-- 3. 删除数据
-- 3.1 delete根据条件进行删除
-- delete from 表名 where 条件
-- 删除表中名称为'苹果手机'的记录
DELETE FROM product WHERE pname='苹果手机';
-- 删除价格小于6000的商品记录
DELETE FROM product WHERE price<6000;

-- 删除所有数据，就是不加条件
DELETE FROM product;

1.3.2 truncate

把表直接DROP掉，然后再创建一个同样的新表。删除的数据不能找回。执行速度比DELETE快。

-- 3.2 要删除所有数据，不太建议使用delete语句进行删除，因为delete语句是根据条件一行一行删除
-- truncate进行删除，将整张表整体干掉，然后自动创建一张结构一模一样的新表
TRUNCATE TABLE product;

1.3.3 实际工作中删除数据

进行逻辑删除，并没有真正的删除数据，数据还在。用一个标记进行标识。

1.4 查询记录

1.4.1 简单查询

-- 1. 查询所有行和所有列的数据
-- select 要查询哪些列 from 表名 where 条件
SELECT * FROM product;

-- 2. 查询指定列的数据,select 和 from之间写的就是要查询的列名，列名之间使用逗号分隔
SELECT pname,price,num FROM product;

-- 3. 去重查询：在查询结果中去掉重复的数据,使用关键字distinct
-- select distinct 要查询的字段 from 表名 where 条件
SELECT DISTINCT pname FROM product;

-- 4. 别名查询：给查询的字段或者表进行别名设置
-- 给字段取别名的时候，最好使用'别名'
-- 语法：列名 as 别名，as可以省略
SELECT pid,pname AS '商品名',price AS ' 商品价格',num AS '商品库存' FROM product;

-- 5. 运算查询
SELECT pid,pname,price+10 'price',num FROM product;

1.4.2 条件查询

1.4.2.1 运算符

1、比较运算符

• 大于：>
• 小于：<
• 大于等于：>=
• 小于等于：<=
• 等于：= 不能用于null判断
• 不等于：!= 或 <>
• 安全等于：<=> 可以用于null值判断

2、逻辑运算符

• 逻辑与：&& 或 and
• 逻辑或：|| 或 or
• 逻辑非：! 或 not
• 逻辑异或：^ 或 xor

3、范围

• 区间范围：between x and y; not between x and y;
• 集合范围：in(x,x,x); not in(x,x,x)

4、模糊查询和正则匹配

• like ‘xxx’ 模糊查询是处理字符串的时候进行部分匹配。如果想要表示0~n个字符，用%；如果想要表示确定的1个字符，用_
• regexp ‘正则’

5、特殊的null值处理

• xx is null
• xx is not null
• xx <=> null

1.4.2.1 语法

-- 6. 条件查询
-- 6.1 查询商品价格>3000的商品
SELECT * FROM product WHERE price > 3000;

-- 6.2 查询pid=1的商品
SELECT * FROM product WHERE pid=1;

-- 6.3 查询id<>1的商品(不等于1)
SELECT * FROM product WHERE pid<>1;

-- 6.4 查询价格在3000到6000之间的商品(包含3000和6000)
SELECT * FROM product WHERE price BETWEEN 3000 AND 6000;

-- 6.5 查询pid在1,5,7,15范围内的商品
SELECT * FROM product WHERE pid IN (1,5,7,15);

-- 6.6 查询商品名以ipho开头的商品（iPhone系列）  like模糊查询
SELECT * FROM product WHERE pname LIKE 'iPho%';

-- 6.7 查询商品价格大于3000并且数量大于20的商品（条件 and 条件 and ...）
SELECT * FROM product WHERE price >3000 AND num > 20;

-- 6.8 查询id=1或者价格小于3000的商品
SELECT * FROM product WHERE pid = 1 OR price <3000;

1.4.3 排序查询

排序是写在查询的后面，代表把数据查询出来之后再排序。

-- 7.1 单列排序，按照某一列进行排序
-- 升序 asc（默认），降序 desc
-- 以分数降序查询所有的学生
SELECT * FROM student ORDER BY score DESC;

-- 7.2 多列排序
-- 先按照第一个字段进行排序，如果第一个字段相同，那么就按照第二个字段进行排序
-- 以分数降序查询所有的学生，如果分数一致，再以age降序
SELECT * FROM student ORDER BY score DESC,age DESC;

1.4.4 聚合函数

聚合函数通常会和分组查询一起使用，用于统计每组的数据。

1.4.4.1 聚合函数列表

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

1.4.4.2 语法

-- 8. 聚合函数：用于统计的，通常和分组查询一起使用，用于统计每组的数据
-- 8.1 max函数，查询最大值
-- 求出学生表里面的最高分数
SELECT MAX(score) FROM student;

-- 8.2 min函数，查询最小值
-- 求出学生表里面的最低分数
SELECT MIN(score) FROM student;

-- 8.3 avg函数，查询平均值
-- 求出学生表里面的平均分  如果该字段为NULL，则不会将其加入到计算平均值
SELECT AVG(score) FROM student;
-- 如果缺考了就当成0分处理
SELECT AVG(IFNULL(score,0)) FROM student;

-- 8.4 count函数，统计数据条数
-- 统计学生总数
SELECT COUNT(sid) FROM student;

-- 8.5 sum函数，求和
-- 求出学生表里面的分数的总和
SELECT SUM(score) FROM student;

1.4.5 分组查询

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组跟聚合函数一起使用。

1.4.5.1 语法

-- 9. 分组查询：分组的目的是为了做统计，所以要结合聚合函数一起使用
-- 语法：group by 用于分组的字段
-- 根据性别分组，统计每一组学生的总人数
SELECT sex AS '性别',COUNT(sid) AS '总人数' FROM student GROUP BY sex;

-- 根据性别分组，统计每一组学生的平均分
SELECT sex AS '性别',AVG(score) AS '平均分' FROM student GROUP BY sex;

-- 根据性别分组，统计每组学生的总分
SELECT sex AS '性别',SUM(score) AS '总分' FROM student GROUP BY sex;

1.4.5.2 分组后筛选用having

-- 10. 分组后的筛选(having关键字)
-- 分组后的条件，不能写在where之后，where关键字要写在group by之前
-- 练习根据性别分组，统计每一组学生的总人数>5的
SELECT sex AS '性别',COUNT(sid) AS '总人数' FROM student GROUP BY sex HAVING COUNT(sid)>5;

-- 练习根据性别分组，只统计年龄大于等于18的，并且要求组里的人数大于4
SELECT sex AS '性别',COUNT(sid) AS '总人数' FROM student WHERE age>=18 GROUP BY sex HAVING COUNT(sid)>4;

1.4.5.3 where和having的区别

子名	作用
where子句	1）对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2）where后面不可以使用聚合函数。
having子句	1）having子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2）having后面可以使用聚合函数。

1.4.6 分页查询

-- 分页查询
-- limit 关键字是使用在查询的后面，如果有排序则使用在排序后面
-- limit的语法：limit  offset，length  其中offset表示跳过多少条数据，length表示查询多少条数据
SELECT * FROM product LIMIT 0,3;
-- 表示查询product表中的前三条数据（0表示跳过0条，3表示查询3条）

SELECT * FROM product LIMIT 3,3;
-- 表示查询product表中的第4到第6条数据（3表示跳过3条，3表示查询3条）
-- 分页的时候，只会告诉你我需要第几页的数据，并且每页有多少条数据

-- length=每页的数据条数，offset=（当前页数-1）*每页数据条数
-- limit（当前页数-1）*每页数据条数，每页数据条数

1.4.7 查询语法小结

select...from...where...group by...order by...limit
select...from...where...
select...from...where...order by...
select...from...where...limit...
select...from...where...order by...limit

学海无涯苦作舟