Lunski's Clutter

This is a place to put my clutters, no matter you like it or not, welcome here.

0%

SQL

Hi SQL。

O

SQL語法與Mongo

KRs

  1. MySQL 100%
  2. Mongo 80%
  3. Oracle 10%
  4. Container中使用SQL語法 80%

注意

Join ==(inner) join
left/right join == left/right (outer) join
需要注意空格

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ docker pull mysql

$ docker run --name lunski-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest
5ced7384a860f02ab35c8993e27b766fad7be57e3d39cb1e7898fced47b80693

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5ced7384a860 mysql:latest "docker-entrypoint.s…" 55 seconds ago Up 54 seconds 3306/tcp, 33060/tcp lunski-mysql

$ docker exec -it lunski-mysql bash
bash-4.4# mysql -h localhost -u root -p
>

$ mysql>
  • Create DB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> create database mytestdb;  

-- create database mytestdb default character set 'utf8';

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytestdb |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.84 sec)

mysql> use mytestdb;

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mytestdb |
+------------+
1 row in set (0.01 sec)

Database changed

  • USE db

  • Create Table & insert into table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

mysql> create table mytesttable
-> (
-> rollno int,
-> firstname varchar(50),
-> lastname varchar(50)
-> );
Query OK, 0 rows affected (0.25 sec)

or

create table mytesttable(
rollno int primary key auto_increment,
firstname varchar(50) not null,
lastname varchar(50) not null,
CreatedDTTM DATETIME DEFAULT NOW()
);

SELECT CONCAT(firstname, ' ', lastname) as fullname FROM mytesttable;

-- salary numeric(10, 2) not null,
-- IsDeleted bit not null default 0,
-- 8 digits for integer part and 2 digits for fraction part
-- varchar cost less than char

mysql> SHOW COLUMNS FROM mytesttable;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| rollno | int | YES | | NULL | |
| firstname | varchar(50) | YES | | NULL | |
| lastname | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> select rollno, firstname, lastname from mytesttable;
Empty set (0.00 sec)

-- select distinct lastname from mytesttable;
-- select sum(rollno) as s from mytesttable;
-- sum(), min(), max(), avg(), count()

mysql> insert into mytesttable(rollno, firstname, lastname) values(1, 'Eric', 'Lu');
Query OK, 1 row affected (0.03 sec)

-- insert into mytesttable(rollno, firstname, lastname, CreatedDTTM) values(1, 'Eric', 'Lu', now());

mysql> select firstname from mytestdb.mytesttable;
+-----------+
| firstname |
+-----------+
| Eric |
+-----------+
1 row in set (0.01 sec)

-- select * from mytestdb.mytesttable order by rollno desc;

mysql> select firstname from mytestdb.mytesttable where lastname like 'hsu';
Empty set (0.01 sec)

mysql> select firstname from mytestdb.mytesttable where lastname like 'u';
Empty set (0.00 sec)

mysql> select firstname from mytestdb.mytesttable where lastname like 'Lu';
+-----------+
| firstname |
+-----------+
| Eric |
+-----------+
1 row in set (0.00 sec)

mysql> insert into mytesttable(rollno, firstname, lastname) values(2, 'Shvara', 'Lunski');
Query OK, 1 row affected (0.04 sec)

mysql> insert into mytesttable(rollno, firstname, lastname) values(3, 'Lunski', 'Henry');
Query OK, 1 row affected (0.01 sec)

  • Update && Alert

UPDATE TABLE:用於更新資料表中的資料,根據指定的條件更新欄位的值。這個指令用於修改資料表中的資料,而不是變更資料表的結構。

ALTER TABLE:用於修改資料表的結構,例如新增欄位、修改欄位資料型別、刪除欄位、新增索引等。這個指令用於變更資料表的結構和屬性,而不是對資料進行更新。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- UPDATE TABLE: 更新資料
UPDATE Customers
SET Email = 'example@email.com'
WHERE CustomerID = 123;

-- ALTER TABLE: 新增欄位
ALTER TABLE Customers
ADD Email VARCHAR(50);

mysql> update mytestdb.mytesttable set firstname = 'shvara2' where rollno = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select firstname from mytestdb.mytesttable;
+-----------+
| firstname |
+-----------+
| Eric |
| Shvara |
| shvara2 |
+-----------+
3 rows in set (0.00 sec)

-- alter table mytestdb.mytesttable auto_increment = 100;
  • where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> select firstname from mytestdb.mytesttable where rollno =2;
+-----------+
| firstname |
+-----------+
| Shvara |
+-----------+
1 row in set (0.01 sec)

