MySQL快速入门（二）

最新推荐文章于 2021-12-18 15:54:21 发布
最新推荐文章于 2021-12-18 15:54:21 发布
阅读量428 收藏
点赞数
分类专栏： 我的转载文章 数据库 学习笔记

MySQL快速入门（二）

标签： MySQL多表操作外键约束数据库设计多表查询

目录(?)[+]

文档版本 开发工具 测试平台 工程名字 日期 作者 备注
V1.0       2016.05.10 lutianfei none

SQL多表操作

外键约束

  • 作用：保证数据的完整性。

  • 定义外键约束

    • 可以直接在create语句中定义外键 
      • foreign key 当前表名 (字段名) references 目标表名 (目标表的主键)

  • 创建完语句后，可以直接使用修改语句定义

    • alter table 表名 add foreign key 当前表名 (字段名) references 目标表名 (目标表的主键);

  • 作用：保证数据的完整性。

  • 例子

    • alter table emp add foreign key emp(dno) references dept(did); //把dno作为did的外键，且did必须是主键

  • 有一个部门的表，还有一个员工表，

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">database</span> day16;</span>
    use day16;
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> dept(
        did <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> auto_increment,
        dname <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>)
    );</span>

    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> emp(
        eid <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> auto_increment,
        ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>),
        salaly <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>,
        dno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>
    );</span>

    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> dept <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'研发部'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> dept <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'销售部'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> dept <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'人事部'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> dept <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'扯淡部'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> dept <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'牛宝宝部'</span>);</span>

    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'班长'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10000</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'美美'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10000</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'小凤'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">12000</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'如花'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">14000</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'芙蓉'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">11000</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'东东'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">800</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">insert</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">into</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">values</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'波波'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1000</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>);</span>

<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span> emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> salaly=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2500</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> eid = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">5</span>;</span>
</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li></ul>


数据库的设计

  • 多表设计中三种实体关系 


一对多表的设计

  • 一对多:在多方需要添加一个字段，并且和一方主键的类型必须是相同的。

    • 把该字段作为外键指向一方的主键。

  • 建表原则（都是在多方表中维护）：

    • 在多方表中添加一个字段，把该字段作为外键，并且指向一方表中的主键。
    • 将关系的属性及非多方的主标识加入到多方表
    • 多方表的外键是非多方实体的主标识

  • 画图举例部门与员工的关系 

多对多表的设计

  • 在生活中一个学生可以选择多门课程，每一门课程也可以被多个学生所选择。这些例子在数据库设计中可以使用多对多来完成。

  • 建表原则：

    • 需要创建第三张表，该表中至少有两个字段，作为外键分别指向多对多的双方的主键。
    • 联系的属性及两个实体的主标识形成关系表。
    • 关系表的主键为两个实体主标识的组合
    • 拆开两个一对多的关系，中间创建一个中间表，至少有两个字段。作为外键指向两个多对多关系表的主键。 


一对一表的设计（很少用）
  • 在生活中，1对1的应用比较少，因为完全可以作为一张表出现。但是有可能公司的业务原因，而需要设计1对1的表结构。例如：一个公司对应一个地址，一个地址也只能对应一个公司。
  • 建表原则： 
    • 主键对应 
      • 将两个表的主键进行关联
    • 唯一外键对应 
      • 在任何一方加一个外键，但是需要设置成唯一（unique），指向另一方的主键

  • 简单购物的网站表结构设计 
    • 包含哪些实体？ 用户 订单 商品 分类


多表查询 —— 笛卡尔积

  • 笛卡尔积的概念：（了解） 

  • 查询的语法 
    select * from 表A,表B; 返回的结果就是笛卡尔积。

  • 多表查询时，如果不指定表之间的连接条件，则连接变成笛卡尔乘积操作，笛卡尔积的效果如本页所示。

  • 进行笛卡尔积后，查询结果中存在大量无意义的数据，示例中只有A表中的A_ID和B表中A_ID一致的行才有意义，这样我们通过加上WHERE过滤条件得到想要的数据。这种横跨多表的查询操作一般用连接完成。

  • 如果一张表的记录为m，另一张表的记录是n，两个表做交叉连接后，查询结果的数量为m*n条。


