MySQL基本知識復(fù)習(xí)補(bǔ)充

SQL分類

DDL：數(shù)據(jù)定義語言。create、alter、drop、rename、truncate(清空表)

DML：數(shù)據(jù)操作語言。insert、delete、update、select

DCL：數(shù)據(jù)控制語言。commit、rollback、savepoint、grant、revoke

因?yàn)椴樵冋Z句使用頻繁，可以把細(xì)分為DQL（數(shù)據(jù)查詢語言），和commit、rollback細(xì)分為TCL（事務(wù)控制語言）

大小寫規(guī)范

MySQL在Windows下是大小寫不敏感的
MySQL在Linux環(huán)境下是大小寫敏感的：
數(shù)據(jù)庫名、表名、表別名、變量名嚴(yán)格區(qū)分大小寫
關(guān)鍵字、函數(shù)名、列名（字段名）、列別名忽略大小寫

推薦采用統(tǒng)一的書寫規(guī)范：
數(shù)據(jù)庫名、表名、字段名都小寫
SQL關(guān)鍵字、函數(shù)名、綁定變量等都大寫

SHOW CREATE TABLE allblog; 查看建表語句

命名規(guī)則

數(shù)據(jù)庫、表名不得超過30個字符，變量名限制為29個

必須只能包含 A-z,a-z, 0-9，共63個字符

數(shù)據(jù)庫名、表名、字段名等對象名中間不要包含空格

同一個MySQL軟件中，數(shù)據(jù)庫不能同名；同一個庫中，表不能重名；同一個表中，字段不能重名

必須保證你的字段沒有和保留字、數(shù)據(jù)庫系統(tǒng)或常用方法沖突。如果堅(jiān)持使用，請?jiān)赟L語句中使用 ` (著重號) 引起來

保持字段名和類型的一致性，在命名字段并為其指定數(shù)據(jù)類型的時候一定要保證一致性。假如數(shù)據(jù)類型在一個表里是整數(shù)，那在另一個表里可就別變成字符型了

創(chuàng)建數(shù)據(jù)庫

常用SQL命令

注釋

單行注釋 # 這是一條注釋 -- 單行注釋

多行注釋/*這是一條注釋*/

導(dǎo)入

連接工具導(dǎo)入

命令行導(dǎo)入：source xxx.sql

查看數(shù)據(jù)庫表：show database

顯示表結(jié)構(gòu)

DESCRIBE allblog 或 DESC allblog

清空表

TRUNCATE table 危險?。?/p>

truncate table比delete速度快，且使用的系統(tǒng)和事物日志資源少，但無事物（因?yàn)閠runcate中執(zhí)行了commit）且不觸發(fā)trigger，有可能造成事故，故不建議再開發(fā)代碼中使用此語句。

常用數(shù)據(jù)類型

https://blog.csdn.net/m0_52982868/article/details/123032241

MySQL8新特性

DDL原子化，InnoDB表的DDL支持事物完整性

計(jì)算列，某一列的值是通過別的列的值計(jì)算來的；例如a列值為1，b列值為2，c列不需要手動插入，定義a+b的結(jié)果為c的值，那么c就是計(jì)算列，通過別打列計(jì)算得來的

常見數(shù)據(jù)庫對象

• 表
• 數(shù)據(jù)字典
• 約束
• 視圖
• 索引
• 存儲過程
• 存儲函數(shù)
• 觸發(fā)器

SELECT

查詢技巧

列別名

別名 AS或空格，特殊別名可以用雙引號包含

去重

DISTINCT

只能放在SELECT的第一個字段前

如果多個字段：會判斷為多個字段的整體去重

SELECT DISTINCT class, title
FROM ALLBLOG

空值參與運(yùn)算

IFNULL，設(shè)置為想要的數(shù)值

SELECT class, title, IFNULL(comment, 0) as num
FROM ALLBLOG
ORDER BY num DESC

著重號

` 將沖突的字段或者關(guān)鍵字使用著重號處理防止sql報錯

查詢常數(shù)

SELECT '蠟筆小新' AS 動畫, class, title
FROM ALLBLOG

運(yùn)算符

算數(shù)運(yùn)算符

+、-、*、/或DIV（整除）、%或MOD

比較運(yùn)算符

<=>（安全等于，可以對null判斷；字符串和數(shù)值比較會做隱式轉(zhuǎn)換，如果轉(zhuǎn)換不成功這個字符串會被轉(zhuǎn)為0；例如：SELECT 0 = 'a'得到結(jié)果是1）、

=、<>、!=、<、<=、>、>=

非符合類型運(yùn)算符

