MySQL数据库

目录

一、数据库基本概念

二、关系型数据库

四、Mysql 数据库

五、MySQL数据库管理

---

一、数据库基本概念

数据
使用一些介质进行存储，例如文字存在文档中
数据库可以完成数据持久化保存+快速提取
那么想要实现以上功能，需要编写一系列的规则--》SQL语句
SQL语句 按功能分类: 增删改查

数据库类型：关系型数据库、非关系型数据库
① 关系型数据库: 存储的往往是字符、字符串、数值、布尔值等
② 非关系型数据库：存储的往往是图片、视频、语音等

关系型数据库：
Mysql（Oracle公司） 、 SQL server（微软) 、access(微软公司office产品）
Oracle 、DB2（IBM公司），sybase（sybase） 等等

mysql 开源数据库

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

数据

描述事物的符号记录，包括数字，文字、图形、图像、声音、档案记录等，以“记录”形式按统一的格式进行存储。

表
记录：行
字段（属性）: 列
以行+列的形式就组成了表（数据存储在表中）

数据库

表的集合，是存储数据的仓库，以一定的组织方式存储的相互有关的数据集合，是按照数据结构来组织、存储和管理数据的仓库。

数据库管理系统(DBMS)

是实现对数据库资源有效组织、管理和存取的系统软件。数据库的建立和维护功能、数据定义功能、数据操纵功能、数据库的运行管理功能、通信功能。

数据库系统

是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成，用户可以通过DBMS或应用程序操作数据库。

DBMS主要包括以下功能

1）数据库的建立和维护功能：包括建立数据库的结构和数据的录入与转换、数据库的 转储与恢复、数据库的重组与性能监视等功能。

2）数据定义功能：包括定义全局数据结构、局部逻辑数据结构、存储结构、保密模式及信息格式等功能。保证存储在数据库中的数据正确、有效和相容，以防止不合语义的错误数据被输入或输出。

3）数据操纵功能：包括数据查询统计和数据更新两个方面。

4）数据库的运行管理功能：这是数据库管理系统的核心部分，包括并发控制、存取控 制、数据库内部维护等功能。

5）通信功能：DBMS 与其他软件系统之间的通信,如 Access 能与其他 Office 组件进行数据交换。
 

DBMS的工作模式

 

DBMS的工作模式如下：

1.接受应用程序的数据请求和处理请求。

2.将用户的数据请求（高级指令）转换为复杂机器代码（底层指令）。

3.实现对数据库的操作。

4.从数据库的操作中接受查询结果。

5.对查询结果进行处理（格式转换）。

6.将处理结果返回给用户。

二、关系型数据库

实体-关系 E-R 
记录(行) 表示了一个实体（的属性）
而E-R图：
实体与实体之间的联系（关系）
即 表与表的关系

关系数据库4-3
⭐⭐ 客户编码：主键（类比入学学号）
主键特性：唯一、非空

延迟加密--用于与数据库通讯时

三、非关系型数据库

关系型数据库: 适用于对关系很明确的数据建立模型、定义、存储数据
非关系型数据库: 存储海量数据，给与”大数据“进行分析，筛选出有价值的
部分

引申：redis 持久化方式
以键值存储数据，数据保存在内存中，但会定期将数据写入磁盘（持久化方式）

高德：实时分析平台，
使用非关系型数据库存储，如果需要持久化，就可以使用redis

四、Mysql 数据库

日志
数据库日志文件用于备份，恢复，是最 核心的部分
mysql 与 oracle 日志有所区别
mysql 写一条数据，同步到日志中一条
oracle ：重做日志组
一个组中至少3个日志成员，轮流存储日志
还会有另一个组与之同步/备份

阿里云OSS对象存储，一式三份备份(使用重做日志组思想)

Mysql 存储引擎
myisam 和innodb
myisam：快速读取，不支持事务
innodb：更注重写，支持事务

五、MySQL数据库管理

describe mytable; （desc mytable）
查看数据库中 mytable表结构；
解释：
Field:字段名称
Type:数据类型
Null :是否允许为空
Key :主键
Type:数据类型
Null :是否允许为空key :主键
Default :默认值
Extra :扩展属性，例如:标志符列（标识了种子，增量/步长）1 2
id：1 3 5 7

常用的数据类型

int：整型						用于定义整数类型的数据
float：单精度浮点4字节32位		准确表示到小数点后六位		
double：双精度浮点8字节64位
char：固定长度的字符类型		用于定义字符类型数据。
varchar：可变长度的字符类型		
text：文本
image：图片
decimal(5,2)：5个有效长度数字，小数点后面有2位		指定长度数组
#Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错。
主键是唯一的，但主键可以由多个字段构成

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

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

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

MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件
（frm,myd,myi）。 每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。

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

SQL语句

SQL语言分类：
DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

1、DDL

create
drop

创建新的数据库

CREATE DATABASE 数据库名;

查看数据库

show databases;

使用数据库

use 数据库名;

创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型, ...,PRIMARY KEY (主键名));
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

