sql server

1、数据库简介
-》解决的问题：持久化存储，优化读写，保证数据的有效性
-》关系型数据库：
基于E-R模型（数据关系模型）
使用sql语言进行操作
-》分类：文档型sqlite，服务型
-》数据库设计
三范式：列不可拆分，唯一标识，引用主键
关系及存储：
1对1：1个对象A对应着1个对象B，1个对象B对应着1个对象A
关系可以存入A或B对象中（例如：国家规定的夫妻关系）
1对多：1个对象A对应着n个对象B，1个对象B对应着1个对象A
关系存入B对象中（学生和班级，一个班级可以有多个学生，但一个学生只许有一个班级）
多对多：1个对象A对应着n个对象B，1个对象B对应着m个对象A
关系存入新建的一个关系表中（学生和选课， 1个学生可以有多门选课，一门选课可以有多个学生）

2、登录方式
-》开启服务
-》登录方式及设置
-》添加用户名
-》连接局域网中的数据库：启用TCP/IP协议

3、界面操作
-》数据库：创建，删除，分离，附加，生成脚本（架构，数据）
-》表：创建、修改、删除
字段类型介绍：int,bit,datetime,decimal(5,2),char/varchar/nvarchar
字符串类型n的区别：有n表示unicode编码，每个字符占一个字节；
没有n表示非unicode编码，英文或数字占一个字节，中文占两个字节
字符串类型var的区别：有var表示可变长度
没有var表示不可变长度，如果长度不够，会在末尾补空格
如果选择int值时，可以设置标识：此列的值为系统帮助维护，更轻松的实现唯一标识
-》约束：实现数据的有效性检查
主键，非空，惟一，默认，检查，外键
-》示例：
班级表ClassInfo（编号cId，名称cTitle nvarchar(10)）
学生表（编号，姓名，性别，出生日期，电话，邮箱，班级）

4、脚本操作
-》不区分大小写，字符串使用单引号，末尾不需要加分号
-》按照功能分类：
DDL：数据定义语言，用于进行各种数据库对象的创建，主要操作包括create、alter、drop
DML：数据管理语言，用于对表数据的添加、修改、删除、查询，主键操作包括insert、update、delete、select
DCL：数据控制语言，用于进行权限分配等
-》注释：--单行注释，/**/多行注释
-》数据库：创建、删除
可以通过查看master数据库中的sysdatabases表，来了解当前存在的数据库
create database 数据库名
on primary
(
name='stuDB_data', -- 主数据文件的逻辑名称
filename='D:\stuDB_data.mdf', -- 主数据文件的物理名称
size=5mb, --主数据文件的初始大小
maxsize=100mb, -- 主数据文件增长的最大值
filegrowth=15%--主数据文件的增长率
)
log on
(
name='stuDB_log',
filename='D:\stuDB_log.ldf',
size=2mb,
filegrowth=1mb
)
-》使用数据库：use 数据库名
-》表：创建、修改、删除
通过select * from sysobjects where xtype='U'可以查看所有存在的表
多个列间使用逗号分隔
主键：primary key
非空：not null
惟一：unique
默认：default()
检查：check()
外键：foreign key(列名) references 表名(列名)

5、表数据的操作
-》简单查询：select * from 表名
-》增加数据：insert into 表名(列名) values(值)
说明1：要求值的列名与值要位置对应
说明2：如果所有更都插入值，可以省略列名部分
扩展：一次性增加多行，可以直接在values后面拼接多个数据，之间用逗号分隔
-》修改数据：update 表名 set 列名1=值1,列名2=值2... where ...
alter
-》删除数据：delete from 表名 where ...
清空：truncate table 表名
说明：from关键字可以省略不写
通常实现：逻辑删除，物理删除
-》常用辅助命令及快捷键
set statistics time on/off：在消息栏显示详细执行时间
ctrl+e执行
ctrl+r隐藏消息栏
ctrl+l计划任务，对sql语句进行分析

-》E-R模型及关系型数据库
-》三范式、关系的存储方式
-》界面、脚本操作：数据库、表的创建
-》数+++++++++++++++++++++++++++++++++++-据的增加、修改、删除

