SQLServer

SQLServer

SQLServer术语概览

database：数据库
table：表
record：记录
field：字段
key：关键字
DatabaseFile(mdf)：数据库文件
Transaction log File(ldf)：事务日志文件
File Group：文件组
候选关键字：能唯一标示表的关键字
主键：在候选关键字选一个当主键
公共关键字：两表之间共有的关键字
外键：两表关联，一表有主键，另一表必有外键

SQLServer数据类型

SQLServer数据库操作

GO
-- 建库
create database stuDB
-- 默认于这个文件组，可省略
ON Primary
(
    -- 主数据文件逻辑名
    NAME='stuDB_data',
    -- 物理名
    FILENAME='G:\理论课数据库\stuDB_data.mdf',
    -- 初始大小
    SIZE=5,
    -- 增长的最大值
    MAXSIZE=100,
    -- 增长率
    FILEGROWTH=15%
)
LOG ON
(
    NAME='stuDB_log',
    FILENAME='G:\理论课数据库\stuDB_log.ldf',
    SIZE=2,
    FILEGROWTH=1
)
GO

-- 删除数据库
use master
GO
-- 检测是否存在
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
GO

-- 建表
-- 列的特征有：是否为NULL；自动编号；默认值；主键
use stuDB
GO
create table stuInfo
(
    -- identity(起始值，增量值);primary key(主键约束)；自定义主键名(constraint)
    -- mysql中设置自增主键
    -- sNo int auto_increment not null comment '主键'
    sNo int identity(1,1) constraint PK_STUID primary key clustered,
    sName varchar(20) not null,
    sAge int not null,
    -- numeric(18,0)：18位数字，小数位数为0（身份证）
    -- unique:唯一约束
    sID numeric(18,0) constraint U_stuID unique,
    -- default:默认约束
    sDate datetime constraint D_stuDate default(getdate()),
    sAddress text
)

create table tchInfo
(
    tNo int identity(1,1),
    tName varchar(20) not null,
    -- foreign key:外键约束
    tStuNo int constraint FK_stu_tch foreign key(tStuNo) references stuInfo(sNo)
)
GO

-- 删除表
GO
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
drop table tchInfo
GO

-- 添加约束
GO
-- check:检查约束
alter table stuInfo add constraint CK_sAge check(sAge between 15 and 50)
GO

-- 删除约束
GO
alter table stuInfo drop constraint CK_sAge
GO

-- 添加列
alter table tchInfo add tAddress text

-- 改变列的数据类型
alter table tchInfo alter column tAddress varchar(20)

-- 删除列
alter table tchInfo drop column tAddress

SQLServer数据库案例

select * from stuInfo
select * from tchInfo

-- 添加数据
GO
insert into stuInfo(sName,sAge,sID,sAddress) values('juluy',18,'360724155815457542','江西')
insert into stuInfo(sName,sAge,sID,sAddress) values('judy',20,'360724155815457523','珠海')
insert into stuInfo(sName,sAge,sID,sAddress) values('sssk',20,'360724155815457524','珠海')
insert into tchInfo(tName,tStuNo) values('sada',1)
insert into tchInfo(tName,tStuNo) values('jsdg',2)
GO

-- 修改数据
update tchInfo set tStuNo=1 where tNo=1
update tchInfo set tStuNo=2 where tNo=2

-- 删除数据
delete from tchInfo where tName='sada'
delete from tchInfo where tName='jsdg'

-- 清空表(与删除不同，插入时如果有自增会重新开始)
truncate table tchInfo

-- 查询所有数据
select * from stuInfo

-- 查询部分数据
select sName,sID from stuInfo where sNo=1

-- 查询时更改列标题（as,可省略）
select sName 姓名,sID 身份证 from stuInfo

-- 查询时删除重复行（可用all或distinct）distinct：只保留一行输出
select distinct(tName) from tchInfo

