你的位置:首页 > 数据库

[数据库]第五章 单表查询,聚合函数查询,连接查询,子查询,合并查询结果,为表和字段取别名


示例数据:

CREATE TABLE `t_student` (`id` DOUBLE ,`stuName` VARCHAR (60),`age` DOUBLE ,`sex` VARCHAR (30),`gradeName` VARCHAR (60)); 

 

插入数据:

INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('1','张三','23','男','一年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('2','张三丰','25','男','二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('3','李四','23','男','一年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('4','王五','22','男','三年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('5','珍妮','21','女','一年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('6','李娜','26','女','二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('7','王峰','20','男','三年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('8','梦娜','21','女','二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('9','小黑','22','男','一年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('10','追风','25','男','二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('11','小小张三','21',NULL,'二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('12','小张三','23','男','二年级');INSERT INTO `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) VALUES('13','张三锋小','24',NULL,'二年级');

 

 

 

第一节:单表查询


5.1,查询所有字段

1,SELECT 字段 1,字段 2,字段 3...FROM 表名;

SELECT id,stuName,age,sex,gradeName FROM t_student ;

SELECT stuName,id,age,sex,gradeName FROM t_student ;


2,SELECT * FROM 表名;

SELECT * FROM t_student;


5.2,查询指定字段


1,SELECT 字段 1,字段 2,字段 3...FROM 表名;

SELECT stuName,gradeName FROM t_student;


5.3,Where 条件查询

1,SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 条件表达式;

SELECT * FROM t_student WHERE id=1;

SELECT * FROM t_student WHERE age>22;



5.4,带 IN 关键字查询

1,SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1,元素 2,元素 3);

SELECT * FROM t_student WHERE age IN (21,23);

SELECT * FROM t_student WHERE age NOT IN (21,23);


5.5,带 BETWEEN AND 的范围查询

1,SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2;

SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;

 

SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;



5.6,带 LIKE 的模糊查询

1,SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’;

“%”代表任意字符;
“_” 代表单个字符;

SELECT * FROM t_student WHERE stuName LIKE '张三';

SELECT * FROM t_student WHERE stuName LIKE '张三%'

SELECT * FROM t_student WHERE stuName LIKE '张三_';SELECT * FROM t_student WHERE stuName LIKE '张三__';

SELECT * FROM t_student WHERE stuName LIKE '%张三%';



5.7,空值查询

1,SELECT 字段 1,字段 2,字段 3...FROM 表名 WHERE 字段 IS [NOT] NULL;

SELECT * FROM t_student WHERE sex IS NULL;SELECT * FROM t_student WHERE sex IS NOT NULL;



5.8,带 AND 的多条件查询

1,SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23


5.9,带 OR 的多条件查询

1,SELECT 字段 1,字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23



5.10,DISTINCT 去重复查询

SELECT 字段 1,字段 2...FROM 表名 ORDER BY 属性名 [ASC|DESC]

SELECT DISTINCT gradeName FROM t_student;



5.12,GROUP BY 分组查询

GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]

1,单独使用(毫无意义);
2,与 GROUP_CONCAT()函数一起使用;
3,与聚合函数一起使用;
4,与 HAVING 一起使用(限制输出的结果);
5,与 WITH ROLLUP 一起使用(最后加入一个总和行);

 

SELECT * FROM t_student ORDER BY age ASC;

SELECT * FROM t_student ORDER BY age DESC;

 

SELECT * FROM t_student GROUP BY gradeName;SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;



5.13,LIMIT 分页查询

SELECT 字段 1,字段 2...FROM 表名 LIMIT 初始位置,记录数;

SELECT * FROM t_student LIMIT 0,5;SELECT * FROM t_student LIMIT 5,5;SELECT * FROM t_student LIMIT 10,5;

 

 

第二节:使用聚合函数查询

创建数据表:

CREATE TABLE `t_grade` (  `id` INT ,  `stuName` VARCHAR (60),  `course` VARCHAR (60),  `score` INT ); 

 

插入数据:

INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('1','张三','语文','91');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('2','张三','数学','90');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('3','张三','英语','87');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('4','李四','语文','79');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('5','李四','数学','95');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('6','李四','英语','80');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('7','王五','语文','77');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('8','王五','数学','81');INSERT INTO `t_grade` (`id`, `stuName`, `course`, `score`) VALUES('9','王五','英语','89');

 



5.1,COUNT()函数

 

1,COUNT()函数用来统计记录的条数;

SELECT COUNT(*) FROM t_grade;

SELECT COUNT(*) AS total FROM t_grade;


2,与 GOUPE BY 关键字一起使用;

SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;



5.2,SUN()函数

1,SUM()函数是求和函数;

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";


2,与 GOUPE BY 关键字一起使用;

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;



5.3,AVG()函数

 

1,AVG()函数是求平均值的函数;

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";


2,与 GOUPE BY 关键字一起使用;

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;



5.4,MAX()函数

1,MAX()函数是求最大值的函数;

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";


2,与 GOUPE BY 关键字一起使用;

SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;



5.5,MIN()函数

 

1,MIN()函数是求最小值的函数;

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";


2,与 GOUPE BY 关键字一起使用;

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

 



第三节:连接查询

示例数据:

 

/*SQLyog 企业版 - MySQL GUI v8.14 MySQL - 5.1.49-community : Database - db_book**********************************************************************//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_book` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `db_book`;/*Table structure for table `t_book` */DROP TABLE IF EXISTS `t_book`;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`)) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;/*Data for the table `t_book` */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);/*Table structure for table `t_booktype` */DROP TABLE IF EXISTS `t_booktype`;CREATE TABLE `t_booktype` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `bookTypeName` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;/*Data for the table `t_booktype` */INSERT INTO `t_booktype`(`id`,`bookTypeName`) VALUES (1,'计算机类'),(2,'文学类'),(3,'教育类');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

连接查询是将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据;

SELECT * FROM t_book,t_bookType;

 

得出的是笛卡尔积。

假设集合A={a, b},集合B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

这样子出来的数据不是我们想要的。我们需要进行筛选。


3.1,内连接查询

内连接查询是一种最常用的连接查询。内连接查询可以查询两个或者两个以上的表;

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

优化一下:

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;



3.2,外连接查询

外连接可以查出某一张表的所有信息;

SELECT 属性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2;


    3.2.1 左连接查询

    可以查询出“表名 1”的所有记录,而“表名 2”中,只能查询出匹配的记录;
  

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

 

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;


    3.2.2 右连接查询

    可以查询出“表名 2”的所有记录,而“表名 1”中,只能查询出匹配的记录;

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;


3.3,多条件连接查询

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;

 



第四节:子查询

增加示例数据:

/*SQLyog 企业版 - MySQL GUI v8.14 MySQL - 5.1.49-community **********************************************************************//*!40101 SET NAMES utf8 */;CREATE TABLE `t_pricelevel` (  `id` INT ,  `priceLevel` INT ,  `price` FLOAT ,  `description` VARCHAR (300)); INSERT INTO `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) VALUES('1','1','80.00','价格贵的书');INSERT INTO `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) VALUES('2','2','60.00','价格适中的书');INSERT INTO `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) VALUES('3','3','40.00','价格便宜的书');

 


4.1 带 In 关键字的子查询

一个查询语句的条件可能落在另一个 SELECT 语句的查询结果中。

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);



4.2 带比较运算符的子查询

子查询可以使用比较运算符。

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);



4.3 带 Exists 关键字的子查询

假如子查询查询到记录,则进行外层查询,否则,不执行外层查询;

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);



4.4 带 Any 关键字的子查询

ANY 关键字表示满足其中任一条件;

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);



4.5 带 All 关键字的子查询

ALL 关键字表示满足所有条件;

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);



第五节:合并查询结果

5.1 UNION

使用 UNION 关键字是,数据库系统会将所有的查询结果合并到一起,然后去除掉相同的记录;

SELECT id FROM t_book;SELECT id FROM t_booktype;SELECT id FROM t_book UNION SELECT id FROM t_booktype;



5.2 UNION ALL

使用 UNION ALL,不会去除掉系统的记录;

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

 


第六节:为表和字段取别名


6.1 为表取别名

格式: 表名 表的别名

SELECT * FROM t_book WHERE id=1;SELECT * FROM t_book t WHERE t.id=1;


6.2 为字段取别名

格式: 属性名 [AS] 别名

SELECT t.bookName FROM t_book t WHERE t.id=1;SELECT t.bookName bName FROM t_book t WHERE t.id=1;SELECT t.bookName AS bName FROM t_book t WHERE t.id=1;

 




办理普吉岛旅游签证跟团普吉岛旅游需要多少钱几月份去普吉岛旅游最好什么时候去普吉岛旅游最便宜去普吉岛旅游跟团要多少钱七娘山野炊山庄怎么走?深圳七娘山野炊山庄门票多少钱? 七娘山野炊山庄地址?深圳七娘山野炊山庄在哪里? 七娘山野炊山庄好玩吗?深圳七娘山野炊山庄怎么样? 深圳七娘山地质公园在哪里?大鹏半岛国家地质公园地址? 秋天旅游景点推荐 秋天旅游好去处 秋天旅游去哪里推荐 2015永记生态园中秋节活动?惠州永记生态园中秋节门票多少钱? 永记生态园滑草多少钱?惠州永记生态园有滑草场吗? 中秋节去高明盈香生态园有什么活动?盈香生态园中秋节晚上有演出吗? 老君山在哪_洛阳老君山在哪里_栾川老君山在哪个省 悉尼歌剧院简介 太行山在哪里_太行山在哪个省_太行山在什么地方 海螺沟在哪里_海螺沟在哪_海螺沟在什么地方 山西太原特色美食概况 三百年历史风霜雨打--黄烧饼 北京特色美食---扒黑猪脸 北京特色小吃---蜂糕 IDT7201LA50J Datasheet IDT7201LA50J Datasheet ICS843004AG-01LFT Datasheet ICS843004AG-01LFT Datasheet IDT23S08E-1HDCGI Datasheet IDT23S08E-1HDCGI Datasheet 从深圳去欧洲旅游报价 从深圳去欧洲旅游报价 从深圳去欧洲旅游报价 组团欧洲旅游 组团欧洲旅游 组团欧洲旅游 欧洲旅游组团 欧洲旅游组团 欧洲旅游组团