MySQL入门
发表于 · 归类于
技术 · 阅读完需 17 分钟 ·
阅读量 报告错误
一、介绍
MySQL是一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。
二、安装
Windows系统下
集成方式安装
如果仅仅是为了使用MySQL，建议直接使用phpenv、phpStudy进行一键安装，即可使用。
原始安装
下载
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
#较大数据导入
max_allowed_packet = 1024M
[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数据文件
- .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息。
- .ibd文件：使用表独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
- .bdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或多个ibdata文件
MyIsam数据文件
- .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息。
- .myd文件：主要用来存储表数据信息。
- .myi文件：主要用来存储表数据文件中任何索引的数据树。
数据类型
整型：tinyint(一个字节/int8)、smallint(2个字节/int16)、mediumint(3个字节/int24)、int(4个字节/int32)、bigint(8个字节/int64)
索引原则
- 选择唯一性索引
- 为经常需要排序、分组和联合操作的字段建立索引
- 为常作为查询条件的字段建立索引
- 限制索引的数目
- 尽量使用数据量少的索引
- 尽量使用前缀来索引
- 删除不再使用或者很少使用的索引
- 最左前缀匹配原则，非常重要的原则。
.=和in可以乱序。- 尽量选择区分度高的列作为索引。
- 索引列不能参与计算，保持列“干净”。
- 尽量的扩展索引，不要新建索引。
- 当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率
- 适合做索引：主键、外键、手机号、学号、状态
- 不适合做索引：性别、姓名、时间、价格、数量
五、语法
（1）常用语句
SELECT * FROM `record` WHERE `id` = 616;
INSERT INTO `record`(`id`,`tid`,`score`,`remark`) VALUES(1,2,98,'优秀');
UPDATE `record` SET `score` = 92 , `remark` = "优秀" WHERE `id` = 616;
DELETE FROM `record` WHERE `id` = 616;
SELECT * FROM `record` WHERE `tid` = 1 ORDER BY `score` DESC,`id` ASC;
SELECT * FROM `record` WHERE `tid` = 1 LIMIT 10,10;
SELECT `a`.*,`b`.`name` FROM `record` AS a LEFT JOIN `b` ON `a`.`bid` = `b`.`id` WHERE a.`id` = 616;
SELECT sum(`score`) AS `total` FROM `record` WHERE `tid` = 616;
SELECT `tid`,sum(`score`) AS `total` FROM `record` GROUP BY `tid`;
（2）常用函数
| 函数名称 | 示例 | 结果 | 描述 |
|---|
| concat | concat(s1,s2…sn) | s1s2s3 | 字符串拼接 |
| insert | insert(str,x,y,insert) | 123 | 字符串替换 |
| lower/upper | upper('a') | A | 大小写转换 |
| left/right | ——— | ——– | ——– |
| lpad/rpad | ——— | ——– | ——– |
| trim/ltrim/rtrim | ——— | ——– | ——– |
| replace | ——— | ——– | ——– |
| strcmp | ——— | ——– | ——– |
| substring | substring('1234567890',2,4) | 2345 | 字符串截取 |
| abs | abs(-80) | 80 | 绝对值 |
| ceil | ceil(8.36) | 9 | 天花板向上取整 |
| floor | floor(8.36) | 8 | 向下取整 |
| mod | mod(22,4) | 2 | 取余 |
| rand | rand() | 0.5459487750622898 | 返回0-1随机小数 |
| round | round(2.62) | 3 | 四舍五入 |
| truncate | truncate(1.245,2) | 1.24 | 保留两位小数 |
| curdate | curdate() | 2021-09-24 | 获取当前日期 |
| curtime | curtime() | 11:54:59 | 获取当前时间 |
| now | now() | 2021-09-24 11:51:18 | 获取当前时间 |
| DATE_FORMAT | DATE_FORMAT(‘2021-12-11 12:23:34’,’%Y-%m-%d’) | 2021-09-24 | 获取日期 |
| unix_timestamp | unix_timestamp() | 1632455518 | 获取当前时间戳 |
| from_unixtime | from_unixtime(1632455518) | 2021-09-24 11:51:58 | 时间戳转换成时间格式 |
| week | week("2021-09-24 11:51:58") | 38 | 获取在当年的第几周 |
| year | year("2021-09-24 11:51:58") | 2021 | 获取年份 |
| hour | hour("2021-09-24 11:51:58") | 11 | 获取小时 |
| database | database() | mysql | 查询当前数据库 |
| version | version() | 8.0.20 | 查询数据库版本 |
| user | user() | root@localhost | 查询当前用户 |
| password | password("123") | ———- | 密码加密 |
| md5 | md5('1234') | 81d…055 | MD5加密 |
| inet_aton/inet_ntoa | inet_aton('192.168.1.1') | 3232235777 | IPv4转数字 |
| inet6_aton/inet6_ntoa | 0 | 0 | IPv6转 |
（3）库和表操作
drop database table_name;
truncate table table_name;
（4）函数示例
字符串拼接-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`);//左边为单一的，右边为多个
- 记住：能用IN就用IN，不能用IN再用FIND_IN_SET。
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
带条件备份
mysqldump -u root -p[password] [database] [table] --where="id<1000" > /tmp/mydatabasename.sql
Linux定时器备份
40 2 * * * /usr/bin/mysqldump -u root -p[password] [database] > /tmp/database/[database]-`date +%s`.sql