【软件测试】数据库基础知识

admin管理员组

文章数量:1651579

• 以下是软件测试小白关于数据库基础知识的学习记录。
• 为什么要学习数据库？
  1. 有助于更好地理解数据结构和关系。
     • 数据库是存储和管理数据的核心系统。了解数据库的结构（如表、字段、索引等）以及数据之间的关系（如主键、外键、关联等），有助于测试人员更好地理解被测系统的数据逻辑和流程。
  2. 有助于设计有效的测试用例。
     • 测试人员需要根据系统的功能和业务逻辑设计测试用例。如果了解数据库，就可以设计出更加全面和深入的测试用例，包括对数据完整性、一致性和并发性的测试。
  3. 有助于数据验证和调试。
     • 在测试过程中，测试人员需要验证系统是否按预期处理数据。学习数据库可以直接查询和检查数据库中的数据，从而快速定位和解决问题。

目录

• • 一、数据库简介
  • • 1.1 数据库介绍和分类
    • 1.2 关系型数据库
    • 1.3 SQL语句
    • 1.4 MySQL
    • • 1.4.1 MySQL介绍
      • 1.4.2 MySQL组成
      • 1.4.3 MySQL安装
      • 1.4.4 工具连接数据库
    • 1.5 数据类型与约束
  • 二、命令行客户端
  • • 2.1 命令行操作数据库
    • 2.2 命令行操作数据表
  • 三、SQL语句
  • • 3.1 数据库表的操作
    • 3.2 数据操作
    • 3.3 数据查询操作
    • • 3.3.1 查询基本语法
      • 3.3.2 条件查询
      • 3.3.3 排序
      • 3.3.4 聚合函数
      • 3.3.5 分组查询
      • 3.3.6 分组后的数据筛选
      • 3.3.7 分页
      • 3.3.8 连接查询
      • 3.3.8 自关联
      • 3.3.9 子查询
    • 3.4 查询练习

一、数据库简介

1.1 数据库介绍和分类

• 数据库 DB(DataBase)：是存储在计算机系统中的有组织的、结构化的数据集合。它可以看作是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的仓库。
• 数据库管理系统 DBMS(DataBase Management System)：比如Mysql、Oracle等。
  • 为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
  • 是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。
• 数据库的功能：
  • 组织、存储和管理数据。
  • 提供数据的存储、查询、修改、排序和统计等操作。
• 数据库的分类：
  • 关系型数据库：将数据间的关系以数据库表的形式加以表达，并将数据存储在表格中，以便于查询。
    • Oracle：在大型项目中使用，例如：银行、电信等项目。
    • MySQL： Web 项目中使用最广泛的数据库。
    • Microsoft SQL Server：在微软的项目中使用。
    • SQLite：轻量级数据库，主要应用在移动平台。
  • 非关系型数据库
    • redis
    • mongodb
    • hbase

1.2 关系型数据库

• 关系型数据库是一种基于 关系模型 的数据库系统，它使用表格（即关系）来存储和组织数据。

• 关系型数据库核心要素
  • 数据行（记录或元组）：表中的行是记录的具体实例，每一行包含表中所有列的值。
  • 数据列（字段或属性）：每一列都有一个唯一的名称和相应的数据类型。
  • 数据表：关系型数据库中的基本存储结构是表，每个表由行和列组成，类似于Excel中的电子表格。
  • 数据库：数据表的集合，一个数据库可以有n个数据表。
  • 主键（主码）：主键是表中的一列或多列，其值在表中是唯一的，用于唯一标识表中的每一行。主键列的值不能为空（NULL）。
  • 外键（外码）：外键是表中的一列或多列，其值是另一个表的主键。外键用于在两个表之间建立和维护关系。

1.3 SQL语句

• SQL是用于管理和操作关系型数据库的标准编程语言。它允许用户定义、操纵和控制数据库中的数据。
• SQL语言的分类
  • DQL：数据查询语言，用于对数据进行查询，例如：select
  • DML：数据操作语言，对数据进行增加、修改、删除，例如：insert、update、delete
  • TPL：事务处理语言，对事务进行处理，例如：begin transaction、commit、rollback
  • DCL：数据控制语言，进行授权与权限回收，例如：grant、revoke
  • DDL：数据定义语言，进行数据库、表的管理等，例如：create、drop
  • CCL：指针控制语言，通过控制指针完成表的操作，例如：declare cursor
• 【说明】主流的关系型数据库都支持SQL语言，在MySql中SQL语言不区分大小写。

1.4 MySQL

1.4.1 MySQL介绍

• 【说明】MySql是一个关系型数据库，由瑞典MySql AB公司开发，后被Sun公司收购，Sun公司后来又被Oracle收购。目前MySql属于Oracle的产品。
• MySQL特点
  1. 可移植性好。
  2. 支持多操作系统。
  3. 支持多种编程语言。
  4. 开源社区版免费。
  5. 支持多线程。

1.4.2 MySQL组成

• MySQL服务器：存储数据并解析编译后的SQL语句，将执行结果返回给客户端。
• MySQL客户端：下发用户要执行的SQL语句，并显示服务器返回的执行结果。

1.4.3 MySQL安装

• 【说明】这里安装的MySQL是作为服务端使用。
• MySQL安装版本：MySQL5.7(也可选择其他版本)
  • 安装环境（这里我的安装环境如下）
    • 操作系统版本：CentOS7
    • 系统架构：x86_64
• 安装MySQL的方式有多种，比如：通过tar压缩包安装、rpm安装包安装、yum的方式安装等。
• 这里我使用的是通过rpm安装包的方式安装MySQL。
• 【安装步骤】
  1. 查看是否安装了MySQL服务。
     • rpm -qa | grep mysql
     • 这里我是没有安装过的，如下图。
       
  2. 查看系统版本。
     • cat /etc/redhat-release
     • 可以看到我安装的系统是基于 CentOS Linux 的，版本是 7.7.1908，是一个 Core 版本。
       
  3. 查看系统的架构类型。
     • uname -m
       
  4. 在MySQL官网找到合适的版本下载。【官网下载】
     • 通过上面查询已知，我的操作系统是基于Red Hat版本的开发CentOS7，系统架构类型是x86,64位。
     • 所以MySQL选择版本为(如下图)。
       
       
  5. 使用xftp工具上传到Linux。
     • 使用方法可以看我的另一篇博客【软件测试】Linux操作系统基础知识，Linux远程登录章节。
     • 这里我上传的到的地方是/root/下载。
       
       
  6. 创建MySQL安装路径。
     • mkdir -p /opt/mysql5.7
       
  7. 解包
     • tar -xvf /root/下载/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar -C /opt/mysql5.7/
       
  8. 删除CentOS7自带的类MySQL数据库mariadb。（避免其和MySQL冲突）
     • rpm -qa | grep mariadb
     • rpm -e --nodeps mariadb-libs
       
  9. 安装MySQL rpm包，需要按顺序安装。
     • rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm
     • rpm -ivh mysql-community-libs-5.7.26-1.el7.x86_64.rpm
     • rpm -ivh mysql-community-client-5.7.26-1.el7.x86_64.rpm
     • rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
       
  10. 启动MySQL服务。
      • systemctl start mysqld.service
        
  11. 设置root用户密码。（MySQL会自动给root用户设置随机密码，随机密码比较难记忆。）
      • 查看随机密码：grep 'password' /var/log/mysqld.log
        
      • 使用root用户登录：mysql -u root -p
      • 复制随机密码，粘贴使用。
        
      • 设置密码策略：set global validate_password_policy=0;（默认是值为1）
        
      • 【说明】密码策略
        • 0 (LOW): 只验证密码长度。默认密码长度至少为 8。
        • 1 (MEDIUM): 密码长度至少为 8，并且必须包含至少一个大写字母、一个小写字母、一个数字和一个特殊字符。
        • 2 (STRONG): 密码长度至少为 8，并且必须包含至少一个大写字母、一个小写字母、一个数字、一个特殊字符，并且这些字符不能只是简单地在字符串中重复。
      • 设置root用户新密码：set password for 'root'@'localhost' =password('8位密码');
        
      • 应用新密码：flush privileges;
        
  12. 退出MySQL，验证新密码。
      • quit
      • mysql -u root -p
        
  13. 查看MySQL 服务的当前状态。
      • systemctl status mysqld.service
        
  14. 关闭MySQL服务。（注：停止 MySQL 服务可能会导致所有正在使用该数据库的应用程序或服务中断。）
      • systemctl stop mysqld.service
        
• 【说明】安装MySQL客户端。（Navicat数据库管理工具）
  • 安装环境
    • 操作系统版本：Windows10
    • Navicat版本：Navicat Premium Lite 17（ Navicat 的精简版，拥有基本数据库操作所需的核心功能。该版本是免费的，用来学习的完全够用。）【官网下载】
• 【安装步骤】
  1. 进入官网下载地址选择适合的版本。（这里我是Windows操作系统）
     
     
  2. 下载好安装包后，一直点下一步即可完成安装。

1.4.4 工具连接数据库

