MySQL数据库——基本查询（Create）
CRUD：Create（创建）Retrieve（读取）Update（更新）Delete（删除）
1.Create
①单行数据+全列插入
insert [into] table_name [(colume[,colume]……)] values (value_list) [,(value_list)]……
value_list:value,[,value]……mysql> create table stud(-> id int unsigned primary key auto_increment,-> sn int unsigned unique key,-> name varchar(20) not null,-> qq varchar(32) unique key-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc stud;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| sn | int(10) unsigned | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
| qq | varchar(32) | YES | UNI | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)-- id自增，可以忽略不写
mysql> insert into stud (sn,name,qq) values (123,'张飞','1234');
Query OK, 1 row affected (0.00 sec)mysql> select* from stud;
+----+------+--------+------+
| id | sn | name | qq |
+----+------+--------+------+
| 1 | 123 | 张飞 | 1234 |
+----+------+--------+------+
1 row in set (0.00 sec)-- 也可以不指定列进行全列插入
mysql> insert into stud values (10,124,'关羽','1236');
Query OK, 1 row affected (0.00 sec)mysql> select* from stud;
+----+------+--------+------+
| id | sn | name | qq |
+----+------+--------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 124 | 关羽 | 1236 |
+----+------+--------+------+
2 rows in set (0.00 sec)-- 也可以省略into
mysql> insert stud values (11,125,'刘备','1238');
Query OK, 1 row affected (0.01 sec)mysql> select* from stud;
+----+------+--------+------+
| id | sn | name | qq |
+----+------+--------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 124 | 关羽 | 1236 |
| 11 | 125 | 刘备 | 1238 |
+----+------+--------+------+
3 rows in set (0.00 sec)
②多行数据+指定列插入
-- 多行插入
mysql> insert stud values (12,126,'曹操','1231'),(13,127,'许攸','1233');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select* from stud;
+----+------+--------+------+
| id | sn | name | qq |
+----+------+--------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 124 | 关羽 | 1236 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
+----+------+--------+------+
5 rows in set (0.00 sec)-- 指定列进行多行插入
mysql> insert stud (sn,name,qq) values (128,'孙权','1235'),(129,'诸葛亮','1232');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select* from stud;
+----+------+-----------+------+
| id | sn | name | qq |
+----+------+-----------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 124 | 关羽 | 1236 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
| 14 | 128 | 孙权 | 1235 |
| 15 | 129 | 诸葛亮 | 1232 |
+----+------+-----------+------+
7 rows in set (0.00 sec)
③插入否则更新
如果由于主键或者唯一键对应的值已经存在而导致插入失败，可以选择进行同步更新操作，即将原数据不一样的值替换为自己要改的
mysql> insert [into] stud values (10,130,'lvbu','4321')
on duplicate key update sn=130,name='lvbu',qq='4321';-- 主键冲突
mysql> insert stud values (10,130,'lvbu','4321');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
-- 唯一键冲突
mysql> insert stud values (16,124,'lvbu','4321');
ERROR 1062 (23000): Duplicate entry '124' for key 'sn'
-- 更新
mysql> insert stud values (10,130,'lvbu','4321') on duplicate key update sn=130,name='lvbu',qq='4321';
Query OK, 2 rows affected (0.00 sec)mysql> select * from stud;
+----+------+-----------+------+
| id | sn | name | qq |
+----+------+-----------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 130 | lvbu | 4321 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
| 14 | 128 | 孙权 | 1235 |
| 15 | 129 | 诸葛亮 | 1232 |
+----+------+-----------+------+
7 rows in set (0.00 sec)
影响的数据行数
-- 此时表中没有数据，直接插入，qq=1111，影响一行数据
mysql> insert stud values (16,131,'貂蝉','1111') on duplicate key update sn=131,name='貂蝉',qq='1122';
Query OK, 1 row affected (0.00 sec)-- 此时表中已有id=16，数据冲突，所以更新，数据qq变为1122，影响两行数据
mysql> insert stud values (16,131,'貂蝉','1111') on duplicate key update sn=131,name='貂蝉',qq='1122';
Query OK, 2 rows affected (0.00 sec)-- 此时表中有冲突数据，但冲突数据的值和 update 的值相等，不影响数据
mysql> insert stud values (16,131,'貂蝉','1111') on duplicate key update sn=131,name='貂蝉',qq='1122';
Query OK, 0 rows affected (0.00 sec)mysql> select * from stud;
+----+------+-----------+------+
| id | sn | name | qq |
+----+------+-----------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 130 | lvbu | 4321 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
| 14 | 128 | 孙权 | 1235 |
| 15 | 129 | 诸葛亮 | 1232 |
| 16 | 131 | 貂蝉 | 1122 |
+----+------+-----------+------+
8 rows in set (0.00 sec)
④替换
把insert换成replace
-- 插入新数据
mysql> replace into stud (sn,name,qq) values (140,'许攸','4322');
Query OK, 1 row affected (0.00 sec)mysql> select * from stud;
+----+------+-----------+------+
| id | sn | name | qq |
+----+------+-----------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 130 | lvbu | 4321 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
| 14 | 128 | 孙权 | 1235 |
| 15 | 129 | 诸葛亮 | 1232 |
| 16 | 131 | 貂蝉 | 1122 |
| 17 | 140 | 许攸 | 4322 |
+----+------+-----------+------+
9 rows in set (0.00 sec)-- 插入不能重复的数据
mysql> replace into stud (sn,name,qq) values (140,'许攸1','4322');
Query OK, 2 rows affected (0.01 sec)-- id会继续增，这里其实相当于继续添加一行然后删除原来的数据
mysql> select * from stud;
+----+------+-----------+------+
| id | sn | name | qq |
+----+------+-----------+------+
| 1 | 123 | 张飞 | 1234 |
| 10 | 130 | lvbu | 4321 |
| 11 | 125 | 刘备 | 1238 |
| 12 | 126 | 曹操 | 1231 |
| 13 | 127 | 许攸 | 1233 |
| 14 | 128 | 孙权 | 1235 |
| 15 | 129 | 诸葛亮 | 1232 |
| 16 | 131 | 貂蝉 | 1122 |
| 18 | 140 | 许攸1 | 4322 |
+----+------+-----------+------+
9 rows in set (0.00 sec)-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入