Mysql数据库基础

数据库的特点：具有高效、可靠、完整、自同步等特性。

主流数据库：Oracle、MySQL、SQL Server、Nosql、gaussDB

mysql服务端口号3306、默认数据库目录/var/lib/mysql、进程名mysqld、主配置文件/etc/my.cnf、错误日志文件/var/log/mysqld.log

mysql常用数据类型：整型(ini或tinyint)、字符类型(char或varchar)、日期时间类型(datetime或timestamp)、浮点型(float或double)

数据语言分类：数据查询语言、数据定义语言、数据操纵语言、数据控制语言。

show databases;    #显示服务器上已有的库
select user();     #显示当前登录的用户名和客户端地址
select version();  #显示数据库服务软件的版本号
slect database();  #显示当前所在库 类似pwd
use 库名;           #进入该库名的库 类似cd
show tables;       #显示所在库下已有的表
desc 表名;          #显示表结构

create database  库名; #创建库  
drop database  库名;  #删除库
create table 库名.表名(表头名1 数据类型,表头名2 数据类型);#创建表 
drop table 库名.表名(表头名1 数据类型,表头名2 数据类型);#删除表
alter table 库名.表名 操作命令[add|drop|modify|change|rename]; #修改表
create table 库名.表名 select * from  库名.表名; #复制表

基本增删改查命令：增insert into、删delete、改update、查select

常用基本命令使用：

1、定义别名使用 as 或空格

2、数据拼接 concat()

3、去重显示 distinct 字段名列表

4、数值比较符号 = != > >= < <=

5、字符比较 = 相等比较 != 不相等比较

6、使用关键字 null 或 NULL 表示没有数据

7、范围匹配条件 in 在…里 not in 不在…里 between 数字1 and 数字2 在…之间

8、模糊匹配条件 where 字段名 like ‘表达式’;

9、正则匹配使用正则表达式做判断条件:

select 字段名列表 from 库名.表名 where 字段名 regexp ‘正则表达式’;

字符函数的使用：处理字符或字符类型表头

LENGTH(str)	        	返字符串长度，以字节为单位
CHAR_LENGTH(str)		返回字符串长度，以字符为单位
UPPER(str)和UCASE(str)  将字符串中的字母全部转换成大写
LOWER(str)和LCASE(str)  将str中的字母全部转换成小写
SUBSTR(s, start,end) 	从s的start位置开始取出到end长度的子串
INSTR(str,str1)		    返回str1参数，在str参数内的位置
TRIM(s)			        返回字符串s删除了两边空格之后的字符串

数学函数: 处理数字或数值类型表头

