mysql 语句

1.将api_register 字段中的api_name,description字段移到api_version中

方法1.是先在api_register中创建两个字段api_name,description,再按下面的方法将api_reigster中的api_id=api_version.api_id的数据插入，插完后再删除api_register中的两个字段

a. 增加表结构中两个字段api_name varchar 45
                                       description varchar 2000 allowNull

b. 移除：update api_version s inner join api_register a on a.`api_id`=s.`api_id` set s.api_name=a.`api_name`,s.description=a.description;

c. 删除字段：alter table api_register drop column description;

2.删除

   a.删除整张表：drop table test1;

   b.删除表数据，保存表结构：truncate table test1;

   c. 删除表结构中某一字段(将字段user_name及其user_name下的数据全部删除)：alter table test1 drop column user_name;

   d.删除表结构中的某一字段的所有数据(保留表结构中字段名称)：

update test1 set user_name=null;   ===》将user_name字段的数据全置为Null;

或者update test1 set user_name='';  ===>将user_name字段的数据全置为空，即没有值

3. 增加

    a. 对于已有的表test1，增加字段user_name:  alter table test1 add user_name varchar(20);

    b. 对某一字段增加一条数据(给id=1的数据增加user_name的值) ： update test1 set user_name='tang seng' where id='1';

    c. 增加多个字段的数据值(字段间用逗号隔开)；update test1 set user_name='tang seng',score=78 where id=1;

    d. 对某一字段批量增加数据(从别的表中的数据中拷贝过来) ：

    e.将一表中的字段数据插入另一个表中：

  若是没有数据则插入：

insert into test3 select * from test1 where `user_name` in(select user_name from test3); ==>插入test1和test3中user_name相同的数据，全部字段插入到test3中

insert into test3(score) select score from test1 where test1.user_name='sun wu kong';  ==》只插入score的数据

对已经存有数据则更新(子查询,先将要插入的表的数据查出，再作为条件插入到目标表中)：

update test3 set score=(select score from test1 where test1.user_name=test3.user_name);

     f. 将字段中某一列的值加1：update test1 set score=score+1;

     g. 对于datetime格式的字段，date格式是‘YYYY-MM-DD’, 而datetime是‘YYYY-MM-DD HH:M:SS’格式，用insert增加数据如下：要加上单引号才可以插入

insert into user_kills(id,user_id,timestr,kills) values(9,3,'2013-02-7 00:00:00',17);

4.更新即修改

     a.更新原有字段的长度：alter table test1_equipment modify column arms varchar(30);   ===》只能修改单个字段

5. 子查询

MySqL开发技巧

1.join连接

  (1) 内连接 inner join, 取两个表的交集，即取两个表中相同的数据存入新表中

eg:select a.* from test1 a inner join test2 b ON a.user_name=b.user_name;  ===>取两张表中user_name相同的表的数据，即内连接，取交集

 （2）外连接分左外连接(左连接)，全外连接(全连接)，右外连接(右连接)

左连接：以左边表为基础，返回的是左表的符合标准的记录

eg: 取两个表中的数据，且去除交集部分，如图2

select a.* ,b.user_name from test1 a left join test2 b ON a.user_name=b.user_name where b.user_name IS NULL;  ==>不会显示A,B交集的数据

select a.*,b.user_name from test1 a left join test2 b ON a.user_name=b.user_name where b.user_name ISNOT NULL; ==>显示两个表中的交集的数据，但显示的是左表的数据     

     取两个表中的数据，若没有加条件，则会把满足条件的左表全部显示出来，但是B表的数据如果没有则为空。会把交集的数据一起显示出来

select a.* ,b.user_name from test1 a left join test2 b ON a.user_name=b.user_name;

     右连接：与左连接相反，以右表为基础，显示的结果是显示右表中的数据

eg:

select b.*,a.user_name from test1 a right join test2 b ON a.user_name=b.user_name wherea.user_name IS NOT NULL; ===>以test2为右表，right join前面的是test1,故test1是左表。所以显示的是以test2中的数据.

      全连接：A,B两张表的数据都显示。还可以显示A,B两张表中去除交集部分的数据

      

select b.*,a.user_name from test1 a full join test2 b ON a.user_name=b.user_name;  ==》会报1064错误，因为mysql中不支持full join

            技巧1：mysql中不支持全连接，其它的支持 。故可以左连接，右连接后用union all将左连接和右接连的数据接起来

    eg:   

select a.*,b.user_name from test1 a left join test2 b ON a.user_name=b.user_name
union all
select b.*,a.user_name from test1 a right join test2 b ON a.user_name=b.user_name        

      交叉连接：cross join，一般是不会用到迪卡尔集的

交叉连接(cross join),又称笛卡尔连接或者叉乘，如果A和B是两个集合，它们的交叉连接就记为：AxB. 假如A表5条数据，B表4条数据，用cross join会得到5*4=20条数据，且用cross join是没有ON连接条件的

select a.user_name,a.id,b.user_name from test1 a cross join test2 b;

技巧2：mysql中不支持from中的子查询更新自身表，其它像oracle等是可以的，我们可以把子查询放在from 之前。用join连接

eg:

update test1 set over='ji tian da sheng' where test1.user_name in(
select b.user_name from test1 a inner join test2 b on a.user_name=b.user_name); ===》会报错

改成如下：用join连接

update test1 a join(
select b.user_name from test1 a inner join test2 b on a.user_name=b.user_name
)b on a.user_name=b.user_name set a.over='ji tian da sheng';

   技巧3：使用join优化子查询，下面子查询要遍历test2表中所有数据，若表中数据量大时会很慢

       eg:

select a.user_name,a.over,(select over from test2 b where a.user_name=b.user_name) as over2 from test1 a;

优化后的子查询如下：

select a.user_name,a.over,b.over as over2 from test1 a left join test2 b on a.user_name=b.user_name;

   技巧4：使用join优化聚合子查询(避免子查询)

       eg: 如何查询出4人组中打怪最多的日期

select a.user_name,b.timestr,b,kiis from test1 a join user_kills b on a.id=b.user_id where b.kills=(
select max(c.kills) from user_kills c where c.user_id=b.user_id)

优化后的子查询如下：

                 关联两次user_kills表，是因为第一个关联是为了得到b.timestr,b.kills字段，第二个关联是为了用于having来获得max(c.kills)

select a.user_name,b.timestr,b.kiis from user1 a 

join user_kills b on a.id=b.user_id
join user_kills c on c.user_id=b.user_id
group by a.user_name,b.timestr,b.kills
having b.kills=max(c.kills);

    技巧5：实现分组选择，场景：例如imooc中最受欢迎的课程排名。一次性使用聚合分类，而不是每个分类取一次数值。

eg: 实现唐僧4人组每人打怪最多的前两条记录

下面是使用分片，ROW_NUMBER分片，按user_name分组且以b.kills排序分片，分片后会产生一个字段cnt, 且用with 存在tmp临时表中。取临时表tmp的值cnt<=2是取最多两条记录。

with tmp as(
select a.user_name,b.timestr,b.kills,ROW_NUMBER() over(partition by a.user_name order by b.kills) cnt
FROM test1 a join user_kills on a.id=b.`user_id`)
select * from tmp where cnt <=2;        ====》mysql中不能使用分片，oracle等其它中可以使用分片

修改后的mysql语句如下：

select d.user_name,c.timestr,kills 
from (
select user_id,timestr,kills,(select count(*) from user_kills b where
b.user_id=a.user_id and a.kills<=b.kills) AS cnt
from user_kills a
group by user_id,timestr,kills) c
join test1 d on c.user_id=d.id
where cnt<=2