SQL

1 SQL 简介

SQL 是什么？
Structured Query Language，结构化查询语言
SQL 让您可以访问和处理数据库

SQL 能做什么？
SQL 面向数据库执行查询、取回数据、插入新的记录、更新数据库中的数据、从数据库删除记录、创建新数据库、创建新表、创建存储过程、创建视图
存在着多种不同版本的 SQL 语言，为了与 ANSI 标准（American National Standards Institute 美国国家标准化组织）相兼容，它们必须以相似的方式共同地来支持一些主要的命令（比如 SELECT、UPDATE、DELETE、INSERT、WHERE 等等）。

在您的网站中使用 SQL
要创建一个显示数据库中数据的网站，您需要：
RDBMS （关系型数据库管理系统，全称 Relational Database Management System）数据库程序（比如 MS Access、SQL Server、MySQL）
使用服务器端脚本语言，比如 PHP 或 ASP
使用 SQL 来获取您想要的数据
使用 HTML / CSS

2 SQL 语法

数据库表

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

3 SELECT 语句

SELECT Column语法

语法：SELECT column_name,column_name _FROM _table_name ; 从表里提取某列数据

+——+———————+—————————————-+———-+————-+
| id | name | url | alexa | country |
+——+———————+—————————————-+———-+————-+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+——+———————+—————————————-+———-+————-+

实例

从 “Websites” 表中选取 “name” 和 “country” 列：

SELECT name,country FROM Websites;

SELECT * 语法

实例

从 “Websites” 表中选取所有列：

SELECT * FROM Websites;

4 SELECT DISTINCT 语句

DISTINCT 关键词用于返回唯一不同的值。仅从 “Websites” 表的 “country” 列中选取唯一不同的值，也就是去掉 “country” 列重复值：

SELECT DISTINCT country FROM Websites;

5 WHERE 语法

用于提取那些满足指定条件的记录

SELECT column_name,column_name _FROM _table_name _WHERE _column_name operator value;

实例

从 “Websites” 表中选取国家为 “CN” 的所有网站：
SELECT * FROM Websites WHERE country=’CN’;

文本字段 vs. 数值字段

SQL 使用单引号来环绕文本值
如果是数值字段，请不要使用引号。

实例

SELECT * FROM Websites WHERE id=1;

WHERE 子句中的运算符

下面的运算符可以在 WHERE 子句中使用：

搜索 empno 等于 7900 的数据：
Select from emp where empno=7900;
Where +条件（筛选行）
条件：列，比较运算符，值
比较运算符包涵：= > < >= ,<=, !=,<> 表示（不等于）
Select from emp where ename=’SMITH’;
例子中的 SMITH 用单引号引起来，表示是字符串，字符串要区分大小写。
逻辑运算
And:与 同时满足两个条件的值。
Select from emp where sal > 2000 and sal < 3000;
查询 EMP 表中 SAL 列中大于 2000 小于 3000 的值。
Or:或 满足其中一个条件的值
Select from emp where sal > 2000 or comm > 500;
查询 emp 表中 SAL 大于 2000 或 COMM 大于500的值。
Not:非 满足不包含该条件的值。
select from emp where not sal > 1500;
查询EMP表中 sal 小于等于 1500 的值。
逻辑运算的优先级：
() not and or
特殊条件
1.空值判断： is null
Select from emp where comm is null;
查询 emp 表中 comm 列中的空值。
2.between and (在 之间的值)
Select from emp where sal between 1500 and 3000;
查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。
注意：大于等于 1500 且小于等于 3000， 1500 为下限，3000 为上限，下限在前，上限在后，查询的范围包涵有上下限的值。
3.In
Select from emp where sal in (5000,3000,1500);
查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。
4.like
Like模糊查询
Select * from emp where ename like ‘M%’;
查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
% 表示多个字值， 下划线表示一个字符；
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M : 表示查询以M在倒数第二位的所有内容。

6 AND & OR 运算符

