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`) 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