2、查询
-》为表起别名as
-》查询全部列、指定列
为列起别名as
-》查询前n部分数据：
top n 列名：表示查看前n行
top n percent 列名：表示查看前百分之几的数据
-》排序：order by 列名1 asc|desc,列名1 asc|desc...
-》消除重复行：distinct
-》条件查询：写在where后面
对行进行筛选，返回bool类型的值，如果某行中的列数据满足条件，则加入结果集，否则不出现在结果集中
比较运算符：=，>，>=，<，<=，!=或<>
between ... and ...表示在一个连续的范围内
in表示在一个非连续的范围内
逻辑运算符：and，or，not
模糊查询：用于处理字符串类型的值，运算符包括：like % _ [] ^
%与_写在[]中表示本身的含义
在[]表示一个连续的范围可以使用-
^写在[]内部的开头，表示不使用内部的任何字符
null的判断：使用is null或is not null，与其它值计算时返回null，排序时null被认为是最小
优先级：小括号，not，比较运算符，逻辑运算符
-》连接：join 表名 on 关联条件
内连接：inner join，两表中完全匹配的数据
左外连接：left outer join，两表中完全匹配的数据，左表中特有的数据
右外连接：right outer join，两表中完全匹配的数据，右表中特有的数据
完全外连接：full outer join，两表中完全匹配的数据，左表中特有的数据，右表中特有的数据
-》聚合函数：对行数据进行合并
sum,avg,count,max,min
一般是对数字类型的列进行操作
一条查询中可以同时写多个聚合函数，但是不能与普通列混写
聚合中的null问题：不参与计算
-》开窗函数：over()
将统计出来的数据分布到原表的每一行中
结合聚合函数、排名函数使用
-》分组：group by 列名1,列名2...
聚合函数一般结合分组使用，进行分组内的数据进行统计
根据指定列进行分组
分组后条件筛选：having ...
-》总结：完整的select语句及执行顺序
select distinct top n *
from t1 join t2 on ... join t3 on ...
where ....
group by ... having ...
order by …

SELECT 语句的处理顺序
以下步骤显示 SELECT 语句的处理顺序。
完整的select语句执行顺序。
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE 或 WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP

-》练习：查询学生姓名、总分

3、联合查询
-》将多个查询的结果集合并成一个结果集
-》联合要求：
结果集列数要一致
对应列的类型要一致
-》union、union all、except、intersect
-》用处：在查询结果处显示汇总

4、快速备份
-》向未有表备份：select 列名 into 备份表名 from 源表名
-》说明：备份表可以不存在，会新建表，表的结构完成一致，但是不包含约束
如果想只包含结构不包含数据，可以加个top 0
-》向已有表备份：insert into 备份表名 select 列名 from 源表名

5、内置函数
-》类型转换函数：
cast(expression as data_type)：将任意类型转到到任意类型
convert(date_type,expression[,style])：将任意类型转到到任意类型
如果目标类型是字符串，则style可以设置格式，具体格式参考帮助
-》字符串函数：ascii（求字符的ascii值）,char（根据ascii转到字符）,
left,right,substring：字符串截取
len：返回字符串的长度
lower,upper：转小写、大写
ltrim,rtrim：去空格
注意：索引从1开始，而不是0
-》日期函数：getDate（获取当前日期时间）,
dateAdd（日期加）,
dateDiff（日期差）,
datePart（取日期的某部分）,year,month,day
注意：dateAdd、dateDiff、datePart的第一个参数使用双引号
1、复习
-》完整的查询语句
from t1 join t2 on ...
where ...
group by ... having...
select distinct 列名
order by ....
top n

2、ado.net
-》在.net中访问数据库的技术
-》常用类：
Connection：连接，连接字符串，Open()，Close()，Dispose()
Command：执行sql命令，ExecuteNonQuery()，ExecuteScalar()，ExecuteReader()
DataReader：读取，HasRows()，Read()，Get***()或索引器，FieldCount
DataAdapter：读取与更新，Fill()，Update()
DataSet、DataTable、DataRow
操作不同数据库，就使用不同的实现类
-》建议：
使用using简化连接使用
尽量晚打开连接，尽量早关闭连接
数据库连接只能打开一次，不能重复打开
连接可以重复关闭，但是关闭之后就不能再次使用
使用DataReader时必须保证连接是打开状态的，并且采用只进只读的方式进行操作
-》示例1：登录与注册（密码采用md5处理）
FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "MD5");
-》判断值是否为null：reader.IsDbNull(列名或索引)
-》示例2：使用DataReader读取数据，封装到一个List<>集合中，再绑定到DataGridView上
-》示例3：完成无外键表的crud
控件DataGridView：属性DataSource用于指定数据源
列的属性：DataPropertyName表示类的属性，HeaderText表示列标题
属性SelectionMode设置选择方式，设置成行选择
属性MultiSelect设置是否多选
属性SelectedRows获得所有被选中的行，是DataGridViewRow的集合
DataGridViewRow对象的属性Cells是DataGridViewCell的集合
DataGridViewCell对象的属性Value表示单元格中的值，是Object类型
窗体间传值：
方案一：设置窗体对象的Tag属性，可以将编号传递，通过ShowDialog方式打开窗体，通过DialogResult属性进行设置
方案二：使用事件，利用了事件的多播，可以实现一个动作引发多个变化
1、复习
-》连接池
-》参数化sql语句：Parameter,Parameters,@参数名称

