02-MySQL(下)

weixin_33860722發表於2018-11-17
MySql

一、資料庫操作

3.DQL

3.7分組查詢

group by：分組查詢 將欄位中相同值歸為一組

having：有...,表示條件，類似於where的用法

演示：

建立emp員工表:

  員工號  員工姓名    工作    上級編號  受僱日期       薪水   佣金     部門編號
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm    | deptno|
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | smith  | clark    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

#1.查詢各個部門的人數
mysql> select count(*) from emp group by deptno;
+----------+
| count(*) |
+----------+
|        2 |
|        2 |
|        4 |
+----------+
3 rows in set (0.00 sec)

#2.查詢每個部門的部門編號和每個部門的工資和
mysql> select deptno,sum(sal) from emp group by deptno;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     20 |  3800.00 |
|     30 |  8675.00 |
+--------+----------+
3 rows in set (0.00 sec)


#3.查詢每個部門的部門編號和每個部門的人數
mysql> select deptno,count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        2 |
|     30 |        4 |
+--------+----------+
3 rows in set (0.00 sec)


#4.查詢每個部門的部門編號和每個部門工資大於1500的人數
mysql> select deptno,count(*) from emp where sal>1500 group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        2 |
|     20 |        1 |
|     30 |        3 |
+--------+----------+
3 rows in set (0.01 sec)


#5.查詢工資總和大於7000的部門編號以及工資和
#執行順序  from em->group by deptno-->having sum(sal)>7000-->select
mysql> select deptno,sum(sal) from emp group by deptno having sum(sal)>7000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  8675.00 |
+--------+----------+
2 rows in set (0.00 sec)

總結：

​ having和where的區別

​ a.二者都表示對資料執行條件

​ b.having是在分組之後對資料進行過濾

​ where是在分組之前對資料進行過濾

​ c.having後面可以使用聚合函式

​ where後面不可以使用聚合函式

演示：

#查詢工資大於1500，工資總和大於6000的部門編號和工資和


mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     10 |  7450.00 |
|     30 |  7425.00 |
+--------+----------+
2 rows in set (0.00 sec)
3.8分頁查詢

limit：用來限定查詢的起始行，以及總行數

演示：

#LIMIT [offset,] rows
#offset指定要返回的第一行的偏移量，rows第二個指定返回行的最大數目。初始行的偏移量是0(不是1)。

#1.查詢4行記錄，起始行從0開始
mysql> select * from emp limit 0,4;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7369 | smith  | clark    | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from emp limit 2,3;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | ename | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.01 sec)

總結：

​ 查詢語句書寫順序：select----》from---》where---》group by-----》having-----》order by----->limit

​ 查詢語句的執行順序：from----》where-----》group by----》having----》select-----》order by----》limit

二、資料的完整性

作用：保證使用者輸入的資料儲存到資料庫中是正確的

實質：建立表的時候給表中的欄位新增約束

1.實體完整性

實體：表中的一行或者一條記錄代表一個實體

實體完整性的作用：標識每一行資料不重複

約束型別：

​ 主鍵約束【primary key】

​ 唯一約束【unique】

​ 自動增長列【auto_increment】

1.1主鍵約束【primary key】

特點：資料唯一，且不能為null

主關鍵字可以是表中的一個欄位或者多個欄位，它的值用來唯一標識表中的某一條記錄

場景：在多個表的關聯關係中

演示：

