SqlServer基础复习

数据类型

• 包括整数类型（可以用来做主键）的如bit,int ,samllint,tinyint,bigint,存储的范围不同，常用的有int，bigint等；树脂类型decimal(p,s)【p为固定精度，s为宽度，使用这种数据类型时必须指定范围和精度】，numeric（decimal的同义词）money（货币型，精确到货币的万分之一），smallmoney（货币型），float（近似数值型，供浮点数使用）等。
• 字符类型：固定长度char，nchar，可变长度varchar，国际化可变长度nvarchar。固定长度char类型的，用来保存具有固定长度的字符串。如果字符串的长度不满足所定义的长度，则用空格填充，这样在读取字符串的时候也将空格读取出来，很不方便，可变长度varchar，也需要指定一个最大长度，但是如果数据不足最大长度，数据库不会将剩下的部分用空格填充；固定长度和可变长度只能存储基于ASXII的字符，这样对于中文，日文，韩文等Unicode字符集的程序员来说就会造成存储问题，解决办法使用国际化可变长度nvarchar。
• 日期类型：smalldatetime,timestamp(时间戳)，datetime时间格式为‘2008-1-1 19:22:22’
• 二进制类型：在存储一幅图片或者是一段视频的时候，就要使用二进制类型的字段，这种字段通常能够保存非常大的，没有固定结构的数据，如image，可以保存任意的二进制数据。

操作数据表

# 创建数据表
 create table table(id int not null,name varchar(20),age int,primary key(id));
 create table ftable(id int not null, name varchar(20) account int ,customerId int ,
 contraint fk_table_1 foreign key(customerId) refrences table(id))

修改已有表

 -- 添加字段： alter table表名 add 字段名字段类型 
 -- 删除字段 alter table drop 字段名
alter table table add sex char(2);
alter table table drop sex;

删除数据表

 -- 包括清空数据表delete from 和销毁数据表 drop ，两者的区别在于，delete只是将表中的信息全部清空，但是表的矿建还在，还可以使用insert语句为表插入新的数据，而drop是将数据表直接在数据库销毁，数据表不存在了。　
  delete from table
  drop table1
　delete from table where age>20 and age <40

查询插入更新数据

select * from order by desc 
 -- 单字符通配_：匹配单个出现的字符，多字符% ：匹配多次出现的字符， 集合[] ：匹配满足集合要求的字符。
select * from table where name like '_angjun';
select * from table where name like '%y'
select * from table where name like '[wj%]'
select * from table where name like '[^wj%]'
select * from table where name is null
 -- 多值判断
select * from table where age in (19,20,21)

insert int table (id ,name age) values(1,'wangjun',22);
update table1 set age=24 where name='wangjun'

数据分组

select * from table group by age
select * from table where age >20 group by age
 -- 只检索人数大于2的年龄端段 having语句的使用
select age ,count(*) as N'总人数' from table group by age having count(*)>2
 -- 区别：having语句是分组后的信息进行过滤，而where语句是对原始数据进行过滤
 -- 限制结果集行数
select top 5 * from table order by age desc

联合结果集 union union all函数

 -- 查询正式员工和临时工的最高、最低年龄 ，分开查
 select '正式工的最大年龄' , max(FAge) from T_Employee
 union
 select '正式工的最小年龄' , min(FAge) from T_Employee
 union
 select '临时工的最大年龄' , max(FAge) from T_TempEmployee
 union
 select '临时工的最小年龄' , min(FAge) from T_TempEmployee
 -- 联合结果集使用原则：一是每个结果集必须有相同的列，二是每个结果集的列类型必须相容。
 -- union和union all 的区别：union会自动将查询出来的结果进行重复比较如果有重复，则只显示一条，而union  all 则将查询出来的结果直接显示出来。所以，在没有特殊要求下，尽量使用union all 进行连接。

函数，包括数学函数，字符串函、日期函数以及其它函数等

  -- 数学函数，包括常用的求绝对值abs()，求指数函数power（）,求平方根sprt()，求随机数rand()，舍入到最大整数celing(),舍入到最小整数floor()，四舍五入round()等。
 select abs(-12.1)
 select power(2.2)
 select rand()
 -- 字符串函数，包括计算字符串的长度，装换大小写，去掉字符串两边的空格，字符串替换，等到字符串的ASCII值等
select len('sqlserver')
select upper('sqlserver')  --SQLSERVER
select upper('SQLSERVER')  --sqlserver
select substring('sqlserver',4,6)
select charindex('q','sqlserver') --2
select left('sqlserver') --sql
select right('sqlserver') -- ver
select replace('sqlserver','sql','SQL') --SQLserver
select ASCII('a')
  -- 日期函数，date， time，datetime，datestamp
  -- 获取当前时间
select getdate（）
  -- 计算据今天3个月的日期
select dateadd（month，1，getdate()）
  -- 计算今天星期几
select datename(weekday,getdate())

索引和约束

• 但一个数据表中的数据量越来越大时，检索一天记录的速度也会变慢，创建索引是一个很好的解决办法，索引提高的搜索效率，但是索引会占据一定的磁盘空间，减缓了数据插入和删除的速度，同时会造成存储碎片的问题，由于索引是采用的B树结构存储的，在删除一天记录的时候，索引项是不会被删除的，这样经过一段时间的增删改查之后就会产生存储碎片，道理通内存碎片磁盘碎片一样。

  -- 创建索引
 create index idx_name on table（name）
  -- 删除索引