-- 查询时限制返回的行数
select top 2 * from tchInfo
-- 20 percent*：20%
select top 20 percent * from stuInfo
-- 返回tName的前2行
select top 2 tName from tchInfo

-- from后面可以指定256个表或视图(可以为表取个别名)
select * from stuInfo as a,tchInfo as b where a.sNo=b.tStuNo
select * from stuInfo where sNo in(select tStuNo from tchInfo where tNo=3)

-- where后面可以有：比较（>,<,=,<>.!>,!<）；between  and(可以返回包括开头与结尾的所有数据)
-- 判断是否为列表指定项（in,not in）
select * from stuInfo where sName in('juluy','judy')

-- 模式匹配符：like,not like(可用于char,varchar,text,ntext,datetime,smalldatetime)
-- %可用于缺少的值
select * from stuInfo where sName like 'j%'
-- 如下可表示开头为j或s的名字;加^表示不以这个开头
select * from stuInfo where sName like '[js]%'
select * from stuInfo where sName like '[^j]%'
-- 下划线可代表一个未知的值
select * from stuInfo where sName like '_u%'

-- 空值：null,not null
-- 逻辑：not,and,or

-- 备份：目标表不存在时会自动创建
select * into tchInfo20181014 from tchInfo

-- inner join:内联；将内联查询数据放到一张新表中
select stuInfo.sName,tchInfo.tName into sAll from (stuInfo inner join tchInfo on stuInfo.sNo=tchInfo.tStuNo)

-- desc：根据字段反排序；asc：根据字段正排序（优先选择前面排序）
-- 但是如果有二个相同字段的数据，则会根据前面先排序，再二者根据后面排序
select sName,sAge,sNo from stuInfo order by sAge desc,sNo asc

-- 内连接：没有匹配不返回
select stuInfo.sName,tchInfo.tName from (stuInfo inner join tchInfo on stuInfo.sNo=tchInfo.tStuNo)
-- 左连接：返回左边所有数据，没有匹配则对应为null
-- right join:右连接；full join:全连接
select stuInfo.sName,tchInfo.tName from (stuInfo left join tchInfo on stuInfo.sNo=tchInfo.tStuNo)

-- union:具有相似数据类型的2张表合并；union all：即使数据重复也会列出
select tName from tchInfo union select tName from tchInfo20181014

-- group by:按字段分组
-- 使用顺序：where>group by>having
-- where不能与聚合函数一起使用；having则可以
select sName from stuInfo group by sName

-- 更多分页查询方式：https://www.cnblogs.com/liguang/p/12365245.html
-- 分页查询，OFFSET FETCH（2012版本及以上才有），offset_value是要跳过的行数，fetch_count是要检索的行数
SELECT sName FROM stuInfo ORDER BY age
OFFSET offset_value ROWS
FETCH NEXT fetch_count ROWS ONLY;
-- 案例
-- 分页查询（通用型）
select * from student
order by sno
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;
-- 分页查询第2页，每页有10条记录
select * from student
order by sno
offset 10 rows
fetch next 10 rows only;

SQLServer存储过程、触发器

存储过程

-- 视图是封装查询语句；存储过程封装TSQL语句
-- 存储过程：可以包含数据操作语句，变量，逻辑控制语句
-- DDL:数据定义语句
-- DML:数据控制语句
-- DCL:数据操作语句
-- 优点：
-- 1）执行速度更快（预编译，数据库服务器内部执行）
-- 2）允许模块化程序设计
-- 3）提高系统安全性
-- 4）减少网络流通量
-- 5）可维护性
-- 前缀sp_:系统存储过程；xp_:扩展存储过程；usp_:自定义存储过程
-----------------------------------------------------------------
-- 系统存储过程
-- 查看有多少约束
exec sp_helpconstraint stuInfo
-- 查看有多少索引
exec sp_helpindex stuInfo
-----------------------------------------------
-- 扩展存储过程；要在外围配置器里开启应用；一般关闭比较安全
-- 可以通过数据库执行cmd语句
exec xp_cmdshell 'dir e:\'
---------------------------------------------
-- 自定义存储过程
-----------------------------------------
-- 不带参数的存储过程
-- 将create改为alter可以修改存储过程
GO
create procedure usp_StuAge
as
declare @total int
declare @avg int
select @total=sum(sAge),@avg=avg(sAge) from stuInfo
if @avg>10
	print 'good'
