Linux的数据库阶段

数据库知识
数据库第一天 4
两台服务器4.50与4.51 4
常见软件: 4
Mysql数据类型: 5
Mysql管理环境 7
常用的SQL命令分类 7
表管理命令: 8
记录管理 8
枚举类型 11
时间函数的应用: 12
数据库第二天 13
约束条件: 13
修改表结构: 15
更改表字段类型 16
更改总表名 17
键值: 17
Primary key 复合主键: 20
删除主键 22
数据库第三天 24
搜索路径 24
数据库导入 25
数据的导出 26
管理表记录 27
匹配条件 28
高级匹配条件:(适用于 select/update/delete) 30
四则运算:+ - * / %取余 ()提高优先级 31
聚集函数: 32
限制查询结果显示行数: 33
数据库第四天 35
Grant授权: 35
授权库: 37
Root密码: 38
数据的备份: 39
完全备份: 40
binlog日志 41
差异备份: 43
分析日志: 44
数据库第五天 45
Mysql备份工具 45
1 安装percona软件 45
增量恢复: 46
恢复数据 47
删除单张表: 48
数据库进阶一天 50
主从同步: 50
4 主从同步扩展 52
配置主主结构：（ 把数据库服务器192.168.4.56 和 53
数据库进阶第二天 55
数据读写分离: 55
Maxscale服务: 55
部署maxscale服务 57
配置多实例: 58
数据库进阶第三天 61
分库分表: 61
**重要配置文件说明: 63
创建连接用户: 63
数据库进阶第四天 65
部署MHA集群 65
MHA集群环境 66
管理集群命令: 67
创建故障切换脚本 68
启动管理服务 69
数据库进阶第五天 70
MHA 70
PXC集群 70
管理员登录查看状态: 72
列出可用的存储引擎: 73
Mysql锁机制 74
事务特征(ACID): 74
数据库NOSQL第一天 76
RDBMS软件: 76
部署Redis服务: 76
常用命令: 77
优化设置: 79
连接密码: 80
部署LNMP+Redis: 80
配置php支持redis 81
测试配置 82
数据库NOSQL第二天 84
Redis-trib脚本用法: 84
创建集群: 84
管理服务器设备创建集群 86
查看集群信息: 86
访问集群 87
4. 添加服务器 87
在管理主机,查看集群新消息 89
5. 添加slave角色主机到集群里 90
在管理主机,添加slave角色主机 91
移除服务器 91
数据库NOSQL第三天 93
配置主从复制: 93
配置master 93
配置哨兵服务: 94
RDB: 94
AOF 95
优化配置: 96
数据类型分类 97