ABS(x)               返回x的绝对值
PI()                 返回圆周率π，默认显示6位小数
MOD(x,y)	         返回x被y除后的余数 
CEIL(x)、CEILING(x)	返回不小于x的最小整数 (x 是小数)
FLOOR(x）			返回不大于x的最大整数 (x 是小数)
ROUND(x)		     返回最接近于x的整数，即对x进行四舍五入 (x 是小数)
ROUND(x,y) 		     返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位 (x 是小数)

聚集函数：数据统计命令，输出的值只有1个

avg(表头名)			//计算平均值
sum(表头名) 			//求和
min(表头名) 			//获取最小值
max(表头名) 			//获取最大值
count(表头名) 		    //统计表头值个数

日期时间函数 ： mysql服务自带命令获取系统日期和时间

函数	说明	函数	说明
curtime()	获取时间	hour()	获取小时
curdate()	获取日期	minute()	获取分钟
now()	获取日期和时间	second()	获取秒
year()	获取年	quarter()	获取一年中第几季度
month()	获取月	monthname()	获取月份名称
day()/week()	获取日/一年中的第几周	dayname()	获取日期对应的星期名
date()/weekday()	获取日期/一周中的周几	dayofyear()	获取一年中第几天
time()	获取时间	dayofmonth()	获取一月中第几天

数学计算: 加减乘除取余 + - * / %

if语句:

1 2	if(条件,v1,v2) 如果条件是TRUE则返回v1，否则返回v2 ifnull(v1,v2) 如果v1不为NULL，则返回v1，否则返回v2

case语句: 如果字段名等于某个值，则返回对应位置then后面的结果，如果与所有值都不相等，则返回else后面的结果。

CASE 表头名              
WHEN 值1 THEN 结果 
WHEN 值2 THEN 结果  
WHEN 值3 THEN 结果 
ELSE 结果  
END

CASE              
WHEN  判断条件 THEN 结果 
WHEN  判断条件  THEN 结果  
WHEN  判断条件 THEN 结果 
ELSE 结果  
END

查询结果处理

分组: Select查询命令 group by 表头名；
排序: Select 查询命令 order by 表头名 ; #升序 #加desc为降序
过滤: select 字段名列表 from 库.表 having 筛选条件;
分页: select 字段名列表 from 库.表 limit 数字1,数字2；

连接查询

内连接

1 2	SELECT 字段列表 FROM 表1 别名 INNER JOIN 表2 别名 ON 连接条件 INNER JOIN 表3 别名 ON 连接条件 ;

外连接
- 左外连接 LEFT JOIN
- 右外连接 RIGHT JOIN

子查询: select查询命令里包含select查询命令

Mysql数据类型

其他字符类型：用来存储视频、音频、图片、较大的文本

类型	名称	范围
tinytext	短文本	0-255字节
tinyblob	二进制形式短文本字	0-255字节
text	长文本数据	0-65535字节
blob	二进制形式的长文本	0-65535字节
mediumblob	二进制形式的中等长度文本	0-16777215字节
mediumtext	中等长度文本数据	0-16777215字节
longblob	二进制形式极大文本	0-4284867295字节
longtext	极大文本	0-4284867295字节

数值类型：表头存储数字，分为整数类型和浮点类型 2种

整数类型

类型	名称	有符号范围	无符号范围
tinyint	微小整数	-128~127	0 ~ 255
smallint	小整数	-32768~32767	0 ~ 65535
mediumint	中整型	-223 ~ 223-1	0 ~ 224-1
int	大整型	-231 ~ 231-1	0 ~ 232-1
bigint	极大整型	-263 ~ 263-1	0 ~ 264-1
unsigned	使用无符号存储范围

浮点类型

类型	名称	范围
float	单精度	8位精度(4字节)
double	双精度	16位精度(8字节)

枚举类型表头值必须在类型规定的范围内选择

单选 enum(值1，值2，值3 ….) 只能选项范围内的任意一个

多选 set(值1，值2，值3 ….) 选项范围内的一个或多个
日期时间类型 存储日期时间格式的数据

类型	名称	范围	赋值格式
year	年	1901~2155	例如 2022
date	日期	0001-01-01 9999-12-31	例如 20220318
time	时间	00:00:00 23:59:59	例如 091858
datetime	日期时间	1000-01-01 00:00:00~9999-12-31 23:59:59	例如 20220819153819
timestamp	日期时间	1970-01-01 00:00:00 2038-01-19 00:00:00	例如 20220819153819

数据批量处理

数据导入：把系统文件的内容存储到数据库服务的表里文件的内容要规律

1
2
3

load   data  infile   "/检索目录/文件名"   into  table    库名.表名   
fields  terminated by  "文件中列的间隔符号"   
lines   terminated by   "\n"  ;

数据导出:

1	select 字段名列表 from 库.表 where 条件 into outfile "/检索目录/文件名";

1 2	select 字段名列表 from 库.表 where 条件 into outfile "/检索命令名/文件名" fields terminated by "符号";

1	select 字段名列表 from 库.表 where 条件 into outfile "/检索命令名/文件名" fields terminated by "符号" lines terminated by "符号";

字段约束

字段约束： 设置在表头上 ，用来限制字段赋值；每种约束都有各自的功能。

NOT NULL ：非空，用于保证表头的值不能为空。

DEFAULT：默认值，不给表头赋值时，保证表头有值。

UNIQUE：唯一索引，用于保证表头的值具有唯一性，可以为空。

主键 (primary key)： 表头值不允许重复，且不允许赋NULL值

1	create table 库.表（表头名数据类型 primary key , 表头名数据类型 , ..... ）;

1	create table 库.表（字段名类型 , 字段名类型 , primary key（字段名））;

1	alter table 库.表 drop primary key ;

1	alter table 库.表 add primary key(表头名);

主键与auto_increment连用:可以实现自增长

复合主键的约束方式: 多条记录主键的值不允许同时相同

外键: 用于主键与其他子表建立联系，数据同步。

create table   库.表(
表头列表 , 
foreign key(表头名)        #指定外键
references 库.表(表头名)   #指定参考的表头名
on update  cascade         #同步更新
on  delete  cascade        #同步删除
)engine=innodb;

1	alter table 库.表 drop foreign key 外键名;

1 2	alter table 库.表 add foreign key(表头名) references 库.表(表头名) on update cascade on delete cascade;

数据备份与恢复

完全备份

1 2	mysqldump -uroot -p密码库名 > /目录名/备份文件名.sql #备份 mysql -uroot -p密码 [库名] < /目录名/备份文件名.sql #恢复

库名的表示方式：
	库名  表名                  #备份一张的所有数据	
	库名  表名1   表名2          #备份多张表的所有数据
	-B  库名                    #备份1个库的所数据
	-B  库名1     库名2         #备份多个库的所数据
	-A  或   --all-databases   #备份服务器的所有数据

mysqldump的备份缺点：

1、mysqldump 在备份数据和恢复数据的时候会锁表

2、使用 mysqldump备份命令生成备份文件恢复数据，只能把数据恢复备份时刻的数据。

增量备份

使用第三方软件percona提供的备份命令innobackupex 对数据做备份和恢复

特点:在线热备不锁表适合生产环境下备份业务。

软件包：libev-4.15-1.el6.rf.x86_64.rpm、percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

1	innobackupex -uroot -p密码 /备份目录名 --no-timestamp #首次完全备份命令

1	innobackupex -uroot -p密码 --incremental /目录名 --incremental-basedir=/目录名 --no-timestamp #增量备份命令

1
2
3

innobackupex   --apply-log   --redo-only   /首次备份目录名    #准备恢复数据
innobackupex   --apply-log   --redo-only   /首次备份目录名   --incremental-dir=/目录名                                           #合并数据 （合并的顺序要与增量备份的顺序一致） 
innobackupex   --copy-back /完全备份目录   #拷贝数据

实时备份 (启用mysql服务的binlog日志实现)

binlog日志介绍：
也叫二进制日志，是MySQL服务日志文件的一种，记录在数据库服务器上执行的除查询之外的sql命令，启用日志能够达到对数据做备份的目的，搭建MySQL主从同步存储结构的必要条件，默认MySQL服务没有启用binlog日志。

启用binlog日志：在/etc/my.cnf中[mysqld]下添加server_id=50和log_bin(此处可定义文件目录)

show  master status ;   #查看binlog日志信息
flush logs;   #创建新日志文件
show binary  logs; #查看数据库服务器当前已有全部 binlog日志文件
purge master logs to "db50.000004"; #删除编号之前的所有日志文件
reset master ; #删除当前所有的日志文件重新创建新日志文件和索引文件
[root@host ~]# mysqlbinlog   /mylog/db50.000001 #查看日志文件内容
show binlog events in "db50.000001" ; #查看日志文件内容
[root@host ~]# mysqlbinlog   /目录名/日志文件名   |   mysql  -uroot   -p密码 #恢复数据

Linux

Mysql数据库基础

Mysql数据类型

数据批量处理

字段约束

数据备份与恢复

CDN概念基本介绍

raid工具