mysql> create table stu1(
    -> id int primary key,
    -> name varchar(50)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> create table stu2(
    -> id int,
    -> name varchar(50),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table stu3(
    -> id int,
    -> name varchar(50)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> alter table stu3 add constraint stu3_id primary key(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
1.2唯一約束

作用：在非主鍵列中不能輸入重複的值

演示：

mysql> create table stu4(
    -> id int primary key,
    -> name varchar(50) unique
    -> );
Query OK, 0 rows affected (0.04 sec)


#primary key和unique之間的區別
a.二者都強調的是唯一性
b.在同一個表中，只能出現一個primary key，可以出現多個unique
c.primary key不允許為null，但是unique是允許的
1.3自動增長列

給主鍵新增新增自動增長性，列只能是整數型別

場景：一般新增給主鍵

演示：

mysql> create table stu5(
    -> id int primary key auto_increment,
    -> name varchar(50) unique
    -> );
Query OK, 0 rows affected (0.04 sec)

2.域完整性

作用：限制單元格資料的正確性，

​ 域代表當前單元格

約束型別：

​ 資料型別

​ 非空約束【not null】

​ 預設值約束【default】

2.1資料型別

數字型別：int float doule decimal

日期型別：date

字串型別：char varchar

2.2非空約束【not null】

演示：

mysql> create table stu6( id int primary key auto_increment, name varchar(50) unique not null);
Query OK, 0 rows affected (0.03 sec)

#注意：name被約束為not null，插入資料的時候，name堅決不能為null，如果未null，資料庫立馬報錯
2.3預設值約束

演示：

mysql> create table stu7(
    -> id int primary key auto_increment,
    -> name varchar(50) unique not null,
    -> address varchar(50) default "beijing"
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into stu7 (id,name,address) values(1,'aaa','fff');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu7 (id,name,address) values(2,'bbb',default);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu7;
+----+------+---------+
| id | name | address |
+----+------+---------+
|  1 | aaa  | fff     |
|  2 | bbb  | beijing |
+----+------+---------+
2 rows in set (0.00 sec)


練習:
建立一個學生表.
id   int 主鍵 自動增長.
name varchar(20) 不能重複 不能為空
address varchar(20) 預設為廣東
按照上面的約束,插入5條資料.

3.引用完整性

新增外來鍵約束：foreign key

注意：新增外來鍵必須先有主鍵，主鍵和外來鍵的型別必須保持一致

舉例：學生表，成績表

作用：將兩個甚至多個毫無關聯的表產生聯絡

演示：

#建立表
#學生表
 create table student(
    stuid varchar(10) primary key,
    stuname varchar(50)
     );
Query OK, 0 rows affected (0.01 sec)

#成績表
 create table score(
     stuid varchar(10),
     score int,
     courseid int
     );
Query OK, 0 rows affected (0.00 sec)

#插入資料
 insert into student values('1001','zhangsan');
 insert into student values('1002','xiaoming');
 insert into student values('1003','jack');
 insert into student values('1004','tom');

 insert into score values('1001',98,1);
 insert into score values('1002',95,1);
 insert into score values('1003',67,2);
 insert into score values('1004',83,2);
 insert into score values('1004',70,1);

#查詢
mysql> select * from student;
+-------+----------+
| stuid | stuname  |
+-------+----------+
| 1001  | zhangsan |
| 1002  | lisi     |
| 1003  | jack     |
| 1004  | tom      |
+-------+----------+
4 rows in set (0.00 sec)

mysql> select * from score;
+-------+-------+----------+
| stuid | score | courseid |
+-------+-------+----------+
| 1001  |    98 |        1 |
| 1002  |    80 |        2 |
| 1003  |    70 |        1 |
| 1004  |    60 |        2 |
| 1002  |    75 |        3 |
+-------+-------+----------+
5 rows in set (0.00 sec)


#方式一
mysql> create table score1( 
  -> score int, 
  -> courseid int,
  -> stuid varchar(10), 
  -> constraint stu_sco_id foreign key(stuid) references student(stuid) );
Query OK, 0 rows affected (0.05 sec)
#注意：stu_sco_id是給約束起的名字，可以自定義

#方式二
mysql> create table score2(
    -> score int,
    -> courseid int,
    -> stuid varchar(10)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> alter table score2 add constraint stu_sco_id foreign key(stuid) references student(stuid);

#注意：主鍵(主表)和外來鍵(從表)的型別必須保持一致
#1.從表中外來鍵的欄位必須來源於主表.
#驗證: score1中插入的記錄,stuid欄位必須來自student表

#2.如果1001這個stuid在score1中使用了,那麼不能再主表student中將其刪除.
#驗證: 對主表student執行刪除操作時，如果刪除的主鍵值在子表score1中出現，那麼就刪除失敗

三、多表查詢

1.表與表之間的關係

一對一

​ 通過巢狀的方式

一對多【多對一】

​ 新增外來鍵

多對多

​ 單獨建立一張新的表

2.合併結果集

作用：將兩個select語句的查詢結果合併到一起

兩種方式：

​ union：去除重複記錄【並集】

​ union all；獲取所有的結果

演示：

#建立表
mysql> create table A(
    -> name varchar(10),
    -> score int
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> create table B( 
  -> name varchar(10), 
  -> score int );
Query OK, 0 rows affected (0.02 sec)

#批量插入資料
mysql> insert into A values('a',10),('b',20),('c',30);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> insert into B values('a',10),('d',40),('c',30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

#查詢結果
mysql> select * from A;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
+------+-------+
3 rows in set (0.00 sec)
mysql> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| d    |    40 |
| c    |    30 |
+------+-------+
3 rows in set (0.00 sec)

#合併結果集
mysql> select * from A
    -> union
    -> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| d    |    40 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select * from A
    -> union all
    -> select * from B;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| a    |    10 |
| d    |    40 |
| c    |    30 |
+------+-------+
6 rows in set (0.00 sec)

注意：被合併的兩個結果，列數、列型別必須相同

如果遇到列數不相同的情況，如下的解決辦法：

mysql> insert into C values('a',10,29),('e',20,45),('c',30,10);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from A
    -> union 
    -> select name,score from C;
+------+-------+
| name | score |
+------+-------+
| a    |    10 |
| b    |    20 |
| c    |    30 |
| e    |    20 |
+------+-------+
4 rows in set (0.00 sec)

3.連線查詢

作用：求出多個表的乘積，例如t1和t2，如果採用了連線查詢，得到的結果是t1*t2

演示：

mysql> select * from student,score;
+-------+----------+-------+-------+----------+
| stuid | stuname  | stuid | score | courseid |
+-------+----------+-------+-------+----------+
| 1001  | zhangsan | 1001  |    98 |        1 |
| 1002  | lisi     | 1001  |    98 |        1 |
| 1003  | jack     | 1001  |    98 |        1 |
| 1004  | tom      | 1001  |    98 |        1 |
| 1001  | zhangsan | 1002  |    80 |        2 |
| 1002  | lisi     | 1002  |    80 |        2 |
| 1003  | jack     | 1002  |    80 |        2 |
| 1004  | tom      | 1002  |    80 |        2 |
| 1001  | zhangsan | 1003  |    70 |        1 |
| 1002  | lisi     | 1003  |    70 |        1 |
| 1003  | jack     | 1003  |    70 |        1 |
| 1004  | tom      | 1003  |    70 |        1 |
| 1001  | zhangsan | 1004  |    60 |        2 |
| 1002  | lisi     | 1004  |    60 |        2 |
| 1003  | jack     | 1004  |    60 |        2 |
| 1004  | tom      | 1004  |    60 |        2 |
| 1001  | zhangsan | 1002  |    75 |        3 |
| 1002  | lisi     | 1002  |    75 |        3 |
| 1003  | jack     | 1002  |    75 |        3 |
| 1004  | tom      | 1002  |    75 |        3 |
+-------+----------+-------+-------+----------+
20 rows in set (0.01 sec)

#問題：進行連線查詢，會產生笛卡爾積
#笛卡爾積：兩個集合相乘的結果
#解釋：假設集合A={a,b},集合B={0,1,2},則笛卡爾積的結果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

#解決辦法：在實際應用中，需要去除重複記錄，則需要通過條件進行過濾
mysql> select  s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.00 sec)
3.1內連線-inner join on

內連線的特點：查詢結果必須滿足條件

演示：

#內連線
mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.00 sec)

#等價寫法
mysql> select  s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.00 sec)

#練習：查詢成績大於70的學生記錄
#方式一
mysql> select  s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid and c.score>70;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
3 rows in set (0.00 sec)

#方式二
#也是內連線，只不過相當於是方言，join on相當於是普通話
mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid where score>70;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
3 rows in set (0.00 sec)
3.2外連線-outer join on

特點：以其中一個表作為參照連線另外一個表

分類：

​ 左外連線：left join on

​ 右外連線：right join on

​ 全外連線：full join【MySQL不支援】

演示：

#左外連線
#以左側連線的條件為準,右側如果沒有對應的值,就會填充null
mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s left join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.01 sec)

#內連線
mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.00 sec)

#右外連線
#以右側連線的條件為準,左側如果沒有對應的值,就會填充null
#參照為c
mysql> select s.stuid,s.stuname,c.score,c.courseid  from student s right join score c on s.stuid=c.stuid;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1002  | lisi     |    75 |        3 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
+-------+----------+-------+----------+
5 rows in set (0.01 sec)
3.3自然連線-natural join

自然連線無需指定連線列，SQL會檢查兩個表中是否相同名稱的列，且假設他們在連線條件中使用，並且在連線條件中僅包含一個連線列。

演示：

mysql> select * from student natural join score;
+-------+----------+-------+----------+
| stuid | stuname  | score | courseid |
+-------+----------+-------+----------+
| 1001  | zhangsan |    98 |        1 |
| 1002  | lisi     |    80 |        2 |
| 1003  | jack     |    70 |        1 |
| 1004  | tom      |    60 |        2 |
| 1002  | lisi     |    75 |        3 |
+-------+----------+-------+----------+
5 rows in set (0.00 sec)

mysql> select student.stuid,student.stuname,score.score from student natural join score;
+-------+----------+-------+
| stuid | stuname  | score |
+-------+----------+-------+
| 1001  | zhangsan |    98 |
| 1002  | lisi     |    80 |
| 1003  | jack     |    70 |
| 1004  | tom      |    60 |
| 1002  | lisi     |    75 |
+-------+----------+-------+
5 rows in set (0.00 sec)

總結：

​ 連線查詢會產生一些無用笛卡爾積，通常需要使用外來鍵之間的關係去除重複記錄，而自然連線無需給給出主外來鍵之間的關係，會自動找到這個等式

4.子查詢

在一個select語句中包含另外一個完整的select語句【select語句的巢狀】

注意：

​ a.子查詢出現的位置：

​ from後

​ where子句的後面，作為條件的一部分被查詢

​ b。當子查詢出現在where後面作為條件時，可以使用關鍵字：any、all

​ c.子查詢結果集的形式

​ 單行單列

​ 單行多列

​ 多行多列

​ 多行單列

演示：

#1.查詢和scott在同一個部門的員工
#思路：先查詢scott所在的部門，然後根據部門查詢所有的資訊
mysql> select deptno from emp where enname='scott';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)

mysql> select * from emp where deptno=(select deptno from emp where enname='scott');
+-------+--------+---------+------+------------+---------+------+--------+
| empno | enname | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+---------+------+------------+---------+------+--------+
|  7369 | smith  | clark   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7788 | scott  | analyst | 7566 | 1987-02-20 | 3000.00 | NULL |     20 |
+-------+--------+---------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)


#2.查詢工資高於joens的員工資訊
#思路：先查詢jones的工資，然後根據jones查詢其他的員工資訊
mysql> select * from emp where sal>(select sal from emp where enname='jones');
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | enname | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7788 | scott  | analyst   | 7566 | 1987-02-20 | 3000.00 | NULL |     20 |
|  7839 | king   | president | NULL | 1987-02-20 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)


