2.MySQL的基本使用

• • • • 1.查询处理：
        • 1.逻辑查询处理
        • 2.物理查询处理
        • 注意：
          • 1.null的布尔比较
          • 2.Where过滤两种不允许出现的情况
      • 2.子查询
      • 3.连表操作
      • 4.集合操作
      • 5.聚合函数
      • 6.删除表
      • 7.事务
      • 8.分页
      • 9.索引
      • 10.备份和恢复
      • 11.mysql查询优化器查询影响

1.查询处理：

可以分为逻辑查询处理和物理查询处理

1.逻辑查询处理

8) select distinct 列
1) from 表名
3) <jointype> join tablename  
2) on <joincondition>
4) where <condition>
5) group by
6) with{cube|rollup}
7) having
10) orderby
11) limitsql 中第一个被处理的字句是from11步中每步都会产生一个虚拟表；select查询一共会产生3个过滤过程，分别是on，where，having

2.物理查询处理

sql解析器完成sql语句的解析，并通过sql优化器选择最优的执行路径，
索引的使用，在一定程度上可以大幅度提高系统的性能
mysql 数据库通过选择追加路径来执行具有相同结果的语句

注意：

1.null的布尔比较

在on过滤条件下null的比较，比较结果为unknown，却被视为false，即两个null并不相同
下面两种情况将null视为相等
group by 将所有null分配在一组
order by 将所有null值排列在一起添加外部行 outer join ，非保留表中我们用null 填充
在mysql 的排序过程中，升序排列时，null排列在最后，即null值在order by子句中视为最小

2.Where过滤两种不允许出现的情况

1.数据没有分组，不能使用聚合函数
2.不能在条件中使用select 定义的列别名进行操作
对应用了的gruop by 的语句，使用distinct 没什么实际作用limit 通常和order by 一起使用，对于小数据分页影响不会太大，采用逐行遍历的形式
因此在数据量比较庞大的分页问题，在应用层建立一定的缓存机制是有必要的

2.子查询

子查询一般来说，称子查询嵌套于外部查询中，子查询必须包含括号子查询的好处：1.每个子查询允许结构化查询，可以把查询分开成几个部分2，可读性强3.用来解决一些复杂的join和union的操作使用any some in进行子查询any关键字必须与一些比较操作符一起使用，如：where s1》any（子查询）in 又称为=anysome 是有一本分的意思 <> some 的意思是有部分b与a不相等，用的很少All关键字必须与一些比较操作符一起使用
独立子查询定义：不依赖外部查询而运行的子查询标量子查询 只返回一个返回查询结果与多值查询结果相对通过explain 来查看命令的具体解析过程大多数情况下，会将独立子查询转换为相关子查询，这也就是mysql子查询效率较低的原因In转化为exists子查询中派生表的使用能够极大的提高 性能exists 一般关联到外部查询 根据是否返回行返回true或false如果子查询过滤器中为某行返回unknown，被认为是falseexists可以放心使用*，exists只关心行是否存在，而不会取各列的值not exists 和not in 区别 在于对null 的处理，在not exists的处理认为只有 true 和flase两种结果not in 存在 true false unknown派生表 又叫表子查询from (子查询结果) as 表名1.列名名称是唯一的2.不支持limit派生表是完成的虚拟表，并没有具体的物理具体化，因此优化器也不知道派生表的信息，在用explain才看派生表的执行计划时，执行时间可能较长

where过滤有两种不允许出现：

3.连表操作

交叉连接 from t1 cross join t2 产生笛卡尔集也可以采用旧标准的 from t1,t2笛卡尔集的作用可以快速生成重复的测试数据可以产生行号，速度较快SELECT NAME,Quentity,(@a:=@a+1) AS row_number FROM supply ,(SELECT @a:=0) t;比SELECT NAME,Quentity,(SELECT COUNT(1) FROM supply t2 WHERE t1.`SupplyId`>= t2.SupplyId) AS row_numberFROM supply t1;要快的多成本为（o(n)）
常用using 简化on的条件，using 的使用可以将两个表中相同列名的列连接起来
例如：select  a.emp_no ,first_name,last_namefrom employee ainner join dept_manager busing(em_no)     => where a.em_no=b.em_no and dept_no='d001'where dept_no='d001'Natural Join 自然连接是通过 INNER join 和Using 的组合
自动将两个表中含有相同名称的列进行关联
例：
select a.empo first_name
from employee a
Natural  join depte_mag b
Straight_join 强制执行左边的表，只能说是mysql对于join 的一种强制优化处理其他还有 self join   自连接nonequi join 等值连接连接查询 称为水平操作，将集合称谓 垂直操作
cross join  /inner join  /outer join
from t1 ,t2 where 条件 旧ANSI标准
from t1 inner join t2 on 新ansi标准
版本兼容，逻辑查询和物理查询是相同的连表：内连接 inner joinselect 字段1。。 from table1inner join table2 on table1.字段=table2.字段1。。inner join table13 on table2.字段=table3.字段1。注：inner join 和join 是相同的内连接只能出现匹配到的结果，没有匹配的不显示内连接 可分为自连接，交叉连接，和内连接外连接 可分为左外连接、右外连接 强调即时没有
语法：
select 字段1 from table1 left [outer] join table2 on table1.字段=table2.zi字段左表的数据全部显示出来，即时左表的数据在右边表没有与之相对应的数据，也显示出来，其右边用null填充
select 字段1 from table1 right [outer] join table2 on table1.字段=table2.z右边的表数据全部显示出来
等值查询（交叉连接） from 表名用逗号隔开
select stuid，jkj from subject，grade -笛卡尔集 where subject.id=jkjk.id 相当于内连接笛卡尔集并没有什么实际意义，除非它和where进行联合使用
自连接：出现父子，上下级出现在同一张表中而这种关系通过树型多表的表现的话，会出现维护和更改的困难特别出现子变夫 、上下级转变、删除的操作太多
处理方法：将这种管理放在同一表中进行表现通过自查询很容易分开父子，上下级，但是容易产生递归操作

