第二节数据CRUD操作与连接查询和子查询（包含练习）

最新推荐文章于 2023-09-05 11:28:58 发布

玉米迪迪

最新推荐文章于 2023-09-05 11:28:58 发布

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_46041230/article/details/121774014

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

笔记中的大部分例子都是通过部门和部门员工表中的数据进行的举例，表的相关数据创建放在文末，以便理解笔记中的部分语句。

一、数据CRUD操作

1. 插入数据

1.1 插入一条数据

INSERT INTO 表名 VALUES(val_list);
或
INSERT INTO 表名(col_list) VALUES(val_list);
eg:
INSERT INTO grade VALUES(1,“2019级”);

1.2 插入多条数据

INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3),(val_list4);
eg：
INSERT INTO grade VALUES(2,“2018级”),(3,“2017级”),(4,“2016级”);

2. 备份数据表

2.1 如果表不存在

CREATE TABLE emp_bak AS SELECT * FROM emp;
eg:
CREATE TABLE grade_bak as SELECT * FROM grade;

2.2 如果表存在

CREATE TABLE emp_bak AS SELECT * FROM emp;
eg:
INSERT INTO grade_bak SELECT * FROM grade;

3. 更新数据

UPDATE table_name SET col=val,col=val [WHERE< condition >]
eg:
UPDATE grade SET GradeName = “2019级” where GradeId = 4;

4. 删除数据

DELETE FROM table_name [WHERE ];

注意：
如果删除语句没where,此时会将数据表中的记录全部删除，类似TRUNCATE TABLE.
TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为:

TRUNCATE TABLE table_name。

TRUNCATE直接删除表而不是删除记录，执行速度比DELETE快。而且不能用在有主外键关系的主表
中。

5.查询数据

5.1 查询所有字段

SELECT * FROM 表名;
eg:
SELECT * from grade;

5.2 查询所有字段

SELECT 列名 FROM 表名;
eg：
SELECT gradeName from grade;

5.3 查询多个字段

SELECT 字段名1,字段名2,…,字段名n FROM 表名;
eg:
SELECT gradeId,gradeName from grade;

查询的时候可以起别名

SELECT g.gradeId as 学生编号, g.gradeName AS 年级编号 from grade g;
//注意 如果是给表中的所有字段都改名的话，一定要加逗号！一定要加逗号！一定要加逗号！

5.4 查询指定记录

通过WHERE子句可以对数据进行过滤，语法格式为：

SELECT 字段名1,字段名2,...,字段名n FROM 表名 WHERE 查询条件
//eg:查询所有年级为2019级的学生信息
SELECT * FROM grade WHERE gradeName = "2019级";

5.5 带IN关键字的查询

IN操作符用来查询满足指定范围内的条件的记录。
使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项。
在IN关键字前面加上了NOT关键字，这使得查询的结果与前面一个的结果正好相反。

//查询员工编号为100,109,135的员工信息
SELECT * from employees WHERE employee_id = 100 or employee_id = 109 or employee_id = 135;
SELECT * FROM employees WHERE employee_id IN (100,109,135);
//查询除了编号为100,109,135的其他员工的信息
SELECT * FROM employees WHERE employee_id NOT IN (100,109,135);

5.6 带BETWEEN AND的范围查询

BETWEEN AND用来查询某个范围内的值，该操作符需要两个参数,即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回。
BETWEEN AND操作符前可以加关键字NOT，表示指定范围之外的值，如果字段值不满足指定的范围内的值，则这些记录被返回。

//查询工资在10000-20000之间的员工信息
SELECT * FROM employees WHERE salary BETWEEN 10000 and 20000;
SELECT * FROM employees WHERE salary >= 10000 and salary <= 20000;

5.7 带like的字符匹配查询

sql中的like字符匹配查询是中模糊查询，通常与%和下划线“—”一同使用。
（1）百分号通配符"%"，匹配任意长度的字符，甚至包括零字符
（2）下划线通配符" _ "，一次只能匹配任意一个字符

//查询表中的姓是以e开头的员工
SELECT * FROM employees WHERE first_name LIKE "e%";
//数据库中不区分大小写
//查询员工中姓氏为四个字母且第二个字母为e的员工信息
SELECT * FROM employees WHERE first_name LIKE "_E__";

5.8 查询空值与查询空字符串

在SELECT语句中使用IS NULL子句，可以查询某字段内容为空记录。
NOT NULL，该关键字查找字段不为空的记录。

//查询commission_pct为空的员工
SELECT * FROM employees WHERE commission_pct IS NULL;

//查询commission_pct不为空的员工
SELECT * FROM employees where commission_pct IS NOT NULL;

注意！数据库中的查询空值和查询空字符串是不同的！
判断空字符串，要用 = ‘‘或者<>’’

//查找employees中员工住址为空字符串的数据
SELECT * FROM employees WHERE address = '';
//查找employees中员工住址为非空字符串的数据
SELECT * FROM employees WHERE address ！= '';

5.9 带AND的多条件查询

只有当and左右的两个条件都被满足时数据才会被返回。

//查询部门号为90且工资大于10000的员工
SELECT * FROM employees WHERE department_id = 90 AND salary > 10000;

5.10 带OR的多条件查询

与and相反，只要满足其中一个条件记录就会被返回。OR也可以连接两个甚至多个查询条件，多个条件表达式之间用OR分开。

//查询部门号为90或者100的员工
SELECT * FROM employees WHERE department_id = 90 OR department_id = 100;

5.11 查询结果不重复

在SELECT语句中，可以使用DISTINCT关键字指示MySQL消除重复的记录值。
返回的是去除了重复数据的记录

