mysql笔记 - DML语句 - Mkk侯 - SegmentFault 思否

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 依旧，不提了。

mysql笔记 - DML语句