快乐学习
前程无忧、中华英才非你莫属!

Day40-MySQL之开发者-基础小结

1、创建新用户“BOOKSQL ” 密码:“BOOKSQLPW“

CREATE USER 'BOOKSQL'@'localhost' IDENTIFIED BY 'BOOKSQLPW'

2、给SQL用户BOOKSQL创建和操作表的权限。

GRANT ALL PRIVILEGES
ON    *.*
TO    'BOOKSQL'@'localhost'
WITH  GRANT OPTION

注意:1、2 的具体解释:Day0-MySQL入门到精通,第八章:用户表详解  http://www.ztloo.com/2017/02/26/mysql入门到精通

3、创建名为TENNIS的数据库。

CREATE DATABASE TENNIS

4、选择数据库

USE TENNIS

5、创建表

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))

tennis.sql

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 (
  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')

7、查询表

7.1、获取居住在"Stratford " 的每个球员的号码,姓名和出生日期,并按照名字的字母顺序来排列结果。

SELECT   PLAYERNO, NAME, BIRTH_DATE
FROM     PLAYERS
WHERE    TOWN = 'Stratford'
ORDER BY NAME

7.2、获取在1980年以后加入俱乐部并且居住在Stratford的每一个球员的号码,按照球员号码排序。

SELECT   PLAYERNO
FROM     PLAYERS
WHERE    JOINED > 1980
AND      TOWN = 'Stratford'
ORDER BY PLAYERNO

7.3、获取有关每次罚款的所有信息

SELECT   *
FROM     PENALTIES

7.4 、121的33倍是多少?

SELECT   33 * 121

8、更新和删除行

8.1、把44号球员所引起的每笔罚款的数额改为200美元

UPDATE   PENALTIES
SET      AMOUNT = 200
WHERE    PLAYERNO = 44
;

验证:

SELECT   PLAYERNO, AMOUNT
FROM     PENALTIES
WHERE    PLAYERNO = 44

8.2、删除罚款额度大于100美元的每一笔罚款。

DELETE
FROM     PENALTIES
WHERE    AMOUNT > 100

8.3、使用索引优化查询过程

CREATE   INDEX PENALTIES_AMOUNT ON
         PENALTIES (AMOUNT)

说明: 这个索引确保了在前面的例子中,MySQL只需要查看数据库中的那些满足WHERE条件的列,因此可以更快的得出结果!

           索引要合理添加,如有不当会大量增加插入、修改、删除的效率!

           解决索引影响的插入、修改、删除的效率,可以进行读写服务器的分离! 比较优秀的中间件:MaxScale!

           索引篇详解 : http://www.ztloo.com/2017/05/15/day22-mysql基础问题精炼总结/

9、视图

9.1、创建一个视图、其中记录了每场比赛赢得局数和输掉的局数之间的差值,并查看创建好的视图。

CREATE   VIEW NUMBER_SETS (MATCHNO, DIFFERENCE) AS
SELECT   MATCHNO, ABS(WON - LOST)
FROM     MATCHES
;
SELECT   *
FROM     NUMBER_SETS

详细可参考:Day39-MySQL之开发者-视图详解

MySQL_5.1_zh.chm

10、删除数据库对象

10.1  删除MATCHERS表

DROP TABLE MATCHES

10.2、删除NUMBER_SETS视图

DROP VIEW NUMBER_SETS

10.3 、删除PENALTIES_AMOUNT 索引。

DROP INDEX PENALTIES_AMOUNT

10.4 、删除TENNIS数据库

DROP DATABASE TENNIS
打赏

未经允许不得转载:同乐学堂 » Day40-MySQL之开发者-基础小结

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!