约束
主键约束
主键,又称主码,是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空。主键能够唯一地标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询速度。
主键分为两种类型:
-
单字段主键
在定义列的同时指定主键:
字段名 数据类型 primary key
在定义完所有列之后指定主键:constraint <约束名> primary key [字段名]
-
多字段联合主键
多字段联合主键语法如下:
primary key [字段1,字段2,...字段n]
例如:primary key(id,name)
外键约束
外键用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或者多个外键。一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中的主键的某个值。
外键:首先它是表中的一个字段,它可以不是本表的主键,但对应另一个表的主键。外键的作用是保持数据的一致性、完整性。
主表:对于两个具有关联的表而言,相关联字段中主键所在的那个表即是主表。
从表:对于两个具有关联的表而言,相关联字段中外键所在的那个表即是从表。
创建外键的语法规则如下:constraint <外键名> foreign key [字段1,字段2,....字段n] references <主表名> [主键列1,主键列2,...主键列n]
例如:constraint test foreign key(name) references table_1(id);
从表的外键必须关联主表的主键,且关联字段的数据类型必须一致
非空约束
非空约束指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时未指定值,数据库系统会报错。
非空约束语法规则如下:字段名 数据类型 not null
唯一约束
唯一性约束,要求该列唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复的值。
唯一性约束语法规则如下:
-
定义完列之后直接指定唯一性约束
字段名 数据类型 unique
-
定义完所有列之后指定唯一性约束
constraint <约束名> unique [字段名]
例如:constraint test unique(id)
unique
和primary key
的区别:一个表中可以有多个字段声明为unique
,但只能有一个primary key
声明;声明为primary key
的列不允许有空值,但声明为unique
的字段允许空值的存在。
默认约束
默认约束,指定某列的默认值。如男性员工比较多,性别就可以默认为“男”,如果插入一条新的记录时没有给这个字段赋值,那么系统就会自动为这个字段赋值为“男”。
默认约束的语法规则如下:字段名 数据类型 default
例如:sex varchar(25) default man ;
设置表的属性值自动增加
在数据库应用中,经常希望在每次插入新纪录时,系统自动生成字段的主键值。通过auto_increment
,一个表只能有一个字段使用auto_increment
约束,且该字段必须为主键的一部分。auto_increment
约束的字段可以是任何整数类型。
设置表的属性值自动增加的语法规则如下:字段名 数据类型 auto_increment
数据表结构的操作
查看
查看表基本结构语句
describe/desc语句可以查看表的字段信息,其中包括:字段名、字段数据类型、是否为主键,是否有默认值等。
语法规则如下:describe 表名
==desc 表名
例:
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
查看表的详细结构
show create table
语句可以用来显示创建表时的create table
语句,语法格式如下:show create table 表名\G;
例:
mysql> show create table user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
修改数据表
修改表名
语法规则如下:alter table 旧表名 rename 新表名
修改表名不影响创建表时的数据结构。
修改字段数据类型
语法规则如下:alter table 表名 modify 字段名 [数据类型]
例:alter table test modify name varchar(30);
修改字段名
语法规则如下:alter table 表名 change 旧字段名 新字段名 [新数据类型]
例:alter table test change name user_name varchar(30);
添加字段
语法规则如下:alter table 表名 add 新字段名 [数据类型] [约束条件] [first|after 已存在字段名] ;
例:alter table test add id int(11) first;
在表的第一列添加一个id字段。
alter table test add id int(11) after name;
在表的name列下添加一个id字段。
删除字段
语法规则如下:alter table 表名 drop 字段名
修改字段的排列位置
语法规则如下:alter table 表名 modify 字段1 [数据类型] first|after 字段2;
例:alter table test modify id varchar(12) first;
将id字段调整到表中的第一列
alter table test modify id varchar(12) after name
将id字段调整到表中name字段后
更改表的存储引擎
可以使用show engines
来查看MySQL支持的存储引擎。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
更改表的存储引擎的语法格式如下:alter table 表名 engine=更改后的存储引擎名
例:alter table test engine=MyISAM;
删除表的外键约束
语法规则如下:alter table 表名 drop foreign key 外键约束名
删除数据表
drop table [if exists] 表1,表2...,表n;
参数if exists
用于在删除前判断删除的表是否存在,加上改参数后,再删除表的时候,如果表不存在,SQL语句中可以顺利执行,但会发出警告。需要注意的是:当数据表中存在外键关联的情况下,直接删除主表会显示删除失败,需要先接触外键约束条件