数据的增删改

增 insert

快速入门案例

mysql> create table goods(
    -> id int unsigned not null default 0,
    -> goods_name varchar(5) not null default '',
    -> price float not null default 0.0,
    -> mfg date not null comment '生产日期'
    -> )charset=utf8 engine=myisam;
Query OK, 0 rows affected (0.63 sec)

mysql> insert into goods values(001,'雪碧',3.50,'1982.01.01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----+------------+-------+------------+
| id | goods_name | price | mfg        |
+----+------------+-------+------------+
|  1 | 雪碧       |   3.5 | 1982-01-01 |
+----+------------+-------+------------+
1 row in set (0.00 sec)

添加语句的注意事项

1.插入的数据应与字段的数据类型相同

//id 字段类型为int  'abc'不能自动转换为int类型
mysql> insert into goods values('abc','可乐',3.50,'1982.01.01');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'id' at row 1
//id 字段类型为int  先将'002'自动转换为int类型，然后插入002  
mysql> insert into goods values('002','可乐',3.50,'1982.01.01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----+------------+-------+------------+
| id | goods_name | price | mfg        |
+----+------------+-------+------------+
|  1 | 雪碧       |   3.5 | 1982-01-01 |
|  2 | 可乐       |   3.5 | 1982-01-01 |
+----+------------+-------+------------+
2 rows in set (0.00 sec)

2.数据的大小应在列的规定范围内

//不能将一个字符长度为超过5的字符串加入到长度为5的列中
mysql> insert into goods values(003,'蒙牛牌纯牛奶',5.00,'1982.01.01');
ERROR 1406 (22001): Data too long for column 'goods_name' at row 1

3.在values中列出的数据位置必须与被加入的列的排列位置相对应

mysql> insert into goods values('芬达',003,3.00,'1982.01.01');
ERROR 1366 (HY000): Incorrect integer value: '芬达' for column 'id' at row 1

4.字符和日期型数据应包含在单引号中

mysql> insert into goods values(003,'芬达',3.00,1982.01.01);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.01)' at line 1

5.如果是给表中的所有字段添加数据，可以不写前面的字段名称

mysql> insert into goods values(003,'芬达',3.00,'1982.01.01');
Query OK, 1 row affected (0.00 sec)

6.如果你只给表的某几个字段赋值，则需要指定字段名, 而且要求没有给值的字段有默认值

//'mfg' 没有默认值  插入数据失败
mysql> insert into goods (id)values(004);
ERROR 1364 (HY000): Field 'mfg' doesn't have a default value

//插入数据时，所有字段都必须有数据，只给某几个字段赋值时，要求没有给值的字段有默认值
mysql> insert into goods (id,goods_name,mfg)values(004,'脉动','1982.01.01');
Query OK, 1 row affected (0.00 sec)

7.insert into 表名 values (),(),() 形式添加多条记录

mysql> insert into goods values(005,'奶茶',5.00,'1982.01.01'),(006,'绿茶',3.00,'1982.01.01');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

8.建议我们以后添加数据，都使用 ‘’ 引起来, 包括数值也可以

//数字类型会先进行自动数据类型转换  如果不清楚数据类型时 可以前部添加单引号
mysql> insert into goods values('007','红牛','5.00','1982.01.01');
Query OK, 1 row affected (0.00 sec)

mysql> select * from goods;
+----+------------+-------+------------+
| id | goods_name | price | mfg        |
+----+------------+-------+------------+
|  1 | 雪碧       |   3.5 | 1982-01-01 |
|  2 | 可乐       |   3.5 | 1982-01-01 |
|  3 | 芬达       |     3 | 1982-01-01 |
|  4 | 脉动       |     0 | 1982-01-01 |
|  5 | 奶茶       |     5 | 1982-01-01 |
|  6 | 绿茶       |     3 | 1982-01-01 |
|  7 | 红牛       |     5 | 1982-01-01 |
+----+------------+-------+------------+
7 rows in set (0.00 sec)

改 update

update的使用细节说明

• set子句指示要修改哪些列和要给予哪些值
• where子句指定应更新哪些行，如果没有where则更新所有行
• 如果需要修改多个字段，可以通过 set 字段1=值1，字段2=值2 …

快速入门案例

mysql> select * from users;
+----+-----------+------------+------------+------+--------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume                     | image |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 管家 |   4000 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   3800 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
2 rows in set (0.00 sec)

1.所有员工薪水修改为5000元

mysql> update users set salary=5000;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from users;
+----+-----------+------------+------------+------+--------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume                     | image |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 管家 |   5000 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   5000 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
2 rows in set (0.00 sec)

2.将赵信的薪水修改为3000元

mysql> update users set salary=3000 where user_name='赵信';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+-----------+------------+------------+------+--------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume                     | image |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 管家 |   3000 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   5000 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
2 rows in set (0.00 sec)

3.将赵信的薪水在原有基础上增加1000元，工作变为总管

mysql> update users set salary=salary+1000,job='总管' where user_name='赵信';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  select * from users;
+----+-----------+------------+------------+------+--------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume                     | image |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 总管 |   4000 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   5000 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
2 rows in set (0.00 sec)

删 delect

delete 的使用细节说明

• 如果不使用where子句，将删除表中所有数据
• delete语句不能删除某一列的某个值（可使用update设为null或者‘’）
• 使用delete语句仅删除记录，不删除表本身，如果删除表，使用 drop table 表名

参照完整性问题

同insert和update一样，从一个表中删除记录将引起其他表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个问题【外键】

测试前，先将users表复制一份

mysql> creste table users2 like users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creste table users2 like users' at line 1
mysql>
mysql> create table users2 like users;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from users2;
Empty set (0.00 sec)

mysql> insert into users2 select * from users;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users2;
+----+-----------+------------+------------+------+--------+----------------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume                     | image |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
|  1 | 赵信      | 2010-11-11 | 2018-12-12 | 总管 |   4000 | 一点寒芒先到，随后枪出如龙 |       |
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   5000 | 我随影而来，随影而去       |       |
+----+-----------+------------+------------+------+--------+----------------------------+-------+
2 rows in set (0.00 sec)

删除名称为赵信的记录 delete from 表名 where …

mysql> delete from users2 where user_name='赵信';
Query OK, 1 row affected (0.52 sec)

mysql> select * from users2;
+----+-----------+------------+------------+------+--------+----------------------+-------+
| id | user_name | birthday   | entry_date | job  | salary | resume               | image |
+----+-----------+------------+------------+------+--------+----------------------+-------+
|  2 | 阿卡丽    | 2013-03-03 | 2019-09-09 | 刺客 |   5000 | 我随影而来，随影而去 |       |
+----+-----------+------------+------------+------+--------+----------------------+-------+
1 row in set (0.00 sec)

删除表中所有数据 delete from 表名，表的结构还在
删除表中所有数据 truncate table 表名，表的结构还在

清空表的两种方式的区别

• 效果一样 truncate 速度更快
• delete 带有where条件，可以指定删除更灵活，truncate只能清空表
• delcte 可以返回被删除的记录数，而truncate 返回的是0，推荐用delete

//truncate 清空表  返回的是0
mysql> truncate table users2;
Query OK, 0 rows affected (0.57 sec)