MySQL資料庫的知識點

資料庫MySQL

适用于中小型企業，MySQL資料庫适合搭叢集，單獨拿一台MyCat出來管理許多子資料庫

通俗地講，資料庫就是把一些資料整合到一起，其實質還隻存在硬碟裡

SQL語句（Structured Query Language；結構化查詢語言）— 掌握語句是重點

SQL是Structured Query Language(結構化查詢語言)的縮寫。SQL是專為資料庫而建立的操作指令集，是一種功能齊全的資料庫語言。在使用它時，隻需要發出“做什麼”的指令，“怎麼做”是不用使用者考慮的。

SQL語言由3種語言組成：

• 1、DDL - 表
• 2、DML - 資料
• 3、DCL - 權限

MySQL特征：

• 性能快捷、優化SQL語言、
• 容易使用、
• 多線程 和 可靠性、
• 多使用者支援、
• 可移植性和開發源代碼，
• 遵循國際标準和國際化支援、
• 為多種程式設計語言提供API

MySQL的不足之處:

不能直接處理XML資料、一些功能上支援的不夠完善和成熟、不能提供任何OLAP（實時分析系統）功能

資料庫系統的使用：

資料庫基礎知識

資料庫由一批資料構成的 有序集合，這些資料被分門别類地存放在一些結構化的 資料表（table）裡，而資料表之間又往往存在交叉引用的關系，這種關系使資料庫又被稱為 關系型資料庫

• 檔案櫃 <==> 資料庫
• 抽屜 <==> 表
• 檔案 <==> 記錄

資料庫：

資料表：

資料在表中的存放：

• 資料 —— DML語言 （Data Manipulation Language ；資料操作語言）：

  增、删、改、查：

  Select 、insert、 update、 delete

• 權限 —— DCL語言（Data Control Language ；資料控制語言）：

  用來控制存儲許可、存取權限

  GRANT、REVOKE

• 表 —— DDL語言 （Data Definition Language； 資料定義語言）

  用來建立資料庫，資料庫對象和定義其列

MySQL中使用SQL語言幾點說明

• 屬于一個SQL語句，使用分号（；）結尾，否則mysql認為語句沒有輸入完
• 箭頭（->）代表SQL語句沒有輸入完
• 取消SQL語句使用 ( \c )
• SQL語句 關鍵字 和 函數名 不區分大小寫（Linux區分，Windows不區分）

• 使用函數時，函數名 和 後面的括号 之間不能有空格

  表的主鍵（PRIMARY KEY）、

  唯一性 (UNIQUE)、

  非空值限制（NOT NULL）、

一、資料類型種類

數值型 分為 整型 和 浮點型

• 字元串:

• 日期和時間值

存儲如“2005 -1-1” 或者“12:00:00”這樣的數值的值。在MySQL中日期是按”年-月-日”的順序。

• NULL值

是一種無類型的值，表示“空，什麼也沒有”。

• NULL 和 NOT NULL

預設為NULL，即插入值時沒有在此字段插入值時自動填NULL，如果指定了NOT NULL，則必須在插入值時在此字段添入值，不允許插入NULL值。

• DEFAULT

可以通過此屬性來指定一個預設值，即如果沒有在此列添加值，那麼預設添加 DEFAULT 後指定值。

• ZEROFILL

前導零填充數值類型值以達到列的顯示寬度。

舉例：

create table  test2(num1 int default 1，num2 int zerofill);
           

• 字元串列類型

  

  1、CHAR 和 VARCHAR類型

CHAR類型 和 VARCHAR類型 長度範圍都是0~255之間的大小。他們之間的差别在于MySQL處理存儲的方式：

CHAR類型 把這個大小視為值的準确大小（用空格填補比較短的值）。

VARCHAR類型 把它視為最大值并且隻使用了存儲字元串實際上需要的位元組數（增加了一個額外的位元組記錄長度）。

因而，較短的值當被插入一個語句為 VARCHAR類型 的字段時，将不會用空格填補（然而，較長的值仍然被截短 ）。

2、BLOB 和 TEXT類型

BLOB是二進制的對象，如果想存儲二進制數 BLOB 将是最佳選擇，而 TEXT與之相同，隻是BOLOB按照二進制編碼排序（區分大小寫），TEXT按照字元文本模式排序（不區分大小寫）。

3、ENUM 和 SET類型

是特殊的串類型，其列值必須從固定的串集中選擇，二者差别為前者必須是隻能選擇其中的一個值，而後者可以多選。（不推薦使用）

二、字元串列類型的可選屬性

• BINARY

  使用此屬性可以使列值作為二進制串處理，即看成BLOB類型。

• NULL 和NOT NULL

  同數值型功能相同

