数据库—SQL语言

/*创建数据库*/
create database db211416145
/*创建student表*/
create table student
(
Sno  char(10) primary key,
Sname char(20),
Ssex char (10),
Sage int,
Sdept char(20),
);
/*创建course表*/
create table course
(
Cno char(10) primary key,
Cname char(20),
Chours int,
);
/*创建SC表*/
create table SC
(
Sno char(10),
Cno char(10),
Grade int,
foreign key (Sno)references student(Sno),
foreign key (Cno)references course(Cno),
primary key(Sno,Cno),
);
/*插入Student表数据*/
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9512101','李勇','男',19,'计算机系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9512102','刘晨','男',20,'计算机系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9512103','王敏','女',20,'计算机系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9521101','张立','男',22,'信息系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9521102','吴宾','女',21,'信息系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9521103','张海','男',20,'信息系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9531101','钱小力','女',18,'数学系');
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('9531102','王大力','男',19,'数学系');
/*插入course表数据*/
insert into course(Cno,Cname,Chours)
values('C01','计算机文化学',70)
insert into course(Cno,Cname,Chours)
values('C02','VB',90)
insert into course(Cno,Cname,Chours)
values('C03','计算机网络',80)
insert into course(Cno,Cname,Chours)
values('C04','数据库基础',108)
insert into course(Cno,Cname,Chours)
values('C05','高等数学',180)
insert into course(Cno,Cname,Chours)
values('C06','数据结构',72)
/*插入SC表数据*/
insert into SC(Sno,Cno,Grade)
values('9512101','C01',90)
insert into SC(Sno,Cno,Grade)
values('9512101','C02',86)
insert into SC(Sno,Cno,Grade)
values('9512101','C06',null)
insert into SC(Sno,Cno,Grade)
values('9512102','C02',78)
insert into SC(Sno,Cno,Grade)
values('9512102','C04',66)
insert into SC(Sno,Cno,Grade)
values('9512102','C01',82)
insert into SC(Sno,Cno,Grade)
values('9512102','C02',75)
insert into SC(Sno,Cno,Grade)
values('9512102','C04',92)
insert into SC(Sno,Cno,Grade)
values('9512102','C05',50)
insert into SC(Sno,Cno,Grade)
values('9512103','C02',68)
insert into SC(Sno,Cno,Grade)  /*这里注意空值一定要标明NULL*/
values('9512103','C06',null)
insert into SC(Sno,Cno,Grade)
values('9531101','C01',80)
insert into SC(Sno,Cno,Grade)
values('9531101','C05',95)
insert into SC(Sno,Cno,Grade)
values('9531102','C05',85)