4.集合操作

集合操作的两个输入拥有相同的列数，若数据类型不同，mysql数据库会进行自动隐式转化
同时，结果列的名称由第一个输入决定
在集合操作者中into outfile 只能存在与最后一个select语句中否则会出现语法错误
当select语句中出现limit 和orderby 的时候，最好添加括号，否则出错
Union Distinct 和union all
一般省略distinct 关键字直接使用union
union all 输入所有项的及、结果集，并包含重复的选项
except集合操作允许用户找到位于第一个输入中但部位与第二个输入的行数据
可以使用except distinct  和except all
xcept distinct常使用left join 和not exists可以放心使用*，existsselect x.a from xceptleft join yoi x.a=y.allwhere y.a is null
----或 select  a from x  where not exists(select * from y where x.a=y.a)//case when source='x' then cnt else 0 endintersect 返回两个输入都出现的行 单纯使用left join 和not exists要使用 Union all  groupby

5.聚合函数

group_count 将分组后的非null数据通过连接符进行拼接，对null数据返回null值
聚合函数
count（） 记录总行数，不建议使用*，效率低
sum（）  返回一列的总数
avg（） 一列的平均值
max（） 一列的最大值
Min（） 一列的最小值
一般和分组结合使用
having 过滤分组的筛选条件，先分组在运行having
注意在使用分组后，select 只能是分组字段和聚合函数，其余字段不准出现在select后面
order by 可以分为几个批次进行排序 a asc,b desc

6.删除表

TRUNCATE [TABLE] TABLE_name; 有点像不带条件的delete from 表名
但是truncate将id置为1，不建议使用
用于完全清空表数据，但表结构、索引、约束等不变，截断这张表
不同点：truncate不会进行全表扫表，直接删掉，速度快，不可恢复delete from 会产生遍历，产生日志，可恢复truncate table不会对事务产生影响，不产生事务过程truncate table 重新设定auto_increment 计数器

7.事务

使用事务保证数据完整性事务就是将一组sql语句放在同一批次内去执行如果是一个sql语句出错，该批次内的所有sql都将被取消执行
注意：mysql事务处理只支持innodb和DBD表数据类型
保证事务中的sql必须同时执行
#扣钱
update account set balance= balance-500 where accountNo='123'
#加钱
update account set balance= balance-500 where accountNo='17987'
事务回滚 RollBack
ACID原则：原子性 atomic 整体不可分割一致性 consist 保持数据一致性，能量守恒隔离性 isolate  互不影响，几个操作互不影响，锁：不能同时进行同一事物的操作持久性 durable  数据在操作后能够不变
自动提交  SET AUTOCOMMIT
mysql中默认是自动提交，每一条语句都是一个自动提交使用事务时应先关闭自动提交set autocommit=0 ;#关闭自动提交set autocommit=1 ;#开启自动提交START TRANSACTION  开启一个事务，标记事务的起始点COMMIT 提交一个事务给数据库ROLLBACK 将事务回滚，数据回到本次事务的初始状态SET AUTOCOMMIT=1;还原mysql数据库的自动提交1.先关闭自动提交2.设置起点3 提交一个事务给数据库，将事务回滚，取消commit中操作被取消4.还原自动提交 在 实际开发中不必开启自动提交，所有操作都是在自动提交
T-SQL编程set autocommit=0；start transaction;update account set balance= balance-500 where accountNo='123'#加钱update account set balance= balance-500 where accountNo='17987'if****commitelserollbackset autocommit=1;
判断内容要在java部分处理，看是否发生异常，进而比较是使用commit还是rollback

8.分页

