瓜斯拉的逆袭
0%

SQL组队学习01：环境搭建，初识数据库

发表于 分类于
本文字数： 7.6k 阅读时长 ≈ 19 分钟

Task01：环境搭建，初识数据库

SQL 环境搭建

主要 follw 教程：https://github.com/datawhalechina/wonderful-sql

1 安装 MySQL

系统为 MacOS ，根据教程一步步来，很简单。

2 DataGrip 的安装和连接MySQL

安装好 MySQL 以后，使用 DataGrip 连接。DataGrip 教程：https://www.jetbrains.com/help/datagrip/quick-start-with-datagrip.html

3 创建学习用的数据库

根据《SQL基础教程》提供的MySQL版本的数据库,数据表的创建以及数据导入的代码, 经过一些修改, 创建了一份 sql 脚本, 运行该脚本可以一步到位地创建本文档所需的数据库shop及其中所有的表,并插入本教程所需要的所有数据。

下述SQL脚本可用于创建本教程所使用的示例数据库 shop 以及数据库中表的创建和数据的插入。

详见 ./materials/shop.sql

将shop.sql复制到在 DataGrip 里并运行

image-20220710185610866

3.1 执行 SQL 查询

1
select * from shopproduct where quantity > 100;

查询结果

image-20220710190234782

SQL 初识数据库

1.1 初识数据库

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

数据库管理系统（Database Management System，DBMS）：用来管理数据库的计算机系统

1.1.1 DBMS的种类

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

  • 层次数据库（Hierarchical Database，HDB）

  • 关系数据库（Relational Database，RDB）

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

    如上是5种具有代表性的RDBMS，其特点是由行和列组成的二维表来管理数据，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。

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

  • XML数据库（XML Database，XMLDB）

  • 键值存储系统（Key-Value Store，KVS），举例：MongoDB

1.1.2 RDBMS的常见系统结构

客户端 / 服务器类型（C/S类型）

image-20220712000449258

1.2 初识SQL

image-20220712000537847

数据库存储的表结构类似与excel的行和列，每一行称为一条记录，每一列称为一个字段。

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 语句当中有 90% 属于 DML，本书同样会以 DML 为中心进行讲解。

1.2 SQL语句

1.2.1 SQL的基本书写规则

SQL语法规范总得的原则是，清楚、易读并且层次清晰。实际场景中常常动辄几百上千行的SQL语句，如果不写清楚，事后review或者别人接手的时候，会让人怀疑人生。

当然这些规范都是笔者根据实际经验总结归纳的，并不是金科铁律，但是强烈建议新手按以下规范入门编写SQL语句。

常见注意事项如下：

  1. MySQL本身不区分大小写，但强烈要求关键字大写，表名、列名用小写；
  2. 创建表时，使用统一的、描述性强的字段命名规则保证字段名是独一无二且不是保留字的，不要使用连续的下划线，不用下划线结尾；最好以字母开头
  3. 关键字右对齐，且不同层级的用空格或缩进控制，使其区分开，见样例二；
  4. 列名少的时候写在一行里无伤大雅；多的时候以及涉及到CASE WHEN 或者聚合计算的时候，建议分行写；个人习惯是逗号在列名前面，方便之后删除某些列，放列名后亦可；
  5. 表别名和列别名尽量用有具体含义的词组，不要用a b c，不然以后review的时候会非常痛苦；
  6. 运算符前后都加一个空格；
  7. 当用到多个表时，请在所有列名前写上引用的表别名，不要嫌麻烦；
  8. 每条命令用分号结尾；
  9. 养成随手写注释的习惯，注释方法：
1
2
3
单行注释 #注释文字
单行注释 -- 注释文字
多行注释：/* 注释文字 */

P.S. 养成良好的书写习惯，可以避免以后发生流血事件 →_→

1.2.2 数据库的创建（CREATE DATABASE 语句）

语法：

1
CREATE DATABASE < 数据库名称 > ;

创建本课程使用的数据库；因为之前已经用脚本创建过 DATABASE shop 了，这里数据库名改为 shop_test：

1
CREATE DATABASE shop_test;

如图，创建成功空数据库

image-20220712001902049

1.2.3 表的创建（ CREATE TABLE 语句）

语法：

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

创建本课程用到的商品表，照着敲

1
2
3
4
5
6
7
8
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));

创建成功如下：

image-20220712002519329

其中 product 列名称的解释：

image-20220712002054990

1.2.4 数据类型

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

四种最基本的数据类型

  • INTEGER 型

