- order by （排序 默認(rèn)升序辅鲸，des為降序）
select * from students where stu_name ='李四' order by grade_id DESC;
SELECT
s.subj_no,
r.stu_result,
AVG(r.stu_result)
FROM
subjects s,
result r
WHERE
s.subj_no = r.subj_no
GROUP BY s.subj_no;
- having （彌補where無法完成的函數(shù)條件）
SELECT
department_id 部門,
COUNT(1) 員工總數(shù)
FROM
employees
GROUP BY
department_id
HAVING COUNT(employee_id)>5;
- 聚合函數(shù)
avg() 平均值
max() 最大值
min() 最小值
sum() 求和
count() 計數(shù)
SELECT avg(salary) FROM employees ;
SELECT employee_id,salary,last_name,
(salary + salary *ifnull(commission_pct,0))*12 FROM employees;
- 字符串函數(shù)
concat() 字符串拼接
substr() 截雀（不是下標(biāo)）
lpad() 左空位補充
rpad() 右空位補充
select
TRUNCATE (12.2367, -1) 截取數(shù)字,
TRUNCATE (12.36, 0) 截取數(shù)字
FROM
DUAL;
SELECT LPAD(salary,10,'0') FROM employees;
SELECT RPAD(salary,10,'*') FROM employees;
- 數(shù)值函數(shù)
round() 四舍五入
mod() 求余
ceil() 向上取整
floor() 向下取整
rand() 隨機數(shù)
truncate()截取
SELECT
round(234.455, 2) 四舍五入,
ROUND (24.535) 四舍五入,
MOD (14, 3) 求余,
CEIL(253.2) 向上取整,
FLOOR(25.6) 向下取整,
RAND() 隨機數(shù)
FROM
DUAL;
- 分頁查詢（limit (起始行祠墅，條數(shù)),包含起始行）
SELECT DISTINCT
r.stu_no,
st.stu_name,
su.subj_name,
r.stu_result
FROM
result r,
subjects su,
students st
WHERE
r.subj_no = su.subj_no
AND r.stu_no = st.stu_no
AND su.subj_name = '數(shù)據(jù)庫結(jié)構(gòu)-2'
ORDER BY
r.stu_result DESC
LIMIT 0,1;