2、工具类SqlHelper
-》完成常用数据库操作的代码封装
每次进行操作时，不变的代码：连接字符串
往集合存值
创建连接对象、命令对象
打开连接
执行命令
每次操作时，变化的代码：sql语句
参数
-》实现登录
用户连接三次登录失败，则锁定15分钟，15分钟之后才可以再使用

3、断开式数据操作
-》主要类及成员
类DataSet：数据集，对应着库，属性Tables表示所有的表
类DataTable：数据表，对应着表，属性Rows表示所有的行
类DataRow：行数据，一个行数组，就对应着一个实体对象
-》使用DataAdapter的Fill方法，可以将数据填充到DataSet或DataTable中
1、复习
-》断开式操作的主要类：DataTable,DataAdapter,DataSet,DataRow

2、视图
-》限制用户只能存取表内特定的列
-》将一个复杂的select语句进行封装，以更方便使用
-》语法：create/alter view 名称
as
select ...
删除：drop view 名称
-》使用：可以将视图名称当作一个表名，进行各种查询操作
-》建议：不要通过视图进行增加、修改、删除，主要进行查询操作

3、子查询
-》将一个查询语句嵌入另一个查询语句中，称这种查询为子查询
-》出现在条件部分常用的运算符：= in exists
-》分页：已知页大小、页索引，查询出当前页的数据
提示1：排名函数row_number()，结合开窗函数over(order by 列名)进行排序号
提示2：between ... and ...

4、关键字case
-》在select语句中实现判断
-》判等语法：
case 列名或表达式
when 值1 then ...
when 值2 then ...
when 值3 then ...
else ...
end
-》判不等语法：
case
when 条件1 then ...
when 条件2 then ...
when 条件3 then ...
else ...
end
-》示例：行转列（数据透视）
统计学生的语文、数学、英语的成绩
统计各班的男生、女生的人数

5、t-sql编程
-》变量
-》声明：declare 变量名 类型--变量名要求以 @ 开头
-》设置：set/select 变量名=值
-》输出：print/select 变量名
-》全局变量：使用双 @ 符号
@@version --数据库版本
@@identity --进行插入后调用，返回最亲的标识值
@@servername --服务器名称
@@error --返回执行的上一个 Transact-SQL 语句的错误号，如果没有错误则返回0
@@rowcount --返回受上一语句影响的行数
-》选择语句if
-》循环语句while
-》异常处理语句
begin try...end try
begin catch...end catch

6、事务
-》保证多个操作全部成功时才认为是一次有效操作，当有一个操作失败时就认为全都操作无效
-》只有数据改变（增加、修改、删除）时才会引发事务，查询不会引发事务
-》分类：
显式事务（需要手动控制）
隐式事务（不需要手动操作，MSSqlServer默认采用此方式）
-》语法：
begin transaction--开始事务
comit transaction--提交，没错后执行
rollback transaction--回滚，出错后执行

-》作业：登录（锁定15分钟3次），完成表的crud(使用事件调用)
1、复习
-》视图、子查询、关键字case、变量、选择、循环、异常处理、事务

2、存储过程
-》将一段t-sql脚本进行封装，以完成一个逻辑操作
-》创建存储过程：
create proc 名称
参数列表
as
begin
...
end
-》调用：exec 名称 参数列表
-》可以将参数指定为输出：output，调用时也需要加上output关键字
-》在ado.net中调用存储过程

