超详细-SQL语句的增删改查，快点给我学

前言

为什么要学习 SQL 语句？

我相信很多的同学一定有一个疑问🤔️？
现在的计算机都发展得这么快，芯片和cpu的性能已经不再是以前的那种“龟速”了，而且人工智能发展得这么好，都开始逐步实现机器学习了。为啥我们还是要去学习 SQL 语句呢？

机器再怎么智能，它毕竟是机器，它并不能代替人的大脑🧠，有些人的思想和行为是机器无法去理解的，就比如说“社交牛逼症”这个东西，它就算机器再智能，也无法达到人类的这个地步。

所以说，对于有些方面，人脑🧠还是强于人工智能的。

对于有些东西，类似于增删改查的语句写法问题，能够记住一定是强于现场发挥的，至少在速度上就会有所优势。

归根到底，对于下面SQL的语句练习一定要达到炉火纯青的地步，这样才会对数据库有更加深入的研究。

一、“增，删，改，查”

作为后端开发，感觉自己永远重复着“增删改查”，这些基础的操作占据了自己大部分工作时间，看到其他人成为技术大牛，而个人技术水平永远停在原地，也会很迷茫…

当你工作久了是否会发现你的代码越来越复杂？文件的上传下载怎么提高效率？消息推送的实时性怎么保证？同时使用人数过多，服务器爆炸怎么办…需要学习的太多了。

如果这是你还只是会“增删改查”，那估计你和离职就不远了。

但是，学会“增删改查”是基础，是根基，是基本功，如果连“增删改查”都不会的话，你和别人介绍说，你最近又研究了一门新语言，又做了一个项目，你觉得别人会相信啵？

所以说，学习就像修炼武功一般，先从基本功学起，接下来有10道小例题，来开始愉快的学习之旅吧。

二、例题练习（每日十题）

Example 1.1:建立一个用户booksql，密码是123456

CREATE USER 'booksql'@'%' IDENTIFIED BY ‘123456’;

Example 1.2:给这个用户赋予权限，所有权限都给这个用户

GRANT ALL PRIVILEGES
ON    *.*
TO    'booksql'@'%'
WITH  GRANT OPTION;

Example 1.3:建立数据库tennis，字符集utf8，可以支持中文

CREATE DATABASE TENNIS default character set utf8;

Example 1.4:USE TENNIS

USE TENNIS

Example 1.5:CREATE

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

Example 1.6:INSERT

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

Example 1.7:SELECT

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

Example 1.8:

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

Example 1.9:

SELECT   *
FROM     PENALTIES

Example 1.10:

SELECT   33 * 121

小结

人生如一场修行，
得意时，一日看尽长安花，
艰难时，潦倒新停浊酒杯。
哪怕畏途巉岩不可攀，也要会当凌绝顶。

人生太多不如意，更正心态至关重要，与其“杞人忧天”，不如，“厚积薄发”。去接纳生活给你的种种“启发”，即便困难一波未平，一波又起，它不过是一个瞬时罢了。现在的我很糟糕，真的糟糕，虽然糟糕，不要气馁，坚持住，它终将会被坚定不移的信念打败。就像战胜黑暗，迎接黎明一样。

吃不了学习的苦，就要吃生活的苦

别懒了，造起来吧😏。