SQL 数据库操作

use kk
--数据类型：bigint binary(50) smallint char()  nchar() datetime decimal(18,0) float---
--image  int money nchar text numeric(0,小数点后位数) real smalldatetime---
---tinyint timestamp nvarchar() varchar() bit--
--------------------------------------------------------------------------------
use master
if exists(select * from sysdatabases where name='PRODUCT')

drop database PRODUCT
go
create database PRODUCT
on primary
(
name=PRODUCT,
filename='D:\PRODUCT.mdf',
size=10,
maxsize=50,
filegrowth=10%
)
log on
(
name=PRODUCT_log,
filename='D:\PRODUCT_log.ldf',
size=10,
maxsize=50,
filegrowth=10%
)
go

!----学生表Student-----------------------------------------------------------
if exists(select * from sysobjects where name='student')
  drop table student
create table Student
              (Sno char(9) primary key,/*列级完整性约束条件，Sno是主码*/
               Sname char(20) unique,  /*Sname 取唯一值*/
               Ssex char(2),
               Sage smallint,
               sdept char(20)
                );
------课程表Course-------------------------------------------------------------
create table Course
              (Cno char(4) primary key,  /*列级完整性约束条件，Cno是主码*/
               Cname char(40),
               Cpno char(4),             
               Ccredit smallint,         /*Cpno的含义是先修课*/
               FOREIGN KEY Cpno REFERENCES Course(Cno)
/*表级完整性约束条件,Cpno是外码，被参照表是Course,被参照列是Cno*/
                );
-------建立学生选课表SC---------------------------------------------------------
create table SC
               (Sno char(9),
                Cno char(4),
                Grade smallint,
                primary key (Sno,Cno),
                          /*主码由两个属性构成，必须作为表级完整性进行定义*/
                foreign key (Sno) references Student(Sno),
                          /*表级完整性约束条件，Sno是外码，被参照表是Student*/
                foreign key (Cno) references Course(Cno)
                          /*表级完整性约束条件，Cno是外码，被参照表是Course*/
                );
/*每一个基本表都属于某一个模式，一个模式包含多个基本表*/
--当定义基本表时，如何定义它所属的模式？一般有三种方法--
--搜索路径包含一组模式列表--
---修改基本表---
/*
  alter table <表名>
[add <新列名><数据类型>[完整性约束]]
[drop <完整性约束名>]
[alter column<列名><数据类型>]
*/
--向STUDENT表中添加“入学时间”列，其数据类型为日期型-----------------------------
  alter table Student add S_entrance datetime;
  select * from Student/*查询*/
/*将年龄的数据类型由字符型改为整型*/
alter table Student alter column Sage int;
--增加课程名称必须取唯一值的约束条件--------------------------------------------
alter table Course add unique(Cname)
select * from Course]
/*删除基本表*/
drop table Student--受外键约束
delete Student---不受外键约束
--若表上建有视图，选择restrict 时表不能删除，cascade时可以删除表，视图也自动被删除--
create view IS_Student  /*Student 表上建立视图*/
as select Sno,Sname,Sage from Student
where Sdept = 'IS';

drop table Student /*有（外键约束）约束条件删除不了*/
select * from IS_Student;
/*建立索引*/
--为学生-课程数据库中的student course,sc3个表建立索引----------------------------
--student表按学号升序建唯一索引--------------------------------------------------
create unique index Stusno on Student(Sno);
--course 表课程号升序建立索引
create unique index Coucno on Course(Cno);
--SC表按学号升序，课程号降序排列
create unique index Scno on SC(Sno ASC,Cno desc);
--删除索引
--select 灵活查询--
/*select * <目标列表达式>
from student <表名>
 where  <条件表达式>
 groub by <列名> 分组查询
 having 条件表达式
 order by <列名> [asc|desc]
*/
select Sno ,Sname from Student
/*插入数据*/
insert into student(Sno,Sname,Ssex,Sage,Sdept)
values('200215129','李阳','男','20','cu')
select * from dbo.Course
select Sname,2004-Sage from Student;--目标列表达式不仅可以是算术表达式，还可以是函数，字符串常量等。
select Sname,'Year of Birth:',2004-Sage,LOWER(Sdept)
            from Student;
select Sname  NAME,'Year of Birth:'  BIRTH,2004-Sage  BIRTHDAY,LOWER(Sdept) DEPARTMENT
            from Student;-----起用别名，可以用 as 或者 直接 空格接别名
