(一)我与MySQL数据库的第一面~~——增、删、改、查

本文链接：https://blog.csdn.net/weixin_56667320/article/details/118484702

前言

数据:图片、视频、关系、音频、字符、字符串等等
数据的作用:持久化保存(把数据保存在一个位置或者磁盘中)、高可靠，高可用，数据的快速提取

一、数据库基本概念

数据：存储在以“记录”为行，以“字段”为列的数据表中
表的功能：用来存储具体的数据
数据库：表的集合，是存储数据的仓库，以一定的组织方式存储的相互有关的数据集合

小结：数据库可以完成数据持久化保存+快速提取
数据如何保存:最外层是mysql服务——>mysql数据库——>数据表——>记录为行，字段为列——>数据保存在一行行记录中
那么想要实现以上功能，需要编写一系列的规则–》SQL语句；而SQL语句按功能分类:增删改查；下文会具体介绍如何实现此功能。

二、数据库的发展史

第一代数数据库
- 是层次模型(只有一个父分支)与网状模型(只有一个父分支)的数据库系统，为统一管理和共享数据提供了有力的支撑
第二代数据库
- IBM公司的关系数据库系统DB2
- 关系模型可用简单的E-R(实体-关系)图来表示，图中包含了实体(数据对象)、关系和属性三个要素
  - 实体：举例子：张三就是实体
  - 属性：描述一个实体的详细信息
  - 关系：实体机之间的对应关系称为联系，也称为关系
第三代数据库
- 非关系型数据库，面向对象的数据库系统，实用性强、适应面广

三、数据库的类型

1、关系型数据库-SQL

存储方式：二维数据表：数据存储在记录为行，以字段为列的数据表中
- 每一行称为一条记录，用来描述一个对象的信息
- 每一列称为一个字段，用来描述对象的一个属性
- 主键 primary key 的特性：唯一性和非空
存储内容：数字、文字、图形、图像、声音、档案记录等；包含：实体、关系、属性
常用的关系数据库：Mysql (Oracle公司；5.6升级到5.7的；mariadb，mysql的轻量版)、SQL server ( 微软)、access (微软公司office产品)/Oracle (税务局)、DB2 ( IBM公司，国电)，sybase ( sybase)等等

2、非关系型数据库-NoSQL

概念：存储海量数据，给与”大数据“进行分析，筛选出有价值的部分
存储方式：不以关系模型为依据，不需要固定的表格式
存储内容：存储图片、视频、音频等一些非关系型
优点
高并发读写
对海量数据高效率存储与访问；比如高德
具有高扩展性与高可用性；分布式特性
常用非关系型数据库(NoSQL) :MongoDB 、Redis (内存数据库/缓存数据库) K-V键值对、与之类似的Memcache, K-v键值对；
- redi s-memecache对比:
  - 相同点:存储高热数据(在内存中高速运行)
  - 不同点: redis可以做持久化保存，可以存储对象

tomcat --------redis(内存、缓存)—数据库mysql(数据库数据保存在磁盘里面)
tomcat跟数据库进行快速交互时，为了提高效率，可以在中间加载redis，一方面提高加载速率，另一方面降低mysql的压力

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

3、时序数据库-TSD

从定义上来说，就是一串按时间维度索引的数据。用描述性的语言来解释什么是时序数据，简单的说，就是这类数据描述了某个被测量的主体在一个时间范围内的每个时间点上的测量值。它普遍存在于IT基础设施、运维监控系统和物联网中；
简单的说就是类似心电图；持续性的，根据时间点进行变化的一组连续性的数据

四、数据库日志

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

比如：

阿里云对象存储：存储大都是一些静态文件，图片、音频、视频，不能直接在对象存储中修改数据
飞天平台：使用盘古系统，钟馗：安全；女娲：选举/调度；盘古一式三份
MySQL：存储引擎；分类
- myisam和innodb 两种引擎
- myisam：快速读取，不支持事务
- innodb：更注重写，支持事务

五、MySQL的安装及基本命令

1、安装MySQL

------------------------------------------>安装Mysql环境依赖包<------------------------------------------------------------------
yum -y install ncurses ncurses-devel bison cmake
		ncurses\			#字符终端工具包
		bison \		#函数库
-------------------------------------------------->创建运行用户<------------------------------------------------------------------
useradd -s /sbin/nologin mysql
---------------------------------------------->编译安装<----------------------------------------------------------------------------
上传mysql -boost-5.7.20. tar .gz到opt目录下
cd /opt
tar xzvf mysql-boost-5. 7.20. tar.gz 
cd /opt/mysq1-5.7.20/
cmake \
	-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \			##指定安装路径(目录)
	-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \			##指定通信文件，连接数据库，通讯协议的载体
	-DSYSCONFDIR=/etc \		##配置文件目录指向/etc
	-DSYSTEMD_PID_DIR=/usr/local/mysql \		##指定pid目录文件
	-DDEFAULT_CHARSET=utf8  \		##字符集设定
	-DDEFAULT_COLLATION=utf8_general_ci \		##字符集设定
	-DWITH_INNOBASE_STORAGE_ENGINE=1 \ 		##以下三行均为存储引擎ENGINE
	-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
	-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
	-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
	-DMYSQL_DATADIR=/usr/local/mysql/data \		##指定数据存放位置
	-DWITH_BOOST=boost \		##底层C++运营库，5.7的版本独立的表空间结构
	-DWITH_SYSTEMD=1		##守护进程id跑在后台的一个支撑服务正常运行的一个特殊进程
