就业培训学习记录-day006
课堂任务
安装MySQL
MySQL官网下载地址
Mysql的详细安装教程！亲测可用！！！
进入MySQL
安装完毕之后按下快捷键win+r，输入cmd，打开命令提示符。输入mysql -uroot -p回车，看一下是否提示Enter password。如果不提示输入密码，提示‘mysql’不是内部或外部命令，也不是可运行的程序或其处理文件，则先看下一点。如果提示了就输入安装时设置的密码，出现类似下图的界面就是连接成功了。
这里我安装的版本是8.0.22的，上面提供的版本是5.5.27的，自己也可以根据自己的需要和实际情况下载其他版本。
可能出现的问题
在命令提示符窗口，提示‘mysql’不是内部或外部命令，也不是可运行的程序或其处理文件。这一般是没有正确设置环境变量导致的，解决方案如下：
快捷键win+r，输入sysdm.cpl，在打开的窗口中选择【高级】【环境变量】。在下面的系统变量中找到Path，然后【编辑】。在编辑环境变量的窗口中【新建】，然后写上MySQL安装目录里bin目录的路径。如果不知道MySQL安装目录，可以在开始菜单里找到MySQL相关的程序，然后右键【打开文件所在的位置】。写好路径之后，依次点保存直到所有窗口关闭，然后再去尝试在命令提示符中连接MySQL。这里要提一下，要重新开一个cmd窗口，用的才是新的环境变量，旧cmd窗口用的环境变量是旧的。
Navicat for MySQL
学习MySQL就不用命令行了，我们用一个数据库可视化工具来操作MySQL。
下载链接: https://pan.baidu.com/s/1n5h5jHZvxpfBzbCwUvOIuw
提取码: dyvh
解压之后直接运行【navicat.exe】
添加好连接之后，在左边找到刚刚写的连接名，双击打开，如果能看到里面的数据库，连接部分就完成了。
准备工作
新建一个数据库用作练习，名称随意，字符集选utf-8，排序规则选general_ci。
打开新建的数据库（这里我用026数据库演示），新建查询，然后把下面这个文件的内容复制粘贴到查询窗口中，运行。运行之后能看到employees表就可以了。后续的练习也是新建一个查询，然后在里面写语句执行即可。
点击查看详细内容
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50527
Source Host : 127.0.0.1:3306
Source Database : myemployees
Target Server Type : MYSQL
Target Server Version : 50527
File Encoding : 65001
Date: 2021-05-09 09:04:38
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Records of admin
-- ----------------------------
INSERT INTO `admin` VALUES ('1', 'jack1', '0000');
INSERT INTO `admin` VALUES ('2', 'jack2', '0000');
INSERT INTO `admin` VALUES ('3', 'jack3', '0000');
INSERT INTO `admin` VALUES ('7', 'rose1', '1231');
INSERT INTO `admin` VALUES ('8', 'rose2', '1232');
INSERT INTO `admin` VALUES ('9', 'rose3', '1233');
INSERT INTO `admin` VALUES ('10', 'rose4', '1234');
INSERT INTO `admin` VALUES ('11', 'rose5', '1235');
INSERT INTO `admin` VALUES ('12', 'rose6', '1236');
INSERT INTO `admin` VALUES ('13', 'rose7', '1237');
INSERT INTO `admin` VALUES ('14', 'rose8', '1238');
INSERT INTO `admin` VALUES ('15', 'rose9', '1239');
INSERT INTO `admin` VALUES ('16', 'rose10', '12310');
INSERT INTO `admin` VALUES ('17', 'rose11', '12311');
INSERT INTO `admin` VALUES ('18', 'rose12', '12312');
INSERT INTO `admin` VALUES ('19', 'rose13', '12313');
INSERT INTO `admin` VALUES ('20', 'rose14', '12314');
INSERT INTO `admin` VALUES ('21', 'rose15', '12315');
INSERT INTO `admin` VALUES ('22', 'rose16', '12316');
INSERT INTO `admin` VALUES ('23', 'rose17', '12317');
INSERT INTO `admin` VALUES ('24', 'rose18', '12318');
INSERT INTO `admin` VALUES ('25', 'rose19', '12319');
INSERT INTO `admin` VALUES ('26', 'rose20', '12320');
INSERT INTO `admin` VALUES ('27', 'rose21', '12321');
INSERT INTO `admin` VALUES ('28', 'rose22', '12322');
INSERT INTO `admin` VALUES ('29', 'rose23', '12323');
INSERT INTO `admin` VALUES ('30', 'rose24', '12324');
INSERT INTO `admin` VALUES ('31', 'rose25', '12325');
INSERT INTO `admin` VALUES ('32', 'rose26', '12326');
INSERT INTO `admin` VALUES ('33', 'rose27', '12327');
INSERT INTO `admin` VALUES ('34', 'rose28', '12328');
INSERT INTO `admin` VALUES ('35', 'rose29', '12329');
INSERT INTO `admin` VALUES ('36', 'rose30', '12330');
INSERT INTO `admin` VALUES ('37', 'rose31', '12331');
INSERT INTO `admin` VALUES ('38', 'rose32', '12332');
INSERT INTO `admin` VALUES ('39', 'rose33', '12333');
INSERT INTO `admin` VALUES ('40', 'rose34', '12334');
INSERT INTO `admin` VALUES ('41', 'rose35', '12335');
INSERT INTO `admin` VALUES ('42', 'rose36', '12336');
INSERT INTO `admin` VALUES ('43', 'rose37', '12337');
INSERT INTO `admin` VALUES ('44', 'rose38', '12338');
INSERT INTO `admin` VALUES ('45', 'rose39', '12339');
INSERT INTO `admin` VALUES ('46', 'rose40', '12340');
INSERT INTO `admin` VALUES ('47', 'rose41', '12341');
INSERT INTO `admin` VALUES ('48', 'rose42', '12342');
INSERT INTO `admin` VALUES ('49', 'rose43', '12343');
INSERT INTO `admin` VALUES ('50', 'rose44', '12344');
INSERT INTO `admin` VALUES ('51', 'rose45', '12345');
INSERT INTO `admin` VALUES ('52', 'rose46', '12346');
INSERT INTO `admin` VALUES ('53', 'rose47', '12347');
INSERT INTO `admin` VALUES ('54', 'rose48', '12348');
INSERT INTO `admin` VALUES ('55', 'rose49', '12349');
INSERT INTO `admin` VALUES ('56', 'rose50', '12350');
INSERT INTO `admin` VALUES ('57', 'rose51', '12351');
INSERT INTO `admin` VALUES ('58', 'rose52', '12352');
INSERT INTO `admin` VALUES ('59', 'rose53', '12353');
INSERT INTO `admin` VALUES ('60', 'rose54', '12354');
INSERT INTO `admin` VALUES ('61', 'rose55', '12355');
INSERT INTO `admin` VALUES ('62', 'rose56', '12356');
INSERT INTO `admin` VALUES ('63', 'rose57', '12357');
INSERT INTO `admin` VALUES ('64', 'rose58', '12358');
INSERT INTO `admin` VALUES ('65', 'rose59', '12359');
INSERT INTO `admin` VALUES ('66', 'rose60', '12360');
INSERT INTO `admin` VALUES ('67', 'rose61', '12361');
INSERT INTO `admin` VALUES ('68', 'rose62', '12362');
INSERT INTO `admin` VALUES ('69', 'rose63', '12363');
INSERT INTO `admin` VALUES ('70', 'rose64', '12364');
INSERT INTO `admin` VALUES ('71', 'rose65', '12365');
INSERT INTO `admin` VALUES ('72', 'rose66', '12366');
INSERT INTO `admin` VALUES ('73', 'rose67', '12367');
INSERT INTO `admin` VALUES ('74', 'rose68', '12368');
INSERT INTO `admin` VALUES ('75', 'rose69', '12369');
INSERT INTO `admin` VALUES ('76', 'rose70', '12370');
INSERT INTO `admin` VALUES ('77', 'rose71', '12371');
INSERT INTO `admin` VALUES ('78', 'rose72', '12372');
INSERT INTO `admin` VALUES ('79', 'rose73', '12373');
INSERT INTO `admin` VALUES ('80', 'rose74', '12374');
INSERT INTO `admin` VALUES ('81', 'rose75', '12375');
INSERT INTO `admin` VALUES ('82', 'rose76', '12376');
INSERT INTO `admin` VALUES ('83', 'rose77', '12377');
INSERT INTO `admin` VALUES ('84', 'rose78', '12378');
INSERT INTO `admin` VALUES ('85', 'rose79', '12379');
INSERT INTO `admin` VALUES ('86', 'rose80', '12380');
INSERT INTO `admin` VALUES ('87', 'rose81', '12381');
INSERT INTO `admin` VALUES ('88', 'rose82', '12382');
INSERT INTO `admin` VALUES ('89', 'rose83', '12383');
INSERT INTO `admin` VALUES ('90', 'rose84', '12384');
INSERT INTO `admin` VALUES ('91', 'rose85', '12385');
INSERT INTO `admin` VALUES ('92', 'rose86', '12386');
INSERT INTO `admin` VALUES ('93', 'rose87', '12387');
INSERT INTO `admin` VALUES ('94', 'rose88', '12388');
INSERT INTO `admin` VALUES ('95', 'rose89', '12389');
INSERT INTO `admin` VALUES ('96', 'rose90', '12390');
INSERT INTO `admin` VALUES ('97', 'rose91', '12391');
INSERT INTO `admin` VALUES ('98', 'rose92', '12392');
INSERT INTO `admin` VALUES ('99', 'rose93', '12393');
INSERT INTO `admin` VALUES ('100', 'rose94', '12394');
INSERT INTO `admin` VALUES ('101', 'rose95', '12395');
INSERT INTO `admin` VALUES ('102', 'rose96', '12396');
INSERT INTO `admin` VALUES ('103', 'rose97', '12397');
INSERT INTO `admin` VALUES ('104', 'rose98', '12398');
INSERT INTO `admin` VALUES ('105', 'rose99', '12399');
INSERT INTO `admin` VALUES ('106', 'rose100', '123100');
INSERT INTO `admin` VALUES ('107', 'tom1', '1231');
INSERT INTO `admin` VALUES ('108', 'tom2', '1232');
INSERT INTO `admin` VALUES ('109', 'tom3', '1233');
INSERT INTO `admin` VALUES ('110', 'tom4', '1234');
INSERT INTO `admin` VALUES ('111', 'tom5', '1235');
INSERT INTO `admin` VALUES ('112', 'tom6', '1236');
INSERT INTO `admin` VALUES ('113', 'tom7', '1237');
INSERT INTO `admin` VALUES ('114', 'tom8', '1238');
INSERT INTO `admin` VALUES ('115', 'tom9', '1239');
INSERT INTO `admin` VALUES ('116', 'tom10', '12310');
INSERT INTO `admin` VALUES ('117', 'tom11', '12311');
INSERT INTO `admin` VALUES ('118', 'tom12', '12312');
INSERT INTO `admin` VALUES ('119', 'tom13', '12313');
INSERT INTO `admin` VALUES ('120', 'tom14', '12314');
INSERT INTO `admin` VALUES ('121', 'tom15', '12315');
INSERT INTO `admin` VALUES ('122', 'tom16', '12316');
INSERT INTO `admin` VALUES ('123', 'tom17', '12317');
INSERT INTO `admin` VALUES ('124', 'tom18', '12318');
INSERT INTO `admin` VALUES ('125', 'tom19', '12319');
INSERT INTO `admin` VALUES ('126', 'tom20', '12320');
INSERT INTO `admin` VALUES ('127', 'laowang2', '1232');
INSERT INTO `admin` VALUES ('128', 'laowang4', '1234');
INSERT INTO `admin` VALUES ('129', 'laowang6', '1236');
INSERT INTO `admin` VALUES ('130', 'laowang8', '1238');
INSERT INTO `admin` VALUES ('131', 'laowang10', '12310');
INSERT INTO `admin` VALUES ('132', 'laowang12', '12312');
INSERT INTO `admin` VALUES ('133', 'laowang14', '12314');
INSERT INTO `admin` VALUES ('134', 'laowang16', '12316');
INSERT INTO `admin` VALUES ('135', 'laowang18', '12318');
INSERT INTO `admin` VALUES ('136', 'laowang20', '12320');
INSERT INTO `admin` VALUES ('137', 'laowang22', '12322');
INSERT INTO `admin` VALUES ('138', 'laowang24', '12324');
INSERT INTO `admin` VALUES ('139', 'laowang26', '12326');
INSERT INTO `admin` VALUES ('140', 'laowang28', '12328');
INSERT INTO `admin` VALUES ('141', 'laowang30', '12330');
INSERT INTO `admin` VALUES ('142', 'laowang32', '12332');
INSERT INTO `admin` VALUES ('143', 'laowang34', '12334');
INSERT INTO `admin` VALUES ('144', 'laowang36', '12336');
INSERT INTO `admin` VALUES ('145', 'laowang38', '12338');
INSERT INTO `admin` VALUES ('146', 'laowang40', '12340');
INSERT INTO `admin` VALUES ('147', 'laowang42', '12342');
INSERT INTO `admin` VALUES ('148', 'laowang44', '12344');
INSERT INTO `admin` VALUES ('149', 'laowang46', '12346');
INSERT INTO `admin` VALUES ('150', 'laowang48', '12348');
INSERT INTO `admin` VALUES ('151', 'laowang50', '12350');
INSERT INTO `admin` VALUES ('152', 'laowang52', '12352');
INSERT INTO `admin` VALUES ('153', 'laowang54', '12354');
INSERT INTO `admin` VALUES ('154', 'laowang56', '12356');
INSERT INTO `admin` VALUES ('155', 'laowang58', '12358');
INSERT INTO `admin` VALUES ('156', 'laowang60', '12360');
INSERT INTO `admin` VALUES ('157', 'laowang62', '12362');
INSERT INTO `admin` VALUES ('158', 'laowang64', '12364');
INSERT INTO `admin` VALUES ('159', 'laowang66', '12366');
INSERT INTO `admin` VALUES ('160', 'laowang68', '12368');
INSERT INTO `admin` VALUES ('161', 'laowang70', '12370');
INSERT INTO `admin` VALUES ('162', 'laowang72', '12372');
INSERT INTO `admin` VALUES ('163', 'laowang74', '12374');
INSERT INTO `admin` VALUES ('164', 'laowang76', '12376');
INSERT INTO `admin` VALUES ('165', 'laowang78', '12378');
INSERT INTO `admin` VALUES ('166', 'laowang80', '12380');
INSERT INTO `admin` VALUES ('167', 'laowang82', '12382');
INSERT INTO `admin` VALUES ('168', 'laowang84', '12384');
INSERT INTO `admin` VALUES ('169', 'laowang86', '12386');
INSERT INTO `admin` VALUES ('170', 'laowang88', '12388');
INSERT INTO `admin` VALUES ('171', 'laowang90', '12390');
INSERT INTO `admin` VALUES ('172', 'laowang92', '12392');
INSERT INTO `admin` VALUES ('173', 'laowang94', '12394');
INSERT INTO `admin` VALUES ('174', 'laowang96', '12396');
INSERT INTO `admin` VALUES ('175', 'laowang98', '12398');
INSERT INTO `admin` VALUES ('176', 'laowang100', '123100');
-- ----------------------------
-- Table structure for city_innodb
-- ----------------------------
DROP TABLE IF EXISTS `city_innodb`;
CREATE TABLE `city_innodb` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`),
KEY `idx_fk_country_id` (`country_id`),
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of city_innodb
-- ----------------------------
INSERT INTO `city_innodb` VALUES ('1', 'Xian', '100');
INSERT INTO `city_innodb` VALUES ('2', 'NewYork', '2');
INSERT INTO `city_innodb` VALUES ('3', 'BeiJing', '100');
-- ----------------------------
-- Table structure for country_innodb
-- ----------------------------
DROP TABLE IF EXISTS `country_innodb`;
CREATE TABLE `country_innodb` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of country_innodb
-- ----------------------------
INSERT INTO `country_innodb` VALUES ('2', 'America');
INSERT INTO `country_innodb` VALUES ('3', 'Japan');
INSERT INTO `country_innodb` VALUES ('100', 'China');
-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`department_name` varchar(3) DEFAULT NULL COMMENT '部门名称',
`manager_id` int(6) DEFAULT NULL COMMENT '部门领导的员工编号',
`location_id` int(4) DEFAULT NULL COMMENT '位置编号',
PRIMARY KEY (`department_id`),
KEY `loc_id_fk` (`location_id`),
CONSTRAINT `loc_id_fk` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- 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');
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
`first_name` varchar(20) DEFAULT NULL COMMENT '名',
`last_name` varchar(25) DEFAULT NULL COMMENT '姓',
`email` varchar(25) DEFAULT NULL COMMENT '邮箱',
`phone_number` varchar(20) DEFAULT NULL COMMENT '电话号码',
`job_id` varchar(10) DEFAULT NULL COMMENT '工种编号',
`salary` double(10,2) DEFAULT NULL COMMENT '月薪',
`commission_pct` double(4,2) DEFAULT NULL COMMENT '奖金率',
`manager_id` int(6) DEFAULT NULL COMMENT '上级编号',
`department_id` int(4) DEFAULT NULL COMMENT '部门编号',
`hiredate` datetime DEFAULT NULL COMMENT '入职日期',
PRIMARY KEY (`employee_id`),
KEY `dept_id_fk` (`department_id`),
KEY `job_id_fk` (`job_id`),
CONSTRAINT `dept_id_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
CONSTRAINT `job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2223 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- 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 ('2222', 'rose', null, null, null, null, '4000.00', null, null, null, null);
-- ----------------------------
-- Table structure for emp_logs
-- ----------------------------
DROP TABLE IF EXISTS `emp_logs`;
CREATE TABLE `emp_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`operation` varchar(20) NOT NULL COMMENT '操作类型 insert update delete',
`operate_time` datetime NOT NULL COMMENT '操作时间',
`operate_id` int(11) NOT NULL COMMENT '操作表的id',
`operate_parame` varchar(500) DEFAULT NULL COMMENT '操作参数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp_logs
-- ----------------------------
INSERT INTO `emp_logs` VALUES ('1', 'insert', '2021-01-04 10:11:39', '2222', '插入后(id:2222,name:rose,salary:4000.00)');
-- ----------------------------
-- Table structure for goods_innodb
-- ----------------------------
DROP TABLE IF EXISTS `goods_innodb`;
CREATE TABLE `goods_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of goods_innodb
-- ----------------------------
INSERT INTO `goods_innodb` VALUES ('1', 'jack');
INSERT INTO `goods_innodb` VALUES ('2', 'rose');
-- ----------------------------
-- Table structure for goods_myisam
-- ----------------------------
DROP TABLE IF EXISTS `goods_myisam`;
CREATE TABLE `goods_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of goods_myisam
-- ----------------------------
INSERT INTO `goods_myisam` VALUES ('1', 'jack');
-- ----------------------------
-- Table structure for jobs
-- ----------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL COMMENT '工种编号',
`job_title` varchar(35) DEFAULT NULL COMMENT '工种名称',
`min_salary` int(6) DEFAULT NULL COMMENT '最低工资',
`max_salary` int(6) DEFAULT NULL COMMENT '最高工资',
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
-- ----------------------------
-- 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 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=gb2312;
-- ----------------------------
-- Records of job_grades
-- ----------------------------
INSERT INTO `job_grades` VALUES ('A', '1000', '2999');
INSERT INTO `job_grades` VALUES ('B', '3000', '5999');
INSERT INTO `job_grades` VALUES ('C', '6000', '9999');
INSERT INTO `job_grades` VALUES ('D', '10000', '14999');
INSERT INTO `job_grades` VALUES ('E', '15000', '24999');
INSERT INTO `job_grades` VALUES ('F', '25000', '40000');
-- ----------------------------
-- Table structure for locations
-- ----------------------------
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '位置编号',
`street_address` varchar(40) DEFAULT NULL COMMENT '街道',
`postal_code` varchar(12) DEFAULT NULL COMMENT '邮编',
`city` varchar(30) DEFAULT NULL COMMENT '城市',
`state_province` varchar(25) DEFAULT NULL COMMENT '州/省',
`country_id` varchar(2) DEFAULT NULL COMMENT '国家编号',
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- 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');
-- ----------------------------
-- Table structure for stringcontent
-- ----------------------------
DROP TABLE IF EXISTS `stringcontent`;
CREATE TABLE `stringcontent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(120) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=gb2312;
-- ----------------------------
-- Records of stringcontent
-- ----------------------------
INSERT INTO `stringcontent` VALUES ('1', 'stuvwxyz');
INSERT INTO `stringcontent` VALUES ('2', 'wx');
INSERT INTO `stringcontent` VALUES ('3', 'yz');
INSERT INTO `stringcontent` VALUES ('4', 'ab');
INSERT INTO `stringcontent` VALUES ('5', 'yz');
INSERT INTO `stringcontent` VALUES ('6', 'z');
INSERT INTO `stringcontent` VALUES ('7', 'v');
INSERT INTO `stringcontent` VALUES ('8', 'hijk');
INSERT INTO `stringcontent` VALUES ('9', 'tuv');
INSERT INTO `stringcontent` VALUES ('10', 'nop');
INSERT INTO `stringcontent` VALUES ('11', 'nopqrstuv');
INSERT INTO `stringcontent` VALUES ('12', 'bcdefghijk');
INSERT INTO `stringcontent` VALUES ('13', 'ijk');
INSERT INTO `stringcontent` VALUES ('14', 'rstuvw');
INSERT INTO `stringcontent` VALUES ('15', 'xyz');
INSERT INTO `stringcontent` VALUES ('16', 'ab');
INSERT INTO `stringcontent` VALUES ('17', 'gh');
INSERT INTO `stringcontent` VALUES ('18', 'xyz');
INSERT INTO `stringcontent` VALUES ('19', 'x');
INSERT INTO `stringcontent` VALUES ('20', 'a');
INSERT INTO `stringcontent` VALUES ('21', 'yz');
INSERT INTO `stringcontent` VALUES ('22', 'qr');
INSERT INTO `stringcontent` VALUES ('23', 'uvwxyz');
INSERT INTO `stringcontent` VALUES ('24', 'cd');
INSERT INTO `stringcontent` VALUES ('25', 'pqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('26', 'klm');
INSERT INTO `stringcontent` VALUES ('27', 'rstuvwx');
INSERT INTO `stringcontent` VALUES ('28', 'ijk');
INSERT INTO `stringcontent` VALUES ('29', 'tuvwxyz');
INSERT INTO `stringcontent` VALUES ('30', 'efgh');
INSERT INTO `stringcontent` VALUES ('31', 'vwxyz');
INSERT INTO `stringcontent` VALUES ('32', 'tuvw');
INSERT INTO `stringcontent` VALUES ('33', 'efghijk');
INSERT INTO `stringcontent` VALUES ('34', 'def');
INSERT INTO `stringcontent` VALUES ('35', 'abcdefg');
INSERT INTO `stringcontent` VALUES ('36', 'fghijkl');
INSERT INTO `stringcontent` VALUES ('37', 'qrstuv');
INSERT INTO `stringcontent` VALUES ('38', 'yz');
INSERT INTO `stringcontent` VALUES ('39', 'mn');
INSERT INTO `stringcontent` VALUES ('40', 'stuvwxy');
INSERT INTO `stringcontent` VALUES ('41', 'klm');
INSERT INTO `stringcontent` VALUES ('42', 'uvwxyz');
INSERT INTO `stringcontent` VALUES ('43', 't');
INSERT INTO `stringcontent` VALUES ('44', 'defgh');
INSERT INTO `stringcontent` VALUES ('45', 'bcdefghijklmnopqr');
INSERT INTO `stringcontent` VALUES ('46', 'yz');
INSERT INTO `stringcontent` VALUES ('47', 'f');
INSERT INTO `stringcontent` VALUES ('48', 'p');
INSERT INTO `stringcontent` VALUES ('49', 'jklmnopqrs');
INSERT INTO `stringcontent` VALUES ('50', 'jklmn');
INSERT INTO `stringcontent` VALUES ('51', 'tuvwxyz');
INSERT INTO `stringcontent` VALUES ('52', 'stuvwx');
INSERT INTO `stringcontent` VALUES ('53', 'wx');
INSERT INTO `stringcontent` VALUES ('54', 'tuv');
INSERT INTO `stringcontent` VALUES ('55', 'xyz');
INSERT INTO `stringcontent` VALUES ('56', 'k');
INSERT INTO `stringcontent` VALUES ('57', 'klmnopqrstuvwxy');
INSERT INTO `stringcontent` VALUES ('58', 'wxyz');
INSERT INTO `stringcontent` VALUES ('59', 'tuv');
INSERT INTO `stringcontent` VALUES ('60', 'rstuvw');
INSERT INTO `stringcontent` VALUES ('61', 'stuvwxyz');
INSERT INTO `stringcontent` VALUES ('62', 'stuvwxy');
INSERT INTO `stringcontent` VALUES ('63', 'uv');
INSERT INTO `stringcontent` VALUES ('64', 'z');
INSERT INTO `stringcontent` VALUES ('65', 'p');
INSERT INTO `stringcontent` VALUES ('66', 'ijklm');
INSERT INTO `stringcontent` VALUES ('67', 'fghijklmn');
INSERT INTO `stringcontent` VALUES ('68', 'efghijklmnopqrstu');
INSERT INTO `stringcontent` VALUES ('69', 'klmnopqrstuvw');
INSERT INTO `stringcontent` VALUES ('70', 'cdefg');
INSERT INTO `stringcontent` VALUES ('71', 'yz');
INSERT INTO `stringcontent` VALUES ('72', 'cd');
INSERT INTO `stringcontent` VALUES ('73', 'ghi');
INSERT INTO `stringcontent` VALUES ('74', 'yz');
INSERT INTO `stringcontent` VALUES ('75', 'wx');
INSERT INTO `stringcontent` VALUES ('76', 'vw');
INSERT INTO `stringcontent` VALUES ('77', 'hijk');
INSERT INTO `stringcontent` VALUES ('78', 'yz');
INSERT INTO `stringcontent` VALUES ('79', 'b');
INSERT INTO `stringcontent` VALUES ('80', 'ijklmnopqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('81', 'wxyz');
INSERT INTO `stringcontent` VALUES ('82', 'hij');
INSERT INTO `stringcontent` VALUES ('83', 'rstuvwxyz');
INSERT INTO `stringcontent` VALUES ('84', 'def');
INSERT INTO `stringcontent` VALUES ('85', 'p');
INSERT INTO `stringcontent` VALUES ('86', 'h');
INSERT INTO `stringcontent` VALUES ('87', 'efghijkl');
INSERT INTO `stringcontent` VALUES ('88', 'stuvwxyz');
INSERT INTO `stringcontent` VALUES ('89', 'yz');
INSERT INTO `stringcontent` VALUES ('90', 'rs');
INSERT INTO `stringcontent` VALUES ('91', 'cdefgh');
INSERT INTO `stringcontent` VALUES ('92', 'defghijklmnopqrst');
INSERT INTO `stringcontent` VALUES ('93', 'vwxyz');
INSERT INTO `stringcontent` VALUES ('94', 'h');
INSERT INTO `stringcontent` VALUES ('95', 'cdefghijklmnopqrstu');
INSERT INTO `stringcontent` VALUES ('96', 'abcdefghijk');
INSERT INTO `stringcontent` VALUES ('97', 'mnopqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('98', 'rstu');
INSERT INTO `stringcontent` VALUES ('99', 'vwxyz');
INSERT INTO `stringcontent` VALUES ('100', 'uvwxy');
INSERT INTO `stringcontent` VALUES ('101', 'jkl');
INSERT INTO `stringcontent` VALUES ('102', 'stuvwxyz');
INSERT INTO `stringcontent` VALUES ('103', 'jkl');
INSERT INTO `stringcontent` VALUES ('104', 'klmnopqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('105', 'klmnop');
INSERT INTO `stringcontent` VALUES ('106', 'tuvwxyz');
INSERT INTO `stringcontent` VALUES ('107', 'z');
INSERT INTO `stringcontent` VALUES ('108', 'a');
INSERT INTO `stringcontent` VALUES ('109', 'fghijklmnopqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('110', 'z');
INSERT INTO `stringcontent` VALUES ('111', 'b');
INSERT INTO `stringcontent` VALUES ('112', 'tuvwxyz');
INSERT INTO `stringcontent` VALUES ('113', 'xyz');
INSERT INTO `stringcontent` VALUES ('114', 'l');
INSERT INTO `stringcontent` VALUES ('115', 'cd');
INSERT INTO `stringcontent` VALUES ('116', 'pqrs');
INSERT INTO `stringcontent` VALUES ('117', 'qrstuv');
INSERT INTO `stringcontent` VALUES ('118', 'uvwxyz');
INSERT INTO `stringcontent` VALUES ('119', 'yz');
INSERT INTO `stringcontent` VALUES ('120', 'vw');
INSERT INTO `stringcontent` VALUES ('121', 'z');
INSERT INTO `stringcontent` VALUES ('122', 'f');
INSERT INTO `stringcontent` VALUES ('123', 'ghijklmn');
INSERT INTO `stringcontent` VALUES ('124', 'vwx');
INSERT INTO `stringcontent` VALUES ('125', 'stuvw');
INSERT INTO `stringcontent` VALUES ('126', 'uvwxyz');
INSERT INTO `stringcontent` VALUES ('127', 'pqrstu');
INSERT INTO `stringcontent` VALUES ('128', 'g');
INSERT INTO `stringcontent` VALUES ('129', 'defg');
INSERT INTO `stringcontent` VALUES ('130', 'ijkl');
INSERT INTO `stringcontent` VALUES ('131', 'hijkl');
INSERT INTO `stringcontent` VALUES ('132', 'pqrstuvwxyz');
INSERT INTO `stringcontent` VALUES ('133', 'lmnopqrstu');
INSERT INTO `stringcontent` VALUES ('134', 'xyz');
INSERT INTO `stringcontent` VALUES ('135', 'z');
INSERT INTO `stringcontent` VALUES ('136', 'y');
INSERT INTO `stringcontent` VALUES ('137', 'fg');
INSERT INTO `stringcontent` VALUES ('138', 'defg');
INSERT INTO `stringcontent` VALUES ('139', 'defghijk');
INSERT INTO `stringcontent` VALUES ('140', 'wxyz');
INSERT INTO `stringcontent` VALUES ('141', 'hi');
INSERT INTO `stringcontent` VALUES ('142', 'lmnop');
INSERT INTO `stringcontent` VALUES ('143', 'uvwxyz');
INSERT INTO `stringcontent` VALUES ('144', 'jklmn');
INSERT INTO `stringcontent` VALUES ('145', 'rstuvwxyz');
INSERT INTO `stringcontent` VALUES ('146', 'qrstuvw');
INSERT INTO `stringcontent` VALUES ('147', 'rstuvwxyz');
INSERT INTO `stringcontent` VALUES ('148', 'cdefgh');
INSERT INTO `stringcontent` VALUES ('149', 'cdefghijklmnopqrs');
INSERT INTO `stringcontent` VALUES ('150', 'opqrstuvw');
INSERT INTO `stringcontent` VALUES ('151', 'efghijklmn');
INSERT INTO `stringcontent` VALUES ('152', 'abcdefghijkl');
INSERT INTO `stringcontent` VALUES ('153', 'vwxyz');
INSERT INTO `stringcontent` VALUES ('154', 'qrs');
INSERT INTO `stringcontent` VALUES ('155', 'opqrstuvw');
INSERT INTO `stringcontent` VALUES ('156', 's');
INSERT INTO `stringcontent` VALUES ('157', 'xyz');
INSERT INTO `stringcontent` VALUES ('158', 'm');
INSERT INTO `stringcontent` VALUES ('159', 'jklmnopqrst');
INSERT INTO `stringcontent` VALUES ('160', 'jklmnopqr');
INSERT INTO `stringcontent` VALUES ('161', 'yz');
INSERT INTO `stringcontent` VALUES ('162', 'rs');
INSERT INTO `stringcontent` VALUES ('163', 'yz');
INSERT INTO `stringcontent` VALUES ('164', 'a');
INSERT INTO `stringcontent` VALUES ('165', 'wxyz');
INSERT INTO `stringcontent` VALUES ('166', 'ij');
INSERT INTO `stringcontent` VALUES ('167', 'ghijklmn');
INSERT INTO `stringcontent` VALUES ('168', 'tuvwxyz');
INSERT INTO `stringcontent` VALUES ('169', 'h');
INSERT INTO `stringcontent` VALUES ('170', 'xyz');
INSERT INTO `stringcontent` VALUES ('171', 'l');
INSERT INTO `stringcontent` VALUES ('172', 'z');
INSERT INTO `stringcontent` VALUES ('173', 'z');
INSERT INTO `stringcontent` VALUES ('174', 'l');
INSERT INTO `stringcontent` VALUES ('175', 'mnopqrstuvwx');
INSERT INTO `stringcontent` VALUES ('176', 'y');
INSERT INTO `stringcontent` VALUES ('177', 'wx');
INSERT INTO `stringcontent` VALUES ('178', 'defg');
INSERT INTO `stringcontent` VALUES ('179', 'stuvwxyz');
-- ----------------------------
-- Procedure structure for admininsert
-- ----------------------------
DROP PROCEDURE IF EXISTS `admininsert`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `admininsert`()
begin
insert into admin(username,password) values('jack1','0000'),('jack2','0000'),('jack3','0000');
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for employeesinfo
-- ----------------------------
DROP PROCEDURE IF EXISTS `employeesinfo`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `employeesinfo`()
BEGIN
DECLARE e_id int(11);
DECLARE e_name VARCHAR(50);
DECLARE e_salary int(11);
DECLARE emp_result cursor for select employee_id,CONCAT(FIRST_name,last_name),salary from employees;
OPEN emp_result;
fetch emp_result into e_id,e_name,e_salary;
select e_id,e_name,e_salary;
fetch emp_result into e_id,e_name,e_salary;
select e_id,e_name,e_salary;
close emp_result;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for employeesinfo1
-- ----------------------------
DROP PROCEDURE IF EXISTS `employeesinfo1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `employeesinfo1`()
BEGIN
DECLARE e_id int(11);
DECLARE e_name VARCHAR(50);
DECLARE e_salary int(11);
DECLARE has_data INT DEFAULT 1;
DECLARE emp_result cursor for select employee_id,CONCAT(FIRST_name,last_name),salary from employees;
DECLARE exit HANDLER FOR NOT FOUND set has_data=0;
OPEN emp_result;
REPEAT
fetch emp_result into e_id,e_name,e_salary;
select e_id,e_name,e_salary;
until has_data=0
end REPEAT;
close emp_result;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for login
-- ----------------------------
DROP PROCEDURE IF EXISTS `login`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(in username VARCHAR(20),in PASSWORD VARCHAR(20))
BEGIN
DECLARE result int default 0;# 声明并初始化
select count(*) INTO result #INTO 把查询到的sql语句的内容 赋值给变量result
from admin
where admin.username=username
and admin.password=PASSWORD;
select if(result>0,'成功','失败');
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for pro_while1
-- ----------------------------
DROP PROCEDURE IF EXISTS `pro_while1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_while1`(in insertCount int)
begin
DECLARE i int DEFAULT 1;
while i<=insertCount DO
insert into admin(username,password) values (concat('rose',i),concat('123',i));
set i=i+1;
end while;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for pro_while2
-- ----------------------------
DROP PROCEDURE IF EXISTS `pro_while2`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_while2`(in insertCount int)
begin
DECLARE i int DEFAULT 1;
a:while i<=insertCount DO
insert into admin(username,password) values (concat('tom',i),concat('123',i));
if i>=20 then leave a;
end if;
set i=i+1;
end while;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for pro_while3
-- ----------------------------
DROP PROCEDURE IF EXISTS `pro_while3`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_while3`(in insertCount int)
begin
DECLARE i int DEFAULT 1;
a:while i<=insertCount DO
set i=i+1;
if mod(i,2)!=0 then ITERATE a;
end if;
insert into admin(username,password) values (concat('laowang',i),concat('123',i));
end while;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for test_case
-- ----------------------------
DROP PROCEDURE IF EXISTS `test_case`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_case`(in score int)
begin
CASE
when score>=90 and score<=100 Then select 'A';
when score>=80 Then select 'B';
when score>=60 Then select 'C';
else select 'D';
end case;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for test_if
-- ----------------------------
DROP PROCEDURE IF EXISTS `test_if`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_if`(in score int)
begin
if score>=90 and score<=100 Then select 'A';
elseif score>=80 Then select 'B';
elseif score>=60 Then select 'C';
else select 'D';
end if;
end
;;
DELIMITER ;
-- ----------------------------
-- Procedure structure for test_randomstr
-- ----------------------------
DROP PROCEDURE IF EXISTS `test_randomstr`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_randomstr`(in insertCount int)
begin
declare i int default 1;#定义一个循环变量i ,表示插入次数
declare str varchar(26) default 'abcdefghijklmnopqrstuvwxyz';
declare startindex int default 1; #代表开始索引
declare len int default 1;#代表截取的字符的长度
while i<=insertcount DO
set len=FLOOR(RAND()*(26-startindex+1)+1);
set startindex=FLOOR(RAND()*26+1);#代表起始索引1-26
insert into stringcontent(content) values(SUBSTR(str,startIndex,len));
set i=i+1;
end while;
end
;;
DELIMITER ;
-- ----------------------------
-- Function structure for GetAvgSalByDepName1
-- ----------------------------
DROP FUNCTION IF EXISTS `GetAvgSalByDepName1`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `GetAvgSalByDepName1`(DepName varchar(20)) RETURNS double
BEGIN set @sal=0;
SELECT avg(salary) into @sal
FROM employees e join departments d
on e.department_id=d.department_id
WHERE DepName=d.department_name;
return @sal;
END
;;
DELIMITER ;
-- ----------------------------
-- Function structure for getEmpCount
-- ----------------------------
DROP FUNCTION IF EXISTS `getEmpCount`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `getEmpCount`() RETURNS int(11)
begin
DECLARE c int default 0;
select count(*) into c
from employees;
return c;
end
;;
DELIMITER ;
-- ----------------------------
-- Function structure for getEmpNameToSal
-- ----------------------------
DROP FUNCTION IF EXISTS `getEmpNameToSal`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `getEmpNameToSal`(empName varchar(20)) RETURNS double
BEGIN
set @sal=0;#定义用户变量
select salary into @sal
from employees
where last_name=empName;
return @sal;
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `emp_insert_trigger`;
DELIMITER ;;
CREATE TRIGGER `emp_insert_trigger` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
insert into emp_logs(id,operation,operate_time,operate_id,operate_parame) VALUES
(null,'insert',now(),NEW.employee_id,concat('插入后(id:',NEW.employee_id,',name:',NEW.first_name,',salary:',NEW.salary,')'));
end
;;
DELIMITER ;
单表查询
语法：
select 查询列表 from 表名;
- 查询列表可以是：表中的字段，常量值，表达式，函数
- 查询的结果是一个虚拟的表格
- 查询表中的单个字段
select last_name from employees;
select last_name,salary,email from employees;
select * from employees;
- 查询常量
select 100;
- 查询表达式
select 100-98;
- 查询函数
select version();
起别名
- 便于理解
- 如果要查询的字段有重名的情况，使用别名可以区分开来
-- 方式1：使用as
select 100-98 as 结果;
-- 方式2：使用空格
select last_name 姓,first_name 名 from employees;
-- 方式3：使用双引号
select salary as "output" from employees;
MySQL的+号
只有一个功能：运算符
select 100+90;
-- 只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
select '123'+90;
-- 如果转换失败,则将字符型数值转换为0
select 'aaa'+90;
-- 只要其中一方为null,则结果肯定为null
select null+10;
concat连接字符串函数
查询员工名和姓连接成一个字段,并显示为姓名
select last_name+first_name as 姓名
from employees;
这样查出来的结果为0，正确的做法应该是用concat函数
select concat('a','b','c') as 结果;
select concat(last_name,first_name) as 姓名 from employees;
IFNULL函数
语法：
IFNULL(expression, alt_value)
如果表达式的值为null，则返回alt_value；如果表达式的值不为null，则返回表达式的值。
select commission_pct 奖金率 from employees;
select IFNULL(commission_pct,0) 奖金率 from employees;
各种筛选
-
按条件表达式筛选
> < = != <> >= <= -
按逻辑表达式筛选
&&和and 两个条件都为true，结果就为true，反之为false
||和or 只要有一个条件为true，结果为true
!取反，=为等于，!=为不等于
NOT类似!，也是取反，但是两者用的情况不同，比如不等于用!=，不在集合中用not in -
模糊查询
like 像，一般和通配符搭配
between and 介于两者之间，包含边界值
in 在集合中
is null 为空
按条件表达式筛选
- 查询工资大于12000的员工信息
select *
from employees
where salary>12000;
- 查询部门编号不等于90号的员工名和部门编号
select last_name,department_id
from employees
where department_id!=90;
按逻辑表达式筛选
- 查询工资在10000到20000之间的员工名，工资，以及奖金
select last_name,salary,commission_pct
from employees
where salary>=10000 and salary<=20000;
- 查询部门编号不是在90到110之间，或者工资高于15000的员工信息
select *
from employees
where (department_id<90 or department_id>110) or salary>15000;
模糊查询
like一般和通配符搭配使用
通配符：%代表任意个字符，_代表任意单个字符
- 查询员工名中包含字符a的员工信息
select *
from employees
where last_name like '%a%';
- 查询员工名中第二个字符为_的员工名称
select last_name
from employees
where last_name like '_\_%';
select last_name
from employees
where last_name like '_&_%' escape '&';
第一种写法中，\代表转义符，转义符后的第一个字符将失去特殊含义，_的特殊含义就是代表任意单个字符。
第二种写法中，自定义了转义符&，用到的关键字是escape。
between and介于两者之间：
- 使用between and可以提高语句的简洁渡
- 包含临界值
- 两个临界值不要调换顺序，小值在前，大值在后
- 查询员工编号在100到120之间的员工信息
select *
from employees
where employee_id>=100 and employee_id<=120;
select *
from employees
where employee_id between 100 and 120;
in判断某字段或列表是否属于in列表中的某一项：
- 使用in可以提高语句的简洁度
- in列表中的类型必须兼容或者一致
- 查询员工的工种编号是IT_PROT，AD_VP，AD_PRES中某一个的员工名和工种编号
select last_name,job_id
from employees
where job_id='IT_PROT' or job_id ='AD_VP' or JOB_ID='AD_PRES';
select last_name,job_id
from employees
where job_id in ('IT_PROT' ,'AD_VP','AD_PRES');
is null为空，查询空值时使用。
参考null值需要注意的地方
- 查询没有奖金的员工名称奖金率
select last_name,commission_pct
from employees
where commission_pct=null;
select last_name,commission_pct
from employees
where commission_pct is null;
排序查询
语法：
select 查询列表
from 表
[where 筛选条件]
[order by 排序列表 asc|desc]
asc代表的是升序，desc代表降序，默认是升序。
- 查询员工信息，要求工资从高到低排序
select * from employees order by salary desc;
- 查询部门编号>=90的员工信息，按入职的时间的先后进行排序[按照筛选条件排序]
select * from employees where department_id>90 order by hiredate asc;
- 按年薪的高低显示员工名和年薪[按照表达式排序]
select last_name,salary*12*(1+ifnull(commission_pct,0)) 年薪
from employees
order by salary*12*(1+ifnull(commission_pct,0)) desc;
- 按年薪的高低显示员工名和年薪[按照别名排序]
select last_name,salary*12*(1+ifnull(commission_pct,0)) 年薪
from employees
order by 年薪 desc;
- 按姓名的长度显示员工名和工资
select length(last_name) 名字长度,last_name,salary
from employees
order by length(last_name) desc;
- 查询员工信息，要求先按照工资升序，再按照员工编号降序(多个字段进行排序)
select *
from employees
order by salary asc,employee_id desc;
- 查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
from employees
order by 年薪 desc,last_name asc;
- 选择工资不在8000到17000的员工的姓名和工资，按工资降序
select salary,last_name from employees
where salary not between 8000 and 17000
order by salary desc;
- 查询邮箱中包含e的员工信息，并先按照邮箱的字节数降序，再按部门号升序
select *,length(email)
from employees
where email like '%e%'
order by length(email) desc ,department_id asc;
常见函数
- 单行函数
concat，length，ifnull - 分组函数
做统计使用，又叫聚合函数，统计函数
单行函数
字符函数
length(str)字符串长度
concat(str)拼接字符串
upper(str)转大小
lower(str)转小写
先输出名字长度，接着将姓变大写，名变小写，然后拼接输出姓名
select length(concat(last_name,first_name)),
concat(upper(last_name),lower(first_name)) 姓名
from employees;
substr(str,index,length)截取字符串，索引从1开始。如果省略length则显示index开始之后的所有字符。
select SUBSTR('今天天气凉快',2);
select SUBSTR('今天天气凉快',2,4);
instr(str)返回子串第一次出现的索引，如果找不到返回0
select instr('今天天气凉快','天气');
select instr('今天天气凉快','热');
replace(str,searchstr,newstr)替换字符串
select replace('hello world, hello mysql','ll','TT');
数值函数
round(number,num_digits)四舍五入，number是需要进行四舍五入的数字，num_digits是指定的位数，按此位数进行四舍五入，默认为0。
-- 四舍五入
select round(-1.55);
-- 四舍五入，保留两位小数
select round(1.567,2);
ceil(number) 向上取整，返回>=该参数的最小整数
floor(number) 向下取整，返回<=该参数的最大整数
select ceil(1.02);
select floor(9.99);
mod取余
select mod(10,3);
select 10%3;
日期函数
select NOW(); -- 返回当前系统日期+时间
select CURDATE(); -- 日期
select curtime(); -- 时间
-- 可以获取指定的部分，年，月，日
select YEAR(NOW()); -- 年
select MONTH(NOW()); -- 月
select DAY(NOW()); -- 日
常用日期格式：
%Y 4位年份2021
%y 2位年份21
%m 月05,10,12
%c 月5,10,12
%d 日01,09,22
%e 日1,9,22
%H 24小时制
%h 12小时制
%p AM或PM
%i 分钟
%s 秒
- 将字符串通过指定格式转换为日期
-- 注意日期格式要对应
select STR_TO_DATE('2021-05-09','%Y-%m-%d');
- 更改日期格式
select DATE_FORMAT(now(),'%Y年%c月%e日');
- 查询入职日期为1992-4-3的员工信息
select * from employees where hiredate='1992-4-3';
流程控制语句
if函数
select if(10<5,'小','大');
-- 查询员工是否有奖金，有奖金的输出有奖金的，没有输出没有奖金
select last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金') from employees;
case when
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
查看员工的工资，显示30部门的工资为1.3倍，40为1.4，50为1.5，其他不变
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.3
when 40 then salary*1.4
when 50 then salary*1.5
else salary
end as 新工资
from employees;
分组函数
用作统计使用，又称为聚合函数
常用的有如下函数：
sum 求和
avg 平均值
max 最大值
min 最小值
count计算个数
其中，sum和avg一般用于处理数值型，而max、min、count可以处理任何类型。
- 简单使用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(*) from employees;
select count(salary) from employees;
select sum(last_name)from employees;
select max(last_name)from employees;
- 分组函数会忽略null值
select count(IFNULL(commission_pct,0)),count(employee_id) from employees;
- 以下三种写法都可以计算个数
select count(salary) from employees;
select count(*) from employees;
select count(1) from employees;
但是效率上有差别：
MYISAM存储引擎下，count(*)效率高。
INNODB存储引擎下，count(*)和count(1)效率差不多，比count(字段)要高一些。
- 查询部门编号为90的员工个数
select count(*)
from employees
WHERE department_id=90;
group by
分组函数要配合group by使用，group by可以将表中的数据分成若干组
- 查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;
- 查询每个部门员工的个数
select count(*),department_id
from employees
group by department_id;
- 查询邮箱中包含a字符的，每个部门的平均工资
select avg(salary),department_id
from employees
where email like '%a%'
group by department_id;
- 查询有奖金的每个领导手下的员工的最高工资
select max(salary),manager_id
from employees
where commission_pct is not null
group by manager_id;
having
分组查询中的筛选条件分为两类：
- 分组前的筛选：对原始表筛选，在group by子句的前面使用
where字句 - 分组后的筛选：对分组后的结果集筛选，在group by子句的后面使用
having字句
- 查询哪个部门的员工个数>2
select count(*),department_id
from employees
group by department_id
having count(*)>2;
- 查询每个工作有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;
- 查询领导编号>102的每个领导手下的最低工资>5000的领导编号，以及其最低工资
select manager_id,min(salary)
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;
- 按员工的姓名的长度分组，查询每一组的员工个数，并筛选员工个数>5
select count(*),length(last_name)
from employees
group by length(last_name)
having count(*)>5;
按多个字段分组
- 查询每个部门每个工种的平均工资，按平均工资的高低显示
select avg(salary),department_id,job_id
from employees
group by department_id,job_id
order by avg(salary) desc;
- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000;
- 查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
select department_id,count(*),avg(salary)
from employees
group by department_id
order by avg(salary) desc;
多表查询
导入以下练习数据
点击查看详细内容
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
INSERT INTO `beauty` VALUES ('1', '张无忌', '女', '1987-01-01 00:00:00', '1234567', null, '2');
INSERT INTO `beauty` VALUES ('2', '鹿晗', '女', '1987-01-01 00:00:00', '1234567', null, '2');
INSERT INTO `beauty` VALUES ('3', 'Angelababy', '女', '1989-02-03 00:00:00', '114', null, '3');
INSERT INTO `beauty` VALUES ('4', '热巴', '女', '1993-02-03 00:00:00', '114', null, '2');
INSERT INTO `beauty` VALUES ('5', '周冬雨', '女', '1992-02-03 00:00:00', '18209179577', null, '9');
INSERT INTO `beauty` VALUES ('6', '周芷若', '女', '1988-02-03 00:00:00', '114', null, '1');
INSERT INTO `beauty` VALUES ('7', '岳灵珊', '女', '1987-12-30 00:00:00', '18219876577', null, '9');
INSERT INTO `beauty` VALUES ('8', '小昭', '女', '1989-02-03 00:00:00', '114', null, '1');
INSERT INTO `beauty` VALUES ('10', '王语嫣', '女', '1992-02-03 00:00:00', '114', null, '4');
INSERT INTO `beauty` VALUES ('12', '赵敏', '女', '1992-02-03 00:00:00', '114', null, '1');
INSERT INTO `beauty` VALUES ('13', '金星', '女', '2020-11-28 11:11:54', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('14', '金星', '女', '2020-11-28 11:13:21', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('15', '金星', '女', '2020-11-28 11:16:42', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('16', '金星2', '女', '2020-11-28 11:18:38', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('20', '金星3', '女', '2020-11-28 11:21:13', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('21', '金星4', '女', '2020-11-28 11:21:13', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('22', '金星5', '女', '2020-11-28 11:21:13', '138888888', null, '2');
INSERT INTO `beauty` VALUES ('23', '金星6', '女', '2020-11-28 11:21:13', '138888888', null, '2');
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `boys` VALUES ('1', '张无忌', '100');
INSERT INTO `boys` VALUES ('2', '张飞', '10');
INSERT INTO `boys` VALUES ('3', '黄晓明', '50');
INSERT INTO `boys` VALUES ('4', '段誉', '300');
多表查询又称为连接查询，当查询的字段来自于多个表时候，就会用到连接查询。
连接的分类有：等值连接、自连接、非等值连接、左外连接、右外连接、全外连接。
等值连接
- 查询女神名和对应的男神名
select name,boyName
from boys,beauty
where beauty.boyfriend_id=boys.id;
- 查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;
上面的查询中，因为两张表中都有department_id，为了区分多个重名的字段，所以需要指定是哪张表的department_id。但是每次写表名会很繁琐，为了提高语句的简洁度，我们可以给表起别名。
select last_name,department_name
from employees e,departments d
where e.department_id=d.department_id;
- 查询员工名，工种号，工种名
select e.last_name,j.job_id,j.job_title
from employees e,jobs j
where e.job_id=j.job_id;
- 查询有奖金的员工号，部门名
select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id and e.commission_pct is not null;
- 查询城市名中第二个字符为o的部门名和城市名
select l.city,d.department_name
from departments d,locations l
where d.location_id=l.location_id and l.city like "_o%";
- 查询每个城市的部门个数
select count(*) 个数,city
from departments d,locations l
where d.location_id=l.location_id
group by city;
- 查询有奖金员工的部门名和部门的领导编号和该部门的最低工资
select department_name,d.manager_id,min(salary)
from departments d,employees e
where d.department_id=e.department_id
and commission_pct is not NULL
group by department_name,d.manager_id;
- 查询员工名，部门名和所在的城市，并且城市是以s开头的名字
select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id and city like 's%';
sql99的查询语法
select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
- 查询员工名，部门名
select last_name,department_name
from departments d
join employees e
on e.department_id=d.department_id;
- 查询部门个数>3的城市名和部门个数
select city,count(*)
from departments d
join locations l
on d.location_id=l.location_id
group by city
having count(*)>3;
- 查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序
select department_name,count(*)
from departments d INNER JOIN employees e
on d.department_id=e.department_id
group by department_name
having count(*)>3
order by count(*) desc;
自连接
- 查询员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id;
非等值连接
- 查询员工的工资级别
select salary,grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
- 查询工资级别的个数>20的个数，并且按工资级别降序
select count(*),grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc;
外连接
应用场景：用于查询一个表中有，另一个表中没有的记录
特点:
- 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示null。外连接的查询结果=内连接的结果+主表中有而从表中没有的记录
- 左外连接left join左边是主表，右外连接right join右边是主表
- 交换两个表的顺序，可以使左外和右外等价。如a left join b和b right join a。
- 查询哪个部门没有员工
select d.*,e.employee_id
from departments d
left join employees e
on d.department_id=e.department_id
where e.employee_id is null;
- 查询哪个城市没有部门
select l.city,d.department_id from departments d
right join locations l
on d.location_id = l.location_id and d.location_id is null;
- 查询部门名为SAL或IT的员工信息
select e.*,d.department_name
from departments d
left join employees e
on d.department_id =e.department_id
where d.department_name IN('SAL','IT');
子查询
出现在其他语句中的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询。
子查询的位置可以在：
- select 后面
- from 后面
- where或having的后面
- exists后面
- 谁的工资比Abel高？
select *
from employees
where salary>(
select salary
from employees
where last_name='Abel'
);
- 找出job_id与141员工相同，salary比143员工多的员工姓名，job_id和工资
select last_name,job_id,salary
from employees
where job_id=(
select job_id from
employees where employee_id=141
) and salary>(
select salary
from employees
where employee_id=143
);
- 返回公司工资最少的员工的last_name，job_id和salary
select last_name,job_id,salary
from employees
where salary=(
select min(salary)
from employees
);
- 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50
);
- 非法使用子查询
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
select salary
from employees
where department_id=50
);
[Err] 1242 - Subquery returns more than 1 row
错误原因：子查询返回了多行数据。分析一下上面的查询，min(salary)的结果是一个值A，后面查询语句的结果有多个（B,C,D,...），A>(B,C,D,...)不是合法的逻辑判断，所以报错说子查询返回了多行数据。
多行子查询
in 是列表中的某一个
not in 不在列表中
any/some 和子查询返回的某一个值比较
all 和子查询返回的所有值比较
- 返回location_id是1400或1700的部门中的所有的员工姓名
select last_name from employees
where department_id in (
select department_id
from departments
where location_id in(1400,1700)
);
- 返回其他工种中比job_id为'IT_PROG'部门任一员工低的员工号，姓名，job_id以及salary
select last_name,employee_id,job_id,salary
from employees
where salary<any(
select distinct salary
from employees
where job_id='IT_PROG'
) and job_id <> 'IT_PROG';
或者
select last_name,employee_id,job_id,salary
from employees
where salary<(
select distinct max(salary)
from employees
where job_id='IT_PROG'
) and job_id != 'IT_PROG';
- 返回其他工种中比job_id为'IT_PROG'部门所以工资都低的员工号，姓名，job_id以及salary
select last_name,employee_id,job_id,salary
from employees
where salary<all(
select distinct salary
from employees
where job_id='IT_PROG'
) and job_id != 'IT_PROG';
或者
select last_name,employee_id,job_id,salary
from employees
where salary<(
select distinct min(salary)
from employees
where job_id='IT_PROG'
) and job_id != 'IT_PROG';
行子查询
行子查询是指子查询返回的结果集是一行N列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。
- 查询与管理者编号为123员工的部门编号和工种相同的员工
select *
from employees
where (department_id,job_id) in (
select distinct department_id,job_id
from employees
where manager_id=123
);
- 查询每个部门的平均工资的工资等级
select ag_dept.*,g.grade_level
from (
select avg(salary) avgsal,department_id
from employees
group by department_id
) ag_dept join job_grades g
on ag_dept.avgsal between lowest_sal and highest_sal;
图解MySQL各种连接
图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了
课后任务
接着做之前的练习
超经典SQL练习题，做完这些你的SQL就过关了
【SQL练习】经典SQL练习题
