MySQL 创建修改和删除表.docx
《MySQL 创建修改和删除表.docx》由会员分享，可在线阅读，更多相关《MySQL 创建修改和删除表.docx（21页珍藏版）》请在冰豆网上搜索。
MySQL创建修改和删除表
MySQL：
创建、修改和删除表
其实对很多人来说对于SQL语句已经忘了很多，或者说是不懂很多，因为有数据库图形操作软件，方便了大家，但是我们不能忘记最根本的东西，特别是一些细节上的东西，可能你用惯了Hibernate，不用写SQL语句，但是不是任何项目都要用到大框架的，如果不用，那你是不是就不会操作数据库了呢，所以我们最好还是熟悉一点好，对我们以后找工作和工作都有帮助。
在说创建、修改和删除表前，我们还是要进行一个操作的简单说明：
1.登陆数据库系统
在命令行中登陆MySQL数据库管理系统，输入一下内容：
mysql-hlocalhost-uroot-p
很多人都知道这个，但是其中参数的具体表示什么我们还是要了解的，其中，“-h”参数指连接的主机名，所以后面是localhost；“-u”参数表示用户名，此处的用户名为root；“-p”参数表示用户的密码，按下Enter键后就显示“Enterpassword：
”，输入密码即可登录进去了。
2.创建数据库
在创建数据库之前，我们可以查看已经存在的数据库：
mysql>SHOWDATABASES;
+--------------------+
|Database|
+--------------------+
|information_schema|
|community|
|community_test|
|data|
|mydata|
|mysql|
|performance_schema|
|test|
+--------------------+
8rowsinset（0.04sec）
创建数据库的格式：
CREATEDATABASE数据库名;
示例：
创建一个名为example的数据库
mysql>CREATEDATABASEexample;
QueryOK,1rowaffected（0.00sec）
mysql>SHOWDATABASES;
+--------------------+
|Database|
+--------------------+
|information_schema|
|community|
|community_test|
|data|
|example|
|mydata|
|mysql|
|performance_schema|
|test|
+--------------------+
9rowsinset（0.00sec）
3.删除数据库：
格式：
DROPDATABASE数据库名;
示例：
删除example数据库
mysql>DROPDATABASEexample;
QueryOK,0rowsaffected（0.07sec）
mysql>SHOWDATABASES;
+--------------------+
|Database|
+--------------------+
|information_schema|
|community|
|community_test|
|data|
|mydata|
|mysql|
|performance_schema|
|test|
+--------------------+
8rowsinset（0.00sec）
4.数据库存储引擎
存储引擎就是指表的类型，数据库存储引擎决定了表在计算机的存储方式。
MySQL中查询存储引擎的类型命令：
SHOWENGINES;
mysql>SHOWENGINES;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
|Engine|Support|Comment
|Transactions|XA|Savepoints|
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
|FEDERATED|NO|FederatedMySQLstorageengine
|NULL|NULL|NULL|
|MRG_MYISAM|YES|CollectionofidenticalMyISAMtables
|NO|NO|NO|
|MyISAM|YES|MyISAMstorageengine
|NO|NO|NO|
|BLACKHOLE|YES|/dev/nullstorageengine（anythingyouwriteto
itdisappears）|NO|NO|NO|
|CSV|YES|CSVstorageengine
|NO|NO|NO|
|MEMORY|YES|Hashbased,storedinmemory,usefulfortempor
arytables|NO|NO|NO|
|ARCHIVE|YES|Archivestorageengine
|NO|NO|NO|
|InnoDB|DEFAULT|Supportstransactions,row-levellocking,andf
oreignkeys|YES|YES|YES|
|PERFORMANCE_SCHEMA|YES|PerformanceSchema
|NO|NO|NO|
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9rowsinset（0.00sec）
查询结果中，Engine参数指存储引擎名称；Support参数说明MySQL是否支持该类型引擎；Comment参数表示对该引擎的评论；Transaction参数表示是否支持事务处理；XA参数表示是否分布式交易处理的XA规范；Savepoints参数表示是否支持保存点，以方便事务的回滚操作；由上面我们看到InnoDB存储引擎是default的，也就是数据库默认的存储引擎，下面我们简单介绍一下InnoDB。
InnoDB是MySQL的一种存储引擎，InnoDB给MySQL提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全。
InnoDB是MySQL上第一个提供外键约束的表引擎，而且对事务处理的能力，也是其他存储引擎不能比拟的。
不过这种引擎的缺点就是读写效率稍差，占用的数据空间相对比较大。
下面就是正式的内容：
创建表：
1）创建表的形式：
CREATETABLE表名（
属性名数据类型[完整约束条件],
属性名数据类型[完整约束条件],
...
...
属性名数据类型[完整约束条件]
）;
如果你很急的登陆进去就创建表，恭喜你，你会出现“Nodatabaseselected”的错误，因为你没有告诉别人你要选择在哪个数据库创建表，所以在创建之前要选择数据库，格式：
USE数据库名;
示例创建一个student表：
mysql>useexample;
Databasechanged
mysql>CREATETABLEstudent（
->idint,
->namevarchar（20）
->）;
QueryOK,0rowsaffected（0.09sec）
上面创建表的时候涉及到一个完整性约束条件，下面就列出一个完整性约束条件表：
约束条件
说明
PRIMARY KEY
标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGNKEY
标识该属性为该表的外键，是与之联系某表的主键
NOTNULL
标识该属性不能为空
UNIQUE
标识该属性的值是唯一的
AUTO_INCREMENT
标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT
为该属性设置默认值
下面讲解一下上面完整性约束条件的应用：
2）设置表的主键
单字段主键格式：
属性名数据类型PRIMARYKEY
示例：
mysql>CREATETABLEstudent1（
->idintPRIMARYKEY,
->namevarchar（20）
->）;
QueryOK,0rowsaffected（0.06sec）
多字段主键格式：
PRIMARYKEY（属性名1,属性名2....属性名n）
示例：
mysql>CREATETABLEstudent2（
->idint,
->stu_idint,
->namevarchar（20）,
->PRIMARYKEY（id,stu_id）
->）;
QueryOK,0rowsaffected（0.00sec）
3）设置表的外键
格式：
CONSTRAINT外键别名FOREIGNKEY（属性1,属性2,....属性n）REFERENCES表名（属性1',属性2',...属性n'）
示例：
mysql>CREATETABLEteacher（
->idintPRIMARYKEY,
->stu_idint,
->namevarchar（20）,
->CONSTRAINTSTUIDFOREIGNKEY（stu_id）REFERENCESstudent1（id）
->）;
QueryOK,0rowsaffected（0.00sec）
4）设置表的非空约束
简单的说就是不让这个属性的值为空，不填的话就会报错
格式：
属性名数据类型NOTNULL
5）设置表的唯一性约束
就是这个属性的值是不能重复的
格式：
属性名数据类型UNIQUE
6）设置表的属性值自动增加
AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLINT、INT和BIGINT），在默认的情况下，该字段的值是从1开始自增
格式：
属性名数据类型AUTO_INCREMENT
7）设置表的属性的默认值
格式：
属性名数据类型DEFAULT默认值
下面对4-7进行综合示例：
mysql>CREATETABLEstudent3（
->idintPRIMARYKEYAUTO_INCREMENT,
->teacher_idintUNIQUE,
->namevarchar（20）NOTNULL,
->sexvarchar（10）DEFAULT'male'
->）;
QueryOK,0rowsaffected（0.01sec）
查看表结构
查看表基本结构语句DESCRIBE
格式：
DESCRIBE表名;
通过查看表的结构，就很明确的对表进行解读，而且可以查看一下自己创建的表有没错误，这个SQL语句必须会用啊
示例：
mysql>descstudent3;
+------------+-------------+------+-----+---------+----------------+
|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||
+------------+-------------+------+-----+---------+----------------+
4rowsinset（0.01sec）
查看表详细结构语句SHOWCREATETABLE
通过这个SQL语句可以查看表的详细定义，除了字段名、字段的数据类型、约束条件外，还可以查看表的默认存储引擎和字符编码
格式：
SHOWCREATETABLE表名;
示例：
mysql>SHOWCREATETABLEstudent3;
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
|Table|CreateTable
|
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
|student3|CREATETABLE`student3`（
`id`int（11）NOTNULLAUTO_INCREMENT,
`teacher_id`int（11）DEFAULTNULL,
`name`varchar（20）NOTNULL,
`sex`varchar（10）DEFAULT'male',
PRIMARYKEY（`id`）,
UNIQUEKEY`teacher_id`（`teacher_id`）
）ENGINE=InnoDBDEFAULTCHARSET=gb2312|
+----------+-----------------------------------
-----------------------------------------------
-----------------------------------------------
---------------------------------------------+
1rowinset（0.00sec）
修改表：
1）修改表名
表名可以在一个数据库中唯一的确定一张表。
格式：
ALTERTABLE旧表名RENAME新表名;
示例：
mysql>ALTERTABLEstudentRENAMEstudent4;
QueryOK,0rowsaffected（0.11sec）
mysql>DESCRIBEstudent;
ERROR1146（42S02）:
Table'example.student'doesn'texist
由上面可以看出，改名后的表已经不存在了。
2）修改字段的数据类型
格式：
ALTERTABLE表名MODIFY属性名数据类型;
示例：
mysql>DESCRIBEstudent1;
+-------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
|name|varchar（20）|YES||NULL||
+-------+-------------+------+-----+---------+-------+
2rowsinset（0.08sec）
mysql>ALTERTABLEstudent1MODIFYnamevarchar（30）;
QueryOK,0rowsaffected（0.06sec）
Records:
0Duplicates:
0Warnings:
0
mysql>DESCRIBEstudent1;
+-------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
|name|varchar（30）|YES||NULL||
+-------+-------------+------+-----+---------+-------+
2rowsinset（0.01sec）
3）修改字段名：
格式：
ALTERTABLE表名CHANGE旧属性名新属性名新数据类型;
示例：
mysql>DESCRIBEstudent1;
+-------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
|name|varchar（30）|YES||NULL||
+-------+-------------+------+-----+---------+-------+
2rowsinset（0.00sec）
mysql>ALTERTABLEstudent1CHANGEnamestu_namevarchar（40）;
QueryOK,0rowsaffected（0.01sec）
Records:
0Duplicates:
0Warnings:
0
mysql>DESCRIBEstudent1;
+----------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
|stu_name|varchar（40）|YES||NULL||
+----------+-------------+------+-----+---------+-------+
2rowsinset（0.00sec）
这里我修改的字段名的同时也修改了数据类型了，如果你不想修改数据类型的话就按照原来的写就行了。
4）增加字段
格式：
ALTERTABLE表名ADD属性名1数据类型[完整性约束条件][FIRST|AFTER属性名2];
其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段
示例：
mysql>DESCRIBEstudent1;
+----------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
|stu_name|varchar（40）|YES||NULL||
+----------+-------------+------+-----+---------+-------+
2rowsinset（0.00sec）
mysql>ALTERTABLEstudent1ADDteacher_namevarchar（20）NOTNULLAFTERid;
QueryOK,0rowsaffected（0.01sec）
Records:
0Duplicates:
0Warnings:
0
mysql>DESCRIBEstudent1;
+--------------+-------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+--------------+-------------+------+-----+---------+-------+
|id|int（11）|NO|PRI|NULL||
升级会员 