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;