在线免费视频网站推广,网络安全服务机构,嵌入式软件开发工资,做类似淘宝的网站前景我们在前面曾构建过三个用于实验的表格#xff0c;下面将基于这三个表进行实践。
# 建立一个用于实验的三个表格
mysql create table emp (- empno varchar(10),- ename varchar(50),- job varchar(50),- mgr int,- hiredate timestamp,-下面将基于这三个表进行实践。
# 建立一个用于实验的三个表格
mysql create table emp (- empno varchar(10),- ename varchar(50),- job varchar(50),- mgr int,- hiredate timestamp,- sal decimal(10, 2),- comm decimal(10, 2),- deptno int- );
Query OK, 0 rows affected (0.03 sec)mysql insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values- (007369, smith, clerk, 7902, 1980-12-17 00:00:00, 800.00, null, 20),- (007499, allen, salesman, 7698, 1981-02-20 00:00:00, 1600.00, 300.00, 30),- (007521, ward, salesman, 7698, 1981-02-22 00:00:00, 1250.00, 500.00, 30),- (007566, jones, manager, 7839, 1981-04-02 00:00:00, 2975.00, null, 20),- (007654, martin, salesman, 7698, 1981-09-28 00:00:00, 1250.00, 1400.00, 30),- (007698, blake, manager, 7839, 1981-05-01 00:00:00, 2850.00, null, 30),- (007782, clark, manager, 7839, 1981-06-09 00:00:00, 2450.00, null, 10),- (007788, scott, analyst, 7566, 1987-04-19 00:00:00, 3000.00, null, 20),- (007839, king, president, null, 1981-11-17 00:00:00, 5000.00, null, 10),- (007844, turner, salesman, 7698, 1981-09-08 00:00:00, 1500.00, 0.00, 30),- (007876, adams, clerk, 7788, 1987-05-23 00:00:00, 1100.00, null, 20),- (007900, james, clerk, 7698, 1981-12-03 00:00:00, 950.00, null, 30),- (007902, ford, analyst, 7566, 1981-12-03 00:00:00, 3000.00, null, 20),- (007934, miller, clerk, 7782, 1982-01-23 00:00:00, 1300.00, null, 10);
Query OK, 14 rows affected (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0mysql create table dept (- deptno int,- dname varchar(50),- loc varchar(50)- );
Query OK, 0 rows affected (0.03 sec)mysql insert into dept (deptno, dname, loc) values- (10, accounting, new york),- (20, research, dallas),- (30, sales, chicago),- (40, operations, boston);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql create table salgrade (- grade int,- losal int,- hisal int- );
Query OK, 0 rows affected (0.03 sec)mysql insert into salgrade (grade, losal, hisal) values- (1, 700, 1200),- (2, 1201, 1400),- (3, 1401, 2000),- (4, 2001, 3000),- (5, 3001, 9999);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql select * from emp;
--------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
--------------------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select * from dept;
------------------------------
| deptno | dname | loc |
------------------------------
| 10 | accounting | new york |
| 20 | research | dallas |
| 30 | sales | chicago |
| 40 | operations | boston |
------------------------------
4 rows in set (0.00 sec)mysql select * from salgrade;
---------------------
| grade | losal | hisal |
---------------------
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
---------------------
5 rows in set (0.00 sec)还需要使得表之间有联系。
# 关联表格
# 设置主键
mysql alter table dept modify column deptno int not null, add primary key (deptno);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0# 添加外键
mysql alter table emp add foreign key (deptno) references dept(deptno);
Query OK, 14 rows affected (0.08 sec)
Records: 14 Duplicates: 0 Warnings: 0# 查看设置
mysql desc emp;
------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------------------------------------
| empno | varchar(10) | YES | | NULL | |
| ename | varchar(50) | YES | | NULL | |
| job | varchar(50) | YES | | NULL | |
| mgr | int(11) | YES | | NULL | |
| hiredate | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sal | decimal(10,2) | YES | | NULL | |
| comm | decimal(10,2) | YES | | NULL | |
| deptno | int(11) | YES | MUL | NULL | |
------------------------------------------------------------------------------------
8 rows in set (0.00 sec)mysql desc dept;
------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------
| deptno | int(11) | NO | PRI | NULL | |
| dname | varchar(50) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
------------------------------------------------
3 rows in set (0.00 sec)1.单表查询 查询工资高于 1000 或岗位为 manager 的雇员同时还要满足他们的姓名首字母为 j # 需求 1
mysql select * from emp where ((sal1000 or jobmanager) and left(ename, 1)j);
--------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
--------------------------------------------------------------------------
1 row in set (0.00 sec)select * from EMP where (sal500 or jobMANAGER) and ename like J%;
mysql select * from emp where (sal1000 or jobmanager) and ename like J%;;
--------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
--------------------------------------------------------------------------
1 row in set (0.00 sec)按照部门号升序、雇员的工资降序来排序 # 需求 2
mysql select sal, deptno, ename from emp order by deptno asc, sal desc;
-------------------------
| sal | deptno | ename |
-------------------------
| 5000.00 | 10 | king |
| 2450.00 | 10 | clark |
| 1300.00 | 10 | miller |
| 3000.00 | 20 | scott |
| 3000.00 | 20 | ford |
| 2975.00 | 20 | jones |
| 1100.00 | 20 | adams |
| 800.00 | 20 | smith |
| 2850.00 | 30 | blake |
| 1600.00 | 30 | allen |
| 1500.00 | 30 | turner |
| 1250.00 | 30 | ward |
| 1250.00 | 30 | martin |
| 950.00 | 30 | james |
-------------------------
14 rows in set (0.00 sec)使用年薪年薪月薪*12奖金进行降序排序 # 需求 3
mysql select ename, sal*12ifnull(comm,0) as 年薪 from emp order by 年薪 desc;
------------------
| ename | 年薪 |
------------------
| smith | 9600.00 |
| allen | 19500.00 |
| ward | 15500.00 |
| jones | 35700.00 |
| martin | 16400.00 |
| blake | 34200.00 |
| clark | 29400.00 |
| scott | 36000.00 |
| king | 60000.00 |
| turner | 18000.00 |
| adams | 13200.00 |
| james | 11400.00 |
| ford | 36000.00 |
| miller | 15600.00 |
------------------
14 rows in set (0.00 sec)显示工资最高的员工的名字和工作岗位 # 需求 4
mysql select max(sal) from emp;
----------
| max(sal) |
----------
| 5000.00 |
----------
1 row in set (0.00 sec)mysql select ename, job from emp where sal5000;
------------------
| ename | job |
------------------
| king | president |
------------------
1 row in set (0.00 sec)mysql select ename, job from emp where sal(select max(sal) from emp); # 复合查找也叫“查找子句”
------------------
| ename | job |
------------------
| king | president |
------------------
1 row in set (0.01 sec)显示工资高于平均工资的员工信息 # 需求 5
mysql select * from emp where sal (select avg(sal) from emp);
----------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
----------------------------------------------------------------------------
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
----------------------------------------------------------------------------
6 rows in set (0.00 sec)显示每个部门的平均工资和最高工资 # 需求 6
mysql select deptno, avg(sal), max(sal) from emp group by deptno;
-------------------------------
| deptno | avg(sal) | max(sal) |
-------------------------------
| 10 | 2916.666667 | 5000.00 |
| 20 | 2175.000000 | 3000.00 |
| 30 | 1566.666667 | 2850.00 |
-------------------------------
3 rows in set (0.00 sec)显示平均工资低于 2000 的部门号和它的平均工资 # 需求 7
mysql select deptno, avg(sal) 平均工资 from emp group by deptno having 平均工资2000;
----------------------
| deptno | 平均工资 |
----------------------
| 30 | 1566.666667 |
----------------------
1 row in set (0.01 sec)显示每种岗位的雇员总数平均工资 # 需求 8
mysql select job, format(avg(sal), 2) 平均工资, count(*) 人数 from emp group by job;
---------------------------------
| job | 平均工资 | 人数 |
---------------------------------
| analyst | 3,000.00 | 2 |
| clerk | 1,037.50 | 4 |
| manager | 2,758.33 | 3 |
| president | 5,000.00 | 1 |
| salesman | 1,400.00 | 4 |
---------------------------------
5 rows in set (0.00 sec)2.多表查询 显示雇员名、雇员工资以及所在部门的名字 # 需求 1
# 将表合外表整合为一个表
mysql select * from emp, dept where emp.deptnodept.deptno;
--------------------------------------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc |
--------------------------------------------------------------------------------------------------------------
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 10 | accounting | new york |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 10 | accounting | new york |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 10 | accounting | new york |
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 20 | research | dallas |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 20 | research | dallas |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 20 | research | dallas |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 20 | research | dallas |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 20 | research | dallas |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 30 | sales | chicago |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 30 | sales | chicago |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 30 | sales | chicago |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 30 | sales | chicago |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 30 | sales | chicago |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 30 | sales | chicago |
--------------------------------------------------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select emp.ename sal dname from emp, dept where emp.deptnodept.deptno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near dname from emp, dept where emp.deptnodept.deptno at line 1
mysql select emp.ename sal, dname, from emp, dept where emp.deptnodept.deptno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near from emp, dept where emp.deptnodept.deptno at line 1
mysql select emp.ename, sal, dname from emp, dept where emp.deptnodept.deptno;
-----------------------------
| ename | sal | dname |
-----------------------------
| clark | 2450.00 | accounting |
| king | 5000.00 | accounting |
| miller | 1300.00 | accounting |
| smith | 800.00 | research |
| jones | 2975.00 | research |
| scott | 3000.00 | research |
| adams | 1100.00 | research |
| ford | 3000.00 | research |
| allen | 1600.00 | sales |
| ward | 1250.00 | sales |
| martin | 1250.00 | sales |
| blake | 2850.00 | sales |
| turner | 1500.00 | sales |
| james | 950.00 | sales |
-----------------------------
14 rows in set (0.00 sec)显示部门号为 10 的部门名员工名和工资 # 需求 2
mysql select dept.dname ,emp.ename, emp.sal from emp, dept where emp.deptnodept.deptno and emp.deptno10;
-----------------------------
| dname | ename | sal |
-----------------------------
| accounting | clark | 2450.00 |
| accounting | king | 5000.00 |
| accounting | miller | 1300.00 |
-----------------------------
3 rows in set (0.00 sec)显示各个员工的姓名工资及工资级别 # 需求 3
# (1)查看工资等级
mysql select * from salgrade;
---------------------
| grade | losal | hisal |
---------------------
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
---------------------
5 rows in set (0.00 sec)# (2)查看员工信息表
mysql select * from emp;
--------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
--------------------------------------------------------------------------------
14 rows in set (0.00 sec)# (3)整合两表求笛卡尔积
mysql select * from emp, salgrade;
-----------------------------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal |
-----------------------------------------------------------------------------------------------------
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 1 | 700 | 1200 |
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 2 | 1201 | 1400 |
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 3 | 1401 | 2000 |
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 4 | 2001 | 3000 |
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | 5 | 3001 | 9999 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 1 | 700 | 1200 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 2 | 1201 | 1400 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 3 | 1401 | 2000 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 4 | 2001 | 3000 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | 5 | 3001 | 9999 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 1 | 700 | 1200 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 2 | 1201 | 1400 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 3 | 1401 | 2000 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 4 | 2001 | 3000 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | 5 | 3001 | 9999 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 1 | 700 | 1200 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 2 | 1201 | 1400 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 3 | 1401 | 2000 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 4 | 2001 | 3000 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | 5 | 3001 | 9999 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 1 | 700 | 1200 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 2 | 1201 | 1400 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 3 | 1401 | 2000 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 4 | 2001 | 3000 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | 5 | 3001 | 9999 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 1 | 700 | 1200 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 2 | 1201 | 1400 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 3 | 1401 | 2000 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 4 | 2001 | 3000 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | 5 | 3001 | 9999 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 1 | 700 | 1200 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 2 | 1201 | 1400 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 3 | 1401 | 2000 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 4 | 2001 | 3000 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | 5 | 3001 | 9999 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 1 | 700 | 1200 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 2 | 1201 | 1400 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 3 | 1401 | 2000 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | 5 | 3001 | 9999 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 1 | 700 | 1200 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 2 | 1201 | 1400 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 3 | 1401 | 2000 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 4 | 2001 | 3000 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | 5 | 3001 | 9999 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 1 | 700 | 1200 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 2 | 1201 | 1400 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 4 | 2001 | 3000 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | 5 | 3001 | 9999 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 1 | 700 | 1200 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 2 | 1201 | 1400 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 3 | 1401 | 2000 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 4 | 2001 | 3000 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | 5 | 3001 | 9999 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 1 | 700 | 1200 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 2 | 1201 | 1400 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 3 | 1401 | 2000 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 4 | 2001 | 3000 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | 5 | 3001 | 9999 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 1 | 700 | 1200 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 2 | 1201 | 1400 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 3 | 1401 | 2000 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | 5 | 3001 | 9999 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 1 | 700 | 1200 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 2 | 1201 | 1400 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 3 | 1401 | 2000 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 4 | 2001 | 3000 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | 5 | 3001 | 9999 |
-----------------------------------------------------------------------------------------------------
70 rows in set (0.00 sec)# (4)列出员工姓名、员工工资、工资对应等级
mysql select emp.ename, emp.sal, salgrade.grade from emp, salgrade where sal between salgrade.losal and salgrade.hisal;
------------------------
| ename | sal | grade |
------------------------
| smith | 800.00 | 1 |
| allen | 1600.00 | 3 |
| ward | 1250.00 | 2 |
| jones | 2975.00 | 4 |
| martin | 1250.00 | 2 |
| blake | 2850.00 | 4 |
| clark | 2450.00 | 4 |
| scott | 3000.00 | 4 |
| king | 5000.00 | 5 |
| turner | 1500.00 | 3 |
| adams | 1100.00 | 1 |
| james | 950.00 | 1 |
| ford | 3000.00 | 4 |
| miller | 1300.00 | 2 |
------------------------
14 rows in set (0.00 sec)补充同一张表也可以进行笛卡尔积也就是“自连接” # 尝试自连接
mysql select * from salgrade as t1, salgrade as t2;
------------------------------------------
| grade | losal | hisal | grade | losal | hisal |
------------------------------------------
| 1 | 700 | 1200 | 1 | 700 | 1200 |
| 2 | 1201 | 1400 | 1 | 700 | 1200 |
| 3 | 1401 | 2000 | 1 | 700 | 1200 |
| 4 | 2001 | 3000 | 1 | 700 | 1200 |
| 5 | 3001 | 9999 | 1 | 700 | 1200 |
| 1 | 700 | 1200 | 2 | 1201 | 1400 |
| 2 | 1201 | 1400 | 2 | 1201 | 1400 |
| 3 | 1401 | 2000 | 2 | 1201 | 1400 |
| 4 | 2001 | 3000 | 2 | 1201 | 1400 |
| 5 | 3001 | 9999 | 2 | 1201 | 1400 |
| 1 | 700 | 1200 | 3 | 1401 | 2000 |
| 2 | 1201 | 1400 | 3 | 1401 | 2000 |
| 3 | 1401 | 2000 | 3 | 1401 | 2000 |
| 4 | 2001 | 3000 | 3 | 1401 | 2000 |
| 5 | 3001 | 9999 | 3 | 1401 | 2000 |
| 1 | 700 | 1200 | 4 | 2001 | 3000 |
| 2 | 1201 | 1400 | 4 | 2001 | 3000 |
| 3 | 1401 | 2000 | 4 | 2001 | 3000 |
| 4 | 2001 | 3000 | 4 | 2001 | 3000 |
| 5 | 3001 | 9999 | 4 | 2001 | 3000 |
| 1 | 700 | 1200 | 5 | 3001 | 9999 |
| 2 | 1201 | 1400 | 5 | 3001 | 9999 |
| 3 | 1401 | 2000 | 5 | 3001 | 9999 |
| 4 | 2001 | 3000 | 5 | 3001 | 9999 |
| 5 | 3001 | 9999 | 5 | 3001 | 9999 |
------------------------------------------
25 rows in set (0.00 sec)mysql select * from salgrade;
---------------------
| grade | losal | hisal |
---------------------
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
---------------------
5 rows in set (0.00 sec)有些情况下是需要自连接的例如“显示员工 ford 的上级领导的编号和姓名” # 尝试寻找上级领导
# (1)查看员工表
mysql select * from emp;
--------------------------------------------------------------------------------
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
--------------------------------------------------------------------------------
| 007369 | smith | clerk | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 007499 | allen | salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 007521 | ward | salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 007566 | jones | manager | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 007654 | martin | salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 007698 | blake | manager | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 007782 | clark | manager | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 007788 | scott | analyst | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 007839 | king | president | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 007844 | turner | salesman | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 007876 | adams | clerk | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 007900 | james | clerk | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 007902 | ford | analyst | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 007934 | miller | clerk | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
--------------------------------------------------------------------------------
14 rows in set (0.00 sec)# (2)查看某个员工的上级领导编号
mysql select mgr from emp where enameford;
------
| mgr |
------
| 7566 |
------
1 row in set (0.00 sec)# (3)查看领导编号对应的领导姓名
mysql select ename, empno from emp where empno7566;
---------------
| ename | empno |
---------------
| jones | 007566 |
---------------
1 row in set (0.00 sec)# (4)子查询做法
mysql select ename, empno from emp where empno(select mgr from emp where enameford);
---------------
| ename | empno |
---------------
| jones | 007566 |
---------------
1 row in set (0.01 sec)# (5)自连接做法
mysql select e2.empno 领导编号, e2.ename 领导名 from emp as e1, emp as e2 where e1.enameford and e1.mgre2.empno;
-------------------------
| 领导编号 | 领导名 |
-------------------------
| 007566 | jones |
-------------------------
1 row in set (0.00 sec)