用来指定存储整数的列的数据类型（数字型），不能存储小数。

  • CHAR 型

用来存储定长字符串，当列中存储的字符串长度达不到最大长度的时候，使用半角空格进行补足，由于会浪费存储空间，所以一般不使用。

  • VARCHAR 型

用来存储可变长度字符串，定长字符串在字符数未达到最大长度时会用半角空格补足，但可变长字符串不同，即使字符数未达到最大长度，也不会用半角空格补足。

  • DATE 型

用来指定存储日期（年月日）的列的数据类型（日期型）。

CHAR 是定长字符串，VARCHAR 是变长字符串

1.2.5 约束的设置

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

NOT NULL是非空约束，即该列必须输入数据。

PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

1.2.6 表的删除和更新

  • 删除表的语法：
1
DROP TABLE < 表名 > ;
  • 删除 product 表

需要特别注意的是，删除的表是无法恢复的，只能重新插入，请执行删除操作时要特别谨慎。

1
DROP TABLE product;

表已删除：

image-20220712224235900

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

重新建表并添加列：

image-20220712224503279image-20220712224537910

  • 删除列的 ALTER TABLE 语句
1
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
  • 删除 product_name_pinyin 列
1
ALTER TABLE product DROP COLUMN product_name_pinyin;

删除后恢复到添加列之前：

image-20220712224626807

  • 删除表中特定的行（语法）
1
2
-- 一定注意添加 WHERE 条件，否则将会删除所有的数据
DELETE FROM product WHERE COLUMN_NAME='XXX';

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

  • 清空表内容
1
TRUNCATE TABLE TABLE_NAME;

优点：相比drop / delete，truncate用来清除数据时，速度最快。

  • 数据的更新

基本语法：

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

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

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

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

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

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

多列更新

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

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

该写法可以得到正确结果，但是代码较为繁琐。可以采用合并的方法来简化代码。

1
2
3
4
5
-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = '厨房用具';

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

1.2.7 向 product 表中插入数据

为了学习 INSERT 语句用法，我们首先创建一个名为 productins 的表，建表语句如下：

1
2
3
4
5
6
7
8
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));

建表如下，product_id 为主键：

image-20220712225215297

插入语句的基本语法：

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

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

1
2
3
4
-- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '电饭煲', '厨房用具', 1500, 1000, '2022-07-12');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');  

都成功插入一条记录：

image-20220712225543281

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

1
2
3
4
-- 多行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');   

结果如下：

image-20220712225751354

1.2.7* 从其他表插入

可以使用INSERT … SELECT 语句从其他表复制数据。

1
2
3
4
-- 将productins表中的数据复制到product表中
INSERT INTO product (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 productins;

把productins表的内容复制到了product中：

image-20220712230108663

1.2.8 索引

  • 索引的作用

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引创建了一种有序的数据结构，采用二分法搜索数据时，其复杂度为 O(logN) ，1000多万的数据只要搜索23次，其效率是非常高效的。

  • 如何创建索引（两种方式）

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

1
2
3
4
5
6
7
8
9
10
-- 创建表时就建立索引
CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

也可以使用如下语句创建：

1
2
3
4
5
-- 方法1
CREATE INDEX indexName ON table_name (column_name)

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

练习题

1.1

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

表1-A　表 Addressbook （地址簿）中的列

image-20220712230844459

答：

1
2
3
4
5
6
7
CREATE TABLE Addressbook(
    regist_no INTEGER NOT NULL ,
    name VARCHAR(128) NOT NULL ,
    tel_no CHAR(10) ,
    mail_address CHAR(20),
    PRIMARY KEY (regist_no)
);

image-20220712231249778

1.2

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

列名 ： postal_code

数据类型 ：定长字符串类型（长度为 8）

约束 ：不能为 NULL

答：

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

image-20220712231436444

1.3 填空题

请补充如下 SQL 语句来删除 Addressbook 表。

1
(  DROP  ) table Addressbook;

1.4 判断题

是否可以编写 SQL 语句来恢复删除掉的 Addressbook 表？

答：不可以。ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。

参考资料

wonderful-sql 教程：https://github.com/datawhalechina/wonderful-sql/blob/main/ch01:%20%E5%88%9D%E8%AF%86%E6%95%B0%E6%8D%AE%E5%BA%93.md

SQL语法规范：https://github.com/datawhalechina/wonderful-sql/blob/main/materials/%E9%99%84%E5%BD%951%EF%BC%9ASQL%E8%AF%AD%E6%B3%95%E8%A7%84%E8%8C%83.md