MySQL学习笔记(四): 数据的增删改查

MySQL中数据的增删改查

1.添加数据

新增数据的语法结构是:

INSERT INTO table_name [(field1,field2...fieldn)] VALUES(value1,value2...valuen);

一次插入多条数据:

INSERT INTO table_name [(field1,field2...fieldn)] VALUES(value1,value2...valuen), (value1,value2...valuen),(value1,value2...valuen), ... ;

添加成功后的提示语:

Query OK, 1 rows affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

如果插入了主键重复的数据,将会得到:

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
# ‘1’这个条目已经有了副本,因为它是主键.

注意:

• 这里要注意列名要和表中的个列一一对应，顺序名称类型一个都不能错，后面的数据内容也是一样！
• 方括号中的内容不建议省略, 因为低版本的MySQL语法不允许省略此处
• 在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。

2.删除数据

删除数据的语法结构是:

DELETE FROM table_name WHERE CONDITION;

成功删除后的提示语:

Query OK, 1 row affected (0.07 sec)

注意：

• 删除语句一定要写删除条件，否则整张表中的数据都将被删除！！

如果确定想要删除表中的所有行, 不要使用delete, 可以使用 truncate table 语句,它的速度更快.因为truncate实际是删除原来的表并重新创建了一个表,而不是逐行去删除. 语法如下:

truncate table table_name;

3.更新数据

更新数据的语法结构是:

UPDATE table_name SET field1=value1,field2=value2,...fieldn=valuen WHERE CONDITION;

更新成功后的提示语:

Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

注意:

• 一定不要忘了条件语句，否则整张表数据将被修改成一样的！！

4.查询数据

完整的SQL查询语句:

select [select 选项] 字段列表 [字段别名]
FROM 数据源
[WHERE 条件子句]
[GROUP BY 子句]
[HAVING 子句]
[ORDER BY 子句]
[LIMIT 子句]

1.查询

select关键字用于输出查询结果, MySQL中使用’’(引号)来标识值 使用``(反引号)来标识字段或者表名称:

SELECT HELLO;
# ERROR 1054 (42S22): Unknown column 'HELLO' in 'field list'

SELECT 'HELLO';
+-------+
| HELLO |
+-------+
| HELLO |
+-------+
1 row in set (0.00 sec)

查询数据的语法结构是:

SELECT field1,field2, ... ,fieldn FROM table_name;

查询所有的数据:

SELECT * FROM table_name;

MySQL还提供了简单的四则运算符(加减乘除)

SELECT field1, field2, field2-field1 FROM table_name;

注意:

• 当 NULL 参与运算的时候，结果也为 NULL .

2.设置别名

AS 关键字可以给列名起别名:

SELECT <表名> [AS] <别名> FROM table_name;

注意:

1. as 是可选关键字
2. 其别名注意不要和其他列名冲突

3.去重(过滤重复数据)

可以使用 DISTINCT 关键字去掉重复数据

SELECT DISTINCT field FROM table_name;

4.条件查询语句

语法格式:

WHERE <查询条件> {<判定运算1>，<判定运算2>，…}

关系运算符:

算术运算符	描述	逻辑运算符	描述
>	大于	AND(&&)	逻辑与
<	小于	OR(||)	逻辑或
=	等于	XOR	逻辑异或
!=(<>)	不等于	NOT(!)	逻辑非
<=	小于等于
>	大于等于

AND : 可以在where后同时给出两个或以上的条件时使用

注意:

• SQL(像多数语言一样)在处理 OR 操作符前, 优先处理 AND 操作符, 换句话说, AND 的优先级比 OR 高.

select field1 from tabel_name where field2='value2' and field3>value3;

BETWEEN AND: 可以在需要查询取值范围区间时使用

SELECT field1 FROM table_name WHERE field2 [not] BETWEEN valuea AND valueb;

注意:

• 查询取值范围包含valuea, valueb 两个值

IS NULL: 可以用来查找字符为空的数据

SELECT field1 FROM table_name WHERE field2 IS [not] NULL;

IN : 可以用来匹配括号内任一值得查询条件:

SELECT field1 FROM table_name WHERE field2 [not] IN (value1,value2...valuen);

注意:

