MySql-pratices

CREATE TABLE players(
playerno int 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 players_xxl(
playerno int 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 int not null,
playerno int not null,
division char(6) not null,
primary key (teamno)
);
create table matches(
matchno int not null,
teamno int not null,
playerno int not null,
won smallint not null,
lost smallint not null,
primary key(matchno)
);
create table matches_with_balance(
matchno int not null primary key,
teamno int not null,
playerno int not null,
won smallint not null,
lost smallint not null,
balances as abs(won-lost) /*不支持派生列*/
);
create table penalties(
paymentno int not null,
playerno int not null,
payment_date date not null,
amount decimal(7,2) not null,
primary key (paymentno)
);
create table penalties_with_comment(
paymentno int not null comment 'primary key of the orders',
playerno int not null comment 'playerno who has incurred the penality',
payment_date date not null comment'date on which the penality has been paid',
amount decimal(7,2) not null comment'amount of penalties in dollars'
);
select column_name , column_comment from information_schema.columns where table_name
= 'penalties_with_comment';
create table committee_members(
playerno int not null,
begin_date date not null,
end_date date ,
position char(20),
primary key (playerno,begin_date)
);
create table players_data(
playerno int not null ,
number_mat int,
sum_penalties decimal(7,2),
primary key(playerno)
);
insert into players values
(2,'everett' ,'R','1948-09-01','M',1977,'Haseltine Lane','43','3575NH','startford','070-237893','2
411'),
(6,'parmenter','R','1964-06-25','M',1977,'Haseltine Lane','80','1234KK','Stratford','070-47653
9','8467'),
(7,'WIse' ,'GWS','1963-05-11','M',1981,'Edgecombe Way' ,'39','9758VB','Stratford','070-34768
9',null),
(8,'newcastle','B','1962-07-08','F',1980,'Station Road' ,'4','6548RO','Inglewood','070-458458','2
983'),
(27,'Collins','DD','1964-12-28','F',1983,'Long Drive' ,'804','8457DK','Elthan','079-234857','251
3'),
(28,'Collins','C' ,'1963-06-22','F',1983,'Old Main Road' ,'10','1294QK','Midhurst','071-659599',nu
ll),
(39,'Bishop' ,'D' ,'1956-10-29','M',1980,'Eaton Square' ,'78','9629CD','Stratford','070-393435',n
ull),
(44,'Baker' ,'D' ,'1963-01-09','M',1980,'Lewis Street' ,'23','444LJ','Inglewood','070-368753','112
4'),
(57,'Brown' ,'M' ,'1971-08-17','M',1985,'Edgecombe Way' ,'16','4377CB','startford','070-45678
9','2356'),
(83,'Hope' ,'PK' ,'1956-11-11','M',1982,'Magdalene Road','16A','1812up','startford','070-45631
7','1608'),
(95,'Miller','P' ,'1963-05-14','M',1972,'High Street','33A','5764OP','douals','070-45683',null),
(100,'Parmenter','P','1963-02-28','M',1979,'Haseltine Lane','80','1234DF','Stratford','07065123
4','6542'),
(104,'Moorman','D','1970-05-10','F',1984,'Stout Street','65','4567LK','eltham','046-564213','456
7'),
(112,'Bailey','IP','1963-10-01','F',1984,'Vixen Road','8','4781DK','Playmouth','010-456786','187
2');
insert into teams values(1,6,'first'),(2,27,'second');
insert into matches values(1,1,6,3,1),
(2,1,6,2,3),
(3,1,6,3,0),
(4,1,44,3,2),
(5,1,83,0,3),
(6,1,2,1,3),
(7,1,57,3,0),
(8,1,8,0,3),
(9,2,27,3,2),
(10,2,104,3,2),
(11,2,112,2,3),
(12,2,112,1,3),
(13,2,8,0,0);
insert into penalties values
(1,6,'1980-12-08',100),
(2,44,'1981-05-05',75),
(3,27,'1983-09-10',100),
(4,104,'1984-12-08',50),
(5,44,'1980-12-08',25),
(6,8,'1980-12-08',25),
(7,44,'1982-12-30',30),
(8,27,'1984-11-12',75);
insert into committee_members values
(2,'1990-01-01','1992-12-31','Chairman'),
(2,'1994-01-01',null,'member'),
(6,'1990-01-01','1990-12-31','Secretary'),
(6,'1991-01-01','1992-12-31','Member'),
(6,'1992-01-01','1993-13-31','Treasurer'),
(6,'1993-01-01',null,'Chairman'),
(8,'1990-01-01','1990-12-31','Treasurer'),
(8,'1992-01-01','1991-12-31','Secretary'),
(8,'1993-01-01','1993-12-31','Member'),
(8,'1994-01-01',null,'Member'),
(27,'1990-01-01','1990-12-31','Member'),
(27,'1991-01-01','1991-12-31','Treasurer'),
(27,'1993-01-01','1993-12-31','Treasurer'),
(57,'1992-01-01','1992-12-31','Secretary'),
(95,'1994-01-01',null,'Treasurer'),
(112,'1992-01-01','1992-12-31','Member'),
(112,'1994-01-01',null,'Secretary');
create view users(username)as
select distinct upper(concat('''',user,'''@''',host,''''))
from mysql.user;
create view tables(table_creator,table_name,create_timesamp,comment)as
select upper(table_schema),upper(table_name),create_time,table_comment
from information_schema.tables
where table_type in('base table' , 'temporary');
create view columns(table_creator , table_name,column_name,column_no,data_type,char_lengt
h,presicion,scale , nullable,comment) as
select upper(table_schema),upper(table_name),upper(column_name),ordinal_position,upper(da
ta_type),character_maximum_length,numeric_precision,numeric_scale,is_nullable,column_com
ment
from information_schema.columns;
create view indexes(index_creator,index_name,create_timestamp,table_creator,table_name,uni
que_id,index_type)as
select distinct upper(i.index_schema),upper(i.index_name),t.create_time,upper(i.table_schema),
upper(i.table_name),
case when i.non_unique = 0 then 'yer' else 'no'
end,
i.index_type
from information_schema.statistics as i,
information_schema.tables as t
where i.table_name = t.table_name
and
i.table_schema = t.table_schema;
create view database_auths(grantor,grantee,privilige,withgrantopt)as
select 'unkown' , upper(grantee),privilege_type , is_grantable
from information_schema.schema_privileges;
select playerno,
case sex when 'F' then 'Female'
else 'male' end as sex,
name
from players;
select playerno ,
case town
when 'Stratford' then
case birth_date when '1964-06-25' then 'young stratford' else 'old stratford' end
when 'Inglewood' then
case birth_date when '1963-05-11' then 'young Inglewood' else 'old Inglewood' end
end
as town
from players;
/*表达式没有对应的数据类型时,按情形制定数据类型是很重要的*/
select playerno from penalties where amount > cast(50 as decimal(7,2));
/*查询某个日间间隔的列,使用 interval */
select payment_date,amount from penalties where
-> payment_date >= '1980-12-08'
-> and
-> payment_date <= '1980-12-08' + interval 7 day;
/*复合时间表达式*/
create table matches_special(
matchno int not null,
teamno int not null,
playerno int not null,
won smallint not null,
lost smallint not null,
start_date date not null,
start_time time not null,
end_time time not null,
primary key (matchno)
);
insert into matches_special values(1,1,6,3,1,'2004-10-25' ,'14:10:12','16:50:09');
insert into matches_special values(2,1,44,3,2,'2004-10-25','17:00:00','17:55:48');
select matchno , start_time , addtime(start_time , '08:00:00') from matches_special;
/*使用 union 运算符,检索只罚款一次的运动员*/
select playerno from players
union
select playerno from penalties;
/*显示连接和隐式连接的区别在于,显示连接所得的中间表是已经应用条件的笛卡尔乘积*/
select players.playerno,name,amount
from players,penalties
where players.playerno = penalties.playerno
and birth_date > '1930-06-30';
select players.playerno,name,amount
from players inner join penalties on(players.playerno = penalties.playerno)
where birth_date > '1930-06-30';
/*外连接*/
select players.playerno , players.name ,amount from players left outer join penalties on players.
playerno = penalties.playerno;
/*在 from 语句中添加筛选条件会出现的例外*/
select teams.palyerno ,teams.teamno,penalties.paymentno
from teams left outer join penalties on teams.palyerno = penalties.playerno
where division = 'second';
select teams.palyerno ,teams.teamno,penalties.paymentno
from teams left outer join penalties on teams.palyerno = penalties.playerno and division = 'sec
ond' /*sql 会确保左表的行均得到保留,所以不符合 division 的条件的行也会保留。*/
;
Charpter8 select 语句,where 从句
/*对于字符串,按字符顺序比较字符串*/
select 'ABC' < 'BCD';
/*行表达式(时间类型)对相同位置的值进行比较*/
select '1985-5-5' >'1958-6-5';
/********************************/
select (2,4) > (2,3)
equal to
select 2 >2 and 4 > 3;
/********************************/
/*子查询至多返回一个值用于条件筛选,如果没有子查询结果为空,则返回 null 值用于下一步的比较*/
/*带有 in 运算符的条件做如下处理*/
E1 in(E2,E3,E4) equal to E1 = E2 or E1 = E3 or E1 = E4;
/*exist 检查子查询返回结果(行)是否为空,可与 in 运算符相互转化 asAsz */
select * from players where exists(select * from penalties where penalties.playerno = players.
playerno);
select * from players where players.playerno in (select playerno from penalties);
8.15 否定查询
select playerno from penalties where amount <>25;
/*这里面 playerno = 44 的队员出现了罚款金额等于 25 的情况,违背了查询目标设定。队员号 44 的罚款金
额为(75,25,33)。
原因在于队员号 44 的行满足筛选条件。
*/
/*正确的表达应为*/
select playerno
from players
where playerno not in(
select playerno from penalties where amount = 25);
9.7 聚合函数
/*以下写法是不允许的,但如果你想得到关于统计信息的行,可这样写*/
select playerno , count(*) from players.`der5
select playerno , name (select count(*) from penalties where players.playerno=penalties.player
no) as number
from penalties;
/********************************************************************/
select players.playerno , players.name , sum(penalties.amount)
from players,penalties
/*可用 left join 代替*/
where players.playerno = penalties.playerno
group by penalties.playerno;
/********************************************************************/
select count(matchno)
from matches
where won > lost
group by won,lost;
select won , lost , count(*)
from matches
where won > lost
group by won , lost
order by 1,2;
select begin_date,end_date,count(*)
from committee_members
where position = 'member'
group by begin_date,end_date;
/***********************************************************************************************
***************************/
select penalties.playerno , T_inglewood.name , T_inglewood.initials , count(*)
from penalties inner join
((select playerno , name , town ,initials from players where town = 'inglewood')
as T_inglewood)
using(playerno)
group by playerno, name , initials;
/***********Every derived table must have its own alias***********/
select A.playerno , A.name , A.initals , count(*)
from (select playerno , name , town from players where town = 'inglewood') as A;
/***********Every derived table must have its own alias***********/
select name , initials , count(*)
from players as P inner join penalties as Pen
on P.playerno = Pen.playerno
where town = 'inglewood'
group by playerno
order by P.playerno , name , initials;
/***********************************************************************************************
***************************/
select T.teamno,T.division,count(matchno)
from
matches
inner join
(select teamno , division from teams) as T
using(teamno)
where won > lost
group by won , lost;
/***********************************************************************************************
***************************/
select year(payment_date) as Year , count(*) from penalties group by Year order by Year;
/***********************************************************************************************
***************************/
/***********************************************************************************************
***************************/
select players.playerno , players.name , count(*),teams.teamno
from players inner join
(penalties inner join teams using(playerno))
using(playerno)
group by players.playerno;
/*如下表达易读性更好*/
select players.playerno , number_of_penalties , number_of_teams
from (select playerno , count(*) as number_of_penalties from penalties group by playerno )as n
umber_penalties ,
(select playerno , count(*) as number_of_teams from teams group by playerno) as number_t
eams,
players
where players.playerno = number_penalties.playerno
and
players.playerno = number_teams.playerno;
/***********************************************************************************************
***************************/
select T_attend.playerno , T_pen.counter_pen
from
(select playerno from matches group by playerno) as T_attend,
(select playerno , count(*) as counter_Pen from penalties group by playerno) as T_pen
where T_attend.playerno = T_pen.playerno;
/实现 amount 随订单号累加***********************************************************************
*******************************/
select P1.paymentno , P1.amount , sum(P2.amount)
from
penalties as P1,
penalties as P2
where P1.paymentno >= P2.paymentno
group by P1.paymentno
order by P1.paymentno
;
/***********************************************************************************************
***************************/
/***********************************************************************************************
***************************/
select P1.paymentno , P1.amount , (P1.amount/sum(P2.amount)) as amount_persent
from penalties P1 , penalties P2
order by P1.paymentno;
/*进行笛卡尔乘积后,sum(P2.amount)的意思已不再是计算一张表的 amount 的总值,P2.amount 指代的
是笛卡尔乘积形成的表的列*/
select P1.paymentno , P1.amount , sum(P2.amount) as amount_persent from penalties P1 , pen
alties P2;
select P1.paymentno , P1.amount , sum(P2.amount) as amount_persent from penalties P1 , pen
alties P2 group by P1.paymentno;
/***********************************************************************************************
***************************/
select teams.teamno , teams.division , T_num.num
from teams,
(select teamno , count(*) as num from matches group by teamno) as T_num
where T_num.teamno = teams.teamno;
/***********************************************************************************************
***************************/
select players.playerno , players.name , pen.total , teams.teamno
from players ,
(select playerno , sum(amount) as total from penalties group by playerno) as pen,
teams
where players.playerno = pen.playerno
and
players.playerno = teams.playerno
and
teams.division = 'first';
/***********************************************************************************************
***************************/
/***********************************************************************************************
***************************/
select win_player.teamno , sum(win_player.num)
from players ,
(select playerno , teamno , count(distinct playerno) as num from matches where won > lost
group by playerno , teamno) as win_player
where players.playerno = win_player.playerno
and
players.town = 'stratford';
/***********************************************************************************************
***************************/
select P1.playerno , P1.name , P1.joined , P1.joined - P2.avg_joined
from players as P1,
(select avg(joined) as avg_joined from players) as P2;
select penalties.playerno , sum(amount)
from penalties inner join teams using(playerno)
group by playerno
having sum(amount) > 80;
select T1.playerno , max(T1.each_high)
from
(select playerno , max(amount) as each_high from penalties group by playerno) as T1;
select playerno , count(paymentno) from penalties group by playerno
having count(paymentno) = (select count(paymentno) from penalties where playerno = 6 grou
p by playerno)
;
/***********************************************************************************************
***************************
select playerno , amount , avg(amount)
from penalties
group by playerno
order by avg(amount);
select playerno , amount
from penalties P1
order by (select avg(amount) from penalties as P2 where P1.playerno = P2.playerno);
/*第一个表达式比第二个表达式更清楚*/
/***********************************************************************************************
***************************
(select playerno from penalties)
union
(select playerno from teams);
/***********************************************************************************************
***************************/
select playerno
from penalties inner join teams using(playerno)
equal to
select playerno from penalties
intersect
select playerno from teams;
mysql-5.5.25 没有 intersect , except 操作。
/***********************************************************************************************
***************************/
/***********************************************************************************************
***************************/
insert into players_data
select playerno , count(matchno) , sum(amount)
from (players left join matches using(playerno)) left join penalties using(playerno)
group by playerno;
equal to
insert into players_data(playerno) select playerno from players;
update players_data PD set number_mat = (select count(matches) from matches where PD.pla
yerno = matches.playerno group by playerno),
set sum_penalties = (select sum(amount) from penalties where PD.playerno = p
enalties.playerno group by playerno);
/*方案二提供了表和表对接录入的表达方式*/
/***********************************************************************************************
***************************/
/***********************************************************************************************
***************************/
delete from players
where joined >
(select avg(joined) from players where town = 'stratford');
以上语句在 5.25 版本中被禁止 “ You can't specify target table 'players' for update in FROM clause”
/***********************************************************************************************
***************************/
创建 temporary table 会在用户下线后 drop 掉。
/***********************************************************************************************
***************************/
create table table_structure like players;
create table table_content as (select * from players);
/***********************************************************************************************
***************************/
create table team_cop(playerno int not null,comment varchar(8)) as (select * from teams);
create temporary table penalties_temp as (select * from penalties);
update penalties as PD set amount = (select year(payment_date) * 10 from penalties_temp wh
ere PD.paymentno = penalties_temp.paymentno);
alter table mathes add balance as abs(won-lost);
alter table matches add foreign key(teamno) references teams(teamno) , add foreign key(playe
rno) references players(playerno);
/***********************************************************************************************
***************************/
create procedure fill_players_xxl(in number_players int)
begin
declare counter int;
truncate table players_xxl;
commit work;
set counter = 1;
while counter <=number_players do
insert into players_xxl values(
counter,
concat('name' , cast(counter as char(10))),
case mod(counter,2) when 0 then 'v11' else 'v12' end,
date('1960-01-01') + interval (mod(counter,300))month,
case mod(counter , 20) when 0 then 'f' else 'm' end,
1980 + mod(counter , 20),
concat('street' , cast(counter /10 as unsigned integer)),
cast(cast(counter /10 as unsigned integer) + 1 as char(4)),
concat('p' , mod(counter,50)),
concat('town' , mod(counter,10)),
'070-6868689',
case mod(counter ,3) when 0 then '0' else counter end);
if mod(counter , 1000) = 0 then commit work; end if;
set counter = counter + 1;
end while;
commit work;
update players_xxl set leagueno = null where leagueno = '0';
commit work;
end
/***********************************************************************************************
***************************/
count(*) for initals='v11' , postcode = 'p25' , street = 'street164'
with index/without index
count(*):(50000,50000)
(2000,2000)
(10,10)
row
:(50031,100063)
(1999,100063)
(10,100063)
time :(0.04,0.07)
(0.00,0.07)
(0.00,0.07)
/***********************************************************************************************
***************************/
/*多表索引*/create index play_mat on players(playerno),matches(matchno);
/*散列索引*/create hash index players_hash on players(playerno) with pages =100;
/*虚拟列索引*/create index mat_balance on matches(won - lost);
CREATE temporary TABLE t (qty INT, price INT);
INSERT INTO t VALUES(3, 50);
SELECT qty, price, qty*price AS value FROM t;
SELECT * FROM v;