首页 > 代码库 > MySQL的初次见面礼基础实战篇

MySQL的初次见面礼基础实战篇

2024-09-13 19:02:30   222人阅读

【版权申明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
http://blog.csdn.net/javazejian/article/details/61614366
出自【zejian的博客】

本篇将以最简单的方式呈现并演绎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,
  `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`)  <---------------------------指明items的唯一主键字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中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> 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表的外键，由此它们之间也就产生了关联关系，如下图：

技术分享

那为什么需要产生关联关系呢？这里我们以订单详情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 |
+-------------+---------+-----------+-----------+

使用的sql语句如下：

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;

其中分别使用了AS操作符创建了商品表的别名tm和订单详情表的别名od，并分别在各自表中需要查询的字段前加上了别名，如tm.name , tm.price ,od.items_num, ，这样做的好处是标明字段来自那张表，毕竟有些时候不同的表可能存在重复的字段名，总是标明字段的出处是个好习惯（事实上如果明确某个字段不会重复而不添加表前缀也是可行），然后在表关联方面使用了子语句 inner join … on 表示查询订单详情表中items_id与商品id相同的商品信息， 其语法如下：

SELECT 列名1 ... FROM 表1 
INNER JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

需要注意的是，在内关联表1和表2时两张表并不需要有顺序之分,同时我们发现内关联使用on子语句关联条件，这也可使用and操作符增加其他条件。

外关联

外关联，又称外连接，分为左外连接和右外连接，实际上还有全外连接(左外结果+右外结果)，但是mysql并不支持全外连接。

左外连接

左外连接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值， 左外连接语法如下：

SELECT 列名1 ... FROM 表1 
LEFT [OUTER] JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

现在需要查询所有用户的订单信息(用户名称、订单编号number和订单创建时间)，包括那些没有订单的用户也要查询出来，也就是说即使user表中的用户在orders表中没有数据也要查询出来，此时利用左外连接最合适不过了，其sql如下：

#内关联查询的结果
mysql> select u.username , o.number , o.createtime from user as u  inner join orders as o  on u.id = o.user_id;
+-----------+---------+---------------------+
| username  | number  | createtime          |
+-----------+---------+---------------------+
| 王五      | 1000010 | 2016-02-04 13:22:35 |
| 王五      | 1000011 | 2016-02-03 13:22:41 |
| 张书记    | 1000012 | 2016-02-12 16:13:23 |
+-----------+---------+---------------------+
3 rows in set (0.00 sec)

#左外连接查询的结果
mysql> select u.username , o.number , o.createtime from user as u  left join orders as o  on u.id = o.user_id;
+-----------+---------+---------------------+
| username  | number  | createtime          |
+-----------+---------+---------------------+
| 王五      | 1000010 | 2016-02-04 13:22:35 |
| 王五      | 1000011 | 2016-02-03 13:22:41 |
| 张书记    | 1000012 | 2016-02-12 16:13:23 |
| 张曹宇    | NULL    | NULL                |
| 李达康    | NULL    | NULL                |
| 任在明    | NULL    | NULL                |
| 陈小明    | NULL    | NULL                |
| 任传海    | NULL    | NULL                |
+-----------+---------+---------------------+

如执行结果所示，即使没有订单的用户都被列举出来，使用NULL值填充，这就是左外连接，左边的表总是显示所有信息，无论右边的表是否有对应的数据，注意表间的顺序将影响查询结果。

右外连接

右外连接的作用恰好与左外连接相反，即右边的表总是显示所有信息，无论左边的表是否有对应的数据，同样地，表间的顺序将影响查询结果，其语法如下：

SELECT 列名1 ... FROM 表1 
RIGHT [OUTER] JOIN 表2
ON 表1.外键 = 表2.外键
[WHERE / ORDER BY 语句等]

现在我们使用右外连接查询出与左外连接相同的结果，实际上只换了表间的顺序，执行语句如下：

mysql> select u.username , o.number , o.createtime from orders as o right join user as u  on u.id = o.user_id;
+-----------+---------+---------------------+
| username  | number  | createtime          |
+-----------+---------+---------------------+
| 王五      | 1000010 | 2016-02-04 13:22:35 |
| 王五      | 1000011 | 2016-02-03 13:22:41 |
| 张书记    | 1000012 | 2016-02-12 16:13:23 |
| 张曹宇    | NULL    | NULL                |
| 李达康    | NULL    | NULL                |
| 任在明    | NULL    | NULL                |
| 陈小明    | NULL    | NULL                |
| 任传海    | NULL    | NULL                |
+-----------+---------+---------------------+
10 rows in set (0.00 sec)

