MySql-base

MySql
CS
发布日期:   2024-02-08
更新日期:   2024-07-01
文章字数:   2.5k

MySQL-base

Linux安装mysql

屏蔽防火墙

# 确认服务器防火墙等安全配置关闭
# 防火墙关闭
[root@localhost mysql]# firewall-cmd --state
not running
# iptables 无策略
[root@localhost mysql]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
# SELinux安全组件关闭
[root@localhost mysql]# sestatus
SELinux status:                 disabled
# 在正常安装完成之后，再开启安全相关的组件及添加相关的安全配置
# Linux安全组件相关移步Linux相关内容学习

rpm安装mysql

  1. 查看MySQL用户组和用户信息

    # 查看是否存在mysql相关的用户组和用户信息
groups mysql
# 创建mysql用户组和用户
groupadd mysql && useradd -r -g mysql mysql
# 数据目录授权
chown mysql:mysql -R /data/mysql

  2. 删除已存在的MySQL、mariadb

    rpm -qa|grep mysql
rpm -qa|grep mariadb
rpm -e --nodeps mysql-libs-5.1.73-1.el6.x86_64
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

  3. 提升/tmp权限

    因为默认安装会在tmp下面创建sock

    chmod -R 777 /tmp

  4. 检查依赖

    rpm -qa|grep libaio
rpm -qa|grep net-tools
yum -y install libaio net-tools

  5. 准备安装包

    官网下载对应的rpm，以下rpm是必要的

    mysql-community-common-5.7.16-1.el6.x86_64.rpm
    mysql-community-libs-5.7.16-1.el6.x86_64.rpm
    mysql-community-client-5.7.16-1.el6.x86_64.rpm
    mysql-community-server-5.7.16-1.el6.x86_64.rpm

  6. 依次执行安装

    # 卸载
rpm -e mysql-community-server-xxx.rpm 
# 安装
rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm

单实例

默认cnf配置

[root@localhost mysql]# cat /etc/my.cnf
# mysql 默认cnf 配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

  1. 初始化MySQL

    mysqld --initialize --user=mysql
# 获取初始密码
cat /var/log/mysqld.log | tail -n 10

  2. 启动MySQL服务

    systemctl start mysqld.service
systemctl status mysqld
# 设置开机自启
systemctl enable mysqld.sercice

  3. 初始化（首次登陆）

    # 通过sock登陆
mysql -u root -p --
socket=/data/mysql/mysql_3306/tmp/mysql3306.sock
# 初次登陆要求修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
# 授权所有Host可登陆
UPDATE mysql.user SET Host = '%' WHERE User = 'root';
# 授权登陆 注意还有Linux的防火墙端口监听、白名单设置，参见Linux相关内容
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
# 修改字符集
show variables like 'character%';
vi /etc/my.cnf
    # 增加以下配置
    character_set_server=utf8
    init_connect=’SET NAMES utf8’
systemctl restart mysqld

多实例

配置文件

  1. my.cnf
[root@localhost ~]# cat /etc/my.cnf.d/mysql-3306.cnf
[mysqld]
port=3306
datadir=/data/mysql/mysql_3306
socket=/data/mysql/mysql_3306/tmp/mysql3306.sock
symbolic-links=0

[mysqld_safe]
log-error=/data/mysql/mysql_3306/log/3306.log
pid-file=/data/mysql/mysql_3306/3306.pid

[client]
port=3306
socket=/data/mysql/mysql_3306/tmp/mysql3306.sock
  1. mysql.service
[root@localhost ~]# cat /etc/systemd/system/mysqld-3306.service
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/sbin/mysqld --defaults-file=/etc/my.cnf.d/mysql-3306.cnf
Restart=always

[Install]
WantedBy=multi-user.target
  1. 文件目录，cnf配置中依赖的文件路径都需要手动创建，实例化数据库不会 自动创建
[root@localhost mysql]# tree /data/mysql/ -d
/data/mysql/
├── mysql_3306
│   ├── log
│   ├── mysql
│   ├── performance_schema
│   ├── sys
│   └── tmp
├── mysql_3307
│   ├── log
│   ├── mysql
│   ├── performance_schema
│   ├── sys
│   └── tmp
└── mysql_3308
    ├── log
    ├── mysql
    ├── performance_schema
    ├── sys
    └── tmp

实例化

# 实例化MySQL
/usr/sbin/mysqld --defaults-file=/etc/my.cnf.d/mysql-3306.cnf --initialize --user=mysql
# 启动服务 
systemctl start mysql-3306.service
# 查看当前mysql相关的服务
[root@localhost mysql]# systemctl list-units --type=service | grep mysql
mysqld-3306.service                loaded active running MySQL Server
mysqld-3307.service                loaded active running MySQL Server
mysqld-3308.service                loaded active running MySQL Server

