【MySQL】MySQL数据库结构与操作

 

🌴一. 数据库介绍

🌿1.1 什么是数据库

和我们表面所见一样顾名思义就是用来储存数据的

存储数据用文件就可以了为什么还要弄个数据库?

文件保存数据有以下几个缺点

• 文件的安全性问题
• 文件不利于数据查询和管理
• 文件不利于存储海量数据
• 文件在程序中控制不方便

为了解决上述问题专家们设计出更加利于管理数据的软件——数据库它能更有效的管理数据。数据库可以提供远程服务即通过远程连接来使用数据库因此也称为数据库服务器。

 

🌿1.2 数据库分类

数据库大体可以分为 关系型数据库 和 非关系型数据库

• 关系型数据库RDBMS
   
  是指采用了关系模型来组织数据的数据库。 简单来说关系模型指的就是二维表格模型而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
   
  基于标准的SQL只是内部一些实现有区别。常用的关系型数据库如
   
  1.Oracle甲骨文产品适合大型项目适用于做复杂的业务逻辑如ERP、OA等企业信息系统。收费。,世界上最强数据库对数据安全性要求极高且收费高
  2.MySQL属于甲骨文不适合做复杂的业务。开源免费所以广受欢迎。
  3.SQL Server微软的产品安装部署在windows server上适用于中大型项目。收费。
   
  典型特征使用 " 数据表 " 的格式来组织数据的
   

• 非关系型数据库
   
  了解不规定基于SQL实现。现在更多是指NoSQL数据库如
   
  1.基于键值对Key-Value如 memcached、redis
  2.基于文档型如 mongodb
  3.基于列族如 hbase
  4.基于图型如 neo4j
   
  所以Nosql是不用数据表来组织数据的

关系型数据库与非关系型数据库的 区别

关系型数据库	非关系型数据库
是使用SQL	不强制要求一般不基于SQL实现
支持事务支持	不支持事务支持
支持复杂操作	不支持复杂操作
海量读写操作效率低	海量读写操作效率高
基本结构基于表和列结构固定	基本结构灵活性比较高
使用场景在业务方面的OLTP系统	使用场景在用于数据的缓存、或基于统计分析的OLAP系统

注OLTPOn-Line Transaction Processing是指联机事务处理OLAPOn-Line AnalyticalProcessing是指联机分析处理。

 

🌲二. MySQL的结构

🍂2.1 MySQL服务器和客户端

MySQL是一个 ” 客户端 - 服务器 “ 结构的程序

• 客户端主动的一方就是客户端
• 服务器被动的一方就是服务器

一个服务器可能同一时刻要给多个客户端提供服务

例如我们生活中的例子
 
我们去餐厅就餐我们要求老板来一份青椒炒肉丝盖浇饭没一会儿老板就端上来了一碗香喷喷的盖浇饭

此时我们就可以把这一事件的性质代入到数据库中

客户端给服务器发送的数据称为 ” 请求 “来一份青椒炒肉丝盖浇饭
服务器给客户端返回的数据称为 “ 相应 ”端上来了一碗香喷喷的盖浇饭

咱们安装的MySQL是既带有客户端也带有服务器

MySQL客户端默认自带命令行客户端

除此之外还有一些第三方客户端有些是带图形化界面的

MySQL客户端和MySQL服务器可以在同一个主机上也可以在不同的主机上。

在实际工作中更常见的是客户端和服务器在不同的主机上而我们学习中客户端和服务器更多的是在同一个主机上虽然是同一个主机但是仍然是通过网络进行通信的

对于MySQL来说储存和管理数据都是由MySQL服务器来负责的
 
因此MySQL服务器要比MySQL客户端复杂很多甚至可以认为MySQL服务器就是MySQL的本体大部分时候谈及MySQL就是代指了MySQL服务器

 

🍂2.2 MySQL服务器是如何组织数据的

一个MySQL服务器可以包含多个 " 数据库 " 此处的 " 数据库 " 其实是 " 数据集合 "这里面就放了一些具有关联关系的数据

例如我们牛客网为例
 
题库各种公司往年真题在线编程专项练习
面试各种工程师面经以及详细题目汇总
求职校招社招实习生的招聘
 
以上的这些项目都没必要每个数据都安排一个主机一个主机一个MySQL服务器就可以管理起来但是为了不让这些数据相互影响因此逻辑上分开了也就放到不同的数据库里了
 
每个数据库数据集合里面有很多数据表
例如题库表通过率排行榜…

