福建建设工程有限公司网站,网站图标怎么下载,石家庄行业网站,网站留言板怎么做phpsql多表查询不能使用 SELECT * from emp, dept; 会产生笛卡尔积。
笛卡尔积#xff0c;有A#xff0c;B两个集合#xff0c;A中有5条信息#xff0c;B中有4条信息#xff0c;那么查询结果就是5*420条一、内连接查询 -- 隐式内连接SELECT 字段列表 FROM 表1,表2,… WHERE 条件…多表查询不能使用 SELECT * from emp, dept; 会产生笛卡尔积。
笛卡尔积有AB两个集合A中有5条信息B中有4条信息那么查询结果就是5*420条一、内连接查询 -- 隐式内连接SELECT 字段列表 FROM 表1,表2,… WHERE 条件;例select emp.name,emp.age,dept.dep_name from emp,dept where emp.dep_id dept.id;可以给表起别名将上面的语句转换成下面这种。select t1.name,t1.age,t2.dep_name from emp t1,dept t2 where t1.dep_id t2.id;-- 显示内连接SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;如select emp.name,dept.dep_name from emp inner join dept on emp.dep_id dept.id;可以给表起别名将上面的语句转换成下面这种。select t1.name,t2.dep_name from emp t1 inner join dept t2 on t1.dep_id t2.id;二、外连接查询 1左外连接相当于查询A表所有数据和交集部分数据左外连接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;select * from emp left join dept on emp.dep_id dept.id;2右外连接相当于查询B表所有数据和交集部分数据右外连接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;select * from emp right join dept on emp.dep_id dept.id;三、子查询 1子查询概念查询中嵌套查询称嵌套查询为子查询2子查询根据查询结果不同作用不同①单行单列作为条件值使用 ! 等进行条件判断SELECT 字段列表 FROM 表 WHERE 字段名 (子查询);查询年龄大于周八的select * from emp where age(select age from emp where name 周八);②多行单列作为条件值使用in关键字进行条件判断SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);查询研发和销售的所有员工信息select * from emp where dep_id in (select id from dept where dep_name 研发部 or dep_name 销售部 );③多行多列作为虚拟表SELECT 字段列表 FROM (子查询) WHERE 条件;-- 查询入职日期是‘1998-08-01’之后的员工信息和部门信息把SELECT * from emp WHERE join_date1998-08-01这个多行多列表作为虚拟表查询放到from后面SELECT * FROM (SELECT * from emp WHERE join_date1998-08-01) t1,dept WHERE t1.dep_id dept.id;四、练习
新建员工表部门表职务表工资等级表
1.查询所有员工信息。查询员工编号id员工姓名ename工资salary职务名称jname职务描述description
2.查询员工编号员工姓名工资职务名称职务描述部门名称部门位置
3.查询员工姓名工资工资等级
4.查询员工姓名工资职务名称职务描述部门名称部门位置工资等级
5.查询出部门编号、部门名称、部门位置、部门人数具体步骤如下首先新建四张表-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY auto_increment, -- 部门iddname VARCHAR ( 50 ), -- 部门名称loc VARCHAR ( 50 ) -- 部门所在地
);
-- 职务表职务名称职务描述
CREATE TABLE job ( id INT PRIMARY KEY, -- 职务idjname VARCHAR ( 20 ), -- 职务名称description VARCHAR ( 50 ) -- 职务描述
);
-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY,-- 员工idename VARCHAR ( 50 ),-- 员工姓名job_id INT,-- 工作编号mgr INT,-- 上级领导joindate DATE, -- 入职日期salary DECIMAL ( 7, 2 ), -- 工资bonus DECIMAL ( 7, 2 ),-- 奖金dept_id INT, -- 所在部门编号CONSTRAINT fk_emp_jobid_job_id FOREIGN KEY ( job_id ) REFERENCES job ( id ),CONSTRAINT fl_emp_deptid_dept_id FOREIGN KEY ( dept_id ) REFERENCES dept ( id )
);
-- 工资等级表
CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 界别losalary INT, -- 最低工资hisalary INT -- 最高工资
);添加数据-- 添加4个部门
INSERT INTO dept ( id, dname, loc )
VALUES( 10, 教研部, 北京 ),( 20, 学工部, 上海 ),( 30, 销售部, 广州 ),( 40, 财务部, 深圳 );
-- 添加4个职务
INSERT INTO job ( id, jname, description )
VALUES( 1, 董事长, 管理整个公司接单 ),( 2, 经理, 管理部门员工 ),( 3, 销售员, 向客人推销产品 ),( 4, 文员, 使用办公软件 );
-- 添加员工
INSERT INTO emp ( id, ename, job_id, mgr, joindate, salary, bonus, dept_id )
VALUES( 1001, 孙悟空, 4, 1004, 2000-12-17, 8000.00, NULL, 20 ),( 1002, 卢俊义, 3, 1006, 2001-02-10, 16000.00, 3000.00, 30 ),( 1003, 林冲, 3, 1006, 2001-02-22, 12500.00, 5000.00, 30 ),( 1004, 唐僧, 2, 1009, 2001-04-02, 29750.00, NULL, 20 ),( 1005, 李逵, 4, 1006, 2001-09-17, 12500.00, 14000.00, 30 ),( 1006, 宋江, 2, 1009, 2001-05-17, 28500.00, NULL, 30 ),( 1007, 刘备, 2, 1009, 2001-09-17, 24500.00, NULL, 10 ),( 1008, 猪八戒, 4, 1004, 2007-12-17, 30000.00, NULL, 20 ),( 1009, 罗贯中, 1, NULL, 2000-12-17, 50000.00, NULL, 10 ),( 1010, 吴用, 3, 1006, 2001-09-17, 15000.00, 0.00, 30 ),( 1011, 沙僧, 4, 1004, 2007-05-17, 11000.00, NULL, 20 ),( 1012, 李逵, 4, 1006, 2001-12-03, 9500.00, NULL, 30 ),( 1013, 小白龙, 4, 1004, 2001-12-03, 30000.00, NULL, 20 ),( 1014, 关羽, 4, 1007, 2002-01-12, 13000.00, NULL, 10 );
-- 添加工资等级
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES( 1, 7000, 12000 ),( 2, 12010, 14000 ),( 3, 14010, 20000 ),( 4, 20010, 30000 ),( 5, 30010, 99990 );1.查询所有员工信息。查询员工编号id员工姓名ename工资salary职务名称jname职务描述description-- 1.查询所有员工信息。查询员工编号id员工姓名ename工资salary职务名称jname职务描述description
/*
分析
1.员工编号id员工姓名ename工资salary 在emp表中
2.职务名称jname职务描述description在职务表中
3.职务表和员工表是一对多的关系 e.job_id j.id
通过内连接可完成
*/
-- 隐式内连接
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description
FROMemp,job
WHEREemp.job_id job.id;
-- 显示内连接
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description
FROMempINNER JOIN job ON emp.job_id job.id;2.查询员工编号员工姓名工资职务名称职务描述部门名称部门位置-- 2.查询员工编号员工姓名工资职务名称职务描述部门名称部门位置
/*
分析
1.员工编号员工姓名工资在emp表中
2.职务名称职务描述在job表中
3.部门名称部门位置在dept表中
4.职务表和员工表是一对多的关系 e.job_id j.id
5.部门表和员工表是一对多的关系 e.dept_id d.id
*/
-- 隐式内连接
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
FROMemp,job,dept
WHEREemp.job_id job.id AND emp.dept_id dept.id;-- 显示内连接
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
FROMempINNER JOIN job ON emp.job_id job.idINNER JOIN dept ON emp.dept_id dept.id;3.查询员工姓名工资工资等级-- 3.查询员工姓名工资工资等级
/*1.员工姓名,工资,emp2.工资等级,salarygrade3. salarygrade.losalary emp.salary AND salarygrade.hisalary emp.salary
*/SELECTemp.ename,emp.salary,s.grade
FROMemp,salarygrade s
WHEREemp.salary s.losalary AND emp.salary s.hisalary;
4.查询员工姓名工资职务名称职务描述部门名称部门位置工资等级-- 4.查询员工姓名工资职务名称职务描述部门名称部门位置工资等级
/*1.员工姓名工资,emp2.职务名称职务描述,job3.部门名称部门位置,dept4.工资等级,salarygrade5.职务表和员工表是一对多的关系 e.job_id j.id6.部门表和员工表是一对多的关系 e.dept_id d.id7.salarygrade.losalary emp.salary AND salarygrade.hisalary emp.salary
*/
-- 隐式内连接
SELECTemp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,s.grade
FROMemp,job,dept,salarygrade s
WHEREemp.job_id job.id AND emp.dept_id dept.id AND emp.salary s.losalary AND emp.salary s.hisalary;-- 显式内连接
SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
FROM
empINNER JOIN job ON emp.job_id job.idINNER JOIN dept ON emp.dept_id dept.idINNER JOIN salarygrade s ON emp.salary s.losalary AND emp.salary s.hisalary;
5.查询出部门编号、部门名称、部门位置、部门人数-- 5.查询出部门编号、部门名称、部门位置、部门人数
/*1.部门编号、部门名称、部门位置,dept2.部门人数,按照dept_id,进行GROUP BY分组count(*) 统计人数*/
-- 1.先在emp表中分组并统计人数2.然后将1中产生的表作为子查询使用
SELECTdept.*,t1.total
FROMdept,(SELECT dept_id, count( * ) total FROM emp GROUP BY dept_id ) t1
WHEREt1.dept_id dept.id;-- 第二种方法先把员工和部门关联起来然后按照部门id分组并统计人数
SELECTcount( * ),t.*
FROM( SELECT dept.* FROM emp, dept WHERE emp.dept_id dept.id ) t
GROUP BYt.id;
