mysql入门(十六)

来源：互联网发布：廖python 编辑：程序博客网时间：2024/05/18 03:12

1:复制表（将查询结果作为一张表复制给另一张表)

create table emp1 as select * from emp;
Query OK, 14 rows affected (1.00 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> select * from emp1;
+-------+---------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+---------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 2017-05-13 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 2017-05-13 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 2017-05-13 | 2975.00 | NULL | 20 |
| 7654 | MARTIM | SALESMAN | 7698 | 2017-05-13 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 2017-05-13 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGERAN | 7839 | 2017-05-13 | 2450.00 | NULL | 10 |
| 7788 | SCOLL | ANALIST | 7566 | 2017-05-13 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 2017-05-13 | 5000.00 | NULL | 10 |
| 7844 | IUSRNER | SALESMAN | 7698 | 2017-05-13 | 1500.00 | NULL | 30 |
| 7876 | ADAMS | CLECK | 7788 | 2017-05-13 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLECK | 7698 | 2017-05-13 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 2017-05-13 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERY | 7782 | 2017-05-13 | 1300.00 | NULL | 10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

2:将查询结果作为一张表复制到创建表中

create table emp2 as select empno,ename,sal from emp;
Query OK, 14 rows affected (0.51 sec)
Records: 14 Duplicates: 0 Warnings: 0

mysql> select * from emp2;
+-------+---------+---------+
| empno | ename | sal |
+-------+---------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIM | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOLL | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | IUSRNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
+-------+---------+---------+
14 rows in set (0.00 sec)

3：将查询结果作为一张表插入到指定表中

insert into emp2 select * from emp2 where sal=3000;
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from emp2;
+-------+---------+---------+
| empno | ename | sal |
+-------+---------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7566 | JONES | 2975.00 |
| 7654 | MARTIM | 1250.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOLL | 3000.00 |
| 7839 | KING | 5000.00 |
| 7844 | IUSRNER | 1500.00 |
| 7876 | ADAMS | 1100.00 |
| 7900 | JAMES | 950.00 |
| 7902 | FORD | 3000.00 |
| 7934 | MILLER | 1300.00 |
| 7788 | SCOLL | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+---------+---------+
16 rows in set (0.00 sec)

阅读全文

0 0