第二章 Select语句：常用元素

/*第二章 Select语句：常用元素*/

/*
1.为一个特定的表添加一个不正确的日期并显示结果。
insert into incorrect_date values ('2009-13-12');
注意这个13超过了月份，这里有一个检测的机制，不给插入数据，需把月份改回到1-12

*/

use tennis;

create table incorrect_date(column1 date);

insert into incorrect_date values ('2009-10-12');

select column1 from incorrect_date;

/*2.对于赢得的局数等于输掉的局数加2的每场比赛，获得比赛号码以及赢得的局数和输掉的局数之间的差值。*/

select matchno,won-lost as difference
from matches
where won-lost=2;

/*3.对于每个球队得到编号和分级。*/

select teamno,division
from teams;

/*4.对于每个球队，获得编号和分级，并使用全名。
其实就是来一个别名。
*/

select teamno as team_number,division as division_of_team
from teams;

/*5.对于每次罚款，获取支付编号和以分为单位的罚款数额。*/

select paymentno , amount  from penalties;

select paymentno,amount*100 as cents
from penalties;

/*6.从matches表中获得一些数据。*/

select matchno as primaryKey,80 as eighty,
won-lost as difference,time('23:59:59') as 'Almost_midNight','Text' as Text
from matches
where matchno<=4;

/*7.把所有罚款按照罚款金额以美分为单位分组，并且按照美分的数目来排序。*/

select amount*100 as cents
from penalties
group by cents
order by cents;

/*8.获取球员编号大于6的编号，名字和初始字母，使用完整的列指定。*/

select players.playerno,players.name,players.initials
from players
where players.playerno>6;

/*简单列表示*/
select playerno,name,initials
from players
where playerno>6;


/*9.创建用户变量playerno，并用7来初始化它。
说明：MySQL用户变量是基于会话变量实现的, 可以暂存值, 并传递给同一连接里的下一条sql使用的变量，
当客户端连接退出时,变量会被释放。
另外,注意,用户变量前只有1个@, 2个@是用于存取系统变量的.
*/

set @playerno=7;

/*10.获取球员号码小于已经创建的用户变量playerno的值的所有球员的姓，居住城市也邮政编码。*/

select players.name,town,postcode
from players
where playerno<@playerno;

/*11.查询playerno变量的值。*/

select @playerno;

/*12.打开SQL_WARNINGS变量。*/

set @@SQL_WARNINGS=TRUE;

/*13.对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为10.这个变量决定了一条select语句结果中最大的行数。*/

set @@session.SQL_SELECT_LIMIT=10;

/*注意：session放在系统变量之前，说明仅对当前会话有用，全局变量仍然是原来的值。获得系统变量sql_select_limit的方式为：*/

select @@global.sql_select_limit;

/*14.获取当前系统的日期，时间，时间+日期以及当前用户。*/

select current_date;

select current_time;

select current_timestamp;

select current_user;

/*15.case表达式。获取1980年后加入俱乐部的每个球员号码，性别，名字。性别必须显示为’Female’或’Male’*/

select playerno,name,


  case sex
           when 'F' then 'Female'
           when 'M' then 'Male'
  end as sex
  
  from players
 where joined>1980;

/*16.在一条select语句中嵌套使用case。*/

select playerno,town,birth_date,town  from players;

select playerno,town,birth_date,
 case town
 	when 'Stratford' then 'ST'
 	when 'Plymouth' then 'PM'
 	when 'Inglewood' then 'IW'
 	else 'OT'
 end as Position,
 
 case town
 	when 'Stratford' then
 		 case birth_date
          			when '1948-09-01' then 'Old Stratford'
         			 else 'Young Stratford'
		 end
 	when 'Inglewood' then
 		case birth_date
			when  '1962-07-08' then 'Old Ingelwood'
		 	else 'Young Inglewood' 
		end
 	else 'Rest'
 end as type
 
 from players;

/*17.对每个球员找到球员号码，他加入俱乐部的年份和球员的年龄组。*/

select playerno,joined,


case
when joined<1980 then 'senior'
	when joined<1983 then 'jubior'
	else 'children'
end as age_group


from players
order by joined;

/*18.对于每个球员，找出球员号码，加入俱乐部的年份，他居住的城市以及分级。*/

