Skip to content

入门

https://db-engines.com/en/ranking

https://github.com/jobbole/awesome-mysql-cn

https://notes.diguage.com/mysql/
https://github.com/diguage/mysql-notes

https://github.com/zhangdejian/MySQL_Refinement_Resource

简介

MySQL 是一个小型关系数据库管理系统。
与其他大型数据库管理系统(例如 Oracle、DB2、SQL Server 等)相比，MySQL 规模小、功能有限，但是它体积小、速度快、成本低，并且提供的功能对稍微复杂的应用来说已经够用，这些特性使用 MySQL 成为世界上最受欢迎的开放源代码数据库

SQL 语言语句

DDL（Data Definition Language）数据库定义语言statements are used to define the database structure or schema.

DDL是SQL语言的四大功能之一。
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.
CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME

DML（Data Manipulation Language）数据操纵语言statements are used for managing data within schema objects.

由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.
SELECT
INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

DCL（Data Control Language）数据库控制语言 授权，角色控制等
GRANT 授权
REVOKE 取消授权

TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点
ROLLBACK 回滚
SET TRANSACTION

SQL主要分成四部分：
（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。

客户单/服务器软件

主从式架构(Client-Server Model)或客户端/服务器(Client/Server)结构(简称 C/S 结构)，是一种网络架构，通常在该网络架构下的软件可分为客户端(Client)和服务器(Server)

服务器是整个应用系统资源的存储与管理中心，多个客户端则各自处理相应的功能，共同实现完整的应用。
在客户端/服务器结构中，客户端用户的请求被传送到数据库服务器，数据库服务器进行处理后，将结果返回给用户，从而减少了网络数据传输量。

用户使用应用程序时，首先启动客户端通过有关命令告知服务器进行连接以完成各种操作，而服务器则按照此请示提供相应的服务。
每一个客户端软件的实例都可以向一个服务器或应用程序服务器发出请求。

MySQL 版本

针对不同用户，MySQL 分为两个不同的版本:

  • MySQL Community Server(社区服务器): 该版本完全免费，但是官方不提供技术支持
  • MySQL Enterprise Server(企业版服务器): 能够以很高的性价比为企业提供数据仓库应用，支持 ACID 事物处理，提供完整的提交、回滚、崩溃恢复和行级锁定功能。但是该版本需付费使用，官方提供电话技术支持

MySQL 的命名机制由 3 个数字和 1 个后缀组成，例如 MySQL-8.0.13 版本

第一个数字 (8) 是主版本号，描述了文件格式，所有版本 8 的发行版都有相同的文件格式
第二个数字 (0) 是发行级别，主版本号和发行级别组合在一起便构成了发行序列号
第三个数字 (13) 是在此发行系列的版本号，随每次新分发版本递增。通常选择已经发行的最新版本。

在 MySQL 开发过程中，同时存在多个发布系列，每个发布处在成熟度的不同阶段

MySQL 的优势

MySQL 的主要优势如下:
(1) 速度: 运行速度快
(2) 容易使用: 与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习
(3) 可移植性: 能够工作在众多不同的系统平台上
(4) 丰富的接口: 提供了很多种语言的 API
(5) 支持查询语言: MySQL 可以利用标准 SQL 语法和支持 ODBC 的应用程序
(6) 安全性和连接性: 十分灵活和安全的权限和密码系统，允许基于主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码安全。由于 MySQL 是网络化的，因此可以在因特网上的任何地方访问，提供数据共享的效率。

MySQL 工具

MySQL 数据库管理系统提供了许多命令行工具，这些工具可以用来管理 MySQL 服务器、对数据库进行访问控制、管理 MySQL 用户以及数据库备份和恢复工具等。

命令行实用程序

mysqld:
SQL 后台程序(MySQL 服务器进程)。必须在该程序运行之后，客户端才能通过连接服务器来访问数据库。

MySQL 8.0的新特性简介

和 MySQL 5.7 相比，MySQL 8.0 的新特性主要包括以下几个方面

数据字典

MySQL 8.0 包含一个事务数据字典，用于存储有关数据库对象的信息。
在 MySQL 8.0 之前的版本中，字典数据存储在元数据文件和非事务表中。

字符集支持

默认字符集已经更改 latin1 为 utf8mb4。该 utf8mb4 字符集有几个新的排序规则，其中包括 utf8mb4_ja_0900_as_cs

查询的优化

MySQL 8.0 在查询方面的优化表现如下:

