2015.5.20
数据库
类型：层次型，网状型，关系型(目前应用最广的数据库类型)
常见数据库：SQLServer，Oracle，DB2，Access，VisualFoxpro，MySQL(目前应用最广的是SQLServer)
身份验证：①Windows身份验证(登录界面默认帐号名)
②SQLServer身份验证(必须用SQLServer帐号登录)
数据登陆的几个要素：
1.服务器类型---数据库类型
2.服务器名称---Windows身份验证：本机用.或local命名
SQLServer身份验证：远程计算机名或IP命名
3.身份验证---Windows身份验证(管理员使用)
SQLServer身份验证(开发人员使用)
4.用户名---sa(意为超级管理员)
5.密码---需自己设置
如何开启SQL身份验证？
1.在服务器属性中，修改身份验证模式：SQLServer身份验证和Windows身份验证
2.启用sa帐号的两个功能：①授予访问数据库②启用登录
3.修改sa的登录密码
SQLServer的三层结构：
1.硬盘Data文件夹中的.mdf(存储数据的地方)
2.SQLServer的服务
3.数据库管理界面
创建数据库 create database
文件类型：
1.主数据文件.mdf(只有一个)(主数据文件需限制大小)
2.次数据文件.ndf(可以有多个)
3.日志文件.ldf(可以有多个)
c#与数据库的数据类型类比
c# 数据库
int int，small int，big int，tiny int
double float，real，decimal
string char，varchar，nchar，nvarchar，text(nchar长度固定，nvarchar长度不固定)
datetime datetime(1753-1-1,9999-12-31)，small datetime(1900-1-1,2079-6-6)
数据库独有数据类型：binnary(二进制)，image(大型二进制)
删除数据库 drop database
修改数据库 alter database
数据库操作---大小写不敏感
创建:create database 数据库名
删除:drop database 数据库名
修改:alter database 数据库名
数据操作：增、删、改、查
建表：
create table 表名
(
列名 列的类型 约束，
列名 列的类型 约束，
......
列名 列的类型 约束(限定列名字符长度)
)
与c#代码注释不同，c#代码注释使用//，数据库建表注释用--
与c#语法不同，c#语法用{}，数据库语法用()
删表：drop table 表名
改表：alter table 表名
几个术语：
DDL---Data Definition Language(数据定义语言)
DML---数据操纵语言(开发人员使用)
DCL---数据控制语言(管理员使用)
2015.5.21
数据库操作语法
创建表：
use 数据库名
go
create table 表名
(
列名1 数据类型(字符长度),
列名2 数据类型(字符长度),
列名3 数据类型(字符长度),
......
列名n 数据类型(字符长度)
)
插入数据：
INSERT INTO 表名(列名1,列名2,列名3,...,列名n) VALUES(列值1,列值2,列值3,...,列值n)
修改数据：
UPDATE 表名 SET 列名='列值' WHERE 列名='列值'
说明：创建的数据库、表，写代码过程中标点符号为英文状态，与语句不同，数据库、表在写代码时字母大小写不敏感
SELECT查询：
(1)精确查询
select * from 表名(*代表查询所有列)
select 列名 from 表名(查询某一列或某几列数据)
select distinct 列名 from 表名(单独列出某一列数据)
select * from 表名 where 列名表达式(等号是等值筛选，> < 是不等值筛选，多条件筛选使用and或or)(选出表内部分数据)
select * from 表名 where 列名='列值'---等值筛选
select * from 表名 where 列名>某个值---不等值筛选
select * from 表名 where 列名<某个值---不等值筛选
select * from 表名 where 列名表达式1 and 列名表达式2(也可写作select * from 表名 where between 某个值1 and 某个值2)
select * from 表名 where 列名表达式1 or 列名表达式2
(and是求中间范围内的，or是分段范围内的)
select distinct 列名表达式1 from 表名 where 列名表达式2(在固定条件列名表达式1的前提下查询符合列名表达式2的数据)
select 列名1,列名2*列名3 from 表名(添加新的列，此时新添加的列显示无列名)
select 列名1,列名2*列名3 新列名 from 表名(添加新的列，此时新添加的列显示新列名)
select 列名1 汉字列名1,列名2 汉字列名2(将原先的拼音列名改为汉字列名)
select * from 表名 order by 列名+asc升序/desc降序(order by排序，多个列名筛选用逗号隔开列名)
DISTINCT应用：
1.筛选身份证号
2.筛选部门数
(2)模糊查询：
select * from 表名 where 列名 like'筛选条件'
①select * from 表名 where 列名 like'字符%'(筛选以某个字符开头的数据)
②select * from 表名 where 列名 like'%字符'(筛选以某个字符结尾的数据)
③select * from 表名 where 列名 like'%字符%'(筛选含有某个字符的数据)
④select * from 表名 where 列名 like'_字符'(筛选在某个位置含有某个字符的数据)
like后单引号中内容，后%意为以某字符开头，前%意为以某字符结尾，前后%意为含有某字符，_意为在第几个位置含有某字符
UPDATE修改：
UPDATE 表名 SET 列名表达式 FROM 列名=列值(修改一个值)
UPDATE 表名 SET 列名表达式1,列名表达式2,列名表达式3 FROM 列名=列值(修改多个值)
2015.5.24
(一)连接查询---列的扩展
1.等值连接查询(内连接)
①笛卡尔积
②join on
2.外连接---left，right，full
①left左链接
left左边的表为主表，右边的表为从表，显示的结果集以主表为主，如果从表中没有和他匹配的数据，就以NULL值填充
②right右连接
right右边的表为主表
③full全连接
每个表都完全显示，互相匹配数据，如果对方没有则以NULL值填充
3.联合查询---行的扩展
用Union将两个查询联合起来显示，追加行的形式在结果集中显示，必须保证两个查询结果的列的格式是一样的
(二)子查询
1.无关子查询
①select...from...where...not in(select...from...)
②select...from...where...not exsits(select...from...)
2.相关子查询
2015.5.24
5个统计函数：
个数count 最大max 最小min 求和sum 平均avg
select 列名,统计函数名 from 表名 group by 列名 having count(*) 不等值查询(根据关键字筛选)(having与group by子句配合使用)
判断某个表中某个数据是否为空
select * from 表名 left join 表名 on 表名.列名=表名.列名 left join 表名 on 表名.列名=表名.列名 where 表名.列名 is not null
2015.5.28
***数据库·表的约束***
一、防止同一条数据完全重复
1.主关键字约束（主键约束）---PK
①能够唯一地区分每一行数据---不许重
②表中的数据按照主关键字排序---有序
③主关键字不能为空---不为空
④一个表只能有一个主键，但可以设置组合主键
操作：
添加主键：在表的设计界面，在相应的列上右击“设为主键”
删除主键：在表的设计界面，在相应的列上右击“删除主键”
2.唯一键约束（候选键）---UK
①唯一键不许重，可以为空
②一个表中可以有多个唯一键
③唯一键不能自动排序
操作：
在表的设计界面上右击“索引/键”，在弹出窗口上点击左下角“添加”，右键属性列表，修改类型“唯一键”，设置列
tips：
ctrl+0 清空不想要的某列某项数据
3.自增长列（标识列，Identity列）
①不能手动向自增长列里面填值，如果要写INSERT语句的话，则INSERT语句应当把这一列忽略
②自增长列主要用于整型、长整型、Decimal型
③不要随便把自增长列设置为主键
操作：
在表的设计界面中选中相应的列，在下面的属性面板→标识规范→选择“是”
二、防止有些该填的没有填
1.非空约束
不能为NULL
操作：
在表的设计界面中“允许NULL值”的复选框去掉
2.默认值
如果不给赋值的话，会使用默认值填上
操作：
在表的设计界面，选择相应的列，在下面的属性面板中设置默认值
三、防止乱填
1.类型
2.Check约束---CK
按照某种规则对数据进行检查
操作：
在表的设计界面中，右击相应的列，选择CHECK约束，在弹出的对话框中，设置约束的名称和表达式
3.外键约束---主表，从表
①主表是用来约束从表的，外键应当建立在从表上，从表中的外键列是不能随便乱填内容的，只能填写主表主键中存在的数据
②一旦建立起外键关系来，从表的外键不能乱填，反过来，主表中的主键列也不能乱删除乱改动
操作：
先建好主表，从表的设计界面上右击“关系”，在弹出的对话框中选择“添加”，在右边属性列表中设置“表和列的规范”
★级联删除的设置
***数据库函数***
一、数学函数
SQRT 平方根
select SQRT(2)//输出结果为1.414
ABS 绝对值
select ABS(-234)//输出结果为234
ROUND
①保留小数点后几位
select ROUND(4.532,1)
②四舍五入
select ROUND(4.532,0)//输出结果为4.500
CEILING 取大于已知小数的最小整数
select ceiling(COUNT(*)/5.0) from News
FLOOR 取小于已知整数的最大整数
select floor(COUNT(*)/5.0) from News
二、字符串函数
LOWER 大写转小写
select LOWER('UserName')---username
UPPER 小写转大写
select UPPER('UserName')---USERNAME
LTRIM 压缩左边空格
select LTRIM(' me')---me
RTRIM 压缩右边空格
select RTRIM('me ')---me
*截取字符串
select LEFT(Title,5) from News 截取前几个字符
select RIGHT(Title,5) from News 截取后几个字符
select SUBSTRING(Title,3,5） from News 任意位置截取字符//3表示截取字符位置，5表示截取字符个数
*替换字符串
select REVERSE(Title) from News 翻转字符串
select CHARINDEX('中国'，Title) from News 字符串出现的位置---标题中字符"中国"出现的位置
select REPLACE(Title,'中国','美国') from News 替换字符串---标题中字符"中国"的位置替换成"美国"//"中国"表示原字符，"美国"表示替换字符
select REPLACE(Title,3,4,'china') from News 指定位置、指定个数替换字符串//3表示替换位置，4表示替换个数，"china"表示替换字符
三、其他
select RAND()
随机数生成函数，0-1之间
select LEN('china')//输出结果为5
取字符串长度
select GETDATE()
获取当前时间
select YEAR(sbirthday) from Student
取日期时间年份
select MONTH(sbirthday) from Student
取日期时间月份
select DAY(sbirthday) from Student
取日期时间天数
*取日期时间部分字符
select DATEPART(yy,sbirthday) from Student 取日期时间年份
select DATEPART(mm,sbirthday) from Student 取日期时间月份
select DATEPART(dd,sbirthday) from Student 取日期时间天数
select DATEPART(hh,sbirthday) from Student 取日期时间小时
select DATEPART(mi,sbirthday) from Student 取日期时间分钟
select DATEPART(ss,sbirthday) from Student 取日期时间秒数
*转换格式
select CAST(123 as varchar(20))//输出varchar格式123
select CAST('123' as int)//输出int格式123
2015.5.30
视图View
视图：查询结果---与视图相对应的是表，表才是真正存储数据的地方
视图不能存储数据，只展示查询结果
语法:
create view View_表名
as
select查询
go
as...go等效于c#语句中的花括号
使用创建的视图:
select * from view_表名
tips：
①视图就是为了查询数据方便，一般不要试图向视图中插入数据，容易出错。
②视图可以由一个表生成，也可以由多个表生成，还可以由表中各视图生成新的视图。
掌握：
1.视图是什么
2.会建视图、会查视图
3.知道视图的主要功能是查询，不是增删改
索引Index
索引：为了提高查询效率
索引的类型：簇索引(聚集索引)、非簇索引(非聚集索引)---簇索引比非簇索引检索效率高
操作：在表的设计界面上右击“索引/键”，在弹出窗口上点击左下角“添加”，设置索引
tips：
①数据的默认存储次序跟簇索引的次序是一样的。一个表中只有一个簇索引。
②非簇索引是单独放置的，查询的时候，先查询非簇索引，再根据非簇索引查询内容。一个表可以有多个非簇索引。
要记住的：
1.索引是什么
2.索引的分类、效率、原理
3.索引提升查询效率，但会影响增删改的效率
4.如何建索引、删索引
存储过程
SQL编程
数据类型：列的类型
变量定义：declare 变量名 类型名---变量名以@开头
赋值和取值：
赋值：set 变量=值或select 变量=值
eg.set @a='hello world'
select @a='hello world'
取值：变量名
declare @a varchar(50)---定义变量
set @a='hello world'---给变量赋值
print @a---取值输出
运算符：算术、关系、逻辑、其他
算术：+ - * / % 没有++(自增) --(自减)
关系：> < >= <= = <> != //<> !=都是不等于，但常用的是<>
逻辑：and(与) or(或) not(非)
其他：
between...and...
exists
like
is(null)
没有条件运算符?: 也没有复合运算符%= += -=()
条件分支：
if 表达式---一定注意SQL的if语句不加小括号
begin
end
else
begin
end
存储过程的创建：
create procedure(或简写作proc) 存储过程名
形参名 形参类型,
形参名 形参类型,
......
形参名 形参类型
as
函数体
go
存储过程的调用：
存储过程名 实参1,实参2,...实参n
案例一：往Info中送入数据，如果主键已经存在，则执行修改操作，否则执行插入操作
案例二：做一个存储过程，输入水果代号、人员用户名、购买数量，把库存和账户扣掉相应的值
案例三：同案例二。但要检查：1.库存够不够 2.余额够不够
案例1
create procedure testinfo
@code varchar(50),
@name varchar(50),
@sex bit,
@nation varchar(50),
@birthday datetime
as
declare @c int
select @c=count(*) from info where code = @code
if @c>0
begin
update info set name = @name, sex = @sex, nation = @nation, birthday = @birthday where code = @code
end
else
begin
insert into info(code,name,sex,nation,birthday)values(@code,@name,@sex,@nation,@birthday)
end
go
testinfo '代号','姓名1','性别1','民族','生日1'
select * from info//执行插入数据
testinfo '代号','姓名2','性别2','民族','生日2'
select * from info//执行修改数据
案例2
create proc buyfruit
@uid varchar(50),--用户名
@id varchar(50),--水果代号
@count int--购买数量
as
--一、扣库存
update fruit set numbers = numbers - @count where ids = @id
--二、扣钱
declare @cost decimal--花费
--1.查出单价
declare @p decimal--单价
select @p=price from fruit where ids = @id //从表中查询单价
--2.计算花费
set @cost=@count*@p
--3.修改账户余额
update login set account = account - @cost where username = @uid
go
buyfruit '用户名代号','水果代号',购买数量//执行后查询库存和账户是否扣掉相应的值
↓ 账户扣掉的钱数和实际应扣的钱数不符，检查后发现是@count的数据类型不对，应强转为cast(@count as decimal)且应定义@cost、@p的decimal
create proc buyfruit
@uid varchar(50),--用户名
@id varchar(50),--水果代号
@count int--购买数量
as
--一、扣库存
update fruit set numbers = numbers - @count where ids = @id
--二、扣钱
declare @cost decimal(8,2)--花费
--1.查出单价
declare @p decimal(8,2)--单价
select @p=price from fruit where ids = @id //从表中查询单价
--2.计算花费
print'单价:'+cast(@p as varchar(50))
set @cost=cast(@count as decimal)*@p
print'花费:'+cast(@cost as varchar(50))
--3.修改账户余额
update login set account = account - @cost where username = @uid
go
buyfruit '用户名','水果代号',个数//执行后查询库存和账户是否扣掉相应的值
↓因为属于修改，所以create应换为alter
alter proc buyfruit
@uid varchar(50),--用户名
@id varchar(50),--水果代号
@count int--购买数量
as
--一、扣库存
update fruit set numbers = numbers - @count where ids = @id
--二、扣钱
declare @cost decimal(8,2)
--1.查出单价
declare @p decimal(8,2)
select @p=price from fruit where ids = @id //从表中查询单价
--2.计算花费
set @cost=cast(@count as decimal)*@p
--3.修改账户余额
update login set account = account - @cost where username = @uid
go
buyfruit '用户名','水果代号',个数//执行后查询库存和账户是否扣掉相应的值
2015.5.31
案例3
select * from fruit
select * from login
alter proc fruitbuy
@uid varchar(50),--用户名
@id varchar(50),--水果代号
@count decimal(8,2)--购买数量
as
一、检查余额和库存
--(一)准备数据：购买数量、库存数量、总花费、余额
--1.准备库存
declare @kc int
select @kc=numbers set fruit where ids = @id
--2.准备余额
declare @ye decimal(8,2)
select @ye=account set login where username = @uid
--3.准备花费
--(1)准备单价
declare @dj decimal(8,2)
select @dj=price set fruit where ids = @id
--(2)计算花费
declare @cost decimal(8,2)
select @cost=@dj*@count
--(二)比较判断
if @kc>numbers and @ye>account
begin
二、扣库存扣余额
--(一)扣库存
update fruit set numbers = numebers - @count where ids = @id
--(二)扣余额
update login set account = account - @cost where username = @uid
--(三)向订单中添加数据
declare @ordercode varchar(50)
set @ordercode=@uid+cast(YEAR(GETDATE()) as varchar(50))+cast(MONTH(getdate()) as varchar(50)) + cast(DAY(GETDATE()) as varchar(50)) + cast (DATEPART(hour,GETDATE()) as varchar(50))+cast(DATEPART(minute,GETDATE()) as varchar(50)) +cast(DATEPART(SECOND,GETDATE()) as varchar(50))
insert into orders values(@ordercode,@uid,GETDATE())
--(四)向订单明细中加数据
insert into orderdetails values(@ordercode,@id,@count)
print'购买成功'
else
begin
print'库存或余额不足'
end
go
select * from orders
fruitbuy '用户名代号','水果代号',购买数量
触发器
1.什么是触发器？---一段SQL代码，挂到某个表的某个增、删、改的操作上，当这个表执行相应的操作时，就会触发这段相应的SQL代码。
触发器与存储过程的区别：
①存储过程是独立于表存在的，触发器需要依附某个表的某个操作。
②存储过程需要使用名称去调用才能执行，触发器则在表的操作过程中自动被触发调用。
2.触发器的分类：
after触发器---先执行表的增删改的操作后，再触发触发器。
instead of 触发器---不执行表的增删改操作，它的这些操作只起到触发触发器的功能。
3.创建触发器的语法
create trigger 触发器名 on 表名 after/instead of insert/delete/update
as
go
4.触发器中两个临时表：inserted,deleted---触发器在运行时的有着固定名字的两个临时表
这两个表是临时表，触发器执行完成后，会自动消失，再次触发会再次创建。
这两个表的结构与on后面的表的结构是一样的（列名、列数、类型），而且里面只有一条记录。
插入操作：把新增的数据放到inserted表中。
删除操作：把删除的数据放到deleted表中。
修改操作：把旧数据放到deleted表中，把新数据放到inserted表中。
5.对两个临时表的使用。
从两个临时表中把数据取出来放到变量中，以备后面的使用。
案例一：做一个汽车变动表，汽车的增加、删除后都往变动表中增加一条。用触发器来实现。(after触发器)
案例二：把Info表的数据删除，删除之前先用触发器把Work和Family两个表中的相应数据删掉。(instead of触发器)
案例1
select * from Car
select * from cardelete
delete from Car where Code='c003'
--创建触发器
create trigger TR_Car_Delete on car after delete
as
--触发器在运行的时候会有两个临时表，表的名子是固定的分别叫inserted,deleted
--第一步：把刚刚删除的这行数据的name和price取出来，放在两个变量中
declare @name varchar(50),@price decimal(8,2)
select @name=name,@price=price from deleted
--第二步：把上面的两个变量插入到CarDelete表中
insert into CarDelete values(@name,@price)
go
案例2
create trigger TR_Car_Insert on car after insert
as
--第一步：把插入的数据的name和price取出来，放在两个变量中
declare @name varchar(50),@price decimal(8,2)
select @name=name,@price=price from inserted
--第二步：把上面的两个变量插入CarDelete表中
insert into cardelete values(@name,@price)
go
select * from info
select * from work
select * from family
delete from Info where Code='p003'
alter trigger TR_INFO_DELETE on Info instead of delete
as
--从临时表中获取出主键值
declare @code varchar(50)
select @code=code from deleted
--从work中的InfoCode列中删除上面那个值
delete from work where infocode = @code
--从Family中的InfoCode列中删除上面的那个值
delete from family where infocode = @code
--从Info中删除Code中上面的那个值
delete from info where code = @code
go
事务：
一、什么是事务
能够保证数据一致的代码控制。它要么执行提交，要么回滚到事务的初始状态。
二、事务的四大特性：ACID
A：原子性(Atomicity)---事务不可折开，要么都执行，要么都回滚，无中间状态。
C：一致性(Consistency)---数据前后要一致。
I：隔离性(Isolation)---事务在执行的过程中是不许外界干扰的。
D：持久性(Durability)---事务的所有操作是存在硬盘上的。
三、事务的代码
定义：
事务的开始：begin transaction(tran)
事务回滚：rollback
事务提交：commit
@@ERROR---系统变量，判断最近一条语句是否执行成功。成功返回0，否则返回错误代号。
例如：人员信息表中有人员代号列、人员姓名列、人员性别列、人员民族列、人员生日列
法一：使用@@ERROR和if判断来使用事务
declare @t int --记录累计的错误信息
set @t = 0
begin tran --启动事务
insert into Info values('p003','吴倩','0','n004','1990-8-12')
set @t = @t + @@ERROR --累计错误信息
insert into Info values('p006','张三','1','n004','1992-3-19')
set @t = @t + @@ERROR --累计错误信息
insert into Info values('p005','李四','0','n001','1991-12-24')
set @t = @t + @@ERROR --累计错误信息
if @t<> 0 --如果累计发现有错误 ，就回滚
begin
rollback
end
else --如果累计中没有发现错误，就提交
begin
commit
end
法二：使用try...catch....来使用事务
begin try
insert into Info values('p007','王五','0','n002','1991-4-1')
insert into Info values('p009','张四','1','n004','1992-3-19')
insert into Info values('p008','李九','0','n001','1991-12-24')
commit
end try
begin catch
rollback
end catch
数据库总结
三层关系：文件(mdf主数据文件，ndf次数据文件，ldf日志文件) 界面(①可视化的操作②代码填写界面) 服务(服务名称MSSSQLSERVER)
两个验证：Windows验证，SQLServer验证
如何开启SQLServer验证？
在服务器属性中，设置身份验证模式为SQLServer和Windows验证，并设置sa账户密码
数据库、表的几个操作：
create(创建)/drop(删除)/alter(修改) database/table 数据库名/表名
text---长文本，可放置数量较大的数据(一般的存储类型只能放置4000汉字或8000英文)
表的三种界面：设计界面、编辑界面、查询界面
表的约束：
①防空：非空、默认
②防重：主键(不能空不能重)、唯一键(可空但不能重)、自增长列(存放整型、Decimal型数据，使用过的数据不能再次使用)
③防错：check、外键(保证引用的完整性，一般有两个表：主表，从表)、类型
表的增删改查
增 insert into 表名(列名) values(值)---列名和值要一一对应
删 delete from 表名 where子句
改 update 表名 set 列名=值 where子句
查 select
①简单查询：【行】筛选、【列】投影(特殊：distinct去重复)
②复杂查询：group by，join，子查询，联合，筛选(等值，不等值)，排序order by(升序asc，降序desc)
(不等值查询的特殊形式：多条件查询 and，or)
(多条件查询的特殊形式：模糊查询 ①like②%，_)
(group by是针对某列的查询，语法是group by+列名/统计函数)
(group by...having(*)是对分组后的数据进行再筛选)
连接查询---列的扩展①，from...where子句②join...on...(外连：left join，right join 内连：join)
联合查询---两个表的某几列横向粘贴形成一个新表，再进行数据查询union
子查询---无关子查询，相关子查询
其他查询---in，is null，between...and...，exists
视图---主要用来展示数据，调用简单，运行速度快，但不能进行增删改
create view 视图名
as
go
索引---提高查询效率，但同时增删改效率会降低
create index 索引名
存储过程
create procedure(proc) 存储过程名
as
go
触发器---after触发器，instead of触发器
create trigger 触发器名 on 表名 after/instead of 操作
as
go
事务---独立执行的代码块
(四特性:ACID---A：原子性(Atomicity)C：一致性(Consistency)I：隔离性(Isolation)D：持久性(Durability))
(三操作：开始---begin transaction(tran) 提交---commit 回滚---rollback)
@@ERROR+if... --- @@ERROR是系统变量，@@ERROR+if...用来判断最近一条语句是否执行成功，成功返回0，否则返回错误代号
begin try，begin catch 抓错语句
作业：试着写一个分页存储过程(汽车表)(子查询)