数据库经典语句(新)_数据库经典语句

数据库经典语句(新)由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“数据库经典语句”。

创建数据库

创建之前判断该数据库是否存在 if exists(select * from sysdatabases where name='databaseName')drop database databaseName go Create DATABASE database-name 删除数据库

drop database dbname 备份sql server

---创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:mql7backupMyNwind_1.dat'

---开始 备份

BACKUP DATABASE pubs TO testBack 创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：go

use 原数据库名

go

select * into 目的数据库名.dbo.目的表名 from 原表名(使用旧表创建新表)

B：create table tab_new as select col1,col2„ from tab_old definition only 创建序列

create sequence SIMON_SEQUENCE minvalue 1--最小值

maxvalue ***999999999999 最大值 start with 1 开始值

increment by 1 每次加几 cache 20;删除新表

drop table tabname 增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。添加主键

Alter table tabname add primary key(col)

说明：删除主键： Alter table tabname drop primary key(col)创建索引

create [unique] index idxname on tabname(col„.)

删除索引：drop index idxname on tabname

注：索引是不可更改的，想更改必须删除重新建。创建视图

create view viewname as select statement

删除视图：drop view viewname 几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2)values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’（所有包含‘value1’这个模式的字符串）---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count(*)as totalcount from table1

求和：select sum(field1)as sumvalue from table1

平均：select avg(field1)as avgvalue from table1

最大：select max(field1)as maxvalue from table1

最小：select min(field1)as minvalue from table1[separator] 几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。使用外连接

A、left outer join：

左外连接（左连接）：结果集既包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

编辑本段

判断对象是否存在判断数据库是否存在if exists(select * from sys.databases where name = '数据库名')

drop database [数据库名] 判断表是否存在if not exists(select * from sysobjects where [name] = '表名' and xtype='U')

begin

--这里创建表

end 判断存储过程是否存在if exists(select * from sysobjects where id = object_id(N'[存储过程名]')and OBJECTPROPERTY(id, N'IsProcedure')= 1)

drop procedure [存储过程名] 判断临时表是否存在if object_id('tempdb..#临时表名')is not null

drop table #临时表名 判断视图是否存在--SQL Server 2000

IF EXISTS(SELECT * FROM sysviews WHERE object_id = '[dbo].[视图名]'

--SQL Server 2005

IF EXISTS(SELECT * FROM sys.views WHERE object_id = '[dbo].[视图名]' 判断函数是否存在if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[函数名]')and xtype in(N'FN', N'IF', N'TF'))

drop function [dbo].[函数名] 获取用户创建的对象信息

SELECT [name],[id],crdate FROM sysobjects where xtype='U'

/*

xtype 的表示参数类型，通常包括如下这些 C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束（类型是 K）RF = 复制筛选存储过程 S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束（类型是 K）V = 视图 X = 扩展存储过程 */ 判断列是否存在if exists(select * from syscolumns where id=object_id('表名')and name='列名')

alter table 表名 drop column 列名 判断列是否自增列

if columnproperty(object_id('table'),'col','IsIdentity')=1

print '自增列'

else

print '不是自增列' SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名')AND is_identity=1 判断表中是否存在索引

if exists(select * from sysindexes where id=object_id('表名')and name='索引名')

print '存在'

else

print '不存在 查看数据库中对象

SELECT * FROM sys.sysobjects WHERE name='对象名' 编辑本段 提升

复制表

(只复制结构,源表名：a 新表名：b)(Acce可用)

法一：select * into b from a where 11

法二：select top 0 * into b from a 拷贝表

(拷贝数据,源表名：a 目标表名：b)(Acce可用)

insert into b(a, b, c)select d,e,f from b;跨数据库之间表的拷贝

(具体数据使用绝对路径)(Acce可用)

insert into b(a, b, c)select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in '“&Server.MapPath(”.“&”data.mdb“ &”' where..子查询

(表名1：a 表名2：b)

select a,b,c from a where a IN(select d from b 或者: select a,b,c from a where a IN(1,2,3)显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate)adddate from table where table.title=a.title)b 外连接查询

(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 在线视图查询

(表名1：a

select * from(Select a,b,c FROM a)T where t.a > 1;between的用法

between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2 in 的使用方法

select * from table1 where a [not] in(‘值1’,’值2’,’值4’,’值6’)

删除主表中已经在副表中没有的信息

两张关联表delete from table1 where not exists(select * from table2 where table1.field1=table2.field1 四表联查问题

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where.....日程安排提前五分钟提醒

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 一条sql 语句搞定数据库分页

select top 10 b.* from(select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc)a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 前10条记录

select top 10 * form table1 where 范围 选择排名

选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a)from tablename tb where tb.b=ta.b)派生结果表

包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA except(select a from tableB)except(select a from tableC)随机取出10条数据

select top 10 * from tablename order by newid()随机选择记录

select newid()删除重复记录

Delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)列出数据库里所有的表名

select name from sysobjects where type='U' 列出表里的所有的select name from syscolumns where id=object_id('TableName')列示排列

列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end)FROM tablename group by type

