mysql数据库笔记

官网：http://dev.mysql.com/downloads/mysql

1.什么是数据库?
　　存储数据的仓库
　　DB 数据库 DBMS 数据库管理系统 DBS 数据库系统 即DB+DBMS

连接数据库的方式

命令行
工具软件:提供图形界面对数据做管理

2.搭建数据库服务器?
3.连接方式 使用规则 基本操作 ?
4.MySQL数据类型 字符类型 数据类型 日期时间 枚举 ?
5.数据库错误日志文件路径(初始密码存储位置)

主配置文件 /etc/my.cnf
传输协议 tcp
数据库目录 /var/lib/mysql
stuinfo.frm 存放表头信息 stuinfo.ibd 存放数据信息
floa单精度 0–2^32-1 double双精度 0–2^64-1

MYSQL默认日志存储路径

/var/log/mysqld.log

新安装密码存放路径

   grep “password”   /var/log/mysqld.log

修改登录密码

    alter user root@”localhost” identified by “密码”;
    mysqladmin -uroot  -p密码  password “新密码”

修改密码规则

show variables like 'validate_password%';
validate_password_length=6
validate_password_policy=0

数据库的增删改查

drop  database  库名;   //删除库
drop  table  库.表;         //删除表
delete  from 库.表;     //删除表所有记录
delete  from 库.表   where   条件;    //删除表表符合条件的内容

create   database/table  库/库.表;       //创建新的库/表
insert   into  库.表[(指定插入的字段)] values();   //插入字段值   

update  库.表  set  字段=值   [where]  条件;    //更新字段内容,可加条件

desc  查看表结构  show 查看       select 

常见的信息种类

　　数值型

　　字符型

　　枚举型

　　日期时间型

数值类型

 浮点型

日期时间类型

datetime / timestamp    格式: yyyymmddhhmmss
当未给timestamp字段赋值时,自动以当前系统时间赋值,而datetime值为null
date   yyyymmdd
year yyyy
01--69视为 2001-2069    70--99视为 1970--1999
time  HH:MM:SS

时间函数

枚举类型

enum   格式: 字段名   enum(值1,值2...)  仅能选一个值,并且必须在列表选择
set   格式:字段名 (值1,值2...)  选择一个或者多个,字段值必须在列表里选择

约束条件

 create table t4(name char(10) not null,homeaddr char(30) not null default "");
 mysql> create  table t2(class char(9),name char(10) not null default "",age tinyint not null default "19",likes set("a","b","c","d") default “a,b”);

 修改表结构

Mysql>alter table  库名.表名  执行动作

add     添加字段
modify  修改字段类型
change  修改字段名
drop    删除字段
rename  修改表名

添加字段

  mysql> alter table t1 add dirke enum("weater","kele") first;  //添加字段到首行
  mysql> alter table t1 add play set("sleep","read","run") not null  default "sleep" after dirke;   //添加字段到指定字段下,如不指定默认为最底行.
+-------+---------------------------+------+-----+----------+-------+
| Field | Type                      | Null | Key | Default  | Extra |
+-------+---------------------------+------+-----+----------+-------+
| dirke | enum('weater','kele')      | YES  |     | NULL     |       |
| play  | set('sleep','read','run')    | NO   |     | sleep    |       |
| name | char(10)                  | NO   |     | NULL     |       |
| age   | tinyint(3) unsigned       | YES  |     | 19       |       |
| class  | char(7)                   | NO  |     | nsd1907  |       |
| pay    | float(7,2)                | YES  |     | 28000.00 |       |
+-------+---------------------------+------+-----+----------+-------+
mysql>insert into t1 values("weater","sleep,read","tom","18","nsd1908","30000.00");
修改字段名:
mysql> alter table t1 change name xingming varchar(20) not null default "yy";
        change可以修改字段名称,也可以修改字段类型.
删除字段:
 mysql> alter  table  t1  drop party,drop your_chart;
修改表名 eg:
 mysql> alter table t2 rename wb;  
 mysql> alter table  t1  add qq  char(11) not null,add sina varchar(20), modify xuexiao varchar(30) not null default "CN",change birthday shengri char(8),drop qq;   //综合

MySQL 键值Index 索引 btree 使用二叉树木算法一个表中可以有多个Index字段,字段值允许重复,且可以赋null值,通常把作为查询条件的字段设置为index字段,index字段的标志是 MUmysql> create table t1 (name char(9) not null default "xbb",age tinyint unsigned,pay float(5,1),index(name),index(age)); //建表时添加index字段

mysql> create  index  索引名  on a(字段名);    //在已有表里添加index字段
mysql> show index from  a\G;       //查看索引信息
mysql> drop index ab on a;          //删除索引

