MySQL基本知识复习补充_kgeehk

MySQL基本知识复习补充

SQL分类

DDL：数据定义语言。create、alter、drop、rename、truncate(清空表)

DML：数据操作语言。insert、delete、update、select

DCL：数据控制语言。commit、rollback、savepoint、grant、revoke

因为查询语句使用频繁，可以把细分为DQL（数据查询语言），和commit、rollback细分为TCL（事务控制语言）

大小写规范

MySQL在Windows下是大小写不敏感的
MySQL在Linux环境下是大小写敏感的：
数据库名、表名、表别名、变量名严格区分大小写
关键字、函数名、列名（字段名）、列别名忽略大小写

推荐采用统一的书写规范：
数据库名、表名、字段名都小写
SQL关键字、函数名、绑定变量等都大写

SHOW CREATE TABLE allblog; 查看建表语句

命名规则

数据库、表名不得超过30个字符，变量名限制为29个

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

数据库名、表名、字段名等对象名中间不要包含空格

同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名

必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SL语句中使用 ` (着重号) 引起来

保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

创建数据库

常用SQL命令

注释

单行注释 # 这是一条注释 -- 单行注释

多行注释/*这是一条注释*/

导入

连接工具导入

命令行导入：source xxx.sql

查看数据库表：show database

显示表结构

DESCRIBE allblog 或 DESC allblog

清空表

TRUNCATE table 危险！！

truncate table比delete速度快，且使用的系统和事物日志资源少，但无事物（因为truncate中执行了commit）且不触发trigger，有可能造成事故，故不建议再开发代码中使用此语句。

常用数据类型

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

MySQL8新特性

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

计算列，某一列的值是通过别的列的值计算来的；例如a列值为1，b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，通过别打列计算得来的

常见数据库对象

• 表
• 数据字典
• 约束
• 视图
• 索引
• 存储过程
• 存储函数
• 触发器

SELECT

查询技巧

列别名

别名 AS或空格，特殊别名可以用双引号包含

去重

DISTINCT

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

如果多个字段：会判断为多个字段的整体去重

SELECT DISTINCT class, title
FROM ALLBLOG
1
2

空值参与运算

IFNULL，设置为想要的数值

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

着重号

` 将冲突的字段或者关键字使用着重号处理防止sql报错

查询常数

SELECT '蜡笔小新' AS 动画, class, title
FROM ALLBLOG
1
2

运算符

算数运算符

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

比较运算符

<=>（安全等于，可以对null判断；字符串和数值比较会做隐式转换，如果转换不成功这个字符串会被转为0；例如：SELECT 0 = 'a'得到结果是1）、

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

非符合类型运算符

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

逻辑运算符

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

位运算符

使用频率低

排序与分页

ORDER BY 列名/列别名

ASC 升序

DESC 降序

分页参数规则：

# 每页size条，取page页
LIMIT (page - 1) * size, size
1
2

LIMIT可以在MYSQL、PGSQL、MariaDB、SQLite等数据库中使用，不能在SQL Server、DB2、Oracle中使用

笛卡尔积

交叉连接。笛卡尔乘积是一个数学运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积数。

没有设置连接条件，就会返回笛卡尔积，一般得到的是错误结果。

SELECT employee_id, department_name
FROM employees,
     departments;

SELECT employee_id, department_name
FROM employees
         CROSS JOIN
     departments;
1
2
3
4
5
6
7
8

从sql优化的角度，建议多表查询时，每个字段都指明其所在的表

多表查询分类

等值连接/非等值连接
自连接/非自连接
内连接/外连接

等值连接/非等值连接

非等值连接条件：

查询salary工资等级：grade_level等级lowest_sal～highest_sal区间

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
1
2
3
4
5

自连接/非自连接

自连接：一个表操作

# 查询员工姓名及其管理者的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
1
2
3
4
5

内连接/外连接

**内连接：**合并具有同一列的两个以上表的行，结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id, department_name
FROM employees AS e,
     departments AS d
WHERE e.department_id = d.department_id
# 也可以用[inner] join的方式实现
1
2
3
4
5

**外连接：**合并具有同一列的两个以上表的行，结果集中除了包含一个表与另一个表不匹配的行，还查询到了左表或右表中不匹配的行