• IN 当中的内容之间是 或关系
• NOT IN 当中的内容之间是 且关系

LIKE : 模糊查询:

LIKE要和通配符一起用,常用的通配符有“_” 和 “%”

• “_” : 匹配的是 单个字符(只能1个)
• “%”: 匹配的是 任意个字符(0个,1个或多个)

LIKE关键字在实际开发中，常用于实现模糊查询功能，比如查询张姓同学，就可以这么写:

select * from student where stu_name like '张%';

又比如要查找名字中有小米关键字的产品，则可以这么写：

select name from table where name like '%小米%';

注意:

• LIKE 关键字主动放弃使用索引, 查询效率低, 应尽量避免使用
• MySQL 默认是不区分大小写的，若要区分大小写，则需要更换字符集的校对规则。
• 通配符不匹配 NULL 值。

5.排序语句

ORDER BY : 可以用来对查询到的数据进行排序, 具体语法如下:

SELECT field1,field2,... FROM table_name
WHERE CONDITION
ORDER BY fieldn [ASC|DESC];

注意:

• ASC: 升序,默认的, 可以不写
• DESC: 降序
• 如果比较数据中有 NULL 值, 则一般作为最小值
• 对于字符串的排序, 按照ASCII码进行排序
• 有时需要根据多列进行排序。对多列数据进行排序要将需要排序的列之间用逗号隔开。在对多列进行排序时，首行排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有的值都是唯一的，将不再对第二列进行排序。

6.限制查询结果的数量

LIMIT 关键字可以被用于强制 SELECT 语句返回指定的记录数:

基本的语法格式如下：

<LIMIT> [<位置偏移量>,] <行数>

第一个参数“位置偏移量”指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

select * from table limit 2;  	# 只输出前2行;
select * from table limit 0,5;	# 从第0行开始，输出5行；

注意:

• MySQL 5.7 中可以使用“LIMIT 4 OFFSET 3”，意思是获取从第5条记录开始的后面的3条记录，和“LIMIT 4，3”返回的结果相同。

7.聚合函数

5种常用统计函数

函数名	描述
COUNT()	统计记录数
AVG()	求平均值
SUM()	求和
MAX()	求最大值
MIN()	求最小值

注意:

• 聚合函数名(field) 自动实现了过滤 NULL 值

COUNT()函数可以通过以下两种方法来实现统计：

• count(*) : 实现对表中记录进行统计，不管是否包含NULL还是NOT NULL
• count(field) : 对指定字段进行统计，将忽略NULL值!
• 如果查询结果为空，count函数返回的是0，其它函聚合数返回null；

8.分组查询

GROUP BY : 实现分组效果

简单的分组查询语法如下:

SELECT * FROM tabel_name GROUP BY field;

我们将这个语法和统计函数一起用就可以达到分组查询统计的功能了，举个例：

select count(*) from table group by type;

这句语句实现了按照类型进行分组，分别查询出了每组商品数量！

注意:

• 实际工作中分组查询一般都是和统计函数一起出现的！

9. 分组过滤条件

HAVING: 对分组结果进行条件限制查询, 不能使用WHERE关键字

例如:

select count(*) from table where sal>50 group by type;
select avg(c_inprice) ,c_type from commodity group by c_type having avg(c_inprice) > 1;
# 这两句语句的区别是将售价大于50的产品查询出来后进行分组，和将查询出来的所有结果进行分组排序后显示售价大于50的数据；

HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：

• WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING 子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
• WHERE 子句不可以包含聚合函数，HAVING 子句中的条件可以包含聚合函数。
• HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。WHERE 子句排除的行不包含在分组中，可能会影响 HAVING 子句基于这些值过滤掉的分组。

注意:

• MySQL5.7之前, having 中的结果集必须出现在select关键字后;

   select c_type, avg(c_inprice)
   from commodity
   group by c_type
   having avg(c_inprice) > 100;
  

  5.7之后开始支持以下写法:

  select c_type
  from commodity
  group by c_type
  having avg(c_inprice) > 100;
  

• 聚合函数返回的表数据并不存在于数据库中, 先经过了select 和where关键字从数据库中读取数据后再使用聚合函数在内存中计算结果, 因此聚合函数返回的表数据存在于内存中, 且where关键字优先级大于having关键字