SQL笔记

SQL 基础教程：

SELECt与SELECT DISTINCT：SELECT 语句用于从数据库中选取数据；DISTINCT 关键词用于返回唯一不同的值。用法均为：

SELECT DISTINCT 列名称 FROM 表名称.

WHERe：WHERe语句有条件地从表中选取数据；用法：

SELECt 列名称 FROM 表名称 WHERe 列 运算符 值。如：SELECt * FROM "FIRST" WHERe age > 20

AND & OR：AND 和 OR 可在 WHERe 子语句中把两个或多个条件结合起来。如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。用法：

SELECt * FROM Persons WHERe FirstName='Thomas' AND LastName='Carter'

​

ORDER BY：ORDER BY 语句默认按照升序对记录进行排序。按照降序对记录进行排序，可以使用ORDER BY DESC 关键字。用法：

SELECt Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

INSERT INTO：INSERT INTO 语句用于向表中插入新记录。第一种用法：

INSERT INTO 表名称 VALUES (值1, 值2,....)*

第二种用法：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

UPDATe：UPDATE 语句用于更新表中已存在的记录。用法：

UPDATE 表名称 SET 列名称 = 新值 WHERe 列名称 = 某值

DELETE：DELETE 语句用于删除表中的行。用法：

DELETE FROM 表名称 WHERe 列名称 = 值

SQL 高级教程：

SELECt TOP：SELECT TOP 子句用于规定要返回的记录的数目。用法：（从 “Websites” 表中选取头两条记录）

SELECT * FROM Websites LIMIT 2

LIKE：LIKE 操作符用于在 WHERe 子句中搜索列中的指定模式。用法：

①选取 name 以字母 “G” 开始的所有客户：

SELECt * FROM Websites WHERe name LIKE 'G%'

②选取 name 以字母 “k” 结尾的所有客户：

SELECt * FROM Websites WHERe name LIKE '%k'

③ 选取 name 包含模式 “oo” 的所有客户：

SELECt * FROM Websites WHERe name LIKE '%oo%'

IN：IN 操作符允许在 WHERe 子句中规定多个值。用法：(选取 name 为 “Google” 或 “菜鸟教程” 的所有信息)

SELECt * FROM Websites WHERe name IN ('Google','菜鸟教程')

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

①(选取 alexa 介于 1 和 20 之间的信息)、

SELECt * FROM Websites
WHERe alexa BETWEEN 1 AND 20

②（选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站）

SELECt * FROM Websites
WHERe (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND')

③（选取 name 以介于 ‘A’ 和 ‘H’ 之间字母开始的所有网站）

SELECt * FROM Websites
WHERe name BETWEEN 'A' AND 'H'

④（选取 date 介于 ‘2016-05-10’ 和 ‘2016-05-14’ 之间的所有访问记录）

SELECt * FROM access_log
WHERe date BETWEEN '2016-05-10' AND '2016-05-14'

JOIN：JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。用法：（“Websites” 表中的 “id” 列指向 “access_log” 表中的字段 “site_id”。上面这两个表是通过 “site_id” 列联系起来的）。

SELECt Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id;

UNIOn：UNIOn 操作符用于合并两个或多个 SELECt 语句的结果集。注意：UNIOn 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。用法：

①UNIOn 语法：（从 “Websites” 和 “apps” 表中选取所有不同的country）

SELECT country FROM Websites UNIOn SELECt country FROM apps ORDER BY country;

②UNIOn ALL语句：（从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值））

SELECt country FROM Websites UNIOn ALL SELECt country FROM apps ORDER BY country

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

①复制所有的列插入到新表中

SELECT * INTO newtable FROM table1

②复制希望的列插入到新表中

SELECt column_name(s) INTO newtable FROM table1

CREATE DATABASE：CREATE DATABASE 语句用于创建数据库。用法：

CREATE DATABASE my_db

CREATE TABLE：CREATE TABLE 语句用于创建数据库中的表。用法：创建一个名为 “Persons” 的表，包含五列：PersonID、LastName、FirstName、Address 和 City。我们使用下面的 CREATE TABLE 语句

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

PersonID 列的数据类型是 int，包含整数。LastName、FirstName、Address 和 City 列的数据类型是 varchar，包含字符，且这些字段的最大长度为 255 个字符。

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

①下面的 SQL 强制 “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

UNIQUE约束：唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。用法：

①(在 “Persons” 表创建时在 “P_Id” 列上创建 UNIQUE 约束)；

CREATE TABLE Persons
(
Id_P int NOT NULL,
UNIQUE (Id_P)
)

②如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

PRIMARY KEY约束：主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束， 那么这些列都不允许为空值，并且组合的值不允许重复。 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。用法：

①在 “Persons” 表创建时在 “Id_P” 列创建 PRIMARY KEY 约束：

CREATE TABLE Persons
(
Id_P int NOT NULL,
PRIMARY KEY (Id_P)
)

②命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)

CHECK 约束：CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制。用法：

①在 “Persons” 表创建时在 “P_Id” 列上创建 CHECK 约束。CHECK 约束规定 “P_Id” 列必须只包含大于 0 的整数。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CHECK (P_Id>0)
)

②命名 CHECK 约束，并定义多个列的 CHECK 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

DEFAULT 约束：DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。用法：在 “Persons” 表创建时在 “City” 列上创建 DEFAULT 约束。

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
)

DROp 语句：通过使用 DROP 语句，可以轻松地删除索引、表和数据库。用法：

①DROP INDEX 语句用于删除表中的索引：

DROP INDEX table_name.index_name

②DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：

DROP TABLE 表名称

③DROP DATABASE 语句用于删除数据库：

DROP DATABASE 数据库名称

④仅仅删除表内的数据，但并不删除表本身:

TRUNCATE TABLE table_name

ALTER TABLE语句：ALTER TABLE 语句用于在已有的表中添加、删除或修改列。用法：

①在表中添加（删除）列

ALTER TABLE table_name
ADD（DROP） column_name datatype

②改变表中列的数据类型

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

AUTO INCREMENT语句：在每次插入新记录时，自动地创建主键字段的值。把 “Persons” 表中的 “ID” 列定义为 auto-increment 主键：

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
)

**提示：**IDENTITY 的开始值是 1，每条新记录递增 1。,要规定 “ID” 列以 10 起始且递增 5，请把 identity 改为 IDENTITY(10,5)。

视图(VIEWS)：视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。可以向视图添加 SQL 函数、WHERe 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。用法：

CREATE VIEW 视图名称 AS
SELECT 列名称
FROM 表名称
WHERe 选取条件

查询视图：

SELECt * FROM 视图名称

Date 函数：下面的表格列出了 MySQL 中最重要的内建日期函数：

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

SQL 函数：

AVG() 函数：AVG() 函数返回数值列的平均值。用法：

从 “access_log” 表的 “count” 列获取平均值：

SELECt AVG(count) AS CountAverage FROM access_log

选择访问量高于平均访问量的 “site_id” 和 “count”：

SELECt site_id, count FROM access_log
WHERe count > (SELECt AVG(count) FROM access_log);

COUNT() 函数：返回匹配指定条件的行数。用法：

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECt COUNT(*) FROM table_name

COUNT(*) 函数返回表中的记录数：

SELECt COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECt COUNT(DISTINCT column_name) FROM table_name

FIRST()函数 LAST()函数：返回指定的列中第一个（最后一个）记录的值。用法：

SELECt column_name FROM table_name
ORDER BY column_name ASC(DESC)
LIMIT 1;

MAX()函数 MIN()函数：函数返回指定列的最大（小）值。用法：

SELECt MAX(alexa) AS max_alexa FROM Websites;
SELECt MIN(alexa) AS max_alexa FROM Websites;

SUM()函数：返回数值列的总数。用法：

SELECt SUM(count) AS nums FROM access_log;

GROUP BY语句：可结合一些聚合函数来使用。用法：

①统计 access_log 各个 site_id 的访问量：

SELECt site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

② 统计有记录的网站的记录数量

SELECt Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

HAVINg子句：在 SQL 中增加 HAVINg 子句原因是，WHERe 关键字无法与聚合函数一起使用。HAVINg 子句可以让我们筛选分组后的各组数据。用法：

想要查找总访问量大于 200 的网站。

SELECt Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVINg SUM(access_log.count) > 200;

EXISTS 运算符：运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。用法：

查找总访问量(count 字段)大于 200 的网站是否存在。

SELECt Websites.name, Websites.url 
FROM Websites 
WHERe EXISTS (SELECt count FROM access_log WHERe Websites.id = access_log.site_id AND count > 200);

EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录。

UCASE()函数 LCASE()函数：把字段的值转换为大（小）写。用法：

SELECt UCASE(column_name) FROM table_name;
SELECt LCASE(column_name) FROM table_name;

MId()函数：用于从文本字段中提取字符。用法：

SELECt MID(column_name,start[,length]) FROM table_name;

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MId() 函数返回剩余文本。

从 “Websites” 表的 “name” 列中提取前 4 个字符：

SELECt MId(name,1,4) AS ShortTitle
FROM Websites;

LENGTH()函数：函数返回文本字段中值的长度。用法：

SELECt LENGTH(column_name) FROM table_name;

ROUND()函数：用于把数值字段舍入为指定的小数位数。用法：

ROUND(X)：返回参数X的四舍五入的一个整数。

SELECt ROUND(column_name) FROM table_name;

ROUND(X,D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

SELECt ROUND(column_name,decimals) FROM table_name;

NOW()函数：返回当前系统的日期和时间。用法：

SELECt NOW() FROM table_name;

FORMAT()函数：用于对字段的显示进行格式化。用法：

SELECt FORMAT(column_name,format) FROM table_name;

从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

SELECt name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;