SQL高级语句（二）

电玩女神 2022-05-12 13:20 150阅读 0赞

**SELECT TOP子句：**

SELECT TOP子句用于指定要返回的记录数量，在包含千条记录的大型表上很有用。

并不是所有的数据库系统都支持SELECT TOP子句。MYSQL支持LIMIT子句来选择有限数量的记录，而Oracle使用ROWNUM。

**SQL server语法：**

SELECT TOP number\\percent column\_name(s)

FROM table\_name

WHERE condition

选择满足条件condition的table\_name表中的一定数量的column\_name(s)列；

MYSQL语法：

SELECT column\_name(s)

FROM table\_name

WHERE condition

LIMIT number

选择满足条件condition的table\_name表中的一定数量的column\_name(s)列；

**Oracle语法：**

SELECT column\_name(s)

FROM table\_name

WHERE condition AND ROWNUN<=number

选择满足条件condition的table\_name表中的一定数量的column\_name(s)列；

**SQL LIKE运算符：**

LIKE操作符用于在WHERE子句中搜索列中指定的模式；

有两个通配符与LIKE一起使用：

% -百分号表示零个、一个或多个字符；

\_ -下划线表示单个字符；

MS ACCESS 使用问号（？）而不是下划线（\_)；

百分号可以和下划线组合使用。

SELECT column1，column2...

FROM table\_name

WHERE columnN LIKE pattern；

选择table\_name的columnN列中匹配pattern模式的column1，column2...

‘%’和‘\_’通配符显示不同LIKE运算符的例子：

<table> 
 <tbody> 
 <tr> 
 <td> LIKE运算符 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE 'a%' </td> 
 <td> 查找以‘a’开头的任何值 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE '%a' </td> 
 <td> 查找以‘a’结尾的任何值 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE '%or%' </td> 
 <td> 在任何位置查找具有“or”字符的任何值 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE '_r%" </td> 
 <td> 查找第二个字符是‘r’的任何值 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE ‘a_%_%’ </td> 
 <td> 查找以‘a’开头且字符串至少是三个字符的值 </td> 
 </tr> 
 <tr> 
 <td> WHERE CustomerName LIKE ‘a%o’ </td> 
 <td> 查找以‘a’开头‘o’结尾的任何值 </td> 
 </tr> 
 </tbody> 
</table>

**SQL通配符：**

通配符用于替换字符串中的其他任何字符，如LIKE语句中提到的“%”和“\_”。

在MS Access和SQL Server中，也可以使用：

\[charlist\] -定义要匹配的字符的集合和范围；

\[^charlist\]或\[!charlist\] -定义不匹配字符的集合和范围。

如：

SELECT \* FROM Customers

WHERE City LIKE '\[bsp'\]%';

选择所有City以“b”，“s”或“p”开头的客户；

**IN运算符：**

IN运算符允许在WHERE子句中指定多个值；是多个OR条件的简写；

SELECT column\_name(s)

FROM table\_name

WHERE column\_name IN（value1，value2...);

或者：