desc players;


select playerno,joined ,town from players group by town  order by town;

select playerno,joined,town,


case
	when joined>=1980 and joined<1983 then 'senior'
	when town='Eltham' then 'Elthammers'
	when playerno<10 then 'first_members'
	else 'Rest'
end as level


from players;

/*19.获取1980年以后每笔罚款的支付号码以及年份。*/

desc penalties;

select paymentno,payment_date from penalties where payment_date>1980;

/*注意年份的获取*/


select paymentno,year(payment_date)
from penalties
where year(payment_date)>1980;

/*20.对于姓氏以大写字母B开头的每个球员，获取其号码以及名字的首字母，随后跟一个小数点和姓。

concat函数返回结果为连接参数产生的字符串，该函数可以可以连接一个或者多个字符串，极具实用价值
常用的mysql截取函数有：left(), right(), substring(), substring_index();
1.左截取left(str, length)
2.右截取right(str, length)
3.substring(str, pos); substring(str, pos, len)
4.按关键字截取字符串  substring_index(str,delim,count)
说明：
substring（被截取字段，从第几位开始截取） 
substring（被截取字段，从第几位开始截取，截取长度） 
substring_index（被截取字段，关键字，关键字出现的次数） 
注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度

select playerno,name from players ;
select left('www.localhost.com',3);
select right('www.localhost.com',3);
select substring('www.localhost.com',3);
select substring('www.localhost.com',3,3);
select substring('www.localhost.com',3,-1);
select substring_index('test.blog.jb51.net','.',2);
*/

desc players;

select playerno,concat(left(initials,1),'.',name) as full_name
from players
where left(name,1)='B';

/*21.对于居住在Stratford的每个球员，获取其名字，姓氏和联盟会员号码，如果联盟会员号码为空，将其显示为1。
coalesce()解释：返回参数中的第一个非空表达式（从左向右）； 
mysql中没有nvl()函数, 我们用coalesce()来代替。
coalesce相比nvl优点是，coalesce中参数可以有多个，而nvl()中参数就只有两个。
当然，在oracle中也可以使用 case when....then....else......end
select coalesce(a,b,c);  
如果a==null,则选择b；
如果b==null,则选择c；
如果a!=null,则选择a；
如果a b c 都为null ，则返回为null（没意义）。
*/

select initials,name,coalesce(leagueno,’1’) as league
from players
where town=’Stratford’;

/*22.对于所有号码小于10的球员，获取球员号码，他们出生的那天是星期几，他们出生所在的月份以及他们出生那天是该年的多少天。*/

select playerno,dayname(birth_date),monthname(birth_date),dayofyear(birth_date)
from players
where playerno<10;

/*23.对于出生在Saturday的每一个球员，获取号码，出生日期和出生后7天的日期。*/

select playerno,birth_date,adddate(birth_date,interval 7 day) as add7days
from players where dayname(birth_date)='Saturday';

/*24.哪个球员在俱乐部某个职位超过500天？*/

select playerno,begin_date,end_date,datediff(end_date,begin_date)
from committee_members
where datediff(end_date,begin_date)>500
or(end_date is null 
and datediff(current_date,begin_date)>500)
order by playerno;

select playerno,begin_date,end_date,
datediff(coalesce(end_date,current_date),begin_date) as diff
from committee_members
where datediff(coalesce(end_date,current_date),begin_date)>500
order by playerno;

/*25.对于每一笔大于50的罚款，获取支付编号。*/

select paymentno
from penalties
where amount>50;

/*26.对于居住在Inglewood的每个球员，获取他们的名字和生日作为一个完整的字符值。*/

select concat(rtrim(name),cast(birth_date as char(10)))
from players
where town='Inglewood';

/*27.把编号为2的球员的联盟会员号码改为空值。*/

update players
set leagueno=NULL
where playerno=2;


select * from players;

/*28.对于每个球队，获取球队号码，后面跟着一个空值。*/

select teamno,cast(NULL as char)
from teams;

/*29.对于胜出局数大于或等于输掉的局数乘以2的每场比赛，获得比赛号码，胜出局数和输掉局数。*/

select matchno,won,lost
from matches
where won>=lost*2;