mysql表结构说明只能为1 8_SQL基础

1 SQL语句分类

名称

作用

语法关键字

DDL

数据定义语言

CREATE,DROP,ALTER

DML

数据操纵语言

INSERT,DELETE,UPDATE

DQL

数据查询语言

SELECT

DCL

数据控制语言

GRANT,REVOKE,COMMIT,ROLLBACK

2 数据类型

数值类型

日期/时间类型

字符串(字符)类型

2.1 数值类型

2.1.1 整数型

类型

储存空间(字节)

范围

tinyint(m)

1

-128~127

smallint(m)

2

-32768~32767

mediumint(m)

3

-8388608~8388607

int(m)

4

-2147483648~2147483647

bigint(m)

8

-2^63~2^63-1

上述数据类型，如果加修饰符unsigned后，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

2.1.2 浮点型(float和double)，近似值

float(m,d)单精度浮点型8位精度(4字节)m总个数，d小数位

double(m,d)双精度浮点型16位精度(8字节)m总个数，d小数位

假设一个字段定义为float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位

2.1.3 定点数

在数据库中存放的是精确值，存为十进制

decimal(m,d)参数m<65 是总个数，d<30且 d

MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。

例如:

decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

2.2 日期/时间类型

数据类型

值

DATE

'0000-00-00'

TIME

'00:00:00'

DATETIME

'0000-00-00 00:00:00'

TIMESTAMP

'0000-00-00 00:00:00'

YEAR(2),YEAR(4)

00,0000

2.3 字符串类型

数据类型

值

char(n) 固定长度

最多255个字符

varchar(n) 可变长度

最多65535个字符

tinytext 可变长度

最多255个字符

text 可变长度

最多65535个字符

mediumtext 可变长度

最多2的24次方-1个字符

longtext 可变长度

最多2的32次方-1个字符

BINARY(M) 固定长度

可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度

可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举，SET集合

ENUM是一个字符串对象，其值是从允许值的列表中选择的，这些值在表创建时在列规范中明确枚举

SET是可以具有零个或多个值的字符串对象，每个值都必须从创建表时指定的允许值列表中选择。 SET由多个set成员组成的列值用用逗号(,)分隔的成员指定。这样的结果是 SET成员值本身不应包含逗号。

2.3.1 char和varchar比较

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此

2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节(n< n>255)，所以varchar(4),存入3个字符将占用4个字节

3.char类型的字符串检索速度要比varchar类型的快

2.3.2 varchar和text比较

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节(n< n>255)，text是实际字符数+2个字节。

2.text类型不能有默认值

3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

数据类型

2.3.3 BLOB和TEXT比较

1.BLOB和test存储方式不同，text以文本方式存储，英文存储区分大小写，而blob以二进制方式存储，不区分大小写

2.BLOB存储的数据只能整体读出

3.text可以指定字符集，blob不用指定字符集

2.4 修饰符

2.4.1 适合所有类型的修饰符：

名称

作用

NULL

数据列可包含NULL值

NOT NULL

数据列不允许包含NULL值

DEFAULT

默认值

PRIMARY KEY

主键

UNIQUE KEY

唯一键

CHARACTER SET name

指定一个字符集

2.4.2 适用数值型的修饰符

名称

作用

AUTO_INCREMENT

自动递增，适用于整数类型，不支持列的负值。

UNSIGNED

无符号，可用于仅允许一列中使用非负数，或者在您需要该列的较大的较高数字范围时使用

3 DDL语句

3.1 创建表

HELP CREATE TALBE;

创建表的方法

3.1.1 直接创建

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符,

...)

#字段信息

col type1

PRIMARY KEY(col1,...)

INDEX(col1, ...)

UNIQUE KEY(col1, ...)

#表选项：

ENGINE [=] engine_name

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎

同一库中不同表可以使用不同的存储引擎

同一个库中表建议要使用同一种存储引擎类型

范例：

mysql> CREATE TABLE student (id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED);

Query OK, 0 rows affected (0.03 sec)

mysql> DESC student;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> CREATE TABLE employee (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED,PRIMARY KEY(id,name));

Query OK, 0 rows affected (0.02 sec)

3.1.2 通过查询现存表创建，新表会被直接插入查询而来的数据

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

[table_options]

[partition_options] select_statement

【例】通过查询现存表student的数据来创建新表new_student：

mysql> desc student;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(20) | NO | | NULL | |

| age | tinyint(3) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+----------------+

3 rows in set (0.01 sec)

mysql> select * from student;

+----+------