mysql查询

数据库：studentmanager
表：classinfo(班级信息表),student(学生信息表),score(成绩表),subject(课程信息表)
单表查询：
查询所有学生信息

-- select 列 from 表名
select * from student;

– 查询所有学生信息，只显示姓名和生日

select name,birthday from student;

– 查询所有学生都在哪些班级中,DISTINCT对查询结果去重。

select DISTINCT cid from student;

– 所有学生的成绩+10分
– 别名关键词用as

select socre+10 as 成绩加10分 from score;
select score/10 成绩除以10 from score;

– where
– 查询学号是4的学生

select * from student where stuid=4;

BETWEEN AND 在两个值之间
– 查询出生日期在1995-07-14 到 1995-07-16出生的学生信息

select * from student where birthday BETWEEN '1995-07-14' and '1995-07-16';

– in ：在什么里面
select *from student where birthday in (‘1995-07-14’,‘1995-07-15’,‘1995-07-16’);

select * from student where birthday >='1995-07-14' and birthday<='1995-07-16';

– not BETWEEN AND :查询出生日期不在1995-07-14 到 1995-07-16出生的学生信息
select* from student where birthday not BETWEEN ‘1995-07-14’ and ‘1995-07-16’;

– like: 像 模糊查询

-- 查询姓张的学生信息
select * from student where name like '张%';
-- 查询以张结尾的学生信息
select * from student where name like '%张';
-- 查询姓名中包含四的学生信息
select * from student where name like '%四%';

– _:代表一个字符

select * from student where name like '张_';

– AND

-- 查询性别是男并且班级编号是1的学生编号
select *from student where sex='男' and cid=1;
-- 查询性别是男或者班级编号是1的学生编号
select *from student where sex='男' or cid=1;

聚合函数：sum求和，avg平均值，count计算记录条数，max最大值，min最小值

-- 查询学号为1的学员的平均成绩 AVG 
select avg(socre) as 平均成绩 from score where stuid=1;

-- COUNT(expr) ：统计参加了科目一考试的学生数量
select COUNT(stuid) as 学生人数 from score where subid=1;

-- 查询科目一考试的所有学生成绩总和
select SUM(socre) as 成绩总和 from score where subid=1;

-- 查询科目一最高分和最低分 MAX(expr) MIN(expr)
select MAX(socre) 最高分,MIN(socre) 最低分 from score where subid=1;

分组：group by
– 1. 查询有哪些科目

select DISTINCT subid from score;

– 2. 根据每个科目编号查询每个科目的平均成绩

select avg(socre) from score where subid=1;

– 这样太麻烦
– GROUP BY:一般用在统计场合，单独来用没什么太大意义，一般配合五大聚合函数来使用

select subid,avg(socre) from score 
GROUP BY subid,subid;

排序：order by 默认升序（asc） ，降序（desc）
查询学生信息并按照班级编号排序 ,默认升序（asc） 降序（desc）

select * from student  ORDER BY cid asc;
-- 可以根据多个字段进行升序
select * from student ORDER BY birthday,cid asc;

LIMIT(数据的坐标，显示多少条) 数据库坐标从0开始
– 查询年龄最大的五名学生的信息
– 1.根据生日进行升序 ASC
– 2.根据查询结果取前五名

select * from student ORDER BY birthday
LIMIT 0,5;

常用函数：

-- 获取当前日期
select CURRENT_DATE();
-- 获取当前时间
select CURRENT_TIME();

– 获取当前时间，年月日时分秒，yyyy-MM-dd : HH:mm:ss

select NOW();

– 连接字符

select CONCAT(name,'+',sex) from student;

– 大小写转换

select UCASE(name) from student;
select LCASE(name) from student;

– 获取字符串长度

SELECT LENGTH(name) from student;

– 截取字符串

select SUBSTRING(name,1,2) from student;

– 类似三元运算符：if

SELECT scoid,socre,IF(socre>=60,'及格','不及格') AS 等级 from score;

查询语句语法顺序：
编写顺序：

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …limit

执行顺序：
WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

连接方式：等值连接，内连接，外连接

等值连接：
获取姓名为jim的学生所在的班级，显示学生编号
– 1.把两张表链接成一张大表
– 2.确定两个表的链接字段
– 3.显示哪些列
– 4.确定其他筛选条件

select stuid,name,student.cid,cname from student,classinfo 
where student.cid=classinfo.cid and name='jim';

内连接：
INNER JOIN ：内连接

-- 获取姓名为jim的学生所在的班级，显示学生编号
select stuid,name,student.cid,cname from student INNER JOIN classinfo
-- 2.确定链接条件
ON student.cid=classinfo.cid
-- 3.确定删选条件
where name='Jim';

外连接：
左外连接：以左表为准，去右表找匹配的数据，如果找不到匹配，用null填充
– 右外链接：以右表为准，去左表找匹配的数据，如果找不到匹配，用null填充
– 内链接：左表与右表交际，匹配成功，拿出来，不成功丢掉。

-- 查询结果保留学生ID、姓名、性别、课程ID、成绩
select student.stuid,name,sex,subid,socre from student LEFT JOIN score
on student.stuid=score.stuid;
-- 左右链接可以互相转换,建议把右链接转换成左链接；因为DB2不支持右链接。
select student.stuid,name,sex,subid,socre from score right JOIN student
on student.stuid=score.stuid;

子查询:把内层查询结果供外层使用，先做内层再做外层。

-- 查询比tom小的所有学生的信息
-- 找出tom的出生日期
select birthday from student 
where name='tom';
-- 比较
select * from student
where birthday>'1995-07-14';

– 合并

select * from student
where birthday>
(select birthday from student 
where name='tom');

– 查询比"16级计科一班"所有"Java"成绩都高的"16级计科二班"的学生信息

select student.*,socre from score,student,classinfo,subject
where student.cid=classinfo.cid 
and student.stuid=score.stuid 
and score.subid=subject.subid
and subname='Java' and cname='16级计科二班'
and socre>all
(select socre from score,student,classinfo,subject
where student.cid=classinfo.cid 
and student.stuid=score.stuid 
and score.subid=subject.subid
and subname='Java' and cname='16级计科一班');

in和not in 用法：
SELECT 列名
FROM 表名
WHERE 列名 [NOT] IN（子查询）

– in

-- 查询参加考试的学生考试信息
select* from student
where stuid in
(
select stuid from score 
);

exists :把外层查询结果拿到内层去比较，如果成立则返回true,不成立返回false。
– 查询参加了考试的学生的信息

select * from student 
where EXISTS
(
select stuid from score 
where student.stuid=score.stuid
);

in，not in 和exists,not exists 区别：
IN/NOT IN先执行子查询，子查询返回的是一个
集合，然后再将子查询的结果作为外层查询的条
件进行过滤。
EXISTS/NOT EXISTS先执行外层查询，再将外层
查询的每一条记录作为条件进行子查询，子查询
返回的只是返回一个TRUE或FALSE，因此一般情况
下子查询中直接使用SELECT 1提高效率。