这篇博客详细介绍了MySQL数据库管理的基础知识，包括库和表的概念、常用数据类型、查看数据库结构的方法，以及SQL语句的使用，如创建、删除数据库和表，插入、查询、更新和删除数据，以及表的修改。此外，还涉及到了数据表的高级操作，如克隆表、清空表、创建临时表和外键约束的操作方法。
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
文章目录
前言
数据库基本操作
数据库用户授权
一、基本概念
1-1库和表
数据库–>数据表–>行（记录）：用来描述一个对象的信息
列（字段）：用来描述对象的一个属性
1-2常用的数据类型
| int | 整型 |
| float | 单精度浮点 4字节32位 |
| double | 双精度浮点 8字节64位 |
| char | 固定长度的字符类型 |
| varchar | 可变长度的字符类型 |
| text | 文本 |
| image | 图片 |
| decimal(5,2) | 5个有效长度数字，小数点后面有2位 |
char和varchar区别
| Value | char(4) | storage required | varchar(4) | storage required |
|---|---|---|---|---|
| ’ ’ | ’ ’ | 4bytes | ’ ’ | 1byte |
| ‘ab’ | 'ab ’ | 4bytes | ‘ab’ | 3bytes |
| ‘abcd’ | ‘abcd’ | 4bytes | ‘abcd’ | 5bytes |
| ‘abcdefg’ | ‘abcd’ | 4bytes | ‘abcd’ | 5bytes |
首先有char(4)和varchar(4)，括号内的4就代表所定义的字符串的长度
char是固定长度，无论字符的值有多长，他存储在硬盘上的固定长度就4个字节，多了的都不算
如果’ '引号中间为无值，依然是用4个字节保存，也就是四个空格
所以说char这种固定字节，存储大小也是按定义的字节长度来保存的，比如说char(4)无论是大于还是小于四个字节，都是以四个字节来进行保存
varchar是可变长的字符类型，也就是他的字符大小不一定固定为四个字节，
如果’ '引号中间为无值，varchar是以一个字符保存，因为varchar在保存数据的时候会默认加一个结束符，结束符算一个字符
如果超过4个字节，因为定义的字符串长度为4个字节，当大于四个字节依然按照四个字节计算，所以在保存的时候会默认增加一个结束符就是五个字节
| 优点 | 缺点 | |
|---|---|---|
| char | 处理速度快 | 浪费存储空间 |
| varchar | 可以节约磁盘空间 | 处理速度慢 |
根据场景
如果关注于存储空间就用varchar
如果关注读写效率就用char
二、查看数据库结构
2-1 查看当前服务器中的数据库
大小写不区分，分号“;”表示结束
SHOW DATABASES;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
2-2 查看数据库中包含的表
2-2-1.进库查看表
查看库中有哪些表，需要先切换到库，才能进入到表，
因为现在是在库的外面，想查看mysql有哪些表需要先
USE MYSQL;
SHOW TABLES;
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
2-2-2.不进库查看表
SHOW TABLES FROM MYSQL;
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
2-3 查看表的结构（字段）
2-3-1.进库查看
USE MYSQL;
DESCRIBE USER;(缩写desc user；效果是一样的)
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 | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
# Filed:字段
# type:数据类型
# Null:是否可以为空值
# key:具有什么样的键
# Default:是否有什么样的默认值
# Extra:其他扩展的默认配置，扩展属性
2-3-2.不进库查看
DESC MYSQL.(点)user;
mysql> desc mysql.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 | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
三、SQL语句
SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。
SQL语句分类：
| 语句 | 代表的意思 |
|---|---|
| DDL | 数据定义语言，用于创建数据库对象，如库、表、索引等（create ） |
| DML | 数据操纵语言，用于对表中的数据进行管理(insert drop delete update ) |
| DQL | 数据查询语言，用于从数据表中查找符合条件的数据记录(select ) |
| DCL | 数据控制语言，用于设置或者更改数据库用户或角色权限(grant ) |
3-1创建及删除数据库和表
3-1-1 创建新的数据库
CREATE DATABASE 数据库名;
新建一个名为hu的库
mysql> show databases; #查看库
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec) #show databases 此时库里是没有hu的
mysql> create database hu; #新建名为hu的库
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| hu |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec) #可以看到此时库列表里已经多了一个名为hu的库
3-1-2 创建新的表
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,…][,PRIMARY KEY (主键名)]);
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| hu |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use hu #首先要先切换库
Database changed
mysql> show tables; #查看表，此时库里面是空的
Empty set (0.00 sec)
mysql> create table hu1 (id int, name char(10) not null,age int,sex char(4),score decimal(5,2));
#创建id数据类型为int类型，name定义为char(字符长度为10个字符)不允许为空值，age为int类型，sex性别为char类型(字符长度为4个字节)
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看表，此时已有名为hu1的表
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
+--------------+
1 row in set (0.00 sec)
mysql> desc hu1; #查看表的结构
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |#没有定义int的长度默认情况下为11个字符
| name | char(10) | NO | | NULL | |#name定义不能为空，所以Null的位置为NO
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> create table hu2 (id int, name char(10) not null,age int,sex char(4),score decimal(5,2));
Query OK, 0 rows affected (0.00 sec) #按照这个再次创建名为hu2的表
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| hu2 |
+--------------+
2 rows in set (0.00 sec) #此时hu2已经创建
3-1-3 进入库删除指定的数据表
DROP TABLE 表名;
mysql> show tables; #查看表，此时有名为hu1和hu2表
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| hu2 |
+--------------+
2 rows in set (0.00 sec)
mysql> drop table hu2; #删除名为hu2的表
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; #查看表，此时hu2的表已被删除
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
+--------------+
1 row in set (0.00 sec)
3-1-4 不进入库删除指定的数据表
DROP TABLE 库名.(点)表名;
mysql> drop table hu.hu1; #删除库hu中的hu1表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables from hu; #此时库内的表已经显示为空
Empty set (0.00 sec)
3-1-5删除指定的数据库
DROP DATABASE 数据库名;
mysql> drop database hu; #删除名为hu的库
Query OK, 0 rows affected (0.00 sec)
mysql> show databases; #此时查看库可以看到hu这个库已经被删除
+--------------------+
| Database |
+--------------------+
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
3.2 管理表中的数据记录
3-2-1向数据表中插入新的数据记录
向数据表中插入新的数据记录
insert into 表名（字段1，字段2，字段3） values（字段1的值，字段2的值，字段3的值）；
mysql> use hu; #切换库
Database changed
mysql> show tables; #查看表
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
+--------------+
1 row in set (0.00 sec)
#往里添加新的数据
mysql> insert into hu1(id,name,age,sex,score) values(1,'张三',18,'男',60.5);
Query OK, 1 row affected (0.00 sec) #字符类型要加引号，数值不用加引号，int类型不能加小数点
mysql> insert into hu1 values(2,'李四',22,'男',71.256); #不指定字段插入新的数据，不指定字段就是按照表结构顺序依次填入字段值
Query OK, 1 row affected, 1 warning (0.00 sec) #这里数据插入成功
mysql> insert into hu1(id,name,sex,score) values(3,'王五','男',23.5); #如果缺少一个字段，比如年龄。可以在前面指定其他字段创建，这里指定了id,姓名，性别和分数，没有指定年龄是可以成功创建的
Query OK, 1 row affected (0.00 sec)
3-2-2 查询数据记录
SELECT 字段名1,字段名2[,…] FROM 表名 [WHERE 条件表达式];
3-2-2-1查看所有数据
select * from info（表名）;
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 22 | 男 | 71.26 |
| 1 | 张三 | 21 | 男 | 77.00 |
| 3 | 王五 | 228 | 男 | 99.99 |
| 4 | 赵六 | 28 | 男 | 69.90 |
| 5 | 孙六 | 18 | 女 | 61.90 |
| 6 | 冯七 | NULL | 女 | 61.90 |
+------+--------+------+------+-------+
6 rows in set (0.00 sec)
3-2-2-2 查询指定数据
select 字段，字段，from 表名；
mysql> select name,sex,age from hu1; #查询指定字段name,sex,age
+--------+------+------+
| name | sex | age | #查询指定数据查询出来结果顺序由自己指定，可以不按顺序来
+--------+------+------+
| 李四 | 男 | 22 |
| 张三 | 男 | 21 |
| 王五 | 男 | 228 |
| 赵六 | 男 | 28 |
| 孙六 | 女 | 18 |
| 冯七 | 女 | NULL |
+--------+------+------+
6 rows in set (0.00 sec)
3-2-2-3 用where语句查询
1.仅显示性别为女的行
select * from hu1 where sex=‘女’;
mysql> select * from hu1 where sex='女';
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 5 | 孙六 | 18 | 女 | 61.90 |
| 6 | 冯七 | NULL | 女 | 61.90 |
+------+--------+------+------+-------+
2 rows in set (0.00 sec)
2.仅显示id字段为3或6的
select * from hu1 where id=3 or id=6;
（“or”代表只要满足一个就可以）
mysql> select * from hu1 where id=3 or id=6;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 3 | 王五 | 228 | 男 | 99.99 |
| 6 | 冯七 | NULL | 女 | 61.90 |
| 6 | 冯七 | NULL | NULL | 61.90 |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)
3.仅显示分数为99.99且性别为男的
mysql> select * from hu1 where score=99.99 and sex=‘男’;
（“and”代表两个都要满足）
mysql> select * from hu1 where score=99.99 and sex='男';
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 3 | 王五 | 228 | 男 | 99.99 |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
3-2-2-4 以列表方式竖向显示
select * from info(表名) \G;
mysql> select * from user\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
6 rows in set (0.00 sec)
3-2-2-5 查看指定行内容
1.select * from info limit 2; 查看头两行内容
mysql> select * from hu1 limit 2; # 查看头两行内容
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 22 | 男 | 71.26 |
| 1 | 张三 | 21 | 男 | 77.00 |
+------+--------+------+------+-------+
2 rows in set (0.00 sec)
2.select * from info limit 3,1; 这个意思是查看第3行之后的1行，且不包括第3行
mysql> select * from hu1 limit 3,1; # 查看第四行内容
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 4 | 赵六 | 28 | 男 | 69.90 |
+------+--------+------+------+-------+
1 row in set (0.00 sec)
3.select * from hu1 limit 3,3; 查看第3行后的后3行，且不包括第3行
mysql> select * from hu1 limit 3,3;
+------+--------+------+------+-------+
| id | name | age | sex | score | #查看4,5,6行
+------+--------+------+------+-------+
| 4 | 赵六 | 28 | 男 | 69.90 |
| 5 | 孙六 | 18 | 女 | 61.90 |
| 6 | 冯七 | NULL | 女 | 61.90 |
+------+--------+------+------+-------+
3 rows in set (0.00 sec)
如果想查看头几行内容，比如前3行就是limit 3,头10行就是limit 10
如果想查看中间的几行，比如想要从第四行查看两行内容，就是limit 3，2，就是查看4,5两行，如果有100行内容要查看第20行到第30行，就是limit 19,11，表示从19行之后不包括19行的后面11行，就是20到30行
3.3修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];
3-3-1 修改字段
update hu1 set age=18;
这个命令这样执行会把表中的所有age都改为18
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 22 | 男 | 71.26 |
| 1 | 张三 | 21 | 男 | 77.00 |
| 3 | 王五 | 228 | 男 | 99.99 |
| 4 | 赵六 | 28 | 男 | 69.90 |
| 5 | 孙六 | 18 | 女 | 61.90 |
| 6 | 冯七 | NULL | 女 | 61.90 |
| 6 | 冯七 | NULL | NULL | 61.90 |
+------+--------+------+------+-------+
7 rows in set (0.00 sec)
mysql> update hu1 set age=18; #这个命令这样执行会把表中所有的age都改为18
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7 Changed: 6 Warnings: 0
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 3 | 王五 | 18 | 男 | 99.99 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 18 | 女 | 61.90 |
| 6 | 冯七 | 18 | 女 | 61.90 |
| 6 | 冯七 | 18 | NULL | 61.90 |
+------+--------+------+------+-------+
7 rows in set (0.00 sec)
正确的修改方式为
update hu1 set age=20 where id=5;
需要修改年龄字段，后面用where语句，根据他的名称，ID，名称可能会重复，ID不会重复，最好指定的条件用唯一字段去修改
mysql> update hu1 set age=20 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 3 | 王五 | 18 | 男 | 99.99 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 | # 这里上表age处为18现更改为20
| 6 | 冯七 | 18 | 女 | 61.90 |
| 6 | 冯七 | 18 | NULL | 61.90 |
+------+--------+------+------+-------+
7 rows in set (0.00 sec)
3-3-2 修改多个字段
update hu1 set age=21,sex=‘不明’ where id=3;
修改id为3字段，年龄为21，性别为不明，中间用逗号隔开
mysql> update hu1 set age=21,sex='不明' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from hu1;
+------+--------+------+--------+-------+
| id | name | age | sex | score |
+------+--------+------+--------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 3 | 王五 | 21 | 不明 | 99.99 | # 依据上表的数据王五的年龄为18，性别为男，这里更改为年龄21，性别不明
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 |
| 6 | 冯七 | 18 | 女 | 61.90 |
| 6 | 冯七 | 18 | NULL | 61.90 |
+------+--------+------+--------+-------+
7 rows in set (0.00 sec)
3-4 在数据表中删除指定的数据记录
DELETE FROM 表名 [WHERE 条件表达式];
3-4-1 删除表中性别为不明的的数据
方法一：delete from hu1 where id=6 or id=3;
使用‘or’配合唯一的id号将sex为不明的数据删除
mysql> select * from hu1;
+------+--------+------+--------+-------+
| id | name | age | sex | score |
+------+--------+------+--------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 3 | 王五 | 21 | 不明 | 99.99 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 |
| 6 | 冯七 | 18 | 不明 | 60.50 |
+------+--------+------+--------+-------+
6 rows in set (0.00 sec)
mysql> delete from hu1 where id=6 or id=3;
Query OK, 2 rows affected (0.01 sec)
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 |
+------+--------+------+------+-------+
4 rows in set (0.00 sec)
方法二：delete from hu1 where sex=‘不明’;
直接将定义sex='不明’的数据删除
mysql> select * from hu1;
+------+--------+------+--------+-------+
| id | name | age | sex | score |
+------+--------+------+--------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 |
| 3 | 冯七 | 18 | 不明 | 60.50 |
| 6 | 王五 | 19 | 不明 | 50.50 |
+------+--------+------+--------+-------+
6 rows in set (0.00 sec)
mysql> delete from hu1 where sex='不明';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from hu1;
+------+--------+------+------+-------+
| id | name | age | sex | score |
+------+--------+------+------+-------+
| 2 | 李四 | 18 | 男 | 71.26 |
| 1 | 张三 | 18 | 男 | 77.00 |
| 4 | 赵六 | 18 | 男 | 69.90 |
| 5 | 孙六 | 20 | 女 | 61.90 |
+------+--------+------+------+-------+
4 rows in set (0.00 sec)
3-4-2 如果delete from hu1 后不加条件会导致整个表数据都被删除。需谨慎使用
3-5 修改表名和表结构
3-5-1 修改表名
ALTER TABLE 旧表名 RENAME 新表名;
将表名hu1更改为hu2
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
+--------------+
1 row in set (0.00 sec)
mysql> alter table hu1 NENAME hu2;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu2 |
+--------------+
1 row in set (0.00 sec)
3-5-2 扩展表结构（增加字段）
ALTER TABLE 表名 ADD address varchar(50) default ‘default值’；
添加字段address，字节50，不允许为空，且默认值为地址不详
mysql> DESC hu1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> alter table hu1 ADD address varchar(50) not null default '地址不详';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> DESC hu1;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
插入内容查看效果
mysql> insert into hu1 values(3, '张三', 18,'男', 9.9,'城南');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hu1(id,name,age,sex) values (1,'李四',21,'男');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hu1;
+------+--------+------+------+-------+--------------+
| id | name | age | sex | score | address |
+------+--------+------+------+-------+--------------+
| 3 | 张三 | 18 | 男 | 9.90 | 城南 |
| 1 | 李四 | 21 | 男 | NULL | 地址不详 |
+------+--------+------+------+-------+--------------+
2 rows in set (0.00 sec)
3-5-3 修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [unique key] (唯一键);
将表字段score更改为phone并设置为唯一键
mysql> DESC hu1;
+---------+--------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+--------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> alter table hu1 change score phone int unique key;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc hu1;
+---------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+-------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
3-5-4删除字段
ALTER TABLE 表名 drop 字段名;
删除地址的字段
mysql> desc hu1;
+---------+-------------+------+-----+--------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+--------------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
| address | varchar(50) | NO | | 地址不详 | |
+---------+-------------+------+-----+--------------+-------+
6 rows in set (0.00 sec)
mysql> alter table hu1 DROP address;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc hu1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
补充：添加主键
将ID设置为主键
mysql> desc hu1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| phone | int(11) | YES | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> alter table hu1 ADD primary key(id); # 设置主键
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc hu1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | | # 将id设置为主键之后Null自动变为NO，因为主键不能为空
| name | char(10) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| phone | int(11) | YES | UNI | NULL | | # 唯一键是可以为空的
+-------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
插入数据查看
insert into hu1 values (2,‘王五’,28,‘男’,11111111);
插入数据id为2(因为设置了主键所以该处不能为空也不能重复)，姓名为王五，年龄28岁，性别为男，号码为11111111
mysql> select * from hu1;
+----+--------+------+------+-------+
| id | name | age | sex | phone |
+----+--------+------+------+-------+
| 1 | 李四 | 21 | 男 | NULL |
| 3 | 张三 | 18 | 男 | 10 |
+----+--------+------+------+-------+
2 rows in set (0.00 sec)
mysql> insert into hu1 values (2,'王五',28,'男',11111111);
Query OK, 1 row affected (0.00 sec)
mysql> select * from hu1;
+----+--------+------+------+----------+
| id | name | age | sex | phone |
+----+--------+------+------+----------+
| 1 | 李四 | 21 | 男 | NULL |
| 2 | 王五 | 28 | 男 | 11111111 |
| 3 | 张三 | 18 | 男 | 10 |
+----+--------+------+------+----------+
3 rows in set (0.00 sec)
mysql> create table if not exists info (
-> id int(4) zerofill primary key auto_increment,
-> name varchar(10) not null default'匿名',
-> cardid int(18) not null unique key,
-> hobby varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
+--------------+
2 rows in set (0.00 sec)
mysql> desc info;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | 匿名 | |
| cardid | int(18) | NO | UNI | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
补充案例
use hu;
create table if not exists info ( #检测要创建的表是否存在，如果不存在就继续创建，如果存在就忽略这个表的创建
id int (4) zerofi11 primary key auto_ihcrement, #指定主键的第二种方式
name varchar(10) not nul1 default'匿名',
cardid int(18) not null unique key,
hobby varchar(50)):
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4）zerofi11：表示若数值不满4位数，则前面用“0"填充，例0001 __
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增：自增长字段数据不可以重复：自增长字段必须是主键：如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复：一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null:表示此字段不允许为wULL
mysql> desc info;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | 匿名 | |
| cardid | int(18) | NO | UNI | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into info(name,cardid,hobby) values('zhangsan',12345678,'running');
#这里没有指定id值，但根据上表中可以看到id值为主键，主键不能为空，现在没有设置但是没有报错
Query OK, 1 row affected (0.00 sec)
mysql> select * from info; #查看该表
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running | #这里看到id由1开始递增，因为之前表字段设置了0填充，所以不满4位的情况下前面会用0填充，因为int括号内为4
+------+----------+----------+---------+
1 row in set (0.00 sec)
mysql> insert into info(name,cardid,hobby) values('lisi',12345679,'dancing');
#再添加了一条数据，因为cardid为唯一值，所以需要更改
Query OK, 1 row affected (0.00 sec)
mysql> select * from info; #查看该表
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running |
| 0002 | lisi | 12345679 | dancing | # 可以看到id号自动递增了一位
+------+----------+----------+---------+
2 rows in set (0.00 sec)
3.6 使用 if 判断 创建表并测试自增和填充
use hobby;
create table if not exists info.bak (
id int(4) zerofill primary key auto_increment,
name char(20) not null,
sid int(18) not null unique key,
ge int(3) not null,
address char(100));
分析：
if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
not null：表示此字段不允许为NULL
测试：
四、数据表高级操作
4-1克隆表——将数据表的数据记录生成到新的表中
方法一：先创建再导入
通过 LIKE 方法，复制 info 表结构生成 info1 表
create table info1 like info;
此时表结构已经被复制过来，但是表内的数据还是空的
导入数据
insert into info1 select * from info;
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
+--------------+
2 rows in set (0.00 sec)
mysql> select *from info;
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running |
| 0002 | lisi | 12345679 | dancing |
+------+----------+----------+---------+
2 rows in set (0.00 sec)
mysql> create table info1 like info; # 复制表结构
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
| info1 |
+--------------+
3 rows in set (0.00 sec)
mysql> desc info1;
+--------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | 匿名 | |
| cardid | int(18) | NO | UNI | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into info1 select * from info; # 将info内的数据导入info1
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info1; # 查看info1，数据与info内容相同
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running |
| 0002 | lisi | 12345679 | dancing |
+------+----------+----------+---------+
2 rows in set (0.00 sec)
法二：创建的时候同时导入
create table info2 (select * from info);
mysql> select * from info;
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running |
| 0002 | lisi | 12345679 | dancing |
+------+----------+----------+---------+
2 rows in set (0.00 sec)
mysql> create table info2 (select * from info);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info2;
+------+----------+----------+---------+
| id | name | cardid | hobby |
+------+----------+----------+---------+
| 0001 | zhangsan | 12345678 | running |
| 0002 | lisi | 12345679 | dancing |
+------+----------+----------+---------+
2 rows in set (0.00 sec)
以命令形式显示表结构
show create table info1\G;
*************************** 1. row ***************************
Table: info2
Create Table: CREATE TABLE `info2` (
`id` int(4) unsigned zerofill NOT NULL DEFAULT '0000',
`name` varchar(10) NOT NULL DEFAULT '匿名',
`cardid` int(18) NOT NULL,
`hobby` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
l
4-2 清空表——删除表内的所有数据
4-2-1 deldte删除
DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。
delete from 表名；
delete from info;
mysql> insert into info(name,cardid,hobby) values('lisi',34567889,'he'); # 插入的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+------+--------+----------+-------+
| id | name | cardid | hobby |
+------+--------+----------+-------+
| 0005 | 李二 | 345678 | he |
| 0006 | 张三 | 24567889 | chi |
| 0007 | lisi | 34567889 | he | # 这里id号显示为0007
+------+--------+----------+-------+
3 rows in set (0.00 sec)
mysql> delete from info; # 删除info表的数据
Query OK, 3 rows affected (0.00 sec) # 这里显示为3条数据删除，表示这里是一条一条删除的数据
mysql> insert into info(name,cardid,hobby) values('lisi',34567889,'he'); # 再次插入新的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+------+------+----------+-------+
| id | name | cardid | hobby |
+------+------+----------+-------+
| 0008 | lisi | 34567889 | he | # 这里id号从0008开始
+------+------+----------+-------+
1 row in set (0.00 sec)
4-2-2 truncate删除
TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录
truncate table info;
mysql> insert into info(name,cardid,hobby) values('zhaoliu',54567859,'b'); # 插入新的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+------+---------+----------+-------+
| id | name | cardid | hobby |
+------+---------+----------+-------+
| 0008 | lisi | 34567889 | he |
| 0009 | wangwu | 54567889 | a |
| 0010 | zhaoliu | 54567859 | b | # 这里最大的id数为0010
+------+---------+----------+-------+
3 rows in set (0.00 sec)
mysql> truncate table info; # 删除表内的数据
Query OK, 0 rows affected (0.00 sec) # 这里没有显示多少行记录被影响，就是直接被删除
mysql> insert into info(name,cardid,hobby) values('zhaoliu',54567859,'b'); # 再次插入新的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+------+---------+----------+-------+
| id | name | cardid | hobby |
+------+---------+----------+-------+
| 0001 | zhaoliu | 54567859 | b | # id编号从0001开始了
+------+---------+----------+-------+
1 row in set (0.00 sec)
4-3 创建临时表
临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。
创建一个临时表hu2
create temporary table hu2 (id int,name char(10),age int,sex char(4));
mysql> create temporary table hu2 (id int,name char(10),age int,sex char(4));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; # 查看表，并未显示新建的表hu2
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
| info1 |
| info2 |
+--------------+
4 rows in set (0.00 sec)
mysql> insert into hu2 values(1,'zhangsan',20,'man'); # 插入新的数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into hu2 values(2,'lisi',21,'man'); # 插入第二条数据
Query OK, 1 row affected (0.00 sec)
mysql> show tables; # 查看表依然没有hu2
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
| info1 |
| info2 |
+--------------+
4 rows in set (0.00 sec)
mysql> select * from hu2; # 查看hu2表内数据，可以看到刚刚插入的数据
+------+----------+------+------+
| id | name | age | sex |
+------+----------+------+------+
| 1 | zhangsan | 20 | man |
| 2 | lisi | 21 | man |
+------+----------+------+------+
2 rows in set (0.00 sec)
mysql> desc hu2; # 查看表结构，也是可以查看的
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> quit # 退出mysql
Bye
[root@localhost ~]#mysql # 再次登入mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.24-log Source distribution
mysql> select * from hu2; # 查看刚才新建的临时表，此时该表已经不存在
ERROR 1046 (3D000): No database selected
临时表的作用就是用来测试表的数据结构的用处
4-4 创建外键约束
创建外键约束，保证数据的完整性和一致性
外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。
4-4-1 首先创建主键表和主键字段
创建主键表class，并设置cid为主键
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| hu1 |
| info |
| info1 |
| info2 |
+--------------+
4 rows in set (0.00 sec)
mysql> create table class (cid int, cname char(10)); # 创建主键表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| class |
| hu1 |
| info |
| info1 |
| info2 |
+--------------+
6 rows in set (0.00 sec)
mysql> alter table class ADD constraint PK_CID primary key(cid); # 给主键表结构添加主键，如果想给这个主键自定义一个名称就用constraint PK_名称
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc class; # 查看主键表结构
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| cid | int(11) | NO | PRI | 0 | | # 此时cid处已经显示添加为主键
| cname | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
4-4-2 其次，创建外键表和外键字段
student作为外键表，现在需要在外键表中创建一个外键字段，然后用外键字段和主键表中的主键字段进行关联
mysql> create table student (id int, cname char(10),age int, classid int); # 创建外键表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------+
| Tables_in_hu |
+--------------+
| class |
| hu1 |
| info |
| info1 |
| info2 |
| student |
+--------------+
6 rows in set (0.00 sec)
mysql>
mysql> alter table student ADD constraint FK_CLASSID foreign key(classid) references class(cid); # 创建外键关联主键表中的cid，如果想给这个主键自定义一个名称就用constraint FK_名称
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| cname | char(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| classid | int(11) | YES | MUL | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table student\G; # 以列表方式竖向显示(这样可以更清楚的显示)
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`cname` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classid` int(11) DEFAULT NULL,
KEY `FK_CLASSID` (`classid`),
CONSTRAINT `FK_CLASSID` FOREIGN KEY (`classid`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4-4-3 插入数据测试
mysql> INSERT INTO class VALUES(1,'语文'); # 在主键表中插入数据(注意命令不区分大小写，但是表名是要区分大小写的)
Query OK, 1 row affected (0.00 sec)
mysql> select * from class; # 查看主键表
+-----+--------+
| cid | cname |
+-----+--------+
| 1 | 语文 |
+-----+--------+
1 row in set (0.00 sec)
mysql> insert into student values(1,'zhangsan',18,1); # 在外键表中插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from student; # 查看表中数据
+------+----------+------+---------+
| id | cname | age | classid |
+------+----------+------+---------+
| 1 | zhangsan | 18 | 1 |
+------+----------+------+---------+
1 row in set (0.00 sec)
mysql> insert into student values(2,'lisi',18,2); # 此时想插入新的数据，但是插入失败，因为classid字段和主键字段相关联，但是主键表中并为定义2这个字段，所以插入失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hu`.`student`, CONSTRAINT `FK_CLASSID` FOREIGN KEY (`classid`) REFERENCES `class` (`cid`))
mysql> INSERT INTO class VALUES(2,'数学'); # 给主键表插入第二条的数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(2,'lisi',18,2); # 此时再给外键表创建2这条数据的时候就可以成功创建，因为已经给主键表创建了第二条的数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from student; # 查看表内的数据
+------+----------+------+---------+
| id | cname | age | classid |
+------+----------+------+---------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 18 | 2 | # 可以看到数据被成功建力了
+------+----------+------+---------+
2 rows in set (0.00 sec)
mysql> delete from class where cname='语文'; # 同理，当你想要删除主键表中的数据的时候，会报错，因为此时外键表和主键表的数据正在关联，所以删除会失败
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hu`.`student`, CONSTRAINT `FK_CLASSID` FOREIGN KEY (`classid`) REFERENCES `class` (`cid`))
mysql> delete from student where classid=1; # 如果想要删除外键表的第一条数据的话，就需要先删除主键表对应的第一条数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from class; #查看表数据，主键表中的第一条数据已被删除
+-----+--------+
| cid | cname |
+-----+--------+
| 2 | 数学 |
+-----+--------+
1 row in set (0.00 sec)
mysql> delete from student where classid=1; # 然后再次尝试删除外键表的第一条数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from student; #
+------+-------+------+---------+
| id | cname | age | classid |
+------+-------+------+---------+
| 2 | lisi | 18 | 2 | # 可以看到这边第一条数据已经被成功删除
+------+-------+------+---------+
1 row in set (0.00 sec)
做了外键约束后，要插入新的数据记录时，需要先在主键表中插入相关数据，才能在外键表插入相关数据
插入数据时，是先主后从
要删除数据记录时，要先删除外键表的相关数据，才能在主键表删除相关数据记录
删除数据时，是先从再主
4-4-4 删除外键约束
mysql> show create table student\G; # 查看外键的定义
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`cname` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classid` int(11) DEFAULT NULL,
KEY `FK_CLASSID` (`classid`),
CONSTRAINT `FK_CLASSID` FOREIGN KEY (`classid`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table student DROP FOREIGN KEY FK_CLASSID; # 删除外键的约束
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`cname` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classid` int(11) DEFAULT NULL,
KEY `FK_CLASSID` (`classid`) # 这里可以看到“ CONSTRAINT `FK_CLASSID` FOREIGN KEY (`classid`) REFERENCES `class` (`cid`)”这条已经被删除，但是别名依然还在
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table student drop key `FK_CLASSID`; # 删除别名
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G; # 此时再查看外键的定义
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`cname` char(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`classid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 此时已经全部被删除
1 row in set (0.00 sec)
第一次删除的是外键约束，第二次删除的是键
注意：如果在设置外键约束的时候没有设置别名，系统会自动设置一个别名，所以删除的时候还是需要在删除外键约束后再删除外键的别名
MySQL中6种常见的约束：
主键约束(primary key)
外健约束（foreign key)
非空约束（not nul1）
唯一性约束(unique [keylindex]）
默认值约束（default）
自增约束(auto_increment)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