在已有表里添加主键
mysql>alter table 表名 add primary key(字段名);
复合主键
create table t3(name char(10),class char(10),pay enum("yes","no"),primary key(name,class,pay));    //建表时创建复合主键(多个字段作为主键)
mysql>insertintot3values("bob","nsd1907",yes),("bob","nsd1908",yes),("bob","nsd1907",no);

mysql> create  table t4 (id int primary key auto_increment,name char(10),age tinyint unsigned,class char(7) default "nsd1907");(每次增加表记录,主键值自增1,在不赋予指定值得情况下,字段值不允许重复,且不允许赋空值null(若设置 auto_increment,则若输入null时,自增长1)).
主键通常与 auto_increment 连用,自增长依赖主键.

删除主键:
mysql> alter table t4 modify id int not null; 移除主键前,如果有自增属性,必须先去掉
mysql> alter table t4 drop primary key;        取消主键
eg:
mysql> insert into t4 values(null,"yy",28,"nsd1917");

Foreign key 外键
功能:插入记录时,字段值在另一个表字段值范围内选择.
使用规则:表存储引擎必须是innodb,字段类型要一致,被参照字段必须时索引类型的一种(primary key)
mysql> create table yg(yg_id int primary key auto_increment,name char(9))engine=innodb;
mysql> insert   into yg(name) values("wb"),("bb"),("yy"),("xbb");
mysql> create table 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> delete from gz;
mysql> alter  table gz add primary key(gz_id);

删除外键
 mysql> show create table gz;
 mysql> alter table gz drop foreign key gz_ibfk_1;
 mysql> show create table gz;

数据的导入导出

数据库导入导出默认检索目录 /var/lib/mysql-files/
自定义检索目录
mkdir /myload 目录必须真实存在,且用户所有者为mysql用户;

 vim /etc/my.cnf
 [mysqld]
 secure_file_priv=”/myload”   指定检索目录
 systemctl  restart  mysqld   重新加载配置文件

Mysql在登录状态下使用shell脚本 前面要使用 system命令.

数据导入的步骤?

　　1.把系统文件拷贝到检索目录下;

　　 mysql>system  cp /etc/passwd   /myload/

　　2.创建存储数据的库和表

  Mysqld>create table user(name char(50),passwd char(1),uid int,gid int,comment char(150),homedir char(150),shell char(150));

　　3.导入数据

mysql> load data infile “/myload/passwd” into table user fields terminated by “:” lines terminated by “\n”;
mysql> alter table user add id int primary key auto_increment first; 添加id字段,添加主键,自增长.

　　4.查看数据

desc user;
select * from user\G;

数据的导出

  mysql> select name,uid,shell,id from user where homeaddr="/root"into outfile "/myload/user.txt" fields terminated by "::" lines terminated by "\n";  //指定分隔符和换行符

查询表记录

  mysql> select name,passwd from user where 10>id and id>5;
         delete from yg where yg_id=3;

更新表记录(删除差不多)

  mysql> update t1 set play=run where xingming="wb";

匹配条件
数值比较,字段必须是数值类型

 字符比较,字段必须是字符类型

逻辑匹配,多个判断条件时使用

范围匹配/去重显示,匹配范围内的任意一个值即可

高级匹配条件
模糊查询
用法 --where 字段名 like “通配符” “_” 匹配一个字符 “%”匹配0-n个字符

正则表达式
用法 --where 字段名 regexp ‘正则表达式’ //正则查询必须加regexp选项
^以什么开头 $以什么结尾 . 匹配所有 * 匹配前面字符任意次数 [] 匹配选项任意一个即可 | 或者

四则运算
字段必须是数值类型

select 数值字段(符号)数值字段 from 表 [条件]

聚集函数,MySQL内置数据统计函数

select 函数(字段名) from 表 [条件]

查询结果排序

　　SQL查询 order by 字段名(通常是数值类型字段) [asc|desc];
　　asc 升序排序 默认为asc
　　desc 降序排序

查询结果分组

　　SQL查询 group by 字段名; //跟去重查询区别不一样,查询结果分组时执行查询后的结果.

 数据备份

用户授权:在数据库服务器上添加新的连接用户;
撤销授权:删除添加的用户对数据的访问权限;
删除用户:把新添加的用户删除;
授权库: mysql库(系统默认存在)----------> 保存新添加的用户信息和权限信息;

数据备份策略有哪几种?

　　完全备份 -----------------> 备份所有数据
　　增量备份 ------------------> 备份上次备份后,所有新产生的数据
　　差异备份 ------------------> 备份完全备份后,所有新产生的数据
　　Binlog日志也叫二进制日志,记录除查询之外的\所有sql命令,可用于数据备份与恢复.

数据的备份方法有哪几种？

　　冷备份(也称作物理备份): cp tar

　　逻辑备份

　　增量备份

冷备份(也称作物理备份): cp tar