SECECT column\_name(s）

FROM  table\_name

WHERE column\_name IN (SELECT statement)

** BETWEEN运算符：**

BETWEEN运算符选择给定范围内的值，值可以是数字、文本或日期；

SELCET column\_name(s)

FROM table\_name

WHERE column\_name BETWEEN value1 AND value 2;

从table\_name处选择column\_name(s)哪里的column\_name的值范围在value1到value2之间；

显示示例范围外的值用NOT BETWEEN;

在不同的数据库中，BETWEEN操作符包含的范围不同，如一些数据库中包含value1和value2；一些数据库选择两个数据之间的值但不包括value1和value2；一些数据库只包括前者，即value1但不包括value2。因此，使用时需检测BETWEEN操作符的包含范围；

**SQL通用数据类型：**

<table> 
 <tbody> 
 <tr> 
 <td> 数据类型 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> CHARACTER（n） </td> 
 <td> 字符/字符串。固定长度n </td> 
 </tr> 
 <tr> 
 <td> VARCHAR（n）或CHARACTER VARYING（n） </td> 
 <td> 字符/字符串。可变长度，最大长度为n </td> 
 </tr> 
 <tr> 
 <td> BINARY（n） </td> 
 <td> 二进制串。固定长度n </td> 
 </tr> 
 <tr> 
 <td> BOOLEAN </td> 
 <td> 存储TRUE或FALSE值 </td> 
 </tr> 
 <tr> 
 <td> VARBINARY（n）或BINARY VARING（n） </td> 
 <td> 二进制串。可变长度，最大长度为n </td> 
 </tr> 
 <tr> 
 <td> INTEGER（p） </td> 
 <td> 整数值（没有小数点）。精度p </td> 
 </tr> 
 <tr> 
 <td> SMALLINT </td> 
 <td> 整数值（没有小数点）。精度5 </td> 
 </tr> 
 <tr> 
 <td> INTEGER </td> 
 <td> 整数值（没有小数点）。精度10 </td> 
 </tr> 
 <tr> 
 <td> &nbsp;BIGINT </td> 
 <td> 整数值（没有小数点）。精度19&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;DECIMAL（p，s） </td> 
 <td> 精确数值，精度p，小数点后位数s。&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;NUMERIC（p，s） </td> 
 <td> 同DECIMAL（p，s）&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;FLOAT（p） </td> 
 <td> 近似数值。尾数精度p&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;REAL </td> 
 <td> 近似数值。尾数精度7&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;FLOAT </td> 
 <td> 近似数值。尾数精度16&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;DOUBLE PRECISION </td> 
 <td> 近似数值。尾数精度16&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;DATE </td> 
 <td> 存储年月日的值&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;TIME </td> 
 <td> 存储小时，分，秒的值&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;TIMESTAMP </td> 
 <td> 存储年月日，小时，分，秒的值&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;INTERVAL </td> 
 <td> 由一些整数数段组成，代表一段时间，取决于区间的类型&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;ARRAY </td> 
 <td> 元素的固定长度的有序集合&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;MULTISET </td> 
 <td> 元素的可变长度的无序集合&nbsp; </td> 
 </tr> 
 <tr> 
 <td> &nbsp;XML </td> 
 <td> 存储XML数据&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

不同数据库平台某些数据类型的通用数据名称：

<table> 
 <tbody> 
 <tr> 
 <td> 数据类型 </td> 
 <td> Access </td> 
 <td> SQLServer </td> 
 <td> Oracle </td> 
 <td> MySQL </td> 
 <td> PostgreSQL </td> 
 </tr> 
 <tr> 
 <td> boolean </td> 
 <td> Yes/No </td> 
 <td> Bit </td> 
 <td> Byte </td> 
 <td> N/A </td> 
 <td> Boolean </td> 
 </tr> 
 <tr> 
 <td> integer </td> 
 <td> Number（integer） </td> 
 <td> Int </td> 
 <td> Number </td> 
 <td> Int（Integer） </td> 
 <td> Int（Integer) </td> 
 </tr> 
 <tr> 
 <td> float </td> 
 <td> Number（single） </td> 
 <td> Float（Real） </td> 
 <td> Number </td> 
 <td> Float </td> 
 <td> Numeric </td> 
 </tr> 
 <tr> 
 <td> currency </td> 
 <td> Currency </td> 
 <td> Money </td> 
 <td> N/A </td> 
 <td> N/A </td> 
 <td> Money </td> 
 </tr> 
 <tr> 
 <td> string（fixed） </td> 
 <td> N/A </td> 
 <td> Char </td> 
 <td> Char </td> 
 <td> Char </td> 
 <td> Char </td> 
 </tr> 
 <tr> 
 <td> string（variable） </td> 
 <td> Text（&lt;256） Memo（65k+） </td> 
 <td> Varchar </td> 
 <td> Varchar Varchar2 </td> 
 <td> Varchar </td> 
 <td> Varchar </td> 
 </tr> 
 <tr> 
 <td> binary object </td> 
 <td> OLE Object Memo </td> 
 <td> Binary（fixed up to 8k） Varbinary（&lt;8k） Image（&lt;2GB) </td> 
 <td> Long Raw </td> 
 <td> Blob Text </td> 
 <td> Binary Varbinary </td> 
 </tr> 
 </tbody> 
</table>

**SQL连接语句：**

JOIN用于把两个或多个表的行结合起来，基于这些表中的共同字段；

**JOIN类型：**

INNER JOIN：如果表中有至少一个匹配，则返回行；

LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行；

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有行；

FULL JOIN：只要其中一个表存在匹配，则返回行；

**INNER JOIN语法：**

SELECT column\_name(s)

FROM table1

INNER JOIN table2 ON table1.column\_name = table2.column\_name;

选择table1和table2中所有满足table1.column\_name = table2.column\_name的column\_name(s)列的所有记录；

如果表中至少有一个匹配项，INNER JOIN关键字将会列出满足条件的所有行，如果不匹配，则不会列出行；

**LEFT JOIN语法：**

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；

SQL左连接LEFT JOIN关键字返回左表（table1）中所有行，即使在右表（table2）中没有匹配；当右表中无对应的匹配值时，对应的位置输出NULL。