SELECT DISTINCT first_name,last_name,email,phone_number,job_id,salary,commission_pct,manager_id,"departement_id",hiredate FROM employees;

5.12 对查询结果排序

使用ORDER BY子句对指定的列数据进行排序。desc:倒叙 asc:默认值，升序
（1）单列排序

SELECT * FROM employees ORDER BY employee_id DESC;

（2）多列排序

SELECT * FROM employees ORDER BY employee_id DESC,department_id DESC;

5.13 聚合函数

函数	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

（1）COUNT()函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种：

COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。

（2）SUM()是一个求总和的函数，返回指定列值的总和。 SUM()函数在计算时，忽略列值为NULL的行。
（3）AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
（4）MAX()函数返回指定列中的最大值。

MAX()函数除了用来找出最大的列值或日期值之外，还可以返回任意列中的最大值，包括返回字符类型的最大值。
在对字符类型数据进行比较时，按照字符的ASCII码值大小进行比较。
（5）MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。
这里的演示会在后面的习题中展现

5.14 分组查询

关于此类的查询需要多加练习
GROUP BY关键字通常和集合函数一起使用。
只有出现在group by子句中的列才能加入select查询结果的后面，有可能多列分组

//统计各部门经理的人数
SELECT manager_id,COUNT(1) FROM employees GROUP BY manager_id;
//统计每个部门下每个managerid有多少人
select   manager_id 管理者编号,department_id 部门编号,count(1) 人数 from employees group by manager_id,department_id order by manager_id;// 分组后的结果再次排序

在这里插入图片描述

5.15 使用having过滤分组.

GROUP BY可以和HAVING一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

where和having的区别
where：先筛选出满足条件的记录
having:对分组后的结果再次进行过滤筛选

select … from table where … group by …having …order by … limit ?,?;

//部门人数大于等于个人的部门
SELECT manager_id,COUNT(1) FROM employees GROUP BY manager_id HAVING COUNT(1) >= 8;

5.16LIMIT

返回想要查看的某几行数据。

//查看表的第一二行数据
select * from  employees LIMIT 1,2;
select * from employees limit 2;//等价于0-2

每次看5条记录，前5条记录，第n条应该是什么？ (n - 1) * 5 ,5
这里让我联想到了分页查询╹▽╹

//从第几条数据开始，每次查看几行
//从第0条数据开始，每次查看5行
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5,5;
SELECT * FROM employees LIMIT 10,5;

5.17 多表连接查询

这里是个例子

select * from users; -- 1 2 3 4 
select now();
insert into users values(null,'test01','123456',0,'192.168.137.162',now()),(null,'test02','123456',0,'192.168.137.162',now()),(null,'test03','123456',0,'192.168.137.162',now()),(null,'test04','123456',0,'192.168.137.162',now());
select * from roles; -- 1 2 3
insert into roles values(null,'射手',null),(null,'法师',null),(null,'战士',null);
insert into userroles value(1,1),(1,2),(2,2),(2,3),(3,1),(3,3);
-- 查看每个人拥有的角色信息
select* from userroles;
select u.uid,u.uname,r.rname from users u inner join userroles ur on u.uid=ur.uid inner join roles r on ur.rid=r.rid order by uid;

二、连接查询和子查询

2.1 连接查询

2.1.1 内连接查询 inner join

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。
两张表相关联的数据，两张表中一定会有表示一样信息的字段，通过这两个字段将两个表连接起来，从而可以对两张表中的任何数据都能进行查询

2.1.2 外连接 outer join

左外连接 left join 以左边的表为基准，找到所有表的信息
右外连接 right join 以右边的表为基准，找到所有标的信息
SELECT … FROM table1 INNER | LEFT|RIGHT JOIN table2 on关联条件 WHERE…

eg:
//查询   不加任何条件 导致结果为两张表的笛卡尔积数，为全链接
SELECT * FROM department,employee;
//内连接，找到两个表相关联的记录
SELECT * FROM employee e,department d WHERE d.did = e.dept_id;
//join查询  两个字段名相同的话 需要给表起别名
SELECT * FROM employee INNER JOIN department on department.did = employee.dept_id;
//左连接
SELECT * FROM employee LEFT JOIN department on department.did = employee.dept_id;
//右连接
SELECT * FROM employee RIGHT JOIN department on department.did = employee.dept_id;

2.2 子查询

子查询在练习中具体展示。

2.2.1 any,some

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回 TRUE。

//创建两个表分别插入数据
CREATE TABLE tb1(
	num1 int
);
CREATE TABLE tb2(
	num2 int
);

SELECT * FROM tb1; /* 1 5 13 27*/
SELECT * FROM tb2;/*6 14 11 20*/
/*把tb1中的所有数字与tb2中的所有数字一个个进行比较，只要满足了一个就返回满足了条件的数字*/
SELECT num1 FROM tb1 WHERE num1 > ANY (SELECT num2 FROM tb2);
SELECT num2 FROM tb2 WHERE num2 > ANY (SELECT num1 FROM tb1);

在这里插入图片描述

2.2.2 All

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

-- all，全部
/*一个个作比较且都大于了才返回值*/
SELECT num1 FROM tb1 WHERE num1 > ALL (SELECT num2 FROM tb2);
SELECT num2 FROM tb2 WHERE num2 > ALL (SELECT num1 FROM tb1);

在这里插入图片描述

2.2.3 EXISTS 与 NOT EXISTS

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。NOT EXISTS与之相反。

