MySQL数据查询

1.select查询语句

• 语法：select 字段名1,字段名2 from 表

  -- 查询所有学生
  select * from `student`
  
  -- 查询指定字段
  select `name`,`pwd` from `student`
  

• 别名，as可以给字段起名，也可以给表取名

  select `name` as 姓名,`pwd` as 密码 from `student` as s
  

• 函数 concat(a,b)，拼接字符串在查询结果里

  select concat('姓名：',name) as 新名字 from `student`
  

• 去重：去除select查询出来的结果中重复的数据

  select distinct 字段名 from 表名
  

• 查看系统版本

  select version()
  

• 用来计算

  select 100*3-1 as 计算结果
  

• 查询自增的步长

  select @@auto_increment_increment
  

• 学生年级全部+1

  select `name`,`gradeid`+1 as 新年级 from `student`
  

2.where条件子句

• 作用：检索数据中符合条件的值

• 逻辑运算符（尽量使用英文，少用符号）

  运算符	语法	描述
  and或&&	a and b 或 a&&b	逻辑与，两个为真，则结果为真
  or或||	a or b 或 a||b	逻辑或，其中一个为真，则结果为真
  not或!	not a 或 !a	逻辑非，真为假，假为真

  -- 查询成绩在95到100之间的学生
  select `studentName`,`score` from `result` where `score` >= 95 and `score` <= 100;
  select `studentName`,`score` from `result` where `score` >= 95 && `score` <= 100;
  select `studentName`,`score` from `result` where `score` between 95 and 100;
  
  -- 查询除小红外所有人的成绩
  select `studentName`,`score` from `result` where `studentName` != '小红';
  select `studentName`,`score` from `result` where not `studentName` = '小红';
  

• 模糊查询：比较运算符

  运算符	语法	描述
  is null	a is null	如果操作符为null，结果为真
  is not null	a is not null	如果操作符为not null，结果为真
  between…and…	a between b and c	若a在b和c之间，则结果为真
  like	a like b	SQL匹配，若a匹配到b，则结果为真
  in	a in(a1,a2,a3…)	假设a在a1或者a2…其中的某一个值中，结果为真

  -- 查询姓刘的同学
  -- like结合 %(代表0到任意个字符) _(一个字符)
  select * from `student` where `name` like '刘%';
  -- 查询姓刘的后面有两个字的同学
  select * from `student` where `name` like '刘__';
  
  --查询id为1001、1002、1003、1004的学生
  select * from `student` where `id` in (1001,1002,1003,1004);
  
  -- 查询地址为空的学生
  select * from `student` where `address` = '' or `address` is null;
  
  -- 查询出生日期不为空的学生
  select * from `student` where `birthday` != '' or `birthday` is not null;
  

3.联表查询join

例1：查询出学生的学号、姓名、科目id和成绩

前提：result表中有学生学号、成绩和科目id，student表中有学生学号和姓名

方法一：inner join

-- as可以省略
select s.`studentNo`,`studentName`,`subjectNo`,`source` 
from `student` as s  
inner join `result` as r
on s.`studentNo` = r.`studentNo`

方法二：right join

select s.`studentNo`,`studentName`,`subjectNo`,`source` 
from `student` as s  
right join `result` as r
on s.`studentNo` = r.`studentNo`

left join：查出未参加考试的同学

select s.`studentNo`,`studentName`,`subjectNo`,`source` 
from `student` as s  
left join `result` as r
on s.`studentNo` = r.`studentNo`
where `source` is null

• join 连接的表 on 判断的条件 （连接查询）
• where （等值查询）
• 对比

操作	描述
inner join	如果表中至少有一个匹配，就可以返回匹配的值
right join	会从右表中返回所有的值，即使左表中没有匹配
left join	会从左表中返回所有的值，即使右表中没有匹配

例2：查询出学生的学号、姓名、科目名和成绩

前提：result表中有学生学号、成绩和科目id，student表中有学生学号和姓名，subject表中有科目id和科目名

select s.`studentNo`,`studentName`,`subjectName`,`score`
from `student` s
right join `result` r
on r.`studentNo` = s.`studentNo`
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`

自连接

category表：pid为父类id，categoryid为自身id

categoryid	pid	categoryName
3	1	软件开发
5	1	美术设计
4	3	数据库
8	2	办公信息
2	1	信息技术
6	3	web开发
7	5	ps技术

自连接就是自己的表和自己的表连接，核心：一张表拆为两张表即可

父类表：pid为1就是顶级父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类表：子类pid=父类categoryid，即该父类包含该子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

select a.`categoryName` as '父类',b.`categoryName` as '子类'
from `category` as a, `category` as b
where a.`categoryid` = b.`pid`

4.分页和排序

排序：升序asc ，降序desc

• 语法：select 字段名1,字段名2 from 表 order by 排序条件 asc|desc

  select * from `result` order by `score` desc
  

分页

• 语法：select 字段名1,字段名2 from 表 limit 起始值（从零开始）,页面大小

  select * from `result` limit 0,5
  

• 程序中的分页代码

  • (n-1)*pageSize：起始值
  • pageSize：页面大小
  • n：当前页
  • Math.ceil(数据总数/页面大小)=总页数：向上取整（或者三目运算判断）

5.子查询

• 本质：在where语句中嵌套一个子查询语句

  例：查询数据库结构-1所有考试的结果（学号、科目id、成绩），降序排列

  方式一：使用联表查询

  select `studentNo`,r.`subjectNo`,`score`
  from `result` r
  inner join `subject` sub
  on r.`subjectNo` = sub.`subjectNo`
  where `subjectName` = '数据库结构-1'
  order by `score` desc
  

  方式二：使用子查询（由里及外）

  select `studentNo`,`subjectNo`,`score`
  from `result`
  where `subjectNo` = (
      select `subjectNo` from `subject`
  	where `subjectName` = '数据库结构-1'
  ) order by 'score' desc
  

6.分组和过滤

• group by：指定结果按照哪几个字段来分组

• having：过滤分组的记录必须满足的次要条件

  例：查询不同课程的平均分、最高分、最低分，且平均分要大于等于80

  select `subjectName`,avg(`score`),max(`score`),min(`score`)
  from `result` r
  inner join `subject` sub
  on r.`subjectNo` = sub.`subjectNo`
  group by r.`subjectNo`
  having avg(`score`) >= 80