数据库高级教程（三）

1.SQL create INDEX

用于在表中创建索引

在不读取整个表得情况下，索引是数据库应用程序可以更快的查找数据

索引

您可以在表中创建索引，以便更加快速高效的查询数据，

用户无法看到索引，他们只能用来加速搜索和查询

注意 更新一个包含索引的表需要比更新一个无索引的表需要更多的时间。这是因为索引本身也需要更新，因此理想的做法是仅仅在常常被搜索的列（以及表中）上面创建索引

语法

在表中创建一个简单的索引，允许使用重复的值

create  index  index_name

ON table_name (column_name)

注意 column_name规定需要索引的列

SQL create  unique index  index_name   ON  table_name(column _name)

create  index实例

创建一个简单的索引，名字为PersonIndex 在Person表中得LastName列

create  index  PersonIndex

ON Person （LastName）

如果希望降序索引某个列中得值，可以在列名称后添加保留字DESC

create  index  PersonIndex

ON Person（LastName DESC）

如果索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开

create  Index PersonIndex  ON  Person （LastName,FirstName）

2.SQL撤销索引。表以及数据库

Drop语句

SQL DROP INDEX

我们可以使用DROP INDEX 命令删除表格中得索引。

用于MicrosoftSQLJet（以及 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的语法

Alert  table  table_name  DROP  INDEX  index_name

SQL DROP  TABLE 语句

DROP table语句用于删除表（表的结构，属性以及索引也将会被删除）

DROP table 表名称

3.SQL DROP database语句

用于删除数据库

DROP database 数据库名称

SQL truncate  table语句

如果我们仅仅需要出去表中得数据，但不需要删除表本身、则可以使用该语句

TRUNCATE TABLE 表名称 仅仅删除表格中得数据、。

4.Alter Table 语句

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

语法

在表总添加列

ALERT TABLE  table_name  ADD colume_name datatype

删除表中的列

ALERT TABLE table_name  DROP COLUMN column_name(某些数据库系统中不允许这种删除方式)

改变表中列的数据类型

ALERT TABLE table_name ALERT column column_name dataType

原始的表

Id                 LastName                 FirstName                city

1                   adamas                      john                        London

2                   Bush                           Geodge                  New York

3                 Carter                           Thomas                  Beijing

SQL  Alert Table 实例

ALERT  TABLE  Person ADD Birthday   date

注意： birthday的数据类型为 date 可以存放日期

新的表中得类型，

Id                 LastName                 FirstName                city                      Birthday

1                   adamas                      john                        London

2                   Bush                           Geodge                  New York

3                 Carter                           Thomas                  Beijing

改变数据类型实例

改变 Person中Birthday列的数据类型

ALERT TABLE Person ALERT COLUMN Birthday year

注意 Birthday 的数据类型为year 可以存放2位或4位格式的年份。

DROP COLUMN

删除 Person表中的Birthday的列

Id                 LastName                 FirstName                city

1                   adamas                      john                        London

2                   Bush                           Geodge                  New York

3                 Carter                           Thomas                  Beijing

5.SQL AUTO INCREMENT

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

AUTO INCREMENT 字段

我们通常希望在每次插入新纪录时，自动创建主键字段的值。

用于MYSQL的语法

把Person表中的P_Id 列定义为auto-increment主键

create TABLE Person

（

 P_Id int NOT  NULL AUTO_INCREMENT，

LastName varchar（255）NOT NULL，

firstName varchar （255），

address varchar （255），

city varchar （255），

PRIMARY KEY （P_Id）

）

MySQL 使用auto_INCREMENT 关键字来执行auto-increment任务，

默认，AUTO-INCREMENT的开始值为1，每条新纪录递增1，

要让AUTO_INCREMENT序列以其他的值起始，使用语法

alter  TABLE Person AUTO_INCREMENT=100

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

insert  into   Person（FirstName，LastName）

values  ('Bill','Gates')

上面的SQL语句会在Person表中插入一条新纪录，P_Id会被赋予一个唯一的值FirstName会被设置为Bill，LastName会被设置为Gates

用于SQL SERVER的语法

create TABLE Person

（

 P_Id int Primary KEY identity，

LastName varchar（255）NOT NULL，

firstName varchar （255），

address varchar （255），

city varchar （255），

）

MySQL 使用IDENTITY 关键字来执行auto-increment任务，

默认的 IDENTITY的开始值为1，每条新纪录递增1

要规定P_Id 列以20起始且递增10，可以把identtity 改为 IDENTITY（20，10）

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

insert  into   Person（FirstName，LastName）

values  ('Bill','Gates')

上面的SQL语句会在Person表中插入一条新纪录，P_Id会被赋予一个唯一的值FirstName会被设置为Bill，LastName会被设置为Gates

用于Access的语法

把Person表中的P_Id 列定义为auto-increment主键

create TABLE Person

（

 P_Id int PRIMARY KEY AUTO_INCREMENT，

LastName varchar（255）NOT NULL，

firstName varchar （255），

address varchar （255），

city varchar （255），

）

