mysql连接查询

mysql连接查询

直接po代码，代码中有注释

#连接查询
/*
含义：又称为多表查询、多表连接
(当查询的字段来自于多个表时，就会用到连接查询)

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

	按年代分类：
	sql92标准:仅仅支持内连接
	sql99标准【推荐】：支持内连接+外连接(左外和右外)+交叉连接
	
	按功能分类：
		内连接：
			等值连接
			非等值连接
			自连接
		外连接：
			左外连接
			右外连接
			全外连接
		
		交叉连接

*/

#
SELECT * FROM beauty;
#
SELECT * FROM boys;
#笛卡尔积
SELECT `name`, boyname FROM beauty,boys;

#添加有效的连接条件
SELECT `name`, boyname FROM beauty,boys WHERE boyfriend_id = boys.id;
SELECT `name`, boyname FROM beauty,boys WHERE beauty.boyfriend_id = boys.id;
#
#一、sql92标准
#1、等值连接

/*

1. 多表等值连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3. 多表的顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用，比如排序、分组、筛选等
*/

#案例1：查询女神名和对应的男神名
SELECT 
  `name`,
  boyname 
FROM
  beauty,
  boys 
WHERE beauty.boyfriend_id = boys.id ;

#案例2：查询员工名和对应的部门名
SELECT 
  last_name,
  employees.department_id,
  department_name 
FROM
  employees,
  departments 
WHERE employees.department_id = departments.department_id ;
#查询员工名、工种号、工种名
SELECT 
  last_name,
  employees.job_id,
  job_title 
FROM
  employees,
  jobs 
WHERE employees.`job_id` = jobs.`job_id` ;

