本文为学习网站资源的笔记

原资源地址

简介

SQL = Structured Query Language

SQL是一种 ANSI - American National Standards Institute 标准的语言

SQL的基础是 RDBMS - Relational Datbase Management System

RDBMS 中的数据存储在被称为表的数据库对象中

表是相关的数据项的集合，由列和行组成

SQL能做的事情

• 面向数据库执行查询

• 从数据库取回数据

• 在数据库中插入新记录

• 从数据库中删除记录

• 更新数据库中数据

• 创建新数据库

• 在数据库中创建新表

• 在数据库中创建存储过程

• 在数据库中创建视图

• 设置权限

注意语句最后的分号

常用SQL命令

Command	Meaning
SELECT	从数据库中提取数据
UPDATE	更新数据库中的数据
DELETE	从数据库中删除数据
INSERT INTO	像数据库中插入新数据
CREATE DATABASE	创建新数据库
ALTER DATABASE	修改数据库
CREATE TABLE	创建新表
ALTER TABLE	改变数据库中的表
DROP TABLE	删除表
CREATE INDEX	创建索引/搜索键
DROP INDEX	删除索引

SELECT 语句

简述

SELECT语句语句用于从数据库中选取数据；结果被存储在一个结果表中，称为结果集

基本语法

SELECT COLUMN, COLUMN FROM TABLE;

SELECT * FROM TABLE;

SELECT DISTINCT

在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同(distinct)的值; 这时，可以使用 DISTINCT 返回唯一不同的值

SELECT DINSTINCT COLUMN FROM TABLE;

SQL WHERE

WHERE子句用于过滤记录

SELECT COLUMN, COLUMN FROM TABLE WHERE CONDITION;

引号的问题

如果是文本字段用单引号；如果是数值字段不用引号

WHERE中的运算符

OPERATOR	DESCIPTION
=	等于
<>	不等于(maybe !=)
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

AND & OR 运算符

SELECT COLUMN, COLUMN FROM TABLE WHERE CONDITION1 AND CONDITION2;

SELECT COLUMN, COLUMN FROM TABLE WHERE CONDITION1 OR CONDITION2;

SQL ORDER BY

SELECT COLUMN, COLUMN FROM TABLE ORDER BY COLUMN, COLUMN ASC|DESC;

ASC 升序
DESC 降序

INSERT INTO 语句

用于向表中插入新记录

语法

INSERT INTO TABLE VALUES (V1, V2, V3...);

INSERT INTO TABLE (C1, C2, C3...) VALUES (V1, V2, V3...);

对于id列来说，数值是自动更新的，表中每条记录都拥有唯一的id

UPDATE 语句

用于更新表中的记录

语法

UPDATE TABLE SET COLUMN1=V1, COLUMN2=V2,... WHERE SOME_COLUMN = SOME_VALUE;

敲黑板！！！

上述UPDATE语句千万不能少了WHERE子句，
否则会更改表中某列所有的数据

DELETE 语句

用于删除表中的行

语法

DELETE FROM TABLE WHERE SOME_COLUMN = SOME_VALUE;

敲黑板X2！！！

这里也不要少了WHERE子句，不然你的记录都要被删掉啦

删除所有数据

DELETE FROM TABLE;

DELETE * FROM TABLE;

SELECT TOP, LIMIT, ROWNUM 子句

用于规定要返回的记录的数目

不是所有数据库系统都支持SELECT TOP，MySQl支持LIMIT语句来选取指定的数据条数，Oracle支持ROWNUM

MySQL 语法

SELECT COLUMN FROM TABLE LIMIT number;

SQL Server 语法

SELECT TOP 50 PERCENT * FROM TABLE;

LIKE 操作符

用于在 WHERE 子句中搜索列中的指定模式

语法

SELECT COLUMN(S) FROM TABLE WHERE SOME_COLUMN LIKE pattern;

关于模式中的通配符

通配符通过和 LIKE 操作符一起使用，用于搜索表中的数据

SYMBOL	MEANING
%	替代0个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] or [!charlist]	不再字符列中的任何单一字符

REGEXP or NOT REGEXP 运算符操纵正则表达式

RLIKE or NOT RLIKE 运算符操纵正则表达式

---

IN 操作符

用于在 WHERE 子句中规定多个值

语法

SELECT COLUMN(S) FROM TABLE WHERE SOME_COLUMN IN (V1, V2, V3...);

BETWEEN 操作符

用于选取介于两个值之间的数据范围内的值(number, text, date...)

语法

SELECT COLUMN(S) FROM TABLE WHERE SOME_COLUMN BETWEEN V1 AND V2;

NOT BETWEEN

用于选取不介于两个值之间的数据范围内的值

带 IN 的 BETWEEN 操作符

SELECT * FROM TABLE WHERE (COLUMN1 BETWEEN number1 AND number2) AND COLUMN2 NOT IN (.., ..)

带有文本值的 BETWEEN 操作符

SELECT * FROM TABLE WHERE COLUMN BETWEEN 'A' AND 'X';

SELECT * FROM TABLE WHERE COLUMN NOT BETWEEN 'B' AND 'D';

敲黑板X3！！！

在不同的DB中，BETWEEN操作符会产生不同的结果！
尽量在使用之前先查看你的DBS是如何处理BETWEEN的！

别名

可以为表名称or列名称指定别名，增强可读性

列别名语法

SELECT COLUMN AS ALIAS_NAME FROM TABLE;

表别名语法

SELECT COLUMN(S) FROM TABLE AS ALIAS_NAME;

使用别名的场景

• 查询过程超过一个表
• 在查询中使用了函数
• 列名称很长或可读性很差
• 需要把两个列或者多个列结合在一起

敲黑板！！！

JOIN 连接

用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

常见的JOIN类型：INNER JOIN (从多个表中返回满足JOIN条件的所有行)

JOIN 的类型

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

此处有图

INNER JOIN 关键字

表中存在至少一个匹配时返回行，如果没有匹配则不会返回结果

语法

SELECT COLUMN(S) FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

SELECT COLUMN(S) FROM TABLE1 JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

LEFT JOIN 关键字

从 左表/TABLE1 返回所有的行，即使 右表/TABLE2 中没有匹配；对于右表没有匹配的返回NULL

语法

SELECT COLUMN(S) FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

SELECT COLUMN(S) FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

RIGHT JOIN 关键字

从 右表/TABLE2 返回所有的行，即使 左表/TABLE1 中没有匹配；对于左表没有匹配的返回NULL

语法

SELECT COLUMN(S) FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

SELECT COLUMN(S) FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

FULL OUTER JOIN 关键字

左表/TABLE1 和 右表/TABLE2 其中一个表中存在匹配，则返回行

语法

SELECT COLUMN(S) FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.COLUMN1=TABLE2.COLUMN2;

敲黑板！！！MySQl中不支持 FULL OUTER JOIN

UNION 操作符

用于合并两个or多个 SELECT 语句的结果

使用 UNION 的要求

• 每个 SELECT 语句必须有相同数量的列
• 每个 SELECT 语句的列必须有相似的数据类型
• 每个 SELECT 语句中的列的顺序必须相同

语法

默认模式下，UNION选取不同的值；如果需要允许重复的值，请使用UNION ALL

SELECT COLUMN(S) FROM TABLE1
UNION
SELECT COLUMN(S) FROM TABLE2;

SELECT COLUMN(S) FROM TABLE1
UNION ALL
SELECT COLUMN(S) FROM TABLE2;

SELECT INTO 语句

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

语法

SELECT * INTO NEW_TABLE FROM TABLE1;

SELECT COLUMN(S) INTO NEW_TABLE FROM TABLE1;

敲黑板！！！MySQL不支持SELECT...INTO语句

但是支持 INSERT INTO ... SELECT

INSERT INTO SELECT 语句

从一个表复制数据，然后把数据插入到另一个已经存在的表中
目标表中任何已经存在的行都不会被影响

语法

INSERT INTO TABLE2 SELECT * FROM TABLE1;

INSERT INTO TABLE2 (COLUMN(S)) SELECT COLUMN(S) FROM TABLE1;

CREATE DATABASE 语句

语法

CREATE DATABASE DB_NAME;

CREATE TABLE 语句

CREATE TABLE TABLE_NAME(
COLUMN_NAME1 DATA_TYPE(SIZE),
COLUMN_NAME2 DATA_TYPE(SIZE),
...
);

待补的坑：MySQl中的数据类型

Constraints 约束

用于规定表中的数据规则
若存在违反约束的数据行为，行为会被约束终止

规定约束的时间

• 通过 CREATE TABLE; 在创建表时规定
• 通过 ALTER TABLE; 在表创建之后规定

语法

CREATE TABLE TABLE_NAME(
COLUMN1 DATA_TYPE(SIZE) CONSTRAINT_NAME,
COLUMN2 DATA_TYPE(SIZE) CONSTRAINT_NAME,
...
);

SQL中的约束概述

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

NOT NULL 约束

在默认的情况下，列接受NULL

NOT NULL 约束强制列不接受 NULL；约束强制字段始终包含值

UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录；
UNIQUE 和 PRIMARY KEY 约束均为列或列集提供唯一性的保障

每个表中可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束

注意！！！下面均是在MySQL环境下的操作！！！

CREATE TABLE 时

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)
);

多个列的UNIQUE约束：

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)
);

ALTER TABLE 时

ALTER TABLE TABLE_NAME ADD UNIQUE (COLUMN_NAME);

多个列的UNIQUE约束：

ALTER TABLE TABLE_NAME ADD CONSTRAINT NAME UNIQUE (COLUMN1, COLUMN2);

撤销 UNIQUE 约束

ALTER TABLE TABLE_NAME DROP INDEX U_NAME;

