首页 > 数据库 >五天搞定Mysql基础知识-Day04

五天搞定Mysql基础知识-Day04

时间:2024-06-11 16:29:19浏览次数:27  
标签:搞定 JOIN -- Day04 studentNo INNER Mysql 查询 SELECT

学习目标:

        1、掌握内连接

        2、掌握左连接和右连接

        3、掌握自关联和子查询·

第一章 数据准备

一、创建表,并向表插入数据

第二章 连接查询

一、基本概念

        1、当查询结果来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的结果返回;

        2、连接查询可以通过链接运算符(连接条件)可以实现多个表查询;

        3、内连接:查询的结果只显示两个表中满足链接条件的部分;

        4、左连接:查询的结果为两个表匹配到的数据加左表特有的数据,对于右表中不存在的数据使用null填充;

        5、右连接:查询的结果为两个表匹配到的数据加右表特有的数据,对于左表中不存在的数据使用null填充;

二、内连接

语法一:

select * from 表1

inner join 表2 on 表1.字段 = 表2.字段

内连接最重要的是找对两张表要关联的字段

-- 创建表 a

        DROP TABLE if EXISTS a;

        CREATE TABLE a(

         id INT,

        name VARCHAR(10)

        );

-- 向表a插入数据

        INSERT INTO a VALUES

        ('1', '张三'),

        ('2', '李四'),

        ('3', '王五');

-- 创建表 b

        DROP TABLE if EXISTS b;

        CREATE TABLE b(

        id INT,

        score int

        );

-- 向表 b 插入数据

        INSERT INTO b VALUES

        ('1', '30'),

        ('1', '40'),

        ('2', '50'),

        ('2', '60');

        SELECT * FROM a INNER JOIN b ON a.id = b.id; (查询结果如下图)

查询结果列说明:

表a和表b的列都会出现在查询结果中,由于表a和表b都有列名叫id,为了区分,表b的id列名显示为id1

查询结果行说明:

记录只保留表a和表b中id字段相同的记录。

表students和score表内连接查询结果:

SELECT * FROM students INNER JOIN scores ON students.studentNo = scores.studentNo;

语法二:隐式内连接

select * from 表1, 表2

where 表1.字段 = 表2.字段

查询学生信息和成绩,只显示有成绩的学生:

SELECT * FROM students, scores WHERE students.studentNo = scores.studentNo;

选择部分字段:

-- 查询学生信息,要求只显示姓名、课程号、成绩

SELECT name, courseNo, score FROM students

INNER JOIN scores ON students.studentNo = scores.studentNo;

表的别名在查询中的使用:

SELECT name 姓名, courseNo 课程编号, score 成绩 FROM students st

INNER JOIN scores sc ON st.studentNo = sc.studentNo;

练习:

练习1:查询成绩表信息,同时显示成绩对应的课程名称

SELECT * FROM scores s INNER JOIN courses c ON s.courseNo = c.courseNo;

带有where条件的内连接:

-- 查询王昭君的信息,要求只显示姓名、课程号、成绩

SELECT name, courseNo, score FROM students s1

INNER JOIN scores s2 ON s1.studentNo = s2.studentNo

WHERE s1.name = '王昭君';

带有and逻辑运算符的内连接查询:

-- 查询姓名为王昭君,且成绩小于90的信息,要求只显示姓名,成绩

SELECT name, score FROM students s1

INNER JOIN scores s2 ON s1.studentNo = s2.studentNo

WHERE s1.name = '王昭君' AND s2.score < 90;

多表内连接查询:

-- 查询学生信息和成绩以及成绩对应的课程名称

SELECT * FROM students s

INNER JOIN scores sc ON s.studentNo = sc.studentNo

INNER JOIN courses cs ON sc.courseNo = cs.courseNo;

练习:

练习2:查询所有学生的linux课程成绩,要求只显示姓名,成绩,课程名

        SELECT name, score, coursename FROM students

INNER JOIN scores ON students.studentNo = scores.studentNo

INNER JOIN courses ON scores.courseNo = courses.courseNo

WHERE courses.coursename = 'Linux';

写SQL三步法:

1、搭框架:

基本的select语句框架搭建起来,如果有表,把相应的多表也联合进来

2、看条件:

决定where后面的具体条件

3、显示的字段

select后面到底要显示什么字段

4、写sql时,写完一句运行一句吗、,避免出错

带有order by排序的内连接查询

-- 查询成绩最高的男生信息,要求只显示姓名,课程名,成绩、

SELECT name, coursename, score FROM students s

INNER JOIN scores sc on s.studentNo = sc.studentNo

INNER JOIN courses cs ON sc.courseNo = cs.courseNo

WHERE s.sex = '男'

ORDER BY sc.score DESC LIMIT 1;

三、左连接

语法:

select * from 表1

left join 表2 on 表1.字段 = 表2.字段

-- 例1:查询所有学生的信息以及成绩,包括没有成绩的学生

SELECT * FROM students

LEFT JOIN scores ON students.studentNo = scores.studentNo;

四、右连接

语法:

select * from 表1

right join 表2 on 表1.字段 = 表2.字段

