SQL常用操作

Rainie999

　　基本插入
　　先创建一张新表，把原表的数据复制到新表
　　直接把原表复制到新表（新表自动生成）
　　一次性插入多行数据
　　删除（标识列不变）
　　删除（标识列清除）
　　修改
　　常用的字符串函数
　　常用的日期函数
　　常用的日期参数及缩写
　　常用的数学函数
　　常用的系统函数
　　--创建数据库
　　--删除数据库
　　--创建文件夹
　　--创建表
　　--删除表
　　--检查该数据库是否存在
　　--检查该表是否存在
　　--增加一列
　　--删除一列
　　--修改一列
　　--修改一列的类型
　　--添加主键约束
　　--添加唯一约束
　　--添加默认约束
　　--添加check约束
　　--添加外键约束
　　--删除约束
　　--纵向连接两个表
　　--重命名，表名
　　--重命名，列名
　　--使用alter table 语句可以创建以下约束
　　--全局变量
　　--Cast（）与convert（）函数
　　--截取小数点后的长度
　　--Case多分支语句
　　--执行事务的语法
　　--视图的创建与删除
　　--索引
　　--常用的系统存储过程
　　--扩展存储过程语法
　　--自定义创建存储过程
　　--RALSERROR

——基本插入
　　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=物理文件名

　　>　　maxsize=最大容量
　　filegrowth=增长量)
　　Log on
　　(
　　name=逻辑文件名
　　filename=物理文件名

　　>　　maxsize=最大容量
　　filegrowth=增长量
　　)
--删除数据库
　　Drop database 数据库名
--创建文件夹
　　Exec xp-cmdshell ‘mkdir D:\project’
--创建表
　　Create table 表名
　　(

　　( 列名 数据类型 primary key>　　列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     --创建
　　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 ] ] )