else
	print 'bad'
GO
-- 调用存储过程
usp_StuAge
exec usp_StuAge
-----------------------------------------
-- 带参数的存储过程（输入参数，输出参数）
-- 输入参数
GO
create procedure usp_StuAgeMax
(@mAge int)
as
select * from stuInfo where sAge<@mAge
GO
usp_StuAgeMax 20
-------------------------
-- 多个时如果顺序不一样，需指明
-- 也可以给参数一个默认值（例：@mAge int=20）
GO
create procedure usp_StuSelect
(@mAge int,@name varchar(20))
as
select * from stuInfo where sAge<@mAge and sName=@name
GO
usp_StuSelect @name='xdzy',@mAge=20
-------------------------------------------
-- 输出参数
GO
create procedure usp_StuCount
(@count int output,@age int=20)
as
select @count=count(sName) from stuInfo where sAge<@age
GO
-- 执行存储过程
declare @count int
exec usp_StuCount @count output
print '年龄大于20岁人数：'+convert(varchar(5),@count)
-------------------------------------------------------
-- 返回值：返回值必须是整数
GO
alter procedure usp_StuCount1
(@count1 int output,@age int=20)
as
select @count1=count(sName) from stuInfo where sAge<@age
return @count1
GO
declare @count int
declare @sum int
exec @sum=usp_StuCount1 @count output
print '年龄大于20岁人数：'+convert(varchar(5),@sum)

触发器

-- 触发器可分为事前（还没添加数据，只有一个触发器），
-- 事后（添加数据后，可对应多个触发器）

-- insert触发器，转账业务的实现
create trigger trig_transInfo
on transInfo
for insert
as
declare @type char(4),@outMoney money
declare @myCardID char(10)
select @type=transType,@outMoney=transMoney,
	@myCardID=cardID from inserted
-- 在inserted表中找添加的数据
if(@type='支取')
	update bank set currentMoney=currentMoney-@outMoney
	where cardID=@myCardID
else
	update bank set currentMoney=currentMoney+@outMoney
	where cardID=@myCardID
go

-- delete触发器，备份删除的数据
create trigger trig_delete_transInfo
on transInfo
for delete
as
	print '开始备份数据，请稍后》》》'
	if not exists(select * from sysobjects
	where name='backupTable')
		select * into backupTable from deleted
	else
		insert into backupTable select * from deleted
	print '备份数据成功，备份表中的数据为：'
	select * from backupTable
go

-- update触发器,交易超过20000，取消交易
create trigger trig_update_bank
on bank
for update
as
	declare @beforeMoney money,@afterMoney money
	select @beforeMoney=currentMoney from deleted
	select @afterMoney=currentMoney from inserted
	if abs(@afterMoney-@beforeMoney)>20000
	begin
		print '交易金额：'+convert(varchar(8),abs(@afterMoney-@beforeMoney))
		raiserror('每笔交易不能超过2万元，交易失败',16,1)
		rollback transaction
	end
go

-- update函数判断列是否被修改,判断交易日期是否修改
create trigger trig_update_transInfo
on transInfo
for update
as
	if update(transDate)
	begin
		print '交易失败》》》》'
		raiserror('安全警告：交易日期不能修改，由系统自动产生',16,1)
		rollback transaction
	end
go

-- 事前触发器的使用，一个种类触发器只能有一个事前
create trigger t32
on student
instead of insert
as
print '欢迎新同学'
select * from inserted
go