一些SQL脚本
 
SQL的一些小技巧，很实用，也不好整理，很零散。
都是一些处理经验，有的使得SQL简洁，有的效率上会更好。
 
还没有完成，有空了慢慢完善。
 
 
环境MySQL5
数据模型图：
 
一、AND条件组合简化
 
例子1：查询两胜三负的比赛号。下面两个SQL是等价的：
 
SELECT MATCHNO
  FROM MATCHES
 WHERE (WON, LOST) = (2, 3);
 
SELECT MATCHNO
  FROM MATCHES
 WHERE WON = 2
   AND LOST = 3;
 
+---------+
| MATCHNO |
+---------+
|       2 |
|      11 |
+---------+
 
二、子查询及连接查询
 
一般来说，子查询相对连接查询效率要高。
 
1、子查询返回单值参与比较运算，可以使用＝、<、>、<>、NOT运算符号。
 
例子2：查询球队1队长运动员的编号和名字。
 
方法一：子查询方式，效率高。代价＝0:00:00.062。
SELECT PLAYERNO, NAME
  FROM PLAYERS
 WHERE PLAYERNO = (SELECT PLAYERNO FROM TEAMS WHERE TEAMNO = 1);
 
方法二：内链接方式，效率低下。代价＝0:00:00.109。
SELECT P.PLAYERNO, P.NAME
  FROM PLAYERS P
 INNER JOIN TEAMS T ON P.PLAYERNO = T.PLAYERNO
 WHERE T.TEAMNO = 1;
 
 例子3：查询年龄大于8467号运动员的编号、名、姓。
 
 SELECT PLAYERNO, NAME, INITIALS
  FROM PLAYERS
 WHERE BIRTH_DATE <
       (SELECT BIRTH_DATE FROM PLAYERS WHERE LEAGUENO = '8467')
 
例子4: 查询城市与队员7相同且性别与队员2相同的所有运动员号码、城镇、性别。
 
SELECT PLAYERNO, TOWN, SEX
  FROM PLAYERS
 WHERE (TOWN, SEX) = (
        (SELECT TOWN FROM PLAYERS WHERE PLAYERNO = 7),
        (SELECT SEX FROM PLAYERS WHERE PLAYERNO = 2))
 
例子5：查询住在Stratford或者生于1963年的所有运动员的编号、城镇、生日，但不包含住在Stratford并生日为1963年的运动员。
 
SELECT PLAYERNO, TOWN, BIRTH_DATE
  FROM PLAYERS
 WHERE (TOWN = 'Stratford' OR YEAR(BIRTH_DATE) = 1963)
   AND NOT (TOWN = 'Stratford' AND YEAR(BIRTH_DATE) = 1963)
 
 
2、子查询返回多个值参与比较运算，可以使用IN、NOT IN。
 
例子6：查询以3：1或者3:2赢得的所有比赛。展示比赛号、输赢局数。
 
SELECT M.MATCHNO, M.WON, M.LOST
  FROM MATCHES M
 WHERE (M.WON, M.LOST) IN ((3, 1), (3, 2));
 
例子7：至少为1队打过一场比赛的所有运动员，给出编号和名字。
 
SELECT P.PLAYERNO, P.NAME
  FROM PLAYERS P
 WHERE P.PLAYERNO IN (SELECT PLAYERNO FROM MATCHES M WHERE M.TEAMNO = 1);
 
三、EXISTS运算符
 
EXISTS运算符有两种EXISTS和NOT EXISTS形式。一般来说，能用EXISTS实现的查询，也能用IN来实现，效率难分伯仲。
 
例子8：查询至少遭受一次罚款的运动员名和姓。
 
这个问题可以用子查询，也可以使用EXISTS来实现。
 
SELECT NAME, INITIALS
  FROM PLAYERS
 WHERE PLAYERNO IN (SELECT PLAYERNO FROM PENALTIES);
 
SELECT P.NAME, P.INITIALS
  FROM PLAYERS P
 WHERE EXISTS (SELECT * FROM PENALTIES PN WHERE PN.PLAYERNO = P.PLAYERNO);
 
EXISTS只判断是否存在记录，返回值仅为true和false，而不管返回的记录是什么。
为了节省资源，可以将EXISTS子查询中返回的值设为任意常量，这样丝毫不影响整个SQL的执行结果。
如，上面的SQL还可以继续优化为：
 
SELECT P.NAME,P.INITIALS
FROM PLAYERS P
WHERE EXISTS(SELECT 1 FROM PENALTIES PN WHERE PN.PLAYERNO = P.PLAYERNO);
 
例子9：给出不是队长的队员的名和姓。
 
SELECT P.NAME, P.INITIALS
  FROM PLAYERS P
 WHERE P.PLAYERNO NOT IN
       (SELECT T.PLAYERNO FROM TEAMS T WHERE P.PLAYERNO = T.PLAYERNO);
 
SELECT P.NAME, P.INITIALS
  FROM PLAYERS P
 WHERE NOT EXISTS (SELECT 1 FROM TEAMS T WHERE P.PLAYERNO = T.PLAYERNO);
 
 
