MySQL基本操作
一、MySQL的使用
如果通过命令行启动和停止MySQL数据库,一定要以管理员身份运行命令行cmd。
否则,会出现错误。
1、启动和停止MySQL服务
(1)通过Windows计算机管理方式
右击此电脑—管理—服务和应用程序—服务,启动或停止MySQL服务
(2)通过命令行方式
- 启动:net start mysql
- 停止:net stop mysql
一定要以管理员的身份打开cmd命令。
2、登录和退出MySQL数据库
(1)使用命令行登录和退出
C:\Windows\system32>mysql -h localhost -P 3306 -u root -p1234 db_hr -e"select * from user;"
- 最前面的mysql你可以理解成一个关键字或者理解成一个固定的命令,是固定写法,类似于java、jdk中的javac命令或java命令;
- -h表示host,即主机的ip地址——127.0.0.1;
- -P表示port,端口,mysql数据库的默认端口是3306,当然,你也可以自己修改端口号,我这里没改端口号(注意:这是大写的字母P);
- -u表示user用户名,这里是root;
- -p表示password密码1234(注意:这是小写的字母p);
- db_hr:数据库名表示登录到哪一个数据库中;
- -e参数后面可以直接加SQL语句。登录mysql服务器之后,立即执行这个SQL语句。-e后面不要有空格。
下面说说mysql这个命令的注意事项:
大写的P表示端口号,小写的p表示密码;
小写的p表示密码,-p和密码之间一定不能有空格,其他的像-u,-h,-P之类的,是可以有空格的,也可以没有空格。
如果是本机的话,主机ip和端口号可以不写(即主机ip和端口号可以省略),直接写成mysql -u root -p1234
如果是本机,但是端口号你改成了其他的端口号,不是默认的3306了,比如你把端口号改成了6688,那你就加上端口号,即mysql -P 6688 -u root -p1234
以下这3种语法都是正确的,我依次举例和截图演示:
我这里用的用户名是root,密码也是1234
语法1:mysql -h 主机ip地址 -P 端口号 -u 用户名 -p密码
(-h和主机ip地址之间有空格,-P和端口号之间有空格,-u和用户名之间有空格,-p和密码之间一定不能有空格)
mysql -h localhost -P 3306 -u root -p1234
如果是本机的话,主机ip地址和端口号(是默认3306的情况下)-h localhost -P 3306可以省略不写,
mysql -u root -p1234
如果是本机,但是端口你之前改成了其他的,比如端口你改成了8801,不是默认的3306端口了,那么主机ip地址可以省略不写,但是要写上端口号,
mysql -P 8801 -u root -p1234
如果是远程主机的话,必须写-h 远程主机的ip,
mysql -h 192.168.117.66 -P 3306 -u root -p1234
如果远程主机的mysql数据库端口默认是3306,那端口号可以省略不写,但是远程主机的ip地址要写,
mysql -h 192.168.117.66 -u root -p1234
如果远程主机的mysql数据库端口不是默认的3306,端口而被改成了比如6655,那远程主机ip地址和端口号都要写上,
mysql -h 192.168.117.66 -P 6655 -u root -p1234
语法2:mysql -h主机 ip地址 -P端口号 -u用户名 -p密码
(-h和主机ip地址之间无空格,-P和端口号之间无空格,-u和用户名之间无空格,-p和密码之间一定不能有空格)
mysql -h192.168.117.66 -P3306 -uroot -p1234
语法3:mysql -h主机ip地址 -P端口号 -u用户名 -p
(最后一个-p,小写字母p后面不写密码)
mysql -h 192.168.117.66 -P 3306 -u root -p
或者
mysql -h192.168.117.66 -P3306 -uroot -p
注意:小写字母p后面不写密码,这样的话,密码就不会显示暴露出来了,输入密码的时候也是显示成****,进入数据库之后再输入密码。
退出登录,可以使用 quit 或者 exit 或者 \q 命令
(2)通过MySQL自带的客户端,仅限于root用户。
3、MySQL的相关命令
List of all client commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement. --清除当前输入的语句
connect (\r) Reconnect to the server. Optional arguments are db and host. --重新连接,通常用于被踢出或异常断开后重新连接,SQL*plus下也有这样一个connect命令。
delimiter (\d) Set statement delimiter.--设置命令终止符,缺省为;,比如我们可以设定为/来表示语句结束
edit (\e) Edit command with $EDITOR. --编辑缓冲区的上一条SQL语句到文件,缺省调用vi,文件会放在/tmp路径下
ego (\G) Send command to MariaDB server, display result vertically. --控制结果显示为垂直显示
exit (\q) Exit mysql. Same as quit. --退出mysql
go (\g) Send command to MariaDB server. --发送命令到mysql服务
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout. --关闭页设置,打印到标准输出
notee (\t) Don't write into outfile. --关闭输出到文件
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. --设置pager方式,可以设置为调用more,less等等,主要是用于分页显示
print (\p) Print current command.
prompt (\R) Change your mysql prompt. --改变mysql的提示符
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash. --自动补齐相关对象名字
source (\.) Execute an SQL script file. Takes a file name as an argument. --执行脚本文件
status (\s) Get status information from the server. --获得状态信息
system (\!) Execute a system shell command. --执行系统命令
tee (\T) Set outfile [to_outfile]. Append everything into given outfile. --操作结果输出到文件
use (\u) Use another database. Takes database name as argument. --切换数据库
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. --设置字符集
warnings (\W) Show warnings after every statement. --打印警告信息
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
注意:上面的所有命令,扩号内的为快捷操作,即只需要输入“\”+ 字母即可执行。
二、MySQL支持的基本数据类型
1、数值类型
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)、近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 Bytes | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 Bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 Bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 Bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 Bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 Bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2、字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
3、日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 |
1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 |
YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
三、数据库的基本操作
1、创建和查看数据库
我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下:
CREATE DATABASE 数据库名;
以下命令简单的演示了创建数据库的过程,数据名为db_hr:
mysql> create DATABASE db_hr;
使用 show命令来查看已创建的数据库,语法如下:
mysql> show DATABASE;
另外,还可以使用 show命令来查看已创建的数据库信息,语法如下:
mysql> show CREATE DATABASE db_hr;
以上的执行结果显示数据库db_hr的创建信息,例如编码方式是utf8。
除了可以用默认的编码方式创建数据库外,还可以在创建数据库时指定编码方式,
mysql> CREATE DATABASE db_hr2 CHARACTER SET gbk;
可以看出数据库db_hr2的编码方式为gbk。
2、使用数据库
在你连接到 MySQL 数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库。
在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。语法格式如下:
use 数据库名;
mysql> use db_hr;
Database changed
在出现Database changed提示时,证明已经切换到了数据库db_hr。
mysql数据库文件的真实的物理存储位置。
mysql> show global variables like "%datadir%";
3、修改数据库
在 MySQL中,可以使用 ALTER DATABASE 语句来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为:
ALTER DATABASE [数据库名] { [ DEFAULT ] CHARACTER SET <字符集名> | [ DEFAULT ] COLLATE <校对规则名>}
语法说明如下:
- ALTER DATABASE 用于更改数据库的全局特性。这些特性存储在数据库目录的 db.opt 文件中。
- 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
- 数据库名称可以忽略,此时语句对应于默认数据库。
- CHARACTER SET 子句用于更改默认的数据库字符集
例如,用alter命令将数据库 db_hr2 的指定字符集修改为 gb2312,默认校对规则修改为 gb2312_unicode_ci,输入 SQL 语句与执行结果如下所示:
mysql> ALTER DATABASE db_hr2 DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;
4、删除数据库
当数据库不再使用时应该将其删除,以确保数据库存储空间中存放的是有效数据。
删除数据库是将已经存在的数据库从磁盘空间上清除,清除之后,数据库中的所有数据也将一同被删除。
在 MySQL 中,当需要删除已创建的数据库时,可以使用 DROP DATABASE 语句。其语法格式为:
DROP DATABASE [ IF EXISTS ] <数据库名>
语法说明如下:
- <数据库名>:指定要删除的数据库名。
- IF EXISTS:用于防止当数据库不存在时发生错误。
- DROP DATABASE:删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心,以免错误删除。如果要使用 DROP DATABASE,需要获得数据库 DROP 权限。
注意:MySQL 安装后,系统会自动创建名为 information_schema 和 mysql 的两个系统数据库,系统数据库存放一些和数据库相关的信息,如果删除了这两个数据库,MySQL 将不能正常工作。
使用命令行工具将数据库db_hr2从数据库列表中删除,输入的 SQL 语句与执行结果如下所示:
mysql> DROP DATABASE db_hr2;
此时数据库db_hr2不存在。再次执行相同的命令,直接使用 DROP DATABASE db_hr2,系统会报错,如下所示:
如果使用IF EXISTS
从句,可以防止系统报此类错误,如下所示:
mysql> DROP DATABASE IF EXISTS db_hr2;
使用 DROP DATABASE 命令时要非常谨慎,在执行该命令后,MySQL 不会给出任何提示确认信息。
DROP DATABASE 删除数据库后,数据库中存储的所有数据表和数据也将一同被删除,而且不能恢复。
因此最好在删除数据库之前先将数据库进行备份。备份数据库的方法会在教程后面进行讲解。
四、表的基本操作
1、创建数据表
在创建数据库之后,接下来就要在数据库中创建数据表。所谓创建数据表,指的是在已经创建的数据库中建立新表。
创建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性(包括实体完整性、引用完整性和域完整性)约束的过程。
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
接下来我们介绍一下创建数据表的语法形式。
可以使用 CREATE TABLE 语句创建表。其语法格式为:
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
其中,[表定义选项]的格式为:<列名1> <类型1> [,…] <列名n> <类型n>
CREATE TABLE 命令语法比较多,其主要是由表创建定义(create-definition)、表选项(table-options)和分区选项(partition-options)所组成的。
这里首先描述一个简单的新建表的例子,然后重点介绍 CREATE TABLE 命令中的一些主要的语法知识点。
CREATE TABLE 语句的主要语法及使用说明如下:
- CREATE TABLE:用于创建给定名称的表,必须拥有表CREATE的权限。
- <表名>:指定要创建表的名称,在 CREATE TABLE 之后给出,必须符合标识符命名规则。表名称被指定为 db_name.tbl_name,以便在特定的数据库中创建表。无论是否有当前数据库,都可以通过这种方式创建。在当前数据库中创建表时,可以省略 db_name。如果使用加引号的识别名,则应对数据库和表名称分别加引号。例如,'mydb'.'mytbl' 是合法的,但 'mydb.mytbl' 不合法。
- <表定义选项>:表创建定义,由列名(col_name)、列的定义(column_definition)以及可能的空值说明、完整性约束或表索引组成。
- 默认的情况是,表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在,则会出现错误。
提示:使用 CREATE TABLE 创建表时,必须指定以下信息:
- 要创建的表的名称不区分大小写,不能使用SQL语言中的关键字,如DROP、ALTER、INSERT等。
- 数据表中每个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
(1)在指定的数据库中创建表
数据表属于数据库,在创建数据表之前,应使用语句“USE <数据库>”指定操作在哪个数据库中进行,
如果没有选择数据库,就会抛出 No database selected 的错误。
创建员工表 tb_emp1,结构如下表所示。
字段名称 | 数据类型 | 备注 |
---|---|---|
id | INT(10) | 员工编号 |
name | VARCHAR(25) | 员工名称 |
deptld | INT(10) | 所在部门编号 |
salary | FLOAT | 工资 |
如果之前创建过数据库,可以使用之前创建的数据库,如果没有,我们可以新建一个数据库,我们创建一个新的数据库db_test。
mysql> create DATABASE db_test;
Query OK, 1 row affected (0.00 sec)
选择创建表的数据库 db_test,创建 tb_emp1 数据表,输入的 SQL 语句和运行结果如下所示。
mysql> use db_test;
Database changed
mysql> CREATE TABLE tb_emp1 ( id INT(10), name VARCHAR(25), deptId INT(10), salary FLOAT );
Query OK, 0 rows affected (0.02 sec)
语句执行后,便创建了一个名称为 tb_emp1 的数据表,使用 SHOW TABLES;语句查看数据表是否创建成功,如下所示。
可以看出,数据库中已经成功创建了tb_emp1表。
2、查看数据表
我们可以通过SHOW CREATE TABLE语句来查看数据表,语法格式如下。
SHOW CREATE TABLE <表名>
查看前面创建的tb_emp1表。
mysql> SHOW CREATE TABLE tb_emp1;
结果看起来有点乱,我们可以在查询语句后面加上参数“\G”进行格式化。
mysql> SHOW CREATE TABLE tb_emp1 \G;
这样看起来比之前整齐多了。
另外,我们还可以使用DESCRIBE语句或简写形式DESC语句查看表中列的信息。语法格式如下。
DESCRIBE <表名>;
DESC <表名>;
mysql> DESCRIBE tb_emp1;
3、修改数据表
在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构,例如增加或删减列、更改原有列类型、重新命名列或表等。其语法格式如下:
ALTER TABLE <表名> [修改选项]
修改选项的语法格式如下:
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }
(1)修改表名
MySQL 通过 ALTER TABLE 语句来实现表名的修改,语法规则如下:
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
其中,TO 为可选参数,使用与否均不影响结果。
使用 ALTER TABLE 将数据表 tb_emp1改名为 tb_emp_info,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1 RENAME TO tb_emp_info;
提示:修改表名并不修改表的结构,因此修改名称后的表和修改名称前的表的结构是相同的。用户可以使用 DESC 命令查看修改后的表结构。
(2)修改字段
MySQL 中修改表字段名的语法规则如下:
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中:
- 旧字段名:指修改前的字段名;
- 新字段名:指修改后的字段名;
- 新数据类型:指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样,但数据类型不能为空。
使用 ALTER TABLE 修改表 tb_emp_info 的结构,将 deptId字段名称改为 sex,同时将数据类型变为 VARCHAR(10),SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp_info CHANGE deptId sex VARCHAR(10);
语句执行后,发现表 tb_emp_info 中 deptId字段名称改为 sex,同时将数据类型变为 VARCHAR(10),修改成功。
CHANGE 也可以只修改数据类型,实现和 MODIFY (下一小节)同样的效果,方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称,只改变“数据类型”。
提示:由于不同类型的数据在机器中的存储方式及长度并不相同,修改数据类型可能会影响数据表中已有的数据记录.
因此,当数据表中已经有数据时,不要轻易修改数据类型。
(3)修改字段的数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下:
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中:
- 表名:指要修改数据类型的字段所在表的名称;
- 字段名:指需要修改的字段;
- 数据类型:指修改后字段的新数据类型。
使用 ALTER TABLE 修改表 tb_emp_info 的结构,将 name 字段的数据类型由 VARCHAR(25) 修改成 VARCHAR(30),SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp_info MODIFY name VARCHAR(30);
语句执行后,发现表 tb_emp_info 中 name 字段的数据类型已经修改成 VARCHAR(30),修改成功。
(4)删除字段
删除字段是将数据表中的某个字段从表中移除,语法格式如下:
ALTER TABLE <表名> DROP <字段名>;
其中,“字段名”指需要从表中删除的字段的名称。
使用 ALTER TABLE 修改表 tb_emp_info 的结构,删除 salary字段,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp_info DROP salary;
语句执行后,发现表 tb_emp_info 中 salary 字段没有了,删除成功。
(5)添加字段
MySQL 允许在开头、中间和结尾处添加字段。
在末尾添加字段
一个完整的字段包括字段名、数据类型和约束条件。MySQL 添加字段的语法格式如下:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件];
对语法格式的说明如下:
- <表名> 为数据表的名字;
- <新字段名> 为所要添加的字段的名字;
- <数据类型> 为所要添加的字段能存储数据的数据类型;
- [约束条件] 是可选的,用来对添加的字段进行约束。
这种语法格式默认在表的最后位置(最后一列的后面)添加新字段。
注意:这里我们只添加新的字段,不关注它的约束条件。
使用 ALTER TABLE 语句在tb_emp_info表中添加一个 INT 类型的字段 age,SQL 语句和运行结果如下:
mysql> ALTER TABLE tb_emp_info ADD age INT(4);
由运行结果可以看到,tb_emp_info表已经添加了 age 字段,且该字段在表的最后一个位置,添加字段成功。
在开头添加字段
MySQL 默认在表的最后位置添加新字段,如果希望在开头位置(第一列的前面)添加新字段,那么可以使用 FIRST 关键字,语法格式如下:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
FIRST 关键字一般放在语句的末尾。
使用 ALTER TABLE 语句在表的第一列添加 INT 类型的字段 stuId,SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp_info ADD empNum INT(4) FIRST;
由运行结果可以看到,tb_emp_info表中已经添加了empNum字段,且该字段在表中的第一个位置,添加字段成功。
在中间位置添加字段
MySQL 除了允许在表的开头位置和末尾位置添加字段外,还允许在中间位置(指定的字段之后)添加字段,此时需要使用 AFTER 关键字,语法格式如下:
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
AFTER 的作用是将新字段添加到某个已有字段后面。
注意,只能在某个已有字段的后面添加新字段,不能在它的前面添加新字段。
使用 ALTER TABLE 语句在tb_emp_info表中添加名为 school,数据类型为 VARCHAR(30)的字段,school字段位于 name 字段的后面。SQL 语句和运行结果如下:
mysql> ALTER TABLE tb_emp_info ADD school VARCHAR(30) AFTER name;
由运行结果可以看到,tb_emp_info表中已经添加了 school字段,且该字段在 name 字段后面的位置,添加字段成功。
(6)修改字段的排位顺序
如果需要修改表中字段的位置,可以使用 ALTER TABLE 语句,语法格式如下:
ALTER TABLE <表名> MODIFY <字段名1> <数据类型> FIRST | AFTER <字段名2>;
对语法格式的说明如下:
- <表名> 为数据表的名字;
- <字段名1> 为需要修改位置的字段;
- <数据类型> 为需要修改位置的字段的数据类型;
- FIRST是可选参数,表示将字段1修改为第一个字段;
- AFTER <字段名2>表示将字段1插入到字段2的后面。
使用 ALTER TABLE 语句在tb_emp_info表中,将empNum字段移动到 id字段的后面。SQL 语句和运行结果如下:
mysql> ALTER TABLE tb_emp_info MODIFY empNum INT(4) AFTER id;
4、删除数据表
在 MySQL 数据库中,对于不再需要的数据表,我们可以将其从数据库中删除。
在删除表的同时,表的结构和表中所有的数据都会被删除,因此在删除数据表之前最好先备份,以免造成无法挽回的损失。
使用 DROP TABLE 语句可以删除一个或多个数据表,语法格式如下:
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
对语法格式的说明如下:
表名1, 表名2, 表名3 ...
表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表,只要将表名依次写在后面,相互之间用逗号隔开即可。- IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS,当数据表不存在时 MySQL 将提示错误,中断 SQL 语句的执行;加上 IF EXISTS 后,当数据表不存在时 SQL 语句可以顺利执行,但是会发出警告(warning)。
两点注意:
- 用户必须拥有执行 DROP TABLE 命令的权限,否则数据表不会被删除。
- 表被删除时,用户在该表上的权限不会自动删除。
删除数据表 tb_emp_info,输入的 SQL 语句和运行结果如下所示:
mysql> DROP TABLE tb_emp_info;
五、表中数据的基本操作
1、添加数据
数据库与表创建成功以后,需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。
基本语法
INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…SET 语句。
(1)INSERT…VALUES语句
INSERT VALUES 的语法格式为:
INSERT INTO <表名> [ <列名1> [ , … <列名n>] ] VALUES (值1) [… , (值n) ];
语法说明如下。
<表名>
:指定被操作的表名。<列名>
:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可以省略,直接采用 INSERT<表名>VALUES(…) 即可。VALUES
或VALUE
子句:该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。
(2)INSERT…SET语句
语法格式为:
INSERT INTO <表名> SET <列名1> = <值1>, <列名2> = <值2>, …
此语句用于直接给表中的某些列指定对应的列值,即要插入的数据的列名在 SET 子句中指定,等号前面为指定的列名,等号后面为指定的数据,而对于未指定的列,列值会指定为该列的默认值。
由 INSERT 语句的两种形式可以看出:
- 使用 INSERT…VALUES 语句可以向表中插入一行数据,也可以插入多行数据;
- 使用 INSERT…SET 语句可以指定插入行中每列的值,也可以指定部分列的值;
- INSERT…SELECT 语句向表中插入其他表的数据。
- 采用 INSERT…SET 语句可以向表中插入部分列的值,这种方式更为灵活;
- INSERT…VALUES 语句可以一次插入多条数据。
在 MySQL 中,用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。
当使用单条 INSERT 语句插入多行数据的时候,只需要将每行数据用圆括号括起来即可。
向表中的全部字段添加值
在db_test 数据库中创建一个课程信息表 tb_courses。
包含:课程编号 course_id、课程名称 course_name、课程学分 course_grade 、课程备注 course_info,输入的 SQL 语句和执行结果如下所示。
mysql> CREATE TABLE tb_courses -> ( -> course_id INT NOT NULL AUTO_INCREMENT, -> course_name CHAR(40) NOT NULL, -> course_grade FLOAT NOT NULL, -> course_info CHAR(100) NULL, -> PRIMARY KEY(course_id) -> ); Query OK, 0 rows affected (0.02 sec)
向表中所有字段插入值的方法有两种:一种是指定所有字段名;另一种是完全不指定字段名。
【例 1】在 tb_courses 表中插入一条新记录,course_id 值为 1,course_name 值为“Network”,course_grade 值为 3,info 值为“Computer Network”。
在执行插入操作之前,查看 tb_courses 表的SQL语句和执行结果如下所示。
mysql> SELECT * FROM tb_courses; Empty set (0.00 sec)
查询结果显示当前表内容为空,没有数据,接下来执行插入数据的操作,输入的 SQL 语句和执行过程如下所示。
mysql> INSERT INTO tb_courses (course_id,course_name,course_grade,course_info) VALUES(1,'Network',3,'Computer Network'); Query OK, 1 rows affected (0.01 sec) mysql> SELECT * FROM tb_courses;
可以看到插入记录成功。在插入数据时,指定了 tb_courses 表的所有字段,因此将为每一个字段插入新的值。
INSERT 语句后面的列名称顺序可以不是 tb_courses 表定义时的顺序,即插入数据时,不需要按照表定义的顺序插入,只要保证值的顺序与列字段的顺序相同就可以。
【例 2】在 tb_courses 表中插入一条新记录,course_id 值为 2,course_name 值为“Database”,course_grade 值为 3,info值为“MySQL”。输入的 SQL 语句和执行结果如下所示。
mysql> INSERT INTO tb_courses (course_name,course_info,course_id,course_grade) VALUES('Database','MySQL',2,3); Query OK, 1 rows affected (0.01 sec) mysql> SELECT * FROM tb_courses;
使用 INSERT 插入数据时,允许列名称列表 column_list 为空,此时值列表中需要为表的每一个字段指定值,并且值的顺序必须和数据表中字段定义时的顺序相同。
【例 3】在 tb_courses 表中插入一条新记录,course_id 值为 3,course_name 值为“Java”,course_grade 值为 4,info 值为“Jave EE”。输入的 SQL 语句和执行结果如下所示。
mysql> INSERT INTO tb_courses VALUES(3,'Java',4,'Java EE'); Query OK, 1 rows affected (0.01 sec) mysql> SELECT * FROM tb_courses;
INSERT 语句中没有指定插入列表,只有一个值列表。在这种情况下,值列表为每一个字段列指定插入的值,并且这些值的顺序必须和 tb_courses 表中字段定义的顺序相同。
注意:虽然使用 INSERT 插入数据时可以忽略插入数据的列名称,若值不包含列名称,则 VALUES 关键字后面的值不仅要求完整,而且顺序必须和表定义时列的顺序相同。如果表的结构被修改,对列进行增加、删除或者位置改变操作,这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称,就不会受到表结构改变的影响。
向表中指定字段添加值
为表的指定字段插入数据,是在 INSERT 语句中只向部分字段中插入值,而其他字段的值为表定义时的默认值。
【例 4】在 tb_courses 表中插入一条新记录,course_name 值为“System”,course_grade 值为 3,course_info 值为“Operating System”,输入的 SQL 语句和执行结果如下所示。
mysql> INSERT INTO tb_courses (course_name,course_grade,course_info) VALUES('System',3,'Operation System'); Query OK, 1 rows affected (0.08 sec) mysql> SELECT * FROM tb_courses;
可以看到插入记录成功。如查询结果显示,这里的 course_id 字段自动添加了一个整数值 4。这时的 course_id 字段为表的主键,不能为空,系统自动为该字段插入自增的序列值。在插入记录时,如果某些字段没有指定插入值,MySQL 将插入该字段定义时的默认值。
(3)使用 INSERT INTO…FROM 语句复制表数据
INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据,并将这些数据作为行数据插入另一个表中。
SELECT 子句返回的是一个查询到的结果集,INSERT 语句将这个结果集插入指定表中,结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。
在数据库 test_db 中创建一个与 tb_courses 表结构相同的数据表 tb_courses_new,创建表的 SQL 语句和执行过程如下所示。
mysql> CREATE TABLE tb_courses_new -> ( -> course_id INT NOT NULL AUTO_INCREMENT, -> course_name CHAR(40) NOT NULL, -> course_grade FLOAT NOT NULL, -> course_info CHAR(100) NULL, -> PRIMARY KEY(course_id) -> ); Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM tb_courses_new; Empty set (0.00 sec)
【例 5】从 tb_courses 表中查询所有的记录,并将其插入 tb_courses_new 表中。输入的 SQL 语句和执行结果如下所示。
mysql> INSERT INTO tb_courses_new (course_id,course_name,course_grade,course_info)
-> SELECT course_id,course_name,course_grade,course_info FROM tb_courses;
Query OK, 4 rows affected (0.17 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tb_courses_new;
2、修改数据
在 MySQL 中,可以使用 UPDATE 语句来修改、更新一个或多个表的数据。
UPDATE 语句的基本语法
使用 UPDATE 语句修改单个表,语法格式为:
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]
语法说明如下:
<表名>
:用于指定要更新的表名称。SET
子句:用于指定表中要修改的列名及其列值。其中,每个指定的列值可以是表达式,也可以是该列对应的默认值。如果指定的是默认值,可用关键字 DEFAULT 表示列值。WHERE
子句:可选项。用于限定表中要修改的行。若不指定,则修改表中所有的行。ORDER BY
子句:可选项。用于限定表中的行被修改的次序。LIMIT
子句:可选项。用于限定被修改的行数。
注意:修改一行数据的多个列值时,SET 子句的每个值用逗号分开即可。
(1)修改表中的数据
【例 1】在 tb_courses_new 表中,更新所有行的 course_grade 字段值为 4,输入的 SQL 语句和执行结果如下所示。
mysql> UPDATE tb_courses_new SET course_grade=4; Query OK, 3 rows affected (0.01 sec) Rows matched: 4 Changed: 3 Warnings: 0 mysql> SELECT * FROM tb_courses_new;
(2)根据条件修改表中的数据
【例 2】在 tb_courses 表中,更新 course_id 值为 2 的记录,将 course_grade 字段值改为 3.5,将 course_name 字段值改为“DB”,输入的 SQL 语句和执行结果如下所示。
mysql> UPDATE tb_courses_new SET course_name='DB',course_grade=3.5 WHERE course_id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM tb_courses_new;
注意:保证 UPDATE 以 WHERE 子句结束,通过 WHERE 子句指定被更新的记录所需要满足的条件,如果忽略 WHERE 子句,MySQL 将更新表中所有的行。
3、删除数据
在 MySQL 中,可以使用 DELETE 语句来删除表的一行或者多行数据。
(1)删除单个表中的数据
使用 DELETE 语句从单个表中删除数据,语法格式为:
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下:
<表名>
:指定要删除数据的表名。ORDER BY
子句:可选项。表示删除时,表中各行将按照子句中指定的顺序进行删除。WHERE
子句:可选项。表示为删除操作限定删除条件,若省略该子句,则代表删除该表中的所有行。LIMIT
子句:可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
注意:在不使用 WHERE 条件的时候,将删除所有数据。
(2)删除表中的全部数据
【例 1】删除 tb_courses_new 表中的全部数据,输入的 SQL 语句和执行结果如下所示。
mysql> DELETE FROM tb_courses_new; Query OK, 4 rows affected (0.12 sec) mysql> SELECT * FROM tb_courses_new; Empty set (0.00 sec)
(3)根据条件删除表中的数据
【例 2】在 tb_courses_new 表中,删除 course_id 为 4 的记录,输入的 SQL 语句和执行结果如下所示。
mysql> DELETE FROM tb_courses WHERE course_id=4; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tb_courses;
由运行结果可以看出,course_id 为 4 的记录已经被删除。
4、清空表记录
MySQL 提供了 DELETE 和 TRUNCATE 关键字来删除表中的数据。这里主要讲解 TRUNCATE 关键字的使用。
TRUNCATE 关键字用于完全清空一个表。其语法格式如下:
TRUNCATE [TABLE] 表名
其中,TABLE 关键字可省略。
【例1】新建表 tb_student_course,插入数据并查询,SQL 语句和运行结果如下:
mysql> CREATE TABLE `tb_student_course` ( -> `id` int(4) NOT NULL AUTO_INCREMENT, -> `name` varchar(25) NOT NULL, -> PRIMARY KEY (`id`) -> ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO tb_student_course(name) VALUES ('Java'),('MySQL'),('Python'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM tb_student_course;
使用 TRUNCATE 语句清空 tb_student_course 表中的记录,SQL 语句和运行结果如下:
mysql> TRUNCATE TABLE tb_student_course; Query OK, 0 rows affected (0.03 sec) mysql> SELECT * FROM tb_student_course; Empty set (0.00 sec)
TRUNCATE 和 DELETE 的区别
从逻辑上说,TRUNCATE 语句与 DELETE 语句作用相同,但是在某些情况下,两者在使用上有所区别。
- DELETE 是 DML 类型的语句;TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
- DELETE 是逐行一条一条删除记录的;TRUNCATE 则是直接删除原来的表,再重新创建一个一模一样的新表,而不是逐行删除表中的数据,执行数据比 DELETE 快。因此需要删除表中全部的数据行时,尽量使用 TRUNCATE 语句, 可以缩短执行时间。
- DELETE 删除数据后,配合事件回滚可以找回数据;TRUNCATE 不支持事务的回滚,数据删除后无法找回。
- DELETE 删除数据后,系统不会重新设置自增字段的计数器;TRUNCATE 清空表记录后,系统会重新设置自增字段的计数器。
- DELETE 的使用范围更广,因为它可以通过 WHERE 子句指定条件来删除部分数据;而 TRUNCATE 不支持 WHERE 子句,只能删除整体。
- DELETE 会返回删除数据的行数,但是 TRUNCATE 只会返回 0,没有任何意义。
总结
当不需要该表时,用 DROP;当仍要保留该表,但要删除所有记录时,用 TRUNCATE;当要删除部分记录时,用 DELETE。
六、查询数据
在 MySQL 中,可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求,使用不同的查询方式来获取不同的数据,是使用频率最高、最重要的操作。
SELECT 的语法格式如下:
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]
其中,各条子句的含义如下:
{*|<字段列名>}
包含星号通配符的字段列表,表示所要查询字段的名称。<表 1>,<表 2>…
,表 1 和表 2 表示查询数据的来源,可以是单个或多个。WHERE <表达式>
是可选项,如果选择该项,将限定查询数据必须满足该查询条件。GROUP BY< 字段 >
,该子句告诉 MySQL 如何显示查询出来的数据,并按照指定的字段分组。[ORDER BY< 字段 >]
,该子句告诉 MySQL 按什么样的顺序显示查询出来的数据,可以进行的排序有升序(ASC)和降序(DESC),默认情况下是升序。[LIMIT[<offset>,]<row count>]
,该子句告诉 MySQL 每次显示查询出来的数据条数。
下面先介绍一些简单的 SELECT 语句,关于 WHERE、GROUP BY、ORDER BY 和 LIMIT 等限制条件,后面我们会一一讲解。
1、查询表中所有字段
查询所有字段是指查询表中所有字段的数据。MySQL 提供了以下 2 种方式查询表中的所有字段。
- 使用“*”通配符查询所有字段
- 列出表的所有字段
(1)使用“*”查询表的所有字段
SELECT 可以使用“*”查找表中所有字段的数据,语法格式如下:
SELECT * FROM 表名;
使用“*”查询时,只能按照数据表中字段的顺序进行排列,不能改变字段的排列顺序。
【例1】从 tb_courses 表中查询所有字段的数据,SQL 语句和运行结果如下所示。
mysql> SELECT * FROM tb_courses;
结果显示,使用“*”通配符时,将返回所有列,数据列按照创建表时的顺序显示。
注意:一般情况下,除非需要使用表中所有的字段数据,否则最好不要使用通配符“*”。虽然使用通配符可以节省输入查询语句的时间,但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。使用“*”的优势是,当不知道所需列的名称时,可以通过“*”获取它们。
(2)列出表的所有字段
SELECT 关键字后面的字段名为需要查找的字段,因此可以将表中所有字段的名称跟在 SELECT 关键字后面。如果忘记了字段名称,可以使用 DESC 命令查看表的结构。
有时,由于表的字段比较多,不一定能记得所有字段的名称,因此该方法很不方便,不建议使用。
【例2】查询 tb_students_info 表中的所有数据,SQL 语句还可以书写如下:
mysql> SELECT course_id,course_name,course_grade,course_info FROM tb_courses;
运行结果和例 1 相同。
这种查询方式比较灵活,如果需要改变字段显示的顺序,只需调整 SELECT 关键字后面的字段列表顺序即可。
虽然列出表的所有字段的方式比较灵活,但是查询所有字段时通常使用“*”通配符。使用“*”这种方式比较简单,尤其是表中的字段很多的时候,这种方式的优势更加明显。当然,如果需要改变字段显示的顺序,可以选择列出表的所有字段。
2、查询表中指定的字段
查询表中的某一个字段的语法格式为:
SELECT < 列名 > FROM < 表名 >;
【例3】查询 tb_courses 表中 name 列所有学生的姓名,SQL 语句和运行结果如下所示。
mysql> SELECT course_name FROM tb_courses;
输出结果显示了 tb_courses表中 course_name 字段下的所有数据。
使用 SELECT 声明可以获取多个字段下的数据,只需要在关键字 SELECT 后面指定要查找的字段名称,不同字段名称之间用逗号“,”分隔开,最后一个字段后面不需要加逗号,语法格式如下:
SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;
【例4】从 tb_courses 表中获取course_id、course_name 和 course_info三列,SQL 语句和运行结果如下所示。
mysql> SELECT course_id,course_name,course_info FROM tb_courses;
输出结果显示了tb_courses 表中的course_id、course_name 和 course_info三个字段下的所有数据。
3、使用DISTINCT过滤重复数据
在 MySQL 中使用 SELECT 语句执行简单的数据查询时,返回的是所有匹配的记录。
如果表中的某些字段没有唯一性约束,那么这些字段就可能存在重复值。
为了实现查询不重复的数据,MySQL 提供了 DISTINCT 关键字。
DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤,只返回其中的一条数据给用户。
DISTINCT 关键字的语法格式为:
SELECT DISTINCT <字段名> FROM <表名>;
其中,“字段名”为需要消除重复记录的字段名称,多个字段时用逗号隔开。
使用 DISTINCT 关键字时需要注意以下几点:
- DISTINCT 关键字只能在 SELECT 语句中使用。
- 在对一个或多个字段去重时,DISTINCT 关键字必须在所有字段的最前面。
- 如果 DISTINCT 关键字后有多个字段,则会对多个字段进行组合去重,也就是说,只有多个字段组合起来完全是一样的情况下才会被去重。
我们先在db_test数据库中,创建一个测试用表tb_students,输入的 SQL 语句如下所示。
mysql> use db_test
Database changed
mysql> CREATE TABLE tb_students
-> (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(40) NOT NULL,
-> stuNo INT NOT NULL,
-> deptID INT NOT NULL,
-> age INT NOT NULL,
-> sex VARCHAR(1) NOT NULL,
-> height FLOAT,
-> weight FLOAT,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> DESC tb_students;
然后,插入数据,输入的 SQL 语句如下所示。
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('zhangsan',10001,360101,18,'F',173,65);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('lisi',10002,360101,19,'M',175,68);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('wangwu',10003,360101,18,'F',165,48);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('zhaoliu',10004,360202,18,'M',185,68);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('zhangsan',10005,360201,21,'M',185,88);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('wangwu',10006,360202,21,'F',170,55);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb_students (name,stuNo,deptId,age,sex,height,weight)
-> VALUES('zhaoliu',10007,360102,19,'F',173,48);
Query OK, 1 row affected (0.00 sec)
【例 1】下面通过一个具体的实例来说明如何实现查询不重复数据。
db_test 数据库中 tb_students 表的表结构和数据如下所示:
mysql> SELECT * FROM tb_students;
结果显示,tb_students 表中存在 7条记录。
下面对 tb_students 表的 age 字段进行去重,SQL 语句和运行结果如下:
mysql> SELECT DISTINCT age FROM tb_students;
对 tb_students 表的 name 和 sex字段进行去重,SQL 语句和运行结果如下:
mysql> SELECT DISTINCT name,sex FROM tb_students;
对tb_students 表中的所有字段进行去重,SQL 语句和运行结果如下:
mysql> SELECT DISTINCT * FROM tb_students;
因为 DISTINCT 只能返回它的目标字段,而无法返回其它字段,所以在实际情况中,我们经常使用 DISTINCT 关键字来返回不重复字段的条数。
查询tb_students 表中对 name 和 sex 字段去重之后记录的条数,SQL 语句和运行结果如下:
mysql> SELECT COUNT(DISTINCT name,sex) FROM tb_students;
结果显示,tb_students 表中对 name 和 sex 字段去重之后有 6条记录。
4、AS:设置别名
为了查询方便,MySQL 提供了 AS 关键字来为表和字段指定别名
(1)为表指定别名
当表名很长或者执行一些特殊查询的时候,为了方便操作,可以为表指定一个别名,用这个别名代替表原来的名称。
为表指定别名的基本语法格式为:
<表名> [AS] <别名>
其中各子句的含义如下:
<表名>
:数据库中存储的数据表的名称。<别名>
:查询时指定的表的新名称。AS
关键字可以省略,省略后需要将表名和别名用空格隔开。
注意:表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。
在条件表达式中不能使用字段的别名,否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。
【例 1】下面为 tb_students表指定别名 stu,SQL 语句和运行结果如下。
mysql> SELECT stu.name,stu.height FROM tb_students AS stu;
(2)为字段指定别名
在使用 SELECT 语句查询数据时,MySQL 会显示每个 SELECT 后面指定输出的字段。有时为了显示结果更加直观,我们可以为字段指定一个别名。
为字段指定别名的基本语法格式为:
<字段名> [AS] <别名>
其中,各子句的语法含义如下:
<字段名>
:为数据表中字段定义的名称。<字段别名>
:字段新的名称。AS
关键字可以省略,省略后需要将字段名和别名用空格隔开。
【例 2】查询 tb_students 表,为 name 指定别名 student_name,为 age 指定别名 student_age,SQL 语句和运行结果如下。
mysql> SELECT name AS student_name, age AS student_age FROM tb_students;
注意:表别名只在执行查询时使用,并不在返回结果中显示。而字段定义别名之后,会返回给客户端显示,显示的字段为字段的别名。
5、LIMIT:限制查询结果的条数
当数据表中有上万条数据时,一次性查询出表中的全部数据会降低数据返回的速度,同时给数据库服务器造成很大的压力。
这时就可以用 LIMIT 关键字来限制查询结果返回的条数。
LIMIT 是 MySQL 中的一个特殊关键字,用于指定查询结果从哪条记录开始显示,一共显示多少条记录。
LIMIT 关键字有 3 种使用方式,即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。
(1)指定初始位置
LIMIT 关键字可以指定查询结果从哪条记录开始显示,显示多少条记录。
LIMIT 指定初始位置的基本语法格式如下:
LIMIT 初始位置,记录数
其中,“初始位置”表示从哪条记录开始显示;“记录数”表示显示记录的条数。第一条记录的位置是 0,第二条记录的位置是 1。后面的记录依次类推。
注意:LIMIT 后的两个参数必须都是正整数。
【例 1】在 tb_students 表中,使用 LIMIT 子句返回从第 4 条记录开始的行数为 2 的记录,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students LIMIT 3,2;
由结果可以看到,该语句返回的是从第 4 条记录开始的之后的 2 条记录。
LIMIT 关键字后的第一个数字“3”表示从第 4 行开始(记录的位置从 0 开始,第 4 行的位置为 3),第二个数字 2 表示返回的行数。
(2)不指定初始位置
LIMIT 关键字不指定初始位置时,记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。
LIMIT 不指定初始位置的基本语法格式如下:
LIMIT 记录数
其中,“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数,则会从第一条记录开始,显示指定条数的记录。如果“记录数”的值大于查询结果的总数,则会直接显示查询出来的所有记录。
【例 2】显示 tb_students 表查询结果的前 4 行,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students LIMIT 4;
结果中只显示了 4 条记录,说明“LIMIT 4”限制了显示条数为 4。
【例 3】显示 tb_students 表查询结果的前 15 行,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students LIMIT 15;
结果中只显示了 10 条记录。虽然 LIMIT 关键字指定了显示 15 条记录,但是查询结果中只有 10 条记录。因此,数据库系统就将这 10 条记录全部显示出来。
带一个参数的 LIMIT 指定从查询结果的首行开始,唯一的参数表示返回的行数,即“LIMIT n”与“LIMIT 0,n”返回结果相同。
带两个参数的 LIMIT 可返回从任何位置开始指定行数的数据。
(3)LIMIT和OFFSET组合使用
LIMIT 可以和 OFFSET 组合使用,语法格式如下:
LIMIT 记录数 OFFSET 初始位置
参数和 LIMIT 语法中参数含义相同,“初始位置”指定从哪条记录开始显示;“记录数”表示显示记录的条数。
【例 4】在 tb_students 表中,使用 LIMIT OFFSET 返回从第 4 条记录开始的行数为 2 的记录,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students LIMIT 2 OFFSET 3;
由结果可以看到,该语句返回的是从第 4 条记录开始的之后的 2 条记录。即“LIMIT 2 OFFSET 3”意思是获取从第 4 条记录开始的后面的 2 条记录,和“LIMIT 3,2”返回的结果相同。
6、ORDER BY:对查询结果排序
通过条件查询语句可以查询到符合用户需求的数据,但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。
为了使查询结果的顺序满足用户的要求,MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。
在实际应用中经常需要对查询结果进行排序,比如,在网上购物时,可以将商品按照价格进行排序;在医院的挂号系统中,可以按照挂号的先后顺序进行排序等。
ORDER BY 关键字主要用来将查询结果中的数据按照一定的顺序进行排序。其语法格式如下:
ORDER BY <字段名> [ASC|DESC]
语法说明如下:
- 字段名:表示需要排序的字段名称,多个字段时用逗号隔开。
- ASC|DESC:
ASC
表示字段按升序排序;DESC
表示字段按降序排序。其中ASC
为默认值。
使用 ORDER BY 关键字应该注意以下几个方面:
- ORDER BY 关键字后可以跟子查询(子查询后面就会详细讲到)。
- 当排序的字段中存在空值时,ORDER BY 会将该空值作为最小值来对待。
- ORDER BY 指定多个字段进行排序时,MySQL 会按照字段的顺序从左到右依次进行排序。
(1)单字段排序
下面通过一个具体的实例来说明当 ORDER BY 指定单个字段时,MySQL 如何对查询结果进行排序。
【例 1】下面查询 tb_students 表的所有记录,并对 height 字段进行排序,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students ORDER BY height;
由结果可以看到,MySQL 对查询的 height 字段的数据按数值的大小进行了升序排序。
(2)多字段排序
下面通过一个具体的实例来说明当 ORDER BY 指定多个字段时,MySQL 如何对查询结果进行排序。
【例 2】查询 tb_students 表中的 name 和 height 字段,先按 height 排序,再按 name 排序,SQL 语句和运行结果如下。
mysql> SELECT name,height FROM tb_students ORDER BY height,name;
注意:在对多个字段进行排序时,排序的第一个字段必须有相同的值,才会对第二个字段进行排序。
如果第一个字段数据中所有的值都是唯一的,MySQL 将不再对第二个字段进行排序。
默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用 ORDER BY 中的 DESC 对查询结果进行降序排序(Z~A)。
【例 3】查询 tb_students 表,先按 height 降序排序,再按 name 升序排序,SQL 语句和运行结果如下。
mysql> SELECT name,height FROM tb_students ORDER BY height DESC,name ASC;
DESC 关键字只对前面的列进行降序排列,在这里只对 height 字段进行降序。
因此,height 按降序排序,而 name 仍按升序排序。如果想在多个列上进行降序排序,必须对每个列指定 DESC 关键字。
7、WHERE:条件查询数据
在 MySQL 中,如果需要有条件的从数据表中查询数据,可以使用 WHERE 关键字来指定查询条件。
使用 WHERE 关键字的语法格式如下:
WHERE 查询条件
查询条件可以是:
- 带比较运算符和逻辑运算符的查询条件
- 带 BETWEEN AND 关键字的查询条件
- 带 IS NULL 关键字的查询条件
- 带 IN 关键字的查询条件
- 带 LIKE 关键字的查询条件
(1)单一条件的查询语句
单一条件指的是在 WHERE 关键字后只有一个查询条件。
【例 1】在 tb_students 数据表中查询身高为 170cm 的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name,height FROM tb_students WHERE height=170;
可以看到,查询结果中记录的 height 字段的值等于 170。如果根据指定的条件进行查询时,数据表中没有符合查询条件的记录,系统会提示“Empty set(0.00sec)”。
【例 2】在 tb_students 数据表中查询年龄小于 20 的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name,age FROM tb_students WHERE age<20;
可以看到,查询结果中所有记录的 age 字段的值均小于 20 岁,而大于或等于 20 岁的记录没有被返回。
(2)多条件的查询语句
在 WHERE 关键词后可以有多个查询条件,这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND(&&)、OR(||)或 XOR 隔开。
- AND:记录满足所有查询条件时,才会被查询出来。
- OR:记录满足任意一个查询条件时,才会被查询出来。
- XOR:记录满足其中一个条件,并且不满足另一个条件时,才会被查询出来。
【例 3】在 tb_students 表中查询 age 大于 19,并且 height 大于等于 170 的学生信息,SQL 语句和运行结果如下。
mysql> SELECT name,age,height FROM tb_students WHERE age>19 AND height>=170;
可以看到,查询结果中所有记录的 age 字段都大于19 且 height 字段都大于等于 170。
【例 4】在 tb_students 表中查询 age 大于 19,或者 height 大于等于 170 的学生信息,SQL 语句和运行结果如下。
mysql> SELECT name,age,height FROM tb_students WHERE age>19 OR height>=170;
可以看到,查询结果中所有记录的 age 字段都大于 19 或者 height 字段都大于等于 170。
【例 5】在 tb_students 表中查询 age 大于 19,并且 height 小于 173 的学生信息
和 age 小于等于 19,并且 height 大于等于 173 的学生信息,SQL 语句和运行结果如下。(好好理解,有点难!!!)
mysql> SELECT name,age,height FROM tb_students WHERE age>19 XOR height>=173;
可以看到,查询结果中所有记录的 age 字段都大于 19 且 height 字段都小于 173
和 age 字段小于等于 19 且 height 字段大于等于 173 的记录。
注意:OR、AND 和 XOR 可以一起使用,但是在使用时要注意运算符的优先级。
8、LIKE:模糊查询
在 MySQL 中,LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下:
[NOT] LIKE '字符串'
其中:
- NOT :可选参数,字段中的内容与指定的字符串不匹配时满足条件。
- 字符串:指定用来匹配的字符串。“字符串”可以是一个很完整的字符串,也可以包含通配符。
LIKE 关键字支持百分号“%”和下划线“_”通配符。
通配符是一种特殊语句,主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时,可以使用通配符来代替一个或多个真正的字符。
(1)带有“%”通配符的查询
“%”是 MySQL 中最常用的通配符,它能代表任何长度的字符串,字符串的长度可以为 0。
例如,a%b
表示以字母 a 开头,以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。
【例 1】在 tb_students 表中,查找所有以字母“Z”开头的学生姓名(默认情况下,LIKE 关键字匹配字符的时候是不区分大小写的),SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE name LIKE 'Z%';
可以看到,查询结果中只返回了以字母“Z”开头的学生姓名。
注意:匹配的字符串必须加单引号或双引号。
NOT LIKE 表示字符串不匹配时满足条件。
【例 2】在 tb_students 表中,查找所有不以字母“Z”开头的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT NAME FROM tb_students WHERE NAME NOT LIKE 'Z%';
可以看到,查询结果中返回了不以字母“Z”开头的学生姓名。
【例 3】在 tb_students 表中,查找所有包含字母“L”的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE name LIKE '%L%';
可以看到,查询结果中返回了所有包含字母“e”的学生姓名。
(2)带有“_”通配符的查询
“_”只能代表单个字符,字符的长度不能为 0。例如,a_b
可以代表 acb、adb、aub 等字符串。
【例 4】在 tb_students 表中,查找所有以字母“U”结尾,且“U”前面只有 5 个字母的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE name LIKE '_____U';
(3)LIKE 区分大小写
默认情况下,LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写,可以加入 BINARY 关键字。
【例 5】在 tb_students 表中,查找所有以字母“t”开头的学生姓名,区分大小写和不区分大小写的 SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE name LIKE 'Z%';
mysql> SELECT name FROM tb_students WHERE name LIKE BINARY 'Z%';
Empty set (0.01 sec)
由结果可以看到,区分大小写后,“zhangsan”和“zhaoliu”等记录就不会被匹配到了。
(3)使用通配符的注意事项和技巧
下面是使用通配符的一些注意事项:
- 注意大小写。MySQL 默认是不区分大小写的。如果区分大小写,像“zhangsan”这样的数据就不能被“Z%”所匹配到。
- 注意尾部空格。尾部空格会干扰通配符的匹配。例如,“Z% ”就不能匹配到“zhangsan”。
- 注意 NULL。“%”通配符可以到匹配任意字符,但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students 数据表中值为 NULL 的记录。
下面是一些使用通配符要记住的技巧:
- 不要过度使用通配符。如果其它操作符能达到相同的目的,应该使用其它操作符。因为 MySQL 对通配符的处理一般会比其他操作符花费更长的时间。
- 在确定使用通配符后,除非绝对有必要,否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
总之,通配符是一种极其重要和有用的搜索工具,以后我们会经常用到它。
(4)拓展
如果查询内容中包含通配符,可以使用“\”转义符。
例如,在 tb_students 表中,将学生姓名“zhaoliu”修改为“zhaoliu%”后,查询以“%”结尾的学生姓名,SQL 语句和运行结果如下:
mysql> SELECT NAME FROM tb_students WHERE NAME LIKE '%\%';
9、BETWEEN AND:范围查询
MySQL 提供了 BETWEEN AND 关键字,用来判断字段的数值是否在指定范围内。
BETWEEN AND 需要两个参数,即范围的起始值和终止值。如果字段值在指定的范围内,则这些记录被返回。如果不在指定范围内,则不会被返回。
使用 BETWEEN AND 的基本语法格式如下:
[NOT] BETWEEN 取值1 AND 取值2
其中:
- NOT:可选参数,表示指定范围之外的值。如果字段值不满足指定范围内的值,则这些记录被返回。
- 取值1:表示范围的起始值。
- 取值2:表示范围的终止值。
BETWEEN AND 和 NOT BETWEEN AND 关键字在查询指定范围内的记录时很有用。例如,查询学生的年龄段、出生日期,员工的工资水平等。
【例 1】在表 tb_students 中查询年龄在 20 到 23 之间的学生姓名和年龄,SQL 语句和运行结果如下。
mysql> SELECT name,age FROM tb_students WHERE age BETWEEN 19 AND 23;
查询结果中包含学生年龄为 19 和 23 的记录,这就说明,在 MySQL 中,BETWEEN AND 能匹配指定范围内的所有值,包括起始值和终止值。
【例 2】在表 tb_students 中查询年龄不在 19 到 23 之间的学生姓名和年龄,SQL 语句和运行结果如下。
mysql> SELECT name,age FROM tb_students WHERE age NOT BETWEEN 19 AND 23;
10、IS NULL:空值查询
MySQL 提供了 IS NULL 关键字,用来判断字段的值是否为空值(NULL)。空值不同于 0,也不同于空字符串。
如果字段的值是空值,则满足查询条件,该记录将被查询出来。如果字段的值不是空值,则不满足查询条件。
使用 IS NULL 的基本语法格式如下:
IS [NOT] NULL
其中,“NOT”是可选参数,表示字段值不是空值时满足条件。
准备工作:在 tb_students 表中增加 birthday字段。
mysql> ALTER TABLE tb_students ADD birthday DATE;
【例 1】下面使用 IS NULL 关键字来查询 tb_students 表中 birthday字段是 NULL 的记录。
mysql> SELECT name,birthday FROM tb_students WHERE birthday IS NULL;
注意:IS NULL 是一个整体,不能将 IS 换成“=”。
如果将 IS 换成“=”将不能查询出任何结果,数据库系统会出现“Empty set (0.00 sec)”这样的提示。
同理,IS NOT NULL 中的 IS NOT 不能换成“!=”或“<>”。
IS NOT NULL 表示查询字段值不为空的记录。
【例 2】下面使用 IS NOT NULL 关键字来查询 tb_students 表中 birthday 字段不为空的记录。
mysql> SELECT name,birthday FROM tb_students WHERE birthday IS NOT NULL;
Empty set (0.00 sec)
11、使用GROUP BY分组查询
在 MySQL 中,GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下:
GROUP BY <字段名>
其中,“字段名”表示需要分组的字段名称,多个字段时用逗号隔开。
(1)GROUP BY单独使用
单独使用 GROUP BY 关键字时,要注意,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的。
【例 1】下面根据 tb_students 表中的 sex 字段进行分组查询,SQL 语句和运行结果如下:
mysql> SELECT name,sex FROM tb_students GROUP BY sex;
ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列不在GROUP BY从句中,那么这个字段就应该出现在聚合函数里面。
也就是,在SELECT指定的字段要么包含在GROUP BY语句的后面,作为分组的依据;要么就要被包含在聚合函数中。
mysql> SELECT name,sex FROM tb_students GROUP BY name,sex;
将SELECT查询的字段都放到GROUP BY语句后,这样SQL语句是正确的,但是这样的查询结果意义不大。
(2)GROUP BY 与 GROUP_CONCAT()
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
【例 2】下面根据 tb_students 表中的 sex 字段进行分组查询,使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。SQL 语句和运行结果如下:
mysql> SELECT sex, GROUP_CONCAT(name) FROM tb_students GROUP BY sex;
由结果可以看到,查询结果分为两组,sex 字段值为“F”(女)的是一组,值为“M”(男)的是一组,且每组的学生姓名都显示出来了。
【例 3】下面根据 tb_students 表中的 age 和 sex 字段进行分组查询。SQL 语句和运行结果如下:
mysql> SELECT age,sex,GROUP_CONCAT(name) FROM tb_students GROUP BY age,sex;
上面实例在分组过程中,先按照 age 字段进行分组,当 age 字段值相等时,再把 age 字段值相等的记录按照 sex 字段进行分组。
多个字段分组查询时,会先按照第一个字段进行分组。如果第一个字段中有相同的值,MySQL 才会按照第二个字段进行分组。
如果第一个字段中的数据都是唯一的,那么 MySQL 将不再对第二个字段进行分组。
(3)GROUP BY 与聚合函数
在数据统计时,GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT(),SUM(),AVG(),MAX() 和 MIN()。
其中,COUNT() 用来统计记录的条数;
SUM() 用来计算字段值的总和;
AVG() 用来计算字段值的平均值;
MAX() 用来查询字段的最大值;
MIN() 用来查询字段的最小值。
【例 4】下面根据 tb_students 表的 sex 字段进行分组查询,使用 COUNT() 函数计算每一组的记录数。SQL 语句和运行结果如下:
mysql> SELECT sex,COUNT(sex) FROM tb_students GROUP BY sex;
结果显示,sex 字段值为“F”(女)的记录是一组,有 4 条记录;sex 字段值为“M”(男)的记录是一组,有 3 条记录。
(4)GROUP BY 与 WITH ROLLUP
WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。
【例 5】下面对【例2】的 sex 字段进行分组查询,并使用 WITH ROLLUP 显示记录的总和。
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students GROUP BY sex WITH ROLLUP;
查询结果显示,GROUP_CONCAT(name) 显示了每个分组的 name 字段值。同时,最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。
【例 6】下面对【例4】的 sex 字段进行分组查询,并使用 WITH ROLLUP 显示记录的总和。
mysql> SELECT sex,COUNT(sex) FROM tb_students GROUP BY sex WITH ROLLUP;
查询结果显示,COUNT(sex) 显示了每个分组的 sex的记录数(每个性别的人数)。同时,最后一条记录的 COUNT(sex) 字段的值刚好是上面分组 人数的总和。
12、HAVING:过滤分组
在 MySQL 中,可以使用 HAVING 关键字对分组后的数据进行过滤。
使用 HAVING 关键字的语法格式如下:
HAVING <查询条件>
HAVING 关键字和 WHERE 关键字都可以用来过滤数据,且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异:
- 一般情况下,WHERE 用于过滤数据行,而 HAVING 用于过滤分组。
- WHERE 查询条件中不可以使用聚合函数,而 HAVING 查询条件中可以使用聚合函数。
- WHERE 在数据分组前进行过滤,而 HAVING 在数据分组后进行过滤 。
- WHERE 针对数据库文件进行过滤,而 HAVING 针对查询结果进行过滤。也就是说,WHERE 根据数据表中的字段直接进行过滤,而 HAVING 是根据前面已经查询出的字段进行过滤。
- WHERE 查询条件中不可以使用字段别名,而 HAVING 查询条件中可以使用字段别名。
下面通过实例让大家更直观的了解 WHERE 和 HAVING 关键字的相同点和不同点。
【例 1】分别使用 HAVING 和 WHERE 关键字查询出 tb_students 表中身高大于 150 的学生姓名,性别和身高。SQL 语句和运行结果如下。
mysql> SELECT name,sex,height FROM tb_students HAVING height>150;
mysql> SELECT name,sex,height FROM tb_students WHERE height>150;
上述实例中,因为在 SELECT 关键字后已经查询出了 height 字段,所以 HAVING 和 WHERE 都可以使用。
但是如果 SELECT 关键字后没有查询出 height 字段,MySQL 就会报错。
【例 2】使用 HAVING 和 WHERE 关键字分别查询出 tb_students 表中身高大于 150 的学生姓名和性别(与例 1 相比,这次没有查询 height 字段)。SQL 语句和运行结果如下。
mysql> SELECT name,sex FROM tb_students WHERE height>150;
mysql> SELECT name,sex FROM tb_students HAVING height>150; ERROR 1054 (42S22): Unknown column 'height' in 'having clause'
由结果可以看出,如果 SELECT 关键字后没有查询出 HAVING 查询条件中使用的 height 字段,MySQL 会提示错误信息:“having子句”中的列“height”未知”。
【例 3】根据 height 字段对 tb_students 表中的数据进行分组,并使用 HAVING 和 WHERE 关键字分别查询出分组后平均身高大于 170 的学生姓名、性别和身高。SQL 语句和运行结果如下。
mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students GROUP BY sex,height HAVING AVG(height)>170;
mysql> SELECT GROUP_CONCAT(name),sex,height FROM tb_students WHERE AVG(height)>170 GROUP BY sex,height;
ERROR 1111 (HY000): Invalid use of group function
由结果可以看出,如果在 WHERE 查询条件中使用聚合函数,MySQL 会提示错误信息:无效使用组函数。
13、CROSS JOIN:交叉连接
前面所讲的查询语句都是针对一个表的,但是在关系型数据库中,表与表之间是有联系的,所以在实际应用中,经常使用多表查询。
多表查询就是同时查询两个或两个以上的表。
在 MySQL 中,多表查询主要有交叉连接、内连接和外连接。
交叉连接(CROSS JOIN)一般用来返回连接表的笛卡尔积。
本段的末尾介绍了笛卡尔积,不了解笛卡尔积的同学可以先阅读本段末尾部分,然后再回来学习交叉连接。
交叉连接的语法格式如下:
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
语法说明如下:
- 字段名:需要查询的字段名称。
- <表1><表2>:需要交叉连接的表名。
- WHERE 子句:用来设置交叉连接的查询条件。
注意:多个表交叉连接时,在 FROM 后连续使用 CROSS JOIN 或,
即可。以上两种语法的返回结果是相同的,但是第一种语法才是官方建议的标准写法。
当连接的表之间没有关系时,我们会省略掉 WHERE 子句,这时返回结果就是两个表的笛卡尔积,返回结果数量就是两个表的数据行相乘。
需要注意的是,如果每个表有 1000 行,那么返回结果的数量就有 1000×1000 = 1000000 行,数据量是非常巨大的。
交叉连接可以查询两个或两个以上的表,为了让读者更好的理解,下面先讲解两个表的交叉连接查询。
准备工作:在tb_students表中增加course_id字段,并且,给该字段赋上几个值。
mysql> ALTER TABLE tb_students ADD course_id INT;
mysql> UPDATE tb_students SET course_id=1 WHERE deptId=360101;
mysql> UPDATE tb_students SET course_id=2 WHERE deptId=360202;
mysql> UPDATE tb_students SET course_id=3 WHERE deptId=360201 OR deptId=360102;
【例 1】查询学生信息表和科目信息表,并得到一个笛卡尔积。
为了方便观察学生信息表和科目表交叉连接后的运行结果,我们先分别查询出这两个表的数据,再进行交叉连接查询。
1)查询 tb_students 表中的数据,SQL 语句和运行结果如下:
mysql> SELECT * FROM tb_students;
2)查询 tb_courses 表中的数据,SQL 语句和运行结果如下:
mysql> SELECT * FROM tb_courses;
3)使用 CROSS JOIN 查询出两张表中的笛卡尔积,SQL 语句和运行结果如下:
mysql> SELECT * FROM tb_courses CROSS JOIN tb_students;
由运行结果可以看出,tb_courses 和 tb_students 表交叉连接查询后,返回了 21 条记录。
可以想象,当表中的数据较多时,得到的运行结果会非常长,而且得到的运行结果也没太大的意义。
所以,通过交叉连接的方式进行多表查询的这种方法并不常用,我们应该尽量避免这种查询。
【例 2】查询 tb_courses 表中的 id 字段和 tb_students 表中的 course_id 字段相等的内容, SQL 语句和运行结果如下:
mysql> SELECT * FROM tb_courses CROSS JOIN tb_students WHERE tb_students.course_id = tb_courses.course_id;
如果在交叉连接时使用 WHERE 子句,MySQL 会先生成两个表的笛卡尔积,然后再选择满足 WHERE 条件的记录。
因此,表的数量较多时,交叉连接会非常非常慢。一般情况下不建议使用交叉连接。
在 MySQL 中,多表查询一般使用内连接和外连接,它们的效率要高于交叉连接。
笛卡尔积
笛卡尔积(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 集合的元素个数。
多表查询遵循的算法就是以上提到的笛卡尔积,表与表之间的连接可以看成是在做乘法运算。
在实际应用中,应避免使用笛卡尔积,因为笛卡尔积中容易存在大量的不合理数据,简单来说就是容易导致查询结果重复、混乱。
14、INNER JOIN:内连接
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行。
内连接使用 INNER JOIN 关键字连接两张表,并使用 ON 子句来设置连接条件。如果没有连接条件,INNER JOIN 和 CROSS JOIN 在语法上是等同的,两者可以互换。
内连接的语法格式如下:
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
语法说明如下。
- 字段名:需要查询的字段名称。
- <表1><表2>:需要内连接的表名。
- INNER JOIN :内连接中可以省略 INNER 关键字,只用关键字 JOIN。
- ON 子句:用来设置内连接的连接条件。
INNER JOIN 也可以使用 WHERE 子句指定连接条件,但是 INNER JOIN ... ON 语法是官方的标准写法,而且 WHERE 子句在某些时候会影响查询的性能。
多个表内连接时,在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
内连接可以查询两个或两个以上的表。为了让大家更好的理解,暂时只讲解两个表的连接查询。
【例 1】在 tb_students 表和 tb_courses 表之间,使用内连接查询学生姓名和相对应的课程名称,SQL 语句和运行结果如下。
mysql> SELECT s.name,c.course_name FROM tb_students s INNER JOIN tb_courses c ON s.course_id = c.course_id;
在这里的查询语句中,两个表之间的关系通过 INNER JOIN 指定,连接的条件使用 ON 子句给出。
注意:当对多个表进行查询时,要在 SELECT 语句后面指定字段是来源于哪一张表。
因此,在多表查询时,SELECT 语句后面的写法是表名.列名
。
另外,如果表名非常长的话,也可以给表设置别名,这样就可以直接在 SELECT 语句后面写上表的别名.列名
。
15、LEFT/RIGHT JOIN:外连接
内连接的查询结果都是符合连接条件的记录,而外连接会先将连接的表分为基表和参考表,再以基表为依据返回满足和不满足条件的记录。
外连接可以分为左外连接和右外连接,下面根据实例分别介绍左外连接和右外连接。
左连接
左外连接又称为左连接,使用 LEFT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
左连接的语法格式如下:
SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>
语法说明如下。
- 字段名:需要查询的字段名称。
- <表1><表2>:需要左连接的表名。
- LEFT OUTER JOIN:左连接中可以省略 OUTER 关键字,只使用关键字 LEFT JOIN。
- ON 子句:用来设置左连接的连接条件,不能省略。
上述语法中,“表1”为基表,“表2”为参考表。左连接查询时,可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。
如果“表1”的某行在“表2”中没有匹配行,那么在返回结果中,“表2”的字段值均为空值(NULL)。
【例 1】在 tb_students 表和 tb_courses 表中查询所有学生姓名和相对应的课程名称,包括没有课程的学生,SQL 语句和运行结果如下。
mysql> SELECT s.name,c.course_name FROM tb_students s LEFT OUTER JOIN tb_courses c ON s.course_id=c.course_id;
可以看到,运行结果显示了 7 条记录。
如果出现某个学生没有对应课程,那是因为对应的 tb_courses 表中没有该学生的课程信息,所以该条记录只取出了 tb_students 表中相应的值,而从 tb_courses 表中取出的值为 NULL。
右连接
右外连接又称为右连接,右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表,并使用 ON 子句来设置连接条件。
右连接的语法格式如下:
SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>
语法说明如下。
- 字段名:需要查询的字段名称。
- <表1><表2>:需要右连接的表名。
- RIGHT OUTER JOIN:右连接中可以省略 OUTER 关键字,只使用关键字 RIGHT JOIN。
- ON 子句:用来设置右连接的连接条件,不能省略。
与左连接相反,右连接以“表2”为基表,“表1”为参考表。右连接查询时,可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。
如果“表2”的某行在“表1”中没有匹配行,那么在返回结果中,“表1”的字段值均为空值(NULL)。
【例 2】在 tb_students 表和 tb_courses 表中查询所有课程,包括没有学生的课程,SQL 语句和运行结果如下。
mysql> SELECT s.name,c.course_name FROM tb_students s RIGHT OUTER JOIN tb_courses c ON s.course_id=c.course_id;
可以看到,结果显示了 7 条记录。
如果某门课程没有对应的学生,是因为对应的 tb_students 表中并没有该学生的信息,所以该条记录只取出了 tb_courses 表中相应的值,而从 tb_students 表中取出的值为 NULL。
多个表左/右连接时,在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。
使用外连接查询时,一定要分清需要查询的结果,是需要显示左表的全部记录还是右表的全部记录,然后选择相应的左连接和右连接。
16、子查询
通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。
子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用,而且可以进行多层嵌套。
在实际开发时,子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下:
WHERE <表达式> <操作符> (子查询)
其中,操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
(1)IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回值正好相反。
【例 1】使用子查询在 tb_students 表和 tb_courses 表中查询学习 Java 课程的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE course_id IN (SELECT course_id FROM tb_courses WHERE course_name = 'Java');
结果显示,学习 Java 课程的有 zhangsan和 zhaoliu%。
上述查询过程也可以分为以下 2 步执行,实现效果是相同的。
1)首先单独执行内查询,查询出 tb_course 表中课程为 Java 的 id,SQL 语句和运行结果如下。
mysql> SELECT course_id FROM tb_courses WHERE course_name = 'Java';
可以看到,符合条件的course_id 字段的值为 3。
2)然后执行外层查询,在 tb_students 表中查询 course_id 等于 3 的学生姓名。SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE course_id IN (3);
习惯上,外层的 SELECT 查询称为父查询,圆括号中嵌入的查询称为子查询(子查询必须放在圆括号内)。
MySQL 在处理上例的 SELECT 语句时,执行流程为:先执行子查询,再执行父查询。
【例 2】与例 1 类似,在 SELECT 语句中使用 NOT IN 关键字,查询没有学习 Java 课程的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE course_id NOT IN (SELECT course_id FROM tb_courses WHERE course_name = 'Java');
可以看出,运行结果与例 1 刚好相反,没有学习 Java 课程的学生。
【例 3】使用=
运算符,来解决例1问题,在 tb_courses 表和 tb_students 表中查询出所有学习 Java课程的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE course_id = (SELECT course_id FROM tb_courses WHERE course_name = 'Java');
结果显示,学习 Java 课程的有 zhangsan和 zhaoliu%。结果与例1一样。
【例 4】使用<>
运算符,来解决例2问题,在 tb_courses 表和 tb_students 表中查询出没有学习 Java课程的学生姓名,SQL 语句和运行结果如下。
mysql> SELECT name FROM tb_students WHERE course_id <> (SELECT course_id FROM tb_courses WHERE course_name = 'Java');
可以看出,运行结果与例 3 刚好相反,没有学习 Java课程的学生。结果与例2一样。
(2)EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空(是否存在),若子查询的结果集不为空,返回 TRUE,否则返回 FALSE;若使用关键字 NOT,则返回的值正好相反。
【例 5】查询 tb_courses 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students 表中的记录,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students WHERE EXISTS(SELECT course_name FROM tb_courses WHERE id=1);
由结果可以看到,tb_course 表中存在 id=1 的记录,因此 EXISTS 表达式返回 TRUE,外层查询语句接收 TRUE 之后对表 tb_students 进行查询,返回所有的记录。
EXISTS 关键字可以和其它查询条件一起使用,条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。
【例 6】查询 tb_course 表中是否存在 id=1 的课程,如果存在,就查询出 tb_students 表中 age 字段小于19 的记录,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students WHERE age<19 AND EXISTS(SELECT course_name FROM tb_courses WHERE id=1);
结果显示,从 tb_students 表中查询出了一条记录,这条记录的 age 字段取值为 18。
内层查询语句从 tb_courses 表中查询到记录,返回 TRUE。
外层查询语句开始进行查询。根据查询条件,从 tb_students 表中查询 age 小于 19 的记录。
17、子查询注意事项
在完成较复杂的数据查询时,经常会使用到子查询,编写子查询语句时,要注意如下事项。
(1)子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置
在 SELECT 语句中,子查询可以被嵌套在 SELECT 语句的列、表和查询条件中,即 SELECT 子句,FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。
前面已经介绍了 WHERE 子句中嵌套子查询的使用方法,下面是子查询在 SELECT 子句和 FROM 子句中的使用语法。
嵌套在 SELECT 语句的 SELECT 子句中的子查询语法格式如下。
SELECT (子查询) FROM 表名;
提示:子查询结果为单行单列,但不必指定列别名。
嵌套在 SELECT 语句的 FROM 子句中的子查询语法格式如下。
SELECT * FROM (子查询) AS 表的别名;
注意:必须为表指定别名。一般返回多行多列数据记录,可以当作一张临时表。
(2)只出现在子查询中而没有出现在父查询中的表不能包含在输出列中
多层嵌套子查询的最终数据集只包含父查询(即最外层的查询)的 SELECT 子句中出现的字段,而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。
常见错误如下:
SELECT * FROM (SELECT * FROM result);
这个子查询语句产生语法错误的原因在于主查询语句的 FROM 子句是一个子查询语句,因此应该为子查询结果集指定别名。正确代码如下。
SELECT * FROM (SELECT * FROM result) AS Temp;
18、怎样将子查询修改为表连接
子查询如递归函数一样,有时侯能达到事半功倍的效果,但是其执行效率较低。
与表连接相比,子查询比较灵活,方便,形式多样,适合作为查询的筛选条件,而表连接更适合查看多表的数据。
一般情况下,子查询会产生笛卡儿积,表连接的效率要高于子查询。因此在编写 SQL 语句时应尽量使用连接查询。
我们在前面《16、子查询》部分介绍表连接(内连接和外连接等)都可以用子查询替换,但反过来却不一定,有的子查询不能用表连接来替换。
下面我们介绍哪些子查询的查询命令可以改写为表连接。
在检查那些倾向于编写成子查询的查询语句时,可以考虑将子查询替换为表连接,看看连接的效率是不是比子查询更好些。
同样,如果某条使用子查询的 SELECT 语句需要花费很长时间才能执行完毕,那么可以尝试把它改写为表连接,看看执行效果是否有所改善。
(1)改写用来查询匹配值的子查询
下面这条示例语句包含一个子查询,使用子查询在 tb_students 表和 tb_courses 表中查询学习 Java 课程的学生,SQL 语句和运行结果如下。
mysql> SELECT * FROM tb_students WHERE course_id IN (SELECT course_id FROM tb_courses WHERE course_name = 'Java');
在编写以上语句时,可以不使用子查询,而是把它转换为一个简单的连接:
mysql> SELECT tb_students.* FROM tb_students INNER JOIN tb_courses ON tb_students.course_id=tb_courses.course_id WHERE tb_courses.course_name = 'Java';
我们可以发现这些子查询语句都遵从这样一种形式:
SELECT * FROM table1 WHERE column1 IN (SELECT column2a FROM table2 WHERE column2b = value);
其中,column1 代表 table1 中的字段,column2a 和 column2b 代表 table2 表中的字段。这类查询都可以被转换为下面这种形式的连接查询:
SELECT table1. * FROM table1 INNER JOIN table2 ON table1. column1 = table. column2a WHERE table2. column2b = value;
在某些场合,子查询和关联查询可能会返回不同的结果。
比如,当 table2 包含 column2a 的多个实例时,就会发生这种情况。
这种形式的子查询只会为每个 column2a 值生成一个实例,而连接操作会为所有值生成实例,并且其输出会包含重复行。
如果想要防止这种重复记录出现,就要在编写连接查询语句时使用 SELECT DISTINCT,而不能使用 SELECT。
(2)改写用来查询非匹配(缺失)值的子查询
另一种常见的子查询语句类型是:把存在于某个表里,但在另一个表里并不存在的那些值查找出来。“哪些值不存在”有关的问题通常都可以用 LEFT JOIN 来解决。
准备工作:创建一个记录旷课同学的表tb_absence,并且添加一些数据。SQL 语句和运行结果如下。
mysql> CREATE TABLE tb_absence (id INT NOT NULL AUTO_INCREMENT, student_id INT NOT NULL, day DATE NOT NULL, PRIMARY KEY (id));
mysql> INSERT INTO tb_absence (student_id , day) VALUES(2, '2021-03-17');
mysql> INSERT INTO tb_absence (student_id , day) VALUES(3, '2021-04-08');
mysql> INSERT INTO tb_absence (student_id , day) VALUES(5, '2021-05-14');
mysql> INSERT INTO tb_absence (student_id , day) VALUES(2, '2021-05-14');
如下语句用来测试哪些学生没有出现在 tb_absence 表里(用于查找全勤学生):
mysql> SELECT * FROM tb_students WHERE id NOT IN (SELECT student_id FROM tb_absence);
以上查询语句可以使用 LEFT JOIN 来改写:
mysql> SELECT tb_students.* FROM tb_students LEFT JOIN tb_absence ON tb_students.id = tb_absence.student_id WHERE tb_absence.student_id IS NULL;
通常情况下,如果子查询语句符合如下所示的形式:
SELECT * FROM table1 WHERE column1 NOT IN ( SELECT column2 FROM table2) ;
那么可以把它改写为下面这样的连接查询:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2 WHERE table2.column2 IS NULL;
这里需要假设 table2.column2 被定义成了 NOT NULL 的。
与 LEFT JOIN 相比,子查询更加直观。
大部分人都可以毫无困难地理解“没被包含在...里面”的含义,因为它不是数据库编程技术带来的新概念。
而“左连接”有所不同,很难用自然语言直观地描述出它的含义。
19、REGEXP:正则表达式
正则表达式主要用来查询和替换符合某个模式(规则)的文本内容。
例如,从一个文件中提取电话号码,查找一篇文章中重复的单词、替换文章中的敏感语汇等,这些地方都可以使用正则表达式。
正则表达式强大且灵活,常用于非常复杂的查询。
MySQL 中,使用 REGEXP 关键字指定正则表达式的字符匹配模式,其基本语法格式如下:
属性名 REGEXP '匹配方式'
其中,“属性名”表示需要查询的字段名称;“匹配方式”表示以哪种方式来匹配查询。
“匹配方式”中有很多的模式匹配字符,它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。
选项 | 说明 | 例子 | 匹配值示例 |
---|---|---|---|
^ | 匹配文本的开始字符 | '^b' 匹配以字母 b 开头的字符串 | book、big、banana、bike |
$ | 匹配文本的结束字符 | 'st$' 匹配以 st 结尾的字符串 | test、resist、persist |
. | 匹配任何单个字符 | 'b.t' 匹配任何 b 和 t 之间有一个字符 | bit、bat、but、bite |
* | 匹配零个或多个在它前面的字符 | 'f*n' 匹配字符 n 前面有任意个字符 f | fn、fan、faan、abcn |
+ | 匹配前面的字符 1 次或多次 | 'ba+' 匹配以 b 开头,后面至少紧跟一个 a | ba、bay、bare、battle |
<字符串> | 匹配包含指定字符的文本 | 'fa' 匹配包含‘fa’的文本 | fan、afa、faad |
[字符集合] | 匹配字符集合中的任何一个字符 | '[xz]' 匹配 x 或者 z | dizzy、zebra、x-ray、extra |
[^] | 匹配不在括号中的任何字符 | '[^abc]' 匹配任何不包含 a、b 或 c 的字符串 | desk、fox、f8ke |
字符串{n,} | 匹配前面的字符串至少 n 次 | 'b{2}' 匹配 2 个或更多的 b | bbb、bbbb、bbbbbbb |
字符串{n,m} | 匹配前面的字符串至少 n 次, 至多 m 次 | 'b{2,4}' 匹配最少 2 个,最多 4 个 b | bbb、bbbb |
MySQL 中的正则表达式与 Java 语言、PHP 语言等编程语言中的正则表达式基本一致。
(1)查询以特定字符或字符串开头的记录
字符^
用来匹配以特定字符或字符串开头的记录。
【例 1】在 tb_students 表中,查询 name 字段以“w”开头的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP '^w';
【例 2】在 tb_students 表中,查询 name 字段以“zhao”开头的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP '^zhao';
(2)查询以特定字符或字符串结尾的记录
字符$
用来匹配以特定字符或字符串结尾的记录。
【例 3】在 tb_students 表中,查询 name 字段以“u”结尾的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'u$';
【例 4】在 tb_students 表中,查询 name 字段以“iu”结尾的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'iu$';
(3)替代字符串中的任意一个字符
字符.
用来替代字符串中的任意一个字符。
【例 5】在 tb_students 表中,查询 name 字段值包含“i”和“i”,且两个字母之间只有一个字母的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'i.i';
(4)匹配多个字符
字符*
和+
都可以匹配多个该符号之前的字符。不同的是,+
表示至少一个字符,而*
可以表示 0 个字符。
【例 6】在 tb_students 表中,查询 name 字段值包含字母“a”,且“a”后面出现字母“o”(0次、1次或多次)的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'ao*';
【例 7】在 tb_students 表中,查询 name 字段值包含字母“a”,且“o”后面至少出现“o”一次的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'ao+';
(5)匹配指定字符串
正则表达式可以匹配字符串。当表中的记录包含这个字符串时,就可以将该记录查询出来。
指定多个字符串时,需要用|
隔开。只要匹配这些字符串中的任意一个即可。
【例 8】在 tb_students 表中,查询 name 字段值包含字符串“an”的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'an';
【例 9】在 tb_students 表中,查询 name 字段值包含字符串“an”或“en”的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP 'an|ao';
注意:字符串与|
之间不能有空格。因为,查询过程中,数据库系统会将空格也当作一个字符,这样就查询不出想要的结果。
(6)匹配指定字符串中的任意一个
使用方括号[ ]
可以将需要查询的字符组成一个字符集合。
只要记录中包含方括号中的任意字符,该记录就会被查询出来。
例如,通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。
【例 10】在 tb_students 表中,查询 name 字段值包含字母“i”或“u”的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP '[iu]';
从查询结果可以看到,所有返回记录的 name 字段值都包含字母 i 或 u,或者两个都有。
方括号[ ]
还可以指定集合的区间。
例如,“[a-z]”表示从 a~z 的所有字母;“[0-9]”表示从 0~9 的所有数字;“[a-z0-9]”表示包含所有的小写字母和数字;“[a-zA-Z]”表示匹配所有字符。
【例 11】在 tb_students 表中,查询 stuNo 字段值中包含数字3、4 或 5 的记录,指定集合区间,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE stuNo REGEXP '[3-5]';
(7)匹配指定字符以外的字符
[^字符集合]
用来匹配不在指定集合中的任何字符。
【例 12】在 tb_students 表中,查询 name 字段值包含字母 a~z 以外的字符的记录,SQL 语句和执行过程如下。
mysql> SELECT * FROM tb_students WHERE name REGEXP '[^a-z]';
(8)使用{n,}或者{n,m}来指定字符串连续出现的次数
字符串{n,}
表示字符串连续出现 n 次;字符串{n,m}
表示字符串连续出现至少 n 次,最多 m 次。
例如,a{2,} 表示字母 a 连续出现至少 2 次,也可以大于 2 次;a{2,4} 表示字母 a 连续出现最少 2 次,最多不能超过 4 次。
【例 13】在 tb_students 表中,查询 name 字段值出现字母‘a’ 至少 2 次的记录,SQL 语句如下:
mysql> SELECT * FROM tb_students WHERE name REGEXP 'a{2,}';
【例 14】在 tb_students 表中,查询 name 字段值出现字符串“a” 最少 1 次,最多 3 次的记录,SQL 语句如下:
mysql> SELECT * FROM tb_students WHERE name REGEXP 'a{1,3}';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库