创建表:

CREATE TABLE <表名> (

<列名><数据类型>[列级完整性约束条件]

[,<列名><数据类型>[列级完整性约束条件]]

...

[,<表及完整性约束条件>]);

例:

CREATE TABLE Student(
	Sno CHAR(9) PRIMARY KEY,/*列级完整性约束条件,Sno是主码*/
	Sname CHAR(20) UNIQUE,/*列级完整性约束条件,Sname取唯一值*/
	Ssex CHAR(2),
	Sage SMALLINT,
	Sdept CHAR(20)
);
CREATE TABLE Course(
	Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件,Cno是主码*/
	Cname CHAR(40) NOT NULL,/*列级完整性约束条件,Cname不为空*/
	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),/*表级完整性约束条件,Sno和Cno是主码*/
	FOREIGN KEY (Sno) REFERENCES Student(Sno),/*表级完整性约束条件,Sno是外码,被参照表是Student*/
	FOREIGN KEY(Cno) REFERENCES Course(Cno)/*表级完整性约束条件,Cno是外码,被参照表是Course*/
);

修改表:

ALTER TABLE <表名>

[ADD [COLUMN] <新列名><数据类型>[列级完整性约束]]/*添加新列*/

[ALTER COLUMN <列名><数据类型>]/*修改列*/

[ADD <表级完整性约束>]/*添加完整性约束*/

[DROP CONSTRAINT <完整性约束名>]/*删除完整性约束*/

ALTER TABLE Student ADD S_entrance DATETIME;/*添加新的一列S_entrance*/
ALTER TABLE Student ALTER COLUMN Sage INT;/*将Sage的数据类型改为INT*/
ALTER TABLE Course ADD UNIQUE(Cname);/*给Cname添加取唯一值的约束条件*/

删除表:

DROP TABLE <表名>

DROP TABLE Student;

索引

建立索引:

CREATE [UNIQUE][CLUSTER] INDEX <索引名> ON <表名>(

<列名> [<次序>]

[,<列名> [<次序>]]

...

);

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucon ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

删除索引:

DROP INDEX <索引名>;

DROP INDEX Student.stusname;

查询:

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式> ...]

FROM <表名或视图名> [,<表名或视图名> ...]|(<SELECT 语句>) [AS] <别名>

[WHERE <条件表达式>]

[GROUP BY <列名> [HAVING <条件表达式>]]

[ORDER BY <列名> [ASC|DESC]];

例:

SELECT Sno,Sname FROM Student;	/*从Student表中查询出所有学生的Sno和Sname*/
SELECT * FROM Student;	/*从Student表中查询出所有学生的信息*/
SELECT Sname '姓名',YEAR(GETDATE())-Sage '出生年份'FROM Student;	/*从Student表中查询出所有学生的姓名(Sname)和出生年份(当前年份-Sage)*/
SELECT DISTINCT Sno FROM SC;	/*从SC表中查询出SC表中存在的Sno*/
SELECT * FROM Student WHERE Sage<18;	/*从Student表中查询Sage<18的学生的所有信息*/
SELECT * FROM Student WHERE Sage BETWEEN 20 AND 23;	/*从Student表中查询Sage在20到23岁之间的学生的所有信息*/
SELECT * FROM Student WHERE Sage NOT BETWEEN 20 AND 23;	/*从Student表中查询Sage不在20到23岁之间的学生的所有信息*/
SELECT * FROM Student WHERE Sdept IN ('CS','MA','IS');	/*从Student表中查询Sdept为CS或MA或IS的学生的所有信息*/
SELECT * FROM Student WHERE Sdept NOT IN ('CS','MA','IS');	/*从Student表中查询Sdept不为CS或MA或IS的学生的所有信息*/
SELECT * FROM Student WHERE Sname LIKE '王%';	/*从Student表中查询姓王的学生的学生的所有信息*/
SELECT * FROM Student WHERE Sname LIKE '王_';	/*从Student表中查询姓王(名字为2个字)的学生的所有信息*/
SELECT Sno,Cno FROM SC WHERE Grade IS NULL;	/*从SC表中查询Grade为空的Sno和Cno*/
SELECT * FROM Student WHERE Sdept='CS' OR Sdept='MA';	/*从Student表中查询出Sept为CS或MA的学生的所有信息*/
SELECT * FROM Student ORDER BY Sage,S_entrance DESC,Sno;	/*从学生表中查询出所有信息,将Sage升序、S_entrance降序、Sno升序排序*/
SELECT Cno,MAX(Grade)'最高分',MIN(Grade)'最低分',AVG(Grade)'平均分' FROM SC GROUP BY Cno;	/*查询出各个课程的最高分、最低分、平均分*/
SELECT Sdept,AVG(Sage)'平均年龄',COUNT(Sno)'人数' FROM student GROUP BY Sdept HAVING AVG(Sage)>18;	/*查询出学生平均年龄大于18的学院的学生人数和平均年龄*/
SELECT CA.Cno'课程号',CA.Cname'课程',CB.Cpno'先行课程号',CB.Cname'先行课' FROM Course CA,Course CB WHERE CA.Cpno=CB.Cno AND CB.Cpno IS NOT NULL;	/*查询出课程的先行课*/
SELECT Sno,Sname FROM Student WHERE Sno IN	/*根据学号Sno查学生的Sno和Sname*/
	(SELECT Sno FROM SC WHERE Cno IN	/*根据课程号Cno查学号Sno*/
		(SELECT Cno FROM Course WHERE Cname='信息系统')/*根据课程名称Cname查课程号Cno*/
	);	/*查询选修了信息系统的学生*/
