版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、mysql 开发者sql 权威指南sql for mysql developers a comprehensive tutorial and reference 整理:侯杰mysql 开发范例- 1 - 第一章 sql 概要1.引入一个名为 booksql 的新用户,他的密码是booksqlpw 。create user booksql localhost identified by booksqlpwcreate user houjie410782 localhost identified by 630529 2.给 sql 用户 booksql 创建和操作表的权限。grant all pr
2、ivileges on *.* to booksql localhost with grant option; grat all privileges on *.* to houjie410782 localhost with grant option; 3.创建数据库 tennis。create database tennis; 4.指定 tennis 为当前数据库。use tennis; 5.创建表 players ,teams,matches ,penalties ,committee_members 。create table players (playerno integer not
3、 null, name char(15) not null, initials char(3) not null, birth_date date, sex char(1) not null, joined smallint not null, street varchar(30) not null, houseno char(4), postcode char(6), town varchar(30) not null, phoneno char(13), leagueno char(4), primary key (playerno) ) create table teams( teamn
4、o integer not null, playerno integer not null, division char(6) not null, primary key (teamno) ) create table matches( matchno integer not null, teamno integer not null, playerno integer not null, won smallint not null, lost smallint not null, primary key (matchno) ) create table penalties( paymentn
5、o integer not null, playerno integer not null, payment_date date not null, amount decimal(7,2) not null, primary key (paymentno) mysql 开发范例- 2 - ) create table committee_members( playerno integer not null, begin_date date not null, end_date date, position char(20), primary key (playerno,begin_date)
6、) 6.为表中填充数据。insert into players values(2,everett,r,1948-09-01,m,1975,stoney road,43,3575nh,stratford,070-237893,2411); insert into players values(7,wise,gws,1963-05-11,m,1981,edgecombe way,39,9758vb,stratford,070-347689,null); insert into players values(8,newcastle,b,1962-07-08,f,1980,station road,4
7、,6584r0,inglewood,070-458458,2983); insert into players values(27,collins,dd,1964-12-28,f,1983,long drive,804,8457dk,eltham,079-234857,2513); insert into players values(28,collins,c,1963-06-22,f,1983,old mian road,10,1294qk,midhurst,071-659599,null); insert into players values(39,bishop,d,1956-10-29
8、,m,1980,eaton square,78,9629cd,stratford,070-393435,null); insert into players values(44,baker,e,1963-01-09,m,1980,lewis street,23,4444lj,inglewood,070-368753,1124); insert into players values(57,brown,m,1971-08-17,m,1985,edgecombe way,16,4377cb,stratford,070-473458,6409); insert into players values
9、(83,hope,pk,1956-11-11,m,1982,magdalene road,16a,1812up,stratford,070-353548,1608); insert into players values(95,miller,p,1963-05-14,m,1972,high street,33a,5746op,douglas,070-867564,null); insert into players values(100,parmanter,p,1963-02-28,m,1979,haseltine lane,80,1234kk,stratford,070-494593,652
10、4); insert into players values(104,moorman,d,1970-05-10,f,1984,stout street,65,9437a0,eltham,079-987571,7060); insert into players values(112,bailey,ip,1963-10-01,f,1984,vixen road,8,6392lk,plymouth,010-548745,1319); insert into players values(6,parmenter,r,1964-06-25,m,1977,haseltine lane,80,1234kk
11、,stratford,070-476537,8467); insert into teams values(1,6,first); insert into teams values(2,27,second); insert into matches values(1,1,6,3,1); insert into matches values(2,1,6,2,3); insert into matches values(3,1,6,3,0); insert into matches values(4,1,44,3,2); insert into matches values(5,1,83,0,3)
12、; insert into matches values(6,1,2,1,3); insert into matches values(7,1,2,1,3); insert into matches values(8,1,8,0,3); insert into matches values(9,2,27,3,2); mysql 开发范例- 3 - insert into matches values(10,2,104,3,2); insert into matches values(11,2,112,2,3); insert into matches values(12,2,112,1,3);
13、 insert into matches values(13,2,8,0,3); insert into penalties values(1,6,1980-12-08,100.00); insert into penalties values(2,44,1981-05-05,75.00); insert into penalties values(3,27,1983-09-10,100.00); insert into penalties values(4,104,1984-12-08,50.00); insert into penalties values(5,44,1980-12-08,
14、25.00); insert into penalties values(6,8,1980-12-08,25.00); insert into penalties values(7,44,1982-12-30,30.00); insert into penalties values(8,27,1984-11-12,75.00); insert into committee_members values(2,1990-01-01,1992-12-31,chairman); insert into committee_members values(2,1994-01-01,null,member)
15、; insert into committee_members values(6,1990-01-01,1990-12-31,secretary); insert into committee_members values(6,1991-01-01,1992-12-31,member); insert into committee_members values(6,1992-01-01,1993-12-31,treasurer); insert into committee_members values(6,1993-01-01,null,chairman); insert into comm
16、ittee_members values(8,1990-01-01,1990-12-31,treasurer); insert into committee_members values(8,1991-01-01,1991-12-31,secretary); insert into committee_members values(8,1993-01-01,1993-12-31,member); insert into committee_members values(8,1994-01-01,null,member); insert into committee_members values
17、(27,1990-01-01,1990-12-31,member); insert into committee_members values(27,1991-01-01,1991-12-31,treasurer); insert into committee_members values(27,1993-01-01,1993-12-31,treasurer); insert into committee_members values(57,1992-01-01,1992-12-31,secretary); insert into committee_members values(95,199
18、4-01-01,null,treasurer); insert into committee_members values(112,1992-01-01,1992-12-31,member); insert into committee_members values(112,1994-01-01,null,secretary); 7. 获取居住在 stratford 的每个球员的号码、名字和出生日期,按照名字的顺序排列结果。select playerno,name,birth_date from players where town= stratford order by name; 8.获取
19、在 1980 年后加入俱乐部并且居住在stratford 的每个球员号码,按照球员号码排序。select playerno from players where town= stratford and joined1980 order by playerno; 9.获取有关罚款的所有信息。select * from penalties; 10.121 的 33 倍是多少?select 33*121; 11.获取 44 号球员的罚款数额,并把该球员的金额该为200 美元。select playerno,amount from penalties where playerno=44; mysql
20、开发范例- 4 - update penalties set amount=200 where playerno=44; 12.删除 penalties 表中罚款金额大于100 美元的每一笔罚款。delete from penalties where amount100; 13.在 penalties 表的 amount 列上创建一个索引。create index penalties_amount on penalties(amount); 14.创建一个视图,记录每场比赛赢得的局数和输掉的局数。create view number_sets(matchno,difference) as se
21、lece matchno,abs(win-lost) from matches; mysql 开发范例- 5 - 第二章 select语句:常用元素1.为一个特定的表添加一个不正确的日期并显示结果。create table incorrect_date(column1 date); insert into incorrect_date values ( 2009-13-12 ); select column1 from incorrect_date; 2.对于赢得的局数等于输掉的局数加2 的每场比赛,获得比赛号码以及赢得的局数和输掉的局数之间的差值。select matchno,won-los
22、t 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*100 as cents from penalties; 6.从 matches 表中获得一些数据。select m
23、atchno as primarykey,80 as eighty, won-lost as difference,time(23:59:59) as almost_midnight,text as text from matches where matchno6; 9.创建用户变量 playerno ,并用 7 来初始化它。set playerno=7; 10.获取球员号码小于已经创建的用户变量playerno 的值的所有球员的姓,居住城市也邮政编码。select ,town,postcode from players where playerno1980; 16.在
24、一条 select 语句中嵌套使用 case。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
25、 when 1962-07-08 then old ingelwood else young inglewood end else rest end as type from players; 17.对每个球员找到球员号码,他加入俱乐部的年份和球员的年龄组。select playerno,joined, case when joined1980 then senior when joined=1980 and joined1983 then senior when town= eltham then elthammers mysql 开发范例- 7 - when playerno1980; 2
26、0.对于姓氏以大写字母b 开头的每个球员,获取其号码以及名字的首字母,随后跟一个小数点和姓。select playerno,concat(left(initials,1), . ,name) as full_name from players where left(name,1)= b ; 21.对于居住在 stratford 的每个球员,获取其名字, 姓氏和联盟会员号码, 如果联盟会员号码为空,将其显示为 1。select initials,name,coalesce(leagueno, 1 ) as league from players where town= stratford ; 注
27、意: coalesce(leagueno, 1 )的意思是:如果 leagueno 非空,就取当前 leagueno ,否则取 1. 22.对于所有号码小于 10 的球员,获取球员号码, 他们出生的那天是星期几, 他们出生所在的月份以及他们出生那天是该年的多少天。select playerno,dayname(birth_date),monthname(birth_date),dayofyear(birth_date) from players where playerno500 or(end_date is null and datediff(current_date,begin_date)
28、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 amoun
29、t50; 26.对于居住在 inglewood 的每个球员,获取他们的名字和生日作为一个完整的字符值。select concat(rtrim(name),cast(birth_date as char(10) from players where town= inglewood ; 27.把编号为 2 的球员的联盟会员号码改为空值。update players mysql 开发范例- 8 - set leagueno=null where playerno=2; 28.对于每个球队,获取球队号码,后面跟着一个空值。select teamno,cast(null as char) from te
30、ams; 29.对于胜出局数大于或等于输掉的局数乘以2 的每场比赛, 获得比赛号码, 胜出局数和输掉局数。select matchno,won,lost from matches where won=lost*2; 30.把 50 左移 2 位。select 502; 31.把二进制的 11 向左移动三位。select b 11 1)5; 或者这样写:select paymentno,payment_date, adddate(payment_date,interval 7 day) as add7days from penalties where paymentno5; 39.获取在 198
31、2 年圣诞节( 12 月 25 日)以及新年前夕支付的罚款。select paymentno,payment_date from penalties where payment_date= 1982-12-25 and payment_dateadddate( 1982-12-25 ,interval 7 day); mysql 开发范例- 9 - 40.在日期直接量 2004-13-12 上增加一天,接下来显示错误信息。select adddate( 2004-13-12 ,interval 1 day); show warnings; 41.创建一个 matches 表的特殊形式 matc
32、hes_special, 其中包含比赛进行的日期,开始时间和结束时间。create table matches_special( matchno integer not null, teamno integer not null, playerno integer not null, won smallint not null, lost smallint not null, start_date date not null, start_time time not null, end_date date not null, end_time time not null, primary ke
33、y(matchno) 插入两组数据:insert into match_special values(1,1,6,3,1, 2004-10-25 , 14:10:12 , 2004-10-25 , 16:50:09 ); insert into match_special values(2,1,44,3,2, 2004-10-29 , 08:30:00 , 2004-10-25 , 10:00:00 ); 42.对于每场比赛,获取其开始时间,并获取开始时间加上8 小时候的时间。select matchno,start_time,addtime(start_time, 08:00:00 ) fr
34、om match_special; 43.获得至少在午夜前 6.5 小时结束的比赛。select matchno,end_time from match_special where addtime(end_time, 06:30:30 )4 from penalties; 47.找出下面两个条件都满足或都不满足的球员,球员的号码小于15, 加入俱乐部的年份晚于1979年。select playerno,joined,playerno1979 from players where (playerno1979); 48.(行表达式 )为 committee_members表添加一个新行。inser
35、t into committee_members values(7+15,current_date,current_date+interval 17 day, member ); 49.获取居住在 stratford 的 haseltine lane 的球员号码和名字。select playerno from players where (town,street)=( stratford , haseltine lane ); 50.找到那些居住在 stratford 的 haseltine lane 的所有球员号码和名字。mysql 开发范例- 10 - select playerno,na
36、me from players where (town,street)=(select stratford , haseltine lane ); 答案通 49 题。mysql 开发范例- 11 - 第三章 select语句、表表达式和子查询1.找到至少引发两次多余25 美元的罚款的每个球员号码,根据球员号码对结果排序。select playerno from players,penalties where amount25 having count(*)1 order by playerno; 2.获得居住在 stratford 的每个球员的号码和联盟会员号码,根据联盟号码排序。select
37、 playerno,leagueno from players where town= stratford order by leagueno; 3.获取球队队长的号码以及引发罚款的球员号码。select playerno from teams union select playerno from penalties; 4.获得编号小于 10 的男性球员号码。select playerno from(select playerno,sex from players where playerno10) as greater10 where playerno1980) as joined1980
38、where sex= m ; 以上语句同:select playerno from players where playerno10 and joined1980 and sex= m ; 6.对于号码小于 60 的每个球员,获取他们加入俱乐部的年份和100 号球员加入俱乐部年份之间的差值。select playerno,joined- (select joined from players where playerno=100) as diff_100 from players where playerno60; 7.获取和 27 号球员出生于同一年的球员号码。select playerno
39、 from players mysql 开发范例- 12 - where year(birth_date)=(select year(birth_date) from players where playerno=27); 8.获取 27 号球员、 44 号球员和 100 号球员的生日作为一行。select (select birth_date from players where playerno=27), (select birth_date from players where playerno=44), (select birth_date from players where pla
40、yerno=100); 9.获取与 100 号球员性别相同且居住在同一城市的球员号码。select playerno from players where (sex,town)=(select sex,town from players where playerno=100); mysql 开发范例- 13 - 第四章 select子句: from 子句1.创建一个数据库 extra,并创建一个 cities 表,插入两行数据。create database extra; use extra; create table cities(cityno integer not null, cityn
41、ame char(20) not null, primary key(cityno); insert into cities values(1, stratford ); insert into cities values(2, inglewood ); 2.在当前数据库为 tennis 的前提下,访问 extra 数据库的 cities 列。select * from extra.cities; 同理,在当前数据库为extra 的情况下,也可以范围tennis 数据库里的所有内容。3.获取球队编号和每个球队队长的名字。select teamno,name from teams,players
42、 where teams.playerno=players.playerno; 4.对每一笔罚款,找出支付编号,罚款数额以及引起罚款的球员号码,名字和首字母。select players.playerno,,players.initials, penalties.paymentno,penalties.amount from players,penalties where players.playerno=penalties.playerno; 5.(使用假名) 对于每一笔罚款, 获得支付编号, 罚款数额,引发罚款球员的号码, 名字和首字母。select pen.pay
43、mentno,pen.amount,p.playerno,,p.initials from players as p,penalties as pen where p.playerno=pen.playerno; 6.获取至少引起一次罚款的队长号码。select t.playerno from teams as t,penalties as pen where t.playerno=pen.playerno; 7.获取至少引起一次罚款的队长号码,删除重复的号码。select distinct t.playerno from teams as t,penalties as pen w
44、here t.playerno=pen.playerno; 8.获取至少参加了一场比赛的球员名字和首字母,注意,一个球员不一定必须出现在matches 表中(他可能整个赛季都因伤缺席)select distinct ,p.initials from players as p,matches as m where p.playerno=m.playerno; 9.对于每一场比赛,获取比赛编号,球员代号,球队编号,球员名字以及参加的球队分级。select m.matchno,p.playerno,m.teamno,,t.division from matches as m,
45、players as p,teams as t where p.playerno=m.playerno and t.teamno=m.teamno; 10.对于球员加入俱乐部当年所引起的每一笔罚款,获得支付编号,球员号码以及支付日期。select pen.paymentno,p.playerno,pen.payment_date from players as p,penalties as pen where pen.playerno=p.playerno and year(pen.payment_date)=p.joined; 11.(必须使用假名的情况)获取比r.parmenter 年龄大
46、的每个球员的号码,在这个例子中,假设名字和首字母的组合是唯一的。mysql 开发范例- 14 - select p.playerno from players as p,players as par where =parmenter and par.initials=r and p.birth_date1920-06-30; 显示表示为:select p.playerno,name,pen.amount from players as p inner join penalties as pen on(p.playerno=pen.playerno) where p.birth_
47、date1920-06-30; 14.对于每个球队,找出球队的号码和队长的名字,分别用隐式表示和显示表示。隐式:select t.teamno, from teams as t,players as p where t.playerno=p.playerno; 显示:select t.teamno, from teams as t inner join players as p on(t.playerno=p.playerno); 15.(左外连接)对于 所有球员 ,获得球员号码,名字和他所引起的罚款,结果按照球员号码排序。按照一般的写法是这样的:select p.pl
48、ayerno,name,pen.amount from players as p,penalties as pen where p.playerno=pen.playerno order by p.playerno; 但是这样的写法是不完整的,为了强调所有球员 ,应当使用左外连接:select p.playerno,name,pen.amount from players as p left outer join penalties as pen on p.playerno=pen.playerno order by p.playerno; 对于所有外连接, outer 关键字可以省略,最终结
49、果不会受任何影响。左外连接是否必要,取决于问题和连接之间的关系,在p.playerno 和 pen.playerno 之间,存在一个子集关系,因此,左外连接是有用的。16.对于每一笔罚款,获取支付编号和球员名字。select pen.paymentno, from penalties as pen left outer join players as p on pen.playerno=p.playerno order by pen.paymentno; 在此例中, penalties 在一个左表,由于没有那一笔罚款不是助于一个球员的,因此没有一笔罚款是漏掉的,左外连接不起作用,使
50、用内连接也会得到相同的结果。17.对于每个球员 ,获得球员编号和名字以及他当队长的球队的编号和分级,按照球员编号排序。select p.playerno,name,t.teamno,t.division from players as p left outer join teams as t on p.playerno=t.playerno mysql 开发范例- 15 - order by p.playerno; 18.对于居住在 inglewood 的每个球员 ,获取球员编号,名字,罚款列表并且列出他曾经效力过的球队。select p.playerno,,pen.amount,
51、m.teamno from players as p left outer join penalties as pen on p.playerno=pen.playerno left outer join matches as m on p.playerno=m.playerno where p.town=inglewood; 19.(右外连接)对于 所有球员 ,获取球员号码,名字和他们当队长的球队编号。select p.playerno,,t.teamno from teams as t right outer join players as p on t.playerno=p.
52、playerno; 20.(自然连接) 对于出生于 1920-06-30 后且至少一次罚款的每个球员,获得球员号码名字和所有罚款。一般情况下我们会这样写:select p.playerno,,pen.amount from players as p,penalties as pen where p.playerno=pen.playerno and p.birth_date1020-06-30; 使用自然连接后,语句就缩短了:select p.playerno,,pen.amount from players as p natural join penalties as
53、 pen where p.birth_date1920-06-30; 21.(连接条件中的附加条件)下一条select 语句包含了一个左外连接加上where 子句的一个附加条件。select teams.playerno,teams.teamno,penalties.patmentno from teams left outer join penalties on teams.playerno=penalties.playerno where division= second ; 22.(使用 using 替换连接条件)如:from teams inner join players on te
54、ams.playerno=players.playerno 和下面的语句是相等的:from teams inner join players on using (playerno) 在 penalties 表和 teams 表之间做一个左外连接。select * from penalties left join teams using (playerno); 23.获取居住在 stratford 的球员号码。select p.playerno from players as p where p.town=stratford; 或者:select playerno from (select *
55、from players where town=stratford) as stratforders; 24.获取在 first 级别的球队中担任队长的每个球员编号。select small_teams.playerno from(select playerno,division from teams) as small_teams where small_teams.division=first; mysql 开发范例- 16 - 25.对于赢得的局数和输掉的局数之间的差值大于2 的每一场比赛,获取比赛编号和比赛之间的差值。select matchno,difference from(sel
56、ect matchno,abs(won-lost) as difference from matches) as m where difference2; 26.创建一个名为 towns 的虚拟表。select * from (select stratford as town,4 as number union select plymouth,6 union select inglewood,1 union select douglas,2) as towns order by town; 27.对于每一个球员,获得球员编号,姓名,城市以及他居住城市的居民数目。select playerno,
57、name,players.town,number*1000 from players left outer join (select stratford as town,4 as number union select plymouth,6 union select inglewood,1 union select douglas,2) as towns where players.town=towns.town order by playerno; 28.获取所居住的城市人口指数大于2 的球员号码。select playerno from players left outer join (s
58、elect stratford as town,4 as number union select plymouth,6 union select inglewood,1 union select douglas,2) as towns on players.town=towns.town where towns.number2; 29.获取名字为 john,mark 和 arnold 以及姓为 berg、johnson 和 william 的所有可能的组合。select * from(select john as first_name union select mark union selec
59、t arnold) as first_names, (select berg as last_name union select johnson union mysql 开发范例- 17 - select william) as last_names; 30.对于数字 10-19,一次获取 3 次方的值,如果值大于4000,就不要包含在结果中。select number,power(number,3) from (select 10 as number union select 11 union select 12 union select 13 union select 14 union s
60、elect 15 union select 16 union select 17 union select 18 union select 19) as numbers where power(number,3)=4000; 31.生成 0-999 之间的数字,包含999. select number from (select cast(concat(digit1.digit,concat(digit2.digit,digit3.digit) as unsigned integer) as number from (select 0 as digit union select 1 union
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2025标准的摄影作品使用许可合同
- 二零二五年度净水器绿色环保认证采购合同
- 2025年度文化产业分红合作协议范本(含IP授权)3篇
- 2025年度公司设立前股东合作协议书(含知识产权保护)3篇
- 2025年度公司股东间应急事件处理合作协议书3篇
- 2025年度农产品电商平台农产品物流配送优化合同版3篇
- 2025年度农机租赁与农业科研合作开发合同3篇
- 二零二五年度农村宅基地租赁及土地流转服务协议
- 2025年度农产品深加工项目原料供应合同版3篇
- 二零二五年度婚庆服务市场区域保护竞业禁止合同2篇
- GB/T 9755-2024合成树脂乳液墙面涂料
- 哔哩哔哩MATES人群资产经营白皮书【哔哩哔哩】
- 【历史】第一、二单元测试题2024~2025学年统编版七年级历史上册
- 婚姻家庭规划
- 认识实习报告(10篇)
- 【MOOC】内科护理学-中山大学 中国大学慕课MOOC答案
- 2024年商业地产买卖合同样本
- 2023-2024学年广东省深圳市福田区七年级(上)期末英语试卷
- 双碳全景系列培训第一章碳达峰、碳中和
- 【企业盈利能力探析的国内外文献综述2400字】
- 变电检修班组规范化建设实施细则
评论
0/150
提交评论