MySQL数据库常用SQL语句

1.数据定义

Create table sc(sno char(9),cno char(4),grade smallint,primary key(sno,cno),froeign key(sno) references student(sno),foreign key(cno) references course(cno));

Create view is_student as select sno,sname,sage from student where sdept='IS';

Drop table sc;drop view is_student;

2.数据查询

2.1字符匹配

%代表任意长度的字符串，_代表任意单个字符串。

Select * from student where sname like '王%';

2.2ORDER BY

升序(ASC)降序(DESC)

2.3聚集函数

COUNT SUM AVG MAX MIN

2.4WHERE与HAVING的区别

WHERE字句与HAVING短语的区别在于作用对象不同，WHERE字句作用于基本表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。

Select sno from sc group by sno having count(*)>3;

2.5嵌套查询

(NOT)IN, >, >ANY, >ALL, !=ANY,(NOT)EXISTS

查询选修了全部课程的学生姓名（没有一门课是他不选的）

Select sname from student where not exists(select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno));

2.6limit的使用

查询课程成绩排名第三的学生

Select sno,grade from sc order by grade desc limit 2,1;

3.数据更新

Insert into student(sno,sname)values('200215128','陈冬');

Update student set sage=22 where sno='200215121';

Delete from student where sno='200215128';

4.触发器

Create trigger t_student after insert on student for each row

insert into course values(new.sno,new.sname);

Drop trigger t_student;