数据库的认识和基础操作

向右看齐 2024-04-06 13:44 20阅读 0赞

#### 目录 ####

*   *  数据库产生背景
     *  主流的数据库
     *  服务器，数据库，表关系
     *  MySQL基础操作
     *  MySQL架构
     *  SQL分类
     *  存储引擎
     *  MySQL详细操作
     *   *  创建库
         *  修改库
         *  删除库
         *  数据库的备份和还原
         *  查看连接情况
         *  创建表
         *  查看表结构
         *  修改表

### 数据库产生背景 ###

传统文件保存数据的缺点:

*  文件的安全性问题
 *  文件不利于查询管理数据;(如果我要中间插入，难道要打开文件遍历解着插入移动后半部分数据吗?)
 *  文件不利于海量数据存储(不断地插入删除,意味着不断地打开关闭IO流,效率问题？)
 *  文件在程序中控制不方便

因此，专家设计出了数据库这个东西，专门来高效管理数据;

数据是it业的石油，**数据库的水平是衡量一个程序员水平的重要指标**;

### 主流的数据库 ###

*  SQL Sever:微软的产品，.Net程序员的最爱，中大型项目。
 *  Oracle:甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
 *  MySQL:世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
 *  SQLite:是一款**轻型的数据库**，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多**嵌入**式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。

### 服务器，数据库，表关系 ###

*  安装数据库的同时会安装数据库服务器和数据库客户端;
 *  1.  所谓安装数据库服务器，只是在机器上安装了一个数据库管理**系统程序**，这个管理程序可以管理多个数据库， 一般开发人员会针对每一个应用创建一个数据库。
 *  1.  安装客户端，是为了方便操作人员通过客户端程序+一系列SQL语言与数据库server进行交互;
 *  为保存应用中**实体的数据**，一般会在数据库中**创建多个表**，以保存程序中实体的数据;(正式员工表，外包员工表)

数据库服务器、数据库和表的关系如下:

![在这里插入图片描述][671ef340377b45549e030efc0c4124b1.png]

### MySQL基础操作 ###

我们主要研究Linux下,mysql5.7版本数据库（安装client端和server端过程省略）为了更好的可视化和便捷操作，可以选择win端配合如SQLyog这类可视化工具进行操作;

mysql -h 127.0.0.1 -P 3306 -u root -p //Liunx连接sql服务器
    
    /*SQL语句每部分后面都需要加;否则识别不出来;*/
    create database helloworld;   /* 创建数据库helloworld */
    
    use helloworld /* 使用(进入)数据库 */
    
    create table student( /* 在数据库中创建表(列属性) */
    id int,
    name varchar(32),
    gender varchar(2)
    );
    
    insert into student (id, name, gender) values (1, '张三', '男');/* 表中插入数据 */
    insert into student (id, name, gender) values (2, '李四', '女');
    insert into student (id, name, gender) values (3, '王五', '男');
    
    select * from student /* 查询表中所有数据(后可跟where name = 'xxx';查询限定条件的记录) */

在Linux下连接mysql后,需要执行shellcode的话需要在指令前面加system \[shellcode\]

![在这里插入图片描述][74a76637d4ee46ceba1abd77095ddc29.png]

![在这里插入图片描述][d8a8b09870884996981f82bab28c9cba.png]

### MySQL架构 ###

MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行，如 Unix/Linux、Windows、Mac 和 Solaris。各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台上的物理体系结构的一致性。

![在这里插入图片描述][1245e17b05834a54a8bfb4a95291952a.png]

### SQL分类 ###

*  DDL【data definition language】 数据定义语言，用来维护存储数据的结构 代表指令: create, drop, alter
 *  DML【data manipulation language】 数据操纵语言，用来对数据进行操作 代表指令： insert，delete，update
 *  DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务 代表指令： grant，revoke，commit

### 存储引擎 ###

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

MySQL的核心就是插件式存储引擎，**支持多种存储引擎**。

show engines; //查看存储引擎

![在这里插入图片描述][f85042f07351438db6ae87ebe1377f42.png]

![在这里插入图片描述][585607a027f846359bab2153d4fe772c.png]

### MySQL详细操作 ###

#### 创建库 ####

create database xxx [charset=utf8 collate utf8_general_ci];//[字符集，校验规则]为可选项,一般配置好的default自动填;
    
    create database test1 [collate utf8_general_ci];//utf8_general_ci校验规则不区分大小写
    create database test2 [collate utf8_bin区分大小写];//utf8_bin区分大小写

创建库之后,对应的文件会多出这个库的有关文件用来保存；

则假设有表:

create table person(name varchar(20));
    
    insert into person values('a');//四条记录
    insert into person values('A');
    insert into person values('b');
    insert into person values('B');

utf8\_general\_ci校验规则不区分大小写**查询**结果:

![在这里插入图片描述][6550b6af5c744d608e2079827b62b77f.png]

