Task01:环境搭建,初识数据库
0. 教程地址
https://github.com/datawhalechina/wonderful-sql
https://gitee.com/datawhalechina/wonderful-sql
1. 环境搭建
1.1 安装数据库
ubuntu@VM-0-12-ubuntu:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04 LTS
Release: 20.04
Codename: focal
ubuntu@VM-0-12-ubuntu:~$
先来看一下系统信息,我这个是腾讯云服务器上面的Ubuntu 20.04
然后之前在做Juicy Big Data
中Hive
部分的时候,已经安装过MySQL
了,大概过程如下:
master@VM-0-12-ubuntu:/opt$ sudo apt update
Hit:1 http://mirrors.tencentyun.com/ubuntu focal InRelease
Get:2 http://mirrors.tencentyun.com/ubuntu focal-security InRelease [114 kB]
Get:3 http://mirrors.tencentyun.com/ubuntu focal-updates InRelease [114 kB]
Fetched 228 kB in 1s (364 kB/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
97 packages can be upgraded. Run 'apt list --upgradable' to see them.
master@VM-0-12-ubuntu:/opt$ sudo apt install mysql-server
···
done!
update-alternatives: using /var/lib/mecab/dic/ipadic-utf8 to provide /var/lib/mecab/dic/debian (mecab-dictionary) in auto mode
Setting up mysql-server (8.0.28-0ubuntu0.20.04.3) ...
Processing triggers for systemd (245.4-4ubuntu3.15) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.2) ...
master@VM-0-12-ubuntu:/opt$ sudo netstat -tap | grep mysql
tcp 0 0 localhost:33060 0.0.0.0:* LISTEN 663796/mysqld
tcp 0 0 localhost:mysql 0.0.0.0:* LISTEN 663796/mysqld
master@VM-0-12-ubuntu:/opt$ systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-03-21 19:55:54 CST; 56s ago
Main PID: 663796 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 2265)
Memory: 362.6M
CGroup: /system.slice/mysql.service
└─663796 /usr/sbin/mysqld
master@VM-0-12-ubuntu:/opt$
其实还有一个更优雅的方式去查看运行状态,如下:
ubuntu@VM-0-12-ubuntu:~$ service mysql status
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2022-05-11 22:30:31 CST; 3 days ago
Process: 664 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 865 (mysqld)
Status: "Server is operational"
Tasks: 46 (limit: 2265)
Memory: 483.6M
CGroup: /system.slice/mysql.service
└─865 /usr/sbin/mysqld
May 11 22:30:27 VM-0-12-ubuntu systemd[1]: Starting MySQL Community Server...
May 11 22:30:31 VM-0-12-ubuntu systemd[1]: Started MySQL Community Server.
ubuntu@VM-0-12-ubuntu:~$
我在查看Redis
和MongoDB
的时候也是用的这个格式的命令,当然安装的时候也是用的apt
命令
然后关于MySQL
的账号部分,我并没有进行其他设置,一直用的都是root
账户和空密码
1.2 创建数据库
ubuntu@VM-0-12-ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 650
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
先使用命令登录数据库,然后再执行sql
文件
mysql> source /home/ubuntu/shop.sql;
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.07 sec)
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.02 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 13 rows affected (0.01 sec)
Records: 13 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
mysql>
我之前查的博客好像有说可以直接输入sql
文件的路径,但是显然会报错
mysql> /home/ubuntu/shop.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/home/ubuntu/shop.sql' at line 1
1.3 查看数据库
大概看一下创建结果吧
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hive_metadata |
| information_schema |
| mysql |
| performance_schema |
| shop |
| sys |
| toyrec |
+--------------------+
7 rows in set (0.01 sec)
mysql> use shop;
Database changed
mysql> show tables;
+------------------+
| Tables_in_shop |
+------------------+
| chars |
| empskills |
| inventoryproduct |
| product |
| product2 |
| productcopy |
| productins |
| producttype |
| samplelike |
| samplemath |
| samplestr |
| shopproduct |
| skills |
| view_product |
+------------------+
14 rows in set (0.00 sec)
mysql> desc chars;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| chr | char(3) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> desc product;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | NULL | |
| purchase_price | int | YES | | NULL | |
| regist_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
啊,最后,当然还是要退出数据库的连接
mysql> exit;
Bye
ubuntu@VM-0-12-ubuntu:~$ exit
logout
Connection closing...Socket close.
Connection closed by foreign host.
Disconnected from remote host(Tencent Cloud Server) at 15:28:48.
2. 初识数据库
2.1 初识数据库
2.1.1 DBMS的种类
-
层次数据库
层次(Hierarchical)数据用的是树模型的结构,那都是很早以前的事情了,其后续被关系型数据库所替代 -
关系数据库
这个应该不用多说了,应用非常广泛,它的前身还有网状数据库 -
面向对象数据库
好像也是很久之前的东西了,我感觉跟 ORM 差不多的意思。与之相似的可能是 MongoDB 这种文档数据库,每一条文档就是一个记录 -
XML 数据库
感觉也挺冷门的,查资料说便于存储网页这样的结构,因为同为 XML 格式。 -
键值存储系统
教程中给的示例是 MongoDB 数据库,因为其有一个类似于主键的字段 id 来唯一标识记录,看上去就好像是根据这个 Key 找到这个记录。我感觉或许更加符合这个称号的,是 Redis 数据库。
2.1.2 DBMS的常见系统结构
一般就是客户端/服务器( C/S 类型)结构吧。我记得之前想把整个文件导入到 MySQL 当中,但是提示权限错误,客户端和服务器端都需要授权才行。当然最后担心格式错误,还是用 Python 逐行导入了。
2.2 初识 SQL
SQL 的语句大概分为以下三类:
-
DDL(Data Definition Language,数据定义语言)
-
CREATE
-
DROP
-
ALTER
-
-
DML(Data Manipulation Language,数据操纵语言)
-
SELECT
-
INSERT
-
UPDATE
-
DELETE
-
-
DCL(Data Control Language,数据控制语言)
-
COMMIT
-
ROLLBACK
-
GRANT
-
REVOKE
-
实际上来说用的最多的还是 DML
2.2.1 SQL的基本书写规则
-
SQL 语句要以英文号结尾(;)
-
SQL 的关键字不区分大小写,但是推荐在代码当中统一使用大写格式
-
Win 默认不区分表名和字段名的大小写,但是Linux/Mac 默认严格区分
-
单词与单词之间一般使用半角空格隔开,也可以用换行
2.2.2 数据库的创建
CREATE DATABASE < 数据库名称 > ;
# 注意末尾需要带有分号
2.2.3 表的创建
CREATE TABLE < 表名 >
( < 列名 1> < 数据类型 > < 该列所需约束 > ,
< 列名 2> < 数据类型 > < 该列所需约束 > ,
< 列名 3> < 数据类型 > < 该列所需约束 > ,
< 列名 4> < 数据类型 > < 该列所需约束 > ,
.
.
.
< 该表的约束 1> , < 该表的约束 2> ,……);
2.2.4 命名规则
只能使用半角英文字母、数字、下划线( _ )作为数据库、表和列的名称,而名称必须以半角英文字母开头。
在设置这些名称的时候,尽量使用有实际含义的单词
2.2.5 数据类型
- INTEGER 型
只能存储数字类型的整数,不能存小数 - CHAR 型
定长的字符串类型,长度不足的回用半角空格补齐,可能会浪费空间 - VARCHAR 型
变长的字符串类型,不会补足长度 - DATE 型
指定存储日期格式
2.2.6 约束设置
NOT NULL 是非空约束,即该列必须输入数据。
PRIMARY KEY 是主键约束,代表该列是唯一值,可以通过该列取出特定的行的数据。
2.2.7 表的删除和更新
-
删除表
DROP TABLE < 表名 > ;
-
修改表
- 添加列
ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;
- 删除列
ALTER TABLE < 表名 > DROP COLUMN < 列名 >;
- 添加列
-
删除行
-- 一定注意添加 WHERE 条件,否则将会删除所有的数据 DELETE FROM product WHERE COLUMN_NAME='XXX';
ALTER TABLE 语句和 DROP TABLE 语句一样,执行之后无法恢复。
-
清空表
TRUNCATE TABLE TABLE_NAME;
相比drop / delete,truncate用来清除数据时,速度最快。
-
更新表
# 使用 update 时要注意添加 where 条件,否则将会将所有的行按照语句修改 UPDATE <表名> SET <列名> = <表达式> [, <列名2>=<表达式2>] ...; WHERE <条件>; # 可选,非常重要 ORDER BY 子句; #可选 LIMIT 子句; #可选
2.2.8 插入数据
-
插入行
INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);
-
复制表
# 将商品表中的数据复制到商品复制表中 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;
2.2.9 索引
-
作用
索引创建了一种有序的数据结构,采用二分法搜索数据时,其复杂度为 l o g 2 ( N ) log_2(N) log2(N) ,1000 多万的数据只要搜索 23 次,其效率是非常高效的。增加索引就是增加MySQL查询数据的途径,但是也不能过多地添加索引。 -
创建索引
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)
-
索引分类
- 主键索引
建立在主键上的索引,在创建表的时候创建,不允许有空值 - 唯一索引
建立在UNIQUE字段上,允许多个唯一索引,允许为索引值为空 - 普通索引
建立在普通字段上 - 前缀索引
仅对字符类型的前几个字符或者二进制类型的前几个 bytes 建立索引,节省存储空间 - 全文索引
利用“分词技术”实现在长文本中搜索关键字,建议插入所有数据后再创建全文索引,否则索引会随着新数据的插入而不断更新 - 单列索引
建立在单个列上的索引被称为单列索引 - 联合索引
建立在多个列上的索引被称为联合索引,又叫复合索引、组合索引
- 主键索引
-
索引的使用
select `login_name` from `user_demo` where `age` + 10 = 30; --不会走索引,因为所有索引列参与了计算 select `login_name` from `user_demo` where left(`age`, 1) < 3; --不会使用索引,因为使用了函数运算,原理与上面相同 # left 返回具有指定长度的字符串的左边部分 select * from `user_demo` where `login_name` like "Jack%"; -- 走索引 # 感觉像是前缀索引 select * from `user_demo` where `login_name` like "%Jack%"; -- 不走索引
好像不用特地标明从索引当中查找,同样还是使用 select 语句
关于索引详细的用法见博客1,感觉有必要抽空仔细看看
3. 练习题
3.1 创建表
CREATE TABLE Addressbook
(
register_no INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
address VARCHAR(256) NOT NULL,
tel_no CHAR(10) ,
mail_address CHAR(20) ,
PRIMARY KEY (register_no)
);
命令行的操作如下:
ubuntu@VM-0-12-ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 656
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hive_metadata |
| information_schema |
| mysql |
| performance_schema |
| shop |
| sys |
| toyrec |
| ztc |
+--------------------+
8 rows in set (0.00 sec)
mysql> use ztc
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE Addressbook
-> (
-> register_no INTEGER NOT NULL,
-> name VARCHAR(128) NOT NULL,
-> address VARCHAR(256) NOT NULL,
-> tel_no CHAR(10) ,
-> mail_address CHAR(20) ,
-> PRIMARY KEY (register_no)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc Addressbook;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| register_no | int | NO | PRI | NULL | |
| name | varchar(128) | NO | | NULL | |
| address | varchar(256) | NO | | NULL | |
| tel_no | char(10) | YES | | NULL | |
| mail_address | char(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql>
3.2 修改表
ALTER TABLE Addressbook
ADD COLUMN postal_code CHAR(8) NOT NULL;
命令行操作如下
mysql> desc Addressbook;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| register_no | int | NO | PRI | NULL | |
| name | varchar(128) | NO | | NULL | |
| address | varchar(256) | NO | | NULL | |
| tel_no | char(10) | YES | | NULL | |
| mail_address | char(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE Addressbook
-> ADD COLUMN postal_code CHAR(8) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc Addressbook;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| register_no | int | NO | PRI | NULL | |
| name | varchar(128) | NO | | NULL | |
| address | varchar(256) | NO | | NULL | |
| tel_no | char(10) | YES | | NULL | |
| mail_address | char(20) | YES | | NULL | |
| postal_code | char(8) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
3.3 删除表
drop table Addressbook;
清空表是:
truncate table Addressbook;
即保留表结构,但是不保留记录
删除数据是:
delete table Addressbook [where];
即逐行删除符合条件的记录,条件为空就是逐行删除所有记录
3.4 恢复表
上面删除的表不能够被恢复