-- 例1:查询所有课程信息,包括没有成绩的课程

SELECT * FROM scores

RIGHT JOIN courses ON scores.courseNo = courses.courseNo;

多表联合查询,同名字段的处理方式:

如果一条 select 要用到多个表,表中有相同字段名,就需要 表名.字段名 加以区分

SELECT students.studentNo FROM students

INNER JOIN scores ON students.studentNo = scores.studentNo;

第三章 自关联

一、背景思考

1、设计省信息的表结构 provinces:

proid:省编号

pname:省名称

2、设计市信息的表结构 citys:

cityid:市编号

cname:市名称

proid:市所属的省编号;

3、citys 表的 proid表示城市所属的省,对应着 provinces 表的 proid 值;

4、问题:能不能将两个表合成一张表呢?

5、思考:观察两张表发现:citys 表比 provinces 表多一个字段 proid,其他字段的类型都是一样的;

6、意义:存储的都是地区信息,而且每种信息的数据量有限,没必要增加一个新表,或者将来还要存储区、乡镇信息,都增加新表的开销太大;

7、答案:定义表 areas,结构如下:

id;

name;

pid;

8、表中的一条记录,可以省略省,也可以省略市

9、记录为省的时候,字段说明:

id:代表省id

name:代表省名称

pid:因为省没有所属的省份,所以pid为null

10、记录为市的时候,字段说明:

id:代表市id

name:代表市名称

pid:代表市所属省id

11、这就是自关联,表中的某一字段,关联了这个表中的另外一个字段,但是他们的业务逻辑含义是不一样的,城市信息 pid 引用的是省信息的id;

12、在这个表中,结构不变,以后还可以扩充区县、乡镇街道、村社区等信息

二、创建区域表 areas,向表插入数据

-- 例1:查询一共有多少个省

SELECT COUNT(*) FROM areas WHERE pid IS NULL;

-- 例2:查询一共有多少个市

SELECT COUNT(*) FROM areas WHERE pid IS NOT NULL;

自关联是同一张表做链接查询;自关联下,一定能找到同一张表可关联的不同字段

-- 例3:查询广东省所有城市

SELECT * FROM areas a1

INNER JOIN areas a2 ON a1.id = a2.pid

WHERE a1.NAME = '广东省';

第四章 子查询

一、定义

        一个select语句中,嵌入了另外一个 select 语句,那么嵌入的 select 语句称之为子查询语句。

二、主查询

        外层的第一条 select 语句为主查询

三、主查询和子查询的关系

        子查询是嵌入到主查询中

        子查询是辅助主查询的,要么充当条件,要么充当数据源

        子查询是可以独立存在的语句,是一条完整的 select 语句;

-- 例1:查询大于平均年龄的学生记录

SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

标量子查询:例1中,子查询返回结果只有一个值(一行一列),这种称为标量子查询

-- 例2:查询30岁的学生的成绩

SELECT score FROM scores WHERE studentNo IN (SELECT studentNo FROM students WHERE age = 30);

列子查询:例2中,子查询返回的结果是一列(一列多行),这种称之为列子查询

-- 例3:用子查询,查询所有女生的信息和成绩

SELECT * FROM(SELECT * FROM students WHERE sex='女')stu

INNER JOIN scores sc ON stu.studentNo = sc.studentNo;

表级子查询:例3中,子查询返回的结果是多行多列(一个表),这种称之为表级子查询

数据准备:

/* 如果区域表areas存在,删除areas */
drop table if exists areas;

/* 创建区域表areas */
create table areas(
id int primary key,
name varchar(20),
pid int);

/* 向区域表areas插入数据 */
insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('440000', '广东省', NULL),
('440300', '深圳市', '440000'),
('440100', '广州市', '440000'),
('440500', '汕头市', '440000'),
('440600', '佛山市', '440000'),
('440200', '韶关市', '440000');

1.1 数据说明

1、部门表 departments;

部门表的 deptid 字段与员工表的 deptid 字段关联

2、员工表 employees

员工表 empid 与工资表的 empid 字段关联

leader 字段为空代表领导岗

3、工资表 salary

1.2 建表与数据插入

练习:

-- 1.1 列出男职工的总数和女职工的总数

SELECT sex, COUNT(*) FROM employees GROUP BY sex;

-- 1.2 列出非党员职工的总数

SELECT COUNT(*) FROM employees WHERE politicalstatus != '党员';

-- 1.3列出所有职工工号,姓名以及所在部门名称

SELECT empid, empname, deptname FROM employees e

INNER JOIN departments d ON e.deptid = d.deptid;

-- 1.4列出所有职工工号,姓名和对应工资

SELECT e.empid, empname, salary FROM employees e

INNER JOIN salary s ON e.empid = s.empid;

-- 1.5列出领导岗的姓名以及部门所在名称

SELECT empname, deptname FROM employees e

INNER JOIN departments d ON e.deptid = d.deptid

WHERE e.leader is NULL;

-- 1.6列出职工总人数大于4的部门号和总人数

SELECT deptid, COUNT() FROM employees GROUP BY deptid HAVING COUNT() > 4;

