Sql server 2008 的基础知识总结

Finished by新浪微博

1、SQL语句增加列、修改列类型、修改列、删除列

数据库SQL语言的修改语句，可以用来修改基本表，其一般表示格式为： ALTER TABLE<表名>[改变方式]

改变方式：

· 加一个栏位: ADD "栏位 1" "栏位 1 资料种类"

· 删去一个栏位: DROP "栏位 1"

· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类" · 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"

由上可以看出，修改基本表提供如下四种修改方式：

(1)ADD方式：

用于增加新列和完整性约束，列的定义方式同CREARE TABLE语句中的列定义方式相同，其语法格式：

ALTER TABLE <表名> ADD <列定义>|<完整性约束>。由于使用此方式中增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

(2)DROP方式：

用于删除指定的完整性约束条件，或删指定的列，其语法格式为：

ALTER TABLE<表名> DROP [<完整性约束名>]

ALTER TABLE<表名> DROP COLUMN <列名>

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN <列名>)。

(3)CHANGE方式

用于修改某些列，其语法格式：

ALTER TABLE [表名] CHANGE <原列名> TO <新列名><新列的数据类型>--有错的 EXECUTE sp_rename N'dbo.学生表.姓名', N'姓名', 'COLUMN' -—正确的

(4)MODIFY方式

用于修改某些列的数据类型，其语法格式：

ALTER TABLE [表名] MODIFY [列名] [数据类型]

2、备注：所有mssql中系统自带的函数

(1)日期和时间类型

---datatime 精确度 三百分之一秒

alter table 学生表

add 创建时间 datetime default getdate()

insert into 学生表(学生班级,学生姓名,学生年龄,教师编号) values('一班','田七','22','2')

select * from 教师表

select * from 学生表

--1-- getdate( )函数 ： 返回当前系统日期和时间

select getdate() as 当前时间

--2-- dateadd(datepart,number, date)函数 ： 将指定的数值添加到指定的日期部分后的日期。//date需要用单引号标记

select dateadd(year,10,(select 创建时间 from 学生表 where 学生姓名='田七'))//获取时间，第三个参数减去中间的参数，以第一个参数为单位。

--3-- datediff(datepart,startdate,enddate) 函数 ： 两个日期之间的指定日期部分的区别。

select datediff(month,getdate(),'2009-10-1')//后的的时间减去中间的时间以第一个参数为单位。

--4-- datename(datepart,date) 函数 ： 日期中指定日期部分的字符串形式。 select datename(month,'2008-1-1')//以第一个参数的为单位获取该单位的数值 select datename(year,(select 创建时间 from 学生表 where 学生姓名='田七')) --5-- datepart(datepart,date )函数 ： 日期中指定日期部分的整数形式。 select datepart(day,getdate())//以第一个参数的为单位获取该单位的数值 select datepart(day,'2008-1-1')

--6-- year(date)函数 ： 返回表示指定日期中的年份的整数

select year(getdate())

--7-- month(date)函数 ：返回代表指定日期月份的整数

select month(getdate())

--8-- day(date)函数 ： 返回代表指定日期的天的整数。

select day(getdate())

(2)数值函数

--1-- abs(num_expr)函数 : 数值表达式的绝对值

select abs(-1)

--2-- ceiling(num_expr)函数 ： 大于或等于指定数值表达式的最小整数

select ceiling(32.2)

--3-- floor(num_expr)函数 ： 小于或等于指定表达式的最大整数

select floor(32.9)

--4-- power(num_expr,num_expr)函数 ： 数值表达式的y次幂的值

select power(5,3)

select power((select 教师编号 from 学生表 where 学生编号='4'),(select 教师编号 from 学生表 where 学生编号='5'))

--5-- rand( )函数 ： 大于或等于0,小于1之间的随机数

select rand()

select floor(RAND()*100)//获取100以内的一个随机数

--6-- round(num_expr)函数 ： 将数值表达式四舍五入为指定精度

select round(123.456,2)//还会保留0，其结果为123.450

(3)字符串函数

--1-- ASCII( )函数 : 返回字符表达式最左端字符的ASCII值

select ascii('abc')

--2-- char( )函数 : 将0-255之间的整数转化为字符，超出范围返回NULL

select char(97)

--3-- lower( )函数 : 把字符串全部转化为小写

