Mysql基础

I thank thee that I am none of the wheels of power but I am one with the living creatures that are crushed by it.

谢谢神，我不是一个权力的轮子，而是被压在这轮子下的活人之一。

数据库

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

• 数据以表格的形式出现
• 每行为各种记录名称
• 每列为记录名称所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成database

RDBMS 术语

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据。
• 行：一行（=元组，或记录）是一组相关的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

• 表头(header): 每一列的名称;
• 列(col): 具有相同数据类型的数据的集合;
• 行(row): 每一行用来描述某条记录的具体信息;
• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
• 键(key): 键的值在当前列中具有唯一性。

Mysql安装与卸载

本来网上对Mac Mysql的介绍就参差不齐，换了M1MAX之后更找不到好的教程！

功夫不负有心人，还是被我找到了！

亲测可用！

Mac

安装

B站高人的视频

卸载

可以彻底删除Mac的所有残留Mysql文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14

打开终端：
sudo su

执行代码：
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
rm -rf ~/Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*

即可彻底卸载

配置

/Users/你的用户名/.zshrc

1
2
3
4
5
6

#Mysql
alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop'
alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start'
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin
export PATH=$PATH:/usr/local/mysql/bin

命令行操作Mysql

• 检查MySQL服务器是否启动

  1	ps -ef | grep mysqld

• 启动与停止Mysql

  1 2	mysqlstart mysqlstop

• 进入Mysql

  1	mysql -uroot -p

• 进入Mysql之后

  所有的语句都要使用;结尾

  1 2 3 4

  ---单行注释 /* 多行注释 */

• 刷新权限

  1	mysql> flush privileges;

• 修改密码

  1 2 3

  mysql> use mysql; mysql> FLUSH PRIVILEGES; mysql> ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassward'

• 列出 MySQL 数据库管理系统的数据库列表

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | DB01 | | information_schema | | jdbc | | mybatis | | mysql | | performance_schema | | smbms | | ssmbuild | | sys | +--------------------+ 9 rows in set (0.00 sec)

• 选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库

  1 2	mysql> use smbms; Database changed

• 显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库

  1 2 3 4 5 6 7 8 9 10 11

  mysql> SHOW TABLES; +-----------------+ | Tables_in_smbms | +-----------------+ | smbms_address | | smbms_bill | | smbms_provider | | smbms_role | | smbms_user | +-----------------+ 5 rows in set (0.00 sec)

• 显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  mysql> SHOW COLUMNS FROM smbms_address; +--------------+-------------+------+-----+---------+---------------- | Field | Type | Null | Key | Default | Extra +--------------+-------------+------+-----+---------+---------------- | id | bigint | NO | PRI | NULL | auto_increment | contact | varchar(15) | YES | | NULL | | addressDesc | varchar(50) | YES | | NULL | | postCode | varchar(15) | YES | | NULL | | tel | varchar(20) | YES | | NULL | | createdBy | bigint | YES | | NULL | | creationDate | datetime | YES | | NULL | | modifyBy | bigint | YES | | NULL | | modifyDate | datetime | YES | | NULL | | userId | bigint | YES | | NULL | +--------------+-------------+------+-----+---------+---------------- 10 rows in set (0.00 sec)

• 显示数据表的详细索引信息，包括PRIMARY KEY（主键）

  1	mysql> SHOW INDEX FROM smbms_address;

• 创建数据库

  1	mysql> CREATE DATABASE [IF NOT EXISTS] 数据库名;

• 删除数据库

  1	mysql> drop database [IF EXISTS] 数据库名;

• 创建Mysql数据表

  1	mysql> CREATE TABLE table_name (column_name column_type);

• 删除MySQL数据表

  1	mysql> DROP TABLE [IF EXISTS] `表名` ;

• 向MySQL数据表插入数据

  1 2 3

  mysql> INSERT INTO `表名` ( `字段1`, `字段2`,...`字段N` ) VALUES ( value1, value2,...valueN );

• 在MySQL数据库中查询数据

  1 2 3 4 5 6 7

  SELECT[ALL|DISTINCT|DISTINCTROW|TOP] {*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]} FROM tableexpression[,…][IN externaldatabase] [WHERE…] [GROUP BY…] [HAVING…] [ORDER BY…]

> - 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
> - SELECT 命令可以读取一条或者多条记录。
> - 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
> - 你可以使用 WHERE 语句来包含任何条件。
> - 你可以使用 LIMIT 属性来设定返回的记录数。
> - 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
> - 可以将使用`CONCAT("拼接的字符串：" ，字段1)` 查询出来的结果将被拼接字符串

* SQL SELECT 语句使用 DISTINCT 将读取出来的数据去重

  
1
2
3
4
mysql> SELECT DISTINCT `字段1` 
FROM `表名`
[WHERE Clause]
[LIMIT 查询起始下标,pageSize][ OFFSET M];


