连接查询
又叫多表查询，当查询的字段来自多个表时，
等值连接
使用第一张表 的每一行，匹配第二章表的每一行，通过连接条件筛选，满足条件筛选出来
1.查询女神名和对应的男神名
SELECT name,boyname FROM boys,beauty WHERE beauty.boyfriend_id= boys,id;
2.查询部门名和对应的部门名
SELECT last_name,deparyment_name FROM employees,departments WHERE employees.department_id=departments.department_id;
为表起表名
1.查询员工名，工种号，工种号
SELECT last_name,p1.job_id,job_title FROM employees p1,jobs p2 WHERE p1.job_id=p2.job_id
添加筛选
1.查询有奖金的员工名，部门名
SELECT last_name,deparment_name,commission_pct FROM employees p1,departments p2 WHERE p1.department_id=p2.department_id AND p1.commission_pct IS NOT NULL;
执行
Russell Sal 0.4 Partners Sal 0.3 Errazuriz Sal 0.3 Cambrault Sal 0.3 Zlotkey Sal 0.2 Johnson Sal 0.1
2.查询城市名中第二个字符为o的部门名和城市名
SELECT deparyment_name,city FROM deparyment p1,locations p2 WHERE p1.location_id =p2.location_id AND city LIKE '_o%';
执行
IT Southlake Shi South San Francisco Mar Toronto Hum London
添加分组
1.查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM departments p1,locations p2 WHERE p1.location_id =p2.location_id GROUP BY city
执行
1 London 1 Munich 1 Oxford 21 Seattle 1 South San Francisco 1 Southlake 1 Toronto
2.查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,p1.manager_id,MIN(salary) FROM departments p1,employeees p2 WHERE p1.department_id=p2.department_id AND commission_pct IS NOT NULL GROUP BY department_name,p1.manager_id
3.查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title,COUNT(*) FROM employees p1.jobs p2 WHERE p1.job_id=p2.job_id GROUP BY job_title ORDER BY COUNT(*) DESC;
三表连接
1.查询员工名，部门名，所在的城市
SELECT last_name,department_name,city FROM employees p1,departments p2,locations p3 WHERE p1.department_id = p2.department_id AND p2.location_id = p3.location_id;
结果
Whalen Adm Seattle Hartstein Mar Toronto Fay Mar Toronto Raphaely Pur Seattle Khoo Pur Seattle Baida Pur Seattle Tobias Pur Seattle Himuro Pur Seattle Colmenares Pur Seattle Mavris Hum London Weiss Shi South San Francisco
添加筛选
SELECT last_name,department_name,city FROM employees p1,departments p2,locations p3 WHERE p1.department_id = p2.department_id AND p2.location_id = p3.location_id AND city LIKE 'S%';
结果
Hunold IT Southlake Ernst IT Southlake Austin IT Southlake Pataballa IT Southlake Lorentz IT Southlake Weiss Shi South San Francisco Fripp Shi South San Francisco
非等值连接
1.查询员工的工资和工资级别
job_grades工资级别表
employees员工工资表
SELECT salary,grade_level
FROM job_grades p1,employees p2
WHERE salary BETWEEN p1.`lowest_sal` AND p1.`highest_sal`;结果
24000 E 17000 E 17000 E 9000 C 6000 C 4800 B 4800 B
2.筛选，只看A级别的
SELECT salary,grade_level FROM job_grades p1,employees p2 WHERE salary BETWEEN p1.`lowest_sal` AND p1.`highest_sal` AND p1.`grade_level`='A';
结果
2900 A 2800 A 2600 A 2500 A 2700 A 2400 A 2200 A 2800 A 2500 A
自连接【自己连接自己】
1.查询员工名和上级的名称
SELECT p1.last_name, p1.employee_id, p2.last_name, p2.employee_id FROM employees p1,employees p2 WHERE p1.`manager_id`=p2.`employee_id`;
练习：
1.显示员工表的最大工资，工资平均值
select MAX(salary),AVG(salary) from employees;
2.查询员工表的empyee_id，job_id，last_name，按department_id降序，salary升序
select employee_id,job_id,last_name from employees ORDER BY department_id DESC,salary ASC
206 AC_ACCOUNT Gietz 205 AC_MGR Higgins 113 FI_ACCOUNT Popp 111 FI_ACCOUNT Sciarra 112 FI_ACCOUNT Urman 110 FI_ACCOUNT Chen 109 FI_ACCOUNT Faviet
3.查询员工表的job_id中包含a和e的，并且a在e前面
SELECT job_id FROM employees WHERE job_id LIKE '%a%e%'
AD_PRES SA_REP SA_REP SA_REP SA_REP SA_REP
4.
已知表student，里面有id（学号），name（名字)，gradeid（年纪编号）
已知表grade，里面有id（年纪编号），name（年纪名）
已知表result，里有id，score，studentNo（学号）
要求查询，姓名，年纪名，成绩
SELECT p1.name,p2.name,p3.score FROM student p1,grade p2,result p3 WHERE p1.`gradeid`=p2.`id` AND p1.`id`=p3.`studentNo`
5.显示当前日期，以及去前后空格，截取子字符串的函数
select now（）； select trim(字符 from '')
继续阅读
我的微信
这是我的微信扫一扫
评论