盛日辉的博客
0%

数据库

发表于 分类于

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
/*
例2-2：创建一个Student数据库，
该数据库的主文件逻辑名称为Student_data，
物理文件名为Student.mdf，初始大小为10MB，
最大尺寸为无限大，增长速度为10%；
数据库的日志文件逻辑名称为Student_log，
物理文件名为Student.ldf，初始大小为1MB，
最大尺寸为100MB，增长速度为1MB。 
*/

Create database Student
on
primary
(
	name = Student_data,   --逻辑文件名
	filename = 'E:\Student.mdf',  --物理文件名
	size = 10MB,  --初始大小
	filegrowth = 10%, --增长速度
	MaxSize = UNLIMITED  --最大尺寸
)
Log on 
(
	name = Student_log,
	filename = 'E:\Student.ldf', 
	size = 1MB,
	filegrowth = 1MB,
	maxsize = 100MB
)

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use Student
create table Student
(
	Stu_id char(8) primary key, --字符型  主键约束
	Stu_name varchar(8) not null, --字符型 非空约束
	Stu_age tinyint,				--整型
	Stu_sex char(2) not null constraint sex check(Stu_sex in ('男','女')),
	     -- 字符型  非空约束  约束/限制  约束名字   检查Stu_sex 是不是（）里的值
	Score decimal(5,2),  --浮点型
	College char(50) default '信息工程学院',  --默认约束
	Tel varchar(11) unique check(len(Tel)=11),
	  --  字符型   唯一性约束 检查约束长度是否为11
	Birthday datetime ,--日期型
	
)

创建表并修改列的相关信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
--创建数据库

create database Garage

--创建表
use Garage
create table householder  --户主信息表
(
	h_name char(20), --户主姓名
	h_house_id char(11),   ---房号
	h_id varchar(18) constraint ID_card check(len(h_id)=18), --身份证号码
	h_age tinyint, --年龄
	h_tel char(11) constraint tel check(len(h_tel)=11), --电话号码
	h_car_num tinyint ,  --拥有的车辆数量
	p_id varchar(4) unique,  --车位号
	birthday datetime --户主生日
)
create table Parkong_lot  --车位信息
(
	p_id varchar(4)  ,  --车位号
	h_id char(11), --哪一户的车位
	p_length float,--车位长
	p_width float,   --车位宽
	car_id varchar(8)    --车牌号
)



--增加列和删除列
--use Garage
alter table Parkong_lot 
add car_id_2 varchar(9),car_id_3 varchar(9)
exec sp_help Parkong_lot --表名！
alter table householder 
drop column h_car_num
alter table Parkong_lot drop column p_length,p_width
alter table Parkong_lot add P_type int 


--修改列数据类型
alter table Parkong_lot alter column car_id_2 varchar(8)
alter table Parkong_lot alter column car_id_3 varchar(8)
exec sp_help Parkong_lot


--添加约束
alter table Parkong_lot alter column p_id varchar(4) not null
alter table Parkong_lot add constraint pk_lotid primary key(p_id)


--添加带约束的列
/*
alter table householder
add constraint h_pk primary key(aaa)

alter table householder
drop constraint h_pk
*/

--添加检查约束
alter table householder
add constraint h_h_id check(len(h_house_id)=11)
--删除约束
alter table householder
drop constraint h_h_id
--添加唯一性约束
alter table householder
add constraint h_id_unique unique(h_house_id)

alter table householder
alter column h_house_id char(9) 

alter table householder
drop constraint h_id_unique

总结一点

  • 添加列
    1
    2
    3
    alter table 表名
    add 列名1 数据类型1,列名2 数据类型2
    exec sp_help 表名！
    add后面没有colunm
  • 删除列
    1
    drop column 列名
  • 修改列数据类型
    1
    2
    alter table 表名 alter column 列名 新数据类型
    exec sp_help Parkong_lot
  • 添加主键约束
    1
    2
    alter table Parkong_lot alter column p_id varchar(4) not null
    alter table Parkong_lot add constraint pk_lotid primary key(p_id)
    要先变成not null才能再变成主键。
  • 添加检查约束/唯一性约束
    1
    2
    3
    alter table 表名
    add constraint h_h_id（约束名字） check(len(h_house_id)=11)
    								约束类型check/unique（对那个列约束）
  • 删除约束
    1
    2
    alter table householder
    drop constraint h_h_id

查询

1
2
3
4
5
select ... as ...
from ...
where ...
order by ... 
group by ...

top

1
2
select top 10
select top 10 percent

like 语句

  • % 0-n个字符
  • _ 单个字符
  • [ ] 在方括号里列出的任意字符
  • 任意没有在方括号内出现的字符

    order by

    1
    2
    order by ... desc--倒序
    order by ... asc --正序（默认）
    二次排序，中间逗号隔开

一些函数

获取当前时间getdate()
year() month() day()某个时间的年、月、日

快速建表

1
2
3
4
5
6
7
8
9
10
/*** 新建表 ***/
/*select *
into st 
from SubjectTable
where 1=2
*/

select *
into teacher_new
from Teacher

用where 1=2 就创建了一个没有内容但结构一样的表。

建表的时候比避免重名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
if exists(select [TABLE_NAME]
	from [INFORMATION_SCHEMA].[TABLES]
	where [TABLE_NAME]='place'
)
drop table place
go
select 上课校区
	,上课楼宇
	,上课教室
