SQL Serverデータベース基本操作語句まとめ
--sql
--
create database Studets
--
create table student ( sno char(5), sname char(20), ssex char(2), sage smallint, sdept char(15) )
create table course ( cno char(3), cname char(30), cpno char(3), ccredit smallint )
create table sc ( sno char(5), cno char(3), grade int )
--
select * from student select sno as from student select * from course select * from sc
--
--
alter table student add scome datetime
-- alter table student alter column scome char(50)
-- --
alter table student drop column scome
-- drop table student drop table course drop table sc
--
--sno ，ssex ， sage
create table student ( sno char(5) not null unique, sname char(20), sex char(2), sage smallint default 20, sdept char(15), constraint sex check(sex in(' ',' ')), )
-- alter table student drop constraint ssex
-- alter table student add constraint ssex check(sex in(' ',' '))
-- alter table student add constraint PK_SNO primary key(sno) create table course ( cno char(3) not null unique, cname char(30), cpno char(3), ccredit smallint )
-- ，
alter table course add constraint PK_CNO primary key(cno), constraint FK_CPNO foreign key(cpno) REFERENCES sc(cno)
create table sc
(
sno char(5) foreign key references student(sno),
cno char(3) foreign key references course(cno),
grade int,
constraint PK_SC primary key(sno,cno)
)
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [FK__sc__sno__0F975522]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
ALTER TABLE [dbo].[sc] DROP CONSTRAINT [PK_SC]
-- sc ，
alter table sc add constraint PK_SC primary key(sno,cno),
constraint FK_SNO foreign key(sno) references student(sno),
constraint FK_CNO foreign key(cno) references course(cno)
-- 。
--not null 。 ， ， -- ，
create unique index STUsno
on student(sno)
create unique index COUcno
on course(cno)
create unique index SCno
on sc(sno asc,cno desc)
drop index SCno on sc
-- DBCC SHOWCONTIG
-- select * from student
alter table student alter column sno char(10)
insert into student values('10021',' ',' ',20,' ')
insert into student values('10022',' ',' ',18,' ')
insert into student values('10023',' ',' ',20,' ')
insert into student values('10024',' ',' ',18,' ')
insert into student values('10025',' ',' ',20,' ')
insert into student values('10026',' ',' ',19,' ')
insert into student values('10027',' ',' ',24,' ')
select * from course insert into course values('001',' ','005',4)
insert into course values('002',' ','',2)
insert into course values('003',' ','001',4)
insert into course values('004',' ','006',2)
insert into course values('005',' ','007',3)
insert into course values('006',' ','',2)
insert into course values('007','C ','006',5)
select * from sc insert into sc values('10021','002',100)
insert into sc values('10021','001',88)
insert into sc values('10021','006',100)
insert into sc values('10021','007',68)
insert into sc values('10022','002',100)
insert into sc values('10023','005',30)
insert into sc values('10024','002',100)
insert into sc values('10024','006',56)
select * from student --
-- select * from student select * from course select * from sc
-- select distinct sno from sc
--
select sname as ' ',2013-sage as ' ' from student
select sname,' ',2013-sage from student
select =sname, =2013-sage from student
--
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit<5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--
select * from student where sname like ' __'
select * from student where sname like '_ __'
select * from student where sname like '% %'
--
select grade*(1+0.2) as ,grade/(10) as from sc
--
select COUNT(*) as from student
select COUNT(distinct sno) as ' ' from sc
select AVG(grade) as ' ' from sc where sno='10021'
select MAX(grade) as 'MAX ' from sc where sno='10021'
select MIN(grade) as 'MIN ' from sc where sno='10021'
select SUM(grade) as ' ' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as ' ' from sc where sno='10021'
select SUM(grade) as ' ' from sc group by sno having sum(grade)>100 -
- 、
--
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--
select distinct A.*,B.* from student A,sc B where A.sno=B.sno
select B.sname as ' ' from student A,student B where A.sname=' ' and A.sdept=B.sdept
--
select A.*,B.* from student A left join sc B on A.sno=B.sno
select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
--
select * from sc select * from course
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade<60) group by sname
--
select * from student where sage>(select AVG(sage) from student)
--
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--
grant select on student to bnc
select * from student
revoke select on student from bnc
--
create view VIEW_STUGrade( , , , )
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept=' '
--
select * from VIEW_STUGrade
--
alter view VIEW_STUGrade( , , , )
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept=' '
with check option
--
--
update VIEW_STUGrade set =' ' where ='10022' select * from student where sno='10022'
/* 1, ： a, 2， a b c d distinct */
-- drop view VIEW_STUGrade
-- sql
-- 1,int 2,smallint 3,tinyint (0--255) 4,bigint 5char <800. ： ， 6，varchar 800 7,text 2GB 8，nvarchar1--4000 */
--
select GETDATE()-1 ,GETDATE() ,GETDATE()+1
select 59&12
select 59|12
select 59^12
--
select * from student where sname like '% %'
select * from student where sno like '1002[5-9]'
--
declare @name char(10) set @name=' '
print @name
-- ， select @name
--
declare @a nvarchar(50),@b nvarchar(50)
set @a=33 set @b=34 --- select@a=33，@b=34
if @a>@b
print ' ：'+@a
else
print ' ：'+@b
--waitfor
waitfor delay '00:00:04' print ' 4 '
waitfor time '17:45:50' print ' '
--
CREATE FUNCTION GetTime ( @date1 datetime, @date2 datetime )
RETURNS TABLE
AS RETURN (
select datediff(dd,@date1,@date2) ,datediff(mm,@date1,@date2) , datediff(yy,@date1,@date2)
)
-- ，
--
GO create proc [dbo].[sel] (
@sno char(10)
)
as
select * from student where sno=@sno
exec sel @sno='10021'
--
GO create proc sel2
as
select * from student
exec sel2
--
GO create proc updat @sno char(10), @sex char(2)
as
update student set sex=@sex where sno=@sno
select * from student exec updat @sno='10021', @sex=' '
--
GO create proc dele @sno char(10)
as
delete student where sno=@sno
select * from student
exec dele @sno='10029'
--
GO create proc inser @sno char(10), @sname char(20), @sex char(2), @sage smallint, @sdept char(15)
as
insert into student values(@sno,@sname,@sex,@sage,@sdept)
exec inser @sno='10029', @sname='tom', @sex=' ', @sage=100, @sdept='sc' select * from student
--
--
select * from student select * from course select * from sc
-- select distinct sno from sc
--
select sname as ' ',2013-sage as ' ' from student
select sname,' ',2013-sage from student
select =sname, =2013-sage from student
--
select * from course where ccredit>3
select * from course where ccredit between 2 and 5
select * from course where ccredit> 2 and ccredit<5
select * from course where ccredit in(2)
select * from course where ccredit not in(2)
--
select * from student where sname like ' __'
select * from student where sname like '_ __'
select * from student where sname like '% %'
--
select grade*(1+0.2) as ,grade/(10) as from sc
--
select COUNT(*) as from student
select COUNT(distinct sno) as ' ' from sc select AVG(grade) as ' ' from sc where sno='10021'
select MAX(grade) as 'MAX ' from sc where sno='10021'
select MIN(grade) as 'MIN ' from sc where sno='10021'
select SUM(grade) as ' ' from sc where sno='10021'
select SUM(grade)/COUNT(grade) as ' ' from sc where sno='10021'
select SUM(grade) as ' ' from sc group by sno having sum(grade)>100
-- 、 --
select distinct student.*,sc.* from student,sc where student.sno=sc.sno
--
select distinct A.*,B.* from student A,sc B where A.sno=B.sno select B.sname as ' ' from student A,student B where A.sname=' ' and A.sdept=B.sdept
--
select A.*,B.* from student A left join sc B on A.sno=B.sno select A.*,B.* from student A right join sc B on A.sno=B.sno
select A.*,B.* from student A FULL join sc B on A.sno=B.sno
-
select distinct A.*,B.* from student A,sc B where A.sno=B.sno and B.grade>99 and B.cno='002'
select distinct A.*,B.*,C.* from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and B.grade>99 and B.cno='002'
--
select sname+sno from student
select distinct sname from student ,sc where student.sno=sc.sno
select sname from student ,sc where student.sno=sc.sno and student.sno not in (select sno from sc where grade<60) group by sname
--
select * from student where sage>(select AVG(sage) from student)
--
select * from student where exists(select * from sc where sno=student.sno)
select * from student where not exists(select * from sc where sno=student.sno)
--sql
sys.sp_addlogin bnc,bnc,Studets sp_adduser bnc,bnc
--
grant select on student to bnc
select * from student
revoke select on student from bnc
--
create view VIEW_STUGrade( , , , )
as
select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept=' '
--
select * from VIEW_STUGrade
--
alter view VIEW_STUGrade( , , , ) as select student.sno,student.sname,course.cname,sc.grade from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and student.sdept=' '
with check option
-- --
update VIEW_STUGrade set =' ' where ='10022' select * from student where sno='10022'
/* 1, ： a, 2， a b c d distinct */
-- drop view VIEW_STUGrade
--
use Studets
GO create trigger insert_Tri
ON student after
insert as print ' ！'
GO create trigger update_Tri
on student after
update as print ' ！'
GO create trigger delete_Tri
on student after
delete as print ' ！'
--
GO alter trigger delete_Tri
on student after delete
as
if ' ' in (select sname from deleted)
print ' ！'
rollback transaction
--
exec sel @sno='10021'
exec inser @sno='10029', @sname=' ', @sex=' ', @sage=25, @sdept=' '
exec updat @sno='10029', @sex=' '
exec dele @sno='10029'
-- , ,
/* sp_*+
sp_helptext： sp_help: ， ， ， ， sp_depends： sp_helptrigger： */ sp_help delete_Tri
sp_helptext delete_Tri
sp_depends delete_Tri
sp_helptrigger student
--
drop trigger delete_Tri