MYSQL数据库基础

2023-12-24 22:30:23

目录

一、数据库的基本概念

二、数据库系统的组成

三、数据库系统发展史

1、第一代数据库

2、第二代数据库

3、第三代数据库

四、数据库的分类

1、关系型数据库

2、非关系型数据库

五、MYSQL数据库介绍

六、MYSQL数据库安装

七、MYSQL语句

1、常用的数据类型

2、查看数据库版本号和登录用户

3、?查看数据库结构

①查看当前服务器中已存在的数据库

②查看数据库中包含的表

4、创建及删除数据库和表

①创建新数据库

②创建新的表

③删除指定的数据表

④删除指定的数据库

5、管理表中的数据记录

①向数据表中插入新的数据记录

②查询数据记录

?编辑

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

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

6、修改表名和表结构

①修改表名

②扩展表结构(增加字段)

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

④删除字段

7、数据表高级操作

①克隆表,将数据表的数据记录生成到新的表中

②清空表,删除表内的所有数据

③创建临时表(测试时使用)

八、MySQL的六大约束特性

1、primary key 主键约束

2、unique key?唯一键约束

3、not null?非空约束

4、default 默认值约束

5、auto_increment 自增约束?

6、foreign key 外键约束

①创建主键表

②创建外键表

③向主键表和从键表中插入数据(注意先后顺序)

④从主键表和从键表中删除数据(注意先后顺序)

九、数据库用户管理

1、新建用户

2、重命名用户

3、root用户修改用户登录密码

4、删除用户

5、忘记 root 密码的解决办法

①修改MySQL配置文件,在 [mysqld] 配置项下面添加 skip-grant-tables 配置参数

②重启mysqld服务,使用 mysql 密码直接登录数据库

③ 修改 root 用户密码

④还原MySQL配置,重启mysqld服务,登录数据库验证

十、数据库用户授权

1、授予权限

2、查看权限

3、登录验证

4、撤销权限


一、数据库的基本概念

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

? ?数据库中的表将不同的记录组织在一起,用来存储具体数据。

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

? ?数据库、表、数据的关系:库中有表,表中有数据

二、数据库系统的组成

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

? ?数据库管理系统(DBMS)是管理数据库的系统软件,其包括数据库及用于访问、管理数据库的接口系统,主要功能是维护数据库,并方便、有效地访问数据库中各个部分的数据。

三、数据库系统发展史

1、第一代数据库

  • 自20世纪60年代起,第一代数据库系统问世
  • 是层次模型与网状模型的数据库系统
  • 为统一管理和共享数据提供了有力的支撑

2、第二代数据库

  • 20世纪70年代初,第二代数据库-关系数据库开始出现
  • 20世纪80年代初,IBM公司的关系数据库系统DB2问世,开始逐步取代层次与网状模型的数据库,成为行业主流
  • 到目前为止,关系数据库系统仍占领数据库应用的主要地位

3、第三代数据库

  • 自20世纪80年代开始,适应不同领域的新型数据库系统不断涌现
  • 面向对象的数据库系统,实用性强、适应面广
  • 20世纪90年代后期,形成了多种数据库系统共同支撑应用的局面
  • 一些新的元素被添加进主流数据库系统中,例如,Oracle支持的“关系-对象”数据库模型

四、数据库的分类

数据库分为两大类:关系型数据库(SQL)和非关系型数据库(NoSQL)

1、关系型数据库

? ?关系型数据库的典型代表有 MYSQL(免费、开源、体积小)、Oracle(面向所有主流平台,安全、完善,操作复杂,付费)、Microsoft SQL Server(一般在windows server系统中使用)、PostgreSQL(开源、免费)。

? ?关系型数据库的国产典型代表有阿里云RDB、华为高斯、阿里Oceanbase、腾讯TDBA、人大金仓达梦、greatsol。

? ?关系数据库系统是基于关系模型的数据库系统,关系模型的数据结构使用简单易懂的二维数据表(有行和列),关系模型可用简单的“实体-关系”(E-R)图来表示,E-R图中包含了实体(数据对象)、关系和属性三个要素,所有实体及实体之间联系的集合构成一个关系数据库

