MySql教程
17.MySQL数据库
本文主要是介绍17.MySQL数据库，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1 数据库原理
数据库：是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。简单理解就是存储电子文件的仓库，用户可以对仓库中的资料进行增删改查等操作。
数据库管理系统（Database Management System）：简称DBMS,是为了管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。
数据库分类：
关系型数据库（SQL）
- MySQL
- MariaDB
- Oracle
非关系型数据库（NOSQL）
- Redis
- MongoDB
键值数据库
- Dynamo
- LevelDB
对于数据库相关说明，例如：数据库的设计、数据库的模型、基本词汇解释MariaDB官方给出了相关知识库：https://mariadb.com/kb/en/database-theory/。
2 MySQL的安装和基本使用
2.1 MySQL介绍
2.1.1 MySQL历史
MySQL原本是一个开源的关系数据库管理系统，不过后来被Sun公司收购，而后Sun公司又被（Oracle）公司收购，现在MySQL已成为Oracle旗下产品。
2.1.2 MySQL分支
MySQL的三大主要分支：
- mysql
- mariadb
- percona server
其中mysql和mariadb是较为主流的开源数据库产品
他们的官方文档地址：
https://dev.mysql.com/doc/
https://mariadb.com/kb/en/
https://www.percona.com/software/mysql-database/percona-server
2.1.3 MySQL的特性
-
插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用 户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
-
单进程，多线程
-
诸多扩展和新特性
-
提供了较多测试组件
-
开源
2.2 MySQL安装
2.2.1 安装方式介绍
- 源码包编译安装
- 二进制程序包（类似于windows中绿色安装）：解压至指定路径，简单配置即可使用
- 使用程序包管理器安装，也就是yum安装
2.2.2 RPM包安装MySQL
这种方式较为简单，一般选择国内镜像仓库，配置yum源安装即可，与普通rpm包安装没有区别，缺点是不能定制配置，生产环境不建议采用这种方式安装
CentOS国内镜像：
https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/
注意：这种方式安装完存在允许root用户空口令登陆，允许匿名用户登陆等安全问题，针对这个问题可以运行脚本mysql_secure_installation来设置
设置数据库管理员root口令 禁止root远程登录 删除anonymous用户帐号 删除test数据库
2.3 MySQL组成
2.3.1 客户端程序
-
mysql：交互式的客户端工具
-
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成 insert等写操作语句保存文本文件中
-
mysqladmin：基于mysql协议管理mysqld
-
mysqlimport：数据导入工具
MyISAM存储引擎的管理工具：
-
myisamchk：检查MyISAM库
-
myisampack：打包MyISAM表，只读
2.3.2 服务端程序
- mysqld_safe
- mysqld
- mysqld_multi 多实例 ，示例：mysqld_multi --example
2.3.3 用户账号
mysql用户账号组成：
'USERNAME'@'HOST‘
HOST限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：
% 匹配任意长度的任意字符 172.16.0.0/255.255.0.0 或 172.16.%.%
_ 匹配任意单个字符
2.3.4 mysql客户端命令
mysql命令格式：
mysql [OPTIONS] [database]
常用选项：
- -u, --user= 用户名,默认为root
- -h, --host= 服务器主机,默认为localhost
- -p, --passowrd= 用户密码,建议使用-p,默认为空密码
- -P, --port= 服务器端口
- -S, --socket= 指定连接socket文件路径
- -C, --compress 启用压缩
- -e “SQL“ 执行SQL命令
- -V, --version 显示版本
- -v --verbose 显示详细信息
- --print-defaults 获取程序默认使用的配置
mysql 使用模式：
交互式
客户端命令：本地执行，每个命令都完整形式和简写格式 mysql> \h, help mysql> \u，use mysql> \s，status mysql> \!，system 服务端命令：通过mysql协议发往服务器执行并取回结果，命令末尾都必须使用命令结束符号，默认为分号 mysql>SELECT VERSION();
非交互式
mysql –uUSERNAME -pPASSWORD < /path/somefile.sql cat /path/somefile.sql | mysql –uUSERNAME -pPASSWORD mysql>source /path/from/somefile.sql
范例：
2.3.5 mysqladmin命令
格式：
mysqladmin [OPTIONS] command command....
范例：
#查看mysql服务是否正常，如果正常提示mysqld is alive mysqladmin -uroot -pcentos ping #关闭mysql服务，但mysqladmin命令无法开启 mysqladmin –uroot –pcentos shutdown #创建数据库testdb mysqladmin -uroot –pcentos create testdb #删除数据库testdb mysqladmin -uroot -pcentos drop testdb #修改root密码 mysqladmin –uroot –pcentos password ‘magedu’ #日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N mysqladmin -uroot -pcentos flush-logs
2.3.6 服务端配置
服务器端可以通过命令行或者配置文件进行配置，这里主要介绍配置文件配置方式。
服务器端配置文件：
/etc/my.cnf #Global选项 /etc/mysql/my.cnf #Global选项 ~/.my.cnf #User-specific 选项
配置文件格式：
[mysqld] [mysqld_safe] [mysqld_multi] [mysql] [mysqldump] [server] [client] 格式：parameter = value 说明：_和- 相同 1，ON，TRUE意义相同， 0，OFF，FALSE意义相同
服务器监听的两种socket地址：
- ip socket: 监听在tcp的3306端口，支持远程通信 ，侦听3306/tcp端口可以在绑定有一个或全部接 口IP上
- unix sock: 监听在sock文件上，仅支持本机通信, 如：/var/lib/mysql/mysql.sock) 说明：host为localhost 时自动使用unix sock
可以通过修改配置文件实现只侦听本地客户端
vim /etc/my.cnf [mysqld] skip-networking=1
2.4 二进制安装MySQL
这里以安装MySQL5.7版本为例
官方安装手册：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
MySQL下载地址：https://downloads.mysql.com/archives/community/
2.4.1 创建MySQL用户和组
[root@localhost local]# groupadd -r -g 306 mysql [root@localhost local]# useradd -r -g 306 -u 306 -d /data/mysql -s /bin/false mysql [root@localhost local]# getent passwd mysql mysql:x:306:306::/data/mysql:/bin/false
2.4.2 准备二进制文件
[root@localhost local]# cd /usr/local [root@localhost local]# tar xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [root@localhost local]# ln -s mysql-5.7.33-linux-glibc2.12-x86_64 mysql [root@localhost local]# ls bin games lib libexec mysql-5.7.33-linux-glibc2.12-x86_64 sbin src etc include lib64 mysql mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz share [root@localhost local]# chown -R root.root /usr/local/mysql/ #将路径添加至环境变量 [root@localhost mysql]# echo 'PATH=/app/boots-dev/bin/:$PATH' > /etc/profile.d/mysql.sh [root@localhost mysql]# . /etc/profile.d/mysql.sh
2.4.3 准备配置文件
[root@localhost local]# vim /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock
2.4.4 创建数据库文件
#初始命令会读取配置文件中的配置，由于之前已经指定了数据库文件位置，所以这里不用加入数据库路径选项 [root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql 2021-05-17T15:36:16.457156Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-05-17T15:36:18.652116Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-05-17T15:36:18.950575Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-05-17T15:36:19.034228Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9ff6938c-b725-11eb-b707-000c2906d605. 2021-05-17T15:36:19.038515Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-05-17T15:36:19.564502Z 0 [Warning] CA certificate ca.pem is self signed. 2021-05-17T15:36:19.707592Z 1 [Note] A temporary password is generated for root@localhost: !Mn4hek<bO%. #系统生成的首次登陆密码 #查看数据库文件是否生成 [root@localhost mysql]# ls /data/mysql/ auto.cnf client-cert.pem ibdata1 mysql public_key.pem sys ca-key.pem client-key.pem ib_logfile0 performance_schema server-cert.pem ca.pem ib_buffer_pool ib_logfile1 private_key.pem server-key.pem
2.4.5 准备服务脚本并启动
#准备服务脚本
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
#启动数据库
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'.
... SUCCESS!
#查看是否启动成功
[root@localhost mysql]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 80 [::]:3306 [::]:*
2.4.6 修改默认口令
[root@localhost mysql]# mysqladmin -uroot -p'aY4lhQ/uyB+%' password database mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. #测试是否能登陆 [root@localhost mysql]# mysql -uroot -pdatabase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. ...... #安全初始化 [root@localhost mysql]# mysql_secure_installation Securing the MySQL server deployment. Enter password for user root: ......
2.5 源码编译安装
这里以安装MySQL5.7版本为例
下载源码包：https://dev.mysql.com/downloads/mysql/
官方参考文档：https://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html
注意：安装过程应保证4G以上内存
2.5.1 安装相关依赖包
[root@localhost ~]# yum -y install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel
2.5.2 创建用户
[root@localhost local]# useradd -r -s /sbin/nologin -d /data/mysql mysql
2.5.3 源码编译安装
[root@localhost local]# ls bin etc games include lib lib64 libexec mysql-5.7.34.tar.gz sbin share src #解压缩源码包 [root@localhost local]# tar xvf mysql-5.7.34.tar.gz #进入目录编译安装 [root@localhost local]# cd mysql-5.7.34 [root@localhost mysql-5.7.34]# mkdir bld [root@localhost mysql-5.7.34]# cd bld #注意：5.7版本mysql要求Boost版本为1.59.0，由于yum版本不满足要求，所以还需再官网下载boost1.59.0版本压缩包，这里使用-DWITH_BOOST可以指定boost1.59.0版本压缩包的路径，即boost_1_59_0.tar.gz的位置 [root@localhost bld]# cmake .. -DCMAKE_INSTALL_PREFIX=/app/mysql \ -DMYSQL_DATADIR=/data/mysql/ \ -DSYSCONFDIR=/etc/ \ -DMYSQL_USER=mysql \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITHOUT_MROONGA_STORAGE_ENGINE=1 \ -DWITH_DEBUG=0 \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_BOOST=/app/boots \ -DWITH_LIBWRAP=0 \ -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci [root@localhost bld]# make -j 16 #编译过程由于内存不足导致报错c++: internal compiler error，添加内存重新编译即可 [root@localhost bld]# make install
2.5.4 配置数据库
#配置环境变量 [root@localhost bld]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@localhost bld]# . /etc/profile.d/mysql.sh #生成数据库文件 [root@localhost mysql]# mysqld --initialize --datadir=/data/mysql/ --user=mysql 2021-05-18T17:55:55.406121Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2021-05-18T17:55:55.663535Z 0 [Warning] InnoDB: New log files created, LSN=45790 2021-05-18T17:55:55.703042Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2021-05-18T17:55:55.760620Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4b4b5646-b802-11eb-8afd-000c2906d605. 2021-05-18T17:55:55.772427Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2021-05-18T17:55:56.274206Z 0 [Warning] CA certificate ca.pem is self signed. 2021-05-18T17:55:56.660120Z 1 [Note] A temporary password is generated for root@localhost: na6t_YuJgrAs #配置数据库配置文件 [root@localhost mysql]# vim /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock #拷贝服务启动脚本 [root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld [root@localhost mysql]# chkconfig --add mysqld [root@localhost mysql]# service mysqld start Starting MySQL.Logging to '/data/mysql/mysql.log'. SUCCESS!
2.5.5 安全初始化
[root@localhost mysql]# mysql_secure_installation #测试是否能登陆 [root@localhost mysql]# mysql -uroot -pdatabase mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.34 Source distribution
3 SQL语言
3.1 关系型数据库的常见组件
- 数据库：database
- 表：table，行：row 列：column
- 索引：index
- 视图：view
- 用户：user
- 权限：privilege
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
3.2 SQL语言的语法标准
3.2.1 SQL语法
SQL语句不区分大小写，建议使用大写
SQL语句可以多行或者单行书写，通常以;结尾
关键词不能跨多行或简写
注释：
-
SQL标准：
-- 注释内容 用于单行注释，注意有空格
/*注释内容*/ 多行注释
-
MySQL注释：
# 注释内容
3.2.2 数据库对象和命名
数据库的组件（对象）：
数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
- 必须以字母开头，可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
- 同一database(Schema)下的对象不能同名
3.2.3 SQL语句分类
-
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
-
DML: Data Manipulation Language 数据操纵语言 INSERT，DELETE，UPDATE
-
DQL：Data Query Language 数据查询语言
SELECT
-
DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK
官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
本地mysql提供的帮助
mysql> HELP KEYWORD
3.2.3 数据类型
官方数据类型参考链接：https://dev.mysql.com/doc/refman/8.0/en/data-types.html
整数型
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
浮点型
定点数
字符串
这篇关于17.MySQL数据库的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
部署MySQL集群入门：新手必读指南11-20
-
部署MySQL集群教程：初学者指南11-20
-
部署MySQL集群项目实战：新手教程11-20
-
部署MySQL集群资料：新手入门教程11-20
-
MySQL集群部署教程：入门级详解11-20
-
MySQL集群教程：入门与实践指南11-20
-
部署MySQL集群教程：新手入门指南11-20
-
MySQL读写分离教程：轻松入门11-20
-
部署MySQL集群入门：一步一步搭建你的数据库集群11-20
-
部署MySQL集群学习：入门教程11-19
-
如何部署MySQL集群：新手入门教程11-19
-
Mysql安装教程：新手必看的详细安装指南11-19
-
Mysql安装入门：新手必读指南11-18
-
MySQL事务MVCC原理入门详解11-18
-
MySQL资料：新手入门教程11-16
栏目导航
