一、表的基本操作1、数据类型整型数据类型字节数带符号值范围默认不带符号值范围tinyint1[-128,127][0,255]smallint2[-32768,32767][0,65535]mediumint3[-8388608,8388607][0,16777215]int4[-2147483648,2147483647][0,4294967295]bigint8[-9223372036854775808,9223372036854775807][0,18446744073709551616]浮点型数据类型字节数备注float4单精度浮点型double8双精度浮点型注意可以使用float(MD)、double(MD)格式限制宽度按M和精度D如float3,2不指定M、D的时会按照实际的精度来处理定点型数据类型备注decimal定点型decimal(size,d) 作为字符串存储的 DOUBLE 类型允许固定的小数点由于float、double类型存在精度丢失问题即写入数据库的数据未必是插入数据库的数据而decimal无论写入数据中的数据是多少都不会存在精度丢失问题这就是要引入decimal类型的原因decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中结论float/double在db中存储的是近似值而decimal则是以字符串形式进行保存文本型数据类型描述char(size)保存固定长度的字符串可包含字母、数字以及特殊字 符。在括号中指定字符串的长度。最多 255 个字符。varchar(size)保存可变长度的字符串可包含字母、数字以及特殊字 符。在括号中指定字符串的最大长度。最多 255 个字 符。 注释如果值的长度大于 255则被转换为 TEXT 类型。tinytext存放最大长度为 255 个字符的字符串。text存放最大长度为 65,535 个字符的字符串。blob用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。mediumtext存放最大长度为 16,777,215 个字符的字符串。mediumelob用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。longtext存放最大长度为 4,294,967,295 个字符的字符串。longblob用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。enum(x,y,z,etc.)允许输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值则插入空值。 注释这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值 ENUM(X,Y,Z)set与 enum 类似 SET 最多只能包含 64 个列表项不过 SET 可存储一个以上的值。blob和text都是为了存储很大数据而设计的字符串数据类型blob采用二进制形式存储无排序规则和字符集text采用字符形式存储且有排序规则和字符集适用于存储如文章内容、评论等char是固定长度字符串其长度范围为0~255且与编码方式无关无论字符实际长度是多少都会按照指定长度存储不够的用空格补足char类型数据时会将结尾的所有空格处理掉char适合存固定长度的内容手机号、身份证varchar长度不固定的内容date 类型数据类型字节数格式备注date3yyyy-MM-dd存储日期值time3HH:mm:ss存储时分秒year1yyyy存储年datetime8yyyy-MM-dd HH:mm:ss存储日期时间timestamp4yyyy-MM-dd HH:mm:ss存储日期时间可作时间戳总结常用数据类型2、创建表格式create table [if not exists] 表名 表选项示例1mysql create table student1(id int, name varchar(30), sex char(2), age int unsigned, score float, addr varchar(50)); Query OK, 0 rows affected (0.02 sec) mysql desc student1; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | sex | char(2) | YES | | NULL | | | age | int unsigned | YES | | NULL | | | score | float | YES | | NULL | | | addr | varchar(50) | YES | | NULL | | ------------------------------------------------3、查看表show命令mysql show tables; ------------------------- | Tables_in_mydb2_stuinfo | ------------------------- | class1 | | student1 | ------------------------- 2 rows in set (0.00 sec)describe命令mysql desc student1; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | sex | char(2) | YES | | NULL | | | age | int unsigned | YES | | NULL | | | score | float | YES | | NULL | | | addr | varchar(50) | YES | | NULL | | ------------------------------------------------ 6 rows in set (0.00 sec)字段意义分析Field :字段名称type字段类型null是否允许为空key索引类型default默认值extra填充4、删除表格式drop table [if exists] 表名5、修改表# 修改表名 alter table 旧表名 rename 新表名; # 表中添加新列 alter table 表名 add 新列名 列类型 [after|first] 列名; after为之后 # 删除列 alter table 表名 DROP 列名; # 修改列名类型 alter table 表名 change 旧列名 新列名 列类型; # 修改列类型 alter table 表名 modify 列名 列类型; # 移动列 alter table 表名 modify 列名 列类型 after 某列;6、复制表6.1、复制表结构格式create table 新表名 like 源表示例mysql insert into student1 value(1000,zhangsan,M,18,98.5,xian,12345678,2005-10-01); Query OK, 1 row affected (0.00 sec) mysql insert into student1 value(1001, lisi, W, 20,66.79, baoji,87654321,2003-5-01); mysql select * from student1; mysql create table student2 like student1; Query OK, 0 rows affected (0.01 sec) mysql show tables; ------------------------- | Tables_in_mydb2_stuinfo | ------------------------- | class1 | | student1 | | teacher1 | ------------------------- 3 rows in set (0.00 sec) mysql select * from student2; # 注意只是复制结构框架 Empty set (0.00 sec)6.2、复制结构和数据格式create table 新表名 select * from 源表示例mysql create table student3 select * from student1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql select * from student3; ---------------------------------------------------------------- | id | name | sex | age | score | addr | passwd | birthday | ---------------------------------------------------------------- | 1000 | zhangsan | M | 18 | 98.5 | xian | 12345678 | 2005-10-01 | | 1001 | lisi | W | 20 | 66.79 | baoji | 87654321 | 2003-05-01 | ----------------------------------------------------------------二、表的约束1、非空约束1.1、创建非空约束作用限定某个字段/某列的值不允许为空两个值null默认的和 not null不为空示例mysql create table myclass1(class_name varchar(20) not null, class_room varchar(20) not null, class_id int); Query OK, 0 rows affected (0.02 sec) mysql desc myclass1; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | class_name | varchar(20) | NO | | NULL | | | class_room | varchar(20) | NO | | NULL | | | class_id | int | YES | | NULL | | ---------------------------------------------------- 3 rows in set (0.00 sec) # 插入数据时 mysql insert into myclass1 (class_room) values(计科1); ERROR 1364 (HY000): Field class_name does not have a default value # 失败因为name有约束不能为空 mysql insert into myclass1 (class_name,class_room) values(A104,计科1); # 同时插入成功 Query OK, 1 row affected (0.00 sec) mysql select * from myclass1; ---------------------------------- | class_name | class_room | class_id | ---------------------------------- | A104 | 计科1 | NULL | ---------------------------------- 1 row in set (0.00 sec) # id字段无约束可以为空1.2、修改非空约束示例mysql alter table student1 modify id int not null;1.3、删除非空约束示例mysql alter table student1 modify id int null; # 或者 null 可省略默认为 null2、默认值约束2.1、创建默认值约束作用给某个字段/某列指定默认值一旦设置默认值在插入数据时如果此字段没有显式赋值则赋值为默认值。mysql create table teacher1(id int not null, name varchar(30) not null, sex char(2) default 女); Query OK, 0 rows affected (0.03 sec) mysql desc teacher1; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | id | int | NO | | NULL | | | name | varchar(30) | NO | | NULL | | | sex | char(2) | YES | | 女 | | ----------------------------------------------- 3 rows in set (0.01 sec) mysql insert into teacher1(id,name) values(1001,林雪); Query OK, 1 row affected (0.00 sec) mysql select * from teacher1; -------------------- | id | name | sex | -------------------- | 1001 | 林雪 | 女 | -------------------- 1 row in set (0.00 sec) mysql insert into teacher1 values(1002,石磊,男); Query OK, 1 row affected (0.01 sec) mysql select * from teacher1; -------------------- | id | name | sex | -------------------- | 1001 | 林雪 | 女 | | 1002 | 石磊 | 男 | -------------------- 2 rows in set (0.00 sec)2.2、增加默认值约束mysql alter table student1 modify sex char(2) default 女;3.列描述列描述没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义相当于C/C的注释mysql create table myclass2( class_name varchar(20) not null comment 教室,class_room varchar(20) default 计科2);4、主键约束作用数据库存储数据不是唯一的目的,还要考虑到提取数据一般要求数据库表中的记录要有一个 特定的唯一标识来表示唯一性,这个唯一的特定标识就是主键类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录主键primary key用来唯一的约束该字段里面的数据特点主键字段不能为空不能重复一张表中最多只能有一个主键主键所在的列通常是整数类型主键约束是最频繁的约束4.1、创建主键示例 创建时增加主键mysql create table t1 ( id int unsigned primary key comment 学号不能为空, name varchar(20) not null ,sex char(2) default 男); Query OK, 0 rows affected (0.02 sec) mysql desc t1; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | sex | char(2) | YES | | 男 | | ------------------------------------------------ 3 rows in set (0.01 sec) mysql insert into t1 values(1,孙文,女); Query OK, 1 row affected (0.00 sec) mysql select * from t1; ------------------ | id | name | sex | ------------------ | 1 | 孙文 | 女 | ------------------ 1 row in set (0.00 sec) mysql insert into t1(id,name) values(2,李文华); Query OK, 1 row affected (0.00 sec) mysql select * from t1; --------------------- | id | name | sex | --------------------- | 1 | 孙文 | 女 | | 2 | 李文华 | 男 | --------------------- 2 rows in set (0.00 sec) mysql insert into t1(id,name) values(1,黎明); ERROR 1062 (23000): Duplicate entry 1 for key t1.PRIMARY # 再次插入出错因为需要保持唯一性4.2、修改表追加主键mysql alter table teacher1 add primary key(id);4.3、删除主键alter table 表名 drop primary key;4.4、复合主键在创建表的时候,在所有字段之后,使用primary key主键字段列表来创建主键,如果有 多个字段作为主键,可以使用复合主键,这些字段合在一起是一个主键也就是让多个字段联合在一起 作为唯一标识,单个字段主键重复是没有问题的,只要不是成为复合主键的字段一起冲突就行示例mysql create table t2 (id int, hostname varchar(10), ip varchar(20), port int unsigned, primary key (ip,port));4.5、主键自增长作用给主键添加自增长的数值示例指定自增长的起始值如果第一条记录设置了该字段的初始值那么新增加的记录就从这个初始值 开始自增。例如如果表中插入的第一条记录的 id 值设置为 5那么再插入记录时id 值就会从 5 开始往上增加5、唯一性约束作用限制某个字段/某列的值不能重复唯一键和主键的关系1、主键更多的是标识唯一性2、唯一键更多的是保证在业务上不要和别的信息出现重复3、主键只能有一个,唯一键能设置多个示例unique为唯一性约束mysql create table t4( id int primary key, name varchar(20) unique comment 名子 不能重名可以为空 );6、外键约束作用限定某个表的某个字段的引用完整性概念1、主表父表被引用的表、被参考的表2、从表子表引用别人的表参考别人的表3、外键从表中的某个字段引用自主表的某个字段或多个字段4、引用键主表被引用的字段格式foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联示例学生表通过class_id和班级表产生关联,在语义上,class_id称为外键,此时的这个学生表就相当于从表, 班级表相当于是主表, 比如我们新增一个class_id为30的学生,但是班级表中得先有id为30的班级,才 能在学生表中添加, 要删除班级表中id为20的班级,首先必须保证学生表中没有对应班级为class_id 20的学生.,当然我们也可以新增一个class_id为NULL的学生,表示该学生还没有分配班级, 外键可以 为空代码示例7、检查约束作用检查约束check是用来检查数据表中字段值有效性的一种手段一般用于设置值的范 围示例1创建表时设置示例2修改表时设置8、删除表的约束9、总结