drop table.inx_name
  -- 约束：包括非空约束，唯一约束，复合约束，主键约束，外键约束等。
  -- 非空约束
not null
  -- 唯一约束
create table （id int not null ，name char（20） unique）
  -- 复合约束 ，被约束的字段不能同时重复
create table person(
id int not null,name varchar(20),number varchar(20) ,age int,deparUne nvarchar(20),
constraint uni_num_deparnum unique(number,departUne),
constraint uni_name_num unique(name,number)
)
  -- 如果在创建表的时候，没有添加约束，可以利用alter来修改添加
alter table person add constraint uni_name unique(name,number)
  -- 删除约束
alter table person drop constraint uni_name
  -- check约束
create table person (id int not null,name vchar(20),number varchar(20),age int check(age>0))
  -- 主键约束
create table T_Author (FId int primary key ,FName varchar(20),FAge  int )
create table T_Book (FId int primary key ,FName varchar (20),FAuthotId int ,
    foreign key ( FAuthorId) references T_Author (FId)) 

表连接

• 当数据表中存在许多重复的冗余信息时，就要考虑将这些信息建在另一张新表中，在新表中为原表设置好外键，在进行数据查询的时候，就要使用到连接了，表连接就好像两根线，线的两端分别连接两张表的不同字段上，将两个字段进行关联。表连接分为内连接（inner join）、交叉连接、自连接和外连接等。

  -- 内连接
　select c.name,o.number,o.price from T_order o inner join T_Customer on o.customerId = c.id where c.name='wj'
  -- 交叉连接
  -- 与内连接相比，交叉连接非常简单，不需要on子句，交叉连接将表中的所有记录都包含到结果集中，交叉连接分为隐式连接和显示的cross join连接，其中隐式的交叉连接，所有的数据库都支持，cross join 显示的cros join显示的交叉连接DB2数据库不支持。
  -- 隐式的交叉连接
　　select T_Customer.id ,T_Customer.name,T_Customter.age,T_Order.name,T_Order,number form T_Customer ,T_Order
　　--显示的交叉连接
 　 select T_Customer.id,T_Customer.name,T_Customer.age,T_Order.name,T_Order.number from T_Customer across join T_Order
  -- 自连接
  -- 其用途就是检索一张表中的内部情况，例如检索T_Order表中订单类型相同的订单列表
　　select o1.FNumber ,o1.FName,o1.FType, o2.FNumber ,o2.FName,o2.FType
　　from T_Order o1 
  inner join T_Order o2 on o1.FType=o2.FType and o1.FId <o2.FId 
  -- 外连接，内连接要求连接的两张表必须有匹配的记录，但是如果说T_Order表中的FCustomerId一列有null值，那么内连接是无法将这条信息查询出来的，但是我们是需要这条数据的，这样就需要外部连接了，外连接包括左外连接，右外连接和全外连接，全外连接在数据库MySql中是不支持的，所以，可以使用union all 联合两个查询语句，即左外连接查询和右外连接查询。
　-- 左外连接就是将左表中的所有数据，无论是否满足on后面的条件语句，都匹配出来。右外连接就是将右表中的所有数据都匹配出来，无论是否满足on后面的条件语句，都匹配初恋，而全外连接，就是将两个表中的所有数据，无论是否满足on后面的条件语句都匹配出来。
 -- 左外连接取出左表T_Customer中的所有数据，无论是否匹配 T_Customer.FId =T_Order.FCustomerId
　  select c.number,c.name,o,number,o,price 
　　from T_Customer c
　　left outer join T_Order o on T_Customer.id =T_Order,customerId
 -- 右外连接 取出右表T_Order中所有的数据无论是否匹配了条件
　　select c.number,c.name,o.number,o.price 
　　from T_Customer c
　　right outer join T_Order o on T_Customer.id=T_Order.CustomerId
 -- 全外连接,取出两张表中的所有数据，无论是否匹配 T_Customer.FId =T_Order.FCustomerId
select  c.FNumber ,C.FName,o.FNumber ,o.FPrice
from T_Customer c 
full outer join T_Order o on T_Customer.FId =T_Order.FCustomerId

子查询

• sql允许将一个查询语句作为结果集提供给其他sql语句使用，被当做结果集的查询语句就是子查询。可以将子查询看做是一张临时生产的数据表，这张数据表在开始时被创建，在查询结束的时候被删除。子查询可以使用在select语句，insert语句，update语句。子查询大大简化了SQL语句的编写，提高了效率，但是如果使用不当，容易造成性能问题。

存储过程

craete proc GetPage(
     @pageIndex int,
     @pageSize int,
     @pageCount int output,
     @RecordCount int output       
)
as
begin
     set @RecordCount= select count(*) form emp
     set @pageCount =celling((@RecordCount*0.1)/@pageSize)
     select * from ( select *, row_number over(order by id) as rows) as t
     where rows between (@pageIndex-1)*@pageSize and pageIndex*pageSize
end