mysql 之单表查询基础sql

最新推荐文章于 2024-04-10 21:35:01 发布

yjaspire

最新推荐文章于 2024-04-10 21:35:01 发布

阅读量577

点赞数

分类专栏：数据库文章标签：查询基础sql 聚合函数连接查询

本文链接：https://blog.csdn.net/yjaspire/article/details/80660011

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

建立测试表

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

查询所有字段

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

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

SELECT * FROM 表名

SELECT * FROM student;

带 IN 关键字查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] IN (元素 1，元素 2，元素 3)；
SELECT * FROM student WHERE age IN (21,23);
SELECT * FROM student WHERE age NOT IN (21,23);

带 BETWEENAND 的范围查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] BETWEEN 取值 1 AND 取值 2;
SELECT * FROM student WHERE age BETWEEN 21 AND 24;/*包含21岁和24岁的学生*/
SELECT * FROM student WHERE age NOT BETWEEN 21 AND 24;

带 LIKE 的模糊查询

SELECT 字段 1，字段 2，字段 3...FROM 表名 WHERE 字段 [NOT] LIKE ‘字符串’;
“%”代表任意字符;
“_” 代表单个字符;

SELECT * FROM student WHERE stuName LIKE '张三';/*全匹配相当于where =*/
SELECT * FROM student WHERE stuName LIKE '张三%';/*右匹配,张三开头的学生*/
SELECT * FROM student WHERE stuName LIKE '%张三';/*左匹配,张三结尾的学生*/
SELECT * FROM student WHERE stuName LIKE '张三_';/*三个名字的学生,开头是张三*/
SELECT * FROM student WHERE stuName LIKE '%张三%';/*模糊全匹配,只要包含就查出来*/

空值查询

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

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

带 AND 的多条件查询

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 AND 条件表达式 2 [...AND 条件表达式 n]
SELECT * FROM student WHERE gradeName='一年级' AND age=23/*并的关系*/

带 OR 的多条件查询

SELECT 字段 1，字段 2...FROM 表名 WHERE 条件表达式 1 OR 条件表达式 2 [...OR 条件表达式 n]
SELECT * FROM student WHERE gradeName='一年级' OR age=23/*或的关系*/

DISTINCT 去重复查询

SELECT DISTINCT 字段名 FROM 表名;

SELECT DISTINCT gradeName FROM student;

查询结果排序

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

SELECT * FROM student ORDER BY age ASC;/*升序*/
SELECT * FROM student ORDER BY age DESC;/*降序*/

GROUP BY 分组查询

GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]
1，单独使用(毫无意义)；
2，与 GROUP_CONCAT()函数一起使用；
3，与聚合函数一起使用；
4，与 HAVING 一起使用(限制输出的结果)；
5，与 WITH ROLLUP 一起使用(最后加入一个总和行)

SELECT * FROM student GROUP BY gradeName;

SELECT gradeName,GROUP_CONCAT(stuName) FROM student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM student GROUP BY gradeName;

SELECT gradeName,COUNT(stuName) FROM student GROUP BY gradeName HAVING COUNT(stuName)>3;

SELECT gradeName,COUNT(stuName) FROM student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM student GROUP BY gradeName WITH ROLLUP;

LIMIT 分页查询

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

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

聚合函数

更改数据以测试聚合函数->此时学生表只有叫张三和王峰的学生了

UPDATE student SET stuname='张三' WHERE stuname='小小张三'

UPDATE student SET stuname='王峰' WHERE stuname!='张三'

COUNT()函数

COUNT()函数用来统计记录的条数；
SELECT COUNT(*) FROM student;

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

SUM()函数

SUM()函数是求和函数;

SELECT stuName,SUM(age) FROM student WHERE stuName="张三";
SELECT stuName,SUM(age) FROM student GROUP BY stuName;

AVG()函数

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

SELECT stuName,AVG(age) FROM student WHERE stuName="张三";
SELECT stuName,AVG(age) FROM student GROUP BY stuName;

MAX()函数

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

SELECT stuName,MAX(age) FROM student WHERE stuName="张三";
SELECT stuName,MAX(age) FROM student GROUP BY stuName;

MIN()函数

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

SELECT stuName,MIN(age) FROM student WHERE stuName="张三";
SELECT stuName,MIN(age) FROM student GROUP BY stuName;

连接查询

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

建表

CREATE TABLE `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 ;

INSERT  INTO `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 `booktype` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `bookTypeName` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT  INTO `booktype`(`id`,`bookTypeName`) VALUES (1,'计算机类'),(2,'文学类'),(3,'教育类');

内连接查询

SELECT * FROM  book b  CROSS JOIN booktype t  

/*

CROSS JOIN 可以省略

*/SELECT * FROM book,booktype ;/*笛卡儿积*/

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

SELECT * FROM  book b,booktype t WHERE b.bookTypeId=t.id
SELECT * FROM  book b  INNER JOIN booktype t  ON b.bookTypeId=t.id

外连接查询

外连接可以查出某一张表的所有信息；
SELECT 属性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2；

左连接查询

可以查询出“表名 1”的所有记录，而“表名 2”中，只能查询出匹配的记录;
SELECT b.*,t.* FROM  book b LEFT JOIN booktype t ON b.bookTypeId=t.id

右连接查询

可以查询出“表名 2”的所有记录，而“表名 1”中，只能查询出匹配的记录；
SELECT b.*,t.* FROM  book b RIGHT JOIN booktype t ON b.bookTypeId=t.id

LEFT JOIN 是LEFT OUTER JOIN 的缩写,同理,RIGHT JOIN 是 RIGHT OUTER JOIN 的缩写;JOIN 是 INNER JOIN 的缩写。

全外连接（full outer join）<mysql 不支持,替换方案union>

返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

UNION

SELECT b.*,t.* FROM  book b  LEFT  JOIN booktype t  ON b.bookTypeId=t.id
 UNION 
SELECT b1.*,t1.* FROM  book b1  RIGHT  JOIN booktype t1  ON b1.bookTypeId=t1.id

join语句优化:

1.尽可能减少join语句中的NestedLoop的循环总次数:永远使用小结果集驱动大的结果集

2.优先优化NestedLoop的内层循环

3.保证Join语句中被驱动表Join条件字段已经被索引

4.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

join Buffer 介绍-> join buffer

yjaspire

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql 之单表查询基础sql

建立测试表CREATE TABLE `student` ( `id` INT , `stuName` VARCHAR (60), `age` INT , `sex` VARCHAR (30), `gradeName` VARCHAR (60)); INSERT INTO `student` (`id`, `stuName`, `age`, `sex`, `gradeName`)...
复制链接

扫一扫

专栏目录