SQL是什么?
官方解释:SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统(RDBMS)。
SQL能用来干什么?
通俗的讲:让您可以访问和处理数据库,包括数据插入、查询、更新和删除。
下面让我们看看小美是如何零基础学习SQL的:
例如:
计算器:SELECT 365 * 24 FROM dual;
CREATE TABLE bookshelf
(
BOOK_ID NUMBER,
BOOK_NAME VARCHAR2(100),
BOOK_TYPE VARCHAR2(100),
AUTHOR VARCHAR2(100),
INTIME DATE
);
表名为:bookshelf
,有列:图书id,图书名称,图书类型,作者,入库时间。通过上面学习的 SELECT
语法,来查询一下这张表:
SELECT * FROM bookshelf;
INSERT INTO bookshelf
(book_id,
book_name,
book_type,
author,
intime)
VALUES
(1,
'飘',
'长篇小说',
'玛格丽特·米切尔',
SYSDATE);
COMMIT;
增 的基本语法:
insert into 表名 (需要插入的列名,用逗号隔开) values (对应列名的值);
UPDATE 表名 SET 列名 = 新的值;
删 的基本语法:
DELETE FROM 表名;
现在来模拟一下场景:
1、修改作者名:
UPDATE bookshelf SET author='Margaret Mitchell';
COMMIT;
DELETE FROM bookshelf;
COMMIT;
INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (1,'飘','长篇小说','玛格丽特·米切尔',SYSDATE);
INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (2,'倾城之恋','爱情小说','张爱玲',SYSDATE);
INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (3,'从你的全世界路过','短篇小说','张嘉佳',SYSDATE);
COMMIT;
SELECT * FROM bookshelf WHERE BOOK_NAME = '倾城之恋';
UPDATE bookshelf SET author='Margaret Mitchell' WHERE book_name = '飘';
COMMIT;
DELETE FROM bookshelf WHERE book_name = '从你的全世界路过';
COMMIT;
文末,赠送给各位看官几个一句SQL画图的趣味小SQL:
⭐️ 五角星:
WITH a AS
(SELECT DISTINCT round(SUM(x) over(ORDER BY n)) x,
round(SUM(y) over(ORDER BY n)) y
FROM (SELECT n,
cos(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) y
FROM (SELECT rownum - 1 n
FROM all_objects
WHERE rownum <= 20 * 5)))
SELECT REPLACE(sys_connect_by_path(point,
'/'),
'/',
NULL) star
FROM (SELECT b.y,
b.x,
decode(a.x,
NULL,
' ',
'*') point
FROM a,
(SELECT *
FROM (SELECT rownum - 1 + (SELECT MIN(x)
FROM a) x
FROM all_objects
WHERE rownum <= (SELECT MAX(x) - MIN(x) + 1
FROM a)),
(SELECT rownum - 1 + (SELECT MIN(y)
FROM a) y
FROM all_objects
WHERE rownum <= (SELECT MAX(y) - MIN(y) + 1
FROM a))) b
WHERE a.x(+) = b.x
AND a.y(+) = b.y)
WHERE x = (SELECT MAX(x)
FROM a)
START WITH x = (SELECT MIN(x)
FROM a)
CONNECT BY y = PRIOR y
AND x = PRIOR x + 1;
🇨🇳 奥运五环:
WITH a AS
(SELECT DISTINCT round(a.x + b.x) x,
round(a.y + b.y) y
FROM (SELECT (SUM(x) over(ORDER BY n)) x,
round(SUM(y) over(ORDER BY n)) y
FROM (SELECT n,
cos(n / 30 * 3.1415926) * 2 x,
sin(n / 30 * 3.1415926) y
FROM (SELECT rownum - 1 n
FROM all_objects
WHERE rownum <= 30 + 30))) a,
(SELECT n,
(SUM(x) over(ORDER BY n)) x,
round(SUM(y) over(ORDER BY n)) y
FROM (SELECT n,
cos(m / 3 * 3.1415926) * 2 * 15 x,
sin(m / 3 * 3.1415926) * 15 y
FROM (SELECT CASE
WHEN rownum <= 2 THEN
3
WHEN rownum = 3 THEN
-2
ELSE
-6
END m,
rownum - 1 n
FROM all_objects
WHERE rownum <= 5))) b)
SELECT REPLACE(sys_connect_by_path(point,
'/'),
'/',
NULL) star
FROM (SELECT b.y,
b.x,
decode(a.x,
NULL,
' ',
'*') point
FROM a,
(SELECT *
FROM (SELECT rownum - 1 + (SELECT MIN(x)
FROM a) x
FROM all_objects
WHERE rownum <= (SELECT MAX(x) - MIN(x) + 1
FROM a)),
(SELECT rownum - 1 + (SELECT MIN(y)
FROM a) y
FROM all_objects
WHERE rownum <= (SELECT MAX(y) - MIN(y) + 1
FROM a))) b
WHERE a.x(+) = b.x
AND a.y(+) = b.y)
WHERE x = (SELECT MAX(x)
FROM a)
START WITH x = (SELECT MIN(x)
FROM a)
CONNECT BY y = PRIOR y
AND x = PRIOR x + 1;
SELECT MAX(decode(dow,
1,
d,
NULL)) sun,
MAX(decode(dow,
2,
d,
NULL)) mon,
MAX(decode(dow,
3,
d,
NULL)) tue,
MAX(decode(dow,
4,
d,
NULL)) wed,
MAX(decode(dow,
5,
d,
NULL)) thu,
MAX(decode(dow,
6,
d,
NULL)) fri,
MAX(decode(dow,
7,
d,
NULL)) sat
FROM (SELECT rownum d,
rownum - 2 + to_number(to_char(trunc(SYSDATE,
'MM'),
'D')) p,
to_char(trunc(SYSDATE,
'MM') - 1 + rownum,
'D') dow
FROM all_objects
WHERE rownum <=
to_number(to_char(last_day(to_date(SYSDATE)),
'DD')))
GROUP BY trunc(p / 7)
ORDER BY sun NULLS FIRST;
增删改查
操作!希望能给读者不一样的体验~
联系客服