/*查询非计科系比计科系里任意一个学生年龄小的学生*/
SELECT * FROM Student WHERE Sage<ANY(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept!='CS';	/*方法1,使用谓词*/
SELECT * FROM Student WHERE Sage<(SELECT MAX(Sage) FROM Student WHERE Sdept='CS') AND Sdept!='CS';	/*方法2,使用聚集函数*/

/*查询非计科系比计科系里所有学生年龄小的学生*/
SELECT * FROM Student WHERE Sage<ALL(SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept!='CS';	/*方法1,使用谓词*/
SELECT * FROM Student WHERE Sage<(SELECT MIN(Sage) FROM Student WHERE Sdept='CS') AND Sdept!='CS';	/*方法2,使用聚集函数*/

/*查询了选修了1号课程的学生的姓名*/
SELECT Student.Sno,Sname FROM Student,SC WHERE SC.Cno='1' AND Student.Sno=SC.Sno;	/*方法1,多表连接查询*/
SELECT Sno,Sname FROM Student WHERE Sno in(SELECT Sno FROM SC WHERE Cno='1');	/*方法2,不相关子查询*/
SELECT Sno,Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Cno='1');	/*方法3,相关子查询,利用EXISTS谓词*/

/*EXISTS的几种用法:*/
/*查询和刘晨同一个系的学生*/
SELECT * FROM Student S1 WHERE EXISTS (
	SELECT * FROM Student S2 WHERE S2.Sno = S1.Sno and S2.Sdept=(
		SELECT Sdept FROM Student WHERE Sname = '刘晨'));


/*查询选修了“信息系统”的学生学号和姓名*/
SELECT Sno,Sname FROM Student WHERE EXISTS (
	SELECT * FROM SC WHERE Student.Sno=SC.Sno AND Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统'));


/*找出每个学生超过他自己选修课程平均成绩的课程号*/
SELECT Sno,Cno FROM SC WHERE EXISTS (
	SELECT * FROM SC SC1 WHERE SC.Sno=SC1.Sno AND SC.Grade>=(SELECT avg(SC2.Grade) FROM SC SC2 WHERE SC2.Sno = SC.Sno));


/*查询非计科系中比计科系任意一个学生年龄小的学生姓名和年龄*/
SELECT Sname,Sage FROM Student WHERE EXISTS (
	SELECT * FROM Student S2 WHERE S2.Sdept!='CS' AND Student.Sno=S2.Sno AND S2.Sage<(
		SELECT MAX(Sage) FROM Student S3 WHERE S3.Sdept='CS'))


/*查询非计科系中比计科系所有学生年龄小的学生姓名和年龄*/
SELECT Sname,Sage FROM Student WHERE EXISTS (
	SELECT * FROM Student S2 WHERE S2.Sdept!='CS' AND Student.Sno=S2.Sno AND S2.Sage<(
		SELECT MIN(Sage) FROM Student S3 WHERE S3.Sdept='CS'));

/*查询每个系的平均成绩,并把系名和平均成绩存入一个新的表*/
CREATE TABLE GradeAvg(Dept CHAR(20) PRIMARY KEY,Grade SMALLINT);	/*创建均成绩表*/
INSERT INTO GradeAvg (Dept,Grade) SELECT Student.Sdept,AVG(Grade) FROM Student,SC WHERE Student.Sno=SC.Sno GROUP BY Student.Sdept;	/*插入数据*/

断言:

CREATE ASSERTION <断言名><CHECK子句>

例:


/*限制数据库课程最多被60名课程选修*/
CREATE ASSERTION ASSE_SC_DB_NUM CHECK(
	(SELECT COUNT(*) FROM Course,SC WHERE SC.Cno=Course.Cno AND Course.Cname='数据库')<=60);

/*限制每一门课程最多被60名学生选修*/
CREATE ASSERTION ASSE_SC_NUM CHECK(
	ALL(SELECT COUNT(*) FROM SC GROUP BY Cno)<=60);

持续更新中...

0
扫描二唯码在微信中打开

我一直在开辟我的天空