-- EXISTS 查询，先判断子查询是否返回结果，如果返回的是true,则会执行外部查询；否则会返回表结构。
SELECT * FROM tb2 WHERE num2 < 10; //结果为true
SELECT * FROM tb1 WHERE EXISTS(SELECT * FROM tb2 WHERE num2 < 10); //所以执行外部查询

在这里插入图片描述

2.2.4 unioin

-- union 查询
SELECT * FROM tb1; /* 1 5 13 27 6 14 11*/
SELECT * FROM tb2;/*6 14 11 20*/
/*重复的显示一次*/
SELECT * FROM tb1
union
SELECT * FROM tb2;
/*重复的多次显示，相当于是把数据放在了一起且总个数不变*/
SELECT * FROM tb1
union ALL
SELECT * FROM tb2;

2.2.5 正则查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。
在这里插入图片描述

三、练习

3.1 练习一

在这里插入图片描述

二、数据更新
在商品销售表中添加一列，为总金额
1.会员等级表插入记录
插入数据：
一般用户
金卡用户
银卡用户
钻石用户
删除 “一般用户”

2.用户表插入记录
张三男 1989-2-1 13399999999999 默认地址（等级编号，参照等级表）
李四男 1990-4-1 13899999999999 科技六路（等级编号，参照等级表）
王五男 1983-12-1 13789999999999 鱼化寨（等级编号，参照等级表）
赵六男 1992-10-1 1359999999999 三辛庄（等级编号，参照等级表）
修改赵六的生日为 1982-10-1
3.商品类型表插入记录
日用百货
烟酒
高档礼品

4.商品表插入记录（至少插入五条条记录）
更改某条商品的数量为100，单价为98.8
更改商品的单价，为原有价格+10元
5.商品销售表插入记录（至少插入五条记录）
三、查询数据
1.查询本月销售的商品总数量
2.查询本月有购买记录的顾客信息
3.查询会员的基本信息，包括会员的等级信息
4.查询商品的基本信息，包含该商品属于那种类别
5.查询本月销售数量最多的商品信息
6.按照销售数量从高到低排序
7.查询本月的销售总金额
8.查询本月购买最多的顾客信息

USE exercise;
SHOW TABLES;

-- 会员信息表
CREATE TABLE Vipinfo(
	vipId int PRIMARY KEY,
	vipname VARCHAR(50) NOT NULL,
	vipsex CHAR(3),
	vipbirth date,
	vipphone VARCHAR(50),
	vipaddress VARCHAR(200) DEFAULT "清华软件园",
	viplevel int 
);


-- 会员等级表
CREATE TABLE Viplevel(
	levelid INT PRIMARY KEY,
	levelname VARCHAR(20) NOT NULL UNIQUE,
	leveldesc VARCHAR(50)
);

-- 外键的设置，表中的字段必须是另一个表的主键，同时数据类型需要一致
ALTER TABLE vipinfo ADD CONSTRAINT fk_vipinfo FOREIGN KEY(viplevel) REFERENCES viplevel(levelid);

ALTER TABLE vipinfo DROP FOREIGN KEY fk_vipinfo;

-- 商品类别表
CREATE TABLE Comtype(
	typeid INT PRIMARY KEY,
	typename VARCHAR(20) NOT NULL UNIQUE,
	typedesc VARCHAR(100)
	
);

-- 商品表

CREATE TABLE goods(
	goodsid int PRIMARY KEY,
	goodsname VARCHAR(20) not NULL,
	typeid int ,
	goodsprice double  not NULL,
	goodsnum int not NULL,
	goodsdesc VARCHAR(50)
);

ALTER TABLE goods ADD CONSTRAINT fk_goods_comtype FOREIGN KEY(typeid) REFERENCES comtype(typeid);

ALTER TABLE goods DROP FOREIGN KEY fk_goods_comtype;
-- 商品销售表

CREATE TABLE goodssale(
	saleid int PRIMARY KEY,
	goodsid int,
	vipid int,
	num int not NULL,
	saletime datetime
);

ALTER TABLE goodssale ADD CONSTRAINT fk_goodssale_goods FOREIGN KEY(goodsid) REFERENCES goods(goodsid);

ALTER TABLE goodssale ADD CONSTRAINT fk_goodssale_vipinfo FOREIGN KEY(vipid) REFERENCES vipinfo(vipid);

ALTER TABLE goodssale DROP FOREIGN KEY fk_goodssale_goods;

ALTER TABLE goodssale DROP FOREIGN KEY fk_goodssale_vipinfo;

-- 数据更新
-- 在商品销售中添加一列，总金额
ALTER TABLE goodssale ADD totalmoney VARCHAR(50); 

-- 会员表等级插入记录
INSERT into viplevel(levelid,levelname) VALUES(1,"一般用户"),(2,"金卡用户"),(3,"银卡用户"),(4,"钻石用户");

DELETE FROM viplevel WHERE levelname = "一般用户";

-- 用户表插入记录
INSERT into vipinfo VALUES(1,"张三","男","1989-2-1","138999999999",DEFAULT,3),(2,"李四","男","1990-4-1","138999999999","科技六路",2),(3,"王五","男","1983-12-1","138999999999","鱼化寨",4),(4,"赵六","男","1992-10-1","138999999999","三辛寨",1);

-- 修改赵六的生日为1982-10-1
UPDATE vipinfo SET vipbirth = "1982-10-1" WHERE vipid = 4;

-- 商品类型表插入记录
INSERT INTO comtype(typeid,typename) VALUES(1,"日用百货"),(2,"烟酒"),(3,"高档礼品");

