SQL 进阶学习之三

reate  database  SCR

use  scr
go
--

create   table  s
(
sid  int   identity ( 1000 , 5 )  primary   key ,
sname  varchar ( 50 )   null ,
sage  int    null ,
ssex  char ( 2 )  check (ssex = ' 男 '   or  ssex = ' 女 ' )
)

create   table  c
(
cid  int   identity  ( 10000 , 100 ) ,
cname  varchar ( 50 )  null ,
cteacher  varchar ( 50 )  null ,
primary   key  (cid)
)

create   table  sc
(
sid  int  ,
cid  int  ,
cgrade  int    null   check ( between   0   and   100 ),
foreign   key  (sid)  references  s(sid),
foreign   key   (cid)  references  c(cid)
)


insert   into  C
(cname,cteacher)
values ( ' C# ' , ' Teacher Liu ' )

insert   into  C
(cname,cteacher)
values ( ' ASP ' , ' Teacher  ' )

select   top   1   *   from  C

select   *   from  C

update  C
set  cname  =   ' SQL '
where  cid  =   10000


delete   top ( 1 )  C

drop   table  C


drop   database  SCR



truncate    table  C



insert   into  s
(sname ,sage ,ssex)
values ( ' 宋斌 ' , 23 , ' 男 ' )

select   *   from  S
select   *   from  C
select   *   from  sc

delete   top ( 1 ) S


UPDATE  C
SET  CTEACHER  =   ' TEACHER5 '
WHERE  CID  =   10800  
UPDATE  C
SET  CTEACHER  =   ' TEACHER6 '
WHERE  CID  =   10900  
UPDATE  C
SET  CTEACHER  =   ' TEACHER8 '
WHERE  CID  =   11100  




--  检索出课程号为10700的学生的姓名和学号
--  1 ）join
  select  s.sname,s.sid
from  s  join  sc
on  s.sid  =  sc.sid
where  sc.cid  =   10700

--  联接查询  相当于一个 FULL JOIN
select  s.sname,s.sid
from  s ,sc
where  s.sid  =  sc.sid
and  sc.cid  =   10700

--  2）select 嵌套查询或子查询
select  sname,sid
from  s
where  sid  =
(
select  sid
from  sc
where  cid  =   10700
)


select  sname,sid
from  s
where  sid  in   -- in 结构为：（集合1） in  （集合2） 表示集合1中的每个元素均在集合2中
(
select  sid
from  sc
where  cid  =   10700
)

select  sname,sid
from  s
where   10700   in
(
select  cid
from  sc
where  sid  =  s.sid
)

select  sname,sid
from  s
where   10700   =   all
(
select  cid
from  sc
where  sid  =  s.sid
)



select  sname,sid
from  s
where   exists
(
select  sid
from  sc
where  cid  =   10700
and  sid  =  s.sid
)


select  sname,sid
from  s
where   not   exists
(
select  sid
from  sc
where  cid  =   10700
and  sid  =  s.sid
)



SELECT  SNAME,SID
FROM  S
WHERE  SID  <>  
(
SELECT  SID
FROM  SC
WHERE  CID  =   10700
)




--  检索选修课程名为 SQL 的学生的学号和姓名
select  sid,sname
from  s
where  sid  in
    (
         select  sid
         from  sc
         where  cid  in
        (
             select  cid
             from  c
             where  cname  =   ' SQL '
        )
    )

select  s.sid ,s.sname
from  s,c,sc
where  s.sid  =  sc.sid  and  c.cid  =  sc.cid
and  c.cname  =   ' sql '

select   *
from  s,c,sc

--  检索任课教师为 teacher liu 的学生的学号和姓名


--  检索不学 C# 课程的学生的姓名和年龄
select  sname,sage
from  s
where  sid  not   in
(
     select  sid
     from  sc
     where  cid  not   in
    (
         select  cid
         from  c
         where  cname  =   ' C# '
    )
)


