SQL系列 - 创建数据库与数据表

1. 创建数据库

#

• CREATE DATABASE 用于创建数据库。

  CREATE DATABASE database_name
  CREATE DATABASE my_db
  

  -- 创建数据库之前判断是否已经存在
  CREATE DATABASE IF NOT EXISTS my_db;
  

2. 创建数据表

#

• CREATE TABLE 语句用于创建数据库中的表。

  CREATE TABLE 表名称
  (
  列名称1 数据类型,
  列名称2 数据类型,
  列名称3 数据类型,
  ....
  )
  

  -- 创建数据表之前，判断是否已存在
  CREATE TABLE IF NOT EXISTS 表名称
  (
  列名称1 数据类型,
  列名称2 数据类型,
  列名称3 数据类型,
  ....
  )
  

  -- 实例
  CREATE TABLE Persons
  (
  Id_P int,
  LastName varchar(255),
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
  )
  

3. Constraints 约束

#

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

3.1. NOT NULL

#

• NOT NULL 约束强制列不接受 NULL 值。

• 这意味着，如果不向该字段添加值，就无法插入新记录或者更新记录。

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

3.2. UNIQUE

#

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

• UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

• PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

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

  -- 在 "Persons" 表创建时在 "Id_P" 列创建 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)
  )
  

  /*
  命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束。
  Id_P和LastName的组合必须唯一
  */
  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)
  )
  

  -- 当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束
  ALTER TABLE Persons
  ADD UNIQUE (Id_P)
  

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

  -- 撤销 UNIQUE 约束
  ALTER TABLE Persons
  DROP INDEX uc_PersonID
  

3.3. PRIMARY KEY

#

• PRIMARY KEY 约束是唯一标识数据库表中的每条记录。主键必须是唯一的值。

• 主键列不能包含 NULL 值。

• 每个表都应该有一个主键，并且每个表只能有一个主键。

• 如果使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）。

  --  在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：
  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)
  )
  

  /* 
  命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束
  这是一个复合主键，由两个列Id_P和LastName组成，
  这意味着这两个列的组合在表中必须唯一，且不能为空。
  */
  CREATE TABLE Persons
  (
  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)
  )
  

  -- 当表已被创建时，如需在 "Id_P" 列创建 PRIMARY KEY 约束
  ALTER TABLE Persons
  ADD PRIMARY KEY (Id_P)
  

  -- 命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束
  ALTER TABLE Persons
  ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
  

  -- 撤销 PRIMARY KEY 约束
  ALTER TABLE Persons
  DROP PRIMARY KEY
  

3.4. FOREIGN KEY

#

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

  -- 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY
  CREATE TABLE Orders
  (
  Id_O int NOT NULL,
  OrderNo int NOT NULL,
  Id_P int,
  PRIMARY KEY (Id_O),
  FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
  )
  

  -- 命名 FOREIGN KEY 约束，为外键约束指定了一个名称
  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)
  )
  

  -- 在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束
  ALTER TABLE Orders
  ADD FOREIGN KEY (Id_P)
  REFERENCES Persons(Id_P)
  

  -- 命名 FOREIGN KEY 约束，为外键约束指定了一个名称
  ALTER TABLE Orders
  ADD CONSTRAINT fk_PerOrders
  FOREIGN KEY (Id_P)
  REFERENCES Persons(Id_P)
  

  -- 撤销 FOREIGN KEY 约束
  ALTER TABLE Orders
  DROP FOREIGN KEY fk_PerOrders
  

3.5. CHECK

#

• CHECK 约束用于限制列中的值的范围。

• 如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

• 如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

  /*
  在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。
  CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。
  */
  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)
  )
  

  -- 命名 CHECK 约束，以及为多个列定义 CHECK 约束
  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')
  )
  

  -- 在表已存在的情况下为 "Id_P" 列创建 CHECK 约束
  ALTER TABLE Persons
  ADD CHECK (Id_P>0)
  

  -- 命名 CHECK 约束，以及为多个列定义 CHECK 约束
  ALTER TABLE Persons
  ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
  

  -- 撤销 CHECK 约束
  ALTER TABLE Persons
  DROP CHECK chk_Person
  

3.6. DEFAULT

#

• DEFAULT 约束用于向列中插入默认值。

• 如果没有规定其他的值，那么会将默认值添加到所有的新记录。

  --  在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束
  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()
  )
  

  -- 表已存在的情况下为 "City" 列创建 DEFAULT 约束
  ALTER TABLE Persons
  ALTER City SET DEFAULT 'SANDNES'
  

  -- 撤销 DEFAULT 约束
  ALTER TABLE Persons
  ALTER City DROP DEFAULT
  

4. 数据类型

#

4.1. DATE 函数

#

• 处理日期时，最难确保的是插入的日期的格式与数据库中日期列的格式相匹配
• 只要数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间，情况就有点复杂了。
• MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• 如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分

  -- MySQL Date 函数
  
  NOW()               -- 返回当前的日期和时间
  CURDATE()           -- 返回当前的日期
  CURTIME()           -- 返回当前的时间
  DATE()              -- 提取日期或日期/时间表达式的日期部分
  EXTRACT()           -- 返回日期/时间按的单独部分
  DATE_ADD()          -- 给日期添加指定的时间间隔
  DATE_SUB()          -- 从日期减去指定的时间间隔
  DATEDIFF()          -- 返回两个日期之间的天数
  DATE_FORMAT()       -- 用不同的格式显示日期/时间
  
  SELECT * FROM Orders WHERE OrderDate='2008-12-26'
  

4.2. NULL 值

#

• NULL 值是遗漏的未知数据。

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

• 如果表中的某个列是可选的，可以在不向该列添加值的情况下插入新记录。该字段将以 NULL 值保存。

• NULL 用作未知的或不适用的值的占位符。

• 无法比较 NULL 和 0；它们是不等价的。

• 无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>

• 必须使用 IS NULL 和 IS NOT NULL 操作符。

  SELECT LastName,FirstName,Address FROM PersonsWHERE Address IS NULL
  
  SELECT LastName,FirstName,Address FROM PersonsWHERE Address IS NOT NULL
  
  -- MySQL 里的 IFNULL 用法
  SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
  FROM Products
  
  -- 或者
  SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
  FROM Products
  

4.3. Text 类型

#

4.4. Number 类型

#

• 有符号整数：有符号整数包括正数、负数和零。
• 无符号整数：无符号整数仅表示非负数（包括零）。
• 在MySQL中，默认是有符号的，但你可以使用UNSIGNED关键字来声明无符号。

4.5. Date 类型

#