mysql的简单查询

//1.查询升序排序:
  select * from 表名(table) 
  order by  字段名(一般按更新时间排序  updtTime)
  ASC;    
//2.查询降序排序
  select * from 表名(table) 
  order by  字段名(一般按更新时间排序  updtTime)
  DESC; 
//3.插入一条数据(两种方法)(是按id的顺序依次插入的,不考虑排序问题)
  //第一种:
  insert into 表名(table) (字段1,字段2,字段3,字段4)
  values('值1','值2','值3',NOW()(只是值4,如果是当前时间就是now()));
  //第二种:
  insert into 表名(table)  
  values('值1','值2','值3',NOW()(只是值4,如果是当前时间就是now()));
//4.insert插入多条数据(两种方法)(是按id的顺序依次插入的,不考虑排序问题)
  //第一种:
   insert into 表名(table) (字段1,字段2,字段3,字段4)
   values('值1','值2','值3',NOW()(只是值4,如果是当前时间就是now())),('值1','值2','值3',NOW()    (只是值4,如果是当前时间就是now()));
  //第二种:
   insert into 表名(table)  
   values('值1','值2','值3',NOW()(只是值4,如果是当前时间就是now())),('值1','值2','值3',NOW()    (只是值4,如果是当前时间就是now()));
//5.查询语句:
   //查询表里所有数据
    selcet * from 表名(table);
   //查询where子语句
   select * from 表名(table) 
   where
   字段名(runoob_author)=字段下面的某个值('菜鸟教程');
   //注意:如果要区分大小写请在where后面加一关键字binary就可以区分大小写
   例: select * from 表名(table) 
   where binary
   字段名(runoob_author)=字段下面的某个值('菜鸟教程');
//6.update更新语句:
  //注意一般都是根据id修改
  update 表名(table) set 字段名='要修改的内容' where id=?(要修改的id);
  例子:
  update runoob_tb1 set runoob_title='学习 c++' where runoob_id=13;
//7.delete删除语句
  //只删除id值
  delete from 表名(table) where id=(需要删除的id);
  例子:
  delete from runoob_tb1 where runoob_id=12;
//8.like模糊查询
  select * from 表名(table) where 字段  like '%模糊内容';
  例子:
  select * from runoob_tb1 where runoob_title like'%大数据';
//9.union查询2张表中某一相同字段下面不同的值(不重复)
   1.
    select 字段1 from 表名1(table)
    union 
    select 字段1 from 表名2(table)
    order by
    字段1;
   例子:
    select runoob_author from account
    union 
    select runoob_author from runoob_tb1
    order by
    runoob_author;
   2.union all查询2张表中某一相同字段下面不同的值(重复)
    select 字段1 from 表名1(table)
    union all
    select 字段1 from 表名2(table)
    order by
    字段1;
    例子:
    select runoob_author from account
    union all
    select runoob_author from runoob_tb1
    order by
    runoob_author;
   3.union all查询2张表中某一相同字段下面不同的值对应的name字段(重复)
    select 字段1 , 字段2 from 表名1(table)
    where 字段1='菜鸟教程'
    union all
    select 字段1 , 字段2 from 表名2(table)
    where 字段1='菜鸟教程'
    order by
    字段1;
    例子:
    select runoob_author , runoob_title from runoob_tb1
    where runoob_author='菜鸟教程'
    union all
    select runoob_author ,name from account
    where runoob_author='菜鸟教程'
    order by
    runoob_author;
//10.group by分组语句:在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
    1.查询表中某一字段出现的次数进行分组
    select 字段名 ,count(*) from 表名(table) group by 字段名;
    例子:
    select runoob_title ,COUNT(*) from runoob_tb1 group by runoob_title;
//11.inner join....on内连接查询
     select a1.`字段id`,a1.`字段名`,b.`字段名` 
     from 表名1(table) a1(别名)
     inner join
     表名2(table) a2(别名)
      on
     a.`相同字段`=b.`相同字段`;
     例子:
     select a.`runoob_id`,a.`runoob_author`,b.`NAME` from runoob_tb1 a
     inner join
     account b
      on
     a.`runoob_author`=b.`runoob_author`;
相当于:
     select a1.`字段id`,a.`字段名`,b.`字段名` from
     表名1(table) a1(别名),
     表名2(table) a2(别名)
     where
     a.`相同字段`=b.`相同字段`;
     例子:
     select a.`runoob_id`,a.`runoob_author`,b.`NAME` from
     runoob_tb1  a,
     account  b
     where
     a.`runoob_author`=b.`runoob_author`;
//12.left join ... on左外连接
      select
      a1.`字段id`,
      a.`字段名`,
      b.`字段名`
      from
      表名1(table) a1(别名)
      left join
      表名2(table) a2(别名)
      on
      a1.`相同字段`=b2.`相同字段`;
     例子:
      select
      a.`runoob_id`,
      a.`runoob_author`,
      b.`NAME`
      from
      runoob_tb1 a
      left join
      account b
      on
      a.`runoob_author`=b.`runoob_author`;
//13.right join ...on
     select
     a1.`字段id`,
     a.`字段名`,
     b.`字段名`
     from
     表名1(table) a1(别名)
     right join
     表名2(table) a2(别名)
     on
     a1.`相同字段名`=b2.`相同字段名`;
     例子:
     select
     a.`runoob_id`,
     a.`runoob_author`,
     b.`NAME`
     from
     runoob_tb1 a
     right join
     account b
     on
     a.`runoob_author`=b.`runoob_author`;