-- 商品表插入记录（五条）
INSERT into goods VALUES (1,"薯片",1,6,50,"乐事薯片"),(2,"牛奶",1,50,30,"特仑苏牛奶一箱"),(3,"延安",2,20,24,"烟"),(4,"青岛啤酒",2,8,24,"酒水"),(5,"冬虫夏草",3,300,10,"养生补品"),(6,"人参",3,500,5,"养生补品");

-- 更改某条商品的数量为100，单价为98.8
UPDATE goods SET goodsprice = 98.8,goodsnum = 100 WHERE goodsid = 1;
-- 更改某条商品的单价，为原有价格+10元
UPDATE goods SET goodsprice = goodsprice + 10 WHERE goodsid = 3;

-- 商品销售表插入记录
INSERT INTO goodssale VALUES(1,1,3,5,NOW(),30);
INSERT INTO goodssale VALUES(2,2,1,2,NOW(),100);
INSERT INTO goodssale VALUES(3,3,4,2,NOW(),40);
INSERT INTO goodssale VALUES(4,4,1,2,NOW(),16);
INSERT INTO goodssale VALUES(5,5,2,1,NOW(),300);
INSERT INTO goodssale VALUES(6,6,1,1,NOW(),500);

-- 查询数据
-- 1.查询本月销售的商品总数量
SELECT SUM(num)"总销量"  FROM goodssale;
-- 2.查询本月有购买记录的顾客信息
SELECT DISTINCT v.* FROM goodssale g,vipinfo v WHERE g.vipid = v.vipId;
-- 3.查询会员的基本信息，包括会员的等级信息
SELECT f.*,v.levelname FROM vipinfo f,viplevel v WHERE f.viplevel = v.levelid;
-- 4.查询商品的基本信息，包含该商品属于那种类别
SELECT g.*,c.typename FROM goods g,comtype c WHERE g.typeid = c.typeid;
-- 5.查询本月销售数量最多的商品信息
SELECT MAX(num) FROM goodssale; 
SELECT * FROM goodssale WHERE num = (SELECT MAX(num) FROM goodssale);
-- 6.按照销售数量从高到低排序
SELECT * FROM goodssale ORDER BY num desc;
-- 7.查询本月销售总额
SELECT SUM(totalmoney)"销售总金额" FROM goodssale;
-- 8.查询本月购买最多的顾客信息

3.2 练习二

创建以下四个数据表
学生表

在这里插入图片描述
科目表

在这里插入图片描述
成绩表

在这里插入图片描述
年级信息表

一、数据修改与删除
1.将地址是西安市的更新为西安科技二路
2.将S1001的email修改为空的字符串
3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
4.将S1001 ，课程编号为 2 的成绩提高 5分
5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
6.经核实 S1004 课程编号为2 的缺考
7.将计算机网络课程删除掉
二、数据查询
1、查询全部一年级的学生信息。
2、查询全部二年级的学生的姓名和电话。
3、查询全部一年级女同学的信息。
4、查询课时超过60的科目信息。
5、查询二年级的科目名称
6、查询二年级男同学的姓名和住址。
7、查询无电子邮件的学生姓名和年级信息。
8、查询出生日期在1992年之后的男学生姓名和年级信息。
9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
10、按照出生日期查询一年级的学生信息。
11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
13、查询课时最多的科目名称及课时。
14、查询年龄最小的学生所在的年级及姓名。
15、查询考试的最低分出现在哪个科目
16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
17、查询年龄超过25周岁的学生信息。
18、查询1月份过生日的学生信息
19、查询今天过生日的学生姓名及所在年级。
20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
21、查询住址为“雁塔区”的学生姓名、电话、住址
22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
23、查询电话中含有以“130”开头的学生姓名，住址和电话。
24、查询姓“赵”的学号、姓名和住址。
25、统计一年级女生的总人数。
26、查询李四总成绩
27、学号为s1003的学生所有课总成绩
28、学号为s1003的学生考试的平均分。
29、查询一年级的科目“Mysql”的最高分、最低分、平均分。
30、查询每个年级的总学时数，并按照升序排列。
31、查询每个参加考试的学员的平均分。（Group by 学号）
32、查询每门课程的平均分，并按照降序排列。（group by 课程）
33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
34、查询一年级的平均年龄。
35、查询每个年级西安地区的学生人数。
36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
37、查询参加考试至少两次考试不及格的学生学号、姓名、不及格次数。
38、查询学生姓名、所属年级名称及联系电话。
39、查询年级编号为1的科目名称、年级名称及学时。
40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
42、查询所有科目的参考信息（某些科目可能还没有被考试过）
43、查询没有被考过的科目信息。

//建表、建库语句
create DATABASE MySchool;
use MySchool;

CREATE TABLE Grade(
	GradeId INT PRIMARY KEY,
	GradeName VARCHAR(50) not null
);

CREATE TABLE Student(
	StudentNo VARCHAR(50) PRIMARY KEY,
	LoginPwd VARCHAR(50),
	STudentName VARCHAR(50),
	Sex CHAR(2),
	GradeId int,
	Phone VARCHAR(255),
	Address VARCHAR(255),
	BornDate date,
	Email VARCHAR(50)

);

CREATE TABLE Subjects(
	SubjectId INT PRIMARY KEY,
	SubjectName VARCHAR(20) NOT NULL,
	ClassHour INT NOT NULL,
	GradeId INT NOT NULL
);

CREATE TABLE Result(
	Id int PRIMARY KEY auto_increment,
	StudentNo VARCHAR(50) NOT NULL,
	SubjectId INT NOT NULL,
	StudentResult int ,
	ExamDate datetime NOT NULL

);