? ?关系数据库的存储结构是二维表格,在每个二维表格中,每一行称为一条记录,用来描述一个对象的信息,每一列称为一个字段,用来描述对象的一个属性。关系数据库的使用场景有账户信息和业务数据等。

2、非关系型数据库

? ?非关系数据库也被称作NOSQL (Not Only?SQL),存储数据不以关系模型为依据,不需要固定的表格式。不同类型的非关系数据库使用不同的的存储结构,比如有键值对、文档、索引、时间序列等。

常用的非关系数据库:

  • Redis、Memcached? ?缓存型
  • mongoDB? ?文档型
  • Elasticsearch? ?索引型
  • Prometheus、InfluxDB? ?时序型
  • HBase? ?列式型

非关系型数据库的优点:

  • 数据库可高并发读写
  • 对海量数据高效率存储与访问
  • 数据库具有高扩展性与高可用性

五、MYSQL数据库介绍

MYSQL一款深受欢迎的开源关系型数据库,是Oracle旗下的产品,遵守GPL协议,可以免费使用与修改。

特点:

  • 性能卓越、服务稳定
  • 开源、无版权限制、成本低
  • 多线程、多用户
  • 基于C/S(客户端/服务器)架构
  • 安全可靠

版本:

  • MySQL商业版:由MySQL AB公司负责开发与维护,需要付费才能使用(更稳定)
  • MySQL社区版:由分散在世界各地的MySQL开发者爱好者一起开发与维护,可以免费使用

产品阵营:

  • 第一阵营:5.0-5.1阵营,可说是早期产品的延续
  • 第二阵营: 54-5.7阵营,整合了MySQLAB公司、社区和第三方公司开发的存储引擎,从而提高性能
  • 第三阵营: 6.0-7.1阵营,就是MySQL Cluster版本为适应新时代对数据库的集群需求而开发

目前主流版本为:5.7版本和8.0版本

六、MYSQL数据库安装

如果已安装mariadb需要先卸载

1、mysql-boost-5.7.44.tar.gz放进/opt,并解压缩

2、使用本地yum安装依赖包

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake?openssl openssl-devel?

3、cmake配置

cd /opt/mysql-5.7.44/

cmake \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定mysql的安装路径

-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ ? ? ? ? ? ?#指定mysql进程监听套接字文件(数据库连接文件)的存储路径

-DSYSCONFDIR=/etc \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定配置文件的存储路径

-DSYSTEMD_PID_DIR=/usr/local/mysql \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定进程文件的存储路径

-DDEFAULT_CHARSET=utf8 ?\ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定默认使用的字符集编码,如 utf8

-DDEFAULT_COLLATION=utf8_general_ci \ ? ? ? ? ? ? ? ? ? ? ? #指定默认使用的字符集校对规则

-DWITH_EXTRA_CHARSETS=all \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定支持其他字符集编码

-DWITH_INNOBASE_STORAGE_ENGINE=1 \ ? ? ? ? ? ? ? ? ? ? ? ? ? ?#安装INNOBASE存储引擎

-DMYSQL_DATADIR=/usr/local/mysql/data \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定数据库文件的存储路径

-DWITH_BOOST=/usr/local/boost \ ? ? ? ? ?#指定boost的路径,若使用mysql-boost集成包安装则-DWITH_BOOST=boost

-DWITH_SYSTEMD=1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#生成便于systemctl管理的文件
?

注意:如果在CMAKE的过程中有报错,当报错解决后,需要把源码目录中的CMakeCache.txt文件删除,然后再重新CMAKE,否则错误依旧

4、编译及安装

make -j2 && make install

5、创建mysql用户

useradd -M -s /sbin/nologin ?mysql

6、修改mysql 配置文件(删除原有的内容)

vim /etc/my.cnf

[client] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#客户端连接设置

port = 3306

socket = /usr/local/mysql/mysql.sock

[mysql] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #服务端连接设置

port = 3306?

socket = /usr/local/mysql/mysql.sock

auto-rehash ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#开启自动补全功能

[mysqld] ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #服务全局设置

user = mysql ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#设置运行用户

basedir=/usr/local/mysql ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定数据库的安装目录

datadir=/usr/local/mysql/data ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定数据库文件的存储路径

port = 3306 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定端口号

character-set-server=utf8 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #设置服务器字符集编码格式为utf8

