mysql sql语句

删除

delete from class where caption="test"

全删delete from 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