ALTER TABLE Subjects ADD CONSTRAINT fk_subject_grade FOREIGN KEY(GradeId) REFERENCES Grade(GradeId);

ALTER TABLE Student ADD CONSTRAINT fk_student_grade FOREIGN KEY(GradeId) REFERENCES Grade(GradeId);

ALTER TABLE Result ADD CONSTRAINT fk_result_student FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo);

ALTER TABLE Result ADD CONSTRAINT fk_result_subject FOREIGN KEY(SubjectId) REFERENCES Subjects(SubjectId);


INSERT INTO grade VALUES(1,"一年级");
INSERT INTO grade VALUES(2,"二年级"),(3,"三年级"),(4,"四年级");

CREATE TABLE grade_bak as SELECT * FROM grade;


INSERT INTO grade_bak SELECT * FROM grade; 

UPDATE grade SET GradeName = "四年级" where GradeId = 4;

SELECT * from grade;

SELECT gradeName from grade;

SELECT gradeId,gradeName from grade;

SELECT g.gradeId as 学生编号, g.gradeName AS 年级编号 from grade g;

SELECT * FROM grade WHERE gradeName = "四年级";

//插入数据
SELECT * FROM student;
INSERT into student VALUES("s1001","张三","zhagnsan","男",1,"13022225555","宿舍","1994-01-01",NULL),("s1002","李四","lisi","男",1,"13266669999","宿舍","1994-01-01",NULL),("s1003","张丽","123456","女",1,"13099999999","宿舍","1994-01-01",NULL),("s1004","王磊","123456","男",1,"15066668888","西安","1994-01-01",NULL),("s1005","张丹","zhangdan","女",1,"15036998888","宿舍","1994-01-01",NULL),("s1006","李亮","123456","男",1,"15022226669","西安市雁塔区","1994-01-01","liliang@126.com"),("s1007","李丹","123456","女",1,"15036699965","宿舍","1994-01-01","20161201141947@126.com"),("s1008","王亮","123456","男",2,"15022223333","西安市","1994-01-01",NULL),("s1009","赵龙","123456","男",2,"13022229999","西安市","1994-01-01",NULL),("s1010","徐丹","123456","女",2,"15899996666","宿舍","1994-01-01",NULL);

SELECT * FROM subjects;
INSERT into subjects VALUES(1,"MySQL深入",65,1),(2,"C语言面向过程",45,1),(3,"计算机基础原理",70,1),(4,"毛邓概论",61,1),(5,"英语",55,1),(6,"jsp",40,2),(7,"数据结构",60,2),(8,"oracle",65,2),(9,"计算机网络",50,2);
INSERT into subjects VALUES(9,"计算机网络",50,2);

SELECT * FROM result;
INSERT into result VALUES(1,"s1001",1,80,"2015-07-01"),(2,"s1002",1,40,"2015-07-01"),(3,"s1001",2,10,"2015-07-01"),(4,"s1002",2,20,"2015-07-01"),(5,"s1003",1,60,"2015-07-01"),(6,"s1001",3,82,"2015-07-03"),(7,"s1001",4,90,"2015-07-03"),(8,"s1001",5,75,"2015-07-01"),(9,"s1002",3,65,"2015-07-03"),(10,"s1002",4,35,"2015-07-03"),(11,"s1002",5,87,"2015-07-01"),(12,"s1003",2,65,"2015-07-01"),(13,"s1003",3,45,"2015-07-03"),(14,"s1003",4,92,"2015-07-03"),(15,"s1003",5,55,"2015-07-01"),(16,"s1004",1,65,"2015-07-01"),(17,"s1004",2,85,"2015-07-01"),(18,"s1004",3,45,"2015-07-03");


