首页 > 代码库 > SQL常用操作
SQL常用操作
——基本插入.................................................................................................. 2
——先创建一张新表,把原表的数据复制到新表................................... 2
——直接把原表复制到新表(新表自动生成)....................................... 2
——一次性插入多行数据............................................................................. 2
——删除(标识列不变)............................................................................. 3
——删除(标识列清除)............................................................................. 3
——修改........................................................................................................... 3
常用的字符串函数.......................................................................................... 3
常用的日期函数.............................................................................................. 4
常用的日期参数及缩写................................................................................. 5
常用的数学函数.............................................................................................. 5
常用的系统函数.............................................................................................. 5
--创建数据库.................................................................................................... 6
--删除数据库.................................................................................................... 7
--创建文件夹.................................................................................................... 7
--创建表............................................................................................................ 7
--删除表............................................................................................................ 7
--检查该数据库是否存在............................................................................... 7
--检查该表是否存在....................................................................................... 7
--增加一列........................................................................................................ 7
--删除一列........................................................................................................ 7
--修改一列........................................................................................................ 8
--修改一列的类型........................................................................................... 8
--添加主键约束............................................................................................... 8
--添加唯一约束............................................................................................... 8
--添加默认约束............................................................................................... 8
--添加check约束............................................................................................ 8
--添加外键约束............................................................................................... 8
--删除约束........................................................................................................ 9
--纵向连接两个表........................................................................................... 9
--重命名,表名............................................................................................... 9
--重命名,列名............................................................................................... 9
--使用alter table 语句可以创建以下约束................................................... 9
--全局变量........................................................................................................ 9
--Cast()与convert()函数.................................................................... 10
--截取小数点后的长度................................................................................. 10
--Case多分支语句........................................................................................ 10
--执行事务的语法......................................................................................... 10
--视图的创建与删除..................................................................................... 11
--索引.............................................................................................................. 11
--常用的系统存储过程................................................................................. 11
--扩展存储过程语法..................................................................................... 12
--自定义创建存储过程................................................................................. 12
--RALSERROR.............................................................................................. 13
——基本插入
insert into 表名(列名) values(值)
——先创建一张新表,把原表的数据复制到新表
Insert into 新表(列名)select 列1,列2......from原表
——直接把原表复制到新表(新表自动生成)
Select 列1,列2......into 新表 from 原表
——一次性插入多行数据
Insert into 表名(列名)
Select 值1,值2....union
Select 值1,值2.....union
Select 值1,值2.....
——删除(标识列不变)
Delete from 表名 where 条件
——删除(标识列清除)
Truncate table 表名
——修改
Update 表名 set 列1=值1,列2=值2.....where 条件
常用的字符串函数
charindex | 用来寻找一个指定的字符串在另一个字符串中的起始位置 | Select charindex(‘name’,‘My name is Tom’,1) 返回:4 |
len | 返回传递给它的字符串长度 | Select len(‘SQL Server 课程’) 返回:12 |
upper | 把传递给它的字符串转换为大写 | Select upper(‘sql server 课程’) 返回:SQL SERVER 课程 |
ltrim | 清除字符左边的空格 | Select ltrim(‘ 周志宇 ’) 返回:周志宇 (后面的空格保留) |
ririm | 清除字符右边的空格 | Select ririm(‘ 周志宇 ’) 返回: 周志宇(前面的空格保留) |
right | 从字符串右边返回指定数目的字符 | Select right(‘买卖提.吐尔松’,3) 返回:吐尔松 |
replace | 替换一个字符串中的字符 | Select replace(‘莫乐可切.杨可’,‘可’,‘兰’) 返回:莫乐兰切.杨兰 |
stuff | 在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串 | Select stuff(‘ABCDEFG’,2,3,‘我的音乐我的世界’) 返回:A我的音乐我的世界EFG |
常用的日期函数
getdate | 取得当前的系统日期 | Select getdate() 返回:今天的日期 |
dateadd | 将指定的数值添加到指定的日期部分后的日期 | Select adteadd(mm,‘01-01-2009’) 返回:以当前的日期格式返回01-01-2009 |
datediff | 两个日期之间的指定日期部分的间隔 | Select datediff(mm,‘01-01-2009’,‘05-05-2009’) 返回:4 |
datename | 日期中指定日期部分的字符串形式 | Select datename(dw,‘01-01-2000’) 返回:Saturday 或 星期六 |
datepart | 日期中指定日期部分的整数形式 | Select datepart(day,‘01-15-2000’) 返回:15 |
常用的日期参数及缩写
日期部分参数 | 缩写 | 日期部分参数 | 缩写 |
year | yy,yyyy | weekday | dw,w |
quarter | qq,q | hour | hh |
month | mm,m | minute | mi,n |
dayofyear | dy,y | second | ss,s |
day | dd,d | millisecond | ms |
week | wk,ww |
常用的数学函数
rand | 返回从0到1之间的随机float值 | Select rand() 返回:0.465132132 |
abs | 取数值表达式的绝对值 | Select abs(-43) 返回:43 |
ceiling | 向上取整,取大于或等于指定数值、表达式的最小整数 | Select ceiling(43.5) 返回:44 |
floor | 向下取整,取小于或等于指定表达式的最大整数 | Select floor(43.5) 返回:43 |
power | 取数值表达式的幂值 | Select power(5,2) 返回:25 |
round | 将数值表达式四舍五入为指定精度 | Select round(43.543,1)返回:43.500 |
sign | 对于正数返回+1,对于负数返回-1,对于0返回0 | Select sign(-43) 返回:-1 |
sqrt | 取浮点表达式的平方根 | Select sqrt(9) 返回:3 |
常用的系统函数
convert | 用来转变数据类型 | Select convert(varchar(5),12345) 返回:字符串12345 |
Current_user | 返回当前用户的名字 | Select current_user 返回:你登录的用户名 |
datalength | 返回用于指定表达式的字节数 | Select datalength(‘中国A联盟’) 返回:9 |
Host_name | 返回当前用户所登录的计算机名字 | Select host_name() 返回:你所登录的计算机的名字 |
System_user | 返回当前所登录的用户名称 | Select system_user 返回:你当前所登录的用户名 |
User_name | 从给定的用户ID返回用户名 | Select user_name(1) 返回:从任意数据库中返回“abo” |
--创建数据库
Create database 数据库名
On
(
name=逻辑文件名
filename=物理文件名
size=大小
maxsize=最大容量
filegrowth=增长量)
Log on
(
name=逻辑文件名
filename=物理文件名
size=大小
maxsize=最大容量
filegrowth=增长量
)
--删除数据库
Drop database 数据库名
--创建文件夹
Exec xp-cmdshell ‘mkdir D:\project’
--创建表
Create table 表名
(
( 列名 数据类型 primary key identity(1,1) )--自动标识列
列1 数据类型 列的特征
列2 数据类型 列的特征
……
)
--删除表
Drop table 表名
--检查该数据库是否存在
If exists (select * from sysdatabases where name=’数据库名’)
--检查该表是否存在
If exists (select * from sysobjects where name=’表名’)
--增加一列
alter table 表名 add 列名 varchar(20) null
--删除一列
alter table 表名 drop column 列名
--修改一列
alter table 表名 alter column 列名 varchar(40) null
--修改一列的类型
alter table 表名 alter column 列名 varchar(40)
--添加主键约束
alter table 表名
add constraint 约束名 primary key (列名)
--添加唯一约束
alter table 表名
add constraint 约束名 unique (列名)
--添加默认约束
alter table 表名
add constraint 约束名 default(内容) for 列名
--添加check约束
alter table 表名
add constraint 约束名 check(内容)
--添加外键约束
alter table 表名
add constraint 约束名 foreign key(列名) references 另一表名(列
名)
--删除约束
alter table 表名
drop constraint 约束名
--纵向连接两个表
select *from stuInfo union
select *from stuMarks
--重命名,表名
exec sp_rename ‘原表名‘,‘改后表名‘
--重命名,列名
exec sp_rename ‘表名.原列名‘,‘改后列名‘,‘column‘
--使用alter table 语句可以创建以下约束
主键约束 Primary Key Constraint
唯一约束 Unique Constraint
检查约束 Check Constraint
默认约束 Default Constraint
外键约束 Foreign Key Constraint
--全局变量
变量 | 含义 |
@@ERROR | 最后一个T_SQL错误的错误号 |
@@IDENTITY | 最后一次插入的标识列 |
@@MAX_CONNECTIONS | 可以创建的丶同时连接的最大数目 |
@@ROWCOUNT | 受上一个SQL语句影响的行数 |
@@SERVICENAME | 该计算机上的SQL服务的名称 |
@@SERVERNAME | 本地服务器的名称 |
@@TIMETICKS | 当前计算机上每刻度的微秒数 |
@@TRANSCOUNT | 当前连接打开的事务数 |
@@VERSION | SQL Server的版本信息 |
@@LANGUAGE | 当前使用的语言的名称 |
--Cast()与convert()函数
Cast(表达式 as 数据类型)
Convert(数据类型[长度],表达式[,样式])
--截取小数点后的长度
select left(cast(1.7455 as varchar(50)) , charindex(‘.‘,cast(1.7455 as varchar(50))) + 2)
select left(1.7455 , charindex(‘.‘,1.7455) + 2)
--Case多分支语句
Case
When 条件 1 then 结果1
When 条件 2 then 结果2
[else 其他结果]
End
--执行事务的语法
事务是座位单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性,即原子性(atomicity)、一致性(consistency)、隔离性(isolation)及持久性(durability),这些特性通常简称为ACID
Begin transaction 开始事务
Commit transaction 提交事务
Rollback transaction 回滚(撤销)事务
--视图的创建与删除
CREATE VIEW view_name AS <select 语句> --创建
DROP VIEW view_name --删除
--索引
分类:唯一索引、主键索引、聚集索引、非聚集索引、复合索引、全文索引
索引的创建与删除:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table_name (column_name [,column_name]…)
[WITH FILLFACTOR=x] --创建
注:UNIQUE指定唯一索引,可选。
CLUSTERED、NONCLUSTERED 指定是聚集索引还是非聚集索引,可选。
FILLFACTOR 表示填充因子,指定一个0~100的值,该值指示索引页填满的空间所占的百分比。
指定索引查询_例:
( Select * from Student with(index=索引名) where name=’’ )
DROP INDEX table_name.index_name --删除
(索引存放在系统表sysindexes中)
--常用的系统存储过程
系统存储过程 | 说 明 |
Sp_databases | 列出服务器上的所有数据库信息,包括数据库名称和数据库大小 |
Sp_helpdb | 报告有关指定数据库或所有数据库的信息 |
Sp_renamedb | 更改数据库的名称 |
Sp_tables | 返回当前环境下可查询的表或视图的信息 |
Sp_columns | 返回某个表或视图的列信息,包括列的数据类型和长度等 |
Sp_help | 查看某个数据库对象的信息,如列名、主键、约束、外键、索引等 |
Sp_helpconstraint | 查看某个表的约束 |
Sp_helpindex | 查看某个表的索引 |
Sp_stored_procedures | 显示存储过程的列表 |
Sp_password | 添加或修改登录账户的密码 |
Sp_helptext | 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本 |
执行语法:EXEC [UTE] 存储过程名 [参数值] |
--扩展存储过程语法
EXEC xp_cmdshell DOB命令 [NO_OUTPUT]
(查看创建文件_例:exec xp_cmdshell ‘dir \bank\’)
--自定义创建存储过程
Create proc [edure] 存储过程名
[ {@参数1 数据类型} [=默认值] [output] ,
……,
{@参数1 数据类型} [=默认值] [output]
] AS SQL语句
执行带参数的存储过程:
EXEC[UTE] [返回变量=] 存储过程名 [@参数1= ] 参数值1 [ OUTPUT ] | [DEFAULT],
……,
[@参数1= ] 参数值N [ OUTPUT ] | [DEFAULT]
注:output 表明参数是输出参数,default表明参数的默认值。
删除存储过程:
[ if exists (select * from susobjects where name = 存储过程名)]
DROP PROCEDURE 存储过程名
--RALSERROR
Roiserror 返回用户定义的错误信息时,可指定严重级别,设置系统变量记录所发生的错误,语法为:
RELSERROR ( {msg_id | msg_str} { , severity ,state } [WITH option [ ,…… n ] ] )
本文出自 “张志鹏” 博客,请务必保留此出处http://zhangzhipeng.blog.51cto.com/9115459/1571407
SQL常用操作