常用sql语句总结

1.创建一张与已经存在表的结构以及数据一样的表:

   create table test1 as select * from student;

2.创建一张与已经存在表的结构一样的空表:

   create table test1 as select * from student where 1=2;

3.俩张表的结构一样将其中一张表的数据导入另一张表:

   insert into test2 select * from test1;

4.将另一张表的某几列数据导入到另一张表中对应的列:

   insert into test3(sid,sname) select sid,sname from test2;

5.插入数据:

   insert into student(sid,sname,sage) values('1','小李',27);

6.修改数据:

   update student set name='小王' ,sage='28' where  sid='1';

7.删除数据:

   delete from student where sid='1';

8.删除表中所有的数据并且释放空间,表的索引将重新设置成初始大小:

   truncate table student;

9.删除表结构和数据:

   drop table student;

10.表中添加列并添加注释:

   alter table student add address varchar(100) comment '地址';

11.修改列的名字,并添加列注释:

   alter table tb_vehicle_model change column  tb_vehicle_brand_id  vehicle_brand  varchar(32);
   alter table tb_vehicle_model modify column vehicle_brand varchar(32) comment '车品牌'; 

12.修改列的数据类型:

   alter table tb_maintence_station  MODIFY column  id varchar(32);

13.删除列:

   alter table th_push_message drop is_read;

14.根据子查询更新:

   update tb_vehicle SET status = '1' where id in(select a.id from(select id from tb_vehicle where status = '0') a);

15.主表中存在但从表中不存在数据,但是要查询出具体哪个主表的数据不在从表中:

   select * from student a left join student_cource b on a.sid=b.sid where b.cid is null;

16.获取多少条到多少条数据(mysql写法,第一个参数为起始位置,第二个参数为获取的条数):

   select * from student_cource limit 0,5;

17.获取多少条到多少条数据(oracle写法):

   select * from (select rownum rn,cn.* from student cn) where rn BETWEEN 10 and 20;

18.汇总语句:

    select
       a.sname,
       sum(case when b.courcename='语文' then c.score else null end) 语文
       sum(case when b.courcename='数学' then c.score else null end) 数学,
       sum(case when b.courcename='英语' then c.score else null end) 英语,
       sum(case when b.courcename='物理' then c.score else null end) 物理,
       sum(case when b.courcename='化学' then c.score else null end) 化学,
       sum(c.score) total,
       round(avg(c.score),2) avg
   from student a,cource b,student_cource c where a.sid=c.sid and b.cid=c.cid group by a.sname;

19.删除表中姓名重复的记录(单个字段),并且保留ID最小的记录( mysql写法):

delete from  test1 where 

sname in ( select  b.sname from  (select sname from test1 group by sname having count(sname)>1) b)

and 

sid not in(select  a.sid from  (select min(sid) sid from test1 group by sname having count(sname)>1) a );

20.删除表中姓名重复的记录(单个字段),并且保留ID最小的记录 (oracle写法):

delete from test1 where 

sname in (select t.sname from test1 t group by  t.sname having count(t.sname)>1 )

and

 sid not in  (select min(b.sid) from test1  b group by b.sname having count(b.sname)>1 );