SQL-基础02

发表于 更新于 分类于 本文字数： 9.3k 阅读时长 ≈ 8 分钟
主要是介绍了SQL的进阶知识。

SQL进阶

TOP 子句

用于规定要返回的记录的数目，对于拥有数千条记录的大型表来说，TOP子句是非常有用的。注意：并非所有的数据库系统都支持 TOP 子句

1
select TOP Number|percent 字段名(s) from 表名；

LIKE 子句

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

1
select 字段名(s) from 表名 where 字段名 like "%a"；

通配符

在搜索数据库中的数据时，SQL 通配符可以替代一个或者多个字符，必须与 LIKE 运算符一起使用。

1
2
3
4
5
-- % 匹配多个字符
select * from 表名 where 字段名 like "%a"；

-- _ 匹配一个字符
select * from 表名 where 字段名 like "_a"；

IN 操作符

​ 允许我们在 where 子句中规定多个值。

1
select 字段名 from 表名 where 字段名 in (Value1，value2，...)；

BETWEEN 操作符

​ 用在where 子句中，作用是选取介于两个值之间的数据范围

1
select * from 表名 where 字段名 between Value1 and Value2；

ALIAS 别名

1
2
3
select * from 表名 as 表别名；
-- 等价于
select * from 表名  表别名；

JOIN 联表

​ 用于多个表中的字段名之间的关系，从这些表中查询数据

  • JOIN :如果表中有至少一个匹配，则返回行
  • LEFT JOIN : 内连接，即使右表中没有匹配，也从左表返回所有行
  • RIGHT JOIN : 右连接，即使左表中没有匹配，也从右表返回所有行
  • FULL JOIN : 只要其中一个表中存在匹配，就返回行
1
2
3
4
5
--联表方式1
select * from 表名1 t1，表名2 t2 where t1.字段名 = t2.字段名；

--联表方式2
select * from 表名1 t1 Inner join 表名2 t2 on t1.字段名 = t2.字段名；

UNION 和 UNION ALL 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。注意：查出来的字段数量相同，列的类型也相同，对应的顺序也需要相同。

1
2
3
4
5
-- UNION 默认不允许取重复的值
select 字段1 from 表1 UNION select 字段1 from 表2 ；

-- UNION ALL 允许取重复的值
select 字段1 from 表1 UNION ALL select 字段1 from 表2；

SELECT INTO

用于从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份复件或者用于对记录进行存档。

1
select * into 新表名 from 旧表名；

AUTO INCREMENT 字段

每次插入新纪录时，自动创建主键字段的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 在mysql 中的用法
CREATE TABLE Persons
(
P_Id int PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；


-------------------------------------
-- 在Oracle 中的用法
--先创建一个序列对象 sequence 
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10；
--面的代码创建名为 seq_person 的序列对象，它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。CACHE 选项规定了为了提高访问速度要存储多少个序列值。
--再插入数据
INSERT INTO Persons (P_Id,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')；


---------------------------------------
--在SQL Sever 的用法
CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；
--如果要修改起始位置，和每次递增的规律。使用IDENTITY(x,y),以 x 起始位置，y 递增。

CREATE VIEW 视图

在 SQL 中，是基于 SQL 语句的结果集的可视化的表。视图包括行和列，就像一个真实的表。视图中的字段来自真实表中的字段。可以向视图中添加 SQL 函数、 WHERE 以及 JOIN 语句，也可以提交数据。注意：数据库的设计和结构不会受视图中的函数、WHERE 以及 JOIN 语句的影响。

1
create view 视图名 as select 字段名 from 表名 where 条件；

注意：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

SQL 约束 Constraints

约束用于限制加入表的数据的类型。可以在创建表时规定约束（通过create table语句），或者在表创建之后也可以（通过 alter table 语句）。

主要的几种约束：

  • NOT NULL

  • UNIQUE

  • PRIMARY KEY

  • FOREIGN KEY

  • CHECK

  • DEFAULT

NOT NULL 非空约束

强制列不接受 NULL 值。强制该字段必须有值，无值的话不能添加或者更新记录。

1
2
3
4
5
6
7
8
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；

UNIQUE 唯一约束

UNIQUE 约束唯一标识数据库表中额每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或者集合提供唯一性的保证。其中 PRIMARY KEY 中拥有自动定义的 UNIQUE 约束。

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

创建表时添加 UNIQUE 约束：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--MySQL中，创建表时添加 UNIQUE 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)；


---------------------------------------------------------
--SQL Sever / Oracle / MS Access中创建表时 添加 UNIQUE 约束
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；

---------------------------------------------------------
--如果需要重命名 UNIQUE 约束 ，以及为多个列定义 UNIQUE 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)；

