西西軟件園多重安全檢測(cè)下載網(wǎng)站、值得信賴的軟件下載站！
軟件
軟件
文章
搜索

首頁(yè) → 西西教程 → 數(shù)據(jù)庫(kù)教程 → MySQL數(shù)據(jù)庫(kù)創(chuàng)建、修改和刪除表操作實(shí)例

MySQL數(shù)據(jù)庫(kù)創(chuàng)建、修改和刪除表操作實(shí)例

相關(guān)軟件相關(guān)文章發(fā)表評(píng)論 來(lái)源：西西整理時(shí)間：2013/9/25 8:36:10字體大�。�A-A+

作者：西西點(diǎn)擊：677次評(píng)論：1次標(biāo)簽： MySQL

  • 類型：數(shù)據(jù)庫(kù)類大�。�1.7M語(yǔ)言：英文 評(píng)分：6.6
  • 標(biāo)簽：
立即下載

其實(shí)對(duì)很多人來(lái)說(shuō)對(duì)于SQL語(yǔ)句已經(jīng)忘了很多，或者說(shuō)是不懂很多，因?yàn)橛?a href="http://www.innovatechautomation.com/k/sql/" target="_blank">數(shù)據(jù)庫(kù)圖形操作軟件，方便了大家，但是我們不能忘記最根本的東西，特別是一些細(xì)節(jié)上的東西，可能你用慣了Hibernate，不用寫(xiě)SQL語(yǔ)句，但是不是任何項(xiàng)目都要用到大框架的，如果不用，那你是不是就不會(huì)操作數(shù)據(jù)庫(kù)了呢，所以我們最好還是熟悉一點(diǎn)好，對(duì)我們以后找工作和工作都有幫助。

在說(shuō)創(chuàng)建、修改和刪除表前，我們還是要進(jìn)行一個(gè)操作的簡(jiǎn)單說(shuō)明：

1.登陸數(shù)據(jù)庫(kù)系統(tǒng)

在命令行中登陸MySQL數(shù)據(jù)庫(kù)管理系統(tǒng)，輸入一下內(nèi)容：

mysql -h localhost -u root -p

很多人都知道這個(gè)，但是其中參數(shù)的具體表示什么我們還是要了解的，其中，“-h”參數(shù)指連接的主機(jī)名，所以后面是localhost；“-u”參數(shù)表示用戶名，此處的用戶名為root；“-p”參數(shù)表示用戶的密碼，按下Enter鍵后就顯示“Enter password：”，輸入密碼即可登錄進(jìn)去了。

2.創(chuàng)建數(shù)據(jù)庫(kù)

在創(chuàng)建數(shù)據(jù)庫(kù)之前，我們可以查看已經(jīng)存在的數(shù)據(jù)庫(kù)：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| community          |
| community_test     |
| data               |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.04 sec)

創(chuàng)建數(shù)據(jù)庫(kù)的格式：CREATE DATABASE 數(shù)據(jù)庫(kù)名;

示例：創(chuàng)建一個(gè)名為example的數(shù)據(jù)庫(kù)

mysql>  CREATE DATABASE example;
Query OK, 1 row affected (0.00 sec)

mysql>  SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| community          |
| community_test     |
| data               |
| example            |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
9 rows in set (0.00 sec)

3.刪除數(shù)據(jù)庫(kù)：

格式：DROP DATABASE 數(shù)據(jù)庫(kù)名;

示例：刪除example數(shù)據(jù)庫(kù)

mysql>  DROP DATABASE example;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| community          |
| community_test     |
| data               |
| mydata             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
8 rows in set (0.00 sec)

4.數(shù)據(jù)庫(kù)存儲(chǔ)引擎

存儲(chǔ)引擎就是指表的類型，數(shù)據(jù)庫(kù)存儲(chǔ)引擎決定了表在計(jì)算機(jī)的存儲(chǔ)方式。

MySQL中查詢存儲(chǔ)引擎的類型命令：SHOW ENGINES;

mysql> SHOW ENGINES;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine             | Support | Comment
                | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine
                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine
                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to
 it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine
                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and f
oreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.00 sec)

查詢結(jié)果中，Engine參數(shù)指存儲(chǔ)引擎名稱；Support參數(shù)說(shuō)明MySQL是否支持該類型引擎；Comment參數(shù)表示對(duì)該引擎的評(píng)論；Transaction參數(shù)表示是否支持事務(wù)處理；XA參數(shù)表示是否分布式交易處理的XA規(guī)范；Savepoints參數(shù)表示是否支持保存點(diǎn)，以方便事務(wù)的回滾操作；由上面我們看到InnoDB存儲(chǔ)引擎是default的，也就是數(shù)據(jù)庫(kù)默認(rèn)的存儲(chǔ)引擎，下面我們簡(jiǎn)單介紹一下InnoDB。