实例

选取国家为 “CN” 且alexa排名大于 “50” 的所有网站：

select from website where alexa > 50 and country = CN



选取 alexa 排名大于 “15” 且国家为 “CN” 或 “USA” 的所有网站：

select from website where alexa > 15 and (country = CN or Country = USA)

7 ORDER BY 关键字

对结果集按照一个列或者多个列进行排序

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

默认升序排列，使用 DESC 关键字按照降序排列

order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A ,B desc 这个时候 A 升序，B 降序排列

实例
选取所有网站，并按照 “alexa” 列排序：
select * from website order by alexa ASC

实例
从 “Websites” 表中选取所有网站，并按照 “country” 和 “alexa” 列排序：
select * from website order by country , alexa

8 INSERT INTO （） VALUE （）语句

①无需指定要插入数据的列名

INSERT INTO table_name
VALUES (value1,value2 ,value3 ,…); 这种情况需要列出插入行的每一列数据
INSERT INTO table_name
VALUES (value1,value2,value3,…);

②需要指定列名及被插入的值

INSERT INTO table_name (column1,column2 ,column3 ,…)
VALUES (value1,value2 , value3 ,…)

实例

向 “Websites” 表中插入一个新行
N：insert into websites 6,sa,thhps://www.douban.com,10,CN
Y：NSERT INTO Websites (name, url, alexa, country)
VALUES (‘百度’,’https://www.baidu.com/‘,’4’,’CN’);


id 列是自动更新的，表中的每条记录都有一个唯一的数字

9 UPDATE 语句

UPDATE table_name
SET column1=value1,column2 =value2 ,…
WHERE some_column =some_value ;

注意

set sql_safe_updates=1 自带的参数，当该参数开启的情况下，必须在update 语句后携带 where 条件，否则就会报错。这样可以防止将所有行的数据都修改

实例

把 “菜鸟教程” 的 alexa 排名更新为 5000，country 改为 USA。
N: UPDATE websites set alexa = 5000, country = USA where name = “菜鸟教程”
Y: UPDATE Websites SET alexa=’5000’, country=’USA’ WHERE name=’菜鸟教程’;

10 DELETE from语句

DELETE FROM table_name
WHERE some_column =some_value ;

删除网站名为 “百度” 且国家为 CN 的网站 。
Y:delete from websites where name = ‘ 百度 ‘ and country = ‘CN’
Y: DELETE FROM Websites WHERE name=’百度’ AND country=’CN’;


删除所有数据
您可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变：
DELETE FROM table_name;

或

DELETE * FROM table_name;

注意

delete：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复；
drop：删除表内容和结构，释放空间，没有备份表之前要慎用；
truncate：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用；

11SELECT LIMIT, TOP, ROWNUM 子句

SELECT column_name(s)
FROM table_name
LIMIT number;

实例

从 “Websites” 表中选取头两条记录
N:select from websites top 2
Y: SELECT FROM Websites LIMIT 2;

12 LIKE 操作符

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

实例

选取 name 以字母 “G” 开始的所有客户：
N: Select from websites name like ‘G%’
Y: SELECT FROM Websites WHERE name LIKE ‘G%’;


name 不包含模式 “oo” 的所有客户：
Y:select from websites where name not like ‘%oo%’;
Y:SELECT FROM Websites WHERE name NOT LIKE ‘%oo%’;

注意

‘%a’ //以a结尾的数据
‘a%’ //以a开头的数据
‘%a%’ //含有a的数据
‘a’ //三位且中间字母是a的
‘a’ //两位且结尾字母是a的
‘a‘ //两位且开头字母是a的

13 通配符

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

实例
url 以字母 “https” 开始的所有网站：
N: select from websites where like ‘https%’
Y: SELECT FROM Websites WHERE url LIKE ‘https%’;

实例
选取 name 以一个任意字符开始，然后是 “oogle” 的所有客户：
select * from websites where name like ‘_oogle’

14 IN 操作符

in 与 = 的转换
select from Websites where name in (‘Google’,’菜鸟教程’);
可以转换成 = 的表达：
select from Websites where name=’Google’ or name=’菜鸟教程’;

实例
选取 name 为 “Google” 或 “菜鸟教程” 的所有网站：
N:select from websites where name in ‘Google,菜鸟教程’
Y: SELECT FROM Websites WHERE name IN (‘Google’,’菜鸟教程’);
Y: select * from websites where name = ‘google’ or name = ‘菜鸟教程’

15 BETWEEN 操作符

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值
SELECT column_name(s)
FROM table_name
WHERE column_name _BETWEEN _value1 AND value2;

实例
选取 alexa 介于 1 和 20 之间的所有网站
N:select from websites where alexa between (1,20)
Y:SELECT FROM Websites WHERE alexa BETWEEN 1 AND 20;

16 SQL 别名 *

列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;
表的 SQL 别名语法
SELECT column_name(s)
FROM table_name _AS _alias_name;
_

17 SQL 连接(JOIN)

select websites.id,websites.name,websites.url,access_log.count,access_log.date from websites INNER JOIN access_log on websites.id = access_log.site_id

18 INNER JOIN 关键字

SQL INNER 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;

实例
所有网站的访问记录
select websites.id,websites.name,access_log.count,access_log.date from access_log JOIN websites on websites.id=access_log.site_id

19 LEFT JOIN 关键字

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

20 UNION 操作符

合并两个或多个 SELECT 语句的结果集
SQL UNION 语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

实例
“Websites” 和 “Apps” 表中选取所有不同的country(出现过的、不重复的)
select country from websites union select country from apps
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;

21 约束（Constraints）

用于规定表中的数据规则
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

22 NOT NULL 约束

约束强制列不接受 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;

23 UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

24 PRIMARY KEY 约束

主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
CREATE TABLE 时的 SQL PRIMARY KEY 约束
下面的 SQL 在 “Persons” 表创建时在 “P_Id” 列上创建 PRIMARY KEY 约束：
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)
)

