mysql學習之表的基本操作的程式碼分享

本文分享給大家的是MySQL學習筆記系列文章的入門篇，主要講述MySQL表的基本操作指令，非常詳細，有需要的小夥伴可以來查看下

#建立表

create table 表名

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>

顯示目前庫下的所有表格<br/>

show tables;

<br/>

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

檢視表的結構<br/>

describe 表名<br/>

#簡單寫<br/>

##desc 表名

<br/>

##

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)

<br/>查看表格的詳細

show create table 表名

<br/>

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)

#刪除表格<br/>

<br/>drop table 表格名稱drop table if exists 表格名稱

<br/>

##

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

修改表名<br/>

ALTER TABLE old_table_name RENAME [TO] new_table_name<br/><br/><br/>ALTER TABLE old_table_name RENAME [TO] new_table_name

old_table_name 原始表名

new_table_name 新表名<br/>將t_dept修改為tab_dept

new_table_name 新表名

將t_dept修改為tab_dept<br/> <br/>#

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)

為表格增加一個欄位預設在最後

ALTER TABLE table_name ADD 屬性名稱屬性類型

<br/>

為tab_dept增加一個字段descri varchar(20)<br/>

<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:bash;">mysql&gt; 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&gt; alter table tab_dept add descri varchar(20); Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql&gt; 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)</pre><div class="contentsignin">登入後複製</div></div>在表的第一個位置增加一個字段

<br/>

ALTER TABLE table_name ADD屬性名稱屬性類型first

<br/>

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)

#在表格的指定欄位之後增加欄位

<br/>

ALTER TABLE table_name ADD 屬性名稱屬性類型AFTER 屬性名稱<br/>

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)

#刪除欄位

<br/>

##ALTER TABLE table_name DROP 屬性名稱

<br/>

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)

欄位修改-修改欄位資料類型<br/>

ALTER TABLE table_name MODIFY 屬性名稱資料類型

<br/>#

mysql> alter table tab_dept modify descri 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)

欄位修改-修改欄位名稱

<br/>

ALTER TABLE table_name CHANGE 舊屬性名稱新屬性名稱舊資料類型

<br/>

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)

欄位修改-同時修改欄位名稱與資料類型

<br/>

ALTER TABLE table_name CHANGE 舊屬性名稱新屬性名稱新資料類型

<br/>#

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)

修改順序<br/>

ALTER TABLE table_name MODIFY 屬性名稱1 資料類型FIRST|AFTER 屬性名稱2<br/>

<br/>#2個屬性必須存在

將deptno調到第一個位置

<br/>#

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)

將ID放在最後######### ###

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 descri;
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)

以上是mysql學習之表的基本操作的程式碼分享的詳細內容。更多資訊請關注PHP中文網其他相關文章！