InnoDB是MySQL的一種存儲(chǔ)引擎，InnoDB給MySQL提供了事務(wù)、回滾、崩潰修復(fù)能力和多版本并發(fā)控制的事務(wù)安全。InnoDB是MySQL上第一個(gè)提供外鍵約束的表引擎，而且對(duì)事務(wù)處理的能力，也是其他存儲(chǔ)引擎不能比擬的。不過(guò)這種引擎的缺點(diǎn)就是讀寫(xiě)效率稍差，占用的數(shù)據(jù)空間相對(duì)比較大。

下面就是正式的內(nèi)容：

創(chuàng)建表：

1）創(chuàng)建表的形式：

CREATE TABLE 表名 (
    屬性名 數(shù)據(jù)類型 [完整約束條件],
    屬性名 數(shù)據(jù)類型 [完整約束條件],
    ...
    ...
    屬性名 數(shù)據(jù)類型 [完整約束條件]
);

如果你很急的登陸進(jìn)去就創(chuàng)建表，恭喜你，你會(huì)出現(xiàn)“No database selected”的錯(cuò)誤，因?yàn)槟銢](méi)有告訴別人你要選擇在哪個(gè)數(shù)據(jù)庫(kù)創(chuàng)建表，所以在創(chuàng)建之前要選擇數(shù)據(jù)庫(kù)，格式：USE 數(shù)據(jù)庫(kù)名;

示例創(chuàng)建一個(gè)student表：

