Hi SQL。

O

KRs

MySQL 100%
Mongo 80%
Oracle 10%
Container中使用SQL語法 80%

注意

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

MySQL

$ 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

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


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：用於修改資料表的結構，例如新增欄位、修改欄位資料型別、刪除欄位、新增索引等。這個指令用於變更資料表的結構和屬性，而不是對資料進行更新。

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

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

select top 2 *
from Table
order by col desc

-- ascerding order by default or add asc

Group By & Having

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.

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

inner/outer join
on

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

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

Exists
Date

1	select getdate()

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

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

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.

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

$ 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

sql to mongo

時程: 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

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.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 (函數條件);

SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name
HAVING SUM(Sales) > 1500;

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

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

科德正規化

第一正規化（1NF）：欄位不重複
確保每個資料表中的每個欄位都是不可再分的原子值，並消除重複的組。
第二正規化（2NF）：只跟主鍵相依
確保每個資料表中的非主鍵欄位完全依賴於主鍵，並消除部分相依性。
第三正規化（3NF）：
確保每個資料表中的非主鍵欄位互不相依，並消除非鍵字段之間的傳遞性相依性。

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

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