SqlSerer数据库【进阶】

六、约束

（1）主键约束

1.单一主键

格式:

alter table 表名
add constraint 主键名 primary key (列名)
go

例子:

alter table t_student
add constraint pk_t_student primary key (stud_id)
go

注意:在建表的时候主键不能为空

2.复合主键

复合主键即两个字段可以不唯一,但是两个字段联合起来必须是唯一的,例如一些手机,华为p10,魅族Pro10,iphone pro12,只有商标和型号联合起来才是一个唯一且不重复的值.
格式:

alter table 表名
add primary key(列名1,列名2)
go

例子:

alter table [dbo].[phone]
add primary key(brand,type)
go

注意:
在建表的时候这两个字段必须非空,因为主键非空且唯一.

3.主键标识列

标示列为非空自动增长且 不可自行修改的 主关键字一般用来在一个表中引用来自于另一个表中的特定记录 其为非空 唯一的 其可以有一个以上的列合为主键 相同点就是都不可为空且都是唯一的 能理解这些就够用了 给分吧！
格式:

create table 表名(标识列名 int identity(1,1) primary key not null )

其中(1,1)是表示从1开始,每行自增1
例子:

create table phone(id int identity(1,1) primary key not null )

（2）外键约束

格式:

alter table 表名 add constraint 外键关系名foreign key (外键名) references 
主键表名 (主键)

例子:

alter table t_user add constraint FK_t_user_t_group_id foreign key (groupid) references t_group (id)

（3）唯一约束

格式:

alter table 表名 add constraint 约束关系名unique(列名)

例子:

alter table Users add constraint UQ_Users_Name unique(Name)

（4）检查约束

1.普通检查约束

格式:

alter table Users add constraint 约束关系名 check(约束条件)

例子:

alter table Users add constraint CK_Users_Age check(Age>0 and Age<100)

（5）初建表格多个约束同时进行

例子:

create table Users (
Id int  identity(1,1) primary key ,
Name nvarchar(32) unique,
Phone nvarchar(16) not null,
Email nvarchar(128) default('123456@qq.com'),
Age  int check(Age>0 and Age<100),
Sex  char(2) check(Sex in('男','女')),
createtime datetime default(getdate())
foreign key(pid)references partment(pid)
)

（6） 删除约束

alter table 表名 drop constraint 约束名

七、视图

视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上，视图中并不存放数据，而是存放在视图所引用的原始表（基表）中，同一张原始表，根据不同用户的不同需求，可以创建不同的视图
视图的用途：
　　筛选表中的行
　　防止未经许可的用户访问敏感数据
　　降低数据库的复杂程度
　　将多个物理数据库抽象为一个逻辑数据库

（1）创建视图

格式:

 create view 视图名称
 as
sql语句

例子:

-- 4.5编写视图查询借书人的姓名，手机号码和地址；
 create view VW_borrow_student
 as
select c.card_name as '姓名',c.mobile as '电话号码'
from  [dbo].[T_borrow] B
left join [dbo].[T_card] as C
 on b.card_no=c.card_no
 go

（2）删除视图

格式:

drop view  视图名

例子:

drop view  VW_borrow_student

（3）更新视图

视图是一张虚拟表不适合更新,可以先删除再修改.我只遇到过这种情况可以更新.

create view  VW_1
as
select * from [dbo].[musics]
where musicName='菊花台'

update VW_1
set score='100'
where id='3'

select * from VW_1
-- 其中的score被更新至200

（4）查询视图

格式:

例子:

select * from VW_1

八、存储过程

存储过程（procedure）用来执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。
存储过程可以包含数据操纵语句、变量、逻辑 控制语句等，比如：单个select语句，select语句块，select语句与逻辑控制块。
存储过程优点：　
　　执行速度更快
　　允许模块化程序设计
　　提高系统安全性
　　减少网络流通量

（1）创建存储过程

1.不带参数的存储过程

格式:
创建:

create proc 存储过程名
as
sql语句

查询:

exec 存储过程名

例子:

create proc proc_1
as
select * from [dbo].[musics]

exec proc_1

2.带参数的存储过程

格式:
创建:

create proc 存储过程名
 @参数名 参数类型
 as
带有数据的sql语句,其中参数又@参数名代替
go

查询:

exec 存储过程名 参数
例子:
-- 4.6 编写存储过程，查询指定图书名称的借阅次数。 
 create proc book_times
 @num int
 as
 select bookname from [dbo].[T_book] where loan=@num
 -- end
 go
 
exec book_times 30

3.带输入参数,输出参数的存储过程

例1:

create proc P_Titles_ByTitleID_SelectPrice2
@title_id varchar(6), --输入参数
@price money output   --输出参数
as
select @price=price from titles where  title_id=@title_id
go

----执行
declare @price2 money               ----1，先声明变量
exec P_Titles_ByTitleID_SelectPrice2    ----2.之后在调用
@title_id='BU1032',
@price=@price2 output               ----3.配参后面要加output标识
select @price2                        ----4.之后在查声明变量

例2:

create proc my_musice
@score varchar(20),
@musicName varchar(40) output
as
select @musicName=musicName 
from [dbo].[musics]
where score=@score
go

declare @musicName2 varchar(40) 
exec my_musice
@score='20',
@musicName=@musicName2 output
select @musicName2

结果展示:

4.带变量和返回值的存储过程

--创建
use test
go
create proc testReturn
 @deptno char(10),
 @deptname char(10)
 as
 declare @dname char(10)
 select @dname=name from dept where id=@deptno
 if (@dname=@deptname)
  return 1
  else
  return 0
go
--执行
DECLARE @rs char(10)
 exec @rs=testReturn @deptno='1', @deptname='soft'
 select @rs

（2）删除存储过程

格式:

drop proc 存储过程名

例子:

drop proc my_musice

（3）存储过程,视图,聚合查询综合应用

例子:

--将点击数最高的那首歌插入某个歌单。
-- 创建视图查找到点击量最高的歌曲id
create view vw_maxclick
as
select musicid
from [dbo].[tbl_Music]
where clickNumber=(select max(clickNumber) 
-- 利用子查询查到最高点击量,根据点击量查找点击量最高的歌曲
from [dbo].[tbl_Music])
go

-- 创建带参数的存储过程,将查询的结果赋值给参数
create proc inser_music
@menuName varchar(40),
@userid int,
@musicid int
as
insert into [dbo].[tbl_Menu] values
( @menuName,@userid,@musicid
)
go
-- 执行储存过程
declare @menuName varchar(40),@userid int,@musicid int
select @menuName='我喜欢的歌曲',@userid=1, @musicid=(select * from vw_maxclick)
exec inser_music @menuName ,@userid,@musicid