数据库操作指南：MySQL与SQL语言详解-优快云博客

本文链接：https://blog.youkuaiyun.com/lurance_/article/details/124283703

本文详细介绍了数据库的分类，重点关注了关系型数据库中的MySQL，包括SQL语言的基础，MySQL的特点，数据库操作如查询、增删改，以及数据表操作。还探讨了数据备份的重要性，提供了一些实用的数据库操作技巧和命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、常见的数据库分类

当前主要使用两种类型的数据库：

关系型数据库：以数据表为核心

非关系型数据库：不存在数据表的概念

1、关系型数据库：通过行列关系（表）进行数据的存储

1.1代表产品：

mysql：使用最广泛免费的跨平台（安装再不同的操作系统）

Orcale：大型项目使用收费的跨平台（安装再不同的操作系统）

sql server：在微软项目中使用只能安装再windows系统

SQlite：轻量型，主要用于在移动平台

1.2关系型数据库的核心元素

数据行（一条记录）、数据列（字段）、数据表（数据行的集合）、数据库（数据表的集合）

2、非关系型数据库：数据以键值对、文本、图片等形式存储的数据构成

例如：redis：数据存储，key : value

mongoDB: 数据：json {}

二、SQL语言

说明：结构化查询语言，通过SQL语言可对数据库进行操作

注意：SQL语言默认支持操作所有常见的关系型数据库

作为测试人员，必须掌握SQL的查询语句（select）

SQL语句，不区分字母的大小写

三、MySQL数据库

特点：开源、免费、多平台/多语言

1、连接工具Navicat

由于数据库处于服务器中，使用工具远程连接操作数据库

2、连接数据库操作步骤：

创建数据库、连接数据库、新建数据表、设计数据表、增删数据表、操作数据

3、数据类型与约束

常用数据类型：int、decimal、varchar（0-65533）、datetime

约束：

主键：能唯一识别表中的每一条记录的属性组

非空：此字段不允许填写空值

唯一：此字段的值不允许重复

默认值：当不填写此值时会使用默认值

外键：一个表中的一个字段引用另一个表的主键

四、数据库操作—查询

1、创建数据库

    -- 创建数据库语法
    create database 数据库名 charset=utf8 collate=utf8_general_ci
    -- 查看数据库
    show create database 数据库名;

    create database python charset=utf8 collate=utf8_general_ci;
    show create database python;

2、使用数据库

    -- use 数据库名
    use pyhton;
    -- 查看当前数据库：database()是SQL的内置函数，括号不能省略！
    select database();

3、修改数据库

    -- 创建testpython数据库，字符集为gb2312
    create database testpython charset=gb2312;
    -- 修改
    alter database testpython;
    default character testpython;
    default CHARACTER set utf8mb4;
    default character utf8mb4_general_ci;

4、删除数据库和查看所有数据库

    -- 删除数据库
    drop database python;
    -- 查看所有数据库
    show databases;

5、重点：数据库备份

说明：在测试工作中，为了防止数据库产生错误操作，或产生垃圾数据，都需要在操作前，适当对数据库进行备份操作。

垃圾数据：例如在自动化测试中，对注册模块操作生成的所有数据，属于典型的垃圾数据，应该清理。

备份方法：还原数据库：

使用命令备份（Linux下）：

Mysqldump -u数据库用户名 -p 目标数据库名 > 备份文件名.sql;

Mysqldump -u数据库用户名 -p 目标数据库名 < 备份文件名.sql;

五、数据表操作

1、创建表

·语法格式
    create table 表名（
    字段名 类型（约束）
    ）;
·举例说明
    -- 完整创建
    -- unsigned 无符号
    create table student(
	    id int unsigned primary key auto_increment,
	    name varchar(10),
	    age int unsigned,
	    heigeht decimal(5,2)
    );

2、查看表信息

方法一：选择数据表、点击DDL查看

方法二：show create table student;

3、查看表结构（字段）：desc 表名

4、删除表：drop table 数据库名;

六、数据操作-增删改

1、添加数据

1.1 添加一行数据：insert into 表名 values(.....)

注意：1、数据值需要和表的字段一一对应（数据个数及数据类型）
2、主键列是自动增长，插入时需要占位，通常使用 0 或者 null 或者 default来占位，插入成功后一实际数据为准

    insert into student values(null,'张三',15,1.75);

    -- 部分字段设置值,字段顺序与值的顺序一一对应
    insert into student(name,height) values('李四',1.56);

1.2 插入多行数据

-- 方式1：将单行插入语句，多句执行，每句用逗号隔开
    insert into student values(null,'王五',15,1.75);
    insert into student(name,height) values('赵六',1.56); 