• 【步骤】
  1. 确定MySQL服务端IP地址。
     • ifconfig
  2. 确认MySQL服务是否开启。
     • netstat -anptu | grep 3306
  3. 确保root用户有权限从任意IP地址连接。
     • 进入MySQL：mysql -u root -p
     • 使用MySQL数据库：use mysql;
     • 授权root用户可从任意IP地址连接MySQL服务：grant all privileges on *.* to 'root'@'%' identified by 'mysqlroot' with grant option;
     • ps：在执行上面命令时我这里报一个错，提示我密码不符合当前密码策略的规则。
       
       • 查看当前密码策略：show variables like 'validate_password%';
         
       • 【说明】
         1. validate_password_length: 密码的最小长度为 8。
         2. validate_password_policy: 密码策略为 MEDIUM。
         3. validate_password_mixed_case_count: 密码中至少需要包含 1 个大小写字母（不区分是大写还是小写，只要有一个与其余部分不同即可）。
         4. validate_password_number_count: 密码中至少需要包含 1 个数字。
         5. validate_password_special_char_count: 密码中至少需要包含 1 个特殊字符。
       • 根据当前密码策略修改密码为‘MysqlRoot1!’(这里是我的密码，根据自己密码修改)。
     • 再次执行：grant all privileges on *.* to 'root'@'%' identified by 'MysqlRoot1!' with grant option;
     • 刷新权限：flush privileges;
     • 查看user表：select user,host from user;
     • 可以看到user表内host列多了一个‘%’，说明root用户使用任意IP地址连接的权限授权成功。
       
     • 退出mysql：exit;/quit;
  4. 开放3306端口
     • 检查防火墙的状态：systemctl status firewalld
     • 可以看到我的系统防火墙处于开启状态。
       
     • 如果防火墙没有启用，可以通过该命令启动：systemctl start firewalld
     • 将3306端口添加到公共区域的防火墙规则中，并设置为永久生效：firewall-cmd --zone=public --add-port=3306/tcp --permanent
       
     • 重新加载防火墙配置：firewall-cmd --reload
       
     • 验证端口是否开放：firewall-cmd --zone=public --query-port=3306/tcp
       
     5. 使用Navicat连接MySQL数据库。
        • 文件 -> 新建连接 -> 选择MySQL
        • 主机就是通ifconfig命令查到的ip地址。
        • 用户名和密码是之前授权的root用户。
          
     • 测试连接。可以看到连接成功（如下图）。
       
     • 双击开启连接，可以看到里面有一些MySQL数据库自带的表。
       

1.5 数据类型与约束

