SQL语句之高级使用

1、select top

select top 用于规定要返回的数据的数目

注意:并非所有的数据库系统都支持 SELECT TOP 语句。 MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

SqlServer语法：

1	`SELECT` `TOP` `50 PERCENT *` `FROM` `Websites;`

Oracle语法：

SELECT *
FROM Persons
WHERE ROWNUM <=5;

MySQL语法：

SELECT *
FROM Persons
LIMIT 5;

2、like

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

"%" 符号用于在模式的前后定义通配符（默认字母）

3、SQL通配符

1 2	`1、通配符可用于替代字符串中的任何其他字符。` `2、在 SQL 中，通配符与 SQL` `LIKE` `操作符一起使用`

例：

选取 name 以 "G"、"F" 或 "s" 开始的所有网站：（ MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。）

1 2	`SELECT` `*` `FROM` `Websites` `WHERE` `name` `REGEXP` `'^[GFs]';`

4、in

IN 操作符允许您在 WHERE 子句中规定多个值。

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

例：

1 2	`SELECT` `*` `FROM` `Websites` `WHERE` `name` `IN` `('Google','菜鸟教程');`

in和=对比：

相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义。

不同点：IN可以规定多个值，等于规定一个值。

IN
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);<br>
=
SELECT column_name(s)
FROM table_name
WHERE column_name=value1;

5、between

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

如需显示不在上面实例范围内的网站，使用 NOT BETWEEN：

1 2	`SELECT` `*` `FROM` `Websites` `WHERE` `alexa` `NOT` `BETWEEN` `1` `AND` `20;`

6、SQL别名

语法：

1 2	`SELECT` `column_name` `AS` `alias_name` `FROM` `table_me;`

例：

SELECT name AS n, country AS c
FROM Websites;
 
 
把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名：
 
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

表的SQL别名语法：

1 2	`SELECT` `column_name(s)` `FROM` `table_name` `AS` `alias_name;`

例：

SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";

7、join

作用：SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

（1）inner join（也可以缩写成join）——内联接

求交集，如果表中有至少一个匹配，则返回行

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

（2）left join——左联接

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

（3）right join——右联接

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;　

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

（4）full join（也叫作full outer join）——全联接

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;　　

8、union

作用： UNION 操作符合并两个或多个 SELECT 语句的结果。

注意：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

union语法：

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

union all语法：

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
 
注意：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

9、select into

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

　　　　注意：

　　　　MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT

select into语法：

SELECT *
INTO newtable [IN externaldb]
FROM table1;
 
或：
 
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

下面的语句可以复制表结构和表数据：

CREATE TABLE 新表 AS SELECT * FROM 旧表;
或：
CREATE TABLE 新表 SELECT * FROM 旧表;

10、insert into select

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

语法：

INSERT INTO table2
SELECT * FROM table1;<br><br>----------------------------------
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

扩展：

1. 复制表结构及其数据：

1	`create` `table` `table_name_new` `as` `select` `*` `from` `table_name_old`

2. 只复制表结构：

create table table_name_new as select * from table_name_old where 1=2;
或者：
create table table_name_new like table_name_old

3. 只复制表数据：

如果两个表结构一样：
insert into table_name_new select * from table_name_old
如果两个表结构不一样：
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

11、创建数据库语句

1	`CREATE` `DATABASE` `dbname;`

12、创建表语句

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

13、SQL约束

SQL 约束用于规定表中的数据规则。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

（1） not null

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

例子：

强制 "ID" 列、 "LastName" 列以及 "FirstName" 列不接受 NULL 值：
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
 
在一个已创建的表的 "Age" 字段中添加 NOT NULL 约束如下所示：
ALTER TABLE Persons MODIFY Age int NOT NULL;
 
在一个已创建的表的 "Age" 字段中删除 NOT NULL 约束如下所示：
ALTER TABLE Persons MODIFY Age int NULL;

（2）unique

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

在 "Persons" 表创建时在 "P_Id" 列上创建 UNIQUE 约束：
MySQL语法：
 
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
 
 
 
SQL Server / Oracle / MS Access语法：
 
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，使用下面的 SQL 语法：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

当表已被创建时，如需在 "P_Id" 列创建 UNIQUE 约束，使用下面的 SQL：
MySQL / SQL Server / Oracle / MS Access：
 
ALTER TABLE Persons
ADD UNIQUE (P_Id);
 
如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
 
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

如需撤销 UNIQUE 约束，使用下面的 SQL：
MySQL：
 
ALTER TABLE Persons
DROP INDEX uc_PersonID
 
 
SQL Server / Oracle / MS Access：
 
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

（3）primary key

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE 时的 SQL PRIMARY KEY 约束:<br>
MySQL：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
 
SQL Server / Oracle / MS Access：
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)<br>
如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

ALTER TABLE 时的 SQL PRIMARY KEY 约束
 
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
 
-------------------------------------------------------------------------------------
如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法：
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

撤销 PRIMARY KEY 约束
 
MySQL：
ALTER TABLE Persons
DROP PRIMARY KEY
 
SQL Server / Oracle / MS Access：
 
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

（4）foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

（5）check约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。

（6）default约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

14、创建索引

create index

CREATE INDEX 语句用于在表中创建索引。

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

在表上创建一个简单的索引。允许使用重复的值：
 
CREATE INDEX index_name
ON table_name (column_name)

表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。Creates a unique index on a table. Duplicate values are not allowed:<br>
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

注释：用于创建索引的语法在不同的数据库中不一样。因此要检查数据库中创建索引的语法。

15、删除索引、表以及数据库

16、alter

17、auto increment字段

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

18、视图

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

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

（1）创建视图语法：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

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

（2）创建视图示例：

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
 
我们可以像这样查询上面这个视图：
 
SELECT * FROM [Current Product List]

（3）更新视图

语法：

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

向 "Current Product List" 视图添加 "Category" 列。将通过下列 SQL 更新视图：
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No