SQL server Base

继整理c++方面资料后，blog好久都没有更新了，最近准备抽空把关于DotNET的学习记录整理上传。此篇为SQL Server方面的最基本常用资料。

如果觉得文章内容对您有用欢迎任意转载。但请注明出处http://www.cnblogs.com/nniixl/。

THE 1

系统数据库

1. Master数据库

所有系统级别信息，所有登录帐户和系统配置设置，所有其他的数据库及数据库文件的位子，SQL Server的初始化信息。

2. Tempdb数据库

保存所有临时表和临时存储过程，以及临时生成的工作表。

3. Model数据库

系统上创建的所有数据库的模板。

4. Msdb数据库

提供SQL Server代理程序调度警报、作业以及操作时使用。

创建数据库

CREATE DATABASE [数据库名]

ON（NAME = N’***’， FILENAME = N’***.MDF’,

SIZE = *, MAXSIZE = *, FILEGROWTH = * ）

LOG ON (NAME = N’***’， FILENAME = N’***.LDF’,

SIZE = *, MAXSIZE = *, FILEGROWTH = *)

COLLATE Chinese_PRC_CI_AS

删除数据库

USE master
GO

IF EXISTS (SELECT * FROM sysdatabases WHERE name='数据库名')
DROP DATABASE 数据库名

THE2

数据完整性

1. 实体完整性

（对记录）通过：索引唯一约束主键约束标识列属性

2. 域完整性

（对列）通过：数据类型检查约束输入格式外键约束默认值非空约束

3. 引用完整性

（主从表关联）通过：主键与外键间引用

4. 自定义完整性

通过：存储过程和触发器

外键无法增加主键中没有的数值在删除外键前无法删除主键数据（外键依赖主键数据）

数据类型

精确数字

bigint 从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字	decimal 从 -10^38 +1 到 10^38 –1 的固定精度和小数位的数字数据
int 从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）	numeric 功能上等同于 decimal
smallint 从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整数数据	money 货币数据值介于 -2^63 (-922,337,203,685,477.5808) 与 2^63 - 1 (+922,337,203,685,477.5807) 之间，精确到货币单位的千分之十
tinyint 从 0 到 255 的整数数据	smallmoney 货币数据值介于 -214,748.3648 与 +214,748.3647 之间，精确到货币单位的千分之十
bit 布尔

近似数字

float 从 -1.79E + 308 到 1.79E + 308 的浮点精度数字

real 从 -3.40E + 38 到 3.40E + 38 的浮点精度数字

日期和时间

datetime 从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒)

smalldatetime 从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据，精确到分钟

字符串

char 固定长非Unicode字符最大8000字符	text 可变长度的非 Unicode 数据，最大长度为 2^31 - 1 (2,147,483,647) 个字符
Varchar 可变长度的非 Unicode 数据，最长为 8,000 个字符

Unicode 字符串

nchar 固定长度的 Unicode 数据，最大长度为 4,000 个字符	ntext 可变长度 Unicode 数据，其最大长度为 2^30 - 1 (1,073,741,823) 个字符
nvarchar 可变长度 Unicode 数据，其最大长度为 4,000 字符。sysname 是系统提供用户定义的数据类型，在功能上等同于 nvarchar(128)，用于引用数据库对象名

二进制字符串

binary 固定长度的二进制数据，其最大长度为 8,000 个字节	image 可变长度的二进制数据，其最大长度为 2^31 - 1 (2,147,483,647) 个字节
varbinary 可变长度的二进制数据，其最大长度为 8,000 个字节

其他数据类型

cursor 游标的引用	timestamp 数据库范围的唯一数字，每次更新行时也进行更新
sql_variant 一种存储 SQL Server 支持的各种数据类型（text、ntext、timestamp 和 sql_variant 除外）值的数据类型	uniqueidentifier 全局唯一标识符 (GUID)
table 一种特殊的数据类型，存储供以后处理的结果集	xml

※ 姓名、地址、电话号码一般用字符数据类型

※ 性别一般用bit数据类型

※ 年龄一般使用日期存储

※ 照片用Image

※ 薪水使用货币数据类型

※ 身份证 NUMERIC(18, 0)

创建数据表

CREATE TABLE [表名]

（

[ID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL,

[ImpID] SMALLINT IDENTITY(1, 1),

. . .

）

对表进行修改

加约束：

(约束名约定)

主键(Primary Key) PK_

唯一(Unique Key) UQ_

默认(Default Key) DF_

检查(Check Key) CK_

外键(Foreign Key) FK_

ALTER TABLE tab1

ADD CONSTRAINT PK_Num PRIMARY KEY (Num)

ALTER TABLE tab1

ADD CONSTRAINT UQ_Num UNIQUE (Num)

ALTER TABLE tab1

ADD CONSTRAINT DF_sAddr DEFAULT (‘地址无’) FOR (sAddr)

ALTER TABLE tab1

ADD CONSTRAINT CK_sAge CHECK (sAge BETWEEN 15 AND 20)

ALTER TABLE tab1

ADD CONSTRAINT FK_stuHA FOREIGN KEY(外键列名) REFERENCES 表名(列名)

删除约束：

ALTER TABLE 表名 DROP CONSTRAINT 约束名

对表增 删改：

ALTER TABLE [表名]

add [列名] 数据类型是否空

drop column [列名]

alter column [列名] 数据类型是否空

删除表：

USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='表名')
DROP TABLE 数据库名

