MySql DQL查询数据 (4)

4.1 DQL

（Date query language：数据库查询语言）

• 所有的查询操作都用他 query

• 简单的查询，复杂的查询都可以做

• 数据库最核心的语言，最重要的语言

• 使用频率最高的语言

Select 完整表达式：

SELECT
  [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STARIGHT_JOIN]
    [SQL_SMALL_RESULT][SQL_BIG_RESULT][SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE][SQL_CALC_FOUND_ROWS]
    SELECT_RXPR [,SELECT_RXPR...]
    [FROM table_references
      PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
     [ACS |DESC], ....[WITH ROLLUP]]
    [HAVING where_condition] 
    [GROUP BY {col_name | expr | position}
     [ACS |DESC], ....[WITH ROLLUP]]
    [LIMIT [OFFSET,] ROW_COUNT | ROW_COUNT offset offset]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'fileName'
      [CHARCTER SET charset_name]
      export_options
      | INFO DUMPFILE 'fileName'
      | INFO var_name[,var_name]]
    [for UPDATE | LOCK IN SHARE MODE]

 

4.2 指定查询字段

-- 查询全部学生   select 字段 from 表
select * from `student`;
​
-- 查询指定字段
select id,name from `student`;
​
-- 别名，给结果起一个别名。as 或者空格
select id as 序号,name as 名称 from `student`;
​
-- 函数concat(a,b)
select concat('姓名：',name) as 新名字 from `student`;

语法 ： select 字段1，字段2... from 表；

有的时候列明不能见名知意，我们起别名 as 字段名 as 别名，表名 as 别名

 

去重 distinct

作用：去除select 中查询出来的重复的数据，重复的数据只显示一条

-- 查询一下都有哪些同学参加了考试，成绩
select * from result; -- 查询结果
select student_name from result; -- 查询参加考试的人
select distinct student_name from result; -- 查询去除重复后参加考试的人

表达式

select version();  -- 查询系统版本
select 100*3-1 as 结果; -- 用来计算
select @@auto_increment_increment  --查看自增的步长
​
-- 学员考试成绩全部+1
select student_name, student_result + 1 as 成绩 from result;

数据库中的表达式：文本值，列，NULL，函数，计算表达式，系统变量......

SELECT 表达式 from 表;

4.3 where 条件子句

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

搜索得条件由一个或多个表达式组成，结果为布尔值。

逻辑运算符

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

尽量使用英文字母！

-- =============where ==================
select student_name from result;
​
-- 查询成绩95-100分之间
select student_name from result where student_result>95 and student_result<100;
​
-- and  &&
select student_name from result where student_result>95 && student_result<100;
​
-- 模糊查询（区间）
select student_name from result where student_result bewteen 95 and 100;
​
-- 除了学号1000的学生
select student_name from result where student_result != 1000;
​
-- !=   not
select student_name from result where not student_result = 1000;

模糊查询 ：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
BETWEEN	a between b and c	在b和c之间的为真
LIKE	a like b	sql匹配，如果a like b则为真
IN	a in (b,c,d...)	假设a在b,c,d之间则为真，否则为假

-- ==============模糊查询===================
-- 查询姓刘的同学
select student_name from result where student_name like '刘%';
​
-- 查询姓刘的同学,后面只有一个字的
select student_name from result where student_name like '刘_';
​
-- 查询姓刘的同学,后面只有两个字的
select student_name from result where student_name like '刘__';
​
-- 查询中间有'美'的同学
select student_name from result where student_name like '%美%';
​
-- =================in======================
-- 查询 1，2，3好学员
select student_name from result where student_id in (1,2,3);
​
-- 查询在北京的学员
select student_name from result where address='' or address in ('北京');
​
-- ===============null   not null=============
-- 查询地址为空的学生
select student_name from result where address is null;
​
-- 查询有出生日期的学生
select student_name from result where birthday is not null;
​
-- 查询没有出生日期的学生
select student_name from result where birthday is null;

