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: 表中有冲突数据，删除后重新插入