MY Access 使用AUTOINCREMENT 关键字来执行auto-increment任务，

默认的 IDENTITY的开始值为1，每条新纪录递增1

要规定P_Id 列以20起始且递增10，可以把identtity 改为 AUTOINCREMENT（20，10）

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

insert  into   Person（FirstName，LastName）

values  ('Bill','Gates')

上面的SQL语句会在Person表中插入一条新纪录，P_Id会被赋予一个唯一的值FirstName会被设置为Bill，LastName会被设置为Gates

用于Oracle的语法

必须通过sequence对创建auto-increment字段，（该对象生成数字序列）

使用下面的create sequence  语法

create sequence seq_Person

minvalue 1

start with  1

increment by 1

cache  10

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

要在Person表中插入新纪录，我们必须使用nextval函数（该函数从seq_Person序列中取回下一个值）

insert  into Person  （P_Id,FirstName,LastName）

values(seq_Person.nextval,'Lars','Monsen')

上面的SQL语句会在Person表中插入一条新纪录，P_Id的赋值是来自seq_Person序列的下一个数字FirstName会被设置为Lars，LastName会被设置为Monsen

6.SQL View 

视图是基于SQL结果集的可视化的表。

视图包含行和列，就像一个真实的表，视图中得字段就是来自一个或者多个数据库中真是的表中的字段，我们可以向视图中添加SQL函数、WHERE以及join语句。我们可以提交数据，就像这些来自于某个单一的表。

注意 数据库中得设计和结构不会受到视图中的函数、where或者join语句的影响。

SQL  create view的语法

create view  view_name  As  select column_name(s) from  table_name where condition

注意 视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据

实例 ：

可以从某个查询内部。某个存储过程。或者从另一个视图内部来使用视图，可以通过向视图中添加函数、join等等，我们可以向用户精确的提交我们希望提交的数据。

样本数据库northwind拥有一些被默认安装的视图。视图current product list会从products表列出所有正在使用的产品。这个视图使用下列SQL创建

create view [current  product List]  as SELECT productID，ProductName From Products where Discontinued=No

我们可以查询这个视图

SELECT *FROM [current product List]

northwind 样本数据库中得另一个视图会选取Products表中所有单位价格高于平均价格的产品

create view [products  above average  Price]  as SELECTProductName UnitPrice From Products where UnitPrice>(SELECT AVG（unitPrice）FROM products)

我们可以向这样查询上面这个视图

SELECT *FROM [product above  average Price]

另一个来自northwind数据库的视图实例计算在1997年每个种类的销售总数，注意这个视图会从另一个名为Product Sales for 1997 的视图那里选取数据

create TABLE  [category  Sales for 1997 ] as SELECT distinct categoryName，Sum（Productsales）As CategorySales from  [Product Sales for 1997] group BY CategoryName

我们可以像这样查询上面这个视图

SELECT *FROM [Category Sales For 1997] 

我们也可以查询添加条件 现在我们需要查询“Beverage”类的全部销量

SELECT *FROM [Category Sales For 1997]  where CategoryName=‘Beverages’

SQL 更新视图

SQL create or replace view syntax 

create or replace view view_name as SELECT column_name From table_name where condition

现在我们希望向current product List 视图中添加category列

create view [current product List] as SELECT productId，ProductName，category FROM Product where discontinued=no

SQL撤销视图

SQL  DROP  view syntax DROP view  view_name

7. SQL Date 函数

当我们处理日期时，最难得任务恐怕是确保插入的日期的格式，与数据库中日期列的格式相匹配，只要数据包含的时日期部分，运行查询就不会出问题，但是如果涉及到时间，就复杂了

Mysql Date函数

函数                                                      描述

NOW（）                                        返回当前日期和时间

CURDATE（）                                返回当前的日期

CURTIME（）                                 返回当前时间

DATE（）                                         提取日期或日期/时间表达式中的日期部分

EXTRACT（）                                 返回日期/时间按的单独部分

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

SQL Server

DATE  YYYY--MM-DD

DATETIME   YYYY-MM-DD HH：MM：SS

SMALLDATETIME  YYYY-MM-DD  HH：MM：SS

TIMESTAMP     唯一的数字

SQL 日期处理

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

看下面的Order表

 OrderId      ProductName              OrderDate

   1             computer    2008-12-26

   2             printer       2008-12-26

  3  electrograph2008-11-12

   4           tellephone 2008-10-18

我们希望选取OrderDate为2008-12-26的记录

SELECT *from Order where  OrderName=‘2008-12-26’

结果为

 OrderId      ProductName              OrderDate

   1             computer    2008-12-26

   2             printer       2008-12-26

  

现在假设Order为下表

 OrderId      ProductName              OrderDate

   1             computer    2008-12-26  16：23：55

   2             printer       2008-12-26  12：34：22

  3  electrograph2008-11-12  14：12：08

   4           tellephone 2008-10-18  11：55：10

SELECT *from Order where  OrderName=‘2008-12-26’

将查询不到结果，这是由于改查询不含有时间部分的日期

注意  如果希望查询简单且易维护，则请不要再日期中使用时间部分

