/*汇整目的,记住语句格式规范,以及搜集经典SQL语句*/
--绑定当前数据库
USE GradeManager
GO
/*DDL部分,例如:CREATE、DROP、ALTER等语句。*/
--创建数据库
CREATE DATABASE GradeManager
--删除数据库
drop GradeManager
--创建表
CREATE TABLE Student(Sno Char(7) NOT NULL UNIQUE,Sname VarChar(20) NOT NULL,Ssex Char(2) NOT
NULL,Sage Smallint NULL,Clno Char(5) NOT NULL );
--删除表
drop table Student
--添加表字段
Alter Table Student Add Nation VarChar(20) Not Null;
--删除表字段
Alter Table Student Drop Column Nation;
--创建索引
Create Index IX_Class ON Student(Clno ASC);
--删除索引
Drop Index Student.IX_Class;
--创建视图
Create View Stu_year As Select Sno,Sname,2011-Sage as Syear From Student;
--删除视图,略
/*DML部分,例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。*/
--insert操作
Insert Into Grade Values('2001110','3','80');
--update操作
Update Grade Set Gmark=70 Where Sno='2001110';
--delete操作
Delete From Grade Where Sno='2001110';
--备份数据库
backup database aaa to disk='f:/aaa.bak';
--恢复数据库
restore database aaa from disk='f:/aaa.bak';
--备份数据库表,待添加
--Group by与Having,假设已添加字段Avgage到表Class中,然后
Set Avgage=(Select avg(Sage) From Student Group by Clno Having Student.Clno=Class.Clno);
/*DQL部分,例如:SELECT语句。*/
--distinct的使用 找出所有被学生选修了的课程号
select distinct cno from grade;
--别名以及函数的使用,as可有可无
Select sname,ssex,year(getdate())-sage birthday
From student
Where clno in ('01311','01312');
--聚合函数的使用avg,max,min,count
Select avg(gmark),Max(gmark),Min(gmark)
From grade,course
Where cname='操作系统' and grade.cno=course.cno;
--找出选修了课程的学生人数
select count(distinct Sno) from grade;
--找出选修了全部课程的学生姓名
Select sname from student
Where not exists
(Select * from course
where not exists
(Select * from grade
Where sno=student.sno and cno=course.cno));
/*约束*/
--给Course表添加约束
alter table Course
add constraint Course_Cno primary key(Cno);
--删除约束
alter table Course
drop constraint Course_Cno;
/*触发器*/
--向Student表插入一元组,Class表Number字段自动加1
create trigger insert_student_1 on Student for insert
as begin transaction update Class set Number=Number+1 where Clno=(select Clno from inserted)
commit transaction
go
--向Student表删除元组,Class表Number字段减小
create trigger delete_student_1 on Student for delete
as begin transaction declare @shuliang int set @shuliang=(select count(*) from deleted)
update Class set Number=Number-@shuliang where Clno in(select Clno from deleted)
commit transaction
go
/*DQL部分*/
--找出所有被学生选修了的课程号
select distinct Cno from Grade;
--1)找出与李勇在同一个班级的学生信息
select * from Student where Clno in(select Clno from Student where Sname='李勇');
--2)找出所有与学生李勇有相同选修课程的学生信息
select * from Student where Sno in(select Sno from Grade where Cno in (select Cno from Grade
where Sno=(select Sno from Student where Sname='李勇')));
--3)找出年龄介于学生李勇的年龄和25岁之间的学生信息
--4)找出选修了课程操作系统的学生学号和姓名,忽略,与上面类似
select Sno,Sname from Student where Sno in(select Sno from Grade where Cno in(select Cno
from Course where Cname='操作系统'));
--5)找出所有没有选修1号课程的学生姓名
select Sname from Student where Sno not in (select Sno from Grade where Cno='1');
--6)找出选修了全部课程的学生姓名
select Sname from Student where not exists (select * from Grade where not exists(select *
from Course where Student.Sno=Grade.Sno and Grade.Cno=Course.Cno));
--7)查询选修了3门课程的学生学号
select Sno from Grade Group by Sno having count(Cno)>=3;
--8)求每个课程号及相应的选课人数
select C.Cno,count(G.Sno) num from Course C right outer join Grade G on C.Cno=G.Cno Group by
C.Cno;
--9)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列
select * from Student order by Clno asc,Sage desc;
--具体详细连接就不深入学习了,这些面对工作应该就够了,只要不是数据库职业方向