数据库第一天
准备环境
两台服务器4.50与4.51
[stud@room9mysql]$scp mysql-5.7.17.tar [email protected]:/root/
mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-client-5.7.17-1.el7.x86_64.rpm
[root@1 ~]# tar -xf mysql-5.7.17.tar //解包
[root@1 ~]# yum -y install mysql-community-.rpm
//安装数据库相应软件包rdbms软件,部署redis服务,常用命令,优化设置,连接密码,部署LNMP+Redis
[root@1 ~]# systemctl start mysqld
[root@1 ~]# systemctl enable mysqld
[root@1 ~]# systemctl status mysqld
[root@1 ~]# netstat -anput | grep 3306
tcp6 0 0 :::3306 ::: LISTEN 23419/mysqld
常见软件:
关系型:oracle mysql sql server db2 开源2跨平台
非关系型:redis memcache mongodb
数据库 默认端口:3306
主配置文件: /etc/my.cnf
错误日志文件: /var/log/mysqld.log
数据库目录: /var/lib/mysql
进程名:mysqld
传输协议:TCP协议
进程所有者:mysql 进程所有组:mysql
数据库信息刷新:flush privileges;
数据库密码默认长度为8 最短长度4
切换概要库:use informarion_schema
切换授权库:use mysql
切换性能结构库:use performancr_schema
切换系统元数据库:use sys;
Mysql数据类型:
定长char
变长varchar
整数型
浮点型
日期时间类型
枚举类型
[root@1 ~]# grep password /var/log/mysqld.log
2019-10-08T02:30:49.705931Z 1 [Note] A temporary password is generated for root@localhost: W1Cjw1uhvo=1
[root@1 ~]# mysql -hlocalhost -uroot -p’W1Cjw1uhvo=1’
//生成随机密码,进入mysql
mysql> alter user root@“localhost” identified by ‘123qqq…A’;
mysql> show databases;
//重新设置数据库密码
mysql> show variables like “%password%”; //查看密码变量
mysql> set global validate_password_policy=0;
//修改密码策略
mysql> set global validate_password_length=6;
//修改密码长度
mysql> alter user root@localhost identified by ‘123456’;
//直接命令行生效非永久
[root@1 ~]# vim /etc/my.cnf 永久生效(主配文件)
[mysqld]
validate_password_policy=0
validate_password_length=6
[root@1 ~]# systemctl restart mysqld
[root@1 ~]# mysql -hlocalhost -uroot -p123456
mysql> use mysql; //使用mysql库
mysql> show tables; //查看mysql表
Mysql管理环境
SQL命令不区分字母大小写;
每条SQL以;结束
默认命令不支持TAB自动补齐.
/c 终止sql命令;
常用的SQL命令分类
1.DDl 数据定义语言 如:create alter drop
2.DML数据操作语言 如:insert update delete
3.DCL 数据控制语言 如:grant revoke
4.DTL 数据事物语言 如:commit rollback savepoint
库管理命令:
库名 命名规则
仅可以使用数字.字母.下划线.不能纯数字
区分字母大小写,具有唯一性
不可使用指令关键字.特殊字符
mysql> show databases; //显示所有的库
mysql> select user(); //显示连接用户
mysql> use 库名; //切换库
mysql> select database(); //显示当前所在的库
mysql> create database 库名 //创建新库
mysql> show tables; //显示已有的表
mysql> drop database 库名 //删除库
表管理命令:
Desc 库名.表名;
Drop table 库名.表名;
Mysql>create table 库名.表名(字段名1 类型 (宽度)
字段名2 类型(宽度),…) default charset=utf8; (中文必备)
mysql> create database db1; //创建db1库
mysql>create table db1.studinfo
( name char(15),homeaddr char(20));
//创建表在db1库中两个字符字段表头name与homeaddr
mysql> use db1; //使用db1表
mysql>show tables; //查看db1库的表
mysql> desc db1.studinfo; //查看db1库的表结构
记录管理
Select * from 库名.表名; /查看表记录
Insert into 库名.表名 values(值); /插入表记录
Update 库名.表名 set 字段=值 /修改表记录
Delete From 表名; /删除表记录
mysql>insert into db1.studinfo
values (“bob”,“USA”),(“lucy”,“USA”),(“lilei”,“china”);
//写入内容三个括号代表三行分别为两列
mysql> select * from db1.studinfo; //查看表内容
mysql> update db1.studinfo set homeaddr=“bejing”;
//替换二列homeaddr所有都为 beijing
mysql> select * from db1.studinfo; //查看表改后内容
mysql> delete from db1.studinfo; //删除表中内容
mysql> create table 学生信息表(姓名 char(15) , 性别 char(2) ) default charset=utf8;
mysql> show tables;
mysql> insert into db1.学生信息表 values (“张三”,“男”);
mysql> select * from db1.学生信息表;
mysql> create table db1.t1 (class char(7), name char(15), email varchar(30)); //创建t1表 email为变长类型
mysql> show tables;
mysql> desc t1; //查表结构
mysql>insert into db1.t1
values (“nsd1907”,“bob”,“[email protected]”);
//写入内容class,名字,与变长类型邮箱
mysql> select * from db1.t1 //查看表t1
mysql> drop table 学生信息 //删除学生信息表
mysql> create table t3 (name char(10) , age tinyint unsigned ); //创建t3表 名字 指定长度,年龄微小整数无符号范围
mysql> insert into t3 values (“bob”,-11);
ERROR 1264 (22003): Out of range value for column ‘age’ at row 1 //有unsigned不支持负值
mysql> insert into t3 values (“bob”,11); //写入信息
mysql> insert into t3 values (“bob”,256);
ERROR 1264 (22003): Out of range value for column ‘age’ at row 1 //tinyint超出范围
mysql> insert into t3 values (“bob”,255); //写入信息
mysql> select * from t3; //查看t3 表
浮点型类型
mysql> create table t4 (name char(10) ,pay float(5,2) );
//创建浮点型类型 格式:字段名 类型(总个数,小数位数)
mysql> show tables;
mysql> insert into t4 values (“john”,1000);
ERROR 1264 (22003): Out of range value for column ‘pay’ at row 1 //写入浮点型信息 超出pay float(5,2) 范围
mysql> insert into t4 values (“john”,999); //写入信息
mysql> select * from t4; //查看表信息
mysql> insert into t4 values ( “johnA”, 999.12 ); //写入信息
mysql> select * from t4; //查看表信息
日期时间类型
mysql> use db1
mysql> create table t5( name char(10), csnf year, up_time time, birthday date, party datetime);
//创建t5表,五个表头.
mysql> desc t5; //查看表结构
mysql>insert into t5
values (“bob”,2000,083000,20191120,20190101083000);
//写入五个日期字段
mysql> select * from t5; //查看t5表