显示结果：

type vender pcs

电脑 A 1

电脑 A 1

光盘 B 2 光盘 A 2 手机 B 3 手机 C 3 初始化表table1

TRUNCATE TABLE table1 选择从10到15的记录

select top 5 * from(select top 15 * from table order by id asc)table_别名 order by id desc 数据类型转换

declare @numid int

declare @id varchar(50)

set @numid=2005

set @id=convert(varchar,@numid)

通过上述语句完成数据类型Int转换成varchar，其他转换类似，可参看convert函数

编辑本段 技巧

1=1，1=2的使用

在SQL语句组合时用的较多

“where 1=1” 是表示选择全部 “where 1=2”全部不选，如：

if @strWhere!=' begin

set @strSQL = 'select count(*)as Total from [' + @tblName + '] where ' + @strWhere

end

else

begin

set @strSQL = 'select count(*)as Total from [' + @tblName + ']'

end

我们可以直接写成set @strSQL = 'select count(*)as Total from [' + @tblName + '] where 1=1 and '+ @strWhere 收缩数据库

--重建索引

DBCC REINDEX DBCC INDEXDEFRAG--收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 压缩数据库

dbcc shrinkdatabase(dbname)转移数据库给新用户以已存在用户权限

exec sp_change_users_login 'update_one','newname','oldname' go 检查备份集

RESTORE VERIFYONLY from disk='E:dvbbs.bak' 修复数据库

Alter DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB('dvbbs',repair_allow_data_lo)WITH TABLOCK GO Alter DATABASE [dvbbs] SET MULTI_USER GO 日志清除

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,@MaxMinutes INT, @NewSize INT USE tablename--要操作的数据库名

Select @LogicalFileName = 'tablename_log',--日志文件名 @MaxMinutes = 10,--Limit on time allowed to wrap log.@NewSize = 1--你想设定的日志文件的大小(M)--Setup / initialize DECLARE @OriginalSize int Select @OriginalSize = size

FROM sysfiles

Where name = @LogicalFileName Select 'Original Size of ' + db_name()+ ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize)+ ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+ 'MB' FROM sysfiles Where name = @LogicalFileName Create TABLE DummyTrans(DummyColumn char(8000)not null)DECLARE @Counter INT, @StartTime DATETIME,@TruncLog VARCHAR(255)

Select @StartTime = GETDATE(),@TruncLog = 'BACKUP LOG ' + db_name()+ ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE(@LogicalFileName, @NewSize)

EXEC(@TruncLog)

--Wrap the log if neceary.WHILE @MaxMinutes > DATEDIFF(mi, @StartTime, GETDATE())--time has not expired

AND @OriginalSize =(Select size FROM sysfiles Where name = @LogicalFileName)

AND(@OriginalSize * 8 /1024)> @NewSize

BEGIN--Outer loop.Select @Counter = 0

WHILE((@Counter

BEGIN--update

Insert DummyTrans VALUES('Fill Log')

Delete DummyTrans

Select @Counter = @Counter + 1

END

EXEC(@TruncLog)END Select 'Final Size of ' + db_name()+ ' LOG is ' + CONVERT(VARCHAR(30),size)+ ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024))+ 'MB' FROM sysfiles Where name = @LogicalFileName Drop TABLE DummyTrans

SET NOCOUNT OFF 更改某个表

exec sp_changeobjectowner 'tablename','dbo' 存储更改全部表

Create PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128)AS DECLARE @Name as NVARCHAR(128)DECLARE @Owner as NVARCHAR(128)DECLARE @OwnerName as NVARCHAR(128)DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid)from sysobjects where user_name(uid)=@OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0)BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name)exec sp_changeobjectowner @OwnerName, @NewOwner end--select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner END

close curObject

deallocate curObject

GO SQL SERVER中直接循环写入数据

declare @i int

set @i=1

while @i

begin

insert into test(userid)values(@i)

set @i=@i+1 end

--语 句 功 能--数据操作

SELECT--从数据库表中检索数据行和列 INSERT--向数据库表添加新数据行 DELETE--从数据库表中删除数据行 UPDATE--更新数据库表中的数据--数据定义

