MS SQLServer 基础知识回顾(一)
一、 数据类型
包括整数类型(可以用来做主键)的如bit,int,smallint,tinyint,bigint,存储的范围个不行同,常用的有int,bigint等;数值类型decimal(p,s)【p为固定精度,s为宽度,使用这种数据类型时必须指定范围和精度】,numeric(decimal的同义词),money(货币型,精确到货币的万分之一),smallmoney(货币型),float(近似数值型,供浮点数使用)等。
字符类型:固定长度char,nchar,可变长度varchar,国际化可变长度nvarchar。固定长度char类型的,用来保存具有固定长度的字符串,如果字符长度不满所定义的长度,则用空格填充,这样在读取字符串的时候也将空格读取出来,很不方便;可变长度varchar,也需要指定一个最大长度,但是如果数据不足最大长度,数据库不会将剩下的部分用空格填充;固定长度和可变长度只能存储基于ASCII的字符,这样对于中文、日文、韩文等Unicode字符集的程序来说就会造成存储问题,解决办法使用国际化可变长度nvarchar。
日期时间类型:smalldatetime,timestamp(时间戳),datetime-格式为‘2008-8-8 19:30:34’。
二进制类型:在存储一幅图片或者一段视频的时候,就要使用二进制类型的字段,这种字段通常能够保存非常大的,没有固定结构的数据。如image,可以保存任意的二进制的数据。
二、操作数据表
1、创建数据表
sql语句创建数据表:create table 表名(字段1 字段类型,字段2,字段类型…… 约束定义1, 约束定义2……) (关于主键和外键约束,稍后再详细讲解)
-- 例如创建用于保存人员信息的数据表 create table T_Person (FId int not null , FName varchar(20),FAge int,primary key(FId) )
-- 创建有外键的表T_Order,其中的FCustomerId为表T_Person的主键FId create table T_Order (FId int not null ,FName varchar(20),FAccount int ,FCustomerId int , foreign key (FCustomerId) references T_Person (FId))
2、修改已有表
添加字段:alter table 表名 add 字段名 字段类型 。删除字段 :alter table 表名 drop 字段名
-- 为T_Person 添加字段FYearofBirth alter table T_Person add FYearofBirth int -- 删除表T_Person中的字段FAge alter table T_Person drop FAge
3、删除数据表
包括清空数据表(delete from)和销毁数据表(drop),两者的区别在于,delete只是将表中的信息全部清空,但是表的框架还在,还可以用insert语句为表插入新的数据,而drop是将数据表直接在数据库中销毁,改表不存在了。
语法 delete from 表名 drop表名
-- 例如 :删除表T_Person delete from T_Person drop T_Person -- 删除年龄在20-30岁之间的数据 delete from T_Person where FAge >20 and FAge<30
4、为数据表插入新的数据(insert语句)
语法:insert into 表名 (字段1,字段2……字段n) values (值1,值2,……值n)
-- 例如: 为T_Person插入一条数据 insert into T_Person (FId , FName ,FAge ,FYearofBirth) values (1,'Tom',24,1989)
再插入数据时,要注意原表中的约束条件,如FId就有非空约束,如果不为FId插入数据,则会报错。同样FId有主键的约束,如果为T_Person表再插入一条FId=1的数据,也会报错,这违反了主键必须唯一的约束。再如外键约束,请看实例:
-- 现在的T_Person表中只有一条名为Tom的数据,现在向T_Order表中插入一条数据 insert into T_Order (FId ,FName,FAccount ,FCustomerId) values (1,'Milk',100,2)
此时会报错“INSERT 语句与 FOREIGN KEY 约束"FK__T_Order2__FCusto__1BFD2C07"冲突。该冲突发生于数据库"Sql jindian",表"dbo.T_Person", column 'FId'。语句已终止。”这是因为在表T_Person中没有FId=2的这条数据,违反了外键的约束。
5、数据更新(Update语句)
-- 将T_Person表中所有数据的FAge列的值更新为30,执行完后,该列的值全变为30 update T_Person set FAge=30
-- update还可以执行带有where的语句,将在1990年出生的人的年龄更新为30岁 update T_Person set FAge=30 where FYearofBirth =1990同样的,在更新语句中非空约束,主键和外键也会对其产生影响。如对于T_Person中的FId字段,有非空约束和主键约束,那么在更新的时候,不能将一条数据的set FId=null ,违反了非空约束,不能将FId更新为已经存在的FId的值。对于更新T_Order表中的数据时,也不能更新T_Person表中没有的数据,因为存在T_Order.FCustomerId =T_Person.FId 这个外键约束。
6、数据表的检索(select语句)
(1)、取出数据表T_Perosn中的所有数据,下面的两条语句执行效果一样
select * from T_Person select FId ,FName,FAge,FYearofBirth from T_Person
(2)、排序 。例如,将表T_Person 中数据按照年龄的大小排列
select * from T_Person order by FAge DESCdesc是descend的缩写,意为下降,向下;asc为ascend的缩写,意为向上,上升,攀登
(3)、条件过滤,包括一般的where语句和通配符过滤(单字符,多字符和集合通配符)
select * from T_Person where FAge=20 and FYearofBirth >1990
单字符通配 _ :匹配单个出现的字符,多字符%:匹配多次出现的字符,集合[]:匹配满足集合要求的字符
-- 匹配姓名以任意一个字符开头,后面为erry的,例如kerry将会匹配出来 select * from T_Person where FName like '_erry' -- 匹配姓名中以y结尾,前面任意多字符 ,例如 kerry,lily等 select * from T_Person where FName like '%y' -- 集合匹配:姓名以s或者j开头的,不是以bt开头的 select * from T_Person where FName like '[sj]%' select * from T_Person where FName like '[^bt]%'没有添加非空约束的列是可以为空值的(null),有时我们要对空值进行监测
select * from T_Person where FName is null
反义运算符:< > = != 等这些是用来进行判断值的,其中不等于可以用多种形式表示
select * from T_Person where FAge != 20 and FAge >19 select * from T_Person where FAge <>20 and FAge >19 select * from T_Person where not (FAge =20) and FAge >19
“!”只能用在MS sql server和DB2中个,其它数据库不支持。推荐使用“<>”和”not ”来进行不等的判断
多值判断,范围值判断 IN语句,between and 语句的使用。
select * from T_Person where FAge =19 or FAge =20 or FAge =24 -- 上面这条语句等价于下面这条 select * from T_Person where FAge in (19,20,24) -- 选择年龄在19到28岁这个年龄段的人的信息 select * from T_Person where FAge between 19 and 28
(4)、数据分组(group by)与聚合函数(max min count sum等)
-- 将T_Person表中数据按照年龄相同的检索 select * from T_Person group by FAge select * from T_Person where FAge >20 group by FAge -- 查看每个年龄段的人数 select FAge ,count (*) as N'总人数' from T_Person group by FAge -- 只检索人数大于2的年龄段 having语句的使用 select FAge ,count (*) as N'总人数' from T_Person group by FAge having count(*)>2
区别: having语句是滴分组后的信息进行过滤,而where语句是对原始数据进行过滤
(5)、限制结果集行数
有时候进行数据检索的时候只需要检索结果集中的部分行,比如“检索成绩排在前3位的学生信息”,这种功能被称作限制结果集行数。
-- 从数据表T_Employee中检索工资排在前五位的人员信息,下面两句查询等价 select top 5 * from T_Employee order by FSarlary DESC select * from ( select ROW_NUMBER () over (order by FSarlary ) as row,FName from T_Employee ) a where a.row <=5
(6)、联合结果集union ,union all函数
有时候我们需要组合两个完全不同的查询结果,而这俩个查询结果直接没有任何的联系,只是需要他们显示在一起,这时候可以使用union函数联合。
-- 查询正式员工和临时工的最高、最低年龄 ,分开查 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 进行连接。
三、函数,包括数学函数,字符串函、日期函数以及其它函数等
1、数学函数,包括常用的求绝对值函数abs() ,求指数函数power() ,求平方根 sqrt () ,求随机数 rand () ,舍入到最大整数celing () ,舍入到最小整数 floor () ,四舍五入 round () 等,还有一些求正弦、余弦、正切、余切、反正弦、反正切等函数。
select abs(- 12.27) select power ( 2,2 )-- 求2的平方 select rand () -- 生成随机数
2、字符串函数,包括计算字符串的长度,转换大小写,去掉字符串两边的空格,字符串替换,得到字符串的ASCII值等。 看例子:
-- 计算 sqlserver的长度 select len ('sqlserver') -- 返回 9 select upper('sqlserver') -- 返回 SQLSERVER select upper('SQLSERVER') -- sqlserver select substring('sqlserver',4,6) -- 返回 server select charindex('q','sqlserver'') -- 返回 2,查询q在sqlserver中的位置 select left('sqlserver',3) -- 取出前三个字符 ,返回 sql select right ('sqlserver',3) -- 取出后三个字符,返回 ver select replace('sqlserver','sql','SQL') -- 替换字符 ,返回 SQLserver select ASCII('a') -- 取a的ASCII值,返回 97 select ASCII('abc') -- 返回 97 ,只取第一个字符的ASCII值
3、日期函数,包括日期(date),时间(time),日期时间(datetime)时间戳(datestamp)等。
获取当前时间,计算日期相加减函数,计算两个日期之间的差额函数,计算一个日期是星期几,取得指定日期的指定部分。
-- 获取当前时间,返回xx- xx-xx ss:ss:ss select getdate()-- 计算据今天3个月的日期 select dateadd (month,3,getdate()) -- 计算两个日期之间的差额,2011-1-1距今多少天 select datediff(day,'2011-1-1',getdate()) -- 计算今天星期几 select datename(weekday,getdate()) -- 获取今天的时间部分 select datapart(hour,getdate())
4、其它函数如类型转换(convert,cast),空值处理(coalesce,isnull)case函数等。
-- 将字符类型的20转换成int型 select cast('20' as int ), convert (int ,'20')
空值处理:coalesce(expression,value1,value2……,valuen),如果expression不为空,则返回expression的值,若为空,则判断value1的值,value1不空,返回value1 的值,value1空,判断value2……,如果valuen的值也为空,则返回null。isnull(expression,value)是其简化版。
-- 将FName为null的显示为匿名 select coalesce(FName,N'匿名') from T_Person select isnull (FName,N'匿名') from T_Employee
case函数:流程控制函数,可以控制返回,例如:如果年龄>20,则返回姓名,否则返回小孩
select ( case when FAge >20 then FName else N'小孩' end ) from T_Person
--case函数基本语句的练习 select FName, ( case FLevel when 1 then '普通客户' when 2 then '会员' when 3 then 'VIP客户' else '未知类型客户' end ) as 客户类型 from T_Employee
--例如:从表中选出收入小于4000的为低收入人群,收入在4000-6000之间的为中等收入,大于6000的为高收入
select FName, ( case when FSarlary <'4000' then '低收入' --FSarlary 是varchar类型的,所以'4800' when FSarlary >='4000' and FSarlary <'6000' then '中等收入' else '高收入' end ) as 收入水平 from T_Employee
四、索引与约束
当一个数据表中的数据量越来越大时,检索一条记录的速度也会变慢,创建索引是一个很好的解决办法,索引提高的搜索效率,但是索引会占据一定的磁盘空间,减缓了数据插入和删除的速度,同时会造成存储碎片的问题,由于索引是采用B树结构存储的,在删除一条记录时,索引项并不会被删除,这样经过一段时间的增删查改之后就会产生存储碎片,道理同内存碎片磁盘碎片一样。
创建索引:create index 索引名 on 表名(字段1,字段2……字段n)
create index idx_name on T_Person (FName)
删除索引:drop 表名.索引名
drop T_Person.idx_name
约束:包括非空约束,唯一约束,复合约束,主键约束,外键约束等。
1、非空约束not null
create T_table (FId int not null ,Fnumber int )
2、唯一约束,防止特定的列中含有相同的值,比如说一个邮箱只能注册一次。unique约束
create T_table (FId int not null ,Fnumber int unique)
3、复合约束,被约束的字段不能同时重复。例如一个公司有多个部门,每个部门有多个小组,每个部门里面的小组都可以从1开始往后排,小组名相同的小组必定部门名不相同。constraint 约束名 unique (字段1,……),可以向一个表中添加多个复合约束,只要约束名不同即可。
create talbe T_Person(FId int not null,FName varchr(20),FNumber varchar(20),FAge int ,FDepartNum nvarchar(20), constraint uni_ num_departnum unique (FNumber,FDepartNum), constraint uni_name_num unique (FName,FNumber)
如果在创建表的时候,没有添加约束,可以利用alter来修改添加,alter table 表名 add constraint 约束名 unique (字段1,字段2……)。
alter table T_Person add constraint uni_name_num unique (FName,FNumber)
删除约束 alter table 表名 drop constraint 约束名。
alter table drop constraint uni_name_num
4、check约束,检查输入的记录值是否符合一个条件,如果不符合,则对数据表的修改不会成功,check约束同唯一约束一样,在声明的时候在字段后面就可以声明一个约束。
create talbe T_Person(FId int not null,FName varchr(20),FNumber varchar(20),FAge int check (FAge >0))这样的check约束是不能约束两列的,比如约束FWorkYear< FAge ,在哪个字段后面添加这个约束都是不可以的。解决方法:在create table 语句的末尾使用constraint 约束名 check (约束条件)来约束这个条件。
-- 这条语句是错误的,不能在字段后声明约束两列 create talbe T_Person(FId int not null,FName varchr(20),FNumber varchar(20),FAge int , FWorkYear int check (FWorkYear < FAge )) -- 解决方法 create talbe T_Person(FId int not null,FName varchr(20),FNumber varchar(20),FAge int ,FWorkYear int, constraint ck_age_workyear chect (FWorkYear < FAge) )
5、主键约束,主键必须是唯一的,不能重复的,可以创建复合主键,即在多个字段上创建主键,但是这样做会带来很大 的系统性能问题,除了考虑向下兼容的问题,还是不要用复合主键。
create table T_Person (FId int not null primary key ,FName varchar(20)) create table T_Person (FId int not null ,FName varchar(20),primary key(FId)) -- 两条语句等价6、外键约束,当一些信息重复出现时,我们就要考虑将这些信息提取出来创建到另一张表中,然后再原表中引用这些数据,这就用到了外键。
创建外键:在create table 末尾添加:foreign key (字段名)references 表名(字段名)
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))
删除外键的时候,必须先删除被关联的表,如上例就是必须先删除T_Author,在删除T_Book
drop table T_Author drop table T_Book
作者:xirong
出处:http://www.cnblogs.com/xirongliu
说明:本文是自己学习编程的一个历程,版权归作者和博客园共有,欢迎转载,请标明原文连接,如有问题 xirong 联系我,非常感谢。