SQL语句学习记录(三)

四.SQL高级教程

1.SQL SELECT TOP 子句

SELECT TOP 子句用于规定要返回的记录的数目

SELECT TOP 子句对于拥有数千条记录的大型表来说，是非常有用的

(1)SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;

输出前50%的页面

SELECT TOP 50 PERCENT * FROM Websites;

(2)MySQL 语法

SELECT column_name(s)
FROM table_name
LIMIT number;

样例

select * from websites
limit 2

(3)Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

样例

SELECT * FROM Persons
WHERE ROWNUM <=5;

2.SQL LIKE 操作符

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

(1)SQL LIKE 语法

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

(2)实例

①选取 name 以字母 "F" 开始的所有客户

select * from websites
where name like 'F%'

②选取 name 以字母 "e" 结尾的所有客户

SELECT * FROM Websites
WHERE name LIKE '%e';

③选取 name 包含模式 "ov" 的所有客户

SELECT * FROM Websites
WHERE name LIKE '%ov%';

3.SQL 通配符

通配符可用于替代字符串中的任何其他字符。

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

(1)SQL%通配符

实例：

选取 url 以字母 "https" 开始的所有网站

SELECT * FROM Websites
WHERE url LIKE 'https%';

(2)SQL_通配符

实例：

选取 name 以 "G" 开始，然后是一个任意字符，然后是 "o"，然后是一个任意字符，然后是 "le" 的所有网站

SELECT * FROM Websites
WHERE name LIKE 'G_o_le';

(3)SQL [charlist] 通配符

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

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

4.SQL IN 操作符

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

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

实例

选取 name 为 "Google" 或 "百度" 的所有网站

SELECT * FROM Websites
WHERE name IN ('Google','百度');

 

5.SQL BETWEEN 操作符

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

(1)语法

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

(2)实例

①between

查找不在范围内的信息，请使用 BETWEEN

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

②not between

查找不在范围内的信息，请使用 NOT BETWEEN

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

③带条件的 BETWEEN

选取 alexa 介于 100 和 200 之间但 country 不为 USA 和 IND 的所有网站

SELECT * FROM Websites
WHERE (alexa BETWEEN 100 AND 200)
AND country NOT IN ('USA', 'IND');

选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站

SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H'

 

6.SQL 别名

(1)列的别名

SELECT column_name AS alias_name
FROM table_name;

实例

SELECT name AS n, country AS c
FROM Websites;

(2)表的别名

SELECT column_name(s)
FROM table_name AS alias_name;

7.SQL 连接(JOIN)

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法

(1)样例

inner join

select emp_info.employee_id,employees.manager_id,emp_info.employee_name from emp_info
inner join employees
on emp_info.employee_id=employees.employee_id;

 

(2)不同的SQL JOIN

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

 

8.SQL INNER JOIN 关键字

INNER JOIN 关键字在表中存在至少一个匹配时返回行

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

或者省略ininer

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

9.SQL LEFT JOIN 关键字

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

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

实例

select emp_info.employee_name,employees.manager_id
from emp_info
left join employees
on emp_info.employee_id=employees.employee_id
order by emp_info.employee_id desc;

emp_info为左表，employees为右表

右表没有匹配记录也会返回值，左表所有值都显示，对应右表中没有匹配的内容则右表元素栏中显示null

10.SQL RIGHT JOIN 关键字

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

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

select emp_info.employee_name,employees.manager_id
from emp_info
right join employees
on emp_info.employee_id=employees.employee_id
order by emp_info.employee_id asc;

11.SQL FULL OUTER JOIN 关键字

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

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

在Mysql中用full join

12.SQL UNION 操作符

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

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

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

SELECT country FROM Websites
UNION
SELECT employee_name FROM emp_info
ORDER BY country;

UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值

SELECT country FROM Websites
UNION all
SELECT employee_name FROM emp_info
ORDER BY country;

SELECT country,url FROM Websites
where country='CN'
UNION all
SELECT employee_name,employee_id FROM emp_info
ORDER BY country;