1、SQL TOP子句
TOP 50 PERCENT
* FROM Persons
2、SQL LIKE 操作符
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern举例:
3、SQL通配符
4、SQL IN 操作符
5、SQL BETWEEN 操作符
选取介于两个值之间的数据范围(有的包含边界,有的不包含)
6、SQL Alias(别名)
7、SQL JOIN
(1)引用两个表
我们可以通过引用两个表的方式,从两个表中获取数据:
谁订购了产品,并且他们订购了什么产品?
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P #(通过Id_P来将两个表连接在一起)
(2)使用Join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
补充:
- JOIN: 如果表中有至少一个匹配,则返
- INNER JOIN:内连接,只返回两表中连接字段相等的行
- LEFT JOIN: 返回左表中所有记录及其右表中连接字段相等的记录。(即使右表中没有匹配,也从左表返回所有的行)
- RIGHT JOIN: 返回右表中所有记录及其左表中连接字段相等的记录。即使左表中没有匹配,也从右表返回所有的行)
- FULL JOIN: 外连接,返回两表中的行。(只要其中一个表中存在匹配,就返回行)
8、SQL INNER JOIN 关键字
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
9、SQL LEFT JOIN 关键字
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
希望列出所有的人以及他们的订购-如果有的话。
10、SQL RIGHT JOIN 关键字
11、SQL FULL JOIN 关键字
12、SQL UNION 操作符
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
13、SQL SELECT INTO 语句
SELECT INTO 语句从一个表中选取数据(可以是所有列* 也可以是指定的列),然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
举例:SELECT
*
INTO
Persons_backup FROM Persons
SELECT
*
INTO
Persons
IN
'Backup.mdb' FROM Persons
14、SQL CREATE DATABASE 语句
15、SQL CREATE TABLE 语句
CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... )举例:
CREATE TABLE Persons ( Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )其中,
16、SQL 约束
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
17、SQL NOT NULL约束
下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值:
CREATE TABLE Persons ( Id_P intNOT NULL
, LastName varchar(255)NOT NULL
, FirstName varchar(255), Address varchar(255), City varchar(255) )
18、SQL UNIQUE 约束
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 / MS Access:
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons
ADD UNIQUE (Id_P)
如果需要命名UNIQUE约束,并定义多个列的UNIQUE约束:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
ALTER TABLE Persons
DROP INDEX uc_PersonID (MySQL)
DROP CONSTRAINT uc_PersonID (SQL)
19、SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键,并且每个表只能有一个主键。
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)
)
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons
DROP PRIMARY KEY (MYSQL)
DROP CONSTRAINT pk_PersonID (SQL)
20、SQL FOREIGN KEY约束
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
请注意,"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。
"Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY 主键。
"Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY 外键。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders (MySQL)
DROP CONSTRAINT fk_PerOrders (SQL)
21、SQL CHECK约束
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)
)
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')
)
ALTER TABLE Persons
ADD CHECK (Id_P>0) (MySQL)
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') (SQL)
撤销CHECK约束:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person (SQL)
DROP CHECK chk_Person (MYSQL)
22、SQL DEFAULT约束
CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' )如果表已存在的情况下为“city”创建DEFAULT约束
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' (MySQL)
ALTER COLUMN City SET DEFAULT 'SANDNES'(SQL)
ALTER TABLE Persons ALTER City DROP DEFAULT (Mysql)
ALTER CULUMN City DROP DEFAULT (sql)
23、SQL CREATE INDEX语句
CREATE INDEX index_name ON table_name (column_name)创建一个不重复的简单索引:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
举例:
CREATE INDEX PersonIndex ON Person (LastName DESC)
24、SQL DROP
25、SQL ALTER TABLE语句
ALTER TABLE table_name
ADD column_name datatype
删除列:
ALTER TABLE table_name
DROP COLUMN column_name
修改列的数据类型:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
举例:
26、SQL AUTO INCREMENT 字段
27、SQL VIEW(视图)
样本数据库 Northwind 拥有一些被默认安装的视图。视图 "Current Product List" 会从 Products 表列出所有正在使用的产品。这个视图使用下列 SQL 创建:
SQL更新视图:CREATE VIEW
[Current Product List]AS
SELECT
ProductID,ProductNameFROM
ProductsWHERE
Discontinued=No#正在使用的产品
SQL CREATE OR REPLACE VIEW Syntax CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE conditionSQL撤销视图:
您可以通过 DROP VIEW 命令来删除视图。
SQL DROP VIEW Syntax DROP VIEW view_name
28、SQL Date函数
SQL Server Date 函数
下面的表格列出了 SQL Server 中最重要的内建日期函数:
函数 | 描述 |
---|---|
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
SELECT * FROM Orders WHERE OrderDate='2008-12-26'
29、SQL NULL值
NULL 值是遗漏的未知数据。
默认地,表的列可以存放 NULL 值。如果向表中插入一条不带值的记录,该列会使用NULL值保存。使用IS NULL和IS NOT NULL操作符来区别。
选取“Address”列中带有NULL值的记录:
SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL
30、SQL ISNULL函数
如果 "UnitsOnOrder" 是 NULL,则不利于计算,因此如果值是 NULL 则 ISNULL() 返回 0。
SQL Server / MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0)) FROM Products
31、SQL 数据类型
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 | 存储结果集,供稍后处理。 |