MySQL入门


一、介绍

MySQL是一款安全、跨平台、高效的,并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持,由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。

二、安装

Windows系统下

集成方式安装

如果仅仅是为了使用MySQL,建议直接使用phpenvphpStudy进行一键安装,即可使用。

原始安装

下载

Linux系统下

安装MySQL-8.0.20数据库

三、配置

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=F:\\phpEnv\\mysql\\mysql-8.0.25   # 切记此处一定要用双斜杠"\\"
# 设置mysql数据库的数据的存放目录
datadir=F:\\phpEnv\\mysql\\mysql-8.0.25\\Data   # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#严格模式下使用这个
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#非严格模式下
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

四、理论

存储引擎

在MySQL中,主要的存储引擎有两种:MyIsam和InnoDB。

MyIsam是高速引擎,拥有较高的插入、查询速度。但不支持事务、行为等;

InnoDB是5.5版本后MySQL默认的数据库,支持事务和行级锁定,事务处理、回滚、崩溃修复能力和多版本并发控制,比MyIsam处理速度稍慢,支持外键。

InnoDB:支持事务处理,支持外键,支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那一般都会选择InnoDB。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为它支持事务的提交和回滚。

MyIsam:插入数据快,空间和内存使用比较低。如果表主要是用于插入新纪录和读出记录,那么选择MyIsam能实现处理高效率。

数据格式

InnoDB数据文件
  1. .frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息。
  2. .ibd文件:使用表独享表空间存储表数据和索引信息,一张表对应一个ibd文件。
  3. .bdata文件:使用共享表空间存储表数据和索引信息,所有表共同使用一个或多个ibdata文件
MyIsam数据文件
  1. .frm文件:主要存放与表相关的数据信息,主要包括表结构的定义信息。
  2. .myd文件:主要用来存储表数据信息。
  3. .myi文件:主要用来存储表数据文件中任何索引的数据树。
数据类型

整型:tinyint(一个字节/int8)、smallint(2个字节/int16)、mediumint(3个字节/int24)、int(4个字节/int32)、bigint(8个字节/int64)

五、语法

常用函数

函数名称示例结果描述
concatconcat(s1,s2…sn)s1s2s3字符串拼接
insertinsert(str,x,y,insert)123字符串替换
lower/upperupper('a')A大小写转换
left/right—————–——–
lpad/rpad—————–——–
trim/ltrim/rtrim—————–——–
replace—————–——–
strcmp—————–——–
substringsubstring('1234567890',2,4)2345字符串截取
absabs(-80)80绝对值
ceilceil(8.36)9天花板向上取整
floorfloor(8.36)8向下取整
modmod(22,4)2取余
randrand()0.5459487750622898返回0-1随机小数
roundround(2.62)3四舍五入
truncatetruncate(1.245,2)1.24保留两位小数
curdatecurdate()2021-09-24获取当前日期
curtimecurtime()11:54:59获取当前时间
nownow()2021-09-24 11:51:18获取当前时间
unix_timestampunix_timestamp()1632455518获取当前时间戳
from_unixtimefrom_unixtime(1632455518)2021-09-24 11:51:58时间戳转换成时间格式
weekweek("2021-09-24 11:51:58")38获取在当年的第几周
yearyear("2021-09-24 11:51:58")2021获取年份
hourhour("2021-09-24 11:51:58")11获取小时
databasedatabase()mysql查询当前数据库
versionversion()8.0.20查询数据库版本
useruser()root@localhost查询当前用户
passwordpassword("123")———-密码加密
md5md5('1234')81d…055MD5加密
inet_aton/inet_ntoainet_aton('192.168.1.1')3232235777IPv4转数字
inet6_aton/inet6_ntoa00IPv6转

函数示例

字符串拼接-CONCAT
UPDATE `record` SET `remark` = CONCAT(`remark`,",增加其他备注") WHERE `id` = 616;
SELECT GROUP_CONCAT(`telephone`) FROM `b_admin`;
FIND_IN_SET
SELECT * FROM wii_project WHERE name FIND_IN_SET(`participant`,'{$myid}')
SELECT `realname`,`studentid` FROM `b_userinfo` WHERE `id` IN (101,102,103);
SELECT * FROM b_admin WHERE account IN ('user1','user2');
用于查找以逗号分隔的多个数字,例如names:"张三,李四,王五"
SELECT FIND_IN_SET('b','a,b,c,d');返回2
SELECT FIND_IN_SET('b',`sets`);//左边为单一的,右边为多个
JSON查询
SELECT collect->>"$.ip" AS ip FROM `b_volunteer`;
UPDATE t_json SET info = json_set(info,'$.ip','192.168.1.1');

六、维护

Linux下MySQL数据库备份

普通备份
mysqldump -u root -p[password] [database] > /tmp/mydatabasename.sql
压缩备份
mysqldump -h主机名 -u用户名 -p密码 数据库名字  | gzip >备份的数据库名字.sql.gz
全备份
mysqldump --all-databases -h主机名 -u用户名 -p密码 > 备份数据库名字.sql
备份表
mysqldump -u root -p[password] [database] [table1] [table2] > /tmp/mydatabasename.sql
不备份某个表
mysqldump -u root -p[password] [database] --ignore-table=[database].[table_not] > /tmp/mydatabasename.sql
Linux定时器备份
40 2 * * * /usr/bin/mysqldump -u root -p[password] [database] > /tmp/database/[database]-`date +%s`.sql