MySQL的基本指令MySQL登录的指令MySQL的增删改查-MySQL-E先生的博客

[TOC]

# MySQL的基本指令

## MySQL登录的指令

```mysql

C:UsersAdministrator>mysql -uroot -hlocalhost -p123456 -P8888

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 8

Server version: 8.0.15 MySQL Community Server - GPL

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

```

- -u 用户名

- -h IP地址.本机可以使用localhost/127.0.0.1

- -p 密码

- -P 端口号

## 查看当前有哪些数据库

```mysql

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.22 sec)

mysql>

```

## 使用某个数据库

```mysql

mysql> use mysql

Database changed

mysql>

```

## 查询当前库下有哪些表

```mysql

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| component |

| db |

| default_roles |

| engine_cost |

```

## 建立数据库

```mysql

mysql> create database tc39;

Query OK, 1 row affected (0.10 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| tc39 |

+--------------------+

5 rows in set (0.00 sec)

```

## 建立数据表

```mysql

mysql> create table _user(

-> uid int primary key auto_increment,

-> uname varchar(32) unique,

-> ugender varchar(2) not null,

-> uage int not null);

Query OK, 0 rows affected (0.67 sec)

mysql> show tables;

+----------------+

| Tables_in_tc39 |

+----------------+

| _user |

+----------------+

1 row in set (0.00 sec)

```

建表语句的语法结构

```mysql

create table 表名(

字段1 数据类型 [完整性约束条件],

字段2 数据类型 [完整性约束条件],

...,

字段n 数据类型 [完整性约束条件]);

```

## 向表中插入数据

```mysql

mysql> insert into _user(uid,uname,ugender,uage)values(0,'张三','男',20);

Query OK, 1 row affected (0.21 sec)

```

插入语句的语法结构

```mysql

insert into 表名 (字段列表) values(值);

```

值的数量要和字段列表的数量一致.

```mysql

mysql> insert into _user(uid,uname,ugender,uage)values('张三','男',20);

ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql>

```

如果插入的是全部字段.字段列表可以省略.value中的赋值顺序必须和字段列表的顺序一致.

```mysql

mysql> insert into _user values(0,'女','李四',20);

Query OK, 1 row affected (0.11 sec)

mysql> select * from _user;

+-----+--------+---------+------+

+-----+--------+---------+------+

| 1 | 张三 | 男 | 20 |

| 2 | 女 | 李四 | 20 |

+-----+--------+---------+------+

2 rows in set (0.00 sec)

mysql>

```

## 从表中查询数据

查询使用select关键字.

```mysql

mysql> select * from _user;

+-----+--------+---------+------+

+-----+--------+---------+------+

| 1 | 张三 | 男 | 20 |

| 2 | 女 | 李四 | 20 |

+-----+--------+---------+------+

2 rows in set (0.00 sec)

mysql>

```

### 根据条件查询

```mysql

mysql> select uid,uname,ugender,uage from _user where uname = '王五';

+-----+--------+---------+------+

+-----+--------+---------+------+

| 5 | 王五 | 男 | 20 |

+-----+--------+---------+------+

1 row in set (0.00 sec)

mysql> select uid,uname,ugender,uage from _user where uname = 王五;

ERROR 1054 (42S22): Unknown column '王五' in 'where clause'

mysql>

# 值必须要加上单引号

```

查询语句的语法结构是

```mysql

select 字段列表 from 数据源;

```

```mysql

mysql> select uid,uname,uage,ugender from _user;

+-----+--------+------+---------+

+-----+--------+------+---------+

| 1 | 张三 | 20 | 男 |

| 2 | 女 | 20 | 李四 |

+-----+--------+------+---------+

2 rows in set (0.00 sec)

```

使用select 需要注意:*select*永远是最后执行的

## 删除表中数据

```mysql

mysql> delete from _user ;

Query OK, 2 rows affected (0.11 sec)

mysql> select uid,uname,uage,ugender from _user;

Empty set (0.00 sec)

mysql>

```

在没有指定条件的情况下会删除所有数据.想要删除指定的数据必须通过条件语句**where子句**

```mysql

mysql> select uid,uname,uage,ugender from _user;

+-----+--------+------+---------+

+-----+--------+------+---------+

| 3 | 张三 | 20 | 男 |

| 4 | 李四 | 20 | 女 |

| 5 | 王五 | 20 | 男 |

+-----+--------+------+---------+

3 rows in set (0.00 sec)

mysql> # 删除李四

mysql> delete from _user where uid = 4;

Query OK, 1 row affected (0.09 sec)

mysql> select uid,uname,uage,ugender from _user;

+-----+--------+------+---------+

+-----+--------+------+---------+

| 3 | 张三 | 20 | 男 |

| 5 | 王五 | 20 | 男 |

+-----+--------+------+---------+

2 rows in set (0.00 sec)

mysql> delete from _user where uid = 7;

Query OK, 0 rows affected (0.00 sec)

mysql> select uid,uname,uage,ugender from _user;

+-----+--------+------+---------+

+-----+--------+------+---------+

| 3 | 张三 | 20 | 男 |

| 5 | 王五 | 20 | 男 |

+-----+--------+------+---------+

2 rows in set (0.00 sec)

mysql>

```

## 修改表中的数据

```mysql

mysql> update _user set ugender = '女';

Query OK, 2 rows affected (0.07 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select uid,uname,ugender,uage from _user ;

+-----+--------+---------+------+

+-----+--------+---------+------+

| 3 | 张三 | 女 | 20 |

| 5 | 王五 | 女 | 20 |

+-----+--------+---------+------+

2 rows in set (0.00 sec)

mysql> update _user set ugender = '男' where uid = 5;

Query OK, 1 row affected (0.10 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select uid,uname,ugender,uage from _user ;

+-----+--------+---------+------+

+-----+--------+---------+------+

| 3 | 张三 | 女 | 20 |

| 5 | 王五 | 男 | 20 |

+-----+--------+---------+------+

2 rows in set (0.00 sec)

mysql>

```

## 查看表结构

```mysql

mysql> describe _user;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.14 sec)

mysql> desc _user

-> ;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql>

```

### 使用G来格式化输出

```mysql

mysql> desc _user G;

*************************** 1. row ***************************

Field: uid

Type: int(11)

Null: NO

Key: PRI

Default: NULL

Extra: auto_increment

*************************** 2. row ***************************

Field: uname

Type: varchar(32)

Null: YES

Key: UNI

Default: NULL

Extra:

*************************** 3. row ***************************

Field: ugender

Type: varchar(2)

Null: NO

Key:

Default: NULL

Extra:

*************************** 4. row ***************************

Field: uage

Type: int(11)

Null: NO

Key:

Default: NULL

Extra:

4 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

```

## 查看表详细结构语句

```mysql

mysql> show create table _user;

+-------+--------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

--------+

| Table | Create Table

+-------+--------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

--------+

| _user | CREATE TABLE `_user` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`uname` varchar(32) DEFAULT NULL,

`ugender` varchar(2) NOT NULL,

`uage` int(11) NOT NULL,

PRIMARY KEY (`uid`),

UNIQUE KEY `uname` (`uname`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+-------+--------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

--------+

1 row in set (0.02 sec)

mysql>

```

## 修改表的结构

> ? 必须掌握内容

### 修改表名

```mysql

mysql> use tc39;

Database changed

mysql> alter table _user rename to tc39_user;

Query OK, 0 rows affected (0.49 sec)

mysql> show tables;

+----------------+

| Tables_in_tc39 |

+----------------+

| tc39_user |

+----------------+

1 row in set (0.00 sec)

mysql>

```

注意:只可以改表的名字.库的名字是不可以改的.从规范角度来说不推荐随便改表的名字.所以在一开始设计表的时候就必须要设计好!

### 修改字段的数据类型

```mysql

mysql> desc _user;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

mysql>

```

比如当前uid字段的数据类型是int类型想要修改成varchar类型.

```mysql

mysql> alter table _user modify uid varchar(32);

Query OK, 3 rows affected (1.26 sec)

Records: 3 Duplicates: 0 Warnings: 0

# 表示有3行受影响.因为表中有三条记录.改变数据类型的同时,已有的记录中uid的数据类型也要被改变.

mysql> select * from _user;

+-----+--------+---------+------+

+-----+--------+---------+------+

| 3 | 张三 | 女 | 20 |

| 5 | 王五 | 男 | 20 |

| 6 | XYZ | 男 | 19 |

+-----+--------+---------+------+

3 rows in set (0.00 sec)

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>

```

### 修改字段名

> ? 准确说change可以做到同时改字段名和字段的数据类型.

```mysql

mysql> alter table _user change ugender usex varchar(2);

Query OK, 0 rows affected (1.40 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>

```

如果在修改时,给的是新的数据类型.那就会同时改字段名称和该字段的数据类型

```mysql

mysql> alter table _user change usex ugender char(2);

Query OK, 3 rows affected (1.04 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>

```

在改变字段的数据类型时要注意.该字段是否已经有值.已有值是否匹配新的数据类型.

### 增加字段

```mysql

mysql> alter table _user add uphone varchar(11) unique after uname;

Query OK, 0 rows affected (1.16 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql>

```

```mysql

mysql> alter table _user add uaddress varchar(50) not null;

Query OK, 0 rows affected (0.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql> alter table _user add uemail varchar(50) not null first;

Query OK, 0 rows affected (1.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

mysql>

```

当我们在增加字段时,如果不指定位置.则顺序添加.或者使用first来指定该字段位于第一个.或者使用after 字段X.来指明新加入的字段位于字段X的后面.

### 删除字段

```mysql

mysql> desc _user;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

7 rows in set (0.01 sec)

mysql> alter table _user drop uemail;

Query OK, 0 rows affected (1.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| uid | varchar(32) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+----------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

mysql>

```

### 修改表的存储引擎

#### 表的存储引擎介绍

存储引擎是MySQL的特点.它决定了MySQL数据库中的表使用什么样的方式来存储数据,我们可以通过指令来查看当前库中有哪些引擎

#### 查看库中的引擎

```mysql

mysql> show engines G;

*************************** 1. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 2. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 3. row ***************************

Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 4. row ***************************

Engine: FEDERATED

Support: NO

Comment: Federated MySQL storage engine

Transactions: NULL

XA: NULL

Savepoints: NULL

*************************** 5. row ***************************

Engine: PERFORMANCE_SCHEMA

Support: YES

Comment: Performance Schema

Transactions: NO

XA: NO

Savepoints: NO

*************************** 6. row ***************************

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 7. row ***************************

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

*************************** 8. row ***************************

Engine: BLACKHOLE

Support: YES

Comment: /dev/null storage engine (anything you write to it disappears)

Transactions: NO

XA: NO

Savepoints: NO

*************************** 9. row ***************************

Engine: ARCHIVE

Support: YES

Comment: Archive storage engine

Transactions: NO

XA: NO

Savepoints: NO

9 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

```

- engine 是引擎的名称

- support 表示MySQL是否支持该类型的引擎

- Yes 代表支持

- comment 对引擎的评论引擎的特点介绍

- transactions 该引擎是否支持事务

- xa 是否遵守分布式交易处理的XA规范

- savepoint 是否支持保存点.在事务处理中可以回滚到保存点. Yes代表支持

通过上面我们可以得知MySQL的默认存储引擎是InnoDB.(Support Default)

#### 查看MySQL所支持的引擎

```mysql

mysql> show variables like 'have%';

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

| have_compress | YES |

| have_dynamic_loading | YES |

| have_geometry | YES |

| have_openssl | YES |

| have_profiling | YES |

| have_query_cache | NO |

| have_rtree_keys | YES |

| have_ssl | YES |

| have_statement_timeout | YES |

| have_symlink | DISABLED |

+------------------------+----------+

10 rows in set, 1 warning (0.09 sec)

```

第一列:`Variable_name`表示存储引擎的名称.第二列`Value`表示MySQL的支持情况.YES表示支持.NO表示不支持.DISABLE表示支持但是还没有开启.

在建立表的时候如果没有指定存储引擎,表的存储引擎将采用默认的存储引擎.我们可以通过查询语句来查看当前的默认存储引擎

#### 查看当前默认存储引擎

```mysql

mysql> show variables like '%storage_engine%';

+----------------------------------+-----------+

| Variable_name | Value |

+----------------------------------+-----------+

| default_storage_engine | InnoDB |

| default_tmp_storage_engine | InnoDB |

| disabled_storage_engines | |

| internal_tmp_disk_storage_engine | InnoDB |

| internal_tmp_mem_storage_engine | TempTable |

+----------------------------------+-----------+

5 rows in set, 1 warning (0.01 sec)

```

#### 修改默认存储引擎

如果想要修改存储引擎.我们一般有两种修改方式

- 直接修改my.ini配置文件

- 使用SQL语句修改

在配置文件中使用`default-storage-engine=InnoDB`.修改完成后要重启服务

.下面使用alter语句来修改表的存储引擎

