目录
INSERT语句-插入
1.格式
2.操作
UPDATE语句-修改
1.格式
2.操作
DELETE语句-删除
1.格式
2.操作
INSERT语句-插入
1.格式
格式: insert into 表名 values (value1,value2,.....)
1. value后的内容：与表字段匹配的数据，如果字段为主键，则可以使用null,让其自增
2. 主键使用insert时是唯一的，不能重复插入
3. 字符串可以int转化，eg: "111" = 111
4. 自增长策略中，如果手动设置值，则下一个新增数据按最大索引值为准
2.操作
1.创建库
mysql> create database dml_lls;
Query OK, 1 row affected (0.00 sec)2.使用库
mysql> use dml_lls;
Database changed3.创建表
mysql> create table user(
-> `user_id` int(10) primary key auto_increment,
-> `username` varchar(255),
-> `password` varchar(255));
Query OK, 0 rows affected (0.04 sec)4.检查表
mysql> desc user;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| user_id | int(10) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)5.插入数据
mysql> insert into user values(null,"zhangsan","111");
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
+---------+----------+----------+
1 row in set (0.00 sec)插入不同数量的数据--不可以
mysql> insert into user values(null,"zhangsan","111","111");
ERROR 1136 (21S01): Column count doesn’t match value count at row 1插入不同类型的数据--结果可以
mysql> insert into user values(null,"zhangsan",111);
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
| 2 | zhangsan | 111 |
+---------+----------+----------+
2 rows in set (0.00 sec)继续插入不同类型的而数据-- 结果可以
mysql> insert into user values("111","zhangsan",111);
Query OK, 1 row affected (0.00 sec) -- 字符串和整数可以转换mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
| 2 | zhangsan | 111 |
| 111 | zhangsan | 111 |
+---------+----------+----------+
3 rows in set (0.00 sec)插入不同类型数据---出错
mysql> insert into user values("111q","zhangsan",111);
ERROR 1265 (01000): Data truncated for column 'user_id' at row 1mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
| 2 | zhangsan | 111 |
| 111 | zhangsan | 111 |
+---------+----------+----------+
3 rows in set (0.00 sec)6.主键自增是按照索引最大值自增
mysql> insert into user values(null,"zhangsan",111);
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
| 2 | zhangsan | 111 |
| 111 | zhangsan | 111 |
| 112 | zhangsan | 111 |
+---------+----------+----------+
4 rows in set (0.00 sec)7.主键是唯一的
mysql> insert into user values(1,"zhangsan","222");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into user values(78,"zhangsan","222");
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | zhangsan | 111 |
| 2 | zhangsan | 111 |
| 78 | zhangsan | 222 |
| 111 | zhangsan | 111 |
| 112 | zhangsan | 111 |
+---------+----------+----------+
5 rows in set (0.00 sec)
UPDATE语句-修改
1.格式
格式:
update 表名 set 字段1=值1，字段n=值n [where 筛选]
2.操作
1.修改索引为1的用户名及密码
mysql> update user set username="lijian",password="123456" where user_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user where user_id = 1;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | lijian | 123456 |
+---------+----------+----------+
1 row in set (0.00 sec)2.修改所有密码为111的为666
mysql> update user set password="666" where password="111";
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | lijian | 123456 |
| 2 | zhangsan | 666 |
| 78 | zhangsan | 222 |
| 111 | zhangsan | 666 |
| 112 | zhangsan | 666 |
| 113 | lijian | 222 |
| 114 | zhangsan | NULL |
| 115 | tutu | 666 |
| 116 | NULL | NULL |
| 117 | zhang3 | 123 |
| 118 | zhang4 | 123 |
| 119 | zhang5 | 123 |
| 1000 | wangwu | 123456 |
| 1001 | li5 | NULL |
| 1036 | zhang5 | NULL |
+---------+----------+----------+
48 rows in set (0.00 sec)
DELETE语句-删除
1.格式
delete:
格式: delete from 表名 [where 条件]
删除数据后，保留自增的最大索引值并放在缓存，如果重新增加数据，则按照缓存的最大索引自增❓删除表数据有几种方式？它们的区别是什么？
1. delete from user; 删除表数据，保留表结构，可以回滚，效率慢
2. truncate table user; 删除表数据，保留表结构，不能回滚，一次性删除所有数据，效率高，
并初始化索引
3. drop table user; 删除表，什么都没了。
2.操作
1 .删除单条数据
mysql> delete from user where user_id = 1;
Query OK, 1 row affected (0.02 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 2 | zhangsan | 666 |
| 78 | zhangsan | 222 |
| 111 | zhangsan | 666 |
| 112 | zhangsan | 666 |
| 113 | lijian | 222 |
| 1034 | zhang3 | NULL |
| 1035 | zhang4 | NULL |
| 1036 | zhang5 | NULL |
+---------+----------+----------+
47 rows in set (0.00 sec)2.删除密码为null的多条数据
mysql> delete from user where password is null;
Query OK, 37 rows affected (0.02 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 2 | zhangsan | 666 |
| 78 | zhangsan | 222 |
| 111 | zhangsan | 666 |
| 112 | zhangsan | 666 |
| 113 | lijian | 222 |
| 115 | tutu | 666 |
| 117 | zhang3 | 123 |
| 118 | zhang4 | 123 |
| 119 | zhang5 | 123 |
| 1000 | wangwu | 123456 |
+---------+----------+----------+
10 rows in set (0.00 sec)3.删除表数据
mysql> delete from user;
Query OK, 10 rows affected (0.02 sec)mysql> select * from user;
Empty set (0.00 sec)4.插入数据时按照最大索引缓存自增
mysql> insert into user values(null,"lijian","111");
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1050 | lijian | 111 |
+---------+----------+----------+
1 row in set (0.00 sec)5.删除表数据，并初始化主键索引
mysql> truncate table user;
Query OK, 0 rows affected (0.03 sec)mysql> select * from user;
Empty set (0.00 sec)6.插入数据，索引从1开始
mysql> insert into user values(null,"lijian","1111");
Query OK, 1 row affected (0.01 sec)mysql> select * from user;
+---------+----------+----------+
| user_id | username | password |
+---------+----------+----------+
| 1 | lijian | 1111 |
+---------+----------+----------+
1 row in set (0.00 sec)7.删除表及表结构和数据
mysql> drop table user;
Query OK, 0 rows affected (0.03 sec)mysql> show tables;
Empty set (0.00 sec)