**SQL 右连接：**

RIGHT JION返回右表table2的所有行，即使左表table1中没有匹配；当左表中无对应的匹配值时，对应的位置输出为NULL；

SELECT column\_name(s)

FROM table\_name

RIGHT JOIN table2

ON table1.column\_name = table2.column\_name

SQL  FULL OUTER JOIN关键字（完整外部连接）

当左表table1和右表table2表记录匹配时，FULL OUTER JOIN关键字将会返回所有记录；

SELECT column\_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column\_name = table2.column\_name;

** SQL自连接：**

SELECT column\_name(s)

FROM table1 T1，table1 T2

WHERE condition；

示例：

以下SQL语句匹配来自同一城市的客户：

SELECT A.CostomerName,B.CostomerName,A.City

FROM Customer A,Customer B

WHERE A.CustomerID <>B.CustomerID

AND A.City = B.City

ORDER BY A.City;

**SQL UNION运算符：**

UNION运算符用于组合两个或更多SELECT语句的结果集；

UNION中每个SELECT语句必须具有相同的列数，这些列也必须具有相似的数据类型，每个SELECT语句中的列也必须以相同的顺序排列；

SELECT column\_name(s) FROM table1

UNION

SELECT column\_name(s) FROM table2;

默认情况下，UNION运算符选择一个不同的值，如果需要允许重复值，则需要使用UNOIN ALL；

**SQL UNION ALL 语法：**

SELECT column\_name(s) FROM table1

UNION ALL

SELECT column\_name(s) FROM table2;

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

**SELECT INTO语句：**

从一个表中复制数据，然后将数据插入到另一个新表中；

SELECT column\_name(s)

INTO newtable \[IN externaldb\]

FROM table1;

从table1中复制column\_name(s)列到新表newtable中，IN externaldb表示复制到另一个数据库中，不写表示复制与本数据库目录下；

将使用SELECT语句中定义的列名和类型创建新表，可以使用AS语句来定义一个新名称；

INSERT INTO SELECT语句：

从表中复制数据，并将数据插入到现有的表中；目标表中的任何现有行都不会受到影响；

INSERT INTO table2

（column\_name(s))

SELECT column\_name(s)

FROM table1；

撤销索引，表以及数据库：

DROP INDEX用于删除表中的索引；

不同数据库语法稍有差异：

<table> 
 <tbody> 
 <tr> 
 <td> MS Access </td> 
 <td> DROP INDEX index_name ON table_name </td> 
 </tr> 
 <tr> 
 <td> MS SQL Server </td> 
 <td> DROP INDEX table_name.index_name </td> 
 </tr> 
 <tr> 
 <td> DB2/Oracle </td> 
 <td> DROP INDEX index_name </td> 
 </tr> 
 <tr> 
 <td> MySQL </td> 
 <td> ALTER TABLE table_name DROP INDEX index_name </td> 
 </tr> 
 </tbody> 
</table>

**DROP TABLE语句：**

DROP TABLE语句用于删除表；

DROP TABLE table\_name

DROP DATABASE：

DROP DATABASE用于删除数据库；

DROP DATABASE database\_name

TRUNCATE TABLE：

删除表中的数据，而非表本身；

TRUNCATE TABLE table\_name;

CREAT DATABASE语句：

CREAT DATABASE dbname；

创建数据库dbname；

CREAT TABLE语句：

CREAT TABEL table\_name

(

column1\_name data\_type(size),

column2\_name data\_type(size),

...

);

**ALTER TABLE语句：**

ALTER TABLE用于向现有表中添加、删除或修改行；

添加列：

ALTER TABLE table\_name

ADD column\_name datatype；

删除列：（一些数据库中不允许用该方法删除列）

ALTER TABLE table\_name

DROP COLUMN column\_name;

更改表中的数据类型：

SQL Server/Ms Access：

ALTER TABLE table\_name

ALTER COLUMN column\_name datatype；

MySQL/Oracle：

ALTER TBALE table\_name

MODIFY COLUMN column\_name datatype；

AUTO INCREMENT 字段：

以下SQL语句将“Person”表中的“ID”字段定义为自动递增主键字段：

**MySQL：**

CREAT TABLE Person

(

ID int NOT NULL AUTO\_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMAR KEY (ID)

);

默认情况下，AUTO\_INCREMENT的起始值为1，每个记录增加1；

若要以其他值开始AUTO\_INCREMENT序列，需对其赋初值，如：

ALTER TABLE Person AUTO\_INCREMENT = 100

SQL Server为“Person”中“ID”字段设置自动递增语法：

CREAT TABLE

