[PHP知识库]MySQL 应用笔记

零、资源

https://www.mysqlzh.com/（重要）

http://c.biancheng.net/mysql/（重要）

https://www.w3cschool.cn/mysql/（重要）

https://www.cnblogs.com/programmer-tlh/p/5782418.html

https://www.yiibai.com/sql/sql-overview.html

一、MySQL概述

1、MySQL 官网

https://www.mysql.com/

2、MySQL 简介

MySQL是一个轻量级关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。目前MySQL被广泛地应用在Internet上的中小型网站中，由于体积小、速度快、总体拥有成本低，开放源码、免费，一般中小型网站的开发都选择Linux + MySQL作为网站数据库。

MySQL是一个关系型数据库管理系统，MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，就增加了速度并提高了灵活性。

3、MySQL 特性

MySQL是一种使用广泛的数据库，特性如下：

• 使用C和C编写，并使用了多种编译器进行测试，保证源代码的可移植性；

• 支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种操作系统；

• 为多种编程语言提供了API。编程语言包括C、C、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等；

• 支持多线程，充分利用CPU资源；

• 优化的SQL查询算法，有效地提高查询速度；

• 既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中提供多语言支持，常见的编码如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名；

• 提供TCP/IP、ODBC和JDBC等多种数据库连接途径；

• 提供用于管理、检查、优化数据库操作的管理工具；

• 可以处理拥有上千万条记录的大型数据库。

4、MySQL 应用

与大型数据库例如 Oracle、DB2、SQL Server 等相比，MySQL自有它的不足之处，如规模小、功能有限（MySQL Cluster 的功能和效率都相对比较差）等，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本。 目前Internet上流行的网站构架方式是 LAMP（Linux+Apache+MySQL+PHP），即使用Linux作为操作系统，Apache作为Web服务器，MySQL作为数据库，PHP作为服务器端脚本解释器。由于Linux+Apache+MySQL+PHP都是自由或开放源码软件（FLOSS)，因此使用LAMP不用花一分钱就可以建立起一个稳定、免费的网站系统。

二、MySQL安装和卸载

1、ubuntu 下安装 MySQL

见：https://developer.aliyun.com/article/758177；

1、安装 MySQL

sudo apt update
sudo apt install mysql-server

2、查看 MySQL 服务

onlylove@ubuntu:~$ sudo systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2022-04-02 00:37:41 PDT; 49s ago
   Main PID: 6776 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2246)
     Memory: 356.0M
     CGroup: /system.slice/mysql.service
             └─6776 /usr/sbin/mysqld

Apr 02 00:37:40 ubuntu systemd[1]: Starting MySQL Community Server...
Apr 02 00:37:41 ubuntu systemd[1]: Started MySQL Community Server.
onlylove@ubuntu:~$

3、登录MySQL

onlylove@ubuntu:~$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye
onlylove@ubuntu:~$

2、保护加固 MySQL

见：https://newsn.net/say/mysql-secure-installation.html

localhost:~ sunan$ sudo mysql_secure_installation
Password:

Securing the MySQL server deployment.

# 1、建立密码验证插件
Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

# 2、选择密码规则
There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

# 3、创建符合上述选择好规则的新密码
Please set the password for root here.

New password:

Re-enter new password:
Sorry, passwords do not match.

New password:

Re-enter new password:

Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

Re-enter new password:
Sorry, passwords do not match.

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

# 4、删除匿名用户
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

# 5、禁用远程root登录
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

# 6、删除测试数据表
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

# 7、完成
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

3、ubuntu 下卸载 MySQL

1、查看 MySQL 服务是否启动

onlylove@ubuntu:~$ sudo service mysql status
[sudo] password for onlylove: 
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2022-04-01 23:35:46 PDT; 17min ago
    Process: 813 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 1103 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 2246)
     Memory: 419.0M
     CGroup: /system.slice/mysql.service
             └─1103 /usr/sbin/mysqld

Apr 01 23:35:07 ubuntu systemd[1]: Starting MySQL Community Server...
Apr 01 23:35:46 ubuntu systemd[1]: Started MySQL Community Server.
onlylove@ubuntu:~$

2、停用 MySQL 服务

