SQL笔记

SQL笔记

数据库启动服务的方式

1.在命令提示符界面输入(管理员) net start mssqlserver 
2.计算器右击--管理---服务和应用程序--服务---mssqlserver--右击启动

sql的组成：

1.DML 数据操作语言 insert delete update
2.DQL 数据查询语言 select
3.DDL 数据定义语言 create alter drop
4.DCL 数据控制语言 revoke commit

约束

约束类型：保证数据的完整性与准确性(不合理的数据不允许出现)
1.主键 primary key
2.非空 not null
3.检查 check
4.默认 default
5.唯一 unique
6.外键 forgin key

增删改查语法

新增语法
insert into 表名 values(值,值);所有的列都必须添加

insert into 表名(列名,列名)values(值,值)

注意点：1.值与列需要匹配(个数，顺序,类型)
	2.设置标识列的列不需要录入数据
	3.值都必须满足约束的要求
	4.默认值使用default代替
	5.符号区分中英文(文本需要带上单引号)

删除语法

 delete from 表名 where  条件

修改语法

 update 表名 set 列名=值, 列名=值 where 条件

查询语法

select * from 表名 where 条件

条件：列名 比较表达式 值  如：age列>18   name列='Zs'
多个条件使用逻辑运算符连接：and或者or

关键字
distinct 去掉重复的数据
top 限制返回的行数
between 数值 and 数值 在…区间
in(值,值)在小括号的范围中
like 模糊查询 结合% _ [] [^]使用
%:匹配任意多个字符 %在前表示以…结尾 双%：包含
_:匹配任意一个字符
[值,值]: 等价于 in关键字
[^值,值]:等价于 not in关键字

order by  数据排序  升序为默认方式(asc) 降序使用desc多列排序逗号隔开
select * from 表名 order by 列名,列名 排序方式
如果第一个列名排序存在相同的数据则再根据第二列排

group by  数据分组
select 分组列,函数 from 表名 
where 条件		--在分组之前限定数据
group by 分组的列
having 聚合函数的比较   --在分组之后筛选数据
order by 列名 排序	

union all 连接连个表的数据  要求各查询的列数据一致

连接查询：

内连接
	select * from 表A,表B where 表B.主键=表A.外键;
	select * from 表A inner join 表B where 表B.主键=表A.外键;
外连接	
	select * from 表A left  join 表B where 表B.主键=表A.外键;
全连接  
	select * from 表A full join 表B where 表B.主键=表A.外键;
交叉连接 
	select * from Student cross join teacher 

函数
字符串函数 日期函数 数学函数 聚合函数 系统函数等

三大范式：

1.每列不可在分(原子性)
2.每个表只描述一件事情
3.表中的列都直接依赖主键，不能间接相关

建库建表建约束

1.建库 	
 a. create database 库名;
    create database 库名 on(name='',属性=值) log on(属性=值)
 b. drop database 库名;删库
 c. 删除数据库需要判断是否存在
    if exists(select * from sysdatabases where name='库名')
	drop database 库名;
    如果需要删除的数据库它存在则执行
    sysdatabases 存储所有的数据库
数据库创建时相关属性：
	name 	 数据库名  在sqlserver中显示的名字
	filename 数据库所在路径/库名.mdf 本地名称
	size	 初始容量  mb
	maxsize  最大容量
	filegrowth 增长量
2.建表
 a. 数据类型 文本类型(char nchar varchar nvarchar)   
	     数值类型(int float decimal)
	     日期类型(date)
	     货币类型(money)
	     布尔类型(bit)
   char nchar varchar nvarchar的区别
   char nchar 固定长度 :当实际值与长度不符合时，使用英文空格填充
 b. 建表	 create table 表名(列名 类型 属性)多列使用逗号隔开
 c. 删表 if exists (select * from sysobjects where name='表名')
	    drop table 表名
3.约束
 a.完整性：实体完整性	保证行数据的准确		主键 标识
	   域完整性	保证列数据的有效		检查 默认
	   引用完整性	保证表与表之间的关系	外键
	   自定义完整性	特定的业务规则		存储过程
 b.约束 ：保证数据的准确
	主键约束 primary 唯一且非空
	唯一约束 unique  不允许重复出现相同的值可以出现一次null
	默认约束 default	 不添加数据时给定一个默认值
	检查约束 check   数据必须满足条件表达式的要求
	外键约束 foreign	 表与表之间的关系
	非空约束 not null
	标识列   identity 自动增长

c.添加约束
约束语法 alter table 表名 add constraint 约束名 约束类型 
1.添加主键约束
alter table 表名 add constraint pk_stuid primary key (列名);
2.唯一约束
alter table 表名 add constraint un_stucard unique (列名);
3.默认约束
alter table 表名 add constraint df_stusex default('男') for 列名;
4.检查约束
alter table 表名 add constraint ck_stuage check(条件表达式) ;
5.外键约束
alter table 外键表 add constraint fk_tid foreign key(外键列)
references 主键表 (主键列);

d.删除约束
alter table 表名 drop constraint 约束名；

变量

