高级查询 SELECT

http://www.verejava.com/?id=1717413210274

查询语句 SELECT
      
知识点:
  内连接(Inner Join)，外连接(Outer Join)，自连接(Self Join)，GROUP BY 和 having 查询，子查询 ，相关子查询，
获取最前面的指定行数记录，分页查询，查询结果操作 等
      
1. 连接查询 (内连接  Inner Join)
  将多个表联合查询出多个表中的数据.
      
  SELECT 字段名 | 表达式 | 分组函数 等等
  FROM 表名, 表名 等等
  WHERE 查询条件
  GROUP BY 分组字段列表
  HAVING 分组查询过滤条件
  ORDER BY 排序字段列表
        
  例如:
    创建一个部门表
    CREATE TABLE dept
    (
        id int(10) PRIMARY KEY,
        name VARCHAR(10)
    );
    向部门表插入数据
    INSERT INTO dept(id,name)VALUES(1,'IT');
    INSERT INTO dept(id,name)VALUES(2,'财务');
      
    创建一个员工表
    CREATE TABLE emp
      (
      id int(10),
      name VARCHAR(10),
      salary int(10),
      dept_id int(10) REFERENCES dept(id)
      );
    向员工表插入数据
    INSERT INTO emp(id,name,salary,dept_id)VALUES(1,'王浩',6000,1);
    INSERT INTO emp(id,name,salary,dept_id)VALUES(2,'李洁',5000,1);
    INSERT INTO emp(id,name,salary,dept_id)VALUES(3,'张强',6000,2);
    INSERT INTO emp(id,name,salary,dept_id)VALUES(4,'张涛',8000,2);
      
    1 . 笛卡尔积交叉连接 
      结果为两个表的乘积
      SELECT * FROM dept,emp;
      
    2. 等值连接
      通过相等的字段值为条件建立起来的连接
      SELECT 字段 | 表达式 
      FROM 表名1,表名2 
      WHERE 表名1.列名=表名2.列名;
      
      当连接的表中有相同字段名时, 字段一般加上 "表名." 前缀
      例如:  
        显示所有员工信息, 和 其所在的部门名称
        SELECT dept.name,emp.* FROM dept,emp WHERE dept.id=emp.dept_id;
      
        显示 财务部门 的所有员工名称,工资 和 其所在的部门名称
        SELECT dept.name,emp.name,emp.salary FROM dept,emp WHERE dept.id=emp.dept_id AND dept.id=2;
      
    3 . 连接中表使用 别名
      显示 财务部门 的所有员工信息, 和 其所在的部门名称
      SELECT a.name,b.* FROM dept a,emp b  WHERE a.id=b.dept_id AND a.id=2;
      SELECT a.name,b.* FROM dept a JOIN emp b  ON a.id=b.dept_id AND a.id=2;
      
    4 . 连接中字段 使用 别名
      显示 财务部门 的所有员工信息, 和 其所在的部门名称
      SELECT a.name dept_name ,b.name ,b.salary  FROM dept a,emp b  WHERE a.id=b.dept_id AND a.id=2;
      SELECT a.name dept_name ,b.name ,b.salary FROM dept a JOIN emp b  ON a.id=b.dept_id AND a.id=2;
      
    5. 多表等值连接
      创建一个国家表 country
      CREATE TABLE country
        (
        id int(10),
        country_name VARCHAR(20)
        );
      
      INSERT INTO country VALUES(1,'中国');
      INSERT INTO country VALUES(2,'美国');
      
      创建一个省份表  province
       CREATE TABLE province
        (
        id int(10),
        province_name VARCHAR(20),
        country_id int(10)
        );
       INSERT INTO province VALUES(1,'湖南',1);
       INSERT INTO province VALUES(2,'湖北',1);
       INSERT INTO province VALUES(3,'加州',2);
      
      创建一个城市表  city
      CREATE TABLE city
        (
        id int(10),
        city_name VARCHAR(20),
        province_id int(10)
        );
      
      INSERT INTO city VALUES(1,'长沙',1);
      INSERT INTO city VALUES(2,'武汉',2);
      INSERT INTO city VALUES(3,'三番市',3);
      
      将 country province city 三个表联合查询出城市数据对应的省份名和国家名
       SELECT a.city_name,b.province_name,c.country_name  FROM city a,province b,country c WHERE a.province_id=b.id AND b.country_id=c.id;
        
      SELECT a.city_name,b.province_name,c.country_name  FROM city a JOIN province b ON  a.province_id=b.id JOIN country c ON  b.country_id=c.id;
      
      
    6. 非等值连接
      包括 > , >= , < , <=  , <> , BETWEEN  AND
            
      例如:
        查询出工资范围在 5000 到  6000 的所有员工信息
        SELECT * FROM emp WHERE salary>=5000 AND salary<=6000;
        SELECT * FROM emp WHERE salary BETWEEN 5000 AND 6000;
      
      
