MySQL数据类型

字符型数据

字符型可以用定长和变长来实现， 不同点在于：

  • 固定长度使用空格向右填充， 以保证用同样的字节数;
  • 变长字符串不需要向右填充, 并且所有字节数可变.
char(20) /*固定长度20字节,最长为2^8=256*/
varchar(20)/*可变长度20字节,最长为2^16=65535*/

字符集

不同语言每个字母占用的字节数可能不同.

  • 可以用如下语句查看
show character set
  • 定义字符列指定字符集
varchar(20) character set utf8
  • 改变整个数据库的默认字符集
create database foreign_sales character set utf8;

文本数据

varchar列所容许的上限为65kb, 就需要使用文本类型.

文本类型

最大字节

tinytest

2^8-1=255

test

2^16-1=65535

mediumtext

2^32-1=16777215

longtext

2^64-1=4294967295

注意:

  • 超出长度会被截断
  • 再向文本列装在数据时,不会消除尾部的空格
  • 当使用文本列排序或分组时, 只会使用前1024个字节,当在需要时可以放宽这个限制
  • 这些不同的文本类型只是针对MySQL服务器.SQLServer对于大的字符型数据只提供text类型
    一般varchar足够了.

数值型数据

一般使用独立的数值数据类型numeric

整形

类型

带符号的范围

无符号范围

tinyint

-128~127

0~255

smallint

-32768~32767

0~65535

mediumint

-8388608~8388607

0~2^32-1

int

-2^64~2^64-1

0~2^64-1

bigint

-2^128~2^128-1

0~2^128-1

浮点型

类型

数值范围

float(p,s)

double(p,w)

float(4,2): 存储4为数字有两位小数点，超过的位数会被4舍5入。

时间数据

类型

默认模式

允许的值

date

YYYY-MM-DD

1000-01-01~9999-12-31

datetime

YYYY-MM-DD HH:MI:SS

1000-01-01 00:00:00~9999-12-31 23:59:59

timestamp

YYYY-MM-DD HH:MI:SS

1970-01-01 00:00:00~2037-12-31 23:59:59

year

YYYY

1901~2155

time

HHH:MM:SS

-838:59:59~838:59:59

创建表

person表

CREATE TABLE person
(person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),#enum将检查约束与数据类型定义到一起,只能输入m,f
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)#指定主键
);

sqlite3 字段字符数不足2位补0修改位两位 sql设置字符串长度_sql

第三列表示是否允许再插入数据时被省略，第四列表示是否是键值（主键或外键），第5列表示是否有默认值。

favoritefood表

create TABLE favorite_food
(person_id smallint unsigned,
food char(20),
constraint pk_favorite_food PRIMARY KEY (person_id, food),
constraint fk_fav_food_person_id foreign key (person_id)
references person (person_id)
);
  • 由于一个人可能有多种喜爱的食物,仅靠person_id是不能保证表数据的唯一性的, 因此本表的主键包含两列: person_id和food.
  • favorite_food表包含了另一种约束,它限制了favorite表中person_id列的值只能来自person表.通过这种约束,使得当person表中没有person_id列的值只能够来自person表. 通过这种约束,使得当person表中没有person_id为27记录时,向favorite表中增加是不可能的.
  • 如果首次创建表忘记增加外键约束,那么可以在后面通过altertable语句添加.

操作与修改表

insert, update, delete, select

插入数据

生成数字型主键数据

关于数字型主键的生成机制
- 查看表中当前主键的最大值,+1;
- 让数据库服务器自动提供
第一种的问题是:　两个用户在同一时间访问列表并产生相同的主键值.
在一些服务器中, 提供了安全, 健壮的产生数字型主键的方法:
- Oracle数据库, 使用独立的方案对象, 即序列号(sequence);
- MySQL中, 只需为主键打开自增(anto-increment)特性.

alter table

用于修改表已经存在的定义.

alter table person modify person_id smallint unsigned AUTO_INCREMENT;

insert

insert into person (
   person_id
  ,fname
  ,lname
  ,gender
  ,birth_date
) 
values (null, 'William','Turner','M','1972-05-27');

sqlite3 字段字符数不足2位补0修改位两位 sql设置字符串长度_主键_02

插入喜爱食物的表: 

insert into favorite_food(person_id, food)
values (1,'pizza');
insert into favorite_food(person_id, food)
values (1,'cookies');
insert into favorite_food(person_id, food)
values (1,'nachos');

sqlite3 字段字符数不足2位补0修改位两位 sql设置字符串长度_主键_03

更新数据

update person
set street = '1225 Tremont St.',
city = 'Boston',
state = 'MA',
country = 'USA',
postal_code = '02138'
where person_id = 1;

如果where person_id < 10;那么小于10的都会被修改.

删除数据

delete from person WHERE person_id =2;