Rename table in MySQL

RENAME TABLE is used to change the name of table. We must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

Syntax:

RENAME TABLE old_table TO new_table;

1. Create employee table

CREATE TABLE EMP_NEW (EMPNO NUMERIC(4) NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR NUMERIC(4), HIREDATE DATETIME, SAL NUMERIC(7, 2), COMM NUMERIC(7, 2), DEPTNO NUMERIC(2));

mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | EMP | | EMP_NEW | +-------------------+

mysql> describe EMP_NEW; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | |

mysql>mysql> select*from EMP_NEW; +-------+--------+-----------+------+---------------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-28 00:00:00 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+---------------------+---------+---------+--------+

Rename table EMP_NEW to EMP_OLD

mysql>mysql> RENAME TABLE EMP_NEW to EMP_OLD; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | EMP | | EMP_OLD | +-------------------+ 2 rows in set (0.00 sec)

3. Check the structure and data of renamed table. Everything will be same except table.

mysql> describe EMP_OLD; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | EMPNO | decimal(4,0) | NO | | NULL | | | ENAME | varchar(10) | YES | | NULL | | | JOB | varchar(9) | YES | | NULL | | | MGR | decimal(4,0) | YES | | NULL | | | HIREDATE | datetime | YES | | NULL | | | SAL | decimal(7,2) | YES | | NULL | | | COMM | decimal(7,2) | YES | | NULL | | | DEPTNO | decimal(2,0) | YES | | NULL | |

mysql> select*from EMP_OLD; +-------+--------+-----------+------+---------------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+---------------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-28 00:00:00 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+---------------------+---------+---------+--------+

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: