同乐学堂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入门到精通，第八章：用户表详解 https://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))
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！
索引篇详解 ： https://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之开发者-视图详解
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
微信扫一扫，打赏作者吧～
