【SQL01】初识数据库_银行信息系统初识sql数据库-CSDN博客

本文链接：https://blog.csdn.net/apple_50678962/article/details/111146056

0 环境搭建

homebrew install mysql一直不成功，后来就直接mysql官网上下载dmg文件手动安装。
亲测 M1 chip Macbook可正常下载使用mysql。

1.1 初识数据库

数据库是将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。该数据集合称为数据库（Database，DB）。用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

1.1.1 DBMS的种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类，现阶段主要有以下 5 种类型.

层次数据库（Hierarchical Database，HDB）

关系数据库（Relational Database，RDB）

这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。比较具有代表性的 RDBMS 有如下 5 种。

Oracle Database：甲骨文公司的RDBMS
SQL Server：微软公司的RDBMS
DB2：IBM公司的RDBMS
PostgreSQL：开源的RDBMS
MySQL：开源的RDBMS

面向对象数据库（Object Oriented Database，OODB）

使用对象数据库技术的主要原因

- 关系数据库（RDMS）在管理复杂数据时显得笨重
- 被应用软件操作的数据一般时用面向对象的编程语言如C++，Java，Delphi和C#写成，而那些用来转化数据表示和关系数据库元祖的代码很冗繁

代表性的OODB

ObjectDB - 对象关系数据库的一个例子

技术特性

OODB用对象查询语言OQL做了标准化的尝试
表的联合常常时不必要的
因为无需查询只需通过指针（pointer）就可以直接获得对象

Pro & Con （和RDBMS对比）：

Pro
- OODB stores complex data and relationships between data directly, without mapping to relational rows and columns, and this makes them suitable for applications dealing with very complex data
- OODB have a mang-to-many relationship and are accessed by the use of pointers. (pointers are linked to objects to establish relationships)
- OODBMS can be programmed with small procedural differences without affecting the entire system
- OODB也提供对SQL的完全支持，如objectivity/SQL++
- 只能通过公布的一些接口访问和RDB技术假定：数据只能由基于数据内容的查询，而不是预定义的查询路径的形式访问之间，存在着本质的张力。
Con
- 对一般目的的查询，基于pointer的技术比关系型要慢并难于形式化
- OODB缺少形式化的数学基础，导致他们在查询支持上的弱势

XML数据库（XML Database，XMLDB）

data persistence (persistence data structure 可持久化数据结构 - 一种能够在修改之后保留历史版本的数据结构，相关操作不会直接修改被保持的数据，而时会在原版本上产生一个新分支)software system that allows data to be specified, and sometimes stored in XML format.
XMLDB属于NoSQL数据库（非传统关系型数据库）

使用XMLDB的原因：

XML is very well suited to sparse data, deeply nested data and mixed content
XML is human readable, relational tables requires expertise to access
metadata is often available as XML
increasingly common use of XML for data transport - data is extracted from databases and put into XML documents and vice-versa.
in content-based applications, the ability of the native XML database also minimize the need for extraction or entry of metadata to support searching and navigation.

XML databases are a type of structured document-oriented database that allows querying based on XML document attributes.

XML databases are mostly used in applications where the data is conveniently viewed as a collection of documents, with a structure that can vary from the very flexible to the highly rigid: examples include scientific articles, patents, tax fillings, and personnel records

XML Database 的：

键值存储系统（Key-Value Store，KVS）

属于NoSQL数据库（非传统关系型数据库）

需要key去获取(retrieve)

1.1.2 RDBMS的常见系统结构

使用 RDBMS 时，最常见的系统结构就是客户端 / 服务器类型（C/S类型）这种结构

1.2 初识SQL

数据库中存储的表结构类似于excel中的行和列，在数据库中，行称为记录，它相当于一条记录，列称为字段，它代表了表中存储的数据项目

行和列交汇的地方称为单元格，一个单元格中只能输入一条记录。

SQL是为操作数据库而开发的语言。国际标准化组织（ISO）为 SQL 制定了相应的标准，以此为基准的SQL 称为标准 SQL。

完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句。

根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类.