2. 外连接(Outer Join)
      
  创建一张汽车表 car
   CREATE TABLE car
    ( 
    id int(10),
    producer VARCHAR(15),
    model VARCHAR(10),
    price float
    );
      
  INSERT INTO car(id,producer,model,price)VALUES(1,'上海大众','桑塔纳',150000);
  INSERT INTO car(id,producer,model,price)VALUES(2,'德国大众','奥迪',400000);
  INSERT INTO car(id,producer,model,price)VALUES(3,'一汽大众','奥迪 A6',500000);
  INSERT INTO car(id,producer,model,price)VALUES(4,'北京奔驰','奔驰 280',1000000);
      
  在员工表 emp 添加一个  该员工拥有的汽车id字段 car_id
  ALTER TABLE emp add(car_id int(10));
      
  设置王浩拥有 上海大众 car_id=1,   李洁拥有 德国大众  car_id=2
  UPDATE emp SET car_id=1 WHERE name='王浩';
  UPDATE emp SET car_id=2 WHERE name='李洁';
        
  回顾等值连接  查出员工的姓名 和 员工拥有的车的信息
  SELECT a.name, b.producer,b.model FROM emp a,car b WHERE a.car_id=b.id;
      
  左外连接(Left Outer Join)  左边表不匹配的数据显示, 右边表以NULL 填充
  SELECT a.name, b.producer,b.model FROM emp a,car b WHERE a.car_id=b.id(+);  
  SELECT a.name, b.producer,b.model FROM emp a LEFT JOIN car b ON a.car_id=b.id;
      
      
  右外连接(Right Outer Join)  左边表以NULL填充, 右边表不匹配的数据显示
  SELECT a.name, b.producer,b.model FROM emp a,car b WHERE a.car_id(+)=b.id;
  SELECT a.name, b.producer,b.model FROM emp a RIGHT JOIN car b ON a.car_id=b.id;
      
  全外连接(FULL Join)  左右边表不匹配的数据全显示
  SELECT a.name, b.producer,b.model FROM emp a FULL JOIN car b ON a.car_id=b.id;
    
      
3. 自连接(Self Join)
      
  自连接就是 一个表 自己和自己关联查询
  创建一个产品的分类表  category
  CREATE TABLE category
    (
    id int(10),
    name VARCHAR(15),
    pid int(10)
    );
  添加分类的上级
  INSERT INTO category(id,name,pid)VALUES(1,'IT',0);
  INSERT INTO category(id,name,pid)VALUES(2,'书籍',0);
      
  添加分类的子级
  INSERT INTO category(id,name,pid)VALUES(100,'台式机',1);
  INSERT INTO category(id,name,pid)VALUES(101,'笔记本',1);
  INSERT INTO category(id,name,pid)VALUES(200,'动漫书籍',2);
  INSERT INTO category(id,name,pid)VALUES(201,'小说',2);
      
  查询出分类信息 和 对应的上级分类名称
  SELECT a.name,b.name FROM category a,category b WHERE a.pid=b.id;
  SELECT a.name,b.name FROM category a JOIN category b ON a.pid=b.id; 
      
      
      
