一、上堂回顧
1.概念
? 數(shù)據(jù)庫管理系統(tǒng)，數(shù)據(jù)庫，表
? SQL的分類：DDL、DML、DQL、DCL
2.數(shù)據(jù)庫的使用
? DDL:create【創(chuàng)建數(shù)據(jù)庫和創(chuàng)建表】 alter【操作表】 drop【刪除表】
? DML:insert【給表中插入數(shù)據(jù)】 update【需要結(jié)合where使用】 delete、truncate【表中的數(shù)據(jù)】
? DQL：select
? where
? where like--------->_ %
? as ifnull distinct
? order by:asc desc
? 聚合函數(shù)：count sum max min avg
二、數(shù)據(jù)庫操作
3.DQL
3.7分組查詢
group by：分組查詢 將字段中相同值歸為一組
having：有...,表示條件，類似于where的用法
演示：
創(chuàng)建emp員工表: 員工號 員工姓名 工作 上級編號 受雇日期 薪水 傭金 部門編號 +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | smith | clark | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | clark | manageer | 7839 | 1980-06-17 | 2450 | NULL | 10 | | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000 | NULL | 20 | | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ #1.查詢各個部門的人數(shù) mysql> select count(*) from emp group by deptno; +----------+ | count(*) | +----------+ | 2 | | 2 | | 5 | +----------+ 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.查詢每個部門的部門編號和每個部門的人數(shù) 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的人數(shù) 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的部門編號以及工資和 #執(zhí)行順序 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)總結(jié)：
? having和where的區(qū)別
? a.二者都表示對數(shù)據(jù)執(zhí)行條件
? b.having是在分組之后對數(shù)據(jù)進行過濾
? where是在分組之前對數(shù)據(jù)進行過濾
? c.having后面可以使用聚合函數(shù)
? where后面不可以使用聚合函數(shù)
演示：
#查詢工資大于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：用來限定查詢的起始行，以及總行數(shù)
演示：
#LIMIT [offset,] rows #offset指定要返回的第一行的偏移量，rows第二個指定返回行的最大數(shù)目。初始行的偏移量是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)總結(jié)：
? 查詢語句書寫順序：select----》from---》where---》group by-----》having-----》order by----->limit
? 查詢語句的執(zhí)行順序：from----》where-----》group by----》having----》select-----》order by----》limit
三、數(shù)據(jù)的完整性
作用：保證用戶輸入的數(shù)據(jù)保存到數(shù)據(jù)庫中是正確的
實質(zhì)：創(chuàng)建表的時候給表中的字段添加約束
1.實體完整性
實體：表中的一行或者一條記錄代表一個實體
實體完整性的作用：標識每一行數(shù)據(jù)不重復
約束類型：
? 主鍵約束【primary key】
? 唯一約束【unique】
? 自動增長列【auto_increment】
1.1主鍵約束【primary key】
特點：數(shù)據(jù)唯一，且不能為null
主關(guān)鍵字可以是表中的一個字段或者多個字段，它的值用來唯一標識表中的某一條記錄
場景：在多個表的關(guān)聯(lián)關(guān)系中
演示：
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之間的區(qū)別 a.二者都強調(diào)的是唯一性 b.在同一個表中，只能出現(xiàn)一個primary key，可以出現(xiàn)多個unique c.primary key不允許為null，但是unique是允許的
1.3自動增長列
給主鍵添加添加自動增長性，列只能是整數(shù)類型
場景：一般添加給主鍵
演示：
mysql> create table stu5( -> id int primary key auto_increment, -> name varchar(50) unique -> ); Query OK, 0 rows affected (0.04 sec)
2.域完整性
作用：限制單元格數(shù)據(jù)的正確性，
? 域代表當前單元格
約束類型：
? 數(shù)據(jù)類型
? 非空約束【not null】
? 默認值約束【default】
2.1數(shù)據(jù)類型
數(shù)字類型：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，插入數(shù)據(jù)的時候，name堅決不能為null，如果未null，數(shù)據(jù)庫立馬報錯
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) 練習: 創(chuàng)建一個學生表. id int 主鍵 自動增長. name varchar(20) 不能重復 不能為空 address varchar(20) 默認為廣東 按照上面的約束,插入5條數(shù)據(jù).
3.引用完整性
添加外鍵約束：foreign key
注意：添加外鍵必須先有主鍵，主鍵和外鍵的類型必須保持一致
舉例：學生表，成績表
作用：將兩個甚至多個毫無關(guān)聯(lián)的表產(chǎn)生聯(lián)系
演示：
#創(chuàng)建表 #學生表 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) #插入數(shù)據(jù) 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執(zhí)行刪除操作時，如果刪除的主鍵值在子表score1中出現(xiàn)，那么就刪除失敗
四、多表查詢
1.表與表之間的關(guān)系
一對一
? 通過嵌套的方式
一對多【多對一】
? 添加外鍵
多對多
? 單獨創(chuàng)建一張新的表
2.合并結(jié)果集
作用：將兩個select語句的查詢結(jié)果合并到一起
兩種方式：
? union：去除重復記錄【并集】
? union all；獲取所有的結(jié)果
演示：
#創(chuàng)建表 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) #批量插入數(shù)據(jù) 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 #查詢結(jié)果 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) #合并結(jié)果集 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)注意：被合并的兩個結(jié)果，列數(shù)、列類型必須相同
如果遇到列數(shù)不相同的情況，如下的解決辦法：
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，如果采用了連接查詢，得到的結(jié)果是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) #問題：進行連接查詢，會產(chǎn)生笛卡爾積 #笛卡爾積：兩個集合相乘的結(jié)果 #解釋：假設(shè)集合A={a,b},集合B={0,1,2},則笛卡爾積的結(jié)果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)} #解決辦法：在實際應(yīng)用中，需要去除重復記錄，則需要通過條件進行過濾 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內(nèi)連接-inner join on
內(nèi)連接的特點：查詢結(jié)果必須滿足條件
演示：
#內(nèi)連接 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) #方式二 #也是內(nèi)連接，只不過相當于是方言，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不支持】
演示：
#左外連接 #以左側(cè)連接的條件為準,右側(cè)如果沒有對應(yīng)的值,就會填充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) #內(nèi)連接 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) #右外連接 #以右側(cè)連接的條件為準,左側(cè)如果沒有對應(yīng)的值,就會填充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會檢查兩個表中是否相同名稱的列，且假設(shè)他們在連接條件中使用，并且在連接條件中僅包含一個連接列。
演示：
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)總結(jié)：
? 連接查詢會產(chǎn)生一些無用笛卡爾積，通常需要使用外鍵之間的關(guān)系去除重復記錄，而自然連接無需給給出主外鍵之間的關(guān)系，會自動找到這個等式
4.子查詢
在一個select語句中包含另外一個完整的select語句【select語句的嵌套】
注意：
? a.子查詢出現(xiàn)的位置：
? from后
? where子句的后面，作為條件的一部分被查詢
? b。當子查詢出現(xiàn)在where后面作為條件時，可以使用關(guān)鍵字：any、all
? c.子查詢結(jié)果集的形式
? 單行單列
? 單行多列
? 多行多列
? 多行單列
演示：
#1.查詢和scott在同一個部門的員工 #思路：先查詢scott所在的部門，然后根據(jù)部門查找所有的信息 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的工資，然后根據(jù)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號部門中的最高工資，根據(jù)最高工資查詢其他的員工信息 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個以上下屬的員工信息 mysql> select * from emp where empno in (select mgr from emp group by mgr having count(*)>2); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+-------+-----------+------+------------+------+------+--------+ 2 rows 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的員工姓名，經(jīng)理編號和經(jīng)理姓名 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)
五、數(shù)據(jù)庫的備份和恢復
1.備份
生成SQL腳本，導出數(shù)據(jù)
命令：mysqldump -u root -p 數(shù)據(jù)庫名>生成sql腳本的路徑
注意：可以不需要登錄數(shù)據(jù)庫
演示：
rock@rockrong:~$ mysqldump -u root -p mydb1>/home/rock/Desktop/mydb1.sql Enter password:
2.恢復
執(zhí)行sql腳本，恢復數(shù)據(jù)
前提：必須先創(chuàng)建數(shù)據(jù)庫【空的】
注意：需要先登錄數(shù)據(jù)庫，然后進入指定的數(shù)據(jù)庫，執(zhí)行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)