• DEFAULT

  同數值型功能相同

  

  

說明：

每個時間和日期列類型都有一個零值，當插入 非法數值 時就用 零值 來添加

表示日期時必須先按：年，月，日的順序給出

DATE ,TIME ,DATETIME 分别是存儲日期，時間與日期和時間的組合，其格式為“YYYY-MM-DD”，“hh:mm:ss” 和 “YYYY-MM-DD hh:mm:ss”，對于 DATETIME類型，日期和時間部分都需要

TIMESTAMP

時間戳列類型以YYYYMMDDhhmmss的格式來表示值，其取值範圍是19700101000000到2037年的某個時間，主要用于記錄更改或建立某個記錄

---------------------------------------------------------------------DDL-------------------------------------------------------------------------------------------------------------

三、建立資料庫操作 ・DDL

建立資料庫操作：

文法：

create database 資料庫名

叙述：建立一個具有指定名稱的資料庫。如果要建立的資料庫已經存在，或者沒有建立它的适當權限，則此語句失敗。

例：建立一個student庫。

mysql> create database student;
           

1.建立表

查詢—>建立查詢

建立表操作：

文法：

create table 表名（
列名1  列類型  [<列的完整性限制>],
列名2  列類型  [<列的完整性限制>], 
... ...);
           

叙 述：在目前資料庫下新建立一個資料表。

列類型：表示該列的資料類型。

例：

建立一個表 school， 其由兩列組成，第一列屬性為非空，并做為主鍵,并自增

create table school(
  school_id  int(10) not null auto_increment primary key,
  school_name varchar(20) 				
)；

   CREATE TABLE stu(
   Sno INT(4) PRIMARY KEY AUTO_INCREMENT,    #“4”表示學号類型
   Sname VARCHAR(20) UNIQUE,      #“20”表示位元組長
   Ssex VARCHAR(4),               #“4”也表示位元組長
   Sbirthday datetime,
   Stel VARCHAR(30)
   )
           

常見完整性限制：

PRIMARY  KEY 	            主碼限制(主鍵)

UNIQUE						 唯一性限制

NOT  NULL				     非空值限制		
	
AUTO_INCREMENT	         用于整數列預設自增1

UNSIGNED                     無符号整數

DEFAULT default_value		預設值限制

DEFAULT cur_timestamp      建立新記錄時預設儲存目前時間（僅适用timestamp資料列）

ON UPDATE cur_timestamp     修改記錄時預設儲存目前時間（僅适用timestamp資料列）

CHARACTER SET name          指定字元集（僅适用字元串）
           

資料表類型：

在建立一個新的MySQL資料表時，可以為它設定一個類型，其中最重要的 3種類型 是

MyISAM：成熟、穩定和易于管理

InnoDB：加入事物、資料行級鎖定機制、外鍵限制條件、崩潰恢複等新功能
 
HEAP：隻存在于記憶體中，可做臨時表
           

create table tmp(…)ENGINE=MyISAM
           

主鍵與外鍵

資料表之間的關聯 / 引用關系是依靠具體的 主鍵（primary key） 和 外鍵（foreign key） 建立起來的。

主鍵： 幫助MySQL以最快的速度把一條特定的資料記錄的位置确定下來。

主鍵必須是唯一的

主鍵應該是緊湊的，是以整數類型比較适合

外鍵： 引用另外一個資料表的某條記錄。

外鍵列類型 盡可能與 主鍵列類型 保持一緻

外鍵列應該加上 NOT NULL

主鍵
create table student1(
   sid int not null auto_increment,
   name varchar(20) not null,
   gender char(1),
   primary key(sid,gender)
);

外鍵（自動檢查外鍵是否比對，僅适用InnoDB）
create table score(
   cid int not null auto_increment primary key,
   score int,
   sid int,
   foreign key(sid) references student(sid)
);
           

主表和從表:

1. 當主表中沒有對應的記錄時，不能将記錄添加到子表

   ——成績表中不能出現在學員資訊表中不存在的學号；

2. 不能更改主表中的值而導緻子表中的記錄孤立

   ——把學員資訊表中的學号改變了，學員成績表中的學号也應當随之改變；

3. 子表存在與主表對應的記錄，不能從主表中删除該行

   ——不能把有成績的學員删除了

4. 删除主表前，先删子表

   ——先删學員成績表、後删除學員資訊表

選擇主鍵的原則:

最少性_盡量選擇單個鍵作為主鍵

穩定性_盡量選擇數值更新少的列作為主鍵

2.檢視表結構

顯示表結構操作：      
文法：  describe  表名 ：                
Desc  表名:     
叙述： 用于顯示表的建立結構
describe 表名
           

3.删除資料庫或表操作

删除表操作