(

ID int IDENTITY（1，1） PRIMARY KEY，

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

）；

SQL Server 中使用IDENTITY关键字执行自动增量；上面示例设置初始值为1，增量为1；若要设置初始值为10，增量为2，则改为IDENTITY（10，2）；

Access设置为“person”表中的“ID”字段自增语法：

CREAT TABLE Persons

（

ID Integer PRIMARY KEY AUTOINCREMENT，

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

）；

Access中使用AUTOINCREMENT完成自增，默认情况下初始值为1，每次自动增加1；若要设置初始值为10，每次增量为2，则改为AUTOINCREMENT（10，2）；

**Oracle语法：**

CREAT SEQUENCE seq\_person

MINVALUE 1

START WITH 1

INCREMENT BY 1

CACHE 10

上面的代码创建了一个名为“seq\_person”的序列（sequence）对象，它以1开头，以1递增，次对象缓存10个值以提高性能；

若要向“Person”表中插入新记录，需用nextval函数，该函数从序列中检索下一个值：

INSERT INTO Persons（ID，FirstName，LastName）

Value(seq\_person.nextval,'lars','Monsen')

**SQL视图：**

在SQL中，视图是基于SQL语句的结果集的可视化表；视图包括行和列，就像真正的表一样；视图中的字段是一个或多个数据库中真实表中的字段；视图中总是显示最新数据，每当用户查询视图时，数据库引擎就用视图的SQL语句重新构建数据；

CREAT VIEW语法：

CREAT VIEW view\_name AS

SELECT column\_name(s)

FROM table\_name

WHERE condition；

**更新视图：**

CREAT OR REPLACE VIEW 语法：

CREAT OR REPLACE VIEW view\_name AS

SELECT column\_name(s)

FROM table\_name

WHERE condition

**撤销视图：**

DROP VIEW view\_name

SQL Date函数：

**MySQL中内置日期函数：**

<table> 
 <tbody> 
 <tr> 
 <td> 函数 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> NOW（） </td> 
 <td> 返回当前的时间和日期 </td> 
 </tr> 
 <tr> 
 <td> CURDATE（） </td> 
 <td> 返回当前的日期 </td> 
 </tr> 
 <tr> 
 <td> CURTIME（） </td> 
 <td> 返回当前的时间 </td> 
 </tr> 
 <tr> 
 <td> DATE（） </td> 
 <td> 提取日期或日期/时间表达式中的日期 </td> 
 </tr> 
 <tr> 
 <td> EXTRACT（） </td> 
 <td> 返回日期/时间的单独部分 </td> 
 </tr> 
 <tr> 
 <td> DATE_ADD() </td> 
 <td> 向日期中添加指定的时间间隔 </td> 
 </tr> 
 <tr> 
 <td> DATE_SUB() </td> 
 <td> 从日期减去指定的时间间隔 </td> 
 </tr> 
 <tr> 
 <td> DATEDIFF </td> 
 <td> 返回两个日期间的天数 </td> 
 </tr> 
 <tr> 
 <td> DATE_FORMAT() </td> 
 <td> 用不同的格式显示日期/时间 </td> 
 </tr> 
 </tbody> 
</table>

**SQL Server Date函数：**

<table> 
 <tbody> 
 <tr> 
 <td> 函数 </td> 
 <td> 描述 </td> 
 </tr> 
 <tr> 
 <td> GETDATE（） </td> 
 <td> 返回当前的时间和日期 </td> 
 </tr> 
 <tr> 
 <td> DATEPART（） </td> 
 <td> 返回日期/时间单独的部分 </td> 
 </tr> 
 <tr> 
 <td> DATEADD（） </td> 
 <td> 在日期中添加或减去指定的时间间隔 </td> 
 </tr> 
 <tr> 
 <td> DATEDIFF（） </td> 
 <td> 返回两个日期间的时间 </td> 
 </tr> 
 <tr> 
 <td> CONVERT（） </td> 
 <td> 用不同的格式显示日期/时间 </td> 
 </tr> 
 </tbody> 
</table>

**SQL Date数据类型：**

MySQL：

DATE -格式：YYYY-MM-DD

DATETIME -格式：YYYY-MM-DD HH：MM：SS

TIMESTAMP -格式：YYYY-MM-DD HH：MM：SS

YEAR -格式：YYYY或YY

SQL Server:

DATE -格式：YYYY-MM-DD

DATETIME -格式：YYYY-MM-DD HH：MM：SS

SMALLDATETIME -格式：YYYY-MM-DD HH：MM：SS

TIMESTAMP -格式：唯一的数字

为了查询更加简单和易于维护，尽量不要使用日期中的时间部分；