/*第二章 Select语句:常用元素*/
/*
1.为一个特定的表添加一个不正确的日期并显示结果。
insert into incorrect_date values ('2009-13-12');
注意这个13超过了月份,这里有一个检测的机制,不给插入数据,需把月份改回到1-12
/*2.对于赢得的局数等于输掉的局数加2的每场比赛,获得比赛号码以及赢得的局数和输掉的局数之间的差值。*/
/*3.对于每个球队得到编号和分级。*/
/*4.对于每个球队,获得编号和分级,并使用全名。
其实就是来一个别名。
*/
/*5.对于每次罚款,获取支付编号和以分为单位的罚款数额。*/
/*6.从matches表中获得一些数据。*/
/*7.把所有罚款按照罚款金额以美分为单位分组,并且按照美分的数目来排序。*/
/*8.获取球员编号大于6的编号,名字和初始字母,使用完整的列指定。*/
/*简单列表示*/
select playerno,name,initials
from players
where playerno>6;
/*9.创建用户变量playerno,并用7来初始化它。
说明:MySQL用户变量是基于会话变量实现的, 可以暂存值, 并传递给同一连接里的下一条sql使用的变量,
当客户端连接退出时,变量会被释放。
另外,注意,用户变量前只有1个@, 2个@是用于存取系统变量的.
*/
/*10.获取球员号码小于已经创建的用户变量playerno的值的所有球员的姓,居住城市也邮政编码。*/
/*11.查询playerno变量的值。*/
/*12.打开SQL_WARNINGS变量。*/
/*13.对于当前会话,把系统变量SQL_SELECT_LIMIT的值设置为10.这个变量决定了一条select语句结果中最大的行数。*/
/*注意:session放在系统变量之前,说明仅对当前会话有用,全局变量仍然是原来的值。获得系统变量sql_select_limit的方式为:*/
/*14.获取当前系统的日期,时间,时间+日期以及当前用户。*/
/*15.case表达式。获取1980年后加入俱乐部的每个球员号码,性别,名字。性别必须显示为’Female’或’Male’*/
/*16.在一条select语句中嵌套使用case。*/
/*17.对每个球员找到球员号码,他加入俱乐部的年份和球员的年龄组。*/
/*18.对于每个球员,找出球员号码,加入俱乐部的年份,他居住的城市以及分级。*/
/*19.获取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);
*/
/*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(没意义)。
*/
/*22.对于所有号码小于10的球员,获取球员号码,他们出生的那天是星期几,他们出生所在的月份以及他们出生那天是该年的多少天。*/
/*23.对于出生在Saturday的每一个球员,获取号码,出生日期和出生后7天的日期。*/
/*24.哪个球员在俱乐部某个职位超过500天?*/
/*25.对于每一笔大于50的罚款,获取支付编号。*/
/*26.对于居住在Inglewood的每个球员,获取他们的名字和生日作为一个完整的字符值。*/
/*27.把编号为2的球员的联盟会员号码改为空值。*/
/*28.对于每个球队,获取球队号码,后面跟着一个空值。*/
/*29.对于胜出局数大于或等于输掉的局数乘以2的每场比赛,获得比赛号码,胜出局数和输掉局数。*/
/*
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;