举例： create table class (id int NOT NULL,name char(10) NOT NULL,score decimal(5,2),passwd char(48)default'',primary key(id));

解释

#NOT NULL   不允许为空值
#DEFAULT''  默认值为空
PRIMARY KEY :主键一般选择没有重复并且不为空值的字段

 

删除指定的数据表

方法一

use 数据库名
DROP TABLE 表名

方法二：不用USE进入库中，则需加上数据库名

DROP TABLE 数据库名. 表名;

 

删除指定的数据库

DROP DATABASE 数据库名;

 

2、DML管理表中的数据记录

insert
update
delete

向表中插入数据

INSERT INTO 表名(字段1,字段2,...) VALUES (字段1的值，字段2的值,...);

举例：insert into class (id,name,score,passwd) values (1,'my',99.9,PASSWORD('123456'));
#PASSWORD('123456'):查询数据记录时，密码字串以MD5加密形式显示:若不使用PASSWORD()，查询时以明文显示。
insert into class values (1,'my',99,131415);
#如果知道表参数，可以不用加 (id,name,score,passwd)，直接在values 括号内写值即可。

查询表数据

 seclect * from 表名;

修改、更新数据表中的数据记录

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

举例：update class set passwd=PASSWORD('111111') where name='my2';
update class set name='zhangsan',passwd='111222' where id=2;

修改本机myql的root用户登录密码

update mysql.user set authentication_string=password('123') where user='root';
注意需要进行刷新：flush privileges;

在数据表中删除指定的数据记录

DELETE FROM 表名 WHERE 条件表达式;

举例：delete from class where name='my';

3、DQL查询数据记录

select

SELECT 字段名1,字段名2,... FROM 表名 WHERE 条件表达式;

举例：select * from class;# *表示查看表所有内容
select id,name from class where id=2;#在class表中只显示id=2的 id 和 name。 
select name from class\G;  		 #以列表方式竖向显示class表中的name字段
select * from class info limit 2;   	 #只显示class表头2行
select * from class info limit 2,3;     #显示class表第2行后的前3行

 

DCL修改表名和表结构

ALTER TABLE 旧表名 RENAME 新表名;

举例：alter table class rename class30;
#将表名class改为class30

 

扩展表结构(增加字段)

ALTER TABLE 表名 ADD address varchar(50) default '地址不详';
#default '地址不详'：表示此字段设置默认值为地址不详，可与NOT NULL配合使用

举例： alter table class30 add adderss varchar(50) not null default '地址不详';

 修改字段(列)名，添加唯一键

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 unique key;
#CHANGE可修改字段名、数据类型、约束等所有项

举例：alter table class30 change name user_name varchar(10) unique key;
#修改class30表中的name列，将其改名为user_name，并将数据类型更改为varchar(10)，同时添加唯一性约束。

 删除字段