mysql> use example;
Database changed
mysql> CREATE TABLE student (
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

上面創(chuàng)建表的時(shí)候涉及到一個(gè)完整性約束條件，下面就列出一個(gè)完整性約束條件表：

約束條件說(shuō)明
PRIMARY KEY 標(biāo)識(shí)該屬性為該表的主鍵，可以唯一的標(biāo)識(shí)對(duì)應(yīng)的元組
FOREIGN KEY 標(biāo)識(shí)該屬性為該表的外鍵，是與之聯(lián)系某表的主鍵
 NOT NULL 標(biāo)識(shí)該屬性不能為空
UNIQUE 標(biāo)識(shí)該屬性的值是唯一的
AUTO_INCREMENT 標(biāo)識(shí)該屬性的值是自動(dòng)增加，這是MySQL的SQL語(yǔ)句的特色
 DEFAULT為該屬性設(shè)置默認(rèn)值

下面講解一下上面完整性約束條件的應(yīng)用：

2）設(shè)置表的主鍵

單字段主鍵格式：屬性名 數(shù)據(jù)類型 PRIMARY KEY

示例：

mysql>  CREATE TABLE student1 (
    -> id int PRIMARY KEY,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.06 sec)

多字段主鍵格式：PRIMARY KEY(屬性名1,屬性名2....屬性名n)

示例：

mysql> CREATE TABLE student2 (
    -> id int,
    -> stu_id int,
    -> name varchar(20),
    -> PRIMARY KEY(id,stu_id)
    -> );
Query OK, 0 rows affected (0.00 sec)

3）設(shè)置表的外鍵

格式：CONSTRAINT 外鍵別名 FOREIGN KEY(屬性1,屬性2,....屬性n) REFERENCES 表名(屬性1',屬性2',...屬性n')

示例：

mysql> CREATE TABLE teacher (
    -> id int PRIMARY KEY,
    -> stu_id int,
    -> name varchar(20),
    -> CONSTRAINT STUID FOREIGN KEY(stu_id) REFERENCES student1(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

4）設(shè)置表的非空約束

簡(jiǎn)單的說(shuō)就是不讓這個(gè)屬性的值為空，不填的話就會(huì)報(bào)錯(cuò)

格式：屬性名 數(shù)據(jù)類型 NOT NULL

5）設(shè)置表的唯一性約束

就是這個(gè)屬性的值是不能重復(fù)的

格式：屬性名 數(shù)據(jù)類型 UNIQUE

6）設(shè)置表的屬性值自動(dòng)增加

AUTO_INCREMENT約束的字段可以是任何整數(shù)類型（TINYINT、SMALLINT、INT和BIGINT），在默認(rèn)的情況下，該字段的值是從1開(kāi)始自增

格式：屬性名 數(shù)據(jù)類型 AUTO_INCREMENT

7）設(shè)置表的屬性的默認(rèn)值

格式：屬性名 數(shù)據(jù)類型 DEFAULT 默認(rèn)值

下面對(duì)4-7進(jìn)行綜合示例：

mysql> CREATE TABLE student3 (
    -> id int PRIMARY KEY AUTO_INCREMENT,
    -> teacher_id int UNIQUE,
    -> name varchar(20) NOT NULL,
    -> sex varchar(10) DEFAULT 'male'
    -> );
Query OK, 0 rows affected (0.01 sec)

查看表結(jié)構(gòu)

查看表基本結(jié)構(gòu)語(yǔ)句DESCRIBE

格式：DESCRIBE 表名;

通過(guò)查看表的結(jié)構(gòu)，就很明確的對(duì)表進(jìn)行解讀，而且可以查看一下自己創(chuàng)建的表有沒(méi)錯(cuò)誤，這個(gè)SQL語(yǔ)句必須會(huì)用啊

示例：

mysql> desc student3;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| teacher_id | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | NO   |     | NULL    |                |
| sex        | varchar(10) | YES  |     | male    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

查看表詳細(xì)結(jié)構(gòu)語(yǔ)句SHOW CREATE TABLE

通過(guò)這個(gè)SQL語(yǔ)句可以查看表的詳細(xì)定義，除了字段名、字段的數(shù)據(jù)類型、約束條件外，還可以查看表的默認(rèn)存儲(chǔ)引擎和字符編碼

格式：SHOW CREATE TABLE 表名;

示例：

mysql> SHOW CREATE TABLE student3;
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
| Table    | Create Table
                                             |
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
| student3 | CREATE TABLE `student3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) DEFAULT NULL,
  `name` varchar(20) NOT NULL,
  `sex` varchar(10) DEFAULT 'male',
  PRIMARY KEY (`id`),
  UNIQUE KEY `teacher_id` (`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
1 row in set (0.00 sec)

修改表：

1）修改表名

表名可以在一個(gè)數(shù)據(jù)庫(kù)中唯一的確定一張表。

格式：ALTER TABLE 舊表名 RENAME 新表名;

示例：

mysql> ALTER TABLE student RENAME student4;
Query OK, 0 rows affected (0.11 sec)

mysql> DESCRIBE student;
ERROR 1146 (42S02): Table 'example.student' doesn't exist

由上面可以看出，改名后的表已經(jīng)不存在了。

2）修改字段的數(shù)據(jù)類型

格式：ALTER TABLE 表名 MODIFY 屬性名 數(shù)據(jù)類型;

示例：

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)

mysql> ALTER TABLE student1 MODIFY name varchar(30);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3）修改字段名：

格式：ALTER TABLE 表名 CHANGE 舊屬性名 新屬性名 新數(shù)據(jù)類型;

示例：

mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE student1 CHANGE name stu_name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

這里我修改的字段名的同時(shí)也修改了數(shù)據(jù)類型了，如果你不想修改數(shù)據(jù)類型的話就按照原來(lái)的寫(xiě)就行了。

4）增加字段

格式：ALTER TABLE 表名 ADD 屬性名1 數(shù)據(jù)類型 [完整性約束條件] [FIRST | AFTER 屬性名2];

其中，“屬性名1”參數(shù)指需要增加的字段的名稱；“FIRST”參數(shù)是可選參數(shù)，其作用是將新增字段設(shè)置為表的第一個(gè)字段；“AFTER”參數(shù)也是可選的參數(shù)，其作用是將新增字段添加到“屬性名2”后面；“屬性名2”當(dāng)然就是指表中已經(jīng)有的字段

示例：

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL AFTER id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| teacher_name | varchar(20) | NO   |     | NULL    |       |
| stu_name     | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

5）刪除字段

格式：ALTER TABLE 表名 DROP 屬性名;

示例：

mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| teacher_name | varchar(20) | NO   |     | NULL    |       |
| stu_name     | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> ALTER TABLE student1 DROP teacher_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(40) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6)更改表的存儲(chǔ)引擎

格式：ALTER TABLE 表名 ENGINE = 存儲(chǔ)引擎名;

示例：

mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| Table    | Create Table

+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| student2 | CREATE TABLE `student2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
1 row in set (0.05 sec)

mysql> ALTER TABLE student2 ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| Table    | Create Table

+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| student2 | CREATE TABLE `student2` (
  `id` int(11) NOT NULL DEFAULT '0',
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`,`stu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
1 row in set (0.00 sec)

7)刪除表的外鍵約束

格式：ALTER TABLE 表名 DROP FOREIGN KEY 外鍵別名;

示例：

mysql> SHOW CREATE TABLE teacher;
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
| Table   | Create Table
                                                  |
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `STUID` (`stu_id`),
  CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `stu
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
1 row in set (0.08 sec)

mysql> ALTER TABLE teacher DROP FOREIGN KEY STUID;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE teacher;
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
| Table   | Create Table
                                                        |
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `STUID` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
1 row in set (0.00 sec)

刪除表：

格式：DROP TABLE 表名;

刪除沒(méi)有被關(guān)聯(lián)的普通表：直接上面的SQL語(yǔ)句就行了

刪除被其他表關(guān)聯(lián)的父表：

方法一：先刪除子表，在刪除父表

方法二：刪除父表的外鍵約束（上面有介紹），再刪該表

    相關(guān)文章

    相關(guān)評(píng)論

    閱讀本文后您有什么感想? 已有人給出評(píng)價(jià)!

    • 8 喜歡喜歡
    • 3 頂頂
    • 1 難過(guò)難過(guò)
    • 5 囧囧
    • 3 圍觀圍觀
    • 2 無(wú)聊無(wú)聊

    熱門(mén)評(píng)論

    最新評(píng)論

    發(fā)表評(píng)論 查看所有評(píng)論(1)

    昵稱:
    表情: 高興 可 汗 我不要 害羞 好 下下下 送花 屎 親親
    字?jǐn)?shù): 0/500 (您的評(píng)論需要經(jīng)過(guò)審核才能顯示)