MySQL的初次见面礼基础实战篇
【版权申明】 http://blog.csdn.net/javazejian/article/details/61614366
出自【zejian的博客】
关联文章:
MySQL的初次见面礼基础实战篇
MySQL的进阶实战篇
本篇将以最简单的方式呈现并演绎MySQL数据库的必知必会的知识点，通过本篇博文您将会对mysql从起点到终点的较为全面的认识，关于mysql的知识，将分两篇来记录，即MySQL的基础实战篇和MySQL的进阶实战篇，以下是本篇的主要知识点。
基础实战篇
- 环境准备
- 数据库与表的创建以及SQL增删改查
- 数据库和表的创建与删除
- 表的创建与删除
- 外键的约束
- 数据类型
- 数字数据类型
- 日期和时间类型
- 字符串类型
- 表的增删改查操作
- 增删改
- 变化多端的查询
- 过滤条件 - WHERE
- NULL 操作符
- LIKE 操作符
- BETWEEN 操作符
- IN 操作符
- 多条件组合AND 和 OR 操作符
- 对查询结果排序 ORDER BY
- 获取指定行数的数据-LIMIT
- 数据分组-GROUP BY 与 过滤分组 - HAVING
- 灵活使用计算字段
- 可能需要知道的常用的数据处理函数
- mysql中的常用聚合函数
- 表关联的那点事儿
- 内关联
- 外关联
- 左外连接
- 右外连接
- 复杂关联查询
- 子查询
- 基本子查询
- 多返回值的子查询
- 相关子查询
- 组合查询
- 表维护与改造
- 表的改造
- 表的复制
基础实战篇
环境准备
本篇不会讲解如何去安装mysql，也没有这样的必要，网上这方面的资料随处可见，因此这里的环境搭建主要的我们可能在后面会使用到的数据库和表结构，本篇将采用电商网站最常见的四张表,如下：
+-------------------+
| Tables_in_webshop |
+-------------------+
| items |
| orderdetail |
| orders |
| user |
+-------------------+
商品表结构items(简化版)：
+------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | | NULL | | | price | float(10,1) | NO | | NULL | | | detail | text | YES | | NULL | | | pic | varchar(64) | YES | | NULL | | | createtime | datetime | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+
订单详情表结构orderdetail(简化版):
+-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | orders_id | int(11) | NO | MUL | NULL | | | items_id | int(11) | NO | MUL | NULL | | | items_num | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+
订单表结构orders(简化版)：
+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | MUL | NULL | | | number | varchar(32) | NO | | NULL | | | createtime | datetime | NO | | NULL | | | note | varchar(100) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+
用户表结构user(简化版):
+----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(32) | NO | | NULL | | | birthday | date | YES | | NULL | | | sex | char(1) | YES | | NULL | | | address | varchar(256) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
它们间的关系如下图，即一个订单详情orderdetail可以有多个商品items，但只能属于一个订单orders，而一个订单orders也只能属于一个user用户，一个user用户可以拥有多个orders订单。
这里我们使用mysql提供的命令行窗口监视器对mysql数据库进行各项操作。
数据库与表的创建以及SQL增删改查
数据库和表的创建与删除
通过以下命令链接上mysql监听器：
mysql -u root -p ********(输入密码)
我们可以通过以下语句来创建数据库和删除数据库并查看当前有哪些数据库(大小不敏感)
#创建数据库 CREATE DATABASE 数据库名称 #删除数据库 DROP DATABASE 数据库名称 #查看当前所有数据库 SHOW DATABASES
先查看当前有哪些数据库：
mysql> show databases; <-----------查看当前数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
其中information_schema、performance_schema、mysql、sys 都是mysql自动创建的数据库，如下给出这几库的简单信息：
-
information_schema数据库又称为信息架构，数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
-
performance_schema数据库主要用于收集数据库服务器性能参数，以便优化mysql数据库性能。
-
mysql数据库是存储着已MySQL运行相关的基本信息等数据管理的数据库。
-
sys 数据库是mysql5.7增加的，通过这个库可以快速的了解系统的元数据信息
这个库可以方便DBA发现数据库的很多信息，提供解决性能瓶颈的信息。
而test数据库则是一个测试数据库可有可无。ok，就此打住，现在通过创建和删除一个名为debug的数据库来演示数据库的创建语句。
mysql> create database debug; <------创建数据库 Query OK, 1 row affected (0.01 sec) <------代表执行成功 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | debug |<------已被创建的数据库 | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec)
使用DROP DATABASE语句删除数据库：
mysql> drop database debug ; <------删除数据库 Query OK, 0 rows affected (0.02 sec) mysql> show databases; <------重新查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
表的创建与删除
表的操作语句如下：
#创建表 CREATE TABLE 表名 (字段名1 数据类型 [其他可选项], 字段名2 数据类型 [其他可选项], ...... ) #删除表 DROP TABLE 表名 #显示表的数据结构 DESC 表名 #查看数据库中所有的表 SHOW TABLES
创建表的最基本的3点是：
表的名称 表字段名称 每个字段的数据类型
现在利用上述的SQL操作语言，先创建一个名称webshop的数据库，并使用USE 关键字选择该数据库，然后创建前面 items 、orderdetail、orders、user 四张表，ok，先创建数据库，操作如下：
mysql> create database webshop; <------创建webshop数据库 Query OK, 1 row affected (0.00 sec) mysql> use webshop <------使用use关键字选择webshop数据库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
创建 items 表，语句如下:
CREATE TABLE items ( id INT(11) NOT NULL AUTO_INCREMENT, item_name VARCHAR(32) NOT NULL COMMENT '商品名称', price FLOAT(10,1) NOT NULL COMMENT '商品价格', detail TEXT COMMENT '商品详情', pic VARCHAR(64) DEFAULT NULL COMMENT '图片', createtime DATETIME NOT NULL COMMENT '生成日期', PRIMARY KEY (id) )ENGINE = INNODB DEFAULT CHARSET =UTF8 COMMENT '商品表'
其中id是唯一主键，使用关键字 PRIMARY KEY 进行指定，并且不能为空，因此使用 NOT NULL 标识非空，而 AUTO_INCREMENT 选项代表该id为自动增长从1开始。在其他列中如name中还使用到了 COMMENT 来标识name的含义。每个列中使用到诸如int(11)、varchar(32)、float(10,1)、text、datetime 等数据类型对每个字段的数据存储类型进行标明（关于数据类型后面会说明）。在表创建的结尾，使用 ENGINE=InnoDB 来说明该items表在mysql数据库中使用的引擎为InnoDB（mysql数据库中提供多种数据库引擎供选择，而InnoDB是具备事务功能的引擎，后面还能见到它，这里暂且打住），通过 CHARSET=utf8 指定该表的字符集，到此创建表的语句就完成了。接着创建其他3张表：
#user表创建语句 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` date DEFAULT NULL COMMENT '生日', `sex` char(1) DEFAULT NULL COMMENT '性别', `address` varchar(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8; #订单表orders创建语句 CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '下单用户id', `number` varchar(32) NOT NULL COMMENT '订单号', `createtime` datetime NOT NULL COMMENT '创建订单时间', `note` varchar(100) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; #订单详情表orderdetail创建语句 CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `orders_id` int(11) NOT NULL COMMENT '订单id', `items_id` int(11) NOT NULL COMMENT '商品id', `items_num` int(11) DEFAULT NULL COMMENT '商品购买数量', PRIMARY KEY (`id`), <--------创建外键约束-----------> CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
外键的约束
在订单详情表orderdetail表的创建语句中使用到如下语句：
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
其中orders_id 和 items_id 分别的订单表orders和商品表items的主键，像这种属于其他表主键又存在于orderdetail表中的字段，称之为orderdetail的外键字段，使用外键的好处是可以使得两张表存在关联，保证数据的一致性和实现一些级联操作；如每次购物时必须存在相对应的items_id商品数据才能创建订单详情的数据，因为没有商品也没有所谓的订单详情了，而每次可能会购买多种商品，而每种商品也将生成不同订单详情，而客户的购买行为属一次购买，因此订单详情汇聚成一个整体的订单（orders_id），也就是说一个订单详情只能属于一个订单，而一个订单可以拥有多个订单详情。在MySQL中，InnoDB引擎类型的表支持了外键约束，而外键的使用条件如下：
1.两个表必须使用InnoDB引擎
2.外键列必须建立了索引(关于索引后面分析，主键创建时会自动创建索引)，MySQL 4.1.2以后的版本在建立外键时会自动创建索引
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，但int和char则不可以；
外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …) REFERENCES tbl_name (index_col_name, …) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
实例对照：
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION <-----默认行为，可以不写
注意该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，CONSTRAINT symbol，指明了约束标识符，在SQL排错时可能有不错的表现，如果不指明CONSTRAINT symbol，MYSQL会自动生成一个名字。两表间的更新删除时数据的同步可以使用ON DELETE、ON UPDATE 来表明相互间删除和更新事件触发后的影响，可设参数以下参数，假设主表是orders，从表是orderdetail。
-
RESTRICT、NO ACTION(默认行为)
删除：从表数据记录不存在时，主表数据记录才可以删除，如当从表orderdetail的数据被删除后主表的orders的数据才能被删除，否则无法删除。删除从表数据，主表数据不变
更新：从表记录数据不存在时，主表数据才可以更新。当更新从表数据，主表数据不变 -
CASCADE（级联）
删除：删除主表数据时自动删除从表数据。删除从表数据，主表数据不变
更新：更新主表数据时自动更新从表数据。更新从表数据，主表数据不变
-
SET NULL
删除：删除主表数据时自动更新从表对于数据值为NULL。删除从表数据，主表数据不变
更新：更新主表数据时自动更新从表数据值为NULL。更新从表数据数据，主表不变
到此，4张表都创建完成，我们使用show tables 语句来查看数据库中的表：
mysql> show tables; +-------------------+ | Tables_in_webshop | +-------------------+ | items | | orderdetail | | orders | | orders-dely | | user | +-------------------+ 5 rows in set (0.01 sec)
其中orders-dely表是多余的，使用drop table 表名 语句将其删除：
mysql> drop table `orders-dely` ; <-------删除orders-dely表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; <------再次查看数据库中的表 +-------------------+ | Tables_in_webshop | +-------------------+ | items | | orderdetail | | orders | | user | +-------------------+ 4 rows in set (0.00 sec)
数据类型
MySQL使用所有标准的ANSI SQL数字数据类型，下面将列出常见的数据类型及其说明
数字数据类型
-
TINYINT - 一个非常小的整数，可以带符号。如果是有符号，它允许的范围是从-128到127。如果是无符号，允许的范围是从0到255，占1个字节。。
-
SMALLINT - 一个小的整数，可以带符号。如果有符号，允许范围为-32768至32767。如果无符号，允许的范围是从0到65535，占2个字节。
-
MEDIUMINT - 一个中等大小的整数，可以带符号。如果有符号，允许范围为-8388608至8388607。 如果无符号，允许的范围是从0到16777215，占3个字节。。
-
INT - 正常大小的整数，可以带符号。如果是有符号的，它允许的范围是从-2147483648到2147483647。如果是无符号，允许的范围是从0到4294967295，占4个字节。
-
BIGINT - 一个大的整数，可以带符号。如果有符号，允许范围为-9223372036854775808到9223372036854775807。如果无符号，允许的范围是从0到18446744073709551615. 占8个字节。
-
FLOAT(M,D) - 不能使用无符号的浮点数字。可以定义显示长度(M)和小数位数(D)。这不是必需的，并且默认为10,2。其中2是小数的位数，10是数字(包括小数)的总数。占4个字节。
-
DOUBLE(M,D) - 不能使用无符号的双精度浮点数。可以定义显示长度(M)和小数位数(D)。 这不是必需的，默认为16,4，其中4是小数的位数，占8个字节。
-
DECIMAL(M,D) - 非压缩浮点数不能是无符号的。在解包小数，每个小数对应于一个字节。定义显示长度(M)和小数(D)的数量是必需的。 NUMERIC是DECIMAL的同义词。
日期和时间类型
-
DATE - 以YYYY-MM-DD格式的日期，在1000-01-01和9999-12-31之间。 例如，1999年10月30日将被存储为1999-10-30。
-
DATETIME - 日期和时间组合以YYYY-MM-DD HH:MM:SS格式，在1000-01-01 00:00:00 到9999-12-31 23:59:59之间。例如，1999年10月30日下午3:30，会被存储为1999-10-30 15:30:00。
-
TIMESTAMP - 1970年1月1日午夜之间的时间戳，到2037的某个时候。这看起来像前面的DATETIME格式，无需只是数字之间的连字符; 1973年12月30日下午3点30分将被存储为19731230153000(YYYYMMDDHHMMSS)。
-
TIME - 存储时间在HH:MM:SS格式。
-
YEAR(M) - 以2位或4位数字格式来存储年份。如果长度指定为2(例如YEAR(2))，年份就可以为1970至2069(70〜69)。如果长度指定为4，年份范围是1901-2155，默认长度为4。
字符串类型
-
CHAR(M) - 固定长度的字符串是以长度为1到255之间个字符长度(例如：CHAR(5))，存储右空格填充到指定的长度。 限定长度不是必需的，它会默认为1。
-
VARCHAR(M) - 可变长度的字符串是以长度为1到255之间字符数(高版本的MySQL超过255); 例如： VARCHAR(25). 创建VARCHAR类型字段时，必须定义长度。
-
BLOB 或 TEXT - 字段的最大长度是65535个字符。 BLOB是“二进制大对象”，并用来存储大的二进制数据，如图像或其他类型的文件。定义为TEXT文本字段还持有大量的数据; 两者之间的区别是，排序和比较上存储的数据，BLOB大小写敏感，而TEXT字段不区分大小写。不用指定BLOB或TEXT的长度。
-
TINYBLOB 或 TINYTEXT - BLOB或TEXT列用255个字符的最大长度。不指定TINYBLOB或TINYTEXT的长度。
-
MEDIUMBLOB 或 MEDIUMTEXT - BLOB或TEXT列具有16777215字符的最大长度。不指定MEDIUMBLOB或MEDIUMTEXT的长度。
-
LONGBLOB 或 LONGTEXT - BLOB或TEXT列具有4294967295字符的最大长度。不指定LONGBLOB或LONGTEXT的长度。
-
ENUM - 枚举，这是一个奇特的术语列表。当定义一个ENUM，要创建它的值的列表，这些是必须用于选择的项(也可以是NULL)。例如，如果想要字段包含“A”或“B”或“C”，那么可以定义为ENUM为 ENUM(“A”，“B”，“C”)也只有这些值(或NULL)才能用来填充这个字段。
表的增删改查操作
增删改
有上述的表结构就可以对表进行增删改查的操作，其语句法结构如下：
#insert 插入操作 INSERT INTO 表名(列名1,列名2,...) VALUES (数据1,数据2...); #update 更新操作 UPDATE 表名 SET 列名1=值1,列名2=值2,...WHERE 条件表达式； #delete 删除操作 DELETE FROM 表名 WHERE 条件表达式 #select 查询操作 SELECT 列名1，列名2，... FROM 表名 [条件表达式]
接着使用上述语句对user进行增删改查的操作，首先查询一下user表有哪些用户数据，通过select 语句进行查询：
mysql> select * from user; <---- select查询语句 +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------+------------+------+--------------------+ 9 rows in set (0.00 sec)
其中 * 号代表查询出该表的所有字段，当然也向下面那样一个个字段列举出来：
select id, username, birthday, sex, address from user;
现在通过下面语句向user表插入一条数据：
insert into user (id, username, birthday, sex, address) values('3','新数据','1909-12-12','1','常年在外'); #当确定插入表中所有列时可以省略列名称 insert into user values('3','新数据','1909-12-12','1','常年在外');
结果如下：
#插入新数据 mysql> insert into user (id, username, birthday, sex, address) values('3','新数据','1909-12-12',1,'常年在外'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 新数据 | 1909-12-12 | 1 | 常年在外 <--------新插入的数据 | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------+------------+------+--------------------+ 10 rows in set (0.00 sec)
使用更新操作，更新ID为3的记录：
update user set username='大王让我来巡山',address='北京朝阳' where id=3;
执行过程如下：
mysql> update user set username='大王让我来巡山',address='北京朝阳' where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------------------+------------+------+--------------------+ 10 rows in set (0.01 sec)
显然id为3的数据已更新，接着使用delete语句删除id为3和33的记录，执行如下：
mysql> delete from user where id = 3 and id=33; Query OK, 0 rows affected (0.00 sec) <-----删除成功 mysql> select * from user; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------------------+------------+------+--------------------+ 10 rows in set (0.00 sec)
变化多端的查询
对于增删改都比较简单，这里我们主要来细说一下查询，因为查询可以更加不同条件组合来获取不同的查询结果，这点还是比较有意思的。
过滤条件 - WHERE
先来看看条件查询，前面我们更新和删除时都使用到了条件语句，使用where子句指明要删除记录的id是哪个，也就是指明删除的符合具体条件的行数据，同样的条件也适合select语句，通过where子句查询，可以过滤不是期望的数据，下面通过一个例子来演示，查询id大于15的所有数据，执行语句如下：
mysql> select * from user where id > 15 ; +----+-----------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------+------------+------+--------------------+ | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------+------------+------+--------------------+ 6 rows in set (0.00 sec)
其中 > 属于比较运算符 ，如上面id大于15的所有数据将会被检索出来，而id小于15的就被过滤掉了，当然在where子句中还可使用其他运算符，如下
| 运算符 | 描述 | 演示 |
|---|---|---|
= |
相等 | id = 15 |
> |
大于 | id > 15 |
< |
小于 | id < 15 |
>= |
大于等于 | id >= 15 |
<= |
小于等于 | id <= 15 |
<> |
不相等 | id <> 15 |
IS [NOT] NULL |
为NULL(不为NULL) | address is NULL |
[NOT] LIKE |
模糊查询，指向模糊查询目标 | address LIKE '北京%' |
[NOT] BETWEEN |
(不包含)包含在指定范围内 | id BETWEEN 1 AND 15 |
[NOT] IN |
包含在指定范围值内 | id in (1,2,15,20) |
NULL 操作符
NULL条件有点需要注意，在使用NULL条件检索时不能使用=号，必须使用 is 指明查询条件的值为空，当然如果是not null 那就是非空数据了，如：
#查询生日为空的用户 mysql> select * from user where birthday is NULL; +----+----------+----------+------+---------+ | id | username | birthday | sex | address | +----+----------+----------+------+---------+ | 1 | 王五 | NULL | 2 | NULL | +----+----------+----------+------+---------+ 1 row in set (0.00 sec)
LIKE 操作符
关键字like主要用于模糊查询，如下查询名称为‘任’开头的用户：
mysql> select username from user where username like '任%'; +-----------+ | username | +-----------+ | 任在明 | | 任传海 | +-----------+ 2 rows in set (0.00 sec)
其中% 称为外卡符号，代表0个以上的字符。如上述的【任%】代表以任开头的所有字符串。如果是 【%任】则代表以任结尾的所有字符串，而【%任%】代表所有含有任字的字符串。当然除了% 是外卡符号，还有‘_’下划线也是外卡符号，，代表一个字符，也就是说条件为任_ 时 只有【任良】是符合条件而【任其阿】则是不符合条件。同理加上NOT 则取反的意思。
BETWEEN 操作符
BETWEEN 是让我们可以运用一个范围 (range) 内抓出数据库中的值。执行语句如下：
#查询id在1到15之间的用户(包括1和15) mysql> select * from user where id between 1 and 15 ; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | +----+-----------------------+------------+------+--------------------+ 4 rows in set (0.00 sec)
IN 操作符
表示某一组指明的数据，在括弧内可以有一或多个值，而不同值之间由逗点分开。值可以是数目或是文字。如下语句：
#查询id为1，2，3的用户 mysql> select * from user where id in (1,2,3); +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 2 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | +----+-----------------------+------------+------+--------------------+ 4 rows in set (0.00 sec)
多条件组合AND 和 OR 操作符
当需要在查询中使用多个条件组合时，可以使用AND 或者 OR ，其中指明两个条件必须成立，而OR则需要一个条件成立即可，如下语句使用AND进行条件组合查询：
#查询生日为空并且sex=1的用户 mysql> select * from user where birthday is null and sex = 1; +----+----------+----------+------+---------+ | id | username | birthday | sex | address | +----+----------+----------+------+---------+ | 1 | 王五 | NULL | 1 | NULL | +----+----------+----------+------+---------+ 1 row in set (0.00 sec)
使用OR进行条件组合查询的结果如下：
#查询生日为空或者sex=1的用户 mysql> select * from user where birthday is null or sex = 1; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 1 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------------------+------------+------+--------------------+ 7 rows in set (0.00 sec)
由此看出AND和OR的区别确实很明显，而且在where条件语句中可包含任意数目的AND和OR操作符，因此我们在语句查询时对于AND和OR的使用以及计算顺序要特别注意，否则将会得到非预期的查询结果。
对查询结果排序 ORDER BY
有时我们希望查询出的数据按照一定的规律排序，此时ORDER BY就是很好的帮手了，如想让查询出来的数据按生日排序：
#默认按升序，ASC 升序（可省略） ， DESC 降序 mysql> select username,birthday from user where id > 15 order by birthday ASC; +-----------+------------+ | username | birthday | +-----------+------------+ | 任传海 | 1992-03-08 | | 陈小明 | 1995-05-10 | | 任在明 | 1996-12-01 | +-----------+------------+ 6 rows in set (0.00 sec)
如果有多个排序条件则用逗号隔开，第一个条件的优先级总是高于第二个条件，如下：
#先按id排序再按生日排序 mysql> select id ,username,birthday from user where id > 15 order by id ,birthday; +----+-----------+------------+ | id | username | birthday | +----+-----------+------------+ | 16 | 任在明 | 1996-12-01 | | 22 | 陈小明 | 1995-05-10 | | 24 | 任传海 | 1992-03-08 | +----+-----------+------------+ 6 rows in set (0.00 sec)
获取指定行数的数据-LIMIT
通过LIMIT可以获取到指定行数的记录。比如想获取前3条数据
#获取前3条数据 mysql> select * from user limit 0 , 3; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | +----+-----------------------+------------+------+--------------------+
其中limit 0 ，3 ，第一个参数代表从第0个(也就是第一行数据)开始获取，第二个参数3，表示获取的条数。如下从第1个数据开始获取，结果就不一样了。
mysql> select * from user limit 1 , 3; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 1 | 北京市朝阳区 | +----+-----------------------+------------+------+--------------------+
数据分组-GROUP BY 与 过滤分组 - HAVING
有时候可能需要依据某个字段进行查询结果分组，这时GROUP BY就显得很有用了，比如在user 表中我们依据sex字段进行分组,统计两种性别分别有多少人，先查看没有分组的数据：
mysql> select * from user ; +----+-----------------------+------------+------+--------------------+ | id | username | birthday | sex | address | +----+-----------------------+------------+------+--------------------+ | 1 | 王五 | NULL | 1 | NULL | | 2 | 张曹宇 | 1990-08-05 | 1 | 广东省汕头市 | | 3 | 大王让我来巡山 | 1909-12-12 | 1 | 北京朝阳 | | 10 | 张三 | 1999-06-06 | 0 | 北京市朝阳区 | | 16 | 任在明 | 1996-12-01 | 1 | 广东省广州市 | | 22 | 陈小明 | 1995-05-10 | 0 | 广东省深圳市 | | 24 | 任传海 | 1992-03-08 | 1 | 海南三亚 | +----+-----------------------+------------+------+--------------------+ 7 rows in set (0.00 sec)
分组数据如下，其count()是统计函数，可以统计某个字段的数量,执行结果如下：
#根据sex统计分组数量 mysql> select sex ,count(id) from user group by sex; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 2 | | 1 | 5 | +------+-----------+ 2 rows in set (0.01 sec)
有点要注意的是如果列中具有NULL值，则NULL将作为一个分组返回，如果列中有多个NULL值，它们将分为一组，GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 语句之前。如下添加两条sex为null的数据。
insert into user values(11,'aaa',NULL,NULL,NULL); insert into user values(12,'bbb',NULL,NULL,NULL); mysql> select sex ,count(id) from user group by sex; +------+-----------+ | sex | count(id) | +------+-----------+ | NULL | 2 |<------ 以NULL进行分组 | 0 | 2 | | 1 | 5 | +------+-----------+ 3 rows in set (0.00 sec)
除了能使用group by 分组数据外，我们还可以对分组的数据进行过滤，从而指定包括哪些分组，排除哪些分组，比如根据sex字段进行user查询分组时想排除sex字段值为null的数据，此时需要指明一个条件进行过滤，可能我们已想到where 子语句，遗憾的是where并不能对数据进行分组过滤，因为where更多地是进行行数据的过滤而不是分组数据的过滤，实际上where并没有分组的概念。幸运的是，mysql提供另外的子语句having，having与where有点类似，只不过where是进行行数据过滤，而having是进行组数据过滤,其演示过程如下：
#使用 having 排除sex为null的分组 mysql> select sex ,count(id) from user group by sex having sex is not null; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 2 | | 1 | 5 | +------+-----------+ 2 rows in set (0.01 sec)
记住，where是进行行数据的过滤，也就是说在分组前where已过滤了数据，而having是进行分组数据的过滤，即在分组后才进行数据过滤，接着来看一个where 和 having 结合使用的例子，比如还是根据sex进行分组查询并排除sex为null的分组，同时希望排除id小于10的数据，其sql编写并执行如下：
#先查看所有数据 mysql> select id ,username, sex from user; +----+-----------------------+------+ | id | username | sex | +----+-----------------------+------+ | 1 | 王五 | 1 | | 2 | 张曹宇 | 1 | | 3 | 大王让我来巡山 | 1 | | 10 | 张三 | 0 | | 11 | aaa | NULL | | 12 | bbb | NULL | | 16 | 任在明 | 1 | | 22 | 陈小明 | 0 | | 24 | 任传海 | 1 | +----+-----------------------+------+ 9 rows in set (0.00 sec) #同时使用where 和 having 进行数据过滤 mysql> select sex ,count(id) from user where id > 10 group by sex having sex is not null; +------+-----------+ | sex | count(id) | +------+-----------+ | 0 | 1 | | 1 | 2 | +------+-----------+ 2 rows in set (0.00 sec)
灵活使用计算字段
所谓的计算字段就通过组合计算出来的字段，下面通过一个简单实例来说明，如我们想查询出所有用户的名称和生日，并通过 name (birthday) 的格式显示 ，sql语句如下：
#为让排版漂亮些，更新一下数据 mysql> update user set username = '李达康' where id =3 ; mysql> update user set username = '张书记' where id = 10; #计算字段(拼接字段) mysql> select Concat(username,'(',birthday,')') from user where birthday is not null; +-----------------------------------+ | Concat(username,'(',birthday,')') | <-----------字段的名称使用AS操作符创建别名 +-----------------------------------+ | 张曹宇(1990-08-05) | | 李达康(1909-12-12) | | 张书记(1999-06-06) | | 任在明(1996-12-01) | | 陈小明(1995-05-10) | | 任传海(1992-03-08) | +-----------------------------------+
在sql语句中使用了concat函数拼接字符串，concat 函数可以把多个字符串拼接成一个长字符串，其使用方式如下，返回结果为连接参数产生的字符串。注意如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT(str1,str2,…)
这样就完成了计算字段的拼接，但是我们发现字段名称比较长也不直观，这里可以使用mysql提供的AS操作符创建别名，如下：
mysql> select Concat(username,'(',birthday,')') AS name_birthday from user where birthday is not null; +-----------------------+ | name_birthday | +-----------------------+ | 张曹宇(1990-08-05) | | 李达康(1909-12-12) | | 张书记(1999-06-06) | | 任在明(1996-12-01) | | 陈小明(1995-05-10) | | 任传海(1992-03-08) | +-----------------------+
除了上述的计算字段，还可执行算术计算的字段如下，items表的数据，查询出来后按8折价格计算
mysql> select name , price ,price * 0.8 AS discount_price from items; +------------------+---------+----------------+ | name | price | discount_price | +------------------+---------+----------------+ | MacBook Air | 8298.9 | 6639.1 | | MacBook Pro | 10982.0 | 8785.6 | | 背包 | 200.0 | 160.0 | | 台式机 | 3000.0 | 2400.0 | | Java EE 7 | 78.0 | 62.4 | | Java编程思想 | 83.0 | 66.4 | +------------------+---------+----------------+
price 字段为items表的原字段，而 discount_price 则是计算出来后的折扣价格，这种字段也称计算字段。这里我们反复使用了AS操作符创建别名，实际上该操作符也可使用表名称上为表创建别名，当表名很长时，利用AS创建别名不失为一种好办法。
可能需要知道的常用的数据处理函数
如果需要进一步了解每个函数具体用法，可以访问-这篇文章
- 文本处理函数
| 函数 | 描述 |
|---|---|
| Concat(str1,str2,…) | 连接字符串 |
| Left(str,len) | 从字符串的左端开始取得len长的子串 |
| Right(str,len) | 从字符串的右端开始取得len长的子串 |
| Length(str) | 返回串str的长度 |
| Lower(str) | str转为小写 |
| Upper(str) | str转为大写 |
| Trim(str) | 去掉字符串str两边的空格 |
| SubString(str,pos[,len]) | 取得字符串str中从pos位置开始的len长的子串 |
| Replace（t1，t2，t3） | 把t1字符串中的t2换为t3 |
| SubString(t，x，y) | 返回t中始于x的长为y的字符串 |
- 数值处理函数
| 函数 | 描述 |
|---|---|
| Abs(num) | 返回一个数的绝对值 |
| Mod(x,y) | 求x/y余数 |
| Pow(x，n) | x的n次方 |
| Rand() | 返回一个随机数 |
| Round(n1,n2) | 返回数n1，它被四舍五入为n2位小数 |
| Sqrt(num) | 返回一个数的平方根 |
| Ceiling(num) | 基于num的值的下一个最大的整数 |
| Floor(num) | 返回num的整数值 |
| Format（n1,n2） | 返回格式化为一个数的n1，这个数带有n2位小数，并且每3位之间插入一个逗号 |
- 日期函数
| 函数 | 描述 |
|---|---|
| Now | 当前时间(2017-04-08 17:06:45) |
| CurDate() | 当前日期（2017-04-08） |
| CurTime() | 当前时间(17:06:45) |
| DateDiff(d1,d2) | 计算两个日期差值 |
| AddDate() | 添加一个日期(天、周等) |
| AddTime() | 添加一个时间（时、分等） |
| date_format(d1,format) | 格式化日期 |
| Date() | 返回日期时间的日期部分 |
| Month() | 返回一个日期的月份部分 |
| Year() | 返回一个日期的年份部分 |
| Day() | 返回日期的天数部分 |
| DayOfWeek() | 对于一个日期返回对于星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Second() | 返回一个时间的秒部分 |
mysql中的常用聚合函数
有些情况下我们可能只是需要查询结果的汇总数据而不是把每行每列检索出来，如确定表中的行数，获取表中每个字段的总和等，此时使用mysql提供的聚合函数就可很容易获得预期结果，一般常用的聚合函数如下：
| 函数 | 描述 |
|---|---|
| AVG(列名) | 平均值 |
| COUNT(列名) | 总数量 |
| MAX(列名) | 最大值 |
| MIN(列名) | 最小值 |
| SUM(列名) | 合计值 |
-
AVG()函数
AVG函数是用来查找各种记录的一个字段的平均值，下面通过一个查询案例来说明其作用，现在通过sql查询items商品表中的商品平均价格，其语句及其执行过程如下：
#此sql返回的avg_price字段就是商品表中商品的平均价格 mysql> select AVG(price) AS avg_price from items; +------------+ | avg_price | +------------+ | 3773.65007 | +------------+ 1 row in set (0.00 sec)
-
注意，在使用AVG()函数求平均值时，计算的字段是必须提供的，而且对于NULL值，AVG()函数将会忽略值为NULL的行。
-
COUNT()函数
COUNT()主要用于行数计算，可利用该函数来确定表中的行数或者符合特定条件的数目。现在通过COUNT()函数查询user的总数量，其sql如下：
mysql> select COUNT(*) AS count from user; +-------+ | count | +-------+ | 9 | +-------+ 1 row in set (0.00 sec) mysql> select COUNT(birthday) AS count from user; +-------+ | count | +-------+ | 6 | +-------+ 1 row in set (0.00 sec) mysql> select COUNT(id) AS count from user; +-------+ | count | +-------+ | 9 | +-------+ 1 row in set (0.00 sec)
-
从上述sql可以看出COUNT()函数存在两种使用方式：
-
使用COUNT(*)对表中的行数进行统计，不管列中包含的是NULL值还是非NULL值，都可正常统计出数量（大多数情况下建议使用该方式）。
-
使用COUNT(column)对特定列中的具有值的行进行统计，忽略NULL值，上述通过birthday字段和id字段查询出来的总数不一样，就是因为birthday字段存在NULL值，而id字段不存在NULL值。
-
-
MAX()函数 和 MIN()函数
MAX()函数返回最大值，而MIN()函数返回最小值，查询商品表items中价格最大的商品和价格最低的商品，执行过程如下：
mysql> select MAX(price) AS max_price ,MIN(price) AS min_price from items; +-----------+-----------+ | max_price | min_price | +-----------+-----------+ | 10982.0 | 78.0 | +-----------+-----------+ 1 row in set (0.00 sec)
SUM()函数
SUM()函数主要用来返回指定列值的和，如查询订单详情orderdetail表中的商品总数量
#订单详情的数据 mysql> select * from orderdetail; +----+-----------+----------+-----------+ | id | orders_id | items_id | items_num | +----+-----------+----------+-----------+ | 1 | 3 | 1 | 1 | | 2 | 3 | 2 | 3 | | 3 | 4 | 3 | 4 | | 4 | 4 | 2 | 3 | +----+-----------+----------+-----------+ #查询订单详情orderdetail表中的商品总数量 mysql> select SUM(items_num) AS items_sum_count from orderdetail; +-----------------+ | items_sum_count | +-----------------+ | 11 | +-----------------+
表关联的那点事儿
在前面的sql语句中，我们对表操作始终只有一张并没有涉及到多张表的关联，但在实际开发中，单表查询并不能很好满足预期的需求，而通过多表关联查询则能实现预期的需求，这也就是为什么需要表关联的原因了，在前面的表创建中，orderdetail表中同时拥有关联商品表items的item_id字段和订单表order的order_id字段，这两个字段称为orderdetail表的外键，由此它们之间也就产生了关联关系，如下图：
在前面的sql语句中，我们对表操作始终只有一张并没有涉及到多张表的关联，但在实际开发中，单表查询并不能很好满足预期的需求，而通过多表关联查询则能实现预期的需求，这也就是为什么需要表关联的原因了，在前面的表创建中，orderdetail表中同时拥有关联商品表items的item_id字段和订单表order的order_id字段，这两个字段称为orderdetail表的外键，由此它们之间也就产生了关联关系，如下图：
那为什么需要产生关联关系呢？这里我们以订单详情orderdetail和商品表items为例子，分析不把商品信息直接都放到订单详情表中的原因？事实上分表存储商品信息和订单详情表是有如下理由
-
对于每个订单来说，相同的商品的信息都是一样的，因此如果把商品信息直接存放到订单表中，对于不同订单的相同商品的信息的存储将是重复的，这属于既浪费时间又浪费存储空间，完全没有必要。
-
如果此时商品信息发生变化，对于商品表来说只需改变一次即可，但如果直接把商品信息存储到订单表中，那么重复的商品信息都将要修改，维护成本得不偿失呢。
所有分表存储商品信息和订单详情信息是个不错的选择。虽然分解数据为多个表能更有效地存储，更方便地处理数据，但这些好处是有一定的代价的，那就是在查询预期数据时需要多表关联查询，也称为多表联结，这样的查询效率显然不会有单表查询的效率高，不过相对于上述存在的问题，牺牲点效率还是很值得的。下面的语句将演示如何进行联结查询。
#查看订单详情表的信息 mysql> mysql> * from nt from orderdetail; +----+-----------+----------+-----------+ | id | orders_id | items_id | items_num | +----+-----------+----------+-----------+ | 1 | 3 | 1 | 1 | | 2 | 3 | 2 | 3 | | 3 | 4 | 3 | 4 | | 4 | 4 | 2 | 3 | +----+-----------+----------+-----------+ 4 rows in set (0.03 sec) #查看商品表的信息 mysql> select * from items; +----+------------------+---------+--------------------+------+---------------------+ | id | name | price | detail | pic | createtime | +----+------------------+---------+--------------------+------+---------------------+ | 1 | MacBook Air | 8298.9 | MAC Air | NULL | 2016-09-03 13:22:53 | | 2 | MacBook Pro | 10982.0 | MAC Pro | NULL | 2016-02-09 13:22:57 | | 3 | 背包 | 200.0 | 名牌背包 | NULL | 2016-02-06 13:23:02 | | 4 | 台式机 | 3000.0 | 联想 | NULL | 2016-02-03 13:22:53 | | 5 | Java EE 7 | 78.0 | 机器学习领域 | NULL | 2016-12-01 18:29:33 | | 6 | Java编程思想 | 83.0 | Java EE 7平台 | NULL | 2016-12-05 13:21:33 | +----+------------------+---------+--------------------+------+---------------------+
内关联
现在通过关联orderdetail和items表查询出每个订单详情的商品名称，商品价格，购买数量，购买总价格，sql语句如下：
#关联查询 mysql> select tm.name , tm.price ,od.items_num, (tm.price * od.items_num) AS sum_price from orderdetail AS od inner join items AS tm on od.items_id = tm.id; +-------------+---------+-----------+-----------+ | name | price | items_num | sum_price | +-------------+---------+-----------+-----------+ | MacBook Air | 8298.9 | 1 | 8298.9 | | MacBook Pro | 10982.0 | 3 | 32946.0 | | 背包 | 200.0 | 4 | 800.0 | | MacBook Pro | 10982.0 | 3 | 32946.0 | +-------------+---------+-----------+-----------+