---取消重复的行，元组
--两个本来并不完全相同的元组，投影到指定的某些列上后，可能变成相同的行，可以用distinct取消它们
select Sno from SC--从选课表中查询选了课的学生学号
select distinct Sno from SC--distinct 消除重复的行
select all Sno from SC---查询全部，一般为默认
delete Student--删除student
select * from Student
insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5','4')
/*--------------------------------------------------------------------
查询条件        
1  比较       |=,>,<,>=,<=,!=,!>,!<,not+上述比较运算符
2确定范围     |between and ,not between and  值是升序放置1 and 10;
3确定集合     |in ,not in
4字符匹配     |like ,not like
5  空值        |is null, is not null
6多重条件     |and ,or, not
*/------------------------------------------------------------------------
select Sname from Student where Sdept ='cs'
select Sname,Sage from Student where Sage<20
--一个学生有多门课不及格时，他的学号只列一次用distinct--------------------------
select distinct Sno from SC where Grade <60;
select Sname,Ssex from Student where Sdept in ('cs','ma','is')
select * from Student where Sdept not in ('ma','is');
select * from Student where Sno like '200215121'
select * from Student where Sname like '欧阳_'
select * from Student where Sname like '_阳%';
select * from Course where Cname like 'DB\_design'ESCAPE'\';
select * from Course where Cname  like 'DB\_%i__'escape'\';
select Sno ,Cno from SC where Grade is null\
select count(*)from student
select count (distinct Sno) from sc
select avg(grade) from sc where Cno ='1'
select max(Grade) from sc where Cno='1'
select sum(Ccredit) from sc , Course  where Course.Cno='200215121' and sc.Cno=Course.Cno;
--group by----将查询结果按某一列或多列的值分组，值相等的为一组---------------------
select Cno,count(Sno) as count from sc
group by Cno--对各个课程的选课进行统计
having count(*)>2--对各个课程选课大于2个的课程进行统计
--多表联查-------------------------------------------------------------------------
select Student.*,SC.*
from Student ,SC
where Student.Sno=SC.Sno
--自然连接去掉两表重复的列，----------------------------------------------------
--自身连接
select first.Cno,second.Cpno
from Course first,Course second
where first.Cpno=second.Cno
--左外连接保留左边表的所有数据--------------------------------------------------
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from Student
left outer join SC
on Student.Sno=SC.Sno;
--右外连接-----------------------------------------------------------------------
select student.sno,sname ,ssex,sage,sdept,cno,grade
from student
right outer join sc
on (student.sno=sc.sno);
--多表连接---------------------------------------------------------------------
select Student.Sno,Sname,Cname,Grade
from Student,SC,Course
where Student.Sno=SC.Sno and SC.Cno=Course.Cno;
--- 选修了全部课程的学生------------------------------------------------------
select Sname
from student
where not exists
(select * from Course
  where not exists
       (select * from SC
                 where Sno=Student.Sno
                    and Cno= Course.Cno));
----没有选修一号课程的学生
select Sname
from student
where not exists
 (select *
    from sc
       where sno=student.sno and cno='1')
---查询选修了1号课程的所有学生
select *
 from student
   where exists
     (select * from sc
       where sno=student.sno and cno='1' )
---查询每门课程选修的人数且选修课在3门以上的
select cno, count(Sno) as total
from sc
group by cno
having count(*)>3
-----连接查询
--等值与非等值连接查询
--当连接运算符为‘=’时，称为等值连接
--若在等值连接中把目标列中重复的属性列去掉则为：自然连接
--自身连接-----------------------------------
select First.cno,Second.cpno    ----
from course First,course Second ----
where First.cpno=Second.cno     ----
---------------------------------------------
--SQL语言允许多层嵌套查询，子查询的select 语句中不能使用order by子句，order by 只能对最终查询结果排序
------------------------------查询与刘晨在同一个系学习的学生-------------------------------
select Sno ,Sname,Sdept  ----------
from Student             ----------
where Sdept in           ----------
 (select Sdept           ----------
   from Student          ----------
     where Sname='刘晨')  ---------
----用自身连接求解
select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept=s2.sdept and
        s2.sname='刘晨'
--------------------------------------
------------------------------找出每个学生超过他选修课程平均成绩的课程号---------------------------------
--子查询返回单个值可以用比较运算符
select sno ,cno
from sc x
where grade >=(select avg(grade)
       from sc y
         where y.sno=x.sno)