枚举类型
字段名 enum(值1,值2,值n) /单选
字段名 set(值1,值2,值n) /多选
mysql> create table t6 ( name char(10),
Sex enum(“boy”,“girl”,“noboynogirl”),
likes set (“eat”,“game”,“piao”,“du”));
//创建枚举表头 enum控制多选一 likes set 多选
mysql> desc t6; //查看t6表结构
mysql> insert into t6
values (“alices”,“noboynogirl”,“game,piao”); //写入表信息
mysql> select * from t6; //查看表信息
时间函数的应用:
mysql>use db1;
mysql> show tables;
mysql> select now(); //显示当前系统时间与日期
mysql> select curtime(); //查看当前系统时间
mysql> select curdate(); //查看当前系统日期
mysql> select year(now()); //查看指定时间年
mysql> select month(now()); //查看指定时间月
mysql> select day(now()); //查看指定时间日
mysql> select date(now()); //查看指定时间日期
mysql> select time(now()); //查看指定时间 时间
mysql>insert into t5
values (“tom”,year(now()),curtime(),curdate(),now());
//写入新内容
mysql> create table t7(name char(10),meetting datetime,party timestamp);
//创建t7表 datetime无值为空timestamp无值应用现默认
mysql> desc t7; //查看t7表结构
mysql> insert into db1.t7 values
-> (“bob”,20191009112545,20191009213025);
//写入表内容
mysql> select *from t7;
//查看表信息
mysql> insert into t7(name,meetting) values(“tom”,20191224204558);
//写入两个字段 metting有值,party默认当前时间
mysql>insert into t7(name,party) values(“jim”,20171224204558);
//写入两个字段 metting无值,party有值
mysql> desc t5;
mysql> insert into t5(name ,csnf) values
-> (“john”,21),
-> (“jerry”,71),(“jack”,2071);
mysql> select * from t5;

