MYSQL : 数据表的基本操作

数据表的基本操作

1. 创建数据表

1.1.1 规则

create table table_name(
	字段名1，数据类型 [列级别约束条件][默认值],
	字段名2，数据类型 [列级别约束条件][默认值],
	字段名3，数据类型 [列级别约束条件][默认值],
	字段名4，数据类型 [列级别约束条件][默认值],
	......
	[表级别约束条件]
)；

使用create table 创建表时，必须要指定以下信息：

1. 要创建表的名称，不区分大小写，不能使用SQL语言中的关键字
2. 数据表中的每一列（字段）的名称和数据类型，如果创建多个列，需要用逗号隔开。

示例：

create database test;
use test;
create table tb_emp1(
	id       INT(11),
    name     VARCHAR(25),
    deptId   INT(11),
    salary   FLOAT
);

1.1.2 显示所有的表

show tables;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MmjOFofB-1610716445935)(C:\Users\book\AppData\Roaming\Typora\typora-user-images\image-20210115164810220.png)]

1.1.3 显示特定的表内结构

desc tb_emp1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qYEk2jfC-1610716445936)(C:\Users\book\AppData\Roaming\Typora\typora-user-images\image-20210115165114932.png)]

1.1.4 使用主键约束

主键数据唯一，并且不允许为空。

可以唯一的标识表中的一条记录，可以加快查询速度

分为两种类型：单字段主键&多字段联合主键。

1.1.4.1 单字段主键

主键由一个字段组成

SQL语句格式分为以下两种个情况

1.1.4.1.1 在定义列的同时指定主键

语法规则：

字段名 数据类型 PRIMARY KEY [默认值]

实例：

create table tb_emp2(
	id      INT(11) primary key,
    name    VARCHAR(25),
    deptId  INT(11),
    salary  FLOAT
);

desc tb_emp2结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YFRlFBME-1610716445938)(C:\Users\book\AppData\Roaming\Typora\typora-user-images\image-20210115170141304.png)]

可以发现在id行KEY列有一个PRI标记，即主键标记

1.1.4.1.2 在定义完所有的列之后指定主键

语法规则：

[CONSTRAINT<约束名>]PRIMARY KEY [字段名]

示例：

create table tb_emp3(
	id      INT(11),
    name    VARCHAR(25),
    deptld  INT(11),
    salary  FLOAT,
    primary key(id)
)

可以产生同样的结果

1.1.4.2 多字段联合主键

主键由多个字段联合组成

语法规则：

PRIMARY KEY [字段1，字段2,...,字段n]

示例：

假设表中没有主键id，为了唯一确定一个员工，可以联合使用name和deptId作为主键。

create table tb_emp4(
	name      VARCHAR(25),
    deptId    INT(11),
    salary    FLOAT,
    PRIMARY KEY(name, deptID)
)

显示表结构

desc tb_emp4;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-X3hncxzf-1610716445940)(C:\Users\book\AppData\Roaming\Typora\typora-user-images\image-20210115183924452.png)]

1.1.5 使用外键约束

外键：用来在两个表的数据之间简历链接，它可以时一列或者多列。一个表可以由一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空，则每一个外键值必须等于另一个表中主键的某个值

语法规则

[CONSTRAINT<外键名>]FOREIGN KEY 字段名1[,字段名2,字段名3,······]
REFERENCE<主表名> 主表列1[,主表列2,主表列3,······]

示例：

部门表：主表

create table tb_dept(
	id          int(11)primary key,
    name        varchar(25),not null,
    location    varchar(25)
);

数据表：从表

create table tb_emp5(
	id         int(11)primary key,
    name       varchar(25),
    deptd      int(11),
    salary     float,
    constraint fk_emp_dept foreign key(deptId) references tb_dept1(id)
);

1.1.6 使用非空约束

指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

字段名  数据类型  not null

1.1.7 使用唯一约束

要求该列唯一，允许为空。但只能出现一个空值

两种定义方式：

1. 在定义完列之后直接指定唯一约束

   字段名 数据类型 UNIQUE
   

   示例：

   create table tb_dept2(
   	id          int(11) primary key,
       name        varchar(25) unique,
       location    varchar(50)
   );
   

2. 在定义完所有列之后指定唯一约束

   [constraint <约束名>] UNIQUE(<字段名>)
   

   示例：

   create table tb_dept3(
   	id           int(11) primary key,
       name         varchar(25),
       location     varchar(50),
       constraint   sth unique(name)
   );
   

1.1.8 使用默认约束

字段名 数据类型 DEFAULT 默认值

1.1.9 设置表的属性值自动增加

一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分

字段名  数据类型  AUTO_INCREMENT

示例:

create table tb_emp8(
	id        int(11) primary key auto_increment,
    name      varchar(25) not null,
    deptld    int(11),
    salary    float
)

2. 查看数据表结构

desc 表名

查看表基本结构

shwo create table <表名\G>

显示创建表时的语句，不加G会使格式不易阅读。

3. 修改数据表

主要是通过 alter table 实现的

3.1 修改表名

alter table <旧表名> rename <新表名>;

3.2 修改字段的数据类型

alter table <表名> modify <字段名> <数据类型>

3.3 修改字段名

alter table <表名> change <旧字段名> <新字段名><新数据类型>;

3.4 添加字段

alter table <表名> add <新字段名> <数据类型> [约束条件][first|after 已存在字段名]：

[]内的为可选

first代表在第一个的位置添加字段

after 已存在的字段名，表示在已经存在的字段名之后添加字段

3.5 删除字段

alter table <表名> drop <字段名>;

最好在删除前desc一下查看表内有哪些字段

3.6 修改字段的排列位置

在建成表之后，修改其排列顺序

alter table <表名> modify <字段1> <数据类型> first|after <字段2>;

first 为将 字段1 改为 第一个 的位置

after <字段2> 为将 字段1 放到 字段2 之后的位置

3.7 更改表的存储引擎

alter table <表名> engine=<更改后的存储引擎名>;

3.8 删除表的外键约束

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