这些天为了面试，弄得人受不了，特别是小公司，你是招程序员呢？还是招项目经理，工资没有大公司的高，要求却很高。不过也能理解啊，人少，就要什么都会干，能独当一面。好了，说正题吧，现在小弟在这边把sqlserver的一些基础和优化给大家整理一下，大家以后面试或者设计数据库的时候可以稍微参考一下，小弟水平不高，如果有什么说错的地方，大家一定要指正啊！
下面一篇文章，基本上会把sqlserver从最基本的函数到t-sql语句，存储过程，事务，触发器，到一些优化都介绍一下，相信应该是目前大家可以找到的最全的资料吧。当然，除了一些大大们写的书和微软出的帮助文档了。1，表创建（只是为了方便大家看下面的例子，其实大家都会）
create table Score
(
id int identity(1,1) primary key not null,
userName varchar(20) not null,
score int not null)
表结构是这样的
id username score
1 sdf 1232,简单的增删改查
首先是增加
insert into score values('sdf',123)对于这种的必须要values种的字段和表中一一 对齐
insert into score (score,userName) values (345,'wer')这种方式就不用了
下来就说查询吧
select * from score查询表中所有的数据
得到结果
1 sdf 123
2 wer 345
select username from score查询表中所有的姓名
得到结果
sdf
wer
由于数据量太少我们再插入几条
insert into score select username,score from score
这句sql语句的意思是将score表中现有的数据继续插入到score表中（即两条变四条，四条变八条），好，我们现在score表中有1024条数据
select * from score where id=3查询id=3的所有数据
得到结果
3 sdf 123
select top 5 * from score 查询前五条数据
得到结果 大家看id就知道了
1 sdf 123
2 wer 345
3 sdf 123
4 wer 345
5 sdf 123
select * from score where id between 50 and 55查询id在50到55的所有数据(含50，55)
得到结果
50 wer 345
51 sdf 123
52 wer 345
53 sdf 123
54 wer 345
55 sdf 123
好，下面我们利用上面说的top写一个分页的语句，比如每页有5条，要第七页的数据
select top 5 * from score where id not in
(select top (5*6) id from score)
得到结果
31 sdf 123
32 wer 345
33 sdf 123
34 wer 345
35 sdf 123当然not in我是不建议大家用的，在后面的优化上面会说到
select * from score where username like '%s%'
查询username中含有s的所有数据，当然like我也是不建议大家用的，在后面的优化上面会说到
select * from score order by id desc按照id倒序查询所有
得到结果
1024 wer 345
1023 sdf 123
1022 wer 345
1021 sdf 123
1020 wer 345
1019 sdf 123
1018 wer 345
select username from score group by username 按照姓名分组查询
得到结果
sdf
wer
小泽
select score from score group by score having score>124 按照分数分组再找到大于124的
好，先来说一下修改
update score set username='小泽' where id=1
sqlserver常用函数
----统计函数----
avg--平均值
select avg(id) from score得到512
max--最大值
select max(id) from score得到1024
min--最小值
select min(id) from score得到1
sum--求和
select sum(id) from score得到524800
count--求数量
select count(score) from score得到1024
/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
select sin(90) as '正弦',cos(90) as '余弦',tan(90) as '正切',cot(90) as '余切'
0.893996663600558 -0.44807361612917 -1.99520041220824 -0.501202783380153
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
这些数学函数我就不一一赘述了，需要用的时候大家查帮助文档就可以了/***取近似值函数***/
这个还是比较重要的
ceiling()返回大于它的最小整数
select ceiling(13.4)返回14
floor()返回小于它的最大整数
select floor(-13.4)返回-14
round()四舍五入
abs()返回绝对值
select abs(-34.2)返回34.2
sign()测试正负号 正得1.0负得-1.0 0得0
select sign(-8)得-1
rand（）取0-1之间的随机小数
select rand()得0.795489779469641不定
pi()返回π
select pi()得3.14159265358979
----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
select ascii('asdf')得97即a的ascii为97
CHAR() --函数用于将ASCII 码转换为字符
select char(97)返回a
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
这两个就不多说了
STR() --函数把数值型数据转换为字符型数据
select str(123456)得到'123456'
LTRIM() --函数把字符串头部的空格去掉
select ltrim(' 123')得'123'
RTRIM() --函数把字符串尾部的空格去掉
同上
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
select left(' 123',2)得到空格和1
select substring(' 123',2,2)返回12 从第二位开始截取2个字符
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
select CHARINDEX(' 123',' 1234')表示第一个参数在第二个参数中出现的第一个位置
ptindex()可以包含通配符
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个SOUNDEX 函数返回值的第一个字符不同
--1 两个SOUNDEX 函数返回值的第一个字符相同
--2 两个SOUNDEX 函数返回值的第一二个字符相同
--3 两个SOUNDEX 函数返回值的第一二三个字符相同
--4 两个SOUNDEX 函数返回值完全相同
QUOTENAME() --函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --函数将指定的字符串的字符排列顺序颠倒
REPLACE() --函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串----数据类型转换函数----
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
199 Jan 15 2000
----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
--函数返回两个指定日期在datepart 方面的不同之处
DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分
GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
下面我们来看一下索引
为了方便等会查看效率，我们重新建立两张表
create table class
(
id int identity(1,1) primary key not null,
className varchar(20) not null
)
go
create table student
(
id int identity(1,1) primary key not null,
studentName varchar(20) not null,
class_id int references class(id) not null
)
class表中的主键id为student表中的外键class_id
在class中插入三条数据
insert into class values('一班')
insert into class values('二班')
insert into class values('三班')
在student表中插入三条数据
insert into student values('刘德华',1)
insert into student values('张惠妹',2)
insert into student values('梁朝伟',3)
然后insert into student select studentName,class_id from student
将数据重复插入，现在student表中有1572864条数据
现在表中有主键索引，我们看一下使用主键查询所用的时间，使用下面的tsql语句
declare @startdate datetime
declare @enddate datetime
select @startdate=getdate()
--sql语句
select @enddate=getdate()
select datediff(ms,@startdate,@enddate) as '用时/毫秒' 可以测试出sql语句用时
本测试计算机为双核cpu，2g内存，运行qq1个，pplive一个，卡巴斯基杀软，360安全卫士一个
我们重新再studnet表中插入一条新的语句
insert into student values ('周杰伦',2)用时0秒
好，下面我们开始测试
select * from student where id=6使用主键索引的数据，用时0秒
select * from student where studentname='周杰伦'未使用索引的查询用时568毫秒
select * from student where studentname='周杰伦' and class_id=2未使用索引的复合查询用时263毫秒
下面我们查询多条数据
select * from student where studentname='刘德华'用时8420毫秒
select * from student where studentname='刘德华' and class_id=1用时8533毫秒
下面我们给studnetName创建一个简单索引
create index studnetN_index
on student(studentName)
下面我们继续测试
select * from student where studentname='周杰伦'用时0秒
select * from student where studentname='刘德华'用时6020毫秒
select * from student where studentname='刘德华' and class_id=1用时8250毫秒
创建组合索引
CREATE NONCLUSTERED INDEX [sdf] ON [dbo].[student]
(
[studentName] ASC,
[class_id] ASC
)
select * from student where studentname='刘德华' and class_id=1用时6223毫秒
insert into student values('wer',2)用时16毫秒
从以上我们可以看出使用适合的索引可以提高查询效率，但是增删改的效率却降低了，所以是否使用索引还是看项目的需求
下面一篇文章，基本上会把sqlserver从最基本的函数到t-sql语句，存储过程，事务，触发器，到一些优化都介绍一下，相信应该是目前大家可以找到的最全的资料吧。当然，除了一些大大们写的书和微软出的帮助文档了。1，表创建（只是为了方便大家看下面的例子，其实大家都会）
create table Score
(
id int identity(1,1) primary key not null,
userName varchar(20) not null,
score int not null)
表结构是这样的
id username score
1 sdf 1232,简单的增删改查
首先是增加
insert into score values('sdf',123)对于这种的必须要values种的字段和表中一一 对齐
insert into score (score,userName) values (345,'wer')这种方式就不用了
下来就说查询吧
select * from score查询表中所有的数据
得到结果
1 sdf 123
2 wer 345
select username from score查询表中所有的姓名
得到结果
sdf
wer
由于数据量太少我们再插入几条
insert into score select username,score from score
这句sql语句的意思是将score表中现有的数据继续插入到score表中（即两条变四条，四条变八条），好，我们现在score表中有1024条数据
select * from score where id=3查询id=3的所有数据
得到结果
3 sdf 123
select top 5 * from score 查询前五条数据
得到结果 大家看id就知道了
1 sdf 123
2 wer 345
3 sdf 123
4 wer 345
5 sdf 123
select * from score where id between 50 and 55查询id在50到55的所有数据(含50，55)
得到结果
50 wer 345
51 sdf 123
52 wer 345
53 sdf 123
54 wer 345
55 sdf 123
好，下面我们利用上面说的top写一个分页的语句，比如每页有5条，要第七页的数据
select top 5 * from score where id not in
(select top (5*6) id from score)
得到结果
31 sdf 123
32 wer 345
33 sdf 123
34 wer 345
35 sdf 123当然not in我是不建议大家用的，在后面的优化上面会说到
select * from score where username like '%s%'
查询username中含有s的所有数据，当然like我也是不建议大家用的，在后面的优化上面会说到
select * from score order by id desc按照id倒序查询所有
得到结果
1024 wer 345
1023 sdf 123
1022 wer 345
1021 sdf 123
1020 wer 345
1019 sdf 123
1018 wer 345
select username from score group by username 按照姓名分组查询
得到结果
sdf
wer
小泽
select score from score group by score having score>124 按照分数分组再找到大于124的
好，先来说一下修改
update score set username='小泽' where id=1
sqlserver常用函数
----统计函数----
avg--平均值
select avg(id) from score得到512
max--最大值
select max(id) from score得到1024
min--最小值
select min(id) from score得到1
sum--求和
select sum(id) from score得到524800
count--求数量
select count(score) from score得到1024
/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切
select sin(90) as '正弦',cos(90) as '余弦',tan(90) as '正切',cot(90) as '余切'
0.893996663600558 -0.44807361612917 -1.99520041220824 -0.501202783380153
/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2)
--返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)
--把弧度转换为角度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为
--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根
这些数学函数我就不一一赘述了，需要用的时候大家查帮助文档就可以了/***取近似值函数***/
这个还是比较重要的
ceiling()返回大于它的最小整数
select ceiling(13.4)返回14
floor()返回小于它的最大整数
select floor(-13.4)返回-14
round()四舍五入
abs()返回绝对值
select abs(-34.2)返回34.2
sign()测试正负号 正得1.0负得-1.0 0得0
select sign(-8)得-1
rand（）取0-1之间的随机小数
select rand()得0.795489779469641不定
pi()返回π
select pi()得3.14159265358979
----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
select ascii('asdf')得97即a的ascii为97
CHAR() --函数用于将ASCII 码转换为字符
select char(97)返回a
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
这两个就不多说了
STR() --函数把数值型数据转换为字符型数据
select str(123456)得到'123456'
LTRIM() --函数把字符串头部的空格去掉
select ltrim(' 123')得'123'
RTRIM() --函数把字符串尾部的空格去掉
同上
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
select left(' 123',2)得到空格和1
select substring(' 123',2,2)返回12 从第二位开始截取2个字符
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
select CHARINDEX(' 123',' 1234')表示第一个参数在第二个参数中出现的第一个位置
ptindex()可以包含通配符
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
--0 两个SOUNDEX 函数返回值的第一个字符不同
--1 两个SOUNDEX 函数返回值的第一个字符相同
--2 两个SOUNDEX 函数返回值的第一二个字符相同
--3 两个SOUNDEX 函数返回值的第一二三个字符相同
--4 两个SOUNDEX 函数返回值完全相同
QUOTENAME() --函数返回被特定字符括起来的字符串
/*select quotename('abc', '{') quotename('abc')
运行结果如下
----------------------------------{
{abc} [abc]*/
REPLICATE() --函数返回一个重复character_expression 指定次数的字符串
/*select replicate('abc', 3) replicate( 'abc', -2)
运行结果如下
----------- -----------
abcabcabc NULL*/
REVERSE() --函数将指定的字符串的字符排列顺序颠倒
REPLACE() --函数返回被替换了指定子串的字符串
/*select replace('abc123g', '123', 'def')
运行结果如下
----------- -----------
abcdefg*/
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串----数据类型转换函数----
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
select cast(100+99 as char) convert(varchar(12), getdate())
运行结果如下
------------------------------ ------------
199 Jan 15 2000
----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期
DATEDIFF(<datepart> ,<number> ,<date>)
--函数返回两个指定日期在datepart 方面的不同之处
DATENAME(<datepart> , <date>) --函数以字符串的形式返回日期的指定部分
DATEPART(<datepart> , <date>) --函数以整数值的形式返回日期的指定部分
GETDATE() --函数以DATETIME 的缺省格式返回系统当前的日期和时间
----系统函数----
APP_NAME() --函数返回当前执行的应用程序的名称
COALESCE() --函数返回众多表达式中第一个非NULL 表达式的值
COL_LENGTH(<'table_name'>, <'column_name'>) --函数返回表中指定字段的长度值
COL_NAME(<table_id>, <column_id>) --函数返回表中指定字段的名称即列名
DATALENGTH() --函数返回数据表达式的数据的实际长度
DB_ID(['database_name']) --函数返回数据库的编号
DB_NAME(database_id) --函数返回数据库的名称
HOST_ID() --函数返回服务器端计算机的名称
HOST_NAME() --函数返回服务器端计算机的名称
IDENTITY(<data_type>[, seed increment]) [AS column_name])
--IDENTITY() 函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中
/*select identity(int, 1, 1) as column_name
into newtable
from oldtable*/
ISDATE() --函数判断所给定的表达式是否为合理日期
ISNULL(<check_expression>, <replacement_value>) --函数将表达式中的NULL 值用指定值替换
ISNUMERIC() --函数判断所给定的表达式是否为合理的数值
NEWID() --函数返回一个UNIQUEIDENTIFIER 类型的数值
NULLIF(<expression1>, <expression2>)
--NULLIF 函数在expression1 与expression2 相等时返回NULL 值若不相等时则返回expression1 的值
下面我们来看一下索引
为了方便等会查看效率，我们重新建立两张表
create table class
(
id int identity(1,1) primary key not null,
className varchar(20) not null
)
go
create table student
(
id int identity(1,1) primary key not null,
studentName varchar(20) not null,
class_id int references class(id) not null
)
class表中的主键id为student表中的外键class_id
在class中插入三条数据
insert into class values('一班')
insert into class values('二班')
insert into class values('三班')
在student表中插入三条数据
insert into student values('刘德华',1)
insert into student values('张惠妹',2)
insert into student values('梁朝伟',3)
然后insert into student select studentName,class_id from student
将数据重复插入，现在student表中有1572864条数据
现在表中有主键索引，我们看一下使用主键查询所用的时间，使用下面的tsql语句
declare @startdate datetime
declare @enddate datetime
select @startdate=getdate()
--sql语句
select @enddate=getdate()
select datediff(ms,@startdate,@enddate) as '用时/毫秒' 可以测试出sql语句用时
本测试计算机为双核cpu，2g内存，运行qq1个，pplive一个，卡巴斯基杀软，360安全卫士一个
我们重新再studnet表中插入一条新的语句
insert into student values ('周杰伦',2)用时0秒
好，下面我们开始测试
select * from student where id=6使用主键索引的数据，用时0秒
select * from student where studentname='周杰伦'未使用索引的查询用时568毫秒
select * from student where studentname='周杰伦' and class_id=2未使用索引的复合查询用时263毫秒
下面我们查询多条数据
select * from student where studentname='刘德华'用时8420毫秒
select * from student where studentname='刘德华' and class_id=1用时8533毫秒
下面我们给studnetName创建一个简单索引
create index studnetN_index
on student(studentName)
下面我们继续测试
select * from student where studentname='周杰伦'用时0秒
select * from student where studentname='刘德华'用时6020毫秒
select * from student where studentname='刘德华' and class_id=1用时8250毫秒
创建组合索引
CREATE NONCLUSTERED INDEX [sdf] ON [dbo].[student]
(
[studentName] ASC,
[class_id] ASC
)
select * from student where studentname='刘德华' and class_id=1用时6223毫秒
insert into student values('wer',2)用时16毫秒
从以上我们可以看出使用适合的索引可以提高查询效率，但是增删改的效率却降低了，所以是否使用索引还是看项目的需求
解决方案 »
免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货