sql高级

创建数据库

这样做就可以创建一个数据库：

CREATE DATABASE 数据库名称

创建一个表

这样做就可以创建一个数据库中的表：

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)

实例

本例演示如何创建名为 "Person" 的表，有四个列。列名是："LastName"、"FirstName"、"Address" 以及 "Age"：

CREATE TABLE Person 
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
) 

这个例子演示如何为某些列定义长度的最大值：

CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d) numeric(size,d)	容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

创建索引

索引被创建于已有的表中，它可使对行的定位更快速更有效。可以在表格的一个或者多个列上创建索引，每个索引都会被起个名字。用户无法看到索引，它们只能被用来加速查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常用于搜索的列上面创建索引。

唯一的索引 (Unique Index)

在表格上面创建某个一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX 索引名称
ON 表名称 (列名称) 

"列名称" 规定你需要索引的列。

简单的索引

在表上创建一个简单的索引。当我们省略关键词 UNIQUE 时，就可以使用重复的值。

CREATE INDEX 索引名称
ON 表名称 (列名称)

"列名称" 规定你需要索引的列。

实例

本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 字段：

CREATE INDEX PersonIndex
ON Person (LastName) 

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

CREATE INDEX PersonIndex
ON Person (LastName DESC) 

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

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

  
  


   
   
撤销 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 index_name

  
  

  
  


   
   
删除表或数据库

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

   
   
DROP TABLE 表名称

   
   
删除数据库：

   
   
DROP DATABASE 数据库名称

  
  

  
  


   
   
截取表格

   
   
如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：

   
   
TRUNCATE TABLE 表名称

   
   
 

   
   
 

   
   
    
    


     
     
ALTER TABLE

     
     
ALTER TABLE 语句用于在已有的表中添加或删除列。
     
     
ALTER TABLE 表名称 ADD 列名称 数据类型

ALTER TABLE 表名称 DROP COLUMN 列名称


     
     
注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN
column_name)。

    
    

    
    


     
     
Person:

     
     
LastName
FirstName
Address
Bush
George
Fifth Avenue

    
    

    
    


     
     
实例 1

     
     
在 "Person" 表中添加名为 "City" 的列：

     
     
ALTER TABLE Person ADD City varchar(30)

     
     
结果：

     
     
LastName
FirstName
Address
City
Bush
George
Fifth Avenue
 

    
    

    
    


     
     
实例 2

     
     
在 "Person" 表中删除 "Address" 列：

     
     
ALTER TABLE Person DROP COLUMN Address

     
     
结果：

     
     
LastName
FirstName
City
Bush
George
 

    
    


  
  

 

SQL 拥有很多可用于计数和计算的内建函数。

函数的语法

内建 SQL 函数的语法是：

SELECT function(列) FROM 表

函数的类型

在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：

• Aggregate 函数
• Scalar 函数

合计函数（Aggregate functions）

Aggregate 函数的操作面向一系列的值，并返回一个单一的值。

注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，则这个 SELECT 必须使用 GROUP BY 语句！

"Persons" table (在大部分的例子中使用过)

Name	Age
Adams, John	38
Bush, George	33
Carter, Thomas	28

MS Access 中的合计函数

函数	描述
AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括 NULL 值）
COUNT(*)	返回被选行数
FIRST(column)	返回在指定的域中第一个记录的值
LAST(column)	返回在指定的域中最后一个记录的值
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column)	返回某列的总和
VAR(column)
VARP(column)

在 SQL Server 中的合计函数

函数	描述
AVG(column)	返回某列的行数
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column)	返回某列的行数（不包括NULL值）
COUNT(*)	返回被选行数
COUNT(DISTINCT column)	返回相异结果的数目
FIRST(column)	返回在指定的域中第一个记录的值（SQLServer2000 不支持）
LAST(column)	返回在指定的域中最后一个记录的值（SQLServer2000 不支持）
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
STDEV(column)
STDEVP(column)
SUM(column)	返回某列的总和
VAR(column)
VARP(column)

