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)