多表查询—内链接
  • 两个表之间是有联系的，通过一个外键关联
  • 内连接分成两种 
    • 普通内连接 
      • 前提条件：需要有外键的。
      • 提交关键字 inner join … on
      • select * from dept inner join emp on dept.did = emp.dno;
    • 隐式内连接（用的是最多的） 
      • 可以不使用inner join … on关键字
      • select * from dept,emp where dept.did = emp.dno;


多表查询—外链接

  • 左外链接（看左表，把左表所有的数据全部查询出来）

    • 前提条件：需要有外键的。
    • 语法： 使用关键字 left [outer] join … on 
      • select * from dept left outer join emp on dept.did = emp.dno;

  • 右外链接（看右表，把右表所有的数据全部查询出来）

    • 前提条件：需要有外键的。
    • 语法： 使用关键字 right [outer] join … on 
      • select * from dept right join emp on dept.did = emp.dno;

  • 外链接与内连接的关系


多表查询—子查询
  • 在sql语言中，select…from…语为一个查询块，将一个查询块嵌套在另一个查询块中作为条件称为嵌套查询,也称为子查询。

  • 外层的查询块称为父查询，内层的查询块称为子查询。

  • 语法：select * from table where 条件 > (select * from table where 条件)

    • any 任意
    • all 全部
    • >any 大于结果的最小值
    • >all 大于结果的最大值


基础练习

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> dept(
    did <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> auto_increment,
    dname <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>)
);</span>

<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">create</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> emp(
    eid <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> auto_increment,
    ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">varchar</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>),
    salaly <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>,
    dno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>
);</span>


//查看所有人所属的部门名称和员工名称?
    //方法一：
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> dept.dname,emp.ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dept,emp <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> dept.did = emp.dno;</span>

    //方法二：
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> d.dname,e.ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dept d,emp e <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> d.did = e.dno;</span>


//统计每个部门的人数(按照部门名称统计，分组group by  count)
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> d.dname,<span class="hljs-aggregate" style="box-sizing: border-box;">count</span>(*) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dept d,emp e <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> d.did = e.dno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">group</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> d.dname;</span>


//统计部门的平均工资（按部门名称统计 ，分组group by  avg）
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> d.dname,<span class="hljs-aggregate" style="box-sizing: border-box;">avg</span>(salaly) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dept d,emp e <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> d.did = e.dno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">group</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> d.dname;</span>


//统计部门的平均工资大于公司平均工资的部门（子查询）
    * 公司的平均工资
        <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-aggregate" style="box-sizing: border-box;">avg</span>(salaly) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> emp;</span>
    * 部门的平均工资
        <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> d.dname,<span class="hljs-aggregate" style="box-sizing: border-box;">avg</span>(e.salaly) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> sa <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> dept d,emp e <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> d.did = e.dno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">group</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> d.dname <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">having</span> sa > (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-aggregate" style="box-sizing: border-box;">avg</span>(salaly) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> emp);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li></ul>


应用练习

  • 表结构
<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DROP</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> dept;</span>
<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">--部门表</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> dept(
    deptno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span>,
    dname <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">14</span>) , --部门名称
    loc <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">13</span>)  ---部门地址
    ) ;</span>

<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> dept(
    deptno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span>,
    dname <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">14</span>) , 
    loc <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">13</span>) 
    ) ;</span>


    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> DEPT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ACCOUNTING'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'NEW YORK'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> DEPT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'RESEARCH'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'DALLAS'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> DEPT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SALES'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CHICAGO'</span>);</span>
    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> DEPT <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">40</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'OPERATIONS'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'BOSTON'</span>);</span>