四、ALL 和 ANY 运算符
这两个运算符很容易被遗忘，但是它们也很有用。
 
例子10：查询年龄最大的运动员号码、姓名、和生日。
 
同样，这个例子有两种写法，一种是用MIN函数，一种是用ALL关键字。
 
SELECT P.PHONENO, P.NAME, P.BIRTH_DATE
  FROM PLAYERS P
 WHERE P.BIRTH_DATE = (SELECT MIN(BIRTH_DATE) FROM PLAYERS);
 
SELECT P.PHONENO, P.NAME, P.BIRTH_DATE
  FROM PLAYERS P
 WHERE P.BIRTH_DATE <= ALL (SELECT BIRTH_DATE FROM PLAYERS);
 
注意，ALL 和 ANY 关键字在使用的时候，要注意NULL的情况，一个NULL值和另外一个值做比较的结果是难以预知的，具体和各个SQL产品的实现有关系。这是个巨大的陷阱，因此，ALL和ANY平时很少用到。
 
五、VARIANCE和STDDEV聚合函数
 
相信很多开发者对COUNT、MAX/MIN、SUM/AVG聚合函数很熟悉，但不一定都知道VARIANCE和STDDEV聚合函数，这个两个函数分别用来聚酸某个列中值的方差和标准偏差，在数据统计分析中很常用。
 
例子11：求队员44的所有罚款的方差。
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
－－－－－－－－－－－－－－－－－－－－－－－－
测试环境的脚本：
 
drop database if exists introsql;
 
create database if not exists introsql;
 
use introsql;
 
COMMIT WORK
;
 
CREATE TABLE PLAYERS
      (PLAYERNO     INTEGER NOT NULL PRIMARY KEY,
       NAME         CHAR(15) NOT NULL,
       INITIALS     CHAR(3) NOT NULL,
       BIRTH_DATE   DATE,
       SEX          CHAR(1) NOT NULL
                    CHECK(SEX IN ('M','F')),
       JOINED       SMALLINT NOT NULL
                    CHECK(JOINED > 1969) ,
       STREET       CHAR(30) NOT NULL,
       HOUSENO      CHAR(4),
       POSTCODE     CHAR(6) CHECK(POSTCODE LIKE '______'),
       TOWN         CHAR(10) NOT NULL,
       PHONENO      CHAR(13),
       LEAGUENO     CHAR(4))
