自适应网站方案,wordpress密码忘了,免费开店的电商平台,学网站开发网页制作前言简单的数据我们可以直接从一个表中获取#xff0c;但在真实的项目中查询符合条件的数据通常需要牵扯到多张表#xff0c;这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上…前言简单的数据我们可以直接从一个表中获取但在真实的项目中查询符合条件的数据通常需要牵扯到多张表这就不得不使用多表查询。多表查询分为多表连接查询、符合条件链接查询、子查询。多表连接查询包括内连接、外连接、全连接。符合条件连接查询本质上是多表连接查询过滤条件。子查询是将一个查询语句嵌套在另一个查询语句中内层查询语句的查询结果作为外层查询语句的数据源。准备# 建表create table department(id int,name varchar(20));create table employee(id int primary key auto_increment,name varchar(20),sex enum(male,female) not null default male,age int,dep_id int);# 插入数据insert into department values(1,技术),(2,财务),(3,法律’);# 此处省略员工表数据...多表连接查询语法SELECT 字段列表FROM 表1 INNER|LEFT|RIGHT JOIN 表2ON 表1.字段 表2.字段;交叉连接在介绍多表查询的时候有必要先介绍下交叉连接如下select * from employee, department;查询语句就是交叉连接查询可以看出同一个数据在在employee表和department表交叉连接之后产生了重复记录其重复个数取决于department表的记录个数。所以最后交叉连接之后的记录个数是count(employee) * count(department)即笛卡尔积。通常情况下笛卡尔积的结果在工作中无实际意义我们需要在笛卡尔积的基础上进行筛选找到employee.dep_id department.id的那条记录。mysql select * from employee, department;--------------------------------------------------------------------| id | name | age | sex | position | salary | dep_id | id | name |--------------------------------------------------------------------| 1 | jack | 20 | male | lawyer | 888889 | 3 | 1 | 技术 || 1 | jack | 20 | male | lawyer | 888889 | 3 | 2 | 财务 || 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 || 2 | mark | 22 | male | lawyer | 888889 | 3 | 1 | 技术 || 2 | mark | 22 | male | lawyer | 888889 | 3 | 2 | 财务 || 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 || 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 1 | 技术 || 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 2 | 财务 || 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 || 4 | nick | 39 | male | lawyer | 4438890 | 3 | 1 | 技术 || 4 | nick | 39 | male | lawyer | 4438890 | 3 | 2 | 财务 || 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 || 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 1 | 技术 || 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 2 | 财务 || 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 || 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技术 || 6 | tony | 35 | male | RD | 100000000 | 1 | 2 | 财务 || 6 | tony | 35 | male | RD | 100000000 | 1 | 3 | 法律 || 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技术 || 7 | emmy | 27 | female | RD | 9999 | 1 | 2 | 财务 || 7 | emmy | 27 | female | RD | 9999 | 1 | 3 | 法律 || 8 | emmy | 23 | female | finance | 5000 | 2 | 1 | 技术 || 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 财务 || 8 | emmy | 23 | female | finance | 5000 | 2 | 3 | 法律 || 9 | lucy | 45 | female | finance | 10000 | 2 | 1 | 技术 || 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 财务 || 9 | lucy | 45 | female | finance | 10000 | 2 | 3 | 法律 || 10 | emmi | 20 | female | finance | 20000 | 2 | 1 | 技术 || 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 财务 || 10 | emmi | 20 | female | finance | 20000 | 2 | 3 | 法律 || 11 | james | 18 | male | NULL | 3000 | NULL | 1 | 技术 || 11 | james | 18 | male | NULL | 3000 | NULL | 2 | 财务 || 11 | james | 18 | male | NULL | 3000 | NULL | 3 | 法律 |--------------------------------------------------------------------33 rows in set (0.00 sec)内连接内连接只会连接两张表匹配的行即取交集。找两张表公共部分相当于利用条件从笛卡尔积结果中筛选出了正确的结果mysql select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_iddepartment.id;---------------------------------| id | name | age | sex | name |---------------------------------| 1 | jack | 20 | male | 法律 || 2 | mark | 22 | male | 法律 || 3 | hank | 25 | male | 法律 || 4 | nick | 39 | male | 法律 || 5 | jenny | 26 | female | 法律 || 6 | tony | 35 | male | 技术 || 7 | emmy | 27 | female | 技术 || 8 | emmy | 23 | female | 财务 || 9 | lucy | 45 | female | 财务 || 10 | emmi | 20 | female | 财务 |---------------------------------10 rows in set (0.00 sec)上述内连接查询语句等同于mysql select employee.id,employee.name,employee.age,employee.sex,department.namefrom employee,departmentwhere employee.dep_iddepartment.id;外连接外连接分为左连接、右连接、全外连接左连接左连接在内连接的基础上优先显示左表全部记录。即左连接内连接左表未符合条件的记录#以左表为准即找出所有员工信息当然包括没有部门的员工#本质就是在内连接的基础上增加左边有右边没有的结果mysql select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_iddepartment.id;------------------------| id | name | depart_name |------------------------| 6 | tony | 技术 || 7 | emmy | 技术 || 8 | emmy | 财务 || 9 | lucy | 财务 || 10 | emmi | 财务 || 1 | jack | 法律 || 2 | mark | 法律 || 3 | hank | 法律 || 4 | nick | 法律 || 5 | jenny | 法律 || 11 | james | NULL |------------------------11 rows in set (0.00 sec)右连接又连接在内连接的基础上优先显示右表的内容。即右连接内连接右表未符合条件的记录#以右表为准即找出所有部门信息包括没有员工的部门#本质就是在内连接的基础上增加右边有左边没有的结果mysql select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_iddepartment.id;--------------------------| id | name | depart_name |--------------------------| 1 | jack | 法律 || 2 | mark | 法律 || 3 | hank | 法律 || 4 | nick | 法律 || 5 | jenny | 法律 || 6 | tony | 技术 || 7 | emmy | 技术 || 8 | emmy | 财务 || 9 | lucy | 财务 || 10 | emmi | 财务 |--------------------------10 rows in set (0.00 sec)可以发现左表(employee表)的第11条记录没有被查询出来全外连接全外连接会在内连接查询的基础上显示左表和右表的全部记录mysql select * from employee left join department on employee.dep_id department.id- union- select * from employee right join department on employee.dep_id department.id- ;----------------------------------------------------------------------| id | name | age | sex | position | salary | dep_id | id | name |----------------------------------------------------------------------| 6 | tony | 35 | male | RD | 100000000 | 1 | 1 | 技术 || 7 | emmy | 27 | female | RD | 9999 | 1 | 1 | 技术 || 8 | emmy | 23 | female | finance | 5000 | 2 | 2 | 财务 || 9 | lucy | 45 | female | finance | 10000 | 2 | 2 | 财务 || 10 | emmi | 20 | female | finance | 20000 | 2 | 2 | 财务 || 1 | jack | 20 | male | lawyer | 888889 | 3 | 3 | 法律 || 2 | mark | 22 | male | lawyer | 888889 | 3 | 3 | 法律 || 3 | hank | 25 | male | lawyer | 7777.8 | 3 | 3 | 法律 || 4 | nick | 39 | male | lawyer | 4438890 | 3 | 3 | 法律 || 5 | jenny | 26 | female | lawyer | 10000.8 | 3 | 3 | 法律 || 11 | james | 18 | male | NULL | 3000 | NULL | NULL | NULL || NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 人力 |----------------------------------------------------------------------12 rows in set (0.00 sec)符合条件链接查询mysql select employee.name, employee.age, department.name from employee inner join department- on employee.dep_iddepartment.id- where age 30- order by age asc;--------------------| name | age | name |--------------------| tony | 35 | 技术 || nick | 39 | 法律 || lucy | 45 | 财务 |--------------------3 rows in set (0.00 sec)子查询子查询是将一个查询语句的嵌套在另一个查询语句中内层查询语句的查询结果作为外层查询语句的数据源子查询中可以包含 IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字注意被嵌套的查询语句需要用( )包裹查询员工部门平均年龄大于30的部门信息mysql select * from department- where id in- (select dep_id from employee group by dep_id having avg(age) 30);--------------| id | name |--------------| 1 | 技术 |--------------1 row in set (0.00 sec)查询技术部员工姓名mysql select name from employee- where dep_id in- (select id from department where name技术);------| name |------| tony || emmy |------2 rows in set (0.00 sec)查询无员工的部门名(子查询得到的是所有人的部门id需要disctinct去除)mysql select name from department- where id not in- (select distinct dep_id from employee);带比较运算符的子查询查询大于所有人平均年龄的员工名和年龄mysql select * from employee where age (select avg(age) from employee);-----------------------------------------------------| id | name | age | sex | position | salary | dep_id |-----------------------------------------------------| 4 | nick | 39 | male | lawyer | 4438890 | 3 || 6 | tony | 35 | male | RD | 100000000 | 1 || 9 | lucy | 45 | female | finance | 10000 | 2 |-----------------------------------------------------3 rows in set (0.00 sec)不能这样在前面没有group by的时后面不能使用分组函数mysql select * from employee where age avg(age);ERROR 1111 (HY000): Invalid use of group function带EXISTS关键字的子查询EXISTS关字键字表示存在。在使用EXISTS关键字时内层查询语句不返回查询的记录。而是返回一个真假值。True或False当返回True时外层查询语句将进行查询当返回值为False时外层查询语句不进行查询mysql select * from employee- where exists- (select id from department where id200);MYSQL中可以把一个查询语句用括号括起来使用as起一个别名当做一个表使用查询每个职位最新入职的员工SELECT*FROMemp AS t1INNER JOIN (SELECTpost,max(hire_date) max_dateFROMempGROUP BYpost) AS t2 ON t1.post t2.postWHEREt1.hire_date t2.max_date;查询语句关键字执行顺序一个完整的mysql的查询语句如下SELECT DISTINCT FROM JOIN ON WHERE GROUP BY HAVING ORDER BY LIMIT 其关键字执行顺序如下(7) SELECT(8) DISTINCT (1) FROM (3) JOIN (2) ON (4) WHERE (5) GROUP BY (6) HAVING (9) ORDER BY (10) LIMIT