cp  -r   /var/lib/mysql/   备份目录/文件名
Tar  -zcvf /root/mysql.tar.gz  /var/lib/mysql/*

恢复操作

cp  -r  备份/文件名   /var/lib/mysql/
Tar  -zxvf   /root/mysql.tar.gz  /var/lib/mysql/
Chown  -R  /var/lib/mysql/

逻辑备份

mysqldump -uroot  -p密码 (-A(所有),-B库名1 库名2,库名,库名 表名) > 目录/文件名

恢复数据

mysql -uroot -p密码  [库名]  < 目录/文件名 ------------->若要恢复某个库数据或表的数据,首先得新建空库,再执行恢复.
一定要验证用户对目录的权限.

增量备份

　　启用binlog日志来备份和恢复数据
　　Binlog日志也称为二进制日志;
　　binlog日志记录除查询命令以外的sql命令,属于mysql服务日志的一种;

配置mysql主从同步的必要条件：

　　启用binlog日志文件:

log_bin=/mysql/zl.log(目录要存在,且对mysql有权限)
server_id=50(1-255)

 查看增量日志信息 默认日志文件大小为1G,若文件内存不足,则自动创建新的日志文件,也可以手动创建新的日志文件,则数据库自动使用数值最大的日志文件.

show master status

手动创建新的日志文件:

　　1.systemctl restart mysqld
　　2.mysql>flush logs;
　　/]# mysql -uroot -p密码 -e “flush log”; 

3.完全备份的时候加上这个选项,自动生成日志文件

mysqldump --flush-logs 
/]# mysqldump --flush-logs -uroot -phahaha -A > /myload/wq.sql 

2.删除指定编号之前的binlog日志文件

mysql> purge master logs to “zl.000014”; (删除指定表号之前的日志文件)
mysql> reset master; 删除所有日志,重建新日志 

使用binlog日志恢复数据

1.在主配置文件中写入 binlog_format=“mixed” ,修改记录模式为混合模式,在配置文件内添加字段:修改记录方式为 “混合模式”,混合模式可以显示易读的偏移量与时间,用来恢复数据.
2.拷贝日志文件给用来恢复数据的服务器主机;

Mysqlbinlog(–start(stop)-datetime=”yyyy-mm-ddhh:mm:ss”,–start(stop)-position=数字(起始or结束偏移量)) | mysql -uroot -p密码 ------->恢复指定范围内数据3.mysqlbinlog 日志文件名称 | mysql -uroot -p密码 恢复上次备份后所有数据

恢复指定范围内的数据:

　　要是想恢复到最后一条操作命令的数据只需要指定起始的”时间”或”偏移量”,系统默认恢复到最后一条命令.若只指定结束的时间或者偏移量,则默认恢复全部.
　　实验使用另外一台服务器恢复数据,必须提前创建好要恢复的库;
　　读取日志文件的指定命令范围恢复数据;

日志的记录模式有哪几种?

　　1.statement 报表模式
　　2.row 行模式
　　3.mixed 混合模式

使用percona软件完全备份数据

备份过程中不锁表,innobackupex组件以per\脚本封装xtrabaclup.
需要软件: percona-xtrabackup, libev
完全备份:

innobackupex --user 用户名 --password 密码 备份目录名 [–notimestamp]

完全恢复

systemctl stop mysqld
rm -rf /var/lib/mysql/*
Innobackupex --apply-log 目录名 //准备恢复数据
]# cat xtrabackup_checkpoints (可选操作查看)
backup_type = full-backuped (当完成准备恢复数据命令后,ful-backuped 会变为full-prepared)
Innobackupex --copyback 目录名 //恢复数据
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld

 

恢复单张表数据:

模拟误删除一张表的数据,使用所有数据的备份来恢复单张表的数据.

sql>alter table db5.a discard tablespace; //删除表空间
]#innobackupex --apply-log --export 备份目录 //导出表信息
]#cp 目录/库.表名.{ibd,cfg,exp} 数据库目录/库名 //拷贝表信息
]#chown -R mysql:mysql /var/lib/mysql/ //授权
sql>alter table 库.表 import tablespace; //导入表空间
Sql> select ***
]#rm -rf /var/lib/mysql/库名/表.{cfg,exp}

 

增量备份与增量恢复:
增量备份

]#innobackupex --user root --password hahaha /fullback --no-timestamp //所有备份
]#innobackupex --user root --password hahaha --incremental /new1dir --incremental-basedir=/fullback --no-timestamp //增量备份
]#innobackupex --user root --password hahaha --incremental /new2dir --incremental-basedir=/new1dir --no-timestamp //增量备份
…

增量恢复数据

systemctl stop mysqld
rm -rf /var/lib/mysql/*
innobackupex --apply-log --redo-only /fullback/
Innobackupex --apply-log --redo-only /fullback/ --incremental-dir=/new1dir
cat xtrabackup_checkpoints
Innobackupex --apply-log --redo-only /fullback/ --incremental-dir=/new2dir
innobackupex --copy-back /fullback/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld