SQL语句的增删改查(详细)
前言
为什么要学习 SQL 语句?
我相信很多的同学一定有一个疑问🤔️?
现在的计算机都发展得这么快,芯片和cpu的性能已经不再是以前的那种“龟速”了,而且人工智能发展得这么好,都开始逐步实现机器学习了。为啥我们还是要去学习 SQL 语句呢?
机器再怎么智能,它毕竟是机器,它并不能代替人的大脑🧠,有些人的思想和行为是机器无法去理解的,就比如说“社交牛逼症”这个东西,它就算机器再智能,也无法达到人类的这个地步。
所以说,对于有些方面,人脑🧠还是强于人工智能的。
对于有些东西,类似于增删改查的语句写法问题,能够记住一定是强于现场发挥的,至少在速度上就会有所优势。
归根到底,对于下面SQL的语句练习一定要达到炉火纯青的地步,这样才会对数据库有更加深入的研究。
一、“增,删,改,查”
作为后端开发,感觉自己永远重复着“增删改查”,这些基础的操作占据了自己大部分工作时间,看到其他人成为技术大牛,而个人技术水平永远停在原地,也会很迷茫…
当你工作久了是否会发现你的代码越来越复杂?文件的上传下载怎么提高效率?消息推送的实时性怎么保证?同时使用人数过多,服务器爆炸怎么办…需要学习的太多了。
如果这是你还只是会“增删改查”,那估计你和离职就不远了。
但是,学会“增删改查”是基础,是根基,是基本功,如果连“增删改查”都不会的话,你和别人介绍说,你最近又研究了一门新语言,又做了一个项目,你觉得别人会相信啵?
所以说,学习就像修炼武功一般,先从基本功学起,接下来有10道小例题,来开始愉快的学习之旅吧。
二、例题练习(每日十题)
Example 1.1:建立一个用户booksql,密码是123456
CREATE USER 'booksql'@'%' IDE
NTIFIED 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
小结
人生如一场修行,
得意时,一日看尽长安花,
艰难时,潦倒新停浊酒杯。
哪怕畏途巉岩不可攀,也要会当凌绝顶。
人生太多不如意,更正心态至关重要,与其“杞人忧天”,不如,“厚积薄发”。去接纳生活给你的种种“启发”,即便困难一波未平,一波又起,它不过是一个瞬时罢了。现在的我很糟糕,真的糟糕,虽然糟糕,不要气馁,坚持住,它终将会被坚定不移的信念打败。就像战胜黑暗,迎接黎明一样。
吃不了学习的苦,就要吃生活的苦
别懒了,造起来吧😏。