文法：drop table [if exists] tab_name [,tab_name]…

叙述：從資料庫中删除給定的表。如果給出 if exists 子句，則删除不存在的表不會出錯。

删除資料庫操作

文法：

drop database [if exists] db_name

叙述：删除給定的資料庫。在删除一個資料庫後，它就永遠沒有了，是以要特别小心。如果給出

if exists

子句，則删除不存在的資料庫不會出錯。

文法：drop table [if exists]  tab_name [,tab_name]...  
           

if exists

是說，如果這個表存在你就删，不存在就别删了；還有

tab_name

後面用逗号隔開，加省略号 的意思是 可以一次删除好多表

4.更改表結構操作

文法：

alter table 表名 action:

說明：action 可以是如下語句：

可以為表添加一列，如果沒指定

first

或者

after

，則在列尾添加一列，否則在指定列添加新列

add primary key (列名)

為表添加一個主鍵，如果主鍵已經存在，則出現錯誤

add foreign key(列名) references 表名(列名)

為表添加一個外鍵

alter 列名 set default 預設值

可以更改指定列預設值

change 舊列名 新列名  [first | after 列名]
可以更改列類型和列名稱，如果原列的名字和新列的名字相同

modify 列名  [first | after 列名]
		 和change的作用相同

drop 列名            // 可以删除一列

drop primary key    // 可以删除主鍵

rename as 新表名     // 可以将表名更改
           

舉例：

舉例1：
向people表中添加字段address2, 類型為varchar, 最大長度為100
alter table people add address2 varchar(100);

舉例2:	
将 people 表 中的 name 列 預設值改為 100:
alter table people alter name set default 100;

舉例3:
向 student 表 增加 “入學時間” 列，其資料類型為日期型。
alter table student add scome date；

注：無論基本表中原來是否已有資料，新增加的列一律為空值。

舉例4:
将年齡的資料類型 改為 半字長整數。
alter table student modify sage smallint；

注：修改原有的列定義有可能會破壞已有資料
           

四、MySQL運算符

1. 算術運算符

字元串自動轉換數字

用字元串表示的數字在任何可能便于計算的地方都被自動地轉換為數字。當執行轉換時，MySQL遵循兩個基本規則：

• 如果第一位是數字的，字元串被用于一個算數運算中，那麼它被轉換為這個數字的值。
• 如果第一個包含字元和數字混合的，字元串不能被正确的轉換為數字，那麼它被轉換成0。

  

  

  

  

2. 比較運算符

比較運算符允許我們對表達式的左邊和右邊進行比較。一個比較運算符的結果總是 1（真），0（假），或是為 NULL（不能确定）。

比較運算符可以用于比較數字和字元串。數字作為浮點值比較，而字元串以不區分大小寫

對于＜＞運算符，如果表達式兩邊不相等，傳回真值，相等傳回假值。還可以比較字元串

• BETWEEN運算符 用于檢驗一個值（或者一個求值表達式）是否存在一個指定的範圍内：

  

• IＮ運算符 用于檢驗一個值（或者一個求值表達式）是否包含在一個指定的值集合中。

  

  可以使用 IS NULL 或者 IS NOT NULL 運算符來測定是否為空。

可以使用特殊的 <=>運算符，MySQL稱為 NULL安全的等于 運算符。這意味着即使當包含在比較運算符中的表達式含有一個NULL值時，MySQL也會為比較運算符傳回一個真值 或 假值。

如果想執行通配符資料搜尋，應該使用LIKE運算符。它通過在表達式中允許使用專門的通配字元，可以找出與指定搜尋字元串全部或部分比對的記錄。

預設情況下，比較是不區分大小寫的方式執行的。然而，以前我們注意到，可以添加 BINARY關鍵字 讓MySQL執行區分大小寫的比較。

MySQL資料庫中的通配符

“%” (百分号)  代表任意長度（長度可以為0）的字元串

舉例：
a%b表示以a開頭，以b結尾的任意長度的字元串。如acb，addgb，ab 等都滿足該比對串

“_ ”(下橫線)  代表任意單個字元
舉例：
a_b表示以a開頭，以b結尾的長度為3的任意字元串。如acb，afb等都滿足該比對串
           

REGEXP運算符 允許我們執行更複雜的字元串比較運算，在這裡使用 UNIX正規表達式。

3. 邏輯運算符

4. 位運算符

---------------------------------------------------------------------------------------DML-------------------------------------------------------------------------

五、插入記錄操作 ・DML

文法：

INSERT into <表名> ([列名1],[列名2]) VALUES <值清單>
           

叙述：

如果表名後面沒寫字段名，則預設是向所有的字段添加值，另外字元串值應該用‘ ’或“ ”引号括起來

