1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 数据库

mysql进阶

• 数据库 • 阅读 88

1.二进制格式mysql安装

下载二进制格式的mysql软件包

[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-27 15:52:00--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 23.33.16.230
Connecting to cdn.mysql.com (cdn.mysql.com)|23.33.16.230|:443... connected.

HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: 'mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz'

mysql-5.7.38-linux- 100%[=================>] 643.57M  1.43MB/s    in 4m 25s

2022-07-27 15:56:27 (2.42 MB/s) - 'mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz' saved [674830866/674830866]

创建用户和组

[root@localhost src]# useradd -M -r -s /sbin/nologin mysql
[root@localhost src]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)

解压软件到/usr/local/

[root@localhost src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.38-linux-glibc2.12-x86_64  share
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64 mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 Jun 22  2021 bin
drwxr-xr-x. 2 root root   6 Jun 22  2021 etc
drwxr-xr-x. 2 root root   6 Jun 22  2021 games
drwxr-xr-x. 2 root root   6 Jun 22  2021 include
drwxr-xr-x. 2 root root   6 Jun 22  2021 lib
drwxr-xr-x. 3 root root  17 Jul 12 21:45 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
lrwxrwxrwx. 1 root root  35 Jul 27 16:06 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Jul 27 16:04 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Jul 12 21:45 share
drwxr-xr-x. 2 root root   6 Jun 22  2021 src

修改目录/usr/local/mysql的属主属组

[root@localhost local]# chown -R mysql.mysql mysql*
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root  root    6 Jun 22  2021 bin
drwxr-xr-x. 2 root  root    6 Jun 22  2021 etc
drwxr-xr-x. 2 root  root    6 Jun 22  2021 games
drwxr-xr-x. 2 root  root    6 Jun 22  2021 include
drwxr-xr-x. 2 root  root    6 Jun 22  2021 lib
drwxr-xr-x. 3 root  root   17 Jul 12 21:45 lib64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 libexec
lrwxrwxrwx. 1 mysql mysql  35 Jul 27 16:06 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jul 27 16:04 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 Jun 22  2021 sbin
drwxr-xr-x. 5 root  root   49 Jul 12 21:45 share
drwxr-xr-x. 2 root  root    6 Jun 22  2021 src

添加环境变量

[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@localhost mysql]# cd bin/
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@localhost bin]# source /etc/profile.d/mysql.sh
[root@localhost bin]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

创建头文件

[root@localhost bin]# cd ..

[root@localhost mysql]# ls
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@localhost mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@localhost mysql]# ldconfig
[root@localhost mysql]# vim /etc/man_db.conf
#MANDATORY_MANPATH                      /usr/src/pvm3/man
#
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man    //添加一句

建立数据存放目录