<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">drop</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">table</span> emp;</span>
<span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">--员工表</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> emp(
    empno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span>, --员工编号
    ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>), ---员工姓名
    job <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">9</span>), --员工工作
    mgr <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>, ----员工直属领导编号
    hiredate <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DATE</span>, ----入职时间
    sal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>, ---工资
    comm <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>, --奖金
    deptno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REFERENCES</span> dept);</span>  <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">--关联dept表</span>



<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> emp(
    empno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>  <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">PRIMARY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span>,
    ename <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>),
    job <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VARCHAR</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">9</span>),
    mgr <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>, 
    hiredate <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DATE</span>, 
    sal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>,
    comm <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>,
    deptno <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> ,
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">foreign</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span> emp(deptno) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">REFERENCES</span> dept(deptno)
);</span>  


<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7369</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SMITH'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CLERK'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7902</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"1980-12-17"</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">800</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7499</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ALLEN'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SALESMAN'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-02-20'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1600</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">300</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7521</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'WARD'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SALESMAN'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-02-22'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1250</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">500</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7566</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'JONES'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'MANAGER'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7839</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-04-02'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2975</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7654</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'MARTIN'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SALESMAN'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-09-28'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1250</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1400</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'BLAKE'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'MANAGER'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7839</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-05-01'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2850</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7782</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CLARK'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'MANAGER'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7839</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-06-09'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2450</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7788</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SCOTT'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ANALYST'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7566</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1987-07-03'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3000</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7839</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'KING'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'PRESIDENT'</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-11-17'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">5000</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7844</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'TURNER'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'SALESMAN'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-09-08'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1500</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7876</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ADAMS'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CLERK'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7788</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1987-07-13'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1100</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7900</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'JAMES'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CLERK'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7698</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-12-03'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">950</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">30</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7902</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'FORD'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'ANALYST'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7566</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-12-03'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3000</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">20</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> EMP <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7934</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'MILLER'</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'CLERK'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">7782</span>,<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'1981-01-23'</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1300</span>,<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">NULL</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">10</span>);</span>


<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> salgrade( 
    grade <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>,--等级
    losal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>, --最低工资
    hisal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span> );</span> <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">--最高工次</span>