复杂关联查询

现在来思考更复杂的关联查询sql语句该如何书写，如果我们需要查询每个订单中所购买的商品详细信息，此时会涉及到3张表（orders，orderdetail，items），那么这样的sql语句会是什么样的呢？

# 3张表关联查询，number重复的原因很简单，每个订单会多条订单详情的信息
mysql> select o.number , tm.name , tm.price , od.items_num from (orders as o inner join orderdetail as od on o.id = od.orders_id ) inner join items as tm on od.items_id = tm.id ;
+---------+-------------+---------+-----------+
| number  | name        | price   | items_num |
+---------+-------------+---------+-----------+
| 1000010 | MacBook Air |  8298.9 |         1 |
| 1000010 | MacBook Pro | 10982.0 |         3 |
| 1000011 | 背包        |   200.0 |         4 |
| 1000011 | MacBook Pro | 10982.0 |         3 |
+---------+-------------+---------+-----------+
4 rows in set (0.00 sec)

从sql可以知道，先通过order表和orderdetail表关联出订单详情的信息，再通过orderdetail和items关联出每个订单详情的商品信息，因此在这条sql中连续使用两次inner join ... on ... 语句。那么如果我们还想同时知道这些订单是属于哪个客户的呢？那就再内关联user表了，sql如下：

mysql> select u.username , o.number , tm.name , tm.price , od.items_num from 
    -> (
    -> (orders as o inner join orderdetail as od on o.id = od.orders_id ) 
    -> inner join items as tm on od.items_id = tm.id 
    -> )
    -> inner join user as u on o.user_id = u.id;
+----------+---------+-------------+---------+-----------+
| username | number  | name        | price   | items_num |
+----------+---------+-------------+---------+-----------+
| 王五     | 1000010 | MacBook Air |  8298.9 |         1 |
| 王五     | 1000010 | MacBook Pro | 10982.0 |         3 |
| 王五     | 1000011 | 背包        |   200.0 |         4 |
| 王五     | 1000011 | MacBook Pro | 10982.0 |         3 |
+----------+---------+-------------+---------+-----------+
4 rows in set (0.00 sec)

我们发现关联查询似乎可以无限关联下去，但这显然不会是一件令人愉快的事，除了sql语句过于复杂外，还将造成查询效率的低下，因此我们应该尽量不要使用过多表的关联查询。从查询结果可以看出订单都属于王五的，现在如果不需要知道订单的详情，只想知道每个用户的订单数量呢？那么sql中就需要结合聚合函数和分组语句来查询：

mysql> select u.username , count(o.number) as order_count from 
    -> orders as o 
    -> inner join user as u on o.user_id = u.id
    -> group by u.id; <-----以用户id作为分组
+-----------+-------------+
| username  | order_count |
+-----------+-------------+
| 王五       |           2 |
+-----------+-------------+

子查询

子查询在mysql中是允许的，所谓的子查询就是嵌套在其他查询中的查询，其基本常用语法如下（并不限于这种类型，where语句的子查询比较常见罢了）：

基本子查询 

SELECT 列名1... FROM 表名
WHERE 列名 比较运算符 (SELECT 命令)

现在用子查询编写一个用于查询商品价格在平均价格之上的所有商品：

