SQL 学习一
1. SQL一直没有像样的学过，或者说，对SQL的使用不能信手拈来，今天(2012-1-31)开始从w3c上学习sql教程，记录一二。
2. 这些不是很难，快速的过一遍为妙，毕竟时间不是无限的。
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--INSERT INTO
INSERT INTO table_name VALUES (值1, 值2,....);
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');
--DELETE
DELETE FROM Person WHERE LastName = 'Wilson';
--删除所有行
DELETE FROM table_name;
DELETE * FROM table_name;
--UPDATE
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson';
--对查询的结果集进行去重
DISTINCT;
--SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。
SELECT * FROM Persons WHERE FirstName='Bush';
SELECT * FROM Persons WHERE Year>1965;
--ORDER BY默认是ASC升序，DESC降序。
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SQL Server
--从 "Persons" 表中选取 50% 的记录
SELECT TOP 50 PERCENT * FROM Persons;
--从 "Persons" 表中选取头两条记录
SELECT TOP 2 * FROM Persons;
--Oracle
SELECT * FROM Persons WHERE ROWNUM <= 5;
--MySQL
SELECT * FROM Persons LIMIT 5;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-- % 替代一个或多个字符
-- _ 仅替代一个字符
-- [charlist] 字符列中的任何单一字符
-- [^charlist]或者[!charlist] 不在字符列中的任何单一字符
SELECT * FROM Persons WHERE City LIKE 'Ne%';
SELECT * FROM Persons WHERE City LIKE '%lond%';
SELECT * FROM Persons WHERE FirstName LIKE '_eorge';
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er';
--从 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons WHERE City LIKE '[ALN]%';
--从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Persons WHERE City LIKE '[!ALN]%';
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--IN
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');
--操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。
--某些数据库会列出介于 "Adams" 和 "Carter" 之间的人，但不包括 "Adams" 和 "Carter" ；
--某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人；
--而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人，包括 "Adams" ，但不包括 "Carter" 。
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter';
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';
--SQL Alias
SELECT column_name AS alias_name FROM table_name;
SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p, Product_Orders AS po WHERE p.LastName='Adams' AND p.FirstName='John';
SELECT LastName AS Family, FirstName AS Name FROM Persons;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--INNER JOIN 与 JOIN 是相同的。LEFT JOIN / RIGHT JOIN
--在某些数据库中， FULL JOIN 称为 FULL OUTER JOIN。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName;
--UNION 操作符用于合并两个或多个 SELECT 语句的结果集
--UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
--默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中
--从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表
SELECT LastName,Firstname INTO Persons_backup FROM Persons WHERE City='Beijing';
--MySQL不支持SELECT INOT, 替代方法
CREATE TABLE new_table_name (SELECT * FROM old_table_name);
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
CREATE DATABASE database_name;
CREATE TABLE Persons(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--NOT NULL 约束强制列不接受 NULL 值
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证
--PRIMARY KEY 拥有自动定义的 UNIQUE 约束.每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
--MySQL:
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
);
--SQL Server / Oracle:
CREATE TABLE Persons(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
--如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
);
--当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束
ALTER TABLE Persons ADD UNIQUE (P_Id);
--如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);
--撤销 UNIQUE 约束
--MySQL:
ALTER TABLE Persons DROP INDEX uc_PersonID;
--SQL Server / Oracle:
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SQL PRIMARY KEY Constraint on CREATE TABLE
--MySQL:
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
);
--SQL Server / Oracle / MS Access:
CREATE TABLE Persons(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
--如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID PRIMARY KEY (Id_P,LastName)
);
--SQL PRIMARY KEY Constraint on ALTER TABLE, 如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）
--MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD PRIMARY KEY (Id_P);
--如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName);
--撤销 PRIMARY KEY 约束
--MySQL:
ALTER TABLE Persons DROP PRIMARY KEY;
--SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SQL FOREIGN KEY Constraint on CREATE TABLE
--MySQL:
CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
);
--SQL Server / Oracle / MS Access:
CREATE TABLE Orders(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
);
--如果需要命名 FOREIGN KEY 约束
CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
);
--SQL FOREIGN KEY Constraint on ALTER TABLE
--如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束：
ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);
--如果需要命名 FOREIGN KEY 约束：
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);
--撤销 FOREIGN KEY 约束
--MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;
--SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SQL CHECK Constraint on CREATE TABLE
--CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数
--My SQL:
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
);
--SQL Server / Oracle / MS Access:
CREATE TABLE Persons(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
--如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
);
--SQL CHECK Constraint on ALTER TABLE
--如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束
ALTER TABLE Persons ADD CHECK (Id_P>0);
--如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes');
--撤销 CHECK 约束
ALTER TABLE Persons DROP CONSTRAINT chk_Person;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--SQL DEFAULT Constraint on CREATE TABLE
CREATE TABLE Persons(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);
--通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
);
--SQL DEFAULT Constraint on ALTER TABLE
--MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES';
--SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES';
--撤销 DEFAULT 约束
--MySQL:
ALTER TABLE Persons ALTER City DROP DEFAULT;
--SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--索引，用来加速搜索、查询，更新一个包含索引的表需要比更新一个没有索引的表更多的时间，
--因为索引本身也需要更新。理想的做法是仅在常常被搜索的列上创建索引。
--在表上创建一个唯一的索引.唯一的索引意味着两个行不能拥有相同的索引值
CREATE UNIQUE INDEX index_name ON table_name (column_name);
--假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex ON Person (LastName, FirstName);
--如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex ON Person (LastName DESC);
--DROP INDEX
--SQL Server
DROP INDEX table_name.index_name;
--DB2/Oracle
DROP INDEX index_name;
--MySQL
ALTER TABLE table_name DROP INDEX index_name;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--删除表（表的结构、属性以及索引也会被删除）
DROP TABLE table_name;
DROP DATABASE database_name;
--除去表内的数据，但并不删除表本身
TRUNCATE TABLE table_name;
--DELETE与TRUNCATE的区别
1。delete from后面可以写条件，truncate不可以
2。delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete from更新日志，truncate基本不，所用的事务日志空间较少
3。delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页
4。当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
5。如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始
6。truncate不会触发delete的触发器，因为truncate操作不记录各个行删除
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
ALTER TABLE Persons ADD Birthday date;
ALTER TABLE Person DROP COLUMN Birthday;
ALTER TABLE Persons ALTER COLUMN Birthday year;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务,默认地，AUTO_INCREMENT 的开始值是 1，每条新纪录递增 1。
CREATE TABLE Persons(
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)
);
--要让 AUTO_INCREMENT 序列以其他的值起始：
ALTER TABLE Persons AUTO_INCREMENT=100
--SQL Server MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务,默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。
--要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)
CREATE TABLE Persons(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
--Oracle，通过sequence创建auto-increment字段
--创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。
CREATE SEQUENCE seq_person
MINVALUE 1 START WITH 1
INCREMENT BY 1
CACHE 10;
--要在 "Persons" 表中插入新纪录，使用 nextval 函数（该函数从 seq_person 序列中取回下一个值）
INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (seq_person.nextval,'Lars','Monsen');
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
--创建视图
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No;
CREATE VIEW [Products Above Average Price] AS SELECT ProductName,UnitPrice FROM Products WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products);
CREATE VIEW [Category Sales For 1997] AS SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales FROM [Product Sales for 1997] GROUP BY CategoryName;
--查询视图
SELECT * FROM [Current Product List];
--更新视图
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName,Category FROM Products WHERE Discontinued=No;
--DROP视图
DROP VIEW view_name;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。
可用IS NULL 和 IS NOT NULL 操作符
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL;
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL;
--如果 "UnitsOnOrder" 是 NULL，则不利于计算，因此如果值是 NULL 则 ISNULL() 返回 0。
--SQL Server
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products;
--Oracle
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products;
--MySQL
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products;
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products;
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
-------------------------------------------~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~---------------------------------------------
--GROUP BY
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;
SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000;
