数据分析之MySQL学习笔记

作者: shimo_chang | 来源:发表于2020-07-21 00:18 被阅读0次

MySQL 学习笔记

一、使用终端操作数据库

（一）、基本语句

1、登录数据库服务器

C:\Users\acer>mysql -u root -p    # 回车后输入密码即可登录

2、查询数据库服务器中所有的数据库

mysql> show databases;

3、如何选中一个数据库进行操作

mysql> select * from mysql;
ERROR 1046 (3D000): No database selected    # 当没有选择数据库就进行查询时会报错
mysql> use mysql;      # 选择数据库
Database changed

4、查看数据库中的表（table）

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| classes        |
| pet            |
| students       |
| testtype       |
| user           |
| user10         |
| user2          |
| user3          |
| user4          |
| user5          |
+----------------+
10 rows in set (0.00 sec)

5、查看表中的数据结构

mysql> desc pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (1.62 sec)

6、查看表中的数据字段

mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| puffball  | diane     | hamster | f    | 1999-03-30 | NULL  |
| 旺财狗    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| 猫咪      | 于洪      | 猫      | 母   | 2002-01-02 | NULL  |
+-----------+-----------+---------+------+------------+-------+
3 rows in set (1.65 sec)

7、退出数据库服务器

mysql> exit;    # 退出后需要重新登录MySQL
Bye

或者：

mysql> quit;    # 退出后需要重新登录MySQL
Bye

8、在数据库服务器里创建/删除一个数据库

mysql> create database test1;     #  创建
Query OK, 1 row affected (1.96 sec)

mysql> drop database test1;       #  删除
Query OK, 0 rows affected (0.33 sec)

9、确认当前所处的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

10、创建一个数据表（table）

mysql> create table pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date,
    -> death date);
ERROR 1050 (42S01): Table 'pet' already exists         
# 因为我的test数据库里已经有一个pet表了，所以不能重复table名，故而报错

注：删除table就是用drop对应create

11、插入/修改/删除数据（增删改查CRUD）

CRUD是指增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)
查看：

mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| 旺财狗    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| 猫咪      | 于洪      | 猫      | 母   | 2002-01-02 | NULL  |
+-----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

插入（增：创建table后要对表进行数据插入）：

mysql> insert into pet values ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
Query OK, 1 row affected (1.87 sec)

删除表中数据（删）：

mysql>delete from pet where name = "puffball";
Query OK, 2 rows affected (1.90 sec)

mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| 旺财狗    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| 猫咪      | 于洪      | 猫      | 母   | 2002-01-02 | NULL  |
+-----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

更新数据（改）：

mysql> update pet set name = "lala" where owner = "Diane";
Query OK, 1 row affected (1.80 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看结果：

mysql> select * from pet;
+-----------+-----------+---------+------+------------+-------+
| name      | owner     | species | sex  | birth      | death |
+-----------+-----------+---------+------+------------+-------+
| 旺财狗    | 周星驰    | 狗      | 公   | 1990-01-01 | NULL  |
| 猫咪      | 于洪      | 猫      | 母   | 2002-01-02 | NULL  |
| lala      | Diane     | hamster | f    | 1990-03-30 | NULL  |
+-----------+-----------+---------+------+------------+-------+
3 rows in set (0.00 sec)

12、常用的数据类型

MySQL支持多种类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串(字符)类型。

(1).总览

1 数值型：
    整型：int、tinyint
    小数：定点数(dec)，浮点数(float\double)
2 字符型：
    较短的文本：char、varchar
    较长的文本：text、blob（较长的二进制数据）
3 日期型
    日期+时间：datetime 
    时间戳：timestamp

(2).整型

分类：
            tinyint 、smallint、mediumint、int/integer、bigint
   字节数    1         2           3          4         8        

如何设置无符号和有符号
mysql> CREATE TABLE tab_int(
     >t1 INT,
     >t2 INT UNSIGNED)  #无符号（即无符号指的是正数和0，有符号指的是负数）

(3).浮点数

分类：
1、浮点型  float(M,D)  、  double(M,D)
2、定点型 dec(M,D)  decimal(M,D)
特点：
①  M：整数部位+小数部位  总的位数
   D：小数部位 的位数
      如果超过范围，则插入临界值
②   M和D都可以省略
        如果是decimal，则M默认是10，D默认是0
        如果是float和double，则会根据插入的数值的精度来决定精度
③   定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

原则：
所选择的类型越简单越好，能保存数值的类型越小越好
例：
mysql> INSERT INTO tab_float VALUES
(323.45,343.23,333.45),
(223.45,233.23,233.45);

(4).字符型

较短的文本：
     char: 存储字符数 0-255，定义时可省略长度，默认是1
     varchar：存储字符数 0-65535 ，定义时不可省略长度
区别：  
{1、char的长度是不可变的，varchar的长度是可变的，比如现在定义一个char[10]和varchar[10],现在往里边存储’csdn’四个字符，
此时char中存储的是’csdn’四个字符外还有六个空格，而varchar则是存储是’csdn’四个字符。
2、char的存储速度还是比varchar快，因为其长度固定，方便程序的存储于查找。
3、char类型对英文的存储的ASCII码占一个字节，汉子占两个字节；而varchar则是英文和汉字都是两个字节；
}

其他：
    binary 和 varbinary 用于保存较短的二进制
    enum用于保存枚举
    set用于保存集合
较长的文本：
    text
    blob（较大的二进制）

案例：
#创建表，只有一个枚举型字段，插入值的时候只能是枚举值，可以用来限定保存密码
mysql> CREATE TABLE tab_char(
    c1 ENUM('a','b','c')
);
mysql> INSERT INTO tab_char VALUES('a');#插入成功
mysql> INSERT INTO tab_char VALUES('d');#插入失败，插入的值必须是枚举的值

#创建集合
mysql> CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);
mysql> INSERT INTO tab_set VALUES('a');#√
mysql> INSERT INTO tab_set VALUES('a,c');#√
mysql> INSERT INTO tab_set VALUES('b,c,d');#√
mysql> INSERT INTO tab_set VALUES('a','b','e');#×，e不在集合之中