4.4 联表查询

查询原理图：

-- =================联表查询   join===================
-- 查询参加了考试的同学（学号，姓名，科目编号，成绩）
select * from result;
select * from student;
​
/* 思路：
  1.分析需求，分析都来自哪个表
  2.确定要使用哪种连接方式？ 7种
  确定交叉点（两个表中哪些数据是相同的）
  判断的条件：学生表中的studentNo = 成绩表中的studentNo
*/
​
select s.studentNo,studentName,subjectNo,StudentResult
from student s inner join result r on s.studentNo = r.studentNo;
​
-- right join
select s.studentNo,studentName,subjectNo,StudentResult
from student s right join result r on s.studentNo = r.studentNo;
​
--left join 
select s.studentNo,studentName,subjectNo,StudentResult
from student s left join result r on s.studentNo = r.studentNo;

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

自连接

自己表和自己表连接，核心：一张表拆成两张表即可。

父类：

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

子类

pid	categoryId	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

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

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

-- 查询父类信息：把一张表看成两张一摸一样的表
select a.categoryName, b.categoryName from category a ,category b
where a.categoryId = b.pid;

4.5 分页和排序

排序

-- 排序：升序ASC，降序 DESC
-- BRDER BY 通过这个字段进行排序
-- 查询成绩结果，根据降序排序
select s.studentNo,studentName,SubjectName,studentResult
from student s inner join result r on s.studentNo = r.studentNo
where SubjectName = '信息技术'
order by studentResult ASC;

分页

-- 100w
-- 为什么要分页？
-- 缓解数据库压力，给人更好的体验，瀑布流
​
-- 分页，每页只展示5条数据
-- 语法：limit 起始值，页面大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0，5   1-5
-- LIMIT 1，5   2-6
-- LIMIT 6，5
select s.studentNo,studentName,SubjectName,studentResult
from student s inner join result r on s.studentNo = r.studentNo
where SubjectName = '信息技术'
order by studentResult ASC
limit 5,5;
-- 第一页 LIMIT 0，5   （1-1）*5
-- 第二页 LIMIT 5，5   （2-1）*5
-- 第三页 LIMIT 10，5  （3-1）*5
-- 第N页  LIMIT N，5   （N-1）*5
-- 【pageSize,页面大小】
-- 【(n-1)*pagesize：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小=总页数】

4.6 子查询

where（这个值是计算出来的）

-- 1.查询'信息技术'所有考试结果
-- 方式一：你用连接查询
select s.studentNo,studentName,SubjectName,studentResult
from student s inner join result r on s.studentNo = r.studentNo
where SubjectName = '信息技术'
order by studentResult ASC；
​
-- 方式二：使用子查询(由里及外)
select s.studentNo,studentName,SubjectName,studentResult
from result r 
where SubjectNo = (
  select SubjectNo from Subject sub where SubjectName = '信息技术'
)
​
-- 分数不小于80分的学员的学号和成绩
select s.studentNo,studentName,SubjectName,studentResult
from student s inner join result r on s.studentNo = r.studentNo
where studentResult >= 80;
​
-- 分数不小于80分的学员的学号和成绩
-- 查询'信息技术'
select s.studentNo,studentName,SubjectName,studentResult
from student s inner join result r on s.studentNo = r.studentNo
where studentResult >= 80 and SubjectName = (
  select SubjectName from Subject sub where SubjectName = '信息技术'
)
​
-- 改造
select studentNo,studentName from student where studentNo = (
  select studentNo from result where studentResult >= 80 and SubjectName = (
  select SubjectName from Subject sub where SubjectName = '信息技术'
  )
)

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80的学生
-- 核心：（根据不同的课程进行分组）
select studentName,
AVG(`studentResult`) AS 平均分,
MAX(`studentResult`) AS 最高分,
MIN(`studentResult`) AS 最低分
from student s inner join result r on s.studentNo = r.studentNo
group by r.studentNo
having 平均分>80;

4.8 select 小结