常用SQL数据库知识点

常用SQL数据库知识点

一、语句：

1、说明：创建数据库

CREATE DATABASE database-name 
2、说明：删除数据库

drop database dbname
3、说明：备份sql server

--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack 
4、说明：创建新表

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

5、说明：删除新表

drop table tabname 
6、说明：增加一个列

Alter table tabname add column col type
7、说明：创建索引：

create [unique] index idxname on tabname(col….) 
       删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
8、说明：创建视图：

create view viewname as select statement 
         删除视图：drop view viewname

看视图的源码：sp_helptext viewname
10、说明：几个简单的基本的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%’

排序：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
11、说明：几个高级查询运算词
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)，不消除重复行。 
注：使用运算词的几个查询结果行必须是一致的。 
12、说明：使用外连接 

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/cross （outer） join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
12、说明：分组:Group by:一张表，一旦分组 完成后，查询后只能得到组相关的信息。
       组相关的信息：（统计信息） count,sum,max,min,avg  

13、说明：复制表(只复制结构,源表名：a 新表名：b)
法一：select * into b from a where 1<>1

法二：select top 0 * into b from a
14、说明：拷贝表(拷贝数据,源表名：a 目标表名：b)

 insert into b(a, b, c) select d,e,f from a;

15、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)

插入不存在的表: select * into anywell2013.dbo.b from anywell.dbo.a;

插入已存在的表：insert into anywell2013.dbo.b(a, b, c) select d,e,f from anywell.dbo.a;

16、说明：子查询(表名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)

17、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

18、说明：外连接查询(表名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

19、说明：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

20、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

21、说明：两张关联表，删除主表中已经在副表中没有的信息 
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

22、说明：四表联查问题：
select * from a inner join b on a.a=b.b inner join c on a.a=c.c inner join d on a.a=d.d where .....

23、说明：日程安排提前五分钟提醒 
SQL: select * from rcap where datediff('minute',kssj,getdate())>5

24、说明：前10条记录
select top 10 * form table1 where 条件范围

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

26、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)

27、说明：随机取出10条数据
select top 10 * from tablename order by newid()

28、说明：随机选择记录

select newid()

29、说明：删除重复记录
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
2),select distinct * into temp from tablename
  delete from tablename
  insert into tablename select * from temp
评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作
3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段

alter table tablename
--添加一个自增列
add  column_b int identity(1,1)
 delete from tablename where column_b not in(
select max(column_b)  from tablename group by column1,column2,...)
alter table tablename drop column column_b

30、说明：列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户

31、说明：列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')

32、说明：列示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

33、说明：初始化表a  （若有自增量ID，初始化后则会从1开始）

truncate table a  （不能加WHERE 条件范围）

34、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

35、1=1，1=2的使用，在SQL语句组合时用的较多

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

36、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE

37、创建触发器

CREATE TRIGGER [dbo].[atri]

 ON  [dbo].[a]

 FOR UPDATE,INSERT,DELETE AS

......

GO

更新触发器：ALTER TRIGGER [dbo].[atri]

 ON  [dbo].[a]

 FOR UPDATE,INSERT,DELETE AS

......

GO

删除触发器：DROP TRIGGER [dbo].[atri]

38、创建存储过程

CREATE PROCEDURE [dbo].[P_XSBB](@USERID AS VARCHAR(80),@RQC AS DATETIME,@RQD AS DATETIME) 

 AS 

...... 

GO

更新存储过程：ALTER PROCEDURE [dbo].[P_XSBB](@USERID AS VARCHAR(80),@RQC AS DATETIME,@RQD AS DATETIME) 

 AS 

...... 

GO

删除存储过程：DROP PROCEDURE [dbo].[P_XSBB]

执行存储过程：EXEC P_XSBB

 

二、函数：

1、聚合函数

报表的典型用途是从全部数据中提取出代表一种趋势的值或者汇总值，这就是聚合的意义。

聚合函数应用特定的聚合操作并返回一个标量值(单一值)。返回的数据类型对应于该列或者传递到函数中的值。聚合经常和分组、累积以及透视等表运算一起使用，生成数据分析结果。

AVG()函数

AVG()函数用于返回一组数值中所有非空数值的平均值。

SELECT AVG(Score)

COUNT()函数

COUNT()函数用于返回一个列内所有非空值的个数，这是一个整型值。

 SELECT COUNT(rs)

MIN()与MAX()函数

MIN()函数用于返回一个列范围内的最小非空值；MAX()函数用于返回最大值。这两个函数可以用于大多数的数据类型，返回的值根据对不同数据类型的排序规则而定。

SELECT MIN(RQ)

