mysql数据库表介绍_mysql数据库第三篇.表介绍

3.1表的存储方式

存储方式一: MyISAM 5.5及以下默认存储方式

存储文件个数:表结构表中的数据,索引

适合做读,插入数据比较频繁的,对修改和删除涉及较少,索引和数据分开存储

支持表级锁

不支持行级锁 不支持事务 不支持外键

存储方式二: innoDB 5.6及以上默认存储方式

存储文件个数 : 表结构,表中的数据

适合并发较高,对事物一致性要求较高,行队更适应频繁的删除和修改操作,索引和数据存在一起

支持行级锁和表级锁

支持事务

支持外键

存储方式三: MEMORY内存

存储文件个数:表结构

优势 : 增删改查都很快(用于热点新闻)

数据存在内存中,表结构存在硬盘上,查询速度快

劣势 : 重启数据消失,容量有限

3.2表的创建

表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

3.2.1建表语句

#语法:

mysql>create table 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件],

);#注意：

1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选3. 字段名和类型是必须的

例一: mysql>create table t1 (id int,name char(4));

例二: mysql>create table t3 (id int,name char(4)) engine=myisam;

3.2.2.查看表结构

mysql>desc 表名查看表字段的基础信息

mysql>describe 表名;

mysql>show create table 表名;能够看到和这张表相关的所有信息

mysql>show create table 表名 \G能显示较为整洁

mysql>show create table 表名 \G;能显示较为整洁(加分号会报错)

使用分号或者\G

证明使用memory存储退出数据库后登录会清空.

3.3字段的数据类型

3.3.1数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

1.整数

总结

int默认是有符号的

它能表示的数字范围不被宽度约束

他只能约束数字的显示宽度

#默认创建(有符号)

mysql>create table t4 (id1 int,id2 int);#创建一个无符号的int数据

mysql>create table t5 (id1 int unsigned,id2 int);

2.小数

#create table t6 (f1 float(5,2),d1 double(5,2)); 会四舍五入#create table t7 (f1 float,d1 double);

create table t8 (d1 decimal,d2 decimal(25,20));#非常准确,能表示小数点后30位,后面不指定位数时默认保留整数

3.3.2时间类型

1.date / time / datetime

mysql>create table t4 (d date,t time,dt datetime);

Query OK, 0 rows affected (0.02sec)

​

mysql>desc t4;+-------+----------+------+-----+---------+-------+

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

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

| d | date | YES | | NULL | |

| t | time | YES | | NULL | |

| dt | datetime | YES | | NULL | |

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

3 rows in set (0.01sec)

​

mysql>insert into t4 values (now(),now(),now());

Query OK,1 row affected, 1 warning (0.01sec)

​

mysql> select * fromt4;+------------+----------+---------------------+

| d | t | dt |

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

| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |

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

1 row in set (0.00sec)

​

mysql>insert into t4 values (null,null,null);

Query OK,1 row affected (0.01sec)

​

mysql> select * fromt4;+------------+----------+---------------------+

| d | t | dt |

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

| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |

| NULL | NULL | NULL |

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

2 rows in set (0.00 sec)

2.timestemp

#示例一:

mysql>create table t5 (id1 timestamp);

Query OK, 0 rows affected (0.02sec)

​

mysql>desc t5;+-------+-----------+------+-----+-------------------+-----------------------------+

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

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

| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

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

1 row in set (0.00sec)

​#插入数据null，会自动插入当前时间的时间

mysql>insert into t5 values (null);

Query OK,1 row affected (0.00sec)

​

mysql> select * fromt5;+---------------------+

| id1 |

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

| 2018-09-21 14:56:50 |

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

1 row in set (0.00sec)

​#添加一列 默认值是'0000-00-00 00:00:00'

mysql>alter table t5 add id2 timestamp;

Query OK, 0 rows affected (0.02sec)

Records: 0 Duplicates: 0 Warnings: 0

​

mysql>show create table t5 \G;*************************** 1. row ***************************Table: t5

