面包来了~
#一、SQL92标准
/*等值连接
①交集
②n表连接，n-1个条件
③起别名
④可搭配排序、分组、筛选....
*/
#等值连接
#案例1：查询女神名和对应的男生名SELECT NAME,boyName FROM boys,beautyWHERE beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名`myemployees`
①
SELECT last_name,department_nameFROM employees,departmentsWHERE employees.`department_id`=departments.`department_id`;
②、表明顺序可换 AND 起别名，一定要统一
#查询员工名、工种号、工种名
SELECT last_name,employees.job_id,job_titleFROM employees,jobsWHERE employees.`job_id`=jobs.`job_id`;SELECT last_name,e.job_id,job_titleFROM employees e,jobs jWHERE e.`job_id`=j.`job_id`;
③、可以加筛选
#查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pctFROM employees e,departments dWHERE e.department_id=d.department_idAND e.commission_pct IS NOT NULL;
#查询城市名中第二个字符为o的对应的部门名和城市名
SELECT department_name,cityFROM departments d,locations lWHERE d.location_id=l.`location_id`AND city LIKE '_o%';/*第二个字符怎么表示？'_o%'*/
④可以加分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(department_id)FROM departments d,locations lWHERE d.location_id=l.location_idGROUP BY city;
#案例2：查询有奖金的每个部门的部门名称和部门的领导编号和该部门最低工资
SELECT department_name,MIN(salary)FROM departments d,employees eWHERE d.department_id=e.`department_id`AND commission_pct IS NOT NULLGROUP BY department_name;
⑤添加排序
#查询每个工种的工种名和员工个数，按员工个数排序
SELECT job_title,COUNT(*) 人数FROM jobs j,employees eWHERE j.job_id=e.job_idGROUP BY job_titleORDER BY 人数 DESC;
⑥三表链接
#案例：员工名，部门名和所在城市
SELECT last_name,department_name,cityFROM employees e,departments d,locations lWHERE e.department_id=d.department_idAND d.location_id=l.location_idAND city LIKE 's%'ORDER BY last_name DESC;
#非等值连接
案例1：查询员工工资和工资级别
SELECT salary,grade_levelFROM employees e,job_grades jgWHERE salary BETWEEN `lowest_sal` AND`highest_sal`;
SELECT salary,grade_levelFROM employees e,job_grades jgWHERE salary BETWEEN `lowest_sal` AND`highest_sal`AND jg.`grade_level`='A';/*只看A等级*/
#自连接
#查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e,employees mWHERE e.`manager_id`=m.`employee_id`;
练习
#显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary)FROM employees;
#查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,last_name,job_id,department_id,salaryFROM employeesORDER BY department_id DESC, salary ASC;
#查询员工表的job_id中包含 a和e的，并且a在e前面
SELECT last_name,job_idFROM employeesWHERE job_id LIKE '%a%e%';
#显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();SELECT TRIM
发表评论