sql语句查询

create table players
(
    playerno int,
    name varchar,
    sex char,
    join date,
    town varchar,
    birthday date
)

create table pentity
(
    id int,
    playerno int,
    amount float
)
按照球员级别分组，并统计总数（球员总数）
select count(*) as number,
    case
        when joined < 1980 then 'old'
        when joined <=1983 then 'youny'
        else 'ok' end as levels
from players
group by levels;

当性别为 F时，为 Feman,否则为 Man:
select name,
        case sex
        when 'F' then 'Feman'
        esle 'Man' end as sex
from players;
或者为
select name,
case
    when sex = 'F' then 'Feman'
    when sex = 'M' then 'Man'
    end as sex
from 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 age
from players;

查找号码，并在名字前面加入首字母和点号，并且是以 s 结尾的球员数据
select playerno,concat(left(name,1), '.', name) as name
from players
where right(name,1) = 's';

找到至少引发两次罚款且都不低于 25元的每个球员的号码 
select playerno              1
from players               2
where amount > 25          3
group by playerno          4 
having count(*) > 1        5
order by playerno asc;     6
执行顺序为：2--3--4--5--6--1

union 联合查询
查询队长号和罚款的球员号
select playerno from players   1
union                          2
select playerno from pentity   3
执行顺序为 1---3,然后 是2即合并 1,3并去重复项

查询号码小于10且为男的球员
select * from players where playerno < 10 and sex = 'M'
或者 
select * from (select * from players where playerno < 10) 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 players
where players.sex = (select sex from players where playerno = 100)
and 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.name
from players p1,players p2
where p1.town = p2.town
and p2.playerno = 27
and p1.playerno <> 27

当联接的列名相同时可用
select * 
from players 
join team on players.playerno = team.playerno
或者 
select *
from players
join team using(playerno)