Create Table: CREATE TABLE `t5` (

`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`id2` timestamp NOT NULL DEFAULT'0000-00-00 00:00:00') ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

​

ERROR:

No query specified

​#手动修改新的列默认值为当前时间

mysql>alter table t5 modify id2 timestamp default current_timestamp;

Query OK, 0 rows affected (0.02sec)

Records: 0 Duplicates: 0 Warnings: 0

​

mysql>show create table t5 \G;*************************** 1. row ***************************Table: t5

Create Table: CREATE TABLE `t5` (

`id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

​

ERROR:

No query specified

​

mysql>insert into t5 values (null,null);

Query OK,1 row affected (0.01sec)

​

mysql> select * fromt5;+---------------------+---------------------+

| id1 | id2 |

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

| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |

| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |

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

2 rows in set (0.00 sec)

#示例二

mysql>create table t6 (t1 timestamp);

Query OK, 0 rows affected (0.02sec)

​

mysql>desc t6;+-------+-----------+------+-----+-------------------+-----------------------------+

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

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

| t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

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

1 row in set (0.01sec)

​

mysql> insert into t6 values (19700101080001);

Query OK,1 row affected (0.00sec)

​

mysql> select * fromt6;+---------------------+

| t1 |

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

| 1970-01-01 08:00:01 |

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

1 row in set (0.00sec)#timestamp时间的下限是19700101080001

mysql> insert into t6 values (19700101080000);

ERROR1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1​

mysql> insert into t6 values ('2038-01-19 11:14:07');

Query OK,1 row affected (0.00sec)#timestamp时间的上限是2038-01-19 11:14:07

mysql> insert into t6 values ('2038-01-19 11:14:08');

ERROR1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1mysql>

3.year

mysql>create table t7 (y year);

Query OK, 0 rows affected (0.02sec)

​

mysql> insert into t7 values (2018);

Query OK,1 row affected (0.00sec)

​

mysql> select * fromt7;+------+

| y |

+------+

| 2018 |

+------+

1 row in set (0.00 sec)

4.时间的写入

写入当前时间

mysql>create table tt1(y year,d date,dt datetime,ts timestamp);

Query OK, 0 rows affected (0.96sec)

​

mysql>insert into tt1 values(now(),now(),now(),now());

Query OK,1 row affected, 1 warning (0.21sec)

​

mysql> select * fromtt1;+------+------------+---------------------+---------------------+

| y | d | dt | ts |

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

| 2019 | 2019-09-28 | 2019-09-28 16:48:49 | 2019-09-28 16:48:49 |

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

1 row in set (0.00sec)

​

mysql> insert into tt1 (y) values (2018);

Query OK,1 row affected (0.14sec)

​

mysql> select * fromtt1;+------+------------+---------------------+---------------------+

| y | d | dt | ts |

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

| 2019 | 2019-09-28 | 2019-09-28 16:48:49 | 2019-09-28 16:48:49 |

| 2018 | NULL | NULL | 2019-09-28 16:50:38 |

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

2 rows in set (0.00sec)

​

mysql>show create table tt1;+-------+----------------------------------------------------------------------------------

| Table |Create Table--------------------------------------------------------+

| tt1 |CREATE TABLE `tt1` (

`y` year(4) DEFAULT NULL,

`d` date DEFAULT NULL,

`dt` datetime DEFAULT NULL,

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+----------------------------------------------------------------------------------create table t9(

y year,d date,

dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ts timestamp

);

写入任意时间

#数字型写入

mysql> insert into tt1 values(2019,20180701,20170701121212,20160102030405);

Query OK,1 row affected (0.10sec)

​#字符串型写入

mysql> insert into tt1 values(2019,20180701,'2017-07-01 12:12:12','2016-01-02 03:04:05');

Query OK,1 row affected (0.11sec)

​

mysql> select * fromtt1;+------+------------+---------------------+---------------------+

| y | d | dt | ts |

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

| 2019 | 2018-07-01 | 2017-07-01 12:12:12 | 2016-01-02 03:04:05 |

| 2019 | 2018-07-01 | 2017-07-01 12:12:12 | 2016-01-02 03:04:05 |

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

2 rows in set (0.00 sec)

另外这种方式也可以写入:

3.3.3字符串类型

char 定长的单位

存储时 :alex -----> alex ,

varchar 变长的单位

存储时:alex ------> alex4

哪一种存储方式好?

vachar :节省空间,存储效率相对低

char :浪费空间,存储效率相对高,长度变化小的

#手机号码,身份证号,用户名,密码 char *****#评论,微博,说说,微信状态 varchar

​

create table t11 (name1 char(5),name2 varchar(5));

3.3.4ENUM和SET类型

#ENUM 枚举#set 多选,自动去重

​

create table tt2(

name char(12),

gender ENUM('male','female'),

hobby set('抽烟','喝酒','烫头','洗脚')

);

3.3.5对象

blob 二进制大对象(存储图片,视频等媒体流信息)

clob 字符大对象(存储较大文本)

3.4约束

3.4.1 unsigned

设置某一个数字无符号

3.4.2 not null

某一个字段不能为空

create table t1(

id intnotnull,

name char(12) notnull,

age int

);

not null 不生效

#not null不生效：

不支持对not null字段插入null值

不支持对自增长字段插入"”值

不支持text字段有默认值

​#直接在mysql中生效(重启失效):

mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

​#配置文件添加(永久失效)：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"设置严格模式：

不支持对not null字段插入null值

不支持对自增长字段插入”值

不支持text字段有默认值

3.4.3default

给某个字段设置默认值

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

#创建表

create table t2(

id intnotnull,

name char(12) notnull,

age intnotnull,

gender enum('male','female') not null default 'male');

​#插入数据

insert into t2(id,name,age) values(1,'张三','20');

3.4.4 unique

设置某一个字段不能重复

#创建表

create table t3(

id int unique,

username char(12) unique,

password char(18)

);

​#插入数据

insert into t3 values(1,'张三','12345');

联合唯一

#创建表

create table t4(

id int,

ip char(15),

server char(10),

port int,

unique(ip,port)

);

​#插入数据

insert into t4 values(1,"192.168.12.11","mysql",3306);

3.4.5 auto_increment

设置某一个int类型的字段,自动增加

前提 :自增字段必须是 数字 且 必须唯一 ,自带非空属性

#

创建表

create table t5(

id int unique auto_increment,

name char(12)

);

​#插入数据

insert into t5 values(1,'张三');

3.4.6 primary

主键 :约束这个字段 非空(not null)且唯一(unique),的字段,你指定的第一个非空且唯一自动定义为主键

一张表只能设置一个主键且最好设置一个主键

单字段主键

#方法一：not null+unique

create table department1(

id intnot null unique, #主键

name varchar(20) notnull unique,

comment varchar(100)

);

​

mysql>desc department1;+---------+--------------+------+-----+---------+-------+

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

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

| id | int(11) | NO | PRI | NULL | |

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

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+rowsin set (0.01sec)

​#方法二：在某一个字段后用primary key

create table department2(

id int primary key,#主键

name varchar(20),

comment varchar(100)

);

​

mysql>desc department2;+---------+--------------+------+-----+---------+-------+

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

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

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+rowsin set (0.00sec)

​#方法三：在所有字段后单独定义primary key

create table department3(

id int,

name varchar(20),

comment varchar(100),

primary key(id)#创建主键并为其命名pk_name

);

​

mysql>desc department3;+---------+--------------+------+-----+---------+-------+

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

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

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+rowsin set (0.01sec)

​#方法四：给已经建成的表添加主键约束

mysql>create table department4(->id int,-> name varchar(20),-> comment varchar(100));

Query OK, 0 rows affected (0.01sec)

​

mysql>desc department4;+---------+--------------+------+-----+---------+-------+

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

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

| id | int(11) | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

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

3 rows in set (0.01sec)

​

mysql>alter table department4 modify id int primary key;

Query OK, 0 rows affected (0.02sec)

Records: 0 Duplicates: 0 Warnings: 0

​

mysql>desc department4;+---------+--------------+------+-----+---------+-------+

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

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

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

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

3 rows in set (0.01 sec)

多字段主键

create table service(

ip varchar(15),

port char(5),

service_name varchar(10) notnull,

primary key(ip,port)

);

​

​

mysql>desc service;+--------------+-------------+------+-----+---------+-------+

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

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

| ip | varchar(15) | NO | PRI | NULL | |

| port | char(5) | NO | PRI | NULL | |

| service_name | varchar(10) | NO | | NULL | |

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

3 rows in set (0.00sec)

​

mysql>insert into service values-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','3306','mariadb')->;

Query OK,2 rows affected (0.00sec)

Records:2Duplicates: 0 Warnings: 0

​

mysql> insert into service values ('172.16.45.10','3306','nginx');

ERROR1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

联合主键(不常用)

3.4.7foreign key

#创建表

​#部门表

create table post(

pid int primary key,

postname char(10) notnull unique,

comment varchar(255),

phone_num char(11)

);

​#员工表

create table staff(

id int primary key auto_increment,

age int,

gender enum('male','female'),

salary float(8,2),

hire_date date,

post_id int,

foreign key(post_id) references post(pid)

);#注:外键关联的那张表的字段必须为unique

​#插入数据

insert into post values(1,'java部','一个专职于java的部门','1234567');

insert into staff values(1,20,'male',10000,'20181201030405',1);

cascade方式 :在父表上update/delete记录时，同步update/delete掉子表的匹配记录

set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null

Set default方式 :父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

create table staff2(

id int primary key auto_increment,

age int,

gender enum('male','female'),

salary float(8,2),

hire_date date,

post_id int,

foreign key(post_id) references post(pid) on update cascade on delete set null

);

3.5 表结构的修改

修改表名

alter table 表名 rename 新表名;

增加字段

alter table 表名 add 字段名 数据类型 [完整性约束条件];

删除字段

alter table 表名 drop 字段名;

修改字段

alter table 表名 modify 字段名 数据类型 [完整性的约束条件];

修改已经存在的字段的类型,宽度和约束

alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

修改已经存在的字段的类型,宽度,约束 及字段名字

修改字段排列顺序/在增加的时候指定字段位置

例 : 原字段顺序 id name age

alter table 表名 modify age int not null after id

将age 字段放在name字段之前

alter table 表名 modify age int not null first

3.6 表与表之间的关系

3.6.1一对多

foreign key 永远是在多的那张表中设置外键

多个学生都是一个班级的

学生表 关联 班级表

学生是多 ,班级是一

3.6.2一对一

foreign key +unique 后出现的的那张表中的数据作为外键(并且约束这个外键唯一)

客户关系表 :手机号码,招生老师,上次联系时间,备注信息

学生表 :姓名,入学日期,缴费日期,结业时间

3.6.3多对多

产生第三张表,把两个关联关系的字段作为第三张表的外键

书

作者