select lower('abcDEF')

--4-- upper( )函数 : 把字符串全部转化成大写

select upper('sdasdDD')

--5-- ltrim( )函数 : 将字符串头部的空格去掉

select ltrim(' abc')

select ltrim((select 学生姓名 from 学生表 where 学生编号='6'))//错误的

insert into 学生表(学生班级,学生姓名,学生年龄,教师编号) values('一班',' aaa ',22,2)

insert into 学生表(学生班级,学生姓名,学生年龄,教师编号) values('一班',' bbb ',24,1)

select * from 学生表

--6-- rtrim( )函数 : 将字符串尾部的空格去掉

select rtrim('abc ')

select rtrim((select 学生姓名 from 学生表 where 学生编号='6'))

---- 注：在许多情况下需要得到头尾都没有空格的字符串，则将上两个函数嵌套使用 select rtrim(ltrim(' 前后无空格 '))

--7-- left( )函数 : 返回部分字符串，子串是从字符串最左边起到第n个字符的部分 select left('abcdef',3)

--8-- right( )函数 : 返回部分字符串，子串是从字符串右边第n个字符到最后一个字符的部分

select right('abcdef',3)

--9-- substring( )函数 : 返回部分字符串，子串是从字符串左边第n个字符起m个字符长度的部分

select substring('abcdefgh',3,4)

--10-- replace( )函数 : 用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。

select replace('acbcabc','ab','13')

--11-- stuff( )函数 : 删除指定长度的字符并在指定的起始点插入另一组字符。

select stuff('abcdef',3,2,'12345')

--12-- len( )函数 : 返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格//开通和中间的空格会计算

select len('abcdef')

select len('东方不败')

--13-- charindex( )函数 : 返回在字符串2中字符串1的起始位置，n为查找的起始位置,没找到返回0

select charindex('cd','abcdef',2)

--14-- str(float,length,decimal)函数 : 由数字数据转换来的字符数据

-- float 是带小数点的表达式 //decimal十进制

-- length 指定长度，包括小数点、符号、数字或空格,默认值为 10

-- decimal 是小数点右边的位数,默认值为 0

--1-- 将包含五个数字和一个小数点的表达式转换为有六个位置的字符串。数字的小数部分四舍五入为一个小数位//length包括小数点

select str(123.47,6,1)

--2-- 指定长度应该大于或等于小数点前面的数字的长度，当表达式超出指定长度时，字符串为指定长度并返回 *** 。

select str(123.45,2,3)

--3-- 小数部分位数不足，自动补0

select str(123.45,8,5)

--15—sum函数

Select sum(字段名) from table名

select * from t_TydBill where FDate between '2012-08-01' and '2012-10-19' order by FDate—-sql2008中between and包含第一个数不包含位数 例子中包含2012-08-01不包含2012-10-19

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

3、索引

--创建索引

create index index_name on Table_1 (ID)

--删除索引

drop index index_name on Table_1

4、自定义函数

--自定义函数

use XJ

go

create function MoneyBack(@hireDate datetime,

as

begin

return (YEAR(@today)-YEAR(@hireDate))*@per_wage @today datetime, @per_wage money) returns money

end--结束函数定义

--创建函数

go

--调用函数

select XJ.dbo.MoneyBack('1991-7-1',GETDATE(),15) as 工龄工资

 

第二篇：sql-excel互导总结(成功)

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

EXEC

注意：

?

?

excel-->sql

必须在C盘上加个文件夹aa； 该服务器的名字； master..xp_cmdshell 'bcp BYFZ_pingjiao.dbo.users out C:\aa\users.xls -c -q -S"wyt1" -U"sa" -P"wyt101"'

直接用刚从数据库转化出的excel表导入sql数据库，出现以下错误：

消息7399，级别16，状态1，第3 行

链接服务器"(null)" 的OLE DB 访问接口"Microsoft.Jet.OLEDB.4.0" 报错。提供程序未给出有关错误的任何信息。

消息7303，级别16，状态1，第3 行

无法初始化链接服务器"(null)" 的OLE DB 访问接口"Microsoft.Jet.OLEDB.4.0" 的数据源对象。（文件名字错，excel文件正打开，也会出此错）

此时要把刚才从数据库导出的excel表重新：文件—》保存—》选择否—-保存为一个excel工作薄表。或另存为：选择excel工作表形式。

