MYSQL进阶
1 二进制安装mysql
//下载软件包拖进/usr/src/下面 网址https://downloads.mysql.com/archives/community/
[root@yzy ~]# cd /usr/src/
[root@yzy src]# ls
debug kernels mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
//创建用户和组
[root@yzy src]# useradd -r -M -s /sbin/nologin mysql
[root@yzy src]# grep mysql /etc/group
mysql:x:972:
//解压软件至/usr/local/
[root@yzy src]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@yzy src]# ls /usr/local/
mysql-5.7.34-linux-glibc2.12-x86_64
[root@yzy local]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/ /usr/local/mysql
//修改目录/usr/local/mysql的属主属组
[root@yzy local]# chown -R mysql.mysql /usr/local/mysql
[root@yzy local]# ll
总用量 0
drwxr-xr-x. 6 root root 58 7月 11 01:43 apr
drwxr-xr-x. 5 root root 43 7月 11 01:52 apr-util
drwxr-xr-x. 2 root root 6 8月 12 2018 bin
drwxr-xr-x. 2 root root 6 8月 12 2018 etc
drwxr-xr-x. 2 root root 6 8月 12 2018 games
drwxr-xr-x. 14 root root 164 7月 11 02:02 httpd
drwxr-xr-x. 2 root root 6 8月 12 2018 include
drwxr-xr-x. 2 root root 6 8月 12 2018 lib
drwxr-xr-x. 2 root root 6 8月 12 2018 lib64
drwxr-xr-x. 2 root root 6 8月 12 2018 libexec
lrwxrwxrwx 1 mysql mysql 47 8月 26 06:44 mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64/
drwxr-xr-x 9 root root 172 8月 26 06:45 mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 8月 12 2018 sbin
drwxr-xr-x. 5 root root 49 3月 1 10:05 share
drwxr-xr-x. 2 root root 6 8月 12 2018 src
//添加环境变量
[root@yzy local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@yzy local]# . /etc/profile.d/mysql.sh
[root@yzy local]# echo $PATH
/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
//建立数据存放目录
[root@yzy mysql]# mkdir /opt/data
[root@yzy mysql]# chown -R mysql.mysql /opt/data/
[root@yzy mysql]# ll /opt/
总用量 4
drwxr-xr-x 2 mysql mysql 6 8月 26 06:51 data
//初始化数据库
[root@yzy ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2021-08-26T12:54:34.536540Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-26T12:54:34.817882Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-26T12:54:34.852168Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-26T12:54:34.926986Z 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: 000d429a-065c-11ec-be62-000c299fb9ba.
2021-08-26T12:54:34.928529Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-26T12:54:35.858130Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-26T12:54:35.991763Z 1 [Note] A temporary password is generated for root@localhost: CN#7eoWTlx;m
//这个命令的最后会生成一个临时密码,此处密码是 CN#7eoWTlx;m
//这个密码是随机的，一定要记住这个密码，因为一会登录时会用到
//生成配置文件
[root@yzy ~]# 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@yzy ~]# 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
//配置服务启动脚本
[root@yzy mysql]# cd support-files/
[root@yzy support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@yzy support-files]# vim mysql.server
46 basedir= /usr/local/mysql
47 datadir= /opt/data
[root@yzy ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/wxy.err'.
SUCCESS!
[root@yzy ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@yzy ~]# yum whatprovides libncurses.so.5
[root@yzy ~]# yum -y install ncurses-compat-libs
//修改密码
//使用临时密码登录
[root@yzy ~]# mysql -uroot -p'CN#7eoWTlx;m'
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('1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//开机自启mysql
[root@yzy ~]# cd /usr/lib/systemd/system
[root@yzy system]# cp sshd.service mysqld.service
[root@yzy system]# vim mysqld.service
[root@yzy system]# cat mysqld.service
[Unit]
Description=mysql server daemon
After=network.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@yzy ~]# systemctl daemon-reload
[root@yzy ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@yzy ~]# systemctl start mysqld
2 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将无法正常处理连接请求 |
3 mysql数据库备份与恢复
3.1 数据库常用备份方案
-
全量备份
-
增量备份
-
差异备份
方案 特点 全量 对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长 增量 在一次全备份或上一次增量备份后，以后每次的份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。没有重复的备份数据备份时间短恢复数据时必须按一定的顺序进行 差异 在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
3.2 mysql备份工具：mysqldump
语法：
mysqldump [OPTIONS] database_name [tables_name ...]
#备份数据库中的一个或多个表
mysqldump [OPTIONS] --all-databases [OPTIONS]
#备份所有的数据库
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
#备份其中一个或多个数据库,使用空格分隔
常用的OPTIONS
-uUSERNAME #指定数据库用户名
-hHOST #指定服务器主机，请使用ip地址
-pPASSWORD #指定数据库用户的密码
-P# #指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
--all-databases,-A #备份所有数据库
--databases,-B #要备份的数据库，可以同时备份多个，使用空格分隔
备份数据库 使用全量备份
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database y;
Query OK, 1 row affected (0.00 sec)
mysql> use y;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_weixiaoya |
+---------------------+
| student |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
[root@yzy ~]# mysqldump -uroot -p1 --all-databases> all-$(date '+%Y%m%d').sql
[root@yzy ~]# ls
all-20210826.sql
[root@yzy ~]# mysql -uroot -p1 -e 'drop database weixiaoya;'
[root@yzy ~]# mysql -uroot -p < all-20210826.sql
[root@yzy ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| y |
+--------------------+
//备份yy库
mysqldump -uroot -p --databases yy > yy_database_backup.sql
恢复数据库yy：
[root@yzy ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| y |
+--------------------+
[root@yzy ~]# ls
all-20210826.sql yy_database_backup.sql
[root@yzy ~]# mysql -uroot -p -hlocalhost < yy_database_backup.sql
[root@yzy ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| y |
| yy |
+--------------------+
3.3 差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@yzy ~]# 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=1 #设置服务器标识符；只能是数字
log-bin=mysql_bin #开启二进制日志功能
重启数据库服务
[root@yzy ~]# service mysqld start
对数据库进行完全备份
[root@yzy ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20210826.sql
[root@yzy ~]# ls
all-20210826.sql
添加内容
mysql> use yy;
Database changed
mysql> create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)
mysql> insert student(name,age) values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangsan',26),('zhangsan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangsan | 26 |
| 6 | zhangsan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
mysql差异备份恢复
[root@wxy ~]# mysql -uroot -p1 -e 'drop database yy;'
[root@wxy ~]# mysql -uroot -p1 -e 'show databases;'mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| y |
+--------------------+
刷新二进制文件
[root@yzy ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@yzy ~]# ll /opt/data/
总用量 122992
-rw-r----- 1 mysql mysql 56 8月 26 08:15 auto.cnf
-rw------- 1 mysql mysql 1680 8月 26 08:15 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 8月 26 08:15 ca.pem
-rw-r--r-- 1 mysql mysql 1112 8月 26 08:15 client-cert.pem
-rw------- 1 mysql mysql 1680 8月 26 08:15 client-key.pem
-rw-r----- 1 mysql mysql 571 8月 26 09:13 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月 26 09:24 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月 26 09:24 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月 26 08:15 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月 26 09:16 ibtmp1
drwxr-x--- 2 mysql mysql 4096 8月 26 09:01 mysql
-rw-r----- 1 mysql mysql 996 8月 26 09:24 mysql_bin.000003
-rw-r----- 1 mysql mysql 154 8月 26 09:24 mysql_bin.000004
-rw-r----- 1 mysql mysql 38 8月 26 09:24 mysql_bin.index
-rw-r----- 1 mysql mysql 7 8月 26 09:13 mysql.pid
drwxr-x--- 2 mysql mysql 8192 8月 26 08:15 performance_schema
-rw------- 1 mysql mysql 1676 8月 26 08:15 private_key.pem
-rw-r--r-- 1 mysql mysql 452 8月 26 08:15 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 8月 26 08:15 server-cert.pem
-rw------- 1 mysql mysql 1680 8月 26 08:15 server-key.pem
drwxr-x--- 2 mysql mysql 8192 8月 26 08:15 sys
drwxr-x--- 2 mysql mysql 58 8月 26 09:01 y
恢复完全备份
[root@yzy ~]# mysql -uroot -p < all-20210826.sql
Enter password:
[root@yzy ~]# mysql -uroot -p1 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| y |
| yy |
+--------------------+
恢复差异备份
检查误删数据库的位置
mysql> show binlog events in 'mysql_bin.000003';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| mysql_bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql_bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 219 | Query | 1 | 399 | use `yy`; create table student(id int(11) not null primary key auto_increment,name varchar(100) not null,age tinyint(4)) |
| mysql_bin.000003 | 399 | Anonymous_Gtid | 1 | 464 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 464 | Query | 1 | 534 | BEGIN |
| mysql_bin.000003 | 534 | Table_map | 1 | 586 | table_id: 141 (yy.student) |
| mysql_bin.000003 | 586 | Write_rows | 1 | 767 | table_id: 141 flags: STMT_END_F |
| mysql_bin.000003 | 767 | Xid | 1 | 798 | COMMIT /* xid=474 */ |
| mysql_bin.000003 | 798 | Anonymous_Gtid | 1 | 863 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000003 | 863 | Query | 1 | 949 | drop database yy |
| mysql_bin.000003 | 949 | Rotate | 1 | 996 | mysql_bin.000004;pos=4 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.01 sec)
使用 mysqlbinlog 恢复差异备份
[root@yzy ~]# mysqlbinlog --stop-position=949 /opt/data/mysql_bin.000004 | mysql -uroot -p
Enter password:
[root@yzy ~]# mysql -uroot -p -e "show tables from y;"
Enter password:
+---------------------+
| Tables_in_weixiaoya |
+---------------------+
| student |
+---------------------+
4 密码破解
1 绕过密码验证 跳过skip-grant-tables加入/etc/my.cnf
[root@yzy ~]# 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=1
log-bin=mysql_bin
skip_grant-tables
2 重启服务
[root@yzy ~]# systemctl restart mysqld.service
3 修改密码
[root@yzy ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log 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> 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
Database changed
mysql> update user set authentication_string = password('111') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
4 恢复密码验证 从/etc/my.cnf文件中删除skip-grant-tables
[root@yzy ~]# vim /etc/my.cnf
[root@yzy ~]# 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=1
log-bin=mysql_bin
5 重启服务
[root@yzy ~]# systemctl restart mysqld.service
5 SQL多表查询
| order by | 排序 |
|---|---|
| group by | 计数、算平均值、求和等等 |
| inner join | 查询多张表中都存在的数据 |
| left join | 显示以左边表为主的内容，右边表有匹配左边表内容时完成显示，无匹配时NULL |
| right by | 显示以右边表为主的内容，左边表有匹配右边表内容时完成显示，无匹配时NULL |
group by（聚合函数或分组查询）
group by 一般和聚合函数一起使用才有意义，比如count sum avg等，使用group by的两个要素：
出现在select后面的字段，要么是聚合函数中的，要么就是group by中的。
要筛选结果，可以先使用where再用group by或者先用group by再用having
mysql> create table test(a varchar(20),b varchar(20),c varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert test value(1,'a','boom');
Query OK, 1 row affected (0.01 sec)
mysql> insert test value(1,'a','boom');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','boom');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','boom');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'a','boom');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','ga');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','ga');
Query OK, 1 row affected (0.00 sec)
mysql> insert test value(1,'b','ga');
Query OK, 1 row affected (0.01 sec)
mysql> insert test value(1,'b','ga');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | a | boom |
| 1 | a | boom |
| 1 | a | boom |
| 1 | a | boom |
| 1 | a | boom |
| 1 | b | ga |
| 1 | b | ga |
| 1 | b | ga |
| 1 | b | ga |
+------+------+------+
9 rows in set (0.00 sec)
mysql> select count(a),b from test group by b;
+----------+------+
| count(a) | b |
+----------+------+
| 5 | a |
| 4 | b |
+----------+------+
2 rows in set (0.01 sec)
//group by c：以c为组去计算
mysql> select count(1),c from test group by c;
+----------+------+
| count(1) | c |
+----------+------+
| 5 | boom |
| 4 | ga |
+----------+------+
2 rows in set (0.00 sec)
//求和
mysql> select sum(age) from student;
+----------+
| sum(age) |
+----------+
| 190 |
+----------+
1 row in set (0.00 sec)
//求平均值
mysql> select avg(age) from student;
+----------+
| avg(age) |
+----------+
| 19.0000 |
+----------+
1 row in set (0.00 sec)
inner by（内连接）、left join（左连接）、right by（右连接）
mysql> create table tablea(id int,name varchar(30));
Query OK, 0 rows affected (0.00 sec)
mysql> create table tableb(id int,job int,parent_id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert tablea values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert tableb values(1,23,1),(2,34,2),(3,34,4);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tablea;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from tableb;
+------+------+-----------+
| id | job | parent_id |
+------+------+-----------+
| 1 | 23 | 1 |
| 2 | 34 | 2 |
| 3 | 34 | 4 |
+------+------+-----------+
3 rows in set (0.00 sec)
内连接，查询两张表相同的数据
mysql> select tablea.*,tableb.* from tablea inner join tableb on tablea.id = tableb.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
+------+----------+------+------+-----------+
2 rows in set (0.00 sec)
左连接，以左边为标准：在left join左边的就是左，在left join右边的就是右如果右边跟左边不匹配的值则显示空null
mysql> select tablea.*,tableb.* from tablea left join tableb on tablea.id = tableb.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| 3 | wangwu | NULL | NULL | NULL |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)
右连接
mysql> select tablea.*,tableb.* from tablea right join tableb on tablea.id = tableb.parent_id;
+------+----------+------+------+-----------+
| id | name | id | job | parent_id |
+------+----------+------+------+-----------+
| 1 | zhangsan | 1 | 23 | 1 |
| 2 | lisi | 2 | 34 | 2 |
| NULL | NULL | 3 | 34 | 4 |
+------+----------+------+------+-----------+
3 rows in set (0.00 sec)
87
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
