SQL 基础加固 一

为了成为正规军，我得几下这些专业素养：

什么是 SQL？

• SQL 指结构化查询语言
• SQL 使我们有能力访问数据库
• SQL 是一种 ANSI 的标准计算机语言

SQL 能做什么？

• SQL 面向数据库执行查询
• SQL 可从数据库取回数据
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

重要事项

一定要记住，SQL 对大小写不敏感！

SQL DML 和 DDL

可以把 SQL 分为两个部分：数据操作语言 (DML)(Data Manipulation Language) 和 数据定义语言 (DDL)(Data Definition Language)；简单来讲DML 就是操作数据的语言，DDL就是操作数据库对象的语言

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分：

• SELECT - 从数据库表中获取数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据
• INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:

• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

 

TOP ：

sql server 和 oracle 的 top 和 rownum 我都相当熟悉 ，而mySql 的 limit 和oracle 的 rownum 是一样的用法

 

LIKE：

略

SQL 通配符：

%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不在字符列中的任何单一字符

 

%：

略

_：

现在，我们希望从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE FirstName LIKE '_eorge'

接下来，我们希望从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头，然后是一个任意字符，然后是 "r"，然后是任意字符，然后是 "er"：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er'

 

[charlist]：

现在，我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE City LIKE '[ALN]%'

[^charlist] 或者 [!charlist] ：

现在，我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：

我们可以使用下面的 SELECT 语句：

SELECT * FROM Persons
WHERE City LIKE '[!ALN]%'

IN：

略

BETWEEN：

如需以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人，请使用下面的 SQL：

SELECT * FROM Persons
WHERE LastName
BETWEEN
 'Adams' 
AND
 'Carter'

重要事项：不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "Adams" 和 "Carter" 之间的人，但不包括 "Adams" 和 "Carter" ；某些数据库会列出介于 "Adams" 和 "Carter" 之间并包括 "Adams" 和 "Carter" 的人；而另一些数据库会列出介于 "Adams" 和 "Carter" 之间的人，包括 "Adams" ，但不包括 "Carter" 。

所以，请检查你的数据库是如何处理 BETWEEN....AND 操作符的！

SQL Alias（别名）

略

JOIN 即 INNER JOIN

不以谁为基准，满足就列出

LEFT JOIN

先查询左表，右表有不符合的以null表示

Bush

George

LEFT JOIN

先查询右表，左表有不符合的以null表示

34764

FULL JOIN

总之就是全部都要显示，左边不符合就以null填充，右边不符合也以null补充

Bush	George
		34764

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注意：union 会排除重复的 值，如果允许重复 就用 union all

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT INTO

将结果集插入到表中  

下面的例子会制作 "Persons" 表的备份复件：

SELECT
 *
INTO
 Persons_backup
FROM Persons

IN 子句可用于向另一个数据库中拷贝表：

SELECT
 *
INTO
 Persons 
IN
 'Backup.mdb'
FROM Persons

如果我们希望拷贝某些域，可以在 SELECT 语句后列出这些域：

SELECT
 LastName,FirstName
INTO
 Persons_backup
FROM Persons

我们也可以添加 WHERE 子句。

下面的例子通过从 "Persons" 表中提取居住在 "Beijing" 的人的信息，创建了一个带有两个列的名为 "Persons_backup" 的表：

SELECT
 LastName,Firstname
INTO
 Persons_backup
FROM Persons
WHERE
 City='Beijing'

SQL SELECT INTO 实例 - 被连接的表

从一个以上的表中选取数据也是可以做到的。

下面的例子会创建一个名为 "Persons_Order_Backup" 的新表，其中包含了从 Persons 和 Orders 两个表中取得的信息：

SELECT
 Persons.LastName,Orders.OrderNo
INTO
 Persons_Order_Backup
FROM
 Persons
INNER JOIN
 Orders
ON
 Persons.Id_P=Orders.Id_P

CREATE DATABASE 创建数据库

CREATE DATABASE my_db

CREATE TABLE

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

约束 (Constraints)

我们将主要探讨以下几种约束：

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

NOT NULL 约束   ===>是CHECK 约束的一种用法

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

创建方式如下 

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

或者修改表约束   删除约束

alter table test add constraint idnotnull  check(id is not null)
alter table test drop constraint idnotnull 

SQL UNIQUE 约束

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

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

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

创建方式（已经创建就 ALTER ）：

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

为已经创建的表添加 UNIQUE 约束 和 删除 

alter table test add constraint  idunique unique(id,name)  --可以为多列定义unique 约束

alter table test add unique(id,name) --没有明确定义约束名字，想要删除该约束时，会麻烦一点，可以通过 执行存储过程：

sp_helpconstraint test 查询 列的约束名，再删除

alter table test drop constraint idunique 

 

 

PRIMARY KEY 约束 （主键约束）

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

主键必须包含唯一的值。

主键列不能包含 NULL 值。

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

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

或者

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

联合主键 ：

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

或者

alter table test add primary key (id,name)

--显示命名

alter table test add constraint PK_TEST_134 primary key (id,name)

撤销唯一标识,

MySQL:
ALTER TABLE PersonsDROP PRIMARY KEY

SQL Server / Oracle / MS Access

ALTER TABLE PersonsDROP CONSTRAINT pk_PersonID

 

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	Carter	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 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

即

1、想要删除 persons 中主键为 1 和 3 的时候就会返回错误信息

消息 547，级别 16，状态 0，第 1 行
DELETE 语句与 REFERENCE 约束"FK__orders__ID_P__5FB337D6"冲突。该冲突发生于数据库"myself"，表"dbo.orders", column 'ID_P'。
语句已终止。

2、想要插入一行到 orders表中，并且 id_p 列是 persons 表中不具有的 10 时 如下

 insert into orders values(3,'12346',10)

结果：

消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 FOREIGN KEY 约束"FK__orders__ID_P__5FB337D6"冲突。该冲突发生于数据库"myself"，表"dbo.persons", column 'id_p'。
语句已终止。

语句

create table orders （

id_o int primary key,

orderNo nvarchar(50) ,

id_p int ,

constraint  rk_orders_12444   regine key(id_p) references persons(id_p)

)

已经创建的情况下

alter orders add constraint     rk_orders_44464         regine key（id_p）references persons(id_p)

删除

alter orders drop constraint  rk_orders_44464 

 

SQL CHECK 约束

之前的 非空约束就是Check 约束的一种用法

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

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

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)

 

SQL 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 Server / Oracle / MS Access:

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

 ALTER TABLE test
add default('北京') for city