(5).日期型

分类：
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
timestamp 保存日期+时间

datetime  vs  timestamp

           字节          范围       是否受时区等影响I
datetime    8       1000-9999        不受
timestamp   4       1970-2038         受

（二）、建表约束

1、主键约束

使某个字段不重复且不得为空，确保表内所有数据的唯一性。

mysql>CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

联合主键
联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。

mysql>CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

自增约束
自增约束的主键由系统自动递增分配。

mysql>CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,       # 自增约束：AUTO_INCREMENT
    name VARCHAR(20)
);

添加主键约束
如果忘记设置主键，还可以通过SQL语句进行设置（两种方式）：

mysql>ALTER TABLE user ADD PRIMARY KEY(id);    #  没有设置时，增加一个主键
mysql>ALTER TABLE user MODIFY id INT PRIMARY KEY;    # 已经设置了，此时修改为int型，并设为主键

删除主键

mysql>ALTER TABLE user drop PRIMARY KEY;

2、唯一主键

-- 建表时创建唯一主键
mysql>CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
mysql>ALTER TABLE user ADD UNIQUE(name);
mysql>ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主键
mysql>ALTER TABLE user DROP INDEX name;    # 注意删除的是index

唯一主键UNIQUE和主键约束PRIMARY KEY 的区别:


              保证唯一性      是否允许为空        一个表中可以有多少个        能否组合

primary key      √               ×                  至多一个                √

unique           √               √                  可以有多个               √

3、非空约束

-- 建表时添加非空约束，约束某个字段不能为空

mysql>ACREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL          # 设置为非空的
);
-- 建表时添加非空约束
-- 约束某个字段不能为空
mysql>ACREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

4、默认约束

-- 建表时添加默认约束
-- 约束某个字段的默认值
mysql>CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10    # 年龄默认都是10岁;

-- 移除非空约束
mysql>ALTER TABLE user MODIFY age INT;

5、外键约束

-- 班级表
mysql>CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);
-- 学生表
mysql>CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),         
    class_id INT,                                     #  这里的 class_id 要和 classes 中的 id 字段相关联
    FOREIGN KEY(class_id) REFERENCES classes(id)      #  设置外键（班级表里面的id字段）
);                                                    #  表示 class_id 的值必须来自于 classes 中的 id 字段值

1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
2. 主表中的记录被副表引用时，主表不可以被删除。

（三）、建表三大范式

1、第一NF

只要字段值还可以继续拆分，就不满足第一范式。
如：杭州市西湖区 XX街道，可以拆分为市，区，街道三个字段
第一范式即为：尽量拆分字段

2、第二NF

在满足第一范式的前提下，其他列都必须"完全"依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：

mysql>CREATE TABLE myorder (
      product_id INT,
      customer_id INT,
      product_name VARCHAR(20),
      customer_name VARCHAR(20),
      PRIMARY KEY (product_id,customer_id)
);