表已经创建，修改其中的 UNIQUE 约束：

1
2
3
4
5
6
-- 如果是单列的添加 UNIQUE 约束
ALTER TABLE Persons ADD UNIQUE (Id_P)；

-- 如果需要重命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)；

撤销 UNIQUE 约束

1
2
3
4
5
--MySQL中
alter table 表名 drop index 约束名；

--SQL Sever / Oracle / MS Access中
alter table 表名 drop constraint 约束名；

PRIMARY KEY 主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一值。主键不能包含NULL值。每个表都应该有一个主键，并且每个表只能有一个主键。

创建表时添加 PRIMARY KEY 约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- MySQL中
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)；

--SQL Sever / Oracle / MS Access 中
CREATE TABLE Persons
(
Id_P int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；

------------------------------------
--如果需要重命名 PRIMARY KEY 约束，以及为多个列定义PRIMARY KEY 约束
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)；

已存在的表的 PRIMARY KEY 约束

1
2
3
4
5
6
7
-- MySQL / SQL Sever / Oracle / MS Access 中添加 PRIMARY KEY
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

-- MySQL / SQL Sever / Oracle / MS Access 中重命名 PRIMARY KEY 约束，以及多个列定义 PRIMARY KEY 约束
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)；

注意：如果使用alter table 方式 添加主键，必须吧主键列声明为不包含 NULL 值 （在表首次创建时）。

撤销 PRIMARY KEY 约束

1
2
3
4
5
-- MySQL 中
alter table 表名 drop PRIMARY KEY;

-- SQL Sever / Oracle / MS Access 中
alter table 表名 drop constraint 主键名；

FOREIGN KEY 外键约束

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY 。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

创建表时添加 FOREIGN KEY 约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--MySQL中
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (另一个表的字段名) REFERENCES 另一个表名(字段名)
)；

--SQL Server / Oracle / MS Access 中
CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
字段名 int FOREIGN KEY REFERENCES 另一个表名(字段名)
)；
------------------------------------
--如果需要重命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束
--MySQL / SQL Server / Oracle / MS Access 中
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)；

已经存在表的 FOREIGN KEY 约束

1
2
3
4
5
6
7
8
9
10
11
--如果是单列添加 FOREIGN KEY 
-- MySQL / SQL Server / Oracle / MS Access 中
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)；

--如果需要重命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，MySQL / SQL Server / Oracle / MS Access 中
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)；

撤销 FOREIGN KEY 约束

1
2
3
4
5
-- MySQL中
alter table 表名 drop FOREIGN KEY 外键名；

-- SQL Server / Oracle / MS Access 中
alter table 表名 drop constraint 外键名；

CHECK 检查约束

CHECK 约束用于限制列中的值的范围。只允许这个列特定的值。

创建表时添加 CHECK 约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- MySQL 中
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)；

--SQL Server / Oracle / MS Access 中
CREATE TABLE Persons
(
Id_P int NOT NULL CHECK (Id_P>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)；

-----------------------------------
--如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束
--MySQL / SQL Server / Oracle / MS Access 中
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)；

已经存在表时的 CHECK 约束

