insert、replace语句及相关习题

insert语句

一、插入的数据形式：
1.显式插入(字面量形式插入，直接给出值)

mysql> desc t5;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| sys_date | datetime | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t5 values('2019-03-23 9:10:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+
| sys_date            |
+---------------------+
| 2019-03-23 09:10:00 |
+---------------------+
1 row in set (0.00 sec)

2.隐式插入(可以用表达式、函数等)

mysql> insert into t5 values(now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+---------------------+
| sys_date            |
+---------------------+
| 2019-03-23 09:10:00 |
| 2019-03-14 13:53:14 |
+---------------------+
2 rows in set (0.00 sec)

二、Syntax:：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]-------不显示错误信息(为了排错，定位问题点，不建议使用)
    [INTO] tbl_name-------------------------------为了和其他数据库形成统一的SQL书写习惯，into必须写
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...(可以一次输入多行数据)--------------为了和其他数据库形成统一的SQL书写习惯，写values
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

关于语法详解

一、 insert语句的不同插入形式:
1.表名后面跟所有列名，所有列名用小括号括起来，列名与列名之间用逗号分隔
注意事项：对某一列不想赋值时，可以使用NULL进行占位(放置因为插入数据个数跟表中列数不匹配报错)

2.表名后面不跟列名(整行插入，每一列都要进行赋值)
注意事项：该形式插入数据时，要了解表的列数及列的数据类型

3.在表名后面只列出部分列名
注意事项：假如说插入的列是某一些列，其他的列值会自动为NULL值

二、使用字面量null给列赋予空值(注意null不用加单引号，如果加—是字符串)

mysql> inser into test values(1,'Xiao Ming',null);

三、.一条insert into语句可以插入多个行，通常去插入一行
注意：插入多行这种语法只要一行出错，则插入全部取消
1.正确形式

mysql> insert into test values(2,'Xiao Hong',21),(2,'Zhang Na','22'),(3,'Li Xin','23');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

·
2.错误形式，并且插入全部取消，表中数据为空

mysql>CREATE TABLE t1(id INT,NAME VARCHAR(10));
mysql> INSERT INTO t1 VALUES(1),(2,'NANA');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> select * from t1;
Empty set (0.00 sec)

四、insert可以加子查询(子查询返回的必须是一行一列一个值，并且放在单独的小括号里面)
values的数据来源：
1.字面量
2.函数
3.子查询(必须使用小括号括起来)
注意事项：insert语句的子查询可以是表自身

mysql> insert into t1 values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into t1 select * from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    1 | a    |
|    2 | b    |
+------+------+
4 rows in set (0.00 sec)

五、.当在主键列插入相同的值时，会报错(Duplicate entry ’ ’ for key ‘PRIMARY’)
解决方法：insert ignore into values();(此时会插入重复值，但是会有警告)

六、.insert into…select（带子查询的insert语句，要求两张表列的结构一样，insert语句的子查询可以是表自身）

三、例题

例4： 创建一个表，存储球员数量和罚款总额

mysql> create table total(count int,money int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into total values((select count(1) from PLAYERS),(select sum(AMOUNT)from PENALTIES));
Query OK, 1 row affected (0.00 sec)

mysql> select * from total;
+-------+-------+
| count | money |
+-------+-------+
|    14 |   480 |
+-------+-------+
1 row in set (0.00 sec)

例8： 把那些罚款额大于平均罚款额的所有罚款添加到penalties表中

1.先将要插入的数据提取出来

mysql> select * from PENALTIES where AMOUNT > (select avg(AMOUNT) FROM PENALTIES);
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|         1 |        6 | 1980-12-08   | 100.00 |
|         2 |       44 | 1981-05-05   |  75.00 |
|         3 |       27 | 1983-09-10   | 100.00 |
|         8 |       27 | 1984-11-12   |  75.00 

2.再向表中插入数据

mysql> insert into PENALTIES select PAYMENTNO+100,PLAYERNO,PAYMENT_DATE,AMOUNT from PENALTIES where AMOUNT > (select avg(AMOUNT) FROM PENALTIES);             (注意:为什么将PAYMENTNO的值加100呢？ 因为在PENALTIES表中，此列是主键列，它的值不允许重复，不允许有空值)
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from PENALTIES;
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|         1 |        6 | 1980-12-08   | 100.00 |
|         2 |       44 | 1981-05-05   |  75.00 |
|         3 |       27 | 1983-09-10   | 100.00 |
|         4 |      104 | 1984-12-08   |  50.00 |
|         5 |       44 | 1980-12-08   |  25.00 |
|         6 |        8 | 1980-12-08   |  25.00 |
|         7 |       44 | 1982-12-30   |  30.00 |
|         8 |       27 | 1984-11-12   |  75.00 |
|       101 |        6 | 1980-12-08   | 100.00 |
|       102 |       44 | 1981-05-05   |  75.00 |
|       103 |       27 | 1983-09-10   | 100.00 |
|       108 |       27 | 1984-11-12   |  75.00 |

replace语句
替代已有的行，insert的变种语句。使用replace语句时，表上一定要有主键
两种情况：
1.要插入的主键值存在，则覆盖
2.要插入的主键值不存在，则插入

一、Syntax:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

二、举例：
1.创建表，需要有主键

mysql> create table rep(id int primary key,name varchar(1));
Query OK, 0 rows affected (0.02 sec)

mysql> desc rep;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into rep values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

2.向rep表插入数据

先向表中插入主键值存在的行

mysql> replace into rep values(1,'c');
Query OK, 2 rows affected (0.00 sec)

mysql> select * from rep;
+----+------+
| id | name |
+----+------+
|  1 | c    |
|  2 | b    |
+----+------+
2 rows in set (0.00 sec)

向表中插入主键不存在的行

mysql> replace into rep values(3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> select * from rep;
+----+------+
| id | name |
+----+------+
|  1 | c    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)