Java - MySQL查询语句和多表查询(重点)

今天药忘吃喽~ 2021-07-27 02:43 747阅读 0赞

**一、单表查询:**

1.简单查询

–查询所有的字段:
         select * from 表名 ;
    - - 查询指定的字段:
         select 字段1,字段2… from 表名 ;
    - - 表别名 : 别名中如果有特殊符号或空格,需要用引号引起来
         select * from 表名 [as] 表别名
    - - 列别名 : as 可省略.
         select 字段1 [as] 别名 , 字段2 [as] 别名 from 表名 ;
    - - 去掉重复值: 如果有多个字段 , 要同时重复.
         select distinct 字段 from 表名 ;
    - - 运算查询:
         select (math+english) 总成绩 from 表名 ;

2.条件查询:

比较运算符: > , < , = , >= , <= , <>(!=)
    逻辑运算符:
         between…and… : 显示在某一区间的值(含头含尾)
         in( 多个条件) : 或者(or)关系
         like: 模糊查询
               % 代表零个或者多个任意字符.
                _ 代表一个字符.
         is null : 判断是否为空.

3.排序查询 : 写在sql语句末尾.

select * from 表名 order by 排序字段 ASC(升序 - 默认)/DESC(降序)
    
    被排序的字段如果有多个,先按照第一个排序,在按照后边的依次排序

4.聚合函数 : select之后 , from 之前.

sum(求和): 指定列不是数值类型 , 计算结果为 0 ;
    count(统计数): 不包含null; 一般用 * ;
    max(最大值): 如果是字符串类型 ,使用字符串排序;
    min(最小值) :
    avg(平均值): 指定列不是数值类型 , 计算结果为 0 ;

5.分组查询

group by 被分组的字段.[Having 条件]
              where : 分组查询前过滤 .
              having: 分组查询后过滤 .
    注意; 被分组的字段,一般都写在select后作为查询条件 , 方便查看

6.分页查询(了解)

使用关键字limit
    格式一:只要前x条数据
                select * from 表名 limit x;
    格式二:分页查询
                select * from 表名 limit m,n;
                m:每页数据的开始行数,变化的
                n:每页显示的数量,固定的
    注意:
             数据库中行的索引是从0开始
             列的索引是从1开始

单表案例:

