欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql 之单表查询基础sql

程序员文章站 2022-05-03 20:53:26
...

建立测试表

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 ;/*笛卡儿积*/

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

 

 

mysql 之单表查询基础sql

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


mysql 之单表查询基础sql

 

外连接查询

 

外连接可以查出某一张表的所有信息;
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   

mysql 之单表查询基础sql

右连接查询

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

mysql 之单表查询基础sql

 

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
 

mysql 之单表查询基础sql

 

join语句优化:

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

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

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

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

join Buffer 介绍->  join buffer