sql中定义局部变量：
declare @变量名 数据类型
注意点：变量名的前缀必须为@符号
先声明再赋值
全局变量：@@名称 @@servername
全局变量由系统进行维护，只能调用不能进行更改
变量赋值：
1.使用set关键字 set @变量名=值;
2.使用select关键字 select @变量名=列名 from 表
set赋值与select赋值的区别
set只能给单个变量赋值,而select可以赋值多个变量,使用逗号隔开
set如果返回值有多个数据则报错而select将最后一个值赋值给变量
set果然语句没有查询到结果，将null赋值给变量
select则保持该变量原有的数据

全局变量 含义
@@ERROR 最后一个T-SQL错误的错误号
@@IDENTITY 最后一次插入的标识值
@@LANGUAGE 当前使用的语言的名称
@@MAX_CONNECTIONS 可以创建的同时连接的最大数目
@@ROWCOUNT 受上一个SQL语句影响的行数
@@SERVERNAME 本地服务器的名称
@@TRANCOUNT 当前连接打开的事务数
@@VERSION SQL Server的版本信息

数据类型转换
convert(数据类型,需要转换变量)
cast(需要转换变量 as 数据类型)

输出语句：select @变量名 网格状
print @变量名 文本

分支结构

if(条件)
   begin
	代码块
   end
当代码块只有一条语句是，begin与end可以省略

循环

while(条件)
   begin
   end

分支结构case语句

case
     when  条件  then 结果
     when  条件  then 结果
     else 	结果
end

注意点： a.case end成对出现,不能单着
b.when 与then可以重复出现，不需要使用符号隔开，满足when的条件执行then的结果
c.else 后不需要接条件 直接返回一个结果，特指所有when的条件都不成立就会执行else
变量：定义变量的名称前缀为@，变量定义后使用set或者select为其赋值 ，如果变量的类型为int，不需要添加长度
分支： 如果语句块有多条语句，那么begin end不能省略，
数值类型与字符类型进行相加需要使用转换(convert,cast)

子查询
可以嵌套在select，insert,update delete 语句中的查询

select子查询语法：
select * from 表名 where 列名 比较运算符(子查询语句)
子查询是小括号中的代码，优先执行，将子查询的返回的结果作为外面查询(父查询)的条件来使用

子查询返回的结果：单行单列 是父查询所需要的列，不能有多个数据(多行或者多列)

连接查询与子查询对比
连接查询适用于多张表，而子查询一般用于单表，一条查询连接查询语句都可以使用子查询替换
有些子查询语句，连接查询不能替换
in子查询 select * from 表名 where 列名 in(子查询语句)
子查询语句返回的结果可以是单行单列或者是多行一列
列名存在子查询语句中的数据
select * from 表名 where 列名 in（值,值）
exists子查询 建库建表
if exists(子查询)
语句块
子查询如果存在数据，则返回true,如果为空则返回false 结合if使用

事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作
多个操作作为一个整体向系统提交，要么都执行、要么都不执行
事务是一个不可分割的工作逻辑单元
特性：ACID
A：原子性 不可再分割，是一个完整的单元，要么都执行，要么都不执行
C：一致性 当事务执行完之后，数据处于一致状态
I：隔离性 并发事务相互独立，互不干扰，不会互相影响
D：永久性 事务执行后，对数据的操作都是永久保存
开启事务 begin transaction
提交事务 commit
回滚事务 rollback

视图

视图是一张虚拟表
表示一张表的部分数据或多张表的综合数据
其结构和数据是建立在对表的查询基础上
视图中不存放数据
数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图

语法 create view v_视图名 as 查询语句

索引： 提高数据库查询的效率，改善数据库的性能
语法： create 索引类型 index index_name on 表名(列名)
drop index 表名.索引名

按照下列标准选择建立索引的列
频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作连接的列（主键/外键）
请不要使用下面的列创建索引
仅包含几个不同值的列
表中仅包含几行

注意点
查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
避免在ORDER BY子句中使用表达式
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

存储过程

存储过程
create procedure usp_名称
@参数名 数据类型
as
语句块
调用 exec 存储过程名称 [值]

参数 定义在as的前面，在as后面为变量，定义变量需要带关键字declare,

带输入参数且有默认值的存储过程调用
1.建议将带有默认值的参数放在所有参数的后面
2.如果默认值的参数在无默认值参数的前面，调用时可指定default来表示
或者使用指定参数名赋值
eg
create proc usp_名称
@参数名 数据类型 =值,
@参数名2 数据类型
as
语句块
调用方式
exec usp_名称 default,‘值’ | exec usp_名称 值,‘值’
exec usp_名称 @参数名2=值
带输出参数的存储过程
create proc usp_名称
@参数名 数据类型 output,
@参数名2 数据类型 output
as
语句块
调用方式
1.定义变量保存存储过程的返回值
declare 变量名 数据类型,变量名 数据类型
2.调用存储过程传递变量名 output
exec 存储过程名称 变量名 output,变量名 output
3.存储过程的返回值保存在变量中进行输出
print 变量名
自定义错误信息
raiserror(‘错误提示文本信息’,严重级别，错误状态)
严重级别0-18取值
错误状态1-127取值

另一种调用带输出参数的存储过程
exec 存储过程名 输出参数名=变量名 output