django mysql sql语句_mysql sql语句

删除

delete from class where caption="test"

全删deletefrom tables

添加

INSERT into class(caption) values("test")

多个值插入INSERT into class(caption) values("test"),("test")

copy其他表中的数据

INSERT into(caption) select caption from class1

修改

update class set caption="test" where cid=5;

查询

select * from table

select id from tables where id =10

显示的时候添加别名

select cid as id from class

排序

select * from class ORDER BY cid LIMIT 1,3;

倒序

select *   from class ORDER BY cid  DESC LIMIT 1,3;

分页

select * from class LIMIT 1,3;

分组

分组后对于相同的行需要聚合，不然就会报错，聚合函数比较常用的 max min conut sum 等等，如果需要对聚合后的条件判断的时候就不能用 where了需要使用 having。

SELECT sum(cid),caption from class WHERE cid >2 GROUP BY cid HAVING COUNT(cid) >=1

连表查询

LEFT JOIN

SELECT * from course,teacher WHERE course.teacher_id = teacher.tid

SELECT * from course LEFT JOIN teacher on course.teacher_id = teacher.tid