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)