MySQL数据库的基本操作一

目录

什么是MySQL数据库?

数据库的基本操作

数据库操作

表操作 

MySQL的增删改查

插入操作

查找操作

修改操作

删除操作

数据库约束

聚合查询 

聚合函数

分组查询 

条件筛选分组查询

 联合查询

笛卡尔积

内连接

子查询

合并查询

---

什么是MySQL数据库?

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
• 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 为关系型数据, 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

• 表头(header): 每一列的名称;
• 行(row): 每一行用来描述某条记录的具体信息;
• 列(col): 具有相同数据类型的数据的集合,每一行有很多列,每个列也称为一个字段;
• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
• 键(key): 键的值在当前列中具有唯一性。

数据库的基本操作

在学习前先安装好mysql客户端,后续的sql操作都是在自带的mysql客户端来进行编写的.

首先打开客户端会出现下面操作,让我们输入安装数据库的时候设置的密码

输入密码后出现下面界面说明登录成功

 数据库操作

1.  查看当前数据库    show  databases;

        ●show 和databases 之间有一个或者多个空格

        ●使用英文分号结尾(客户端里的任何一个SQL都需要使用 分号 来结尾)

客户端允许一个sql语句分多行写,如果不写 ; 直接换行,此时客户端认为一个sql语句还没写完

