mysql复杂操作

2019独角兽企业重金招聘Python工程师标准>>>

1.联合查询—union:
  把多条select语句的结果合并到一起的操作。
  (select * from table1 order by days limit 3,2)
  union [all]
  (select * from table2 order by days limit 5)
  使用场合：获取数据的条件，出现逻辑冲突，或者很难在一个逻辑表内实现，就可以拆分成多个逻辑，分别实现并将结果合并到一起。
 
  主要:使用union时，要想子语句结果实现排序，必须满足一下两个条件：
  a.子语句必须包含在子（小）括号内；
  b.子语句的order by必须配合limit的配合。
  原因：union在做子语句时，会对没有limit子句的order by实施优化。
 
2.子查询
  使用场景：查询的结果需要两次查询，并且第二次的查询需要以第一次查询的结果作为条件。
 
  a.标量子查询：(作为条件的查询结果为1行1列)
    select * from table1 where days = / > / < / <>  (select max(days) from table1);
  b.列子查询：(作为条件的查询结果为n(n>=1)行1列)
    select * from table1 wherr days in /=any / any/some / all/ / !all /not in / !any (select max(days) from table1);
    注：1.!any当集合的元素大于1个时，几乎不能用,程序中不该出现的语法，面试中容易出现;
           2.all、any和some还可以与除了=、！=之外的运算符配合使用；
  c.行子查询:（作为条件的查询结果为1行,最好使用limit来确保只有一行）:使用（field1,field2）来构建一行
    select * from table1 where (gender,name) = / in (select gender,name from table where name ='XXX' limit 1);
  d.表查询：（作为条件的查询结果为多行多列）一般是在from位置出现
    select * from (select name,age,days from table where age > 20 ) as tabletemp where name like '李%';
    注；一定要给临时表取名。
  e.exists查询：不提供数据，只是判断条件是否成立；(第27节：还没看懂？)
    select * from table2 where exits (select * from table1 where id>0 );

3.连接查询(支持同一个表或者多个表不同或者自己连接想自己的多次连接：)
  a.内连接：连接的多个数据都存在：leftTable [inner] join rightTable on 连接条件（多可连接条件可以在连接条件中使用关键字where、and） （默认连接方式，可以省略inner）
    例如：select leftTable.name,leftTalble.rigjt,rightTalbe.time from leftTable inner join rightTable on leftTabel.id=rightTable.t_id;
    注：1.内连接省略条件的内连接佳作笛卡尔连接，可以用coress jion 代替inner jion.
          2.内连接的写法：on(先过滤、再连接)、where（先连接、再过滤）、using（需要两个连接的字段名完全一致：using(teacher_id)）.
  b.外连接：连接的多个数据有一个或多个都不存在
    例如：select leftTable.name,leftTalble.right,rightTalbe.time from leftTable left outer join rightTable on leftTabel.id=rightTable.t_id;
    注：1.只能使用on、using作为连接添加，不能使用where作为连接条件；
   
    外连接又分为左外连接、右外连接,全外连接（myql暂时不支持，左外连接和右外连接union就是全外连接）；
    左连接：当左边的数据和右边的数据连接不上时候，左边的数据被保留；
    例如：select * from one left join two where one.id=two.id;
    右连接：当左边的数据和右边的数据连接不上的时候，右边的数据被保留；
    例如：select * from one right_join two where on.id=two.id;

  c.自然连接：连接中多个内连接和外连接，即：通过mysql自己的判断（使用相同字段作为连接条件）完成连接，我们不需要指定连接条件。
    内：natural
    外：有分为左外和右外，左外：natrual left join；右外：natural right join；       

4.导出数据：outfile的使用：纯数据的备份,
  将数据表保存到指定地址：select * into outfile 文件需要保存的路径 from 表名 where 查询条件；
  还可以只定义数据导出的格式：
  select * into outfile 'e:/amp/three'
  fields terminated by ',' enclosed by 'X' (自定义字段分割符和字段的包裹符)
  lines terminated by '\n' starting by 'start:'（自定义记录开始符和结束符）
  from teacher_class where t_name = '韩信';
  注意：常规情况下，记录是一行一行的显示的；
        特殊情况下，导出二进制数据的时候例外，我们需要使用into dumpfile,可以避免输出空格、换行之类的输出，很适合二进制数据的保存。

5.导入数据load file（注意数据的格式）
  将文件导出到一个数据表：
  load data in file 需要导入文件的路径 into table 需要导入到的表名；
 

  当需要插入数据的主键与已经存在的数据的主键发生冲突的时候，我么可以使用一下的语句：（其实就是要执行update操作）
  insert into teacher_class (id，name,age) values
 （13，‘tom’,22）
  on duplicate key update
  name='tom' ， age = 22 ；
  这种语法也可以改成这样：
  replace into eacher_class (id，name,age) values
 （13，‘tom’,22）
 
  insert语句还可以执行select出来的语句：
  insert into teacher_class (name,age)
  select name,age from teacher_class；（这半句代替需要需要插入的值：蠕虫复制:复制自己数据表中的数据并插入）
  
  delete还可以配置limit和order by一起来使用，还支持多表同时删除（update也是）：
  模拟外键操作：delete from one,two using one join two on one.public_field=two.public_field where one_id = 2；
                          update one join two one.public_field=two.public_field set one_data='X' ，two_data='Y'; 
 
  清除表：truncate table 表名；
  从数据层面，truncat类似于delete;
  不同点：1.truncat不会返回数据影响的函数，delete会；
          2.truncat会重建id;
  原因：truncat是直接删除整张表，然后重建这张表；delete是逐行删除。

6.视图操作(支持所有mysql操作)：用于在数据库中创建一张新的虚拟表，用来读取你想要的信息，同时隐藏了一些你不想读取的信息。
  1.创建视图：
  语法：create view view_name as 查询语句；
  例如：create view view_infor as select id,name,age from t_teachder;
  注：当我们操作的是视图的时候，对应的数据表也会发生想应的变化。
  2.删除视图：
  语法：drop view [if_exists] view_name;
  3.修改视图：
  alert view view_name as 查询语句;
  也可以给字段重新命名：
  alter view v_teacher(fieldname1,fieldname2) as select id,name from view_name;
 
  视图还可以优化mysql操作,所见业务逻辑：
  可以把每次都需要操作的大表（可能是多次的联合操作）创建成一个视图即可达到优化作用：
  即如果每次都要操作这个语句：
  select * from join_teacher_class as tc left join join_teacher as t on tc.t_id=t.id left join join_class as c tc.c_id=c.id;
  可以把这个语句所产生的查询结果生成一个视图,然后查询这个视图：
  create view v_table as
  select * from join_teacher_class as tc left join join_teacher as t on tc.t_id=t.id left join join_class as c tc.c_id=c.id; 
  select * from v_table;

 

转载于:https://my.oschina.net/u/1771585/blog/472242