#3.查詢工資高於30號部門所有人的員工資訊
#思路：先查詢30號部門中的最高工資，根據最高工資查詢其他的員工資訊
mysql> select * from emp where deptno=30;
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | allen  | salesman | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7566 | jones  | managen  | 7839 | 1981-04-02 | 2975.00 |    NULL |     30 |
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)

mysql> select max(sal) from emp where deptno=30;
+----------+
| max(sal) |
+----------+
|  2975.00 |
+----------+
1 row in set (0.01 sec)

mysql> select * from emp where sal>(select max(sal) from emp where deptno=30);
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | enname | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7788 | scott  | analyst   | 7566 | 1987-02-20 | 3000.00 | NULL |     20 |
|  7839 | king   | president | NULL | 1987-02-20 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)

#4.查詢工作型別和工資與martin完全相同的員工資訊
#思路：先查詢martin的工作型別和工資，然後再查詢其他的員工資訊
mysql> select * from emp where (job,sal) in(select job,sal from emp where enname='martin');
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | enname | job      | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
1 row in set (0.00 sec)

#5.查詢有2個以上下屬的員工資訊
ysql> select mgr,count(mgr) from emp group by mgr having count(mgr)>2;
+------+------------+
| mgr  | count(mgr) |
+------+------------+
| 7839 |          3 |
+------+------------+
1 row in set (0.00 sec)

