MySQL数据库
MySQL数据库
基本概念#
数据库体系 分两类：关系型数据库SQL 非关系型数据库NOSQL
关系型数据库：典型代表 MySQL MariaDB , PostgreSQL（pgdb) , Oracle SQL server , DB2 信创 国产数据库：阿里云 RDB ，华为 高斯 ，阿里 Oceanbase ，腾讯 TDBA , 人大金仓 ， 达梦 ，greatSQL
数据（data）
描述事物的符号记录
包括数字，文字，图形，图像，声音，档案记录等
以“记录”形式按统一的格式进行存储和
表
将不同的记录组织在一起
用来存储具体数据
数据库
表的集合，是存储数据的仓库
以一定的组织方式存储的相互有关的数据集合
是按数据结构来组织，存储和管理数据的仓库
数据库管理系统(DBMS)
是管理数据可的系统软件，其包括数据库及用于访问，管理数据库的接口系统
主要功能是维护数据，并方便，有效地访问数据库中各个部分的数据
数据库系统（DBS）
是一个人机系统，由硬件、os、数据库、DBMS、应用软件和数据库
用户可以通过DBMS或应用程序操作数据库
当今主流数据库
SQL SERVER (微软公司产品)
面向windows 操作系统
简单，易用
ORACLE （甲骨文公司产品）
面向所有主流平台
安全、完善、操作负载
DB2 (IBM公司产品)
面向所有主流平台
大型、安全、完善
MYSQL (甲骨文公司收购)
免费、开源、体积小
#
关系数据库系统是基于关系模型的数据库系统 关系模型的数据结构使用简单易懂的二维数据表 关系模型可用简单的“实体-关系”(E-R)图来表示 E-R图中包含了实体(数据对象)、关系和属性三个要素
实体
也称为实例，对应现实世界中可区别于其他对象的“事件”或事物 如银行客户、银行账户等
属性
实体所具有的某一特性，一个实体可以有多个属性
如“银行客户” 实体集中的每个实体均具有姓名、住址、电话等属性
联系
实体集之间的对应关系称为联系，也称为关系
如银行客户和银行账户之间存在“储蓄”的关系
所有实体及实体之间联系的集合构成一个关系数据库
关系型数据库的存储结构是二维表格
在每个二维表中
每一行称为一条记录，用来描述一个对象的信息
每一列称为一个字段，用来描述对象的一个属性
关系型数据库 SQL 操作命令：sql语句 ， 存储结构：二维表格 ，存储的数据：结构化数据 使用场景：存储业务数据和账户信息
对象：库 ---> 表 ---->二维表格形式的结构化数据 列（字段）：用来描述对象的一个属性 行（记录）：用来描述一个对象的信息
#
非关系数据库 NoSQL(NOT ONLY SQL) 存储数据不以关系模型为依据，不需要固定的表格式 **非关系型数据库的优点** 数据库可高并发读写 对海量数据库高效率存储与访问 数据库具有高扩展与高可用 常用的非关系数据库：REDIS \MONGODB 等
非关系型数据库 NoSQL : 存储结构：不同类型的数据库使用不同的存储结构，如：键值对、文档、索引、时间序列等
缓存行：Redis Memcached
文档型：MongoDB
索隐型：ELASTICSEARCH
时序型：PROMETHEUS InfluxDB
列式型：hbase
目前主要7.0 8.0版本
MYSQL 安装#
https://dev.mysql.com/ #官网安装mysql
yum -y install \ gcc \ gcc-c++ \ ncurses \ #字符终端下图形互动功能的动态库 ncurses-devel \ #ncurses开发包 bison \ #语法分析器 cmake yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake openssl-devel cd /opt mv boost_1_59_0 /usr/local/boost #重命名 cd /opt/mysql-5.7.17/ 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存储引擎 -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ #安装ARCHIVE存储引擎 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #安装BLACKHOLE存储引擎 -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ #安装FEDERATED存储引擎 -DMYSQL_DATADIR=/usr/local/mysql/data \ #指定数据库文件的存储路径 -DWITH_BOOST=/usr/local/boost \ #指定boost的路径，若使用mysql-boost集成包安装则-DWITH_BOOST=boost -DWITH_SYSTEMD=1 #生成便于systemctl管理的文件 cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ -DSYSCONFDIR=/etc \ -DSYSTEMD_PID_DIR=/usr/local/mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DWITH_BOOST=boost \ -DWITH_SYSTEMD=1 make -j4 && make install #编译及安装 useradd -M -s /sbin/nologin mysql #创建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需空格隔开 skip-name-resolve #禁止域名解析，包括主机名，所以授权的时候要使用 IP 地址 max_connections=4096 #设置mysql的最大连接数 default-storage-engine=INNODB #指定默认存储引擎 max_allowed_packet=32M #设置在网络传输中一次消息传输量的最大值。系统默认值为 1MB，最大值是 1GB，必须设置 1024 的倍数。 server-id = 1 #指定服务ID号 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 ---------------------------------------------------------------------------------------------------------- sql_mode常用值如下: NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常 STRICT_TRANS_TABLES 在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制 NO_AUTO_CREATE_USER 禁止GRANT创建密码为空的用户 NO_AUTO_VALUE_ON_ZERO mysql中的自增长列可以从0开始。默认情况下自增长列是从1开始的，如果你插入值为0的数据会报错 NO_ZERO_IN_DATE 不允许日期和月份为零 NO_ZERO_DATE mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告 ERROR_FOR_DIVISION_BY_ZERO 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。默认情况下数据被零除时MySQL返回NULL PIPES_AS_CONCAT 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似 ANSI_QUOTES 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符 [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 pid-file = /usr/local/mysql/mysqld.pid socket=/usr/local/mysql/mysql.sock bind-address = 0.0.0.0 skip-name-resolve max_connections=4096 default-storage-engine=INNODB max_allowed_packet=32M server-id = 1 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 .更改mysql安装目录和配置文件的属主属组 chown -R mysql:mysql /usr/local/mysql/ chown mysql:mysql /etc/my.cnf 设置路径环境变量 echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile source /etc/profile 初始化数据库 cd /usr/local/mysql/bin/ ./mysqld \ --initialize-insecure \ #生成初始化密码为空 --user=mysql \ #指定管理用户 --basedir=/usr/local/mysql \ #指定数据库的安装目录 --datadir=/usr/local/mysql/data #指定数据库文件的存储路径 ---------------------------------------------------------------------------------------------------------- ./mysqld \ --initialize-insecure \ --user=mysql \ --basedir=/usr/local/mysql \ --datadir=/usr/local/mysql/data 添加mysqld系统服务 cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ #用于systemctl服务管理 systemctl daemon-reload #刷新识别 systemctl start mysqld.service #开启服务 systemctl enable mysqld #开机自启动 netstat -anpt | grep 3306 #查看端口 修改mysql 的登录密码 mysqladmin -u root password "123" #给root账号设置密码为123，原始密码为空 授权远程登录 mysql -u root -p grant all privileges on *.* to 'root'@'%' identified by '123'; #授予root用户可以在所有终端远程登录，使用的密码是123，并对所有数据库和所有表有操作权限 show databases; #查看当前已有的数据库
常用数据类型#
int：整型 无符号[0,2^32-1]，有符号[-2^31,2^31-1] float ：单精度浮点 4字节32位 double ：双精度浮点 8字节64位 char ：固定长度的字符类型 varchar ：可变长度的字符类型 text ：文本 image ：图片 decimal(5,2) ：5个有效长度数字，小数点后面有2位
基础操作#
mysql 基础操作 select version (); select user(); show databasese; use 库名; show tables [from 库名]; describe 表明; desc [库名.]表明; desc mysql.user desc tables.user from mysql; 查看有哪些表 show tables; 要切换库 use 库名;
#创建kgc库
#查看已有库
#创建表
#查看表数据
#插入表，id为1，名字为wwy，年龄为22，性别为中性。
#查看ky34表所有
SQL语言分类#
DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限
DDL语句#
create 库名; create kgc; use kgc; 切换库 create table 表名(字段1 数据类型，字段2 数据类型，...) create table ky33 (id,name char(10),age int,sex char(2)) #指定数据类型 drop table ky33; #删除表名 drop databases kgc; #删除库 （库中所有表） create database kgc; use kgc; create table ky33(id int,name varchar(10),age年龄 smallint,sex 性别 char(2),primary创建主键字段 key(id);) 设置主键字段，不允许主键字段为空值，每个字段都是唯一。
#删除ky33表
DML语句#
insert into ky33 (id,name,age,sex) values (1,'lxy',27,'女'); #插入 select * from ky33 #按照表结构的形式查看所有字段 insert into ky33 (id,name,age,) values (2,'mz',27); #不插入性别 insert into ky33 values (3,'fwn',23,'男'); #插入数据 desc ky33 #查看表结构 show create table ky34;#查看表结构 update 表名 set 字段1=值[，字段2=值,...] where 条件表达式; delete from 表名 where 条件表达式; update ky33 set sex='男' where id = 2; 第二行添加性别 update ky33 set sex='女', age=18 where name='lxy'; #指定lxy字段 性别为女，年龄为18 不加上where会修改所有字段的值 delete from ky33 where id >=5; #删除第五行和大于五行的 不加where语句 会删除所有表
#创建四个表
#给ky34表第四行添加性别
#在ky34表中删除大于第三行
#指定wwy字段，性别为男，年龄为18.不加上where会修改所有字段
DQL语句#
select * from 表名 [where 条件表达式]; select 字段1，字段2,... from 表名 [where 条件表达式]; select * from 表名\G #纵向查看每行记录的字段的值 select name from ky33 #只查看名字 select name,sex,age from ky33 #自己指定顺序查看 select name,sex,age from ky33 where sex='女'; #查看女生一类 select name,sex,age from ky33 where sex='女' and age>=24; #查看女生并且年龄大于等于24的 select name,sex,age from ky33 where sex='女' or age>=24; #or 满足其中一条条件，女生或者年龄大于24的 select * from ky33 where id <=2; #查看头两行内容 select * from ky33 limit 3; #查看头三行内容 select * from ky33 limit 2,3; #以第二行为基点，查看后三行内容
修改表结构
alter table 旧表名 rename 新表名 #修改表名
alter table 表名 add 新字段 数据类型 [约束属性]; #增加字段
alter table 表名 旧字段 新字段 数据类型[约束属性]; #修改字段
alter table 表名 drop 字段名; #删除字段
修改表名
alter table ky33 rename class33; #修改表名
show create table ky33; / desc ky33 #查看表结构
alter table ky33 add address varchear(60) default '地址不详' #添加地址字段属性
约束属性 primary key;
alter table ky33 change username varchar(10) unique key not null; #修改字段名，数据类型，唯一键，不允许为空
alter table ky33 drop address; #删除字段
#只看ky34表中名字一列
#自己指定顺序查看
#只看女生那一类
#只看男生一类，且年龄大于等于24
#or只要满足其中一个条件。性别为男，或者年龄大于等于24的
#只看头两行
#也是只看头两行
#以第一行为基点，查看后两行。
#将表名改为class34
#无法查到ky34报错
#查看表的结构
#添加地址字段
DCL语句#
1．新建用户 CREATE USER '用户名'@'来源地址' IDENTIFIED BY [PASSWORD] '密码']; CREATE USER '用户名'@'来源地址' IDENTIFIED BY '密码' #来源地址可以是 localhost/ip/网段/主机名/% '用户名'：指定将创建的用户名 '来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符% '密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密； 若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文'; 若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用 rename user '用户名' @'源地址' to '新用户名'@'源地址'; drop user '用户名'@'源地址' #删除 select user,host,authentication_string from mysql.user; #查看user 表 select user (); #查看当前登录用户 set password [for '用户名'@'源地址'] = password ('密码') #修改其他用户密码 alter user '用户名'@'源地址' identified by '密码' set password = password('密码'); % #任意 create user 'lisi'@'localhost' identified by '123'; create user 'lisi'@'192.168.19.%' identified by '123'; #19网段 create user 'lisi'@'%' identified by '123'; #任意地址 rename user '旧用户'@'地址' to '新用户' @ '地址'; #修改用户 alter user 'root'@'localhost' identified by '123123'; #修改用户密码 通过管理员用户来修改
#
primary key 主键约束 字段的值不能重复，不能为空（null），一个表只能由一个主键 unique key 唯一性约束 字段的值不能重复，但能为null，一个表可以有多个唯一键 not null 非空约束 字段的值不能为null default 默认值约束 字段的值如果没有设置则使用默认值自动填充 auto_increment 自增约束 字段的值如果没有设置默认会从1开始 每次递增1，要求自增字段必须为主键 foreign key 外键约束 <font color='red'>可为主键相呼应</font> 保证外键表与主键表相关联的表数据的完整性和一致性 int(N) ZEROFILL 零填充
#
create table 新表 like 旧表;#克隆表结构 insert into 新表 (select * from 旧表);#克隆表数据 可以实现表结构和表数据与旧表都一致 create table 新表 (select * from 旧表); #表数据和旧表一致，表结构和旧表不一定一致 create table cck like ky33; #前面新名 insert into cck select * form ky33; create table qqa (select * from cck);#只能表数据一样，但结构不一样
#克隆可用ky34 取名为cjw
#看查到克隆的表
#克隆表数据 可以实现表结构和表数据与旧表都一致
#
delete from 表名； #一条一条的删除 记录 效率较慢，自增长仍然会按照清空表前的最大记录继续自增 truncate table 表名； #比delete更快 id会从头开始计算 相当于表格式化重建 清空表效率更快，自增长字段会从头开始
#清空ky34表
#给id添加个auto_increment 自增长约束
#清空表
#重新插入个表数据，id是从清空之前的id号后开始的，不是从头开始的
#用truncate 清空表
#重新插入表数据，id是从头开始的。truncate彻底的清空相当于格式化。
#
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表
CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]); [,PRIMARY KEY (主键名)]);
create temporary table 表名 (....)； #临时表跟普通表一样进行增删改查，但是show tables 是查不到的；临时表只能在当前会话中有效，退出当前会话或在其他会话中，临时表都会失效。
#
主键表和外键表的理解： （1）以公共关键字作主键的表为主键表（父表、主表） （2）以公共关键字作外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
#创建主表 profession create table profession (pid int(4),proname varchar(50)); #创建从表 student create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4)); add #添加 drop #删除 主键表：alter table 表名 add primary key (主键字段); #已存在的表 外键表：alter table 表名 add foreign key (外键字段) references 主键表名 (主键字段)； 插入新数据时，需要先在主键表插入数据再在外键表插入相关联的数据;删除数据时，需要先在外键表删除再在主键表删除相关联的数据; alter table 表名 drop foreign key '外键别名'; alter table 表名 drop key '键的字段名';
找回root密码#
1) 修改mysql配置文件，在[mysqld]配置项添加 skip-grant-tables 配置参数 2)重启mysqld服务，使用mysql 密码直接登录数据库 3)执行 update mysql.user set authentication_string=password('密码') where user='root'; #修改 root 用户密码 4)还原MySQL配置，重启mysqld服务，使用mysql -u 用户名 -p 密码 [-和mysql地址 -P mysql端口] 命令登录数据库验证
#密码忘记
#编辑mysql配置文件
#加入配置，mysql不使用授权表
#重启mysql服务
#输入MySQL直接登录
#查看下mysql密码密钥
#将root密码改为abc123
#root密码已改
#刷新下
#退出MySQL
#用修改后的密码登录可以登录上去
再去配置文件将给的MySQL不授权配置删掉
#再重启下mysql服务
授权用户权限 all privilege。 都有哪些权限
insert(插入数据)
select (查询数据) update (更新表的数据) delete (删除表中数据) create (创建库，表) drop (删除库，表) refernces index(建立索引)
alter(更改表属性) create temp orary tables
lock tables (锁表) execute create view (创建视图）
show view (显示视图)
create routine (创建存储过程）
alter routine(修改存储过程)
event (事件)
trigger on (创建触发器)
权限管理#
grant 权限1,权限2,.. on 库名.表名 to '用户名'@'源地址' [identifide by '密码']; #5.7或之前版本支持创建新用户和授予权限，8.0版本只能用于授予权限 all *.* show grant for '用户名'@'源地址'; #查看用户 revoke 权限1,权限2, ... ON 库名.表名from '用户名'@'源地址';
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？