MySQL（五）

---

表的增删改查

---

CRUD:Create, Retrieve, Update, Delete, 

即就是表的增删改查。
1. 增加
直接看例子。创建一张商品表，如下：

mysql> create table goods(
    -> id int unsigned primary key,
    -> goods_name varchar(32) not null,
    -> price float not null default 0.0);

插入两条记录：

mysql> insert into goods values(100, '牛排', 78.5);

mysql> insert into goods values(101, '披萨', 27.5);

mysql> select * from goods;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
| 100 | 牛排       |  78.5 |
| 101 | 披萨       |  27.5 |
+-----+------------+-------+

插入成功。但是在使用时应该注意以下：
（1）插入的数据应与字段的数据类型相同，否则会出错。如：给 int id 列插入 char 类型的 ‘abc’, 结果出错：

mysql> insert into goods values('abc', 'pizza', 78);
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'id' at row 1

（2）数据的大小应在规定的范围内，否则会出错；
（3）在 values 中列出的数据位置必须与被加入的列位置相对应；
（4）字符和日期类型应该包含在单引号中；
（5）插入空值，不指定或者指定为 null；
（6）批量插入，insert into table values (),(),()如：一次性插入三条记录

mysql> insert into goods values (1,'a',12),(2,'b',20),(3,'c',30);

mysql> select * from goods;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
|   1 | a          |    12 |
|   2 | b          |    20 |
|   3 | c          |    30 |
| 100 | 牛排       |  78.5 |
| 101 | 披萨       |  27.5 |
+-----+------------+-------+

（7）隐含列插入，给表中的所有字段添加数据，可以不写前面的字段名称；
（8）指定列插入，只给表中的某几个字段赋值，则需要指定字段名；如：

mysql> insert into goods values(4,'d',40.0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into goods(id, goods_name) values (5,'e');
Query OK, 1 row affected (0.01 sec)

第一条记录给了所有字段的信息和数据，所以前面没有写字段的名称；而第二条记录因为只给指定的两个字段赋值，所以前面指定了字段名称。
（9）在数据插入的时候，假设主键已经存在，则插入失败！当主键发生冲突时，可以选择性地进行处理，如：

mysql> insert into goods values(101,'ccc',20.5);
ERROR 1062 (23000): Duplicate entry '101' for key 'PRIMARY'

• 更新操作

insert into 表名（字段列表）values (值列表) on duplicate key update 字段 = 新值;

mysql> insert into goods values(101,'ccc',20.5) on duplicate key update goods_name = 'ccc',price = 20.5;
Query OK, 2 rows affected (0.01 sec)

• 替换

replace into 表名（包含字段）values (值列表);

mysql> replace into goods values(100,'oppo',2999);
Query OK, 2 rows affected (0.00 sec)

2. 更新
以上述为例。将所有产品的价格修改为300，如下：

mysql> update goods set price = 300;
Query OK, 7 rows affected (0.00 sec)

mysql> select * from goods;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
|   1 | a          |   300 |
|   2 | b          |   300 |
|   3 | c          |   300 |
|   4 | d          |   300 |
|   5 | e          |   300 |
| 100 | oppo       |   300 |
| 101 | ccc        |   300 |
+-----+------------+-------+

将 id = 100 的产品价格修改为1000

mysql> update goods set price = 1000 where id = 100;
Query OK, 1 row affected (0.01 sec)

mysql> select * from goods where id = 100;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
| 100 | oppo       |  1000 |
+-----+------------+-------+

将 id = 100 的产品介个增加200

mysql> update goods set price = price + 200 where id = 100;
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods where id = 100;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
| 100 | oppo       |  1200 |
+-----+------------+-------+

注意：
（1）update 语法可以用新值更新原有表中的各列值；
（2）set 子句指示要修改哪些列和要给哪些值；
（3）where 子句指定应更新哪些行，若没有该子句，则更新所有行.
3. 删除

delete from tb_1name [where condition]

以上述为例。删除表中 id = 100 的数据

mysql> delete from goods where id = 100;
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+-----+------------+-------+
| id  | goods_name | price |
+-----+------------+-------+
|   1 | a          |   300 |
|   2 | b          |   300 |
|   3 | c          |   300 |
|   4 | d          |   300 |
|   5 | e          |   300 |
| 101 | ccc        |   300 |
+-----+------------+-------+

删除成功！
复制表数据
（1）复制表结构

mysql> create table goods2 like goods;

（2）复制数据

mysql> insert into goods2 select * from goods;
Query OK, 6 rows affected (0.00 sec)

删除表中所有记录

mysql> delete from goods;
Query OK, 6 rows affected (0.00 sec)

删除整个表的数据，但是表的结构还在。
使用 truncate 清空表中数据

mysql> truncate table goods;
Query OK, 0 rows affected (0.03 sec)

delete 和 truncate 两种删除表的区别
（1）效果一样，但是后者速度更快；
（2）delete 返回被删除的记录数，而 truncate 返回0；
（3）清空表数据，建议用后者。
delete 使用的细节
（1）使用时配合 where 子句，可以灵活地删除满足条件的记录；
（2）不能删除某一列的值（可以用 update 置 null）；
（3）使用时仅能删除记录，不能删除表本身（drop table）.
4. 查询
（1）* 表示查询所有列，但是其效率很低，一般我们需要哪些字段就选取哪些字段就可以；
（2）distinct 用于删除表中完全相同的行，即去除重复行；
（3）在 select 语句中，使用 as 起别名：select column as 别名 from 表;
（4）在 select 语句中， 使用 where 进行查询过滤；
（5）在 select 语句中， 使用 order by 排序查询结果；

-order by 指定排序的列，位于 select 的末尾，asc 升序（默认），desc 降序；
（6）limit 分页

select 字段 from 表名 where 条件 limit 起始位置， 记录条数
select 字段 from 表名 where 条件 limit 记录条数 offset 起始位置

5. 聚合函数
（1）count (列名) 返回某一列，行的总数，如：统计一个班的学生数

mysql> select count(*) from student;

（2）sum 函数返回满足 where 条件的行的总和，如：统计一个班数学总成绩

mysql> select sum(math) from student;

（3）avg 函数返回满足 where 条件的一列的平均值，如：求一个班数学均分

mysql> select avg(math) from student;

（4）max/min 函数返回满足 where 条件的一列最大/最小值，如：求班级的最高最低分：

mysql> select max(chinese+math),min(chinese+math) from student;

6. group by 子句的使用
在 select 中使用 group by 子句可以对指定列进行分组查询，一般来讲，having 和 group by 配合使用，对 group by 结果进行过滤。