DDL
- DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令。
  - CREATE：创建数据库和表等对象
  - DROP：删除数据库和表等对象
  - ALTER：修改数据库和表等对象
DML
- （Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。DML 包含以下几种指令。
  - SELECT：查询表中的数据
  - INSERT：向表中插入新数据
  - UPDATE：更新表中的数据
  - DELETE：删除表中的数据
DCL
- DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更。
- 除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。
  - COMMIT：确认对数据库中的数据进行的变更
  - ROLLBACK：取消对数据库中的数据进行的变更
  - GRANT：赋予用户操作全线
  - REVOKE：取消用户的操作权限

实际使用的SQL语句中有90%属于Data Manipulation Language

1.2.1 SQL的基本书写规则

SQL语句要以分号;结尾
SQL不区分关键字的大小写，但是插入到表中的数据时区分大小写的
win系统默认不区分表名&字段名的大小写
linux和mac默认严格区分表名&字段名的大小写
常数的书写方式时固定的
单词需要用半角空格或者换行来分隔

SQL语句的单词之间需使用半角空格或换行符来进行分隔，且不能使用全角空格作为单词的分隔符

1.2.2 数据库的创建 (CREATE DATABASE语句)

syntax：

CREATE DATABASE < 数据库名称 > ;

创建本课程使用的数据库

CREATE DATABASE shop;

1.2.3 表的创建 (CREATE TABLE语句)

sytax:

CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
  < 列名 2> < 数据类型 > < 该列所需约束 > ,
  < 列名 3> < 数据类型 > < 该列所需约束 > ,
  < 列名 4> < 数据类型 > < 该列所需约束 > ,
  .
  .
  .
  < 该表的约束 1> , < 该表的约束 2> ,……);

创建本课程用到的商品表

CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));

1.2.4 命名规则

只能使用半角英文字母、数字、下划线作为数据库、表和列的名称
名称必须以半角英文字母开头

1.2.5 数据类型的制定

数据库创建的表，所有的列都必须制定数据类型，每一列都不能存储于该列数据类型不符的数据

4种最基本的数据类型

INTEGER – 整数（不能小数）
CHAR – 定长字符串
- 如果存储的字符串长度达不到最大长度，使用半角空格进行补足
- 会浪费存储空间，所以一般不使用
VARCHAR – 可变长度字符串
- 如果存储的字符串长度达不到最大长度，也不会补足
DATE – 日期

1.2.6 约束的设置

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

NOT NULL 非空约束，即该列必须输入数据
PRIMARY KEY 主键约束，代表该列时唯一值，可以通过该列取出特定的行的数据

1.2.7 表的删除和更新

删除表

DROP TABLE < 表名 > ;

⚠️ 注意：删除的表时无法恢复的，只能重新插入，执行删除操作时要无比谨慎

添加列的ALTER TABLE语句

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

删除列的ALTER TABLE语句

ALTER TABLE < 表名 > DROP COLUMN < 列名 >;

ALTER TABLE语句和DROP TABLE语句一样，执行之后无法恢复。
误添的列可以通过ALTER TABLE语句删除，或者将表全部删除后重新创建

清空表内容

TRUNCATE TABLE TABLE_NAME;

相比drop delete，truncate用来清除数据时，速度最快
TRUNCATE TABLE statement drop and re-create the table in such a way that any auto-increment value is reset to its start value which is generally 1
DELETE lets you filter which rows to be deleted based on an optional WHERE clause

TRUNCATE 比 DELETE清除数据更快且占用更少的系统资源，因为DELETE在执行删除时，先会去扫描整个表，统计会被影响（被删除）的行/记录。然后再去一行一行得删除他们，在删除的同时为每个删除的行记录一个log。而TRUNCATE TABLE是直接删除所有行，且不记录任何多余信息

💡Tips
使用TRUENCATE TABLE 如果是想要删除所有行然后重新创建整个表
使用DELETE 如果

想要基于特殊情况删除有限数量的行
不希望reset auto-increment 值

数据的更新

basic syntax:

UPDATE <表名>
SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
WHERE <条件>;  -- 可选，非常重要。
ORDER BY 子句;  --可选
LIMIT 子句; --可选

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改