一个表里有很多行每一行称为 " 一条记录 "
每一行也有很多列每个列表示不同的含义每一列也称为一个" 字段 " (field)

所以综合就是数据库 --> 数据表 – > 行 --> 列

不仅是MySQL这样组织数据只要是关系型数据库都是按照上述的结构来组织数据的

 

🌳三. 数据库的操作

🍃3.1 创建数据库

create database 数据库名;

示例

• 创建名为java的数据库

当我们看到如上就是数据库创建成功了

会有很多人疑问0.00 sec是什么

sec ==》 second 秒
 
表示时间的也反应了计算机的操作效率换句话说为什么会有非关系型数据库的存在呢因为非关系型数据库约束更少效率更高也更适合于当下流行的 " 分布式系统 "

注意

• createdatabase都是SQL中的关键字不能拼写错误了单词中间有空格(一个或者多个都行一般是一个)
• 关键词不区分大小写
• 末尾分号不要遗忘是英文分号

说明当我们创建数据库没有指定字符集和校验规则时系统使用默认字符集utf8校验规则是utf8_ general_ ci

字符集描述了储存的数据都支持啥样的字符校验规则就描述了当前字符之间该如何进行比较比较字符串大小相等关系一般都是默认规则很少进行手动干预

 

🍃3.2 显示当前的数据库

show databases;

我们可以看到除了我们自己创建的数据库java还有一些系统自带的数据库

注意

• 此处databases是复数输出的是很多数据库不要忘记加s
• 打印数据库会有很多系统自带的数据库也存在

 

🍃3.3 使用数据库

use 数据库名;

这样我们就选中数据库java完成了

 

🍃3.4 删除数据库

drop database 数据库名;

示例

• 删除名为java的数据库
  
  这样就完成了我们的数据库的删除我们打印出来看看验证一下
  
  此时就可以看到java数据库确实被删除了

说明

• 数据库删除以后内部看不到对应的数据库里边的表和数据全部被删除
• 千万不要随意删除数据库特别是不要在公司的生产环境服务器上进行删除

在此处我们拓展一下关于如何避免不小心删库的带来的危害或者把损失降到最低呢

• 权限设置少数人有可以修改和删除数据库的权限
• 备份把数据拷贝一份存到另外的地方
• 硬盘数据恢复由于MySQL是把数据存储在硬盘上所以删除了数据库的数据是可以在硬盘上恢复数据的

我们在这里详细讲解下如何恢复硬盘数据的

操作系统为了方便进行管理把整个硬盘分为了若干个 " 盘块 " 每个盘块都可以保存一定的数据所以实际上每个文件可能是由一个或者多个盘块上面的数据组成的

当操作系统删除文件的时候为了提高删除动作的效率在删除的时候并不是真的把硬盘上之前保存的数据擦除掉而只是把该文件对应的盘块标记成 " 无效状态 "因此一旦出现误删库的情况下就要尽快让主机断电就避免了操作系统把这些被标记成无效的盘块给分配出去不一定百分之百能恢复大概率是只能恢复一部分

 

🌵四.常用数据类型

MySQL是一个关系型数据库典型特点就是通过表的形式来组织数据的表格的特点就是 " 特别整齐 " 每一行列数都是一样的每一列数据都是同类的(数据类型相同)数据类型一方面能够方便我们对数据进行处理一方面也能够针对数据进行校验和检查。
 

🍀4.1 数值类型

分为整型和浮点型

扩展

• 数值类型可以指定为无符号unsigned表示不取负数。
• 1字节bytes= 8bit。
• 对于整型类型的范围
   
  1.有符号范围-2 ^类型字节数8-1到2 ^类型字节数8-1-1如int是4字节就是-2 ^ 31到2 ^ 31-1
  2.无符号范围0到2 ^类型字节数*8-1如int就是2 ^ 32 - 1

尽量不使用unsigned对于int类型可能存放不下的数据int unsigned同样可能存放不下与其如此还不如设计时将int类型提升为bigint类型。

• DECIMAL相比于FLOAT和DOUBLE可以更精确的表示小数。DECIMAL相当于通过字符串的方式来表示浮点数优势就是能够精确表示精确计算但是劣势就是计算时消耗的更多储存空间也更多。除非是特别需要才考虑使用DECIMAL

 

🍀4.2 字符串类型

扩展

• VARCHAR (SIZE)是最常用的类型
   
  例如varchar(50)这个字段最多存50个字符(注意不是字节)也可能更短。动态的根据存入的数据长度来自适应空间。一个字节固定为8个bit一个字符就不是了取决于具体的字符编码Java中默认使用的是Unicode编码utf8还不太一样背后有着千丝万缕的关系