pid-file = /usr/local/mysql/mysqld.pid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定pid 进程文件路径

socket=/usr/local/mysql/mysql.sock ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定数据库连接文件

bind-address = 0.0.0.0 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #设置监听地址,0.0.0.0代表允许所有,如允许多个IP需空格隔开(生产环境建议指定ip地址)

skip-name-resolve ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#禁止域名解析,包括主机名,所以授权的时候要使用 IP 地址(加快与mysql的连接速度)

max_connections=4096 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #设置mysql的最大连接数

default-storage-engine=INNODB ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定默认存储引擎

max_allowed_packet=32M ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #设置在网络传输中一次消息传输量的最大值。系统默认值为 1MB,最大值是 1GB,必须设置 1024 的倍数。

server-id = 1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定服务ID号(mysql集群编号标识)

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
?

7、更改mysql安装目录和配置文件的属主属组

chown -R mysql.mysql??/usr/local/mysql/

chown mysql.mysql ?/etc/my.cnf

8、优化配置文件路径,并把mysql服务的可执行程序文件放入路径环境变量的目录中便于系统识

ln -s /usr/local/mysql/bin/* /usr/local/bin/

9、初始化数据库

cd /usr/local/mysql/bin/

./mysqld \

--initialize-insecure \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#生成初始化密码为空

--user=mysql \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定管理用户

--basedir=/usr/local/mysql \ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #指定数据库的安装目录

--datadir=/usr/local/mysql/data ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?#指定数据库文件的存储路径

10、添加mysqld系统服务,用于systemctl服务管理

cp? /usr/local/mysql/usr/lib/systemd/system/mysqld.service? ?/usr/lib/systemd/system/mysql

systemctl daemon-reload

11、修改mysql 的登录密码(默认直接用mysql就能进,不安全)

mysqladmin -u root password "123"

给mysql管理员root账号设置密码为123,原始密码为空

七、MYSQL语句

1、常用的数据类型

数字型:

int:整数型,无符号的整数类型范围是[0,2^32-1],有符号的整数类型范围是[-2^31,2^31-1]

float:单精度浮点(小数) ,值的范围是4字节32位

double :双精度浮点 ,值的范围是8字节64位,小数点的范围更多,精度更多

decimal(5,2) :是特殊的浮点数,这里表示共5个有效长度数字,整数3位,小数点后面有2位

字符串类型:

char :固定长度的字符类型,字符串固定长度为四个字节(读取速度更快,适合短字符使用

varchar :可变长度的字符类型,字符串长度不固定,且结束符会固定占用一个字节(适合长字符使用,占用磁盘空间会更少)

text:长文本的字符串

图片类型:

image:图片

2、查看数据库版本号和登录用户

select version();? ? ? ? ? ? ? ? ?#版本号

select user();? ? ? ? ? ? ? ? ? ? ?#当前登录用户

3、?查看数据库结构

①查看当前服务器中已存在的数据库

show databases;

②查看数据库中包含的表

use? 数据库名;

show tables;

③查看表的结构

use? 数据库名;

desc?表名;

4、创建及删除数据库和表

①创建新数据库

create database 数据库名;

②创建新的表

use? 数据库名;

create table?表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);

③删除指定的数据表

use? 数据库名;

drop table 表名;

④删除指定的数据库

drop database 数据库名;

5、管理表中的数据记录

①向数据表中插入新的数据记录

insert?into?表名(字段1,字段2[,...]) values (字段1的值,字段2的值,...);

insert into 表名 values (所有字段的值);

②查询数据记录

select?字段名1,字段名2[,...] from 表名 [where 条件表达式];

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

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where?条件表达式];?

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

delete from 表名 [where 条件表达式];

6、修改表名和表结构

①修改表名

alter table 旧表名 rename 新表名;

②扩展表结构(增加字段)

alter table 表名 add 新字段 数据类型 [约束属性];

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

alter table 表名 change 旧列名 新列名 数据类型 [unique key];

#change 可修改字段名、数据类型、约束等所有项。

④删除字段

alter table 表名 drop 字段名;

7、数据表高级操作

①克隆表,将数据表的数据记录生成到新的表中

方法一:

create table 新表 like 旧表;? ? ? ? ? ? ? ? ? ? ?#克隆表结构
insert into 新表 (select * from 旧表); ? ? ? #克隆表数据 ? ? ? ?可以实现表结构和表数据与旧表都一致

方法二:

create table 新表 (select * from 旧表);? ? ? ? ?#表数据和旧表一致,表结构和旧表不一定一致

②清空表,删除表内的所有数据

方法一:

delete from 表名; ? ?

#一条一条的删除记录,效率较慢;自增长字段仍然会按照清空表前的最大记录继续自增

方法二:

truncate table 表名; ? ?

#直接重建表,清空表效率更快;自增长字段会重新从 1 开始自增?

③创建临时表(测试时使用)

create temporary table 表名 ?(字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

#临时表跟普通表一样可以进行增删改查,但是show tables是查不到的;临时表只能在当前会话中有效,退出当前会话或在其它会话中,临时表都会失效

八、MySQL的六大约束特性

primary key? ? ? ? 主键约束? ? ? 字段的值不能重复,不能为Null,一个表只能有一个主键
unique key? ? ? ? ?唯一性约束 ? 字段的值不能重复,但能为Null,一个表可以有多个唯一键
not null? ? ? ? ? ? ? ?非空约束? ? ? 字段的值不能为Null
default? ? ? ? ? ? ? ? 默认值约束 ? 字段的值如果没有设置则使用默认值自动填充
auto_increment ?自增约束? ? ? ?字段的如果没有设置默认会从 1 开始每次自动递增 1,要求自增字段必须为主键
foreign key? ? ? ? ? 外键约束? ? ? 保证外键表与主键表相关联的表数据的完整性和一致性

1、primary key 主键约束

2、unique key?唯一键约束

3、not null?非空约束

4、default 默认值约束

5、auto_increment 自增约束?

6、foreign key 外键约束

作用:保证数据的完整性和一致性

外键的定义:如果同一个属性字段X在表一中是主键,而在表二中不是主键,则字段X称为表二的外键。

主键表和外键表的理解:

(1)以公共关键字作主键的表为主键表(父表、主表)

(2)以公共关键字作外键的表为外键表(从表、外表)

注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表,主从表的字段具备相同的数据类型、字符长度和约束。

①创建主键表

alter table 表名 add primary key (主键字段);

②创建外键表

alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段);

③向主键表和从键表中插入数据(注意先后顺序)

注意:插入新数据时,需要先在主键表插入数据再在外键表插入相关联的数据;删除数据时,需要先在外键表删除数据再在主键表删除相关联的数据

④从主键表和从键表中删除数据(注意先后顺序)

?alter table 表名 drop foreign key '外键别名';
?alter table 表名 drop key '键名';

九、数据库用户管理

1、新建用户

create user '用户名'@'源地址' ?identified by '密码';

注:源地址可以是localhost/IP/网段/主机名/%

2、重命名用户

rename user '用户名'@'源地址' to '新用户名'@'源地址';

3、root用户修改用户登录密码

方法一:

set password [for '用户名'@'源地址'] = password('密码');

方法二:

alter user '用户名'@'源地址' identified by '密码';

4、删除用户

drop user '用户名'@'源地址';

5、忘记 root 密码的解决办法

①修改MySQL配置文件,在 [mysqld] 配置项下面添加 skip-grant-tables 配置参数

vim /etc/my.cnf

②重启mysqld服务,使用 mysql 密码直接登录数据库

③ 修改 root 用户密码

update mysql.user set authentication_string=password('密码') where user='root';

④还原MySQL配置,重启mysqld服务,登录数据库验证

vim /etc/my.cnf

systemctl restart mysql

mysql -u用户名 -p密码 [-h mysql地址 -P mysql端口]

十、数据库用户授权

1、授予权限

GRANT语句:专门用来设置数据库用户的访问权限。在5.7或之前版本支持创建新用户和授予权限,8.0版本只能用于授予权限。

grant 权限1,权限2,.... ?on 库名.表名 to '用户名'@'源地址' [identified by '密码'];

2、查看权限

show grants for '用户名'@'源地址';

3、登录验证

4、撤销权限

revoke? 权限1,权限2,....|all? on 库名.表名 from ?'用户名'@'源地址';

文章来源:https://blog.csdn.net/lcy913/article/details/135157544
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。