* SQL SELECT 语句使用 WHERE 子句从数据表中读取数据

  
1
2
mysql> SELECT `字段1`, `字段2`,...`字段N` FROM `表名1`, `表名2`...
[WHERE condition1 [AND [OR]] condition2.....


* SQL SELECT 语句使用 LIKE 子句从数据表中读取数据

  
1
2
3
mysql> SELECT `字段1`, `字段2`,...`字段N` 
FROM `表名`
WHERE `字段1` LIKE condition1 [AND [OR]] `字段2` = 'somevalue'


  `%`代表0～任意个字符  `__`代表一个字符

* MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中

  
1
2
3
4
5
6
7
mysql> SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];


* SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据

  
1
2
mysql> SELECT `字段1`, `字段2`,...`字段N` FROM `表名1`, `表名2`...
ORDER BY `字段1` [ASC [DESC][默认 ASC]], [`字段2...`] [ASC [DESC][默认 ASC]


* GROUP BY 语句根据一个或多个列对结果集进行分组

  
1
2
3
4
mysql> SELECT `字段`, function(`字段`)
FROM table_name
WHERE `字段` operator value
GROUP BY column_name;

• UPDATE 命令修改 MySQL 数据表数据

  1 2	mysql> UPDATE `表名` SET `字段1`='new-value1', `字段2`='new-value2' [WHERE Clause]

• SQL DELETE 语句从 MySQL 数据表中删除数据

  1	mysql> DELETE FROM `表名` [WHERE Clause]

  TRUNCATE完全清空一个数据库表，表的结构和索引约束不会变！

  1	mysql> TRUNCATE `表名`

  • 相同点：都能删除数据，不会删除表结构
  • 不同点：
    • TRUNCATE 重新设置自增列，计数器会归零
    • TRUNCATE不会影响事务

• 修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令

  • 修改表名

    1	mysql> ALTER TABLE `旧表名` RENAME TO `新表名`;

  • 删除，添加或修改表字段

    1	mysql> ALTER TABLE `表名` DROP `字段`;

    1	mysql> ALTER TABLE 表`名` ADD `字段` 字段数据类型;

    • 如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

      1 2	mysql> ALTER TABLE `表名` ADD `字段` INT FIRST; mysql> ALTER TABLE `表名` ADD `字段A` INT AFTER `字段B`;

    1 2	mysql> ALTER TABLE `表名` MODIFY `字段` 新字段数据类型; mysql> ALTER TABLE `表名` CHANGE `旧字段名` `新字段名` 新字段数据类型;

  • 修改存储引擎

    1	mysql> alter table `表名` engine=myisam;

  • 添加外键约束

    1	mysql> ALTER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `被引用的表名`(`被引用表的字段`)

  • MySQL 连接的使用

    • INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
    • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
    • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

• 退出Mysql

  1 2	mysql> exit Bye

Mysql

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

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

字符串类型

• char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数
• CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
• BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
• BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
• 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

日期和时间类型

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值

MySQL 索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引：这是最基本的索引，它没有任何限制。

1

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

• 修改表结构(添加索引)

1
2
3
4
5
6
7
8

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
#该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
#这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
#添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
#该语句指定了索引为 FULLTEXT ，用于全文索引。

• 创建表的时候直接指定

1
2
3
4
5

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

• 删除索引的语法

1

DROP INDEX [indexName] ON mytable; 

主键索引：

1

PRIMARY KEY

唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

1

UNIQUE KEY

全文索引：

1

FULLTEXT

MySQL字段属性

• 表的名称和字段尽量使用 ` `括起来
• 字符串使用单引号''括起来
• 所有的语句后面 ,,最后一个不用加

Mysql数据库引擎

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的**操作系统上
4. InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5. InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物务。MyISAM主要特性有：

1. 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2. 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3. 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4. 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5. BLOB和TEXT列可以被索引
6. NULL被允许在索引的列中，这个值占每个键的0~1个字节
7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8. 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9. 可以把数据文件和索引文件放在不同目录
10. 每个字符列可以有不同的字符集
11. 有VARCHAR的表可以固定或动态记录长度
12. VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：.frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：

1. MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
2. MEMORY存储引擎执行HASH和BTREE缩影
3. 可以在一个MEMORY表中有非唯一键值
4. MEMORY表使用一个固定的记录长度格式
5. MEMORY不支持BLOB或TEXT列
6. MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7. MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
8. MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
9. 当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

MySQL 运算符

比较运算符

算术运算符

逻辑运算符

事务

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

关系数据库中的几种设计范式

1 第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF） 是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。

简而言之，第一范式就是无重复的列。

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。

简而言之，第二范式就是非主属性完全依赖于主关键字。

3 第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性。