国产亚洲精品福利在线无卡一,国产精久久一区二区三区,亚洲精品无码国模,精品久久久久久无码专区不卡

當(dāng)前位置： 首頁 > news >正文

jsp和php哪個做網(wǎng)站快百度seo是什么意思

news 2025/7/3 6:53:13
jsp和php哪個做網(wǎng)站快,百度seo是什么意思,個人網(wǎng)站做多久有效果,文創(chuàng)產(chǎn)品我們在前面曾構(gòu)建過三個用于實驗的表格，下面將基于這三個表進行實踐。 # 建立一個用于實驗的三個表格 mysql> create table emp (-> empno varchar(10),-> ename varchar(50),-> job varchar(50),-> mgr int,-> hiredate timestamp,-&…

我們在前面曾構(gòu)建過三個用于實驗的表格，下面將基于這三個表進行實踐。

# 建立一個用于實驗的三個表格
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)

還需要使得表之間有聯(lián)系。

# 關(guān)聯(lián)表格
# 設(shè)置主鍵
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# 查看設(shè)置
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.單表查詢

  1. 查詢工資高于 1000 或崗位為 manager 的雇員，同時還要滿足他們的姓名首字母為 j

    # 需求 1
mysql> select * from emp where ((sal>1000 or job='manager') 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 (sal>500 or job='MANAGER') and ename like 'J%';
mysql> select * from emp where (sal>1000 or job='manager') 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. 按照部門號升序、雇員的工資降序來排序

    # 需求 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)

  3. 使用年薪（年薪=月薪*12+獎金）進行降序排序

    # 需求 3
mysql> select ename, sal*12+ifnull(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. 顯示工資最高的員工的名字和工作崗位

    # 需求 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 sal=5000;
+-------+-----------+
| ename | job       |
+-------+-----------+
| king  | president |
+-------+-----------+
1 row in set (0.00 sec)mysql> select ename, job from emp where sal=(select max(sal) from emp); # 復(fù)合查找，也叫“查找子句”
+-------+-----------+
| ename | job       |
+-------+-----------+
| king  | president |
+-------+-----------+
1 row in set (0.01 sec)

  5. 顯示工資高于平均工資的員工信息

    # 需求 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. 顯示每個部門的平均工資和最高工資

    # 需求 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)

  7. 顯示平均工資低于 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. 顯示每種崗位的雇員總數(shù)，平均工資

    # 需求 8
mysql> select job, format(avg(sal), 2) 平均工資, count(*) 人數(shù) from emp group by job;
+-----------+--------------+--------+
| job       | 平均工資     | 人數(shù)   |
+-----------+--------------+--------+
| 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. 顯示雇員名、雇員工資以及所在部門的名字

    # 需求 1
# 將表合外表整合為一個表
mysql> select * from emp, dept where emp.deptno=dept.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.deptno=dept.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.deptno=dept.deptno' at line 1
mysql> select emp.ename sal, dname, from emp, dept where emp.deptno=dept.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.deptno=dept.deptno' at line 1
mysql> select emp.ename, sal, dname from emp, dept where emp.deptno=dept.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)

  2. 顯示部門號為 10 的部門名，員工名和工資

    # 需求 2
mysql> select dept.dname ,emp.ename, emp.sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
+------------+--------+---------+
| dname      | ename  | sal     |
+------------+--------+---------+
| accounting | clark  | 2450.00 |
| accounting | king   | 5000.00 |
| accounting | miller | 1300.00 |
+------------+--------+---------+
3 rows in set (0.00 sec)

  3. 顯示各個員工的姓名，工資，及工資級別

    # 需求 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)列出員工姓名、員工工資、工資對應(yīng)等級
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 的上級領(lǐng)導(dǎo)的編號和姓名”：

# 嘗試尋找上級領(lǐng)導(dǎo)
# (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)查看某個員工的上級領(lǐng)導(dǎo)編號
mysql> select mgr from emp where ename='ford';
+------+
| mgr  |
+------+
| 7566 |
+------+
1 row in set (0.00 sec)# (3)查看領(lǐng)導(dǎo)編號對應(yīng)的領(lǐng)導(dǎo)姓名
mysql> select ename, empno from emp where empno=7566;
+-------+--------+
| 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 ename='ford');
+-------+--------+
| ename | empno  |
+-------+--------+
| jones | 007566 |
+-------+--------+
1 row in set (0.01 sec)# (5)自連接做法
mysql> select e2.empno 領(lǐng)導(dǎo)編號, e2.ename 領(lǐng)導(dǎo)名  from emp as e1, emp as e2 where e1.ename='ford' and e1.mgr=e2.empno;
+--------------+-----------+
| 領(lǐng)導(dǎo)編號     | 領(lǐng)導(dǎo)名    |
+--------------+-----------+
| 007566       | jones     |
+--------------+-----------+
1 row in set (0.00 sec)
http://m.aloenet.com.cn/news/34387.html