```mysql

mysql> alter table _user engine = MyISAM;

Query OK, 3 rows affected (0.80 sec)

Records: 3 Duplicates: 0 Warnings: 0

```

## Navicat的使用

目前市面上有的MySQL的GUI有以下几个

- Workbench

> MySQL 自带的.安装需要.Net Framework支持免费

- Navicat

> 使用率比较高.特点是好用.缺点是很贵!

- SQLyog

> 早模板那块比较好用.和Navicat差不多

Navicat的使用很简单.连接就好!~

![1555393177995](assets/1555393177995.png)

安装完成后不要立马打开.拷贝破解到安装的目录下进行破解.破解方式:双击运行PatchNavicat.exe就行.

破解完成后我们需要连接数据库

![1555393396323](assets/1555393396323.png)

## 完整性约束介绍

### 完整性约束的作用

保证数据库中数据的完整性.

### MySQL支持的完整性约束

| 约束 | 关键字 | 说明 |

| :--------: | ----------------- | :-------------------------------------------------------: |

| 主键约束 | PRIMARY KEY | 表示当前属性为该表的主键.可以唯一的表示数据表中的一条记录 |

| 外键约束 | FOREIGN KEY | 保证数据的参照完整性.它的取值引用另外一张表的主键 |

| 非空约束 | NOT NULL | 标识该字段不可以为空 |

| 唯一性约束 | UNIQUE | 标识该字段的取值唯一.不可以重复 |

| 默认值约束 | DEFAULT | 在没有赋值的情况下,会自动赋予的默认值 |

| 检查约束 | CHECK(MySQ不支持) | 用于检查字段是否符合指定的条件 |

| 自动增长 | AUTO_INCREMENT | 自增增长(MySQL的特色) |

### 主键约束

对于约束要求掌握两点

- 创建

- 已有表创建

- 新建表创建

- 删除

#### 新建表创建主键约束

主键约束是一个特殊的字段.用于唯一表示表中的每条信息.可以由一列组成.叫做单列主键,也可以由多列组成.叫做复合主键.

主键一般使用和业务完全无关的流水号来记录.在开发中主键往往是一串字符串它由三部分组成

- 缩写(公司.业务等)

- 序列数字(有序的数字.字符串)

- 随机数

在Java中我们往往会使用Uuid来作为主键.目的是为了增强项目对于不同数据库的支持可以随意的更换数据库.

```mysql

-- 创建User表.在建表时建立主键

create table _user(

uid int not null auto_increment,

uname varchar(50),

uage int not null,

ugender varchar(10) not null,

-- 添加主键小括号内有两个字段所以是多列主键

PRIMARY KEY(uid,uname)

);

```

除了上面这种写法我们还可以像这样写(完整写法)

```mysql

-- 创建User表.在建表时建立主键

create table _user(

uid int not null auto_increment,

uname varchar(50),

uage int not null,

ugender varchar(10) not null,

-- 添加主键小括号内有两个字段所以是多列主键

constraint _user_PK PRIMARY key(uid,uname)

);

```

我们可以总结建立约束的写法是

```mysql

constraint 约束名约束类型 (字段1,...,字段n)

```

#### 对已有表增加主键

对已有表增加主键一样属于对表做修改.所以也还是通过alter语句来实现.

```mysql

alter table _user add constraint User_PK primary key (uid);

```

```mysql

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | int(11) | NO | | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> alter table _user add constraint User_PK primary key (uid);

Query OK, 0 rows affected (0.81 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | int(11) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>

```

#### 删除主键

```mysql

mysql> use tc39;

Database changed

mysql> desc _user;

+---------+-------------+------+-----+---------+----------------+

+---------+-------------+------+-----+---------+----------------+

| uid | int(11) | NO | PRI | NULL | auto_increment |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

# 从上面的表结构可以发现.表中有主键且是符合主键.由uid和uname两个字段符合.并且uid有auto_increment属性.想要删除主键首先要删除自动增长.因为自动增长必须是主键才可以使用.否则会报错

mysql> alter table _user drop primary key;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must b

ined as a key

mysql> alter table _user change uid uid int(11) not null;

Query OK, 0 rows affected (1.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | int(11) | NO | PRI | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

# 从上面的表结构可以看到.自动增长没有了.开始删除主键.

mysql> alter table _user drop primary key;

Query OK, 0 rows affected (0.95 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc _user;

+---------+-------------+------+-----+---------+-------+

+---------+-------------+------+-----+---------+-------+

| uid | int(11) | NO | | NULL | |

| uage | int(11) | NO | | NULL | |

+---------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

# key列下面没有任何内容代表我们把主键删除成功

mysql>

```

