索引是加快查找速度的有效手段——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