mysql数据库DML操作说明

2019独角兽企业重金招聘Python工程师标准>>>

--语法参考：https://dev.mysql.com/doc/   （当前用的是5.6）

https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-manipulation.html

--select 查询

SELECT
column_1, column_2, ...FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

--DISTINCT语句
作用：删除select结果重复行

DISTINCT与GROUP BY区别：
DISTINCT子句是GROUP BY子句的特殊情况。DISTINCT子句和GROUP BY子句之间的区别是GROUP BY子句可对结果集进行排序，而DISTINCT子句不进行排序。

--BETWEEN ... AND ...语句
相当于  >=min and <=max

--LIMIT offset, length解读
offset：参数指定要返回的第一行的偏移量。第一行的偏移量为0，而不是1。
length：指定要返回的最大行数。

--查询时生成自增列实现

SELECT
column_1, column_2, ... , (@n:=@n+1) as n FROM
table_1,(select @n:=0) as incr

  --insert 插入

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

INSERT INTO tbl_name(a,b,c) select a,b,c from tbl_name2;

--update 修改

UPDATE t1 SET col1 = col1 + 1;

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

UPDATE Table1 t1
join Table2 t2 on t1.ID=t2.t1ID
join Table3 t3 on t2.ID=t3.t2ID
set t1.Value=12345
where t3.ID=54321

--delete 删除

DELETE FROM somelog WHERE user = 'jcole'

#只删除b表
DELETE b FROM document_classification_copy a
INNER JOIN document_classification_copy b
on a.id = b.parent_id and a.parent_id=0

#a,b两个表里能关联上的都删除
DELETE a,b FROM document_classification_copy a INNER JOIN document_classification_copy b
WHERE a.id=b.parent_id and a.parent_id=0;

转载于:https://my.oschina.net/u/3420885/blog/1859648