SqlServer学习笔记

SqlServer知识点

一、数据库设计
 1.软件开发周期步骤：
      需求分析，概要设计（E-R），详细分析，软件编码，测试，发布
 2.设计数据库步骤：
     收集信息，标识实体，标识实体中的属性，标识实体之间的关系，E-R图，数据库模型图，使用三大范式规范数据库
 3.三大范式：
     第1范式：确保每列都是原子性。
     第2范式：保证每张表都描述一件事情。
     第3范式：保证每列都和主键有直接关系。

二、数据库代码设计

1.建库

if exists(select * from sysobjects where name ='DB') --查找命令
drop DATABASE DB --删除 命令

Create database DB
on(
name='DB',
filename='路径',
size=3mb,
maxsize=10mb,
filegrowth=5% --增长速度为
)
log on(
name='DB_log',
filename='路径',
size=2mb,
maxsize=5mb,
filegrowth=1mb
)--每个数据库至少有两个文件：数据库文件.mdf,日志文件.ldf

2.建表：非空约束，标识列(数值，int,float,real,decimal(8,2),numrice(8,2))

if exists(select * from sysobjects where name='Grade')
Drop table Grade
Create table Grade(
  GradeId int not null identity(1,1),
  GradeName varchar(20)
)
Create table Student(
  StuID int not null identity(1,1),
  StuName varchar(20) not null,
  GradeID int not null,
  IdentityCard varchar(20) not null,
  Age int not null,
  Address varchar(50)
)

3添加约束：主键约束、唯一约束、默认约束、检查约束、外键约束

Alter table 表名 
Add Constraint 约束名称 约束类型 约束描述
主键约束：
 Atler table Grade
 Add Constraint pk_id primary key (GradeID)
唯一约束：
Atler table Grade
Add Constraint uq_pid  unique (IdentityCard)
默认约束:
Atler table Student
Add Constraint df_address default (‘地址不详’) for Address
检查约束：
Atler table Student
Add Constraint ck_Age check (Agebetween 0 and 100)
外键约束:
Alter table 外键表 
Add constraint fk_id foreign key 外键列  references 主键表（主键列）

3.变量

1.局部变量和全局变量：两者用户都可以自己声明，赋值
2.全局变量：@@error，@@idntity，@@rowcount
3.声明变量：declare@age,@age1 int 不可以
  declare @age int,@age1 int 可以
  declare @age int =10 可以
  set @age=10,@age1=20 不可以

4.给变量赋值：set   select
区别：set一般付常量值，一次性只能给一个变量赋值
select：通过查询的方式赋值，一次性可以多个变量赋值
select @age=age from Student 赋值
5.输出信息：select（以表格形式）、print（以文本形式）
打印年龄的信息，并且起名名
select @age 年龄 /select @age as 年龄
select 年龄=@age
print '年龄'+@age  报错：varchar和int 无法兼容
6.类型转换：convert(类型 ,变量名) /cast(变量名 as 类型)
print '年龄'+cast（@age as  varchar(10)）
print '年龄'+convert(varchar(10),@age)

7.逻辑控制语句：
if /if-else/case-end/while
8.if-else条件：关系运算符：1>0 1=1 exists()
9.case-end
 select age = (case
    when age >= 18 then '成年了'
    else '未成年' end)
 from student

4.高级查询

1.三种查询方式：子查询、联表、联合union
2.子查询：in、not in 、exists、not exists
    select 列  from 表 where 列名 =、in（select 列from 表）
3.嵌套查询：子查询写到列上或者表上
    select (select subjectName from Subject)  from Student
    select 列 from ( select 列名 from 表 ) as 别名

4.select 子查询中可以出现什么：select/from/where/orderby/group by /having

5.事务

    一致性（事务在提交之前和之后数据要保持一致）、
    原子性（表示把事务当成整体，要么全部提交，要么全部撤销）、隔离性、
    持久性（事务修改数据后，数据会保存到数据库）

关键SQL语句：
   Begin transaction
   Commit transaction
   Rollback transaction

declare @num int
set @num=0
begin transaction
 select * into HistoryResult from Result where StudentNo in
 (select StudentNo from Student where GradeId =(select GradeId from Grade where GradeName='Y2'))
 set @num += @@ERROR
   if(@num>0)
   begin
     rollback transaction
   end
   else
   begin
     commit transaction
   end

6.视图

 视图：虚拟表
 1.视图的数据来源：从数据表，从其他视图，从其他的数据库得到
 2.视图的作用：用来筛选数据（select）、防止用户不经允许访问敏感数据、可以将多个物理数据表抽象为一个逻辑数据表
 3.视图中不能有什么：order by，除非top关键字，into，临时表,变量

if exists(select * from sysobjects where name='视图名')
Drop view 视图名
Go
Create view 视图名
  As
  select…from 表
Go
如何查询视图：select * from 视图

7.索引

索引：快速查询（是给某张表上的某个列创建）
聚集（1个表中只能1个）、非聚集索引、唯一、主键、复合、全文
为学生表中学生姓名列创建一个索引
If exists(select * from sysindexes where name=’索引名’)
Drop index 表名.索引名
Go
Create nonclustered index 索引名
On 表名（列名）
  With fillfactor=20
Go
--使用索引查询
Select StudentName from Student
With (index=索引名)

8.存储过程

1.存储过程作用：可以提高运行速度，可以保证数据的安全，可以减少网络流量、可以模块化程序
2.输入参数:参数，可以有默认值  输出参数:返回值，可以有默认值
3.调用存储过程时：
if exists(select * from sysobjects where name='usp_Stu')
drop procedure usp_Stu
go
create procedure usp_Stu
   @name varchar(20)=null,
   @count int output
as
   if(@name  is null)
   begin
    print '姓名为空'
    return   --作用：结束存储过程
   end
   select @count=COUNT(*) from Student where StudentName=@name
go
--调用方式
declare @num int
execute usp_Stu'武松',@num output
print '记录条有：'+convert(varchar(10),@num)
--带有默认值
declare @num2 int
execute usp_Stu default,@num2 output
--带着参数名(参数的顺序可以不一致)
declare @num1 int
execute usp_Stu@name='张三',@count=@num1 output
--带着参数
declare @num3 int
execute usp_Stu @count=@num3 output,@name=default
--带着参数
declare @num4 int
execute usp_Stu @count=@num4 output

4.系统存储过程
   Sp_databases:描述当前服务器上所有数据库的信息
   Sp_tables:描述数据表的信息
   Sp_help:描述对象（表）的信息
   Sp_helpIndex:描述某个表上的索引信息
   Sp_helpConstraint:描述某个表上的约束信息
   Sp_helptext:描述存储过程、视图的信息
   Sp_helpdb:描述具体某个数据库的信息
   Sp_renamedb:重名称数据库名
   Sp_password:修改或添加账户密码
   Sp_stored_procedures:显示所有存储过程的列表