【Mysql】Mysql查询

MYSQL查询

MYSQL高级查询

数据准备

CREATE TABLE IF NOT EXISTS `student` ( 
	`id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号', 
	`name` VARCHAR(100) NOT NULL COMMENT '姓名', 
	`age` TINYINT NOT NULL COMMENT '年龄', 
	`sex` TINYINT NOT NULL COMMENT '性别(1:男;2:女)', 
	`score` SMALLINT NOT NULL COMMENT '分数', 
	PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '学生表'; 1234567

INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('darren', 21, 1, 101); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('vico', 22, 1, 102); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('king', 23, 1, 103); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('mark', 25, 1, 104); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('QiuXiang', 25, 2, 105); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('BeiBei', 26, 2, 106); 
INSERT INTO `student` (`name`, `age`, `sex`, `score`) VALUES ('YouZi', 26, 2, 106);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

基础查询

-- 显示当前数据库全部table
SHOW TABLES;
-- 全部查询 
SELECT * FROM student; 
-- 只查询部分字段 
SELECT `name`, `age` FROM student; 
-- 别名
SELECT `name` AS '姓名' , `age` AS '年龄' FROM student; 
-- 把查询出来的结果的重复记录去掉 
SELECT distinct `score` FROM student;
1
2
3
4
5
6
7
8
9
10

条件查询

-- 查询姓名为 mark 的学生信息 
SELECT * FROM `student` WHERE `name` = 'mark'; 
-- 查询性别为 1，并且年龄为 22 岁的学生信息 
SELECT * FROM `student` WHERE `sex`=1 AND `age`=22;
1
2
3
4

范围查询

-- 查询年龄在22到23岁的学生的信息 
SELECT * FROM `student` WHERE age BETWEEN 22 AND 23;
1
2

判空查询

SELECT * FROM `student` WHERE `score` IS NOT NULL; #判断不为空 
SELECT * FROM `student` WHERE `score` IS NULL; #判断为空 
SELECT * FROM `student` WHERE sex <> ''; #判断不为空字符串 
SELECT * FROM `student` WHERE sex = ''; #判断为空字符串
1
2
3
4

模糊查询

-- 使用 like关键字，”%”代表任意数量的字符，”_”代表占位符 
-- 查询名字为 m 开头的学生的信息 
SELECT * FROM `student` WHERE `name` LIKE 'm%'; 
-- 查询姓名里第二个字母为 a 的学生的信息 
SELECT * FROM `student` WHERE `name` LIKE '_a%';
1
2
3
4
5

分页查询

-- 分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用 
-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第 一条记录的参数为0。 
-- 查询第二条到第三条内容 
SELECT * FROM `student` LIMIT 1,2;
1
2
3
4

查询后排序

-- 关键字：order by field, asc:升序, desc:降序 
SELECT * FROM `student` ORDER BY `age` ASC; 
-- 按照多个字段排序 
SELECT * FROM `student` ORDER BY `age` DESC, `score` DESC;
1
2
3
4

聚合查询

SELECT sum(`age`) FROM `student`; 
SELECT avg(`age`) FROM `student`; 
SELECT max(`age`) FROM `student`; 
SELECT min(`age`) FROM `student`; 
SELECT count(`age`) FROM `student`;
1
2
3
4
5

分组查询

-- 分组加group_concat 
SELECT `sex`, group_concat(`age`) as ages FROM `student` GROUP BY `sex`; 
-- 可以把查询出来的结果根据某个条件来分组显示 
SELECT `sex` FROM `student` GROUP BY `sex`; 
-- 分组加聚合 
SELECT `sex`, count(*) as num FROM `student` GROUP BY `sex`; 
-- 分组加条件 
SELECT `sex`, count(*) as num FROM `student` GROUP BY `sex` HAVING num > 3;
1
2
3
4
5
6
7
8

MYSQL连表查询

数据准备

CREATE TABLE IF NOT EXISTS `dept` ( 
	`id` INT UNSIGNED AUTO_INCREMENT COMMENT '部门编号', 
	`name` VARCHAR (32) NULL COMMENT '部门名称', 
	PRIMARY KEY (`id`) 
) COMMENT = '部门表'; 

INSERT INTO `dept` (`name`) VALUES ('讲师');
INSERT INTO `dept` (`name`) VALUES ('助教'); 
INSERT INTO `dept` (`name`) VALUES ('推广'); 

CREATE TABLE IF NOT EXISTS `employee` ( 
	`id` INT UNSIGNED AUTO_INCREMENT COMMENT '员工编号', 
	`name` VARCHAR (32) NULL COMMENT '姓名', 
	`sex` TINYINT NOT NULL DEFAULT 1 COMMENT '性别(1:男;2:女)', 
	`age` TINYINT NULL COMMENT '年龄', 
	`salary` DECIMAL (8, 2) NULL DEFAULT 1000 COMMENT '薪水', 
	`dept_id` INT NOT NULL COMMENT '部门编号', 
	PRIMARY KEY (`id`) 
) COMMENT = '雇员表'; 

INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('vico', 1, 20, 10004, 1); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('mark', 1, 21, 10004, 1); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('king', 1, 22, 10004, 1); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('darren', 1, 23, 10004, 1); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('milo', 1, 24, 10000, 1); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('qiuxiang', 0, 18, 10000, 2); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('beibei', 0, 18, 10001, 2); 
INSERT INTO `employee` (`name`,`sex`,`age`,`salary`,`dept_id`) VALUES ('yiyi', 0, 19, 10001, 2);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

INNER JOIN

只取两张表有对应关系的记录

SELECT * FROM `employee` INNER JOIN `dept` ON `employee`.dept_id = `dept`.id;
1

LEFT JOIN

在内连接的基础上保留左表没有对应关系的记录

SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.id;
1

RIGHT JOIN

在内连接的基础上保留右表没有对应关系的记录

SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.id;
1

MYSQL子查询/合并查询

单行子查询

-- 知道部门名字，找该部门所有员工 
SELECT * FROM `employee` WHERE `dept_id` = (SELECT `id` FROM `dept` WHERE `name`='讲师'); 
-- 知道名字，找与它同部门的所有员工 
SELECT * FROM `employee` WHERE `dept_id` = (SELECT `dept_id` FROM `employee` WHERE `name`='mark');
1
2
3
4

多行子查询

多行子查询即返回多行记录的子查询

• IN 关键字：运算符可以检测结果集中是否存在某个特定的值，如果检测成功就执行外部的查询。
• EXISTS 关键字：内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为true时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。
• ALL 关键字：表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
• ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询中的，任意一个比较条件，就返回一个结果作为外层查询条件。在 FROM 子句中使用子查询：子查询出现在from子句中，这种情况下将子查询当做一个临时表使用。

-- IN 
SELECT `id`,`name`,`level`,`salary` FROM `employee` WHERE `level` IN (SELECT DISTINCT `level` FROM `employee` WHERE `dept_id`=3) AND `dept_id` <> 3;

-- EXISTS 
SELECT * FROM employee WHERE EXISTS(SELECT id FROM dept WHERE id = 9); 

-- ALL 
SELECT `name`,`salary`,`dept_id` FROM `employee` WHERE `salary` > ALL(SELECT `salary` FROM `employee` WHERE `dept_id`=2); -- ANY SELECT `name`,`salary`,`dept_id` FROM `employee` WHERE `salary` > ANY(SELECT `salary` FROM `employee` WHERE `dept_id`=2); 

-- FROM temp table 
SELECT `employee`.`name`, `employee`.`salary`, `employee`.`dept_id`, `max_salary` FROM `employee`, (SELECT max(`salary`) `max_salary`, `dept_id` FROM `employee` group by `dept_id`) `tmp` WHERE `employee`.`dept_id`=`tmp`.`dept_id` AND `employee`.`salary`=`tmp`.`max_salary`;
1
2
3
4
5
6
7
8
9
10
11