CREATE TABLE--创建一个数据库表 DROP TABLE--从数据库中删除表 ALTER TABLE--修改数据库表结构 CREATE VIEW--创建一个视图 DROP VIEW--从数据库中删除视图 CREATE INDEX--为数据库表创建一个索引 DROP INDEX--从数据库中删除索引 CREATE PROCEDURE--创建一个存储过程 DROP PROCEDURE--从数据库中删除存储过程 CREATE TRIGGER--创建一个触发器 DROP TRIGGER--从数据库中删除触发器 CREATE SCHEMA--向数据库添加一个新模式 DROP SCHEMA--从数据库中删除一个模式 CREATE DOMAIN--创建一个数据值域 ALTER DOMAIN--改变域定义

DROP DOMAIN--从数据库中删除一个域--数据控制

GRANT--授予用户访问权限 DENY--拒绝用户访问 REVOKE--解除用户访问权限--事务控制

COMMIT--结束当前事务 ROLLBACK--中止当前事务

SET TRANSACTION--定义当前事务数据访问特征--程序化SQL DECLARE--为查询设定游标 EXPLAN--为查询描述数据访问计划 OPEN--检索查询结果打开一个游标 FETCH--检索一行查询结果 CLOSE--关闭游标

PREPARE--为动态执行准备SQL 语句 EXECUTE--动态地执行SQL 语句 DESCRIBE--描述准备好的查询---局部变量

declare @id char(10)--set @id = '10010001' select @id = '10010001'---全局变量---必须以开头--IF ELSE declare @x int @y int @z int select @x = 1 @y = 2 @z=3 if @x > @y print 'x > y'--打印字符串'x > y' else if @y > @z print 'y > z' else print 'z > y'--CASE use pangu update employee set e_wage = case when job_level = ’1’ then e_wage*1.08 when job_level = ’2’ then e_wage*1.07 when job_level = ’3’ then e_wage*1.06 else e_wage*1.05 end--WHILE CONTINUE BREAK declare @x int @y int @c int select @x = 1 @y=1 while @x

stockname like '[a-zA-Z]%'---------([]指定值的范围)stockname like '[^F-M]%'---------(^排除指定范围)---------只能在使用like关键字的where子句中使用通配符)or stockpath = 'stock_path' or stocknumber

stockname =(select stockname from stock_information where stockid = 4)---------子查询

---------除非能确保内层select只返回一个行的值，---------否则应在外层where子句中用一个in限定符

select distinct column_name form table_name---------distinct指定检索独有的列值，不重复

select stocknumber ,“stocknumber + 10” = stocknumber + 10 from table_name select stockname , “stocknumber” = count(*)from table_name group by stockname---------group by 将表按行分组,指定列中有相同的值 having count(*)= 2---------having选定指定的组 select *

from table1, table2

where table1.id *= table2.id--------左外部连接，table1中有的而table2中没有得以null表示

table1.id =* table2.id--------右外部连接

select stockname from table1 union [all]-----union合并查询结果集，all-保留重复行 select stockname from table2 ***insert*** insert into table_name(Stock_name,Stock_number)value(“xxx”,“xxxx”)value(select Stockname , Stocknumber from Stock_table2)---value为select语句

***update*** update table_name set Stockname = “xxx” [where Stockid = 3] Stockname = default Stockname = null Stocknumber = Stockname + 4 ***delete*** delete from table_name where Stockid = 3 truncate table_name-----------删除表中所有行，仍保持表的完整性 drop table table_name---------------完全删除表 ***alter table***---修改数据库表结构

alter table database.owner.table_name add column_name char(2)null.....sp_help table_name----显示表已有特征

create table table_name(name char(20), age smallint, lname varchar(30))insert into table_name select.........-----实现删除列的方法（创建新表）alter table table_name drop constraint Stockname_default----删除Stockname的default约束

***function(/*常用函数*/)***----统计函数----AVG--求平均值 COUNT--统计数目 MAX--求最大值 MIN--求最小值 SUM--求和--AVG use pangu select avg(e_wage)as dept_avgWage from employee group by dept_id--MAX--求工资最高的员工姓名 use pangu select e_name from employee where e_wage =(select max(e_wage)from employee)--STDEV()--STDEV()函数返回表达式中所有数据的标准差--STDEVP()--STDEVP()函数返回总体标准差--VAR()--VAR()函数返回表达式中所有值的统计变异数--VARP()--VARP()函数返回总体变异数----算术函数----/***三角函数***/ SIN(float_expreion)--返回以弧度表示的角的正弦 COS(float_expreion)--返回以弧度表示的角的余弦 TAN(float_expreion)--返回以弧度表示的角的正切 COT(float_expreion)--返回以弧度表示的角的余切 /***反三角函数***/ ASIN(float_expreion)--返回正弦是FLOAT 值的以弧度表示的角 ACOS(float_expreion)--返回余弦是FLOAT 值的以弧度表示的角 ATAN(float_expreion)--返回正切是FLOAT 值的以弧度表示的角 ATAN2(float_expreion1,float_expreion2)

