一、演示表介绍
1.employees员工表
字段名称 | 字段含义 |
---|---|
employee_id | 员工编号 |
first_name | 名 |
last_name | 姓 |
邮箱 | |
phone_number | 电话号码 |
job_id | 工种编号 |
salary | 工资 |
commission_pct | 奖金率 |
manager_id | 上级领导的编号 |
department_id | 部门编号 |
hiredate | 入职日期 |
2.departments部门表
字段名称 | 字段含义 |
---|---|
department_id | 部门编号 |
department_name | 部门名称 |
manager_id | 部门领导id |
location_id | 位置编号 |
3.location 位置表
字段名称 | 字段含义 |
---|---|
location_id | 位置编号 |
street_address | 街道 |
postal_code | 右边 |
city | 城市 |
state_province | 州/省 |
country_id | 国家编号 |
4.jobs工种表
字段名称 | 字段含义 |
---|---|
job_id | 工种编号 |
job_title | 工种名称 |
min_salary | 最低工资 |
max_salary | 最高工资 |
三、基础查询
语法:
select 查询列表 from 表名称;
1.查询所有字段
mysql> use myemployees;
mysql> show tables;
+-----------------------+
| Tables_in_myemployees |
+-----------------------+
| departments |
| employees |
| jobs |
| locations |
+-----------------------+
4 rows in set (0.00 sec)
mysql> select * from jobs;
+------------+---------------------------------+------------+------------+
| job_id | job_title | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| HR_REP | Human Resources Representative | 4000 | 9000 |
| IT_PROG | Programmer | 4000 | 10000 |
| MK_MAN | Marketing Manager | 9000 | 15000 |
| MK_REP | Marketing Representative | 4000 | 9000 |
| PR_REP | Public Relations Representative | 4500 | 10500 |
| PU_CLERK | Purchasing Clerk | 2500 | 5500 |
| PU_MAN | Purchasing Manager | 8000 | 15000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| SA_REP | Sales Representative | 6000 | 12000 |
| SH_CLERK | Shipping Clerk | 2500 | 5500 |
| ST_CLERK | Stock Clerk | 2000 | 5000 |
| ST_MAN | Stock Manager | 5500 | 8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)
2.查询单个字段
mysql> select job_id from jobs;
+------------+
| job_id |
+------------+
| AC_ACCOUNT |
| AC_MGR |
| AD_ASST |
| AD_PRES |
| AD_VP |
| FI_ACCOUNT |
| FI_MGR |
| HR_REP |
| IT_PROG |
| MK_MAN |
| MK_REP |
| PR_REP |
| PU_CLERK |
| PU_MAN |
| SA_MAN |
| SA_REP |
| SH_CLERK |
| ST_CLERK |
| ST_MAN |
+------------+
19 rows in set (0.00 sec)
3.查询多个字段
mysql> select job_id,job_title from jobs;
+------------+---------------------------------+
| job_id | job_title |
+------------+---------------------------------+
| AC_ACCOUNT | Public Accountant |
| AC_MGR | Accounting Manager |
| AD_ASST | Administration Assistant |
| AD_PRES | President |
| AD_VP | Administration Vice President |
| FI_ACCOUNT | Accountant |
| FI_MGR | Finance Manager |
| HR_REP | Human Resources Representative |
| IT_PROG | Programmer |
| MK_MAN | Marketing Manager |
| MK_REP | Marketing Representative |
| PR_REP | Public Relations Representative |
| PU_CLERK | Purchasing Clerk |
| PU_MAN | Purchasing Manager |
| SA_MAN | Sales Manager |
| SA_REP | Sales Representative |
| SH_CLERK | Shipping Clerk |
| ST_CLERK | Stock Clerk |
| ST_MAN | Stock Manager |
+------------+---------------------------------+
19 rows in set (0.00 sec)
4.查询常量
mysql> select 10010;
+-------+
| 10010 |
+-------+
| 10010 |
+-------+
1 row in set (0.00 sec)
mysql> select "hello world";
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)
mysql> select 'mysql very good';
+-----------------+
| mysql very good |
+-----------------+
| mysql very good |
+-----------------+
1 row in set (0.00 sec)
5.查询表达式
mysql> select 100 / 3;
+---------+
| 100 / 3 |
+---------+
| 33.3333 |
+---------+
1 row in set (0.00 sec)
6.查询函数
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)
mysql> select database();
+-------------+
| database() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-20 11:14:42 |
+---------------------+
1 row in set (0.00 sec)
7.起别名
mysql> select now() as 时间;
+---------------------+
| 时间 |
+---------------------+
| 2020-12-20 11:22:30 |
+---------------------+
1 row in set (0.00 sec)
mysql> select version() as "版本";
+--------+
| 版本 |
+--------+
| 5.6.36 |
+--------+
1 row in set (0.00 sec)
给表中的字段起别名,其中得as也可以省略
mysql> select min_salary as 最少工资,max_salary as 最多工资 from jobs;
+--------------+--------------+
| 最少工资 | 最多工资 |
+--------------+--------------+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
mysql> select min_salary 最少工资,max_salary 最多工资 from jobs;
+--------------+--------------+
| 最少工资 | 最多工资 |
+--------------+--------------+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
| 20000 | 40000 |
给表起别名,一般在多表查询的时候用的较多。
mysql> select * from employees as e;
8.去重distinct
查询员工表中涉及到的所有的部门编号.
mysql> select department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
| NULL |
| 10 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 40 |
| 50 |
| 50 |
| 50 |
| 50 |
+---------------+
15 rows in set (0.00 sec)
这里查询到的数据是重复的,去重的方法就是在字段前面加上去重关键字distinct
mysql> select distinct department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
| NULL |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
+---------------+
12 rows in set (0.00 sec)
四、条件查询
语法:
select 查询列表 from 表名 where 筛选条件;
1.按条件表达式筛选
条件运算符号: > < = != <> >= <=
例子1:查询工资>12000的员工信息
mysql> mysql> select * from employees where salary > 12000;
例子2:查询部门编号不等于90号的员工和部门编号
select last_name,department_id from employees where department_id != 90;
或者
select last_name,department_id from employees where department_id <> 90;
2.按照逻辑表达式筛选
逻辑运算符: && || !
and or not
逻辑运算符作用:连接多个表达式
例子1:查询工资在10000到20000之间的员工名、工资以及奖金
select
last_name,salary,commission_pct
from
employees
where
salary >= 10000 and salary <= 20000;
3.模糊查询
(1)like
例子1:查询员工名称中包含字符a的员工信息
# "%"代表统配符,代表任意长度的任意字符。
# "_" 代表任意单个字符
select
*
from
employees
where
last_name like '%a%';
(2)between and
例子:查询员工编号在120到120之间的员工信息
select
*
from
employees
where
employee_id between 100 and 120;
(3)in
例子1:查询员工的工种编号是IT_PROG 、AD_VP、AD_PRES中的一个员工名和工种编号
select
last_name,
job_id
from
employees
where
job_id in('IT_PROG','AD_VP','AD_PRES');
(4)is null
例子1:查询没有奖金的员工名和奖金率
#在sql中 "=" 是不能判断null值得,所以用is ,is是配合NULL使用得
select
last_name,
commission_pct
from
employees
where
commission_pct is null;
反之
select
last_name,
commission_pct
from
employees
where
commission_pct is not null;
五、排序查询
语法:
select
*
from
表名
where
查询条件
order by 排序字段 [asc | desc];
#asc是升序 desc是降序 默认是升序
例子1;查询员工得工资 按照低到高排序
mysql> select * from employees order by salary limit 10;
六、常见函数
1.字符函数
(1) length():统计字符得字节大小
如果是英文字符串得话,字符长度 = 字符所有占用空间大小
如果是中文得话,要看字符集设置,在UTF-8中一个汉字占用3个字节
mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
mysql> select length('中国你好');
+------------------------+
| length('中国你好') |
+------------------------+
| 12 |
+------------------------+
1 row in set (0.00 sec)
(2) concat():拼接字符串
mysql> select concat('hello',' ','world') as '字符串拼接结果';
+-----------------------+
| 字符串拼接结果 |
+-----------------------+
| hello world |
+-----------------------+
1 row in set (0.00 sec)
拼接字段也是一样得
mysql> select concat(last_name,'_',first_name) from employees limit 3;
+----------------------------------+
| concat(last_name,'_',first_name) |
+----------------------------------+
| K_ing_Steven |
| Kochhar_Neena |
| De Haan_Lex |
+----------------------------------+
3 rows in set (0.00 sec)
(3)upper():转换为大写
mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO |
+----------------+
1 row in set (0.00 sec)
(4)lower():转换为小写
mysql> select lower('HELLO World');
+----------------------+
| lower('HELLO World') |
+----------------------+
| hello world |
+----------------------+
1 row in set (0.00 sec)
(5)substr():字符串截取
substr(数据,开始索引,向后截取得长度)
注意:索引是从1开始得
mysql> select phone_number from employees limit 3;
+--------------+
| phone_number |
+--------------+
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
+--------------+
3 rows in set (0.00 sec)
mysql> select substr(phone_number,5) from employees limit 3;
+------------------------+
| substr(phone_number,5) |
+------------------------+
| 123.4567 |
| 123.4568 |
| 123.4569 |
+------------------------+
3 rows in set (0.00 sec)
mysql> select substr(phone_number,5,3) from employees limit 3;
+--------------------------+
| substr(phone_number,5,3) |
+--------------------------+
| 123 |
| 123 |
| 123 |
+--------------------------+
3 rows in set (0.00 sec)
(6)instr():取出字串得索引
如果找不到返回0
mysql> select instr('张无忌爱周芷若','张') as ret;
+-----+
| ret |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select instr('张无忌爱周芷若','周芷若') as ret;
+-----+
| ret |
+-----+
| 5 |
+-----+
1 row in set (0.00 sec)
mysql> select instr('张无忌爱周芷若','小') as ret;
+-----+
| ret |
+-----+
| 0 |
+-----+
1 row in set (0.00 sec)
(7)trim():去空格函数
mysql> select length(' hello ') as ret;
+-----+
| ret |
+-----+
| 11 |
+-----+
1 row in set (0.00 sec)
mysql> select length(trim(' hello ')) as ret;
+------+
| ret |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
(8)replace():字符替换
mysql> select replace('张无忌喜欢周芷若,周芷若也爱张无忌','周芷若','赵敏') as ret;
+-----------------------------------------------+
| ret |
+-----------------------------------------------+
| 张无忌喜欢赵敏,赵敏也爱张无忌 |
+-----------------------------------------------+
1 row in set (0.00 sec)
2.数学函数
(1)round():四舍五入函数
mysql> select round(3.14);
+-------------+
| round(3.14) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(3.64);
+-------------+
| round(3.64) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
#保留小数点后几位,然后在四舍五入
mysql> select round(3.64,1);
+---------------+
| round(3.64,1) |
+---------------+
| 3.6 |
+---------------+
1 row in set (0.00 sec)
mysql> select round(3.66,1);
+---------------+
| round(3.66,1) |
+---------------+
| 3.7 |
+---------------+
1 row in set (0.00 sec)
(2)truncate():直接保留小数点后边几位,不进行四舍五入
mysql> select truncate(3.1415,2);
+--------------------+
| truncate(3.1415,2) |
+--------------------+
| 3.14 |
+--------------------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
| 3.141 |
+--------------------+
1 row in set (0.00 sec)
3.日期函数
(1)now():系统日期+时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-20 16:08:07 |
+---------------------+
1 row in set (0.00 sec)
(2)curdate():只显示日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-12-20 |
+------------+
1 row in set (0.00 sec)
(3)curtime():只显示时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:09:47 |
+-----------+
1 row in set (0.00 sec)
(4)str_to_date():字符串转日期函数
序号 | 格式符号 | 功能 |
---|---|---|
1 | %Y | 四位的月份 |
2 | %y | 两位的月份 |
3 | %m | 月份(01,02…11,12) |
4 | %c | 月份(1,2…11,12) |
5 | %d | 日(01,02) |
6 | %H | 小时(24小时制) |
7 | %h | 小时(12小时制) |
8 | %i | 分钟(00,01…59) |
9 | %s | 秒(00,01…59) |
例子:在转化的时候,后边要转换的位置和前边的数据位置对应上,最后的结果系统自动会按照’年 月 日’的顺序显示出来
mysql> select str_to_date('6-7 1997','%m-%d %Y') as 'date';
+------------+
| date |
+------------+
| 1997-06-07 |
+------------+
1 row in set (0.00 sec)
mysql> select str_to_date('3/1/2020','%m/%d/%Y') as 'date';
+------------+
| date |
+------------+
| 2020-03-01 |
+------------+
1 row in set (0.00 sec)
(5)将日期转换成字符
mysql> select date_format(now(),'%Y年%m月%d日') as ret;
+-------------------+
| ret |
+-------------------+
| 2020年12月20日 |
+-------------------+
1 row in set (0.01 sec)
mysql> select date_format(now(),'公元%Y年 %m月 %d日') as ret;
+---------------------------+
| ret |
+---------------------------+
| 公元2020年 12月 20日 |
+---------------------------+
1 row in set (0.01 sec)
4.其它函数
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36 |
+-----------+
1 row in set (0.00 sec)
mysql> select database();
+-------------+
| database() |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
七、分组函数
功能:用作统计使用,又称为聚合函数或者统计函数
1.sum():求和
mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
| 691400.00 |
+-------------+
1 row in set (0.00 sec)
2.avg():平均值
mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)
3.max():最大值
mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
| 24000.00 |
+-------------+
1 row in set (0.00 sec)
4.min():最小值
mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
| 2100.00 |
+-------------+
1 row in set (0.00 sec)
5.count():计算个数
用法1: count(字段名) :统计字段一共多少行
mysql> select count(salary) from employees;
+---------------+
| count(salary) |
+---------------+
| 107 |
+---------------+
1 row in set (0.00 sec)
用法2:count(*) : 每一行中只要一个字段有数据,就算一行
6.注意:
1.sum avg 一般只用来处理数值类型数据
2.max min count 可以处理任何类型的数据
3.以上所有函数都忽略null
八、分组查询
语法:
select
分组函数,字段名(这个字段名还要出现在group by的后边)
from
表名
where
筛选条件
group by 上边的字段名(也就是要分组的字段)
1.简单分组查询
例子1:查询每个工种的最高工资
mysql> select max(salary),job_id from employees group by job_id;
+-------------+------------+
| max(salary) | job_id |
+-------------+------------+
| 8300.00 | AC_ACCOUNT |
| 12000.00 | AC_MGR |
| 4400.00 | AD_ASST |
| 24000.00 | AD_PRES |
| 17000.00 | AD_VP |
| 9000.00 | FI_ACCOUNT |
| 12000.00 | FI_MGR |
| 6500.00 | HR_REP |
| 9000.00 | IT_PROG |
| 13000.00 | MK_MAN |
| 6000.00 | MK_REP |
| 10000.00 | PR_REP |
| 3100.00 | PU_CLERK |
| 11000.00 | PU_MAN |
| 14000.00 | SA_MAN |
| 11500.00 | SA_REP |
| 4200.00 | SH_CLERK |
| 3600.00 | ST_CLERK |
| 8200.00 | ST_MAN |
+-------------+------------+
19 rows in set (0.00 sec)
2.带判断的分组查询
例子1:查询邮箱中包含a字符的,每个部门的平均工资
mysql> select avg(salary),department_id,email from employees where email like '%a%' group by department_id;
+--------------+---------------+----------+
| avg(salary) | department_id | email |
+--------------+---------------+----------+
| 7000.000000 | NULL | KGRANT |
| 4400.000000 | 10 | JWHALEN |
| 9500.000000 | 20 | MHARTSTE |
| 4460.000000 | 30 | DRAPHEAL |
| 6500.000000 | 40 | SMAVRIS |
| 3496.153846 | 50 | AFRIPP |
| 6200.000000 | 60 | AHUNOLD |
| 10000.000000 | 70 | HBAER |
| 8535.294118 | 80 | KPARTNER |
| 17000.000000 | 90 | NKOCHHAR |
| 8166.666667 | 100 | DFAVIET |
+--------------+---------------+----------+
11 rows in set (0.00 sec)
九、员工表(4张表)生成数据的sql语句
将以下数据保存到文件,后缀名为sql然后导入到mysql中。
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.15 : Database - myemployees
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`myemployees` /*!40100 DEFAULT CHARACTER SET gb2312 */;
USE `myemployees`;
/*Table structure for table `departments` */
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`department_id` int(4) NOT NULL AUTO_INCREMENT,
`department_name` varchar(3) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`location_id` int(4) DEFAULT NULL,
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;
/*Data for the table `departments` */
insert into `departments`(`department_id`,`department_name`,`manager_id`,`location_id`) values (10,'Adm',200,1700),(20,'Mar',201,1800),(30,'Pur',114,1700),(40,'Hum',203,2400),(50,'Shi',121,1500),(60,'IT',103,1400),(70,'Pub',204,2700),(80,'Sal',145,2500),(90,'Exe',100,1700),(100,'Fin',108,1700),(110,'Acc',205,1700),(120,'Tre',NULL,1700),(130,'Cor',NULL,1700),(140,'Con',NULL,1700),(150,'Sha',NULL,1700),(160,'Ben',NULL,1700),(170,'Man',NULL,1700),(180,'Con',NULL,1700),(190,'Con',NULL,1700),(200,'Ope',NULL,1700),(210,'IT ',NULL,1700),(220,'NOC',NULL,1700),(230,'IT ',NULL,1700),(240,'Gov',NULL,1700),(250,'Ret',NULL,1700),(260,'Rec',NULL,1700),(270,'Pay',NULL,1700);
/*Table structure for table `employees` */
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`employee_id` int(6) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(25) DEFAULT NULL,
`email` varchar(25) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`job_id` varchar(10) DEFAULT NULL,
`salary` double(10,2) DEFAULT NULL,
`commission_pct` double(4,2) DEFAULT NULL,
`manager_id` int(6) DEFAULT NULL,
`department_id` int(4) DEFAULT NULL,
`hiredate` datetime DEFAULT NULL,
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=207 DEFAULT CHARSET=gb2312;
/*Data for the table `employees` */
insert into `employees`(`employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate`) values (100,'Steven','K_ing','SKING','515.123.4567','AD_PRES',24000.00,NULL,NULL,90,'1992-04-03 00:00:00'),(101,'Neena','Kochhar','NKOCHHAR','515.123.4568','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(102,'Lex','De Haan','LDEHAAN','515.123.4569','AD_VP',17000.00,NULL,100,90,'1992-04-03 00:00:00'),(103,'Alexander','Hunold','AHUNOLD','590.423.4567','IT_PROG',9000.00,NULL,102,60,'1992-04-03 00:00:00'),(104,'Bruce','Ernst','BERNST','590.423.4568','IT_PROG',6000.00,NULL,103,60,'1992-04-03 00:00:00'),(105,'David','Austin','DAUSTIN','590.423.4569','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(106,'Valli','Pataballa','VPATABAL','590.423.4560','IT_PROG',4800.00,NULL,103,60,'1998-03-03 00:00:00'),(107,'Diana','Lorentz','DLORENTZ','590.423.5567','IT_PROG',4200.00,NULL,103,60,'1998-03-03 00:00:00'),(108,'Nancy','Greenberg','NGREENBE','515.124.4569','FI_MGR',12000.00,NULL,101,100,'1998-03-03 00:00:00'),(109,'Daniel','Faviet','DFAVIET','515.124.4169','FI_ACCOUNT',9000.00,NULL,108,100,'1998-03-03 00:00:00'),(110,'John','Chen','JCHEN','515.124.4269','FI_ACCOUNT',8200.00,NULL,108,100,'2000-09-09 00:00:00'),(111,'Ismael','Sciarra','ISCIARRA','515.124.4369','FI_ACCOUNT',7700.00,NULL,108,100,'2000-09-09 00:00:00'),(112,'Jose Manuel','Urman','JMURMAN','515.124.4469','FI_ACCOUNT',7800.00,NULL,108,100,'2000-09-09 00:00:00'),(113,'Luis','Popp','LPOPP','515.124.4567','FI_ACCOUNT',6900.00,NULL,108,100,'2000-09-09 00:00:00'),(114,'Den','Raphaely','DRAPHEAL','515.127.4561','PU_MAN',11000.00,NULL,100,30,'2000-09-09 00:00:00'),(115,'Alexander','Khoo','AKHOO','515.127.4562','PU_CLERK',3100.00,NULL,114,30,'2000-09-09 00:00:00'),(116,'Shelli','Baida','SBAIDA','515.127.4563','PU_CLERK',2900.00,NULL,114,30,'2000-09-09 00:00:00'),(117,'Sigal','Tobias','STOBIAS','515.127.4564','PU_CLERK',2800.00,NULL,114,30,'2000-09-09 00:00:00'),(118,'Guy','Himuro','GHIMURO','515.127.4565','PU_CLERK',2600.00,NULL,114,30,'2000-09-09 00:00:00'),(119,'Karen','Colmenares','KCOLMENA','515.127.4566','PU_CLERK',2500.00,NULL,114,30,'2000-09-09 00:00:00'),(120,'Matthew','Weiss','MWEISS','650.123.1234','ST_MAN',8000.00,NULL,100,50,'2004-02-06 00:00:00'),(121,'Adam','Fripp','AFRIPP','650.123.2234','ST_MAN',8200.00,NULL,100,50,'2004-02-06 00:00:00'),(122,'Payam','Kaufling','PKAUFLIN','650.123.3234','ST_MAN',7900.00,NULL,100,50,'2004-02-06 00:00:00'),(123,'Shanta','Vollman','SVOLLMAN','650.123.4234','ST_MAN',6500.00,NULL,100,50,'2004-02-06 00:00:00'),(124,'Kevin','Mourgos','KMOURGOS','650.123.5234','ST_MAN',5800.00,NULL,100,50,'2004-02-06 00:00:00'),(125,'Julia','Nayer','JNAYER','650.124.1214','ST_CLERK',3200.00,NULL,120,50,'2004-02-06 00:00:00'),(126,'Irene','Mikkilineni','IMIKKILI','650.124.1224','ST_CLERK',2700.00,NULL,120,50,'2004-02-06 00:00:00'),(127,'James','Landry','JLANDRY','650.124.1334','ST_CLERK',2400.00,NULL,120,50,'2004-02-06 00:00:00'),(128,'Steven','Markle','SMARKLE','650.124.1434','ST_CLERK',2200.00,NULL,120,50,'2004-02-06 00:00:00'),(129,'Laura','Bissot','LBISSOT','650.124.5234','ST_CLERK',3300.00,NULL,121,50,'2004-02-06 00:00:00'),(130,'Mozhe','Atkinson','MATKINSO','650.124.6234','ST_CLERK',2800.00,NULL,121,50,'2004-02-06 00:00:00'),(131,'James','Marlow','JAMRLOW','650.124.7234','ST_CLERK',2500.00,NULL,121,50,'2004-02-06 00:00:00'),(132,'TJ','Olson','TJOLSON','650.124.8234','ST_CLERK',2100.00,NULL,121,50,'2004-02-06 00:00:00'),(133,'Jason','Mallin','JMALLIN','650.127.1934','ST_CLERK',3300.00,NULL,122,50,'2004-02-06 00:00:00'),(134,'Michael','Rogers','MROGERS','650.127.1834','ST_CLERK',2900.00,NULL,122,50,'2002-12-23 00:00:00'),(135,'Ki','Gee','KGEE','650.127.1734','ST_CLERK',2400.00,NULL,122,50,'2002-12-23 00:00:00'),(136,'Hazel','Philtanker','HPHILTAN','650.127.1634','ST_CLERK',2200.00,NULL,122,50,'2002-12-23 00:00:00'),(137,'Renske','Ladwig','RLADWIG','650.121.1234','ST_CLERK',3600.00,NULL,123,50,'2002-12-23 00:00:00'),(138,'Stephen','Stiles','SSTILES','650.121.2034','ST_CLERK',3200.00,NULL,123,50,'2002-12-23 00:00:00'),(139,'John','Seo','JSEO','650.121.2019','ST_CLERK',2700.00,NULL,123,50,'2002-12-23 00:00:00'),(140,'Joshua','Patel','JPATEL','650.121.1834','ST_CLERK',2500.00,NULL,123,50,'2002-12-23 00:00:00'),(141,'Trenna','Rajs','TRAJS','650.121.8009','ST_CLERK',3500.00,NULL,124,50,'2002-12-23 00:00:00'),(142,'Curtis','Davies','CDAVIES','650.121.2994','ST_CLERK',3100.00,NULL,124,50,'2002-12-23 00:00:00'),(143,'Randall','Matos','RMATOS','650.121.2874','ST_CLERK',2600.00,NULL,124,50,'2002-12-23 00:00:00'),(144,'Peter','Vargas','PVARGAS','650.121.2004','ST_CLERK',2500.00,NULL,124,50,'2002-12-23 00:00:00'),(145,'John','Russell','JRUSSEL','011.44.1344.429268','SA_MAN',14000.00,0.40,100,80,'2002-12-23 00:00:00'),(146,'Karen','Partners','KPARTNER','011.44.1344.467268','SA_MAN',13500.00,0.30,100,80,'2002-12-23 00:00:00'),(147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278','SA_MAN',12000.00,0.30,100,80,'2002-12-23 00:00:00'),(148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268','SA_MAN',11000.00,0.30,100,80,'2002-12-23 00:00:00'),(149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018','SA_MAN',10500.00,0.20,100,80,'2002-12-23 00:00:00'),(150,'Peter','Tucker','PTUCKER','011.44.1344.129268','SA_REP',10000.00,0.30,145,80,'2014-03-05 00:00:00'),(151,'David','Bernstein','DBERNSTE','011.44.1344.345268','SA_REP',9500.00,0.25,145,80,'2014-03-05 00:00:00'),(152,'Peter','Hall','PHALL','011.44.1344.478968','SA_REP',9000.00,0.25,145,80,'2014-03-05 00:00:00'),(153,'Christopher','Olsen','COLSEN','011.44.1344.498718','SA_REP',8000.00,0.20,145,80,'2014-03-05 00:00:00'),(154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668','SA_REP',7500.00,0.20,145,80,'2014-03-05 00:00:00'),(155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508','SA_REP',7000.00,0.15,145,80,'2014-03-05 00:00:00'),(156,'Janette','K_ing','JKING','011.44.1345.429268','SA_REP',10000.00,0.35,146,80,'2014-03-05 00:00:00'),(157,'Patrick','Sully','PSULLY','011.44.1345.929268','SA_REP',9500.00,0.35,146,80,'2014-03-05 00:00:00'),(158,'Allan','McEwen','AMCEWEN','011.44.1345.829268','SA_REP',9000.00,0.35,146,80,'2014-03-05 00:00:00'),(159,'Lindsey','Smith','LSMITH','011.44.1345.729268','SA_REP',8000.00,0.30,146,80,'2014-03-05 00:00:00'),(160,'Louise','Doran','LDORAN','011.44.1345.629268','SA_REP',7500.00,0.30,146,80,'2014-03-05 00:00:00'),(161,'Sarath','Sewall','SSEWALL','011.44.1345.529268','SA_REP',7000.00,0.25,146,80,'2014-03-05 00:00:00'),(162,'Clara','Vishney','CVISHNEY','011.44.1346.129268','SA_REP',10500.00,0.25,147,80,'2014-03-05 00:00:00'),(163,'Danielle','Greene','DGREENE','011.44.1346.229268','SA_REP',9500.00,0.15,147,80,'2014-03-05 00:00:00'),(164,'Mattea','Marvins','MMARVINS','011.44.1346.329268','SA_REP',7200.00,0.10,147,80,'2014-03-05 00:00:00'),(165,'David','Lee','DLEE','011.44.1346.529268','SA_REP',6800.00,0.10,147,80,'2014-03-05 00:00:00'),(166,'Sundar','Ande','SANDE','011.44.1346.629268','SA_REP',6400.00,0.10,147,80,'2014-03-05 00:00:00'),(167,'Amit','Banda','ABANDA','011.44.1346.729268','SA_REP',6200.00,0.10,147,80,'2014-03-05 00:00:00'),(168,'Lisa','Ozer','LOZER','011.44.1343.929268','SA_REP',11500.00,0.25,148,80,'2014-03-05 00:00:00'),(169,'Harrison','Bloom','HBLOOM','011.44.1343.829268','SA_REP',10000.00,0.20,148,80,'2014-03-05 00:00:00'),(170,'Tayler','Fox','TFOX','011.44.1343.729268','SA_REP',9600.00,0.20,148,80,'2014-03-05 00:00:00'),(171,'William','Smith','WSMITH','011.44.1343.629268','SA_REP',7400.00,0.15,148,80,'2014-03-05 00:00:00'),(172,'Elizabeth','Bates','EBATES','011.44.1343.529268','SA_REP',7300.00,0.15,148,80,'2014-03-05 00:00:00'),(173,'Sundita','Kumar','SKUMAR','011.44.1343.329268','SA_REP',6100.00,0.10,148,80,'2014-03-05 00:00:00'),(174,'Ellen','Abel','EABEL','011.44.1644.429267','SA_REP',11000.00,0.30,149,80,'2014-03-05 00:00:00'),(175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266','SA_REP',8800.00,0.25,149,80,'2014-03-05 00:00:00'),(176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265','SA_REP',8600.00,0.20,149,80,'2014-03-05 00:00:00'),(177,'Jack','Livingston','JLIVINGS','011.44.1644.429264','SA_REP',8400.00,0.20,149,80,'2014-03-05 00:00:00'),(178,'Kimberely','Grant','KGRANT','011.44.1644.429263','SA_REP',7000.00,0.15,149,NULL,'2014-03-05 00:00:00'),(179,'Charles','Johnson','CJOHNSON','011.44.1644.429262','SA_REP',6200.00,0.10,149,80,'2014-03-05 00:00:00'),(180,'Winston','Taylor','WTAYLOR','650.507.9876','SH_CLERK',3200.00,NULL,120,50,'2014-03-05 00:00:00'),(181,'Jean','Fleaur','JFLEAUR','650.507.9877','SH_CLERK',3100.00,NULL,120,50,'2014-03-05 00:00:00'),(182,'Martha','Sullivan','MSULLIVA','650.507.9878','SH_CLERK',2500.00,NULL,120,50,'2014-03-05 00:00:00'),(183,'Girard','Geoni','GGEONI','650.507.9879','SH_CLERK',2800.00,NULL,120,50,'2014-03-05 00:00:00'),(184,'Nandita','Sarchand','NSARCHAN','650.509.1876','SH_CLERK',4200.00,NULL,121,50,'2014-03-05 00:00:00'),(185,'Alexis','Bull','ABULL','650.509.2876','SH_CLERK',4100.00,NULL,121,50,'2014-03-05 00:00:00'),(186,'Julia','Dellinger','JDELLING','650.509.3876','SH_CLERK',3400.00,NULL,121,50,'2014-03-05 00:00:00'),(187,'Anthony','Cabrio','ACABRIO','650.509.4876','SH_CLERK',3000.00,NULL,121,50,'2014-03-05 00:00:00'),(188,'Kelly','Chung','KCHUNG','650.505.1876','SH_CLERK',3800.00,NULL,122,50,'2014-03-05 00:00:00'),(189,'Jennifer','Dilly','JDILLY','650.505.2876','SH_CLERK',3600.00,NULL,122,50,'2014-03-05 00:00:00'),(190,'Timothy','Gates','TGATES','650.505.3876','SH_CLERK',2900.00,NULL,122,50,'2014-03-05 00:00:00'),(191,'Randall','Perkins','RPERKINS','650.505.4876','SH_CLERK',2500.00,NULL,122,50,'2014-03-05 00:00:00'),(192,'Sarah','Bell','SBELL','650.501.1876','SH_CLERK',4000.00,NULL,123,50,'2014-03-05 00:00:00'),(193,'Britney','Everett','BEVERETT','650.501.2876','SH_CLERK',3900.00,NULL,123,50,'2014-03-05 00:00:00'),(194,'Samuel','McCain','SMCCAIN','650.501.3876','SH_CLERK',3200.00,NULL,123,50,'2014-03-05 00:00:00'),(195,'Vance','Jones','VJONES','650.501.4876','SH_CLERK',2800.00,NULL,123,50,'2014-03-05 00:00:00'),(196,'Alana','Walsh','AWALSH','650.507.9811','SH_CLERK',3100.00,NULL,124,50,'2014-03-05 00:00:00'),(197,'Kevin','Feeney','KFEENEY','650.507.9822','SH_CLERK',3000.00,NULL,124,50,'2014-03-05 00:00:00'),(198,'Donald','OConnell','DOCONNEL','650.507.9833','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(199,'Douglas','Grant','DGRANT','650.507.9844','SH_CLERK',2600.00,NULL,124,50,'2014-03-05 00:00:00'),(200,'Jennifer','Whalen','JWHALEN','515.123.4444','AD_ASST',4400.00,NULL,101,10,'2016-03-03 00:00:00'),(201,'Michael','Hartstein','MHARTSTE','515.123.5555','MK_MAN',13000.00,NULL,100,20,'2016-03-03 00:00:00'),(202,'Pat','Fay','PFAY','603.123.6666','MK_REP',6000.00,NULL,201,20,'2016-03-03 00:00:00'),(203,'Susan','Mavris','SMAVRIS','515.123.7777','HR_REP',6500.00,NULL,101,40,'2016-03-03 00:00:00'),(204,'Hermann','Baer','HBAER','515.123.8888','PR_REP',10000.00,NULL,101,70,'2016-03-03 00:00:00'),(205,'Shelley','Higgins','SHIGGINS','515.123.8080','AC_MGR',12000.00,NULL,101,110,'2016-03-03 00:00:00'),(206,'William','Gietz','WGIETZ','515.123.8181','AC_ACCOUNT',8300.00,NULL,205,110,'2016-03-03 00:00:00');
/*Table structure for table `jobs` */
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
`job_id` varchar(10) NOT NULL,
`job_title` varchar(35) DEFAULT NULL,
`min_salary` int(6) DEFAULT NULL,
`max_salary` int(6) DEFAULT NULL,
PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
/*Data for the table `jobs` */
insert into `jobs`(`job_id`,`job_title`,`min_salary`,`max_salary`) values ('AC_ACCOUNT','Public Accountant',4200,9000),('AC_MGR','Accounting Manager',8200,16000),('AD_ASST','Administration Assistant',3000,6000),('AD_PRES','President',20000,40000),('AD_VP','Administration Vice President',15000,30000),('FI_ACCOUNT','Accountant',4200,9000),('FI_MGR','Finance Manager',8200,16000),('HR_REP','Human Resources Representative',4000,9000),('IT_PROG','Programmer',4000,10000),('MK_MAN','Marketing Manager',9000,15000),('MK_REP','Marketing Representative',4000,9000),('PR_REP','Public Relations Representative',4500,10500),('PU_CLERK','Purchasing Clerk',2500,5500),('PU_MAN','Purchasing Manager',8000,15000),('SA_MAN','Sales Manager',10000,20000),('SA_REP','Sales Representative',6000,12000),('SH_CLERK','Shipping Clerk',2500,5500),('ST_CLERK','Stock Clerk',2000,5000),('ST_MAN','Stock Manager',5500,8500);
/*Table structure for table `locations` */
DROP TABLE IF EXISTS `locations`;
CREATE TABLE `locations` (
`location_id` int(11) NOT NULL AUTO_INCREMENT,
`street_address` varchar(40) DEFAULT NULL,
`postal_code` varchar(12) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state_province` varchar(25) DEFAULT NULL,
`country_id` varchar(2) DEFAULT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3201 DEFAULT CHARSET=gb2312;
/*Data for the table `locations` */
insert into `locations`(`location_id`,`street_address`,`postal_code`,`city`,`state_province`,`country_id`) values (1000,'1297 Via Cola di Rie','00989','Roma',NULL,'IT'),(1100,'93091 Calle della Testa','10934','Venice',NULL,'IT'),(1200,'2017 Shinjuku-ku','1689','Tokyo','Tokyo Prefecture','JP'),(1300,'9450 Kamiya-cho','6823','Hiroshima',NULL,'JP'),(1400,'2014 Jabberwocky Rd','26192','Southlake','Texas','US'),(1500,'2011 Interiors Blvd','99236','South San Francisco','California','US'),(1600,'2007 Zagora St','50090','South Brunswick','New Jersey','US'),(1700,'2004 Charade Rd','98199','Seattle','Washington','US'),(1800,'147 Spadina Ave','M5V 2L7','Toronto','Ontario','CA'),(1900,'6092 Boxwood St','YSW 9T2','Whitehorse','Yukon','CA'),(2000,'40-5-12 Laogianggen','190518','Beijing',NULL,'CN'),(2100,'1298 Vileparle (E)','490231','Bombay','Maharashtra','IN'),(2200,'12-98 Victoria Street','2901','Sydney','New South Wales','AU'),(2300,'198 Clementi North','540198','Singapore',NULL,'SG'),(2400,'8204 Arthur St',NULL,'London',NULL,'UK'),(2500,'Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK'),(2600,'9702 Chester Road','09629850293','Stretford','Manchester','UK'),(2700,'Schwanthalerstr. 7031','80925','Munich','Bavaria','DE'),(2800,'Rua Frei Caneca 1360 ','01307-002','Sao Paulo','Sao Paulo','BR'),(2900,'20 Rue des Corps-Saints','1730','Geneva','Geneve','CH'),(3000,'Murtenstrasse 921','3095','Bern','BE','CH'),(3100,'Pieter Breughelstraat 837','3029SK','Utrecht','Utrecht','NL'),(3200,'Mariano Escobedo 9991','11932','Mexico City','Distrito Federal,','MX');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
十、多表查询所需要的数据sql
MySQL - 5.7.18-log : Database - girls
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `girls`;
/*Table structure for table `admin` */
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `admin` */
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
/*Table structure for table `beauty` */
DROP TABLE IF EXISTS `beauty`;
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=13 DEFAULT CHARSET=utf8;
/*Data for the table `beauty` */
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','1820987
6577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
/*Table structure for table `boys` */
DROP TABLE IF EXISTS `boys`;
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;
/*Data for the table `boys` */
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;