1.数据库连接⽅式 2.SQL语⾔ 3.Mysql数据库对应与应⽤ 4.数据库基本操作 5.数据库增删查改 6.数据库查询语句 6.1单表查询 6.2多表查询 6.3⼦查询 系统数据库 information_schema(虚拟库) ⽤户表信息、列信息、权限信息、字符信息等 #查询有多少个库 mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA; +----------+ | COUNT(*) | +----------+ | 4 | +----------+ #查询mysql库中有多少个表 mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql'; +----------+ | COUNT(*) | +----------+ | 31 | +----------+ 1 row in set (0.00 sec) performance_schema 主要存储数据库服务器的性能参数 mysql(授权库) 主要存储系统⽤户的权限信息 sys(优化库) 主要存储数据库服务器的性能参数 wing(业务库) ⾃⼰创建实验的库 主要存放业务所需要的库和表 1.数据库连接⽅式 1.1 ⼯作中常⽤到的三种连接⽅式 Java App + JDBC client（其他语⾔也有，⽐如Python的MySQLdb） MySQL client MySQL utility MySQL utility 是指 MySQL 数据库的⼀组命令⾏⼯具，可以⽤于管理和维护 MySQL 数据库。这些⼯具包括： 1. mysql：连接到 MySQL 数据库服务器并执⾏ SQL 语句的命令⾏客户端。 2. mysqldump：将 MySQL 数据库中的数据导出到⽂件中的命令⾏⼯具。 3. mysqlimport：将数据从⽂件导⼊到 MySQL 数据库中的命令⾏⼯具。 4. mysqladmin：⽤于管理 MySQL 服务器的命令⾏⼯具，如启动、停⽌、重启服务器，以及查看服务器状 态等。 5. mysqlcheck：⽤于检查和修复 MySQL 数据库表的命令⾏⼯具。 6. mysql_upgrade：⽤于升级 MySQL 数据库的命令⾏⼯具。 这些⼯具都是免费的，并且可以在 Windows、Linux 和 Mac OS X 等操作系统上使⽤。 1.2 使⽤应⽤程序连接MySQL 应⽤程序使⽤驱动（connector/driver）客户端连接MySQL MySQL驱动程序涵盖各种主流语⾔ 1.3 使⽤命令⾏连接MySQL 安装MySQL客户端软件包 设置环境变量（Linux） 安装MySQL-client 从软件源安装 sudo yum install mysql-client 验证MySQL的安装 mysql -V 命令⾏连接MySQL的两种⽅式 TCP/IP连接（远程连接） Socket连接（本地连接） TCP/IP连接（远程连接） 使⽤ mysql -u root -p 可以连接数据库, 但这只是本地连接数据库的⽅式, 在⽣产很多情况下都是连接⽹络中某 ⼀个主机上的数据库 -P //指定连接远程数据库端⼝ -h //指定连接远程数据库地址 -u //指定连接远程数据库账户 -p //指定连接远程数据库密码 [root@sql ~]# mysql -h127.0.0.1 -P3306 -uroot -p Enter password: mysql 参数帮助 [root@sql ~]# mysql --help mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? -h, --host=name Connect to host. -p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). each row on new line. -S, --socket=name The socket file to use for connection. -u, --user=name User for login if not current user. -U, --safe-updates Only allow UPDATE and DELETE that uses keys. -U, --i-am-a-dummy Synonym for option --safe-updates, -U. -v, --verbose Write more. (-v -v -v gives the table output format). -V, --version Output version information and exit. -w, --wait Wait and retry if connection is down. ... 使⽤Socket连接 # 需要指定socket⽂件和⽤户名、密码 mysql -S /tmp/mysql.sock -uroot -p'Wing@123' 本地连接VS远程连接 本地连接只能在MySQL服务器上创建，常⽤作为MySQL状态检查，或程序和MySQL部署在⼀台机器上。 远程连接在MySQL服务器内外都能连接，适合应⽤服务器和MySQL部署在不同机器上的场景。 使⽤命令⾏连接MySQL的注意事项 socket⼀般存储路径为：/tmp/mysql.sock # 如果找不到⽂件可以通过tcp连接进来然后通过如下命令查找 show global variables like 'socket'; socket⽂件的权限必须是777 不要将密码直接输⼊在命令⾏⾥，存在安全⻛险！ 命令⾏连接MySQL的特点 MySQL命令⾏⾥有丰富的扩展参数 DBA运维管理⼯具⼤多使⽤命令⾏⽅式 多台机器可以同时操作，对于DBA来说⾮常有效率 1.4 连接进⼊之后可以做什么 # 数据库状态 status; #简写 \s # 展示当前连接 show processlist; mysql> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 15 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+s Id ：该字段表示当前会话的唯⼀标识符，每个会话都有⼀个唯⼀的 ID。 User ：该字段表示当前会话的⽤户名。 Host ：该字段表示当前会话的主机名或 IP 地址。 db ：该字段表示当前会话正在使⽤的数据库名，如果为 NULL，则表示当前未选择任何数据库。 Command ：该字段表示当前会话正在执⾏的命令类型，如 Query、Sleep、Connect 等。 Time ：该字段表示当前会话已经执⾏的时间，单位为秒。 State ：该字段表示当前会话的状态，如 starting、sending data、locked 等。 Info ：该字段表示当前会话正在执⾏的 SQL 语句或其他信息。 1.5 使⽤图形客户端连接MySQL 常⽤的图形客户端⼯具 Navicat MySQLWorkBench #创建⼀个具有 root 权限的⽤户,并授予该⽤户远程连接权限。 CREATE USER 'root'@'%' IDENTIFIED BY 'Wing@123'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; #重新加载权限表，使更改⽣效： FLUSH PRIVILEGES; mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | root | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)
图形GUI⼯具的优势 操作简单易于上⼿ ⽀持图形化的导⼊、导出 可视化界⾯输出，输出可视化 1.6 总结 应⽤程序需要使⽤API接⼝连接MySQL 开发⼯程师可以使⽤图形⼯具连接MySQL 命令⾏客户端才是DBA的最爱 2. SQL语⾔(结构化查询语⾔) 2.1 关系型数据库 数据存放在表中 表的每⼀⾏被称为记录 record 表中所有记录都有相同的 字段 （ 列 ） mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | 表头 字段列 +---------------+-----------+ | root | % | ⼀⾏称为记录 | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 2.2 SQL是什么 Structured Query Language 是⼀种特殊⽬的的编程语⾔，⽤于关系型数据库中的标准数据存取操作 与数据库进⾏沟通的钥匙 2.3 SQL语⾔与数据库 ⽤SQL创建表，定义表中的字段 ⽤SQL向表中增加，删除，修改记录 ⽤SQL从表中查询到想要的记录 ⽤SQL操作数据库的⼀切 2.4 SQL语句的分类 1.DDL(数据库定义语⾔ //开发⼈员) 数据库、表、视图、索引、存储过程、函数、CREATE DROP ALTER 2.DML(数据库操作语⾔ //开发⼈员) 插⼊数据 INSERT、删除数据 DELETE、更新数据UPDATE 3.DQL(数据库查询语⾔ //运维、开发⼈员) 查询数据 SELECT 4.DCL(数据库控制语⾔ //运维⼈员) 控制⽤户的访问权限 GRANT、REVOKE example: mysql> select user,host from mysql.user; +---------------+-----------+ | user | host | 表头 字段列 +---------------+-----------+ | root | % | ⼀⾏称为记录 | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ # 查看当前有哪些数据库 show databases; #创建数据库 create database wing; # 使⽤名为wing的数据库 use wing; # 创建⼀张学⽣表 create table stu( id int(10), name varchar(20), age int(10), primary key(id)); # 每⼀张表都需要包含⼀个主键，主键唯⼀标识⼀条记录，唯⼀的字段，不可重复不能为空，通过`primary key`关 键字来定义。 # 查看创建好的表 show create table stu; # 新加⼀个字段 alter table stu add column gender varchar(20); # 修改⼀个字段 alter table stu modify column gender varchar(40); # 删除⼀个字段 alter table stu drop column gender; # 删除表 drop table stu; # 查看当前数据库中的表 show tables; # 向表中插⼊数据 insert into stu(id,name,age) values(1,'wing',28); # 插⼊全部字段时可以只写表名 insert into stu values(2,'nss',29); # 查看刚才添加的数据,"*"代表查询全部字段 select * from stu; # 如果只想查询两个字段，则只写要查询的字段名 select name, age from stu; # 也可以根据某个条件进⾏查询，⽐如只查询id为1的记录 select name age from stu where id=1; # 更新语句 update stu set age=29 where id=1; # 删除表中的数据 delete from stu where id=1; 3. MySQL数据库对象与应⽤ 3.1 MySQL数据类型 3.1.1 Number不⽌⼀种 整形 浮点型 整形 TINYINT SMALLINT MEDIUMINT INT BIGINT 数据储存最⼩单位是bit byte Kb Mb Gb 1024 1 Byte = 8bit bit只有0和1 11111111 最⼤ 256 1Byte储存⼤⼩范围 ⽆符号:2^7-1=127 有符号: 2^7=-128
⽼⽣常谈的问题 int(11) VS int(21) 存储空间，还是存储范围有区别？ 答案是：两者完全⼀样，只是在显示的时候补全0的位数不⼀样。 可以通过下⾯的例⼦来验证 create table t(a int(11) zerofill, b int(21) zerofill); insert into t values (1, 1); select * from t; 括号中的数字并不表示存储空间或存储范围的⼤⼩，⽽是⽤于指定显示宽度，即在显示查询结果时，为了对 ⻬和美观，会在数字前⾯补上空格或 0，使得所有数字的显示宽度都相同。因此， int(11) 和 int(21) 在 存储空间和存储范围上没有区别，只是在显示的时候补全 0 的位数不同。 3.1.2 浮点型 FLOAT(M, D) DOUBLE(M, D) 在 MySQL 中， FLOAT(M,D) 中的 M 和 D 分别表示浮点数的总位数和⼩数位数。其中， M 的范围是从 1 到 24， D 的范围是从 0 到 23。例如， FLOAT(8,2) 表示⼀个 8 位浮点数，其中有 2 位⼩数。在这种情况下， 该浮点数的取值范围为 -999999.99 到 999999.99。需要注意的是， FLOAT 类型的精度是不确定的，因此在 进⾏精确计算时，建议使⽤ DECIMAL 类型。
精度丢失问题 精度丢失 ⼀个例⼦： drop table t; create table t(a int(11), b float(7, 4)); insert into t values (2, 123.12345); select * from t; 3.1.3 DECIMAL 定点数-更精确的数字类型 DECIMAL类型确实是⼀种更精确的数字类型，适⽤于需要⾼精度计算的场景，⽐如货币交易等。DECIMAL(M,N) 中，M代表总精度，N代表⼩数点右侧的位数（标度）。 DECIMAL ⾼精度的数据类型，常⽤来存储交易相关的数据 DECIMAL(M,N).M代表总精度，N代表⼩数点右侧的位数（标度） 1 < M < 254, 0 < N < 60; 存储空间变⻓ 3.1.4 经验之谈 存储性别、省份、类型等分类信息时选择TINYINT、char(1)或者ENUM BIGINT存储空间更⼤，INT和BIGINT之间通常选择BIGINT 交易等⾼精度数据选择使⽤DECIMAL 3.2 字符型 3.2.1 存储⽤户名的属性 CHAR VARCHAR TEXT 3.2.2 CAHR与VARCHAR CHAR和VARCHAR存储的单位都是 字符 CHAR存储定⻓，容易造成空间的浪费 VARCHAR存储变⻓，节省存储空间 3.2.3 字符与字节的区别 字符和字节是两个不同的概念。⼀个字符可以由⼀个或多个字节组成，具体取决于所使⽤的字符集和编码⽅ 式。在MySQL中，常⽤的字符集包括GBK、UTF-8、UTF-8MB4等。 GBK是双字节编码，UTF-8是三字节编码，UTF-8MB4是四字节编码。 UTF8MB4 是 MySQL 数据库中⼀种字符编码⽅式，它是 UTF8 编码的⼀个扩展，⽀持更⼴泛的字符集。 UTF8MB4 中的 "MB4" 表示 "4 bytes maximum"，也就是说它可以⽀持 4 字节⻓的字符，⽽ UTF8 只能⽀持 3 字节⻓的字符。 对于UTF8MB4号称占⽤四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占⽤3字节。 UTF8MB4 编码最有优势的应⽤场景是⽤于 emoji 表情在内的各种字符，这些字符在传统的 UTF8 编码中⽆ 法表示。同时，UTF8MB4 也可以⽤来存储各种语⾔的⽂字，包括中⽂、⽇⽂、韩⽂等等。
a = 96 1byte=8bit = 256 3.2.4 emoji表情 MySQL版本 > 5.5.3 JDBC驱动版本 > 5.1.13 库和表的编码设为utf8mb4 3.2.5 TEXT与CHAR和VARCHAR的区别 CHAR和VARCHAR存储单位为字符 TEXT存储单位为字节，总⼤⼩为65535字节，约为64KB CHAR数据类型最⼤为255字符 VARCHAR数据类型为变⻓存储，可以超过255个字符 TEXT在MySQL内部⼤多存储格式为溢出⻚，效率不如CHAR ⼀个例⼦ drop table t; create table t (a char(256)); create table t (a varchar(256)); 3.2.6 存储头像 BLOB BINARY 在MySQL中，BLOB和BINARY都是⽤来存储⼆进制数据的数据类型。 BLOB是Binary Large Object（⼆进制⼤对象）的缩写，⽤来存储⼤型⼆进制数据，例如图像、⾳频、视频等⽂ 件。BLOB类型可以存储最⼤为65,535字节的数据，如果需要存储更⼤的数据，需要使⽤MEDIUMBLOB、 LONGBLOB等类型。 BINARY是⽤来存储固定⻓度的⼆进制数据，例如加密密钥等。BINARY类型可以存储最⼤为255字节的数据，如果 需要存储更⼤的数据，需要使⽤VARBINARY类型。 性能太差，不推荐 3.2.7 经验之谈 CHAR与VARCHAR定义的⻓度是字符⻓度不是字节⻓度 存储字符串推荐使⽤VARCHAR(N),N尽量⼩ 虽然数据库可以存储⼆进制数据，但是性能低下，不要使⽤数据库存储⽂件⾳频等⼆进制数据 分布式储存系 统ceph minio 3.3 时间类型 3.3.1 存储⽣⽇信息 DATE TIME DATETIME TIMESTAMP 3.3.2 时间类型的区别在哪⾥ 存储空间上的区别 DATE三字节，如：2015-05-01 TIME三字节，如：11:12:00 TIMESTAMP，如：2015-05-01 11::12:00 DATETIME⼋字节，如：2015-05-01 11::12:00 存储精度的区别 DATE精确到年⽉⽇ TIME精确到⼩时分钟和秒 TIMESTAMP、DATETIME都包含上述两者 3.3.3 TIMESTAMP VS DATETIME 存储范围的区别 TIMESTAMP存储范围：1970-01-01 00::00:01 to 2038-01-19 03:14:07 DATETIME的存储范围：1000-01-01 00:00:00 to 9999-12-31 23:59:59 MySQL在5.6.4版本之后，TimeStamp和DateTime⽀持到微妙 字段类型与市区的关联关系 TIMESTAMP会根据系统时区进⾏转换，DATETIME则不会 3.3.4 字段类型和时区的关系 国际化的系统 ⼀个例⼦： create table test (a datetime, b timestamp); select now(); insert into test values (now(), now()); select * from test; set time_zone = '+00:00'; select * from test; #调整时间为默认东⼋区 set time_zone = '+08:00' set time_zone = '+00:00'; 这是MySQL中设置时区的语句，将时区设置为UTC（协调世界时）+0:00，也就是格林威治标准时间。在 MySQL中，时区的设置会影响到⽇期和时间的存储和显示，因此在使⽤MySQL时，需要根据实际情况设置合 适的时区。可以根据⾃⼰所在的时区，设置不同的时区参数，例如set time_zone = '+08:00'可以将时区设置 为东⼋区。 3.3.5 BIGINT如何存储时间类型 应⽤程序将时间转换为数字类型(时间戳) 3.4 类型总结 基本上我们学习任何关于数据库或者开发语⾔都存在数据类型 数字: 整数、浮点数Float 、DECIMAL 字符串: char varchar TEXT 时间:TIME、DATE 、TIMESTAMP、DATETIME 等等 3.4 MySQL数据对象 3.4.1 MySQL常⻅的数据对象有哪些 DataBase/Schema Table Index View/Trigger/Function/Procedure 3.4.2 库、表、⾏层级关系 ⼀个DataBase对应⼀个Schema ⼀个Schema包含⼀个或多个表 ⼀个表⾥⾯包含⼀个或多个字段 ⼀个表⾥包含⼀条或多条记录 ⼀个表包含⼀个或多个索引 3.4.3 多DataBase⽤途 业务隔离 资源隔离 3.4.4表上有哪些常⽤的数据对象 索引 约束 视图、触发器、函数、存储过程 4.数据库基本操作 4.1 查看数据库信息版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.30 | +-----------+ 1 row in set (0.01 sec) mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 5 Current database: Current user: root@localhost SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.30 MySQL Community Server (GPL) Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 26 min 45 sec Threads: 1 Questions: 300 Slow queries: 0 Opens: 452 Flush tables: 1 Open tables: 223 Queries per second avg: 0.186 mysql常⽤特殊命令 \h ：显示帮助信息，包括所有可⽤的 MySQL 命令和特殊命令 \c ：清除当前输⼊的命令 \q ：退出 MySQL 命令⾏⼯具。 \G ：将查询结果按列格式化，并以每⾏⼀个字段的⽅式显示。 \t：将查询结果转换为纯⽂本格式。 \n：将查询结果转换为纯⽂本格式，并在每⾏末尾添加换⾏符。 \d：设置语句分隔符 4.3 创建数据库DDL 注意 -------------- #其他⽅式查看等等 [root@sql ~]# mysql -V mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@sql ~]# mysql -uroot -p'Wing@123' 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 10 Server version: 5.7.30 MySQL Community Server (GPL) [root@sql ~]# mysql --help mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@sql ~]# mysqladmin --help mysqladmin Ver 8.42 Distrib 5.7.30, for linux-glibc2.12 on x86_64 `mysqladmin` 的版本信息。具体解释如下： - `Ver 8.42` 表示版本号为 8.42。 - `Distrib 5.7.30` 表示该版本是基于 MySQL 5.7.30 发⾏的。 - `for linux-glibc2.12` 表示该版本是为 Linux 操作系统和 glibc2.12 运⾏时库编译的。 - `on x86_64` 表示该版本是为 64 位 x86 架构的处理器编译的。 4.2 mysql常⽤特殊命令 \h ：显示帮助信息，包括所有可⽤的 MySQL 命令和特殊命令 \c ：清除当前输⼊的命令 \q ：退出 MySQL 命令⾏⼯具。 \G ：将查询结果按列格式化，并以每⾏⼀个字段的⽅式显示。 \t：将查询结果转换为纯⽂本格式。 \n：将查询结果转换为纯⽂本格式，并在每⾏末尾添加换⾏符。 \d：设置语句分隔符 4.3 创建数据库DDL 注意 -------------- #其他⽅式查看等等 [root@sql ~]# mysql -V mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@sql ~]# mysql -uroot -p'Wing@123' 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 10 Server version: 5.7.30 MySQL Community Server (GPL) [root@sql ~]# mysql --help mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper [root@sql ~]# mysqladmin --help mysqladmin Ver 8.42 Distrib 5.7.30, for linux-glibc2.12 on x86_64 `mysqladmin` 的版本信息。具体解释如下： - `Ver 8.42` 表示版本号为 8.42。 - `Distrib 5.7.30` 表示该版本是基于 MySQL 5.7.30 发⾏的。 - `for linux-glibc2.12` 表示该版本是为 Linux 操作系统和 glibc2.12 运⾏时库编译的。 - `on x86_64` 表示该版本是为 64 位 x86 架构的处理器编译的。 mysql> create database wing; Query OK, 1 row affected (0.00 sec) //以分号结尾 o数据库名称严格区分⼤⼩写 数据库名称必须是唯⼀ 数据库名称不允许使⽤数字 数据库名称不能使⽤关键字命名 create select 4.4 查看当前的库内容 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | wing | +--------------------+ 5 rows in set (0.00 sec) //执⾏命令不区分⼤⼩写 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | wing | +--------------------+ 5 rows in set (0.00 sec) 4.5 删除数据库 mysql> drop database wing; Query OK, 0 rows affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec) //删除库下的表 mysql> drop table wing.t1; 4.6 查询某个库的表 //use进⼊对应库 mysql> use wing; Database changed //列出当前库下⾯的表 mysql> show tables; Empty set (0.00 sec) //查询某个库下的表结构 mysql> desc mysql.slow_log; +----------------+---------------------+------+-----+----------------------+----------- ---------------------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+----------------------+----------- ---------------------+ | user_host | mediumtext | NO | | NULL | | | query_time | time(6) | NO | | NULL | | | lock_time | time(6) | NO | | NULL | | | rows_sent | int(11) | NO | | NULL | | | rows_examined | int(11) | NO | | NULL | | | db | varchar(512) | NO | | NULL | | | last_insert_id | int(11) | NO | | NULL | | | insert_id | int(11) | NO | | NULL | | | server_id | int(10) unsigned | NO | | NULL | | | sql_text | mediumblob | NO | | NULL | | | thread_id | bigint(21) unsigned | NO | | NULL | | +----------------+---------------------+------+-----+----------------------+----------- ---------------------+ 12 rows in set (0.00 sec) //查看某张表的建表语句 mysql> show create table mysql.slow_log\G *************************** 1. row *************************** Table: slow_log Create Table: CREATE TABLE `slow_log` ( `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, `query_time` time(6) NOT NULL, `lock_time` time(6) NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `sql_text` mediumblob NOT NULL, `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 1 row in set (0.00 sec) 5.数据库增删查改 在 MySQL 管理软件中, 可以通过 SQL 语句中的 DML 语⾔来实现数据的操作, 包括如下: INSERT 数据插⼊ UPDATE 数据更新 DELETE 数据删除 1.准备操作环境数据表 #创建数据⽂件 mysql> create database wing; mysql> use wing; mysql> create table t1(id int, name varchar(10), sex enum('man','gril'), age int); #查看表字段 mysql> desc t1; +-------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | sex | enum('man','gril') | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+--------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) 2.插⼊数据 INSERT 语句 #1.插⼊完整数据, 顺序插⼊: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n); mysql> insert into t1(id,name,sex,age) values ("1","wing","man","18"); Query OK, 1 row affected (0.01 sec) #1.插⼊完整数据, 推荐⽅式 INSERT INTO 表名 VALUES (值1,值2,值n); mysql> insert into t1 values("2","wing1","gril","10"); Query OK, 1 row affected (0.01 sec) #2.指定字段插⼊, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…); mysql> insert into t1(name,sex,age) values ("wing2","man","20"); Query OK, 1 row affected (0.00 sec) #3.插⼊多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n); mysql> insert into t1 values ("3","wing3","man","18"), ("4","wing4","man","18"), ("5","wing5","man","18"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+-------+------+------+ | id | name | sex | age | +------+-------+------+------+ | 1 | wing | man | 18 | | 2 | wing1 | gril | 10 | | NULL | wing2 | man | 20 | | 3 | wing3 | man | 18 | | 4 | wing4 | man | 18 | | 5 | wing5 | man | 18 | +------+-------+------+------+ 6 rows in set (0.00 sec) 3.更新数据 UPDATE 语句 //语法: 更新 表 设置 字段1=值1, 字段2=值2, WHERE 条件; 1.查看需要修改的表的字段 desc 2.查询对⽤的字段 select 3.更新对应的表字段 update 4.添加对应的where条件,精准修改 //示例1: 将t1表中, name字段等于wing1的改为update_w1 mysql> update t1 set name="update_w1" where name="wing1"; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from t1; +------+-----------+------+------+ | id | name | sex | age | +------+-----------+------+------+ | 1 | wing | man | 18 | | 2 | update_w1 | gril | 10 | | NULL | wing2 | man | 20 | | 3 | wing3 | man | 18 | | 4 | wing4 | man | 18 | | 5 | wing5 | man | 18 | +------+-----------+------+------+ 6 rows in set (0.00 sec) //示例2: 修改密码示例, 查看表字段内容 mysql> select user,host,authentication_string from mysql.user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | +---------------+-----------+-------------------------------------------+ 3 rows in set (0.00 sec) //更新字段 mysql> update mysql.user set authentication_string=password("Wing@123") where user='root' and host='localhost'; Query OK, 0 rows affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 4.删除数据 DELETE 语法: DELETE FROM 表名 WHERE CONITION; //删除字段包含update_wing mysql> delete from t1 where name="update_w1"; Query OK, 2 rows affected (0.01 sec) mysql> select * from t1; +------+-------+------+------+ | id | name | sex | age | +------+-------+------+------+ | 1 | wing | man | 18 | | NULL | wing2 | man | 20 | | 3 | wing3 | man | 18 | | 4 | wing4 | man | 18 | | 5 | wing5 | man | 18 | +------+-------+------+------+ 5 rows in set (0.00 sec) //清空表数据 mysql> truncate t1; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) 6.数据库查询语句 6.1单表查询 在学习查询前, 需要定义好对应数据进⾏查询 编号 id int 姓名 name varchar(30) 性别 sex enum ⽇期 time date 职位 post varchar(50) 描述 job varchar(100) 薪⽔ salary double(15,2) 办公区域: office 部⻔编号 dep_id int #创建表 mysql> CREATE TABLE wing.t2( id int primary key AUTO_INCREMENT not null, name varchar(30) not null, sex enum('man','gril') default 'man' not null, time date not null, post varchar(50) not null, job varchar(100), salary double(15,2) not null, office int, dep_id int ); #插⼊数据 mysql> insert into wing.t2(name,sex,time,post,job,salary,office, dep_id) values ('jack','man','20230502','instructor','teach',5000,501,100), ('tom','man','20230503','instructor','teach',5500,501,100), ('robin','man','20230502','instructor','teach',8000,501,100), ('alice','gril','20230502','instructor','teach',7200,501,100), ('wing','man','20230502','hr','hrcc',600,502,101), ('harry','man','20230502','hr', NULL,6000,502,101), ('trf','gril','20230506','sale','salecc',20000,503,102), ('test','gril','20230505','sale','salecc',2200,503,102), ('dog','man','20230505','sale', NULL,2200,503,102), ('alex','man','20230505','sale','',2200,503,102) 1.简单查询 //查看表字段与表信息 mysql> desc t2; +--------+--------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | sex | enum('man','gril') | NO | | man | | | time | date | NO | | NULL | | | post | varchar(50) | NO | | NULL | | | job | varchar(100) | YES | | NULL | | | salary | double(15,2) | NO | | NULL | | | office | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+--------------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) #1.查询所有数据 mysql> select * from t2; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 | | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec) #2.指定字段查询 mysql> select name,salary,dep_id from t2; +-------+----------+--------+ +-------+----------+-----------+ | name | salary | salary*14 | +-------+----------+-----------+ | jack | 5000.00 | 70000.00 | 2.单条件查询 单条件查询 多条件查询 关键字 BETWEEN AND 关键字 IS NULL 关键字 IN 集合查询 关键字 LIKE 模糊查询 #1.单条件查询 mysql> select name,post from t2 where post='hr'; +-------+------+ | name | post | +-------+------+ | wing | hr | | harry | hr | +-------+------+ 2 rows in set (0.00 sec) #2.多条件查询 mysql> select name,post,salary from t2 where post='hr' and salary >5000; +-------+------+---------+ | name | post | salary | +-------+------+---------+ | harry | hr | 6000.00 | +-------+------+---------+ 1 row in set (0.00 sec) #3.查找薪资范围在8000-2000,使⽤BETWEEN区间 mysql> select name,salary from t2 where salary between 8000 and 20000; +-------+----------+ | name | salary | +-------+----------+ | robin | 8000.00 | | trf | 20000.00 | +-------+----------+ 2 rows in set (0.00 sec) #4.查找部⻔为Null, 没有部⻔的员⼯ mysql> select name,job from t2 where job is null; +-------+------+ | name | job | +-------+------+ | harry | NULL | | dog | NULL | +-------+------+ 2 rows in set (0.00 sec) #查找有部⻔的员⼯ mysql> select name,job from t2 where job is not null; +-------+--------+ | name | job | +-------+--------+ | jack | teach | | tom | teach | | robin | teach | | alice | teach | | wing | hrcc | | trf | salecc | | test | salecc | | alex | | +-------+--------+ 8 rows in set (0.00 sec) #查看部⻔为空的员⼯ mysql> select name,job from t2 where job=''; +------+------+ | name | job | +------+------+ | alex | | +------+------+ 1 row in set (0.00 sec) #5.集合查询 mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000; mysql> select name,salary from t2 where salary in(4000,5000,8000); +-------+---------+ | name | salary | +-------+---------+ | jack | 5000.00 | | robin | 8000.00 | +-------+---------+ 2 rows in set (0.01 sec) #6.模糊查询like, 通配符% mysql> select * from t2 where name like 'al%'; +----+-------+------+------------+------------+-------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+-------+---------+--------+--------+ | 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | | 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | +----+-------+------+------------+------------+-------+---------+--------+--------+ 2 rows in set (0.00 sec) #通配符__ mysql> select * from t2 where name like 'al__'; +----+------+-----+------------+------+------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+------+-----+------------+------+------+---------+--------+--------+ | 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 | +----+------+-----+------------+------+------+---------+--------+--------+ 1 row in set (0.00 sec) 3.查询排序 单列排序 多列排序 #1.按单列排序, 按薪⽔从低到⾼排序, 默认ASC mysql> select * from t2 ORDER BY salary ASC; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.01 sec) #1.按单列排序, 薪⽔从低往⾼排序, DESC倒序 mysql> select * from t2 ORDER BY salary DESC; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | | 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec) #2.多列排序, 先按⼊职时间，再按薪⽔排序 mysql> select * from t2 ORDER BY time DESC, salary ASC; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 | | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec) #2.多列排序, 先按职位， 再按薪⽔排序 mysql> select * from t2 ORDER BY post, salary DESC; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | | 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 10 rows in set (0.00 sec) 4.限制查询的记录数 #查询薪资最⾼前5名同事, 默认初始位置为0 mysql> select * from t2 ORDER BY salary DESC limit 5; +----+-------+------+------------+------------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+----------+--------+--------+ | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | | 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 | | 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 | | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | +----+-------+------+------------+------------+--------+----------+--------+--------+ 5 rows in set (0.00 sec) #从第4条开始, 并显示5条数据 mysql> select * from t2 ORDER BY salary DESC limit 3,5; +----+-------+------+------------+------------+--------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+--------+---------+--------+--------+ | 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 | | 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 | | 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 | | 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 | | 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 | +----+-------+------+------------+------------+--------+---------+--------+--------+ 5 rows in set (0.00 sec) LIMIT是MySQL中⽤于限制查询结果集的关键字，它可以在SELECT语句中使⽤。LIMIT语法如下： SELECT column1, column2, ... FROM table_name LIMIT [offset,] row_count; 其中， column1, column2, ... 是要查询的列名， table_name 是要查询的表名。 offset 是可选的，表示从第 ⼏条记录开始返回，默认为0，即从第⼀条记录开始返回。 row_count 表示要返回的记录数。 使⽤LIMIT⼦句时，可以只指定 row_count ，这样会从第⼀条记录开始返回指定的记录数。也可以同时指定 offset 和 row_count ，这样会从指定的记录开始返回指定的记录数。 5.使⽤集合函数查询 #统计当前表总共多少条数据 mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) #统计dep_id为101有多少条数据 mysql> select count(*) from t2 where dep_id=101; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec) #薪⽔最⾼ mysql> select MAX(salary) from t2; +-------------+ | MAX(salary) | +-------------+ | 20000.00 | +-------------+ 1 row in set (0.00 sec) #薪⽔最低 mysql> select min(salary) from t2; +-------------+ | min(salary) | +-------------+ | 600.00 | +-------------+ 1 row in set (0.00 sec) #平均薪⽔ mysql> select avg(salary) from t2; +-------------+ | avg(salary) | +-------------+ | 5890.000000 | +-------------+ 1 row in set (0.00 sec) #总共发放多少薪⽔ mysql> select sum(salary) from t2; +-------------+ | sum(salary) | +-------------+ | 58900.00 | +-------------+ 1 row in set (0.00 sec) #hr部⻔发放多少薪⽔ mysql> select sum(salary) from t2 where post='hr'; +-------------+ | sum(salary) | +-------------+ | 6600.00 | +-------------+ 1 row in set (0.00 sec) #哪个部⻔哪个⼈薪⽔最⾼ mysql> select * from t2 where salary=(select max(salary) from t2); +----+------+------+------------+------+--------+----------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+------+------+------------+------+--------+----------+--------+--------+ | 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 | +----+------+------+------------+------+--------+----------+--------+--------+ 1 row in set (0.00 sec) 6.分组查询 # GROUP BY 和 GROUP_CONCAT()函数⼀起使⽤ mysql> select post,GROUP_CONCAT(name) from t2 GROUP BY post; +------------+----------------------+ | post | GROUP_CONCAT(name) | +------------+----------------------+ | hr | wing,harry | | instructor | jack,tom,robin,alice | | sale | trf,test,dog,alex | +------------+----------------------+ 3 rows in set (0.00 sec) mysql> select post,GROUP_CONCAT(name) AS Group_Post from t2 GROUP BY post; +------------+----------------------+ | post | Group_Post | +------------+----------------------+ | hr | wing,harry | | instructor | jack,tom,robin,alice | | sale | trf,test,dog,alex | +------------+----------------------+ 3 rows in set (0.00 sec) #GROUP BY 和集合函数⼀起使⽤ mysql> select post,sum(salary) from t2 GROUP BY post; +------------+-------------+ | post | sum(salary) | +------------+-------------+ | hr | 6600.00 | | instructor | 25700.00 | | sale | 26600.00 | +------------+-------------+ 3 rows in set (0.00 sec) 7.使⽤正则表达式查询 mysql> select * from t2 where name REGEXP '^ali'; +----+-------+------+------------+------------+-------+---------+--------+--------+ | id | name | sex | time | post | job | salary | office | dep_id | +----+-------+------+------------+------------+-------+---------+--------+--------+ | 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 | +----+-------+------+------------+------------+-------+---------+--------+--------+ 1 row in set (0.00 sec) mysql> select * from t2 where name REGEXP 'gx$'; | id | name | sex | time | post | job | salary | office | dep_id | +----+------+-----+------------+------+------+--------+--------+--------+ | 5 | wing | man | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 | +----+------+-----+------------+------+------+--------+--------+--------+ 1 row in set (0.00 sec) 对字符串匹配⽅式 WHERE name = 'trf'; WHERE name LIKE 'ha%'; WHERE name REGEXP 'i$'; 6.2多表查询 多表连接查询 复合条件连接查询 ⼦查询 准备2张数据表 #准备表1 mysql> create table wing.t3( id int auto_increment primary key not null, name varchar(50), age int, dep_id int ); #为表1插⼊数据 mysql> insert into t3(name,age,dep_id) values ('wing',18,200), ('tom',26,201), ('jack',30,201), ('alice',24,202), ('robin',40,'200'), ('natasha',28,204); mysql> select * from t3; +----+---------+------+--------+ | id | name | age | dep_id | +----+---------+------+--------+ | 1 | wing | 18 | 200 | | 2 | tom | 26 | 201 | | 3 | jack | 30 | 201 | | 4 | alice | 24 | 202 | | 5 | robin | 40 | 200 | | 6 | natasha | 28 | 204 | +----+---------+------+--------+ 6 rows in set (0.00 sec) #准备表2 mysql> create table t4( dep_id int, dept_name varchar(100) ); #为表2插⼊数据 mysql> insert into t4 values (200,'hr'), (201,'it'), (202,'xs'), (203,'cw'); mysql> select * from t4; +--------+-----------+ | dep_id | dept_name | +--------+-----------+ | 200 | hr | | 201 | it | | 202 | xs | | 203 | cw | +--------+-----------+ 4 rows in set (0.00 sec) 连接的作⽤是⽤⼀个SQL语句把多个表中相互关联的数据查出来 1.交叉连接, 不使⽤任何匹配条件 查询出来数据 m*n mysql> select t3.name,t3.age,t3.dep_id,t4.dept_name from t3,t4; +---------+------+--------+-----------+ | name | age | dep_id | dept_name | +---------+------+--------+-----------+ | wing | 18 | 200 | hr | | wing | 18 | 200 | it | | wing | 18 | 200 | xs | | wing | 18 | 200 | cw | | tom | 26 | 201 | hr | | tom | 26 | 201 | it | | tom | 26 | 201 | xs | | tom | 26 | 201 | cw | | jack | 30 | 201 | hr | | jack | 30 | 201 | it | | jack | 30 | 201 | xs | | jack | 30 | 201 | cw | | alice | 24 | 202 | hr | | alice | 24 | 202 | it | | alice | 24 | 202 | xs | | alice | 24 | 202 | cw | | robin | 40 | 200 | hr | | robin | 40 | 200 | it | | robin | 40 | 200 | xs | | robin | 40 | 200 | cw | | natasha | 28 | 204 | hr | | natasha | 28 | 204 | it | | natasha | 28 | 204 | xs | | natasha | 28 | 204 | cw | +---------+------+--------+-----------+ 24 rows in set (0.00 sec) mysql> select count(id) from t3; +-----------+ | count(id) | +-----------+ | 6 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dep_id) from t4; +---------------+ | count(dep_id) | +---------------+ | 4 | +---------------+ 1 row in set (0.00 sec) 2.内连接, 只连接匹配的⾏ # 只找出有部⻔的员⼯, (部⻔表中没有natasha所在的部⻔) mysql> select t3.id,t3.name,t3.age,t4.dep_id,t4.dept_name from t3,t4 where t3.dep_id=t4.dep_id; +----+-------+------+--------+-----------+ | id | name | age | dep_id | dept_name | +----+-------+------+--------+-----------+ | 1 | wing | 18 | 200 | hr | | 2 | tom | 26 | 201 | it | | 3 | jack | 30 | 201 | it | | 4 | alice | 24 | 202 | xs | | 5 | robin | 40 | 200 | hr | +----+-------+------+--------+-----------+ 5 rows in set (0.00 sec) 3.外连接 SELECT 字段列表 FROM 表1 LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段; #左连接 mysql> select id,name,t4.dept_name from t3 left join t4 on t3.dep_id = t4.dep_id; +----+---------+-----------+ | id | name | dept_name | +----+---------+-----------+ | 1 | wing | hr | | 5 | robin | hr | | 2 | tom | it | | 3 | jack | it | | 4 | alice | xs | | 6 | natasha | NULL | +----+---------+-----------+ 6 rows in set (0.00 sec) #右连接 mysql> select id,name,t4.dept_name from t3 right join t4 on t3.dep_id = t4.dep_id; +------+-------+-----------+ | id | name | dept_name | +------+-------+-----------+ | 1 | wing | hr | | 2 | tom | it | | 3 | jack | it | | 4 | alice | xs | | 5 | robin | hr | | NULL | NULL | cw | +------+-------+-----------+ 6 rows in set (0.00 sec) 4.符合条件连接查询 #1.以内连接的⽅式查询 t3和t4表, 找出公司所有部⻔中年龄⼤于25岁的员⼯ mysql> select t3.id,t3.name,t3.age,t4.dept_name from t3,t4 where t3.dep_id = t4.dep_id and age >25; +----+-------+------+-----------+ | id | name | age | dept_name | +----+-------+------+-----------+ | 5 | robin | 40 | hr | | 2 | tom | 26 | it | | 3 | jack | 30 | it | +----+-------+------+-----------+ 3 rows in set (0.01 sec) #以内连接的⽅式查询 t3和t4表，并且以age字段降序显示 mysql> select t3.id,t3.name,t3.age,t4.dept_name from t3,t4 where t3.dep_id = t4.dep_id ORDER BY age DESC; +----+-------+------+-----------+ | id | name | age | dept_name | +----+-------+------+-----------+ | 5 | robin | 40 | hr | | 3 | jack | 30 | it | | 2 | tom | 26 | it | | 4 | alice | 24 | xs | | 1 | wing | 18 | hr | +----+-------+------+-----------+ 5 rows in set (0.00 sec) 6.3 ⼦查询 ⼦查询是将⼀个查询语句嵌套在另⼀个查询语句中。 内层查询语句的查询结果，可以为外层查询语句提供查询条件。 ⼦查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字 还可以包含⽐较运算符：= 、 !=、> 、<等 #带 IN 关键字的⼦查询 查询t3表，但dept_id必须在t4表中出现过 mysql> select * from t3 where dep_id IN (select dep_id from t4); +----+-------+------+--------+ | id | name | age | dep_id | +----+-------+------+--------+ | 1 | wing | 18 | 200 | | 2 | tom | 26 | 201 | | 3 | jack | 30 | 201 | | 4 | alice | 24 | 202 | | 5 | robin | 40 | 200 | +----+-------+------+--------+ 5 rows in set (0.00 sec) #代表运算符⼦查询, 查询年龄⼤于等于 25 岁员⼯所在部⻔（查询⽼龄化的部⻔） mysql> select dep_id,dept_name from t4 where dep_id IN (select DISTINCT dep_id from t3 where age >=25); +--------+-----------+ | dep_id | dept_name | +--------+-----------+ | 201 | it | | 200 | hr | +--------+-----------+ 2 rows in set (0.01 sec) #⼦查询 EXISTS 关字键字表示存在。在使⽤ EXISTS 关键字时，内层查询语句不返回查询的记录，⽽是返回⼀个真 假值。 #Ture 或 False，当返回 Ture 时，外层查询语句将进⾏查询；当返回值为 False 时，外层查询语 句不进⾏查询 #t4 表中存在 dep_id=203，Ture mysql> select * from t3 where EXISTS (select * from t4 where dep_id=203); +----+---------+------+--------+ | id | name | age | dep_id | +----+---------+------+--------+ | 1 | wing | 18 | 200 | | 2 | tom | 26 | 201 | | 3 | jack | 30 | 201 | | 4 | alice | 24 | 202 | | 5 | robin | 40 | 200 | | 6 | natasha | 28 | 204 | +----+---------+------+--------+ 6 rows in set (0.00 sec) mysql> select * from t3 where EXISTS (select * from t4 where dep_id=300); Empty set (0.00 sec) 标签：03,12,name,05,sec,mysql,基本操作,id,man From： https://www.cnblogs.com/BXXY5961/p/17476383.html