ALTER TABLE 表名 DROP 字段名;

举例：alter table class30 drop adderss;
#从名为class30的表中删除名为adderss的列。执行此语句后，class30表将不再包含adderss列及其数据。

 

高级操作

方式1.清空表：（工作环境中，一般使用delect方式删除）

 delete from ky where level >= 40;
 删除ky表中 level大于等于40的数据
 delect 清空表后，返回的结果内会有删除的记录条目。（在日志里面可以看到！）
 #DELETE 工作时是一行一行的删除记录数据的;如果表中有自增长字段，使用DELETE FROM 删除
所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。

 方式二：

truncate table ky;
#TRUNCATE清空表后，没有返回被删除的条目: TRUNCATE 工作时是将表结构按原样重新建立，
因此在速度上TRUNCATE会比DELETE清空表快;使用TRUNCATE TABLE 清空表内数据后，
ID会从1开始重新记录

删除速度：
drop> truncate > delete
安全性：delete 最好 （delete可回滚）

2．临时表

MySQL 的临时表，顾名思义，就是临时建立的表，并不会长期存在，主要用于保存一 些临时数据。临时表有个特性，
就是只在当前连接可见，当前连接下可执行增删改查等操作， 当连接被关闭后，临时表就会被 MySQL 删除，相关的资源也会被释放。
下面创建临时表 mytmp，然后插入数据，之后断开当前连接，最后重新连到 MySQL 查看临时是否还存在，具体操作如下
 

mysql>CREATE TEMPORARY TABLE `mytmp` (`id` int(10) NOT NULL AUTO_INCREMENT,`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,`level` int(10) NOT NULL,PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into mytmp(name,level) values('aa',10);
插入数据

select * from mytmp;
查看mytmp表中的数据
exit 退出数据库

mysql -uroot -p
show databases;
use auth;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from mytmp;
ERROR 1146 (42S02): Table 'auth.mytmp' doesn't exist

 3.克隆表：

一.like方式

create table 新表名 like 旧表名;克隆表结构 （通过like方式 复制旧的表名的表结构生成新表名的结构）
insert into 新表名 select * from 旧表名; 复制旧表的内容到新表。

举例：
create table test like ky;克隆表结构 （将ky的表结构复制到test表）
insert into test select * from ky;  克隆表内容（将ky表内容复制到test表中）

 二.直接复制

DCL语句设置用户权限：
1.新建用户

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

2.数据库用户授权
grant提取

grant 权限列表 on 数据库.表名 to '用户名'@'来源地址' identified by '密码';
                       .*代表所有表

举例：允许用户xiaoma在本地查询school数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

grant select on school.* to 'xiaoma'@'localhost' identified by '123';
flush privileges;    #刷新权限

允许用户xiaoma远程授权school数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

GRANT select ON school.* TO 'xiaoma'@'%' IDENTIFIED BY 'abc123';
以上操作不安全！！

允许用户xiaoma远程授权school数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

GRANT select ON school.* TO 'xiaoma'@'%' IDENTIFIED BY 'abc123';
以上操作不安全！！

查看授权信息

show grants for '用户名' @'来源地址'；
show grants for '用户' @'%'； 查看所有来源

解除授权信息

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@'来源地址';
举例：revoke all on bdqn.* from 'dbuser'@'192.168.100.1';

3.允许用户lisi在所有终端远程连接mysql，并拥有所有权限

GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
#PRIVILEGES 所有

4.查询创建的用户信息并且增删改查
创建的用户 在mysql数据库下的user表中
查询：

select user

删除用户

drop 用户名

修改当前用户密码

set password = pssword 密码

修改其他用户密码

set password for '用户名'@'localhost'=password '密码';

忘记用户密码：
进行修改my.cnf 配置文件 跳过密码认证

skip-grant-tables