舉例1：向 people表 中添加一條記錄：

insert into people(name,age) values(“zhangsan”,20);
           

插入案例

建立一張學生資訊表，往表中插入資料
create table students(
  scode int not null auto_increment,
  sname varchar(20) not null,
  saddress varchar(20) default‘未知’,
  sgrade int,
  semail varchar(20),
  ssex bit,
  primary key(scode)
);
           

插入資料行 1

注意事項1:

每次插入一行資料，不可能隻插入半行或者幾列資料，是以，插入的資料是否有效将按照整行的完整性的要求來檢驗；

注意事項2:

每個資料值的資料類型、精度和小數位數必須與相應的列比對；

注意事項3:

如果在設計表的時候就指定了某列不允許為空，則必須插入資料；

注意事項4:

具有預設值的列，可以使用DEFAULT（預設）關鍵字來代替插入的數值

1. 插入多行資料：

INSERT INTO <表名>(列名)    VALUES(<列名值>), (<列名值>), (<列名值>)     ……
           

INSERT INTO <表名>(列名)
   SELECT <列名>
   FROM <源表名>
           

INSERT INTO 	TongXunLu ('姓名','位址','電子郵件') 
         SELECT 	SName,SAddress,SEmail
          FROM 		Students
           

2. 更改記錄操作：

文法：

UPDATE <表名> SET <列名 = 更新值>  [WHERE <更新條件>]    # SET不區分大小寫，都行
           

叙述：

where 子句

是判斷語句，用來

設定條件，限制隻更新比對的行

，

如果不帶where子句，則更新所有行資料

。

舉例：

将 student表 中的所有學生名稱為"Alex"的改為"Tom":

update student set sname="Tom" where sname="Alex";
           

更新資料行：

UPDATE Student SET SSEX = 0

UPDATE Student 
SET Sdept ='家政系' 
WHERE Sdept = '刺繡系'
 
UPDATE Sc
SET Grade = Grade + 5
WHERE Grade <= 95
           

3. 删除記錄操作：

文法：

DELETE FROM <表名> [WHERE <删除條件>]
           

叙述：

此語句

删除表中的行，如果不帶where子句，則删除整個表中的記錄，但是表不被删除

。

舉例：

删除student表中的所有年齡小于18歲的記錄：

delete from student where sage<18;
           

删除學生編号為’95005’的學生資訊，包括成績資訊

說明：

表和字段的引用方式有兩種：絕對引用 和 相對引用

- 絕對引用：資料庫名.表名(.字段名)
- 相對引用：表名.(字段名)
           

4. 查詢：

查詢是最複雜的，它分為好多好多不同的查詢

查詢産生一個虛拟表，看到的是表形式顯示的結果，但結果并不真正存儲，每次執行查詢隻是先從資料表中提取資料，并按照表的形式顯示出來

怎麼查：

SQL文法：

SELECT [DISTINCT | DISTINCTROW | ALL] 
select_expression,... 
[FROM table_references 
   [WHERE where_definition] 
   [GROUP BY col_name,...] 
   [HAVING where_definition] 
   [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...] 	
   [LIMIT [offset,] rows] 
   [PROCEDURE procedure_name]
] 
           

查詢記錄操作：

distinct:      排重

from子句：      指定查詢資料的表

where子句：     查詢資料的過濾條件

group by子句：  對比對where子句的查詢結果進行分組

having子句：    對分組後的結果進行條件限制

order by子句：  對查詢結果進行排序，後面跟desc降序或asc升序（預設）。　  	
	
limit子句：     對查詢的顯示結果限制數目

procedure子句： 查詢存儲過程傳回的結果集資料
           

舉例1 :	查詢全體學生的學号與姓名。
      select sno,sname from student;

舉例2 :	查詢全體學生的詳細記錄。
      select  sno,sname,ssex,sage,sdept from student;  
      或 select   *  from student; 

舉例3 : 	查全體學生的姓名及其出生年份。
        select sname,2014-sage from student； 

舉例4 :	查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。
		select sname,'year of birth: ',2008-sage, lower(sdept) from student;

舉例5 :	查詢選修了課程的學生學号(去掉重複的記錄)
        select   distinct  studentid  from sc;

舉例6 :	查詢全體學生的學号與姓名，用中文顯示列名。
        select sno as ‘編号’,sname as ‘姓名’ from student;

舉例7 :	給表設定别名。
        select   s.sno,s.sname  from student as s; 

舉例8 : 	查詢年齡在20以下的學生的姓名。
        select sname from student where sage<20； 

舉例9 :	查詢全體學生的姓名、年齡，要求按照年齡降序排序。
		select sname,sage from student order by sage desc;

