MySQL-3

一、DCL语句（数据控制语句）

用户帐号及权限管理  ‘user’@’host’

允许的访问和登录mysqld的用户名和主机名。   mysql是客户端   mysqld是服务端

host包括：IP、网络地址、主机名、通配符（%和_）

创建用户帐号

CREATE USER ‘username’@’host’ [IDENTIFIED BY ‘password’];

删除用户：

DROP USER ‘username’@’host’;

授权用户:

权限的分类（作用的对象）

管理权限、数据库、表、字段、存储过程              对象（指的是数据的集合体）

GRANT命令（授权）  新版本不可授权同时创建用户

GRANT pri_type,... ON [object_type] db_name.tb_name TO ‘user’@’host’ [IDENTIFIED BY ‘password’]  8.0不支持此命令密码部分

权限类别pri_type分为：（对于作用的对象权限的细微划分）

ALL SELECT CREATE ALTER....

*.* 所有库和表授予权限

db_name.*授予库权限        mysql.user

*.tb_name授予表权限

【注】：授权之后要重新登录

查看授权：SHOW PRIVILEGES;

授权后重读授权表刷新

FLUSH PRIVILEGES;

撤销授权

REVOKE命令

MySQL用户密码和权限管理

跳过密码实现破解密码免密登录 /etc/my.cnf

 下面修改密码成功案例是在修改配置文件的基础上：

二、mysql数据库的文件类型

数据文件（包括索引） 日志文件（二进制日志、错误日志、查询日志、慢查询日志、中继日志）

myisam  .myi（索引）    .myd（数据）

inodb    .idb

三、索引

索引：

按照特定数据结构存储的数据

应创建在经常用作查询条件的字段上

实现的功能

      没有索引：表扫描（消耗大量资源）

      有了索引：将单个字段抽取出来，进行排序，将索引装载进入内存。

1.索引的作用

①：将经常用于查询的某些字段定义索引后，数据库利用索引定位技术，能够大大加快查询速率。

特别是在当表特别大的时候，或者涉及到多表查询的时候，利用索引可以使查询加快成千倍。

②：极大的降低I/O成本(将随机IO转为顺序IO），降低数据库的排序成本。

③：使用分组、排序时，能大大减少分组和排序时间

索引的缺点：占用额外空间，影响插入速度

2.索引类型

聚集索引：数据和索引存储在一起  innodb

非聚集索引：指针导向  myisam 二叉树 红黑树

【注】：Mysql 使用的是非聚簇索引，非聚簇索引和InnoDB的聚簇索引这两棵B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同而己，InnoDB主键聚续索引B+树的节点存储了主键和数据行，MyISM 非聚族索引 B+树存储了主键和指向data 的指针。这个指针通常是一个地址，指向数据行在 

主键索引、辅助索引82

稠密索引、稀疏索引

B+ TREE索引 HASH索引 R TREE索引

参考链接:

Binary Search Tree Visualization

Red/Black Tree Visualization

B-Tree Visualization

B+ Tree Visualization

区别：

①Btree和b+tree它们都是通过索引检索数据的一种方式，是二叉树的一种表现；

②然后b+tree是把索引和数据都存储在叶子节点，非叶子只存储索引

③

3.索引组织方式

左前缀索引：例如like ‘abc%’

MySQL在索引时，采用左前缀索引。例如字符型255长度，会抽取最左侧的一部分字符来作为索引。

4.管理索引的途径

创建索引：

①创建表的时候指定

②修改表的时候添加或(删除索引)     alter table tb_name add xxx（只能mysql使用）

③单独创建索引  CREATE INDEX index_name on tbl_name(col_name)    只限创建普通索引

删除索引      DROP INDEX index_name ON tbl_name    也可以删除特殊索引（在很多情况下index_name需要加上反引号，以示区别）

查看索引：SHOW INDEXES FROM tbl_name;

查看可能用到的索引前面加上explain

profiling工具--->查看SQL语句的运行时长

set profiling=1  临时开启

set persist profiling=1  永久开启

 添加索引：

添加索引，用第三种方法： 

 删除索引：

四、 MySQL外键设置及定义

定义：为了满足关系型数据库的第三范式，降低数据冗余，在表与表之间需要满足相应的约束关系，可以为表和表之间设置外键

设置外键语法    （表类型不能是myisam存储引擎）

foreign key(col_name) references f_table(col_name)

给外键表插入数据： 

 五、视图 

视图：VIEW

是一种虚表（存储下来的select语句），表结构为空。

创建视图：CREATE VIEW vtbl_name as select_statement;

 受到母表控制，不用可以删除（视图和母表的连系性很强，操作视图的相关数据会导致母表的数据发生变化）

 更改视图，母表也会发生变化：

 六、DML语句之查询语句SELECT高级查询

SELECT

查询

SELECT语句的执行过程

过程详解：

     from clause ---> where clause ---> group by ---> having clause --->order by ---> select --->limit

操作方法

     select col1,col2... from clause--->where clause--->group by--->having--->order by--->limit

SELECT语句默认查询后会被缓存，方便下次查询

SQL_CACHE：显示指定存储查询结果于缓存之中。

查询sql缓存大小  show global variables like ‘query%’;

1：回顾mysql单表查询

SELECT ......

DISTINCT:数据去重  (针对于字段上的数据)

格式：select distinct col_name from tbl_name;

字段显示可以使用别名 AS，同时表也可以使用别名

SELECT col_name AS new_name FROM tbl_name;

SELECT支持算术操作以及调用函数

 where子句

过滤条件：真或假

支持比较操作符  =  !=  (<>) 

【注】：(<>)只可以比较数值，不支持字符

支持算术操作：+  -  *  /  %

 

 比较操作符之IN：

IN (元素1，元素2.....)

 空值比较或匹配

IS NULL      (子句内元素为空)

IS NOT NULL  (子句为非空)

like：做模糊匹配（可接通配符）

     %：任意长度的任意字符

     _：任意单个字符

group子句之聚合运算

group:根据指定的条件把查询结果进行“分组”   目的   ----->以用于做聚合运算

avg(),平均数  max(), 最大值  min(),最小值  count(),做统计  sum()，和数

select avg(col_name) from tbl_name group by an_col_name;

 having:对分组聚合后的结果做过滤

同时对分组聚合运算后的查询结果根据指定条件进行排序（order by），默认升序，降序使用desc。

另外最后也可以进行偏移限制查找  limit [m,[n]]

 七、SELECT语句之连表查询

为了在设计表时，为了符合范式方面的要求，很有可能要将一张表拆分成2张甚至多张的表。

将来在查询时，需要将2张表中的数据按照指定字段（或者某种模式）联合起来，然后显示其相应的结果。 

多表查询的表连接方式

交叉连接：笛卡尔乘积（最耗资源，效率极其低）

连表查询：执行表连接操作是按照指定字段间的关系进行连接。

内连接：

等值连接：表的字段以等值建立连接关系,两个表中具有相同意义的字段（有关联意义的字段）以相等方式进行判断，进行连接

          左右表能相等的行直接构建成新行，不相等的排除在外。

inner join =  join

不等值连接：

自连接：自己跟自己字段建立等值连接（定义别名进行查询）

定义表别名：

外连接：

左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.co1=tb2.col

 右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.co1=tb2.col

 子查询：在查询语句嵌套着查询语句

               基于查询结果再次进行查询。

在where子句中   例子：select name,age from students where age>(select avg(age) from students);

在in中的子查询：例子：mysql> select age from students where age in(select age from teachers);

表联合查询：将多个表的查询结果进行联合在一起   select col_name from tb1_name union select col_name from tb2_name;

            例如mysql> select name,age from students union select name,age from teachers;

将查询的结果作为表来进行嵌套查询

 

 三表联查：

 三表联查外连接：

 自查询：将自己的单张表想象成多张表进行查询