PRIMARY KEY 约束

约束的内容

• 主键必须包含唯一的值
• 主键不能是NULL
• 每个表都有且仅能有一个主键

注意！！！下面均是在MySQL环境下的操作！！！

CREATE TABLE 时

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)
);

多个列的PRIMARY KEY约束：

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)
);

只有一个主键 PRIMARY KEY（pk_PersonID）
然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的

ALTER TABLE 时

ALTER TABLE TABLE_NAME ADD PRIMARY KEY (COLUMN);

多个列的PRIMARY KEY约束：

ALTER TABLE TABLE_NAME ADD CONSTRAINT P_KEY PRIMARY KEY (COLUMN1, COLUMN2);

如果使用ALTER TABLE添加主键，在表首创时声明为NOT NULL

撤销 PRIMARY KEY 约束

ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

FOREIGN KEY 约束

一个表中的FOREIGN KEY指向另一个表中的UNIQUE KEY

CREATE TABLE

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
);

ALTER TABLE

ALTER TABLE TABLE_NAME ADD FOREIGN KEY (COLUMN1) REFERENCES TABLE2(COLUMN1);

ALTER TABLE TABLE_NAME ADD CONSTRAINT UK_NAME FOREIGN KEY (COLUMN1) REFERENCES TABLE2(COLUMN(S));

撤销 FOREIGN KEY 约束

ALTER TABLE TABLE_NAME DROP FOREIGN KEY FK_NAME;

CHECK 约束

用于限制列中的值的范围

限制的类型

• 对单个列定义CHECK，该列只允许特定的值
• 对一个表定义CHECK，此约束会基于行中其他列的值在特定的列中对值进行限制

CREATE TABLE 时

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

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

ALTER TABLE 时

ALTER TABLE TABLE_NAME ADD CHECK (CONDITION);

ALTER TABLE TABLE_nAME CONSTRAINT CHK_TABLE CHECK (CONDITION1 AND CONDITION2)

撤销 CHECK 约束

ALTER TABLE TABLE_NAME DROP CHECK CHK_TABLE;

DEFAULT 约束

用于向列中插入默认值；若此列没有规定其他值，那么会将默认值添加到所有新纪录

又双叒叕！！！敲黑板！！！本文所有操作均是在MySQL环境下的！！！

CREATE TABLE

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes',
    OrderDate date DEFAULT GETDATE()
);

ALTER TABLE

ALTER TABLE TABLE_NAME ALTER COLUMN1 SET DEFAULT '...';

撤销 DEFAULT 约束

ALTER TABLE TABLE_NAME ALTER COLUMN1 DROP DEFAULT;

CREATE INDEX 语句

• 用于在表中创建索引
• 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据
• 用户无法看到索引，只能被用来加速搜索/查询

更新一个有索引地表需要更多的时间；因为索引也需要更新；比较好地做法是仅仅在常被搜索地列/表中创建索引

语法

不同的数据库应用系统可能支持的INDEX语句不一样，使用之前请查阅相关文档

创建一个简单索引+允许使用重复的值

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1);

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1, COLUMN2)

创建一个唯一的索引+不允许使用重复的值

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUM1);

撤销操作

使用 DROP 可以删除*：索引、表和数据库

注意使用前查看自己的数据库系统支持的语句形式

ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;

DROP TABLE TABLE_NAME;

DROP DATABASE DB_NAME;

仅仅删除表中的数据，不删除表本身

TRUNCATE TABLE TABLE_NAME;

ALTER TABLE 语句

用于在已有的表中添加、删除或修改列

语法

ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE;

ALTER TABLE TABLE_NAME DROP COLUMN COLUM_NAME;

ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME DATA_TYPE;

AUTO INCREMENT 字段

• 用于在新纪录插入表中时生成一个唯一的数字
• 在表中插入新纪录时，不必为AUTO_INCREMENT列规定值

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
);

ALTER TABLE TABLE_NAME AUTO_INCREMENT=100;

Views 视图

视图就是可视化的表

创建视图

CREATE VIEW VIEW_NAME AS 
SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE CONDITION(S);

更新视图

CREATE OR REPLACE VIEW VIEW_NAME AS
SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE CONDITION(S);

撤销视图

DROP VIEW VIEW_NAME;

DATE 函数

如果可以尽量不要在数据库中使用时间，每个数据库系统支持不同的时间数据类型和不同的函数，维护起来比较麻烦

MySQL 中 DATE 数据类型

CONTENT	FORMAT
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYY-MM-DD HH:MM:SS
YEAR	YYYY or YY

MySQL DATE 函数

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

NULL 值

敲黑板！！！

NULL 和 0无法比较

IS NULL or IS NOT NULL

用其来判断列中带不带NULL的记录

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IS NULL;

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IS NOT NULL;

关于 NULL 的函数

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products;

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0)) FROM Products;

SQl 中的数据类型

已挖坑-待补