--------------------------------------------------------------------------
--返回多值时要用any,使用any或all 谓词时则必须同时使用比较运算符
---  >  any 大于查询结果中的某个值
---  >  all 大于子查询结果中的所有值
---  <  any 小于子查询结果中的某个值
---  <  all 小于子查询结果中的所有值
---  >= any 大于等于子查询结果中某个值
---  >= all 大于等于子查询结果中所有值
---  <= any 小于等于子查询结果中某个值
---  <= all
--   =  any
---  =  all
---  <> any 不等于查询结果中的某个值
-----------------------------查询其他系比计算机系某一学生年龄小的----------------------------------
select sname,sage
from student
where sage <
(select max(sage)
  from student
       where sdept ='cs')
and sdept <> 'cs'
------------集合查询-----------------------
--集合操作，包括并操作union,交操作intersect,差操作except
--集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
select *
from student
where sdept = 'cs'
union
select *
from student
where sage <='19'
---------------------
select *
from student
where sdept = 'cs'
intersect
select *
from student
where sage <= '19'
--------------------
select *
from student
where sdept = 'cs'
except
select *
from student
where sage <= '19'
------查询即选修了课程1又选修了课程2的学生-----------
select Sno
from SC
where Cno='1'
intersect --也可以用and
select Sno
from SC
where Cno='2'
---------------------------
insert into student
values('200215127','忽忽','女','25','ma','')
insert into sc
values('200215127','1',null)
-----对每个系，求学生的平均年龄-------
----------带子查询的插入语句
create table Dept_age
(Sdept char(15),
   Avg_age smallint);
----将得到的数据保存到数据库
insert into
dbo.Dept_age(sdept,avg_age)
select sdept,avg(sage)
from student
group by sdept;
-----------------------------------------
update sc
set grade=''--设置为0
where 'cs' =
       (select sdept
            from student
            where student.sno=sc.sno);
---------------------------------------------------------------------------------------
--定义视图
--create view <视图名>[(<列名>[，<列名>].....)]
--as <子查询>
--[with check option]
--子查询可以是任意复杂的select 语句，但通常不允许含有order by 子句和distinct短语
--with  check option 表示对视图进行update;insert;delete;操作时要保证更新，插入，删除
--的行满足视图定义中的谓词条件；
--以下情况必须明确指定组成视图的所有列名
--(1)某个目标列不是单纯的属性名，而是聚集函数或列表达式
--(2)多表连接时选出了几个同名列作为视图的字段
--(3)需要在视图中为某个列启用新的更合适的名字
---------------------------------------------------------------------------------------
drop view IS_Student--删除view
create view IS_Student--建立信息系学生的视图
as
select sno,sname,sage
from student
where sdept='is'
with check option
--由于定义在view 上的视图加上了with view option 子句，以后对该视图执行增删改操作时，
--RDBMS会自动加上sdept='is'的条件
--若一个视图是从基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们
--称这类视图为行列子集视图。视图可以建立在多个基本表上,分组视图(带有group by聚集函数的)
--------------------------
--定义一个反映学生出生年份的视图
create view bt_s(sno,sname,sbirth)
as
select sno,sname,2004-sage
from student;
--将学生的学号及它的平均成绩定义一个视图
create view S_G(Sno,Gavg)
as
select Sno,avg(Grade)
from sc
group by Sno
--删除视图----------------------------------------------------------------
--执行查询视图时，首先进行有效性检查，查询是否存在，即视图消解。
--通过基本表和视图表进行联查
--查询平均成绩90分以上的学生学号和平均成绩
select *
from S_G
where Gavg >=90;
select sno, avg(grade) as avg
from sc
group by sno
--where 子句中不能用聚集函数
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90
--------跟新视图------------------------------------------------------------------
--在关系数据库中，并不是所有的视图都是可以跟新的
--一般行列子集视图可以更新，其他大部分不行
--------------------------------------------------------------------------------
--DB2数据库规定：
--若视图是由两个以上的基本表导出，则不允许跟新
--若视图的字段来自字段表达式或常数，只允许执行delete操作，其他不行
--,,来自聚集函数。
-- 含有group by 。
-- 含有distinct 短语。
--视图定义中有嵌套查询，并且内层查询的from 子句中设计的表也是导出该视图的基本表