1
2
3
4
5
6
7
-- 添加 CHECK 约束
-- MySQL / SQL Server / Oracle / MS Access 中
alter table 表名 add check (字段条件);

--如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，MySQL / SQL Server / Oracle / MS Access 中 
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')；

撤销 CHECK 约束

1
2
3
4
5
-- SQL Sever / Oracle / MS Access 
alter table 表名 drop constraint check约束名；

-- MySQL 中
alter table 表名 drop CHECK check约束名；

DEFAULT 默认约束

DEFAULT 约束用于向列中插入默认值。如果没有指定其他的值，那么会默认值添加到所有的新纪录中

创建表时添加 DEFAULT 约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--MySQL / SQL Sever / Oracle / MS Access 中
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)；

-- 通过使用类似getdate() 这样的函数，DEFAULT 约束也可以用于插入系统值
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)；

已经存在表时的 CHECK 约束

1
2
3
4
5
-- MySQL 中
alter table 表名 alter 字段名 set DEFAULT ‘默认值’；

--SQL Server / Oracle / MS Access 中
ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT '默认值'；

撤销 DEFAULT 约束

1
2
3
4
5
-- MySQL 中
alter table 表名 alter 字段名 drop DEFAULT;

--SQL Server / Oracle / MS Access 中
alter table 表名 alter column 字段名 drop DEFAULT;

Date 函数

SQL 日期

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。只要数据包含的知识日期部分，运行查询就不会出现问题。但是，如果涉及时间，情况就有点复杂了。

最重要的是内建日期处理函数。

MySQL Date 函数

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

SQL Sever Date 函数

函数 描述
GETDATE () 返回当前日期和时间
DATEPART () 返回日期/时间的单独部分
DATEADD () 在日期中添加或减去指定的时间间隔
DATEDIFF () 返回两个日期之间的时间
CONVERT () 用不同的格式显示日期/时间

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 Sever 使用下列数据类型再数据库中存储日期或日期/时间值：

  • DATE - 格式 YYYY-MM-DD
  • DATETIME - 格式 YYYY-MM-DD HH:MM:SS
  • SMALLDATETIME - 格式 YYYY-MM-DD HH:MM:SS
  • TIMESTAMP - 格式：唯一的数字

SQL 日期处理

注意：尽量不要使用时间部分，这样的话查询更容易，且维护起来也更容易。

SQL NULL 值

IS NULL 与 IS NOT NULL

NULL 值是遗漏的位置数据。默认地，表的列可以存放 NULL 值。

使用 IS NULL 得到列中的值是为NULL ，不能使用运算符：

1
select * from 表名 where 字段名 IS NULL;

使用 IS NOT NULL 得到列中的值不为 NULL ：

1
select * from 表名 where 字段名 IS NOT NULL;

SQL ISNULL () 等函数

​ SQL ISNULL() —微软的、NVL() —-Oracle的 、IFNULL() 、COALESCE () 这两个是MySQL的 函数。

计算字段为空处的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--SQL Server / MS Access中 
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products

-- Oracle 中没有ISNULL() 函数。是用NVL()
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products

--MySQL中，用IFNULL();
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
----或者
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

SQL 服务器 –RDBMS

现代的 SQL 服务器构建在 RDBMS 之上。

DBMS - 数据库管理系统（Database Management System）

数据库管理系统是一种可以访问数据库中数据的计算机程序。

DBMS 使我们有能力在数据库中提取、修改或者存贮信息。

不同的 DBMS 提供不同的函数供查询、提交以及修改数据。

RDBMS - 关系数据库管理系统（Relational Database Management System）

关系数据库管理系统 (RDBMS) 也是一种数据库管理系统，其数据库是根据数据间的关系来组织和访问数据的。

20 世纪 70 年代初，IBM 公司发明了 RDBMS。

RDBMS 是 SQL 的基础，也是所有现代数据库系统诸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基础。