-- 方式2：
    insert into student values(null,'九九',15,1.75),(null,'十一',15,1.75); 
    insert into student(name,height) values('七七',1.56),('小八',1.56);

2、修改字段值：update 表名 set 列1=值1，列2=值2.....where 条件;

3、删除表数据：delete from 表名 where 条件;

逻辑删除(假删)：【通过逻辑选择不显示】

通过某一特定字段的特定值表示数据是删除，1删除或0未删除-- 修改要删除的数据的特定字段为删除状态

    update student set isdelete=1 where id=4;
    -- 查询所有isdelete 字段为0的所有数据
    select * from student where isdelete=0;

        其他数据删除方法：
                  delete from 表名：删除所有数据，但是不重置主键字段的计数
                truncate from 表名：删除所有数据，并重置主键字段的计数
                drop from 表名：删掉表（字段和数据均不存在）

七、数据操作-查询

1、基础查询

1.1 查询所有字段：select * from 表名;

1.2 查询部分字段：select 字段1，字段2 from student;

1.3 起别名

    select name as '姓名',age as '年龄' from student;
    -- 注：别名的引号可省略
    select name as 姓名,age as 年龄 from student;
    -- 注：别名的as可省略
    select name 姓名,age 年龄 from student;
    -- 起别名的作用：美化数据结构的显示效果；可以起到隐藏真正字段名的作用

1.4 去重：select distinct(字段名) from student;

2、复杂查询

条件查询：按照一定条件筛选需要的结果

排序：按照一定的排序规则筛选所需结果

聚合函数：对一组数据进行计算得到一个结果的实现方法

分组：在同一字段中，将相同值放在一组

分页：对大批量数据进行设定数量展示的过程

连接查询：将不同的表通过特定关系连接的过程

自关联：将同一表通过特定关系连接的过程

子查询：一个查询套入另一个查询的过程

以下对复杂查询展开详细说明:

1、条件查询

1.1 比较运算符（特殊：>=/<=/!=/<>)

select * from student where Sage=20;

1.2 逻辑运算符（and、or、not）

    -- 注：作为查询条件使用的字符串必须带引号
    select * from student where Sage=20 and Ssex='女';
    -- 补充需求：查询年龄等于20但不出自于计算机系的所有人员信息
    select * from student where not Dept='计算机系' and Sage=20;
    -- 注：not and or （双边连接条件）不同之处在于，not 只对自己右侧的条件有作用（右边连接条件）
    -- 不建议此方法
    select * from student where Sage=20 and not Dept='计算机系';

1.3 模糊查询：like + %（任意多个字符）/_(任意一个字符)

    -- 查询全部信息管理系学生信息
    -- %关键字%：关键字在中间
    select * from student where Dept like '%信%';
    -- %关键字：关键字在末尾
    select * from student where Dept like '%计';
    -- 关键字%：关键字在中间
    select * from student where Dept like '计%';

1.4 范围查询：in表示在一个非连续的范围内，in()

    -- 查询年龄在18-25之间
    -- between ... and ...表示在一个连续范围内，从小到大
    select * from student where Sage between 18 and 25;

1.5 判断为空:is null/is not null

    -- 查询没有院系信息的学生
    select * from student where Dept is null;
    -- 补充需求：查询有院系信息的学生
    select * from student where Dept is not null;

2、排序：select * from 表名 order by 列1 asc/desc，列2 asc/desc

默认按照列值从小到大排序，asc升序，desc降序

    select * from student order by Sage asc;
    select * from student order by Sage asc,Sno asc;
    -- 默认为升序，asc可省略
    select * from student order by Sage asc,Sno;

3、聚合函数

使用聚合函数方便进行数据统计；聚合函数不能在where子句中使用

常用聚合函数：count()、max()、min()、sum()、avg()

    -- 查询年龄最大、最小
    select max(Sage) from student;
    select min(Sage) from student;
    select avg(Sage) from student;
    -- 计算机系年龄总和
    select sum(Sage) from student where Dept like '计%';
    -- 以上聚合函数的综合
    select count(*),max(Sage),min(Sage),avg(Sage) from student;

4、分组

4.1 分组：select 字段1，字段2，..... from 表名 group by 字段1，字段2，......

    select count(*) from student group by Dept;
    -- 注：一般情况下，使用哪个字段进行分组，那么只有该字段可以在*的位置处使用，其他
    -- 字段没有实际意义（只要一组数据中的一条）
    --     分组操作多和聚合函数进行使用
    select Sname,count(*) from student group by Dept;

4.2 分组后过滤：select 字段1,... from 表名 group by 字段1,.... having 字段1,....聚合...

having 后面的条件运算符与where的相同

    -- 查询男生人数
    select count(*) from student where Ssex='男';
    select Ssex,count(*) from student group by Ssex having Ssex='男';