mysql> select * from items 
    -> where price > (select AVG(price) 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 |
+----+-------------+---------+---------+------+---------------------+
2 rows in set (0.00 sec)

多返回值的子查询

直接看个简单例子,查询还没有下过订单的用户，多返回值使用到了not in 或者 in 操作符。

mysql> select username ,  address from user where id not in (select user_id from orders);
+-----------+--------------------+
| username  | address            |
+-----------+--------------------+
| 张曹宇    | 广东省汕头市        |
| 李达康    | 北京朝阳           |
| 任在明    | 广东省广州市        |
| 陈小明    | 广东省深圳市        |
| 任传海    | 海南三亚           |
+-----------+--------------------+

相关子查询

相关子查询与EXISTS有关，EXISTS运算符是对子查询中抽出的记录作是否存在的判断，如果存在就返回True，相应的将当前的记录输出。看个简单查询，查询下过订单的用户信息。

mysql> select * from user where exists (select * from orders where user.id=orders.user_id);
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
|  1 | 王五       | NULL       | 1    | 北京市朝阳区         |
+----+-----------+------------+------+--------------------+

EXISTS运算符会针对用户表user的每条信息进行子查询的操作，如果子查询的结果存在，则会返回true，并输出当前用户信息的记录。这种与主查询有关联关系的子查询称为相关子查询。这样的操作时比较消耗性能的，数据量过大时不建议使用。

组合查询

组合查询就是把多个select查询语句的结果相结合，创建组合查询需要使用UNION操作符。如下两个sql 查询语句使用union结合。

mysql> select id , name ,price from items where id in (1,2,3);
+----+-------------+---------+
| id | name        | price   |
+----+-------------+---------+
|  1 | MacBook Air |  8298.9 |
|  2 | MacBook Pro | 10982.0 |
|  3 | 背包         |   200.0 |
+----+-------------+---------+
3 rows in set (0.00 sec)

mysql> select id , name ,price  from items where price < 3000;
+----+------------------+-------+
| id | name             | price |
+----+------------------+-------+
|  3 | 背包              | 200.0 |
|  5 | Java EE 7        |  78.0 |
|  6 | Java编程思想       |  83.0 |
+----+------------------+-------+
3 rows in set (0.00 sec)

# 使用union结合
mysql> select id , name ,price  from items where price < 3000 
    -> union 
    -> select id , name ,price from items where id in (1,2,3);
+----+------------------+---------+
| id | name             | price   |
+----+------------------+---------+
|  3 | 背包             |   200.0 |
|  5 | Java EE 7        |    78.0 |
|  6 | Java编程思想      |    83.0 |
|  1 | MacBook Air      |  8298.9 |
|  2 | MacBook Pro      | 10982.0 |
+----+------------------+---------+
5 rows in set (0.00 sec)

总的来说union的使用方式还是比较简单，不过使用union时需要遵循以下规则

  • UNION 必须由两条或者两条以上的select语句组成并使用 UNION 进行组合。
  • UNION 中的每个查询必须包含相同的列、表达式或者聚合函数，但出现顺序不必一样
  • 列数据类型必须兼容，但不必完全相同。

从前面的查询结果可以发现单独查询时每次结果都是3条数据，组合后只剩下5条数据，显然mysql的组合查询默认帮助我们过滤重复的行记录，但如果不想过滤重复的行记录可以使用union all ，这样将会显示所有结果包括重复的数据。

mysql> select id , name ,price  from items where price < 3000  union all  select id , name ,price from items where id in (1,2,3);
+----+------------------+---------+
| id | name             | price   |
+----+------------------+---------+
|  3 | 背包              |   200.0 | <------重复数据
|  5 | Java EE 7        |    78.0 |
|  6 | Java编程思想      |    83.0 |
|  1 | MacBook Air      |  8298.9 |
|  2 | MacBook Pro      | 10982.0 |
|  3 | 背包              |   200.0 | <------重复数据
+----+------------------+---------+
6 rows in set (0.00 sec)

对于组合查询的结果进行排序与普通查询并没有什么区别如下：

mysql> select id , name ,price  from items where price < 3000  union  select id , name ,price from items where id in (1,2,3) order by price;
+----+------------------+---------+
| id | name             | price   |
+----+------------------+---------+
|  5 | Java EE 7        |    78.0 |
|  6 | Java编程思想      |    83.0 |
|  3 | 背包              |   200.0 |
|  1 | MacBook Air      |  8298.9 |
|  2 | MacBook Pro      | 10982.0 |
+----+------------------+---------+
5 rows in set (0.00 sec)

表维护与改造

表的改造

这一部比较简单，主要是语法的知识，表修改语法如下：

#修改列的定义：
ALTER TABLE ... MODIFY
#追加列
ALTER TABLE ... ADD
#修改列名与定义
ALTER TABLE ... CHANGE
#删除列
ALTER TABLE ... DROP

现在新创建一张表，用演示上述操作：

#创建visitor表
mysql> CREATE TABLE `visitor` (
    ->   `mid` char(5) NOT NULL,
    ->   `nam` varchar(20) DEFAULT NULL,
    ->   `birth` datetime DEFAULT NULL,
    ->   `sex` char(1) DEFAULT ‘0‘,
    ->   PRIMARY KEY (`mid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

#查看表结构
mysql> desc visitor; 
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mid   | char(5)     | NO   | PRI | NULL    |       |
| nam   | varchar(20) | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改列的数据类型使用以下语句

ALTER TABLE 表名 MODIFY 列名 数据类型;

现在把visitor表中的mid 数据类型由char(5) 修改为 int(11)

#修改语句
mysql> alter table visitor modify mid int(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc visitor;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mid   | int(11)     | NO   | PRI | NULL    |       | <----已修改成功
| nam   | varchar(20) | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

有点需要注意的，在修改数据类型时需要明确是否已存在数据，以及存在的数据类型能转成预期的数据类型。

添加新的列使用以下语法

ALTER TABLE 表名 ADD 列名 数据类型;

现在为visitor表添加一个age的字段，sql语句如下：

mysql> alter table visitor add age int(3);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc visitor;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mid   | int(11)     | NO   | PRI | NULL    |       | 
| nam   | varchar(20) | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | 0       |       |
| age   | int(3)      | YES  |     | NULL    |       | <----age字段已添加成功
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

如果想追加到表头可以这样书写sql,使用FIRST进行标明：

alter table visitor add age int(3) FIRST;

如果想添加到某字段之后可以使用AFTER指明：

#添加到birth字段之后
alter table visitor add age int(3) AFTER birth;

如果此时已添加完成，但又想改变列的位置可以使用如下方式：

#add 改为modify即可
alter table visitor modify age int(3) AFTER birth;

同时改变列名和类型，可以使用下列方式

ALTER TABLE 表名 CHANGE 修改前列名 修改后列名 修改后类型

现在使用上述方式修改birth的列名和类型

#修改前
mysql> desc visitor;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| mid   | int(11)     | NO   | PRI | NULL    |       |
| nam   | varchar(20) | YES  |     | NULL    |       |
| birth | datetime    | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | 0       |       |
| age   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

#修改的sql语句
mysql> alter table visitor change birth birthday date;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#修改后
mysql> desc visitor;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| mid      | int(11)     | NO   | PRI | NULL    |       |
| nam      | varchar(20) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | 0       |       |
| age      | int(3)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除列的语句也比较简单

ALTER TABLE 表名 DROP 列名

现在删除之前添加的age字段：

mysql> alter table visitor drop age;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc visitor;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| mid      | int(11)     | NO   | PRI | NULL    |       |
| nam      | varchar(20) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
| sex      | char(1)     | YES  |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

表的复制

复制表在数据库数据维护时使用到，以下的方式不仅可以复制表结构还可以复制表的数据

CREATE TABLE 新表名 SELECT * FROM 旧表名

现在可以尝试利用user表来构造新表：

#复制表
mysql> create table new_user select * from user;
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_webshop |
+-------------------+
| items             |
| new_user          | <------ 产生新表new_user
| orderdetail       |
| orders            |
| user              |
| visitor           |
+-------------------+
6 rows in set (0.00 sec)

mysql> select  * from new_user;
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
|  1 | 王五      | NULL       | 1    | 北京市朝阳区          |
|  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    | 海南三亚             |
+----+-----------+------------+------+--------------------+
9 rows in set (0.00 sec)

如果只向复制表结构而不需要数据，可以使用以下语法：

CREATE TABLE 新表名 LIKE 旧表名

同样再次复制user表的结构

#构建新表结构
mysql> create table new_user_no_data like user;
Query OK, 0 rows affected (0.01 sec)

mysql> desc new_user_no_data;
+----------+--------------+------+-----+---------+----------------+
| 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    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

#查询不到数据了
mysql> select * from  new_user_no_data;
Empty set (0.00 sec)

如果想从一张表中向另外一种表复制数据时可以使用以下语法：

INSERT INTO 表名 SELECT * FROM 带数据的表

现在我们把user表的数据复制给new_user_no_data表

#复制数据
mysql> insert into  new_user_no_data select * from user;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0
#查询新表数据
mysql> select * from  new_user_no_data;
+----+-----------+------------+------+--------------------+
| id | username  | birthday   | sex  | address            |
+----+-----------+------------+------+--------------------+
|  1 | 王五      | NULL       | 1    | 北京市朝阳区          |
|  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    | 海南三亚             |
+----+-----------+------------+------+--------------------+
9 rows in set (0.00 sec)

ok~，到此mysql的基础用法就讲解完结了，限于篇幅mysql的高级应用篇就放在下一篇博文，欢迎继续关注。



如果您喜欢我写的博文，读后觉得收获很大，不妨小额赞助我一下，让我有动力继续写出高质量的博文，感谢您的赞赏！支付宝、微信


技术分享         技术分享

<script type="text/javascript"> $(function () { $(‘pre.prettyprint code‘).each(function () { var lines = $(this).text().split(‘\n‘).length; var $numbering = $(‘
    ‘).addClass(‘pre-numbering‘).hide(); $(this).addClass(‘has-numbering‘).parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($(‘
  • ‘).text(i)); }; $numbering.fadeIn(1700); }); }); </script>

    MySQL的初次见面礼基础实战篇


联系
我们