#02-基本的SELECT语句
USE temp; #使用指定的数据库
#1. 基本使用
SELECT employee_id,last_name,EMAIL
FROM employees;
SELECT department_id,manager_id,location_id
FROM departments;
SELECT * # * : 代表所有的字段
FROM employees;
SELECT *
FROM countries;
#2. 列的别名
#as : alias
#可以使用一对"",给列起别名
SELECT employee_id emp_id,last_name AS lname,salary "monthly salary"
FROM employees;
#3. 去除重复行
SELECT DISTINCT department_id
FROM employees;
#如下操作是错误的
SELECT employee_id, DISTINCT department_id
FROM employees;
#4. 空值问题
#空值,表示没有赋值,理解为null。
#空值参与运算的问题:结果也为空。
#空值,不等同于0,'','null'
SELECT employee_id,commission_pct,salary,salary * (1 + commission_pct),
salary * (1 + IFNULL(commission_pct,0)) "real_salary"
FROM employees;
#5. 显示表结构
DESC employees;
DESCRIBE employees;
#03-过滤数据
#查询90号部门员工的信息
SELECT employee_id,last_name,department_id,salary
FROM employees
# 使用where实现数据的过滤
# where 必须紧跟在from子句的后面
WHERE department_id = 90;
SELECT employee_id,last_name,department_id,salary
FROM employees
#where salary > 5000;
WHERE department_id <> 90;
# 2. between 下边界 and 上边界 (包含边界)
#查询工资大于等于6000 且小于等于 8000之间的员工信息
SELECT employee_id,salary
FROM employees
WHERE salary >= 6000 AND salary <= 8000;
#where salary between 6000 and 8000;
#WHERE salary BETWEEN 8000 AND 6000;
#3. in(set):
SELECT employee_id,department_id,salary
FROM employees
#where department_id = 30 or department_id = 40 or department_id = 50;
#where department_id in (30,40,50);
WHERE salary IN (6000,7000,8000);
#4. like : 模糊查询
#精确查询
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name = 'King'; #字符串、日期需要使用一对''表示。注意,不要使用""
#where hire_date = '1993-01-13';
#查询姓名中包含字符'a'的员工
# % : 表示0个,1个或多个字符
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name LIKE '%a%';
# 查询姓名中包含字符'a'且包含字符'e'的员工
SELECT employee_id,LAST_NAME
FROM employees
#WHERE last_name LIKE '%a%e%' or last_name LIKE '%e%a%';
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
# 查询姓名中第3个字符是a的员工
# _ : 表示一个不确定的字符
SELECT employee_id,LAST_NAME
FROM employees
WHERE last_name LIKE '__a%';
# 查询姓名中第2个字符是_且第3个字符是a的员工
SELECT employee_id,LAST_NAME
FROM employees
#WHERE last_name LIKE '_\_a%'; # sout("林辉很\"帅\"");
WHERE last_name LIKE '_#_a%' ESCAPE '#';
# 5. is null:空值
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;
SELECT last_name,commission_pct
FROM employees
#WHERE !(commission_pct IS NULL);
WHERE commission_pct IS NOT NULL;
# 6. 算术运算符
SELECT employee_id,salary,department_id
FROM employees
WHERE department_id MOD 20 = 0;
#选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
#where salary < 5000 or salary > 12000;
WHERE salary NOT BETWEEN 5000 AND 12000;
#where !(salary >= 5000 and salary <= 12000);
#选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
#04-排序和分页
#1. 排序
# 1.1
#按照员工的工资从高到低排序
# desc: descend,降序
# asc : ascend,升序
SELECT employee_id,salary
FROM employees
#order by salary desc; #降序
#order by salary asc; #升序
ORDER BY salary ;#如果没有显式指名asc或desc,则默认升序排列
# 1.2 使用列的别名进行排序
#别名可以在order by中使用
SELECT employee_id,last_name,salary sal
FROM employees
ORDER BY sal;
#不能在过滤条件中使用列的别名。
#如下操作报错:
SELECT employee_id,last_name,salary sal
FROM employees
WHERE sal >= 6000;
#order by要声明在where的后面
SELECT employee_id,last_name,salary sal
FROM employees
WHERE salary > 5000
ORDER BY last_name ASC;
#1.3 二级排序
SELECT employee_id,last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;
#2. 分页
#每页显示20条记录,显示第1页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 0,20;
#每页显示20条记录,显示第2页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 20,20;
#每页显示20条记录,显示第3页数据:
SELECT employee_id,last_name,salary
FROM employees
LIMIT 40,20;
#每页显示pageSize条记录,显示第pageNo页数据:
#limit (pageNo - 1) * pageSize,pageSize
#查询工资最高的20个员工信息:top-N
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,20; #limit 要声明在order by的后面
#05-多表查询
#出现了笛卡尔积的错误
SELECT employee_id,last_name,department_name
FROM employees,departments; #查询出2889行数据
SELECT 2889/107
FROM DUAL;
SELECT *
FROM departments;
#正确的写法:多表的查询,一定要有连接条件
SELECT employee_id,last_name,department_name
FROM employees,departments
#多表的连接条件
WHERE employees.`department_id` = departments.`department_id`
#进一步:如果查询的字段在多个表中都出现,则一定需要指明来自于哪个表。比如:department_id
SELECT employee_id,last_name,department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`
#建议开发中多表查询中,查询的字段都指明来自于哪个表。--->sql优化
SELECT employees.employee_id,employees.last_name,departments.department_name,departments.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`
#表的别名的使用。一旦给表起了别名,就可以在select中或where中使用。
SELECT e.employee_id,e.last_name,d.department_name,d.department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
#需求:
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
#连接条件
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
#结论:如果实现n个表的多表查询,则至少需要n - 1个连接条件。
##############################################
/*
多表查询的分类:
1. 等值连接 vs 不等值连接
2. 自连接 vs 非自连接
3. 内连接 vs 外连接
*/
#不等值连接
SELECT employee_id,salary,grade_level
FROM employees e,job_grades j
#where e.`salary` >= j.`lowest_sal` and e.`salary` <= j.`highest_sal`;
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
#自连接
#查询员工的employee_id,last_name及其管理者的employee_id,last_name
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;
########################################################
/*
内连接:合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接:分为左外连接 和 右外连接
左外连接:两个表在连接过程中除了返回