SQL 学习总结:
一、查询语句(SELECT)
1、一般用法:
SELECT Column FROM Table;
例 1:
--从员工表里,选择姓名和工资
SELECT last_name, salary FROM employees;
例 2:
--从员工表里,选择姓名和邮件,并将两列数据合成一列,以"员工邮件"为列名显示
SELECT last_name + ' 的电子邮件是: ' + last_name +
[email=]'@testage.com'[/email] AS
"员工电子邮件详细信息" FROM employees;
2、过滤重复行:
SELECT DISTINCT Column FROM Table;
例:
--统计员工表里的工种分为几类
SELECT DISTINCT job_id FROM employees;
3、增加选择条件:
SELECT ColumnFROM TableWHERE condition;
例 1:
--从员工表里,选择工资高于 5000 的员工
SELECT last_name, salary FROM employees WHERE salary > 5000;
例 2:
--从员工表里,选择工资高于 5000,并且属于 80 号部门的员工
SELECT last_name, salary, department_id
FROM employees WHERE salary > 5000 AND department_id = 80;
例 3:
--从员工表里,选择工资高于 8000,或者属于 80 号部门的员工
SELECT last_name, department_id, salary 高于 8000 或者属于 80 号部门的员工
FROM employees WHERE salary > 8000 OR department_id = 80;
例 4:
--从员工表里,选择年薪低于 36000,并且没有奖金的员工信息
SELECT salary*12年薪,*
FROM employeesWHERE salary*12 < 3000*12 AND commission_pct IS NULL;
例 5:
--从员工表里,选择工资在 3000 到 5000 之间(包含 3000 和 5000)的员工信息
SELECT *FROM employeesWHERE salary BETWEEN 3000 AND 5000;
例 6:
--从员工表里,选择属于 20 号部门,30 号部门,80 号部门的员工
SELECT last_name, department_id
FROM employeesWHERE department_id IN(20, 30, 80);
--where 条件另一种写法:
WHERE department_id = 20 OR department_id = 30 OR department_id = 80;
例 7:
评论0
最新资源