Scalar 函数

Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。

MS Access 中的 Scalar 函数

函数	描述
UCASE(c)	将某个域转换为大写
LCASE(c)	将某个域转换为小写
MID(c,start[,end])	从某个文本域提取字符
LEN(c)	返回某个文本域的长度
INSTR(c,char)	返回在某个文本域中指定字符的数值位置
LEFT(c,number_of_char)	返回某个被请求的文本域的左侧部分
RIGHT(c,number_of_char)	返回某个被请求的文本域的右侧部分
ROUND(c,decimals)	对某个数值域进行指定小数位数的四舍五入
MOD(x,y)	返回除法操作的余数
NOW()	返回当前的系统日期
FORMAT(c,format)	改变某个域的显示方式
DATEDIFF(d,date1,date2)	用于执行日期计算

 

合计函数 (比如 SUM) 常常需要添加 GROUP BY 功能。

GROUP BY...

把 GROUP BY 加入 SQL 的原因是，合计函数（比如 SUM）在每次被调用后会返回所有列的合计，如果没有 GROUP BY，就无法计算每个单独列值组合的总和。

GROUP BY 的语法：

SELECT column,SUM(column) FROM table GROUP BY column

GROUP BY 实例

表 "Sales":

Company	Amount
W3School	6500
IBM	5500
W3School	7300

SQL:

SELECT Company, SUM(Amount) FROM Sales 

结果:

Company	SUM(Amount)
W3School	19300
IBM	19300
W3School	19300

上面的代码是无效的，这是由于被返回的列没有进行部分合计。GROUP BY 子句能解决这个问题：

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company

结果:

Company	SUM(Amount)
W3School	13800
IBM	5500

HAVING...

把 HAVING 加入 SQL 的原因是，WHERE 无法应用于合计函数，而如果没有 HAVING，就无法测试结果条件。

HAVING 的语法:

SELECT column,SUM(column) FROM table
GROUP BY column
HAVING SUM(column) condition value

表 "Sales":

Company	Amount
W3School	6500
IBM	5500
W3School	7300

SQL:

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
HAVING SUM(Amount)>10000

结果：

Company	SUM(Amount)
W3School	13800

 

SELECT INTO 语句

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

语法：

SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source

制作备份复件

下面的例子会制作 "Persons" 表的备份复件：

SELECT * INTO Persons_backup FROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT Persons.* INTO Persons IN 'Backup.mdb' FROM Persons

如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：

SELECT LastName,FirstName INTO Persons_backup FROM Persons

你也可以添加 WHERE 子句。下面的例子通过从 "Persons" 表中提取居住在 "Sandnes" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：

SELECT LastName,Firstname INTO Persons_backup
FROM Persons
WHERE City='Sandnes'

从超过一个表中选取数据也是可以做到的。下面的例子会创建一个名为 "Empl_Ord_backup" 的新表，其中包含了从 Employees 和 Orders 两个表中取得的信息：

SELECT Employees.Name,Orders.Product
INTO Empl_Ord_backup
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

  
  


   
   
视图是基于 SELECT 语句的结果集的可视化的表。
  
  
  
  


   
   
什么是视图？

   
   
在 SQL 中，一个视图是基于某个 SELECT 语句的结果集的可视化的表。

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

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

   
   
语法
   
   
CREATE VIEW 视图名称 AS SELECT 列名称 FROM 表名称 WHERE 条件

   
   
注释：数据库不会存储视图数据！每当用户查询某个视图时，数据库引擎会使用视图的SELECT语句来重建数据。

  
  

  
  


   
   
使用视图

   
   
可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、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
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products) 


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

   
   
SELECT * FROM [Products Above Average Price]

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

   
   
CREATE VIEW [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]

   
   
我们也可以向查询添加条件。现在，我们仅仅需要查看 "Beverages" 类的全部销量：

   
   
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'