Java
MySQL
Python
大数据
前端
黑科技
    首页 > 互联网 > MySQL > MySQL的基本指令MySQL登录的指令MySQL的增删改查

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

[导读]:[TOC] # MySQL的基本指令 ## MySQL登录的指令 ```mysql C:\Users\Administratormysql -uroot -hlocalhost -p123456 -P8888 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Comm...
[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
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
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;
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
|   1 | 张三   | 男      |   20 |
|   2 | 女     | 李四    |   20 |
+-----+--------+---------+------+
2 rows in set (0.00 sec)
 
mysql>
 
```
 
## 从表中查询数据
 
查询使用select关键字.
 
```mysql
mysql> select * from _user;
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
|   1 | 张三   | 男      |   20 |
|   2 | 女     | 李四    |   20 |
+-----+--------+---------+------+
2 rows in set (0.00 sec)
 
mysql>
 
```
 
### 根据条件查询
 
```mysql
mysql> select uid,uname,ugender,uage from _user where uname = '王五';
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
|   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;
+-----+--------+------+---------+
| uid | uname  | uage | ugender |
+-----+--------+------+---------+
|   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;
+-----+--------+------+---------+
| uid | uname  | uage | ugender |
+-----+--------+------+---------+
|   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;
+-----+--------+------+---------+
| uid | uname  | uage | ugender |
+-----+--------+------+---------+
|   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;
+-----+--------+------+---------+
| uid | uname  | uage | ugender |
+-----+--------+------+---------+
|   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 ;
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
|   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 ;
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
|   3 | 张三   | 女      |   20 |
|   5 | 王五   | 男      |   20 |
+-----+--------+---------+------+
2 rows in set (0.00 sec)
 
mysql>
 
```
 
## 查看表结构
 
```mysql
mysql> describe _user;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| uid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| uname   | varchar(32) | YES  | UNI | NULL    |                |
| ugender | varchar(2)  | NO   |     | NULL    |                |
| uage    | int(11)     | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.14 sec)
 
mysql> desc _user
    -> ;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| uid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| uname   | varchar(32) | YES  | UNI | NULL    |                |
| ugender | varchar(2)  | NO   |     | NULL    |                |
| 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;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| uid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| uname   | varchar(32) | YES  | UNI | NULL    |                |
| ugender | varchar(2)  | NO   |     | NULL    |                |
| 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;
+-----+--------+---------+------+
| uid | uname  | ugender | uage |
+-----+--------+---------+------+
| 3   | 张三   | 女      |   20 |
| 5   | 王五   | 男      |   20 |
| 6   | XYZ    | 男      |   19 |
+-----+--------+---------+------+
3 rows in set (0.00 sec)
 
mysql> desc _user;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | varchar(32) | NO   | PRI | NULL    |       |
| uname   | varchar(32) | YES  | UNI | NULL    |       |
| ugender | varchar(2)  | NO   |     | 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;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid   | varchar(32) | NO   | PRI | NULL    |       |
| uname | varchar(32) | YES  | UNI | NULL    |       |
| usex  | varchar(2)  | YES  |     | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | varchar(32) | NO   | PRI | NULL    |       |
| uname   | varchar(32) | YES  | UNI | NULL    |       |
| ugender | char(2)     | YES  |     | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | varchar(32) | NO   | PRI | NULL    |       |
| uname   | varchar(32) | YES  | UNI | NULL    |       |
| uphone  | varchar(11) | YES  | UNI | NULL    |       |
| ugender | char(2)     | YES  |     | 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;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uid      | varchar(32) | NO   | PRI | NULL    |       |
| uname    | varchar(32) | YES  | UNI | NULL    |       |
| uphone   | varchar(11) | YES  | UNI | NULL    |       |
| ugender  | char(2)     | YES  |     | NULL    |       |
| uage     | int(11)     | NO   |     | NULL    |       |
| uaddress | varchar(50) | 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;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uemail   | varchar(50) | NO   |     | NULL    |       |
| uid      | varchar(32) | NO   | PRI | NULL    |       |
| uname    | varchar(32) | YES  | UNI | NULL    |       |
| uphone   | varchar(11) | YES  | UNI | NULL    |       |
| ugender  | char(2)     | YES  |     | NULL    |       |
| uage     | int(11)     | NO   |     | NULL    |       |
| uaddress | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
 
mysql>
 
 
```
 
当我们在增加字段时,如果不指定位置.则顺序添加.或者使用first来指定该字段位于第一个.或者使用after 字段X.来指明新加入的字段位于字段X的后面.
 
### 删除字段
 
```mysql
mysql> desc _user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uemail   | varchar(50) | NO   |     | NULL    |       |
| uid      | varchar(32) | NO   | PRI | NULL    |       |
| uname    | varchar(32) | YES  | UNI | NULL    |       |
| uphone   | varchar(11) | YES  | UNI | NULL    |       |
| ugender  | char(2)     | YES  |     | NULL    |       |
| uage     | int(11)     | NO   |     | NULL    |       |
| uaddress | varchar(50) | 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;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| uid      | varchar(32) | NO   | PRI | NULL    |       |
| uname    | varchar(32) | YES  | UNI | NULL    |       |
| uphone   | varchar(11) | YES  | UNI | NULL    |       |
| ugender  | char(2)     | YES  |     | NULL    |       |
| uage     | int(11)     | NO   |     | NULL    |       |
| uaddress | varchar(50) | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | int(11)     | NO   |     | NULL    |       |
| uname   | varchar(50) | NO   |     | NULL    |       |
| uage    | int(11)     | NO   |     | NULL    |       |
| ugender | varchar(10) | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | int(11)     | NO   | PRI | NULL    |       |
| uname   | varchar(50) | NO   |     | NULL    |       |
| uage    | int(11)     | NO   |     | NULL    |       |
| ugender | varchar(10) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql>
 
 
```
 
#### 删除主键
 
```mysql
mysql> use tc39;
Database changed
mysql> desc _user;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| uid     | int(11)     | NO   | PRI | NULL    | auto_increment |
| uname   | varchar(50) | NO   | PRI | NULL    |                |
| uage    | int(11)     | NO   |     | NULL    |                |
| ugender | varchar(10) | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | int(11)     | NO   | PRI | NULL    |       |
| uname   | varchar(50) | NO   | PRI | NULL    |       |
| uage    | int(11)     | NO   |     | NULL    |       |
| ugender | varchar(10) | 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;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| uid     | int(11)     | NO   |     | NULL    |       |
| uname   | varchar(50) | NO   |     | NULL    |       |
| uage    | int(11)     | NO   |     | NULL    |       |
| ugender | varchar(10) | 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 外键别名
 
```
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

本文来自投稿,不代表阿习进阶博客立场,如若转载,请注明出处:https://www.yanxias.com/MySQL/18.html

说点什么吧
  • 全部评论(0
    还没有评论,快来抢沙发吧!
点击这里给我发消息