// 数据修改与删除
//1.将地址是西安市的更新为 西安科技二路
UPDATE student SET Address = "西安科技二路" WHERE Address = "西安市";
// 2.将S1001的email修改为空的字符串
UPDATE student SET Email = "" WHERE StudentNo = "s1001";
// 3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
UPDATE subjects SET SubjectName = "java基础",ClassHour = 60,GradeId = 2 WHERE SubjectId = 2;
// 4.将S1001 ，课程编号为 2 的成绩 提高 5分
SELECT * FROM result WHERE StudentNo = "s1001";
UPDATE result SET StudentResult = StudentResult+5 WHERE StudentNo = "s1001" and SubjectId = 2;
// 5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
SELECT * FROM result WHERE StudentNo = "s1004"; 
UPDATE result SET StudentResult = 60,ExamDate = "2015-10-10" WHERE StudentNo = "s1004" and SubjectId = 3;
// 6.经核实 S1004 课程编号为2 的 缺考
UPDATE result SET StudentResult = NULL WHERE StudentNo = "s1004" and SubjectId = 2;
// 7.将计算机网络课程删除掉
DELETE FROM subjects WHERE SubjectName = "计算机网络";
// 数据查询：
// 1、查询全部一年级的学生信息。
SELECT * FROM student WHERE GradeId = (SELECT GradeId FROM grade WHERE GradeName = "一年级");
SELECT GradeId FROM grade WHERE GradeName = "一年级";
// 2、查询全部二年级的学生的姓名和电话。
SELECT STudentName,Phone FROM student WHERE GradeId = (SELECT GradeId FROM grade WHERE GradeName = "二年级");
// 3、查询全部一年级女同学的信息。
SELECT * FROM student WHERE GradeId = (SELECT GradeId FROM grade WHERE GradeName = "一年级") AND Sex = "女";
// 4、查询课时超过60的科目信息。
SELECT * FROM subjects WHERE ClassHour > 60;
// 5、查询二年级的科目名称
SELECT SubjectName FROM subjects WHERE GradeId = (SELECT GradeId FROM grade WHERE GradeName = "二年级");
// 6、查询二年级男同学的姓名和住址。
SELECT STudentName,Address FROM student WHERE Sex = "男" and GradeId = (SELECT GradeId FROM grade WHERE GradeName = "二年级");
// 7、查询无电子邮件的学生姓名和年级信息。
SELECT s.STudentName,g.GradeName FROM student s INNER JOIN grade g ON s.GradeId = g.GradeId WHERE Email is NULL or Email = "";
select s.studentname,s.gradeid,(select g.gradename from grade g where g.gradeid=s.gradeid ) gradename from student s where email is null or email ='';
// 8、查询出生日期在1992年之后的男学生姓名和年级信息。
SELECT s.STudentName,g.GradeName FROM student s INNER JOIN grade g on s.GradeId = g.GradeId WHERE s.BornDate > "1991-12-31";
select s.studentname,s.gradeid,(select g.gradename from grade g where g.gradeid=s.gradeid ) gradename from student s where s.BornDate > "1991-12-31";
// 9、参加了日期为2015年7月3日的“计算机基础原理”科目考试的成绩信息
SELECT Subjectid FROM subjects WHERE SubjectName = "计算机基础原理";
SELECT r.*,s.SubjectName FROM result r INNER JOIN subjects s on r.SubjectId = s.SubjectId WHERE ExamDate = "2015-07-03" AND s.SubjectName = "计算机基础原理";
// 等价
select * from result where ExamDate='2015-07-03' and SubjectId=(select SubjectId from subjects where SubjectName='计算机基础原理');
// 10、按照出生日期查询一年级的学生信息。
SELECT * FROM student WHERE GradeId = (SELECT GradeId FROM grade WHERE GradeName = "一年级") ORDER BY BornDate;
// 11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
SELECT * FROM result WHERE SubjectId = 1 ORDER BY StudentResult DESC;
// 12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
SELECT * FROM result WHERE SubjectId = (SELECT SubjectId FROM subjects WHERE SubjectName = "MySQL深入") AND ExamDate = "2015-07-01" ORDER BY StudentResult DESC LIMIT 2;
// 排序
row_number() over(ORDER BY ...) -- 不管重复，直接排序
rank()over(ORDER BY...) -- 重复的会显示如1 2 2 4 5 6 
dense_rank() over(ORDER BY...) -- 重复会显示 1 2 2 3 4 5 6
PARTITION by 
/*
使用完伪列之后不能直接去用where去筛选，应该加入子查询
*/
select r.* ,rank() over (order by studentresult desc) rn from result r where examdate='2015-7-1' and subjectid=(select subjectid from subjects where subjectname='MySQL深入') ;

select * from (select r.*,rank() over (order by studentresult desc) rn from result r where examdate='2015-7-1' and subjectid=(select subjectid from subjects where subjectname='MySQL深入')) a where a.rn<3;

use db1023;
select employee_id,first_name,salary,
row_number() over (order by salary desc) rownum,
RANK()over (order by salary desc) rownum2,
dense_rank() over (order by salary desc) rownum3
from employees order by salary desc;

select e.employee_id,e.first_name, e.salary, row_number() over (order by salary desc) rn1,
rank() over (order by salary desc) rn2,dense_rank() over (order by salary desc) rn3 from employees e ;

select * from (select e.employee_id,e.first_name, e.salary, row_number() over (order by salary desc) rn1,
rank() over (order by salary desc) rn2,dense_rank() over (order by salary desc) rn3
from employees e ) a where a.rn1<3



select e.employee_id,e.first_name,e.salary,e.department_id ,row_number() over (partition by department_id order by salary desc) rn1 from employees e where department_id is not null;

select * from (select e.employee_id,e.first_name,e.salary,e.department_id ,row_number() over (partition by department_id order by salary desc) rn1 from employees e where department_id is not null) a where a.rn1<=2;


// 查找工资前3的员工信息
select * from (select employee_id,first_name,salary,
row_number() over (order by salary desc) rownum,
RANK()over (order by salary desc) rownum2,
dense_rank() over (order by salary desc) rownum3
from employees order by salary desc) a where a.rownum2<4;

USE myschool;
// 13、查询课时最多的科目名称及课时。
SELECT SubjectName,ClassHour FROM subjects WHERE ClassHour = (SELECT MAX(ClassHour) FROM subjects);
// 等价
select s.*,row_number() over (order by classhour desc) rn from subjects s;
// 
select a.subjectid,a.subjectname,a.classhour from (select s.*,row_number() over (order by classhour desc) rn from subjects s)  a where a.rn=1;
// 14、查询年龄最小的学生所在的年级及姓名。
SELECT st.*,g.GradeName FROM student st INNER JOIN grade g on st.GradeId = g.GradeId WHERE BornDate = (SELECT MAX(BornDate) FROM student);

// rownum的实现?
select s.*,row_number() over (order by borndate desc),
(select g.gradename from grade g where g.gradeid=s.gradeid)
from student s;

select * from (select s.*,row_number() over (order by borndate desc) rn,(select g.gradename from grade g where g.gradeid=s.gradeid) from student s) a where a.rn=1;
// 15、查询考试的最低分出现在哪个科目
SELECT SubjectName FROM result r INNER JOIN subjects s on r.SubjectId = s.SubjectId WHERE StudentResult = (SELECT MIN(StudentResult)  FROM result);
//
select min(studentresult) from result;
select subjectid from result where studentresult = (select min(studentresult) from result);

