📚 preface
What is SQL?
Official explanation: SQL (Structured Query Language) is used to manage relational database management system (RDBMS).
What can SQL be used for?
Generally speaking, it allows you to access and process the database, including data insertion, query, update and deletion.
SQL is almost a necessary skill for production, R & D and other posts in Internet companies. If you don't know SQL, you may not be able to do anything. SQL can be used as a tool to help you complete your work and create value.
❤️ Situational Learning
Let's take a look at how Xiaomei learns SQL on a zero basis:
Using this SQL statement, you can view the current time of the database. Of course, you can also replace SYSDATE with anything.
For example:
Calculator: SELECT 365 * 24 FROM dual;
This SQL is often used in our development. It's easy and interesting to be the first SQL to get started. Xiaomei, do you think it's difficult?
CREATE TABLE bookshelf ( BOOK_ID NUMBER, BOOK_NAME VARCHAR2(100), BOOK_TYPE VARCHAR2(100), AUTHOR VARCHAR2(100), INTIME DATE );
The table name is bookshelf, and the columns are: Book id, book name, book type, author and warehousing time. Use the SELECT syntax learned above to query this table:
SELECT * FROM bookshelf;
You can see that the newly created bookshelf table has no records. Now, an empty bookshelf has been added in the library. Do you need to put books on the bookshelf? At this time, you need to use the add operation.
INSERT INTO bookshelf (book_id, book_name, book_type, author, intime) VALUES (1, 'Float', 'novel', 'Margaret·Mitchell', SYSDATE); COMMIT;
Basic syntax for adding:
insert into Table name (Column names to be inserted, separated by commas) values (The value of the corresponding column name);
Through sql query, it is found that the book gone with the wind has been put on the shelf for everyone to borrow and view.
Basic syntax of the modification:
UPDATE Table name SET Listing = New value;
Basic syntax of deletion:
DELETE FROM Table name;
Now let's simulate the scenario:
1. Modified author name:
UPDATE bookshelf SET author='Margaret Mitchell'; COMMIT;
2. Off shelf books:
DELETE FROM bookshelf; COMMIT;
Through the above two scenarios, two operations of modification and deletion are demonstrated.
3 books on the shelf first:
INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (1,'Float','novel','Margaret·Mitchell',SYSDATE); INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (2,'Love of the city','Love novel','Zhang Ailing',SYSDATE); INSERT INTO bookshelf (book_id,book_name,book_type,author,intime) VALUES (3,'Passing through your world','short story','Zhang Jiajia',SYSDATE); COMMIT;
See love in the city:
SELECT * FROM bookshelf WHERE BOOK_NAME = 'Love of the city';
Update gone with the wind:
UPDATE bookshelf SET author='Margaret Mitchell' WHERE book_name = 'Float'; COMMIT;
Delete "passing through your world":
DELETE FROM bookshelf WHERE book_name = 'Passing through your world'; COMMIT;
Through the chestnuts above 🌰， You should have a good understanding of WHERE query criteria.
☀️ Interesting SQL
At the end of the article, I'd like to give you some interesting SQL sentences for drawing:
⭐ Five pointed star:
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;
📢 Note: adjust the number 5 in the middle of the period. You can also output 7-angle stars and 9-angle stars!
🇨🇳 Olympic rings:
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;
📅 Print calendar of current month:
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;
This article introduces the addition, deletion, modification and query of SQL in a relaxed and pleasant situational dialogue! Hope to give readers a different experience