--返回正切是float_expreion1 /float_expres-sion2的以弧度表示的角 DEGREES(numeric_expreion)--把弧度转换为角度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型

RADIANS(numeric_expreion)--把角度转换为弧度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型 EXP(float_expreion)--返回表达式的指数值 LOG(float_expreion)--返回表达式的自然对数值

LOG10(float_expreion)--返回表达式的以10 为底的对数值 SQRT(float_expreion)--返回表达式的平方根 /***取近似值函数***/ CEILING(numeric_expreion)--返回>=表达式的最小整数返回的数据类型与表达式相同可为

--INTEGER/MONEY/REAL/FLOAT 类型

FLOOR(numeric_expreion)--返回

--INTEGER/MONEY/REAL/FLOAT 类型

ROUND(numeric_expreion)--返回以integer_expreion 为精度的四舍五入值返回的数据

--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型

ABS(numeric_expreion)--返回表达式的绝对值返回的数据类型与表达式相同可为

--INTEGER/MONEY/REAL/FLOAT 类型

SIGN(numeric_expreion)--测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型

--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型 PI()--返回值为π 即3.14***936 RAND([integer_expreion])--用任选的[integer_expreion]做种子值得出0-1 间的随机浮点数

----字符串函数----ASCII()--函数返回字符表达式最左端字符的ASCII 码值 CHAR()--函数用于将ASCII 码转换为字符

--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值 LOWER()--函数把字符串全部转换为小写 UPPER()--函数把字符串全部转换为大写 STR()--函数把数值型数据转换为字符型数据 LTRIM()--函数把字符串头部的空格去掉 RTRIM()--函数把字符串尾部的空格去掉

LEFT(),RIGHT(),SUBSTRING()--函数返回部分字符串

CHARINDEX(),PATINDEX()--函数返回字符串中某个指定的子串出现的开始位置 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_expreion 指定次数的字符串 /*select replicate('abc', 3)replicate('abc',-2)运行结果如下

----------------------abcabcabc NULL*/ REVERSE()--函数将指定的字符串的字符排列顺序颠倒 REPLACE()--函数返回被替换了指定子串的字符串 /*select replace('abc123g', '123', 'def')运行结果如下

----------------------abcdefg*/ SPACE()--函数返回一个有指定长度的空白字符串 STUFF()--函数用另一子串替换字符串指定位置长度的子串----数据类型转换函数----CAST()函数语法如下

CAST()(AS [ length ])CONVERT()函数语法如下

CONVERT()([ length ], [, style])select cast(100+99 as char)convert(varchar(12), getdate())运行结果如下

------------------------------------------199 Jan 15 2000----日期函数----DAY()--函数返回date_expreion 中的日期值 MONTH()--函数返回date_expreion 中的月份值 YEAR()--函数返回date_expreion 中的年份值 DATEADD(, ,)

--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期 DATEDIFF(, ,)--函数返回两个指定日期在datepart 方面的不同之处

DATENAME(,)--函数以字符串的形式返回日期的指定部分 DATEPART(,)--函数以整数值的形式返回日期的指定部分 GETDATE()--函数以DATETIME 的缺省格式返回系统当前的日期和时间----系统函数----APP_NAME()--函数返回当前执行的应用程序的名称

COALESCE()--函数返回众多表达式中第一个非NULL 表达式的值

COL_LENGTH(,)--函数返回表中指定字段的长度值

COL_NAME(,)--函数返回表中指定字段的名称即列名 DATALENGTH()--函数返回数据表达式的数据的实际长度 DB_ID(['database_name'])--函数返回数据库的编号 DB_NAME(database_id)--函数返回数据库的名称 HOST_ID()--函数返回服务器端计算机的名称 HOST_NAME()--函数返回服务器端计算机的名称

IDENTITY([, seed increment])[AS column_name])--IDENTITY()函数只在SELECT INTO 语句中使用用于插入一个identity column列到新表中

/*select identity(int, 1, 1)as column_name into newtable from oldtable*/ ISDATE()--函数判断所给定的表达式是否为合理日期

ISNULL(,)--函数将表达式中的NULL 值用指定值替换

ISNUMERIC()--函数判断所给定的表达式是否为合理的数值 NEWID()--函数返回一个UNIQUEIDENTIFIER 类型的数值 NULLIF(,)--NULLIF 函数在expreion1 与expreion2 相等时返回NULL 值若不相等时则返回expreion1 的值

以上是本人收藏的，作者我也不记得了。