select * from subjects where subjectid in (select subjectid from result where studentresult = (select min(studentresult) from result));
// row number能否实现？
select r.* ,row_number() over (order by studentresult) rn from result r;

select * from subjects where subjectid in (select a.subjectid from (select r.* ,row_number() over (order by studentresult) rn from result r) a where a.rn=1);
// 16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
SELECT * FROM result WHERE StudentNo = "s1001" ORDER BY ExamDate ASC;
// 17、查询年龄超过25周岁的学生信息。

// 18、查询1月份过生日的学生信息

// 19、查询今天过生日的学生姓名及所在年级。

// 20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com

// 21、查询住址为“西安市雁塔区”的学生姓名、电话、住址
SELECT STudentName,Phone,Address FROM student WHERE Address = "西安市雁塔区";
// 22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
SELECT s.*,(SELECT GradeName FROM grade g WHERE g.GradeId = s.GradeId)GradeName FROM subjects s WHERE SubjectName LIKE "%计算机%"  ORDER BY GradeId ;
// 23、查询电话中含有以“130”开头的学生姓名，住址和电话。
SELECT STudentName,Address,Phone FROM student WHERE Phone LIKE "130%";
// 24、查询姓“赵”的学号、姓名和住址。
SELECT StudentNo,STudentName,Address FROM student WHERE STudentName LIKE "赵%";
// 25、统计一年级女生的总人数。
SELECT COUNT(1)"一年级女生总人数" FROM student WHERE Sex = "女" AND GradeId = (SELECT GradeId FROM grade WHERE GradeName = "一年级");
// 等价
SELECT GradeId,sex,COUNT(1),(select gradename from grade g where g.gradeid=s.gradeid)gradename FROM student s GROUP BY GradeId,Sex HAVING Sex = "女" AND GradeId = (SELECT GradeId FROM grade WHERE GradeName = "一年级");
// 26、查询李四总成绩
SELECT SUM(StudentResult) FROM result WHERE StudentNo = (SELECT StudentNo FROM student WHERE STudentName = "李四");
// 等价
select studentno,'李四' as '姓名',sum(studentresult) from result where studentno in (select studentno from student s where s.studentname='李四') group by studentno;
// 加入常量值

// 27、学号为s1003的学生所有课总成绩
SELECT SUM(StudentResult) FROM result WHERE StudentNo = "s1003";
// 28、学号为s1003的学生考试的平均分。
SELECT AVG(StudentResult) FROM result WHERE StudentNo = "s1003";
// 29、查询一年级的科目“Mysql”的最高分、最低分、平均分。
SELECT MAX(StudentResult)"最高分",MIN(StudentResult)"最低分",AVG(StudentResult)"平均分" FROM result WHERE SubjectId = (SELECT SubjectId FROM subjects WHERE SubjectName = "MySQL深入") ;
// 等价
select subjectid,max(studentresult),min(studentresult),avg(studentresult) from result where subjectid in (select subjectid from subjects where subjectname like  '%MySQL%' and gradeid=(select gradeid from grade where gradename='一年级')) group by subjectid;
// 30、查询每个年级的总学时数，并按照升序排列。
SELECT GradeId, SUM(ClassHour) FROM subjects GROUP BY GradeId ORDER BY SUM(ClassHour) asc;

// 31、查询每个参加考试的学员的平均分。（Group by 学号）
SELECT StudentNo, AVG(StudentResult)"平均分" FROM result GROUP BY StudentNo;
// 32、查询每门课程的平均分，并按照降序排列。（group by 课程）
SELECT SubjectId,AVG(StudentResult) FROM result GROUP BY SubjectId ORDER BY AVG(StudentResult) DESC;
// 33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
SELECT StudentNo, SUM(StudentResult) FROM result GROUP BY StudentNo ORDER BY SUM(StudentResult) DESC;
// 34、查询一年级的平均年龄。

// 35、查询每个年级西安地区的学生人数。
SELECT GradeId, Address, COUNT(1) FROM student WHERE Address LIKE "%西安%" GROUP  BY GradeId,Address;
// 36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
SELECT StudentNo, AVG(StudentResult) FROM result  GROUP BY StudentNo HAVING AVG(StudentResult)>=60 ORDER BY AVG(StudentResult) DESC;
// 37、查询参加考试至少两次考试不及格的学生学号、姓名、不及格次数。
select studentno,(select s.studentname from student s where s.studentno=r.studentno ) stuname ,count(1) from result r where studentresult<60 group by studentno having count(1)>1;
// 38、查询学生姓名、所属年级名称及联系电话。
select s.studentname, (select g.gradename from grade g where g.gradeid=s.gradeid) ,s.phone from student s;
// 39、查询年级编号为1的科目名称、年级名称及学时。
select s.subjectname, (select g.gradename from grade g where g.gradeid=s.gradeid) , s.classhour from subjects s where gradeid=1;
// 40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select  ( select s.studentname from student s where s.studentno=r.studentno ), r.studentresult,r.examdate from result r where subjectid=1;
// 41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
select * from result;
select  (select s.subjectname from subjects s where  s.subjectid=r.subjectid ) ,r.studentresult,r.examdate from result r where studentno='s1001';
// 42、查询所有科目的参考信息（某些科目可能还没有被考试过）
select r.*,(select subjectname from subjects s where s.subjectid=r.subjectid ) from result r;
// 43、查询没有被考过的科目信息。
select distinct subjectid from result;// 考过的科目号
select * from subjects where subjectid not in (select distinct subjectid from result);

