目录
1. 数据库基础
数据库(database)保存有组织的数据的容器(通常是一个文件或一组文件)。
确切地说,数据库软件应称为DBMS(数据库管理系统)。数据库是通过DBMS创建和操纵的容器。数据库可以是保存在硬设备上的文件,但也可以不是。在很大程度上说,数据库究竟是文件还是别的什么东西并不重要,因为你并不直接访问数据库;你使用的是DBMS,它替你访问数据库。
表(table)某种特定类型数据的结构化清单。
模式(schema)关于数据库和表的布局及特性的信息。
表具有一些特性,这些特性定义了数据在表中如何存储,如可以存储什么样的数据,数据如何分解,各部分信息如何命名,等等。描述表的这组信息就是所谓的模式,模式可以用来描述数据库中特定的表以及整个数据库(和其中表的关系)。
列(column)表中的一个字段。所有表都是由一个或多个列组成的。
数据类型(datatype)所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。
行(row)表中的一个记录。
主键(primary key)一列(或一组列),其值能够唯一区分表中每个行。
表中的任何列都可以作为主键,只要它满足以下条件:1.任意两行都不具有相同的主键值;2.每个行都必须具有一个主键值(主键列不允许NULL值)。
主键通常定义在表的一列上,但这并不是必需的,也可以一起使用多个列作为主键。在使用多列作为主键时,上述条件必须应用到构成主键的所有列,所有列值的组合必须是唯一的(但单个列的值可以不唯一)。
SQL是结构化查询语言(Stuctured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。
2. Mysql 简介
数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS(数据库管理系统)完成的。
MySQL是一种DBMS,即它是一种数据库软件。
MySQL已经存在很久了,它在世界范围内得到了广泛的安装和使用。为什么有那么多的公司和开发人员使用MySQL?以下列出其原因。
- 成本—MySQL是开放源代码的,一般可以免费使用(甚至可以免费修改)。
- 性能—MySQL执行很快(非常快)。
- 可信赖——某些非常重要和声望很高的公司、站点使用MySQL,这些公司和站点都用MySQL来处理自己的重要数据。
- 简单——MySQL很容易安装和使用。
客户机-服务器软件
DBMS可分为两类:一类为基于共享文件系统的DBMS,另一类为基于客户机-服务器的DBMS。前者(包括诸如Microsoft Access和FileMaker)用于桌面用途,通常不用于高端或更关键的应用。
MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机-服务器的数据库。客户机-服务器应用分为两个不同的部分。服务器部分是负责所有数据访问和处理的一个软件。这个软件运行在称为数据库服务器的计算机上。
与数据文件打交道的只有服务器软件。关于数据、数据添加、删除和数据更新的所有请求都由服务器软件完成。这些请求或更改来自运行客户机软件的计算机。客户机是与用户打交道的软件。例如,如果你请求一个按字母顺序列出的产品表,则客户机软件通过网络提交该请求给服务器软件。服务器软件处理这个请求,根据需要过滤、丢弃和排序数据;然后把结果送回到你的客户机软件。
所有这些活动对用户都是透明的。数据存储在别的地方,或者数据库服务器为你完成这个处理这一事实是隐藏的。你不需要直接访问数据文件。事实上,多数网络的建立使用户不具有对数据的访问权,甚至不具有对存储数据的驱动器的访问权。
这样的意义何在?因为为了使用MySQL,你需要访问运行MySQL服务器软件的计算机和发布命令到MySQL的客户机软件的计算机。
- 服务器软件为MySQL DBMS。你可以在本地安装的副本上运行,也可以连接到运行在你具有访问权的远程服务器上的一个副本。
- 客户机可以是MySQL提供的工具、脚本语言(如Perl)、Web应用开发语言(如ASP、ColdFusion、JSP和PHP)、程序设计语言(如C、C++、Java)等。
3. 使用mysql
3.1 了解数据库和表
数据库、表、列、用户、权限等的信息被存储在数据库和表中(MySQL使用MySQL来存储这些信息)。不过,内部的表一般不直接访问。可用MySQL的SHOW命令来显示这些信息(MySQL从内部表中提取这些信息)。
show databases;
返回可用数据库的一个列表
show tables;
返回当前选择的数据库内可用表的列表
show columns from customers;
SHOW COLUMNS要求给出一个表名(这个例子中的FROM分析|customers),它对每个字段返回一行,行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如字段cust_id的auto_increment)。
describe customers;
DESCRIBE 语句MySQL支持用DESCRIBE作为SHOWCOLUMNS FROM的一种快捷方式。换句话说,DESCRIBE customers;是SHOW COLUMNS FROM cuStomers;的一种快捷方式。
什么是自动增量?某些表列需要唯一值。例如,订单编号、雇员ID或(如上面例子中所示的)顾客ID.在每个行添加到表中时,MySQL可以自动地为每个行分配下一个可用编号,不用在添加一行时手动分配唯一值(这样做必须记住最后一次使用的值)。这个功能就是所谓的自动增量。如果需要它,则必须在用CREATE语句创建表时把它作为表定义的组成部分。
// show databases; 返回可用数据库的一个列表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_demo |
| db_demo2 |
| db_demo3 |
| db_demo4 |
| db_models |
| de_demo |
| fruit_manage |
| migrate_demo |
| mybatis_test |
| mysql |
| performance_schema |
| spring_cache |
| ssm_crud |
| test |
| vueblog2 |
+--------------------+
16 rows in set (0.00 sec)
mysql> use test;
Database changed
// show tables; 返回当前选择的数据库内可用表的列表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| t_customer |
| user |
| vendors |
+----------------+
8 rows in set (0.00 sec)
//SHOW COLUMNS要求给出一个表名(这个例子中的FROM分析|customers),它对每个字段返回一行,
//行中包含字段名、数据类型、是否允许NULL、键信息、默认值
//以及其他信息(如字段cust_id的auto_increment)。
mysql> show columns from customers;
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
mysql> describe customers;
+--------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+---------+----------------+
| cust_id | int(11) | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
所支持的其他SHOW语句还有:
- SHOW STATUS,用于显示广泛的服务器状态信息;
- SHOW CREATE DATABASE和SHOW CREATE TABLE,分别用来显示创建特定数据库或表的MySQL语句;
- SHOW GRANTS,用来显示授予用户(所有用户或特定用户)的安全权限;
- SHOW ERRORS和SHOW WARNINGS,用来显示服务器错误或警告消息。
mysql> show create table customers;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3.2 检索数据
mysql> describe products;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id | char(10) | NO | PRI | NULL | |
| vend_id | int(11) | NO | MUL | NULL | |
| prod_name | char(255) | NO | | NULL | |
| prod_price | decimal(8,2) | NO | | NULL | |
| prod_desc | text | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
// 检索单个列
//如果没有明确排序查询结果,则返回的数据的顺序没有特殊意义。
//返回数据的顺序可能是数据被添加到表中的顺序,也可能不是。只要返回相同数目的行,就是正常的。
mysql> select prod_name from products;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
//检索多个列
mysql> select prod_id,prod_name,prod_price from products;
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| ANV01 | .5 ton anvil | 5.99 |
| ANV02 | 1 ton anvil | 9.99 |
| ANV03 | 2 ton anvil | 14.99 |
| DTNTR | Detonator | 13.00 |
| FB | Bird seed | 10.00 |
| FC | Carrots | 2.50 |
| FU1 | Fuses | 3.42 |
| JP1000 | JetPack 1000 | 35.00 |
| JP2000 | JetPack 2000 | 55.00 |
| OL1 | Oil can | 8.99 |
| SAFE | Safe | 50.00 |
| SLING | Sling | 4.49 |
| TNT1 | TNT (1 stick) | 2.50 |
| TNT2 | TNT (5 sticks) | 10.00 |
+---------+----------------+------------+
14 rows in set (0.00 sec)
//检索所有列
mysql> select * from products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |
| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |
| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |
| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |
| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |
| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |
| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |
| OL1 | 1002 | Oil can | 8.99 | Oil can, red |
| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |
| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)
//检索不同的行
//使用DISTINCT关键字 指示MySQL只返回不同的值。
mysql> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in set (0.00 sec)
//限制结果
//SELECT语句返回所有匹配的行,它们可能是指定表中的每个行。
//为了返回第一行或前几行,可使用LIMIT子句。
mysql> select prod_name from products limit 5;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
+--------------+
5 rows in set (0.00 sec)
//指定要检索的开始行和行数
mysql> select prod_name from products limit 5,5;
+--------------+
| prod_name |
+--------------+
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
+--------------+
5 rows in set (0.00 sec)
//使用完全限定的表名
mysql> select prod_name from test.products limit 5,5;
+--------------+
| prod_name |
+--------------+
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
+--------------+
5 rows in set (0.00 sec)
3.3 排序检索数据
mysql> select prod_name from products;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
//指示MySQL对prod_name列以字母顺序排序数据
mysql> select prod_name from products order by prod_name;
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Bird seed |
| Carrots |
| Detonator |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
| Safe |
| Sling |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)
其实,检索出的数据并不是以纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。这可以是数据最初添加到表中的顺序。但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响。因此,如果不明确控制的话,不能(也不应该)依赖该排序顺序。关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字,据此对输出进行排序。
//按多个列排序数据
//仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序
mysql> select prod_id,prod_name,prod_price from products order by prod_price,prod_name;
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| FC | Carrots | 2.50 |
| TNT1 | TNT (1 stick) | 2.50 |
| FU1 | Fuses | 3.42 |
| SLING | Sling | 4.49 |
| ANV01 | .5 ton anvil | 5.99 |
| OL1 | Oil can | 8.99 |
| ANV02 | 1 ton anvil | 9.99 |
| FB | Bird seed | 10.00 |
| TNT2 | TNT (5 sticks) | 10.00 |
| DTNTR | Detonator | 13.00 |
| ANV03 | 2 ton anvil | 14.99 |
| JP1000 | JetPack 1000 | 35.00 |
| SAFE | Safe | 50.00 |
| JP2000 | JetPack 2000 | 55.00 |
+---------+----------------+------------+
14 rows in set (0.00 sec)
//指定排序方向 desc 降序排序 默认为升序排序ASC(ascending)
mysql> select prod_id,prod_name,prod_price from products order by prod_price desc;
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| JP2000 | JetPack 2000 | 55.00 |
| SAFE | Safe | 50.00 |
| JP1000 | JetPack 1000 | 35.00 |
| ANV03 | 2 ton anvil | 14.99 |
| DTNTR | Detonator | 13.00 |
| TNT2 | TNT (5 sticks) | 10.00 |
| FB | Bird seed | 10.00 |
| ANV02 | 1 ton anvil | 9.99 |
| OL1 | Oil can | 8.99 |
| ANV01 | .5 ton anvil | 5.99 |
| SLING | Sling | 4.49 |
| FU1 | Fuses | 3.42 |
| FC | Carrots | 2.50 |
| TNT1 | TNT (1 stick) | 2.50 |
+---------+----------------+------------+
14 rows in set (0.00 sec)
//多个列排序 以降序排序产品(最贵的在最前面),然后再对产品名排序
mysql> select prod_id,prod_name,prod_price from products order by prod_price desc,prod_name;
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| JP2000 | JetPack 2000 | 55.00 |
| SAFE | Safe | 50.00 |
| JP1000 | JetPack 1000 | 35.00 |
| ANV03 | 2 ton anvil | 14.99 |
| DTNTR | Detonator | 13.00 |
| FB | Bird seed | 10.00 |
| TNT2 | TNT (5 sticks) | 10.00 |
| ANV02 | 1 ton anvil | 9.99 |
| OL1 | Oil can | 8.99 |
| ANV01 | .5 ton anvil | 5.99 |
| SLING | Sling | 4.49 |
| FU1 | Fuses | 3.42 |
| FC | Carrots | 2.50 |
| TNT1 | TNT (1 stick) | 2.50 |
+---------+----------------+------------+
14 rows in set (0.00 sec)
//找出最贵物品的值
mysql> select prod_id,prod_name,prod_price from products order by prod_price desc limit 1;
+---------+--------------+------------+
| prod_id | prod_name | prod_price |
+---------+--------------+------------+
| JP2000 | JetPack 2000 | 55.00 |
+---------+--------------+------------+
1 row in set (0.00 sec)
在多个列上降序排序 如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。
区分大小写和排序顺序 在对文本性的数据进行排序时,A与a相同吗?a位于B之前还是位于Z之后?这些问题不是理论问题,其答案取决于数据库如何设置。
在字典(dictionary)排序顺序中,A被视为与a相同,这是MySQL(和大多数数据库管理系统)的默认行为。但是,许多数据库管理员能够在需要时改变这种行为(如果你的数据库包含大量外语字符,可能必须这样做)。
这里,关键的问题是,如果确实需要改变这种排序顺序,用简单的ORDERBY子句做不到。你必须请求数据库管理员的帮助。
ORDER BY子句的位置在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
3.4 过滤数据(Where)
mysql> select prod_name,prod_price from products where prod_price=2.5;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| Carrots | 2.50 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
2 rows in set (0.00 sec)
//检查单个值 MySQL在执行匹配值默认不区分大小写,所以fuses与Fuses匹配
mysql> select prod_name,prod_price from products where prod_name='fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses | 3.42 |
+-----------+------------+
1 row in set (0.00 sec)
mysql> select prod_name,prod_price from products where prod_price<10;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
7 rows in set (0.00 sec)
mysql> select prod_name,prod_price from products where prod_price<=10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.00 sec)
// <>等价与!=
mysql> select prod_name,prod_price from products where prod_price<>10;
+---------------+------------+
| prod_name | prod_price |
+---------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| Detonator | 13.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
+---------------+------------+
12 rows in set (0.00 sec)
//范围值查询 BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。
mysql> select prod_name,prod_price from products where prod_price between 5 and 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| Bird seed | 10.00 |
| Oil can | 8.99 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
5 rows in set (0.00 sec)
//空值检查 null 无值,它与字段包含0,空字符串或仅仅包含空格不同
mysql> select prod_name,prod_price from products where prod_price is null;
Empty set (0.00 sec)
mysql> select cust_id from customers where cust_email is null;
+---------+
| cust_id |
+---------+
| 10002 |
| 10005 |
+---------+
2 rows in set (0.00 sec)
mysql> select * from customers;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
5 rows in set (0.00 sec)
注:
1.SQL过滤与应用过滤
数据也可以在应用层过滤。为此目的,SQL的SELECT语句为客户机应用检索出超过实际所需的数据,然后客户机代码对返回数据进行循环,以提取出需要的行。
通常,这种实现并不令人满意。因此,对数据库进行了优化,以便快速有效地对数据进行过滤。让客户机应用(或开发语言)处理数据库的工作将会极大地影响应用的性能,并且使所创建的应用完全不具备可伸缩性,此外,如果在客户机上过滤数据,服务器不得不通过网络发送多余的数据,这将导致网络带宽的浪费。
2.WHERE子句的位置
在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
3.何时使用引号
仔细观察上述WHERE子句中使用的条件,会看到有的值括在单引号内(如前面使用的‘fuses),而有的值未括起来。单引号用来限定字符串。如果将值与串类型的列进行比较,则需要限定引号。用来与数值列进行比较的值不用引号。
4.NULL与不匹配
在通过过滤选择出不具有特定值的行时,你可能希望返回具有NULL值的行。但是,不行。因为未知具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时不返回它们。
因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行。
3.5 数据过滤(and or in not)
mysql> select * from products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01 | 1001 | .5 ton anvil | 5.99 | .5 ton anvil, black, complete with handy hook |
| ANV02 | 1001 | 1 ton anvil | 9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03 | 1001 | 2 ton anvil | 14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR | 1003 | Detonator | 13.00 | Detonator (plunger powered), fuses not included |
| FB | 1003 | Bird seed | 10.00 | Large bag (suitable for road runners) |
| FC | 1003 | Carrots | 2.50 | Carrots (rabbit hunting season only) |
| FU1 | 1002 | Fuses | 3.42 | 1 dozen, extra long |
| JP1000 | 1005 | JetPack 1000 | 35.00 | JetPack 1000, intended for single use |
| JP2000 | 1005 | JetPack 2000 | 55.00 | JetPack 2000, multi-use |
| OL1 | 1002 | Oil can | 8.99 | Oil can, red |
| SAFE | 1003 | Safe | 50.00 | Safe with combination lock |
| SLING | 1003 | Sling | 4.49 | Sling, one size fits all |
| TNT1 | 1003 | TNT (1 stick) | 2.50 | TNT, red, single stick |
| TNT2 | 1003 | TNT (5 sticks) | 10.00 | TNT, red, pack of 10 sticks |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)
//and 操作符 还可以添加多个过滤条件,每添加一条就要使用一个AND。
mysql> select prod_id,prod_name,prod_price from products where vend_id =1003 and prod_price<=10;
+---------+----------------+------------+
| prod_id | prod_name | prod_price |
+---------+----------------+------------+
| FB | Bird seed | 10.00 |
| FC | Carrots | 2.50 |
| SLING | Sling | 4.49 |
| TNT1 | TNT (1 stick) | 2.50 |
| TNT2 | TNT (5 sticks) | 10.00 |
+---------+----------------+------------+
5 rows in set (0.00 sec)
//or 操作符 指示mysql检索匹配任一条件的行
mysql> select prod_name,prod_price from products where vend_id=1002 or vend_id=1003;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.00 sec)
//and 与 or 的计算次序 where可包含任意数目的and与or操作符,允许两者结合以进行复杂和高级的过滤
//SQL在处理0R操作符前,优先处理AND操作符。
//由于AND在计算次序中优先级更高,操作符被错误地组合了。
//此问题的解决方法是使用圆括号明确地分组相应的操作符。
mysql> select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price>=10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
6 rows in set (0.00 sec)
//圆括号具有较AND或0R操作符高的计算次序
mysql> select prod_name,prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price>=10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
4 rows in set (0.00 sec)
//in 操作符 in操作符可以完成与or相同的功能
mysql> select prod_name,prod_price from products where vend_id in (1002,1003) order by prod_name;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Detonator | 13.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.00 sec)
//not where子句中用来否定后跟条件的关键字
mysql> select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
5 rows in set (0.00 sec)
注:
1.在WHERE子句中使用圆括号
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
2.为什么要使用IN操作符?其优点具体如下。
- 在使用长的合法选项清单时,IN操作符的语法更清楚且更直观。
- 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)。
- IN操作符一般比OR操作符清单执行更快。
- IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句。
IN WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当。
3.MySQL中的NOT
MySQL 支持使用NOT对IN、BETWEEN和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
3.6 用通配符进行过滤
like操作符
前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个还是多个值,测试大于还是小于已知值,或者检查某个范围的值,共同点是过滤中使用的值都是已知的。但是,这种过滤方法并不是任何时候都好用。例如,怎样搜索产品名中包含文本anvil的所有产品?用简单的比较操作符肯定不行,必须使用通配符。利用通配符可创建比较特定数据的搜索模式。在这个例子中,如果你想找出名称包含anvil的所有产品,可构造一个通配符搜索模式,找出产品名中任何位置出现anvil的产品。
通配符(wildcard)用来匹配值的一部分的特殊字符。% 通配符 _ 通配符
搜索模式(search pattern)0由字面值、通配符或两者组合构成的搜索条件。
通配符本身实际是SQL的WHERE子句中有特殊含义的字符,SOL支持几种通配符。
为在搜索子句中使用通配符,必须使用LIKE操作符。LIKE指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
//使用搜索模式”jet%'。在执行这条子句时,将检索任意以jet起头的词。
//%告诉MySQL接受jet之后的任意字符,不管它有多少字符
mysql> select prod_id,prod_name from products where prod_name like 'jet%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
2 rows in set (0.00 sec)
//搜索模式%anvi1%'表示匹配任何位置包含文本anvil的值,而不论它之前或之后出现什么字符。
mysql> select prod_id,prod_name from products where prod_name like '%anvil%';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
3 rows in set (0.00 sec)
//通配符也可以出现在搜索模式的中间
//找出以s起头以e结尾的所有产品
mysql> select prod_id,prod_name from products where prod_name like 's%e';
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| SAFE | Safe |
+---------+-----------+
1 row in set (0.00 sec)
// _ 只匹配单个字符而不是多个字符
mysql> select prod_id,prod_name from products where prod_name like '_ ton anvil';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
// % 表示任意字符出现任意次数
mysql> select prod_id,prod_name from products where prod_name like '% ton anvil';
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
3 rows in set (0.00 sec)
注:
1.谓词
操作符何时不是操作符?答案是在它作为谓词(predi-cate)时。从技术上说,LIKE是谓词而不是操作符。
2.注意尾空格
尾空格可能会干扰通配符匹配。例如,在保存词anvil时,如果它后面有一个或多个空格,则子句WHERE prod_name LIKE '%anvi1' 将不会匹配它们,因为在最后的1后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。
3.注意NULL
虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL。即使是WHERE prod_name LIKE%’也不能匹配用值NULL作为产品名的行。
4.使用通配符的技巧
- MySQL的通配符很有用。但这种功能是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。