朝花夕拾——数据库基本概念及Mysql基本命令操作

朝花夕拾——数据库基本概念及Mysql基本命令操作

一、数据库概述

1、什么是数据库？

数据库（Database）即存储数据的“仓库”，其本质是一个文件系统。它是⽤来保存结构化信息和数据的集合。实打实的物理磁盘上的文件即存储的数据集合。如有mysql、sys等的Data文件夹。简单来说就是⽤来存储和查询数据的。

但是存在混淆，有人常用数据库这个术语代表他们使用的数据库软件，这是不正确的，数据库软件应称为DBMS数据库管理系统。数据库是通过DBMS创建和操作的容器。

2、数据库管理系统DBMS

是实现对数据库资源有效组织、管理和存取的系统软件

功能：数据库的建立和维护功能、数据定义功能、数据操控功能、数据库的运行管理功能、通信功能。

DBMS的工作模式：

1. 接收应用程序的数据请求和处理请求
2. 将用户的数据请求转为复杂机器代码
3. 实现对数据库的操作
4. 从数据库的操作中接受查询结果
5. 对查询结果进行处理
6. 将处理结果返回给用户

3、数据库系统DBS

是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成

用户可以通过DBMS或应用程序操作数据库

二、数据库分类

1、关系型数据库

• 关系数据库，是建立在关系模型基础上的数据库。
• 关系型数据库是由多张能互相联接的二维行列表格组成的数据库。
• 关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。
• 当前主流的关系型数据库有Oracle、DB2、PostgreSQL、Microsoft SQL Server、Microsoft Access、MySQL、浪潮K-DB、MariaDB、SqLite等。

2、非关系数据库

非关系型数据库最大的特征就是没有表的概念，他是通过键值对的形式来进行数据的存储，

非关系型模型有2种：

• 1：列模型：存储的数据是一列一列的，这种模式（即数据即索引，常常存在于分布式系统中，查找效率比较高）
• 2：键值对模型:存储的数据是一个个键值对。

支持数据的读写分离，主从同步，提高数据的访问速度。

通过定时快照（snapshot）和基于语句的追加（AppendOnlyFile，aof）两种方式，redis可以支持数据持久化——将内存中的数据存储到磁盘上，方便在宕机等突发情况下快速恢复。

快照的核心原理就是把redis在某个时间内存内的所有数据都写入硬盘

追加：系统会把所有的redis数据进行的写操作的命令记录到硬盘上

3、关系型数据库和非关系型数据库的区别

3.1 关系数据库

优点:

1. 易于维护:都是使用表结构，格式一致;
2. 使用方便:sQL语言通用，可用于复杂查询;
3. 复杂操作:支持sQL，可用于一个表以及多个表之间非常复杂的查询。

缺点:

1. 读写性能比较差，尤其是海量数据的高效率读写;
2. 固定的表结构，灵活度稍欠;
3. 高并发读写需求，传统关系型数据库来说，硬盘I/o是一个很大的瓶颈。

3.2 非关系型数据库（NoSQL）

MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对
redis-memecache对比：
相同点：存储高热数据(在内存中高速运行）
不同点：redis可以做持久化保存，可以存储对象

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1. 格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，
2. 使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
3. 速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
4. 高扩展性；
5. 成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1. 不提供sql支持，学习和使用成本较高；
2. 无事务处理；
3. 数据结构相对复杂，复杂查询方面稍欠。

三、Mysql数据库

• mysql是一个关系数据库管理系统（RDBMS），所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
• 一款深受欢迎的开源关系型数据库
• 遵守GPL协议，可以免费使用与修改

特点：

1. 性能卓越、服务稳定；
2. 开源、无版权限制、成本低；
3. 多线程、多用户；
4. 基于C/S(客户端/服务器）架构；
5. 安全可靠：安全可靠的原因是mysql日志文件能备份，恢复，不怕数据丢失，是mysql最核心部分；

1、基本概念

常见数据类型

char和varchar的区别
对char来说，最多能存放字符个数255个，char如果存入数据的实际长度比指定长度要小 会补空格至指定长度。如果存入的数据的实际长度大于指定长度 低版本会被截取，高版本会报错

char的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar[10],如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了
1
2
3
4

Mysql数据文件

MysQL数据库的数据文件存放在‘/usr/local/mysql/data’目录下，每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为三个文件，扩展名分别为".frm"、“.MYD"和”.MYI”。

1、MYD文件

MYD文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAw表都会有一个"“.MYD"文件与之对应，同样存放于所属数据库的文件夹下，和”.frm"文件在一起。

2、MYI文件

“.MYI"文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM4存储来说，可以被 cache 的内容主要就是来源于”.MYI"文件中。每一个MyISAM表对应一个".MYI”文件，存放于位置和".frm"以及".MYD”一样。

3、MyISAM存储引擎

MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件(frm,myd,myi)。每个表都有且仅有这样三个文件做为MyISAM

存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。

另外还有".ibd"和 ibdata文件，这两种文件都是用来存放Innodb数据的，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据,还是独享表空间存放存储数据。独享表空间存储方式使用".ibd"文件来存放数据，且每个表一个".ibd"文件，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个(或者多个，可自行配置）ibdata文件。

2、基本操作

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

SQL语言分类：

1、DDL数据定义语言
	creat
	drop
	alter
2、DML数据操控语言
	insert
	update
	delete
3、DQL数据查询语言
    select
4、DCL数据控制语言
    grant
    revoke
1
2
3
4
5
6
7
8
9
10
11
12
13

1、查看数据库和数据库表的信息

show databases;
show databases\G
    //注：
    // \g 的作用是分号和在sql语句中写’;’是等效的
    // \G 的作用是将查到的结构旋转90度变成纵向(可以将一个很长的行转为好看的格式)
    
use 数据库名
    show tables;
1
2
3
4
5
6
7
8

2、显示表的数据结构

describe 表名
desc 表名
    两是一样的，只是desc是缩写
1
2
3

3、创建数据库、表

create database 数据库名；
create table 表名 (字段1 数据类型,字段2 数据类型,primary key(字段))； 
1
2

4、删除数据库、表

DROP TABLE 库名/表名；
1

5、插入数据

insert into 表名 （字段1，字段2....） values （字段1的值，字段2的值....）
1

6、查询数据

select 字段1、字段2... from 表名；
    where条件查询
    select 字段1、字段2...from 表名 where 条件；
1
2
3

7、更新数据

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
1

8、删除数据

DELETE FROM 表名 [WHERE 条件表达式];
1

9、修改表名

ALTER TABLE 旧表名 RENAME 新表名;
1

10、增加属性和修改原有属性数据类型

ALTER TABLE 表名 属性;
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key];
1
2

注：

主键：一个表中，只能有一个字段作为主键，并且不能为NULL,且值是唯一（可以多列作为复合主键，当所有设置为复合主键的列都相同是视为唯一，多对多的表中常用）。
    
唯一键，也称(唯一约束)，一个表中可以为有多个唯一键并且唯一键的值值可以为NULL，但NULL也不能重复，也就是说只能有一行的值为NULL。它会隐式的创建唯一索引。
    主键和唯一键的值都唯一，都不能重复。
1
2
3
4

11、复制表（简单的数据备份操作）

方式一：该方式可以复制数据表的数据类型
create table 表名2 like 表名1;
insert into 表名2 select * from 表名1；
方式二：
create table 表名2 (select * from 表名1);
1
2
3
4
5

12、清空数据表

delete from 表名；
//清空表后，返回的结果内有删除的记录条目
//delete是一行一行进行删除记录数据的
//delete清空表后，自增长字段会按之前最大的记录自增
    
truncate table 表名；
//truncate 清空表后，没有返回被删除的条目
//其工作时是将表结构按原样重新建立
//因此其清空速度比delete快
//truncate清空后，自增长字段会重新开始
1
2
3
4
5
6
7
8
9
10

drop、truncate、delete区别

• 当不在需要一张表时，用drop
• 想删除部分数据行时，用delete，并带上where
• 删除数据保留表时用truncate

drop table 表名	
    属于DDL类型，不可进行回滚，无法恢复，不可带where，删除表内容和结构，删除速度最快
    
truncate table 表名	
    属于DDL类型 ，不可进行回滚，不可带where，表内容删除，删除速度快
    
delete from 表名	
    属于DML类型，可回滚，可带where，表结构在，表内容要看where执行情况
1
2
3
4
5
6
7
8

13、创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见，直到临时表被删除)。

内部临时表

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW 　　STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

引用：原文链接：https://blog.csdn.net/weixin_34439836/article/details/113111294

create temporary table (字段1.....);
1

3、mysql用户管理

1、创建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
//‘用户名’：指定将创建的用户名
//‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
//‘密码’：若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
//若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
//若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
1
2
3
4
5
6

方式一：使用明文
    create user 'set'@'localhost' identified by '123456';
方式二：使用密文
    select password('123456');
	create user 'setting'@'localhost' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
1
2
3
4
5

2、查看用户信息

创建后的用户保存在 mysql 数据库的 user 表里

use mysql;   #使用mysql库 
select user from user;
select user from user\G
1
2
3

3、修改用户名

rename user 'A'@'localhost' to 'B'@'localhost';
1

4、删除用户

drop user 'A’@‘localhost’；
1

5、修改用户密码

修改当前用户密码
    set password=password（‘passwd’）；
修改指定用户密码
    set password for 'set'@'localhost'=password('123456');
1
2
3
4

4、数据库用户权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT 语句用于修改用户信息。

权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
    
数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符。
    
用户名@来源地址’：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.accp.com”、“192.168.48.%”等。
    
IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。
1
2
3
4
5
6
7

1、用户授权

查看用户权限

show grants for 'set'@'localhost';
1

对用户进行指定数据库权限

grant select on test.* to 'set'@'localhost'；
1

设置数据库远程登陆

grant select on test.* to 'set'@'%'； //所以客户端可登陆set用户
grant select on test.* to 'set'@'192.168.10.2';//指定客户端登陆
1
2

2、撤销权限

revoke all on test.* from 'set'@'%';
1