2.  创建数据库   create database 数据库名 ;

        ●数据库的名字 由数字,字母和下划线组成,其中数字不能开头,名字不能是SQL中的关键字

        ●如果想用关键字作为数据库名,可以使用(反引号 `) 把数据库的名给引起来 (不建议大家使用)

        ●数据库的名字不能重复

下面创建一个 Student数据库,出现下面界面说明创建成功

在创建数据库的时候可以指定 字符集 

 create database [ 数据库名 ] charset utf8 

这里我们指定的字符集是 utf8 ,utf8是通用的字符集, 使用的更广泛

在我们的编码中如果不指定字符集(用默认的) 很可能导致插入中文就插入失败

3.   选中数据库     use 数据库名;

对某个数据库进行增删改查,要先选中这个数据库,在进行操作

下面选中student数据库进行操作

4.   删除数据库  drop database 数据库名; 

删除操作非常非常危险,删除前一定要慎重在慎重 !!!

下面进行删除student数据库操作,出现下面界面删除成功

表操作 

1.   查看数据库中的表 : show tables;

创建一个名叫java的数据库,并查看当前数据库中的表,在对表进行操作前一定要先选中数据库

因为当前没有创建表,所以没显示出Java数据库中的表

2.   创建表 :   create table 表名 (列名  类型, 列名 类型.......) ; 

创建表的时候需要指定列,以及列的类型,接下来认识一下数据库典型的类型

下面创建一个学生表 

数值类型

double(m,n)  m表示有效数字,n 表示小数点后的位数

日期和时间类型

字符串类型

varchar(size) 是常用的字符串类型,作用是设置一个变长字符串,size指定的是最大长度,单位是字符

3.   查看指定表的表结构   desc 表名 ;

 4.   删除表:  drop table 表名 ;

下面演示删除student 表

MySQL的增删改查

插入操作

1. 新增 / 插入数据 : insert into 表名 values ( 值, 值 ......) ;

括号里的内容要求  个数,类型,顺序和表头结构保持一致

下面在student 表中添加数据

2.  指定列进行插入 : insert into 表名 (指定的列......)  values (值 , 值) ; 

在studebt表中给姓名和性别进行插入 

3.  一次插入多个记录 : insert into 表名 values (值 ),( 值)...(值 ) ; 

在student 表中插入两个记录

4.  时间类型数据的插入 ;  使用的是datetime类型的类

在插入的时候使用形如 '2023-10-12 21:21:21'这样的格式

如果我们在插入的时候想把日期设置成当前时刻 可以用sql提供的函数 now(); 

查找操作

1. 全列查找:  select * from 表名 ;

将表中的数据全部查找出来.

2. 指定列查询:  select 列名, 列名... from 表名 ;

在student表中查找name 和gender

3. 表达式查询:  在查询过程中让列和列进行简单的运算

接下来都是对下面这个表进行操作

给所有人的数学成绩 + 10 分

 因为mysql是一个客户端服务器结构的程序,此时这张表是一个临时表,和原始的表没有关系,显示一下就销毁了

4. 查询的时候可以指定别名. 使用 as关键字来指定别名

按照表达式查询,临时表的列名和表达式一样,在查询的时候可以指定别名,可以让我们更方便的来理解含义

下面将三个人的总成绩设置成total 

5. 去重查询:  使用 distinct 关键字针对列去重(把重复的去掉)

把数学成绩中重复的去掉

distinct对多个列进行去重查询的时候,要多个列都相同才能去重

6. 查询结果排序:  order by子句,指定某些列进行排序

对语文成绩进行升序排序 : 

如果不指定排序,mysql默认是升序排序

对英语成绩进行降序排序 使用desc  在这里desc是descend的缩写

7. 条件查询  通过where子句搭配条件表达式来进行查询

   select * from 表名 where 条件表达式  或者 

   selecct 列名,列名....from 表名 where 条件表达式

 在条件查询中,别名不能作为where条件来进行查询

通过一系列的运算符来表示条件 

         ●一个where中既存在and又存在 or,先执行and 在执行or 

in 运算符  查询数学成绩是 65, 83, 98分 的同学的数学成绩

8.  模糊查询:  like 

支持两个用法 :  

        ●使用 % 代表任意0个或者N个字符

        ●使用 _ 代表任意一个字符

查询姓孙的同学的成绩

       

9.  分页查询: limit 

只获取前三个 数据

还可以通过 limit 搭配 offset 指定从那一列开始查询

接下来写一个复杂的查询语句 (查询总分前三名的同学信息)

::

修改操作

基本用法: update 表名  set 列名 = 值,列名 = 值... where 条件表达式 ;

将孙悟空的数学成绩加10分

删除操作

基本用法: delete from 表名 where 条件表达式 ;  把条件匹配的操作全删除掉

把姓孙的记录全部删除

数据库约束

在创建表时,可以给表中的列名添加约束,主要有下面几种约束

下面创建一个表并且设置约束 id 为 not null ,name 的默认值是无名氏,这样我们在插入数据的时候id一定要插入数据,name如果没有插入数据就是 无名氏

1. primary key 主键

mysql中要求primary key是非空的并且是唯一的,相当于unique + not null,可以通过自增主键来解决在增加数据的时候出现重复的情况

给自增主键插入数据的时候,可以手动指定一个值,也可以让mysql自动分配,如果让mysql自动分配,在insert的时候 把id设为null即可

2.  foreign key 外键

建立student表和class表 通过foreign key来连接这两张表 

此时要求student表中的每个记录的classId要在class表中的classId中存在

student表受到class表的约束,就把class叫做student的父表,student是class的子表

约束之间都是相互的,父表约束子表,子表也会约束父表.

比如下面情况

把查询结果作为新增的数据:  insert into 表名1 select * from 表名2 ;

将表二中查询到的结果插入到表一当中. 需要表二查询的结果得到的列数和类型和表一中的列数.了类型匹配

聚合查询 

聚合函数

  在查询过程中行和行之间进行一定的计算.在SQL中提供了依赖聚合函数

1. count (*)   计算得到的结果的行数

下面演示计算一下student表中有多少列 

在查询过程中,如果有一列有null,用count (指定列)查询的时候是不计算在里面的.

2. sum求和 :  只针对数字列有效,如果是字符串列则无法计算

下面计算exam_result表中语文成绩的总和

3. 计算平均值  avg(表达式)

在exam_result表中计算三门课程的平均值

4. 求最大,最小值  max(指定列)  min(指定列)  

下面求语文成绩的最大值和最小值

分组查询 

使用 group by 进行分组  在下面表中求出每个岗位的平均薪资

代码

 select 指定的列,要么带聚合函数的,要么就是指定的 group by指定的列

条件筛选分组查询

在分组的时候我们可以进行条件筛选

        ●分组前,筛选 : 使用 where 条件

求每个岗位的平均薪资,但去除刘玄德 

        ●分组后筛选,使用 having条件

求每个岗位的平均薪资,但不包括老板

          ●同时在分组前和分组后筛选

  求每个岗位的平均薪资,但不包括老板和曹孟德

 联合查询

 笛卡尔积

笛卡尔积是两个表排列组合得到的,新的表的行是原来表行的乘积,新的列是原来两个表之和, 由于是排列组合得到的,有些数据就是无效的,去掉无效数据之后,剩余的信息就能表示每个同学的信息了,向上面两个classId能对得上,就可以用where子句来描述,基于笛卡尔积 + 条件进行查询,就是联合查询,或者叫多表查询

首先我们先建四张有关联得表

📕classes班级表

📙student学生表

 

📗course课程表

📒score分数表

  实际上，在这个场景中，涉及到的主体有三个，学生，班级和课程，学生和班级是一对一的关系，学生和课程是多对多的关系，而分数表，就是学生和课程之间的关联表。

下面正式开始介绍我们的联合查询中的各种查询~

内连接

以查询许仙 同学的 成绩  为例讲解内连接

通过观察 要查询许仙同学的成绩,就要找到许仙 和成绩分别在student表和score表中,对两个表进行笛卡尔积,此处内容太对就不全部显示出来

通过连接条件缩小范围,我们观察可得,他们的id 是一样的

我们只需要查询许仙的成绩,,我们只需要查询name列和score 列,并且用where 条件指定name = '许仙'  ,就得到许仙同学的所有成绩

多表查询一般实现步骤

1.  分析需求中涉及到的信息都在哪些表里

2.  针对表中数据进行笛卡尔积

3.  筛选出有效数据

4.  结合需求中的条件进一步加强条件

5.  针对列进行精简

子查询

把条件中的某一个值用另外一个查询语句表示出来

查询 和 白素贞 同班的同学的名字

首先用一个查询语句查出白素贞同学所在的班级,把这个语句作为条件用在另外一个查询语句中

合并查询

查询 id < 3 或者名字为'英文' 的课程

 关于上面查询语句我们可以使用 or 来完成,但or 只能针对一个表进行合并,union可以把多个表的查询结果合并,但要求多个结果的列要对应.使用范围更广

union 会自动去重 但union all 不会