数据库第二天
约束条件:
Null 允许为空(默认设置)
Not null 不允许为null(空)
Key 键值类型
Default 设置默认值,没有为null
Extra 额外设置
mysql>use db1;
mysql>create table t8(name char(10) not null,
-> age tinyint unsigned default 19,
-> class char(7) not null default “nsd1907”,
-> pay float(7,2) default 28000);
//创建t8表名字为定长10,不能为空,年龄为微整型,无符号默认值19,班级为定长7不能为空默认值nsd1907,支付为浮点型总个数为7,小数位为2默认值为28000.00
mysql> insert into t8(name)values(“bob”);
//使用默认值
mysql> select * from t8; //查看t8表内容
mysql> insert into t8 values(“tom”
-> ,21,“nsd1908”,28888.88);
//不使用默认值
mysql> insert into t8 values (null,28,null,30088);
ERROR 1048 (23000): Column ‘name’ cannot be null
//name字段不能为空
mysql> insert into t8 values (“jerry”,28,null,30088);
ERROR 1048 (23000): Column ‘class’ cannot be null
//class字段不能为空
mysql>insert into t8
values (“jerry”,28,“nsd1907”,30088);
//非空建立表内容
mysql> select * from t8; //查看t8表内容
mysql> create table t9(
-> name char(10) not null,
-> homeaddr char(30) not null default “”);
mysql> desc t9;
mysql> insert into t9 values (“null”,“null”);
mysql> select *from t9;
mysql> insert into t9(name) values (“tom”);
修改表结构:
添加表字段
Alter table 库名.表名 add 字段名 类型 [约束条件after | first]:
mysql> alter table db1.t1
-> add school char(15) not null default “tarena”;
mysql> desc t1;
mysql> alter table db1.t1
-> add age tinyint unsigned default 19 after name;
mysql> desc t1;
mysql> alter table db1.t1
-> add stu_num int first;
mysql> desc t1;
mysql> select *from t1;
更改表字段类型
Alter table 库名.表名 modify 字段名 类型 [约束条件 after | first];
mysql> delete from db1.t1;
mysql> alter table db1.t1
-> modify name varchar(15) not null default “”;
mysql> alter table db1.t1
-> modify name varchar(15) not null default “” after stu_num;
mysql> desc t1;
改变表字段名字
Alter table change 源字段名 新字段名
mysql> alter table db1.t1 change
->stu_num stu_id int;
mysql> alter table db1.t1 change
-> school xuexiao varchar(30) default “tedu”;
mysql> desc t1;
删除表字段
Alter table 库名.表名 drop 字段名;
mysql> alter table db1.t1 drop email;
mysql> alter table db1.t5 drop csnf;
更改总表名
Alter table 表名 rename 新表明;
mysql> alter table t9 rename school;
mysql> show tables;
批量修改表结构
mysql> alter table db1.t1
-> add qq char(11),
-> add phone char(11),
-> modify class char(9),
-> change name user varchar(15) not null default “”,
-> drop xuexiao;
mysql> desc t1;
键值:
键值类型:
Index 普通索引:
一个表中可以有多个index字段
字段值允许重复,可以赋null值
Index字段标志是MUL
Unique 唯一索引
Fulltext 全文索引
Primary key 主键:
一个表中只能有一个primary key主键.
多个字段作为主键,称为复合主键,必须一起创建.
字段值不允许重复,不允许赋null值.
主键标志PRI
主键通常与auto_increment连用
通常设置表中唯一标识的纪录的字段设置为主键
Foreign key 外键
普通索引:
Create index 索引名 on 表名(字段名); /创
Drop index 索引名 on 表名; /删
Show index from 表名 \G; 查索引
mysql> desc t3; //查看t3表结构
mysql> create index name on t3(name);
//给name设置普通索引MUL
mysql> create index age on t3(age);
//给age设置普通索引MUL
mysql> show index from t3 \G;
//显示设置索引详细信息
mysql> desc t3;
mysql> drop index age on t3; //删除age上的索引
mysql> show index from t3 \G;
↓创建新的数据库
mysql> create database db2;
mysql> create table db2.t1(
-> name char(10),
-> age int,
-> sex enum(“boy”,“girl”),
-> index(name),index(sex));
//建立新t1表设置name与sex为普通索引
mysql> desc db2.t1; //查看t1表结构
2）unique ##唯一索引，不允许有重复值，比如身份证，银行账号
3）fulltext ##全文索引，适合在进行模糊查询的时候使用
Primary key主键:
alter table 表名 字段 primary key(字段名列表);
mysql> create table t8(
-> name char(10) primary key,
-> age int,
-> class char(7));
//建立t8表设置name为主键索引
mysql> desc t8; //查看t8表结构
mysql> create table t9(
-> name char(10),age int,class char(7),primary key(name)); //等同t8表
mysql> insert into t9 values (null,19,“nsd1907”);
ERROR 1048 (23000): Column ‘name’ cannot be null
//验证设置主键索引不得为null值
mysql> insert into t9 values (“bob”,19,“nsd1907”);
//在t9写入表内容
mysql> insert into t9 values (“bob”,20,“nsd1908”);
ERROR 1062 (23000): Duplicate entry ‘bob’ for key ‘PRIMARY’
//验证主键索引name不得重复
Primary key 复合主键:
alter table 表名 字段 primary key(多字段名列表);
mysql> create table pay(
-> name char(10),class char(7),
-> pay enum(“yes”,“no”),
-> primary key(name,class,pay));
//创建pay表同时设置复合主键(name,class与pay)验证不得三段重复,依然可以写入.
mysql> desc pay; //查看pay表结构
mysql> insert into db2.pay values (
-> “bob”,“nsd1907”,“yes”);
mysql> insert into db2.pay values (
-> “bob”,“nsd1907”,“no”);
mysql> insert into db2.pay values (
-> “bob”,“nsd1908”,“yes”);

