三天学会MySQL -宋红康

#如下是一个查询语句
-- 使用的是select ... from 
/*
这是多行注释

Java语言的分类：
1. 基本语法
2. oop
3. io \ 多线程 \ 网络编程 \ 反射 \ 集合 等


SQL的分类：

DML : INSERT \ DELETE \ UPDATE \ SELECT 
DDL : CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE
DCL : COMMIT \ ROLLBACK \ GRANT \ REVOKE \ SAVEPOINT


*/
SELECT * 
FROM employees

#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`;


########################################################
/*
内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接：分为左外连接 和 右外连接
   左外连接：两个表在连接过程中除了返回