MySQL第二天学习笔记

MySQL基础管理-2

1.MySQL的启动过程
1.1 日常启停
mysql.server start ----> mysqld_safe —>mysqld
mysql.service ---->mysqld
需要依赖于 /etc/my.cnf
1.2维护性的任务
mysqld_safe --skip-grant-tables --skip-networking &
我们一般会将我们需要的参数临时加到命令行
也会读取/etc/my.cnf的内容，但是如果冲突，命令行优先级最高

mysqld_safe & 这种方式启动
mysqladmin -uroot -p123 shutdown 要这样关闭

2.初始化配置
2.1 作用
（1）影响数据库的启动
（2）影响到客户端的功能
2.2初始化配置的方法
（1）初始化配置文件（例如/etc/my.cnf）
（2）启动命令行上进行配置（例如mysqld_safe mysqld）
（3）预编译时设置（仅限于编译安装时设置）
2.3 初始化配置文件的书写格式
[标签]
xxx=xxx
[标签]
xxx=xxx

2.4 配置文件标签的归类
服务器端：
[mysqld]
[mysqld_safe]
[server]

客户端
[mysql]
[mysqladmin]
[mysqldump]
[client]

2.5 配置文件设置样板（5.7版本）
#服务器端配置
[mysqld]
#用户
user=mysql
#软件安装目录
basedir=/application/mysql
#数据路径
datadir=/data/mysql/data
#socket文件位置
socket=/tmp/mysql.sock
#服务器id号
server_id=6
#端口号
port=3306

#客户端配置
[mysql]
#socket文件位置
socket=/tmp/mysql.sock

2.6 配置文件读取顺序
[root@db01 ~]#mysql --help --verbose|grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

2.7 强制使用自定义配置文件
–defaults-file
mysqld_safe/mysqld --defaults-file=/tmp/aa.txt &

3.MySQL的连接管理
注意：提前应该将用户授权做好
mysql> grant all on . to root@‘10.0.0.%’ identified by ‘123’;
TCPIP:
mysql -uroot -p -h 10.0.0.51 -P3306

Socket
mysql -uroot -p -S /tmp/mysql.sock

3.2 客户端工具
sqlyog
navicat

4. 多实例管理
4.1准备多个目录
mkdir -p /data/330{7,8,9}/data

4.2 准备配置文件
cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

4.3初始化三套数据

mv /etc/my.cnf /etc/my.cnf.bak
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql

4.4 systemd管理多实例
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service

vim /etc/systemd/system/mysqld3307.service
改一行：ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
vim /etc/systemd/system/mysqld3308.service
改一行：ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
vim /etc/systemd/system/mysqld3309.service
改一行：ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf

4.5 授权
chown -R mysql.mysql /data/*
4.6启动
systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
4.7针对多实例
netstat -lntup|grep 330
[root@db01 ~]#netstat -lntup|grep 330
tcp6 0 0 :::3307 ::😗 LISTEN 5479/mysqld
tcp6 0 0 :::3308 ::😗 LISTEN 5486/mysqld
tcp6 0 0 :::3309 ::😗 LISTEN 5494/mysqld
tcp6 0 0 :::3306 ::😗 LISTEN 5360/mysqld
检查server_id
[root@db01 ~]#mysql -S /data/3307/mysql.sock -e “select @@server_id”
±------------+
| @@server_id |
±------------+
| 7 |
±------------+
[root@db01 ~]#mysql -S /data/3308/mysql.sock -e “select @@server_id”
±------------+
| @@server_id |
±------------+
| 8 |
±------------+
[root@db01 ~]#mysql -S /data/3309/mysql.sock -e “select @@server_id”
±------------+
| @@server_id |
±------------+
| 9 |
±------------+

5. mysql内置的功能

1.1 连接数据库
-u
-p
-S
-h
-P
-e 免交互执行sql语句
< 恢复数据
例子：
1.mysql -uroot -p -S /tmp/mysql.sock
2.mysql -uroot -p -h 10.0.0.51 -P3306
3.mysql -uroot -p -e “show databases;”
Enter password:
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4.mysql -uroot -p123 < /root/world.sql
1.2内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit;exit;ctrl+d 退出mysql
\G 将数据竖起来显示
source 恢复备份数据（进入mysql） mysql> /root/world.sql

6. SQL基础应用
6.1 SQL介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准（SQL_MODE）
6.2 SQL常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言

7. SQL引入-数据库的逻辑结构
库
库名字
库属性：字符集，排序规则
表 表名
表属性：存储引擎类型，字符集，排序规则
列明
列属性：数据类型，约束，其他属性
数据行

8.字符集（charset）
相当于MYSQL的密码本（编码表）

mysql> show charset;
utf8 :3个字节
utf8mb4(建议)：4个字节，支持emoji

排序规则：colltion
mysql> show colltion;

对于英文字符串的，大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音，日文)

9.数据类型介绍
9.1数字
tinyint
int
浮点数
9.2字符串
char(100)最多255，定长字符串类型，不管字符串长度多长，都立即分配100个字符长度的存储空间，未占满的空间使用"空格"填充
varchar(100)最多65535-2，会变长字符串类型，每次存储数据之前，都先判断一下长度，按需分配此盘空间
会单独申请一个字符长度的空间存储字符的总长度，比如长度为20个它就会多一个存21（少于255，如果超过255，会占用两个存储空间来存字符长度）

如何选择这两个数据类型？
1.少于255个字符串长度。定长的列值，选择char
2.多余255字符长度，变长的字符串，可以选择varchar

enum 枚举数据类型
address enum(‘sz’,‘sh’,‘bj’…)有固定索引值，查找效率高
1 2 3

9.3 时间
datetime
范围从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
timestamp
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999

10.DDL的应用

10.1 DDL语句库的定义
—创建数据库
create database zabbix charest utf8mb4 collate utf8mb4_bin;
—查看库情况
show databases;
show create database zabbix;
—删除库（不代表生产操作）
drop database oldguo;
— 修改数据库字符集
— 注意：一定是从小往大了改，比如utf8—>utf8mb4
— 目标字符集一定是源字符集的严格超级
create database olfguo;
show create database olfguo;
alter database oldguo charest utf8mb4;

10.2 关于库定义规范
—1.库名使用小写字符
—2.库名不能以数字开头
—3.不能是数据库内部的关键字。use 123;（在目录下创建并不是在数据库里创建，用是加上``才能用）
—4.必须设置字符集

10.3 DDL表定义
— 建表
表名，列名，列属性，表属性
— 列属性
primary key:主键约束，表中只能有一个，非空且唯一
not null :非空约束，不允许空值
unique key :唯一键约束，不允许重复值
default :一般配合 not null 一起使用
unsigned :无符号，一般是配合数字列，非负数
comment :注释
auto_increment:自增长的列

create table stu(
id int primary key not null auto_increment comment ‘学号’,
sname varchar(255) not null comment ‘姓名’;
age tinyint unsigned not null default 0 comment ‘年龄’;
gender enum(‘m’,‘f’,‘n’)not null default ‘n’ comment ‘性别’,
intime datetime not null default now() comment ‘入学时间’
)engine innodb charset utf8mb4;

— 建表规范
----1. 表名小写字母，不能数字开头
----2. 不能是保留字符，使用和业务有关的表名
----3. 选择合适的数据类型及长度
----4. 每个列设置not null + default 对于数据默认0填充，对于字符使用有效字符
----5. 每个列设置注释
----6. 表必须设置存储引擎和字符集
----7. 主键列尽量是无关列数字列，最好是自增长
----8. enum类型不要保存数字，只能是字符串

— 查询建表信息
show tables;
show create table stu;
---- 创建一个表结构一样的表
create table test like stu;

---- 删表(不代表生产操作)
drop table test;

— 修改
在线ddl会锁表8.0以前
原始方法 :先 copy copy不会锁表，然后在copy的表操作，最后改名rename
用一种工具：pt-osc：percona-toolkits Online schema shange 用这个在线ddl会减少锁表时间
desc stu;看所有的列也就是看表
— 在stu表中添加qq列
alter table stu add qq varchar(20) not null unique comment ‘qq号’;
— 在sname后加微信列
alter table stu add wechat varchar(64) not null unique comment ‘微信号’ after sanme;
— 在id列前加一个新列num
alter table stu add num int not null comment ‘数字’ first;
— 把刚在添加的列都删掉(危险)
alter table stu drop num;

— 修改sname数据类型的属性
desc stu;
alter table stu modify sname varchar(64) not null comment ‘姓名’;

— 将sgender改为sex 数据类型改为char类型
alter table stu change gender sex char(4) not null comment ‘性别’;
10.4 DCL
grant
revoke

10.5 DML
– insert 录入数据
— 最偷懒
insert stu values(1,‘zs’,18,‘m’,now());
— 最规范
insert into stu(id,sanme,age,sex,intime) #mysql可以省略into
values (2,‘ls’,19,‘f’,now());
— 针对性的录入数据
insert into stu(sanme,age,sex)
values (‘w5’,11,‘m’);
— 一次性录入多行
insert into stu(sname,age,sex)
values (‘aa’,11,‘m’),(‘bb’,12,‘f’),(‘cc’,13,‘m’);