-- we can use <, >, <> too
-- select * from mytestdb.mytesttable where CreatedDTTM between '2001-01-21' and '2001-03-21';
-- select * from mytestdb.mytesttable where Month(CreatedDTTM) = 2;

mysql> select firstname from mytestdb.mytesttable where rollno in(2,3);
+-----------+
| firstname |
+-----------+
| Shvara |
| Lunski |
+-----------+
2 rows in set (0.00 sec)

-- not in()

-- where firstname = 'Shvara' or firstname = 'Lunski';

mysql> select firstname from mytestdb.mytesttable where lastname like 'LU';
+-----------+
| firstname |
+-----------+
| Eric |
+-----------+
1 row in set (0.01 sec)

-- sql is case free

mysql> select firstname from mytestdb.mytesttable where lastname like '%u%';
+-----------+
| firstname |
+-----------+
| Eric |
| Shvara |
+-----------+
2 rows in set (0.00 sec)

mysql> select rollno as no from mytestdb.mytesttable;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

  • Delete
1
2
DELETE FROM table
where col = 'some'
  • Top && Order By
1
2
3
4
5
select top 2 *
from Table
order by col desc

-- ascerding order by default or add asc
  • Group By & Having
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
USE `DemoDB`;
CREATE TABLE `Student` (
`StudentID` INT PRIMARY KEY AUTO_INCREMENT,
`Name` VARCHAR(50),
`Course` VARCHAR(10),
`Score` NUMERIC(5,2)
);

-- Sample Data

INSERT INTO `Student`(`Name`, `Course`, `Score`)
VALUES('A', 'CS', 80);

INSERT INTO `Student`(`Name`, `Course`, `Score`)
VALUES('B', 'CS', 60);

INSERT INTO `Student`(`Name`, `Course`, `Score`)
VALUES('C', 'IT', 70);

INSERT INTO `Student`(`Name`, `Course`, `Score`)
VALUES('D', 'IT', 85);

INSERT INTO `Student`(`Name`, `Course`, `Score`)
VALUES('E', 'ECE', 88);

-- Group By

SELECT
`Course`,
SUM(`Score`) AS `Total Score`,
AVG(`Score`) AS `Average`,
MIN(`Score`) AS `Minimum`,
MAX(`Score`) AS `Maximum`
FROM `Student`
GROUP BY `Course`;
HAVING AVG(`Score`) > 75;

--
  • Join
    • one to one: Apply unique constraint on a foreign key.
    • one to many/many to one: Use foreign key.
    • many to many: Use junction table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
USE `DemoDB`;

-- Tables

CREATE TABLE `Course`(
`CourseID` INT PRIMARY KEY AUTO_INCREMENT,
`Name` VARCHAR(50) NOT NULL,
`Fee` NUMERIC(10, 2) NOT NULL
);

CREATE TABLE `Student`(
`StudentID` INT PRIMARY KEY AUTO_INCREMENT,
`FirstName` VARCHAR(50) NOT NULL,
`LastName` VARCHAR(50) NOT NULL
);

-- A junction table to capture many-to-many relation between
-- Course and Student.

CREATE TABLE `CourseEnrollments`(
`CourseID` INT NOT NULL ,
`StudentID` INT NOT NULL,
CONSTRAINT fk_courseid FOREIGN KEY(`CourseID`) REFERENCES `Course`(`CourseID`),
CONSTRAINT fk_studentid FOREIGN KEY(`StudentID`) REFERENCES `Student`(`StudentID`)
);

-- Sample insertions

INSERT INTO `Course`(`Name`, `Fee`)
VALUES('SQL',30);

INSERT INTO `Course`(`Name`,`Fee`)
VALUES('DS', 40);

INSERT INTO `Student`(`FirstName`,`LastName`)
VALUES('a','a');

INSERT INTO `Student`(`FirstName`,`LastName`)
VALUES('b','b');


INSERT INTO `CourseEnrollments`
VALUES(1,1);

INSERT INTO `CourseEnrollments`
VALUES(1,2);

INSERT INTO `CourseEnrollments`
VALUES(2,1);

出現在JOIN後方,將兩個資料表進行串接的子句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
mysql> create table agetable
-> (
-> rollno int,
-> age int
-> );
Query OK, 0 rows affected (0.14 sec)

-- constraint fk_rollno foreign key(rollno) references mytesttable(rollno);

mysql> insert into agetable(rollno, age) values(1, 30);
Query OK, 1 row affected (0.03 sec)

