mysql笔记 - DML语句
INSERT语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
最基本的写法
insert into a values (4);
insert into a values (NULL);
还可以这样写，而且这样的写法比较高效
insert into a values (5),(6),(7),(8);
清除一下数据，加个唯一键
truncate table a;
alter table a add primary key (x);
insert into a values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
select * from a;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
有一个比较特殊的语法ON DUPLICATE KEY UPDATE，插入的时候如果遇到重复的值就把它更新
insert into a values (1) on duplicate key update x = 4;
Query OK, 2 rows affected (0.03 sec)
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+
insert into a values (2) on duplicate key update x = 5;
+---+
| x |
+---+
| 3 |
| 4 |
| 5 |
+---+
上面insert into 1,2,3的时候看到有3条记录收到影响，而当使用ON DUPLICATE KEY的时候显示2条记录受到影响，应该是先删除老的记录再增加update的值
alter table a add column b int not NULL; -- 增加一列
update a set b = x;
select * from a;
+---+---+
| x | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+---+---+
insert into a values(6,6),(7,7);
select * from a;
+---+---+
| x | b |
+---+---+
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+---+---+
insert into a value (3,7) on duplicate key update x = 8;
select * from a;
+---+---+
| x | b |
+---+---+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 3 | -- 上面的33被替换成了83，7并没有产生作用
+---+---+
REPLACE INTO
replace into a values(8,7);
Query OK, 2 rows affected (0.06 sec)
select * from a;
+---+---+
| x | b |
+---+---+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 7 | -- 变成了87
+---+---+
ON DUPLICATE KEY需要达到REPLACE INTO的效果需要这么写
insert into a value(8,9) on duplicate key update x = 8, b = 9; -- 需要更新的列都要写上
select * from a;
+---+---+
| x | b |
+---+---+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 9 |
+---+---+
INSERT INTO SELECT
insert into a select 9,9;
select 9,9; -- 其实就是一个结果集，而上面就是将结果集插入到a表中
+---+---+
| 9 | 9 |
+---+---+
| 9 | 9 |
+---+---+
select * from a;
+---+---+
| x | b |
+---+---+
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 9 |
| 9 | 9 |
+---+---+
desc dbt3.nation;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| n_nationkey | int(11) | NO | PRI | NULL | |
| n_name | char(25) | YES | | NULL | |
| n_regionkey | int(11) | YES | MUL | NULL | |
| n_comment | varchar(152) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
create table n like dbt3.nation;
show create table n\G
*************************** 1. row ***************************
Table: n
Create Table: CREATE TABLE `n` (
`n_nationkey` int(11) NOT NULL,
`n_name` char(25) DEFAULT NULL,
`n_regionkey` int(11) DEFAULT NULL,
`n_comment` varchar(152) DEFAULT NULL,
PRIMARY KEY (`n_nationkey`),
KEY `i_n_regionkey` (`n_regionkey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
alter table n drop column n_comment; -- 删除一列
那么原来的dbt3.nation表有4个列，那么现在要把dbt3.nation(4列)表中的数据批量插入到n(3列)表中，可以这样写
insert into n select n_nationkey,n_name,n_regionkey from dbt3.nation;
有些时候需要造数据
alter table n add column n_comment varchar(255);
alter table n add column n_test varchar(255);
truncate table n;
insert into n select n_nationkey,n_name,n_regionkey,n_comment, 'aaa' from dbt3.nation; -- 其中的'aaa'是自己造的数据，也可以用now()，random()代替，这样在导入数据的时候可以自己额外导入一些原表没有的数据
INSERT INTO SET
效果是这样的，一般不怎么用
insert into a set x = 10, b = 10;
select * from a;
+----+----+
| x | b |
+----+----+
| 10 | 10 |
+----+----+
DELETE 语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
[LOW_PRIORITY] [QUICK] [IGNORE] 参数适用于Myisam引擎，一般也不怎么用到这个引擎了
delete 不加 order by 就会随机删除limit多少条数据，建议加上order by
select * from a;
+----+----+
| x | b |
+----+----+
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 9 |
| 9 | 9 |
| 10 | 10 |
+----+----+
delete from a where x = 10;
select * from a;
+---+---+
| x | b |
+---+---+
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 9 |
| 9 | 9 |
+---+---+
delete from a limit 1; -- 虽然这里删除了44，但是limit的删除操作是随机的这点要注意
select * from a;
+---+---+
| x | b |
+---+---+
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 9 |
| 9 | 9 |
+---+---+
还原一下a表
drop table a;
create table a ( x int );
insert into a values (1),(2),(3);
select * from a;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
+------+
select * from b;
+------+
| y |
+------+
| 1 |
| 2 |
| 2 |
| NULL |
+------+
现在删除在a表中但是不在b表中的数据
select * from a left join b on a.x = b.y where b.y is NULL; -- 也就是删除这条记录
+------+------+
| x | y |
+------+------+
| 3 | NULL |
+------+------+
delete from a where x not in (select y from b where y is not null); -- 注意判断y is not null;
删除a表中a,b表中都存在的数据
insert into a select 3;
delete from a where x in (select y from b where y is not null);
UPDATE 语法
# Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
# Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
update可以更新单表也可以更新多表
update a set x = 4 where x = 1;
alter table a add column y int default 1;
update a set y = x;
select * from a;
+------+------+
| x | y |
+------+------+
| 3 | 3 |
| 1 | 1 |
| 2 | 2 |
+------+------+
update a set x = 3, y = 2 where x = 3;
select * from a;
+------+------+
| x | y |
+------+------+
| 3 | 2 |
| 1 | 1 |
| 2 | 2 |
+------+------+
update a set x=x+1, y=x where x = 3;
select * from a;
+------+------+
| x | y |
+------+------+
| 4 | 4 | -- 其它数据库里这里显示的是4,3，msyql更新的时候会看当前的值，而不是执行时候的值，所以mysq支持一些很特殊的语法
| 1 | 1 |
| 2 | 2 |
+------+------+
把a表中不存在于b表中的数据对应y值更新为NULL值
update a set x=3,y=4 where x = 5;
select * from a;
+------+------+
| x | y |
+------+------+
| 3 | 4 |
| 1 | 1 |
| 2 | 2 |
+------+------+
select * from b;
+------+
| y |
+------+
| 1 |
| 2 |
| 2 |
| NULL |
+------+
update a left join b on a.x = b.y set a.y = NULL where b.y is null;
select * from a;
+------+------+
| x | y |
+------+------+
| 3 | NULL |
| 1 | 1 |
| 2 | 2 |
+------+------+
order by 和 limit 依旧，不提了。