#6.求各個部門中薪水最高的員工資訊
mysql> select * from emp where sal in(select max(sal) from emp group by deptno);
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | enname | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7566 | jones  | managen   | 7839 | 1981-04-02 | 2975.00 | NULL |     30 |
|  7788 | scott  | analyst   | 7566 | 1987-02-20 | 3000.00 | NULL |     20 |
|  7839 | king   | president | NULL | 1987-02-20 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)

5.自連線

自己連線自己

演示：

#1.查詢7654的員工姓名，經理編號和經理姓名
select m.empno,m.ename,n.ename from emp m join emp n on m.empno = n.mgr where n.empno = 7654;
+-------+-------+--------+
| empno | ename | ename  |
+-------+-------+--------+
|  7698 | blake | martin |
+-------+-------+--------+
1 row in set (0.00 sec)

四、資料庫的備份和恢復

1.備份

生成SQL指令碼，匯出資料

命令：mysqldump -u root -p 資料庫名>生成sql指令碼的路徑

注意：可以不需要登入資料庫

演示：

rock@rockrong:~$ mysqldump -u root -p mydb1>/home/rock/Desktop/mydb1.sql
Enter password:

2.恢復

執行sql指令碼，恢復資料

前提：必須先建立資料庫【空的】

注意：需要先登入資料庫，然後進入指定的資料庫，執行sql指令碼

演示：

rock@rockrong:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> source /home/rock/Desktop/mydb1.sql;
Query OK, 0 rows affected (0.00 sec)

相關文章