mysql進階
目錄- mysql進階
- 1.二進制格式mysql安裝
- 2.mysql組態檔
- 2.1 資料庫密碼破解
- 3.多表聯合查詢
- 3.1 什么是多表聯合查詢
- 3.2 交叉連接（CROSS JOIN）
- 3.2.1 笛卡爾積
- 3.2.2 交叉連接
- 3.3 內連接
- 3.4 外連接
- 3.4.1 左連接
- 3.4.2 右連接
- 3.5 分組查詢
- 3.5.1 GROUP BY單獨使用
- 3.5.2 GROUP BY 與 GROUP_CONCAT()
- 3.5.3 GROUP BY 與聚合函式
- 3.5.4 GROUP BY 與 WITH ROLLUP
- 3.6 子查詢
- 4. mysql資料庫備份與恢復
- 4.1 資料庫常用備份方案
- 4.2 mysql備份工具mysqldump
- 4.3 mysql資料恢復
- 4.4 差異備份與恢復
- 4.4.1 mysql差異備份
- 4.4.2mysql差異備份恢復
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
//再次注意，這個密碼是隨機的，你的不會跟我一樣，一定要記住這個密碼，因為一會登錄時會用到
生成組態檔
[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
//先找到啟動腳本檔案位置
[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
//在進行啟動mysql,發現3306埠已監聽
[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 [::]:*
//將腳本加入到系統里去 //使用service控制MySQL啟動或關閉
[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/ //添加安裝位置
datadir=/opt/data/ //添加資料庫存放位置
[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 [::]:*
//組態檔被洗掉后使用不了
[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.
//使用systemctl管理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. //啟動報錯需要關閉防火墻和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 //修改
[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 [::]:*
修改密碼，需要使用臨時密碼進行登錄
[root@localhost ~]# ls
anaconda-ks.cfg passwd
[root@localhost ~]# cat passwd
RIa<P+tD%6qV
[root@localhost ~]# mysql -uroot -p'RIa<P+tD%6qV' //登錄報錯顯示需要libncurses.so.5
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# dnf provides libncurses.so.5 //查看這個在那個軟體包里面
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:37:59 ago on Wed Jul 27 16:33:32 2022.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo : base
Matched from:
Provide : libncurses.so.5
[root@localhost ~]# dnf -y install ncurses-compat-libs //下載包
[root@localhost ~]# mysql -uroot -p'RIa<P+tD%6qV'
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 2
Server version: 5.7.38
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('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>
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將無法正常處理連接請求 |
2.1 資料庫密碼破解
資料庫密碼破解：
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 //添加一句 表示跳過所有授權表
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
//洗掉添加的那一句
[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
//測驗密碼是否能登錄
[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.多表聯合查詢
3.1 什么是多表聯合查詢
前面所講的查詢陳述句都是針對一個表的，但是在關系型資料庫中，表與表之間是有聯系的，所以在實際應用中，經常使用多表查詢，多表查詢就是同時查詢兩個或兩個以上的表，
在 MySQL 中，多表查詢主要有交叉連接、內連接、外連接、分組查詢與子查詢等5種，
3.2 交叉連接（CROSS JOIN）
3.2.1 笛卡爾積
交叉連接(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 的結果就叫做兩個集合的笛卡爾積，
并且，從以上結果我們可以看出：
- 兩個集合相乘，不滿足交換率，即 A×B≠B×A，
- A 集合和 B 集合的笛卡爾積是 A 集合的元素個數 × B 集合的元素個數，
多表查詢遵循的演算法就是以上提到的笛卡爾積，表與表之間的連接可以看成是在做乘法運算，在實際應用中，應避免使用笛卡爾積，因為笛卡爾積中容易存在大量的不合理資料，簡單來說就是容易導致查詢結果重復、混亂，
3.2.2 交叉連接
交叉連接的語法格式如下：
SELECT <欄位名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <欄位名> FROM <表1>, <表2> [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)
3.3 內連接
內連接（INNER JOIN）主要通過設定連接條件的方式，來移除查詢結果中某些資料行的交叉連接，簡單來說，就是利用條件運算式來消除交叉連接的某些資料行，
內連接使用 INNER JOIN 關鍵字連接兩張表，并使用 ON 子句來設定連接條件，如果沒有連接條件，INNER JOIN 和 CROSS JOIN 在語法上是等同的，兩者可以互換，
內連接的語法格式如下：
SELECT <欄位名> FROM <表1> INNER JOIN <表2> [ON子句];
語法說明如下，
- 欄位名：需要查詢的欄位名稱，
- <表1><表2>：需要內連接的表名，
- INNER JOIN ：內連接中可以省略 INNER 關鍵字，只用關鍵字 JOIN，
- ON 子句：用來設定內連接的連接條件，
INNER JOIN 也可以使用 WHERE 子句指定連接條件，但是 INNER JOIN ... ON 語法是官方的標準寫法，而且 WHERE 子句在某些時候會影響查詢的性能，
多個表內連接時，在 FROM 后連續使用 INNER JOIN 或 JOIN 即可，
內連接可以查詢兩個或兩個以上的表，為了更好的理解，暫時只講解兩個表的連接查詢，
在這里的查詢陳述句中，兩個表之間的關系通過 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)
//統計人數
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)
//列出人數
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)
//分別列出學習MySQL和python人的名字
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 陳述句后面寫上表的別名.列名，
3.4 外連接
內連接的查詢結果都是符合連接條件的記錄，而外連接會先將連接的表分為基表和參考表，再以基表為依據回傳滿足和不滿足條件的記錄，
外連接可以分為左外連接和右外連接2種，下面根據實體分別介紹左外連接和右外連接，
3.4.1 左連接
左外連接又稱為左連接，使用 LEFT OUTER JOIN 關鍵字連接兩個表，并使用 ON 子句來設定連接條件，
左連接的語法格式如下：
SELECT <欄位名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>;
語法說明如下:
- 欄位名：需要查詢的欄位名稱，
- <表1><表2>：需要左連接的表名，
- 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，
3.4.2 右連接
右外連接又稱為右連接，右連接是左連接的反向連接，使用 RIGHT OUTER JOIN 關鍵字連接兩個表，并使用 ON 子句來設定連接條件，
右連接的語法格式如下：
SELECT <欄位名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>;
語法說明如下:
- 欄位名：需要查詢的欄位名稱，
- <表1><表2>：需要右連接的表名，
- 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 即可，
使用外連接查詢時，一定要分清需要查詢的結果，是需要顯示左表的全部記錄還是右表的全部記錄，然后選擇相應的左連接和右連接，
3.5 分組查詢
在 MySQL 中，GROUP BY 關鍵字可以根據一個或多個欄位對查詢結果進行分組，
使用 GROUP BY 關鍵字的語法格式如下：
GROUP BY <欄位名>
其中，"欄位名"表示需要分組的欄位名稱，多個欄位時用逗號隔開，
3.5.1 GROUP BY單獨使用
單獨使用 GROUP BY 關鍵字時，查詢結果會只顯示每個分組的第一條記錄，
下面根據 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)
3.5.2 GROUP BY 與 GROUP_CONCAT()
GROUP BY 關鍵字可以和 GROUP_CONCAT() 函式一起使用，GROUP_CONCAT() 函式會把每個分組的欄位值都顯示出來，
下面根據 tb_students_info 表中的 sex 欄位進行分組查詢，使用 GROUP_CONCAT() 函式將每個分組的 name 欄位的值都顯示出來，
//分組查詢
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)
3.5.3 GROUP BY 與聚合函式
在資料統計時，GROUP BY 關鍵字經常和聚合函式一起使用，
聚合函式包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()，其中，COUNT() 用來統計記錄的條數；SUM() 用來計算欄位值的總和；AVG() 用來計算欄位值的平均值；MAX() 用來查詢欄位的最大值；MIN() 用來查詢欄位的最小值，
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)
3.5.4 GROUP BY 與 WITH ROLLUP
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)
3.6 子查詢
子查詢是 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結果相反
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相反
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資料庫備份與恢復
4.1 資料庫常用備份方案
資料庫備份方案：
- 全量備份
- 增量備份
- 差異備份
- 冷備份：先把資料庫停掉，然后拷貝資料庫目錄下的檔案進行備份 又稱物理備份
- 熱備份：資料庫服務正常運行情況，直接對資料庫的資料直接進行備份
- 全量備份，增量備份，差異備份
| 備份方案 | 特點 |
|---|---|
| 全量備份 | 全量備份就是指對某一個時間點上的所有資料或應用進行的一個完全拷貝， 資料恢復快， 備份時間長 |
| 增量備份 | 增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份 與前一次相比增加和者被修改的檔案，這就意味著，第一次增量備份的物件 是進行全備后所產生的增加和修改的檔案；第二次增量備份的物件是進行第一次增量 備份后所產生的增加和修改的檔案，如此類推， 沒有重復的備份資料 備份時間短 恢復資料時必須按一定的順序進行 |
| 差異備份 | 備份上一次的完全備份后發生變化的所有檔案， 差異備份是指在一次全備份后到進行差異備份的這段時間內 對那些增加或者修改檔案的備份，在進行恢復時，我們只需對第一次全量備份和最后一次差異備份進行恢復， |
4.2 mysql備份工具mysqldump
//語法：
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
4.3 mysql資料恢復
模擬洗掉整個資料庫
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)
4.4 差異備份與恢復
4.4.1 mysql差異備份
開啟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 //添加
log-bin=mysql_bin //添加
[root@localhost ~]# systemctl restart mysql
對資料庫進行完全備份
[root@localhost ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=https://www.cnblogs.com/Their-own/archive/2022/08/06/2 --all-databases --delete-master-logs > all-$(date'+%Y%m%d%H%M%S').sql
//添加資料
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)
//更新資料
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)
//洗掉表
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)
4.4.2mysql差異備份恢復
洗掉資料庫
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)
恢復差異備份
//找到洗掉庫的位置
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)
//使用mysqlbinlog恢復差異備份
[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 |
+----+-------------+
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/501136.html
標籤：其他
上一篇：史上最全Mysql規范