suchen

Heim  >  Artikel  >  Datenbank  >  MySQL快速入门（二）Java进阶学习第十六天

MySQL快速入门（二）Java进阶学习第十六天

WBOY
WBOYOriginal
2016-06-07 14:51:26937Durchsuche

文档版本 开发工具 测试平台 工程名字 日期 作者 备注 V1.0 2016.05.10 lutianfei none SQL多表操作 外键约束 作用：保证数据的完整性。 定义外键约束 可以直接在create语句中定义外键 foreign key 当前表名 (字段名) references 目标表名 (目标表的主键) 创

文档版本 开发工具 测试平台 工程名字 日期 作者 备注
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">    <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> day16;</span>
    use day16;
    <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> dept(
        did <span class="hljs-keyword">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span> auto_increment,
        dname <span class="hljs-keyword">varchar</span>(<span class="hljs-number">30</span>)
    );</span>

    <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> emp(
        eid <span class="hljs-keyword">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span> auto_increment,
        ename <span class="hljs-keyword">varchar</span>(<span class="hljs-number">20</span>),
        salaly <span class="hljs-keyword">double</span>,
        dno <span class="hljs-keyword">int</span>
    );</span>

    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dept <span class="hljs-keyword">values</span>(<span class="hljs-keyword">null</span>,<span class="hljs-string">'研发部'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dept <span class="hljs-keyword">values</span>(<span class="hljs-keyword">null</span>,<span class="hljs-string">'销售部'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dept <span class="hljs-keyword">values</span>(<span class="hljs-keyword">null</span>,<span class="hljs-string">'人事部'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dept <span class="hljs-keyword">values</span>(<span class="hljs-keyword">null</span>,<span class="hljs-string">'扯淡部'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dept <span class="hljs-keyword">values</span>(<span class="hljs-keyword">null</span>,<span class="hljs-string">'牛宝宝部'</span>);</span>

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

<span class="hljs-operator"><span class="hljs-keyword">update</span> emp <span class="hljs-keyword">set</span> salaly=<span class="hljs-number">2500</span> <span class="hljs-keyword">where</span> eid = <span class="hljs-number">5</span>;</span>
</code>


数据库的设计

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


一对多表的设计

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

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

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

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

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

多对多表的设计

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

  • 建表原则：

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


一对一表的设计（很少用）

  • 在生活中，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 大于结果的最大值


基础练习

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> dept(
    did <span class="hljs-keyword">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span> auto_increment,
    dname <span class="hljs-keyword">varchar</span>(<span class="hljs-number">30</span>)
);</span>

<span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> emp(
    eid <span class="hljs-keyword">int</span> <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span> auto_increment,
    ename <span class="hljs-keyword">varchar</span>(<span class="hljs-number">20</span>),
    salaly <span class="hljs-keyword">double</span>,
    dno <span class="hljs-keyword">int</span>
);</span>


//查看所有人所属的部门名称和员工名称?
    //方法一：
    <span class="hljs-operator"><span class="hljs-keyword">select</span> dept.dname,emp.ename <span class="hljs-keyword">from</span> dept,emp <span class="hljs-keyword">where</span> dept.did = emp.dno;</span>

    //方法二：
    <span class="hljs-operator"><span class="hljs-keyword">select</span> d.dname,e.ename <span class="hljs-keyword">from</span> dept d,emp e <span class="hljs-keyword">where</span> d.did = e.dno;</span>


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


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


//统计部门的平均工资大于公司平均工资的部门（子查询）
    * 公司的平均工资
        <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-aggregate">avg</span>(salaly) <span class="hljs-keyword">from</span> emp;</span>
    * 部门的平均工资
        <span class="hljs-operator"><span class="hljs-keyword">select</span> d.dname,<span class="hljs-aggregate">avg</span>(e.salaly) <span class="hljs-keyword">as</span> sa <span class="hljs-keyword">from</span> dept d,emp e <span class="hljs-keyword">where</span> d.did = e.dno <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> d.dname <span class="hljs-keyword">having</span> sa > (<span class="hljs-keyword">select</span> <span class="hljs-aggregate">avg</span>(salaly) <span class="hljs-keyword">from</span> emp);</span></code>


应用练习

  • 表结构
<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> dept;</span>
<span class="hljs-comment">--部门表</span>
<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> dept(
    deptno <span class="hljs-keyword">int</span>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,
    dname <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">14</span>) , --部门名称
    loc <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">13</span>)  ---部门地址
    ) ;</span>

<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> dept(
    deptno <span class="hljs-keyword">int</span>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,
    dname <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">14</span>) , 
    loc <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">13</span>) 
    ) ;</span>


    <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> DEPT <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">10</span>,<span class="hljs-string">'ACCOUNTING'</span>,<span class="hljs-string">'NEW YORK'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> DEPT <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">20</span>,<span class="hljs-string">'RESEARCH'</span>,<span class="hljs-string">'DALLAS'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> DEPT <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">30</span>,<span class="hljs-string">'SALES'</span>,<span class="hljs-string">'CHICAGO'</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> DEPT <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">40</span>,<span class="hljs-string">'OPERATIONS'</span>,<span class="hljs-string">'BOSTON'</span>);</span>



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



<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> emp(
    empno <span class="hljs-keyword">int</span>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,
    ename <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">10</span>),
    job <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">9</span>),
    mgr <span class="hljs-keyword">int</span>, 
    hiredate <span class="hljs-keyword">DATE</span>, 
    sal <span class="hljs-keyword">double</span>,
    comm <span class="hljs-keyword">double</span>,
    deptno <span class="hljs-keyword">int</span> ,
    <span class="hljs-keyword">foreign</span> <span class="hljs-keyword">key</span> emp(deptno) <span class="hljs-keyword">REFERENCES</span> dept(deptno)
);</span>  


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


<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> salgrade( 
    grade <span class="hljs-keyword">int</span>,--等级
    losal <span class="hljs-keyword">double</span>, --最低工资
    hisal <span class="hljs-keyword">double</span> );</span> <span class="hljs-comment">--最高工次</span>


<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> salgrade( 
    grade <span class="hljs-keyword">int</span>,
    losal <span class="hljs-keyword">double</span>, 
    hisal <span class="hljs-keyword">double</span> );</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> SALGRADE <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>,<span class="hljs-number">700</span>,<span class="hljs-number">1200</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> SALGRADE <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">2</span>,<span class="hljs-number">1201</span>,<span class="hljs-number">1400</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> SALGRADE <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">3</span>,<span class="hljs-number">1401</span>,<span class="hljs-number">2000</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> SALGRADE <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">4</span>,<span class="hljs-number">2001</span>,<span class="hljs-number">3000</span>);</span>
<span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> SALGRADE <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">5</span>,<span class="hljs-number">3001</span>,<span class="hljs-number">9999</span>);</span></code>

  • 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

  • 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);
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：最新收集QQ搞笑个性签名太搞笑了的QQ搞笑个性签名Nächster Artikel：MySQL索引优化分析和SQL优化

In Verbindung stehende Artikel

Mehr sehen