IS NULL、IS NOT NULL、LEAST（多個值中最小值）、GREATEST（多個值中最大值）、BETWEEN AND、ISNULL、IN、NOT IN、LIKE、REGEXP（正則）、RLIKE（正則）

邏輯運(yùn)算符

OR、||、AND、&&、NOT、!、XOR（異或）

位運(yùn)算符

使用頻率低

排序與分頁

ORDER BY 列名/列別名

ASC 升序

DESC 降序

分頁參數(shù)規(guī)則：

# 每頁size條，取page頁
LIMIT (page - 1) * size, size

LIMIT可以在MYSQL、PGSQL、MariaDB、SQLite等數(shù)據(jù)庫中使用，不能在SQL Server、DB2、Oracle中使用

笛卡爾積

交叉連接。笛卡爾乘積是一個數(shù)學(xué)運(yùn)算。假設(shè)我有兩個集合X和Y，那么X和Y的笛卡爾積就是X和Y的所有可能組合，也就是第一個對象來自于X，第二個對象來自于Y的所有可能。組合的個數(shù)即為兩個集合中元素個數(shù)的乘積數(shù)。

沒有設(shè)置連接條件，就會返回笛卡爾積，一般得到的是錯誤結(jié)果。

SELECT employee_id, department_name
FROM employees,
     departments;

SELECT employee_id, department_name
FROM employees
         CROSS JOIN
     departments;

從sql優(yōu)化的角度，建議多表查詢時，每個字段都指明其所在的表

多表查詢分類

等值連接/非等值連接
自連接/非自連接
內(nèi)連接/外連接

等值連接/非等值連接

非等值連接條件：

查詢salary工資等級：grade_level等級lowest_sal～highest_sal區(qū)間

SELECT e.last_name, e.salary, j.grade_level
FROM employees AS e,
     job_grades AS j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

自連接/非自連接

自連接：一個表操作

# 查詢員工姓名及其管理者的id和姓名
SELECT emp.employee_id, emp.last_name, manager.employee_id, manager.last_name
FROM employees AS emp,
     employees AS manager
WHERE emp.manager_id = manager.employee_id

內(nèi)連接/外連接

**內(nèi)連接：**合并具有同一列的兩個以上表的行，結(jié)果集中不包含一個表與另一個表不匹配的行

SELECT employee_id, department_name
FROM employees AS e,
     departments AS d
WHERE e.department_id = d.department_id
# 也可以用[inner] join的方式實(shí)現(xiàn)

**外連接：**合并具有同一列的兩個以上表的行，結(jié)果集中除了包含一個表與另一個表不匹配的行，還查詢到了左表或右表中不匹配的行

外連接分類：左外連接、右外連接、滿外連接

LEFT [OUTER] JOIN

RIGHT [OUTER] JOIN

SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id

滿外連接：mysql不支持FULL OUTER JOIN（oracle支持）

mysql可以用 LEFT JOIN UNION RIGHT JOIN 代替

7種sql JOINS

左表107條，右表27條，條件相等106條

左上 107條

SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id

右上122條

SELECT last_name, department_name
FROM employees AS e
         RIGHT JOIN departments AS d ON e.department_id = d.department_id

左中 1條

SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_id IS NULL

中心 106條

SELECT last_name, department_name
FROM employees AS e
          JOIN departments AS d ON e.department_id = d.department_id

右中 16條

SELECT last_name, department_name
FROM employees AS e
         RIGHT JOIN departments AS d ON e.department_id = d.department_id
WHERE e.department_id IS NULL

左下 123條(滿外連接)；左上+右中 或 右上+左中

左下 = 左上+右中 123條

SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id
UNION ALL
SELECT last_name, department_name
FROM employees AS e
         RIGHT JOIN departments AS d ON e.department_id = d.department_id
WHERE e.department_id IS NULL

左下 = 右上+左中 123條

SELECT last_name, department_name
FROM employees AS e
         RIGHT JOIN departments AS d ON e.department_id = d.department_id
UNION ALL
SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_id IS NULL

右下 = 左中 + 右中 17條

SELECT last_name, department_name
FROM employees AS e
         LEFT JOIN departments AS d ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT last_name, department_name
FROM employees AS e
         RIGHT JOIN departments AS d ON e.department_id = d.department_id
WHERE e.department_id IS NULL

UNION/UNION ALL

UNION 操作符返回兩個查詢結(jié)果的結(jié)果集的并集，去重重復(fù)記錄

UNION ALL 操作符返回兩個查詢結(jié)果的并集，對于兩個結(jié)果集的重復(fù)部分，不去重