-- 修改所有的注册时间
UPDATE product
   SET regist_date = '2009-10-10';  
-- 仅修改部分商品的单价
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。

-- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL  
UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';

和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。
但是，只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同。

多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

-- 基础写法，一条UPDATE语句只更新一列
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';
UPDATE product
   SET purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';  

-- 该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。
-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

SET 子句中的列不仅可以是两列，还可以是三列或者更多。

1.2.8 向表中插入数据

basic syntax

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

-- 包含列清单
INSERT INTO ProductIns (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) 
VALUES ('0005', '高压锅', 
'厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO ProductIns 
VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

原则上，执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行相应次数的 INSERT 语句。其实很多 RDBMS 都支持一次插入多行数据

-- 通常的INSERT
INSERT INTO ProductIns VALUES ('0002', '打孔器', 
'办公用品', 500, 320, '2009-09-11');
INSERT INTO ProductIns VALUES ('0003', '运动T恤', 
'衣服', 4000, 2800, NULL);
INSERT INTO ProductIns VALUES ('0004', '菜刀', 
'厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO ProductIns VALUES ('0002', '打孔器', 
'办公用品', 500, 320, '2009-09-11'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

INSERT INTO ProductIns (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', 
'厨房用具', 500, NULL, '2009-09-20');

还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。

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

可以使用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;

练习题

1.1

编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表 Addressbook （地址簿），并为 regist_no （注册编号）列设置主键约束

CREATE TABLE addressbook
    (
        regist_no INTEGER NOT NULL ,
        name VARCHAR(128) NOT NULL ,
        address VARCHAR(256) NOT NULL ,
        tel_no CHAR(10),
        email_address CHAR(20),
        PRIMARY KEY (regist_no)
    );

1.2

假设在创建练习1.1中的 Addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请把此列添加到 Addressbook 表中

列名： postal_code
数据类型：定长字符串类型（长度为 8）
约束：不能为 NULL

ALTER TABLE addressbook ADD COLUMN postal_code CHAR(8) NOT NULL ;

1.3

编写 SQL 语句来删除 Addressbook 表。

DROP TABLE addressbook；

1.4

编写 SQL 语句来恢复删除掉的 Addressbook 表。

CREATE TABLE addressbook
    (
        regist_no INTEGER NOT NULL ,
        name VARCHAR(128) NOT NULL ,
        address VARCHAR(256) NOT NULL ,
        postal_code CHAR(8) NOT NULL ,
        tel_no CHAR(10) ,
        email_address CHAR(20) ,
        PRIMARY KEY (regist_no)
    );

autocommit, Commit, 和 Rollback 语句

These statements provide control over use of transactions

START TRANSACTION or BEGIN start a new transaction
COMMIT commits the current transaction, making its changes permanent
ROLLBACK rolls back the current transaction, canceling its changes
SET autocommit disable or enables the default autocommit mode for the current session.

autocommit

默认，MySQL是autocommit的

每一条SQL语句都是一条transaction，除非返回error，会自动commit
如果想要批量多条语句一起commit，以START TRANSACTION或BEGIN开始，以COMMIT或ROLLBACK结束。

autocommit停用

SET autocommit=0;

the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts. 如果不声明 COMMIT 的话，MySQL会执行 ROLLBACK 本条transaction。

COMMIT

the changes made in the current transaction are made permanent and become visible to other sessions.

ROLLBACK

cancels all modifications made by the current transaction.

CREATE TABLE customer 
	(
	a INT, 
	b CHAR (20), 
	INDEX (a)
	);
-- Do a transaction with autocommit turned on.
START TRANSACTION;
INSERT INTO customer VALUES (10, 'Heikki');
COMMIT;
Query OK, 0 rows affected (0.00 sec)

-- Do another transaction with autocommit turned off.
SET autocommit=0;
INSERT INTO customer VALUES (15, 'John');
INSERT INTO customer VALUES (20, 'Paul');
DELETE FROM customer WHERE b = 'Heikki';
-- Now we undo those last 2 inserts and the delete.
ROLLBACK;

SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)