4.3 where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选

having 是对group by的结果进行筛选

having 关键字后侧可以使用聚合函数

5、分页查询

5.1 基本格式：select * from 表名 limit start,count;【start起始行数；count获取数据行数】

    -- 查询当前表中第5-10行的所有数据
    select * from student limit 3,5;
    -- 如果默认从第一行数据开始，0可以省略
    select * from student limit 5;

5.2 根据公式显示某页的数据：select * from 表名 limit (n-1)*m,m;

    -- 示例：每页显示4条数据，求显示第2页的数据内容
    select * from student limit 0,4;
    select * from student limit 4,4;

5.3 分页的应用

    -- 要求查询年龄最大的数据信息
    select * from student order by Sage desc limit 0,1;
    -- 进阶需求：要求查询商品价格最贵的前三条数据信息
    select * from student order by Sage desc limit 3;

6、连接查询

6.1 内连接：查询的结果为两个表匹配到的数据

    -- 显示效果：两张表中有对应关系的数据都会显示出来，没有对应关系的数据均不在显示
    select * from course inner join sc on course.Cno=sc.Cno;
    -- 扩充：给表起别名(缩短表名利于编写；用别名给表创建副本）
    select * from course co inner join sc on co.Cno=sc.Cno;
    -- 另一种写法
    select * from course,sc where course.Cno=sc.Cno;

注：表的顺序根据需求走，没有任何影响

6.2 左连接：查询的结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null

格式：select * from 表1 left join 表2 on 表1.列=表2.列;

如果要保证一张数据表的全部数据都存在，则一定不能选择内连接，可以选择左连接或者右连接

    -- 说明：以left join关键字为界，关键字左侧为主表（都显示），而右侧的表为从表
    -- （对应的内容显示）
    select * from course co left join sc on co.Cno=sc.Cno;
    -- 扩充需求：以分类为主展示所有内容
    select * from sc left join course co on sc.Cno=co.Cno;

注：关键字左侧主表，右表从表

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

格式：select * from 表1 right join 表2 on 表1.列=表2.列;

select * from sc right join course co on sc.Cno=co.Cno;

注：关键字右侧主表，左表从表

7、自关联

前提：数据表只有一张；数据表中至少有两个字段之间有某种联系

方式：通过给表其别名的形式，将原本只有一张的数据表变为两张，然后通过对应字段实现连接查询

    select * from address a1 inner join address a2 on a1.id=a2.parent_id;
    -- 使用内连接
    select * from address a1 inner join address a2 on a1.id=a2.parent_id where a1.name='北 京';
    -- 使用左连接
    select * from address a1 left join address a2 on a1.id=a2.parent_id where a1.name='北京 ';
    -- 查询河北省的所有的市和区
    select * from address a1 
    left join address a2 on a1.id=a2.parent_id 
    left join address a3 on a2.id=a3.parent_id
    where a1.`name`='河南省';

8、子查询

在一个select语句中嵌入了另一个select语句，称之为子查询

作用：子查询是辅助主查询的，充当条件/数据源。

    -- 子查询充当条件，求取平均值
    select avg(Sage) from student;
    -- 说明：充当子查询的语句需要使用括号括起来（运算符优先级）
    select * from student where Sage > (select avg(Sage) from student);
    -- 子查询语句充当数据源
    --注:连接查询的结果中，表和表之间的字段名不能出现重复，否则无法直接使 用
    -- 解决：将重复的字段使用别名加以区分（表名.*：当前表的所有字段）
    select * from (select co.*,co.Cno cno,co.Credit credit,co.Semester semester
from course co left join sc on co.Cno=sc.Cno) new where new.cno='C002';

9、外键：用来和其他表建立连接

说明：通过外部数据表的字段来控制当前数据表的数据内容变更，以避免单方面移除数据导致关联表数据产生垃圾数据的方法。

注意：如果大量增加外键设置，会严重影响数据查询操作意外的其他操作（增删改）的操作效率，因此在实际项目中很少会被采用，但是在面试中容易被问到。

9.1 设置主键：字段名数据类型 primary key;

    create table class(
    id int unsigned primary key auto_increment,name varchar(10)
    );

9.2 删除主键：alter table 表名drop primary key 主建名称;

9.3 设置外键：

    create table student1(
    name varchar(10),
    class_id int unsigned,
    constraint stu_fk foreign key (class_id)references class(id)
    );

9.4 删除外键：alter table 表名drop foreign key 外建名称;

10、索引：快速查找特定值的效率；提高查询排序的速度

    普通索引：没有不能重复得问题，加快了查询
    create index test on student(sno);
    唯一索引：数据值不能重复
    create unique index test on student(sno);
    联合索引：
    create unique index test on  stu31(name,sage);