make && make install
----------------------------------------------------->数据库目录进行权限调整<------------------------------------------------------------------
chown -R mysql:mysql /usr/local/mysql/
---------------------------------------------------->调整配置文件<------------------------------------------------------------------
[root@localhost etc]# vim my.cnf
#删除原有内容，添加以下内容
	[client]		#client用户程序
	port = 3306
	default-character-set=utf8
	socket = /usr/local/mysql/mysql.sock 		##连接数据库的数据端
	[mysql]		#mysql软件内容
	port = 3306		#端口
	default-character-set=utf8
	socket = /usr/local/mysql/mysql.sock
	[mysqld]			#以后对msq1配置时更多的会针对于mysqld进行配置
	user = mysql		#程序用户
	basedir = /usr/local/mysql		 ## 工作目录
	datadir = /usr/local/mysql/data			##数据文件目录
	port = 3306  		##端口
	character_set_server=utf8   		##服务的字符集
	pid-file = /usr/local/mysql/mysqld.pid 		##PID文件目录
	socket = /usr/local/mysql/mysql.sock			##通讯文件
	server-id = 1			##服务id，在之后的mysql集群中用于标识mysq1服务器
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES		 ##模块，默认加载
------------------------------------------------>更改属主属组<-------------------------------------------------------------------------------------
chown mysql:mysql /etc/my.cnf
----------------------------------------------->设置环境变量<------------------------------------------------------------------------------------
echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile
-------------------------------------->初始化数据库<----------------------------------------------------------------------------------------------
bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
cp mysqld.service /usr/lib/systemd/system   ##mysqld.service官方写好的启动脚本，复制到/usr/local/systemd/sysytem
systemctl enable mysqld
--------------------------------------------->数据库开启自启、关闭、状态：enable、start、stop、status<-----------------------------------------------
systemctl start mysqld
netstat -antp | grep 3306
----------------------------------------->设置mysql密码<------------------------------------------------------------------------------------------------
mysqladmin -u root -p password
----------------------------------->登录MySQL数据库<-------------------------------------------------------------------------------------------
mysql -u root -p  ##输入密码abc123

2、基本命令

use mysql；		##进入数据库
show tables;		##查看表

在这里插入图片描述
查询数据库的结构

describe [数据库名.]表名	##查询非当前数据库内的表的结构
describe 表名	##查询当前数据库内的表的结构
describe可以简写desc

在这里插入图片描述
字段详解：

Field:字段名称;比如姓名、年龄、身高、体重
Type:数据类型/属性
Null :是否允许为空
Key :主键
Default :默认值;默认值可以设为空
Extra :扩展属性，例如:标志符列(标识了种子(起始位置)，增量/步长)
- 比如我的种子是1，增量/步长是2，那就是1 3 5 7 …

3、常用数据类型

int: 整型 ##用于定义整数类型的数据
float:单精度浮点4字节32位 ##准确表示到小数点后六位
double:双精度浮点8字节64位 ##float的双倍
char:固定长度的字符类型 ##用于定义字符类型数据；比如：手机号码char(11)
varchar: 可变长度的字符类型 ##设置上限；比如：varchar(11)字符上限11个
text: 文本
image:图片
decimal(5,2): 5个有效长度数字，小数点后面有2位 ##指定长度数组；比如：若为12345.899和1 2345.891会显示12345.90和12345.89;四舍五入
- Char如果存入数据的实际长度比指定长度要小，会补空格至指定长度，如果存入的数据的实际长度大于指定长度，低版本会被截取，高版本会报错
- 截取:四舍五入
- 截断:不四舍五入

六、数据库增、删、改、查-SQL语句

Structured Query Language的缩写，即结构化查询语言；关系型数据库的标准语言，用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能(增、删、改、查)，分为以下四类：

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

1、DDL:数据定义语言

DDL语句可用于创建数据库对象(库、表、索引等)
删除数据库和表

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

实例

create database zyt;		##创建新的数据库
use zyt;   进入数据库库zyt
create table zw (id int not null,name char(10) not null,score decimal(5,2),passwd char(48) default'',primary key(id));		 ##创建新的表
desc zw;  查询数据表zw结构

NOT NULL ##不允许为空值
DEFAULT’ ’ ##默认值为空
PRIMARY KEY ##主键一般选择没有重复并且不为空值的字段
在这里插入图片描述