舉例10 :	查詢年齡最大的前3個學生的姓名和年齡，或第4、5個學生
        select sname,sage from student order by sage desc limit 3;或（limit 3,2）
           

使用集函數

SQL提供的 統計函數 稱為 集函數.

主要的集函數：

記數函數： count(列名) 計算元素的個數

求和函數： sum(列名)	對某一列的值求和，但屬性必須是整型

計算平均值：avg(列名)對某一列的值計算平均值

求最大值： max(列名) 找出某一列的最大值

求最小值： min(列名) 找出某一列的最小值
           

舉例11 :	查詢學生總數。
        select count(*) from student;

舉例12 :	查詢選修了課程的學生人數。
        select count(distinct studentid) from sc;

舉例13 :	查詢1号課程的學生平均成績。
        select avg(grade) from sc where courseid=1;

舉例14 :	查詢1号課程的學生最高分和最低分。
        select max(grade) as ‘最高分’,min(grade) as ‘最低分’ from sc where courseid=1;

舉例15 :	查詢每個學生的平均成績。
        select studentid,avg(grade) as ‘平均成績’ from sc group by studentid;

舉例16 :	查詢學生的平均成績在70分以上的。
        select studentid,avg(grade) as ‘平均成績’ from sc group by studentid having avg(grade)>70;
           

在WHERE子句中使用謂詞 :

BETWEEN    AND    :      在兩數之間
 	
 NOT   BETWEEN    AND　：  不在兩數之間
	
IN <值表>			：    是否在特定的集合裡（枚舉）
	
NOT IN <值表>　：          與上面相反
	
LIKE		：           是否比對于一個模式
	
IS NULL（為空的）或 IS NOT NULL（不為空的）REGEXP :     檢查一個值是否比對一個正常表達式。
           

舉例17 :	查詢年齡在20~23歲（包括20歲和23歲）之間的學生的姓名、系别和年齡 。
        select sname,sdept,sage from student where sage between 20 and 23;

舉例18 :	查詢年齡不在20~23歲之間的學生姓名、系别和年齡。
        select sname,sdept,sage from student where sage not between 20 and 23;

