mysql学习笔记三

mysql表的复杂查询

创建需要的表，sql语句如下:

CREATE TABLE `dept` (
  `deptno` int(10) unsigned NOT NULL auto_increment,
  `depName` varchar(20) default NULL,
  `detLocal` varchar(50) default NULL,
  PRIMARY KEY  (`deptno`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES (1,'开发部','1号楼'),(2,'经济部门','2号楼'),(3,'行政部','3号楼');

CREATE TABLE `emp` (
  `empId` int(10) unsigned NOT NULL auto_increment,
  `eName` varchar(50) default NULL,
  `job` varchar(50) default NULL,
  `deptno` int(10) unsigned default NULL,
  `sal` float default NULL,
  PRIMARY KEY  (`empId`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `emp` VALUES (1,'小明','php开发',1,5000),(2,'小花','前端开发',1,4500),(3,'小凯','php开发',1,6000),(4,'瞄喵','行政',3,3500),(5,'小兰','经济',2,4000),(6,'小咯','经济',2,6000),(7,'小小','经济',2,7000);

1.子查询

（1）.在多行子查询中使用all操作符

eg.显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);

或者Select ename,sal,deptno from emp  where sal>(select max(sal) from emp where deptno=30);

（2）.在多行子查询中使用any操作符

eg：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30)

或者select ename,sal,deptno from emp where sal> (select min(sal) from emp where deptno=30)
（3）.from子句中使用子查询
eg.（1）显示高于自己部门平均工资的员工的信息
select e1.*,e2.myavg  from emp e1,(select avg(sal) myavg,deptno from emp group by deptno) e2 where e1.deptno=e2.deptno and e1.sal>e2.myavg
或select e1.* from emp e1 where e1.sal>(select avg(sal) from emp where deptno=e1.deptno)
eg（2）查找每个部门工资最高的人的详细资料
select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);
或者select e1.* from emp e1 where e1.sal >= all(select sal from emp where deptno=e1.deptno);

eg(3) 显示每个部门的信息和人员数量

  select d.*,(select count(*) from emp where deptno=d.deptno) tot from dept d;

问题一.自我复制数据(蠕虫复制)？

 insert into mytable (id,name,sal,job,deptno) 
    select empno,ename,sal,job,deptno from emp;

问题二.如何删除掉一张表重复记录  temp_table   是一张临时表 aa:是你要处理表（有重复行的.）？

select   distinct   *   into   temp_table   from   aa 
    delete   from   aa   
    insert   into   aa   select   *   from  temp_table      
    drop   table  temp_table

问题三.创建一张表，其结构和另外一张表一样？

create table mytemp like 某表

2.合并查询

(1)union 

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。

select empId,empName,job,deptno,sal from emp where job='经济' union select empId,empName,job,deptno,sal from emp where sal > 4000;

(2)union  all 该操作赋与union相似，但是它不会取消重复行,而且不会排序。

select empId,empName,job,deptno,sal from emp where job='经济' union all  select empId,empName,job,deptno,sal from emp where sal > 4000;

3.表的连接查询

inner join（内连接），left join （左连接），right join（有连接）。