• 65535字节 – > 拓展一个字节表示的整数-128 => 127 --> 0 => 256 ;两个字节表示的整数-32768 => 32767 --> 0 => 65535 ;四个字节表示的整数-21亿 => 21亿 – > 0 =>42亿9千万

• 上面三个存储的是文本数据BLOB存储的是二进制数据。文本数据里面存储的数据都是ASCLL字符二进制数据啥数据都有可能

 

🍀4.3 日期类型

• TIMESTAMP 时间戳以1970年1月1日0时0分0秒作为基准时刻计算当前时刻和基准时刻的秒数之差
• 在百度里可以搜索到时间戳最大表示21亿现在已达到16亿了一旦到达极限很多代码都会失效不建议使用推荐使用DATATIME
  

总结一下常用的类型int , bigint , double , decimal , varchar , datetime。

 

🎋五.表的操作

🌱5.1 创建表

create table 表名(列名 类型列名 类型…);

• 和常见的编程语言不同SQL中列名写在前类型写在后C++Java都是把类型写在前变量名写在后但是像GoPython类型也是写在变量名的后面的

想要创建表就需要先有一个数据库并且选中之

1. 我们上面已经创建了一个Java数据库选中Java数据库即可

mysql> use java;
Database changed

2. 创建一个学生表带有id和name

mysql> create table student(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

创建表的时候必须要明确表结构也就是有哪些列每个列是啥类型叫啥名字

注意

1. 同一个数据库中不能有两个表名字相同
2. 创建表的时候表名或者列名不能和SQL的关键字冲突如果非要使用需要把表名用反引号`引起来

拓展

当我们有的时候创建表语句可能比较复杂比较长就可以分成多行来写

mysql> create table test(
    -> id int,
    -> name varchar(20));
Query OK, 0 rows affected (0.01 sec)

但是体验感却不是很好多行编辑一旦按了回车就再也退不回去了例如

mysql> create table test2(
    -> id int,
    -> name varchar(20),
    ->

我们意识到只有两个类型的时候当开启第三个回车的时候就回不去了

解决办法
 
可以在其他编辑器里把SQL写好然后复制粘贴过来
也可以是记事本其他文本编辑器都可以最主要的时编写好SQL语句再复制粘贴过来就可以

复制粘贴到我们的命令行里

于是test2表就创建好了(其实在坏的表里加个 ; 就可以退出上面操作其实也一样)

 

🌱5.2 查看有哪些表

在选中数据库的前提下使用show tables; 来查看当前数据库里有哪些表

mysql> show tables;
+----------------+
| Tables_in_java |
+----------------+
| student        |
| test           |
| test2          |
+----------------+
3 rows in set (0.00 sec)

 

🌱5.3 查看表结构

desc 表名;

查看这个表里面的列和类型

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

• 每一列都是都是一个字段Field表示表这里有几列
• int(11)的意思不是占11个bit位int就是固定4个字节占32个比特位11表示的是打印数字的时候显示数据的最大宽度是11位数11只是影响在客户端中的显示不影响数据的最大储存和计算
• varchar(20)约束存储的时候最多存20个字符
• Null表示这一列是否可以为空(不填选填项)
• Key后期再提及
• Default是默认值此处的Null就是说默认的默认值是Null(不填)也能手动修改默认值
• Extra额外的默认值后面再详解

 

🌱5.4 删除表

drop table;

实例操作

先打印存在的所有的表然后分别删除test2test就可以看到最后打印确实没有被删除了

注意

1. 删表操作非常危险危害比删库只多不少
2. 表面上看一个库里包含很多表删库就会直接把所有的表都带走如果真的删库了程序在运行的时候只要涉及到数据库操作100％报错第一时间就可以发现问题但是如果是删表100个表删了其中的1个表此时程序运行的时候可就不一定第一时间报错了很可能程序"带伤运行"虽然程序能跑但是结果是错的它的问题不能第一时间暴露出来

 

🌱5.5 创建数据库表

创建商品goods赋予一些属性商品编号名称单价的单位是分分类供应商名称

mysql> create table goods (
    ->     goods_id int,
    ->     goods_name varchar(20),
    ->     unitprice int,
    ->     category varchar(20),
    ->     provider varchar(20)
    -> );

括号里的数字我们处于学习阶段就可以随便写够用就可以
在实际的开发中是要根据产品需求来确定的

表的建立和查询