MySQL使用DDL语句创建表

jericho0702

一、使用DDL语句创建表

　　DDL语言全面数据定义语言（Data Define Language）

主要的DDL动词：

　　CREATE（创建）、DROP（删除）、ALTER（修改）

　　TRUNCATE（截断）、RENAME（重命名）

注意：

　　1、使用DDL语言时，必须在动词后跟上数据库对象名词（例如：TABLE、VIEW、INDEX、SCHEMA、TRIGGER等）。

　　2、MySQL的DDL语言创建数据库，如CREATE DATABASE、DROP DATABASE（在Oracle中没有）。

语法：

　　

CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS] [database_name.] <table_name>　　
(
<column_name>  <data_type>  [[not] null],…　　
)
　　

　　TEMPORARY：指明创建临时表

　　IF NOT EXISTS：如果要创建的表已经存在，强制不显示错误消息

　　database_name：数据库名

　　table_name：表名

　　column_name：列名

　　data_type：数据类型

mysql> help create table　　#查看详细的建表帮助信息

示例：创建一个员工(employee)信息表

　　

mysql> create table emp_xinxi　　

    -> (　　

    -> 　　id int unsigned not null primary key,-> 　　name varchar(20) not null,-> 　　sex char(1) not null,-> 　　birth date not null,-> 　　job varchar(30) null　　-> );
　　

　　
mysql
> desc emp_xinxi;　　

+-------+------------------+------+-----+---------+-------+　　
| Field | Type             | Null | Key | Default | Extra |
　　
+-------+------------------+------+-----+---------+-------+

　　
|>　　
| name  | varchar(20)      | NO   |     | NULL    |       |
　　
| sex   | char(1)          | NO   |     | NULL    |       |
　　
| birth | date             | NO   |     | NULL    |       |
　　
| job   | varchar(30)      | YES  |     | NULL    |       |
　　
+-------+------------------+------+-----+---------+-------+
　　

MySQL反引号`的使用：为了区分保留字与普通字符而引入的符号

举个栗子：

　　SELECT `select` FROM `test` WHERE select='字段值';

解析：

　　在test表中，有个select字段，如果不用反引号，MySQL将把select视为保留字而导致出错，所以，有mysql保留字作为字段的，必须加上反引号来区分。

①保留字不能用于表名，比如desc，此时需要加入反引号来区别；但是，使用表名时可忽略反引号

　　

mysql> create table desc……报错　　
mysql
> create table `desc`……成功　　
mysql
> create table `test`……成功　　
mysql
> drop table test……成功　　

②保留字不能用于字段名，比如desc，此时也需要加入反引号；并且，insert等使用时也要加上反引号

　　

mysql> create table `test`（`desc` varchar(255)）;　　成功　　
mysql
> insert into test(desc) values('fxf');　　失败　　
mysql
> insert into test(`desc`) values('fxf');　　成功　　

所以：在容易因为保留字问题会出现问题的情况下，都使用反引号`进行区分，不容易出错。

二、建表时列的数据类型

1、整型：(5种)

　　每个整型类型可以指定一个最小显示宽度(注意：这个宽度并不表示存储的值有多大)

　　基本格式：
　　INT[(M)] [UNSIGNED] [ZEROFILL]

①M：指定显示宽度(默认是M=10)，以实际的取值范围为准，值超过显示宽度，显示宽度自动增加

②unsigned：无符号的，表示只允许正数，所以取值范围取绝对值且最大值*2

　　(例：int unsigned range is 0 to 4294967295)

③zerofill：填充0(如果声明了zerofill，该列会自动设为unsigned)

影响数字的显示方式：

　　如果一个数字的宽度小于所允许的最大宽度，这个值前面会用0填充；

　　如果宽度大于所允许的最大宽度但不超过取值范围，以实际的取值范围为准，不填0；超出取值范围的报错不存储。

　　

mysql> create table t1(id int zerofill);    #默认显示宽度10　　

　　
mysql
> insert into t1 values(-1);　　
ERROR
1264 (22003): Out of range value for column 'id' at row 1　　
mysql
> insert into t1 values(123);　　
mysql
> insert into t1values(12300);　　
mysql
> insert into t1values(1.123);　　

　　mysql
> select * from t1　　

+------------+
　　
|>　　
+------------+
　　
| 0000000123 |
　　
| 0000012300 |
　　
| 0123456789 |
　　
| 0000000001 |
　　
+------------+
　　

解析：因为建表的设置是int整数，小数点后的数会四舍五入

2、小数类型

1）定点小数：decimal(dec)

　　同义词：decimal=dec=numeric=fixed

基本格式：

　　DEC[(M[,D])] [UNSIGNED] [ZEROFILL]

①M：精度，（整数位数+小数位数）不包含小数点的数字总位数(1--30)。不指定默认为10

②D：小数位数，如果不指定，默认为0

若位数不够，小数末尾补零；若位数超预算(整数位数=M-D)，报错。

　　

mysql> create table t2id dec(5,3) unsigned);　　
##数字总位数5
　　
##整数位数5
-3=2(不够没关系；超出报错)　　
##小数位数3(不够补零；超出四舍五入)
　　

　　
mysql
> insert into t2values(12.345);　　
mysql
> insert into t2values(1.23);　　
mysql
> insert into t2values(123);　　
ERROR
1264 (22003): Out of range value for column 'id' at row 1　　
mysql
> insert into t2values(12.3456);　　

　　
mysql
> select * from t2;　　

+--------+
　　
|>　　
+--------+
　　
| 12.345 |
　　
|  1.230 |
　　
| 12.346 |
　　
+--------+
　　

2）浮点小数：float/double

基本格式：

　　float[(length) | (<display>
　　double[(precision) | (<display>

两者区别在于值所保留的存储空间数量不同

注意：

　　由于对一个浮点数字来说，可用的存储空间有限，如果一个数字非常大或非常小，将存储这个数字的近似值而不是实际值

①使用一个参数来指定浮点类型

length(precision)：在一个浮点数据类型中可以指定长度，来确定具体的浮点类型

　　0~24：单精度float，从第7位有效位，进行四舍五入存储

　　25~30：双精度double，从第16位有效位，进行四舍五入存储

　　

mysql> create table t3(id float(8));　　
m
=8，说明是单精度，存储6位的近似值（第7位四舍五入，后面为0）　　

　　
mysql
> insert into t3 values(123);　　
mysql
> insert into t3 values(1234567);　　
mysql
> insert into t3 values(123456789);　　
mysql
> insert into t3 values(123456789.123456789);　　

　　
mysql
> select * from t3;　　

+-----------+
　　
|>　　
+-----------+
　　
| 123       |
　　
| 1234570   |
　　
| 123457000 |
　　
| 123457000 |
　　
+-----------+
　　

②使用两个参数来指定浮点类型

　　单精度float(m,d)：m表示精度（0~24），d表示小数位数

　　双精度double(m,d)：m表示精度（25~30），d表示小数位数

示例：设一个字段定义为float(5,3)---单精度浮点，精度5，小数位3（和dec一样）

　　

mysql> create table t_3(id float(5,3) unsigned);　　
##数字总位数5
　　
##整数位数5
-3=2(不够没关系；超出报错)　　
##小数位数3(不够补零；超出四舍五入)
　　

　　
mysql
> insert into t_3 values(123);　　
ERROR
1264 (22003): Out of range value for column 'id' at row 1　　
mysql
> insert into t_3 values(12.3);　　
mysql
> insert into t_3 values(12.3456);　　
mysql
> insert into t_3 values(1.23456);　　

　　mysql
> select * from t_3;　　

+--------+　　
|>　　
+--------+
　　
| 12.300 |
　　
| 12.346 |
　　
| 1.235  |
　　
+--------+
　　

解析：float的小数位数不是固定的；超出存储范围，近似值进行存储。

3、位类型

基本格式：

　　BIT [(LENGTH)]

存储基于位的值，Length取值范围1---64.默认为1

　　

mysql> create table an_bit (id bit(8));　　

　　mysql
> insert into an_bit values(b'11');　　
#插入数据，用b注释插入的是位类型数据(默认是十进制数据)，要添加的数据用引号引起
　　

　　
mysql
> select * from an_bit;　　#注意：位类型数据不能如此查询，会乱码
　　

　　
mysql
> select>

+------+
　　
|>　　
+------+
　　
|    3 |
　　
+------+
　　

　　
mysql
> select bin(id+0) from an_bit;    #可以看到二进制的值　　

+-----------+　　
| bin(id+0) |
　　
+-----------+
　　
| 11        |
　　
+-----------+
　　

　　
mysql> select hex(id+0) from an_bit;    #可以看到十六进制的值
　　
+-----------+
　　
| hex(id+0) |
　　
+-----------+
　　
| 3         |
　　
+-----------+
　　

位类型一般用在记录数据只有两种选择，比如：男1女0、省内1省外0……

4、字符串类型

①char[(length)]：固定长度字符串

　　值少于长度则在尾部自动填充空格

　　length的单位是字符，取值范围0---255

②varchar[(length)]：可变长度字符串

　　length的单位是字符，取值范围0---65535

对于char和varchar，如果长度为0，则只能存储null值或者空字符串’’值

③long varchar：可变长度字符串

　　最大长度2的24次方减1个字符

④longtext：可变长度字符串

　　最大长度4G个字符

text是varchar的同义词，mediumtext是long varchar的同义词

5、日期类型

date ： 3字节

datetime：  8字节

time ： 3字节

timestamp ： 4字节

year[(2) | (4)] ： 1字节

6、二进制大对象类型

binary： 255个字符

varbinary ：65535个字符

blob： 65535字符(图片、文件)

long varbinary： 2的24次方减1个字符

long blob：  2的32次方-1个字符

图片存储方式：

　　①在数据库中定义数据类型为blob，进行图片存储

　　②……varchar，“mysql.PNG“，在数据库中存放图片的路径，指向文件系统该照片对象

三、添加数据类型选项

1、对于字符串类型，可以添加类型选项：character set和collate

　　character set：设置字符集

　　collate：设置校对规则

mysql> create table t1(id int,name varchar(20)) character set=gbk collate=gbk_bin;   

#t1建表指定校对规则（区分大小写）

2、对于bit之外的所有数字类型，可以添加一个或是几个类型选项：

　　①unsigned：无符号的，表示只允许正数

　　②zerofill：填充0，(自动设unsigned)若数字的宽度小于所允许的最大宽度，在值前面用0填充

　　③auto_increment：自动增长，只适应于整数类型，一个表中只有一个列可以是自动增长的。（一般会用在主键序列自增）

示例：创建一个city_names表，其主键是自动增长的

　　注意：

　　当在insert语句中为auto_increment列，指定了null值或者没有指定值时，MySQL自动为其赋值；

　　MySQL会记住给出的最后一个顺序号码是多少。当要分配一个新的顺序号码时，最后一个顺序号码被找到，并且列中的最大值被确定。新的顺序号码就是这两个值中的最大者加1。

　　

mysql> insert into city_names values(10,'武汉');　　
mysql
> insert into city_names(name) values('赣州');　　

　　mysql
> select * from city_names;　　

+-------+--------+　　
| seqno | name   |
　　
+-------+--------+
　　
|     1 | 北京   |
　　
|     2 | 上海   |
　　
|     3 | 深圳   |
　　
|    10 | 武汉   |
　　
|    11 | 赣州   |
　　
+-------+--------+
　　

　　
mysql
> delete from city_names;    #删除表中的所有行　　

　　
mysql
> insert into city_names values(null,'天津');　　
mysql
> insert into city_names values(null,'成都');　　

　　
mysql
> select * from city_names;　　

+-------+--------+　　
| seqno | name   |
　　
+-------+--------+
　　
|    12 | 天津   |
　　
|    13 | 成都   |
　　
+-------+--------+
　　

即使删除了表中的所有行，插入新行时，编号还是从原来的位置继续；要想再次从1开始，必须删除整个表并重新创建。

默认情况下，顺序号码从1开始并且每次加1；可以分别使用两个系统变量来改变默认行为：

　　

mysql> show variables like '%auto_increment%';　　

+--------------------------+-------+　　
| Variable_name            | Value |
　　
+--------------------------+-------+
　　
| auto_increment_increment | 1     |
　　
| auto_increment_offset    | 1     |
　　
+--------------------------+-------+
　　

　　
mysql
> set @@auto_increment_offset=10; --起始值　　
mysql
> set @@auto_increment_increment=10; --每次加几　　

　　
mysql
> create table t5(id integer auto_increment not null primary key);　　
mysql
> insert into t5 values(null),(null);　　
mysql
> select * from t5;　　

+----+
　　
|>　　
+----+
　　
| 10 |
　　
| 20 |
　　
+----+