--------------------->删除指定的数据表<------------------------------------------
drop table 表名； 
drop table [数据库名.]表名;		##如不用USE进入库中，则需加.上数据库名
--------------------->删除指定的数据库<------------------------------------------
DROP DATABASE 数据库名;

实例

use zyt;
drop table zw；
//
drop table zyt.zw;		##如不用USE进入库中，则需加.上数据库名
drop database zyt		##删除数据库

在这里插入图片描述

2、DML:数据操纵语言

管理表中的数据记录

2.1、insert:插入新数据

插入新数据；字符类型在数据库内需要使用单引号’ ’

格式一:
INSERT INTO 表名(字段1,字段2[,...]) VALUES (字段1的值，字段2的值,...);
格式二：
INSERT INTO zw values(2,'lili',100,123);  ##可以不写前面的字段
password('123'):查询数据记录是，密码字串以加密形式显示；若不使用password(),查询时以明文显示
SELECT * FROM zw ;	#查询表的数据记录

实例：
insert into zw (id,name,score,passwd) values (1,‘wanger’,80,password(‘123’));
在这里插入图片描述

2.2、update:更新原有数据

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

格式
UPDATE 表名 set（修改）字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式]；

实例：
update zw set name=‘zhangsan’ where id=1; ##修改zw表中在id字段内1的name为shangsan
在这里插入图片描述

2.3、delete:删除不需要的数据(表内容)

DELETE FROM 表名 [WHERE 条件表达式]	##在数据表中删除指定内容

实例

delete from zw where id=2;;  		##删除zw表中在id字段内2的内容
delete from zw where id>2;;  		##删除zw表中2行以上的内容；逐行删除

3、DQL:数据查询语言-select

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

实例

SELECT * FROM zw;		##查询zw表
SELECT id, name from zw ;		##查询zw表内的id和name字段
SELECT id, name, score FROM zw WHERE id=2;	##查询zw表内id、name、score字段，id为2的内容
select name from ky11\G	##以列表方式竖向显示

在这里插入图片描述

select id,name from zw where score>80; 		##查询zw数据表中分数大于80的字段

在这里插入图片描述

select * from info limit 2;		##只显示头3行，包含表头

在这里插入图片描述

select * from info limit 2, 3;	##显示第3行后的前3行
select * from zw limit 2,3;	  ##输出基于第2行的下面3行

在这里插入图片描述

4、DCL:数据控制语言-alter

alter 修改表名和表结构 (修改的是表结构并非表的内容)

--------------------------->修改表名<-----------------------
ALTER TABLE  旧表名 RENAME 新表名
--------------------------->扩展表结构(增加字段)<-----------------------
ALTER TABLE 表名 ADD address varchar(50) default '地址不详' ;  ##增加字段address
	default ' 地址不详':表示此字段设置默认值为地址不详，可与NOT NULL配合使用
	add:增加
--------------------------->添加唯一键<-----------------------
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型；	##修改字段(列)名，添加唯一键
	unique key：唯一键（特性：唯一，但可以为空，控制值允许出现一次）
	primary key：唯一性且非空
	change：可修改字段名、数据类型、约束等所有项

实例

alter table zw rename zw_new;		##更改表名zw为zw_new

在这里插入图片描述

alter table zw_new add address varchar(50) default'地址不详'; 	##添加数据表字段address为默认地址不详

在这里插入图片描述

alter table zw_new change name user_name varchar(20) unique key;

在这里插入图片描述

删除字段

ALTER TABLE 表名 DROP 字段名;

实例

alter table zw drop passwd;		##删除zw数据表的asswd字段

在这里插入图片描述

扩展

use zyt;  进入数据库zyt
create table if not exists info (id int(4) zerofill primary key auto_increment,name varchar(10) not null, cardid int(18) not null uniquee key,hobby varchar (50) );
	id int (4) zerofill primary key auto increment    #指定主键的第二种方式
	cardid：身份证号；数据建议写大一些,因为系统会转义字符就不止18位会报错
	hobby：爱好表格不存在时创建

参数说明：

if not exists: 表示检测要创建的表是否已存在，如果不存在就继续创建
int(4) zerofill: 表示若数值不满4位数，则前面用"0"填充，例0001
auto increment: 表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;如添加的记录数据没有指定此字段的值且
添加失败也会自动递增一次
unique key:表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一-张表中可以有多个唯一键
not null:表示此字段不允许为NULL

实例

insert into info values(1,'lala',320381,'学习'),(2,'lilei',5678,' '天天向上');

在这里插入图片描述
这里需要注意一点的是，前期给出的cardid的数值范围是18，但是实际系统会转义不止18位

七、删除类型小结

drop table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容和结构删除
5)删除速度快
truncate table table_name
1)属于DDL
2)不可回滚
3)不可带where
4)表内容删除
5)删除速度快
delete from table_name
1)属于DML
2)可回滚(可恢复)
3)可带where
4)表结构在，表内容要看where执行的情况
5)删除速度慢,需要逐行删除
总结：
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
速度
drop> truncate > delete
安全性
delete 最好