- 建表语句和测试数据:
create table `t_student` (
`id` int ,
`stuName` varchar (60),
`age` int ,
`sex` varchar (30),
`gradeName` varchar (60)
);
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张三','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张三丰','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','李四','23','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','王五','22','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','珍妮','21','女','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李娜','26','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','王峰','20','男','三年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','梦娜','21','女','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','小黑','22','男','一年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','追风','25','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小小张三','21',NULL,'二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小张三','23','男','二年级');
insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','张三锋小','24',NULL,'二年级');
查询所有学生数据:
1.select id,stuName,age,sex,gradeName from t_student;
或:
2:select * from t_student;
查询指定字段(查询所有学生的姓名和年龄):
select stuName,age from t_student;
Where 条件查询(查询年龄大于22岁的所有学生):
select * from t_student where age>22;
IN 关键字查询(查询年龄在21和23的学生)
select * from t_student where age in (21,23);
NOT IN 关键字查询(查询年龄不在21和23的学生)
select * from t_student where age not in (21,23);
带有BETWEEN AND范围查询(查询年龄在21到24之间的所有学生信息):
select * from t_student where age between 21 and 24;
带有NOT BETWEEN AND范围查询(查询年龄不在21到24之间的所有学生信息):
select * from t_student where age not between 21 and 24;
带 LIKE 的模糊查询:
select * from t_student where stuName like '张三';
1.查询姓名为张三开头的学生(% 代表任意字符):
select * from t_student where stuName like '张三%';
2.查询姓名为张三开头的学生,且名字只有三位的学生信息( _ 代表一位字符,四个字就多加一个 ):
select * from t_student where stuName like '张三_’;
3.查询姓名包含 张三 关键字的学生:
select * from t_student where stuName like '%张三%';
空值查询:
查询性别是(不是) null 的学生信息:
select * from t_student where sex is (not)null;
带 AND 的多条件查询:
查询一年级且年龄为23岁的学生信息:
select * from t_student where gradeName = '一年级' and age = 23;
带 OR 的多条件查询:
查询是 一年级 或者 年龄为23的 所有学生:
select * from t_student where gradeName = '一年级' or age = 23
去重复(DISTINCT)查询:
查询所有年级:
select distinct gradeName from t_student;
对查询结果进行排序(ORDER BY):
select * from t_student order by age asc; asc:升序
select * from t_student order by age desc; desc:降序
GROUP BY 分组查询:
- group by 单独使用毫无意义,分组出来的只会显示一条数据,一般配合函数使用。
- 与 group_concat()一起使用(查询各个年级的所有学生姓名):
select gradeName,group_concat(stuName) from t_student group by gradeName;
- 与聚合函数一起使用:(查询各年级的学生总数) count() 总数
select gradeName,count(stuName) from t_student group by gradeName
- 与HAVING 一起使用(限制输出的结果)(查询各年级学生总数大于3的结果信息)
select gradeName,count(stuName) from t_stuName group by gradeName having count(stuName) > 3;
- 与 with rollup 一起使用(新增一行,对查询结果进行总计,文本就添加所有文本信息到一行); select gradeName count(stuName) from t_student group by gradeName with rollup; select gradeName,group_concat(stuName) from t_student group by gradeName with rollup;
- 查询各年级中学生的id,名字及年龄,且按照id升序排列:
select gradeName,GROUP_CONCAT(CONCAT_WS('|',id,stuName,age)ORDER BY id asc)
from t_student GROUP BY gradeName
分页查询:
每页查询5条数据:
select * from t_student limit 0,5 第一页
select * from t_student limit 5,5 第二页
select * from t_student limit 10,5 第三页
聚合函数:SQL中提供的聚合函数可以用来统计、求和、求最值等等。
建表语句:
CREATE TABLE `t_grade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`stuName` varchar(60) DEFAULT NULL,
`course` varchar(60) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8测试数据:
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','张三','数学','90');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','张三','英语','87');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','语文','79');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','数学','95');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英语','80');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','语文','77');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','数学','81');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英语','89');
–COUNT:统计行数量
//统计总记录数:
SELECT COUNT(*) AS total FROM t_grade
//统计出根据学生姓名,查出每个学生考了几门课程
SELECT stuName,COUNT(*) AS '参与的课程数' FROM t_grade GROUP BY stuName
–SUM:获取单个列的合计值
//求和:求出张三的总成绩
SELECT stuName,SUM(score) AS '总成绩' FROM t_grade WHERE stuName = '张三';
//根据学生姓名分组,查询出每个学生的总分
SELECT stuName,SUM(score) AS '总成绩' FROM t_grade GROUP BY stuName
–AVG:计算某个列的平均值
//求张三的平均分
SELECT stuName,AVG(score) AS '平均分' FROM t_grade WHERE stuName = '张三';
//根据学生分组求出各组学生的平均分
SELECT stuName,AVG(score) AS '各组平均分' FROM t_grade GROUP BY stuName ORDER BY AVG(score) ASC
–MAX:计算列的最大值
//求张三的单门课程最高分
SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName = '张三';//根据学生姓名分组 查询出单门课程的最高分
SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName
–MIN:计算列的最小值
//求张三的单门课程最低分
SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName = '张三';//根据学生分组求各组学生的最低分
SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName