MySQL數(shù)據(jù)庫(kù)創(chuàng)建.修改和刪除表操作實(shí)例介紹
2023-11-23
更新時(shí)間:2023-11-23 00:09:31作者:未知
其實(shí)對(duì)很多人來(lái)說(shuō)對(duì)于SQL語(yǔ)句已經(jīng)忘了很多,或者說(shuō)是不懂很多,因?yàn)橛袛?shù)據(jù)庫(kù)圖形操作軟件,方便了大家,但是我們不能忘記最根本的東西,特別是一些細(xì)節(jié)上的東西,可能你用慣了Hibernate,不用寫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 temporary tables | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign 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)就是讀寫效率稍差,占用的數(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)槟銢]有告訴別人你要選擇在哪個(gè)數(shù)據(jù)庫(kù)創(chuàng)建表,所以在創(chuàng)建之前要選擇數(shù)據(jù)庫(kù),格式:USE 數(shù)據(jù)庫(kù)名;
示例創(chuàng)建一個(gè)student表:
mysql> use example;Database changedmysql> 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開始自增
格式:屬性名 數(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)建的表有沒錯(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: 0mysql> 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: 0mysql> 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)的寫就行了。
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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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 表名;
刪除沒有被關(guān)聯(lián)的普通表:直接上面的SQL語(yǔ)句就行了
刪除被其他表關(guān)聯(lián)的父表:
方法一:先刪除子表,在刪除父表
方法二:刪除父表的外鍵約束(上面有介紹),再刪該表