MYSQL之合并查询结果

本文主要介绍合并查询结果和如何为表和字段取别名。

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookName` varchar(20) DEFAULT NULL,
  `price` decimal(6,2) DEFAULT NULL,
  `author` varchar(20) DEFAULT NULL,
  `bookTypeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert  into `t_book`(`id`,`bookName`,`price`,`author`,`bookTypeId`) values (1,'Java编程思想','100.00','埃史尔',1),(2,'Java从入门到精通','80.00','李钟尉',1),(3,'三剑客','70.00','大仲马',2),(4,'生理学(第二版)','24.00','刘先国',4);

CREATE TABLE `t_booktype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookTypeName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert  into `t_booktype`(`id`,`bookTypeName`) values (1,'计算机类'),(2,'文学类'),(3,'教育类');

合并查询项：

查询t_book中所有id
SELECT id FROM t_book;

查询t_booktype中所有id
SELECT id FROM t_booktype;

将两个查询结果合并，去除重复项
SELECT id FROM t_book UNION SELECT id FROM t_booktype;

将两个查询结果合并，不去除重复项
SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

给表和字段取别名：

在t_book中查询id=1的记录
SELECT * FROM t_book WHERE id=1;

给t_book取个别名t
SELECT * FROM t_book t WHERE t.id=1;

在t_book中查询id=1的书名
SELECT t.bookName FROM t_book t WHERE t.id=1;

给字段t.bookName取个别名bName 
SELECT t.bookName bName FROM t_book t WHERE t.id=1;

加个AS结果一样
SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;