草庐IT

SQL进阶篇之约束(Constraints)

Davieyang 2023-03-28 原文

SQL 约束

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

SQL约束主要包括以下几种约束：

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT

SQL NOT NULL 约束

  • NOT NULL 约束强制列不接受 NULL 值
  • NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录
下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值：

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

SQL UNIQUE 约束

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

SQL UNIQUE Constraint on CREATE TABLE

下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 UNIQUE 约束：

MySQL:
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 Server / Oracle / MS Access:
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 约束，请使用下面的 SQL 语法：

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 uc_PersonID UNIQUE (Id_P,LastName) )

SQL UNIQUE Constraint on ALTER TABLE

当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD UNIQUE (Id_P) 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：

MySQL:
ALTER TABLE Persons DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

SQL UNION 和 UNION ALL 操作符

SQL UNION 操作符

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

SQL UNION 语法

SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

Employees_China表:

E_ID E_Name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas
04 Yang, Ming
Employees_USA表:

E_ID E_Name
01 Adams,John
02 Bush, George
03 Carter, Thomas
04 Gates, Bill

使用 UNION 命令

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA 结果集：

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams,John
Bush, George
Gates, Bill
注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1 UNION ALL SQL Statement 2

使用 UNION ALL 命令

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA 结果集：

E_Name
Zhang, Hua
Wang,Wei
Carter,Thomas
Yang,Ming
Adams,John
Bush,George
Carter,Thomas
Gates,Bill

SQL PRIMARY KEY 约束

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

SQL PRIMARY KEY Constraint on CREATE TABLE

下面的 SQL 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束：

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 Server / 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 约束，使用下面的 SQL 语法：

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 pk_PersonID PRIMARY KEY (Id_P,LastName) )

SQL PRIMARY KEY Constraint on ALTER TABLE

如果在表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束，使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName) 注释：如果您使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）

撤销 PRIMARY KEY 约束

如需撤销 PRIMARY KEY 约束，使用下面的 SQL：

MySQL:
ALTER TABLE Persons DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT pk_PersonID

SQL FOREIGN KEY 约束

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

"Persons" 表：

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 arter Thomas Changan Street Beijing
"Orders" 表：

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
  • "Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列
  • "Persons" 表中的 "Id_P" 列是 "Persons" 表中的 PRIMARY KEY
  • "Orders" 表中的 "Id_P" 列是 "Orders" 表中的 FOREIGN KEY
  • FOREIGN KEY 约束用于预防破坏表之间连接的动作
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

SQL FOREIGN KEY Constraint on CREATE TABLE

下面的 SQL 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY：

MySQL:
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) )
SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( Id_O int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, Id_P int FOREIGN KEY REFERENCES Persons(Id_P) )
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，使用下面的 SQL 语法：

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

SQL FOREIGN KEY Constraint on ALTER TABLE

如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，使用下面的 SQL：

MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

SQL CHECK 约束

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

SQL CHECK Constraint on CREATE TABLE

下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数

My SQL:
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 约束，请使用下面的 SQL 语法：

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

SQL CHECK Constraint on ALTER TABLE

如果在表已存在的情况下为 "Id_P" 列创建 CHECK 约束，请使用下面的 SQL：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CHECK (Id_P>0)
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

撤销 CHECK 约束

如需撤销 CHECK 约束，使用下面的 SQL：

SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT chk_Person
MySQL:
ALTER TABLE Persons DROP CHECK chk_Person

SQL DEFAULT 约束

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

SQL DEFAULT Constraint on CREATE TABLE

下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束：

My SQL / 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) DEFAULT 'Sandnes' ) 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders ( Id_O int NOT NULL, OrderNo int NOT NULL, Id_P int, OrderDate date DEFAULT GETDATE() )

SQL DEFAULT Constraint on ALTER TABLE

在表已存在的情况下为 "City" 列创建 DEFAULT 约束，使用下面的 SQL：

MySQL:
ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'

撤销 DEFAULT 约束

撤销 DEFAULT 约束，使用下面的 SQL：

MySQL:
ALTER TABLE Persons ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
进阶ConstraintscodequotvarcharsqloraclemysqlSpark大数据yyds干货盘点

有关SQL进阶篇之约束(Constraints)的更多相关文章

  1. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  2. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  3. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  4. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

  5. ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2

    我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。 最佳答案

  6. sql - Rails:使用 Postgres 创建对象时重复 ActiveRecord::RecordNotUnique？ - 2

    我正在使用Rails4应用程序，它需要创建大量对象以响应来自另一个系统的事件。当我调用create!时，主键列上出现非常频繁的ActiveRecord::RecordNotUnique错误(由PG::UniqueViolation引起)我的模型之一。我在SO上找到了其他答案，建议挽救异常并调用retry:beginTableName.create!(data:'here')rescueActiveRecord::RecordNotUnique=>eife.message.include?'_pkey'#Onlyretryprimarykeyviolationslog.warn"Retr

  7. ruby-on-rails - Ruby 和 SQL 中的重复业务逻辑 - 2

    我有一个PORO(普通旧Ruby对象)来处理一些业务逻辑。它接收一个ActiveRecord对象并对其进行分类。为了简单起见，以下面为例:classClassificatorSTATES={1=>"Positive",2=>"Neutral",3=>"Negative"}definitializer(item)@item=itemenddefnameSTATES.fetch(state_id)endprivatedefstate_idreturn1if@item.value>0return2if@item.value==0return3if@item.value但是，我还想根据这些st

  8. sql - ActiveRecord 的意外行为包括 - 2

    我正在使用ARincludes在对象User和Building之间执行LEFTOUTERJOIN的方法，其中User可能有也可能没有Building关联:users=User.includes(:building).references(:buildings)因为我正在使用references，任何关联的Building对象都将被预先加载。我的期望是我随后能够遍历用户列表，并检查用户是否有与其关联的建筑物而不会触发额外的查询，但实际上每当我尝试访问建筑物属性时我都会看到对于没有建筑物的用户，AR会进行另一个SQL调用以尝试检索该建筑物(尽管在后续尝试中它只会返回nil)。这些查询显然是

  9. sql - 如何查询具有 3 个标签的事件？ - 2

    我有以下模型:activity.rbtag.rbtagging.rb标签是事件和标签的连接模型。我想搜索具有2个或更多标签的事件。我如何在Rails中执行此操作？例如:我有tag1=Christmas，tag2=Florida，tag3=John如果存在，我想找到tag1、tag2和tag3存在的Activity。[编辑]我最终做了什么:tags=[tag1,tag2,tag3]activities=[]tags.eachdo|tag|activities如果任何组值的大小等于tags.size，则该事件包含所有标签。 最佳答案 如

  10. ruby-on-rails - 如何(大量)减少 Rails 应用程序中的 SQL 查询数量？ - 2

    在我的Rails应用程序中，我有users，它可以有许多invoices，而invoices又可以有许多payments。现在在dashboardView中，我想总结一个user曾经收到的所有payments，按年、季度或月。付款也分割为毛额、Netty和税额。user.rb:classUser:items).allpayments_with_invoice.select{|x|range.cover?x.date}.sum(&:"#{kind}_amount")endend发票.rb:classInvoicepayment.rb:classPaymentdashboards_cont

随机推荐