Basic operations of mysql learning notes table

Basic operations of mysql learning notes table

Create Table

create table table name

create table if not exists 表名

mysql> create database company;
Query OK, 1 row affected (0.00 sec)
mysql> use company;
Database changed
mysql> 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>

Display 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 structure of the table

describe 表名

Abbreviation

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 表名

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 \G

mysql> show create table t_dept \G
*************************** 1. row ***************************
    Table: t_dept
Create Table: 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)

Delete a table

drop table table name
drop table if exists 表名

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 The new table name changes 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 by default at the end
ALTER TABLE table_name ADD 屬性名屬性類型

Add a field descri varchar(20) for 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 description varchar(20);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | |
| 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 屬性名屬性類型first

mysql> alter table tab_dept add id int first;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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: 0
mysql> 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 a 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: 0
mysql> 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 field data type
ALTER TABLE table_name MODIFY attribute name data type

mysql> alter table tab_dept modify description int;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 field name

ALTER TABLE table_name CHANGE old attribute name new attribute 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: 0
mysql> 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 field name and data type at the same time

ALTER TABLE table_name CHANGE old attribute name new attribute 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: 0
mysql> 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)

Modify the order

ALTER TABLE table_name MODIFY 屬性名1 數據類型FIRST|AFTER 屬性名2

2 attributes must exist. Move 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> alter table tab_dept modify id int after description;
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | |
| descri | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

You may also be interested in:
  • MySQL Learning Notes 3: Introduction to basic table operations
  • Detailed explanation of clearing, deleting and modifying MySQL tables
  • Summary of MySQL table structure modification commands
  • A beginner's guide to common commands for operating MySQL databases and tables
  • MySQL Terminal (dos interface) manages databases, data tables, and basic data operations
  • MySQL create and delete table operation command example explanation
  • Tutorial on copying data from a table to a new table in MySQL

<<:  Detailed explanation of 10 common HTTP status codes

>>:  JavaScript implements the generation of 4-digit random verification code

Recommend

A record of pitfalls in JS regular matching

I recently discovered a pitfall in regular expres...

Element with selection table to change the check box in the header into text implementation code

Method 1: Use table attributes: header-cell-class...

Mysql sql slow query monitoring script code example

1. Modify my.cnf #The overall effect is that both...

How to redirect PC address to mobile address in Vue

Requirements: The PC side and the mobile side are...

Implementation of MySQL5.7 mysqldump backup and recovery

MySQL backup Cold backup:停止服務進行備份，即停止數據庫的寫入Hot ba...

js to achieve simulated shopping mall case

Friends who are learning HTML, CSS and JS front-e...

Summary of basic operations for MySQL beginners

Library Operations Query 1.SHOW DATABASE; ----Que...

Detailed tutorial on how to log in to the system using the root user in the new version of Ubuntu 20.04

Ubuntu 20.04 does not have root login enabled by ...

Solution to ERROR 1054 (42S22) when changing password in MySQL 5.7

I have newly installed MySQL 5.7. When I log in, ...

Detailed explanation of how to use relative paths in HTML to obtain files at all levels of directories

The concept of relative path Use the current file...

Detailed explanation of using new methods of html5 to manipulate element class names in JavaScript

Table of contents 1. classList attribute 2. Pract...

The difference and usage between div and span

Table of contents 1. Differences and characterist...

How to implement real-time polygon refraction with threejs

Table of contents Preface Step 1: Setup and front...

Tic-Tac-toe game implemented in pure CSS3

Operation effect: html <div class="tic-ta...

Details after setting the iframe's src to about:blank

After setting the iframe's src to 'about:b...