常用的SQL Parsing - Part2

繼之前 常用的SQL Parsing - Part1的簡單介紹，知道怎麼創建database and table，這篇文章接著協助大家create column and row

• Background: 怎麼有效應用columns/rows
• Command line:
  • How to create/remove/drop/delete columns/rows?

善用＊(star mark)隨時掌握table資訊

SELECT * from `student`;

新增/移除column(ex: gpa)

ALTER TABLE <*table name*> ADD/DROP <*column name*>

ALTER TABLE student ADD gpa DECIMAL(3, 2);
ALTER TABLE student DROP COLUMN gpa;

加入table內數值

INSERT INTO student VALUES(3, ‘小綠’, NULL);
INSERT INTO student(name,major,student_id) VALUES(‘小藍’, ‘英文’, 4);

替代文字(Replace)

設定主修是英文的都改成英語文學
UPDATE `student`
SET `major` = "英語文學"
where  `major` = "英文";

指定狀態下替代文字(Replace under certain phonomenon)

設定只要學號是3都是主修生物

UPDATE `student`
SET `major` = “生物"
where  `student_id` = 3;

刪除

DELETE FROM `student`
WHERE `name` = "小白" AND `major` = "歷史";

排序

ORDER BY DESC/ASC(default)

設定顯示數量

LIMIT 3; #only the first 3 items

補上少設定的foreign key

ALTER TABLE `Employee`
ADD FOREIGN KEY(`branch_id`)
REFERENCES `branch`(`branch_id`)
ON DELETE SET NULL;

ALTER TABLE `Employee`
ADD FOREIGN KEY(`sup_id`)
REFERENCES `Employee`(`emp_id`)
ON DELETE SET NULL;

CREATE TABLE client(
client_id INT PRIMARY KEY,
client_name VARCHAR(20),
phone VARCHAR(20)
);

CREATE TABLE works_with(
emp_id INT,
client_idf INT,
total_sales INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id) ON DELETE CASCADE,
FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

CREATE TABLE works_with(
emp_id INT,
client_id INT,
total_sales INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY (emp_id) REFERENCES Employee(emp_id) ON DELETE CASCADE,
FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

create all the tables and let’s fill in the data

先新增部門資料先設定manager id=null再新增員工資料並改回manager id

INSERT INTO branch VALUES(1, ‘研發’ , NULL);
INSERT INTO branch VALUES(2, ‘行政’ , NULL);
INSERT INTO branch VALUES(3, ‘資訊’ , NULL);

##新增員工資料
INSERT INTO Employee VALUES(206, ‘小黃’ , ‘1998-10-08’, ‘F’, 50000, 1, NULL);
INSERT INTO Employee VALUES(207, ‘小綠’ , ‘1985-09-16’, ‘M’, 29000, 1, 206);
INSERT INTO Employee VALUES(208, ‘小黑’ , ‘2000-12-19’, ‘M’, 35000, 1, 206);
INSERT INTO Employee VALUES(209, ‘小白’ , ‘1997-01-22’, ‘F’, 39000, 1, 207);
INSERT INTO Employee VALUES(210, ‘小蘭’ , ‘1925-11-10’, ‘F’, 84000, 1, 207);

##在修改manager_id

UPDATE branch
SET manager_id = 206
WHERE branch_id = 1;

select name from Employee ;
select DISTINCT branch_id from Employee ;

SELECT COUNT(*)
FROM Employee;

select * from Employee ORDER BY salary DESC LIMIT 3;
select * from client;
select * from works_with;

select name from Employee ;
select DISTINCT branch_id from Employee ;

SELECT COUNT(sup_id)
FROM Employee;

SELECT COUNT(*) FROM Employee WHERE birth_date > ‘1970-01-01’ AND sex = ‘F’;

SELECT AVG(salary) FROM Employee;
SELECT SUM(salary) FROM Employee;
SELECT MAX(salary) FROM Employee; #max/min

SELECT * FROM client WHERE phone like ‘%22’; #% like

SELECT * FROM Employee WHERE birth_date LIKE ‘_____12%’; #_字元

union

SELECT name
FROM Employee
UNIONㄋㄋ
SELECT client_name
FROM client
UNION
SELECT branch_name
FROM branch;

AS 命名as

SELECT salary AS MONEY
FROM Employee
UNION
SELECT total_sales
FROM works_with;

連接員工表格跟部門表格

SELECT *
FROM Employee
JOIN branch
ON emp_id = manager_id;

SELECT *
FROM Employee
JOIN branch
ON Employee.emp_id = branch.manager_id;

不同的連接方式

left join/right join

SELECT *
FROM Employee LEFT JOIN branch
ON emp_id = manager_id;