-- 1.7列出职工总人数大于4的部门号和部门名称

SELECT e.deptid, deptname FROM employees e

INNER JOIN departments d ON e.deptid = d.deptid

GROUP BY deptid HAVING COUNT(*) > 4;

-- 1.8列出开发部和测试部的职工号,姓名

SELECT empid, empname from employees e

INNER JOIN departments d ON e.deptid = d.deptid

where deptname in ('开发部', '测试部');

-- 1.9列出市场部所有女职工的姓名和政治面貌

SELECT empname, politicalstatus from employees e

INNER JOIN departments d ON e.deptid = d.deptid

where deptname = '市场部' and sex = '女';

-- 1.10显示所有职工姓名和工资,包括没有工资的职工姓名

SELECT e.empname, s.salary FROM employees e

LEFT JOIN salary s ON e.empid = s.empid;

-- 1.11求不姓孙的所有职工工资总和

select sum(salary) from employees e

INNER JOIN salary s ON e.empid = s.empid

where not empname like '孙%';

标签:搞定,JOIN,--,Day04,studentNo,INNER,Mysql,查询,SELECT
From: https://blog.csdn.net/weixin_46095822/article/details/139602035

相关文章

  • mysql之数据聚合
    官方文档SUM(column)用于计算指定列的总和。示例:计算每个部门员工的总工资SELECTdepartment,SUM(salary)AStotal_salaryFROMemployeesGROUPBYdepartment;AVG(column)用于计算指定列的平均值。示例:计算每个部门员工的平均工资SELECTdepartment,AVG(sal......
  • Java 开发面试题精选:Mysql 一篇全搞定
    前言在高级Java开发工程师的面试中,MySQL作为常见的数据库技术,其掌握程度往往是评估候选人综合能力的重要组成部分。在这篇文章中,我精选了一些最可能被问到的与MySQL相关的面试题目,这些题目可以全面考察候选人的理论知识、实战经验和问题解决能力,不管你是准备求职的小伙伴,还是......
  • MySQL复习题(期末考试)
    MySQL复习题(期末考试)1.MySQL支持的日期类型?DATE,DATETIME,TIMESTAMP,TIME,TEAR2.为表添加列的语法?altertable表名addcolumn列名数据类型;3.修改表数据类型的语法是?altertable表名modify列名新数据类型;4.更改表的列名的语法?altertable表名(t)change......
  • MySql JOIN
    MySqlJOIN背景最近在做一些项目时需要对数据库进行简单的select,由于之前一直在做HTML+JS+CSS所有对数据库不是很了解,现在从0开始学习,每天学习一点,做个理解总结。有理解偏差欢迎留言指正。简介JOIN用于根据两个或多个表之间关系,查询数据innerjoin内连接leftj......
  • Navicat导入json文件(json文件数据导入到MySQL表中)
    天行健,君子以自强不息;地势坤,君子以厚德载物。每个人都有惰性,但不断学习是好好生活的根本,共勉!文章均为学习整理笔记,分享记录为主,如有错误请指正,共同学习进步。文章目录一、介绍二、准备工作1.工具2.建表3.Json文件三、JSON文件数据导入MySQL表1.导入文件入口2.......
  • mysql建立支持中文字符的库
    建立一个支持中文字符的MySQL数据库,可以通过以下步骤实现: 1.创建数据库时指定字符集和排序规则在创建数据库时,指定字符集为utf8mb4,排序规则为utf8mb4_unicode_ci。这样可以确保数据库支持中文字符及其他多语言字符。sqlCREATEDATABASEmy_databaseCHARACTERSETutf8mb......
  • 论述MySQL的同异步复制
    MySQL的复制功能是数据库系统中一个重要的特性,它可以实现数据的冗余备份和分布式读写,提高系统的可靠性和性能。MySQL的复制主要分为异步复制、半同步复制和同步复制,这三种复制方式在机制和使用场景上有显著的不同。异步复制特点主从模式:在异步复制中,主库(Master)将数据的变更......
  • MySQL数据库的基础:逻辑集合数据库与表的基础操作
    本篇会加入个人的所谓鱼式疯言❤️❤️❤️鱼式疯言:❤️❤️❤️此疯言非彼疯言而是理解过并总结出来通俗易懂的大白话,小编会尽可能的在每个概念后插入鱼式疯言,帮助大家理解的.......
  • MySQL8.0默认TCP端口介绍
    1、本文内容选择题TCP/IPMySQL8.0的默认TCP端口showvariables查看总结2、选择题A、3306B、33060C、33062D、330633、TCP/IPTCP/IP(TransmissionControlProtocol/InternetProtocol,传输控制协议/网际协议)是指能够在多个不同网络间实现信息传输的协议簇。以下是......
  • Ubuntu 24.04 LTS 安装配置 MySQL Community Server 8.4.0 LTS
    1安装 AptRepository​​​​​​​地址MySQL::DownloadMySQLAPTRepositorysudodpkg-imysql-apt-config_0.8.30-1_all.deb #安装mysql8.4ltssudoaptupdatesudoapt-getinstallmysql-server#修改mysqlroot密码策略 2查看版本test@test:~$my......