mysql> insert into agetable(rollno, age) values(2, 20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into agetable(rollno, age) values(3, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from agetable;
+--------+------+
| rollno | age |
+--------+------+
| 1 | 30 |
| 2 | 20 |
| 3 | 10 |
+--------+------+
3 rows in set (0.00 sec)

mysql> select lastname, age from mytesttable left join agetable on mytesttable.rollno=agetable.rollno;
+----------+------+
| lastname | age |
+----------+------+
| Lu | 30 |
| Lunski | 20 |
| Henry | 10 |
+----------+------+
3 rows in set (0.00 sec)

-- full join, left join, right join

  • Subqueries & in
1
Select * from Employee where DeptID in (Select DeptID from Dept where loaction = 'loc2');
1
select getdate()
1
2
3
4
5
6
7
8
mysql> DROP TABLE agetable;
Query OK, 0 rows affected (0.09 sec)

mysql> DROP TABLE mytesttable;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW TABLES;
Empty set (0.03 sec)
  • Stop
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> exit
Bye

docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5ced7384a860 mysql:latest "docker-entrypoint.s…" 6 days ago Up 6 days 3306/tcp, 33060/tcp

docker exec -it 5ced7384a860 /bin/sh

sh-4.4# mysql -u root -p
...
mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)

docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS

Question

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `Student`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(10) NOT NULL,
`score` INT NOT NULL);


CREATE TABLE `Grade`(
`start` INT NOT NULL,
`end` INT NOT NULL,
`grade` CHAR(1)
);

insert into `Student`(`name`, `score`) values('student1', 72);
insert into `Student`(`name`, `score`) values('student2', 61);
insert into `Student`(`name`, `score`) values('student3', 92);
insert into `Student`(`name`, `score`) values('student4', 67);
insert into `Student`(`name`, `score`) values('student5', 24);

-- start and end inclusive for grade.
insert into `Grade` values(40, 60, 'D');
insert into `Grade` values(61, 70, 'C');
insert into `Grade` values(71, 80, 'B');
insert into `Grade` values(81, 100, 'A');

1.Display the student details along with their grade.

1
2
3
4
5
6
alter table Student add Grade Varchar(30);
update Student set Grade='A' where score>=81 and score <= 100;
update Student set Grade='B' where score>=71 and score <= 80;
update Student set Grade='C' where score>=61 and score <= 70;
update Student set Grade='D' where score>=40 and score <= 60;
select * from Student;

2.Display the list of students who got grade ‘C’.

1
select name from Student where Grade = 'C';

3.Display the list of Sstudents who did not get any grade.

1
select name from Student where Grade is null;

4.Display the average, minimum and maximum score of all the students.

1
select avg(score) as avg, min(score) as min, max(score) as max from Student;

Mongo

NoSQL種類

  1. Key/Value Type: Amazon DynamoDB
  2. Column-Based: BigTable
  3. Document-Based: MongoDB
  4. Graph: Neo4j
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
$ docker pull mongo:4.1

$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest 4073e6a6f542 3 days ago 530MB
mongo 4.1 d93e3cc722d8 3 years ago 360MB

$ docker run --name mongo4 -v $(pwd)/data:/data/db -d -p 27017:27017 --rm mongo:4.1
6e69dfcd0e0d67ef9a663ea474882432575904492feb7b59231efca02963851e

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
6e69dfcd0e0d mongo:4.1 "docker-entrypoint.s…" 45 seconds ago Up 44 seconds 0.0.0.0:27017->27017/tcp mongo4

$ docker exec mongo4 mongo --eval "print(version())"
MongoDB shell version v4.1.13
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("8c059f6b-5c9d-4832-9ed4-63d9a823e4ec") }
MongoDB server version: 4.1.13
4.1.13

$ docker exec -it mongo4 bash
root@6e69dfcd0e0d:/# mongo
...
> db.createCollection("people")
{ "ok" : 1 }
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
people 0.000GB

> db.people.insertOne( {
... user_id: "abc123",
... age: 55,
... status: "A"
... } )
{
"acknowledged" : true,
"insertedId" : ObjectId("641ecaededac29be1c3a2f4b")
}
> exit
bye

> use admin
switched to db admin
> db.shutdownServer()
> docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

時程: 2023/3/1- 2023/4/1

  • MySQL: 行DBMS
  • ClickHouse: 列DBMS,列式在查詢較快速

Oracle

CREATE DATABASE databasename;

