首页 / MYSQL / MySQL阶段二——sql语句基础（2）

MySQL阶段二——sql语句基础（2）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL阶段二——sql语句基础（2），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7210字，纯文字阅读大概需要11分钟。

内容图文

数据查询操作

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.嵌套查询

l  带有IN谓词的子查询

select Sno,Sname,Sdept from Student

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

 

l  带有比较运算符的子查询

select Sno, Cno from SC x

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

where y.Sno=x.Sno);

 

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

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

 

l  带有exists引出的子查询

select Sname from Student

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

 

05.集合查询

l  集合并

select * from Student where Sdept= ‘CS‘

UNION

select * from Student where Sage<=19

 

l  集合交

select * from Student where Sdept=‘CS‘

INTERSECT

select * from Student where Sage<=19

 

l  集合差

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.连接查询

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105830372.jpg" title="图片3.png" />  

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;

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105830895.jpg" title="图片4.png" />  

#外连接

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;

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105831201.jpg" title="图片5.png" />  

注意：多了一个孙武

 

04.连接过程

01）先拿左表数据依次与右表连接（左表第一条与右表第一条，左表第一条与右表第二条...）

02）判断条件 on，条件相等，保留结果。

03）循环1，2步

 

05.内连接

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

02）语法：tb_left inner join tb_right on 连接条件；

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

注意： MySQL中的crossjoin与innerjoin相同，但在数据库的定义上，交叉连接就是笛卡儿积，是没有条件的inner join。

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

 

04）有条件的内连接：

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

 

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

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

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

 

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

在数据过滤时（不是指的连接过滤）使用where。

 

注意：查询条件，与外连接通用（外连接，不能使用where作为连接条件）

 

Using条件进行连接：

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105831430.jpg" title="图片6.png" /> 技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105831776.jpg" title="图片7.png" />  

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

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105832277.jpg" title="图片8.png" />  

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

);

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105832708.jpg" title="图片9.png" />  

 

注意：连接时，支持多表连接

08.练习1

01）表

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105833276.jpg" title="图片10.png" />  

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

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（重要面试题）

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

技术分享650) this.width=650;" src="/upload/getfiles/default/2022/11/4/20221104105833561.jpg" title="图片11.png" />  

查询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查询的是字段上的组合


本文出自 “秦斌的博客” 博客，谢绝转载！

原文：http://qinbin.blog.51cto.com/11773640/1953415

内容总结

以上是互联网集市为您收集整理的MySQL阶段二——sql语句基础（2）全部内容，希望文章能够帮你解决MySQL阶段二——sql语句基础（2）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1243329.html

来源：【匿名】