能用UNION ALL的地方盡量不用UNION，因?yàn)槿ブ夭僮鲿档托?/p>

單行函數(shù)

• 操作數(shù)據(jù)對象
• 接受參數(shù)返回一個結(jié)果
• 只對一行進(jìn)行變換
• 每行返回一個結(jié)果
• 可以嵌套
• 參數(shù)可以上一列或一個值

函數(shù)用法：https://blog.csdn.net/qq_38154295/article/details/126416913

聚合函數(shù)

作用與一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值；avg,sum,count,max,min,方差,標(biāo)準(zhǔn)差,中位數(shù)

count(*)
count(1) (count常數(shù))
count(具體字段) (不一定對，null會被忽略)
如果需要對表中的數(shù)據(jù)統(tǒng)計(jì)，執(zhí)行效率跟存儲引擎有關(guān)：
MyISAM存儲引擎，三者效率相同
InnoDB存儲引擎，COUNT(*) = COUNT(1) > COUNT(字段)

MIN(AVG())聚合函數(shù)在mysql中不能嵌套，oracle中可以

GROUP BY

# 員工表中各個部門的平均工資
SELECT d.department_id, d.department_name, AVG(salary) AS money
FROM employees e
         LEFT JOIN departments d on e.department_id = d.department_id
GROUP BY department_id
ORDER BY money DESC;

# 各個職位的平均工資
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

# 查詢各個部門的各個職位的平均工資;分組字段前后順序可以不同，得到的數(shù)據(jù)一樣
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

GROUP BY中WITH ROLLUP

在分組計(jì)算后，將所有結(jié)果再進(jìn)行統(tǒng)計(jì)增加一條；不適合參與ORDER BY一起用

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;

HAVING

用來過濾數(shù)據(jù)；操作聚合函數(shù)，其他條件放到where中提高效率

# 查詢各個部門中最高工資大于1萬的部門信息
SELECT department_id, MAX(salary) AS MONEY
FROM employees
GROUP BY department_id
HAVING MONEY > 10000

子查詢

單行子查詢

。。。略

多行子查詢

也稱為集合比較子查詢，內(nèi)查詢返回多行，使用多行比較操作符

IN 等于列表的任意一個

ANY 需要和單行比較操作符一起使用，和子查詢返回的某一個值比較

SOME 是ANY的別名，一般用ANY

# 返回其他job_id中比job_id為'IT_PROG'部門任意工資低低員工號
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
  AND salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
  
#可以用MIN實(shí)現(xiàn)

ALL 需要和單行比較操作符一起使用，和子查詢返回所有的值比較

# 返回其他job_id中比job_id為'IT_PROG'部門所以工資低低員工號
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
  AND salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

#可以用MAX實(shí)現(xiàn)

相關(guān)子查詢

如果子查詢的執(zhí)行依賴與外部查詢，通常情況下都是因?yàn)樽硬樵冎械谋碛玫搅送獠繑?shù)據(jù)，并進(jìn)行了條件關(guān)聯(lián)，因此每執(zhí)行一次外部查詢，子查詢都要重新計(jì)算一次，這樣的子查詢稱之為關(guān)聯(lián)子查詢

相關(guān)子查詢按一行接一行的順序執(zhí)行，主查詢的每一行都執(zhí)行一次子查詢。

# 查詢員工的工資大于本部門平均工資的員工
SELECT last_name, salary, department_id
FROM employees AS emp1
WHERE salary > (SELECT AVG(salary) FROM employees AS emp2 WHERE emp1.department_id = emp2.department_id)

# 另一種實(shí)現(xiàn)方式
SELECT e.last_name, e.salary, e.department_id
FROM employees AS e
         JOIN (SELECT department_id, AVG(salary) AS avg_salary
               FROM employees
               GROUP BY department_id) AS e_avg
              ON e.department_id = e_avg.department_id
WHERE e.salary > e_avg.avg_salary

# 查詢員工id,salary,按照department_name排序
SELECT employee_id, salary
FROM employees e
ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id) DESC

除了group by和limit后面，其他位置都可以聲明子查詢

視圖

視圖是一種虛擬表，本身不具有數(shù)據(jù)，占用很少的內(nèi)存空間
視圖建立在已有表的基礎(chǔ)上，視圖賴以建立的這些表稱為基表
視圖的創(chuàng)建和刪除只影響視圖本身，不影響對應(yīng)的基表，但對視圖中的數(shù)據(jù)進(jìn)行增加、刪除和修改操作時，數(shù)據(jù)表中的數(shù)據(jù)會相應(yīng)地發(fā)生變化，反之亦然
向視圖提供數(shù)據(jù)內(nèi)容的語句為SELECT語句，可以將視圖理解成存儲起來的SELECT語句
視圖是向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式，可以幫我們把經(jīng)常查詢的結(jié)果集放到虛擬表中，提升使用效率

