一、使用 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.]
- (
- [[not] null],…
- )
TEMPORARY:指明创建临时表
IF NOT EXISTS:如果要创建的表已经存在,强制不显示错误消息
database_name:数据库名
table_name:表名
column_name:列名
data_type:数据类型
mysql> help create table# 查看详细的建表帮助信息
示例:创建一个员工 (employee) 信息表
- mysql> create table emp_xinxi
- -> (
- -> idintunsigned notnull primary key,
- -> name varchar(20) notnull,
- -> sexchar(1) notnull,
- -> birth date notnull,
- -> job varchar(30)null-> );
- mysql> desc emp_xinxi;
- +-------+------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+------------------+------+-----+---------+-------+
- | id |int(10) unsigned | NO | PRI | NULL | |
- | 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(idintzerofill);#默认显示宽度10mysql> insert into t1 values(-1);
- ERROR 1264(22003): Out of range valueforcolumn'id'at row1
- mysql> insert into t1 values(123);
- mysql> insert into t1values(12300);
- mysql> insert into t1values(1.123);mysql>select*from t1
- +------------+
- | id |
- +------------+
- |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 valueforcolumn'id'at row1
- mysql> insert into t2values(12.3456);
- mysql>select*from t2;
- +--------+
- | id |
- +--------+
- |12.345|
- |1.230|
- |12.346|
- +--------+
2)浮点小数:float/double
基本格式:
float[(length) | (<display width>,<scale>)]
double[(precision) | (<display width>,<scale>)]
两者区别在于值所保留的存储空间数量不同
注意:
由于对一个浮点数字来说,可用的存储空间有限,如果一个数字非常大或非常小,将存储这个数字的近似值而不是实际值
①使用一个参数来指定浮点类型
length(precision):在一个浮点数据类型中可以指定长度,来确定具体的浮点类型
0~24:单精度 float,从第 7 位有效位,进行四舍五入存储
25~30:双精度 double,从第 16 位有效位,进行四舍五入存储
- mysql> create table t3(idfloat(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;
- +-----------+
- | id |
- +-----------+
- |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(idfloat(5,3) unsigned);##数字总位数5
- ##整数位数5-3=2(不够没关系;超出报错)
- ##小数位数3(不够补零;超出四舍五入)mysql> insert into t_3 values(123);
- ERROR 1264(22003): Out of range valueforcolumn'id'at row1
- 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;
- +--------+
- | id |
- +--------+
- |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*froman_bit;#注意:位类型数据不能如此查询,会乱码
- mysql>selectid+0 from an_bit; #可以看到十进制的值
- +------+
- | id+0|
- +------+
- |3|
- +------+
- mysql>selectbin(id+0)from an_bit; #可以看到二进制的值
- +-----------+
- | bin(id+0) |
- +-----------+
- |11|
- +-----------+
- mysql>selecthex(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> deletefromcity_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 notnull primary key);
- mysql> insert into t5 values(null),(null);
- mysql>select*from t5;
- +----+
- | id |
- +----+
- |10|
- |20|
- +----+
来源: http://www.cnblogs.com/geaozhang/p/6776424.html