[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# ll /opt/data/
total 0
[root@localhost ~]# chown -R mysql.mysql /opt/data/

初始化数据库

[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/
2022-07-27T08:28:23.607621Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2022-07-27T08:28:23.804276Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2022-07-27T08:28:23.804304Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2022-07-27T08:28:23.804658Z 0 [Warning] CA certificate ca.pem is self signed.

2022-07-27T08:28:23.844366Z 1 [Note] A temporary password is generated for root@localhost: RIa<p+td%6qv 这个命令的最后会生成一个临时密码，此处密码是ria<p+td%6qv 再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到 < code></p+td%6qv>

生成配置文件

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

启动mysql

//&#x5148;&#x627E;&#x5230;&#x542F;&#x52A8;&#x811A;&#x672C;&#x6587;&#x4EF6;&#x4F4D;&#x7F6E;
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
LICENSE  README  bin  docs  include  lib  man  share  support-files
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic  mysql-log-rotate  mysql.server  mysqld_multi.server
[root@localhost support-files]# file mysql.server
mysql.server: POSIX shell script, ASCII text executable
//&#x5728;&#x8FDB;&#x884C;&#x542F;&#x52A8;mysql,&#x53D1;&#x73B0;3306&#x7AEF;&#x53E3;&#x5DF2;&#x76D1;&#x542C;
[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.

 SUCCESS!

[root@localhost ~]# ss -anlt
State            Recv-Q           Send-Q                       Local Address:Port                       Peer Address:Port           Process
LISTEN           0                128                                0.0.0.0:22                              0.0.0.0:*
LISTEN           0                80                                       *:3306                                  *:*
LISTEN           0                128                                   [::]:22                                 [::]:*

//&#x5C06;&#x811A;&#x672C;&#x52A0;&#x5165;&#x5230;&#x7CFB;&#x7EDF;&#x91CC;&#x53BB;   //&#x4F7F;&#x7528;service&#x63A7;&#x5236;MySQL&#x542F;&#x52A8;&#x6216;&#x5173;&#x95ED;
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql/       //&#x6DFB;&#x52A0;&#x5B89;&#x88C5;&#x4F4D;&#x7F6E;
datadir=/opt/data/              //&#x6DFB;&#x52A0;&#x6570;&#x636E;&#x5E93;&#x5B58;&#x653E;&#x4F4D;&#x7F6E;
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!

[root@localhost ~]# ss -anlt
State            Recv-Q           Send-Q                       Local Address:Port                       Peer Address:Port           Process
LISTEN           0                128                                0.0.0.0:22                              0.0.0.0:*
LISTEN           0                80                                       *:3306                                  *:*
LISTEN           0                128                                   [::]:22                                 [::]:*
[root@localhost ~]# service mysqld stop
Shutting down MySQL.^[[A^[[A. SUCCESS!

[root@localhost ~]# ss -anlt
State            Recv-Q           Send-Q                       Local Address:Port                       Peer Address:Port           Process
LISTEN           0                128                                0.0.0.0:22                              0.0.0.0:*
LISTEN           0                128                                   [::]:22                                 [::]:*
//&#x914D;&#x7F6E;&#x6587;&#x4EF6;&#x88AB;&#x5220;&#x9664;&#x540E;&#x4F7F;&#x7528;&#x4E0D;&#x4E86;
[root@localhost ~]# rm -rf /etc/init.d/mysqld
[root@localhost ~]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
Failed to start mysqld.service: Unit mysqld.service not found.

//&#x4F7F;&#x7528;systemctl&#x7BA1;&#x7406;mysql
[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service .
[root@localhost ~]# ls
anaconda-ks.cfg  passwd  sshd.service
[root@localhost ~]# mv sshd.service mysql.service
[root@localhost ~]# ls
anaconda-ks.cfg  mysql.service  passwd
[root@localhost ~]# vim mysql.service
[root@localhost ~]# cat mysql.service
[Unit]
Description=mysql server daemon
After=network.target sshd-keygen.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

[root@localhost ~]# mv mysql.service /usr/lib/systemd/system/
[root@localhost ~]# ls
anaconda-ks.cfg  passwd
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl start mysqld
Failed to start mysqld.service: Unit mysqld.service not found.  //&#x542F;&#x52A8;&#x62A5;&#x9519;&#x9700;&#x8981;&#x5173;&#x95ED;&#x9632;&#x706B;&#x5899;&#x548C;selinux
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/selinux/config
SELINUX=disabled    //&#x4FEE;&#x6539;
[root@localhost ~]# systemctl start mysql
[root@localhost ~]# ss -anlt
State            Recv-Q           Send-Q                       Local Address:Port                       Peer Address:Port           Process
LISTEN           0                128                                0.0.0.0:22                              0.0.0.0:*
LISTEN           0                80                                       *:3306                                  *:*
LISTEN           0                128                                   [::]:22                                 [::]:*

要更改密码，您需要使用临时密码登录

[En]

To change the password, you need to log in with a temporary password

[root@localhost ~]# ls
anaconda-ks.cfg  passwd
[root@localhost ~]# cat passwd
RIa<p+td%6qv 2 27 [root@localhost ~]# mysql -uroot -p'ria<p+td%6qv' 登录报错显示需要libncurses.so.5 mysql: error while loading shared libraries: libncurses.so.5: cannot open object file: no such file or directory dnf provides libncurses.so.5 查看这个在那个软件包里面 failed to set locale, defaulting c.utf-8 last metadata expiration check: 0:37:59 ago on wed jul 16:33:32 2022. ncurses-compat-libs-6.1-9.20180224.el8.i686 : ncurses compatibility libraries repo base matched from: provide -y install ncurses-compat-libs 下载包 [warning] using a password the command line interface can be insecure. welcome monitor. commands end with ; \g. your connection id is server version: 5.7.38 copyright (c) 2000, 2022, oracle and its affiliates. registered trademark of corporation other names may trademarks their respective owners. type 'help;' '\h' for help. '\c' clear current input statement.> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

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>
</p+td%6qv>

2.mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=123456
[root@localhost ~]# mysql -e 'show databases';
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql常用配置文件参数：

参数 说明 port = 3306 设置监听端口 socket = /tmp/mysql.sock 指定套接字文件位置 basedir = /usr/local/mysql 指定MySQL的安装路径 datadir = /data/mysql 指定MySQL的数据存放路径 pid-file = /data/mysql/mysql.pid 指定进程ID文件存放路径 user = mysql 指定MySQL以什么用户的身份提供服务 skip-name-resolve 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

数据库密码破解：
1、修改/etc/my.cnf配置文件
vim /etc/my.cnf
添加skip-grant-tables
2、重启mysql服务
3、mysql 登入修改密码
update user set authentication_string = password(‘654321’) where User = ‘root’ and Host = ‘localhost’;
4、编辑/etc/my.cnf配置文，删除skip-grant-tables，然后重启启动mysql服务
5、使用新密码登入mysql测试

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
skip-grant-tables           //&#x6DFB;&#x52A0;&#x4E00;&#x53E5; &#x8868;&#x793A;&#x8DF3;&#x8FC7;&#x6240;&#x6709;&#x6388;&#x6743;&#x8868;
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)

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> set password = password('654321');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> update user set authentication_string = password('654321') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
//&#x5220;&#x9664;&#x6DFB;&#x52A0;&#x7684;&#x90A3;&#x4E00;&#x53E5;
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

//&#x6D4B;&#x8BD5;&#x5BC6;&#x7801;&#x662F;&#x5426;&#x80FD;&#x767B;&#x5F55;
[root@localhost ~]# mysql -uroot -p654321
mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

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>

3.多表联合查询

上面提到的查询语句都是针对一个表的，但在关系数据库中，表之间存在关系，因此在实际应用中，经常会使用多表查询。多表查询是指同时查询两个或多个表。

[En]

The query statements mentioned above are all for one table, but in a relational database, there is a relationship between tables, so in practical applications, multi-table queries are often used. Multi-table query is to query two or more tables at the same time.

在 MySQL 中，多表查询主要有交叉连接、内连接、外连接、分组查询与子查询等5种。

交叉连接(CROSS JOIN)：有两种，显式的和隐式的2种，一般用来返回连接表的笛卡尔积。
笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。
例如，有 A 和 B 两个集合，它们的值如下：

A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：

A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

而从上述结果中，我们可以看到：

[En]

And, from the above results, we can see:

  • 两个集合相乘，不满足交换率，即 A×B≠B×A。
  • A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。

多表查询所遵循的算法是上面提到的笛卡尔乘积，表之间的连接可以看作乘法。在实际应用中，应避免使用笛卡尔乘积，因为笛卡尔乘积中存在大量不合理的数据，容易造成查询结果的重复和混淆。

[En]

The algorithm followed by multi-table query is the Cartesian product mentioned above, and the join between tables can be regarded as multiplication. In practical application, the use of Cartesian product should be avoided, because there are a lot of unreasonable data in Cartesian product, which can easily lead to repetition and confusion of query results.

交叉连接的语法格式如下：

[En]

The syntax format for cross connections is as follows:

SELECT  FROM  CROSS JOIN  [WHERE子句];

SELECT  FROM ,  [WHERE子句];

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

先创建一个名为tb_student_info的表

mysql> create database zxr;
Query OK, 1 row affected (0.00 sec)

mysql> use zxr;
Database changed
mysql> create table tb_students_info(id int not null primary key auto_increment,name varchar(50),age tinyint,sex varchar(4),height float,course_id int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc tb_students_info;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | YES  |     | NULL    |                |
| age       | tinyint(4)  | YES  |     | NULL    |                |
| sex       | varchar(4)  | YES  |     | NULL    |                |
| height    | float       | YES  |     | NULL    |                |
| course_id | int(11)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> insert tb_students_info(name,age,sex,height,course_id) values('Dany',25,'M',160,1),('Green',23,'M',158,2),('Henry',23,'W',185,1),('Jane',22,'M',162,3),('Jim',24,'W',175,2),('John',21,'W',172,4),('Lily',22,'M',165,4),('Susan',23,'M',170,5),('Thomas',22,'W',178,5),('Tom',23,'W',165,5)
    -> ;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | W    |    175 |         2 |
|  6 | John   |   21 | W    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | W    |    178 |         5 |
| 10 | Tom    |   23 | W    |    165 |         5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

创建一个名为tb_course的表

mysql> create table tb_course(id int not null primary key auto_increment,course_name varchar(50));
Query OK, 0 rows affected (0.02 sec)

mysql> desc tb_course;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| course_name | varchar(50) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

使用cross join查询出两张表中的笛卡尔积

mysql> select * from tb_course cross join tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | MySQL       |  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | M    |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | M    |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | M    |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | M    |    158 |         2 |
|  2 | MySQL       |  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | M    |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | M    |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | M    |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | W    |    185 |         1 |
|  2 | MySQL       |  3 | Henry  |   23 | W    |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | W    |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | W    |    185 |         1 |
|  1 | Java        |  4 | Jane   |   22 | M    |    162 |         3 |
|  2 | MySQL       |  4 | Jane   |   22 | M    |    162 |         3 |
|  3 | Python      |  4 | Jane   |   22 | M    |    162 |         3 |
|  4 | Go          |  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | C++         |  4 | Jane   |   22 | M    |    162 |         3 |
|  1 | Java        |  5 | Jim    |   24 | W    |    175 |         2 |
|  2 | MySQL       |  5 | Jim    |   24 | W    |    175 |         2 |
|  3 | Python      |  5 | Jim    |   24 | W    |    175 |         2 |
|  4 | Go          |  5 | Jim    |   24 | W    |    175 |         2 |
|  5 | C++         |  5 | Jim    |   24 | W    |    175 |         2 |
|  1 | Java        |  6 | John   |   21 | W    |    172 |         4 |
|  2 | MySQL       |  6 | John   |   21 | W    |    172 |         4 |
|  3 | Python      |  6 | John   |   21 | W    |    172 |         4 |
|  4 | Go          |  6 | John   |   21 | W    |    172 |         4 |
|  5 | C++         |  6 | John   |   21 | W    |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | M    |    165 |         4 |
|  2 | MySQL       |  7 | Lily   |   22 | M    |    165 |         4 |
|  3 | Python      |  7 | Lily   |   22 | M    |    165 |         4 |
|  4 | Go          |  7 | Lily   |   22 | M    |    165 |         4 |
|  5 | C++         |  7 | Lily   |   22 | M    |    165 |         4 |
|  1 | Java        |  8 | Susan  |   23 | M    |    170 |         5 |
|  2 | MySQL       |  8 | Susan  |   23 | M    |    170 |         5 |
|  3 | Python      |  8 | Susan  |   23 | M    |    170 |         5 |
|  4 | Go          |  8 | Susan  |   23 | M    |    170 |         5 |
|  5 | C++         |  8 | Susan  |   23 | M    |    170 |         5 |
|  1 | Java        |  9 | Thomas |   22 | W    |    178 |         5 |
|  2 | MySQL       |  9 | Thomas |   22 | W    |    178 |         5 |
|  3 | Python      |  9 | Thomas |   22 | W    |    178 |         5 |
|  4 | Go          |  9 | Thomas |   22 | W    |    178 |         5 |
|  5 | C++         |  9 | Thomas |   22 | W    |    178 |         5 |
|  1 | Java        | 10 | Tom    |   23 | W    |    165 |         5 |
|  2 | MySQL       | 10 | Tom    |   23 | W    |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | W    |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | W    |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | W    |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)

使用,来查询两张表

mysql> select * from tb_course,tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | MySQL       |  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | M    |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | M    |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | M    |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | M    |    158 |         2 |
|  2 | MySQL       |  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | M    |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | M    |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | M    |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | W    |    185 |         1 |
|  2 | MySQL       |  3 | Henry  |   23 | W    |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | W    |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | W    |    185 |         1 |
|  1 | Java        |  4 | Jane   |   22 | M    |    162 |         3 |
|  2 | MySQL       |  4 | Jane   |   22 | M    |    162 |         3 |
|  3 | Python      |  4 | Jane   |   22 | M    |    162 |         3 |
|  4 | Go          |  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | C++         |  4 | Jane   |   22 | M    |    162 |         3 |
|  1 | Java        |  5 | Jim    |   24 | W    |    175 |         2 |
|  2 | MySQL       |  5 | Jim    |   24 | W    |    175 |         2 |
|  3 | Python      |  5 | Jim    |   24 | W    |    175 |         2 |
|  4 | Go          |  5 | Jim    |   24 | W    |    175 |         2 |
|  5 | C++         |  5 | Jim    |   24 | W    |    175 |         2 |
|  1 | Java        |  6 | John   |   21 | W    |    172 |         4 |
|  2 | MySQL       |  6 | John   |   21 | W    |    172 |         4 |
|  3 | Python      |  6 | John   |   21 | W    |    172 |         4 |
|  4 | Go          |  6 | John   |   21 | W    |    172 |         4 |
|  5 | C++         |  6 | John   |   21 | W    |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | M    |    165 |         4 |
|  2 | MySQL       |  7 | Lily   |   22 | M    |    165 |         4 |
|  3 | Python      |  7 | Lily   |   22 | M    |    165 |         4 |
|  4 | Go          |  7 | Lily   |   22 | M    |    165 |         4 |
|  5 | C++         |  7 | Lily   |   22 | M    |    165 |         4 |
|  1 | Java        |  8 | Susan  |   23 | M    |    170 |         5 |
|  2 | MySQL       |  8 | Susan  |   23 | M    |    170 |         5 |
|  3 | Python      |  8 | Susan  |   23 | M    |    170 |         5 |
|  4 | Go          |  8 | Susan  |   23 | M    |    170 |         5 |
|  5 | C++         |  8 | Susan  |   23 | M    |    170 |         5 |
|  1 | Java        |  9 | Thomas |   22 | W    |    178 |         5 |
|  2 | MySQL       |  9 | Thomas |   22 | W    |    178 |         5 |
|  3 | Python      |  9 | Thomas |   22 | W    |    178 |         5 |
|  4 | Go          |  9 | Thomas |   22 | W    |    178 |         5 |
|  5 | C++         |  9 | Thomas |   22 | W    |    178 |         5 |
|  1 | Java        | 10 | Tom    |   23 | W    |    165 |         5 |
|  2 | MySQL       | 10 | Tom    |   23 | W    |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | W    |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | W    |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | W    |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)

查询表中学习mysql的人的名字

mysql> select tb_course.course_name,tb_students_info.name from tb_course,tb_students_info where  tb_course.course_name = 'mysql';
+-------------+--------+
| course_name | name   |
+-------------+--------+
| MySQL       | Dany   |
| MySQL       | Green  |
| MySQL       | Henry  |
| MySQL       | Jane   |
| MySQL       | Jim    |
| MySQL       | John   |
| MySQL       | Lily   |
| MySQL       | Susan  |
| MySQL       | Thomas |
| MySQL       | Tom    |
+-------------+--------+
10 rows in set (0.00 sec)

查询表中id和course_id相同学习mysql的有哪些人

mysql> select tb_course.course_name,tb_students_info.name from tb_course,tb_students_info where tb_course.id = tb_students_info.course_id and tb_course.course_name = 'mysql';
+-------------+-------+
| course_name | name  |
+-------------+-------+
| MySQL       | Green |
| MySQL       | Jim   |
+-------------+-------+
2 rows in set (0.00 sec)

内连接（INNER JOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件。如果没有连接条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

内连接的语法格式如下：

SELECT  FROM  INNER JOIN  [ON子句];

语法说明如下。

  • 字段名：要查询的字段的名称。
    [En]

    Field name: the name of the field to be queried.*

    *

  • INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。
  • ON 子句：用来设置内连接的连接条件。

INNER JOIN 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN … ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。

多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。

内部联接可以查询两个或多个表。为了更好地理解，我暂时只解释两个表的连接查询。

[En]

Internal joins can query two or more tables. For a better understanding, I will only explain the join query of two tables for the time being.

在这里的查询语句中，两个表之间的关系通过 INNER JOIN指定，连接的条件使用ON子句给出。

注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

在 tb_students_info 表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称

mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | MySQL       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
+--------+-------------+
10 rows in set (0.00 sec)

在 tb_students_info 表和 tb_course 表之间,使用内连接查询学习mysql课程的学生有哪些

mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and c.course_name =  'Mysql';
+-------+-------------+
| name  | course_name |
+-------+-------------+
| Green | MySQL       |
| Jim   | MySQL       |
+-------+-------------+
2 rows in set (0.00 sec)
//&#x7EDF;&#x8BA1;&#x4EBA;&#x6570;
mysql> select count(s.name),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and c.course_name =  'Mysql';
+---------------+-------------+
| count(s.name) | course_name |
+---------------+-------------+
|             2 | MySQL       |
+---------------+-------------+
1 row in set (0.00 sec)

在 tb_students_info 表和 tb_course 表之间,使用内连接查询学习mysql和python两门课程的名字

mysql> select s.name,c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name =  'Mysql' or c.course_name = 'Python');
+-------+-------------+
| name  | course_name |
+-------+-------------+
| Green | MySQL       |
| Jane  | Python      |
| Jim   | MySQL       |
+-------+-------------+
3 rows in set (0.00 sec)
//&#x5217;&#x51FA;&#x4EBA;&#x6570;
mysql> select count(s.name),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name =  'Mysql' or c.course_name = 'Python') group by c.course_name;
+---------------+-------------+
| count(s.name) | course_name |
+---------------+-------------+
|             2 | MySQL       |
|             1 | Python      |
+---------------+-------------+
2 rows in set (0.00 sec)
//&#x5206;&#x522B;&#x5217;&#x51FA;&#x5B66;&#x4E60;MySQL&#x548C;python&#x4EBA;&#x7684;&#x540D;&#x5B57;
mysql> select group_concat(s.name),c.course_name from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name =  'Mysql' or
 c.course_name = 'Python') group by c.course_name;
+----------------------+-------------+
| group_concat(s.name) | course_name |
+----------------------+-------------+
| Jim,Green            | MySQL       |
| Jane                 | Python      |
+----------------------+-------------+
2 rows in set (0.00 sec)

在这里的查询语句中，两个表之间的关系通过 INNER JOIN指定，连接的条件使用ON子句给出。

注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

内连接的查询结果都是满足连接条件的记录，而外连接会先将连接的表分成基表和参照表，然后根据基表返回满足和不满足条件的记录。

[En]

The query results of the inner join are all records that meet the join conditions, while the outer join will first divide the joined table into the base table and the reference table, and then return records that meet and do not meet the conditions based on the base table.

外部连接可以分为两种类型：左侧外部连接和右侧外部连接。根据下面的示例介绍左侧外部连接和右侧外部连接。

[En]

The outer connection can be divided into two types: the left outer connection and the right outer connection. The left outer connection and the right outer connection are introduced according to the examples below.

左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

左连接的语法格式如下：

SELECT  FROM  LEFT OUTER JOIN  ;

语法说明如下:

  • 字段名：要查询的字段的名称。
    [En]

    Field name: the name of the field to be queried.*

    *

  • LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
  • ON 子句：用来设置左连接的连接条件，不能省略。

上述语法中，”表1″为基表，”表2″为参考表。左连接查询时，可以查询出”表1″中的所有记录和”表2″中匹配连接条件的记录。如果”表1″的某行在”表2″中没有匹配行，那么在返回结果中，”表2″的字段值均为空值（NULL）。

在tb_course表的基础上先添加一条字段

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

mysql> insert tb_course(course_name) values('HTML');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

在tb_students_info表的基础上添加一条字段

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | W    |    175 |         2 |
|  6 | John   |   21 | W    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | W    |    178 |         5 |
| 10 | Tom    |   23 | W    |    165 |         5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

mysql> insert tb_students_info(name,age,sex,height,course_id) values('LiMing',22,'M',180,7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | W    |    175 |         2 |
|  6 | John   |   21 | W    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | W    |    178 |         5 |
| 10 | Tom    |   23 | W    |    165 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生

mysql> select s.name,c.course_name  from tb_students_info s left outer join tb_course c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | MySQL       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| LiMing | NULL        |
+--------+-------------+
11 rows in set (0.00 sec)

可以看到，运行结果显示了 12 条记录，name 为 LiMing 的学生目前没有课程，因为对应的 tb_course 表中没有该学生的课程信息，所以该条记录只取出了 tb_students_info 表中相应的值，而从 tb_course 表中取出的值为 NULL。

右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

右连接的语法格式如下：

SELECT  FROM  RIGHT OUTER JOIN  ;

语法说明如下:

  • 字段名：要查询的字段的名称。
    [En]

    Field name: the name of the field to be queried.*

    *

  • RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
  • ON 子句：用来设置右连接的连接条件，不能省略。

与左连接相反，右连接以”表2″为基表，”表1″为参考表。右连接查询时，可以查询出”表2″中的所有记录和”表1″中匹配连接条件的记录。如果”表2″的某行在”表1″中没有匹配行，那么在返回结果中，”表1″的字段值均为空值（NULL）。

在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程

mysql> select s.name,c.course_name  from tb_students_info s right outer join tb_course c on s.course_id = c.id;
+--------+-------------+
| name   | course_name |
+--------+-------------+
| Dany   | Java        |
| Green  | MySQL       |
| Henry  | Java        |
| Jane   | Python      |
| Jim    | MySQL       |
| John   | Go          |
| Lily   | Go          |
| Susan  | C++         |
| Thomas | C++         |
| Tom    | C++         |
| NULL   | HTML        |
+--------+-------------+
11 rows in set (0.00 sec)

可以看到，结果显示了 11 条记录，名称为 HTML 的课程目前没有学生，因为对应的tb_students_info 表中并没有该学生的信息，所以该条记录只取出了 tb_course 表中相应的值，而从 tb_students_info 表中取出的值为 NULL。

多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。

在使用外部连接查询时，请务必区分查询结果，无论是显示左表的所有记录还是显示右表的所有记录，然后选择对应的左连接和右连接。

[En]

When using external join queries, be sure to distinguish the results of the query, whether you need to display all the records of the left table or all the records of the right table, and then select the corresponding left join and right join.

在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。

使用 GROUP BY 关键字的语法格式如下：

GROUP BY

其中，”字段名”表示需要分组的字段名称，多个字段时用逗号隔开。

单独使用 GROUP BY 关键字时， &#x67E5;&#x8BE2;&#x7ED3;&#x679C;&#x4F1A;&#x53EA;&#x663E;&#x793A;&#x6BCF;&#x4E2A;&#x5206;&#x7EC4;&#x7684;&#x7B2C;&#x4E00;&#x6761;&#x8BB0;&#x5F55;&#x3002;

下面根据 tb_students_info 表中的 sex 字段进行查询

mysql> select name,sex from tb_students_info group by sex;
+-------+------+
| name  | sex  |
+-------+------+
| Dany  | M    |
| Henry | W    |
+-------+------+
2 rows in set (0.00 sec)

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

下面根据 tb_students_info 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。

//&#x5206;&#x7EC4;&#x67E5;&#x8BE2;
mysql> select group_concat(name),sex from tb_students_info group by sex;
+-----------------------------------+------+
| group_concat(name)                | sex  |
+-----------------------------------+------+
| Dany,Green,Jane,Lily,Susan,LiMing | M    |
| Henry,Jim,John,Thomas,Tom         | W    |
+-----------------------------------+------+
2 rows in set (0.00 sec)

上面实例在分组过程中，先按照 age 字段进行分组，当 age 字段值相等时，再把 age 字段值相等的记录按照 sex 字段进行分组。

多个字段分组查询时，会先按照第一个字段进行分组。如果第一个字段中有相同的值，MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的，那么 MySQL 将不再对第二个字段进行分组。

下面根据 tb_students_info 表中的 age 字段进行分组查询

mysql> select group_concat(name),age from tb_students_info group by age;
+-------------------------+------+
| group_concat(name)      | age  |
+-------------------------+------+
| John                    |   21 |
| Jane,Lily,Thomas,LiMing |   22 |
| Green,Henry,Susan,Tom   |   23 |
| Jim                     |   24 |
| Dany                    |   25 |
+-------------------------+------+
5 rows in set (0.00 sec)

下面根据 tb_students_info 表中的 age 和 sex 字段进行分组查询。

mysql> select age,sex,group_concat(name) from tb_students_info group by age,sex;
+------+------+--------------------+
| age  | sex  | group_concat(name) |
+------+------+--------------------+
|   21 | W    | John               |
|   22 | M    | Jane,Lily,LiMing   |
|   22 | W    | Thomas             |
|   23 | M    | Green,Susan        |
|   23 | W    | Henry,Tom          |
|   24 | W    | Jim                |
|   25 | M    | Dany               |
+------+------+--------------------+
7 rows in set (0.00 sec)

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。

&#x805A;&#x5408;&#x51FD;&#x6570;&#x5305;&#x62EC; COUNT()&#xFF0C;SUM()&#xFF0C;AVG()&#xFF0C;MAX() &#x548C; MIN()&#x3002;&#x5176;&#x4E2D;&#xFF0C;COUNT() &#x7528;&#x6765;&#x7EDF;&#x8BA1;&#x8BB0;&#x5F55;&#x7684;&#x6761;&#x6570;&#xFF1B;SUM() &#x7528;&#x6765;&#x8BA1;&#x7B97;&#x5B57;&#x6BB5;&#x503C;&#x7684;&#x603B;&#x548C;&#xFF1B;AVG() &#x7528;&#x6765;&#x8BA1;&#x7B97;&#x5B57;&#x6BB5;&#x503C;&#x7684;&#x5E73;&#x5747;&#x503C;&#xFF1B;MAX() &#x7528;&#x6765;&#x67E5;&#x8BE2;&#x5B57;&#x6BB5;&#x7684;&#x6700;&#x5927;&#x503C;&#xFF1B;MIN() &#x7528;&#x6765;&#x67E5;&#x8BE2;&#x5B57;&#x6BB5;&#x7684;&#x6700;&#x5C0F;&#x503C;&#x3002;

count(列名)：不会忽略空值

count(*)：不会忽略空值，也不会忽略所有列，统计所有的行

count(1)：会忽略空值

下面根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。

mysql> select sex,count(sex) from tb_students_info group by sex;
+------+------------+
| sex  | count(sex) |
+------+------------+
| M    |          6 |
| W    |          5 |
+------+------------+
2 rows in set (0.00 sec)

count(列名)

mysql> select count(name) from tb_students_info;
+-------------+
| count(name) |
+-------------+
|          11 |
+-------------+
1 row in set (0.00 sec)

count(*)

mysql> select count(*) from tb_students_info;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

count(1)

mysql> select count(1) from tb_students_info;
+----------+
| count(1) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

sun

mysql> select c.course_name,sum(s.age) from tb_students_info s join tb_course c on s.course_id = c.id and c.course_name = 'mysql';
+-------------+------------+
| course_name | sum(s.age) |
+-------------+------------+
| MySQL       |         47 |
+-------------+------------+
1 row in set (0.00 sec)

avg

mysql> select c.course_name,avg(s.age) from tb_students_info s join tb_course c on s.course_id = c.id and c.course_name = 'mysql';
+-------------+------------+
| course_name | avg(s.age) |
+-------------+------------+
| MySQL       |    23.5000 |
+-------------+------------+
1 row in set (0.00 sec)

max

mysql> select c.course_name,max(s.age) from tb_students_info s join tb_course c on s.course_id = c.id and c.course_name = 'mysql';
+-------------+------------+
| course_name | max(s.age) |
+-------------+------------+
| MySQL       |         24 |
+-------------+------------+
1 row in set (0.00 sec)

min

mysql> select c.course_name,min(s.age) from tb_students_info s join tb_course c on s.course_id = c.id and c.course_name = 'mysql';
+-------------+------------+
| course_name | min(s.age) |
+-------------+------------+
| MySQL       |         23 |
+-------------+------------+
1 row in set (0.00 sec)

WITH ROLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和

mysql> select sex,group_concat(name) from tb_students_info group by sex with rollup;
+------+-------------------------------------------------------------+
| sex  | group_concat(name)                                          |
+------+-------------------------------------------------------------+
| M    | Dany,Green,Jane,Lily,Susan,LiMing                           |
| W    | Henry,Jim,John,Thomas,Tom                                   |
| NULL | Dany,Green,Jane,Lily,Susan,LiMing,Henry,Jim,John,Thomas,Tom |
+------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

根据 tb_students_info 表中的名字和课程找出学习mysql和python的人，并使用with rollup显示记录的总合

mysql> select c.course_name,group_concat(s.name) from tb_students_info s inner join tb_course c on s.course_id = c.id and (c.course_name =  'Mysql' or c.course_name = 'Python') group by c.course_name with rollup;
+-------------+----------------------+
| course_name | group_concat(s.name) |
+-------------+----------------------+
| MySQL       | Jim,Green            |
| Python      | Jane                 |
| NULL        | Jim,Green,Jane       |
+-------------+----------------------+
3 rows in set (0.00 sec)

子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

子查询在 WHERE 中的语法格式如下：

WHERE   (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名

mysql> select name from tb_students_info where course_id in (select id from tb_course where course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。

首先单独执行内查询，查询出 tb_course 表中课程为 Java 的 id

mysql> select id from tb_course where course_name = 'Java';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

然后执行外层查询，在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。

mysql> select name from tb_students_info where course_id in (1);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

使用子查询在 tb_students_info 表和 tb_course 表中查询不学习 Java 课程的学生姓名

mysql> select name from tb_students_info where course_id not in (select id from tb_course where course_name = 'Java');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
9 rows in set (0.00 sec)

使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Java 课程的学生姓名

mysql> select name from tb_students_info where course_id = (select id from tb_course where course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

使用<>运算符，在 tb_course 表和 tb_students_info 表中查询出不学习Java课程的学生姓名

mysql> select name from tb_students_info where course_id <> (select id from tb_course where course_name = 'Java');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
9 rows in set (0.00 sec)

查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录

mysql> select * from tb_students_info where exists(select course_name from tb_course where id=1);
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | W    |    185 |         1 |
|  4 | Jane   |   22 | M    |    162 |         3 |
|  5 | Jim    |   24 | W    |    175 |         2 |
|  6 | John   |   21 | W    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | W    |    178 |         5 |
| 10 | Tom    |   23 | W    |    165 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
//not exists&#x7ED3;&#x679C;&#x76F8;&#x53CD;
mysql> select * from tb_students_info where not exists(select course_name from tb_course where id=1);
Empty set (0.00 sec)

由结果可以看到，tb_course 表中存在 id=1 的记录，因此 EXISTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录。

EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。

查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录

mysql> select * from tb_students_info where age>24 and exists (select course_name from tb_course where id=1);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | M    |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)
//not exists&#x76F8;&#x53CD;
mysql> select * from tb_students_info where age>24 and not exists (select course_name from tb_course where id=1);
Empty set (0.00 sec)

4. mysql数据库备份与恢复

数据库备份方案：

  • 全量备份
  • 增量备份
  • 差异备份
  • 冷备份：首先停止数据库，然后复制数据库目录中的文件进行备份，也称为物理备份
    [En]

    Cold backup: first stop the database, and then copy the files in the database directory for backup, also known as physical backup*

  • 热备：在数据库服务正常运行的情况下直接备份数据库数据
    [En]

    Hot backup: backup the database data directly under the normal operation of the database service*

  • 全量备份，增量备份，差异备份

备份方案 特点 全量备份 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 增量备份 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 差异备份 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

备份整个数据库（全备）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zxr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_zxr    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)
[root@localhost ~]# ls
anaconda-ks.cfg  passwd
[root@localhost ~]# mysqldump -uroot -p123456  --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# ls
all-20220728175514.sql  anaconda-ks.cfg  passwd

备份zxr库下的tb_students_info表和tb_course表

[root@localhost ~]# mysqldump -uroot -p123456 zxr tb_students_info tb_course > table-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# ls
all-20220728175514.sql  anaconda-ks.cfg  passwd  table-20220728175759.sql  zxr-20220728175629.sql

备份zxr库

[root@localhost ~]# mysqldump -uroot -p123456  --databases zxr > zxr-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# ls
all-20220728175514.sql  anaconda-ks.cfg  passwd   zxr-20220728181531.sql

模拟删除整个数据库

mysql> drop database zxr;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

恢复整个数据库

[root@localhost ~]# mysql -uroot -p123456 < zxr-20220728181531.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+

删除表

mysql> use zxr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table tb_students_info;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table tb_course;\
Query OK, 0 rows affected (0.01 sec)

恢复表

[root@localhost ~]# mysql -uroot -p123456 zxr < table-20220728184036.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use zxr;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_zxr    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

开启mysql服务器的二进制日志功能

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

server-id=1         //&#x6DFB;&#x52A0;
log-bin=mysql_bin   //&#x6DFB;&#x52A0;
[root@localhost ~]# systemctl restart mysql

对数据库进行完全备份

[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date '+%Y%m%d%H%M%S').sql
//&#x6DFB;&#x52A0;&#x6570;&#x636E;
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
+----+-------------+
6 rows in set (0.00 sec)

mysql> insert tb_course (course_name) values ('Linux');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.01 sec)

//&#x66F4;&#x65B0;&#x6570;&#x636E;
mysql> update tb_course  set course_name = 'Php' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Php         |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | Linux       |
+----+-------------+
7 rows in set (0.00 sec)

//&#x5220;&#x9664;&#x8868;
mysql> show tables;
+------------------+
| Tables_in_zxr    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table tb_students_info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_zxr |
+---------------+
| tb_course     |
+---------------+
1 row in set (0.00 sec)

删除数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database zxr;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新创建的二进制日志

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# ll /opt/data/
total 123896
-rw-r-----. 1 mysql mysql       56 Jul 27 16:28 auto.cnf
-rw-------. 1 mysql mysql     1676 Jul 27 16:28 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 16:28 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 16:28 client-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 16:28 client-key.pem
-rw-r-----  1 mysql mysql      806 Jul 28 18:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Jul 28 19:07 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 27 16:28 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 28 19:07 ibdata1
-rw-r-----  1 mysql mysql 12582912 Jul 28 18:52 ibtmp1
-rw-r-----. 1 mysql mysql    90899 Jul 28 18:44 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 28 19:06 mysql
-rw-r-----  1 mysql mysql        7 Jul 28 18:44 mysql.pid
-rw-r-----  1 mysql mysql     1092 Jul 28 18:59 mysql_bin.000002
-rw-r-----  1 mysql mysql   860115 Jul 28 19:08 mysql_bin.000003
-rw-r-----  1 mysql mysql      154 Jul 28 19:08 mysql_bin.000004
-rw-r-----  1 mysql mysql       57 Jul 28 19:08 mysql_bin.index
drwxr-x---. 2 mysql mysql     8192 Jul 27 16:28 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 27 16:28 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 27 16:28 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 27 16:28 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 27 16:28 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 27 16:28 sys

恢复完全备份

[root@localhost ~]# mysql -uroot -p123456 < all-20220728185240.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use zxr;
mysql> show tables;
+------------------+
| Tables_in_zxr    |
+------------------+
| tb_course        |
| tb_students_info |
+------------------+
2 rows in set (0.00 sec)

恢复差异备份

//&#x627E;&#x5230;&#x5220;&#x9664;&#x5E93;&#x7684;&#x4F4D;&#x7F6E;
mysql> show binlog events in 'mysql_bin.000002';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql_bin.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.38-log, Binlog ver: 4                              |
| mysql_bin.000002 |  123 | Previous_gtids |         1 |         154 |                                                                    |
| mysql_bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                               |
| mysql_bin.000002 |  219 | Query          |         1 |         290 | BEGIN                                                              |
| mysql_bin.000002 |  290 | Table_map      |         1 |         344 | table_id: 140 (zxr.tb_course)                                      |
| mysql_bin.000002 |  344 | Write_rows     |         1 |         390 | table_id: 140 flags: STMT_END_F                                    |
| mysql_bin.000002 |  390 | Xid            |         1 |         421 | COMMIT /* xid=482 */                                               |
| mysql_bin.000002 |  421 | Anonymous_Gtid |         1 |         486 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                               |
| mysql_bin.000002 |  486 | Query          |         1 |         557 | BEGIN                                                              |
| mysql_bin.000002 |  557 | Table_map      |         1 |         611 | table_id: 140 (zxr.tb_course)                                      |
| mysql_bin.000002 |  611 | Update_rows    |         1 |         668 | table_id: 140 flags: STMT_END_F                                    |
| mysql_bin.000002 |  668 | Xid            |         1 |         699 | COMMIT /* xid=484 */                                               |
| mysql_bin.000002 |  699 | Anonymous_Gtid |         1 |         764 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                               |
| mysql_bin.000002 |  764 | Query          |         1 |         891 | use zxr; DROP TABLE tb_students_info /* generated by server */ |
| mysql_bin.000002 |  891 | Anonymous_Gtid |         1 |         956 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                               |
| mysql_bin.000002 |  956 | Query          |         1 |        1045 | drop database zxr                                                  |
| mysql_bin.000002 | 1045 | Rotate         |         1 |        1092 | mysql_bin.000003;pos=4                                             |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
17 rows in set (0.00 sec)

//&#x4F7F;&#x7528;mysqlbinlog&#x6062;&#x590D;&#x5DEE;&#x5F02;&#x5907;&#x4EFD;
[root@localhost ~]# mysqlbinlog --stop-position=956 /opt/data/mysql_bin.000002 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]# mysql -uroot -p123456 -e 'select * from zxr.tb_course;'
mysql: [Warning] Using a password on the command line interface can be insecure.

+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Php         |
|  4 | Go          |
|  5 | C++         |
|  6 | HTML        |
|  7 | Linux       |
+----+-------------+

Original: https://www.cnblogs.com/Their-own/p/16525920.html
Author: 事愿人为
Title: mysql进阶

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505085/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

  • MySQL–创建计算字段

    存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子。  如果想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般包含在不同的表列中。  城市、州和邮政…

    数据库 2023年6月16日
    089

  • 线程的同步

    线程同步机制同步块：Java中提供了同步机制，可以有效的防止资源冲突。同步机制使用 synchronized关键字 使用该关键字的代码块称为同步块。同步块 语法： synchron…

    数据库 2023年6月16日
    0121

  • postman自动化测试

    postman做接口的自动化测试case 记录一次自动化测试的工作，以及该过程中对于测试设计的一些思考。 postman工具 简单介绍，这个工具无论是开发还是测试，使用来调试接口的…

    数据库 2023年6月6日
    0119

  • Hadoop生态二—Hadoop资源管理调度平台Yarn

    Yarn是一个资源调度平台，负责为运算程序提供服务器运算资源，相当于一个分布式的操作系统平台，而mapreduce等运算程序则相当于运行于操作系统之上的应用程序Yarn是一个资源调…

    数据库 2023年6月6日
    0114

  • Python–socket

    socket网络编程：socket、socketserver socket：{server，client} socket_server示例： socket_client示例： 应用…

    数据库 2023年6月9日
    076

  • MySQL max() min() 函数取值错误

    今天日志出现异常，一步一步debug发现SQL语句返回值出错，进一步发现是max()函数返回出错。点击跳转解决办法，赶时间的朋友可以去获得答案。当然我还是希望大伙看看原由。 sel…

    数据库 2023年6月16日
    0178

  • SQLZOO练习7–Using NULL

    teacher表： iddeptnamephonemobile 101 1 Shrivell 2753 07986 555 1234 102 1 Throd 2754 07122 …

    数据库 2023年5月24日
    076

  • 使用Docker安装FastDFS

    1. 获取镜像 可以利用已有的FastDFS Docker镜像来运行FastDFS。 获取镜像可以通过下载： sudo docker image pull delron/fastd…

    数据库 2023年6月14日
    095

  • 如何使用原生的Hystrix

    什么是Hystrix 前面已经讲完了 Feign 和 Ribbon，今天我们来研究 Netflix 团队开发的另一个类库–Hystrix。 从抽象层面看， Hystri…

    数据库 2023年6月6日
    095

  • MVCC多版本并发控制的理解

    前置知识 当前读与快照读 当前读什么是当前读：读取的是 &#x6700;&#x65B0;的数据，不会读到老数据。 &#x4F55;&#x65F6;&…

    数据库 2023年6月14日
    076

  • 牛客SQL刷题第三趴——SQL必知必会

    【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots …

    数据库 2023年6月16日
    097

  • 一文说透 MySQL JSON 数据类型（收藏）

    JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。 相对字符类型，原生的 JS…

    数据库 2023年5月24日
    080

  • ShardingSphere-Proxy 前端协议问题排查方法及案例

    ShardingSphere-Proxy 是 Apache ShardingSphere 的接入端之一，其定位为透明化的数据库代理。ShardingSphere-Proxy 实现了…

    数据库 2023年6月16日
    086

  • 一次线上MySQL死锁告警原因排查

    项目场景：一次线上MySQL死锁告警原因排查最近处理了一次在线数据警报，记录下来。 [En] Recently handled an online data alarm, reco…

    数据库 2023年5月24日
    056

  • Golang context

    Context Go 语言中提供了 context 包，通过显示传递 context, 实现请求级别的元数据、取消信号、终止信号的传递。context 包提供了从现有的上下文值（c…

    数据库 2023年6月16日
    087

  • 容器化 | 在 S3 实现定时备份

    让我们来看看该功能是如何使用的。 [En] Let’s take a look at how this feature is used. 可用 Cron 表达式（与 L…

    数据库 2023年5月24日
    070
亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球