一个包含许多数据的数据库和表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `department_id` int(4) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(3) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `manager_id` int(6) NULL DEFAULT NULL,
  `location_id` int(4) NULL DEFAULT NULL,
  PRIMARY KEY (`department_id`) USING BTREE,
  INDEX `loc_id_fk`(`location_id`) USING BTREE,
  CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 281 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (10, 'Adm', 200, 1700);
INSERT INTO `departments` VALUES (20, 'Mar', 201, 1800);
INSERT INTO `departments` VALUES (30, 'Pur', 114, 1700);
INSERT INTO `departments` VALUES (40, 'Hum', 203, 2400);
INSERT INTO `departments` VALUES (50, 'Shi', 121, 1500);
INSERT INTO `departments` VALUES (60, 'IT', 103, 1400);
INSERT INTO `departments` VALUES (70, 'Pub', 204, 2700);
INSERT INTO `departments` VALUES (80, 'Sal', 145, 2500);
INSERT INTO `departments` VALUES (90, 'Exe', 100, 1700);
INSERT INTO `departments` VALUES (100, 'Fin', 108, 1700);
INSERT INTO `departments` VALUES (110, 'Acc', 205, 1700);
INSERT INTO `departments` VALUES (120, 'Tre', NULL, 1700);
INSERT INTO `departments` VALUES (130, 'Cor', NULL, 1700);
INSERT INTO `departments` VALUES (140, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (150, 'Sha', NULL, 1700);
INSERT INTO `departments` VALUES (160, 'Ben', NULL, 1700);
INSERT INTO `departments` VALUES (170, 'Man', NULL, 1700);
INSERT INTO `departments` VALUES (180, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (190, 'Con', NULL, 1700);
INSERT INTO `departments` VALUES (200, 'Ope', NULL, 1700);
INSERT INTO `departments` VALUES (210, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (220, 'NOC', NULL, 1700);
INSERT INTO `departments` VALUES (230, 'IT ', NULL, 1700);
INSERT INTO `departments` VALUES (240, 'Gov', NULL, 1700);
INSERT INTO `departments` VALUES (250, 'Ret', NULL, 1700);
INSERT INTO `departments` VALUES (260, 'Rec', NULL, 1700);
INSERT INTO `departments` VALUES (270, 'Pay', NULL, 1700);
INSERT INTO `departments` VALUES (280, '测试部', NULL, 1700);

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `employee_id` int(6) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `last_name` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `email` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `phone_number` varchar(20) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `salary` double(10, 2) NULL DEFAULT NULL,
  `commission_pct` double(4, 2) NULL DEFAULT NULL,
  `manager_id` int(6) NULL DEFAULT NULL,
  `department_id` int(4) NULL DEFAULT NULL,
  `hiredate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`employee_id`) USING BTREE,
  INDEX `dept_id_fk`(`department_id`) USING BTREE,
  INDEX `job_id_fk`(`job_id`) USING BTREE,
  CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1003 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

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

-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs`  (
  `job_id` varchar(10) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NOT NULL,
  `job_title` varchar(35) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `min_salary` int(6) NULL DEFAULT NULL,
  `max_salary` int(6) NULL DEFAULT NULL,
  PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

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

-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations`  (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `street_address` varchar(40) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `postal_code` varchar(12) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `city` varchar(30) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `state_province` varchar(25) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  `country_id` varchar(2) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL,
  PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3201 CHARACTER SET = gb2312 COLLATE = gb2312_chinese_ci ROW_FORMAT = Dynamic;

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

-- ----------------------------
-- View structure for view_emp
-- ----------------------------

DROP VIEW IF EXISTS `view_emp`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_emp` AS select `employees`.`employee_id` AS `employee_id`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name`,`employees`.`email` AS `email`,`employees`.`salary` AS `salary`,`departments`.`department_name` AS `department_name` from (`employees` join `departments`) where (`employees`.`department_id` = `departments`.`department_id`);

-- ----------------------------
-- View structure for view_emp02
-- ----------------------------
DROP VIEW IF EXISTS `view_emp02`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_emp02` AS select `employees`.`employee_id` AS `employee_id`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name` from `employees`;

SET FOREIGN_KEY_CHECKS = 1;


CREATE TABLE job_grade(
	job_gradeid int PRIMARY KEY auto_increment,
	lowest_sal int ,
	highest_sal int,
	job_level VARCHAR(50)
);

INSERT INTO job_grade VALUES(NULL,1000,5000,"一等级");
INSERT INTO job_grade VALUES(NULL,5000,10000,"二等级");
INSERT INTO job_grade VALUES(NULL,10000,15000,"三等级");
INSERT INTO job_grade VALUES(NULL,15000,20000,"四等级");
INSERT INTO job_grade VALUES(NULL,20000,25000,"五等级");
INSERT INTO job_grade VALUES(NULL,30000,35000,"六等级");
INSERT INTO job_grade VALUES(NULL,40000,45000,"七等级");
INSERT INTO job_grade VALUES(NULL,50000,55000,"八等级");

玉米迪迪

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第二节数据CRUD操作与连接查询和子查询（包含练习）

一、数据CRUD操作1. 插入数据1.1 插入一条数据INSERT INTO 表名 VALUES(val_list);或INSERT INTO 表名(col_list) VALUES(val_list);eg:INSERT INTO grade VALUES(1,“2019级”);1.2 插入多条数据INSERT INTO 表名(col_list) VALUES(val_list1),(val_list2),(val_list3),(val_list4);eg：INSERT IN
复制链接

扫一扫