在同一张表里,可能同时进行insert和select的操作;进行delete和select的操作;或者update和select的操作。
现在以Mysql5.1为例。
表的定义:
再往表写几天测试数据:
insert和select组合操作:
delete和select组合操作,这个时候也必须要用到表的别名:
update和select组合操作,这个时候要用到表的别名:
分组查询
有时候在一个表保持几个分类,然后想取每个分类的前几条数据,SQL就是这样写的:
现在以Mysql5.1为例。
表的定义:
CREATE TABLE `people` (
`id` varchar(50) NOT NULL,
`name` varchar(200) DEFAULT NULL,
`nindex` int(9) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
再往表写几天测试数据:
insert into `people` (`id`, `name`, `nindex`) values('1000','java','1');
insert into `people` (`id`, `name`, `nindex`) values('1001','csharp','2');
insert into `people` (`id`, `name`, `nindex`) values('1003','python','3');
insert into `people` (`id`, `name`, `nindex`) values('1005','javascript','3');
insert和select组合操作:
insert into people select max(id)+1,name,nindex from people limit 1
delete和select组合操作,这个时候也必须要用到表的别名:
delete from people where id in(select max(p.id) from (select id from people) as p)
update和select组合操作,这个时候要用到表的别名:
update people set nindex=(select max(p.nindex)+1 from (select nindex from people) as p) where id = '1001'
分组查询
有时候在一个表保持几个分类,然后想取每个分类的前几条数据,SQL就是这样写的:
select id,part, name, sell from test a where
(select count(1) from test b where b.part=a.part and b.id!=a.id
and b.name!=a.name and b.sell>a.sell)<2 order by part, sell desc;