8.SQL NULL

NULL值是遗漏的未知数据  默认的 表的列可以存放null值

如果表中的某个列是可选的，那么我们可以在不像该列添加值的情况下插入新纪录或者更新已经有的记录。这意味着该字段以null值保存

null值得处理方式与其他值不同

null用作未知的或者不适用的值得占位符。

注意 无法比较null和0，他们是不等价的。

SQL null值处理

Person表

Id                 LastName                 FirstName                city                                 address

1                   adamas                      john                        London                                           

2                   Bush                           Geodge                  New York                      fifth  Avenue

3                 Carter                           Thomas                  Beijing

假如 Person表中的Address列是可选的，这意味着如果在address列插入一条不带值的记录，address列会使用null值保存

无法使用比较运算符（< >,=,>等）来测试null值

我们必须使用is  null和 is not null操作法

SQL  Is NULL

我们如何仅仅选取在address列中带有NULL值得记录呢

则必须使用 Is NULL运算符

select  LastName，FirstName，Address From Person where  Address is NULL

结果集为

LastName                       FirstName                   Address

Adams                                John

Carter                                  Thomas

提示 请始终使用 is  null 来查找null值

SQL is NOT null 

我们如何选取在address列中不带有null值得记录

我们必须使用使用 is not null 操作符

select   LastName，FirstName，address from  Persons  where address  is  not  null

9.SQL NULL 函数

SQL isnull ，NVL（），IFNULL，COALESCE函数

下面为Product表

P_Id                    ProductName                  UnitPrice                    UnitStock              UnitOnOrder

1                         computer                            699                          25                          14

2                        printer                                  399                          34                       

3                        telephone                            283                          135                          55

假如 UnitOnOrder 是可选的，而且可以包含NULL值

SELECT ProductName，UnitPrice*（UnitsInStock+UnitsOnOrder）From Products

如果 UnitsOnOrder为Null，那么结果为NULL

微软的isNull（）函数用于规定如何处理Null值

NVL（），IFNULL（），和COALESCE（），函数也可以达到相同的效果

在这里，我们希望NULL值为0

下面，如果UnitsOnOrder 是Null，则不利于计算，因此如果值是Null则 isNUll返回0

SQL Server/ Ms Access

SELECT ProductName，UnitPrice*（UnitInStock+ISNull（UnitsOnOrder，0））

Oracle

Oracle没有 isNull函数，但可以使用NVL（）

SELECT ProductName，UnitPrice*（UnitInStock+NVL（UnitsOnOrder，0））

MysQL

MySQL用IFNull（）

SELECT ProductName，UnitPrice*（UnitInStock+IFNull（UnitsOnOrder，0））

或者可以使用 coalesce函数

SELECT ProductName，UnitPrice*（UnitInStock+Coalesce（UnitsOnOrder，0））

10.数据类型

Microsoft Access、MySQL 以及 SQL Server 所使用的数据类型和范围。

Microsoft Access 数据类型

数据类型	描述	存储
Text	用于文本或文本与数字的组合。最多 255 个字符。
Memo	Memo 用于更大数量的文本。最多存储 65,536 个字符。 注释：无法对 memo 字段进行排序。不过它们是可搜索的。
Byte	允许 0 到 255 的数字。	1 字节
Integer	允许介于 -32,768 到 32,767 之间的数字。	2 字节
Long	允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字	4 字节
Single	单精度浮点。处理大多数小数。	4 字节
Double	双精度浮点。处理大多数小数。	8 字节
Currency	用于货币。支持 15 位的元，外加 4 位小数。 提示：您可以选择使用哪个国家的货币。	8 字节
AutoNumber	AutoNumber 字段自动为每条记录分配数字，通常从 1 开始。	4 字节
Date/Time	用于日期和时间	8 字节
Yes/No	逻辑字段，可以显示为 Yes/No、True/False 或 On/Off。 在代码中，使用常量 True 和 False （等价于 1 和 0） 注释：Yes/No 字段中不允许 Null 值	1 比特
Ole Object	可以存储图片、音频、视频或其他 BLOBs (Binary Large OBjects)	最多 1GB
Hyperlink	包含指向其他文件的链接，包括网页。
Lookup Wizard	允许你创建一个可从下列列表中进行选择的选项列表。	4 字节

MySQL 数据类型

在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值：ENUM('X','Y','Z')
SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

SQL Server 数据类型

Character 字符串：

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

Unicode 字符串：

数据类型	描述	存储
nchar(n)	固定长度的 Unicode 数据。最多 4,000 个字符。
nvarchar(n)	可变长度的 Unicode 数据。最多 4,000 个字符。
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	存储结果集，供稍后处理。

11.SQL服务器 RDBMS

DBMS（Database Management System）数据库管理系统

其为可以访问数据库中数据的计算机程序，是我们有能力在数据库中提取，修改或者存贮信息。

不同的DBMS提供不同的函数供查询，提交以及修改数据

RDBMS（ Relational  Database Management System）关系数据库管理系统

也是数据库管理系统，其数据库是根据数据间的关系组织和访问数据的。