【SQL】作为前端，应该了解的SQL知识（第一弹）

基本概念

---

1. 数据库（DB）

   将大量数据保存起来，经加工可进行高效访问的数据集合

2. 数据库管理系统（DBMS）

   用来管理数据库的计算机系统

---

1. 字段：表中的列

2. 记录：表中的行

   💡 关系数据库必须以行为单位进行数据读写

---

1. DDL：（数据定义语言）：用来创建或者删除数据库或者表
   1. CREATE：创建
   2. DROP：删除
   3. ALTER：修改
2. DML:（数据操纵语言）：用来查询或者变更表记录
   1. SELECT:查询
   2. INSERT: 插入
   3. UPDATE: 更新
   4. DELETE: 删除
3. DCL:（数据控制语言）：用来确认或者取消对数据库中数据进行的变更
   1. COMMIT: 确认变更
   2. ROLLBACK: 取消变更
   3. GRANT: 赋予操作权限
   4. REVOKE: 取消用户操作权限

---

1. 主键（primary key）：就是可以特定一行数据的列
2. 聚合键：group by 子句中指定的列称为聚合键或者分组列
3. 排序键: order by 子句中的列名为排序键

---

1. 事务: 是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理。

---

注释：

● 1行注释
书写在“--”之后，只能写在同一行。
● 多行注释
书写在“/*”和“*/”之间，可以跨多行。

---

DBMS分类

1. 层次数据库（HDB）

   将数据通过层次结构（树状结构）表示出来

2. 关系数据库（RDB）

   采用行和列组成的二维表格存储数据，用SQL对数据进行操作

   常见的RDB：Oracle、SQL Server、DB2、PostgreSQL、MySQL

3. 面向对象数据库（OODB）

   保存对象的数据库

4. XML数据库（XMLDB）

   对XML形式的数据进行处理

5. 键值存储数据库（KVS）

   只保存查询所使用的主键和值的组合的数据库（也就是关联数组或散列）

数据库管理工具DBeaver

1. 官网下载需要的数据库。这里以postgreSQL为例。

下载地址

1. 打开dbeaver，新建连接

输入用户名密码即可。

书写规则

1. 以;结尾
2. 不区分关键字的大小写，但是字段区分大小写
3. 字符串、日期、常数需要用单引号，数值不需要引号

---

1. 只能用半角英文字母、数字、下划线（_）作为数据库、表和列的名称，必须以英文字母开头。

RDBMS结构

常见的系统结构为**客户端/服务器（c/s）**类型

操作

建库

CREATE DATABASE <数据库名称>;

建表

CREATE TABLE <表名>
（<列名1> <数据类型> <该列所需约束>，
 <列名2> <数据类型> <该列所需约束>，
 <列名3> <数据类型> <该列所需约束>，
 <列名4> <数据类型> <该列所需约束>，
 .
 .
 .
 <该表的约束1>， <该表的约束2>，……）；

约束是除了数据类型之外，对列中存储的数据进行限制或者追加条件的功能

数据类型：

INTEGER	整数
CHAR	字符	定长字符串
VARCHAR2	字符	可变长字符串
DATE	日期	年月日时分秒

删表

DROP TABLE 可以将表完全删除

DROP TABLE <表名>；

更新表

添加列

-- 添加一列
ALTER TABLE <表名> ADD <列名> ；
-- 添加多列
ALTER TABLE <表名> ADD （<列名>，<列名>，……）；

删除某列

-- 删除一列
ALTER TABLE <表名> DROP <列名> ；
-- 删除多列
ALTER TABLE <表名> DROP （<列名>，<列名>，……）；

变更表名

ALTER TABLE Poduct RENAME TO Product;

更新数据

-- 单列更新
UPDATE <表名> 
SET <列名> = <表达式>
		...
WHERE <条件>;

-- 多列更新
UPDATE <表名> 
SET <列名1> = <表达式1> ,
		<列名2> = <表达式2> ,
		...
WHERE <条件>;

插入数据

-- 插入一行数据
INSERT INTO <表名> (<列名>，<列名>，……) VALUES (<值1,值2,……>)
-- 插入多行数据（此方法适用于 DB2、SQL、SQL Server、PostgreSQL 和 MySQL，不适用于Oracle）
INSERT INTO <表名> (<列名>，<列名>，……) 
VALUES (<值1,值2,……>),
				(<值3,值4,……>)
-- 插入多行数据（Oracle）
INSERT ALL INTO <表名> VALUES (<值1,值2,……>)
						INTO <表名> VALUES (<值3,值4,……>)

注意：

向表中插入NULL值的前提是该列没有NOT NULL约束

插入默认值

建表时设置默认值

在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。

CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL, 
 （略）
 sale_price INTEGER **DEFAULT** 0, -- 销售单价的默认值设定为0;
 （略）
 PRIMARY KEY (product_id));

显式方式设定默认值

INSERT INTO ProductIns 
(product_id, product_name, product_type, sale_price, purchase_price, regist_date) 
VALUES ('0007', '擦菜板', '厨房用具', **DEFAULT**, 790, '2009-04-28')
-- 设置了DEFAULT sale_price的值就是 0

隐式方式设置默认值

省略sale_price列，值也省略（注意：如果建表时没设置默认值，sale_price的值就是NULL）

INSERT INTO ProductIns
(product_id, product_name, product_type,purchase_price, regist_date)
VALUES ('0007', '擦菜板', '厨房用具',790, '2009-04-28')
-- sale_price的值就是 0

从别的表备份数据

INSERT … SELECT语句

-- 将商品表中的数据复制到商品复制表中
INSERT INTO ProductCopy 
(product_id, product_name, product_type,sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price,purchase_price, regist_date
FROM Product

删除数据

DELETE 语句会留下表（容器），而删除表中的全部数据

注意：DELETE语句中只能使用WHERE子句。

-- 删除全部数据
DELETE FROM <表名>;
-- 按条件删除数据
DELETE FROM <表名> WHERE <条件>;

删除所有数据

删除所有数据比DELETE快得多！

Oracle把它定义为DDL语句。

TRUNCATE <表名>;

事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理

事务处理的终止指令包括COMMIT（提交处理）和ROLLBACK（取消处理）两种。

事务的四种特性

1. 原子性（Atomicity）

   所有的更新处理要么全部执行，要么全部不执行。

2. 一致性（Consistency）

   所有的更新处理都要遵循约束

3. 隔离性（Isolation）

   不同事务之间互不干扰，互相隔离。

4. 持久性（Durability）

   在事务结束后，即使计算机发生了故障，该时间节点的数据也会被保存。

创建事务

事务开始语句;
 DML语句①;
 DML语句②;
 DML语句③;
 . . .
事务结束语句（COMMIT或者ROLLBACK）;

注意：Oracle、DB2没有事务开始语句，SQL Server、PostgreSQL的事务开始语句为：BEGIN TRANSACTION ，MySQL的事务开始语句为：START TRANSACTION

事务结束语句-commit

提交事务包含的全部更新处理的结束指令。

注意：一旦提交就无法恢复到事务最开始的状态啦。

流程：直线进行

事务结束语句-rollback

取消事务包含的全部更新处理的结束指令。一旦回滚，数据库就会恢复到事务开始之前的状态。

流程：掉头回起点