Mysql learning notes: basic operations on tables and mysql learning notes

Mysql learning notes: basic operations on tables and mysql learning notes

Create a table

Create table Name

Create table if not exists table name

mysql> create database company;Query OK, 1 row affected (0.00 sec)mysql> use company;Database changedmysql> create table if not exists t_dept(  -> deptno int,  -> dname varchar(20),  -> loc varchar(40));Query OK, 0 rows affected (0.20 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| t_dept      |+-------------------+1 row in set (0.00 sec)mysql>

Show all tables in the current database

show tables;

mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| t_dept      |+-------------------+1 row in set (0.00 sec)

View the table structure

Describe table name


Shorthand

Desc table name

mysql> describe t_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> desc t_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

View table details

Show create table name

mysql> show create table t_dept;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                            |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t_dept | CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)show create table t_dept \Gmysql> show create table t_dept \G*************************** 1. row ***************************    Table: t_deptCreate Table: CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Delete table

Drop table Name
Drop table if exists table name

mysql> drop table if exists t_dept;Query OK, 0 rows affected (0.12 sec)mysql> show tables;Empty set (0.00 sec)

Modify Table Name

Alter table old_table_name RENAME [TO] new_table_name
Old_table_name original table name
New_table_name new table name
Change t_dept to tab_dept.

mysql> alter table t_dept rename tab_dept;Query OK, 0 rows affected (0.09 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| tab_dept     |+-------------------+1 row in set (0.00 sec)mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)

Add a field to the table at the end by default.
Alter table table_name ADD attribute name attribute type


Add a field descri varchar (20) to tab_dept)

mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table tab_dept add descri varchar(20);Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | varchar(20) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

Add a field to the first position of the table.

Alter table table_name ADD attribute name attribute type first

mysql> alter table tab_dept add id int first;Query OK, 0 rows affected (0.38 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | varchar(20) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Add a field after the specified field in the table

Alter table table_name ADD attribute name attribute type AFTER attribute name

mysql> alter table tab_dept add comm varchar(20) after dname;Query OK, 0 rows affected (0.31 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || comm  | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | varchar(20) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)

Delete Field

Alter table table_name DROP attribute name

mysql> alter table tab_dept drop comm;Query OK, 0 rows affected (0.32 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | varchar(20) | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Field modification-Modify the field data type
Alter table table_name MODIFY attribute name Data Type

mysql> alter table tab_dept modify descri int;Query OK, 0 rows affected (0.45 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Field modification-Modify the field name

Alter table table_name CHANGE old property name new property name old data type

mysql> alter table tab_dept change id deptid int;Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptid | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Field modification-Modify the field name and data type at the same time

Alter table table_name CHANGE old property name new property name New Data Type

mysql> alter table tab_dept change deptid id varchar(32);Query OK, 0 rows affected (0.49 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | varchar(32) | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

CHANGE Order

Alter table table_name MODIFY attribute name 1 Data Type FIRST | AFTER attribute name 2

Two attributes must exist.
Adjust deptno to the first position

mysql> alter table tab_dept modify deptno int first;Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || id   | varchar(32) | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

Put the ID at the end

mysql> alter table tab_dept modify deptno int after descri;Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id   | varchar(32) | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    || deptno | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)mysql> alter table tab_dept modify deptno int first;Query OK, 0 rows affected (0.34 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table tab_dept modify id int after descri;Query OK, 0 rows affected (0.47 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11)   | YES |   | NULL  |    || dname | varchar(20) | YES |   | NULL  |    || loc  | varchar(40) | YES |   | NULL  |    || descri | int(11)   | YES |   | NULL  |    || id   | int(11)   | YES |   | NULL  |    |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)