基本sql语句及细节记录

基本sql语句及细节记录

空了可以慢慢看

use  Test
go
--创建基本表
--实体完整性
create table student(
  sno char(9) primary key,   --列级实体完整性
  sname char(20) not null,
  sex char(2),
  sage smallint,
  sdept char(20)
);

create table sc(
  sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key(sno,cno)   --表级实体完整性
);
--实体完整性约束
--检查主码值是否唯一，如果不唯一则拒绝插入或修改
--检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--参照完整性
create table course(
   cno char(4) primary key
);

create table sc1(
  sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key(sno,cno),                       --表级实体完整性
  foreign key(sno) references student(sno),        --表记参照完整性
  foreign key(cno) references course(cno)          --表记参照完整性
);

--  被参照表                    参照表                        违约处理
--  破环参照完整性              插入元组                       拒绝 
--  破坏参照完整性              修改外码值                     拒绝
--  删除元组                    破坏                           拒绝/级联删除/设置为空值          拒绝为默认策略                
--  修改主码值                  破坏                           拒绝/级联删除/设置为空值

--除了定义主码，还应定义外码值可否为空值

create table sc2(
  sno char(9) not null,
  cno char(4) not null,
  grade smallint,
  primary key(sno,cno),
  foreign key(sno) references student(sno)
      on delete cascade
	  on update cascade,
  foreign key(cno) references course(cno)
      on delete cascade
	  on update cascade
);

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--用户定义的完整性

--1.属性上约束条件的定义
  --列值非空  not null
  --列值唯一  unique         eg：Dname char(9) unique
  --检查列值是否满足一个条件表达式  check     eg:  sex char(2) check(sex in('男'，'女'))
  --违约处理：当插入元组或修改属性的值时，若不满足约束，则条件被拒绝执行
--2.元组上约束条件的定义
  create table student1(
     sno char(9) primary key,
	 sname char(8) not null,
	 sex char(2),
	 check(sex = '男' or sname not like 'Ms.%')
  );
  --违约处理：如不满足，则拒绝执行
 --完整性约束命名子句
create table student2(
   sno numeric(6)
   constraint c1 check(sno between 90000 and 99999),
   sname char(20)
   constraint c2 not null,
   sage numeric(3)
   constraint c3 check(sage < 30),
   constraint studentKey primary key(sno)
);
  --eg:constraint courseKey foreign key(cno) references course(cno)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--基本表的修改
--修改表中的完整性限制
  alter table student2
      drop constraint c2;
  alter table student2           --先删除某约束，再添加约束，相当于修改约束
      drop constraint c3;
  alter table student2
      add constraint c3 check(sage < 40);
  alter table student2
      add constraint c4 unique(sname);
--向表中添加列
  alter table student2
      add sdept int;
--修改表中的列
  alter table studnet2
      alter column sname char(15);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--删除基本表
  drop table student1 restrict;     --默认情况是restrict，这种情况下，该表的删除是有限制条件的，欲删除的基本表不能被其他表的约束所引用（如check，foreign key等），不能有视图，不能有触发器，不能有存储过程或函数等
  drop table student2 cascade;
  --此处报错应该是系统不允许级联删除基本表操作


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--数据更新
  --插入数据
  insert into student(sno,sname) values('99991','张彦');    --系统将自动地将新插入记录sage列的值赋为null     
  --insert into Dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept;
  --修改数据
  update student 
  set sage = 32
  where sage is null;

  update sc
  set grade = 0
  where sno in(
     select sno
	 from student
	 where sdept = 'CS'
  );
  --删除数据
  delete
  from student 
  where sno = '20191180571';

  delete 
  from student
  where sno in(
     select sno
	 from student
	 where sdept = 'CS'
  );

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--数据查询
  select 2021-sage birthday    --表达式的重命名
  from student
  where sdept = 'CS';

  select sno from sc;  --由于sno不是sc表的主码，因此会出现重复行   <=> select all sno from sc
  select distinct sno from sc;    --distinct关键词可消除重复行

  select * from student where sname like '霍%'    --%代表任意长度的字符串，长度可为零
  select * from student where sname like '霍_'    --_代表任意单个字符      
  --若要查询字符串中本身含有通配符，使用转义符\_   \%

  --order by    order by不能用于子查询，只能对最终查询结果进行排序
  select sno,grade
  from sc
  where cno = '3'
  order by grade desc    --  asc升序   desc降序    对于空值的排序，不同系统处理不同，一般堆积在一端，保持一致即可
  --group by   为了细化聚集函数的作用对象
  select sno    --eg:查询选修了三门以上课程的学生学号
  from sc
  group by sno
  having count(*) > 3
  --常见聚集函数：count(*),count(列名)，sum(列名),avg(列名),max(列名),min(列名)
  --当聚集函数遇到空值，除了count(*),都跳过空值而处理非空值
  --聚集函数只能用于select和group by子句中，不能用于where子句   ！！！！！！！！！！！！！！
  --eg:查询选修了课程的学生人数
   select count(distinct sno) from sc;   --必须加distinct，因为一个学生选修的课程不止一门

   --自身连接查询
     --eg：查询每门课的间接先修课
	 --select first.cno,second.cpno
	 --from course first,course second
	 --where first.cpno = second.cno
   
   --外连接
   --select * 
   --from student left outer join sc on(student.sno = sc.sno);

   --相关子查询：子查询的查询条件依赖于父查询
   --eg：找出每个学生超过他自己选修课平均成绩的课程号
   select sno,cno
   from sc x
   where grade >= (
      select avg(grade)
	  from sc y
	  where y.sno = x.sno
   );

   ---any 和 all
   --小于任意一个   sage<any(子查询);
   --小于所有       sage<all(子查询)；

   --带有exists谓词的子查询  带有exists子查询不返回任何数据，只产生逻辑真值和逻辑假值
   --查询所有选修了一号课程的学生姓名
   select sname
   from student
   where exists(select * from sc where sc.sno = student.sno and sc.cno = '1');

  --集合查询  
   --并  union
   --交  intersect  
   --差  except
  
  --基于派生表的查询
    select sname
	from student,(select sno from sc where cno = '1') as sc1
	where student.sno = sc1.sno;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--索引   
    --unique  表明此索引的索引值只对应唯一的数据记录    eg：create unique index stusno on student(sno)  按学号升序建立唯一索引
	--cluster 表明要建立的索引是聚簇索引
    --数据字典：关系数据库管理系统内部的一组系统表，它记录了数据库中所有定义的信息
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--视图    虚表 不存在于物理介质中

  --视图创建    视图可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上
    create view s_g(sno,gavg)
	as
	select sno,avg(grade)
	from sc
	group by sno;
    --with check option  以后对该视图进行更新时，关系数据库管理系统会自动加上创建视图时where子句中的条件
  --查询视图  同对基本表的查询      有一个 ！！视图消解！！ 的过程    即转化为对相应基本表的查询
  --更新视图  同对基本表的更新      系统内部最后转化为对基本表的更新<并不是所有视图都可以进行更新的，有些对视图的更新不能唯一的有意义的转化为对相应基本表的更新>
                                    --一般地，行列子集视图（从单个基本表导出的，并且只是去掉了某些基本表的行或者列）是可以更新
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------