SQL学习第6次
1、今天来做一做复习。
2、建立数据库的复习。使用的是SQL SERVER 2005 64位，打了SP3补丁。另装了SQLPrompt和SQLSearch，用起来爽多了。。
1、打开Microsoft SQL Server Management Studio。 2、连接-数据库引擎-地址localhost-验证。 3、右键数据库-新建数据库： 数据库名称：test1 4、分别指定数据文件（D:\MSSQL\test1_DATA）和日志文件（D:\MSSQL\test1_LOG）的路径。 5、确定后报错，看样子需事先建立好文件夹。 6、新建文件夹后，再次确定，成功。 --------------------------------------------------------------------------------------------------------------- 本来想用PowerDesign来建表，但用了下不是很会用，还是Microsoft SQL Server Management Studio直接建吧，以后再用PD。 1、右键表——新建表——填入相关字段或者新建查询——输入语句——点击执行即可。
PS：在编写的时候居然不能像SQLyog一样自动转变关键词为大写，提示功能也没有，所以装了一个SQLPrompt。可以到我的百度盘下载
--创建系表 CREATE TABLE DEPT ( DNO CHAR(3) PRIMARY KEY, DN VARCHAR(255) NOT NULL, TEL CHAR(8) CHECK(SUBSTRING(TEL,1,4)='0571') );
--创建学生表 CREATE TABLE STUD ( XH CHAR(7) PRIMARY KEY, XM CHAR(8), CSNY DATETIME, DNO CHAR(3) REFERENCES DEPT(DNO) );
2、插入一些数据。 INSERT INTO DEPT VALUES('D01','计算机系','0571625'); INSERT INTO DEPT VALUES('D02','物理系','0571626'); INSERT INTO DEPT VALUES('D03','化学系','0571627'); INSERT INTO DEPT VALUES('D04','管理系','0571628'); INSERT INTO DEPT VALUES('D05','外语系','0571629'); INSERT INTO STUD VALUES ('1101138','王明',GETDATE()-365*21,'D01'); INSERT INTO STUD VALUES('1101101','胡天',GETDATE()-365*21,'D01'); INSERT INTO STUD VALUES('1101102','唐海',GETDATE()-365*22,'D01'); INSERT INTO STUD VALUES('1101103','张丽',GETDATE()-365*20,'D01'); INSERT INTO STUD VALUES('1102101','李能',GETDATE()-365*22,'D02'); INSERT INTO STUD VALUES('1102103','王中',GETDATE()-365*22,'D02'); INSERT INTO STUD VALUES('1102102','高强',GETDATE()-365*21,'D02'); INSERT INTO STUD VALUES('1103101','JONE',GETDATE()-365*23,'D03'); INSERT INTO STUD VALUES('1103102','SMITH',GETDATE()-365*21,'D03'); INSERT INTO STUD VALUES('1103103','TOM HONE',GETDATE()-365*22,'D03'); INSERT INTO STUD VALUES('1104101','张三', GETDATE()-365*22,'D04'); INSERT INTO STUD VALUES('1104103','李四', GETDATE()-365*21,'D04'); INSERT INTO STUD VALUES('1104102','王五', GETDATE()-365*20,'D04');
*SYSDATE是Oracle中的，在SQL里为GETDATE，用处是获取系统时间。 3、做一些查询练习。 --查询所有学生记录 SELECT * FROM STUD;
--查询所有系的记录，并按系号升序排列 SELECT * FROM DEPT ORDER BY DNO;
--查询'张三'的XH，XM，CSNY SELECT XH AS '学号',XM AS '姓名',CSNY AS '出生年月' FROM STUD WHERE XM='张三';
--查询计算系所有的学生记录 SELECT * FROM DEPT LEFT JOIN STUD ON DEPT.DNO=STUD.DNO WHERE DN='计算机系' ORDER BY STUD.XH;
--查询各计算系学生在各年龄值的人数，如的为，的有等 SELECT COUNT(XH) AS '人数' ,DATEPART(yyyy,GETDATE())-DATEPART(yyyy,CSNY) AS '年龄' FROM STUD GROUP BY CSNY;
--查询'张三'的XH，CSNY，DNO，DN SELECT STUD.XH AS '学号',STUD.XM AS '姓名',STUD.CSNY AS '出生年月',DEPT.DNO AS '系编号',DEPT.DN AS '系名' FROM STUD LEFT JOIN DEPT ON STUD.DNO=DEPT.DNO WHERE XM='张三';
--查询学生的记录数 SELECT COUNT(XH) AS '学生记录数' FROM STUD;
--查询平均年龄小于岁的系号及学生XH,XM,NL（年龄) --建一个投影方便使用 CREATE VIEW STUD_VIEW AS SELECT XH,XM,NL=DATEPART(yyyy,GETDATE())-DATEPART(yyyy,CSNY),DEPT.DNO,DEPT.DN FROM STUD LEFT JOIN DEPT ON STUD.DNO=DEPT.DNO --分两步，先找出平均年龄小于岁的系，然后再找出该系中的学生。 SELECT XH,XM,NL,DNO FROM STUD_VIEW WHERE DNO IN (SELECT DNO FROM STUD_VIEW GROUP BY DNO HAVING AVG(NL)<22);
--找出平均年龄大于的系名及平均年龄数 SELECT 系名=DN,平均年龄=AVG(NL) FROM STUD_VIEW GROUP BY DN HAVING AVG(NL)>20; |