3、索引
-》是什么？相当于书的目录，字典的检索，实现了数据的快速查找
-》分类：
聚集索引：与实际存储位置一样，一个表中只能有一个聚集索引，主键采用聚集索引
非聚集索引：索引顺序与存储顺序不一致。如学生在教室中的位置，可以按年龄进行查找，也可以按身高进行查找
-》建索引：
界面：右击表-》索引，添加-》选择列
代码：create [unique] [nonclustered|clustered] index ix_索引名 on 表名(字段1 asc|desc,...)
-》为什么会快？答：先根据值找地址，再到地址去找到整个行，减少了查找行的次数
-》应用技巧：为频繁现在在where后面的列添加索引
-》索引越多，物理存储空间越大，所以不是越多越好

4、触发器
-》是什么：实现改变（增加、修改、删除）发生时引发代码执行的对象
-》根据触发时机不同，分类如下：
after触发器
instead of替换触发器
（×）before触发器：sql server不支持
-》创建触发器
CREATE TRIGGER 名称 ON 表名
触发器类型（AFTER或Instead of）
触发操作（INSERT或DELETE或UPDATE）
AS
BEGIN
SET NOCOUNT ON;--不返回受影响行数，可以得到自定义的返回结果
END
-》两个临时表：inserted、deleted
更新的操作：先删除原数据，再插入新数据，这个过程既用到了inserted表又用到了deleted表
-》示例：删除A表数据时，将删除的数据保存到B表中
说明一：根据a表快速创建b表，insert into
说明二：为标识列指定值，set IDENTITY_INSERT 表名 ON/off，并且insert中要将所有的列在表名后写出来
-》建议：性能影响太大，慎重使用

--实体完整性,域完整性，引用完整性

Create 创建
Table 表格
Alter 修改
revoke
Exec
Insert 插入
Update 更新
Delete 删除
Select 选择
Where
Match_expression
Order by
having
With ties
Into
Check
DISTINCT
ifnull
truncate table [tablename] 清空表
INSERT INTO [表名] (字段1,字段2) VALUES (100,'51WINDOWS.NET')
删除数据：
DELETE FROM [表名] WHERE [字段名]>100
更新数据：
UPDATE [表名] SET [字段1] = 200,[字段2] = '51WINDOWS.NET' WHERE [字段三] = 'HAIWA'
新增字段：
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
删除字段：
ALTER TABLE [表名] DROP COLUMN [字段名]
修改字段：
ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL
重命名表：(Access 重命名表，请参考文章：在Access数据库中重命名表)
sp_rename '表名', '新表名', 'OBJECT'
新建约束：
ALTER TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= '2000-1-1')
删除约束：
ALTER TABLE [表名] DROP CONSTRAINT 约束名
新建默认值
ALTER TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT '51WINDOWS.NET' FOR [字段名]
删除默认值
ALTER TABLE [表名] DROP CONSTRAINT 默认值名
删除Sql Server 中的日志，减小数据库文件大小
dump transaction 数据库名 with no_log
backup log 数据库名 with no_log
dbcc shrinkdatabase(数据库名)
exec sp_dboption '数据库名', 'autoshrink', 'true'
\'添加字段通用函数
Sub AddColumn(TableName,ColumnName,ColumnType)
Conn.Execute("Alter Table "&TableName&" Add "&ColumnName&" "&ColumnType&"")
End Sub
\'更改字段通用函数
Sub ModColumn(TableName,ColumnName,ColumnType)
Conn.Execute("Alter Table "&TableName&" Alter Column "&ColumnName&" "&ColumnType&"")
End Sub
\'检查表是否存在
sql="select count(*) as dida from sysobjects where id = object_id(N'[所有者].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1"
set rs=conn.execute(sql)
response.write rs("dida")'返回一个数值，0代表没有，1代表存在

判断表的存在:
select * from sysobjects where id = object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
某个表的结构
select * from syscolumns where id = object_id(N'[dbo].[你的表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
alter table 表名 drop constraint 约束名字  删除约束
alter table 表名 add constraint 约束名字 DEFAULT 默认值 for 字段名称

primary key 用于唯一标识一行记录,只能有一个 ,只常用的单列主键，
identity 自动增长
unique 唯一约束
check(条件) 检查约束
foreign key references 外键约束
default(value) 默认值约束
not null) 非空约束
use RUNOOB; 命令用于选择数据库。
set names utf8; 命令用于设置使用的字符集。
SELECT * FROM Websites; 读取数据表的信息
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
CREATE DATABASE 数据库 创建数据库

