MySQL~操作表

作者: Cyzhouke | 来源:发表于2020-06-18 20:38 被阅读0次

MySql笔记
基于Linux的MySQL操作实例（软件安装，mysql基本操作
3 MySQL数据库操作
Mysql大全
Mysql学习——MySQL数据结构修改（2）
Mysql 相关
My SQL 3
从0到1学习网络安全【MySQL基础-MySQL 删除数据表】
mysql 表操作
MYsql表操作

一、表操作

1、创建表--)CREATE TABLE

CREATE TABLE <表名> ([表定义选项]) [表选项] [分区选项];
# [表定义选项]的格式为：
<列名1> <类型1> [,...] <列名n> <类型n>

# 语法说明：
CREATE TABLE：用于创建给定名称的表，必须拥有CREATE的权限。
<表定义选项>：表创建定义，由列名(col_name)、列的定义(column_definition)以及可能的空值说明、完整性约束或表索引组成。

一般在语法格式描述中会使用如下符号：

<>：表示在语句中必须指定的数据对象，是不可或缺的。
[ ]：表示可以根据需要进行选择，也可以不选。
| ：表示多个选项只能选择其一。
{ }：表示必选项。

示例：创建员工表tb_emp1

CREATE TABLE tb_emp1
(
    id INT(11),                              # 员工编号
    name VARCHAR(25),                        # 员工名称
    deptId INT(11),                          # 员工所在部门编号
    salary FLOAT                             # 工资
);

2、查看表结构--)DESC或SHOW CREATE TABLE

# 以表格形式显示表结构
{ DESCRIBE | DESC } <表名>;

# 以原始SQL语句显示表结构
SHOW CREATE TABLE <表名>\G;
# 使用SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。
# 如果不加“\G”参数，显示结果可能非常混乱

3、修改表--)ALTER TABLE

修改表指的是修改数据库中已存在的数据库表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有修改表名，修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

ALTER TABLE <表名> [修改选项];

# 修改选项的语法格式如下：
{ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> 
}

一般在语法格式描述中会使用如下符号：

<>：表示在语句中必须指定的数据对象，是不可或缺的。
[ ]：表示可以根据需要进行选择，也可以不选。
| ：表示多个选项只能选择其一。
{ }：表示必选项。

3.1、添加字段--)ADD

ALTER TABLE <表名> ADD [COLUMN] <新字段名> <数据类型> 
    [约束条件] [FIRST | AFTER 已存在的字段名];
    
# 语法说明：
新字段名为需要添加的字段的名称;
FIRST为可选参数，其作用是将新添加的字段设置为表的第一个字段;
AFTER为可选参数，其作用是将新添加的字段添加到指定的“已存在的字段名”的后面;

3.2、修改字段数据类型--)MODIFY

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

# 语法说明：
<表名>：指要修改数据类型的字段所在表的名称。
<字段名>：指需要修改的字段。
<数据类型>：指修改后字段的新数据类型。

3.3、删除表数据--)DROP

ALTER TABLE <表名> DROP <字段名>;

# 语法说明：
<字段名>：指需要从表中删除的字段的名称。

3.4、修改字段名称--)CHANGE

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

# 语法说明：
<旧字段名>：指修改前的字段名
<新字段名>：指修改后的字段名。
<新数据类型>：指修改后的数据类型，如果不需要修改字段的数据类型，可以将数据类型设置成与原来一样，但数据类型不能为空。

CHANGE也可以只修改数据类型，只需要将“旧字段名”与“新字段名”设置为相同的名称，只修改“数据类型”。

3.5、修改表名--)RENAME

ALTER TABLE <表名> RENAME [TO] <新表名>;

# 语法说明：
TO：可选参数，使用与否不影响结果

4、删除表--)DELETE TABLE

DROP TABLE [IF EXISTS] <表名> [，<表名1>，<表名1>]...

# 语法说明：
<表名>：被删除的表名，DROP TABLE语句可以同时删除多个表。
IF EXISTS：用于在删除前判断删除的表是否存在，加上该参数后，在删除表的时候，如果表不存在，SQL语句可以顺利执行，但会发生警告。

二、表约束

1、约束的作用

在MySQL中，各种完整性约束是数据库关系模型定义的一部分，可以通过CREATE TABLE或ALTER TABLE语句来定义。对关系模型定义完整性约束，则是为了在数据库应用中保障数据的正确性和一致性，防止数据库中存在不符合语义的、不正确的数据。一旦定义了完整性约束，MySQL服务器会随时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性与正确性。防止操作对数据库的意外破坏，也能提高完整性检测的效率。
关系模型中有三类完整性约束，分别是实体完整性、参照完整性和用户定义完整性。其中，实体完整性和参照完整性是关系模型必须满足的完整性约束条件，被称为是关系的两个不变性。

实体完整性：主键约束、唯一性约束
参照完整性：外键约束
用户定义完整性：非空约束、CHECK约束

2、主键约束

主键(PRIMARY KEY)是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以定义表的实体完整性。

2.1、选取设置主键约束的字段

主键约束即在表中定义一个主键来唯一确定表中的每一行数据的标识。主键可以是某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键的值必须是唯一的，而且构成主键的每一列的值都不允许为空。
主键列应该遵守下面的规则：

每个表只能定义一个主键。
主键值必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
一个列名只能在复合主键列表中出现一次。
复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的，这是最小化原则。

2.2、在创建表时设置主键约束

方法一：在定义列的同时指定主键

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

示例：创建tb_emp数据表，其主键为id

CREATE TABLE tb_emp(
id INT(11) PRIMARY KEY,        # 主键
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

方法二：在定义完所有列之后，指定主键

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

示例：创建tb_emp1数据表，其主键为id

CREATE TABLE tb_emp1(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)                # 主键
);

2.3、在创建表时设置复合主键

# 复合主键由多个字段联合组成：
PRIMARY KEY [字段1，字段2，...]

示例：创建数据表tb_emp2，假设表中没有主键id，为了唯一确定一个员工，可以将name、deptId联合起来作为主键

CREATE TABLE tb_emp2(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);

2.4、在修改表时添加主键约束

ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>)

示例：创建数据表tb_emp3后，修改数据表tb_emp3，将id设置为主键

CREATE TABLE tb_emp3(
id INT(11),    
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
);
ALTER TABLE tb_emp3 ADD PRIMARY KEY(id);

3、外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表tb_dept1的主键id，在员工表tb_emp4有一个键deptId与这个id关联。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

3.1、选取设置外键约束的字段

定义一个外键时，需要遵守的规则：

父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为父表定义主键。
主键不能包含空格，但允许在外键中出现空值，也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或者候选键。
外键中列的数目必须和父表的主键中列的数目相同。
外键中列的数据类型必须和父表主键中对应列的数据类型相同。

3.2、在创建表时设置外键约束

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，...]
REFERENCES <主表名> 主键列1 [，主键列2，...]

# 语法说明：
<外键名>：定义的外键约束的名称，一个表中不能有相同名称的外键。
字段名：表示子表需要添加外键约束的字段列
<主表名>：被子表外键所依赖的表的名称
主键列：表示主表中定义的主键列或者列组合

示例：创建部门表tb_dept1

CREATE TABLE tb_dept1(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);

示例：创建数据表tb_emp4，并在表tb_emp4上创建外键约束，让它的键deptId作为外键关联到表tb_dept1的主键id

CREATE TABLE tb_emp4(
id INT(11) PRIMARY KEY,    
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept1
FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

关联指的是关系数据库中，相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误“ERROR 1005(HY000):Cant't create table 'database.tablename'(errno:150)”。

3.3、在修改表时添加外键约束

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

示例：修改数据表tb_emp3，将字段deptId设置为外键，与数据表tb_dept1的主键id进行关联

ALTER TABLE tb_emp3 ADD CONSTRAINT fk_tb_dept1 
FOREIGN KEY (deptId) REFERENCES tb_dept1(id);

3.4、删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>       
# 外键删除后，就会解除主表和从表之间的关联关系

示例：删除tb_emp4中的外键约束fk_tb_dept1

ALTER TABLE tb_emp4 DROP FOREIGN KEY fk_tb_dept1;

4、唯一约束

4.1、选取设置唯一约束的字段

唯一约束要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

4.2、在创建表时设置唯一约束

<字段名> <数据类型> UNIQUE

示例：创建数据表tb_dept2，指定部门的名称唯一

CREATE TABLE tb_dept2(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);

mysql中PRIMARY KEY与UNIQUE之间的区别：

一个表只能创建一个PRIMARY KEY，但可以定义多个UNIQUE.
定义为PRIMARY KEY的列不允许有空值，但UNIQUE的字段允许。
定义约束时，系统会自动产生索引。

4.3、在修改表时添加唯一约束

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE (<列名>);

示例：修改数据表tb_dept1，指定部门的名称唯一

ALTER TABLE tb_dept1 ADD CONSTRAINT unique_name UNIQUE (name);

4.4、删除唯一约束

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

示例：删除数据表tb_dept1中的唯一约束unique_name

ALTER TABLE tb_dept1 DROP INDEX unique_name;

5、检查约束

5.1、选取设置检查约束的字段

CHECK约束可以通过CREATE TABLE或ALTER TABLE语句来实现。

CHECK <表达式>

# 语法说明：
<表达式>：SQL表达式，用于指定需要检查的限定条件

若将CHECK约束子句置于表中某个列的定义之后，则这种约束也称为基于列的CHECK约束。
若将CHECK约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的CHECK约束。
在更新表数据的时候，系统会检查更新后的数据行是否满足CHECK约束中的限定条件。CHECK约束可以使用简单的表达式，也可以使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

5.2、在创建表时设置检查约束

check(<检查约束>)

示例：创建tb_emp5数据表，要求salary字段值大于0且小于10000

CREATE TABLE tb_emp5(
id INT(11) PRIMARY KEY,    
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CHECK(salary>0 and salary<10000),
FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

5.3、在修改表时添加检查约束

ALTER TABLE <数据表名> ADD CONSTRAINT <检查约束名> CHECK (<检查约束>);

示例：修改tb_dept数据表，要求id字段值大于0,

ALTER TABLE tb_dept ADD CONSTRAINT check_id CHECK (id>0);

5.4、删除检查约束

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

6、默认值约束

6.1、选取设置默认值约束的字段

默认值约束指定某列的默认值

6.2、在创建表时设置默认值约束

<字段名> <数据类型> DEFAULT <默认值>;

示例：创建数据表tb_dept3，指定部门位置默认为Beijing

CREATE TABLE tb_dept3(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50) DEFAULT 'Beijing'
);

6.3、在修改表时添加默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

示例：修改数据表tb_dept3，将部门位置的默认值修改为Shanghai

ALTER TABLE tb_dept3 
CHANGE COLUMN location location VARCHAR(50) DEFAULT 'Shanghai';

6.4、删除默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

示例：修改数据表tb_dept3，将部门位置的默认值约束删除

ALTER TABLE tb_dept3
CHANGE COLUMN location location VARCHAR(50) DEFAULT NULL;

7、非空约束

7.1、选取设置非空约束的字段

非空约束指字段的值不能为空，通过在表中某个列的定义后加上关键字NOT NULL作为限定词，来约束该列的取值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。

7.2、在创建表时设置非空约束

<字段名> <数据类型> NOT NULL

创建数据表tb_dept4，指定部门名称不能为空

CREATE TABLE tb_dept4(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);

7.3、在修改表时添加非空约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;

示例：修改数据表tb_dept4，指定部门名称不能为空

ALTER TABLE tb_dept4 
CHANGE COLUMN location location VARCHAR(50) NOT NULL;

7.4、删除非空约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

示例：修改数据表tb_dept4，将部门位置的非空约束删除

ALTER TABLE tb_dept4
CHANGE COLUMN location location VARCHAR(50) NULL;

8、自增长约束

8.1、选取设置自增长约束的字段

列的数值自动增长，列的类型只能是整数类型
通常给主键添加自增长约束。
一张表只能有一个自增长列，并且该列需要定义约束。

8.2、在创建表时设置自增长约束

<字段名> <数据类型> AUTO_INCREMENT

创建数据表tb_dept5，id设置为主键，自增长约束

CREATE TABLE tb_dept5(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);

8.3、在修改表时添加自增长约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> AUTO_INCREMENT;

将数据表tb_dept4，id字段设置为自增长

ALTER TABLE tb_dept4 
CHANGE COLUMN id id INT(11) AUTO_INCREMENT;

8.4、删除自增长约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <字段类型>;

MySql笔记
Mac安装并运行MySql MySql数据库、数据表的操作 MySql数据类型及常见约束 MySql表操作 MyS...
基于Linux的MySQL操作实例（软件安装，mysql基本操作
基于Linux的MySQL操作实例（软件安装，mysql基本操作，mysql数据类型，建表、插入数据操作）前言 ...
3 MySQL数据库操作
2 MySQL数据库操作 3.1 MySQL操作数据库 3.2 MySQL操作数据表 3.2.1 MySQL创建数...
Mysql大全
1.Mac下Homebrew 安装mysql 2.mysql管理 3.mysql数据库操作 4.mysql表操作 ...
Mysql学习——MySQL数据结构修改（2）
Mysql表结构操作添加表字段修改表字段删除表字段修改表字段类型修改表名简介：Mysql表结构操作,添加表字段,修...
Mysql 相关
MySQL索引 MySQL索引背后的数据结构及算法原理覆盖索引和回表操作 MySQL性能优化 MySql表分区详...
My SQL 3
2.1 MySQL 基础（二）- 表操作 #学习内容# 1. MySQL表数据类型 2. 用SQL语句创建表 ...
从0到1学习网络安全【MySQL基础-MySQL 删除数据表】
MySQL 删除数据表 MySQL中删除数据表是非常容易操作的，但是你再进行删除表操作时要非常小心，因为执行删除...
mysql 表操作
创建数据库：新建表：设置主键：设置id为自增列：表重命名：列操作
MYsql表操作
查看数据库：show databases;show create database school; 切换数据库...