SQL SERVER 一些经典语句 1.

Presentation on theme: "SQL SERVER 一些经典语句 1."— Presentation transcript:

1 SQL SERVER 一些经典语句 1

2 表及数据 create table Student(Sno char(9) primary key,
Sname char(20) unique, Ssex char (2), Sage smallint, Sdept char(20)); create table Course( Cno char(4) primary key, Cname char(40), 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), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno))

3 表及数据 --向表student插入数据 insert into student (sno,sname,ssex,sage,sdept)values(' ','李勇','M',20,'CS'); insert into student (sno,sname,ssex,sage,sdept)values(' ','刘晨','F',19,'CS'); insert into student (sno,sname,ssex,sage,sdept)values(' ','王敏','M',18,'MA'); insert into student (sno,sname,ssex,sage,sdept)values(' ','张丽','F',19,'IS'); --向表course插入数据 insert into course (cno,cname,cpno,ccredit)values('2','Maths',null,2); insert into course (cno,cname,cpno,ccredit)values('6','DS',null,2); insert into course (cno,cname,cpno,ccredit)values('7','PASCAL','6',4); insert into course (cno,cname,cpno,ccredit)values('5','HTML',null,4); insert into course (cno,cname,cpno,ccredit)values('1','DB','5',4); insert into course (cno,cname,cpno,ccredit)values('3','IS','1',4); insert into course (cno,cname,cpno,ccredit)values('4','OS','6',3); --向表SC插入数据 insert into sc(sno,cno,grade) values (' ','1',92); insert into sc(sno,cno,grade) values (' ','2',85); insert into sc(sno,cno,grade) values (' ','3',88); insert into sc(sno,cno,grade) values (' ','2',90); insert into sc(sno,cno,grade) values (' ','3',80);

4 --说明：复制表(只复制表结构,源表名：Student 新表名：Student_new)
select * into Student_new from Student where 1<>1 --说明, 用DATEDIFF函数，判别是否达到法定投票年龄（18岁） alter table student add birthday datetime; update Student set birthday=' ' where Sno=' '; update Student set birthday=' ' where Sno=' '; update Student set birthday=' ' where Sno=' '; select * from student where datediff(year,birthday,getdate())>=18

5 --说明：两张关联表，删除主表中已经在副表中没有的数据
delete from student where not exists ( select * from sc where student.sno=sc.sno ) --选出前n条数据 select top 2 * from sc --列出当前用户下的所有表,"U"表示用户 select name from sysobjects where type='U' --按姓氏笔画排序: Select * From Student Order By Sname Collate Chinese_PRC_Stroke_ci_as --从少到多 -数据加密 select pwdencrypt('原始密码') select pwdcompare('原始密码','加密后密码') --= 1相同；否则不相同

6 --CASE的用法 SELECT CASE WHEN grade IS NULL THEN '没有成绩' WHEN grade < 60 THEN '不及格' WHEN grade BETWEEN 60 and 89 THEN '平均成绩' ELSE '优秀' END AS '平均成绩', Cno , COUNT(*) as 数量 FROM SC GROUP BY END, Cno ORDER BY Cno

7 2013 软件考试 上半年考题 第45题 假设学生Students和教师Teachers关系模式如下所示：
其中，学生关系中的类别分为“本科生“和”研究生“两类 a.查询在读研究生的教师的平均工资、最高与最低工资之间差值的SQL语句如下： SELECT (56) FROM Students,Teachers WHERE (57) 56.A.AVG(工资) AS 平均工资， MAX（工资）-MIN(工资) AS 差值 B.平均工资 AS AVG（工资），差值 AS MAX(工资)-MIN(工资) C.AVG(工资) ANY 平均工资，MAX（工资）-MIN(工资) ANY 差值 D.平均工资 ANY AVG（工资），差值 ANY MAX(工资)-MIN（工资） 57.A.Students.身份证号=Teachers.身份证号 B.Students.类别=’研究生’ C.Students.身份证号=Teachers.身份证号 AND Students.类别=’研究生’ D.Students.身份证号=Teachers.身份证号 OR Students.类别=’研究生’ b.查询既是女教师，又是研究生且工资大于等于3500元的身份证号和姓名的SQL语句如下： (SELECT 身份证号，姓名 FROM Students WHERE (58) INTERSECT (SELECT 身份证号，姓名 FROM Teachers WHERE (59) ) 58.A.工资>= B.工资>=’3500’ C.性别=女 AND 类别=研究生 D.性别=’女’ AND 类别=’研究生’ 59. A.工资>= B.工资>=’3500’ C.性别=女 AND 类别=研究生 D.性别=’女’ AND 类别=’研究生’