Open3
SQL
しみゆーSELECT
SELECT ename as '社員名', floor(sal * 1.02) as '計算後給与'
FROM emp
ORDER BY
FROMやWHEREの後に書く
SELECT ename as '従業員名', job, sal as '給料'
FROM emp
ORDER BY sal
SUM/MAX/MIN/AVG/COUNT
SELECT SUM(sal) as '合計', MAX(sal) as '最大', MIN(sal) as '最小', ROUND(AVG(sal)) as '平均', COUNT(sal) as '総数'
FROM emp
WHERE 任意の条件
WHERE - BETWEEN
SELECT ename as '従業員名', sal as '給料'
FROM emp
WHERE sal BETWEEN 200000 AND 400000
下記と同じ
WHERE 200000 <= sal AND sal <= 400000
NOTを付けて否定で検索も可能
WHERE sal NOT BETWEEN 200000 AND 400000
WHERE - LIKE
-- あいまい検索/部分検索
SELECT ename as '従業員名', sal as '給料'
FROM emp
WHERE ename LIKE '%高橋%'
-- 前方一致
SELECT ename as '従業員名', sal as '給料'
FROM emp
WHERE ename LIKE '藤%'
-- 後方一致
SELECT ename as '従業員名', sal as '給料'
FROM emp
WHERE ename LIKE '%慎之助'
GROUP BY
"GROUP BY グループ化したいカラム名"
SELECT departmentnum, AVG(sal) as '給料平均'
FROM emp
GROUP BY departmentnum
SELECT departmentnum, AVG(sal) as '給料平均'
FROM emp
WHERE 1300 < sal -- whereも使える
GROUP BY departmentnum
HAVING
HAVINGはグループ化した後に条件を指定したい場合に使う
SELECT departmentnum, AVG(sal) as '給料平均'
FROM emp
GROUP BY departmentnum
HAVING AVG(sal) > 1500
UPDATE
WHEREで条件を絞らない場合は、すべてのレコードが更新されてしまうので注意
UPDATE employees
SET sal = sal * 2
WHERE user_id = 0001