Celery's Blog

0%

SQLServer-Day7

发表于 Valine：

SQLServer-Day7

DROP INDEX语句

可以使用DROP INDEX命令删除表格中的索引

用于 Microsoft SQLJet (以及 Microsoft Access) 的语法:

DROP INDEX index_name ON table_name

用于 MS SQL Server的语法

DROP INDEX table_name.index_name

用于IBM DB2 和 Oracle语法：

DROP INDEX index_name

用于MySQL的语法

ALTER TABLE table_name DROP index_name

DROP TABLE 语句

用于删除表（表的结构、属性以及索引也会被删除）

DROP TABLE 表名称

DROP DATABASE语句

用于删除数据库

DROP DATABASE 数据库名称

TRUNCATE TABLE语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，可以使用TRUNCATE TABLE命令，仅仅删除表格中的数据

TRUNCATE TABLE 表名称

ALTER TABLE语句

用于在已有的表中添加、修改或删除列

添加列：

ALTER TABLE table_name

ADD column_name datatype

删除列：

ALTER TABLE table_name

DROP COLUMN column_name

**Tips：**某些数据库系统不允许这种在数据库表中删除列的方式

要更改表中列的数据类型：

ALTER TABLE table_name

ALTER COLUMN column_name datatype

例1：

原始的Persons表：

Id LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

现在，需要在表“Persons”中添加一个名为“Birthday”的新列

1
2
ALTER TABLE Persons
ADD Birthday date

请注意，新列 “Birthday” 的类型是 date，可以存放日期。数据类型规定列中可以存放的数据的类型。

所以，新的表长这样：

Id LastName FirstName Address City Birthday
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

例2：改变数据类型实例

1
2
ALTER TABLE Persons
ALTER COLUMN Birthday year

例3：删除Birthday列

1
2
ALTER TABLE Persons 
DROP COLUMN Birthday

AUTO INCREMENT 字段

会在新纪录插入表示生成一个唯一的数字

我们通常希望在每次插入记录时，自动创建主键字段的值，这时候，可以在表中创建一个auto-increament字段

用于MySQL的语法：

1
2
3
4
5
6
7
8
9
CREATE TABLE
(
P_Id int NOT NULL AUTO_INCREAMENT, 
LastName varchar(255) NOT NULL, 
FirstName varcahr(255), 
Address varcahr(255), 
City varcahr(255), 
PRIMARY KEY (P_Id)
)

上面的语句把“Persons”表中的P_Id列定义为auto-increament主键，默认的，AUTO_INCREAMENT的开始值是1，每条新纪录递增1

要让AUTO_INCREAMENT序列以其他的值起始，需要这样使用SQL语句：

1
ALTER TABLE Persons AUTO_INCREAMENT=100

要在“Persons”表中插入新纪录，我们不必为“P_Id”列规定值（会自动添加一个唯一的值）

1
2
INSERT INTO Persons (FirstName, LastName)
VALUES ('Bill', 'Gates')

用于SQL Serer的语法

1
2
3
4
5
6
7
8
9
CREATE TABLE
(
P_Id int PRIMARY KEY IDENTITY, 
LastName varchar(255) NOT NULL, 
FirstName varcahr(255), 
Address varcahr(255), 
City varcahr(255), 
PRIMARY KEY (P_Id)
)

若要规定“P_Id”列以20起始且递增10，请把identity改为IDENTITY(20, 10)

用于Access的语法

1
2
3
4
5
6
7
8
9
CREATE TABLE
(
P_Id int PRIMARY KEY AUTOINCREAMENT, 
LastName varchar(255) NOT NULL, 
FirstName varcahr(255), 
Address varcahr(255), 
City varcahr(255), 
PRIMARY KEY (P_Id)
)

修改起始值与增量，同SQL Server

用于Oracle的语法：

稍微复杂一点，必须通过sequence 对创建auto-increament字段(该对象生成数字序列)

CREATE SEQUENCE

1
2
3
4
5
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREASE BY1
CACHE 10

上面的代码创建名为seq_person的序列对象，它以1起始且以1递增。该对象缓存10个值以提高性能。CACHE选项规定了为了提高访问速度要储存多少个序列值

要在“Persons”表中插入新纪录，必须使用nextval函数（该函数从seq_person）序列中取回下一个值

1
2
INSERT INTO Persons(P_Id, FirstName, LastName)
VALUES (seq_person.nextval, 'Lars', 'Monsen')

“P_Id” 的赋值是来自 seq_person 序列的下一个数字

Date函数

SQL日期

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配

只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了

在讨论日期查询的复杂性之前，先看看内置日期的处理函数

MySQL Date函数

函数 描述
NOW() 返回当前的时间和日期
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的部分日期
EXTRATE() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

SQL Server Date函数

函数 描述
GETDATE() 返回当前日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期之间的时间
CONVERT() 用不同的格式显示日期/时间

SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式 YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式 YYYY-MM-DD HH:MM:SS
  • YEAR - 格式 YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式 YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME- 格式 YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式 唯一的数字

日期处理

如果不涉及时间部分，可以轻松的比较两个日期

比如，有下面这个”Orders”表：

OrderId ProductName OrderDate
1 computer 2008-12-26
2 printer 2008-12-26
3 electrograph 2008-11-12
4 telephone 2008-10-19

希望获取OrderDate为“2008-12-26”的记录

1
SELECT * FROM Orders WHERE OrderDate = '2008-12-16'

结果：

OrderId ProductName OrderDate
1 computer 2008-12-26
2 printer 2008-12-26

如果Orders表长成这样：

OrderId ProductName OrderDate
1 computer 2008-12-26 16:23:55
2 printer 2008-12-26 10:45：26
3 electrograph 2008-11-12 14:12:08
4 telephone 2008-10-19 12:56:10

依然使用上面的语句，将得不到结果，因为该查询不含有时间部分的日期

**Tips：**如果希望使查询简单并且更容易维护，那么尽量不要使用时间部分

NULL值

NULL值是一楼的位置数据

默认的，表的列可以存放NULL值

SQL NULL值

如果表中的某个列是可选的，那么可以在不向改列添加值的情况下插入新记录或更新已有记录，这意味着该字段将以NULL值保存

NULL值的处理方式与其他值不同

NULL用作为止的或不适用的值的占位符

**Tips：**无法比较NULL 和 0，他们是不等价的

无法使用比较运算符来测试NULL值，比如=， <或者<>

必须使用 IS NULL和IS NOT NULL操作符

SQL IS NULL

Persons表

Id LastName FirstName Address City
1 Adams John London
2 Bush George Fifth Avenue New York
3 Carter Thomas Beijing

仅仅选取在“Address”列中带有NULL值的记录

1
2
SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NULL

Tips: 只能使用IS NULL来查找NULL值

SQL IS NOT NULL

1
2
SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NOT NULL

NULL函数

Products表:

P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 computer 699 25 15
2 printer 365 36
3 telephone 280 159 57

假设“UnitsOnOrder”是可选的，而且可以包含NULL值

1
2
SELECT ProductName, UnitPrices*(UnitsInStock+UnitsOnOrder)
FROM Products

在上面的例子中，如果有“UnitOnOrder”值是NULL，那么结果是NULL

微软的ISNULL()函数用于规定如何处理NULL值

NVL(), IFNULL()和COALESCE()函数也可以达到相同的结果

SQL Server / MS Access

1
2
SELECT ProductName, UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder, 0))
FROM Products

Oracle

Oracle没有ISNULL()函数，但是可以使用NVL()函数达到相同的效果

1
2
SELECT ProductName, UnitPrice*(UnitsInStock+NVL(UnitsOnOrder, 0))
FROM Products

MySQL

1
2
SELECT ProductName, UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder, 0))
FROM Products

或者

1
2
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

SQL Server数据类型

Character字符串：

数据类型 描述
char(n) 固定长度的字符串，最多8000个字符
varchar(n) 可变长度的字符串，最多8000个字符
varchar(max) 可变长度的字符串，最多1,073,741,824个字符
text 可变长度的字符串，最多2GB字符数据

UniCode字符串

数据类型 描述
nchar(n) 固定长度的Unicode 数据，最多4000个字符
nvarchar(n) 可变长度的Unicode 数据，最多4000个字符
nvarchar(max) 可变长度的Unicode 数据，最多536,870,912个字符
ntext 可变长度的Unicode 数据，最多2GB字符数据

Binary类型

数据类型 描述
bit 允许0、1或NULL
binary(n) 固定长度的二进制数据。最多 8,000 字节。
varbinary(n) 可变长度的二进制数据。最多 8,000 字节。
varbinary(max)可变 可变长度的二进制数据。最多 2GB字节。
image 可变长度的二进制数据。最多 2GB。

Number类型

数据类型 描述 存储
tinyint 允许从 0 到 255 的所有数字。 1 字节
smallint 允许从 -32,768 到 32,767 的所有数字。 2 字节
int 允许从 -2,147,483,648 到 2,147,483,647 的所有数字。 4 字节
bigint 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 8 字节
decimal(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
numeric(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
smallmoney 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 4 字节
money 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 8 字节
float(n) 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 4 或 8 字节
real 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 4 字节

Date 类型：

数据类型 描述 存储
datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 8 bytes
datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 6-8 bytes
smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 4 bytes
date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes
time 仅存储时间。精度为 100 纳秒。 3-5 bytes
datetimeoffset 与 datetime2 相同，外加时区偏移。 8-10 bytes
timestamp 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型：

数据类型 描述
sql_variant 存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。
uniqueidentifier 存储全局标识符 (GUID)。
xml 存储 XML 格式化数据。最多 2GB。
cursor 存储对用于数据库操作的指针的引用。
table 存储结果集，供稍后处理。