hhhjj

mysql

文件数据类型：

        数据文件、索引文件

        重做日志、撤销日志、二进制日志、错误日志、查询日志、慢查询日志、中级日志文件

        备注：这就是物理类型看到的

        逻辑类型看到的就是：把上面的文件映射，映射成二位表的结构

mysql用户端的执行过程

 

1、用户请求链接，这时mysql的连接管理器会打开一个线程  ，由线程管理器调用用户模块验证用户身份以及能否执行命令（备注，交给用户模块后连接管理器和线程管理器的使命就完成了）

    

    2、用户输入命令，到命令派发器，命令派发器会到查询缓存器中查询缓存（备注：查询缓存是把命令哈希运算加密后与缓存器中的加密码对比相同则命中，不相同则不命中所以说，我们在写sql语句时，大写就大写小写就小写大小写做出来的哈希运算时不一样的）

    

    3、如果查询缓存中未命中，则送给分析器做分析，这步就有不同的操作，如果是简单的查询语句或者是增删查改语句，

    

    4、要送到优化器进行优化查询，然后送给访问控制模块检查其是否对要求的操作有权限，如果有，则交由表管理器，交给存储引擎接口调用引擎

    

    如果修改表，则交由表修改模块然后送到访问控制模块

    

    如果是维护则由表维护模块交由访问控制模块

    

    如果是复制命令则由复制模块交由访问控制模块

    

    如果查看查询状态则由状态报告模块

索引管理：

 

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

    

    

    索引类型：

        聚集索引：数据按照索引的次序排序，并与索引储存在一起

        非聚集索引：数据不与索引存储在一起

        

        主键索引：可以一对一查询

        辅助索引：辅助查询的

        

        

        稠密索引：索引了表中的每一个数据项

        稀疏索引：未索引表中的每一个数据项

        

        B+tree索引：

        hash索引:

        R TREE索引：

        左前缀索引：抽取最左侧的某些字符作为索引

        

管理索引的途径

 

     创建表时直接索引

     创建或删除索引：修改表的命令

     

    

    create index Index_name

    on Tb_name(index_col_name,...)

    

    删除索引：

    drop index Index_name on TB_name

    

    查看索引：

    

    show indexes from table_name

        

        

    explain CMD:分析查询语句如何执行的。有没有经过索引等

view:视图

从表中取出某些字段组成一张虚拟的表 

     

    creat view  View_name as Select_statment 

    

    创建完视图可以用show tables 看到

    用show  table status like View_name 

    查到的基本都是空

    

    备注select出来的语句如果col名字相同则会报错      

        

    当我们查看视图中的内容，实质上是在原表中操作的

 

    删除视图：

            drop view View_name

            

    如果我们往视图中修改数据，实际上是由基表的结构决定的，如果你要插入或修改的内容不满足基表条件，则会修改失败

    

    

表中插入内容

 

    1)  insert tb_name [col1,...] values (var1,...)(var2,...)

    

    2) insert tb_name  set var1='var1',var2='var2'

    

    删除内容

    

    delete from TB_name where condition

    

    一定要写条件，where

    update修改也必须写

    

    

  

select

      

    单表查询：

    

    select * from table 

    

    

    select name,id from students

    

    select distinct sex from students

    从students表中取出sex并去除重复

    

    distinct:数据去重

    seq_cache:显示指定储存查询结果于缓存之中；

    seq_no_cache:显示查询结果不予缓存

    

 

    show global bariables like 'query%';

    其中query_cache_type为：查询缓存类型

    只有他是on的时候才可以查询缓存

    当他的结果是on时，默认缓 存符合条件的结果，否则不予缓存

 

    其中query_cache—size为：  查询缓存大小   

    

     

     

as：     

    字段显示时可以使用别名

      

    用as表示，跟在显示的字段后

    

    示例

    select name as n from students

    把name字段命名为n 显示

    

查找条件：

 

        where:表示判断条件

        可以用(=,>,<,!=,...还可以用加减乘除后做比较)

        

        between a and  b:在a和b之间的

        

        in(a,b):匹配表中的a或者b的显示

        

        is null:取值为空的

        is not null：取值不空的

        like:表示模糊匹配

              %：任意个数的任意字符

              _：任意的单个字符

    逻辑操作：

    

        not:非

        and:且

        or:或

        xor:异或（相同为假）

     

    

group:根据指定的条件吧粗挨训结果今次那个分组，以用于做聚合运算

 

     分组 group by

     

     示例

     select * from students group by sex;

     

     把students表的所有内容按性别进行分组

 

 

  聚合运算：  

    avg():平均数

    max()：最大值

    min()：最小值

    count()：某列中的行数

    sum()：求和

    

    示例：

    select avg(age) from students group by sex;

    按性别分组并显示其平均的age

    

having

 

    当我们对条件进行分组后，那么后续的判断条件不能用where,需要用having,having后加判断条件

    

    

    示例：

    select avg(age) as a from students group by sex having a>30;

    按性别分组，显示平均年龄大于30的。

    

order by ：排序，放在条件后面。没有条件的直接放在最后。

 

     降序排序在后面加desc

     

     

 

limit:输出时进行数量限制

      

      limit 10:表示跳过10个显示

      limit 10,10 ：表示跳过10个显示10个