使用視圖主要做查詢操作

創(chuàng)建視圖類型

創(chuàng)建視圖的類型：創(chuàng)建單表視圖、創(chuàng)建多表聯(lián)合視圖、基于視圖創(chuàng)建視圖

更新視圖

MySQL支持使用INSERT、UPDATE、DELETE語句對視圖中的數(shù)據(jù)進(jìn)行插入、更新和刪除操作，當(dāng)視圖中的數(shù)據(jù)發(fā)生變化時，數(shù)據(jù)表中的數(shù)據(jù)也會發(fā)生變化，反之亦然

要使視圖可更新，視圖中的行和底層基本表中的行之間必須存在一對一的關(guān)系，另外當(dāng)視圖定義出現(xiàn)如下情況時，視圖不支持更新操作：

在定義視圖的時候指定了ALGORITHM = TEMPTABLE，視圖將不支持INSERT和DELETE操作
視圖中不包含基表中所有被定義為非空又未指定默認(rèn)值的列，視圖將不支持INSERT操作
在定義視圖的SELECT語句中使用了JOIN聯(lián)合查詢 ，視圖將不支持INSERT和DELETE操作
在定義視圖的SELECT語句后的字段列表中使用了數(shù)學(xué)表達(dá)式或子查詢 ，視圖將不支持INSERT，也不支持UPDATE使用了數(shù)學(xué)表達(dá)式、子查詢的字段值
在定義視圖的SELECT語句后的字段列表中使用DISTINCT、 聚合函數(shù) 、GROUP BY、HAVING、UNION等，視圖將不支持INSERT、UPDATE、DELETE
在定義視圖的SELECT語句中包含了子查詢，而子查詢中引用了FROM后面的表，視圖將不支持INSERT、UPDATE、DELETE
視圖定義基于一個 不可更新視圖
常量視圖

視圖優(yōu)缺點(diǎn)

• 操作簡單
• 減少數(shù)據(jù)冗余
• 數(shù)據(jù)安全
• 適應(yīng)靈活多變的需求
• 能夠分解復(fù)雜的查詢邏輯

缺點(diǎn)：如果實(shí)際數(shù)據(jù)表的結(jié)構(gòu)變更了，需要及時對相關(guān)視圖進(jìn)行維護(hù)，視圖過多、嵌套視圖的維護(hù)成本高

存儲過程和函數(shù)

https://blog.csdn.net/Becky_Jia/article/details/108308219文章來源地址http://www.zghlxwxcb.cn/news/detail-480405.html

數(shù)據(jù)庫表練習(xí)數(shù)據(jù)SQL

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.28-log : Database - atguigudb
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`atguigudb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `atguigudb`;

/*Table structure for table `countries` */

DROP TABLE IF EXISTS `countries`;

CREATE TABLE `countries` (
  `country_id` char(2) NOT NULL,
  `country_name` varchar(40) DEFAULT NULL,
  `region_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`country_id`),
  KEY `countr_reg_fk` (`region_id`),
  CONSTRAINT `countr_reg_fk` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `countries` */

insert  into `countries`(`country_id`,`country_name`,`region_id`) values ('AR','Argentina',2),('AU','Australia',3),('BE','Belgium',1),('BR','Brazil',2),('CA','Canada',2),('CH','Switzerland',1),('CN','China',3),('DE','Germany',1),('DK','Denmark',1),('EG','Egypt',4),('FR','France',1),('HK','HongKong',3),('IL','Israel',4),('IN','India',3),('IT','Italy',1),('JP','Japan',3),('KW','Kuwait',4),('MX','Mexico',2),('NG','Nigeria',4),('NL','Netherlands',1),('SG','Singapore',3),('UK','United Kingdom',1),('US','United States of America',2),('ZM','Zambia',4),('ZW','Zimbabwe',4);

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(4) NOT NULL DEFAULT '0',
  `department_name` varchar(30) NOT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `location_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `dept_id_pk` (`department_id`),
  KEY `dept_loc_fk` (`location_id`),
  KEY `dept_mgr_fk` (`manager_id`),
  CONSTRAINT `dept_loc_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`),
  CONSTRAINT `dept_mgr_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `departments` */

