MySQL阶段二——sql语句基础(2)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

数据查询操作

    01.创建数据表

    (02-05练习)

    (连接查询练习使用)

    02.单表查询

    03.分组统计

    04.嵌套查询

    05.集合查询

    06.连接查询

    07.连接查询与集合查询的不同


数据查询操作

01.创建数据表

    1)创建Student

    2)创建Course

    3)创建SC

02-05练习

create table student

(

   sno char(8) primary key,

   sname char(8),

   ssex char(2)not null,

   sage int,

   sdept char(20)

);

create table course

(

   cno char(4) primary key,

   cname char(40)not null,

   cpno char(4),

   ccredit smallint,

);

create table sc

(

   sno char(8)foreign key(sno)references student(sno),

   cno char(4),

   grade smallint

);

 

连接查询练习使用

CREATE TABLE `join_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_name` char(7) DEFAULT NULL,
  `room` char(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `join_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(10) DEFAULT NULL,
  `gender` enum('male','female','secret') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

CREATE TABLE `join_teacher_class` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `t_id` int(11) DEFAULT NULL,

  `c_id` int(11) DEFAULT NULL,

  `days` tinyint(4) DEFAULT NULL,

  `begin_date` date DEFAULT NULL,

  `end_date` date DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

 

02.单表查询

01)按照目标列查询

select sno,sname,sage from student;

 

02)目标列包含表达式查询

select sname,'year of birth:',2014-sage,lower(sdept) from student;  

 

03)查询结果集中修改列名称

select sname,'year of birth:'as birth,2000-sage BIRTHDAY,DEPARTMENT=lower(sdept) from student;

 

04)取消重复行

select sno from sc;

select DISTINCT sno from sc;

 

05)简单条件查询

select sname from student where sdept='cs';

 

06)按范围查询

select sname,sdept,sage from student where sage between 20 and 23

 

07)查询属性值属于指定集合的行

select sname,sno,ssex from student where sdept in('IS','MA','CS');

 

08)模糊查询

select sname,sno,ssex from student where sname like'%'

 

09)查询空值

select sno,cno from sc where grade is null;

 

10)结果集排序

select * from student order by sdept,sage desc;

升序|降序(asc|desc

校对规则决定排序关系

允许多字段排序,先找第一个字段排序,如果不能区分,再按照第二个字段排序,以此类推。

注:如果是分组,则应该使用分组字段进行排序的group by语法。

 

11)多重条件查询

select sno,sname from student where sdept='IS'and sage>23

 

12)limit语法

限制获取的记录数量,放在最后。

语法:limit offset,row_count

Offset:偏移量(类似数组下标,索引位从0开始)

Row_count:总记录数,如果数量大于,则获取余下的。

 

03.分组统计

01)聚集函数的使用

Count(*)            统计元组的个数

Count([distinct|all] 列名)    统计一列中值的个数

Sum([distinct|all] 列名)    计算一列值的总和(此列必须是数值型)

Avg([distinct|all] 列名)    计算一列值的平均值(此列必须是数值型)

Max([distinct|all] 列名)    求一列值中的最大值

Min([distinct|all] 列名)    求一列值中的最小值

 

select count(*) from student;

select count(distinct sno)from sc

select max(grade) from sc

 

select sum(grade) 总分,avg(grade) 均分,max(grade) 最高分

from sc group by cno

 

02)分组统计

如果分组后还要求按一定的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用having短语指定筛选条件。

Where子句和having短语的区别在于作用对象不同。Where子句作用与基本表或视图,从中选择满足条件的元组。Having短语作用与组,从中选择满足条件的组。

 

select cno 课程号,count(*) 人数,avg(grade) 均分,max(grade) 最高分

from sc group by cno

having avg(grade)>90

注意:where子句中是不能用聚集函数作为条件表达式的,这里条件表达式用having

 

04.嵌套查询

带有IN谓词的子查询

select Sno,Sname,Sdept from Student

where Sdept IN ( select Sdept from Student where Sname= '刘晨');

 

带有比较运算符的子查询

select Sno, Cno from SC x

where Grade >= ( select AVG(Grade) from SC y

where y.Sno=x.Sno);

 

带有修饰符的比较运算符引出的子查询

select Sname,Sage from Student

where Sage < ALL ( select Sage from Student where Sdept= 'CS')

AND Sdept <> 'CS' ;

 

>any <any >=any <=any

>all<all>=all <=all

 

带有exists引出的子查询

select Sname from Student

where EXISTS (select * from SC where Sno=Student.Sno AND Cno= '1')

 

05.集合查询

集合并

select * from Student where Sdept= 'CS'

UNION

select * from Student where Sage<=19

 

集合交

select * from Student where Sdept='CS'

INTERSECT

select * from Student where Sage<=19

 

集合差

select * from Student where Sdept='CS'

EXCEPT

select * from Student where Sage <=19;

 

注意:

规定,多个select语句的检索到的字段数,必须一致。

更加严格的是,数据类型上,应该也有要求一致;但是,MySQL内部会做类型转换处理,要求是能够转换成功;

 

提示:

在语句的括号不是必须的。

检索结果中的列名称问题:第一条select语句的列名而定。

排序:

子语句结果的排序:

01.将子语句包裹子括号内

02.子语句的order by

只有在order bby配合limit是才生效。原因是:union在做子语句时,会对limit子句的order by优化(忽略)。

 

06.连接查询

wKioL1mDCi_iRu6OAALKl0HQgJU450.png-wh_50 

01.连接概述

每个实体,一个表,一个业务逻辑,使用多个实体的数据,多张表应该一起使用,将多个表的记录连接起来。

02.总体思路

将所有的数据,按照某种条件,连接起来 ,再进行筛选处理。

03.连接的分类

根据连接的条件不同,分类如下:

内连接:数据内部的连接,要求,连接的多个数据都必须存在才能进行连接。

外连接:如果负责连接的一个或多个数据不真实存在,则称为外连接。

自然连接

内外连接的区别:

#内连接

SELECT join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days

FROM join_teacher INNER JOIN join_teacher_class

ON join_teacher.id=join_teacher_class.t_id;

wKiom1mDCjCCamHaAAAihmOC1UI198.png-wh_50 

#外连接

SELECT join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days

FROM join_teacher LEFT OUTER JOIN join_teacher_class

ON join_teacher.id=join_teacher_class.t_id;

wKiom1mDCjCRhAC0AAAtRZKp6qQ958.png-wh_50 

注意:多了一个孙武

 

04.连接过程

01)先拿左表数据依次与右表连接(左表第一条与右表第一条,左表第一条与右表第二条...

02)判断条件 on,条件相等,保留结果。

03)循环12

 

05.内连接

01)数据内部的连接,要求,连接的多个数据都必须存在才能进行连接。

02)语法:tb_left inner join tb_right on 连接条件;

03)内连接的处理:内连接,在连接的时候,是可以忽略连接条件的。意味着,所有的左表的数据,都要与右表的记录做一个连接。共存在m*n个连接。这种连接,就称之为,交叉连接,或者笛卡儿积连接。此时可以使用cross join代替inner join

注意:MySQL中的crossjoininnerjoin相同,但在数据库的定义上,交叉连接就是笛卡儿积,是没有条件的inner join

MySQL inner join是默认的连接方案,可以省略inner

 

04)有条件的内连接:

会在连接时过滤非法的连接。

 

Where的写法:数据过滤,理解上,数据安装交叉连接完成后,再做数据过滤。

On的写法:在连接时,就对数据进行判断。

Using的写法:using要求,负责连接的两个实体之间的字段名称一致。

 

建议是,在有同名字段时,使用using,而在通用条件的时候,使用on

在数据过滤时(不是指的连接过滤)使用where

 

注意:查询条件,与外连接通用(外连接,不能使用where作为连接条件)

 

Using条件进行连接:

wKioL1mDCjGwpCtLAADLIaji00E497.png-wh_50wKioL1mDCjHTZw4KAAGc4hy0nQ0748.png-wh_50 

05)多种组合连接,查询结果相同但是逻辑不同

wKiom1mDCjOiGF6LAAUvXCaXwqU010.png-wh_50 

06.外连接

如果负责连接的一个或多个数据不真实存在,则称为外连接。

01)分类

左外连接

右外连接

全外连接(MySQL暂时不支持)

02)左外连接(left outer join:outer可以省略)

在连接的时候,如果出现左边表数据连接不到右边表的情况,则左边表的数据在最终结果中保留,右边表采用虚拟数据。如果 出现右边表数据连接不到左边表数据,右边表数据被丢弃。

03)右外连接(right join

与左外连接相反。

07.自然连接

通过MySQL自己的判断完成连接过程:

不需要指定连接条件。MySQL会使用多表内的,相同字段,作为连接条件。

 

分类:自然连接分成内外之分:

内,natural join

 

外,左外,右外

Natrual left join

Natural right join

例:

create table one(

One_id int,

Ont_data char(1),

Public_filed int

);

Create table two(

Two_id int,

Two_data char(1) not null default t,

Putblic_field int

);

wKioL1mDCjSACGxIAAD9cAkehOE334.png-wh_50 

 

注意:连接时,支持多表连接

08.练习1

01)表

wKiom1mDCjSyiV4sAABtE8Z04XE156.png-wh_50 

查询某个班级所有学生的全部信息;

Select s.*,si.* from   info_class as c   left join   info_student as s   on c.id=s.clsss_id

Left join   info_student_info as si   on s.id=si.id where c.class_name=php;

 

09.练习2(重要面试题)

上面两个是表,下面是要得到的结果

wKioL1mDCjXS_F5WAACnIgQshOk100.png-wh_50 

查询4月份所有的比赛结果:

Match left join class on match.host_id=class.id left join class on match.guest_id=class.id

010.练习3

Subquery(子查询)+join

 

Id

Dept_name

Parent_id

1

行政部

0

2

教学部

0

3

PHP

2

4

Java

2

5

.net

2

6

平面

2

7

咨询

1

8

财务

1

 

任务一:获得所有的顶级部门

任务二:获得教学部门的所有子部门

 

07.连接查询与集合查询的不同

Union查询的时记录之间的组合

Join查询的是字段上的组合



本文转自 叫我北北 51CTO博客,原文链接:http://blog.51cto.com/qinbin/1953415

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
20 0
|
1天前
|
SQL 存储 数据处理
实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 1
|
1天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
3天前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】常用SQL
常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。 在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。
204 6
|
3天前
|
SQL 关系型数据库 MySQL
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)
【MySQL-4】简述SQLの通用语法及4种基本语句介绍(DDL/DML/DQL/DCL)
|
22小时前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
16 1
|
1天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
11 0
|
3天前
|
SQL XML Linux
SQL Server的版本
【5月更文挑战第14天】SQL Server的版本
18 3
|
3天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
15 2

推荐镜像

更多