考试重点sol语句

(1)建立数据库以mydb 为例CREATE DATABASE mydb --数据库名 ON --数据文件( NAME=pmydb, --主数据文件 FILENAME='D:\MSSQLDATA\mydb.mdf', --物理文件及位置 SIZE=10MB,MAXSIZE=100MB,FILEGROWTH=1MB --初始大小、最大值、增长 ),( NAME=nmydb, --次数据文件 FILENAME='D:\MSSQLDATA\mydb.ndf', --物理文件及位置 SIZE=10MB,MAXSIZE=100MB,FILEGROWTH=1MB --初始大小、最大值、增长 )LOG ON --日志文件 ( NAME=logmydb,FILENAME='E:\MSSQLLOG\mydb.ldf', --物理文件及位置 SIZE=100MB,MAXSIZE=1000MB,FILEGROWTH=10MB --初始大小、最大值、增长 );(2)删除数据库DROP DATABASE mydb;1.建立dept 表，并设置完整性规则 CREATE TABLE dept ( dno CHAR(2), dname VARCHAR(20) NOT NULL, CONSTRAINT dept_pk PRIMARY KEY(dno), CONSTRAINT dept_ukUNIQUE(dname) );2.建立student 表，并设置完整性规则CREATETABLE student ( sno CHAR (2),snameVARCHAR (20) NOTNULL,ssex CHAR (2) NOTNULL DEFAULT '男', sage INTNOTNULL,dnoCHAR (2) NOTNULL,CONSTRAINT student_pk PRIMARYKEY (sno ),CONSTRAINT student_fk FOREIGNKEY (dno )REFERENCES dept (dno )ONDELETECASCADEONUPDATECASCADE ,CONSTRAINT student_ck CHECK (ssex in('男','女')));3.建立course 表，并设置完整性规则REATETABLE course ( cno CHAR (2),cnameVARCHAR (20) NOTNULL,pcno C HAR (2), creditINTNOTNULL,CONSTRAINT course_pk PRIMARYKEY (cno ),CONSTRAINT course_fk FOREIGNKEY (pcno )REFERENCES course (cno ), CONSTRAINT course_uk UNIQUE (cname ), CONSTRAINT course_ck CHECK (credit >0));4.建立study 表，并设置完整性规则CREATETABLE study ( snoCHAR (2),cno CHAR (2), gradeINT ,CONSTRAINT study_pk PRIMARYKEY (sno ,cno ),CONSTRAINT study_fk_sno FOREIGNKEY (sno )REFERENCES student (sno ), CONSTRAINT study_fk_cno FOREIGNKEY (cno )REFERENCES course (cno ));5.删除建立的4个表。

DROP TABLE dept;INSERTINTO dept VALUES ('D1','计科系');UPDATE dept SET dname='计算机科学系' WHERE dno='D1'; DELETE FROM dept WHERE dno='D3';INSERTINTO student VALUES ('S1','张刚','男',20,'D2'); INSERTINTO student (Sno,Sname,Ssex,Sage,Dno) VALUES ('S1','张刚','男',20,'D2');UPDATE student SET sname='赵强',dno='D1' WHERE sno='S1' DELETE FROM student WHERE sno='S3';查询语句：(1)投影查询[8]：指定列、所有列；表示式列、常量列、函数列；使用DISTINCT 、列别名；表别名和表.字段 ①SELECT sno,sname FROM student; ②SELECT * FROM student;③SELECT sno,sname,2011-sage FROM student;④SELECT sno,'sname' FROM student;⑤SELECT count(*) FROM student;(2)条件查询[6]：比较、逻辑、范围、集合、匹配、空值①SELECT sno,sname FROM student WHERE sage>20;②SELECT sno,sname FROM student WHERE (dno='D1') AND (sage>20);③SELECT sno,sname FROM student WHERE sage BEWEEN 20 AND 23;④SELECT sno,sname FROM student WHERE dno IN ('D1','D2');⑤SELECT sno,sname FROM student WHERE sname LIKE '张%';⑥SELECT sno,cno FROM study WHERE grade IS NULL;(3)排序查询[3]：单列、多列；升序与降序①SELECT * FROM student ORDER BY sno ASC;②SELECT * FROM student ORDER BY sname DESC;③SELECT * FROM student ORDER BY dno DESC,sno ASC;(4)分组查询[2]：分组、分组条件①SELECT cno,AVG(grade)FROM study GROUP BY cno;②SELECT sno,AVG(grade)FROM study GROUP BY sno HAVING(AVG(grade)>=80);2.集合查询[3](1)并UNION[1]SELECT sno,sname,ssex FROM student WHERE dno='D1' UNIONSELECT sno,sname,ssex FROM student WHERE dno='D2';(2)交INTERSECT[1]SELECT sno FROM study WHERE cno='C1' INTERSECTSELECT sno FROM study WHERE cno='C2';(3)差EXCEPT[1]SELECT sno FROM study WHERE cno='C1'EXCEPTSELECT sno FROM study WHERE cno='C2';3.连接查询[8](1)交叉连接[1]SELECT sno,sname,cno,cnameFROM student course;(2)自然连接[3]：两表、多表；连接条件、复合条件①SELECT s.sno,s.sname,o,d.gradeFROM student s, study d;WHERE s.sno= d.sno②SELECT s.sno,sname,cname,gradeFROM student s,course c,study d;WHERE (s.sno=d.sno) AND (o=o)③SELECT s.sno,s.sname,o,d.gradeFROM student s, study d;WHERE (s.sno= d.sno) AND (s.dno='D1')(3)相关子查询[1]: Array SELECT sno,cnoFROM study sWHERE grade>(SELECT AVG(grade)FROM stduy dWHERE (d.sno=s.sno)建立视图①建立的检查视图view1：CREATE VIEW view1 ASSELECT sno,sname,dnoFROM studentWHERE ssex='男' WITH CHECK OPTION②建立的行列视图view2：CREATE VIEW view2 ASSELECT sno,sname,ssexFROM studentWHERE dno='CS'③建立的连接视图view3：CREATE VIEW view3 ASSELECT sno,sname,dnameFROM student s,dept dWHERE s.dno=d.dno④建立的计算视图view4：CREATE VIEW view4 ASSELECT sno,sname,2010-sage sbirthFROM student⑤建立的分组视图view5：CREATE VIEW view5 ASSELECT sno,AVG(grade) avggradeFROM study GROUP BY sno2.查询视图①查询视图view1：SELECT * FROM view1 WHERE dno='CS';②查询视图view2：SELECT * FROM view2 WHERE dno='男';③查询视图view3：SELECT * FROM view3 WHERE sno='S01';④查询视图view4：SELECT * FROM view4 WHERE sbirth>1990;⑤查询视图view5：SELECT * FROM view5 WHERE avggrade>80;。