insert  into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Administration',200,1700),(20,'Marketing',201,1800),(30,'Purchasing',114,1700),(40,'Human Resources',203,2400),(50,'Shipping',121,1500),(60,'IT',103,1400),(70,'Public Relations',204,2700),(80,'Sales',145,2500),(90,'Executive',100,1700),(100,'Finance',108,1700),(110,'Accounting',205,1700),(120,'Treasury',NULL,1700),(130,'Corporate Tax',NULL,1700),(140,'Control And Credit',NULL,1700),(150,'Shareholder Services',NULL,1700),(160,'Benefits',NULL,1700),(170,'Manufacturing',NULL,1700),(180,'Construction',NULL,1700),(190,'Contracting',NULL,1700),(200,'Operations',NULL,1700),(210,'IT Support',NULL,1700),(220,'NOC',NULL,1700),(230,'IT Helpdesk',NULL,1700),(240,'Government Sales',NULL,1700),(250,'Retail Sales',NULL,1700),(260,'Recruiting',NULL,1700),(270,'Payroll',NULL,1700);

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `employee_id` int(6) NOT NULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int(6) DEFAULT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
  CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `employees` */

insert  into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`hire_date`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`) values (100,'Steven','King','SKING','515.123.4567','1987-06-17','AD_PRES',24000.00,NULL,NULL,90),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','1989-09-21','AD_VP',17000.00,NULL,100,90),(102,'Lex','De Haan','LDEHAAN','515.123.4569','1993-01-13','AD_VP',17000.00,NULL,100,90),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','1990-01-03','IT_PROG',9000.00,NULL,102,60),(104,'Bruce','Ernst','BERNST','590.423.4568','1991-05-21','IT_PROG',6000.00,NULL,103,60),(105,'David','Austin','DAUSTIN','590.423.4569','1997-06-25','IT_PROG',4800.00,NULL,103,60),(106,'Valli','Pataballa','VPATABAL','590.423.4560','1998-02-05','IT_PROG',4800.00,NULL,103,60),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','1999-02-07','IT_PROG',4200.00,NULL,103,60),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','1994-08-17','FI_MGR',12000.00,NULL,101,100),(109,'Daniel','Faviet','DFAVIET','515.124.4169','1994-08-16','FI_ACCOUNT',9000.00,NULL,108,100),(110,'John','Chen','JCHEN','515.124.4269','1997-09-28','FI_ACCOUNT',8200.00,NULL,108,100),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','1997-09-30','FI_ACCOUNT',7700.00,NULL,108,100),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','1998-03-07','FI_ACCOUNT',7800.00,NULL,108,100),(113,'Luis','Popp','LPOPP','515.124.4567','1999-12-07','FI_ACCOUNT',6900.00,NULL,108,100),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','1994-12-07','PU_MAN',11000.00,NULL,100,30),(115,'Alexander','Khoo','AKHOO','515.127.4562','1995-05-18','PU_CLERK',3100.00,NULL,114,30),(116,'Shelli','Baida','SBAIDA','515.127.4563','1997-12-24','PU_CLERK',2900.00,NULL,114,30),(117,'Sigal','Tobias','STOBIAS','515.127.4564','1997-07-24','PU_CLERK',2800.00,NULL,114,30),(118,'Guy','Himuro','GHIMURO','515.127.4565','1998-11-15','PU_CLERK',2600.00,NULL,114,30),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','1999-08-10','PU_CLERK',2500.00,NULL,114,30),(120,'Matthew','Weiss','MWEISS','650.123.1234','1996-07-18','ST_MAN',8000.00,NULL,100,50),(121,'Adam','Fripp','AFRIPP','650.123.2234','1997-04-10','ST_MAN',8200.00,NULL,100,50),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','1995-05-01','ST_MAN',7900.00,NULL,100,50),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','1997-10-10','ST_MAN',6500.00,NULL,100,50),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','1999-11-16','ST_MAN',5800.00,NULL,100,50),(125,'Julia','Nayer','JNAYER','650.124.1214','1997-07-16','ST_CLERK',3200.00,NULL,120,50),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','1998-09-28','ST_CLERK',2700.00,NULL,120,50),(127,'James','Landry','JLANDRY','650.124.1334','1999-01-14','ST_CLERK',2400.00,NULL,120,50),(128,'Steven','Markle','SMARKLE','650.124.1434','2000-03-08','ST_CLERK',2200.00,NULL,120,50),(129,'Laura','Bissot','LBISSOT','650.124.5234','1997-08-20','ST_CLERK',3300.00,NULL,121,50),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','1997-10-30','ST_CLERK',2800.00,NULL,121,50),(131,'James','Marlow','JAMRLOW','650.124.7234','1997-02-16','ST_CLERK',2500.00,NULL,121,50),(132,'TJ','Olson','TJOLSON','650.124.8234','1999-04-10','ST_CLERK',2100.00,NULL,121,50),(133,'Jason','Mallin','JMALLIN','650.127.1934','1996-06-14','ST_CLERK',3300.00,NULL,122,50),(134,'Michael','Rogers','MROGERS','650.127.1834','1998-08-26','ST_CLERK',2900.00,NULL,122,50),(135,'Ki','Gee','KGEE','650.127.1734','1999-12-12','ST_CLERK',2400.00,NULL,122,50),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','2000-02-06','ST_CLERK',2200.00,NULL,122,50),(137,'Renske','Ladwig','RLADWIG','650.121.1234','1995-07-14','ST_CLERK',3600.00,NULL,123,50),(138,'Stephen','Stiles','SSTILES','650.121.2034','1997-10-26','ST_CLERK',3200.00,NULL,123,50),(139,'John','Seo','JSEO','650.121.2019','1998-02-12','ST_CLERK',2700.00,NULL,123,50),(140,'Joshua','Patel','JPATEL','650.121.1834','1998-04-06','ST_CLERK',2500.00,NULL,123,50),(141,'Trenna','Rajs','TRAJS','650.121.8009','1995-10-17','ST_CLERK',3500.00,NULL,124,50),(142,'Curtis','Davies','CDAVIES','650.121.2994','1997-01-29','ST_CLERK',3100.00,NULL,124,50),(143,'Randall','Matos','RMATOS','650.121.2874','1998-03-15','ST_CLERK',2600.00,NULL,124,50),(144,'Peter','Vargas','PVARGAS','650.121.2004','1998-07-09','ST_CLERK',2500.00,NULL,124,50),(145,'John','Russell','JRUSSEL','011.44.1344.429268','1996-10-01','SA_MAN',14000.00,0.40,100,80),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','1997-01-05','SA_MAN',13500.00,0.30,100,80),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','1997-03-10','SA_MAN',12000.00,0.30,100,80),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','1999-10-15','SA_MAN',11000.00,0.30,100,80),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','2000-01-29','SA_MAN',10500.00,0.20,100,80),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','1997-01-30','SA_REP',10000.00,0.30,145,80),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','1997-03-24','SA_REP',9500.00,0.25,145,80),(152,'Peter','Hall','PHALL','011.44.1344.478968','1997-08-20','SA_REP',9000.00,0.25,145,80),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','1998-03-30','SA_REP',8000.00,0.20,145,80),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','1998-12-09','SA_REP',7500.00,0.20,145,80),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','1999-11-23','SA_REP',7000.00,0.15,145,80),(156,'Janette','King','JKING','011.44.1345.429268','1996-01-30','SA_REP',10000.00,0.35,146,80),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','1996-03-04','SA_REP',9500.00,0.35,146,80),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','1996-08-01','SA_REP',9000.00,0.35,146,80),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','1997-03-10','SA_REP',8000.00,0.30,146,80),(160,'Louise','Doran','LDORAN','011.44.1345.629268','1997-12-15','SA_REP',7500.00,0.30,146,80),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','1998-11-03','SA_REP',7000.00,0.25,146,80),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','1997-11-11','SA_REP',10500.00,0.25,147,80),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','1999-03-19','SA_REP',9500.00,0.15,147,80),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','2000-01-24','SA_REP',7200.00,0.10,147,80),(165,'David','Lee','DLEE','011.44.1346.529268','2000-02-23','SA_REP',6800.00,0.10,147,80),(166,'Sundar','Ande','SANDE','011.44.1346.629268','2000-03-24','SA_REP',6400.00,0.10,147,80),(167,'Amit','Banda','ABANDA','011.44.1346.729268','2000-04-21','SA_REP',6200.00,0.10,147,80),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','1997-03-11','SA_REP',11500.00,0.25,148,80),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','1998-03-23','SA_REP',10000.00,0.20,148,80),(170,'Tayler','Fox','TFOX','011.44.1343.729268','1998-01-24','SA_REP',9600.00,0.20,148,80),(171,'William','Smith','WSMITH','011.44.1343.629268','1999-02-23','SA_REP',7400.00,0.15,148,80),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','1999-03-24','SA_REP',7300.00,0.15,148,80),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','2000-04-21','SA_REP',6100.00,0.10,148,80),(174,'Ellen','Abel','EABEL','011.44.1644.429267','1996-05-11','SA_REP',11000.00,0.30,149,80),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','1997-03-19','SA_REP',8800.00,0.25,149,80),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','1998-03-24','SA_REP',8600.00,0.20,149,80),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','1998-04-23','SA_REP',8400.00,0.20,149,80),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','1999-05-24','SA_REP',7000.00,0.15,149,NULL),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','2000-01-04','SA_REP',6200.00,0.10,149,80),(180,'Winston','Taylor','WTAYLOR','650.507.9876','1998-01-24','SH_CLERK',3200.00,NULL,120,50),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','1998-02-23','SH_CLERK',3100.00,NULL,120,50),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','1999-06-21','SH_CLERK',2500.00,NULL,120,50),(183,'Girard','Geoni','GGEONI','650.507.9879','2000-02-03','SH_CLERK',2800.00,NULL,120,50),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','1996-01-27','SH_CLERK',4200.00,NULL,121,50),(185,'Alexis','Bull','ABULL','650.509.2876','1997-02-20','SH_CLERK',4100.00,NULL,121,50),(186,'Julia','Dellinger','JDELLING','650.509.3876','1998-06-24','SH_CLERK',3400.00,NULL,121,50),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','1999-02-07','SH_CLERK',3000.00,NULL,121,50),(188,'Kelly','Chung','KCHUNG','650.505.1876','1997-06-14','SH_CLERK',3800.00,NULL,122,50),(189,'Jennifer','Dilly','JDILLY','650.505.2876','1997-08-13','SH_CLERK',3600.00,NULL,122,50),(190,'Timothy','Gates','TGATES','650.505.3876','1998-07-11','SH_CLERK',2900.00,NULL,122,50),(191,'Randall','Perkins','RPERKINS','650.505.4876','1999-12-19','SH_CLERK',2500.00,NULL,122,50),(192,'Sarah','Bell','SBELL','650.501.1876','1996-02-04','SH_CLERK',4000.00,NULL,123,50),(193,'Britney','Everett','BEVERETT','650.501.2876','1997-03-03','SH_CLERK',3900.00,NULL,123,50),(194,'Samuel','McCain','SMCCAIN','650.501.3876','1998-07-01','SH_CLERK',3200.00,NULL,123,50),(195,'Vance','Jones','VJONES','650.501.4876','1999-03-17','SH_CLERK',2800.00,NULL,123,50),(196,'Alana','Walsh','AWALSH','650.507.9811','1998-04-24','SH_CLERK',3100.00,NULL,124,50),(197,'Kevin','Feeney','KFEENEY','650.507.9822','1998-05-23','SH_CLERK',3000.00,NULL,124,50),(198,'Donald','OConnell','DOCONNEL','650.507.9833','1999-06-21','SH_CLERK',2600.00,NULL,124,50),(199,'Douglas','Grant','DGRANT','650.507.9844','2000-01-13','SH_CLERK',2600.00,NULL,124,50),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','1987-09-17','AD_ASST',4400.00,NULL,101,10),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','1996-02-17','MK_MAN',13000.00,NULL,100,20),(202,'Pat','Fay','PFAY','603.123.6666','1997-08-17','MK_REP',6000.00,NULL,201,20),(203,'Susan','Mavris','SMAVRIS','515.123.7777','1994-06-07','HR_REP',6500.00,NULL,101,40),(204,'Hermann','Baer','HBAER','515.123.8888','1994-06-07','PR_REP',10000.00,NULL,101,70),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','1994-06-07','AC_MGR',12000.00,NULL,101,110),(206,'William','Gietz','WGIETZ','515.123.8181','1994-06-07','AC_ACCOUNT',8300.00,NULL,205,110);

/*Table structure for table `job_grades` */

DROP TABLE IF EXISTS `job_grades`;

CREATE TABLE `job_grades` (
  `grade_level` varchar(3) DEFAULT NULL,
  `lowest_sal` int(11) DEFAULT NULL,
  `highest_sal` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_grades` */

