首页 > 代码库 > 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’,23,‘我的音乐我的世界’)

返回:A我的音乐我的世界EFG

 

 

 

 

 

 

常用的日期函数

getdate

取得当前的系统日期

Select getdate()

返回:今天的日期

dateadd

将指定的数值添加到指定的日期部分后的日期

Select adteaddmm,‘01-01-2009’)

返回:以当前的日期格式返回01-01-2009

datediff

两个日期之间的指定日期部分的间隔

Select datediffmm,‘01-01-2009’,‘05-05-2009’)       返回:4

datename

日期中指定日期部分的字符串形式

Select datenamedw,‘01-01-2000’)

返回:Saturday  星期六

datepart

日期中指定日期部分的整数形式

Select datepartday,‘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

返回从01之间的随机float

Select rand()  返回:0.465132132

abs

取数值表达式的绝对值

Select abs-43    返回:43

ceiling

向上取整,取大于或等于指定数值、表达式的最小整数

Select ceiling43.5 返回:44

floor

向下取整,取小于或等于指定表达式的最大整数

Select floor43.5  返回:43

power

取数值表达式的幂值

Select power52 返回:25

round

将数值表达式四舍五入为指定精度

Select round43.5431)返回:43.500

sign

对于正数返回+1,对于负数返回-1,对于0返回0

Select sign-43   返回:-1

sqrt

取浮点表达式的平方根

Select sqrt9  返回:3

 

常用的系统函数

convert

用来转变数据类型

Select convertvarchar5),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_name1

返回:从任意数据库中返回“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) )--自动标识列

数据类型 列的特征

数据类型 列的特征

……

)

--删除表

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指定唯一索引,可选。

CLUSTEREDNONCLUSTERED 指定是聚集索引还是非聚集索引,可选。

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常用操作