(1) MySQL 8.0 开始支持不可见索引。优化器根本不使用不可见索引，但会以其他方式正常维护。默认情况下，索引是可见的。通过不可见索引，数据库管理员可以检测索引对查询性能的影响，而不会进行破坏性的更改。

(2) MySQL 8.0 开始支持降序索引，DESC 在索引定义中不再被忽略，而且会降序存储索引字段

安装

ubuntu

1
2
3
4
sudo apt-get update -y
sudo apt-get install mysql-server -y
sudo apt-get install mysql-client -y
sudo apt-get install libmysqlclient-dev -y

1
sudo service mysql start

设置密码: 

1
2
sudo mysql -u root
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

非root用户安装

首先下载安装包:

https://dev.mysql.com/downloads/mysql/

http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/

1
2
wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-8.0.23.tar.gz
tar -zxvf mysql-8.0.23.tar.gz

mac

安装包可视化安装:

https://dev.mysql.com/downloads/mysql/

安装的时候不要选 strong password

然后在~/.zshrc中添加如下内容: 

1
export PATH="$PATH":/usr/local/mysql/bin

执行

1
2
3
4
5
$ source ~/.zshrc
$ mysql --version
mysql  Ver 8.0.23 for macos10.15 on x86_64 (MySQL Community Server - GPL)
$ mysql -u root -p
Enter password:

1
2
$ mysql --version
mysql  Ver 8.0.22 for osx10.15 on x86_64 (Homebrew)

mysqld 为 mysql 服务器或者称之为服务器上的实例，用于提供客户端访问。
mysql 为客户端访问管理工具。

centos

1
2
3
4
5
6
$ lsb_release -a
LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description:    CentOS Linux release 7.9.2009 (Core)
Release:    7.9.2009
Codename:   Core

下载安装包:

https://dev.mysql.com/downloads/mysql/

  • Select Version: 8.0.36
  • Select Operating System: Red Hat Enterprise Linux / Oracle Linux
  • Select OS Version: Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit)

下载 RPM Bundle 包

mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar

安装包 1个G 左右，还是挺大的

把他上传到服务器: 

1
scp ~/Downloads/mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar buyaoxiangcai:/root/storage

卸载系统自带 MarlaDB: 

1
2
3
yum list installed | grep mariadb && yum list installed | grep mysql
mariadb-libs.x86_64                   1:5.5.68-1.el7                 @anaconda
yum -y remove mariadb-libs.x86_64 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
(base) ➜  storage mkdir mysql
(base) ➜  storage cd mysql
(base) ➜  mysql mv ../mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar .

tar -xf mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar
ls
mysql-community-client-8.0.36-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.36-1.el7.x86_64.rpm
mysql-community-common-8.0.36-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.36-1.el7.x86_64.rpm
mysql-community-devel-8.0.36-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.36-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.36-1.el7.x86_64.rpm
mysql-community-libs-8.0.36-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.36-1.el7.x86_64.rpm
mysql-community-server-8.0.36-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.36-1.el7.x86_64.rpm
mysql-community-test-8.0.36-1.el7.x86_64.rpm

rpm -ivh mysql-community-common-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-debuginfo-8.0.36-1.el7.x86_64.rpm

yum install openssl-devel -y
rpm -ivh mysql-community-devel-8.0.36-1.el7.x86_64.rpm

yum install libaio -y
rpm -ivh mysql-community-embedded-compat-8.0.36-1.el7.x86_64.rpm

rpm -ivh mysql-community-icu-data-files-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-debug-8.0.36-1.el7.x86_64.rpm

yum -y install autoconf
yum install perl-JSON.noarch -y
yum install perl-Test-Simple -y
rpm -ivh mysql-community-test-8.0.36-1.el7.x86_64.rpm

修改数据库目录与文件（默认/var/lib/mysql下）的所有者为mysql用户：

mysqld --initialize --user=mysql

1
2
grep "password" /var/log/mysqld.log
2024-01-20T01:23:36.819550Z 1 [Note] A temporary password is generated for root@localhost: =UTuh6dsmJrO

密码: =UTuh6dsmJrO

启动 mysql 服务: 

1
2
service mysqld start
# Redirecting to /bin/systemctl start mysqld.service

登录 mysql: 

1
2
3
4
5
6
7
mysql -uroot -p
# Enter password:
# ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

mysql -h localhost -uroot -p
# Enter password:
# ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

