YOUTUBE SQL 3hr
https://www.youtube.com/watch?v=gvRXjsrpCHw
constraints 限制約束
unique 唯一
not null
auto_increment
———————————-
set sql_safe_updates=0;
create table `student`(
`student_id` INT PRIMARY KEY,
`name` varchare(20),
`major` varchare(20),
`score` INT
);
select * from `student`;
update `student` set `major` = ‘英語文學’ WHERE `major` = `英語`;
delete from `student` where `student_id`=1`;
———————————-
select * form `student` ORDER BY `score` DESC; #high–>low 排序
select * form `student` LIMIT 3;
select * form `student` WHERE ‘major’ IN(‘英語’,'數學’,'生物’);
select * form `student` WHERE ‘major’ = ‘英語’ OR ‘major’ = ‘數學’ OR ‘major’ = ‘生物’;
上面2種相同
———————————-1:45
FOREIGN KEY
CREATE TABLE ‘branch’(
‘branch_id’ INT PRIMARY KEY,
‘branch_name’ varchare(20),
‘manager_id’ INT’
FOREIGN KEY (‘manager_id’) REFERENCES ‘employee’(‘emp_id’) ON DELETE SET NULL
#FORIGN KEY ‘ID ‘ REFERENCES #對應 到 表格 (屬性)
CASCADE
———————————-1:53
SELECT DISTINCT ‘branch_id’ from ‘employee’;
DISTINCT 不重複
——————————
取得員工人數
SELECT COUNT(*) FROM ‘employee’;
取得所有 1970-01-01 女員工人數
SELECCT COUNT(*) FROM ‘employee’ WHERE ’1970-01-01′> AND ‘sex’ = ‘F’;
取得員工薪水
SELECT AVG(‘salary’) FROM ‘employee’;
取得員工薪水SUM
SELECT SUM(‘salary’) FROM ‘employee’;
取得員工薪水MAX
SELECT MAX(‘salary’) FROM ‘employee’;
取得員工薪水MIN
SELECT MIN(‘salary’) FROM ‘employee’;
——————————2:02
wildcards %多各字元 , _一個字元
取得電話號碼尾數335客戶
SELECT * FROM ‘client’ WHERE ‘phone’ like ‘%335′;
——————————2:09
UNION 聯集 **資料形態要一樣
員工名字 union 客戶名字
SELECT `name` FROM ‘employee’ UNION SELECT ‘client_name’ FROM ‘client’;
員工id +員工名字 + union 客戶id名字 +客戶名字
SELECT ‘emp_id’ AS ‘total_id’ , ‘name’ AS ‘total_name’
FROM ‘employee’
UNION
SELECT ‘client_id’,'cleint_name’
FROM ‘Client’;
員工薪水 union 銷售金額
SELECT ‘salary’
FROM ‘employee’
UNION
SELECT ‘TOTAL_SALES’
FROM ‘work_with’;
——————————2:16
JOIN 連接
SELECT * FROM ‘employee’ JOIN ‘branch’ ON ‘employee’.'emp_id’ = ‘branch’.'manager_id’; #on 條件
LEFT JOIN 左邊一律顯示,但右邊要符合格式才顯示
SELECT ‘employee’.'emp_id’,'employee’.'name’,'branch’.'branch_name’
FROM ‘employee’ LEFT JOIN ‘branch’
ON ‘employee’.'emp_id’ = ‘branch’.'manager_id’;
——————————2:28
sunqrery
尋找研發部經理名字
SELECT ‘name’ FROM ‘employee’
WHERE ‘emp_id’=(
SELECT ‘manager_id’
FROM ‘branch’
WHERE ‘brance_name’='研發’
);
尋找單一客戶銷售金額超過5萬的員工名字 (IN 搜尋結果不只一筆)
SELECT ‘name’ FROM ‘employee’
WHERE ‘emp_id’ IN(
SELECT ‘emp_id’ FROM ‘works_with’
WHERE total_sales >50000
);
——————————2:32
on delete set null #foreign key 對應不到 update null
on delete cascade #foreign key 對應不到跟著刪除