外连接分类：左外连接、右外连接、满外连接

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
1
2
3

满外连接：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
1
2
3

右上122条

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

左中 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
1
2
3
4

中心 106条

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

右中 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
1
2
3
4

左下 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
1
2
3
4
5
6
7
8

左下 = 右上+左中 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
1
2
3
4
5
6
7
8

右下 = 左中 + 右中 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
1
2
3
4
5
6
7
8
9

UNION/UNION ALL

UNION 操作符返回两个查询结果的结果集的并集，去重重复记录

UNION ALL 操作符返回两个查询结果的并集，对于两个结果集的重复部分，不去重

能用UNION ALL的地方尽量不用UNION，因为去重操作会降低效率

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以上一列或一个值

函数用法：https://blog.csdn.net/qq_38154295/article/details/126416913

聚合函数

作用与一组数据，并对一组数据返回一个值；avg,sum,count,max,min,方差,标准差,中位数

count(*)
count(1) (count常数)
count(具体字段) (不一定对，null会被忽略)
如果需要对表中的数据统计，执行效率跟存储引擎有关：
MyISAM存储引擎，三者效率相同
InnoDB存储引擎，COUNT(*) = COUNT(1) > COUNT(字段)

MIN(AVG())聚合函数在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;
1
2
3
4
5
6

# 各个职位的平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;
1
2
3
4

# 查询各个部门的各个职位的平均工资;分组字段前后顺序可以不同，得到的数据一样
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;
1
2
3
4

GROUP BY中WITH ROLLUP

在分组计算后，将所有结果再进行统计增加一条；不适合参与ORDER BY一起用

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

HAVING

用来过滤数据；操作聚合函数，其他条件放到where中提高效率

# 查询各个部门中最高工资大于1万的部门信息
SELECT department_id, MAX(salary) AS MONEY
FROM employees
GROUP BY department_id
HAVING MONEY > 10000
1
2
3
4
5

子查询

单行子查询

。。。略

多行子查询

也称为集合比较子查询，内查询返回多行，使用多行比较操作符

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实现
1
2
3
4
5
6
7

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实现
1
2
3
4
5
6
7

相关子查询

如果子查询的执行依赖与外部查询，通常情况下都是因为子查询中的表用到了外部数据，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询称之为关联子查询

相关子查询按一行接一行的顺序执行，主查询的每一行都执行一次子查询。

# 查询员工的工资大于本部门平均工资的员工
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)

# 另一种实现方式
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
1
2
3
4
5
6
7
8
9
10
11
12
13

# 查询员工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
1
2
3
4

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

视图

视图是一种虚拟表，本身不具有数据，占用很少的内存空间
视图建立在已有表的基础上，视图赖以建立的这些表称为基表
视图的创建和删除只影响视图本身，不影响对应的基表，但对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
向视图提供数据内容的语句为SELECT语句，可以将视图理解成存储起来的SELECT语句
视图是向用户提供基表数据的另一种表现形式，可以帮我们把经常查询的结果集放到虚拟表中，提升使用效率

使用视图主要做查询操作

创建视图类型

创建视图的类型：创建单表视图、创建多表联合视图、基于视图创建视图

更新视图

MySQL支持使用INSERT、UPDATE、DELETE语句对视图中的数据进行插入、更新和删除操作，当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系，另外当视图定义出现如下情况时，视图不支持更新操作：

在定义视图的时候指定了ALGORITHM = TEMPTABLE，视图将不支持INSERT和DELETE操作
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作
在定义视图的SELECT语句中使用了JOIN联合查询 ，视图将不支持INSERT和DELETE操作
在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值
在定义视图的SELECT语句后的字段列表中使用DISTINCT、 聚合函数 、GROUP BY、HAVING、UNION等，视图将不支持INSERT、UPDATE、DELETE
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE
视图定义基于一个 不可更新视图
常量视图

视图优缺点

• 操作简单
• 减少数据冗余
• 数据安全
• 适应灵活多变的需求
• 能够分解复杂的查询逻辑

缺点：如果实际数据表的结构变更了，需要及时对相关视图进行维护，视图过多、嵌套视图的维护成本高

存储过程和函数

https://blog.csdn.net/Becky_Jia/article/details/108308219

数据库表练习数据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 */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229