查询基本结构
Select <列名>
From <表名>
[where <查询表达式>]
[order by <排名列表>[asc或desc]]--默认升序

alert修改
add添加
constraint约束
For指向
DBCC   CHECKIDENT   (TableName) 查看某个表中的Identity(自增列)当前的值
DBCC   CHECKIDENT   (TableName,   RESEED,   value) 修改某个表中的Identity(自增列)当前的
As 为列取别名

打开标识列开关
Set identity_insert 表 no --on打开 off关闭
空值判断
Is null
set identity_insert aclist on

--常用系统变量
select @@version as '版本';---------------------------返回当前数据库的版本信息
select APP_NAME ( ) as '当前会话应用程序';------------返回当前会话的应用程序信息
select @@ERROR as '错误ID';---------------------------返回上一次TSQL的错误ID，如果正常执行了查询，error为0，出错时error一定大于0，可用于事务中
select @@IDENTITY as '标示符';------------------------返回最后一次的标识符,如先执行了
select user_name()as '当前用户';----------------------返回当前用户
select @@connections as '连接次数';-------------------返回自上次SQL启动以来连接或试图连接的次数。
select getdate() as '当前时间';-----------------------返回当前时间
select @@cpu_busy/100 as 'CPU工作时间';---------------返回自上次启动SQL 以来 CPU 的工作时间，单位为毫秒
select @@language as '语言';--------------------------返回当前语言
select @@lock_timeout as '超时锁定设置';--------------返回超时锁定设置的值
select @@max_connections as '允许最大连接数';---------返回允许最大连接数
exec sp_configure;------------------------------------系统存储过程,显示默认配置
select @@options as '选项';---------------------------返回set选项值
select @@servername as '服务器名称';------------------返回当前数据库服务器名称
select @@servicename as '注册名称' ;------------------返回注册的SQL服务名称
select @@total_errors as '错误总数';------------------返回至启动以来的错误总数
select @@total_read as '读取总数';--------------------返回自启动以来的读取总数
select @@total_write as '写入总数';-------------------返回自启动以来的写入总数
select @@spid as '当前进程pid';-----------------------返回当前会话用户pid
select top 2 * from 表A wselect @@Rowcount @@Rowcount主要是返回上次sql语句所影响的数据行数

Sql server常用储存过程--execute不简写 还有一些可以查看联机丛书
exec sp_databases　　--列出当前系统 中的数据库
exec sp_renamedb 'MyBank','Bank'　　--改变数据库的名称()
exec sp_tables　　--当前数据库中可查询对象的列表
exec sp_columns Student 　　--查看Student表中列的信息
exec sp_help Student　　--查看Student表的所有信息
exes sp_helpconstraint Student　　--查看Student表的约束
exec sp_helptext view_student_Result　　--查看视图的语句文本
exec sp_stored_procedures　　--返回当前数据库中存储过程的列表
exec sp_helpindex '表名' --查看指定表索引
exec sp_rename 'oldName','newName'; 重命名表名:
exec sp_rename 'tableName.[oldName]','newName','column'; 重命名字段名:
存储过程
Create procedure '过程名称
Output
Rerutn

函数
Raiserror(自定义错误信息--提示文本,错误级别--0-18,错误状态--0-127)

截取字符串
substring('操作字符串',截取字符串开始位置,用于指定的长度) --截取字符串
Left('字符串','返回的子字符串的长度') --返回指定字符串中指定长度的左侧部分 Right()右侧部分·
CharIndex('查找的字符串','检索的字符串') 返回3 返回指定字符串中指定子字符串出现的起始位置。如果未找到就返回 0
CharIndex('3','1243') 返回4
Stuff()
Len('字符') --返回指定文本长度
ISNULL ( check_expression , replacement_value )
CAST ( expression AS data_type)
CONVERT ( data_type, expression,[style])
datalength();//计算字符串所占用的字节数，不属于字符串函数。测试varchar变量与nvarchar变量存储字符串a的区别。见备注1.
LOWER() 、UPPER () ：转小写、大写
LTRIM()：字符串左侧的空格去掉
RTRIM () ：字符串右侧的空格去掉
LTRIM(RTRIM(' bb '))