Select

  • DISTINCT
    1
    SELECT DISTINCT Store_Name FROM Store_Information; -- 單獨條目 
  • AVG (‘欄位名’)
  • COUNT (‘欄位名’)
  • MAX (‘欄位名’)
  • MIN (‘欄位名’)
  • SUM (‘欄位名’)
    1
    SELECT SUM (Sales) FROM Store_Information;

Update

1
2
3
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

Delete

1
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Insert

1
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)

Join

1
2
3
4
5
6
7
8
9
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

-- 實務上用別名,可以有多個From,並將Inner Join寫法的ON放入Where, 但要清楚關聯

SELECT O.OrderID, C.CustomerName, O.OrderDate
FROM Orders O, Customers C
Where O.CustomerID = C.CustomerID

Where

  • IN (‘值1’,’值2’)

    1
    2
    3
    SELECT *
    FROM Store_Information
    WHERE Store_Name IN ('Los Angeles', 'San Diego'); -- 選擇需要的值
  • BETWEEN ‘起日’ AND ‘終日’/‘字母’ AND ‘字母’

    1
    2
    3
    SELECT *
    FROM Store_Information
    WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';
  • LIKE

    • % (百分比符號):代表零個、一個、或數個字母。
    • _ (底線):代表剛好一個字母。
      1
      2
      3
      SELECT first_name
      FROM employees
      WHERE first_name LIKE '_A%'

ORDER BY “欄位名” [ASC, DESC]

1
2
3
SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY 2 DESC;

GROUP BY “欄位名”;

1
2
3
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name;

HAVING (函數條件);

1
2
3
4
SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name
HAVING SUM(Sales) > 1500;

Where 1=1永遠True, 如果之後and的句子有錯不會造成語法錯誤。
ref

:variable
命名查詢參數的標籤,不是查詢實際語法的一部分。在實際執行查詢之前,該標記將替換為程式碼中指定的某個值。
ref

CASE WHEN

1
2
3
4
5
6
7
SELECT Status
CASE
WHEN STATUS = 'V' THEN '有效'
WHEN STATUS = 'M' THEN '移除'
ELSE '未知'
END AS 狀態 -- 新增一欄
FROM Table;

LISTAGG && TO_CHAR

1
2
3
4
5
SELECT 
LISTAGG(TO_CHAR(SDATE, 'YYYY/MM/DD') || '~' || TO_CHAR(EDATE, 'YYYY/MM/DD'), ', ')
WITHIN GROUP (ORDER BY SDATE) AS 日期區間
FROM
Table;

要組更長字串須從LISTAGG改XMLAGG

https://geek-docs.com/oracle/oracle-questions/736_oracle_xmlagg_with_rtrim_issue.html

1
2
3
4
5
6
7
8
SELECT 
RTRIM( -- 去除字符串末尾的逗號的函數。
XMLAGG( -- 將多行資料合併成一個 XML
XMLELEMENT(E, TO_CHAR(t.field) || ',') -- 將每個field轉換為字符串並添加逗號,包在 XML 元素 <E>
).EXTRACT('//text()').getClobVal(), -- 從 XML 中提取所有文本內容,忽略 XML 標籤, 將結果轉換為 CLOB(字符大對象)類型。
',') AS CNAMES
FROM
Table t;
  • 因為中文名稱太長, 無法用listagg, 改用XMLAGG
  • CLOB是Oracle目前取代LONG的做法, 可以存更多字, 建立索引, 還可以搭配Join

NVL

1
2
3
4
SELECT NVL(comm, 0) 將空值轉換為指定的替代值
CASE WHEN s.NDA_RECORD_ID IS NULL THEN NULL ELSE (SELECT TO_CHAR(VALID_SDATE, 'YYYY/MM/DD')) END
可以改為
NVL(s.NDA_RECORD_ID, (SELECT TO_CHAR(VALID_SDATE, 'YYYY/MM/DD')))

科德正規化

  • 第一正規化(1NF): 欄位不重複
    確保每個資料表中的每個欄位都是不可再分的原子值,並消除重複的組。

  • 第二正規化(2NF):只跟主鍵相依
    確保每個資料表中的非主鍵欄位完全依賴於主鍵,並消除部分相依性。

  • 第三正規化(3NF):
    確保每個資料表中的非主鍵欄位互不相依,並消除非鍵字段之間的傳遞性相依性。

其他正規化規則還包括巴斯-柯德正規化(BCNF)、第四正規化(4NF)、第五正規化(5NF)等,這些規則解決了更高級別的相依性和冗余性問題。


如果你覺得這篇文章很棒,請你不吝點讚 (゚∀゚)

Welcome to my other publishing channels