这时，还是不能把数据插入数据库：

第一行要写上字段名。

先选好数据库，输入以下代码：

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

insert into users(user_id,user_password,user_level)

SELECT user_id,user_password,user_level

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data

出现错误： Source="C:\aa\users1.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[users$]

消息515，级别16，状态2，第2 行

不能将值NULL 插入列'user_id'，表'byfz_vote.dbo.users'；列不允许有Null 值。INSERT 失败。

Excel数据导入Sql Server出现Null。 在Excel中，我们时常会碰到这样的字段(最常见的就是电话号码)，即有纯数字的(如没有带区号的电话号码)，又有数字和其它字符混合 (如“区号-电话号码”)的数据，在导入SQLServer过程中，会发现要么纯数字的数据导过去之后变成了NULL，要么就是数字和其它字符混合的数据导过去之后变成了NULL。

为什么有些是纯数字的数据导过去之后变成了NULL，有些却是数字和其它字符混合的数据导过去之后变成了NULL，原来是在将Excel数据导入SQLServer过程中，SQLServer会做出判断，是采用float型还是nvarchar型来接受数据，测试发现(没有科学依据)，SQLServer采用哪一型取决于将要导入的数据中本身具有哪一型的记录数比例多，如10笔数据，有4笔没有带区号的电话号码，6笔是带区号的电话号码，那么转到SQLServer就会选择nvarchar型，结果就是4笔没有带区号的电话号码导过去之后全成了NULL，反之亦然。不管怎么样，我们最终都希望SQLServer是采用nvarchar来接受数据，毕意我们要导入的数据中有数字和其它字符混合的数据，用float型来接受是不可能的，这样只要我们解决了将纯数字的数据转换成字符型并让SQLServer接受就可以了。

我首先想到的就是将这个字段的所有数据在Excel中设置为文本格式，刚才说了本来就是希望导入SQLServer时成为字符型，但结果令人失望，不起作用。

最终网上搜索到了答案：混合数据类型列的强制解析——IMEX=1，使用 IMEX=1 选参之后，只要取样数据里是混合数据类型的列，一律强制解析为 nvarchar/ntext 文本。当然，IMEX=1 对单一数据类型列的解析是不影响的。

SELECT * INTO Table08

FROM OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data

5.0;HDR=Yes;IMEX=1;"')...[Sheet1$]

注：

1.这条语句是在SQLServer查询分析器中执行，并且要选择好数据库，否则会把要导入的数据往别的数据库中导了。

2.Table08是数据导入后在SQLServer中的表名，属于新建，所以请确认在导入数据前数据库中没有该表名，否则会提示已存在同一表名。 Source="E:\1.xls";Extended properties="Excel

3.Data Source，不要连在一起写，中间有一空格。

4.E:\1.xls，为Excel所在的绝对路径和数据库名。

5.Excel 5.0，根据不同的Excel版本写5.0或8.0或其它。

6.IMEX=1，是转换成文本输入的意思，非常重要，如果没有，就跟你直接导入效果一样。

7.Sheet1是表名，千万别看到语句中有$就在表名后加上$，因为$是语句要加的，别画蛇添足。

根据上面的文章，代码修改如下：

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

insert into users(user_id,user_password,user_level)

SELECT user_id,user_password,user_level

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\aa\users1.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1;"')...[users$]

还是出现错误：不能将值NULL 插入列'user_id'，表'byfz_vote.dbo.users'；列不允许有Null 值。INSERT 失败。

==》查阅 http://support.microsoft.com/kb/194124

修改注册表 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

修改注册表设置 TypeGuessRows = 8—》1000；

运行成功，但单元格显示3.72111e+009？？？？？？？

——用sql处理Excel文件导入到数据库出现科学计数法问题：

解决办法：cast(cast([就诊卡号] as decimal(38,0)) as varchar(255))

最后成功：

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

insert into users(user_id,user_password,user_level)

SELECT user_id,cast(cast (user_password as decimal(18,0)) as

varchar(50)),user_level

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\aa\users1.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1;"')...[users$]

另一个表也成功了，这次Excel表没加第一行段名，把HDR=Yes改成HDR=No

insert into stu(sid,sname,ssex,sgrade,sclass)

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\aa\stu1.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=No;IMEX=1;"')...[stu$]