然而这张表里，产品name只依赖产品id，消费者name只依赖消费者id，产品name和消费者id是没有关系的，因此这个不满足第二范式，没有体现“完全”

CREATE TABLE myorder (                      # 对订单表进行拆分
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

3、第三NF

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

可以发现，customer_phone字段依赖的字段可能式order_id 也可能是customer_id ，而只有
order_id式是主键，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。
进行拆分：

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,      
    name VARCHAR(20),
    phone VARCHAR(15)        # 使手机这个字段只依赖customer_id
);

（四）、查询语句

------不做单独写，具体练习看sql面试50题：B站面试50题

（五）、事务

在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账，a转给b一百元，执行下面的语句：

-- a -> -100       
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

但在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，那么就会出现数据前后不一致。在银行系统管理是非常严重的。

因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

接下来如何解决

1、控制事务

在 MySQL 中，事务有一个默认自动提交的状态，这个自动提交状态是默认开启的

-- 查询事务的自动提交状态
mysql> select @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

自动提交的作用：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能回滚，即不能撤回
举例：

mysql> CREATE DATABASE bank;   # 先创建一个库和表

mysql> USE bank;

mysql> CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

然后插入一条数据：

mysql> insert into user values(1,"a",1000);
Query OK, 1 row affected (0.22 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚：

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

可见，在事务状态是默认的情况下，sql语句一旦提交，就不能在回滚到提交前的状态了；

关闭自动提交：

-- 使用set 语句惊醒修改设置：
mysql> set AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@AUTOCOMMIT;    #  可见autocommit已经被修改为 0 
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

此时，再试一下回滚：

mysql> insert into user values(2,"b",1000);
Query OK, 1 row affected (0.00 sec)     # 插入一条数据
-- 设置 AUTOCOMMIT = 0 后，数据的变化是在一张虚拟的临时数据表中展示，
-- 发生变化的数据并没有真正插入到数据表中。

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

-- 数据表中的真实数据其实还是：
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+



mysql> rollback;            # 回滚
Query OK, 0 rows affected (1.85 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+        #  可见，回滚成功
1 row in set (0.00 sec)

在AUTOcommit = 0的状态下，写完一个事务后使用commit即可提交事务，之后便不能再回滚了

mysql> select @@AUTOCOMMIT;      # 查询事务自动提交状态：是关闭状态
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+
1 row in set (0.50 sec)

mysql> insert into user values(2,"b",1000);    # 此时插入一条数据；
Query OK, 1 row affected (1.79 sec)

mysql> commit;                    # 提交插入语句
Query OK, 0 rows affected (0.29 sec)

mysql> rollback;                  # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.13 sec)
                    #    可见并没有产生回滚，通过提交commit手动提交了事务

总结
1、自动提交

查看自动提交状态：select @@AUTOCOMMIT；
设置自动提交状态：set AUTOCOMMIT = 1;

2、手动提交

设置手动提交：set AUTOCOMMIT = 0;
手动提交状态时@@AUTOCOMMIT = 0，当执行完事务语句后，要使用commit命令提交事务
在手动提交状态下，没有使用commit命令前，可以使用rollback命令来实现事务回滚

2、临时开启事务手动提交

在@@AUTOCOMMIT = 1时，我们可以通过输入begin或者start transaction来实现临时开启事务手动提交，使其发生回滚：


mysql> set autocommit = 1;
Query OK, 0 rows affected (1.74 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
mysql> begin;           # 开启临时手动提交
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE user set money = money - 100 WHERE name = 'a';
Query OK, 1 row affected (1.80 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE user set money = money + 100 WHERE name = 'b';   @ 更新两条数据
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM user;      
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)     
-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。

-- 测试回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (1.28 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)    # 回滚成功！！！！！

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 提交数据
COMMIT;
-- 提交回滚
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+
2 rows in set (0.00 sec)      # 没有撤销成功

3、事务的ACID特征和使用

1、事务的四大特征：

A 原子性：事务是最小的单位，不可以再分割；
C 一致性：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；
I 隔离性：事务1 和事务2 之间是具有隔离性的；
D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。

上面的已经写过ACD这三部分了，接下来写隔离性：

2、事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

READ UNCOMMITTED ( 读取未提交 )
如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
READ COMMITTED ( 读取已提交 )
只能读取到其他事务已经提交的数据。
REPEATABLE READ ( 可被重复读 )
如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
SERIALIZABLE ( 串行化 )
所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

-- MySQL 8.x, GLOBAL 表示系统级别，不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别：

-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)


-- 查询系统隔离级别，发现已经被修改。
mysql> SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)
-- 其他的隔离设置类似