相關(guān)文章：

  • 做網(wǎng)站需要多少錢西安優(yōu)化大師官網(wǎng)下載
  • 打開ecshop網(wǎng)站提示內(nèi)容溢出網(wǎng)站的推廣平臺有哪些
  • 廣州疫情防控最新消息淘寶標(biāo)題優(yōu)化網(wǎng)站
  • 廣州app定制公司百度seo收費
  • 只做外貿(mào)的公司網(wǎng)站seo推廣公司價格
  • 公司郵箱價格免費的seo
  • 青島鑫隆建設(shè)集團網(wǎng)站網(wǎng)絡(luò)推廣山東
  • 網(wǎng)站建設(shè)難點網(wǎng)站推廣優(yōu)化設(shè)計方案
  • 蘇州園區(qū)做網(wǎng)站公司seoul是啥意思
  • 貴州安順建設(shè)主管部門網(wǎng)站百度seo網(wǎng)站優(yōu)化
  • 網(wǎng)站建設(shè) 瀏覽器兼容1688的網(wǎng)站特色
  • 營銷型網(wǎng)站建設(shè)一般要多少錢產(chǎn)品網(wǎng)絡(luò)營銷策劃方案
  • 做網(wǎng)站寫需求千萬不要做手游推廣員
  • 誠信通開了網(wǎng)站誰給做精準(zhǔn)的搜索引擎優(yōu)化
  • 網(wǎng)站建設(shè)公司該怎么銷售最新收錄查詢
  • 手機網(wǎng)站如何做新區(qū)快速seo排名
  • 滄州百度愛采購灰色詞seo推廣
  • 企業(yè)網(wǎng)站排名提升軟件能優(yōu)化谷歌排名推廣公司
  • 如何給網(wǎng)站做seo怎么制作網(wǎng)站?
  • 新鄭做網(wǎng)站佛山網(wǎng)站建設(shè)公司
  • 東莞公司注冊代理青島網(wǎng)站seo服務(wù)
  • 政務(wù)服務(wù)網(wǎng)站 建設(shè)方案百度競價開戶渠道
  • css做網(wǎng)站常用網(wǎng)站seo優(yōu)化服務(wù)
  • 微信小程序注冊需要多少錢南昌seo管理
  • 有贊可以做獨立網(wǎng)站嗎百度關(guān)鍵詞推廣多少錢
  • 電子商務(wù)書店網(wǎng)站設(shè)計實驗競價排名的服務(wù)模式是
  • 通用網(wǎng)址查詢網(wǎng)站網(wǎng)絡(luò)營銷項目
  • 胖哥網(wǎng)站的建設(shè)目標(biāo)網(wǎng)絡(luò)廣告投放渠道有哪些
  • 成都網(wǎng)站制作公司湘潭網(wǎng)站設(shè)計
  • 3小時網(wǎng)站建設(shè)平臺seo診斷優(yōu)化方案