5.GROUP BY 和 having 查询
      
    1. 基于多字段分组
      //修改emp car_id
      UPDATE emp SET  car_id=4 WHERE id IN(3,4);
      
      //按照部门和员工拥有的车类型, 统计人数
      SELECT dept_id,car_id,count(*) FROM emp GROUP BY dept_id,car_id;
      
      多字段分组时, 其分组字段出现的次序是有影响的 , 先按照部门分组, 然后按照部门的车分组.
      SELECT dept_id,car_id,count(*) FROM emp GROUP BY car_id,dept_id;
      
    2. HAVING
            
      WHERE 语句不允许用分组函数, 由于处理顺序的原因 WHERE , GROUP BY , HAVING 字句不允许使用SELECT 字段/表达式列表的别名
      
      //错误
      SELECT dept_id deptment ,car_id car ,count(*) count FROM emp GROUP BY car,deptment;
      
      //正确
      SELECT dept_id deptment ,car_id car ,count(*) count FROM emp GROUP BY dept_id,car_id;
      
      //多表联合分组  按照部门和员工拥有的车类型, 统计人数,显示部门名称和车名
      SELECT a.name ,c.producer ,count(1)  FROM dept a,emp b,car c WHERE a.dept_id=b.dept_id AND b.car_id=c.car_id  GROUP BY a.name,c.producer ;
      
      
6. 子查询
      
  SELECT 字段列表
  FROM 表名
  WHERE 表达式 , 运算符 , 子查询;
      
  例如:
    查出员工的工资 大于 李洁的工资 的员工所有数据
    SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name='李洁');
      
    查询出部门平均工资 高于 单位整体平均工资的部门编号
     SELECT dept_id,avg(salary) FROM emp GROUP BY dept_id HAVING avg(salary)>(SELECT avg(salary) FROM emp);
      
    查询出部门平均工资 高于 单位整体平均工资的部门名称
    SELECT b.name,avg(a.salary) FROM emp a,dept b WHERE a.dept_id=b.dept_id GROUP BY b.name HAVING avg(a.salary)>(SELECT avg(salary) FROM emp);
      
  1. 单行子查询
    返回单行单列的查询.   可以用 ( = , > , < , >= , <= , <>)
      1. 子查询返回单行单列结果
        查出员工的工资 大于 李洁的工资 的员工所有数据
        SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name='李洁');
      
      2. 子查询未返回任何行
        查出员工的工资 大于 王涛的工资 的员工所有数据   
        SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name='王涛');
      
        子查询返回 NULL相当于
        SELECT * FROM emp WHERE salary > NULL;
      
        注意如果要判断NULL 要用 IS NULL 或者  IS NOT NULL
        SELECT * FROM emp WHERE salary IS NULL;
        SELECT * FROM emp WHERE salary IS NOT NULL;
      
      3. 子查询返回多行结果
        此时不允许用 ( = , > , < , >= , <= , <>) 运算符
        错误:
        查出员工的工资 大于 李洁和王浩的工资 的员工所有数据
        SELECT * FROM emp WHERE salary > (SELECT salary FROM emp WHERE name='李洁' OR  name='王浩');
      
      
  2. 多行子查询
    返回多行单列的查询结果 ,使用(IN , All , ANY, EXISTS) 运算符
    IN : 等于列表中的任何一个              id IN (3,4)
    ALL: 和子查询返回的所有值比较          id > ALL(3,4)
    ANY: 和子查询返回的任意一个值比较 id > ANY(3,4)
    EXISTS:判断子查询是否有返回结果        exists  不为NUll true 否则 false
      
    插入测试数据
    INSERT INTO emp(id,name,salary,dept_id,car_id)VALUES(5,'黎明',4000,1,3);
    INSERT INTO emp(id,name,salary,dept_id,car_id)VALUES(6,'张军',5000,1,3);
    INSERT INTO emp(id,name,salary,dept_id,car_id)VALUES(7,'王龙',6000,2,3);
    INSERT INTO emp(id,name,salary,dept_id,car_id)VALUES(8,'永福',8000,2,null);
      
    IN 查询出 与王浩部门相同的所有员工信息
     SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE name='王浩');
      
     IN 查询出 与王浩 或 张强 部门相同的所有员工信息
     SELECT * FROM emp WHERE dept_id IN (SELECT dept_id FROM emp WHERE name='王浩' OR name='张强');
      
    ALL 查询工资 高于 所有的部门平均工资 的员工信息
    SELECT * FROM emp WHERE salary>ALL(SELECT avg(salary) FROM emp GROUP BY dept_id);
      
    ANY 查询工资高于任意部门平均工资的员工信息
     SELECT * FROM emp WHERE salary>ANY(SELECT avg(salary) FROM emp GROUP BY dept_id);
          
    EXISTS 判断子查询是否返回结果  TRUE , FALSE
    如果王浩所在部门存在 返回所有员工信息
    SELECT * FROM emp WHERE exists (SELECT dept_id FROM emp WHERE name='王浩');
      
    IN 和 EXISTS 的区别
      IN  不能处理NULL
      EXISTS  可以匹配NULL
      
      主表数据量小而 从表 数据量非常大时，exists 的查询效率高。
      从表数据量小而 主表 数据量非常大时，in 的查询效率高。
      
          
  3. 多列查询
    如果是单行数据用( = , > , < , >= , <= , <>) 
    如果是多行数据用(IN , All , ANY) 
        
    例如:
      查询出与 王龙 所在部门 和 用同一种类型的车的 员工信息
      SELECT * FROM emp WHERE (dept_id,car_id)=(SELECT dept_id,car_id FROM emp WHERE name='王龙');
      
      查询出与 王龙 或 张军 所在部门 和 用同一种类型的车的 员工信息
      SELECT * FROM emp WHERE (dept_id,car_id)IN(SELECT dept_id,car_id FROM emp WHERE name='王龙' OR name='张军');
      
      