/*
为表取别名的好处
1.提高语句的简洁度
2.区分多个重名的字段
特别注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#取别名，省略AS
SELECT 
  last_name,
  emp.job_id,
  job_title 
FROM
  employees emp,
  jobs j 
WHERE emp.`job_id` = j.`job_id` ;

#取别名,不省略AS
SELECT 
  last_name,
  emp.job_id,
  job_title 
FROM
  employees AS emp,
  jobs AS j 
WHERE emp.`job_id` = j.`job_id` ;

#3、两个表的顺序是否可以调换(可以，效果是一样的)
#查询员工名、工种号、工种名
SELECT 
  last_name,
  emp.job_id,
  job_title 
FROM
  jobs j,
  employees emp 
WHERE emp.`job_id` = j.`job_id` ;

#4、可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT 
  last_name,
  emp.department_id,
  department_name,
  commission_pct 
FROM
  employees emp,
  departments dep 
WHERE emp.`department_id` = dep.`department_id` 
  AND emp.`commission_pct` IS NOT NULL ;
  
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT 
  department_id,
  department_name,
  city 
FROM
  departments dep,
  locations loc 
WHERE dep.`location_id` = loc.`location_id` 
  AND loc.`city` LIKE '_o%' ;
  
#5、可以加分组
#案例1：查询每个城市的部门个数 
SELECT 
  city,
  COUNT(*) 
FROM
  locations loc,
  departments dep 
WHERE loc.`location_id` = dep.`location_id` 
GROUP BY city ;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT 
  MIN(salary),
  department_name,
  emp.manager_id 
FROM
  employees emp,
  departments dep 
WHERE emp.`department_id` = dep.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,
  emp.manager_id;
  
#
 SELECT * FROM departments WHERE department_name = 'Sal';  
 SELECT * FROM employees WHERE department_id = 80;

#加上HAVING
SELECT 
  MIN(salary) min_Money,
  department_name,
  emp.manager_id 
FROM
  employees emp,
  departments dep 
WHERE emp.`department_id` = dep.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,
  emp.manager_id
HAVING min_Money >= 7000;

#加上ORDER BY排序
SELECT 
  MIN(salary) min_Money,
  department_name,
  emp.manager_id 
FROM
  employees emp,
  departments dep 
WHERE emp.`department_id` = dep.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,
  emp.manager_id
HAVING min_Money >= 7000
ORDER BY min_Money ASC;

#加上排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT 
  job_title,
  COUNT(*) AS cou 
FROM
  employees AS emp,
  jobs AS j 
WHERE emp.`job_id` = j.`job_id`
GROUP BY job_title 
ORDER BY cou DESC ;

#表后面省略as
SELECT 
  job_title,
  COUNT(*) AS cou 
FROM
  employees emp,
  jobs j 
WHERE emp.`job_id` = j.`job_id`
GROUP BY job_title 
ORDER BY cou DESC ;

#7、可以实现三表连接
#案例：查询员工名、部门名和所在的城市
SELECT 
  last_name,
  department_name,
  city 
FROM
  employees emp,
  departments dep,
  locations loc 
WHERE emp.`department_id` = dep.`department_id` 
  AND dep.`location_id` = loc.`location_id`;
  
#加筛选条件和降序
SELECT 
  last_name,
  department_name,
  city 
FROM
  employees emp,
  departments dep,
  locations loc 
WHERE emp.`department_id` = dep.`department_id` 
  AND dep.`location_id` = loc.`location_id` AND city LIKE 't%'
  ORDER BY last_name ASC;  
  
 # job_grades工资等级表
 SELECT * FROM job_grades;
 #
 DESC job_grades;
  
#2、非等值连接 
#案例1：查询员工的工资和工资级别
SELECT 
  salary,
  grade_level 
FROM
  employees emp,
  job_grades gra 
WHERE salary >= gra.`lowest_sal` 
  AND salary <= gra.`highest_sal` ;
  
 #使用BETWEEN AND
SELECT 
  salary,
  grade_level,
  lowest_sal, 
  highest_sal
FROM
  employees emp,
  job_grades gra 
WHERE salary BETWEEN gra.`lowest_sal` AND gra.`highest_sal`;
  
#也可以继续加筛选条件，也可以排序，就不依次举例了
SELECT 
  salary,
  grade_level,
  lowest_sal, 
  highest_sal
FROM
  employees emp,
  job_grades gra 
WHERE salary BETWEEN gra.`lowest_sal` AND gra.`highest_sal` AND grade_level = 'e'; 

#3、自连接
#案例：查询 员工名和上级的名称
SELECT 
  emp01.last_name 员工名,
  emp02.`last_name` 员工领导名 
FROM
  employees emp01,
  employees emp02 
WHERE emp01.`manager_id` = emp02.`employee_id` ;
#
SELECT 
  emp01.employee_id AS 员工id,
  emp01.last_name AS 员工名,
  emp01.`manager_id` AS 员工的领导id,
  emp02.`employee_id` AS 领导id,
  emp02.`last_name` AS 领导名
FROM
  employees emp01,
  employees emp02 
WHERE emp01.`manager_id` = emp02.`employee_id` ;

#
#显示员工表的最大工资、工资平均值
SELECT MAX(salary),AVG(salary) FROM employees; 

#查询员工表的employee_id,last_name, job_id,按department_id降序，salary升序
SELECT 
  employee_id,
  last_name,
  job_id 
FROM
  employees 
ORDER BY department_id DESC,
  salary ASC ;

#查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT job_id
FROM employees 
WHERE job_id LIKE '%a%e%';

#特别注意：mysql数据库中索引/下标从1开始
#显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW(), TRIM(NOW()), SUBSTR(NOW(), 1, 4);

#TRIM()函数
SELECT 
TRIM('  江西省  赣州市于都  县     '),
LENGTH(TRIM('  江西省  赣州市于都  县     ')),
LENGTH('  江西省  赣州市于都  县     ');

#TRIM()函数
SELECT TRIM('a' FROM 'aaahellojack   aaatomaaabbbeeeeeaaaaa   '),
TRIM('a' FROM 'aaahellojack   aaatomaaabbbeeeeeaaaaa');

#特别注意：mysql数据库中索引/下标从1开始
#SUBSTR()函数
SELECT SUBSTR('hellojack' FROM 5), SUBSTR('2019-01-18' FROM 4), SUBSTR('2019-01-18', 4);
SELECT SUBSTR('2018-08-17',6,7), SUBSTR('2018-08-17' FROM 6 FOR 7);

#SUBSTRING()函数
SELECT SUBSTRING('hellojack' FROM 5), SUBSTRING('2019-01-18' FROM 4), SUBSTRING('2019-01-18', 4);
SELECT SUBSTRING('2018-08-17',6,7), SUBSTRING('2018-08-17' FROM 6 FOR 7);

#
SELECT RAND(), RAND(), RAND(666), RAND(666), RAND(666), RAND(666), RAND(888), RAND(888);
SELECT last_name, RAND(), RAND(), RAND(3), RAND(3), RAND(89), RAND(89) FROM employees;
#
SELECT RAND()*10, FLOOR(RAND()*10);
#获得指定区间的数字(比如：5-10之间)
SELECT FLOOR(5+RAND()*5);
#随机查询
SELECT * FROM employees ORDER BY RAND();

#随机查询
SELECT * FROM employees ORDER BY RAND() LIMIT 5;

#ASCII 转换成字母
SELECT CHAR(90);
#随机获得大写字母
SELECT CHAR(FLOOR(65+RAND()*25)); 
#字符串连接，使用函数 concat()
SELECT CONCAT('令狐冲喜欢','任盈盈');
#使用md5()产生32位随机字符串
SELECT MD5(RAND() * 10000);
#返回该字符的密码形式，相当于加密
SELECT PASSWORD('江西省赣州市于都县');
# 产生500-1000间的整形
SELECT FLOOR( 500 + RAND() * (1000 - 500)) ;

#
SELECT NOW(), CURDATE(), CURTIME();

#
USE myemployees;

#显示所有员工的姓名、部门号、部门名称
SELECT 
  last_name,
  emp.department_id,
  department_name 
FROM
  employees emp,
  departments dep 
WHERE emp.`department_id` = dep.`department_id` ;

#查询90号部门员工的job_id和90号部门的location_id
SELECT 
  job_id,
  dep.location_id 
FROM
  employees emp,
  departments dep
WHERE emp.`department_id` = dep.`department_id` 
AND emp.`department_id` = 90;

#选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT 
  last_name,
  department_name,
  dep.`location_id`,
  city 
FROM
  employees emp,
  departments dep,
  locations loc 
WHERE emp.`department_id` = dep.`department_id` 
  AND dep.`location_id` = loc.`location_id` 
  AND emp.`commission_pct` IS NOT NULL;

#选在city在Toronto工作的员工的last_name,job_id,department_id,department_name
SELECT 
  last_name,
  job_id,
  emp.`department_id`,
 # loc.`city`,
  department_name 
FROM
  employees emp,
  departments dep,
  locations loc 
WHERE emp.`department_id` = dep.`department_id` 
  AND dep.`location_id` = loc.`location_id` 
  AND loc.`city` = 'Toronto' ;

#查询每个工种、每个部门的部门名、工种名和最低工资
SELECT 
  job_title,
  department_name,
  MIN(salary) 
FROM
  employees emp,
  departments dep,
  jobs j 
WHERE emp.`department_id` = dep.`department_id` 
  AND emp.`job_id` = j.`job_id` 
GROUP BY department_name,
   job_title;

#查询每个国家下的部门个数大于2的国家编号
SELECT 
  loc.`country_id`,
  COUNT(*) cou 
FROM
  departments dep,
  locations loc 
WHERE dep.`location_id` = loc.`location_id` 
GROUP BY country_id 
#having COUNT(*) > 2 ;
HAVING cou > 2 ; #也支持别名
/*
选择指定员工的姓名、员工号以及他的领导的姓名和员工号，结果类似于下面的格式 (自连接)
employees    Emp#     manager       Mgr#
Kochhar      101       K_ing         100 

*/
SELECT 
  emp.last_name AS "employees",
  emp.employee_id AS "Emp#",
  leader.last_name AS manager,
  leader.employee_id AS "Mgr#" 
FROM
  employees emp,
  employees leader 
WHERE emp.`manager_id` = leader.`employee_id` 
AND emp.last_name = 'Kochhar';