<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> salgrade( 
    grade <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span>,
    losal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span>, 
    hisal <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">double</span> );</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> SALGRADE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">700</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1200</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> SALGRADE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1201</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1400</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> SALGRADE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1401</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2000</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> SALGRADE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">4</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2001</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3000</span>);</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> SALGRADE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span> (<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">5</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3001</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">9999</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li><li style="box-sizing: border-box; padding: 0px 5px;">61</li><li style="box-sizing: border-box; padding: 0px 5px;">62</li><li style="box-sizing: border-box; padding: 0px 5px;">63</li><li style="box-sizing: border-box; padding: 0px 5px;">64</li><li style="box-sizing: border-box; padding: 0px 5px;">65</li><li style="box-sizing: border-box; padding: 0px 5px;">66</li><li style="box-sizing: border-box; padding: 0px 5px;">67</li><li style="box-sizing: border-box; padding: 0px 5px;">68</li><li style="box-sizing: border-box; padding: 0px 5px;">69</li><li style="box-sizing: border-box; padding: 0px 5px;">70</li><li style="box-sizing: border-box; padding: 0px 5px;">71</li><li style="box-sizing: border-box; padding: 0px 5px;">72</li><li style="box-sizing: border-box; padding: 0px 5px;">73</li><li style="box-sizing: border-box; padding: 0px 5px;">74</li><li style="box-sizing: border-box; padding: 0px 5px;">75</li><li style="box-sizing: border-box; padding: 0px 5px;">76</li><li style="box-sizing: border-box; padding: 0px 5px;">77</li><li style="box-sizing: border-box; padding: 0px 5px;">78</li><li style="box-sizing: border-box; padding: 0px 5px;">79</li><li style="box-sizing: border-box; padding: 0px 5px;">80</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li><li style="box-sizing: border-box; padding: 0px 5px;">28</li><li style="box-sizing: border-box; padding: 0px 5px;">29</li><li style="box-sizing: border-box; padding: 0px 5px;">30</li><li style="box-sizing: border-box; padding: 0px 5px;">31</li><li style="box-sizing: border-box; padding: 0px 5px;">32</li><li style="box-sizing: border-box; padding: 0px 5px;">33</li><li style="box-sizing: border-box; padding: 0px 5px;">34</li><li style="box-sizing: border-box; padding: 0px 5px;">35</li><li style="box-sizing: border-box; padding: 0px 5px;">36</li><li style="box-sizing: border-box; padding: 0px 5px;">37</li><li style="box-sizing: border-box; padding: 0px 5px;">38</li><li style="box-sizing: border-box; padding: 0px 5px;">39</li><li style="box-sizing: border-box; padding: 0px 5px;">40</li><li style="box-sizing: border-box; padding: 0px 5px;">41</li><li style="box-sizing: border-box; padding: 0px 5px;">42</li><li style="box-sizing: border-box; padding: 0px 5px;">43</li><li style="box-sizing: border-box; padding: 0px 5px;">44</li><li style="box-sizing: border-box; padding: 0px 5px;">45</li><li style="box-sizing: border-box; padding: 0px 5px;">46</li><li style="box-sizing: border-box; padding: 0px 5px;">47</li><li style="box-sizing: border-box; padding: 0px 5px;">48</li><li style="box-sizing: border-box; padding: 0px 5px;">49</li><li style="box-sizing: border-box; padding: 0px 5px;">50</li><li style="box-sizing: border-box; padding: 0px 5px;">51</li><li style="box-sizing: border-box; padding: 0px 5px;">52</li><li style="box-sizing: border-box; padding: 0px 5px;">53</li><li style="box-sizing: border-box; padding: 0px 5px;">54</li><li style="box-sizing: border-box; padding: 0px 5px;">55</li><li style="box-sizing: border-box; padding: 0px 5px;">56</li><li style="box-sizing: border-box; padding: 0px 5px;">57</li><li style="box-sizing: border-box; padding: 0px 5px;">58</li><li style="box-sizing: border-box; padding: 0px 5px;">59</li><li style="box-sizing: border-box; padding: 0px 5px;">60</li><li style="box-sizing: border-box; padding: 0px 5px;">61</li><li style="box-sizing: border-box; padding: 0px 5px;">62</li><li style="box-sizing: border-box; padding: 0px 5px;">63</li><li style="box-sizing: border-box; padding: 0px 5px;">64</li><li style="box-sizing: border-box; padding: 0px 5px;">65</li><li style="box-sizing: border-box; padding: 0px 5px;">66</li><li style="box-sizing: border-box; padding: 0px 5px;">67</li><li style="box-sizing: border-box; padding: 0px 5px;">68</li><li style="box-sizing: border-box; padding: 0px 5px;">69</li><li style="box-sizing: border-box; padding: 0px 5px;">70</li><li style="box-sizing: border-box; padding: 0px 5px;">71</li><li style="box-sizing: border-box; padding: 0px 5px;">72</li><li style="box-sizing: border-box; padding: 0px 5px;">73</li><li style="box-sizing: border-box; padding: 0px 5px;">74</li><li style="box-sizing: border-box; padding: 0px 5px;">75</li><li style="box-sizing: border-box; padding: 0px 5px;">76</li><li style="box-sizing: border-box; padding: 0px 5px;">77</li><li style="box-sizing: border-box; padding: 0px 5px;">78</li><li style="box-sizing: border-box; padding: 0px 5px;">79</li><li style="box-sizing: border-box; padding: 0px 5px;">80</li></ul>

  • 1、返回拥有员工的部门名、部门号。

    • select d.deptno,d.dname from dept d,emp e where d.deptno = e.deptno group by d.deptno;

  • 2、工资水平多于smith的员工信息。

    • select * from emp e where e.sal > (select em.sal from emp em where em.ename = ‘smith’ );

  • 3、返回员工和所属经理的姓名。

    • select e.ename,em.ename from emp e,emp em where e.mgr = em.empno and em.job = ‘MANAGER’;

  • 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

    • select e.ename,em.ename from emp e,emp em where e.mgr = em.empno and em.job = ‘MANAGER’ and e.hiredate < em.hiredate;

  • 5、返回员工姓名及其所在的部门名称。

    • select d.dname,e.ename from dept d,emp e where d.deptno = e.deptno;

  • 6、返回从事clerk工作的员工姓名和所在部门名称。

    • select d.dname,e.ename,e.job from dept d,emp e where d.deptno = e.deptno and job = ‘clerk’;

  • 7、返回部门号及其本部门的最低工资。

    • select d.deptno,min(e.sal) from dept d,emp e where d.deptno = e.deptno group by d.deptno;

  • 8、返回销售部(sales)所有员工的姓名。

    • select * from dept d,emp e where d.deptno = e.deptno and d.dname = ‘sales’;

  • 9、返回工资水平多于平均工资的员工。

    • select * from emp e where e.sal > (select avg(sal) from emp)；

  • 10、返回与SCOTT从事相同工作的员工。

    • select * from emp e where e.job = (select job from emp em where em.ename = ‘SCOTT’);

  • 11、返回与30部门员工工资水平相同的员工姓名与工资。

    • select * from emp e where e.sal in (select e.sal from dept d,emp e where d.deptno = e.deptno and d.deptno = 30);

C51ArmConWinceLinux
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Java开发学习MySQL快速入门和深入
qq_43370419的博客
12-16 192
MySQL基础完整篇以及MySQL进阶完整篇 链接：https://pan.baidu.com/s/14WSxp4kw8EBJ4-5Jg64nUw 提取码：7g7v 下载Xmind软件打开 里面涵括了所有重要知识点，包括分析目标总结练习数据库语法问题延伸和面试题重点等等 MySQL基础篇部分截图： MySQL基础完整篇总体大纲：MySQL进阶篇截图略 ...
MySQL快速入门（一）—Java进阶学习第十五天
lutianfei的专栏
05-11 7716
数据库简介 MySQL数据库 MySQL 安装与配置 MySQL数据库服务器、数据库和表关系 数据在数据库中的存储方式 SQL语句 SQL的简介 SQL语言分类 数据库（CURD — 增删改查） 数据表（CURD—增删改查） 数据—CRUD 备份、恢复数据库
java菜鸟mysql_MySQL入门(三)
weixin_39811166的博客
01-19 54
1.MySQL优化-查看执行记录MySQL提供了一个 EXPLAIN命令,它可以对 SELECT语句进行分析,并输出SELECT执行的详细信息,以供开发人员针对性优化。使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。EXPLAIN命令用法十分简单,在SELECT语句前加上explain就可以...
mysql入门很简单.java_MySQL入门很简单: 15 java访问MySQL数据库
weixin_36480122的博客
02-04 104
1. 连接数据库1.1 下载安装驱动java通过JDBC(Java Database Connectivity，Java数据库连接)来访问MySQL数据库。JDBC的编程接口提供的接口和类与MySQL数据库建立连接，然后将SQL语句的执行结果进行处理。1)下载MySQL Connector/J驱动http://dev.mysql.com/downloads/file/?id=4603632)MyE...
MySQL快速入门（二）——Java进阶学习第十六天
lutianfei的专栏
05-11 6665
SQL多表操作 外键约束 数据库的设计 一对多表的设计 多对多表的设计 一对一表的设计（很少用） 多表查询 —— 笛卡尔积 多表查询—内链接 多表查询—外链接 多表查询—子查询 基础练习 应用练习
Mysql快速入门资料
06-25
这份"Mysql快速入门资料"针对初学者提供了全面的MySQL学习路径。在Java编程中，掌握MySQL是必要的，因为它允许开发者存储、管理和检索应用程序数据。 一、MySQL简介 MySQL是一个高性能、可扩展的SQL数据库，支持...
三十分钟MySQL快速入门(图解)
09-09
在本文中，我们将快速入门MySQL，学习SQL的基础知识和CREATE语句。 首先，我们来安装MySQL。你可以从官方网站（http://dev.mysql.com/downloads/mysql/）下载合适的版本。在安装过程中，你需要选择功能，设置自定义...
MYsql数据库指令快速入门
最新发布
04-11
讲述了对mysql数据库的一些基本知识和指令，可以有效的帮助初学者快速入门并应用在项目里面。
mysql 快速入门
06-14
mysql 最新手册，很好的入门教程，查询手册
mysql快速入门
02-25
mysql入门教程讲到如何启动MySQL，如何对mysql数据库进行登录和操作。
MySQL练习题[单表+多表]
weixin_58362422的博客
06-09 3106
1、返回拥有员工的部门名、部门号。(dept,emp) SELECT DISTINCT d.dname, d.DEPTNO FROM emp e JOIN dept d ON d.deptno=e.deptno; 2、工资水平多于smith的员工信息。 SELECT s.hisal FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal WH.
MYSQL例题和答案
feichong621的博客
07-04 1105
CREATE TABLE employee( id INT, NAME VARCHAR(20), sex VARCHAR(10), salary FLOAT );INSERT INTO employee VALUES(1,"zhangsan","male",2000);INSERT INTO employee VALUES(2,"lisi","male",1000);INSERT INTO employee VALUES(3,"xiaohong","female",4000).
面试之数据库多表查询汇总
qq_43111432的博客
04-28 1124
多表查询 -- -- 1、返回拥有员工的部门名、部门号。 -- 错误 -- 需要员工信息和部门信息，连接emp和dept -- select ename,emp.deptno,dname from emp,dept -- where emp.deptno=dept.deptno -- 要部门名和部门号， -- 拥有员工，在员工表中，有人使用这个部门号 -- select * from dept where deptn...
MySQL数据库练习题
weixin_30914981的博客
08-28 353
表结构 DROP DATABASE IF EXISTS test1; CREATE DATABASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC V...
Mysql总结-2 多表查询
KathyLJQ的博客
05-18 808
emp表： dept表： salgrade表： 返回拥有员工的部门名、部门号。 select * from dept where DEPARTNO in(select DISTINCT deptno from emp); 2. 工资水平多于‘鲁班七号’的员工信息。 select * from emp where sal>(select sal from emp where ename=‘鲁班七号’); 返回员工和所属上级的姓名。 select e.ename eename,e.job e
MySQL综合练习
qq_40245464的博客
11-25 770
表结构 CREATE DATABASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC VARCHA...
MySQL多表与分组练习题及答案
学亮编程手记
01-22 1383
目录 题目 答案 题目 -- 多表与分组练习 -- 注意：员工如果没有特殊指明，指的就是所有人。 -- 1、返回部门号及其本部门的最低工资。 -- 2、计算出员工的年薪，并且以年薪排序。 -- 3、返回员工工作及其从事此工作的最低工资。 -- 3查找和SCOTT从事相同工作的员工信息 -- 4、工资水平多于JAMES的员工信息。 -- 5、返回工资大于平均...
练习与思考
H651883075的博客
12-18 458
#1.返回拥有员工的部门名、部门号。 #2.工资水平多于smith的员工信息。 #3.返回员工和所属经理的姓名。 #4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名 #5.返回员工姓名及其所在的部门名称。 #6.返回从事"保洁工作"的员工姓名和所在部门名称。 #7.返回部门号及其本部门的最低工资。 #8.返回"销售部"所有员工的姓名。 #9.返回工资水平多于平均工资的员工。 #10.返回与SCOTT从事相同工作的员工。 #11.返回与30部门员工工资相同的员工姓名与工资。 #
mysql练习题
会写Bug的攻城狮
06-05 1160
表结构 DROP DATABASE IF EXISTS test1; CREATE DATABASE test1; USE test1;  ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT(  DEPTNO int  PRIMARY KEY,##部门编号  DNAME VARCHAR(14) ,  ##部门名称  LOC VARCHAR(13)...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值