MYSQL之合并查询结果

来源：互联网发布：centos selenium 编辑：程序博客网时间：2024/05/21 05:07

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

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中所有idSELECT id FROM t_book;查询t_booktype中所有idSELECT 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取个别名tSELECT * 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;

0 0