into place
from SubjectTable
group by 上课校区
	,上课楼宇
	,上课教室

插入

insert into 表名(字段1,…) 内容
内容可以是：values(……) 也可以是select的结果。

更新

1
2
3
update 表
set 字段=。。
where ...

删除

1
2
3
delete
from 表
where 。。。没有就是删除全部

视图

创建带检查的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
if exists(select * 
		from [INFORMATION_SCHEMA].[VIEWS] 
		where [TABLE_NAME]='st_200611')
drop view st_200611
go
create view st_200611
as 
select 上课班级
	,课程名
	,教师姓名
	,学期
from SubjectTable
where 上课班级='200611'
with check option

插入更新删除

如果有检查，插入的数据必须符合建立视图时候的where条件。
如果没有检查，插入了不符合视图条件的数据，源表能插入，但视图里仍然没有。
更新删除了视图中的数据，源表也会同步。
删除有些不一样：

1
2
delete 视图
where。。。

加密

1
2
create view st_200611
with ENCRYPTION

加入 with encryption
再去[INFORMATION_SCHEMA].[VIEWS]中[VIEW_DEFINITION]中查看
没有一些代码。

数据库编程

1
2
declare @xx int
set @xx=..

while
if
print

case

1
2
3
4
5
性别 = case [Stu_sex]
when '男' then '男生'
when '女' then '女生'
end
      ,....
1
2
3
4
5
6
7
,[Score等级] = case 
when [Score]>=90 and [Score]<=100 then 'A'
when [Score]>=80 and [Score]<90 then 'B'
when [Score]>=70 and [Score]<80 then 'C'
when [Score]>=60 and [Score]<70 then 'D'
when [Score]<60 then 'E'
end

游标

1
2
3
4
5
6
7
8
9
10
11
12
declare cur cursor scroll   --声明一个游标，名字是cur，scroll表示可以往前往后，对应的是FORWARD_ONLY 只能往前
for select * from Student -- for数据范围
open cur   --打开游标
fetch first from cur  --提取第一个  fetch ... from  游标
fetch last from cur  --提取最后一个
fetch absolute 2 from cur  --提取全部的第2个
fetch prior from cur  --当前的上一个
fetch next from cur   --当前的下一个
fetch relative -1 from cur    --当前的第-1个

close cur  --关闭游标
deallocate cur  --释放游标

实现遍历输出名字：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare cur3 cursor scroll
for select Stu_name from Student
declare @num int
set @num=(select count(*) from Student)
declare @i int
set @i=1
declare @name varchar(8)
open cur3
while @i<=@num
begin
	fetch absolute @i from cur3 into @name
	print @name
	set @i=@i+1
end
close cur3
deallocate cur3

存储过程

基本

1
2
3
Create Procedure  过程名
As
   代码

例如：

1
2
3
4
Create  Procedure   od_sp      --存储过程名
As  
   Select *  from   orders  
   Where   orderid<10250

调用：
1
exec od_sp

带默认值参数

1
2
3
4
create procedure s_age @a int
as
	select * from Student
	where Stu_age<@a

调用：

1
exec s_age 20

带默认值参数

先看ppt的写法

1
2
3
4
5
6
7
8
9
10
11
12
Create  Procedure    CheckFreight     @result   int  =   null
As
     IF    @result  IS  NULL
         Begin
           select   *   from  [order  details]
           where Freight < 30
         End
     Else
          Begin
             select   *    from  [order details]
             where   Freight <  @result
           End

再是我的写法：
1
2
3
4
5
6
7
create procedure s_age2 @a int=null
as
	select * from Student
	where Stu_age<case @a
					when null then 20
					else @a
					end

调用：
1
2
exec s_age2      -- 使用默认参数
exec s_age2  19  -- 设定参数

带输出的参数

1
2
3
4
5
6
create procedure s_age3 @a int ,@sum int output
as
	select * from Student
	where Stu_age< @a
	set @sum=(select count(*) from Student
	where Stu_age< @a)

调用：

1
2
3
declare @s int
exec s_age3 20,@s output
print ...--打印需要的信息

在调用的时候，得先声明变量，并且exec的时候也要加上output。

触发器

概念和说明

一、定义
由于对表的修改操作而触发执行的一段预先定义的一段程序。
二、种类
对任一表而言：insert , update , delete
三、Inserted表和Deleted表
为每个触发器创建，与表结构一致，存于内存，用户不能不能修改，可以查询。
Deleted表:存放执行Delete和Update而从表中删除的行。
Inserted表:存放执行Inserte和Update而向表中插入的行。

Delete触发器

1
2
3
4
5
create trigger stu_delete
	on Student
	after delete
as
	select * from deleted

注意最后是from deleted
在有删除操作的时候会自动执行，显示删除掉的数据。

Update触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
create trigger stu_update
	on Student
	after update
as
	if update(Stu_id)
    begin
        raiserror('数据不能被修改',10,1)
        rollback  transaction
	end
	else
	begin
		select * from inserted
	end

在更新数据的额时候会触发

insert触发器

1
2
3
4
5
create trigger stu_insert
	on Student
	after insert
as
	select * from inserted

每次操作都对某个数据更新

1
2
3
4
5
6
7
8
9
10
11
12
13
create trigger stu_srh
	on Student
	after insert,delete,update
as
	update Student
	set Score=Score+0.1
	where Stu_name='盛日辉'
	select Stu_name,
			Stu_id,
			Score
	from Student
	where Stu_name='盛日辉'