SQL server

--use TESTTWO
/*go
select *,
备注=case
when tb_Grade >= 90 then '成绩优秀'
when tb_Grade >= 80 then '成绩良好'
when  tb_Grade >= 70 then '成绩及格'
else '不及格'
end
from tb_Student
*/

/*
declare @n int, @sum int
select @n= 0, @sum= 0
while @n <= 10
begin
set @n= @n+ 1
if @n%2= 1
	continue
set @sum= @sum + @n
end
print @sum
*/

/*
declare @x int
set @x= 3
loving:
waitfor delay '00:00:01'
print @x
select @x= @x+ 1 
while @x> 0 goto loving
*/

--dbcc checkalloc ('TESTTWO')检查指定数据库的磁盘空间分配结构的一致性

--checkpoint 将更改的数据页或日志页强制写入硬盘

--backup database TESTTWO to disk='C:\Users\Monkey\Desktop\backup.bak'备份数据库


--restore database TESTTWO from disk='C:\Users\Monkey\Desktop\backup.bak' 恢复数据库

/*
select *,
备注= 1
from tb_Student
*/

/*
declare @x int
select @x= tb_Grade
from tb_Student
print @x
*/


/*应该用这个()而不是{} && （）里面最后一句语句不应该加,
create database MRKJ ON PRIMARY
(
NAME= MRKJ_data,
FILEname= 'C:\Users\Monkey\Desktop\MRKJ.mdf',
Size= 5MB,
Maxsize= UNLIMITED,
Filegrowth= 10%
)
LOG ON
(
Name= MRKJ_log,
Filename= 'C:\Users\Monkey\Desktop\MRKJ.ldf',
Size= 3MB,
Maxsize= 50MB,
Filegrowth= 2MB
)
*/


/*
create database MingRi on PRIMARY
(
name= MingRi1,
filename= 'E:\SQL文件\MingRi1.mdf',
size= 100MB,
maxsize= unlimited,
filegrowth= 10MB	
),
(
name= MingRi2,
filename= 'E:\SQL文件\MingRi2.ndf',
size= 80MB,
maxsize= 200MB,
filegrowth= 5MB	
),
(
name= MingRi3,
filename= 'E:\SQL文件\MingRi3.ndf',
size= 50MB,
maxsize= 200MB,
filegrowth= 10%
)
log on
(
name= MingRiLog1,
filename= 'E:\SQL文件\MingRiLog1.ldf',
size= 30MB,
maxsize= 50MB,
filegrowth= 1MB
),
(
name= MingRiLog2,
filename= 'E:\SQL文件\MingRiLog2.ldf',
size= 30MB,
maxsize= 50MB,
filegrowth= 1MB
)
*/

/*
alter database MingRi
add file
(
name= mrkj,
filename= 'E:\SQL文件\mrkj.ndf',
size= 10MB,
maxsize= 100MB,
filegrowth= 2MB
)
*/

/*修改文件的大小只能把文件改大
alter database MingRi
modify file
(
name= mrkj,
size= 100MB
)
*/

--exec sp_renamedb 'MingR', 'MingRi' 修改数据库名称

--DROP database MingRi 删除数据库


 /*建表时constraint+约束名称+约束
create table [TEST].[dbo].[tb_Student2]
(
[ID][int] NULL,
[NAME][nvarchar](50)NULL,
[Sex][char](2),
[AGE][int]NULL,	
[备注][int]CONSTRAINT  Y_B unique 
)
*/

/*创建，修改和删除约束
alter table tb_Student2
alter column ID int not null

增加主键约束
alter table tb_Student2
add constraint p_x primary key(ID)

删除主键约束
alter table tb_Student2
drop constraint p_x
唯一约束，检查约束，默认约束同理

默认约束
alter table tb_Student2
add constraint def_sex 
default '女' for Sex

外键约束
alter table tb_Student2
add constraint FK_ID foreign key(备注)
	references tb_Student(备注)


alter table tb_Student2
drop constraint FK_ID 
*/

/* alter修改表
alter table tb_Student2
ADD Sex char(2)

alter table tb_Student2
alter column Sex char(50)

删除表
drop table tb_Student2
*/

--insert into tb_Student (ID,NAME,AGE) VALUES(1,'杨何强',12)
--insert into tb_Student values (2,'李明',13)
--update tb_Student set NAME='陈捷' where ID= 1
--delete tb_Student where NAME='陈捷'

/*
create view view_Stu
as
select * from tb_Student


创建绑定到架构的视图,此时SELECT语句中不允许使用*,对象名要写两部分，例如dbo.tb_Student
create view view1
with schemabinding
AS
select ID,name,AGE from dbo.tb_Student

创建绑定架构的视图后,不能修改与该视图相关的表,例如下面操作不能执行
alter table tb_Student
drop column ID 
*/

/*
创建带with check option的视图
create view view2
as
select * from tb_Student where ID= 2
with check option

view2为带with check option的视图，向该视图添加记录时ID必须为2，例如下面操作不能执行
insert into view2 values(1,'cc',2)
*/

--exec sp_rename 'view1', 'view_1'  修改视图名称，同数据库名称修改

--exec sp_rename 'view_1.[name]', '姓名', 'column' 修改视图某一列的名称

--drop view view_1 删除视图

/*向视图插入,修改,删除数据时，会把数据写入表中
insert into viee1 values(3,'杨何强',14)
insert into view1 (ID,name,age) values(3,'杨何强',14)
update view1 set age= 10 where ID= 3
delete from view1 where ID=3
*/