SELECT MAX(RQ)

SUM()函数

SUM()函数是最常用的聚合函数之一，它的功能很容易理解：和AVG()函数一样，它用于数值数据类型，返回一个列范围内所有非空值的总和。

SELECT SUM(JE)

2、转换函数

数据类型转换可以通过CAST()和CONVERT()函数来实现。大多数情况下，这两个函数是重叠的，它们反映了SQL语言的演化历史。这两个函数的功能相似，不过它们的语法不同。虽然并非所有类型的值都能转变为其他数据类型，但总的来说，任何可以转换的值都可以用简单的函数实现转换。

CAST()函数

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

SELECT CAST('123' AS int)

CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作。

SELECT CAST('123.4' AS decimal(9,2)) 

CONVERT()函数

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。

SELECT 'Default Date:' + CONVERT(Varchar(50), GETDATE(), 100) 

STR()函数

这是一个将数字转换为字符串的快捷函数。这个函数有3个参数：数值、总长度和小数位数。如果数字的整数位数和小数位数(要加上小数点占用的一个字符)的总和小于总长度，对结果中左边的字符将用空格填充。在下面第1个例子中，包括小数点在内一共是5个字符。结果显示在网格中，显然左边的空格被填充了。这个调用指定，总长度为8个字符，小数位为4位：

SELECT STR(123.4, 8, 4) 

3、日期函数

这些函数可以操作DateTime与SmallDateTime类型的值。有些函数可用于解析日期值的日期与时间部分，有些函数可用于比较、操纵日期/时间值。

DATEADD()函数

DATEADD()函数用于在日期/时间值上加上日期单位间隔。比如，要得到2007年4月29日起90天后的日期，可以使用下列语句：

SELECT DATEADD(DAY, 90, '4-29-2007')

DATEDIFF()函数

DATEADD()和DATEDIFF()函数可以看作一对表兄弟，有点像乘法与除法。在等式的两端有4个元素：起始日期、时间间隔(datepart)、差值和最终日期。如果已知其中的三个值，就可以求出第4个值。如果在DATEADD()函数中使用起始日期、一个整型值和一个时间间隔，就可返回与起始日期相关的最终日期值。如果提供了起始日期、时间间隔和最终日期，DATEDIFF()函数就可以返回差值。

SELECT DATEDIFF(MONTH, '9-8-1989', '10-17-1991') 

DATEPART()与DATENAME()函数

这两个函数用于返回datetime或者shortdatetime值的日期部分。DATEPART()函数返回一个整型值；DATENAME()函数返回一个包含描述性文字的字符串。比如，将日期4-29-1988传递给DATEPART()函数，如指定返回月份值，则返回数字4：

SELECT DATEPART(MONTH, '4-29-1988') 

GETDATE()与GETUTCDATE()函数

这两个函数都用于返回datetime类型的当前日期与时间。GETUTCDATE()函数使用服务器上的时区设置来求出UTC时间，这和格林威治标准时间或飞行员所说的"祖鲁时"(Zulu Time)是一样的。两个函数都能精确到3.33毫秒。

SELECT GETDATE() 

SELECT GETUTCDATE()

 SELECT DATEDIFF(HOUR, GETDATE(), GETUTCDATE())

SYSDATETIME()和SYSUTCDATETIME()函数

这两个SQL Server 2008函数等价于GETDATE()和GETUTCDATE()函数，但不是返回datetime数据类型的结果，而是返回SQL Server 2008新的datetime2数据类型的结果，该数据类型可以精确到100纳秒，当然这取决于服务器安装的硬件。

SELECT SYSDATETIME() 

SELECT SYSUTCDATETIME()

 

DAY()、MONTH()和YEAR()函数

这三个函数分别返回以整数表示的datetime或者smalldatetime类型值的日、月、年。它们的用途很广泛，如可以创建独特的个性化日期格式。假设需要创建一个自定义的日期值作为字符串，通过将这三个函数的输出结果转换成字符类型，然后进行连接操作，就可以对输出结果以任何形式进行组合了：

SELECT 'Year: ' + CONVERT(varchar(4), YEAR(GETDATE()))  

   + ', Month: ' + CONVERT(varchar(2), MONTH(GETDATE()))  

+ ', Day: ' + CONVERT(varchar(2), DAY(GETDATE()))

ASCII()、CHAR()、UNICODE()和NCHAR()函数

这四个函数是相似的，它们都可以在字符和字符的标准数字表示之间转换。美国标准信息交换码(American Standard Code for Information Interchange，ASCII)标准字符集包含128个字母、数字和标点符号。这个字符集是IBM PC体系结构的基础，虽然有些字符现在看来已经很古老了，但还是被保留了下来，且仍是现代计算机技术的核心。如果在计算机上使用英语，则键盘上的每个字符都是用ASCII码表示的。

