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);
基础查询
-- 显示当前数据库全部table
SHOW TABLES;
-- 全部查询
SELECT * FROM student;
-- 只查询部分字段
SELECT `name`, `age` FROM student;
-- 别名
SELECT `name` AS '姓名' , `age` AS '年龄' FROM student;
-- 把查询出来的结果的重复记录去掉
SELECT distinct `score` FROM student;
条件查询
-- 查询姓名为 mark 的学生信息
SELECT * FROM `student` WHERE `name` = 'mark';
-- 查询性别为 1,并且年龄为 22 岁的学生信息
SELECT * FROM `student` WHERE `sex`=1 AND `age`=22;
范围查询
-- 查询年龄在22到23岁的学生的信息
SELECT * FROM `student` WHERE age BETWEEN 22 AND 23;
判空查询
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 = ''; #判断为空字符串
模糊查询
-- 使用 like关键字,”%”代表任意数量的字符,”_”代表占位符
-- 查询名字为 m 开头的学生的信息
SELECT * FROM `student` WHERE `name` LIKE 'm%';
-- 查询姓名里第二个字母为 a 的学生的信息
SELECT * FROM `student` WHERE `name` LIKE '_a%';
分页查询
-- 分页查询主要用于查看第N条 到 第M条的信息,通常和排序查询一起使用
-- 使用limit关键字,第一个参数表示从条记录开始显示,第二个参数表示要显示的数目。表中默认第 一条记录的参数为0。
-- 查询第二条到第三条内容
SELECT * FROM `student` LIMIT 1,2;
查询后排序
-- 关键字:order by field, asc:升序, desc:降序
SELECT * FROM `student` ORDER BY `age` ASC;
-- 按照多个字段排序
SELECT * FROM `student` ORDER BY `age` DESC, `score` DESC;
聚合查询
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`;
分组查询
-- 分组加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;
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);
INNER JOIN
只取两张表有对应关系的记录
SELECT * FROM `employee` INNER JOIN `dept` ON `employee`.dept_id = `dept`.id;
LEFT JOIN
在内连接的基础上保留左表没有对应关系的记录
SELECT * FROM `employee` LEFT JOIN `dept` ON `employee`.dept_id = `dept`.id;
RIGHT JOIN
在内连接的基础上保留右表没有对应关系的记录
SELECT * FROM `employee` RIGHT JOIN `dept` ON `employee`.dept_id = `dept`.id;
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');
多行子查询
多行子查询即返回多行记录的子查询
- 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`;