晴明的博客园 GitHub      CodePen      CodeWars     

[db] sql base

SQL命令:

标准的SQL命令进行互动使用在关系型数据库有：CREATE, SELECT, INSERT, UPDATE, DELETE 和 DROP。这些命令可分为基于其性质组。

DDL - 数据定义语言：

命令 描述
CREATE 创建一个新的表，表的视图，或者在数据库中的对象
ALTER 修改现有的数据库对象，例如一个表
DROP 删除整个表，数据库中的表或其他对象或视图

DML - 数据操纵语言：

命令 描述
SELECT 从一个或多个表中检索特定的记录
INSERT 创建记录
UPDATE 修改记录
DELETE 删除记录

DCL - 数据控制语言：

命令 | 描述

  • | -
    GRANT | 授予用户权限
    REVOKE | 收回用户授予的权限

RDBMS

RDBMS代表关系数据库管理系统。 RDBMS是SQL的基础，对于像MS SQL Server，IBM DB2，Oracle，MySQL和Microsoft Access等所有现代数据库系统。

一个关系数据库管理系统(RDBMS)是一个数据库管理系统(DBMS)，基于关系模型如通过 E. F. Codd 引入。

table(表)

在RDBMS中的数据存储在数据库对象称为表。表是相关数据条目的集合，它由列和行的组成。
表是数据存储在关系数据库中最常见和最简单的形式。

CUSTOMERS表

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

field(字段)

每个表被分成较小的实体称为字段。
在CUSTOMERS表中的字段组成是：ID, NAME, AGE, ADDRESS 和 SALARY。

字段旨在维护有关表中的每个记录特定信息表中的列。

row(行)

一个记录，也被称为一行数据，是存在于一个表中的每个单独的条目。比如有7条记录在上面的CUSTOMERS表。
以下是在CUSTOMERS表中的数据或记录中的一行：

+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
+----+----------+-----+-----------+----------+

column(列)

列是包含在一个表中的特定字段相关联的所有信息表的垂直实体。

例如，在CUSTOMERS表中的一列是ADDRESS，它代表位置描述，并会包括以下内容：

+-----------+
| ADDRESS   |
+-----------+
| Ahmedabad |
| Delhi     |
| Kota      |
| Mumbai    |
| Bhopal    |
| MP        |
| Indore    |
+----+------+

数据完整性

数据的完整性有以下几类存在于每个RDBMS：

  • 实体完整性: 表中没有重复行
  • 域完整性: 通过限制的类型，格式或值的范围强制对于一个给定列的有效条目
  • 参考完整性: 行不能被删除，被其他记录使用
  • 用户定义的完整性: 强制执行不属于实体，域和参照完整性一些具体的业务规则

SQL 约束

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

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

SQL CREATE TABLE + CONSTRAINT

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

NOT NULL(非空)

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

创建一个新的表称为CUSTOMERS，并增加了五列，其中的三个： ID ， NAME 和 AGE,指定不接受NULL值

CREATE TABLE CUSTOMERS(
       ID   INT              NOT NULL,
       NAME VARCHAR (20)     NOT NULL,
       AGE  INT              NOT NULL,
       ADDRESS  CHAR (25) ,
       SALARY   DECIMAL (18, 2),       
       PRIMARY KEY (ID)
);

添加一个NOT NULL约束SALARY列在CUSTOMERS表

ALTER TABLE CUSTOMERS
   MODIFY SALARY  DECIMAL (18, 2) NOT NULL;

DEFAULT(默认值)

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

CREATE TABLE 时的 SQL DEFAULT 约束

在 "Persons" 表创建时在 "City" 列上创建 DEFAULT 约束：

//MySQL / SQL Server / Oracle / MS Access：

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

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

ALTER TABLE 时的 SQL DEFAULT 约束

在 "City" 列创建 DEFAULT 约束

//MySQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

//SQL Server / MS Access：

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

//Oracle：

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

撤销 DEFAULT 约束

//MySQL：
ALTER TABLE Persons
ALTER City DROP DEFAULT

//SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

UNIQUE(唯一)

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。
每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

CREATE TABLE 时的 SQL UNIQUE 约束

"Persons" 表创建时在 "P_Id" 列上创建 UNIQUE 约束：

//MySQL：

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

//SQL Server / Oracle / MS Access：

CREATE TABLE Persons(
    P_Id int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)

定义多个列的 UNIQUE 约束

//MySQL / SQL Server / Oracle / MS Access：

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 时的 SQL UNIQUE 约束

当表已被创建时，在 "P_Id" 列创建 UNIQUE 约束

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    ADD UNIQUE (P_Id)