;
CREATE TABLE TEAMS
      (TEAMNO       INTEGER NOT NULL PRIMARY KEY,
       PLAYERNO     INTEGER NOT NULL,
       DIVISION     CHAR(6)  NOT NULL
                    CHECK(DIVISION IN ('first','second')),
       FOREIGN KEY  (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE MATCHES
      (MATCHNO      INTEGER NOT NULL PRIMARY KEY,
       TEAMNO       INTEGER NOT NULL,
       PLAYERNO     INTEGER NOT NULL,
       WON          SMALLINT NOT NULL
                    CHECK(WON BETWEEN 0 AND 3),
       LOST         SMALLINT NOT NULL
                    CHECK(LOST BETWEEN 0 AND 3),
       FOREIGN KEY (TEAMNO) REFERENCES TEAMS (TEAMNO),
       FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE PENALTIES
      (PAYMENTNO    INTEGER NOT NULL PRIMARY KEY,
       PLAYERNO     INTEGER NOT NULL,
       PAYMENT_DATE DATE NOT NULL
                    CHECK(PAYMENT_DATE >= DATE('1969-12-31')),
       AMOUNT       DECIMAL(7,2)  NOT NULL
                    CHECK (AMOUNT > 0),
       FOREIGN KEY (PLAYERNO) REFERENCES PLAYERS (PLAYERNO))
;
CREATE TABLE COMMITTEE_MEMBERS
      (PLAYERNO     INTEGER NOT NULL,
       BEGIN_DATE   DATE NOT NULL,
       END_DATE     DATE,
       POSITION     CHAR(20),
       PRIMARY KEY  (PLAYERNO, BEGIN_DATE),
       FOREIGN KEY  (PLAYERNO) REFERENCES PLAYERS (PLAYERNO),
       CHECK(BEGIN_DATE < END_DATE),
       CHECK(BEGIN_DATE >= DATE('1990-01-01')))
;
COMMIT WORK
;
INSERT INTO PLAYERS VALUES (
  2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road',
    '43', '3575NH', 'Stratford', '070-237893', '2411')
;
INSERT INTO PLAYERS VALUES (
  6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane',
    '80', '1234KK', 'Stratford', '070-476537', '8467')
;
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', '6584WO', '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 Main Road',
    '10', '1294QK', 'Midhurst', '010-659599', NULL)
;
INSERT INTO PLAYERS VALUES (
 39, 'Bishop', 'D', '1956-10-29', '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 (
 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, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane',
    '80', '6494SG', 'Stratford', '070-494593', '6524')
;
INSERT INTO PLAYERS VALUES (
104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street',
    '65', '9437AO', '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 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)
;
INSERT INTO MATCHES VALUES ( 6, 1,   2, 1, 3)
;
INSERT INTO MATCHES VALUES ( 7, 1,  57, 3, 0)
;
INSERT INTO MATCHES VALUES ( 8, 1,   8, 0, 3)
;
INSERT INTO MATCHES VALUES ( 9, 2,  27, 3, 2)
;
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)
;
INSERT INTO MATCHES VALUES (13, 2,   8, 0, 3)
;
INSERT INTO PENALTIES VALUES (1,  6, '1980-12-08',100)
;
INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75)
;
INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100)
;
INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50)
;
INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25)
;
INSERT INTO PENALTIES VALUES (6,  8, '1980-12-08', 25)
;
INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30)
;
INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75)
;
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 COMMITTEE_MEMBERS VALUES (  2, '1990-01-01', '1992-12-31', 'Chairman')
;
INSERT INTO COMMITTEE_MEMBERS VALUES (  2, '1994-01-01',  NULL, 'Member')
;
INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member')
;
INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01',  NULL, 'Secretary')
;
INSERT INTO COMMITTEE_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 ( 57, '1992-01-01', '1992-12-31', 'Secretary')
;
INSERT INTO COMMITTEE_MEMBERS VALUES ( 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 ( 95, '1994-01-01',  NULL, 'Treasurer')
;
COMMIT WORK
;
 
－－－－－－－－－－
用PD两次反向工程所得的脚本：
/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2008-6-4 18:08:28                            */
/*==============================================================*/

drop table if exists COMMITTEE_MEMBERS;
drop table if exists MATCHES;
drop table if exists PENALTIES;
drop table if exists PLAYERS;
drop table if exists TEAMS;
/*==============================================================*/
/* Table: COMMITTEE_MEMBERS                                     */
/*==============================================================*/
create table COMMITTEE_MEMBERS
(
   PLAYERNO             INTEGER not null comment '运动员号',
   BEGIN_DATE           DATE not null comment '运动员成为委员会现任成员的日期',
   END_DATE             DATE comment '运动员卸任委员会成员的日期',
   POSITION             CHAR(20) comment '职位名称',
   primary key (PLAYERNO, BEGIN_DATE),
   check (BEGIN_DATE >= DATE('1990-01-01'))
);
alter table COMMITTEE_MEMBERS comment '委员会表';
/*==============================================================*/
/* Table: MATCHES                                               */
/*==============================================================*/
create table MATCHES
(
   MATCHNO              INTEGER not null comment '比赛号',
   TEAMNO               INTEGER not null comment '球队号',
   PLAYERNO             INTEGER not null comment '队员号',
   WON                  SMALLINT not null comment '所赢局数',
   LOST                 SMALLINT not null comment '所输局数'
);
alter table MATCHES comment '比赛明细表';
/*==============================================================*/
/* Table: PENALTIES                                             */
/*==============================================================*/
create table PENALTIES
(
   PAYMENTNO            INTEGER not null comment '罚款编号',
   PLAYERNO             INTEGER not null comment '被罚的运动员号',
   PAYMENT_DATE         DATE not null comment '罚款日期',
   AMOUNT               DECIMAL(7,2) not null comment '罚款金额'
);
alter table PENALTIES comment '处罚明细表';
/*==============================================================*/
/* Table: PLAYERS                                               */
/*==============================================================*/
create table PLAYERS
(
   PLAYERNO             INTEGER not null comment '队员号',
   NAME                 CHAR(15) not null comment '名',
   INITIALS             CHAR(3) not null comment '姓',
   BIRTH_DATE           DATE comment '生日',
   SEX                  CHAR(1) not null comment '性别',
   JOINED               SMALLINT not null comment '计入俱乐部的年份',
   STREET               CHAR(30) not null comment '街道名称',
   HOUSENO              CHAR(4) comment '门牌号',
   POSTCODE             CHAR(6) comment '邮编',
   TOWN                 CHAR(10) not null comment '城镇',
   PHONENO              CHAR(13) comment '电话号码',
   LEAGUENO             CHAR(4) comment '由联盟指定的联盟号'
);
alter table PLAYERS comment '队员';
/*==============================================================*/
/* Table: TEAMS                                                 */
/*==============================================================*/
create table TEAMS
(
   TEAMNO               INTEGER not null comment '球队号',
   PLAYERNO             INTEGER not null comment '队长队员号',
   DIVISION             CHAR(6) not null comment '联盟安置球队的场地'
);
alter table TEAMS comment '球队';
alter table COMMITTEE_MEMBERS add constraint FK_Reference_5 foreign key (PLAYERNO)
      references PLAYERS (PLAYERNO);
alter table MATCHES add constraint FK_Reference_2 foreign key (TEAMNO)
      references TEAMS (TEAMNO);
alter table MATCHES add constraint FK_Reference_3 foreign key (PLAYERNO)
      references PLAYERS (PLAYERNO);
alter table PENALTIES add constraint FK_Reference_4 foreign key (PLAYERNO)
      references PLAYERS (PLAYERNO);
alter table TEAMS add constraint FK_Reference_1 foreign key (PLAYERNO)
      references PLAYERS (PLAYERNO);