mysql特殊语法
-
replace into (insert into 的增强版):存在即更新(先删除后插入),不存在即插入,所以需要有唯一主键,否则会插入重复数据;存在外键的情况下,不可以使用!(tips:mysql唯一索引,字段为null时唯一索引失效)
使用方式
1、replace into tbl_name(col_name, …) values(…)
2、replace into tbl_name(col_name, …) select …
3、replace into tbl_name set col_name=value, … -
INSERT INTO SELECT :从一个表复制数据,然后把数据插入到一个已存在的表(目标表)中,目标表中任何已存在的行都不会受影响。
1、Insert into Table2(field1,field2,…) select value1,value2,… from Table1 where condition;
2、Insert into Table2 select * from Table1;(table2和table1表结构一致) -
唯一索引列不能为null,否则不作数
-
create table table1 like table:创建一个跟table表结构一模一样的表
-
create table table1 (col_name, …)select …:从一个查询结果中创建表
-
update table1 别名
inner join table2 别名
on
set -
delete 别名 from table1 AS 别名 inner join table2 别名 on …
-
count支持case when表达式。例、count(case when … then … end)
update语句不同数据库用法
- 通用update
UPDATE table_name
SET column1=value1,
column2=value2,
…
WHERE some_column = some_value; - GreenPlum或者Postgre数据库 update与select结合
UPDATE table1 t1
SET column1 = t2.columnname1
column2 = t2.columnname2
FROM (select columnname1,columnname2 from table2) t2
WHERE t1.column3 = t2.column3
AND t1.column = ‘111’;
注:对于set列中左边的列不能使用t1.这种别名方式,只能使用column名称 - MySQL数据库 update与select结合
UPDATE table1 SET column1 = (SELECT column FROM table2 [WHERE condition])
WHERE table1.column2 = value;
UPDATE table1 inner/left/right join table2/(select columns from table3
[inner/left/right join on condition] [where conditions]) as t3
ON condition
SET column1 = value1,column2 = value2,…
[WHERE conditions]; - Oracle数据库 update与select结合
UPDATE table1 alias
SET (column_name,column_name ) = (
SELECT column_name, column_name FROM table2 WHERE table2.column_name = alias.column_name)
[WHERE column_name = VALUE] - SQLServer数据库 update与select结合
UPDATE table1
SET column1 = t2.column1,
column1 = t2.column2,
…
FROM table1 INNER/LEFT/RIGHT JOIN table2 ON table1.column = table2.column
[WHERE conditions]
sql执行顺序
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>