MySQL进阶
使用二进制安装MySQL
# 也可以直接使用wget下载或者下面的另一种方式
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
1.下载，浏览器输入网址 downloads.mysql.com,点击标识的红线区域（下载归档）
2.点击第一个（MySQL社区服务器）
3.版本这里选择的是5.7.34
系统选择Linux通用的
操作系统版本选择64位的
4.下载标识的这个
将其上传到/usr/src目录下
[root@localhost src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
创建用户和组
[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
将其解压到/usr/local/目录下
[root@localhost src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.34-linux-glibc2.12-x86_64 share
创建软连接
[root@localhost local]# ln -s mysql-5.7.34-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ll
total 0
......
lrwxrwxrwx. 1 root root 35 Aug 25 08:33 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Aug 25 08:32 mysql-5.7.34-linux-glibc2.12-x86_64
修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost local]# ll
total 0
.......
lrwxrwxrwx. 1 mysql mysql 35 Aug 25 08:33 mysql -> mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Aug 25 08:32 mysql-5.7.34-linux-glibc2.12-x86_64
添加环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
建立数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug 25 08:48 data
初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2021-08-25T12:50:24.131273Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-25T12:50:24.270072Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-25T12:50:24.295810Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-25T12:50:24.352039Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 03d19f26-05a3-11ec-bf6e-000c29bb4cb2.
2021-08-25T12:50:24.352561Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-25T12:50:25.039258Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-25T12:50:25.357745Z 1 [Note] A temporary password is generated for root@localhost: gr;JyBKJF3(u
//请注意，这个命令的最后会生成一个临时密码，此处密码是 gr;JyBKJF3(u
//再次注意，这个密码是随机的，每个人都不一样，一定要记住这个密码，因为一会登录时会用到
//将临时密码写入到一个文件里，等下会用到
[root@localhost ~]# echo "gr;JyBKJF3(u" > passwd
[root@localhost ~]# cat passwd
gr;JyBKJF3(u
查看是否缺少依赖包
[root@localhost ~]# ldd /usr/local/mysql/bin/mysql(ldd是看某一个程序文件它所依赖的包，如果没有，就不能用，就需要用yum安装,查找哪个包提供的（yum whatprovides pkgs_name）)
linux-vdso.so.1 (0x00007ffdf09ea000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fdfeb8dd000)
librt.so.1 => /lib64/librt.so.1 (0x00007fdfeb6d4000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007fdfeb4d0000)
libncurses.so.5 => /lib64/libncurses.so.5 (0x00007fdfeb2aa000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fdfeaf15000)
libm.so.6 => /lib64/libm.so.6 (0x00007fdfeab93000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fdfea97b000)
libc.so.6 => /lib64/libc.so.6 (0x00007fdfea5b9000)
libtinfo.so.5 => /lib64/libtinfo.so.5 (0x00007fdfea38e000)
/lib64/ld-linux-x86-64.so.2 (0x00007fdfebafd000)
生成配置文件
[root@localhost ~]# cat > /etc/my.cnf <<EOF
> [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
> EOF
修改配置文件
[root@localhost support-files]# pwd
/usr/local/mysql/support-files
[root@localhost support-files]# vim mysql.server
44 # overwritten by settings in the MySQL configuration files.
45
46 basedir=/usr/local/mysql # 安装mysql的位置
47 datadir=/opt/data # 数据存放的位置
使用脚本控制服务启动
[root@localhost ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
尝试登录数据库
[root@localhost ~]# cat passwd
gr;JyBKJF3(u
[root@localhost ~]# mysql -uroot -p'gr;JyBKJF3(u'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory # 提示没有 "libncurses.so.5"
[root@localhost ~]# yum whatprovides libncurses.so.5 # 查找并安装
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:00:52 ago on Wed Aug 25 09:25:55 2021.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo : baseos
Matched from:
Provide : libncurses.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs # 安装
Failed to set locale, defaulting to C.UTF-8
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
.....
再次登录并修改密码
# 登录
[root@localhost ~]# mysql -uroot -p'gr;JyBKJF3(u'
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.34
Copyright (c) 2000, 2021, 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('redhat'); #修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -predhat # 使用修改后的密码登录成功
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.34 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, 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>
配置启动脚本
# 找一个文件修改
[root@localhost system]# pwd
/usr/lib/systemd/system
[root@localhost system]# cp sshd.service mysqld.service # 复制一份内容sshd.service到mysqld.service
[root@localhost system]# cat mysqld.service # 修改完成后
[Unit]
Description=mysql server daemon # mysql服务器守护程序
After=network.target # 网络之后启动
[Service]
Type=forking # 类型为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 system]# systemctl daemon-reload
[root@localhost system]# systemctl start mysqld
[root@localhost system]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost system]# systemctl status mysqld
● mysqld.service - mysql server daemon
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2021-08-25 09:56:30 EDT; 12s ago
MySQL配置文件
mysql`的配置文件为`/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
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将无法正常处理连接请求 |
MySQL数据备份和恢复
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份是指对某一时间上的所有数据或应用进行的一个完全拷贝。 数据恢复快， 备份时间长。 |
| 增量备份 | 增量备份是指全量备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或被修改的文件。这意味着第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
mysql备份工具:mysqldump
语法：
mysqldump [options] db_name [tbl_name ...] # 备份库里的表
mysqldump [options] --databases db_name ... # 备份库
mysqldump [options] --all-databases # 备份所有的库
常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
备份整个数据库（全备）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| file |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -predhat --all-databases > all-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210825.sql anaconda-ks.cfg passwd
备份库里的表 columns_priv 和 data
mysql> use file
mysql> show tables;
+---------------------------+
| Tables_in_file |
+---------------------------+
| columns_priv |
| data |
......
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -predhat file data columns_priv > tables-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210825.sql anaconda-ks.cfg passwd tables-20210825.sql
备份库 file
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| file |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot -predhat --databases file > file-$(date '+%Y%m%d').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210825.sql anaconda-ks.cfg file-20210825.sql passwd tables-20210825.sql
模拟误删除库 file 操作
mysql> drop database file;
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)
MySQL数据恢复
恢复删除的 file 库
[root@localhost ~]# mysql -uroot -predhat < file-20210825.sql # 恢复
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e 'show databases;' # 查看
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| file |
| mysql |
| performance_schema |
| sys |
+--------------------+
备份 file 库里的表 删除 file 库里的表 恢复
[root@localhost ~]# mysqldump -uroot -predhat file wangwu > table-20210825.sql # 备份表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210825.sql file-20210825.sql passwd anaconda-ks.cfg mysql-20210825.sql table-20210825.sql
mysql> use file
mysql> drop table wangwu; # 删除表
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -predhat file < table-20210825.sql # 恢复 file 库的表
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use file
mysql> show tables;
+----------------+
| Tables_in_file |
+----------------+
| wangwu |
+----------------+
1 row in set (0.00 sec)
mysql> exit
Bye
差异备份和恢复
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
server-id = 7 # 设置服务器标识符
log-bin=mysql_bin # 开启二进制日志功能
[root@localhost ~]# systemctl restart mysql # 重启数据库
对数据库进行完全备份
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| file |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from file;
+----------------+
| Tables_in_file |
+----------------+
| wangwu |
+----------------+
1 row in set (0.00 sec)
mysql> select * from file.wangwu;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | dfd |
| 2 | 21 | qwe |
| 3 | 45 | ok |
| 4 | 23 | fdf |
+----+------+------+
4 rows in set (0.01 sec)
mysql> exit
Bye
# 完全备份
[root@localhost ~]# mysqldump -uroot -predhat --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-databases.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-databases.sql
# 增加新的内容
mysql> use file
mysql> create table syxq(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> desc syxq;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert syxq(name,age) values('qwe',21),('xyz',22),('zxc',23),('er',25);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> selct * from syxq;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | qwe | 21 |
| 2 | xyz | 22 |
| 3 | zxc | 23 |
| 4 | er | 25 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> show tables from file;
+----------------+
| Tables_in_file |
+----------------+
| wangwu |
| syxq |
+----------------+
# 模拟误删除
[root@localhost ~]# mysql -uroot -predhat -e "drop database file;" # 删除库 file
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e "show databases;" # 查看已经删除库 file
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 刷新创建新的二进制日志
[root@localhost ~]# cd /opt/data/
[root@localhost data]# ll
......
-rw-r-----. 1 mysql mysql 889 Aug 26 04:19 mysql_bin.000002 #只有一个二进制日志
-rw-r-----. 1 mysql mysql 38 Aug 26 04:19 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 Aug 25 08:50 performance_schema
-rw-------. 1 mysql mysql 1676 Aug 25 08:50 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 25 08:50 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 25 08:50 server-cert.pem
-rw-------. 1 mysql mysql 1680 Aug 25 08:50 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 25 08:50 sys
[root@localhost data]# mysqladmin -uroot -predhat flush-logs # 刷新
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ll
......
-rw-r-----. 1 mysql mysql 889 Aug 26 04:19 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 Aug 26 04:19 mysql_bin.000003 # 创建新的二进制日志
-rw-r-----. 1 mysql mysql 38 Aug 26 04:19 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 Aug 25 08:50 performance_schema
-rw-------. 1 mysql mysql 1676 Aug 25 08:50 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Aug 25 08:50 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Aug 25 08:50 server-cert.pem
-rw-------. 1 mysql mysql 1680 Aug 25 08:50 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Aug 25 08:50 sys
# 恢复完全备份
[root@localhost ~]# mysql -uroot -predhat < all-databases.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -predhat -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| file |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -predhat -e "show tables from file;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_file |
+----------------+
| wangwu |
+----------------+
[root@localhost ~]# mysql -uroot -predhat -e "select * from file.wangwu";
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | dfd |
| 2 | 21 | qwe |
| 3 | 45 | ok |
| 4 | 23 | fdf |
+----+------+------+
恢复差异备份（恢复新增的内容）
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 | 7 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 7 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 7 | 392 | use `file`; create table syxq(id int not null primary key auto_increment,name varchar(20) not null,age tinyint) |
| mysql_bin.000002 | 392 | Anonymous_Gtid | 7 | 457 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 457 | Query | 7 | 529 | BEGIN |
| mysql_bin.000002 | 529 | Table_map | 7 | 580 | table_id: 144 (file.syxq) |
| mysql_bin.000002 | 580 | Write_rows | 7 | 654 | table_id: 144 flags: STMT_END_F |
| mysql_bin.000002 | 654 | Xid | 7 | 685 | COMMIT /* xid=491 */ |
| mysql_bin.000002 | 685 | Anonymous_Gtid | 7 | 750 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 750(恢复这里就是删除库之前的操作)| Query 7 | 842 | drop database file(误删除库的操作) |
| mysql_bin.000002 | 842 | Rotate | 7 | 889 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------+
12 rows in set (0.00 sec)
# 恢复新增加的内容
[root@localhost data]# mysqlbinlog --stop-position=750 /opt/data/mysql_bin.000002 |mysql -uroot -predhat
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# mysql -uroot -predhat -e "show tables from file;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------+
| Tables_in_file |
+----------------+
| syxq |
| wangwu |
+----------------+
[root@localhost data]# mysql -uroot -predhat -e "select * from file.syxq;" # 新增内容恢复成功
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | qwe | 21 |
| 2 | xyz | 22 |
| 3 | zxc | 23 |
| 4 | er | 25 |
+----+------+------+
多表查询
order by 排序
# 降序排序
mysql> select * from file.wangwu order by age desc;
+----+------+------+
| id | age | name |
+----+------+------+
| 3 | 45 | ok |
| 4 | 23 | fdf |
| 2 | 21 | qwe |
| 1 | 20 | dfd |
+----+------+------+
4 rows in set (0.00 sec)
# 升序排序
mysql> select * from file.wangwu order by age;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | dfd |
| 2 | 21 | qwe |
| 4 | 23 | fdf |
| 3 | 45 | ok |
+----+------+------+
4 rows in set (0.00 sec)
group by 计数(count)，算平均值(avg)，求和(sum)等等
mysql> select * from file.wangwu;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | dfd |
| 2 | 21 | qwe |
| 3 | 45 | ok |
| 4 | 23 | fdf |
| 5 | 20 | dfd |
+----+------+------+
5 rows in set (0.00 sec)
mysql> select count(age),age from file.wangwu group by age; # 记数，有两个年龄是20
+------------+------+
| count(age) | age |
+------------+------+
| 2 | 20 |
| 1 | 21 |
| 1 | 23 |
| 1 | 45 |
+------------+------+
4 rows in set (0.00 sec)
# 求年龄的平均值
mysql> select avg(age) from file.wangwu;
+----------+
| avg(age) |
+----------+
| 25.8000 |
+----------+
# 求年龄的和
mysql> select sum(age) from file.wangwu;
+----------+
| sum(age) |
+----------+
| 129 |
+----------+
inner join 查询多张表中都存在的数据
mysql> use file
Database changed
mysql> show tables;
+----------------+
| Tables_in_file |
+----------------+
| syxq |
| wangwu |
+----------------+
2 rows in set (0.00 sec)
mysql> select * from wangwu;
+----+------+------+
| id | age | name |
+----+------+------+
| 1 | 20 | dfd |
| 2 | 21 | qwe |
| 3 | 45 | ok |
| 4 | 23 | fdf |
| 5 | 20 | dfd |
+----+------+------+
5 rows in set (0.00 sec)
mysql> select * from syxq;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | qwe | 21 |
| 2 | xyz | 22 |
| 3 | zxc | 23 |
| 4 | er | 25 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> select wangwu.*,syxq.* from wangwu inner join syxq on wangwu.id = syxq.id;
+----+------+------+----+------+------+
| id | age | name | id | name | age |
+----+------+------+----+------+------+
| 1 | 20 | dfd | 1 | qwe | 21 |
| 2 | 21 | qwe | 2 | xyz | 22 |
| 3 | 45 | ok | 3 | zxc | 23 |
| 4 | 23 | fdf | 4 | er | 25 |
+----+------+------+----+------+------+
4 rows in set (0.00 sec)
left join显示以左边表为主的内容，右边表有匹配左边表内容时完整显示，无匹配时留NULL
mysql> select wangwu.*,syxq.* from wangwu left join syxq on wangwu.id = syxq.id;
+----+------+------+------+------+------+
| id | age | name | id | name | age |
+----+------+------+------+------+------+
| 1 | 20 | dfd | 1 | qwe | 21 |
| 2 | 21 | qwe | 2 | xyz | 22 |
| 3 | 45 | ok | 3 | zxc | 23 |
| 4 | 23 | fdf | 4 | er | 25 |
| 5 | 20 | dfd | NULL | NULL | NULL |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
right join显示以右边表为主的内容，左边表有匹配右边表内容时完整显示，无匹配时留NULL
mysql> select wangwu.*,syxq.* from wangwu right join syxq on wangwu.id = syxq.id;
+------+------+------+----+------+------+
| id | age | name | id | name | age |
+------+------+------+----+------+------+
| 1 | 20 | dfd | 1 | qwe | 21 |
| 2 | 21 | qwe | 2 | xyz | 22 |
| 3 | 45 | ok | 3 | zxc | 23 |
| 4 | 23 | fdf | 4 | er | 25 |
+------+------+------+----+------+------+
4 rows in set (0.00 sec)
9217
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