-- 创建数据库
    create database day03; 
    -- 员工表
    USE day03; 
    CREATE TABLE emp(
         -- 员工编号
         empno   INT,
         -- 员工姓名
         ename   VARCHAR(50),
         -- 工作
         job  VARCHAR(50),
         -- 管理者
         mgr   INT,
         -- 雇用时间 
         hiredate   DATE,
         -- 工资
         sal  DECIMAL(7,2),
         -- 奖金
         comm  DECIMAL(7,2),
         -- 部门
         deptno  INT
    ) ;
    
    -- 部门表
    CREATE TABLE dept(
       -- 部门
       deptno  INT,
       -- 部门名称
       dname  VARCHAR(14),
       -- 部门位置
       loc   VARCHAR(13)
       );
    
    -- 向员工表中添加数据.
    INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
    INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
    INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
    INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
    INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
    INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
    INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
    INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
    INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
    INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
    INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
    
    -- 向部门表添加数据 , 采用批量插入数据, 用 , 号隔开 .
    INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK')
    ,(20, 'RESEARCH', 'DALLAS'),(30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');
    
    -- 1.  查询工资大于1200的员工姓名和工资
    SELECT ename 员工姓名 , sal 工资 FROM emp  WHERE sal > 1200;
    
    -- 2.   查询员工号为7698的员工的姓名和部门号
    SELECT ename 员工姓名 , deptno 部门号 FROM emp WHERE empno = 7698;
    
    -- 3.   选择工资不在500到1200的员工的姓名和工资
    SELECT ename 员工姓名 ,sal 工资 FROM emp WHERE sal<=1200 && sal >= 500;
    
    -- 4.   选择雇用时间在1981-02-01到1987-05-01之间的员工姓名，job_id和雇用时间
    SELECT ename 员工姓名,empno , hiredate FROM emp WHERE hiredate BETWEEN '1981-02-01' AND '1987-05-01';
    
    -- 5.   选择在20或30号部门工作的员工姓名和部门号
    SELECT ename 员工姓名,deptno 部门号 FROM emp WHERE deptno IN(20 , 30 );
    
    -- 6.   选择在1981年雇用的员工的姓名和雇用时间
    SELECT ename 员工姓名,hiredate 雇佣时间 FROM emp WHERE hiredate LIKE('1981-__-__');
    
    -- 7.   选择公司中没有管理者的员工姓名及job_id
    SELECT ename 员工姓名,empno FROM emp WHERE mgr IS NULL;
    
    -- 8.   选择公司中有奖金的员工姓名，工资和奖金级别
    SELECT ename 员工姓名,sal 工资,comm 奖金 FROM emp WHERE comm IS NOT NULL OR;
    
    -- 9.   选择员工姓名的第三个字母是a的员工姓名
    SELECT ename 员工姓名 FROM emp WHERE ename LIKE '__A%';
    
    -- 10.  选择姓名中有字母a和e的员工姓名
    SELECT ename 员工姓名 FROM emp WHERE ename LIKE '%A%' OR '%E%'
    
    -- 11.  查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
    SELECT empno 员工号, ename 姓名,sal+(sal*0.2) 工资 FROM emp;
    
    -- 12.  将员工的姓名按首字母排序
    SELECT ename FROM emp ORDER BY ename ASC; -- 升序
    SELECT ename FROM emp ORDER BY ename DESC; -- 降序
    
    -- 13.  查询公司员工工资的最大值，最小值，平均值，总和
    SELECT MAX(sal) 最大值,MIN(sal) 最小值 , AVG(sal) 平均值, SUM(sal) 总和 FROM emp;   
    
    -- 14.  查询各deptno的员工工资的最大值，最小值，平均值，总和
    SELECT deptno 部门,MAX(sal) 最大值,MIN(sal) 最小值 , AVG(sal) 平均值, SUM(sal) 总和 FROM emp GROUP BY deptno;   
    
    -- 15.  选择具有各个deptno的员工人数
    SELECT deptno , COUNT(empno) FROM emp GROUP BY deptno;

**二、多表关系**

> a.一对多关系: 从表使用主表的主键作为主键(外键)  
>           主表中有的数据 , 从表中可以没有 ,  
>           主表必须有数据, 才能向从表中添加数据 .  
>           删除主表的数据 , 要先删除从表之后 , 才能删除主表 .
> 
> ![70][]
> 
> b.多对多关系: 创建一个中间表 , 两个字段分别使用主表的主键(外键)  
>           中间表只能添加两个主表都存在的数据.
> 
> ![70 1][]
> 
> c.自关联一对多: 本表的外键使用本表的主键作为外键约束(了解)
> 
> ![70 2][]

**外键约束**

外键: 保证数据的完整性和准确性 .
    语法:
            alter table 从表 add [constraint 外键名称] foreigh key(从表的外键字段) references 主表名(主键字段)
    
    删除外键约束:使用修改表结构,删除列
              alter table 从表名 drop foreign key 外键名称;

**三、多表查询:**

1.交叉连接查询(有错误数据, 基本不用)

select * from 表A , 表B ; (笛卡尔积) 得到的是两个表的乘积

2.内连接查询: 在交叉连接的基础上 , 使用外键约束作为查询条件 .

隐式内连接:
             select * rom 表A , 表B where A.主键 = B . 主键..
    显示外连接:
             select * from 表A [inner] join 表B on A.主键 = B.主键
             on后面可以继续使用where条件查询.

3.外连接查询: 在交叉连接的基础上 , 使用外键约束作为查询条件.

左外连接:
             select * from 表A left [ outer ] join 表B on 条件.
             以左边表为主, 左边有数据, 右边没有使用null代替.
             左边没有的数据 , 右边也不能出现.
    右外连接:
             select * from 表A right [ outer ] join 表B on 条件;
             以右边表为主 .

4.子查询(sql嵌套使用.)

一条select语句结果作为另一条select语句的（查询条件，查询结果等）。
    a.子查询作为查询条件:
             select * from表B where 字段 = (select 字段 from 表A [where 条件])
    b.子查询作为另一张表: (内连接)
            select * from 表B,(select * from 表A [where 条件])
            where A.主键 = B.外键;

**多表案例:**

--  1、按员工编号升序排列不在10号部门工作的员工信息
    SELECT * FROM emp WHERE deptno != 10 ORDER BY empno; 
    SELECT * FROM emp e,dept d WHERE d.deptno !=10 && d.deptno = e.deptno ORDER BY e.empno; 
    
    -- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列
    SELECT sal*12 FROM emp WHERE ename NOT LIKE '_A%' AND sal > 800 ORDER BY sal DESC; 
    
    -- 3、求每个部门的平均薪水
    SELECT d.dname , AVG(sal) FROM emp e,dept d WHERE  e.deptno = d.deptno GROUP BY d.deptno;
    
    -- 4、求各个部门的最高薪水
    SELECT d.dname , MAX(sal) FROM emp e , dept d WHERE e.deptno = d.deptno GROUP BY e.deptno;
    
    -- 5、求每个部门每个岗位的最高薪水
    SELECT e.deptno , e.job, MAX(sal) FROM emp e , dept d WHERE e.deptno = d.deptno GROUP BY e.deptno,e.job;
    
    
    -- 6、求平均薪水大于2000的部门编号
    SELECT e.deptno FROM emp e ,(SELECT deptno , AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000) c
    WHERE e.deptno = c.deptno GROUP BY deptno; 
    
    -- 7、将员工薪水大于1200且部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
    SELECT AVG(sal) FROM emp ; 
    SELECT e.deptno , sal FROM emp e , (SELECT deptno , AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 1500) c
    WHERE e.deptno = c.deptno AND e.sal> 1200 GROUP BY deptno ORDER BY sal DESC; 
    
    -- 8、求最高薪水的员工信息
    SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp );
    
    -- 9、求多于平均薪水的员工信息
    SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp ); 
    
    -- 10、求各个部门薪水最高的员工信息
    SELECT * FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);
    
    -- 11.  查询和Scott相同部门的员工姓名和雇用日期
    SELECT ename 员工姓名, hiredate 雇用日期 FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename='scott');
    
    -- 12.  查询工资比公司平均工资高的员工的员工号，姓名和工资。
    SELECT empno,ename,sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
    
    -- 13.  查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
    SELECT empno,ename,sal FROM emp e , (SELECT deptno , AVG(sal) a FROM emp GROUP BY deptno) c
    WHERE e.deptno = c.deptno AND e.sal > c.a;
    
    -- 14.  查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
    SELECT empno,ename FROM emp WHERE ename LIKE '%u%' GROUP BY deptno;
    
    -- 15.  查询在部门的loc为 DALLAS 的部门工作的员工的员工号，
    SELECT empno FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'DALLAS');
    
    -- 16.  查询管理者是King的员工姓名和工资 
    SELECT ename , sal FROM emp WHERE mgr = (SELECT empno FROM emp WHERE  ename = 'king');
    
    -- 17.  查询所有部门的名字，location_id，员工数量 和 工资平均值 ()
    SELECT deptno , ename , COUNT(ename) , AVG(sal) FROM emp GROUP BY deptno;

[70]: /images/20210726/d3594ec0f16548bdb1069a6adde27da2.png
[70 1]: /images/20210726/e8a047bb401e4cdcac9860224a11ffdf.png
[70 2]: /images/20210726/7db1ab02e9c049a5976a33eed03a3f47.png