# SQL notes
## https://pjchender.dev/database/sql-query/
## ctrl+/: 註解/註解取消
# 撈資料
## select...from

-- select ... from
select * from 'employee'
-- 按照 salary 由低到高排序
select * from 'employee' order by 'salary'; 
-- 按照 salary 由高到低排序
select * from 'employee' order by 'salary' desc;
-- 取得薪水前三高的員工
select * from 'employee' order by 'salary' desc 
limit 3;
-- 取得所有員工名字
select 'name' from 'employee';
-- 取得所有員工性別(不重複) 加 distinct
select distinct 'sex' from 'employee';

- 列出 column 中不重複的值
SELECT DISTINCT column FROM table_name;

-- 列出 column_1 且 column_2 不重複的值（會結合 column_1 和 column_2）
SELECT DISTINCT column_1, column_2, ... FROM table_name;

--
SELECT DISTINCT ON (column1) column_alias FROM table_name ORDER BY column1, column2; 

## 聚合函數

-- 取得員工人數(*換成employee的其他欄位也可)
select count(*) from 'employee';
-- 取得出生於1970-01-01 之後的女性員工人數
select count(*) 
from 'employee'
where 'birth_date' > '1970-01-01' and 'sex'= 'F' 

SELECT column1, column2, …
FROM table_name
WHERE condition;
-- =:等於
-- >
-- <
-- >=
-- <=
-- <> 不等於
-- BETWEEN
-- LIKE
-- IN
-- 
-- 驗證是否有重複資料
SELECT COUNT(column1) FROM table_name;
SELECT COUNT(DISTINCT column1) FROM table_name

-- Select中再Select 
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT column1 FROM table_name); 

-- 取得員工人數平均薪水
select avg('salary') from 'employee';
-- 取得員工人數總和薪水
select sum('salary') from 'employee';
-- 取得員工薪水最高者
select max('salary') from 'employee';
-- 取得員工薪水最低者
select min('salary') from 'employee';

## wildcards 萬用字元 %代表多個字元, _代表一個字元

-- 取得電話號碼尾數是 335 的客戶
select * from 'cilent' where 'phone' like '%335';
-- 取得電話號碼開頭是 254 的客戶
select * from 'cilent' where 'phone' like '254%';
-- 取得生日在12月的員工
select * from 'cilent' where 'birthday' like '_____12%';
-- group by 進行分組
SELECT column_1, aggregate_function(column_2)
FROM table
GROUP BY column_1;

## union 聯集(把兩個搜尋的結果合併一起)

-- 1.員工名字 union 客戶名字(把3個搜尋的結果合併一起)
select 'name' 
from 'employee'
union 
select 'client_name'
from 'client'
union 
select 'branch_name'
from 'branch'

-- 2.員工id+員工名字  union 客戶id+客戶名字
--  as...  將合併後的表欄位名稱改成...
select 'employee_id' as 'total_id','name' as total_name 
from 'employee'
union 
select 'client_id','client_name'
from 'client'

## join 連接

-- 取得所有部門的名字
select * from 'employee'
join 'brach'
on 'employee'.'emp_id'= 'brach'.'manager_id';

-- left join(左邊的表格不管條件有無成立，都會回傳所有資料給我們)
select 'employee'.'emp_id', 'employee'.'name', 'brach'.'branch_name'
from 'employee' left join 'branch'
on 'employee'.'emp_id'='branch'.'manager_id';

-- right join(右邊的表格不管條件有無成立，都會回傳所有資料給我們)
select 'employee'.'emp_id', 'employee'.'name', 'brach'.'branch_name'
from 'employee' right join 'branch'
on 'employee'.'emp_id'='branch'.'manager_id';

## subquery 子查詢

## on delect

## 建立資料庫

CREATE DATABASE `sql_tutorial`;
SHOW databases;
USE `sql_tutorial`;

CREATE TABLE `student`(
  `student id` INT,
  `name` VARCHAR(20),
  `major` VARCHAR(20),
  PRIMARY KEY(`student id`)
);

DESCRIBE `student`;
DROP TABLE `student`;

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

INSERT INTO `student` VALUES(3,'小綠',NULL);
INSERT INTO `student` (`name`,`major`,`student id`) VALUES(NULL,'英語',5);
INSERT INTO `student` (`name`,`major`,`student id`) VALUES('小紅','英語',5);
INSERT INTO `student` (`name`,`major`,`student id`) VALUES('小綠','歷史',6);

DROP TABLE `student`;
SELECT * FROM `student`;


-- constraints 限制 約束
CREATE TABLE `student`(
  `student id` INT,
  `name` VARCHAR(20) NOT NULL,
  `major` VARCHAR(20) UNIQUE,
  PRIMARY KEY(`student id`)
);

CREATE TABLE `student`(
  `student id` INT AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `major` VARCHAR(20) DEFAULT '歷史',
  PRIMARY KEY(`student id`)
);


INSERT INTO `student`(`name`,`major`)VALUES('小白','英語');
INSERT INTO `student`(`name`,`major`)VALUES('小綠','國文');
INSERT INTO `student`(`name`,`major`)VALUES('小黑','數學');

## 修改 刪除資料

SET SQL_SAFE_UPDATES=0;

CREATE TABLE `student`(
  `student id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `major` VARCHAR(20),
  `score` INT
);

INSERT INTO `student`(`name`,`major`, `score`)VALUES('小白','數學',84);
INSERT INTO `student`(`name`,`major`, `score`)VALUES('小a','國文',86);
INSERT INTO `student`(`name`,`major`, `score`)VALUES('小b','英語',81);
INSERT INTO `student`(`name`,`major`, `score`)VALUES('小c','英語',81);
SELECT * FROM `student`;

UPDATE `student`
SET `major` ='英語文學'
WHERE `student id`= 3 OR `major` =`化學`;

UPDATE `student`
SET `name` ='小灰', `major`='物理'
WHERE `student id`= 1;

DELETE FROM `student`
WHERE `student id`= 2;

DELETE FROM `student`;

## 取得資料

select * 
FROM `student` 
ORDER BY `score` ;

select * 
FROM `student` 
LIMIT 1;

select * 
FROM `student` 
WHERE `major` ='英語' and `score`<>81;

select * 
FROM `student` 
WHERE `major` IN('歷史','英語','生物');