定义多个列的 UNIQUE 约束

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

撤销 UNIQUE 约束

撤销 UNIQUE 约束

//MySQL：

ALTER TABLE Persons
    DROP INDEX uc_PersonID

//SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    DROP CONSTRAINT uc_PersonID

PRIMARY KEY(主键)

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

CREATE TABLE 时的 SQL PRIMARY KEY 约束

在 "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)
)

//SQL Server / Oracle / MS Access：

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

定义多个列的 PRIMARY KEY 约束。
只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的。被称为复合键。

//MySQL / SQL Server / Oracle / MS Access：

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

ALTER TABLE 时的 SQL PRIMARY KEY 约束

在 "P_Id" 列创建 PRIMARY KEY 约束

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    ADD PRIMARY KEY (P_Id)

定义多个列的 PRIMARY KEY 约束。
如果使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

撤销 PRIMARY KEY 约束

//MySQL：

ALTER TABLE Persons
    DROP PRIMARY KEY

//SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    DROP CONSTRAINT pk_PersonID

FOREIGN KEY(外键)

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

"Persons" 表：

P_Id | LastName| FirstName | Address | City

  • | - | - | - | -
    1 | Hansen | Ola | Timoteivn 10 | Sandnes
    2 | Svendson | Tove | Borgvn 23 | Sandnes
    3 | Pettersen | Kari | Storgt 20 | Stavanger

"Orders" 表：

O_Id | OrderNo | P_Id

  • | - | -
    1 | 77895 | 3
    2 | 44678 | 3
    3 | 22456 | 2
    4 | 24562 | 1

"Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列。
"Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY。
"Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY。

CREATE TABLE 时的 SQL FOREIGN KEY 约束

在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束

//MySQL：

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

//SQL Server / Oracle / MS Access：

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

定义多个列的 FOREIGN KEY 约束

//MySQL / SQL Server / Oracle / MS Access：

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 时的 SQL FOREIGN KEY 约束

在 "P_Id" 列创建 FOREIGN KEY 约束

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
    ADD FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)

定义多个列的 FOREIGN KEY 约束

//MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
    ADD CONSTRAINT fk_PerOrders
    FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)

撤销 FOREIGN KEY 约束

//MySQL：
ALTER TABLE Orders
    DROP FOREIGN KEY fk_PerOrders

//SQL Server / Oracle / MS Access：

ALTER TABLE Orders
    DROP CONSTRAINT fk_PerOrders

CHECK(检查)

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

CREATE TABLE 时的 SQL CHECK 约束

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

MySQL：

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

SQL Server / Oracle / MS Access：

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

定义多个列的 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

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 时的 SQL CHECK 约束

在 "P_Id" 列创建 CHECK 约束

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
    ADD CHECK (P_Id>0)

定义多个列的 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

撤销 CHECK 约束

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
    DROP CONSTRAINT chk_Person

MySQL：

ALTER TABLE Persons
    DROP CHECK chk_Person

INDEX(索引)

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。
因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

CREATE INDEX

在表上创建一个简单的索引。
允许使用重复的值：

CREATE INDEX index_name
    ON table_name (column_name)

CREATE UNIQUE INDEX

在表上创建一个唯一的索引。
不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX index_name
    ON table_name (column_name)

用于创建索引的语法在不同的数据库中不一样。

CREATE INDEX

在 "Persons" 表的 "LastName" 列上创建一个名为 "PIndex" 的索引：

CREATE INDEX PIndex
    ON Persons (LastName)

索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PIndex
    ON Persons (LastName, FirstName)

数据库规范化

数据库正常化是有效地组织数据库中的数据的过程。归一化处理的两个原因：

  • 消除冗余数据，例如，存储了一个以上的相同的数据在同一个表。
  • 确保数据的相关性意义。

这两者都是值得追求的目标，因为它们减少的空间的数据库消耗的量，并确保数据在逻辑上存储。规范化由一系列指导方针，帮助指导您创建一个良好的数据库结构。

标准化准则分为正常形态;认为形式的格式或数据库结构的布局方式。 正常形态的目的是组织数据库结构，使其符合第一范式，然后第二范式，最后第三范式的规则。

把它进一步去第四范式，第五范式等，但总体来讲，满足第三范式就够了。

第一范式（1NF）

符合1NF的关系中的每个属性都不可再分.

实际上，1NF是所有关系型数据库的最基本要求.
但字段是否真的不可拆分，根据你的设计目标而定。

第二范式（2NF）

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。

第三范式（3NF）

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

NULL

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

posted @ 2017-05-10 23:22  晴明桑  阅读(234)  评论(0)  编辑  收藏  举报