练习： (1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

 

select name,agefrom students where age>'25' and gender='M';

 

   (2) 以ClassID为分组依据，显示每组的平均年龄

   

   select avg(age) from students group by 'classid';

   

   (3) 显示第2题中平均年龄大于30的分组及平均年龄

   select avg(age) as aage,classid from students group by 'classid' having aage>30 ;

  Empty set (0.00 sec)

 

   

   (4) 显示以L开头的名字的同学的信息

   

    select * from students where name like "l%" ;

 

 

   (5) 显示TeacherID非空的同学的相关信息

   

    select * from students where teacherid is null ;

 

   (6) 以年龄排序后，显示年龄最大的前10位同学的信息

   

   select * from students order by age desc limit 10;

   

   (7) 查询年龄大于等于20岁，小于等于25岁的同学的信息        

   select * from students where age between 20 and 25;

多表查询：

 

1、交叉连接：又叫笛卡尔乘积，是如果把两张表连接那么他会把表中的每个字段相互连接。

    

    select * from students,teachers ：表示把syudents表和teachers表交叉连接

 

2、等值连接：几张表以等值为    条件作为判断，则把有相等    条件的区域连接起来

    select * from students,teachers where students.teacherid=teachers.tid

    :表示把students表中teachesid等于teachers表 中tid的行连接起来

    

     两张表做连接，并取出其中的一部分    

      select s.name as snam,t.name as tnam from students as s,teachers as t where t.tid=s.teacherid;

      取出students表中teacherid等于teacher表中tid的学生的名字和老师的名字

    

 

 

左外链接：

    两张表做等值连接，如果左侧表有的，右侧表也有的，就一一对应建立其值

    如果左侧表有右侧表没有的，那么就把左侧表显示，右侧表留空。

    

    from tb1 left join tb2 on tb1.col=tb2.col

    

 

    右外链接：和左外连接相反，以右表为基准    

       

    from tb1 right join tb2 on tb1.col=tb2.col

    

    

            

    自连接：用一张表当两张表，取相同的地方。

      

      

      select s.name,s.tname from students as a,students as a where s.stuid=s.teacherid

       

 

子查询：基于查询结果再次进行查询的查询

 

     1）用在where语句中的查询

     select name,age from syudents where age>(select avg(age) from students)

     

     2用在in中的子查询

     

     select name,age from students where age in (select age from teachers)

    

元数据数据库：

 

     mysql:

          授权表

                :db

                :host

                :user

          colimns_priv

          tables_priv

          procs_priv

          proxies_priv

          

          

    创建用户：

         create user 'username'@'host' [indentified by 'password']

        

    重命名用户：

        

        rename user old_name to new_name

        

    删除用户：

        drop user 'username'@'host'

    

    修改密码：

        set password for name

        

        

        

   忘记管理员密码时怎么办：

   1）启动mysqld进程时加选项 --skip-grant-tables --skip_networking

   

   2)使用update命令修改管理员密码

   

   3）关闭mysqld进程，移除上面两个选项，重启mysqld

        

        

        

授权

     grant priv_type  on priv_level to 'username'@'host' user_specifiation

        

取消授权：

     

    revoke  priv_type  on priv_level from user

    

    

查询缓存：

    查询缓存中相关的服务器变量情况：

    

    show global variables like '%query%'

    其中

     query_cache_min_res_unit：代表查询缓存中分配内存块的最小单位

     query_cache_size：查询缓存总共可用的内存空间，单位是字节必须是1024的整数倍

     query_cache_limit:mysql能够缓存的最大查询结果

     query_cache_type:ON,OFF,DEMAND 

     

    缓存命中率低时提高缓存命中率的方法：

    1）如果因为查询结果过大导致的我们要提高query_cache_limit的值

    2）如果是因为内存碎片化过于严重造成我们要降低query_min_res_unit的值

    3）内存缓存空间过小造成的我们要提升query——cache_size的值

    

    查看缓存状态变量：

    show global status like 'qcache%'

        

引擎：

     

     innodb：处理大量的短期事物

     

     数据储存于“表空间中”

       1）所有innodb表的数据和索引放置于同一个表空间中；

       表空间文件：datadir定义的目录下

       

       idbdata1,

       idbdata2等

 

    

    2）每个表单单独使用给一个表空间存储的数据和索引

       inno_file_per_table

    

    

事务：

      表示一组原子性的sql语句，或者说一个独立的工作单元。

 

     ACID：

         A:原子性：整个事务中的所有操作要么全部执行成功，要么全部失败后回滚

         C：一致性：数据库总从一个一致状态转换到另一个一致性状态

         I:隔离性：一个事务所做出的操作在提交之前是不能为其他所见的，隔离有多种隔离级别。

         D:持久性：一旦提交事务，其所做的修改会永久保存于数据库中。

         

    启动事务命令

         ：start transaction

        结束事务有两种方式：

        1）提交：commit

        2）回滚：rollback

    

  建议：显式请求和提交事务，不使用自动提交功能  

        关掉自动提交的方法

       ：aotucommit=0|1

       （在会话级别）

    

    set autocommit=OFF

        查看与事务相关的命令：help  transactions

    

        事务还支持保存点：savepoint

        设置保存点：savepoint S_name

    

        回到保存点：rollback to S_name 

        释放保存点：release savepoint  S_name

    

    事务的隔离级别

    

       1）read uncommitted (读未提交) 可能遇到：脏读、不可重复、幻读

       2) read committed（

       读提交）

       可能遇到：不可重复读、幻读

       3) repeatable read

       （可重读）

       可能遇到：幻读

       4) seriatizable     

        （可串行化）

        可能遇到：加锁读

      

       可能存在的问题

       1）脏读：读到别人没提交的数据

       2）不可重复读：在自己的一个事务中看到别人的两个数据

       3）幻读：修改过得数据显示都有，一个是该玩的一个是未改的

       4）加锁读：加锁后别人无法读取数据    

    

    查看线程：show processlists    

    查看和隔离级别相关：

    show global variables like '%isola%'

    

    其中定义隔离级别的变量：tx_isolation

    这个可以在会话级进行修改

    

    命令：set tx_isolation='隔离级别名字'

    

    

    日志文件：