数据库定义与操作语言 Mysql

数据库的定义

创建数据库

CRAETE DATABASE test;

删除数据库

DROP DATABASE test;

SQL中创建模式的语句：CRAETE SCHEMA <模式名> AUTHORIZATION <用户名>；
SQL中删除模式的语句：DROP SCHEMA <模式名><CASCADE|RESTRICT>
但是MySQL中没有模式，因此无法创建。

创建表

CREATE TABLE Student(Sno CHAR(9) PRIMARY KEY,Sname CHAR(20) UNIQUE,Ssex CHAR(2),Sage SMALLINT, Sdept CHAR(20));
Create Table Course(Cno char(4) primary key,Cname Char(40) not null,Cpno char(4),Ccredit smallint,foreign key(Cpno) references Course(Cno));
Create table SC(Sno char(9),Cno char(4),Grade Smallint,primary key(Sno,Cno),foreign key(Sno) references Student(Sno),foreign key (Cno) references Course(Cno));

删除表

DROP table Student;

数据基本查询

select sno,sname from student;

查询经过计算的值：

select sname,2019-sage from student;

有条件的查询：

select sname from student where sdept='CS' and sage<20;

分组统计查询：

select sdept,avg(sage) from student group by sdept;

多表连接查询：

select student.*,sc.* from student,sc where student.sno=sc.sno;

自身连接查询：
先修课的先修课

select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

外连接：

select student.*,cno,grade from student left outer join sc on (student.sno=sc.sno);

实验1.3 数据高级查询
不相关子查询：

select sname from student where sno in (select sno from sc where cno='1');

相关子查询：
找出每个学生超过他自己选修课程平均分的课程号

select sno,cno from sc x where grade>=(select avg(grade) from sc y where y.sno=x.sno);

带有any的查询
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄：

select sname,sage from student where sage<any(select sage from student where sdept='cs') and sdept<>'cs';

带有exists的子查询：
查询所有选修了1号课程的学生的姓名

select sname from student where exists (select * from sc where sno=student.sno and cno='1');

查询选修了全部课程的学生的姓名：

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

集合查询：
查询计算机科学系的学生与年龄不大于19岁学生的交集

select * from student where sdept='cs' union select * from student where sage<=19;

实验1.4 数据更新
插入一个学生信息：

insert into student values('201215126','张成民','男','18','CS');

插入一条课程记录：

insert into course values('1','数据库',null,4);

插入一条选课记录：

insert into sc values('201215126','1',null);

插入子查询结果：

create table dept_age(sdept char(15),avg_age smallint);
insert into dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept;

更新数据

update student set sage=22 where sno='201215126';

将所有学生的年龄加一：

update student set sage=sage+1;

将所有计算机系学生成绩置零；

update sc set grade=0 where sno in (select sno from student where sdept='CS');

删除一条数据：

delete from student where sno='201215128';

带子查询的删除：

delete from sc where sno in (select sno from student where sdept='EE');

实验1.5 视图
创建带with check option的视图
建立信息系学生的视图：

create view IS_student as select sno,sname,sage from student where sdept='IS' with check option;

创建分组视图：

create view s_g(sno,gavg) as select sno,avg(grade) from sc group by sno;

删除视图

drop view s_g;

查询视图：

select sno,sage from is_student where sage<=20;

with check option验证

create view seniorstu(sno,sage) as select sno,sage from student where sage>='19' with check option;
insert into seniorstu values('201215127',18);
insert into seniorstu values('201215127',21);

更新不可更新视图

update s_g set gavg=90 where sno='201215121';