THE3

T-SQL组成

DML 数据库操作语言 SELECT UPDATE INSERT DELETE等

DCL 数据控制语言 GRANT REVOKE等

DDL 数据定义语言 CREATE TABLE CREATE VIEW DROP TABLE 等

比较运算符与通配符

比较运算基本与C一样（注：不等于为 <> ）

通配符

_	匹配一个字符	A LIKE ’L_ve’
%	任意长字符	A LIKE ‘%o%’
[]	括号中范围的一个字符	A LIKE ‘g[a-z]d’
[^]	不在括号中范围的任意一个字符	A LIKE ‘g[^a-z]d’

逻辑表达式

AND OR NOT

插入 INSERT

单行插入

INSERT INTO [表名] [列名，列名…] VALUES <值>

EXP：

INSERT INTO Stu (sName, sAge, sAddr )

VALUES (‘ZhangHan’, 20, ‘ShangHai’ )

注：一次插入一行数据不能违反非空约束

数据值数目必须与列数相同数值数据类型精度和位数必须匹配

不能给自动增长标识列指定值

对字符类型用单引号引起来

多行插入1

INSERT INTO [表名] [列名，列名…] SELECT [列名，列名…] FROM [表名]

EXP：

INSERT INTO Tab1 (sName, sAge, sAddr ) SELECT (aName, aAge, aAddr ) FROM Tab2

多行插入2

注：以下插入方式被插入表不可存在 该方式常用于创建临时表

SELECT [列名，列名…] INTO [表名] FROM [表名]

EXP：

SELECT (sName, sAge, sAddr )

INTO TempTable

FROM CrrentTable

EXP2: 该创建包含连续标识列的临时表以便查询用

SELECT IDENTITY(数据类型, 标识种子, 标识增长量) AS [列名, 列名…]

INTO 新表

FROM 原始表

多行插入3

INSERT [表名] [列名, 列名…]

SELECT [列值1，列值1…] UNION

SELECT [列值2，列值2…] UNION

SELECT [列值3，列值4…] UNION

. . .

SELECT [列值n，列值n…]

更新UPDATE

UPDATE [表名] SET [列名 = 更新值] { WHERE [更新条件] }

删除 DELETE TRUNCATE

DELETE FROM [表名] { WHERE [删除条件] }

TRUNCATE TABLE [表名] 可将标识列设置为初始状态

THE4

查询SELECT

SELECT [列名] FROM [表名]

{WHERE [查询条件表达式]}

{ORDER BY [排序列名] [ASC 或 DESC]}

对于列名

AS： sCode AS 学员姓名

= ：学员姓名 = sCode

+ ： first + ‘_’ + last AS 姓名

查询空行 查询非空行 Exp：

IS NULL IS NOT NULL SELECT sID FROM Tab WHERE sAdd IS NULL

查询常量

SELECT sName， sAddr， ‘中国’ AS 地区 FROM Record

查询返回行限制

SELECT TOP 5 sName, sAddr FROM Tab1

SELECT TOP 50 PERCENT sName, sAddr FROM Tab1

查询SELECT 模糊查询

LIKE

SELECT * FROM Stu WHERE sName LIKE ‘a[d-g]’

BETWEEN

SELECT * FROM Stu WHERE sNum BETWEEN 10 AND 70

IN (NOT IN)

SELECT * FROM Stu WHERE sAddr IN (‘SH’, ’WH’, ’BJ’)

常用函数

注：以下仅为最常用函数需了解全部函数和规范请参考MS联机帮助下列函数很简单的略去例子

常用字符串函数

CHARINDEX	返回指定字符串在另一个字符串中的位子	SELECT CHARINDEX(‘abc’, ‘fgabcdefg’) 返回：3
LEN	返回字符串长度	SELECT LEN(‘哈myname’) 返回：7
LOWER	大小转小写	SELECT LOWER(‘SQL SERVER’)
UPPER	小写转大写	SELECT LOWER(‘sql SERVER’)
LTRIM （RTRIM）	清除左（右）空格
RIGHT （LEFT）	返回右（左）指定长度字符
REPLACE	替换字符串中指定字符到指定字符	SELECT REPLACE(‘abcdef’, ‘a’, ‘X’) 返回：Xbcdef
STUFF	删除指定位子开始指定长度字符并在其位子插入指定字符	SELECT STUFF(‘abcde’, 2, 3, ‘FILL’) 返回：aFILLe

