主頁 > 資料庫 > mysql進階

mysql進階

2022-07-28 14:17:38 資料庫

mysql進階

目錄
  • mysql進階
    • 1.二進制格式mysql安裝
    • 2.mysql組態檔
      • 2.1 資料庫密碼破解
    • 3.多表聯合查詢
      • 3.1 什么是多表聯合查詢
      • 3.2 交叉連接（CROSS JOIN）
        • 3.2.1 笛卡爾積
        • 3.2.2 交叉連接

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 ~]# 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
mysql/                               mysql-5.7.38-linux-glibc2.12-x86_64/ 
[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 --initialze --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> insert tb_course(course_name) values('Java'),('MySQL'),('Python'),('Go'),('C++');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

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)

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500469.html

標籤：其他

上一篇：Redis SCAN命令

下一篇：Mysql 資料恢復邏輯 基于binlog redolog undolog

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀：**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題，由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視，實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶，一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀：**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高，采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐，遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀：**滴滴資料通道引擎承載著全公司的資料同步，為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加，資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程，遇到的問題以及今后的規劃。 #1. 背景 資料，對于任何一家互聯網公司來說都是非常重要的資產，公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp？ MPP (Massively Parallel Processing)，即大規模并行處理，在資料庫非共享集群中，每個節點都有獨立的磁盤存盤系統和記憶體系統，業務資料根據資料庫模型和應用特點劃分到各個節點上，每臺資料節點通過專用網路或者商業通用網路互相連接，彼此協同計算，作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀：**指標體系是什么？如何使用OSM模型和AARRR模型搭建指標體系？如何統一流程、規范化、工具化管理指標體系？本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標，系統化的組織起來，通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀：**Ceph是國際知名的開源分布式存盤系統，在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統， ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章，這一次主要寫一個嵌套的聚合，例如先對sex集合，再對desc聚合，最后再對age求和，共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by，avg，sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack（ELK）部署

    傻瓜式部署，只需替換IP與用戶 導讀： 現ELK四大組件分別為：Elasticsearch（核心）、logstash（處理）、filebeat（采集）、kibana（可視化） 下載均在https://www.elastic.co/cn/downloads/下tar包，各組件版本最好一致，配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:33:24 more
  • MySQL中binlog備份腳本分享

    關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......

    uj5u.com 2023-04-20 08:28:06 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:27:27 more
  • 快取與資料庫雙寫一致性幾種策略分析

    本文將對幾種快取與資料庫保證資料一致性的使用方式進行分析。為保證高并發性能，以下分析場景不考慮執行的原子性及加鎖等強一致性要求的場景，僅追求最終一致性。 ......

    uj5u.com 2023-04-20 08:26:48 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:26:35 more
  • 云時代，MySQL到ClickHouse資料同步產品對比推薦

    ClickHouse 在執行分析查詢時的速度優勢很好的彌補了MySQL的不足，但是對于很多開發者和DBA來說，如何將MySQL穩定、高效、簡單的同步到 ClickHouse 卻很困難。本文對比了 NineData、MaterializeMySQL（ClickHouse自帶）、Bifrost 三款產品... ......

    uj5u.com 2023-04-20 08:26:29 more
  • sql陳述句優化

    問題查找及措施 問題查找 需要找到具體的代碼，對其進行一對一優化，而非一直把關注點放在服務器和sql平臺 降低簡化每個事務中處理的問題，盡量不要讓一個事務拖太長的時間 例如檔案上傳時，應將檔案上傳這一步放在事務外面 微軟建議 4.啟動sql定時執行計劃 怎么啟動sqlserver代理服務-百度經驗 ......

    uj5u.com 2023-04-20 08:25:13 more
  • Redis 報”OutOfDirectMemoryError“（堆外記憶體溢位)

    Redis 報錯“OutOfDirectMemoryError（堆外記憶體溢位) ”問題如下： 一、報錯資訊： 使用 Redis 的業務介面 ，產生 OutOfDirectMemoryError（堆外記憶體溢位），如圖： 格式化后的報錯資訊： { "timestamp": "2023-04-17 22: ......

    uj5u.com 2023-04-20 08:24:54 more
  • day02-2-商鋪查詢快取

    功能02-商鋪查詢快取 3.商鋪詳情快取查詢 3.1什么是快取？ 快取就是資料交換的緩沖區（稱作Cache），是存盤資料的臨時地方，一般讀寫性能較高。 快取的作用： 降低后端負載 提高讀寫效率，降低回應時間 快取的成本： 資料一致性成本 代碼維護成本 運維成本 3.2需求說明 如下，當我們點擊商店詳 ......

    uj5u.com 2023-04-20 08:24:03 more
  • day02-短信登錄

    功能實作02 2.功能01-短信登錄 2.1基于Session實作登錄 2.1.1思路分析 2.1.2代碼實作 2.1.2.1發送短信驗證碼 發送短信驗證碼： 發送驗證碼的介面為：http://127.0.0.1:8080/api/user/code?phone=xxxxx<手機號> 請求方式：PO ......

    uj5u.com 2023-04-20 08:23:11 more

Copyright © 2010-2020 有解無憂