Codefreigabe für grundlegende Operationen der MySQL-Lerntabelle

Dieser Artikel stellt Ihnen den Einführungsartikel der MySQL-Studiennotizen-Reihe vor. Er ist sehr detailliert

Tabelle erstellen

Tabellennamen erstellen

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>

Anzeige der aktuellen Datenbank Alle Tabellen

show tables;

Struktur der Tabelle anzeigen

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

describe Tabellenname

describe 表名<br>Abkürzung

desc Tabellenname

Tabellendetails anzeigen

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)

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)

Tabelle löschen

Tabellennamen löschen

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)

Tabellennamen ändern

ALTER TABLE old_table_name RENAME [TO] new_table_name

old_table_name Ursprünglicher Tabellenname

new_table_name Neuer Tabellenname
Ändern Sie t_dept in tab_dept

Fügen Sie eine hinzu Feld zur Tabelle Standard Am Ende

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 attribute name attribute type <p></p>
ALTER TABLE table_name ADD 属性名 属性类型<br>Fügen Sie ein Feld descri varchar(20) zu tab_dept hinzu

Ein Feld an der ersten Position der Tabelle hinzufügen

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: 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)

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)

Fügen Sie ein Feld nach dem angegebenen Feld in der Tabelle hinzu

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)

Feld löschen

ALTER TABLE Tabellenname DROP Attributname

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)

Feldänderung – Felddatentyp ändern

ALTER TABLE Tabellenname MODIFY Attributnamendatentyp

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)

Feldänderung – Feldnamen ändern

ALTER TABLE Tabellenname CHANGE alter Attributname neuer Attributname alter Datentyp

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)

Feldänderung – ändern Sie die Feldname und Datentyp gleichzeitig

ALTER TABLE table_name CHANGE alter Attributname neuer Attributname neuer Datentyp

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)

Änderungsauftrag

ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2
2 Attribute müssen vorhanden sein

Abteilungsnummer an die erste Position anpassen

Fügen Sie den Ausweis am Ende ein

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)

Das obige ist der detaillierte Inhalt vonCodefreigabe für grundlegende Operationen der MySQL-Lerntabelle. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!