• 常用数据类型：int(整型)、decimal(小数)、字符串(varchar)、datetime(时间日期)。
• 常用数据约束：
  • 主键(primary key)：物理上的存储顺序。
    • 当把某个字段设定为主键后，该字段的值必须唯一且不能为空。
    • 针对主键的字段，可以设定自动自增，自动在原来最大的ID值上加1。
  • 非空(not null)：此字段不允许填写空值。
    • 空(null)和空字符是不同的。
  • 唯一(unique)：此字段的值不允许重复。
    • 主键只能有1个(一张表）
    • 维一的字段可以有多个。
  • 默认值(default)：当不填写此值时会使用默认值。
  • 外键(foreign key)：维护两个表之间的关联关系。

二、命令行客户端

2.1 命令行操作数据库

• 连接数据库：mysql -u root -p
  
• 常见数据库操作命令：
  1. 查看所有数据库：show databases;
     
  2. 使用数据库：use 数据库名;
     
  3. 查看当前使用数据库：select database();
     
  4. 创建数据库：create database 数据库名;
     
  5. 删除数据库：drop database 数据库名;
     

2.2 命令行操作数据表

• 常用数据表操作命令：
  1. 查看当前数据库所有表：show tables;
     
  2. 查看表结构：desc 表名;
     
  3. 查看表的创建语句：show create table 表名;
     

三、SQL语句

3.1 数据库表的操作

• 创建表的语法格式

create table 表名(
属性名 类型 约束,
属性名1 类型 约束,
......
);

• 【说明】Navicat使用SQL语句创建表
  
• 【说明】Navicat允许SQL语句
  
• 【练习】创建学生表，其中学号、姓名(长度为10)、年龄、身高(保留两位小数)。

CREATE TABLE students(
s_id INT(8) PRIMARY KEY, #s_id字段为主键
s_name VARCHAR(10),
s_age INT(3),
s_height DECIMAL(5,2)
);

• 注释格式：-- 注释内容 快捷键：ctrl+/
  

3.2 数据操作

• 简单查询：select * from 表名;
• 添加一行数据：insert into 表名 values(...)
  
• 添加部分字段数据：insert into 表名(字段1,字段2,...) values(值1,值2,...)
  
• 添加多行数据：insert into 表名 values(...),(...),.....;
  
• 修改数据：update 表名 set 字段名1=值1,字段名2=值2...where 条件;
  • ps：修改指定数据需要加条件，否则是给整个记录修改。
    
  • 【练习】修改id为2的学生数据，姓名改为tom，年龄改为30。
    
• 删除数据：delete from 表名 where 条件;
  • ps：delete可以通过where子句删除部分记录。delete删除所有数据时，自增长字段不会从1开始。
    
• 【练习】

1. 创建一个成绩表姓名(10)、班级(长度为8)、科目(长度为56)、成绩(保留一位小数)。

create table scores(
name varchar(10),
class varchar(8),
subjects varchar(56),
score decimal(4,1)
);

2. 在成绩表中新增以下数据
   • 张三、一班、数学、85.6
   • 李四、二班、数学、99.5
   • 王五、二班、语文、77.5
   • 赵六、三班、英语、88.5

insert into scores values('张三','一班','数学',85.6),
('李四','二班','数学',99.5),
('王五','二班','语文',77.5),
('赵六','三班','英语',88.5);

3. 将成绩表中李四的成绩改为100。

update scores set score=100 where name='李四';

4. 删除学生王五的记录。

delete from scores where name='王五';

3.3 数据查询操作

• 【说明】数据准备

1. 创建学生表

 create table students (
 studentNo varchar(10) primary key,
 name varchar(10),
 sex varchar(1),
 hometown varchar(20),
 age tinyint(4),
 class varchar(10),
 card varchar(20)
 );

2. 添加数据

insert into students values
 ('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
 ('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
 ('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
 ('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
 ('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
 ('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
 ('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
 ('008', '小乔', '女', '河南', '15', '3班', null),
 ('009', '百里守约', '男', '湖南', '21', '1班', ''),
 ('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
 ('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
 ('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');

3.3.1 查询基本语法

• 查询部分字段数据：select 字段1,字段2,... from 表名;
  
• 起别名
  • select 别名.字段1 别名.字段2...... from 表名 as 别名;
  • select 字段1 as 别名1,字段2 as 别名2,...... from 表名;
    
• 去重：select distinct 字段1,...... from 表名;
  

3.3.2 条件查询

• 条件查询是根据一定的条件去查询数据的结果。
• 语法格式：select 字段1,字段2...... from 表名 where 条件;
  
• 【说明】where支持多种运算符进行条件处理
  1. 比较运算符
     • 等于：=
     • 大于：>
     • 大于等于：>=
     • 小于：<
     • 小于等于：<=
     • 不等于：!=或<>
  2. 逻辑运算
     • 与：and
     • 或：or
     • 非：not
  3. 模糊查询
     • 关键字：like
     • 匹配任意多个字符：%
     • 匹配一个任意字符：_（下划线）
  4. 范围查询
     • 在一个范围内：in
     • 在一个连续范围内：between...and...
  5. 空判
     • Mysql中空表示null，与 ‘’ (空)是不一样的。
     • 判断为空：is null
     • 判断非空：is not null
  • 【练习】在学生表中执行
    1. 查询小乔的年龄。
       • select age from students where name='小乔';
         
    2. 查询20岁以下的学生。
       • select * from students where age<20;
         
    3. 查询家乡不在北京的学生。
       • select * from students where hometown!='北京';
         
    4. 查询河南或河北的学生。
       • select * from students where hometown='河南' or hometown='河北';
         
    5. 查询’1班’的’上海’的学生。
       • select * from students where class='1班' and hometown='上海';
         
    6. 查询非20岁的学生。
       • select * from students where not age=20;
         
    7. 查询姓名为两个字的学生。
       • select * from students where name like '__';
         
    8. 查询姓‘百’且年龄大于20的学生。
       • select * from students where name like '百%' and age>20;
         
    9. 查询学号以1结尾的学生。
       • select * from students where studentNo like %1;
         
    10. 查询年龄为18或19或22的女生。
        • select * from where age in(18,19,22);
          
    11. 查询年龄在20到25以外的学生
        • select * from where age not between 20 and 25;
          
    12. 查询没有填写身份证的学生。
        • select * from students where card is null;
          
    13. 查询填写了身份证的学生。
        • select * from students where card is not null;
          

3.3.3 排序

• 语法格式：select * from 表名 order by 字段名1 asc(desc),字段名2 asc(desc),.......;
• 【说明】
  1. 将行数据按照字段1进行排序，如果某些字段1的值相同时，则按照字段2排序，以此类推。
  2. 默认按照列值从小到大排列。
• 关键字
  • asc：从小到大排列，即升序。
  • desc：从大到小排序，即降序。
• 【练习】
  1. 查询所有学生信息，按年龄升序排序。
     • select * from students order by age asc;
       
  2. 查询所有学生信息，按年龄降序排序，年龄相同时，再按学号升序排序。
     • select * from students order by age desc,studentNo asc;
       

3.3.4 聚合函数

• 【说明】
  1. 使用聚合函数方便进行数据统计。
  2. 聚合函数不能在 where 中使用。
• 关键字
  • count：查询总记录数。
  • max：查询最大值。
  • min：查询最小值。
  • sum：求和。
  • avg：求平均数。
• 【练习】
  1. 查询学生总数。
     • select count(*) from students;
       
  2. 查询男生年龄最大的学生。
     • select max(age) from students where sex='男';
       
  3. 查询1班的最小年龄。
     • select min(age) from students where class='1班';
       
  4. 查询北京学生的年龄总和。
     • select sum(age) from students where hometown='北京';
       
  5. 查询女生的平均年龄。
     • select avg(age) from students where sex='女生';
       
  6. 查询所有学生的最大年龄、最小年龄、平均年龄。
     • select max(age),min(age),avg(age) from students;
       
  7. 查询3班年龄小于18岁的同学有几个。
     • select count(*) from student where class='3班' and age<18;
       

3.3.5 分组查询

• 【说明】
  1. 按照字段分组，此字段相同的数据会被放到一个组中。
  2. 分组的目的是对每一组的数据进行统计(使用聚合函数)。
• 关键字：group by
• 语法格式：select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2..;
• 【练习】
  1. 查询各种性别的人数。(按性别字段分组)
     • select sex,count(*) from students group by sex;
       
  2. 查询每个班级中各种性别的人数。（按班级+性别字段分组）
     • select class,sex count(*) from students group by class,sex;
       

3.3.6 分组后的数据筛选

• 关键字：having
• 语法格式： select 字段1,字段2,聚合函数... from 表名 group by 字段1,字段2,字段3...having 条件;
• 【说明】
  • 关键字 having 后面的条件运算符与 where 的相同。
• having与where对比
  1. where 是对 from 后面指定的表进行数据筛选，属于对原始数据的筛选。
  2. having 是对 group by 的结果进行筛选。
  3. having 后面的条件中可以用聚合函数，where 后面不可以。
• 【练习】
  1. 查询学生表男生的总人数。
     • select count(*) from students where sex='男';
     • select sex,count(*) from students group by sex having sex='男';
       
       
  2. 查询各个班级学生的平均年龄、最大年龄、最小年龄。
     • select class,avg(age),max(age),min(age) from students group by class;
       
  3. 查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄。
     • select class,avg(age),max(age),min(age) from students group by class having not class='1班';
       

3.3.7 分页

• 关键字：limit
• 语法格式：select * from 表名 limit start,count;
  • 从start开始，获取count条数据。
  • start索引从0开始。
• 【练习】
  1. 查询第4到第6行学生信息。(第四行开始索引为4-1，显示3条数据)
     • select * from students limit 3,3;
       
  2. 每页显示5条数据，显示每一页的数据。（学生表共12条数据，每页要显示5条数据故索引值为0、5、10）
     • select * from students limit 0,5;
     • select * from students limit 5,5;
     • select * from students limit 10,5;
       
       
       

3.3.8 连接查询

1. 内连接：
   • 关键字：inner join
   • 返回结果为两个表中满足连接条件的数据。
   • 设A和B是两个集合，inner join相当于求两个集合的交集，即A∩B。
   • 语法格式：select * from 表A inner join 表B on 表A.字段=表B.字段;
   • 使用场景：
     1. 合并两个或多个表中的共同数据。
        • 当有两个或多个表，它们之间通过某些字段（通常是主键和外键）相关联时，可以使用内连接来合并这些表中的共同数据。
     2. 筛选匹配记录。
        • 内连接只返回那些在连接条件上有匹配的行，因此它可以用作筛选工具，只保留那些在所有表中都有匹配记录的数据行。
2. 左连接：
   • 关键字：left join
   • 返回结果为左表中的所有数据以及右表中满足连接条件的数据。对于右表中没有匹配的数据，相应的列将使用null填充。
   • 设A和B是两个集合，left join相当于求左集合A与A和B交集的并集，即A ∪ (A ∩ B)，但来自A和B的交集部分会保持原样，来自A但不在B中的部分会填充为null。
   • 【例子】
     • 表A：学生名单（学号，姓名）
     • 表B：成绩表（学号，成绩）
     • 如果我们想要获取所有学生的名单以及他们的成绩，我们就会使用左连接。
       • 交集部分：那些学号在两个表中都存在的学生，结果将会显示他们的姓名和成绩。
       • 来自A但不在B的部分：那些只在学生名单中，但成绩表中没有他们成绩的学生，结果将会显示他们的姓名，但成绩字段会是null。
     • 所以，左连接就像是给了我们一个完整的学生名单，并且对于每个学生，如果成绩表中有他们的成绩，就显示出来；如果没有，就留空。
   • 语法格式：select * from 表A left join 表B on 表A.字段=表B.字段;
   • 使用场景：
     1. 保留左表数据完整性。
        • 当需要从一个表（左表）中获取所有记录，并希望同时获取另一个表（右表）中与这些记录匹配的记录时，即使右表中没有匹配的记录，也希望保留左表中的所有记录，此时应使用左连接。
     2. 处理右表中可能缺失的数据。
        • 在某些情况下，右表中可能缺少与左表匹配的数据。使用左连接可以确保即使右表中没有匹配的记录，左表中的记录也会被保留下来，并且右表中的字段值会被填充为null。
3. 右连接：
   • 关键字：right join
   • 返回结果为右表中的所有数据以及左表中满足连接条件的数据。对于左表中没有匹配的数据，相应的列将使用null填充。
   • 设A和B是两个集合，right join相当于求右集合B与A和B交集的并集，即B ∪ (A ∩ B)，但来自B和A的交集部分会保持原样，来自B但不在A中的部分会填充为null。(和左连接类似)
   • 语法格式：select * from 表A right join 表B on 表A.字段=表B.字段;
   • **使用场景：**与左连接使用场景类似。
4. 全连接：
   • 关键字：full join
   • 返回结果为左表和右表中的所有数据。对于没有匹配的数据，相应列将使用null填充。
   • 设A和B是两个集合，full join相当于求两个集合的并集，然后对于只在一个集合中出现的元素，在另一个集合对应的部分填充为null。
   • 语法格式：select * from 表A full join 表B on 表A.字段=表B.字段;
5. 交叉连接：
   • 关键字：cross join
   • 返回结果为两个表的笛卡尔积，即每个记录与另一个表的每个记录配对。
   • 设A和B是两个集合，cross join相当于求两个集合的笛卡尔积，即A x B。
   • 【说明】
     • 假设集合A={a, b}，集合B={0, 1, 2}，则A和B的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
     • 如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选课情况。
   • 语法格式：select * from 表A cross join 表B;
6. 自然连接：
   • 关键字：natural join
   • 返回结果为两个表中具有相同列名和相同值的数据。自然连接会自动根据所有同名的列进行连接。
   • 设A和B是两个集合，natural join相当于所有同名列的交集，即A ⨝ B。
   • 语法格式：select * from 表A natural join 表B;

• 【数据准备】

-- 创建课程表
create table courses ( 
courseNo int(10) unsigned primary key auto_increment,
name varchar(10)
);
-- 插入6条数据
insert into courses values
('1', '数据库'), 
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试'),
('5', '单元测试'),
('6', '测试过程');
-- 创建成绩表
create table scores (
id int(10) unsigned primary key auto_increment,
courseNo int(10),
studentno varchar(10),
score tinyint(4)
);
-- 插入8条数据
insert into scores values 
('1', '1', '001', '90'),
('2', '1', '002', '75'),
('3', '2', '002', '98'),
('4', '3', '001', '86'),
('5', '3', '003', '80'),
('6', '4', '004', '79'),
('7', '5', '005', '96'),
('8', '6', '006', '80');

• 【练习】
• 内连接查询

1. 查询学生信息及学生的成绩，只查询成绩表中有成绩的学生。

-- 学生表和成绩表关联字段为学号，连接条件为学号。
select * from students inner join scores on students.studentNo = scores.studentNo; 

2. 查询课程信息及课程的成绩。

-- 课程表和成绩表关联字段为课程号，连接条件为课程号
select * from courses inner join scores on courses.courseNo = scores.courseNo; 

3. 查询王昭君的成绩，要求显示姓名、课程号、成绩。

-- 连接条件为学号，过滤条件为name='王昭君'
select stu.name,sc.courseNo,sc.score from students as stu
inner join scores as sc on stu.studentNo = sc.studentNo
where stu.name = '王昭君';

4. 查询王昭君的数据库成绩，要求显示姓名、课程名、成绩。

select stu.name,c.name,s.score
from students as stu
-- 将学生表与成绩表连接，连接条件为学号
inner join scores as s on stu.studentNo = s.studentNo
-- 将成绩表与课程表连接，连接条件为课程号
inner join courses as c on c.courseNo = s.courseNo
-- 过滤条件为姓名=王昭君，课程名=数据库
where stu.name = '王昭君' and c.name = '数据库';

5. 查询所有学生的数据库成绩，要求显示姓名、课程名、成绩

select stu.name '姓名',c.name '课程名',s.score '成绩'
from students as stu
-- 将学生表与成绩表连接，连接条件为学号
inner join scores as s on stu.studentNo = s.studentNo
-- 将成绩表与课程表连接，连接条件为课程号
inner join courses as c on c.courseNo = s.courseNo
-- 过滤条件课程名=数据库
where c.name = '数据库';

6. 查询男生中最高成绩，要求显示姓名、课程名、成绩。

select stu.name as 姓名,c.name as 课程名,max(s.score) as 最高成绩
from students as stu 
-- 将学生表与成绩表连接，连接条件为学号
inner join scores as s on stu.studentNo = s.studentNo
-- 将成绩表与课程表连接，连接条件为课程号
inner join courses as c on c.courseNo = s.courseNo
-- 过滤条件性别=男
where stu.sex = '男'
-- 将所有男生成绩降序排序
order by s.score desc
-- 只显示最高成绩
limit 0,1;

• 左连接查询

1. 查询所有学生的成绩，包括没有成绩的学生。

select *
from students as stu
left join scores as s on stu.studentNo = s.studentNo;

2. 查询所有学生的成绩，包括没有成绩的学生，要求显示姓名、课程名、成绩。

select stu.name as 姓名,c.name as 课程名,s.score as 成绩
from students as stu
left join scores as s on stu.studentNo = s.studentNo
left join courses as c on s.courseNo = c.courseNo;

3.3.8 自关联

• 自关联是指一个表与其自身进行连接，通常用于比较表中的行或生成基于表中数据的关系、查找重复记录、层次结构数据、查找相邻记录等场景。
• 【说明】
  • 假设我们有一个地址信息表，结构如下：
    
  • 如果我要查找河北省的所有市就可以使用自关联。将地址信息表与自身连接，连接条件是市的pid=省的aid。
    
• 【练习】

1. 查询河南省所有的市。

select *
from areas as a1
inner join areas a2
on a1.aid = a2.pid
where a1.atitle = '河南省';

2. 查询出郑州市所有的区的信息。

select * 
from areas as a1
inner join areas as a2
on a1.aid = a2.pid
inner join areas as a3
on a2.aid = a3.pid
where a2.atitle = '郑州市';

3.3.9 子查询

• 子查询和主查询
  • 嵌入在其他查询语句中的select语句称为子查询，其他的查询语句称之为主查询。
  • 子查询辅助主查询，要么充当条件，要么充当数据源。
  • 子查询是一条完整的、可单独执行的select查询语句。
• 子查询特定关键字
  • in：范围，检查某个值是否存在于子查询返回的结果集中。
  • all：所有，表示与外部查询中的值匹配的子查询中的所有值。
  • any：任意，表示与外部查询中的值匹配的子查询中的任意值。
• 子查询充当条件

1. 标量子查询
   • 查询王昭君的成绩，要求显示成绩。

-- 使用内连接查询
select s.score 
from students as stu
inner join scores as s
on stu.studentNo = s.studentNo
where stu.name = '王昭君';
-- 使用子查询
-- 学生表查询王昭君的学号充当成绩查询的过滤条件。
-- select studentNo from students where name = '王昭君';
-- 返回单一值的子查询称为标量子查询。这里子查询只返回王昭君的学号。
select score 
from scores 
where studentNo = (
select studentNo
from students
where name = '王昭君'
);

2. 列子查询
   • 查询18岁的学生的成绩，要求显示成绩。

-- 返回一列值的子查询称为列子查询
-- 学生表查询18岁学生的学号作为主查询过滤条件。
-- select studentNo from students where age = 18;
select score
from scores
-- 子查询会返回一列学号值，使用in关键字表示学号的查询范围存在于子查询返回的结果中
where studentNo in (
select studentNo
from students 
where age = 18
);

3. 行子查询
   • 查询和王昭君同班、同龄的学生信息。

-- 返回一行多列结果的子查询称为行子查询
-- 先查询王昭君的班级、年龄信息。
select class,age from students where name = '王昭君';
-- 将查询到的信息作为子查询
select *
from students
where (class,age) = (
select class,age
from students
where name = '王昭君'
);

• 子查询充当数据源

1. 查询数据库和系统测试的课程成绩。

-- 课程表查询数据库和系统测试的课程信息
select * from courses where name in('数据库','系统测试');
-- 将查询到的结果作为一张新表
select s.studentNo,c.name,s.score
from scores as s
inner join (
select *
from courses 
where name in('数据库','系统测试')
) as c
on s.courseNo = c.courseNo;

3.4 查询练习

• 数据准备

-- 创建部门表
create table departments(
dep_id int(10) primary key, -- 部门id
dep_name varchar(20) not null -- 部门名称
);

-- 添加三条数据
insert into departments values
(1001,'市场部'),
(1002,'测试部'),
(1003,'开发部');

-- 创建员工表
create table employees(
emp_id int(10) primary key, -- 员工id
emp_name varchar(20) not null, -- 员工姓名
sex varchar(4) default null, -- 性别
dep_id int(10) default null, -- 部门id
job varchar(20) default null, -- 岗位
politicalStatus varchar(20) default null, -- 政治面貌
sa_id int(10) default null -- 薪资id
);

-- 添加12条数据
insert into employees values
(1, '王昭君', '女', 1003, '开发', '群众', 9),
(2, '诸葛亮', '男', 1003, '开发经理', '群众', null),
(3, '张飞', '男', 1002, '测试', '团员', 4),
(4, '白起', '男', 1002, '测试经理', '党员', null),
(5, '大乔', '女', 1002, '测试', '党员', 4),
(6, '孙尚香', '女', 1001, '市场', '党员', 12),
(7, '百里玄策', '男', 1001, '市场', '团员', 12),
(8, '小乔', '女', 1002, '测试', '群众', 4),
(9, '百里守约', '男', 1003, '开发', '党员', 9),
(10, '妲己', '女', 1003, '开发', '团员', 9),
(11, '李白', '男', 1002, '测试', '团员', 4),
(12, '孙膑', '男', 1001, '市场经理', '党员', null);

-- 创建工资表
create table salaries(
sa_id int(10) primary key,
emp_id int(10) not null,
salary int(20) not null
);

-- 添加12条数据
insert into salaries values 
(1, 7, 2100),
(2, 6, 2000),
(3, 12, 5000),
(4, 9, 1999),
(5, 10, 1900),
(6, 1, 3000),
(7, 2, 5500),
(8, 5, 2000),
(9, 3, 1500),
(10, 8, 4000),
(11, 11, 2600),
(12, 4, 5300);

1. 列出总人数大于4的部门号和总人数。

-- 按员工的部门号分组查询
select dep_id,count(*) from employees
group by dep_id
-- 过滤条件
having count(*) > 4;

2. 列出开发部和和测试部的职工号、姓名。

-- 分析：部门表有部门名称，员工表有职工号、姓名
-- 部门表和员工表关联字段为dep_id
-- 方式一：
-- 先连接两表
select e.emp_id,e.emp_name 
from departments as d
inner join employees as e
on d.dep_id = e.dep_id 
-- 过滤非开发部和测试部员工数据
where d.dep_name in ('开发部','测试部');
-- 方式二：
-- 子查询：先查询部门表中开发部和测试部的dep_id作为主查询的数据源
select dep_id from departments where dep_name in ('开发部','测试部');
-- 主查询：返回字段为职工号、姓名
select e.emp_id,e.emp_name
from employees as e 
-- 将子查询获取到的开发部和测试部的dep_id作为新表与员工表连接，如此连接后的表只存在开发部和测试部的员工数据
inner join (
select dep_id 
from departments 
where dep_name in ('开发部','测试部')
) as dept 
on e.dep_id = dept.dep_id;

3. 求出各部门党员的人数，要求显示部门名称。

-- 分析：部门表含部门名称(dep_name)字段,员工表含政治面貌字段(politicalStatus)
-- 显示字段为部门名称，统计各部门党员人数
select d.dep_name,count(*) 
-- 将过滤了非党员员工的表与部门表连接，关联字段dep_id
from departments as d 
inner join employees as e 
on d.dep_id = e.dep_id
where e.politicalStatus = '党员'
-- 按部门名称分组
group by d.dep_name;

4. 列出市场部的所有女职工的姓名和政治面貌。

-- 4. 列出市场部的所有女职工的姓名和政治面貌。
-- 分析：部门表含部门名称(dep_name)字段,员工表含政治面貌和姓名、性别字段(politicalStatus、emp_name、sex)
-- 部门表和员工表连接,再过滤非市场部女员工的员工信息
select e.emp_name,e.politicalStatus
from departments as d 
inner join employees as e 
where d.dep_name = '市场部' and e.sex = '女';

5. 显示所有职工的姓名、部门名和工资数。

-- 5. 显示所有职工的姓名、部门名和工资数。
-- 分析：部门表含部门名称(dep_name),员工表含员工姓名(emp_name),工资表含(工资数)
-- 三表连接
select e.emp_name,d.dep_name,s.salary
from departments as d 
inner join employees as e 
on d.dep_id = e.dep_id
inner join salaries as s 
on e.emp_id = s.emp_id;

6. 显示各部门名和该部门的职工平均工资。

-- 涉及部门表、员工表、工资表
-- 先连接三表，得到员工的工资信息
-- 再以部门名称分组并计算平均工资
select d.dep_name,avg(s.salary)
from departments as d 
inner join employees as e 
on d.dep_id = e.dep_id
inner join salaries as s 
on e.emp_id = s.emp_id
group by d.dep_name;

7. 显示工资最高的前3名职工的职工号和姓名。

-- 涉及工资表和员工表
-- 子查询：工资表查询工资最高的前三名emp_id
-- select emp_id from salaries order by salary desc limit 0,3;
-- 主查询：职工号、姓名
select e.emp_id,e.emp_name
from employees as e
-- 内连接子查询，过滤非前三名员工
inner join (
select emp_id 
from salaries 
order by salary desc 
limit 0,3
) as s 
on e.emp_id = s.emp_id;
-- 方式二
-- 先连接，再过滤
select e.emp_id,e.emp_name
from employees as e 
inner join salaries as s 
on e.emp_id = s.emp_id
order by s.salary desc 
limit 0,3;

8. 列出工资在1000－2000之间的所有职工姓名。

-- 子查询：获取工资表中1000-2000之间的emp_id
-- select emp_id from salaries where salary between 1000 and 2000;
-- 主查询：1000-2000工资员工的姓名
select emp_name
from employees
-- 匹配子查询的emp_id
where emp_id in (
select emp_id 
from salaries 
where salary between 1000 and 2000
);

9. 列出工资比王昭君高的员工。

-- 子查询：王昭君的工资
-- select s.salary from employees as e inner join salaries as s on e.emp_id = s.emp_id where e.emp_name = '王昭君';
-- 主查询：比王昭君工资高的员工信息
select * 
from employees as emp 
inner join salaries as sa on emp.emp_id = sa.emp_id
where sa.salary > (
select s.salary
from employees as e 
inner join salaries as s on e.emp_id = s.emp_id
where e.emp_name = '王昭君'
);

10. 列出每个部门中工资小于本部门平均工资的员工信息。

-- 子查询：获取每个部门平均工资按部门id分组
-- select emp.dep_id,avg(s.salary) as dep_avg_salary 
-- from employees as emp 
-- inner join salaries as s on emp.emp_id = s.emp_id
-- group by emp.dep_id;
-- 主查询：
select e.emp_name,s.salary 
from employees as e 
inner join salaries as s on e.emp_id = s.emp_id
inner join(
select emp.dep_id,avg(s.salary) as dep_avg_salary 
from employees as emp 
inner join salaries as s on emp.emp_id = s.emp_id
group by emp.dep_id
) as temp on e.dep_id = temp.dep_id
-- 过滤比各部门平均工资高的员工
where s.salary < temp.dep_avg_salary;

本文标签： 基础知识数据库测试软件