舉例19 :	查詢'資訊系'、'美術系'和'計算機系'學生的姓名和性别。
        select sname,ssex from student where sdept  in (‘資訊系',‘美術系',‘計算機系');

舉例20 :	查詢學号為95001的學生的詳細情況。
        select * from student where sno like '95001'；
       等價于：select * from student where sno='95001'; 

舉例21 :	查詢所有姓劉學生的姓名、學号和性别。
        select sname,sno,ssex from student where sname like ‘劉%';

舉例22 :	某些學生選修課程後沒有參加考試，是以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學号和相應的課程号。
         select studentid,courseid from sc where grade is null;

舉例23 :	查所有有成績的學生學号和課程号。
        select studentid,courseid from sc where grade is not null;

舉例24 :	查詢計算機系年齡在20歲以下的學生姓名。
        select sname from student where sdept=‘計算機系' and sage<20;

舉例25 :	查詢資訊系、美術系和計算機系學生的姓名和性别
        select sname,ssex from student where sdept in (‘資訊系','美術','計算機系');
          可改寫為：
        Select sname,ssex from student where sdept='資訊系' or sdept='美術' or sdept='計算機系';

舉例26 :	查詢選修了3号課程的學生的學号及其成績，查詢結果按分數降序排列。
        select studentid,grade from sc where courseid=3 orber by grade desc;

舉例27 :	查詢全體學生情況，查詢結果按所在系的系号升序排列，同一系中的學生按年齡降序排列。
        select * from student order by sdept,sage desc; 
           

六、多表查詢—問題

學員内部測試成績查詢的每次顯示的都是學員的編号資訊，因為該表中隻存儲了學員的編号；實際上最好顯示學員的姓名，而姓名存儲在學員資訊表；

如何同時從這兩個表中取得資料？

• 連接配接查詢

  同時涉及多個表的查詢稱為 連接配接查詢

  用來連接配接兩個表的條件稱為 連接配接條件

• 内連接配接 ( INNER JOIN )

• 外連接配接

  ——左外聯結 (LEFT JOIN)

  ——右外聯結 (RIGHT JOIN)

  ——全外聯結（FULL JOIN）

  外連接配接與普通連接配接的差別

• 普通連接配接操作隻輸出滿足連接配接條件的元組
• 外連接配接操作以指定表為連接配接主體，将主體表中不滿足連接配接條件的元組一并輸出：

  

  （嵌套查詢：把一個查詢嵌套在另一個查詢裡面）

多表查詢 - 連接配接查詢

select g.gid, s.sname,g.sno, g.score from t_grade g, t_student s where s.sno = g.sno;
           

多表查詢 — 内連接配接-1

多表查詢 — 内連接配接-2

多表查詢 — 三表連接配接

多表查詢 — 左外連接配接

多表查詢 — 右外連接配接

子查詢

将一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為子查詢。一個SELECT-FROM-WHERE語句稱為一個查詢塊

例如：

select sname from student where sno in (select studentid from sc where courseid=2);
           

例如：

查詢選修了課程名為“Java”的學生學号和姓名

select  sno,sname from student where sno in
 (select studentid  from sc   where  cno =
 (select cno from course     where cname=‘Java’));
           

複制表

可以通過在SELECT查詢中指定字段來限制出現在附表中的字段

通過使用SELECT 語句建立已存在表的空副本，并且傳回一個空結果集。

七、SQL語句實戰練習

step1. 連接配接資料庫

step2. 建立表

五個函數：count、min、max、

limit__從第幾條（5）開始查，一共查幾條（5）

幾條查詢SQL語句的練習：

dml:

1）新增資料

INSERT [INTO] <表名> ([列名1],[列名2]) VALUES <值清單>

–

2）修改語句

UPDATE <表名> SET <列名 = 更新值> [WHERE <更新條件>]

–

3）删除語句

DELETE FROM <表名> [WHERE <删除條件>]

–

4）查詢

SELECT [DISTINCT | DISTINCTROW | ALL] select_expression,… [FROM table_references

[WHERE where_definition] [GROUP BY col_name,…] [HAVING where_definition]

[ORDER BY {unsigned_integer | col_name |formula} [ASC | DESC] ,…]

[LIMIT [offset,] rows] [PROCEDURE procedure_name]]

–

1，不能用*

2，where條件

3，count sum avg max min

4,group by having

5,order by asc|desc

6,limit

–

select size,color_id,count(size) from bbs_sku where price>0 GROUP BY size,color_id having size in (‘S’,‘M’,‘L’) ORDER BY color_id desc LIMIT 5,5

–

導入導出

多表聯查

sql優化

1.選擇部門30中的雇員
  select * from emp where deptno=30;
  
2.列出所有經理的姓名,編号和部門. 
  select ename,empno,dname from emp,dept where job='MANAGER' and emp.deptno=dept.deptno;  # MANAGER是字元串，加引号
  
3.列出傭金高于薪金的雇員. 
  select * from emp where comm>sal;
   
4.找出傭金高于或等于薪金50%的雇員
  select * from emp where comm>=sal*0.5;
  
5.找出部門10中所有經理和部門20中所有辦事員的詳細資訊
  select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);

6.找出部門10中所有經理和部門20中所有 辦事員 以及既不是 經理又不是辦事員但薪金大于或等于2000的所有雇員的詳細資料.
   select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or (job not in('MANAGER','CLERK') and (sal>=2000 and deptno=20);

7.找出收取傭金的雇員的不同工作. 
   select distinct job from emp where comm>0;
   select job from emp where comm is not null and comm != 0;       # : 收取傭金：comm is not null

8.找出不收取傭金或收取的傭金低于是200的雇員
   select *  from emp where comm<200 or comm is null;   # 不收取傭金：comm is null

9.找出各月最後一天受雇的所有雇員. 
   select *  from emp where hiredate = last_day(hiredate);

？？？10.找出早于25年之前受雇的所有雇員. 
   ？？？select *  from emp where add_months(hiredate,25*12)<sysdate;   # add_months是增加月份的函數，若小于sysdate，就是25年前

11.查詢隻有首字母大寫的所有雇員姓名. 
   select *  from emp where ename = initcap(ename);

12.查詢所有雇員的資訊,按照姓名排序. 
   select *  from emp order by ename;

13.查詢所有雇員的資訊,按薪金正序排序. 
    select *  from emp order by sal ;

14.查詢所有經理,并按薪金正序 和 雇員姓名正序排序. 
    select *  from emp where job='MANAGER' order by sal ,ename;

15.查詢姓名是MILLER的雇員.

16.查詢薪金1500和3000,或者傭金高于500的雇員. 
   select *  from emp where sal in(1500,3000) or comm>500;
      
17.查詢每個工種的人數. 
   select  job,count(job)  from emp group by job;

18.查詢每個工種的人數.并按 工種的 人數排序.
     select  job,count(job) a  from emp group by job order by a;

19.查詢MANAGER的平均工資。
   select round(avg(sal),2) from emp where job='MANAGER';  # 2 —— 保留兩位小數

20.查詢每個工種的人數,和平均工資,并按平均工資進行排序. 
   select count(job),avg(sal) a from emp group by job order by a;

21.查詢部門 名稱是SALES 的所有員工. 
   select * from emp ,(select * from dept where dname='SALES') a where emp.deptno=a.deptno;

22.查詢部門名稱是RESERCH的所有員工的平均工資. 
   select avg(sal) from emp ,(select * from dept where dname='REARCH') a where emp.deptno=a.deptno;

23.查詢每個工種中雇員的最高薪金; 
   select job, max(sal) from emp group by job;

24.查詢職務是經理的雇員的最低薪金;
   select job, min(sal) from emp where job = 'MANAGER';

25.新增兩條記錄
   empno為 57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;

   empno為 58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
   insert into emp values
       (57 , 'ANDY' , 'MANAGER' ,  null , '1990-08-12' , 3300 , null , 20),
       (58 , 'JACK' , 'MANAGER' ,  null , '1990-09-12' , 3000 , null , 20)

   修改EMPNO為 57的記錄,将薪金漲20%,DEPTNO改成30;
     update emp set sal = sal*1.2 , deptno=30 where empno = 57
            
   修改ENAME是JACK的記錄,将HIREDATE改成目前日期
      update emp set hiredate = sysdate where ename='JACK';
   
   删除EMPNO為58記錄.
   delete  from emp where empno = 58;

26.查詢平均工資高于1500的所有職務的名稱和平均工資金額. 
    select job,avg(sal) from emp group by job having avg(sal)>1500

27.查詢每個員工的詳細資訊.包括部門編号,部門名稱及部門所在城市. 
   select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;

28.按部門分組,查詢出部門名稱,及部門中員工的平均工資. 
   # 兩表聯查
   select a.*,dname from 
    (select round(avg(sal),2),deptno from emp group by deptno) a,dept where a.deptno=dept.deptno;

29.查詢所有員工中工資數在前五的員工詳細資訊。并從高到低進行排序。
    select * from emp order by sal desc LIMIT 0,5

30.查詢所有經理中工資第三高的員工的詳細資訊。
    select * from emp where job='MANAGER' order by sal desc LIMIT 2,1
   
31.找出所有員工中薪金 第五高 到 第八高 的員工資訊。
    select * from emp order by sal desc LIMIT 4,4;

32.查詢出SCOTT的上級上司的詳細資訊。
    select * from emp where empno in (select MGR from emp where ename='SCOTT');

33.查詢出沒有員工的部門。
    select * from dept where deptno not =(select distinct deptno from emp);

34．查詢出平均高于2000的部門資訊。
   select deptno, avg(sal) from emp group by deptno having avg(sal)>2000

35．向EMP表中插入一條記錄 
 查詢出所的員工的 員工編号，員工姓名，工資，職位，部門名稱（包含未配置設定部門的員工，例如後插入的SCOTT）
  select * from emp e LEFT JOIN dept d on e.deptno=d.deptno

36．找到同名的所有員工
   select ename,count(ename) from emp group by ename having count(ename)>1

37.找出所有同名的員工後，通過一條語句隻保留同名員工中的一個，其餘的則删除掉。
select * from emp where 
    ename in (select ename from emp group by ename having count(ename)>1)
           

1.選擇部門30中的雇員
   select * from emp where deptno=30;

2.列出所有經理的姓名,編号和部門.
  select ename,empno,deptno from emp where job='MANAGER' 

3.列出傭金高于薪金的雇員. 
   select * from emp where comm>sal;

4.找出傭金高于或等于薪金50%的雇員
  select * from emp where comm>=sal*0.5;

5.找出部門10中所有經理和部門20中所有辦事員的詳細資訊
  select * from emp where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20);   
   
6.找出部門10中所有經理和部門20中所有辦事員以及即不是經理又不是辦事員但薪金
  大于或等于2000的所有雇員的詳細資料.
    select * from emp 
      where (job='MANAGER' and deptno=10) or (job='CLERK' and deptno=20) or  (job not in('MANAGER','CLERK') and sal>=2000 and deptno=20);

7.找出收取傭金的雇員的不同工作. 
  select distinct job from emp where comm>0;
  select job from emp where comm is not null and comm != 0 group by job

8.找出不收取傭金或收取的傭金低于是200的雇員
  select *  from emp where comm<200 or comm>0;
  select *  from emp where comm between 0 and 200
  
9.找出各月最後一天受雇的所有雇員.
  select *  from emp where hiredate = last_day(hiredate);

10.找出早于25年之前受雇的所有雇員.
   SELECT * from emp where SUBDATE(hiredate,SYSDATE())>25

11.查詢隻有首字母大寫的所有雇員姓名.

12.查詢所有雇員的資訊,按照姓名排序. 
   select * from emp order by ename;

13.查詢所有雇員的資訊,按薪金正序排序.
   select *  from emp order by sal ;

14.查詢所有經理,并按薪金正序和雇員姓名正序排序. 
   select * from emp where job='MANAGER' order by sal ,ename;

15.查詢姓名是MILLER的雇員.
   select * from emp where ename='MILLER'

16.查詢薪金1500和3000,或者傭金高于500的雇員. 
   select * from emp where sal in(1500,3000) or comm>500;

17.查詢每個工種的人數. 
   select job,count(job)  from emp group by job  ;

18.查詢每個工種的人數.并按工種的人數排序. 
   select  job,count(job) a  from emp group by job order by a;

19.查詢MANAGER的平均工資。
   select round(avg(sal),2) from emp where job='MANAGER';

20.查詢每個工種的人數,和平均工資,并按平均工資進行排序. 
   select count(job),avg(sal) a from emp group by job order by a;

21.查詢部門名稱是SALES的所有員工.
   select * from emp where DEPTNO = 
   (SELECT deptno from dept where dname='SALES')

22.查詢部門名稱是RESERCH的所有員工的平均工資.
   select avg(sal) from emp where DEPTNO = 
   (SELECT deptno from dept where dname='RESERCH')

23.查詢每個工種中雇員的最高薪金;
   select job, max(sal) from emp group by job;

24.查詢職務是經理的雇員的最低薪金;
   select job, min(sal) from emp where job='MANAGER';

25.新增兩條記錄
   empno為57,ENAME是ANDY,JOB是MANAGER,SAL是3300,HIREDATE是'12-8月-90',DEPTNO是20;

   empno為58,ENAME是JACK,JOB是MANAGER,SAL是3000,HIREDATE是'12-9月-90',DEPTNO是20;
   insert into emp values(57,'ANDY','MANAGER',null,1990-08-12,3300,null,20),(58,'JACK','MANAGER',null,1990-09-12,3000,null,20)
           

修改EMPNO為57的記錄,将薪金漲20%,DEPTNO改成30;

update emp set sal = sal*1.2 ,deptno=30 where

empno = 57

修改ENAME是JACK的記錄,将HIREDATE改成目前日期

update emp set hiredate = sysdate where ename=‘JACK’;

删除EMPNO為58記錄.

delete from emp where empno=58

26.查詢平均工資高于1500的所有職務的名稱和平均工資金額. 
     select job,avg(sal) from emp group by job having avg(sal)>1500

27.查詢每個員工的詳細資訊.包括部門編号,部門名稱及部門所在城市. 
    select emp.*,dname,loc from emp,dept where emp.deptno=dept.deptno;

28.按部門分組,查詢出部門名稱,及部門中員工的平均工資. 
    select e.deptno,e.a,d.DNAME from (SELECT deptno,avg(sal) a from emp GROUP BY deptno) e LEFT JOIN dept d on e.deptno=d.DEPTNO

29.查詢所有員工中工資數在前五的員工詳細資訊。并從高到低進行排序。
    select * from emp ORDER BY sal desc LIMIT 0,5

30.查詢所有經理中工資第三高的員工的詳細資訊。
     select * from emp where job='MANAGER' ORDER BY sal desc LIMIT 2,1

31.找出所有員工中薪金第五高到第八高的員工資訊。
    select * from emp ORDER BY sal desc LIMIT 4,4

32.查詢出SCOTT的上級上司的詳細資訊。
   select * from emp where empno=(SELECT mgr from emp where ename='SCOTT')

33.查詢出沒有員工的部門。
select * from emp e RIGHT JOIN dept d on e.DEPTNO=d.DEPTNO where e.EMPNO is null GROUP BY d.DEPTNO

34．查詢出平均高于2000的部門資訊。
    select deptno,avg(sal) from emp GROUP BY deptno having avg(sal)>2000

35．向EMP表中插入一條記錄  
    insert into emp(empno,ename,sal,job,hiredate) values(7999,'SCOTT',2000,'MANAGER',sysdate());  

查詢出所的員工的 員工編号，
員工姓名，工資，職位，部門名稱（包含未配置設定部門的員工，例如後插入的SCOTT）
SELECT * from emp e LEFT JOIN dept d on e.deptno=d.deptno

36．找到同名的所有員工
    SELECT ename,count(ename) from emp group by ename having count(ename)>1

37.找出所有同名的員工後，通過一條語句隻保留同名員工中的一個，其餘的則删除掉。
    select * from emp where ename in (SELECT ename from emp group by ename having count(ename)>1)

FOR
select * from emp where  ename in (SELECT ename from emp group by ename  having count(ename)>1);
DELETE from emp where empno in 
(SELECT empno from emp LIMIT 0,1);
*/
SELECT * from emp where 
datediff(SYSDATE(),hiredate)>25*365