utf8\_bin区分大小写查询  
![在这里插入图片描述][47debd390b3c4775b2e6e63b26c37309.png]

utf8\_general\_ci校验规则不区分大小写**排序**结果:

![在这里插入图片描述][ae0254c7564e43c08188a71277cc524e.png]

utf8\_bin区分大小写排序

![在这里插入图片描述][5c44ca4979314b56ad965eb81f69fec6.png]

可见,校验规则根据业务设定的不同有重要作用;

#### 修改库 ####

eg:将 mytest 数据库字符集改成 gbk

![在这里插入图片描述][d8301604ec5741e190e85b48d2d27ca0.png]

#### 删除库 ####

DROP DATABASE db_ name;

执行删除之后的结果:

*  数据库内部看不到对应的数据库;
 *  对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

#### 数据库的备份和还原 ####

这个工作一般是DBA数据库管理员做的;

备份:

mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql //备份表只需要在mytest数据库名后添上表名
    
    mysqldump -P3306 -u root -p123456 -B mytest table1 > D:/mytest.sql //备份表

还原:

source D:/mysql-5.7.22/mytest.sql;

*  如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用 source来还原。
 *  备份的本质是server之前把每一次对某个库或者表的操作sql命令记录下来,存在一个文件里，恢复的时候相当于重新执行一遍所有的之前执行过的命令;

#### 查看连接情况 ####

show processlist; //查看多少人连接了你的MySQL服务器

![在这里插入图片描述][31b2916d4e5b4a0c8dd3d687b2eb7634.png]

#### 创建表 ####

create table users (
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '密码是32位的md5值',
    birthday date comment '生日'
    ) character set utf8 engine MyISAM;

自定义的属性名 : 类型（comment +'xx’代表说明,类似注释）

id : int

name : varchar(20)

password : char(30)

birthday : date

#### 查看表结构 ####

desc 表名;

![在这里插入图片描述][3fa24200042c4804be5e67195a4544d5.png]

#### 修改表 ####

insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04'); //users表中插入两条记录

**users表中添加属性**

alter table users add path varchar(100) comment '图片路径' after birthday; //添加path属性在birthday后面！,之前的记录这个属性自动设为NULL;

![在这里插入图片描述][da705f07288242df81e4cf9df210c4ba.png]

**修改users表中name属性,长度变为60**

alter table users modify name varchar(60); //修改name属性,长度变为60

![在这里插入图片描述][d2b90508032a432fbe5d1c66c6fb1e0a.png]

**删除password列**

> 注意：删除字段一定要小心，删除字段及其对应的列数据都没了

alter table users drop password;

![在这里插入图片描述][668d96cc48854c378dbb7fdd1f979882.png]

**修改名称**

alter table users rename to employee;//修改表名为employee
    alter table employee change name xingming varchar(60); //将name列修改为xingming,注意修改名称也需要重新定义类型

可以看到，修改表内容操作关键就是看第四个单词,有drop(删除属性)，rename(修改表名)，change(更换属性名称)，modify(修改属性大小)，add(添加属性)

**删除整个表**

drop table t1;

[671ef340377b45549e030efc0c4124b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/134ba43a6b0848089e0d4a954b9574de.png
[74a76637d4ee46ceba1abd77095ddc29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/29d51149a3c34c199d4eb5a7a2062b09.png
[d8a8b09870884996981f82bab28c9cba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/b231d95029b645c48bb46ccf4e77322f.png
[1245e17b05834a54a8bfb4a95291952a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/77fc2bf1a54a4bb898a3336d36ac305d.png
[f85042f07351438db6ae87ebe1377f42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/9dce887747c349b4bc34fe31666566ff.png
[585607a027f846359bab2153d4fe772c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d0c0c7cbd875452d860868ddcd245450.png
[6550b6af5c744d608e2079827b62b77f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8e0834ca14a54752b5bb44645e025085.png
[47debd390b3c4775b2e6e63b26c37309.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/86e033956b2640ccb705af18be1b158b.png
[ae0254c7564e43c08188a71277cc524e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/8602e3bb36bb46179354366ad254002a.png
[5c44ca4979314b56ad965eb81f69fec6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7110119406cb4a90a4f293893674da5b.png
[d8301604ec5741e190e85b48d2d27ca0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4b1f94eeaa6c48deaeb6e74ea4f3ba87.png
[31b2916d4e5b4a0c8dd3d687b2eb7634.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/3cbd1e42250041699ccfa3bc44719fd6.png
[3fa24200042c4804be5e67195a4544d5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e70d68c723af4233a1e50517ccfddf12.png
[da705f07288242df81e4cf9df210c4ba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4a1d1cd43da44df989bf47a75f85fe7d.png
[d2b90508032a432fbe5d1c66c6fb1e0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/6a74813cc2f14133a0879d14e032e0a1.png
[668d96cc48854c378dbb7fdd1f979882.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ea47af7113bc4da5aee8e59c6405fe59.png