MySQL权威开发指南.docVIP

MySQL 开发者 SQL权威指南SQL For MySQL Developers A Comprehensive Tutorial and Reference 整理：侯杰  第一章 SQL概要 1.引入一个名为BOOKSQL的新用户，他的密码是BOOKSQLPW。 CREATE USER ‘BOOKSQL’@’LOCALHOST’ IDENTIFIED BY ‘BOOKSQLPW’‘houjie410782’@’localhost’ identified by ‘630529’ 2.给SQL用户BOOKSQL创建和操作表的权限。 grant all privileges on *.* to ‘BOOKSQL’@’LOCALHOST’ with grant option‘houjie410782’@’localhost’ with grant option; 3.创建数据库tennis。 reate database tennis; 4.指定tennis为当前数据库。 se tennis; 5.创建表players，teams，matches，penalties，committee_members。 create table players (playerno integer not 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( teamno 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( paymentno integer not null, playerno integer not null, payment_date date not null, amount decimal(7,2) not null, primary key (paymentno) ) 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.为表中填充数据。 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,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