MySQL数据表的基本操作

时间:2021-12-20 05:23:03

约束

主键约束

  主键,又称主码,是表中一列或多列的组合。主键约束(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)

uniqueprimary 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语句中可以顺利执行,但会发出警告。需要注意的是:当数据表中存在外键关联的情况下,直接删除主表会显示删除失败,需要先接触外键约束条件