单表查询练习

题目

-- CREATE TABLE liuyuan(
-- id int not null auto_increment PRIMARY key,
-- title VARCHAR(32) not null ,
-- auther VARCHAR(16),
-- addtime datetime not null,
-- content text not null,
-- isdelete char(1) not null DEFAULT 0
-- )ENGINE=INNODB DEFAULT CHARSET=utf8
-- alter TABLE liuyuan add status CHAR(1) DEFAULT 0;
-- alter TABLE liuyuan MODIFY auther VARCHAR(16) not null DEFAULT 'youku';
-- alter table liuyuan DROP isdelete
-- insert into liuyuan VALUES(6,'介绍','大雄','201-02-14 09:59:37','哥不是一批好马，但也不是一头普通的毛驴',0);
-- insert into liuyuan VALUES(7,'叮当猫','熊熊','208-03-14 09:59:37','你牙缝里有韭菜，抠出来贼哥吃了',0);
-- insert into liuyuan VALUES(8,'花花','苗苗','17-08-4 09:59:37','苗苗问花花:卖萌是褒义词还是贬义词',0);
-- insert into liuyuan VALUES(9,'霞哥','大雄','26-12-14 09:59:37','斗战色佛',0);
-- insert into liuyuan VALUES(10,'晨晨','逗比','205-07-14 09:59:37','你笑起来像一朵菊花,菊花残,man腚伤',0);
-- update liuyuan set auther='admin' WHERE id>3;
-- delete from liuyuan where id=4;
-- SELECT content,id from liuyuan where (id>1 and id<7) ORDER BY addtime DESC ;
-- select * from liuyuan order by addtime DESC;
-- select content from liuyuan WHERE auther = '大雄';
-- select auther,count(auther) as i from liuyuan GROUP BY auther ORDER By i asc;
-- UPDATE liuyuan set auther='doudou' WHERE (liuyuan.id=8 or liuyuan.id =9);
-- SELECT content,addtime from liuyuan ORDER BY addtime desc LIMIT 0,3;
-- select content,addtime from liuyuan where content like '%a%' ORDER BY addtime ASC;
-- delete from 
-- 			liuyuan 
-- where 
-- 			auther in 
-- (SELECT * from (
-- SELECT 
-- 		auther 
-- from 
-- 		liuyuan 
-- GROUP BY 
-- 		auther 
-- HAVING 
-- 		count(auther)>1)
-- 		b)  
-- and id not in 
-- (select * from (
-- select 
-- 		min(id) 
-- from 
-- 		liuyuan 
-- GROUP BY 
-- 		auther 
-- HAVING 
-- 		count(auther)>1) 
-- c)
-- 
-- 

转载于:https://www.cnblogs.com/Lucifer77/p/10293532.html