数据库学习之路——七

索引是加快查找速度的有效手段——index

CREATE [unique][cluster] index <索引名>
on <表名>（列名）

create unique index Stusno ON Student(Sno)
create unique index Coucno ON Course(Cno)
create unique index Scno ON SC(Sno ASC,Cno DESC)

修改索引
alter
删除
drop

CREATE TABLE Worker (
  Wno     char ( 7 )  PRIMARY KEY,
  Wname char ( 10 ) NOT NULL,
  Wage    tinyint  
         CHECK (Wage <=45),
  Wpos   char (20 ) ,
  Wsala   char (10),
  Wdno    char (10),
  FOREIGN KEY ( Wdno )  
      REFERENCES  Dept ( Wdno ) 
)
CREATE TABLE Dept (
  Wsala   char (10)
  Wdno    char (10) PRIMARY KEY,
  Wname   char  (20),
  Wmanager  char (20),
  Wtel   char (20),
)

创建学生登记表，年龄<29，性别只能是男或女，姓名非空
CREATE TABLE Student1 (
  Sno     char ( 7 )  PRIMARY KEY,
  Sname char ( 10 ) NOT NULL,
  Ssex    char (2)  
             CHECK (Ssex IN ('男','女')), 
  Sage    tinyint  
         CHECK (Sage <=29),
  Sdept   char (20 ) DEFAULT '计算机系'
)

SC GRADE的值只能在0-100之间
CREATE TABLE SC1 (
  Sno    char(7)  NOT NULL,
  Cno   char(10)  NOT NULL,
  Grade  tinyint,
  CHECK (Grade >= 0 and Grade <= 100),
  PRIMARY KEY ( Sno, Cno ),
  FOREIGN KEY ( Sno )  
      REFERENCES  Student1 ( Sno ),
  FOREIGN KEY ( Cno )  
      REFERENCES  Course1 ( Cno ) )

当学生性别是男的，其名字不能以ms打头
check（sex = ‘nv’ and not  like ‘ms%’）

完整性约束条件c3 小于30岁修改为小于40岁，先删除在添加
CREATE TABLE Student2 (
  Sno     char ( 7 )  PRIMARY KEY,
  Sname char ( 10 ) NOT NULL,
  Ssex    char (2)  
             CHECK (Ssex IN ('男','女')), 
  Sage    tinyint  
      constraint C3 CHECK (Sage <=29),
  Sdept   char (20 ) DEFAULT '计算机系')

alter table Student2
drop constraint C3


alter table Student2
add constraint C3 check (Sage <40)

create ASSERTION asse
check(
select count(Sno) 
from SC
where Cno = ( 
select Cno 
from Course 
where Cnmae = '数据库')
)

/*限制每门课程最多60人选课*/
check(
60 >= ALL(select count(*) 
from SC
group by Cno) 

/*把查询student权限授权给用户u1*/
GRANT SELECT 
ON TABLE Student
TO U1;

/*把所有权限授权给用户u2，u3*/
GRANT all privileges
ON TABLE Student,Course
TO U2,U3;

把查询修改权限授权给用户u4
GRANT SELECT, UPDATE(Sno)
ON TABLE Student
TO U4;
把查询修改权收回
REVOKE SELECT, UPDATE(Sno)
ON TABLE Student
FROM U4;

SELECT COUNT (Sno)
FROM Course, SC
Where Course.Cno = SC.Cno and Cnmae = '数据库' and Grade<60

(6) 没选数学课的学生学号

SELECT Sno
FROM 

（7）查询选修了课程的学生学号

SELECT DISTINCT Sno
FROM SC

8 计算1号课程的学生的评价成绩，最高分，最低分

Select AVG（Grade），MAX（Grade）,MIN(Grade)
from SC
where Cno='1'

9 查询数学系和信息系的学生信息

select *
from student
where Sdept = '数学系' or '信息系'
/*where Sdept in ( '数学系' , '信息系')*/

10将年龄为19岁学生成绩置零

Update SC
Set Grade = 0
Where Sno in(
Select Sno 
FROM Stduent
Where Sage = 19)

11 查询所有选修了1号课程的学生姓名

select Sname
from Student
where Sno in (
select Sno
from SC
Where Cno = '1')

12对每个性别，求学生平均年龄，并存入数据库，先创建表，再插入数据

create table Deptage
(
Sdept char (15),
average SMALLINT

)

INSERT INTO Deptage(Sdept,Average)
SELECT Sdept, AVG (Sage)
FROM Student
group by Sdept

13求每个同学获得的学分

select Sno,Sum(Credit)
from Course, SC
where Course.Cno = SC.Cno and Grade >= 60
group by Sno

14 将所有女生的记录定义为一个试图

Create view female
AS
select *
from Student
Where Ssex = 'nv'

15 查询没有选修1号课程学生的姓名

Select Sname
from Student
except
select Sname from Student，SC
where Student.Sno=Sc.Sno and SC.Cno='1'
)

16将所以选修了数据库课程的学生成绩加5分

update SC
set Grade=Grade + 5
Where Cno in
(
select Cno
from SC,Course
where SC.Cno=Course.Cno,Cname = '数据库'
)

17查询各系男女学生总数，并按升序排列，女生在前

Select Sedpt,Ssex,COUNT(Sno)
from Student
Group by Sdept,Ssex
Order by Sdept ASC,Ssex Desc