English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

  1. Posizione attuale
  2. Pagina iniziale
  3. Appunti di esperienza
  4. Corpo del testo

mysql学习笔记之表的基本操作

Crea tabella

create table nome_tabella

create table if not exists nome_tabella

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

Mostra tutte le tabelle nel database corrente

show tables;

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

Visualizza la struttura della tabella

describe nome_tabella

Sintetico

desc nome_tabella

mysql> describe t_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)   | YES |   | NULL  |    |
| dname | varchar(20) | YES |   | NULL  |    |
| loc  | varchar(40) | YES |   | NULL  |    |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Visualizza dettagli della tabella

show create table nome_tabella

mysql> show create table t_dept;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Tabella | Crea Tabella                                                                            |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_dept | CREATE TABLE `t_dept` (
 `deptno` int(11) DEFAULT NULL,
 `dname` varchar(20) DEFAULT NULL,
 `loc` varchar(40) DEFAULT NULL
) MOTOR=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. riga ***************************
    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 table

drop table 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 原表名
new_table_name 新表名
Rename 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;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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, which is default at the end
ALTER TABLE table_name ADD 属性名 属性类型

Add a field descri varchar(20) to tab_dept

mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe influenzate (0.33 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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)

In the first position of the table, add a field

ALTER TABLE table_name ADD 属性名 属性类型 first

mysql> alter table tab_dept add id int first;
Query OK, 0 rows affected (0.38 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Aggiungere un campo specifico dopo il campo della tabella

ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名

mysql> alter table tab_dept add comm varchar(20) after dname;
Query OK, 0 rows affected (0.31 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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)

Eliminazione del campo

ALTER TABLE table_name DROP 属性名

mysql> alter table tab_dept drop comm;
Query OK, 0 rows affected (0.32 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Modifica del campo - Modifica il tipo di dati del campo
ALTER TABLE table_name MODIFY 属性名 数据类型

mysql> alter table tab_dept modify descri int;
Query OK, 0 rows affected (0.45 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Modifica del campo - Modifica il nome del campo

ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型

mysql> alter table tab_dept change id deptid int;
Query OK, 0 rows affected (0.07 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Modifica del campo - Modifica sia il nome del campo che il tipo di dati

ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型

mysql> alter table tab_dept change deptid id varchar(32);
Query OK, 0 rows affected (0.49 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Modifica dell'ordine

ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2

2 attributi devono esistere
Portare deptno alla prima posizione

mysql> alter table tab_dept modify deptno int first;
Query OK, 0 righe influenzate (0.33 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)

Metti l'ID all'ultimo

mysql> alter table tab_dept modify deptno int after descri;
Query OK, 0 righe influenzate (0.29 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)
mysql> alter table tab_dept modify deptno int first;
Query OK, 0 righe influenzate (0.34 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> alter table tab_dept modify id int after descri;
Query OK, 0 righe influenzate (0.47 sec)
Record: 0 Duplicati: 0 Avvisi: 0
mysql> desc tab_dept;
+--------+-------------+------+-----+---------+-------+
| Campo | Tipo    | Null | Chiave | Predefinito | 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 righe nel set (0.00 sec)
教程基础
猜你喜欢