mysql> select *from pay; //查看pay表内容
mysql> insert into db2.pay
values ( “bob”,“nsd1908”,“yes”);
ERROR 1062 (23000): Duplicate entry ‘bob-nsd1908-yes’ for key ‘PRIMARY’
//验证同时三个字段建立报错
主键与auto_increment连用:
mysql> create table db2.t3(
-> id int primary key auto_increment,
-> name char(10),age tinyint unsigned,
-> class char(7) default “nsd1907”);
mysql> desc t3;
mysql> insert into t3(name,age) values(“bob”,19);
mysql> select *from t3;
mysql> insert into t3(name,age) values(“tom”,16);
mysql> insert into t3(name,age) values(“john”,27);
mysql> select *from t3;
mysql> select *from t3 where id=2;
mysql> insert into t3 values(6,“lili”,18,“nsd1906”);
mysql>insert into t3 values(null,“hanmem”,28,“nsd1906”);
mysql> select *from t3;
删除主键
mysql> alter table 库名.表名 drop primary key;
mysql> alter table t3 modify id int not null;
//改变自增长将其去掉
mysql> alter table t3 drop primary key;
//删除主键
添加主键
在已有表里添加主键
Alter table 表名 add primary key(字段名);
创建复合主键
Alter table 表名 add primary key(字段名列表) ;
mysql> alter table db2.pay add primary key(name,class,pay);
mysql> alter table t1 add stu_num tinyint primary key auto_increment first
外键的使用
Create table 表名( 字段名列表,foreign key(字段名) references 表名(字段名) #指定外键 on update cascade #同步更新 on delete cascade #同步删除 )engine=innodb; #指定存储引擎
;
mysql> create table db2.yg(yg_id int primary key auto_increment,yg_name char(10)
-> )engine=innodb;
mysql> insert into db2.yg(yg_name)
-> values(“bob”),(“bob”),(“tom”),(“lili”);
mysql> select *from db2.yg;
mysql> create table db2.gz(
-> gz_id int ,gz float(7,2),
-> foreign key(gz_id) references yg(yg_id)
-> on update cascade on delete cascade
-> )engine=innodb;
mysql> show create table gz \G;
mysql> insert into gz values (1,25000);
mysql> insert into gz values (2,35000);
mysql> insert into gz values (3,45000);
mysql> insert into gz values (4,55000);
mysql> select *from gz;
mysql> update db2.yg set yg_id=8 where yg_id=1;
//单独更改一个字段单个内容
mysql> delete from yg where yg_id=2;
删除外键