create table players(    playerno id,    name varchar,    sex char,    join date,    town varchar,)create table pentity(    id int,    playerno,    amount)按照球员级别分组，并统计总数（球员总数）select count(*) as number    case        when joined < 1980 then ‘old‘        when joined <=1983 1980 then ‘youny‘        else ‘ok‘ end as levelfrom playersgroup by levels;当性别为 F时，为 Feman,否则为 Man:select name,        case sex        when ‘F‘ then ‘Feman‘        esle ‘Man‘ end as sexfrom players;或者为select name,case    when sex = ‘F‘ then ‘Feman‘    when sex = ‘M‘ then ‘Man‘    end as sexfrom players;根据 town中，查询球员号码,同时如果 town为stratford,则当加入的年份大于1980，表示为 young,当加入年份小于1980时,为old,如果 town 为 elthon,则当加入 年份 大于 1980，表示为 ‘xxxx‘,否则为 ‘yyyy‘select playerno,        case town        when ‘stratford‘ then            case                 when join > 1980 then young                when join < 1980 then old            end         when ‘elthon‘ then            case                when join > 1980 then ‘xxxxx‘                when join < 1980 then ‘yyyyy‘            end        end as agefrom players;查找号码，并在名字前面加入首字母和点号，并且是以 s 结尾的球员数据select playerno,concat(left(name,1), ‘.‘, name) as namefrom playerswhere right(name,1) = ‘s‘;找到至少引发两次罚款且都不低于 25元的每个球员的号码 select playerno              1from players               2where amount > 25          3group by playerno          4 having count(*) > 1        5order by playerno asc;     6执行顺序为：2--3--4--5--6--1union 联合查询查询队长号和罚款的球员号select playerno from players   1union                          2select playerno from pentity   3执行顺序为 1---3,然后 是2即合并 1,3并重复项查询号码小于10且为男的球员select * from players where playerno < 10 and sex = ‘M‘或者 select * from (select * from players) as pp where pp.sex = ‘M‘其中嵌套用法中得有别名，而且以后的子查询也得用别名表示高度嵌套：号码 大于10且小于100，加入时间表大于1980且为男的球员编号1.号码 大于 10  select * from players where playerno > 10;2.号码小于 100  select * from players where playerno < 100;3.加入时间大于 1980  select * from players where join > 1980;4.性别为男的  select * from players where sex = ‘M‘  合并则：  select playerno  from (select *             from (select *                 from (select *                         from (select * from players where sex = ‘M‘) as MM                        where MM.join > 1980) AS JJ                 where JJ.playerno < 100) AS PP             where PP.playerno > 10) AS LAST;查询与编号为 100的球员且城市相同的球员select * from playerswhere players.sex = (select sex from players where playerno = 100)ane town = (select town from players where playerno = 100);或者：select * from (sex,town) = (select sex,town from player where playerno = 100)获得与 27号球员住同一城市的球员名和编号select p1.playerno,p1.namefrom players p1,players p2where p1.town = p2.townand p2.playerno = 27and p1.playerno <> 27当联接的列名相同时可用select * from players join team on players.playerno = team.playerno或者 select *from playersjoin team using(playerno)