mysql语法基础

SQL分类

DDL数据定义语言

定义数据库对象，核心指令CREATE、ALTER、DROP

DML数据库操纵语言

访问数据CRUD（CREATE，READ，UPDATE，DELETE），即增删改查

TCL事务控制语言

管理数据库中的事务，核心指令COMMIT、ROLLBAK

DCL数据控制语言

对数据访问权控制，核心指令GRANT,REVOKE

可以控制的权限有：CONNECT,SELECT,INSERT,UPDATE,DELETE,EXECUTE,USAGE,REFERENCES

连接和组合

JOIN

连接可以代替子查询，效率一般比子查询高。

join

组合

UNION会去除相同行，UNION ALL 会保留相同行。

JOIN vs UNION：

  • JOIN 中连接的列可能不同，UNION中列、列顺序必须相同
  • UNION 讲查询结果行放在一起（垂直放置）但JOIN将查询结果列放在一起（水平放置），即它构成一个笛卡尔积

函数

文本处理

函数 说明
LEFT()、RIGHT() 左边或者右边的字符
LOWER()、UPPER() 转换为小写或者大写
LTRIM()、RTIM() 去除左边或者右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值

日期和时间处理

函 数 说 明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

数值处理

函数 说明
SIN() 正弦
COS() 余弦
TAN() 正切
ABS() 绝对值
SQRT() 平方根
MOD() 余数
EXP() 指数
PI() 圆周率
RAND() 随机数

汇总

函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和

排序与分组

排序

ORDER BY ASC/DESC;

// 指定多个列的排序方向

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

分组

  • GROUP BY
  • HAVING
    • HAVING 用于对汇总的GROUP BY 结果进行过滤
    • HAVING 要求存在一个GROUP BY子句
    • 与WHERE差异：HAVING 用于汇总的组记录，WHERE 用于单个记录

视图、索引

视图

  • 基于SQL语句结果可视化的表
  • 虚拟表，不能进行索引操作
  • 主要用于简化SQL操作
  • 通过给视图访问权限保证数据安全

索引

  • 通过索引更高效地查询数据
  • 用户无法看到索引，只能用来加速查询
  • 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
  • 唯一索引表明此索引的每一个索引值只对应唯一的数据记录。

// 创建索引

CREATE INDEX user_index
ON user (id);

//创建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

//删除索引

ALTER TABLE user
DROP INDEX user_index;

存储过程

  • 存储过程可以看成是对一系列SQL操作的批处理
  • 优点
    • 代码封装
    • 代码复用
    • 因为是预先编译，所以性能很高
  • 创建存储过程
    • 命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
    • 包含 in、out 和 inout 三种参数。
    • 给变量赋值都需要用 select into 语句。
    • 每次只能给一个变量赋值，不支持集合的操作。

DROP PROCEDURE IF EXISTS proc_adder;
DELIMITER ;;
CREATE DEFINER=root@localhost PROCEDURE proc_adder(IN a int, IN b int, OUT sum int)
BEGIN
DECLARE c int;
if a is null then set a = 0;
end if;

if b is null then set b = 0;
end if;

set sum = a + b;
END
;;
DELIMITER ;

set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;

游标

游标是一个存储在DBMS服务器上的数据库查询，不是SELECT语句，而是SELECT检索的结果集。

主要用于交互式应用，用户需要对数据集重的任意行进行浏览和修改。

DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
– 创建接收游标数据的变量
DECLARE sid INT;
DECLARE sname VARCHAR(10);
– 创建总数变量
DECLARE sage INT;
– 创建结束标志变量
DECLARE done INT DEFAULT false;
– 创建游标
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
– 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;

CLOSE cur;
SELECT total;
END $
DELIMITER ;

– 调用存储过程
call getTotal();

触发器

触发器是一种与表操作有关的数据库对象，当表出现指定时间时，将调用该对象。

new/old

  • MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。
  • 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
  • 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
  • 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
  • 使用方法：NEW.columnName （columnName 为相应数据表某一列名）

DELIMITER $
CREATE TRIGGER trigger_insert_user
AFTER INSERT ON user
FOR EACH ROW
BEGIN
INSERT INTO user_history(user_id, operate_type, operate_time)
VALUES (NEW.id, ‘add a user’, now());
END $
DELIMITER ;

文章作者: LoaderLand
文章链接: https://loaderland.github.io/post/e2efbbbb.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 LoaderLand !
MySql
赏
  目录