SQL学习记录丨Ch.01 - 初识数据库

SQL学习记录丨Ch.01 - 初试数据库

repo：https://github.com/datawhalechina/wonderful-sql

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

DBMS 种类

DBMS 主要通过数据的保存格式（数据库的种类）来进行分类

1. 层次数据库（Hierarchical Database，HDB）
2. 关系数据库（Relational Database，RDB），RDBMS 的特点是由行和列组成的二维表来管理数据
   • Oracle Database：甲骨文公司的 RDBMS
   • SQL Server：微软公司的 RDBMS
   • DB2：IBM公司的 RDBMS
   • PostgreSQL：开源的 RDBMS
   • MySQL：开源的 RDBMS
3. 面向对象数据库（Object Oriented Database，OODB）
4. XML数据库（XML Database，XMLDB）
5. 键值存储系统（Key-Value Store，KVS），举例：MongoDB

RDBMS 的常见系统结构

RDBMS 一般使用客户端 / 服务器类型（C/S类型）的结构

1. 客户端（使用数据库的程序），利用 SQL 语句向服务器发起请求
2. 服务器（读取数据库的程序），将请求的数据返回给客户端
3. 数据库（数据保存在硬盘上）

SQL 的基本概念

数据库的结构类似于 Excel 表格

1. 数据库中的「记录」对应「行」，相当于一条记录
2. 数据库中的「字段」对应「列」，存储的数据项目
3. 行与列交汇的地方称为「单元格」
4. 一个单元格中只能有一个记录

SQL 语句分为以下三类

DDL ：DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象

DDL 包含以下几种指令

• CREATE ： 创建数据库和表等对象
• DROP ： 删除数据库和表等对象
• ALTER ： 修改数据库和表等对象的结构

DML :DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录

DML 包含以下几种指令

• SELECT ：查询表中的数据
• INSERT ：向表中插入新数据
• UPDATE ：更新表中的数据
• DELETE ：删除表中的数据

DCL ：DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定

DCL 包含以下几种指令。

• COMMIT ： 确认对数据库中的数据进行的变更
• ROLLBACK ： 取消对数据库中的数据进行的变更
• GRANT ： 赋予用户操作权限
• REVOKE ： 取消用户的操作权限

SQL 的基本书写规则

1. SQL语句要以分号（ ; ）结尾
2. SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
3. win 系统默认不区分表名及字段名的大小写
4. linux / mac 默认严格区分表名及字段名的大小写
5. 常数的书写方式是固定的 'abc', 1234, '26 Jan 2010', '10/01/26', '2010-01-26'…
6. 单词需要用半角空格或者换行来分隔

数据库的创建（CREATE DATABASE）

语法

CREATE DATABASE < 数据库名称 > ;

例如

CREATE DATABASE shop;

表的创建（CREATE TABLE）

语法

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. 名称必须以半角英文字母开头

字段类型

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

四种基本数据类型

• INTERGER，用来指定存储整数的列的数据类型（数字型），不能存储小数
• CHAR，用来存储定长字符串，字符串长度达不到最大长度会使用半角空格进行补足，由于会浪费存储空间，所以一般不使用
• VARCHAR，用来存储可变长度字符串，可变长字符串即使字符数未达到最大长度，也不会用半角空格补足
• DATE，用来指定存储日期（年月日）的列的数据类型（日期型）

约束设置

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

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

表的删除与更新

删除表

删除表（删除的表是无法恢复的，只能重新插入）

-- DROP TABLE < 表名 > ;
DROP TABLE product;

添加列（执行后无法删除，误添加的列可以通过 ALTER TABLE 语句删除）

-- ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
-- 添加一列可以存储100位的可变长字符串的 product_name_pinyin 列
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

删除列

-- ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
-- 删除 product_name_pinyin 列
ALTER TABLE product DROP COLUMN product_name_pinyin;

删除表中特定的行

-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';

清空表内容（相比 drop / delete，truncate 用来清除数据时，速度最快）

TRUNCATE TABLE TABLE_NAME;

数据的更新

基本语法：

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 即可，但是只有未设置 NOT NULL 约束和主键约束的列才可以清空为 NULL

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

多列更新

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 = '厨房用具';  

向表中插入数据

创建一个名为 productions 的表

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

插入数据的基本语法为

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 语句(INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL)

-- 通常的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');  
-- Oracle中的多行INSERT
INSERT ALL INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
           INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
           INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;  
-- DUAL是Oracle特有（安装时的必选项）的一种临时表A。因此“SELECT *FROM DUAL” 部分也只是临时性的，并没有实际意义。  

还可以向表中插入默认值（初始值）。可以通过在创建表的 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; 

索引

创建表时可以直接创建索引，语法如下：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

也可以使用如下语句创建

-- 方法1
CREATE INDEX indexName ON table_name (column_name)

-- 方法2
ALTER table tableName ADD INDEX indexName(columnName)