25 SQL 函数

• SQL Aggregate 函数
• SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。
• 有用的 Aggregate 函数：
• AVG() - 返回平均值
• COUNT() - 返回行数
• FIRST() - 返回第一个记录的值
• LAST() - 返回最后一个记录的值
• MAX() - 返回最大值
• MIN() - 返回最小值
• SUM() - 返回总和
• SQL Scalar 函数
• SQL Scalar 函数基于输入值，返回一个单一的值。
• UCASE() - 将某个字段转换为大写
• LCASE() - 将某个字段转换为小写
• MID() - 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) - 从某个文本字段提取字符
• LEN() - 返回某个文本字段的长度
• ROUND() - 对某个数值字段进行指定小数位数的四舍五入
• NOW() - 返回当前的系统日期和时间
• FORMAT() - 格式化某个字段的显示方式

26 AVG() 函数

AVG() 语法

SELECT AVG(column_name) FROM table_name

实例

从 “access_log” 表的 “count” 列获取平均值：
select avg(count) from access_log

实例

选择访问量高于平均访问量的 “site_id” 和 “count”
N: select site_id,COUNT from access_log where count > (avg(COUNT) from access_log)
Y: select site_id,COUNT from access_log where count > (select avg(COUNT) from access_log)

27 COUNT() 函数

返回匹配指定条件的行数(计数)
SQL COUNT(column_name) 语法
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：
SELECT COUNT(column_name) FROM table_name;
SQL COUNT() 语法
COUNT() 函数返回表中的记录数：
SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
SELECT COUNT(DISTINCT column_name) FROM table_name;

实例

— 查询所有记录的条数
select count(*) from access_log;

— 查询websites 表中 alexa列中不为空的记录的条数
select count(alexa) from websites;

— 查询websites表中 country列中不重复的记录条数
select count(distinct country) from websites;