开源数据库MYSQL DBA运维实战 第二章 SQL

一、SQL语言分类

二、名词解释

三、DDL

1.DDL库

1.1定义库

创建业务数据库

        语法：CREATE  DATABASE   数据库名；

        数据库命名要求：

                区分大小写

                唯一性

                不能使用关键字如create  select

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

                正常的：用拼音或单词即可

查看所有数据库

SHOW  DATABASES；

1.2选择/进入数据库

USE  数据库名

SELECT   database();    调用函数，查询当前库

1.3删除数据库

DROP  DATABASE  数据库名；

1.4系统中的位置

/var/lib/mysql         数据库的实体，清理备份

2.数据类型

2.1数值类型

整数 int

浮点数 float double

2.2字符串类型

字符系列CHAR和VARCHAR

枚举系列ENUM

集合类型SET

2.3时间和日期类型

年YEAR

日期DATE

时间TIME

日期和时间DATETIME

3.DDL表

创表目的

        表是数据库存储数据的基本单位

        表由若干个字段（列）组成

        主要用来存储数据记录（行）

数据库实例1

• 创库        create  database  haha;
• 使用库     use   haha；
• 创建表     create   table   t1  (id  int);
• 查看所有表名      show  databases;
• 插入数据             insert   into   t1   values  (1);
• 查询所有数据      select   *   from   t1;
• 删除表                 drop   table   t1;

数据库实例2

• 创两列的表格-序号和姓名   create  table  t2  （id  int,  name  varchar(20)）;
• 查看表结构     desc  t2
• 插入数据        insert  into  t2   values   (1,"zhangsan");
• 查询所有数据    select   *   from   t2;

数据库实例3

创建表               create  database  school； use  school；

                        create  table   student1（id   int,name  varchar(20),sex  enum('m','f'),age int);

查看表名        show tables;

表中插入内容   insert  into  student1  values( 1,"zhangsan",'m',18);

查看表内容       select   *   from   student1;

查看表结构       desc   student1;

四、DML

1.插入数据INSERT

2.更新数据UPDATE

update  t1  set  id=2 where name="zhangsan";

修改mysql数据库管理员root账户的密码

update  mysql.user  set  authentication_string=password("Root123@")  where user="root";

3.删除数据DELETE

delete  from  t2    where  name="zhangsan";

五、DQL

1.简单查询

select   *   from  student；

select  id,name,age   from  student；

四则运算查询

select  id,name,age*10   from  student；

2.条件查询

单条件查询where

select  name,post  from  employee  where  post="hr";

多条件查询and/or

select  name,salary  from  employee  where  post='hr'  and/or  salary>1000;

关键字between and在什么之间

select  name,salary  from  employee  where  salary between 5000  and  10000;

select  name,salary  from  employee  where  salary  not   between 5000  and  10000;

关键字in集合查询

select  name,salary  from  employee  where  salary in (4000,5000,6000,9000);

select  name,salary  from  employee  where  salary  not  in (4000,5000,6000,9000);

关键字is  null

select  name,salary  from  employee  where  salary  is  null; 

select  name,salary  from  employee  where  salary  is   not    null;

关键字like模糊查询

select  *  from  employee  where  name  like  'a%';      %表示任意多个字符

select  *  from  employee  where  name  like  'a_';       _表示任意一个字符

3.查询排序

select  *  from  employee  order  by   salary  asc;      升序

select  *  from  employee  order  by   salary  desc;      降序

select  *  from  employee  order  by   salary  asc   limit  5;      前五

六、DCL

1.权限级别

Global  level	所有库，所有表的权限
Database  level	某个数据库中的所有表的权限
Table  level	库中的某个表的权限
Column  level	表中某个字段的权限

2.mysql用户管理

2.1创建用户

CREATE  USER  user1@'localhost'  IDENTIFIED  BY  "Root123@"

• CREATE   创建（关键字）
• USER       用户（关键字）
• user1        用户名称（自定义）
• @             分隔符  （关键字）
• ‘localhost’    允许登录的主机   如果是%的话登录全允许，但不包括localhost
• IDENTIFIED  BY   身份认证（关键字）
• "Root123@"         用户密码

2.2删除用户

DROP   USER   ‘user1’@‘localhost’；

2.3修改用户密码

root修改自己密码

mysqladmin  -uroot -p'刚才查出来的密码' password  '新改的密码‘

SET  PASSWORD=password('new_passwd');    password函数

FLUSH  PRIVILEGES      刷新权限

丢失root用户密码

当root用户，忘记了密码，可以使用破解的方式来登录系统，修改密码

原理：使系统在启动时，不加载密码文件

1.修改mysql启动设置

vim  /etc/my.cnf

[mysqld]

skin-grant-tables

2.重启mysql，无密码登录

systemctl   restart   mysqld        重启mysql程序

mysql   -uroot       无密码登录

修改自己的密码

        UPDATE  mysql.user  SET   authentication_string=password("root")  where user='root'  and  host='localhost';

        FLUSH   PRIVILEGES;

3.修改mysql启动设置，注释掉，跳过密码

2.4登录Mysql

mysql   -P   3306   -u   root   -p   123   mysql   -e   'show tables'

• -P   MYSQL服务器端口     【默认3306】   
• -u   指定用户名                  【默认root】
• -p指定登录密码                   【默认为空密码】
• 此处mysql未指定登录的数据库         
• -e接SQL语句

3.mysql权限管理

语法格式

grant   权限列表   on  库名.表名   to   ‘用户名’@‘客户端主机’   [identified  by ‘密码’  with  option参数]；

==权限列表

• all：所有权限（不包括授权权限）
• select，ypdate      查询更新的权限

==数据库.表名

• *.*                          所有库下的表
• web.*                     web库下的所有表
• web.stu_info          web库下的stu_info表
• grant  SELECT(id),INSERT(name,age)  ON  mydb.mytbl   to  'user8'@'localhost'   identified  by   'Root123@';

==客户及主机

==with_option参数

GRANT  OPTION     授权选项

4.mysql权限示例

4.1赋予权限

授予目标：授予admin3对bbs库中的所有表，距有所有的权限（不包含授权）

GRANT  ALL  ON   bbs.*  TO  admin@‘%’   IDENTIFIED  BY  'QianFeng@13812345678'

授权实例

4.2回收权限

查看权限

查看自己的权限        SHOW   GRANTS；

查看别人的权限        SHOW   GRANTS   FOR   admin3@‘%’；

回收权限REVOKE

语法：  REVOKE  权限列表  ON  数据库名  FROM  用户名@‘客户端主机’

示例：REVOKE  ALL   PRIVLEGES  ON  bbs.*  FROM  admin3@‘%’； 回收所有权限

删除用户的版本问题

5.6之前，先revoke  all  privilege    再  drop  user

5.7之后，直接    drop  user

七、日志

1.日志分类

错误日志	启动，婷子，关闭失败报错，rpm安装日志位置/var/log/mysqld.log
通用查询日志	所有的查询都记下来
二进制日志	实现备份，增量备份，只记录改变数据，除了select都记
中继日志	读取主服务器的binlog，在本地回放，保持一致
slow log	慢查询日志，指导调优，定义某一个查询语句，丁一超市时间，通过日志提供调优建议给开发人员
DDL log	定义语句日志

2.Error  Log

vim  /etc/my.cnf         进入主配置文件，观察日志是否启动

log-error=/var/log/mysqld.log       该字段，标记是否启动日志，以及日志位置

如果哪天mysql服务起不来了，请来这个日子文件看看

3.Binary  Log

默认没有开启二进制日志

启动二进制日志

vim    /etc/my.cnf

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

server_id=2         集群问题，必须指定该主机的序号，数字随意

systemctl   restart  mysqld     重启数据库

测试

1.查看二进制日志文件当前状态

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

mysqlbinlog   -v   /var/lib/mysql/localhost-bin.000001

at  123  找到一个记录位置

2.进入数据库，进行数据操作

创库，创表，创数据

3.完成二进制日志启动，查询测试

mysqlbinlog   -v   /var/lib/mysql/localhost-bin.000001

4.Slow  Query  Log

默认慢查询日志未开启

开启慢查询日志功能

vim  /etc/my.cnf

加下面2行配置

slow_query_log=1

long_query_time=3

重启服务器    systemctl   restart   mysqld

查看慢查询日志文件

ls   /var/lib/mysql/*slow*         查询日志文件生成完成

模拟慢查询

mysq>SELECT BENCHMARK(500000000,2*3);    基本测试语句，测试一个超长时间的查询

收集数据库压力测试工具信息

验证慢查询日志

tail  /var/lib/mysql/localhost-slow.log     观察长查询日志记录，记录了刚才超长的查询结果，稍后反馈给开发部，进行优化