MySQL(零基础)详解之DQL

[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'mydb.t_employee.eid' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by

select eid,ename,gender,tel,birthday 
from t_employee 
order by eid desc ;

 示例二：按员工的基本工资的高低来进行显示

select eid, basic_salary 
from t_salary 
order by basic_salary desc;

对于排序的字段也是可以有多个的，当有多个时，会优先对第一个进行排序，只有当第一个的值相同时，才会对第二个值进行排序，依次类推。

示例三：按员工的职位高低进行排序，如果职位相同再按员工的编号升序进行排序。

select eid,ename,gender,job_id
from t_employee
order by job_id
desc,eid asc;

 五、多表查询

5.1、笛卡尔积

select ename,dname from t_employee,t_department;

 从上面的查询结果可以发现，我们在做关联查询时， 要尽量避免出现笛卡尔积的情况出现。

5.2、关联查询

（1）where：适用于所有的关联查询。

（2）on：只能和 join一起使用，只能写在关联条件中，但是它可以和 where 一起使用。

（3）using：只能和 join 一起使用，并且要求两个关联的字段在关联表中名称一致。

示例一：把关联条件写在 where 语句中

select ename,dname 
from t_employee,t_department 
where dept_id=did;

 示例二：把关联条件写在 on 语句中

select ename,dname
from t_employee join t_department on dept_id=did;

 两条查询的结果一样。

示例三：查询员工姓名和它的基本工资信息

select ename,basic_salary 
from t_employee as e,t_salary as s 
where e.eid=s.eid;

select ename,basic_salary 
from t_employee e join t_salary ts on e.eid = ts.eid;

select ename,basic_salary 
from t_employee join t_salary using (eid);

注意：要想使用 using，那么必须要关联查询相关表中被关联的条件字段名称必须相同。

示例四：查询员工姓名、基本工资和所在部门名称

（1）员工姓名：在 t_employee 表中

（2）基本工资：在 t_salary 表中

（3）部门名称：在 t_deparment 表中

select ename,basic_salary,dname 
from t_employee e join t_salary using(eid) 
                  join t_department td on td.did = e.dept_id;

5.3、内连接

select [columns] 
from 表1,表2,... 
where [condition];

select [columns] from 表1 inner join 表2 on [condition] where [其它条件]; 
 
select [columns] from 表1 cross join 表2 on [condition] where [其它条件];
 
select [columns] from 表1 join 表2 on [condition] where [其它条件];

select ename,dname
from t_employee e inner join t_department td on td.did = e.dept_id;

select ename,dname
from t_employee e join t_department td on td.did = e.dept_id;

select ename,dname
from t_employee e cross join t_department td on td.did = e.dept_id;

5.4、外连接

select did,dname,description, ename,gender,tel 
from t_department td left join t_employee te on td.did=te.dept_id;

select ename,gender,tel,did,dname,description
from t_employee te left join t_department td on te.dept_id=td.did;

上面的示例是返回左表中所有行，如果左表中的行在右表中没有匹配的数据，则会以空值来填充。

select did,dname,description,eid,ename,tel,dept_id 
from t_department td left outer join t_employee te on td.did = te.dept_id 
where te.dept_id is null;

 示例三：查询所有员工信息，以及员工所在的部门信息

insert into t_employee(ename) values('刘备');

5.4.2、右外连接

select eid,ename,gender,did,dname,description 
from t_employee e right join t_department d on e.dept_id=d.did;

（2）查询右表在左表中没有的数据。

  示例二：查询没有员工的部门信息。

select eid,ename,gender,did,dname,description 
from t_employee e right join t_department d on e.dept_id=d.did 
where dept_id is null;

5.4.3、全外连接(union)

select did,dname,eid,ename,gender,tel 
from t_department d left join t_employee te on d.did = te.dept_id 
union 
select did,dname,eid,ename,gender,tel 
from t_department d right join t_employee te on d.did = te.dept_id;

 （2）查询所有没有员工的部门和没有部门的员工信息

select did,dname,eid,ename,gender,tel 
from t_department d left join t_employee te on d.did = te.dept_id
where te.dept_id is null 
union
select did,dname,eid,ename,gender,tel 
from t_department d right join t_employee te on d.did = te.dept_id 
where te.dept_id is null;

 5.5、自连接

update t_employee set mid=1 where eid=7;

select e1.ename, e2.ename 
from t_employee e1, t_employee e2 
where e1.mid=e2.eid;

 上面的方式我们使用隐式的内连接来实现的自连接查询。

select e.eid,ename,basic_salary 
from t_employee e, t_salary s 
where e.ename='孙红雷' and e.eid=s.eid;

 第二步：查询工资比 12000 高的员工编号

select eid,basic_salary from t_salary 
where basic_salary > 12000;

 第三步：查询员工编号为 4 和 6 的员工信息

select eid,ename,gender,tel 
from t_employee 
where eid in (4,6);

 根据上面三步的分析可以发现，我们所需要的条件都是另一个 SQL 语句的返回结果中，因此，我们可以使用子查询来实现上面三步的操作。

select eid,ename,gender,tel
from t_employee
where eid in (
    select eid
    from t_salary
    where basic_salary > (
        select basic_salary
        from t_employee e, t_salary s
        where e.ename='孙红雷' and e.eid=s.eid
        )
);

select eid,ename, dept_id 
from t_employee 
where ename in ('孙红雷','鹿晗');

select eid,ename,gender,tel,dept_id 
from t_employee 
where dept_id in (1,1);

 根据上面的分析，我们把第一步查询到的数据作为第二步查询所使用的条件即可。

select eid,ename,gender,tel,dept_id
from t_employee
where dept_id in (
    select dept_id
    from t_employee
    where ename in ('孙红雷','鹿晗')
);

执行结果一样

6.2、from 型子查询

select dept_id, avg(basic_salary) 
from t_employee e, t_salary s 
where e.eid=s.eid 
group by dept_id;

通过上面的SQL语句，我们可以正确得到部门的平均工资。但是有一个小的问题：我们从查询的结果可以看到，平均工资的字段名称为 avg(basic_salary) 。这个名称不便于我们使用，为了便于后续使用，我们经这个字段取一个别名，例如叫 avg_salary。

select dept_id, avg(basic_salary) as avg_salary 
from t_employee e, t_salary s 
where e.eid=s.eid group by dept_id;

 通过这样调整后，后续使用就比较方便了。

select e1.eid, dept_id,basic_salary 
from t_employee e1 join t_salary s1 
on e1.eid=s1.eid 
and dept_id in (1, 2);

 第三步：查询比部门平均工资高的员工和基本工资（需要把前面两步进行结合）

select te.eid, te.ename, basic_salary 
from t_salary ts join t_employee te
    on ts.eid=te.eid 
join (
      select dept_id, avg(basic_salary) as avg_salary 
      from t_employee e, t_salary s 
      where e.eid=s.eid 
      group by dept_id 
      ) as temp 
on te.dept_id=temp.dept_id 
where ts.basic_salary > temp.avg_salary;

6.3、exists 型子查询

select eid,ename,gender,dept_id 
from t_employee 
where dept_id is not null;

 第二步：在第一步的基础上来实现最终的功能

select * from t_department 
where exists(select * from t_employee);

（2）测试子查询中没有数据

select * from t_department 
where exists(select * from t_employee where eid > 10);

 根据上面的测试情况可以发现，我们子查询的结果不能为空，如果为空则外层查询就没有数据了；如果子查询不为空，则外层查询就会有数据。

select did,dname,description 
from t_department 
where exists( 
    select eid,ename,gender,dept_id 
    from t_employee 
    where dept_id is not null 
);

上面的结果中包含了没有员工的部门——财务部，这显然不符合我们的题要求，原因在于我们子查询中条件给的不对，因为没有我外部表进行关联，导致条件不符合。

select did,dname,description 
from t_department td 
where exists( 
    select eid,ename,gender,dept_id 
    from t_employee te 
    where td.did=te.dept_id 
);

 此时就得到了最终的结果了。

6.4、复制表子查询

create table 表名 like 被复制的表名;

 create table t_stu_new like t_stu; 

desc t_stu_new;

create table 表名 as (select * from 被复制的表名);

create table t_stu_new_2 as (select * from t_stu);
 
desc t_stu_new_2;

select * from t_stu_new_2;

insert into 复制后的表名 select * from 被复制的表名;

create table t_stu_new_3 like t_stu;

insert into t_stu_new_3 select * from t_stu; 
 
select * from t_stu_new_3;

create table 表名 like 被复制数据库名.被复制的表名; 
 
create table 新数据库名.表名 like 被复制数据库名.被复制的表名;

create table 表名 as ( 
select 要复制的字段列表 
from 被复制的表名 
where 条件 
)

create table t_stu_new_4 as ( 
select id,name 
from t_stu 
where id=1 
)

desc t_stu_new_4;

lect * from t_stu_new_4;

create table 表名(
 字段名 类型(长度) [主键][自增][非空][默认值][注释] 
) as (
select 被复制的字段 from 被复制的表名 
)

create table t_stu_new_5(
id int(11) primary key auto_increment
) as (
select id,name from t_stu );
 
 
 
desc t_stu_new_5;