这是由于我们连接数据库使用的主机名参数为“localhost”，或者未使用主机名参数、服务器默认使用“localhost”做为主机名。 使用主机名参数为“localhost”连接mysql服务端时，mysql客户端会认为是连接本机，所以会尝试以socket文件方式进行连接(socket文件连接方式，比“ip：端口”方式效率更高)，这时根据配置文件“/etc/mysql.cnf”的路径，未找到相应的socket文件，就会引发此错误。

使用 -h 127.0.0.1 来连接: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
mysql -h 127.0.0.1 -uroot -p
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 12
# Server version: 8.0.36

# Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

# 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>

启动关闭

Mac

Mac 上启动关闭重启 mysql: 

1
2
3
brew services start mysql
brew services stop mysql
brew services restart mysql

注意以brew services start方式启动的，需要以brew services stop方式关闭

卸载mysql: 

1
2
3
brew remove mysql
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /private/var/db/receipts/mysql

一般方式

关闭mysql: 

1
2
mysqladmin -uroot -p  shutdown
Enter password:

简易使用

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
➜  Desktop mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.16 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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>

本地电脑 mysql 密码: 123456

行

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。如果将表想像成网格，网格中垂直的列为表列，水平行为表行

行：表中的一个记录

主键

表中每一行都应该有可以唯一标识自己的一列(或一组列)。一个顾客表可以使用顾客编号列，而订单表可以使用订单ID，雇员表可以使用雇员ID或雇员社会保险号

主键：一列(或一组列)，其值能够唯一区分表中的每个行

主键的最好习惯

  • 不更新主键列中的值
  • 不重用主键列的值
  • 不在主键列中使用可能会更改的值(例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键)

使用Mysql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create database db_name character set utf8; 创建数据库
show databases; 查看数据库列表
show create database t2; 显示创建 t2 的指令
alter database t1 character set utf8; 修改数据库 t1 的编码方式
drop database t1; 删除 t1 数据库

show warnings; 查看警告信息

use 数据库名字; 打开数据库

select database(); 显示当前打开的数据库

create table tb1 ( 创建数据表
    username varchar(20), 列名称和数据类型
    age tinyint unsigned,
    salary float(8, 2) unsigned
)
show tables [from db_name]; 查看数据表
drop table users; 删除数据表

show columns from tbl_name; 查看数据表结构

insert tbl_name [(col_name,...)] values(val,...); 插入记录
insert tb1 values('Tom', 25, 7863.25);
insert tb1(username, salary) values('John', 4500.69);

show create table provinces; 显示创建数据表的指令
show indexes from provinces; 显示数据表的索引

检索数据

1
2
3
4
5
6
7
8
SELECT prod_name FROM products; 检索单个列
SELECT prod_id, prod_name, prod_price FROM products; 检索多个列
SELECT * FROM products; 检索所有列
SELECT DISTINCT vend_id FROM products; 检索不同的行
SELECT prod_name FROM products LIMIT 5; 返回不多于 5 行

返回从行 5 开始的 5 行。第一个数为开始位置，第二个数为要检索的行数
SELECT prod_name FROM products LIMIT 5, 5;

排序检索数据

1
2
3
4
5
6
7
8
9
SELECT prod_name FROM products ORDER BY prod_name;

按多个列排序
SELECT prod_id, prod_price, prod_name 
FROM products ORDER BY prod_price, prod_name; 

指定排序方向
SELECT prod_id, prod_price, prod_name 
FROM products ORDER BY prod_price DESC;

过滤数据

WHERE 子句操作符

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50

列出价格小于 10 的所有产品
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10;

列出不是由供应商 1003 制造的所有产品
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;

检索价格在 5 和 10 之间的所有产品
SELECT prod_name, prod_price
FROM products 
WHERE prod_price BETWEEN 5 AND 10;

检索由供应商 1003 制造且价格小于等于 10 的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

提升效率的命令行工具

https://github.com/dbcli/mycli:
sql 语句自动补全，语法高亮 

1
pip install mycli

https://github.com/xo/usql 

1
brew install xo/xo/usql

图形化管理工具

Sequel Ace:
https://github.com/Sequel-Ace/Sequel-Ace

Sequel Pro:
https://www.sequelpro.com/
https://github.com/sequelpro/sequelpro

DBeaver

MySQL 图形化管理工具有很多，例如 Navicat for MySQL、MySQL Workbench、SQLyog、phpMyAdmin 等。