7. 相关子查询
  子查询要用到主查询的结果， 也就是子查询和主查询相互依赖.
        
  例如:
    查询出所有的部门和对应的员工数量
     SELECT dept.*,(SELECT count(1) FROM emp WHERE emp.dept_id=dept.dept_id) count  FROM dept;
      
    返回已经雇佣了员工的部门
    INSERT INTO dept VALUES(3,'质量');
      
    SELECT dept_id,name FROM dept a WHERE EXISTS (SELECT 'y' FROM emp b WHERE a.dept_id=b.dept_id);
      
      
8.  获取最前面的 limit 行记录
      
  1. 返回全部查询结果 工资降序排列
    SELECT * FROM emp ORDER BY salary DESC;
      
  2. limit n,m
    1. 非排序查询 (无 ORDER　BY)
      返回员工的前 5 条数据
      SELECT ROWNUM ,emp.* FROM emp WHERE limit 5;
      
    2. 排序查询(ORDER BY)
      返回工资最高的前 5 名的员工信息
      SELECT ROWNUM ,emp.* FROM emp WHERE limit 5 ORDER BY salary DESC;
      
      
9. 分页查询
  例如:
    按照工资倒排序 返回员工信息 每页显示 3 条记录 , 当前显示第一页
    SELECT ROWNUM ,emp.* FROM emp WHERE limit 0,3 ORDER BY salary DESC;
      
    按照工资倒排序 返回员工信息 每页显示 3 条记录 , 当前显示第二页
    SELECT ROWNUM ,emp.* FROM emp WHERE limit 3,3 ORDER BY salary DESC;
      
      
10. 查询结果的集合操作
  SELECT 查询集合操作 　 并集(UNION).
      
      
  SELECT 语句1
  UNION[ALL] 
  SELECT 语句2
  ORDER BY 子句
      
      
  创建一个帐户 account 表
  CREATE TABLE account
    (
    id int(10),
    username VARCHAR(10),
    pwd VARCHAR(10),
    name VARCHAR(10)
    );
      
   INSERT INTO account(id,username,pwd,name)VALUES(1,'admin','111','高见');
   INSERT INTO account(id,username,pwd,name)VALUES(2,'hurui','111','胡锐');
   INSERT INTO account(id,username,pwd,name)VALUES(3,'xiaowei','111','晓巍');
   INSERT INTO account(id,username,pwd,name)VALUES(4,'xiaowei','111','永福');
      
  UNION 计算两个结果的并集, 自动去掉结果集的重复行.
  从 emp 和 account 表中查出所有的人名
  SELECT name FROM emp
    UNION
  SELECT name FROM account;
      
  UNION 计算两个结果的并集,不去掉结果集的重复行.
  从 emp 和 account 表中查出所有的人名
  SELECT name FROM emp
    UNION ALL
  SELECT name FROM account;

http://www.verejava.com/?id=1717413210274