我们删除主键的语法可以总结如下

```mysql

alter table 表名 drop primary key

```

对于任何一张表来说.只能有唯一的一个主键.所以在删除时只要指明是删除主键就可以了.不用担心删错

如果主键是数值类型往往会配合使用自动增长`auto_increment`.想要删除主键就首先需要把自动增长去掉.否则删除失败

### 外键约束

外键约束是保证数据的参照完整性.为了验证外检约束.创建三张表

- 学生表.

- 成绩表.

- 课程表

```mysql

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`cid` int(10) unsigned NOT NULL AUTO_INCREMENT,

`cname` varchar(255) DEFAULT NULL,

PRIMARY KEY (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', 'Java');

INSERT INTO `course` VALUES ('2', 'MySQL');

INSERT INTO `course` VALUES ('3', 'HTML');

INSERT INTO `course` VALUES ('4', 'CSS');

-- ----------------------------

-- Table structure for score

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`scid` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '成绩表记录',

`sid` int(10) unsigned DEFAULT NULL COMMENT '学生编号',

`cid` int(10) unsigned DEFAULT NULL COMMENT '课程编号',

`score` double(255,0) DEFAULT NULL,

PRIMARY KEY (`scid`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '1', '80');

INSERT INTO `score` VALUES ('2', '10', '10', '100');

-- ----------------------------

-- Table structure for stu

-- ----------------------------

DROP TABLE IF EXISTS `stu`;

CREATE TABLE `stu` (

`sid` int(10) unsigned NOT NULL AUTO_INCREMENT,

`sname` varchar(50) DEFAULT NULL,

`sage` int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`sid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------

-- Records of stu

-- ----------------------------

INSERT INTO `stu` VALUES ('1', '张三', '20');

INSERT INTO `stu` VALUES ('2', '李四', '20');

INSERT INTO `stu` VALUES ('3', '王五', '19');

INSERT INTO `stu` VALUES ('4', '赵六', '19');

```

因为表示已经建立完成的.所以我们先对已有表做修改

#### 对已有表添加外键

添加方式和对已有表添加主键基本一样.添加主键的语法格式如下

```mysql

alter table 表名 add constraint 约束名约束类型(字段)

```

参考该写法.添加外键的写法是

```mysql

alter table 表名 add constraint 约束名约束类型(字段) references 主键表(主键)

```

我们以成绩表和学生表为例.那写法应该是

```mysql

alter table score add constraint

SCORE_STU_SID foreign key(sid) references stu(sid) ;

```

以此类推成绩表和课程表

```mysql

alter table score add constraint

SCORE_COURSE_CID foreign key(cid) references course(cid) ;

```

外键建立成功后测试

```mysql

mysql> use tc39;

Database changed

mysql> insert into score values(0,5,5,100);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tc39`.`score`

, CONSTRAINT `SCORE_STU_SID` FOREIGN KEY (`sid`) REFERENCES `stu` (`sid`))

mysql> insert into score values(0,3,5,100);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tc39`.`score`

, CONSTRAINT `SCORE_COURSE_CID` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`))

mysql> insert into score values(0,3,3,100);

Query OK, 1 row affected (0.13 sec)

mysql>

```

#### 新建表添加外键

```mysql

-- 随便建张表.引用学生表中的主键

-- 外键引用的是主键

create table test(

sid int UNSIGNED not null,

CONSTRAINT TEST_STU_SID FOREIGN key (sid) REFERENCES stu(sid)

);

```

#### 删除外键

```mysql

-- 删除成绩表中的外键

alter table score drop FOREIGN key SCORE_COURSE_CID;

```

我们可以总结下外键的删除语法

```mysql

alter table 表名 drop foreign key 外键别名

```

本文来自E先生的博客，如若转载，请注明出处：https://www.javajz.cn

MySQL的基本指令MySQL登录的指令MySQL的增删改查

留言区

历史留言

MySQL的基本指令MySQL登录的指令MySQL的增删改查

相关推荐

本地部署Vscode Copilot

笔记本+window+cpu本地十分钟部署大模型

java生成二维码：SpringBoot 与 ZXing 完美结合

fail api scope is not declared in the privacy agreement

移除ID名为id_name的元素和id置空

Vue可编辑表格之EditTable

留言区

历史留言