SELECT ASCII('A') 

SELECT CHAR(65) 

SELECT UNICODE(108)

SELECT NCHAR(220) 

CHARINDEX()和PATINDEX()函数

CHARINDEX()是原始的SQL函数，用于寻找在一个字符串中某子字符串第一次出现的位置。如函数名所示，这个函数返回一个整型值，表示某子字符串的第一个字符在整个字符串中的位置索引。

SELECT CHARINDEX('sh', 'Washington') 

SELECT PATINDEX('%M_rs%', 'The stars near Mars are far from ours')

LEN()函数

LEN()函数用于返回一个代表字符串长度的整型值。这是一个简单、有用的函数，经常与其他函数一起使用，来应用业务规则。以下例子将月份和日期转换为字符类型，然后测试它们的长度。 

SELECT LEN ('Washington')

LEFT()和RIGHT()函数

LEFT()与RIGHT()函数是相似的，它们都返回一定长度的子字符串。这两个函数的区别是，它们返回的分别是字符串的不同部分。LEFT()函数返回字符串最左边的字符，顺序从左数到右。RIGHT()函数正好相反，它从最右边的字符开始，以从右到左的顺序返回特定数量的字符。

SELECT LEFT (NAEE, 5)

SELECT RIGHT (NAEE, 3)

SUBSTRING()函数

SUBSTRING()函数能够从字符串的一个位置开始，往右数若干字符，返回一个特定长度的子字符串。和LEFT()函数不同之处是，该函数可以指定从哪个位置开始计数，这样就可以在字符串的任何位置摘取子字符串了。这个函数需要三个参数：要解析的字符串、起始位置索引、要返回的子字符串长度。如果要返回到所输入字符串尾部的所有字符，可以使用比所需长度更大的长度值。SUBSTRING()函数将返回最大可能长度的字符数，而不会将多出的长度以空格填充。

 SELECT SUBSTRING(FullName, 4, 6)

LOWER()和UPPER()函数

它们用于将字符串中所有字符分别都转换为小写和大写，这在比较用户输入或者存储用于比较的字符串时是非常有用的。字符串比较通常是区分大小写的，这取决于SQL Server安装时的设置。如果和其他的字符串操纵函数一起使用，就可以将字符串转换为合适的大小写，以便存储或显示。

SELECT LOWER (GROUPSX_XH)

SELECT UPPER (GROUPSX_XH)

LTRIM()和RTRIM()函数

这两个函数分别返回将字符串的左边和右边的空白修剪掉之后的字符串:

SELECT LTRIM (Value)

SELECT RTRIM (Value)

REPLACE()函数

REPLACE()函数可以把字符串中的某个字符或某个子字符串替换为另一个字符或者子字符串，该函数可以用于全局查找和替换工具中。

SELECT REPLACE(Phrase, 'aint', 'am not')

REPLICATE()和SPACE()函数

在需要将一些字符重复填充进一个字符串时，这两个函数是非常有用的。这里也使用SUBSTRING()例子中的临时表为每个名字填满20个字符，然后将20减去各个字符串的长度，以便将正确的值传递给REPLICATE()函数:

SELECT FullName + REPLICATE('*', 20 - LEN(FullName))

 

STUFF()函数

这个函数可将字符串中的一部分替换为另一个字符串。它本质上是将一个字符串以特定的长度插入另一个字符串中的特定位置上。这对于源值与目的值的长度不一样的字符串替换是很有用的。

 SELECT STUFF('Please submit your payment for 99.95 immediately. ', 32, 5, '109.95')

QUOTENAME()函数

这个函数和SQL Server对象名组合使用，以将结果传递给表达式。它只用于给输入的字符串加一对方括号，并返回新形成的字符串。如果参数包含保留的分隔符或者封装字符(比如引号或括号)，这个函数将修改字符串，以便SQL Server能将结果字符串中的这类字符当成文本字符。

 

4、排列函数

这些函数被用于以与结果集顺序无关的特定顺序，枚举已排序的或排在前面的结果集。

ROW_NUMBER()函数

ROW_NUMBER()函数根据作为参数传递给这个函数的ORDER BY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同，这些值将不会按顺序列出，但它们表示ROW_NUMBER函数的ORDER BY子句的顺序。

SELECT ProductCategoryID,Name,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum

FROM Production.ProductCategory

ORDER BY Name

不过，在函数调用中使用另一个ORDER BY子句时，这些值就是无序的了。