MySQL数据库笔记（二）

数据库基础

1. SQL语言分类

SQL（Structured Query Language 即结构化查询语言），SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。

1 .DDL语句（data definition language），数据库定义语言：数据库、表、视图、索引、存储过程，例如create，drop等。
2. DML语句（data manipulation language），数据库操作语言：插入数据insert、删除数据delete、更新数据update。
3. DQL语句（Data Query Language ），数据库查询语言select
4. DCL语句（Data Control Language），数据库控制语言：例如控制用户的访问权限grant，revoke。

2. 名词解释

1.数据库服务器：运行这数据库应用程序的设备，硬件+系统软件+MySQL软件。

2.数据库(MySQL数据库内默认的库)

information_schema：虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户信息表、列信息、权限信息、字符信息等。

performance_schema：主要存储数据库服务器的性能参数
1).提供进程等待的详细信息，包括锁、互斥变量、文件信息；
2).保存历史的时间汇总信息，为提供MySQL服务器性能做出详细的判断；
3).对于新增和删除监控时间点都非常容易，并可以随意改变mysql服务器的监控周期。

mysql：授权库，主要存储系统用户的权限信息；

sys：主要存储数据库服务器的性能参数
SYS database中，可以获取mem page、io、latch/mutex/lock等各种性能数据，方便做peformance turning 和troubleshooting。比如可以方便获取2个sql发生 lock block，用户占用的资源等信息。

3.表：记录的管理单元

4.记录(行)：信息的载体，字段的管理单元

5.字段(列)：字段名，字段类型，字段约束组成（可选）

6.类型：字符，约束，日期

7.约束：不能为空，自动增长

3. DDL

3.1 DDL-库

create database 数据库名;    //创建数据库

show datebases;      //查看所有数据库

use  数据库名;        //进入的数据库

drop database 数据库名;   //删除数据库

select database();   //调用函数，查询当前在哪个库内

创建数据库要求：

区分大小写

唯一性

不能使用关键字如create，select等

不能单独使用数字和特殊符号

正常的用单词拼音即可

3.2 数据类型

数值型：int整数类型，float浮点数类型

字符串类型：字符系列（char：长度不可变；varchar：长度可以增加），枚举类型（enum 单选），集合类型（set 多选）

时间和日期类型：年，日期，时间，日期和时间

3.3 DDL-表

use 数据库名;

show tables;     //查看库内所有表

desc table;    //查看表结构

create table 表名 (列名  类型);

create table t1 (id  int);   //创建表t1

4. DML

通过SQL语句中的DML语言来实现数据的操作，包括以下：

4.1 INSERT 实现数据的插入

insert into 表名 values (值1,值2);

insert into 表名 (列名,列名) values (值1,值2);  //部分插入
                 
insert into 库名.表名 values (值1,值2);

4.2 DELETE实现数据的删除

delete from 表名 where condition;
            //删除某个表中的数据，condition表示执行的操作
delete from test1 where id=2;
            //删除test1表下，id为2的记录

4.3 UPDATE实现数据库的更新

update 表名 set 列名=值 where condition；

update mysql.user  set authentication_string=password("新密码")  where user="root";
   //将mysql库user表中user=root的authentication_string修改为..
     修改mysql管理员root账户的密码

5. DQL

通过SQL语句中的DQL来实现数据库的select查询操作

5.1 简单查询

select * from 表名;    //查询表内所有信息(必须在含有该表的库内)
select * from 库名.表名;   //查询表内所有信息

select 列1,列2,列3 from 表名;
select name,salary,salary*14 from employees;
                         //四则运算，查询员工salary的年薪

5.2 条件查询

select name,post from employees where post='hr';
      //查询employees表中职位post是hr的name,和post
      
select name,salary from employees where post='hr' and salary>1000;
      //查询hr部门，并且工资大于1000的员工
      
select name,salary from employees where salary=6000 or salary=8000
      //查询所有部门，工资是6000或者是8000的员工

select name,salary from employees where salary between 5000 and 15000;
      //查询工资在5000-15000的员工
      
select name,salary from employees where salary not between 5000 and 15000;
      //查询工资不在5000-15000的员工
      
select name,salary from employees where salary in(4000,5000,6000,9000);
   //查询工资可能是4000,5000,6000,9000的员工

select name,job_description from employees where where job_description is NULL;
   //查询岗位job_description(列名) 是空的员工

select name,job_description from employees where where job_description is not NULL;
   //查询岗位job_description(列名) 不是空的员工
   
select * from employees where name like'a%';
   //查询姓名为a开头的员工   “%”---表示任意多个字符
   
select * from employees where name like'a__';
   //查询姓名为a的员工    “_”---表示1个任意字符，两个表示两个任意字符

5.3 查询排序

select * from 表名 order by 工资的列名 asc;
               //以工资升序(从小到大)排序

select * from 表名 order by 工资的列名 desc;
               //以工资降序(从大到小)排序

select * from employees order by salary desc limint 5;
               //工资最高的前五名  默认初始位置为0

6. DCL

通过SQL语句控制用户的访问权限

权限级别

1.Global level 所有库，所有表的权限。

2.Database level 某个数据库的所有表的权限

3.Table level 库中的某个表的权限

4.Column level 表中的谋个字段的权限

6.1 MySQL用户管理

6.1.1 创建用户

create user user1@'localhost' identified by '密码';

create        创建(关键字)
user          用户(关键字)
user1         用户名称
@             分隔符(关键字)
'localhost'   允许登陆的主机(此处也可以填写'%'，表示可以与任意IP地址登陆但是不能本地登录，也可以填写具体的IP地址)
identified by 身份认证(关键字)
'密码'         用户密码

6.1.2 删除用户

drop user 'user1'@'localhost';

6.1.3 修改用户密码(丢失root密码找回方法)

修改密码
在系统终端mysqladmin -uroot -p'旧密码' password '新密码'
在mysql内:

update mysql.user set authentication_string=password(‘新密码’)  where user=’root’ and host=’localhost’;
               //修改mysql库内的user表中的root用户的密码
flush privileges;  //刷新数据库
--------------------------------
set password=password('新密码');
flush privileges;  //刷新数据库

丢失root密码找回方法
原理是使MySQL启动时，不加载密码文件

vim /etc/my.cnf
skip-grant-tables //跳过密码

重启MySQL服务systemctl restart mysqld
免密mysql -uroot直接登陆MySQL，进入后输入修改密码命令
写改完新密码后，将配置文件内的免密登陆注释，重启MySQL服务
登陆MySQL

mysql -P 3306 -uroot -p'密码' mysql -e 'show databases;' -h [默认为localhost]

-P   MySQL服务器端口 [默认3306端口]
-u   指定用户名      [默认root]
-p   指定登陆密码    [默认为空密码]
-e   接SQL语句

6.2 MySQL权限原理

6.2.1 查询权限

show grants;    //查看自己的权限
show grants for user1@'%';   //查看别人的权限

6.2.2 赋予用户权限

grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];
举例如下：

权限列表

all —所有权限(不包括授权权限)

select,update —查询更新权限

数据库.表名

*.* —所有库下的所有表

web.* —web库下的所有表

web.test —web库下的test表

with_option参数:

grant option --授权权限

grant all on testdb.* to 'user1'@'localhost' grant option
  //给user1用户授权testdb库的所有权限，同时可以授权给其他用户自身拥有的权限

MySQL5.7对未创建用户授权，用户会自动创建，并且拥有权限
MySQL8开始，授权的用户需要先创建出来

6.2.3 回收权限

revoke 权限列表 on 数据库名 from 用户名@'客户端主机';

revoke all on testdb.* from user1@'%';    
//回收所有权，注意如果回收的权限，用户不曾被赋予，则会发生报错

在MySQL5.6之前，需要先revoke all (回收所有的权限) 再 drop user (删除用户)
在MySQL5.7之后，可以直接 drop user (直接删除用户)

7.日志

7.1日志分类

1).错误日志：启动，停止，关闭失败报错。

2).通过查询日志：所有的查询都记下来。

3).二进制日志：实现备份，增量备份。只记录改变数据，除了select都记。

5).slow log：慢查询日志，指导调优，定义某一个查询语句，定义超发时间，通过日志提供调优建议给开发人员。

6).DDL log：定义语句的日志。

7.2 Erro Log(错误日志)

vim /etc/my.cnf 进入主配置文件，观察日志是否启动。
log-error=/var/log/mysqld.log 该字段标记是否启动日志，以及日志位置 ----如果MySQL服务起不来，查看这个日志文件。

7.3 Binary Log(二进制日志)

默认没有开启二进制日志。
启动二进制日志：

`vim /etc/my.cnf` 进入主配置文件

`log_bin`           
//添加该字段，指启动二进制日志

`server-id=1`       
//集群问题，必须指定该主机的序号，数字随意不能重复

systemctl restart mysqld   
   //重启数据库

测试：查看二进制日志文件当前状态

ls /var/lib/mysql/*bin*     
//查询二进制文件生成完成

mysqlbinlog -v /var/lib/mysql/localhost-bin.000001   
//查看二进制日志(部分可能是mysql-bin.000001)

7.4 Slow Query Log(慢查询日志)

默认慢查询日志未开启
开启慢查询日志功能：

vim /etc/my.cnf

  slow_query_log=1   
  //启动慢查询日志，这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句
  
  long_query_time=3  
  //当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短
  
systemctl restart mysqld   
   //重启数据库

ll /var/lib/mysql/*slow*查看日志文件已经生成