MySQL进阶操作

二、MySQL进阶操作

为字段或表起别名：as 

写在字段名前，去除重复数据：distinct

条件筛选常用命令：

    比较运算符 > < >= <= != <>

    逻辑运算符 and or not

    模糊匹配 like, %匹配任意个字符，_ 匹配一个字符

    范围匹配 in(数据1,数据2,…）、 between num1 and num2

    是否为空 is null 、 in not null

排序: order by

分页：limit start,count 注意第一条数据的索引为0

分组：group by

    对一个二维表进行分组，使二维表变为多维表！

    查询子表内数据 group_concat(字段名）

    条件筛选：having 注意区别和筛选记录的where语法

    在表的最后一行追加求和记录： with rollup

聚合函数： 默认情况下，聚合函数会把当前所在表当作一个组进行统计。

    count():count(*) 查询组内有多少条记录

    max()、min()、avg()、sum()

    round(数据，保留的位数）：round(15.345,2) 结果为15.35，该函数对数据四舍五入

多表连接：原理为笛卡儿积

    内连接：inner join

    外连接：left|right [outer] join  outer可以省略

    条件筛选：on 注意和having、where的区别

    自连接：对表进行连接所需要的数据来自于同一张表，使用自连接必须对表起别名，自连接常应用于三级省市联动；

子查询：查询的条件 是 另一个查询的结果

    标量子查询：子查询结果为单行单列

    行子查询：子查询结果为单行 where 字段 = 子查询结果 

                      select * from students where (height,age) = (select max(height),max(age) from students);

    列子查询：子查询结果为单列 wher 字段 in 子查询结果

                       select name from classes where id in (select cls_id from students);

    子查询常和ANY、ALL、IN、EXISTS联用

    ANY即任意一个数据、ALL即所有的数据、IN即数据存在。

    EXISTS谓词，它允许数据库高效地检查指定查询是否产生某些行。根据子查询是否返回行，该谓词返回TRUE或FALSE。

外键：创建表时加入外键

                        [constraint 外键名] foreign key (字段名1） references tname2 (字段名2）);

           为表新增外键

                         alter table tname1 add foreign key (字段名1) references tname2 (字段名2;

            删除外键：show create table my_table;查看外键名

                          alter table my_table drop foreign key 外键名；

常用范式：范式即设计表格式应遵循的规则

          1NF:字段必须保持原子性

           2NF:在1NF的前提下，有主键且非主键字段必须完全依赖主键，不能只依赖部分主键

           3NF:在2NF的前提下，非主键字段必须直接依赖主键，不能传递依赖

E-R图：E-R图的作用是用来描述数据库中的表结构，E-R图使用三个概念描述现实问题

            实体：对应数据库中的一个表

            属性：对应数据库中表的字段

            联系：实体彼此之间相互连接的方式称为联系，也称为关系。1:1 、1:m可以直接表示在数据库中;  多:多（m:n) 则需要使用中间表来表示，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值。

视图：视图就是一个能够把复杂SQL语句的功能封装起来的一个虚表，只是一个表结构，没有保存任何基本表的表数据，并不存储具体数据， 在用户需要查询视图的时候 再去基本表中取出数据。不建议通过视图修改原表数据。

             创建视图：create view 视图名 as select语句;

             查看视图：show tables;

             删除视图：drop view 视图名；

事务：将一系列SQL语句封装在一起，作为一个原子操作，要么SQL语句全部成功执行，要么全部不执行。

       事务四大特性ACID:

            原子性：所有操作要么全成功，要么全不执行

            一致性：数据库总是从一个一致性状态，到另外一个一致性状态，不存在不一致状态

            隔离性：事务与外部（其他事务）之间是相互隔离的，事务的中间状态，外界无法看到

             永久性：事务一旦提交即永久保存，提交后无法执行回滚

         开启事务：begin;或者start transaction; 注意mysqld终端默认开启事务，并自动提交

         set autocommit = 0 可以关闭自动提交 此时需要手动提交，可以通过select @@autocommit;命令查看自动提交是否开启；

         终端中一旦开启事务，就需要使用commit;手动提交，将缓存中的数据变更维护到物理表中；

         事务可以通过rollback;进行回滚。注意create 和drop不能进行回滚操作，这两种操作为隐式提交！

 索引：索引是一种数据文件，它存储着表内所有记录的位置信息，它可以提高数据查询速度。

               创建索引：create index 索引名 on 表名 （字段名[(字符串类型)(长度)]); 注意字符串类型才需要指定类型和长度

               查看索引：show index from my_table;

               删除索引：drop index 索引名 on my_table;

用户管理：使用服务实例级账户 root，用户信息存储在user表中，进行用户管理前需要进入该数据库。

               更改用户密码：update user set authentication_string=PASSWORD('新密码') where user ='用户名'@'允许登录的主机;

               刷新权限：flush privileges; 建议修改用户权限相关的操作后，都执行一次刷新权限。