order by 字段 【asc】默认升序排列，desc降序排列
独有的语法： LIMIT
limit 【m,】n 或 limit n offset m  常用于分页显示
m 偏移量 即从那行开始显示
n 最大数目
m不指定默认为0 从第一条开始发威千n条数据
注意：select * from result limit 5，10 显示6-15条记录
注意加一，起始
第二页 书写应该是  5+10 即 m=15 （页码-1）*n

9.索引

1.索引作用：1.提高数据库的运行效率2.可以加速表和表之间的连接3.全文索引优化没有索引的时候，使用遍历每一个数据，n/2的查询次数使用二分法进行检索；必须已经排好顺序就行 log2^n/2数据库中采用B树查询2.分类： 主键索引  primary key   形成索引目录文件，且这个索引文件是排好顺序的唯一索引  UNIQUE 避免统一表中某列中数据重复；主键索引只有一个，而唯一索引可以有多个UNIQUE KEY 'GradeID'（'grade'）；唯一属性的名字在''，默认是这一列的列名常规索引  保证数据的唯一性 设置主键，自动形成主键索引 类似b树索引，做查询条件常设置常规索引；如果一列中有几个索引，他会进行比较那个更快可以使用index 或者key index '索引名字'  （'索引列'）创建联合索引 index 'inxd--cd' （'c','d'） c和d作为联合查询条件 c和d先后有顺序的，单独使用c条件产生较少于d条件后，因此c条件，写在前面 alter table 表名 add index '索引名' （'列名'，'列名'）index允许有重复存在全文索引只能用于myisam类型数据表只能用于char varchar text适合与大型数据集like 在大文本时会失效create table '表名'（fulltext 'index--c'('c') c为列名，varchar等类型）engine=myisamalter table employee add fulltext('列名')全文索引的使用，需要自己调用select * from test where match('列名') against('key') key为关键字 如love的关键字，搜索的内容
3.总结：创建索引创建表时添加建表后追加语法：alter table 表名 add 索引类型（数据列名）create unique index'idx_索引名' on 表（列可多列）删除索引drop index 索引名 on 表名  索引名默认为列名alter table 表名  drop index 索引名alter table  表名 drop primary key查看索引show index(或 keys) from 表名
4.索引的原则：索引并不是越多越好，维护目录占内存和时间不要对经常变动的数据加索引小数据量的表建议不要加索引索引一般应加在查找条件的字段在where 或者order by字句中经常使用的字段字段的值是多个字段不是经常变化的不宜添加过多的索引

10.备份和恢复

mysql数据库备份和恢复mysql数据库备份方法：1.导出 导入命令 ，查询2.mysqldump备份工具3.数据库管理工具 如sqlyog4.直接拷贝数据文件和相关配置文件
一、备份       
1.bin目录下的mysqldump 命令不要登陆进mysql ，与命令mysql命令平级mysqldump -h 主机名 -u用户名 -p 【options】 数据库名 表名》保存位置目录注：表名不存在 则保存该数据库下的所有数据表都保存下来mysqldump -uroot -p123456 bdpd库 cat表 dog 表》c:\jkjkj\jjjk.sql产生一个备份的sql文件，命令语句mysqldump -help 查看帮助 --help |moreoption--add-drop-table 默认启用 drop table if exits 语句 可以用--skip-add-drop-table 来取消--add-locks  默认打开，给这个表加锁，防止其他用户使用-t或者--no-CREATE-info 去处创建表的数据，只追加数据*****-c 或cpmplete 在insert 时添加上列名，在合并库的时候非常有用，并不一定添加默认的表中的所有列-d 或 --no-data 不存储表信息，只存储表结构--where '条件' ，-w'条件' 只存储条件内的记录存储--opt 几个限定条件的组合  
2.sqlyog客户端文件本份导入备份  导出sql
3.文件复制：myisam 拷贝 .myi .myd  .frminnodb  拷贝 .frm 不行 缺少.bdata1
4.命令行  （不常用）select studentno,studentname into outfile 'f:\jkjk.sql' from student;把数据提取出来导入到数据表中 必须与导入列的数据类型是一样的load  data infile 'f:/jkjk.sql' into table test (id,name);
二、恢复   
1.使用sourcesource 是在mysql里面的命令用法： source  f：/sqlbak.sqlmysql 登陆上use库source ...		                
2.使用mysql 具体命令mysql -uroot -p123456 texs数据库名称 <f:/sqlbak.sql

11.mysql查询优化器查询影响

mysql查询中带逗号，查询结果的问题:其实啊，你不仅写'1,2'会查出三条数据，就算你写'1ncdsln'或者'1ncskl12397na'之类的也会查出三条，就是Department 等于1的那三条，因为Department是bigint类型，但是你查的时候写的是字符串，这时候mysql就会去字符串前面的数字部分来查询，'312你打开所老'就会截取312这样，因为你的'1,2'不是bigint类型，所以会截取前面的数字，即1来查