insert  into `job_grades`(`grade_level`,`lowest_sal`,`highest_sal`) values ('A',1000,2999),('B',3000,5999),('C',6000,9999),('D',10000,14999),('E',15000,24999),('F',25000,40000);

/*Table structure for table `job_history` */

DROP TABLE IF EXISTS `job_history`;

CREATE TABLE `job_history` (
  `employee_id` int(6) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `department_id` int(4) DEFAULT NULL,
  PRIMARY KEY (`employee_id`,`start_date`),
  UNIQUE KEY `jhist_emp_id_st_date_pk` (`employee_id`,`start_date`),
  KEY `jhist_job_fk` (`job_id`),
  KEY `jhist_dept_fk` (`department_id`),
  CONSTRAINT `jhist_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `jhist_emp_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `jhist_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `job_history` */

insert  into `job_history`(`employee_id`,`start_date`,`end_date`,`job_id`,`department_id`) values (101,'1989-09-21','1993-10-27','AC_ACCOUNT',110),(101,'1993-10-28','1997-03-15','AC_MGR',110),(102,'1993-01-13','1998-07-24','IT_PROG',60),(114,'1998-03-24','1999-12-31','ST_CLERK',50),(122,'1999-01-01','1999-12-31','ST_CLERK',50),(176,'1998-03-24','1998-12-31','SA_REP',80),(176,'1999-01-01','1999-12-31','SA_MAN',80),(200,'1987-09-17','1993-06-17','AD_ASST',90),(200,'1994-07-01','1998-12-31','AC_ACCOUNT',90),(201,'1996-02-17','1999-12-19','MK_REP',20);

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `job_id` varchar(10) NOT NULL DEFAULT '',
  `job_title` varchar(35) NOT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`),
  UNIQUE KEY `job_id_pk` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `jobs` */

