第二课:MySQL 数据定义

一、定义数据库

1、创建数据库

基本语法格式:

create {database | schema} [if not exists] db_name

[[default] character set [=] charset_name   

[[default] collate [=] collation_name];

●[ ]表示可选项

●db_name:数据库名,不区分大小写

if not exists:检测数据库是否已存在,数据库中不存在此数据库才创建数据库

●character set:用于指定数据库字集符

●collate:用于指定字集符的校对规则

●default:指定默认的数据库字集符和字集符校对规则

2、选择与查看数据库

选择数据库:use db_name;

查看数据库:show {databases | schemas};

3、修改数据库

基本语法格式:

alter {database | schema}db_name

[[default] character set [=] charset_name   // 修改数据库默认字符集和字符集校验规则

[[default] collate [=] collation_name];

4、删除数据库

drop {database | schema} [if exists] db_name;

二、定义表

1、常用数据类型

1.1、数值类型

bit[(M)]:位字段类型。M表示每个值的位数,范围1~64,默认为1

◆tinyint[(M)][unsigned][zerofill]:很小的整数,带符号范围-128~127,不带符号范围0~255

◆bool,boolean:tinyint(1)的同义词,zero值视为假,非zero值视为真

◆smallint[(M)][unsigned][zerofill]:小的整数

◆mediumint[(M)][unsigned][zerofill]:中等大小的整数

int[(M)][unsigned][zerofill]:普通大小的整数

integer[(M)][unsigned][zerofill]:int的同义词

◆bigint[(M)][unsigned][zerofill]:大的整数

double[(M,D)][unsigned][zerofill]:普通大小(双精度)的浮点数。M表示小数总位数,D表示小数点后的位数,如果指定unsigned,不允许负值

◆decimal[(M,D)][unsigned][zerofill]:压缩的“严格”定点数。D默认为0,M默认为10,如果指定unsigned,不允许负值

◆dec:decimal的同义词

1.2、日期和时间类型

?date:日期型,格式‘YYYY-MM-DD’

?datetime:日期和时间的组合,格式‘YYYY-MM-DD HH:MM:SS’

?timestamp:时间戳,用于insert或update操作时记录日期和时间

?time:时间型,格式‘HH:MM:SS’

?year[(2|4)]:两位或四位格式的年,格式‘YYYY’

1.3、字符串类型

?char[(M)]、character[(M)]:固定长度的字符数据类型,用于保存以文本格式存储的信息。M表示列长度,范围0~255个字符

?varchar[(M)]:可变长的字符数据类型,用于保存以文本格式存储的信息。M表示最大列长度,范围0~65535个字符

?tinytext:最大长度为255(28-1)字符的text列

?text[(M)]:最大长度为65535(216-1)字符的text列

2、创建表

基本语法格式:

Create table tb_name

(

字段名1 数据类型 [列级完整性约束条件] [默认值]

[,字段名2 数据类型 [列级完整性约束条件] [默认值]]

[,......]

[,表级完整性约束条件]

)[engine=引擎类型];

?auto_inncrement:为列设置自增属性,只有整型列才可以设置,每个表只能定义一个该属性的列。默认初始值为1

?default:设置默认值

3、查看表

3.1、查看表名称

show tables [{from | in} db_name];

3.2、查看数据表基本结构

?show columns {from | in} tb_name [{from | in} db_name];

?{describe |desc} tb_name;

3.3、查看数据表的详细结构

show create table tb_name;

使用show create table 语句不仅可以查看创建表时的详细语句,还可以查看存储引擎和字符编码

4、修改表

4.1、添加字段

alter table tb_name add [column] 新字段名 数据类型 [约束条件] [first| after 已有字段名];

?first | after 已有字段名:用于指定新添加的字段在表中的位置,“first”表示新添加的字段设置为表的第一个字段,“after 已有字段名”表示新添加的字段加到已有字段名的后面

?约束条件:指定字段取值不为空、字段默认值、主键以及候选键约束等

4.2、修改字段

?change [column]子句:可同时修改表中指定列的名称和数据类型,alter table语句中可同时添加多个change [column]子句,子句间用逗号隔开。

alter table tb_name change [column] 原字段名 新字段名 数据类型 [约束条件];

?alter [column]子句:可以修改或删除表中指定列的默认值

alter table tb_name alter [column] 字段名 {set|drop} degault;

?modify [column]子句:只会修改指定列的数据类型,可以通过first和after关键字修改指定列在表中的位置

alter table tb_name modify [column] 字段名 数据类型 [约束条件] [first| after 已有字段名];

4.3、删除字段

alter table tb_name drop [column] 字段名;

一旦删除列,原本存储在该列中的一切内容都会跟着删除

5、重命名表

4.1、rename [to] 子句

alter table 原表名 rename [to] 新表名;

4.2、rename table

rename table 原表名1 to 新表名1 [,原表名2 to 新表名2]......;

6、删除表

drop table [if exists] 表1 [,表2]......;

可同时删除多个表,表与表之间用逗号隔开。删除表时,表的定义和表中所有的数据都会被删除,用户在该表上的权限并不会自动被删除

三、数据的完整性约束

1、定义实体完整性

实体完整性是指关系的主属性不能取空值,即主键和候选键在关系中所对应的属性都不能取空值。MySQL中实体完整性通过主键约束和候选键约束来实现

1.1、主键约束

主键指表中某一列或某些列所构成的一个组合,主键值必须是唯一的且不许为空

?一个表只能定义一个主键

?键值必须唯一且不能为NULL

?复合主键不能包含不必要的多余列,最小化规则

?一个列名在复合主键的列表中只能出现一次

主键约束在create table 或 alter table 语句中使用关键词 primary key

?作为列级完整性约束:在表中某字段定义后加关键词primary key

?作为表级完整性约束,在表中所有字段后面添加 primary key (index_col_name)

1.2、完整性约束的命名

可以对完整性约束进行添加、删除、修改等操作

基本语法格式:

constraint <symbol> { primary key (主键字段列表)| unique(候选键字段列表) | foreign key (外键自选列表) references tb_被参照对象(主键字段列表)| check (约束条件表达式)};

?symbol:为指定的约束名

1.3、候选键约束

候选键指表中某一列或某些列所构成的一个组合,主键值必须是唯一的且不许为NULL

候选键约束在create table 或 alter table 语句中使用关键词 unique,实现方式与主键约束相似

MySQL中primary key和unique之间的区别:

?一个表中只能创建一个primary key,但可以有若干个unique

?定义为primary key的列不允许空值,但定义为unique的字段允许有空值的存在

?定义primary key约束时,系统自动产生primary key索引,定义unique约束时,系统自动产生unique索引

2、定义参照完整性

外键是一个表的一个或一组属性,它不是这个表主键,但它对应另一个表的主键。定义外键后,不允许删除外键引用的另一个表中具有关联关系的记录

参照完整性规则:定义的是外键与主键之间的引用规则,外键的取值或为空,或等于被参照关系中某个主键的值

2.1、定义外键的规则:

?被参照表必须已经创建,或必须是当前正在创建的表(自参照表)

?必须为被参照表定义的主键或候选键

?必须在被参照表的表明后面指定列名或列名的组合(必须为主键或候选键)

?主键不能为空值,但允许在外键中出现空值

?外键对应列的数目必须和被参照表的主键对应列的数目相同

?外键对应列的数据类型必须和被参照表的主键对应列的数据类型相同

2.2、基本语法格式:

?作为列级完整性约束方式定义外键:在表中某字段定义后添加references tb_被参照对象(主键字段列表)

?作为表级完整性约束方式定义外键,在表中所有字段后面添加constraint <symbol> foreign key (外键自选列表) references tb_被参照对象(主键字段列表)

2.3、参照动作

给外键定义参照动作,包含两部分:一是指定参照动作适用的语句,即updatedelete语句;二是要指定采取的动作,即cascaderestrict(默认)、set nullno actionset default

?restrict:限制策略,即当删除或修改被参照表中被参照列上且在外键中出现的值时,系统拒绝对被参照表的删除或修改操作

?cascade:联级策略,即从被参照表中删除或修改记录时,自动删除或修改参照表中匹配的记录

?set null:置空策略,即从被参照表中删除或修改记录时,设置参照表中与之对应的外键列的值为null(这个策略需要被参照表中的外键列没有申明限制词not null)

?no action:表示不采取实施措施,该策略的动作语义同restrict

?set default:默认值策略,即从被参照表中删除或修改记录时,设置参照表中与之对应的外键列的值为默认值(这个策略要求已经为该列定义了默认值)

外键只可以用在使用存储引擎InnoDB创建的表中,其他的存储引擎不支持外键

3、用户定义的完整性

MySQL支持几种用户自定义完整性约束,包括非空约束、check约束和触发器。

3.1、非空约束

非空约束是指字段的值不能为空

在create table 或 alter table 语句中使用非空约束,在某个列定义后面加上关键词not null作为限制词,来约束该列的取值不能为空

3.2、check约束

check约束也是在create table 或 alter table 语句中使用,需要指定限制条件

check约束的语法格式:

check (expr);

check约束可以分为列级或表级完整性约束:

●作为表级完整性约束:在表中所有字段后面添加constraint <symbol> check (约束条件表达式)

●作为列级完整性约束:在表中某字段定义后添加check (约束条件表达式)

4、更新完整性约束

4.1、删除约束

drop table删除表,自动删除所有完整性约束

alter table 语句可以单独的删除完整性约束,而不删除表本身

◆删除外键约束

alter table <表名> drop foreign key <外键约束名>;

◆删除主键约束

alter table <表名> drop primary key

◆删除候选键约束

alter table <表名> drop {约束名|候选键字段名}

4.2、添加约束

■添加主键约束

alter table <表名> add [constraint <约束名>] primary key (主键字段)

■添加外键约束

alter table <表名> add [constraint <约束名>] foreign key (外键字段名) references 被参照表(主键字段名)

■添加候选键约束

alter table <表名> add [constraint <约束名>] unique key (字段名)

完整性约束不能直接被修改,若要修改某个约束,实际上是用alter table语句先删除约束,然后再添加一个同名的新约束

陈双义博客

打赏 支付宝打赏 微信打赏
本文标题:第二课:MySQL 数据定义
本文链接:https://www.chenshuangyi.com/post/510.html
作者授权:除特别说明外,本文由 陈双义 原创编译并授权 陈双义博客 - 互联网Power 刊载发布。
版权声明:本文使用「署名-非商业性使用-相同方式共享」创作共享协议,转载或使用请遵守署名协议。

为您推荐

干货 | 软件性能测试基本测试概念

干货 | 软件性能测试基本测试概念

一、性能测试的目的1、 评估当前系统2、 寻找瓶颈3、 预测未来性能二、性能测试的前提:接...

  第十二课:MySQL 数据库的应用编程

第十二课:MySQL 数据库的应用编程

一、使用PHP进行MySQL数据库应用编程1、建立与MySQL数据库服务器的连接1.1、使用函数mysql_connec...

2019-03-21 标签:MySQL数据库软件测试
第十一课:MySQL 数据库备份与恢复

第十一课:MySQL 数据库备份与恢复

一、MySQL数据库备份与恢复1、使用SQL语句备份和恢复表数据1.1、select into...outfile语句导...

2019-03-21 标签:MySQL数据库软件测试
第十课:MySQL 访问控制与安全管理

第十课:MySQL 访问控制与安全管理

一、用户账户管理1、创建账户基本语法结构:create user user_specification[,us...

2019-03-20 标签:MySQL数据库软件测试
第九课:MySQL 存储过程和存储函数

第九课:MySQL 存储过程和存储函数

一、存储过程存储过程是一组为了完成某特定功能的SQL语句集。一个存储过程是一个可编程的函数,同时可以看做是在数据库编程中...

2019-03-18 标签:MySQL数据库软件测试
第八课:MySQL 事件

第八课:MySQL 事件

一、事件事件和触发器相似,都是在某些事情发生的时候启动,事件也叫作临时触发器事件基于特定时间周期触发来执行某些任务,而触...

2019-03-05 标签:MySQL数据库软件测试
第七课:MySQL 触发器

第七课:MySQL 触发器

一、触发器触发器是一个被指定关联到一个表的数据库对象。触发器用于保障数据库中数据的完整性,以及多个表之间数的一致性。触发...

2019-03-05 标签:MySQL数据库软件测试
第六课:MySQL 视图

第六课:MySQL 视图

一、视图视图是从一个或多个表或者视图中导出的表,也包含一系列带有名称的数据列和若干条数据行视图不同于数据库真实存在的表:...

2019-03-05 标签:MySQL数据库软件测试

发表评论

«   2019年7月   »
1234567
891011121314
15161718192021
22232425262728
293031
云服务器
轻量应用
站点信息
  • 文章总数:947
  • 页面总数:10
  • 分类总数:19
  • 标签总数:1530
  • 评论总数:152
  • 浏览总数:427043

当前非电脑浏览器正常宽度,请使用移动设备访问本站!