--  检索学习全部课程的学生姓名和性别
select  sname ,ssex
from  s
where   not   exists
(
     select   *
     from  c
     where   not   exists
    (
         select   *
         from  sc
         where  sc.sid  =  s.sid
         and  sc.cid  =  c.cid
    )
)


CREATE   VIEW   [  schema_name .  ]  view_name  [  (column [ ,...n  ]  ) ] 
[  WITH <view_attribute> [ ,...n  ]  ] 
AS  select_statement  [  ;  ]
[  WITH CHECK OPTION  ]  

< view_attribute >  :: =  
{
     [  ENCRYPTION  ]
     [  SCHEMABINDING  ]
     [  VIEW_METADATA  ]      }


create   view  视图名 （列名） 
as
select  查询语句 


select  s.sid ,s.sname
from  s,c,sc
where  s.sid  =  sc.sid  and  c.cid  =  sc.cid
and  c.cname  =   ' sql '

create   view  scrview
(sid,cid,sname,cname,cgrade)
as  
select  s.sid,c.cid,s.sname,c.cname,sc.cgrade 
from  s,sc,c
where  s.sid  =  sc.sid
and  c.cid  =  sc.cid

select  sid,sname  from  scrview 
where  cname  =   ' sql '


drop   view  scrview


create   view  scrview
(sid,sname)
as  
select  sid,sname
from  s
where  sid  in
    (
         select  sid
         from  sc
         where  cid  in
        (
             select  cid
             from  c
             where  cname  =   ' SQL '
        )
    )

select   *   from  scrview

--
==  索引的语法
CREATE   [  UNIQUE  ]   INDEX  index_name 
     ON   < object >  ( column_name  [  ASC | DESC  ]   [  ,...n  ]  ) 


create   [ unique ]   index  索引名  on  基本表名 （列名）

--
create   index  sidindex
on  s(sid)


create   unique   index  sname
on  s(sname)

select   *   from  s

insert  s
(sname)
values ( ' lize ' )

drop   index  表名.索引名
drop   index   s.sidindex


ALTER   TABLE  table_name{  [  ALTER COLUMN column_name    {DROP DEFAULT    | SET DEFAULT constant_expression    | IDENTITY [ ( seed , increment )  ]    }  |   ADD     {  <  column_definition  >   |   <  table_constraint  >  }  [  ,...n  ]   |   DROP     {  [  CONSTRAINT  ]  constraint_name     |   COLUMN   column  }] } <  column_definition  >  :: =     { column_name data_type }     [  [ DEFAULT constant_expression  ]         |   IDENTITY   [  ( seed , increment )  ]     ]     [ ROWGUIDCOL ]     [  < column_constraint >  ]   [  ...n  ]  ] <  column_constraint  >  :: =      [  NULL | NOT NULL  ]      [  CONSTRAINT constraint_name  ]     {        |  {  PRIMARY   KEY   |   UNIQUE  }        |   REFERENCES  ref_table  [  (ref_column)  ]         [  ON DELETE { CASCADE | NO ACTION }  ]         [  ON UPDATE { CASCADE | NO ACTION }  ]     } <  table_constraint  >  :: =      [  CONSTRAINT constraint_name  ]     {  [  { PRIMARY KEY | UNIQUE }       { ( column [ ,...n  ]  ) }        |   FOREIGN   KEY          (  column   [  ,...n  ]  )         REFERENCES  ref_table  [  (ref_column [ ,...n  ]  ) ]        [  ON DELETE { CASCADE | NO ACTION }  ]         [  ON UPDATE { CASCADE | NO ACTION }  ]     }

--  给一个表增加一个新列
alter   table  基本表名 
add  列名 类型  [ 约束 ]  

--  给一个表删除列
alter   table  基本表名 
drop  列名  [ cascade/restrict ]
--
alter   table  s
add  saddr  nvarchar ( 100 )

select   *    from  s


alter   table  s
drop   column  saddr

cascade


--  ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
alter   table  c
alter   column  cname  nvarchar ( 100 )