onlylove@ubuntu:~$ sudo systemctl stop mysql
onlylove@ubuntu:~$ sudo service mysql status
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: inactive (dead) since Fri 2022-04-01 23:54:58 PDT; 3s ago
    Process: 813 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 1103 ExecStart=/usr/sbin/mysqld (code=exited, status=0/SUCCESS)
   Main PID: 1103 (code=exited, status=0/SUCCESS)
     Status: "Server shutdown complete"

Apr 01 23:35:07 ubuntu systemd[1]: Starting MySQL Community Server...
Apr 01 23:35:46 ubuntu systemd[1]: Started MySQL Community Server.
Apr 01 23:54:56 ubuntu systemd[1]: Stopping MySQL Community Server...
Apr 01 23:54:58 ubuntu systemd[1]: mysql.service: Succeeded.
Apr 01 23:54:58 ubuntu systemd[1]: Stopped MySQL Community Server.
onlylove@ubuntu:~$

3、删除MySQL以及它的依赖包（重要）

onlylove@ubuntu:~$ sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Note, selecting 'mysql-server-core-5.5' for glob 'mysql-server-core-*'
Note, selecting 'mysql-server-core-5.6' for glob 'mysql-server-core-*'
Note, selecting 'mysql-server-core-5.7' for glob 'mysql-server-core-*'
Note, selecting 'mysql-server-core-8.0' for glob 'mysql-server-core-*'
Package 'mysql-server-core-5.7' is not installed, so not removed
Package 'mysql-server-core-5.5' is not installed, so not removed
Package 'mysql-server-core-5.6' is not installed, so not removed
Note, selecting 'mysql-client-core-5.5' for glob 'mysql-client-core-*'
Note, selecting 'mysql-client-core-5.6' for glob 'mysql-client-core-*'
Note, selecting 'mysql-client-core-5.7' for glob 'mysql-client-core-*'
Note, selecting 'mysql-client-core-8.0' for glob 'mysql-client-core-*'
Package 'mysql-client-core-5.7' is not installed, so not removed
Package 'mysql-client-core-5.5' is not installed, so not removed
Package 'mysql-client-core-5.6' is not installed, so not removed
Package 'mysql-client' is not installed, so not removed
The following packages were automatically installed and are no longer required:
  acl apg colord-data gnome-control-center-faces gnome-online-accounts hplip-data libaio1 libcgi-fast-perl libcgi-pm-perl libcolord-gtk1 libcolorhug2
  libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl libgsound0 libgssdp-1.2-0 libgupnp-1.2-0 libgupnp-av-1.0-2 libgupnp-dlna-2.0-3
  libhtml-template-perl libieee1284-3 libimagequant0 libmecab2 libmediaart-2.0-0 librygel-core-2.6-2 librygel-db-2.6-2 librygel-renderer-2.6-2
  librygel-server-2.6-2 libsane-common libsnmp-base libsodium23 libwebpmux3 mecab-ipadic mecab-ipadic-utf8 mecab-utils mobile-broadband-provider-info
  network-manager-gnome printer-driver-postscript-hp python3-macaroonbakery python3-nacl python3-olefile python3-pexpect python3-pil python3-protobuf
  python3-ptyprocess python3-pymacaroons python3-renderpm python3-reportlab python3-reportlab-accel python3-rfc3339 python3-tz rygel
Use 'sudo apt autoremove' to remove them.
The following packages will be REMOVED:
  colord* gnome-control-center* hplip* libhpmud0* libmysqlclient21* libsane* libsane-hpaio* libsnmp35* mysql-client-8.0* mysql-client-core-8.0*
  mysql-common* mysql-server* mysql-server-8.0* mysql-server-core-8.0* printer-driver-hpcups* sane-utils* ubuntu-desktop* ubuntu-desktop-minimal*