--修改基本表(新增加一竖列）：向student表加入入学时间，类型为日期型            //（新加表为空值）
alter table student add Sentrance date

--修改基本表（更改一竖列名字）：将student表Sentrance改为1111


--修改基本表（删除一竖列）：删除student表的sentrance一行                        //不要忘记column
alter table student drop column Sentrance

--修改基本表（修改列的数据类型）：将student表的Sage转为char
alter table student alter column Sage char
--改回
alter table student alter column Sage int ;                    --        //出错无法改回

--修改基本表（增加属性必须取唯一值约束条件）
alter table student add unique(Sname);

--修改基本表（取消属性约束条件）




--1.分别查询学生表和学生修课表中的全部数据。
select * from student;
select * from SC;


--删除基本表（有限制）有关联到表的东西就删不掉
　　　
　　drop table student restrict
--删除基本表（无限制）  将表和上面有关的表，视图全删了

　drop table student cascade  

--                        ********************************索引********************************

--建立索引
create unique index Stusno ON student(Sno);
--删除索引
drop index Stusno on student                --//按照书上的不行 drop index <索引名>

--建立索引（按照多个属性）按照Sno升序，Cno降序
create unique index SCno on SC(Sno ASC,Cno DESC);
--删除索引
drop index SCno on SC;





--                              ****************************查询*************************
--单表查询：

--查询学生学号，姓名
select Sno,Sname
from student;

--查询全体学生详细记录
select *
from student;

--（查询经过计算的值）：查询出生年份
select Sname,2016-Sage
from student;

--（可以指定别名）           --//lower可将相应的英文字符串转换成小写，记住select后跟的永远是显示的，别名写在后面是标题
select Sname NAME,'Year Of Birth'Birth,2016-Sage BIRTHDAY,LOWER(Sdept)DEPARTMENT
from student;

--（取消重复）        --//在查询的属性前加distinct可取消重复属性
select distinct Sno
from SC;





--查询满足条件元组:

--查询计算机学生名单
select *
from student
where Sdept = '计算机系';

--查询20岁以下学生信息       (< > = >= <= != !> !< 不大小于 ！>)
select *
from student
where Sage < 20;              


--查询年龄在20~23岁之间的学生  (between....and....)
select *
from student
where Sage between 20 and 23;   

--查询年龄不在20~23岁之间的学生(not between... and....)
select *
from student
where Sage not between 20 and 23;

--查询计算机系和数学系的学生       （在确定集合之中，in的用法）
select *
from student
where Sdept in ('计算机系','数学系');

--查询学号为9512101学生详细情况
select *
from student
where Sno like '9512101';

--查询张姓学生情况 (通配符%， 代表任意长度（可以=0）的字符串，a%b表示以a开头b结尾任意长度字符串acb,addgb
select *
from student
where Sname like '张%';

--查询姓”钱小“且全名为3个汉字的学生情况(下划线代表任意单个字符，字符集GBK一个_代表一个汉字，字符集ASCII两个__代表一个汉字）                     //也可以代表空字符吗？
select *
from student
where Sname like '钱小_';

--查询第二个字为“小”的学生        (如果查询的对象中有% _等通配符使用逃逸字符，where Sname like '张\_'escape  ‘\'；这里的_不再当成通配符
select * 
from student
where Sname like '__小%';

--查询不姓钱学生
select *
from student
where Sname not like '钱%';

--查询没有成绩学生学号  （涉及空值查询）
select Sno
from SC
where Grade is NULL;

--查询有成绩学生学号
select Sno
from SC
where Grade is not null;

--查询计算机系且男的学生         (多重条件查询，用and  或 or来连接条件
select *
from student
where Sdept = '计算机系' and Ssex = '男';

--查询选修1号课程的学号和成绩，按分数降序  (order by 子句，使用order by子句对查询结果一个多个属性升序ASC降序DESC排列缺省为升
select Sno,Grade
from SC
where Cno = 'C01'
order by Grade desc;

--查询选修一号课程学号和成绩，按成绩降序，按学号升序
select Sno,Grade
from SC
where Cno = 'C01'
order by Grade desc,Sno;

--查询学生总人数（聚集函数 count sum avg max min)
select COUNT(*)
from student;

--查询选修了课程的学生人数(给聚集函数指定distinct短语，表示取消指定列重复值）
select COUNT(distinct Sno)
from SC;

--计算1号课程学生平均成绩
select AVG(Grade) AverageScore
from SC
where Cno = 'C01';

--查询每个课程号对应选课人数（group by 语句 加上group by语句将会对分组中的项进行统计，而不是对所有表中对象
select Cno,COUNT(Sno)
from SC
group by Cno;

--查询选修了3门课程以上学生学号 (having 短句与where区别在于作用对象不同，where作用于基本表或试图，having作用于groupby中的组，从组中选择满足条件的组
select Sno
from SC
group by Sno
having COUNT(*)>3;


--*********连接查询**********
--查询学生的信息和他的选课情况
select student.*,SC.*
from student,SC
where student.Sno = SC.Sno;

--对上例用自然连接  (去掉了连接表中的相同属性）
select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from student,SC
where student.Sno = SC.Sno;

--使用自身连接(表可以连接自己，使用first，second区分）
--select first.cno,second.cpno
--from course first,course second
--where first.cpno = second.Cno;

--列出全部学生基本情况和选课情况，NULL也要列出（此处即为外连接,此处以student主体，若选课没选该生信息也列出）
select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from student left OUTer JOIN SC on (student.Sno = SC.Sno);


--查询选修二号课程且成绩在90分以上学生  (复合条件连接）
select student.Sno,Sname
from student,SC
where student.Sno = SC.Sno and
      SC.Cno = 'C02'and
      SC.Grade > 90;
      
--查询与李勇在同一个系的学生（嵌套查询）
select *
from student
where student.Sdept in
      (select Sdept 
       from student
       where student.Sname = '李勇');
       
--查询选修了数据结构的学生信息  (三重嵌套）
select *
from student
where student.Sno in
    (select Sno
    from SC
    where SC.Cno in
    (select Cno
    from course
    where course.Cname = '数据结构')
    );
    
--查询每个学生超过他选修课程平均成绩的课程号 (相关子查询，因为要查课程号和学生，所以select sno，cno 从SC表中，呢么什么情况下的课程呢
--分数大于他的平局分，他的平均分是什么？再次求他的平均分，select avg（grade） from sc；是谁的平均分是外层循中的所以用X,Y来代表）
select Sno,Cno
from SC x
where Grade > (
    select AVG(grade)
    from SC y
    where x.Sno = y.Sno);
    
--带有any all谓词的查询，any表示某一个，all表示所有
--查询其他系中比计算机系中某一学生年龄小的学生姓名年龄
select Sname,Sage
from student
where Sage < any (select Sage
                from student
                where Sdept = '计算机系');
                
--带有exits的子查询
--查询所有选修了1号课程的学生姓名  (因为子查询返回BOOL值，所以子查询通常用*，
select Sname
from student
where exists (select *
            from SC
            where Sno = student.Sno and Cno= 'C01');
            

--查询选修了全部课程的学生姓名  (不存在这样的课程该学生没有选修)                   ****************难点，运用到离散数学知识（找时间复习，）
select Sname
from student
where not exists(
                select *
                from course
                where not exists (
                select * from SC
                where Sno = student.Sno
                and Cno = course.Cno)
                );
--查询至少选修了学生选修的全部课程的学生号码（不存在这样的课程Y,学生200215122选修了Y，而学生X没有选               *********难点
select distinct Sno
from SC SCX
where not exists
(select *
from SC SCY
where SCY.Sno = '200215122' and
            not exists
            (select *
            from SC SCZ
            where SCZ.Sno = SCX.Sno AND
            SCZ.Cno = SCY.Cno)
);

--集合查询
--查询计算机系的学生及年龄不大于19的学生(两个查询可用 并unioin 交intersect 差except
select *
from student
where Sdept = '计算机系'
union
select *
from student
where Sage <= 19;

--数据更新
--插入数据一行元组     (指明了数据可以按照顺序填入数据，如果只有表名，要按照属性顺序输入,且空值要手动输入NULL）
insert 
into student(Sno,Sname,Ssex,Sdept,Sage)
values ('1111','啊去','男','计算机系',15);

--插入子查询结果
--insert into deptage(sdept,avg-age)
--select sdept,AVG(sage)
--from student
--group by Sdept;


--修改数据
--修改一行元组值
--将学生951201的年龄改为30岁
update student
set Sage = 30
where Sno = '951201';

--修改多行元组
--将所有学生年龄+1
update student
set Sage = Sage + 1;


--带子查询的修改语句
--将所有学生成绩置0；
update SC
set Grade = 0
where '计算机系'=
(select Sdept
from student
where student.Sno = SC.Sno);

--删除一行（一个元组）
delete 
from  student
where Sno = '951201';

--删除多行
--删除所有学生选课记录
delete 
from SC

--带子查询删除
--删除计算机系学生选课记录
delete
from SC
where 'cs'=
(select Sdept
from student
where student.Sno = SC.Sno);






--*********************************视图*****************************************

--建立信息系学生视图
create view fffgwe
as
select Sno,Sname,Sage
from student
where Sdept = '计算机系';

--建立计算机系学生视图，要求进行修改和插入操作是仍需保证概述图只有信息系学生
create view us_student
as
select * 
from student
where Sdept = '计算机系'
with check option;

--视图可以建立在多个表上
--建立信息系选修了一号课程的学生视图
create view g_1(Sno,Sname,Grade)
as
select student.Sno,Sname,Grade
from student,SC
where Sdept = '计算机系' AND
    student.Sno = SC.Sno and
    SC.Cno = '1';
    
--视图也可建立在其他视图上
--建立信息系选修了1号课程成绩在90分以上学生的视图
create view gg
as
select Sno,Sname,Grade
from g_1
where grade >= 90


--定义反应学生出生年份视图  (带有表中不存在属性的属性的视图，那列成为虚拟列，视图称为代表达式视图
create view bt_s(Sno,Sname,Sbirth)
as
select Sno,Sname,2004 - Sage
from student;

--删除视图
drop view xxxx;
--如果视图上有其他视图（根据该表创建的其他视图都删除了）
drop view  xxxx cascade 

--查询视图
--查询选修了1号课程的信息系学生      (DBMS会将语句转换为对基本表的查询语句）
select g_1.Sno,Sname
from g_1,SC
where g_1.Sno = SC.Sno And SC.Cno = '1';

--注意不是所有情况都可对视图和表进行连接查询（只有行列子集视图才可，（视图只是少了基本表中的几列属性而没有多出其他的属性列），如果
--多出了其他列的视图会错误
--在S-G视图中查询平均分90分以上的学生
--select *
--from s_g
--where gavg >= 90;
--定义的S-G视图的子查询为
--select sno,SVG(GRADE)
--FROM SC GROUP BY Sno;
--结合的话会出现select sno,avg(grade)
--from sc
--where avg(grade)>= 90
--group by sno;
--而where子句不能用聚集函数要用having所以错了，此种情况一般直接对基本表查询

--更新视图与表一样，但有些视图是不能更新的，如那个学生平均成绩表，你不能修改平均成绩，因为无法反应到基本表上，修改基本表成绩使平均成绩与你修改的相等
--一般行列子集视图是可更新的