insert  into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);

/*Table structure for table `locations` */

DROP TABLE IF EXISTS `locations`;

CREATE TABLE `locations` (
  `location_id` int(4) NOT NULL DEFAULT '0',
  `street_address` varchar(40) DEFAULT NULL,
  `postal_code` varchar(12) DEFAULT NULL,
  `city` varchar(30) NOT NULL,
  `state_province` varchar(25) DEFAULT NULL,
  `country_id` char(2) DEFAULT NULL,
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `loc_id_pk` (`location_id`),
  KEY `loc_c_id_fk` (`country_id`),
  CONSTRAINT `loc_c_id_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `locations` */

insert  into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');

/*Table structure for table `order` */

DROP TABLE IF EXISTS `order`;

CREATE TABLE `order` (
  `order_id` int(11) DEFAULT NULL,
  `order_name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `order` */

insert  into `order`(`order_id`,`order_name`) values (1,'shkstart'),(2,'tomcat'),(3,'dubbo');

/*Table structure for table `regions` */

DROP TABLE IF EXISTS `regions`;

CREATE TABLE `regions` (
  `region_id` int(11) NOT NULL,
  `region_name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`region_id`),
  UNIQUE KEY `reg_id_pk` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `regions` */

insert  into `regions`(`region_id`,`region_name`) values (1,'Europe'),(2,'Americas'),(3,'Asia'),(4,'Middle East and Africa');

/*Table structure for table `emp_details_view` */

DROP TABLE IF EXISTS `emp_details_view`;

/*!50001 DROP VIEW IF EXISTS `emp_details_view` */;
/*!50001 DROP TABLE IF EXISTS `emp_details_view` */;

/*!50001 CREATE TABLE  `emp_details_view`(
 `employee_id` int(6) ,
 `job_id` varchar(10) ,
 `manager_id` int(6) ,
 `department_id` int(4) ,
 `location_id` int(4) ,
 `country_id` char(2) ,
 `first_name` varchar(20) ,
 `last_name` varchar(25) ,
 `salary` double(8,2) ,
 `commission_pct` double(2,2) ,
 `department_name` varchar(30) ,
 `job_title` varchar(35) ,
 `city` varchar(30) ,
 `state_province` varchar(25) ,
 `country_name` varchar(40) ,
 `region_name` varchar(25) 
)*/;

/*View structure for view emp_details_view */

/*!50001 DROP TABLE IF EXISTS `emp_details_view` */;
/*!50001 DROP VIEW IF EXISTS `emp_details_view` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_details_view` AS select `e`.`employee_id` AS `employee_id`,`e`.`job_id` AS `job_id`,`e`.`manager_id` AS `manager_id`,`e`.`department_id` AS `department_id`,`d`.`location_id` AS `location_id`,`l`.`country_id` AS `country_id`,`e`.`first_name` AS `first_name`,`e`.`last_name` AS `last_name`,`e`.`salary` AS `salary`,`e`.`commission_pct` AS `commission_pct`,`d`.`department_name` AS `department_name`,`j`.`job_title` AS `job_title`,`l`.`city` AS `city`,`l`.`state_province` AS `state_province`,`c`.`country_name` AS `country_name`,`r`.`region_name` AS `region_name` from (((((`employees` `e` join `departments` `d`) join `jobs` `j`) join `locations` `l`) join `countries` `c`) join `regions` `r`) where ((`e`.`department_id` = `d`.`department_id`) and (`d`.`location_id` = `l`.`location_id`) and (`l`.`country_id` = `c`.`country_id`) and (`c`.`region_id` = `r`.`region_id`) and (`j`.`job_id` = `e`.`job_id`)) */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

到了這里，關(guān)于MySQL基本知識復(fù)習(xí)補(bǔ)充的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！