0 upgraded, 0 newly installed, 18 to remove and 32 not upgraded.
After this operation, 240 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 166080 files and directories currently installed.)
Removing ubuntu-desktop (1.450.2) ...
Removing ubuntu-desktop-minimal (1.450.2) ...
Removing gnome-control-center (1:3.36.5-0ubuntu3) ...
Removing colord (1.4.4-2) ...
Removing hplip (3.20.3+dfsg0-2) ...
Removing libsane-hpaio:amd64 (3.20.3+dfsg0-2) ...
Removing printer-driver-hpcups (3.20.3+dfsg0-2) ...
Removing libhpmud0:amd64 (3.20.3+dfsg0-2) ...
Removing sane-utils (1.0.29-0ubuntu5.2) ...
Removing libsane:amd64 (1.0.29-0ubuntu5.2) ...
Removing libsnmp35:amd64 (5.8+dfsg-2ubuntu2.3) ...
Removing libmysqlclient21:amd64 (8.0.28-0ubuntu0.20.04.3) ...
Removing mysql-server (8.0.28-0ubuntu0.20.04.3) ...
Removing mysql-server-8.0 (8.0.28-0ubuntu0.20.04.3) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Removing mysql-client-8.0 (8.0.28-0ubuntu0.20.04.3) ...
Removing mysql-client-core-8.0 (8.0.28-0ubuntu0.20.04.3) ...
Removing mysql-common (5.8+1.0.5ubuntu2) ...
Removing mysql-server-core-8.0 (8.0.28-0ubuntu0.20.04.3) ...
Processing triggers for mime-support (3.64ubuntu1) ...
Processing triggers for cups (2.3.1-9ubuntu1.1) ...
Updating PPD files for cups-filters ...
Updating PPD files for foomatic-db-compressed-ppds ...
Updating PPD files for openprinting-ppds ...
Updating PPD files for brlaser ...
Updating PPD files for c2esp ...
Updating PPD files for foo2zjs-common ...
Updating PPD files for m2300w ...
Updating PPD files for postscript-hp ...
Updating PPD files for ptouch ...
Updating PPD files for pxljr ...
Updating PPD files for sag-gdi ...
Updating PPD files for splix ...
Processing triggers for gnome-menus (3.36.0-1ubuntu1) ...
Processing triggers for libglib2.0-0:amd64 (2.64.6-1~ubuntu20.04.4) ...
Processing triggers for libc-bin (2.31-0ubuntu9.8) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for dbus (1.12.16-2ubuntu2.1) ...
Processing triggers for udev (245.4-4ubuntu3.15) ...
Processing triggers for desktop-file-utils (0.24-1ubuntu3) ...
(Reading database ... 165418 files and directories currently installed.)
Purging configuration files for hplip (3.20.3+dfsg0-2) ...
Purging configuration files for mysql-server-8.0 (8.0.28-0ubuntu0.20.04.3) ...
Purging configuration files for mysql-common (5.8+1.0.5ubuntu2) ...
dpkg: warning: while removing mysql-common, directory '/etc/mysql' not empty so not removed
Purging configuration files for colord (1.4.4-2) ...
Purging configuration files for sane-utils (1.0.29-0ubuntu5.2) ...
Purging configuration files for libsane-hpaio:amd64 (3.20.3+dfsg0-2) ...
Processing triggers for dbus (1.12.16-2ubuntu2.1) ...
Processing triggers for systemd (245.4-4ubuntu3.15) ...
onlylove@ubuntu:~$ 

4、删除相关数据

sudo rm -rf /var/log/mysql
sudo rm -rf /etc/mysql
sudo rm -rf /usr/bin/mysql
sudo deluser -f mysql

5、清除不再需要的软件包

sudo apt autoremove
sudo apt autoclean

三、初次使用 MySQL

1、mysql服务相关命令

service mysql status	#查看状态
service mysql stop		#停止服务
service mysql start		#启动服务
service mysql restart	#重启服务

2、登录mysql

mysql -u用户名 [-h主机名] -p密码 [-P端口号] [-D数据库名] [-eMySQL命令] [-S socket文件名]

参数说明：

• 【-u用户名】或者【–user=用户名】：指定用户登录的用户名；

• 【-p密码（p小写）】或者【–password=密码】：输入登录密码；

• 【-h主机名或ip地址】或者【–host=主机名ip地址】：指定登录的主机名；

• 【-P端口号（P大写）】或者【–port=端口号】：指定登录的MySQL的端口号；

• 【-D数据库名】或者【–database=数据库名】：指定登录的数据库名称；

• 【-S socket文件名】或者【–socket=socket文件名】：指定登录时使用的socket文件名。

• 【-e MySQL命令】或者【–execute= MySQL命令】：在不登录MySQL的情况下执行MySQL命令。

四、数据库基础

五、SQL 语句

六、函数

七、约束

八、多表查询

九、事务

十、日志

十一、主从复制

十二、分库分表

十三、读写分离