SUBSTRING ( expression ,start , length )      返回字符表达式、二进制表达式、文本表达式或图像表达式的一部分

常用日期函数

GETDATE	取得当前时间
DATEADD	将指定数值添加到指定日期的指定位子	SELECT DATEADD(mm, 5, ‘02/03/08’) 返回：02/08/08
DATEDIFF	两个时间指定位子的区别	SELECT DATEDIFF(mm, ‘02/03/08’, ’08/03/08’) 返回：72
DATENAME	日期中指定位子的字符串形式	SELECT DATENAME(dw, '09/01/08') 返回：礼拜四
DATEPART	日期中指定位子的整数形式	SELECT DATEPART(yy, '09/01/08') 返回：2009

Year    yy, yyyy

quarter    qq, q

Month    mm, m

dayofyear   dy, y

Day    dd, d

Week    wk, ww

Hour    hh

minute    mi, n

second    ss, s

millisecond   ms

常用数学函数

ABS	绝对值
CEILING	大于等于指定值的最小整数
FLOOR	小于等于指定值的最大整数
POWER	去数值的幂
ROUND	将数值四舍五入到指定精度
SIGN	证书返回+1 负数返回-1 0返回0
SQRT	取平方根

常用系统函数

CONVERT	数据类型转换重要	SELECT CONVERT(VARCHAR(7), 3344521) 返回：{字符串} 3344521
CURRENT_USER	返回当前用户名字
DATALENGTH	返回指定表达式字节数	SELECT DATALENGTH(‘三年2班’) 返回：7
HOST_NAME	当前用户登录计算机名字
SYSTEM_USER	当前用户登录名字
USER_NAME	给定用户ID返回名字

聚合函数

仅常用

SUM 求和 AVG平均值 MAX最大值 MIN最小值 COUNT计数

注：聚合函数不可与其他多行结果的列同时被查询除GROUP BY所分组列外

EXP：

SELECT COUNT(*) MAX(score) FROM Stu WHERE sCore >=60

分组查询及筛选

SELECT GradeID , COUNT(*) FROM Stu

WHERE score >= 60

GROUP BY GradeID

HAVING COUNT(*) > 30

ORDER BY GradeID DESC

--从Stu表中以降序的方式列出班上至少有30人以上及格的班级号

WHERE ---> GROUP BY ---> HAVING

WHERE子句从数据源中去掉不符合搜索条件的数据

GROUP BY 子句为分组统计

HAVING 子句去掉不符合条件的分组

多表链接的查询

内联接

INNER JOIN 注：（其中出现<>比较的时候并不是同行点对点比较而是乱序全行比较）

EXP：

SELECT S.sName, C.cID, C.cAddr FROM Stu AS S

INNER JOIN Scii AS C ON (S.ID = C.ID)

外联接

左外联接

LEFT JOIN 左表中所选所有列都将被列出右表中没有与其对应值则置为NULL

右外联接

RIGHT JOIN 右表中所选所有列都将被列出左表中没有与其对应值则置为NULL

完整联接

FULL JOIN 左右表中所选所有列都将被列出对应表中没有与其对应值则置为NULL

交叉连接

CORSS JOIN 左右表每行数据与右表一一组合

posted @ 2008-09-25 17:19 Edward Xie 阅读(694) 评论(0) 编辑收藏举报

刷新页面返回顶部

Edward Xie

( http://t.sina.com.cn/edwxi Or edward.xie123@gmail.com )

SQL server Base

继整理c++方面资料后，blog好久都没有更新了，最近准备抽空把关于DotNET的学习记录整理上传。此篇为SQL Server方面的最基本常用资料。

如果觉得文章内容对您有用欢迎任意转载。但请注明出处http://www.cnblogs.com/nniixl/。

THE 1

系统数据库

创建数据库

THE2

数据完整性

数据类型

创建数据表

对表进行修改

THE3

T-SQL组成

比较运算符与通配符

逻辑表达式

插入 INSERT

更新UPDATE

删除 DELETE TRUNCATE

THE4

查询SELECT

查询SELECT 模糊查询

常用函数

聚合函数

分组查询及筛选

多表链接的查询

公告

Edward Xie

( http://t.sina.com.cn/edwxi Or edward.xie123@gmail.com )

SQL server Base

继整理c++方面资料后，blog好久都没有更新了，最近准备抽空把关于DotNET的学习记录整理上传。此篇为SQL Server方面的最基本常用资料。

如果觉得文章内容对您有用欢迎任意转载。但请注明出处http://www.cnblogs.com/nniixl/。

THE 1

系统数据库

创建数据库

THE2

数据完整性

数据类型

创建数据表

对表进行修改

THE3

T-SQL组成

比较运算符 与 通配符

逻辑表达式

插入 INSERT

更新UPDATE

删除 DELETE TRUNCATE

THE4

查询SELECT

查询SELECT 模糊查询

常用函数

聚合函数

分组查询 及 筛选

多表链接的查询

公告

比较运算符与通配符

分组查询及筛选