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

The HTML 5 draft did not become a formal standard

<br />Yesterday I saw at W3C that the new HT...

Sample code for converting video using ffmpeg command line

Before starting the main text of this article, yo...

Detailed steps and problem solving methods for installing MySQL 8.0.19 on Linux

I recently bought a Tencent Cloud server and buil...

Introduction to Jenkins and how to deploy Jenkins with Docker

1. Related concepts 1.1 Jenkins Concepts: Jenkins...

React antd tabs switching causes repeated refresh of subcomponents

describe: When the Tabs component switches back a...

mysql 5.7.20 win64 installation and configuration method

mysql-5.7.20-winx64.zipInstallation package witho...

A brief discussion on the $notify points of element

My original intention was to encapsulate the $not...

Detailed explanation of the principle and usage of MySQL views

This article uses examples to illustrate the prin...

Mac installation mysqlclient process analysis

Try installing via pip in a virtual environment: ...

Detailed explanation of command to view log files in Linux environment

Table of contents Preface 1. cat command: 2. more...

How to implement parallel downloading of large files in JavaScript

Table of contents 1. HTTP Range Request 1.1 Range...

HTML form value transfer example through get method

The google.html interface is as shown in the figur...

How to find websites with SQL injection (must read)

Method 1: Use Google advanced search, for example...