项目三，创建和管理数据表

最新推荐文章于 2024-05-26 23:46:59 发布

鸷鸟之不群

最新推荐文章于 2024-05-26 23:46:59 发布

阅读量439

点赞数 1

分类专栏： MySQL 文章标签：数据库 mysql Powered by 金山文档

本文链接：https://blog.csdn.net/weixin_64420247/article/details/129760816

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

在继续学习之前，先创建一个库，创建表，导入数据，方便后续的继续学习。

复制以下代码直接运行，会得到一个db_shop的库，已经有相关数据了。

推荐在Navicat里创建运行

安装版，附上教程

链接：https://pan.baidu.com/s/1X2iGhnKrEE98tfdnzAn-rQ?pwd=walq 提取码：walq

/*
 Navicat Premium Data Transfer

 Source Server         : MySQL8.0.15
 Source Server Type    : MySQL
 Source Server Version : 80015
 Source Host           : localhost:3306
 Source Schema         : db_shop

 Target Server Type    : MySQL
 Target Server Version : 80015
 File Encoding         : 65001

 Date: 05/03/2021 21:32:55
添加了中文字符编码
*/
CREATEDATABASE db_shop DEFAULTCHARACTERSET gbk COLLATE gbk_chinese_ci;

USE db_shop;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS =0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROPTABLEIFEXISTS`department`;
CREATETABLE`department`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'部门编号（定义主键）',
  `dept_name`varchar(20)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'部门名称（定义唯一键）',
  `dept_phone`char(13)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'部门电话',
  `dept_memo`varchar(100)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'备注',
  PRIMARYKEY(`id`)USINGBTREE,
  UNIQUEINDEX`dept_name`(`dept_name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=8CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='部门信息'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------
INSERTINTO`department`VALUES(1,'总经办','020','总经理');
INSERTINTO`department`VALUES(2,'销售部','020-87991234','负责销售');
INSERTINTO`department`VALUES(3,'采购部','13661256821','负责商品采购');
INSERTINTO`department`VALUES(4,'客服部','020-87993095',NULL);
INSERTINTO`department`VALUES(5,'技术部','020-87993692',NULL);
INSERTINTO`department`VALUES(6,'仓管部','020-87993691',NULL);
INSERTINTO`department`VALUES(7,'维修部','13612312625',NULL);
INSERTINTO`department`VALUES(8,'公关部','020-38128912',NULL);


-- ----------------------------
-- Table structure for supplier
-- ----------------------------
DROPTABLEIFEXISTS`supplier`;
CREATETABLE`supplier`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `supplier_name`varchar(100)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'供应商名称',
  `phone`char(11)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'手机号',
  PRIMARYKEY(`id`)USINGBTREE,
  UNIQUEINDEX`supplier_name`(`supplier_name`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=7CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='供应商信息'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of supplier
-- ----------------------------
INSERTINTO`supplier`VALUES(1,'北京路城南方天贸','13138490819');
INSERTINTO`supplier`VALUES(2,'农历下路广百天怡','13132312631');
INSERTINTO`supplier`VALUES(3,'怡宝','13132491935');
INSERTINTO`supplier`VALUES(4,'珠江牌','13321490728');
INSERTINTO`supplier`VALUES(5,'广州李锦记','13736490938');
INSERTINTO`supplier`VALUES(6,'农夫山泉','13135490231');
INSERTINTO`supplier`VALUES(7,'味极鲜','020-3890123');



-- ----------------------------
-- Table structure for goods_type
-- ----------------------------
DROPTABLEIFEXISTS`goods_type`;
CREATETABLE`goods_type`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `name`varchar(50)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'商品类型名称',
  `memo`varchar(300)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'备注',
  PRIMARYKEY(`id`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=3CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='商品类型信息表'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of goods_type
-- ----------------------------
INSERTINTO`goods_type`VALUES(1,'饮用水','单支出售');
INSERTINTO`goods_type`VALUES(2,'牛奶','');
INSERTINTO`goods_type`VALUES(3,'酱油','');


-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROPTABLEIFEXISTS`goods`;
CREATETABLE`goods`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `goods_name`varchar(50)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'商品名称',
  `supplier_id`int(11)NOTNULLCOMMENT'供应商标识',
  `goods_type`char(20)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'商品类型',
  `banner`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'商品图片',
  `introduce`varchar(255)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'商品介绍',
  `unit_price`decimal(11,2)UNSIGNEDNULLDEFAULT0.00COMMENT'单价',
  `amount`int(10)UNSIGNEDNULLDEFAULT0COMMENT'数量',
  `goods_memo`varchar(300)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'备注',
  PRIMARYKEY(`id`)USINGBTREE,
  INDEX`supplier_id`(`supplier_id`)USINGBTREE,
  CONSTRAINT`goods_ibfk_1`FOREIGNKEY(`supplier_id`)REFERENCES`supplier`(`id`)ONDELETERESTRICTONUPDATERESTRICT
)ENGINE=InnoDBAUTO_INCREMENT=9CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='商品信息表'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERTINTO`goods`VALUES(1,'普通酱油',1,'酱油',NULL,'甜',12.30,51,'海天');
INSERTINTO`goods`VALUES(2,'顶级酱油',1,'酱油',  NULL,'鲜甜',22.30,14,'开平味极鲜');
INSERTINTO`goods`VALUES(3,'顶级生抽',5,'酱油',  NULL,'甜味',21.00,76,'李锦记');
INSERTINTO`goods`VALUES(4,'精品老抽',4,'酱油',NULL,'味道可口，盐',12.10,93,'珠江牌');
INSERTINTO`goods`VALUES(5,'哇哈哈矿泉水',2,'饮用水',NULL,'清甜',2.30,96,'哇哈哈公司');
INSERTINTO`goods`VALUES(6,'塞上牧场纯牛奶',1,'牛奶',NULL,'香甜',1.50,96,'塞上牧场');
INSERTINTO`goods`VALUES(7,'动力水',2,'饮用水',NULL,'动力源',6.50,98,'动脉');
INSERTINTO`goods`VALUES(8,'蒙牛',6,'牛奶',NULL,'有机牛奶',3.00,8,'特仑苏品牌');
INSERTINTO`goods`VALUES(9,'怡宝矿泉水',3,'饮用水',NULL,'矿泉水',2.00,95,'怡宝公司');

-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROPTABLEIFEXISTS`customer`;
CREATETABLE`customer`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `username`varchar(32)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'账号',
  `password`varchar(32)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'密码',
  `customer_name`varchar(10)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'顾客姓名',
  `sex`enum('F','M')CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULT'F'COMMENT'性别',
  `birthday`dateNULLDEFAULTNULLCOMMENT'生日日期',
  `hobby`set('ball','art','music')CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'兴趣',
  `consumption_amount`decimal(11,2)UNSIGNEDNULLDEFAULT0.00COMMENT'消费金额',
  `menber_balance`decimal(11,2)UNSIGNEDNULLDEFAULT0.00COMMENT'会员余额',
  `photo`varbinary(250)NULLDEFAULTNULLCOMMENT'照片',
  `email`varchar(50)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'邮箱',
  `address` json NULLCOMMENT'地址',
  PRIMARYKEY(`id`)USINGBTREE,
  UNIQUEINDEX`username`(`username`)USINGBTREE
)ENGINE=InnoDBAUTO_INCREMENT=19CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='顾客信息表'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of customer
-- ----------------------------
INSERTINTO`customer`VALUES(1,'10','10','he','F','2020-02-04','art',0.00,0.00,NULL,'','{\"city\": \"广州\", \"road\": \"天天寿路\", \"province\": \"广东省\"}');
INSERTINTO`customer`VALUES(2,'LH','123','李红','F','2000-09-01','ball,art',5000.00,0.00,NULL,NULL,'{\"city\": \"广州\", \"road\": \"黄埔\", \"room\": 16}');
INSERTINTO`customer`VALUES(3,'CHL','123','陈红玲','F','1995-06-01','art',1200.00,0.00,NULL,NULL,'{\"city\": \"潮州\", \"road\": \"朝阳街\", \"room\": \"9\"}');
INSERTINTO`customer`VALUES(13,'zhangs','123','张三','F',NULL,NULL,0.00,0.00,NULL,NULL,'{\"city\": \"广州\", \"road\": \"天河比\", \"room\": 5}');
INSERTINTO`customer`VALUES(14,'LiS','123','李四','F',NULL,NULL,0.00,0.00,NULL,NULL,'{\"city\": \"广州\", \"road\": \"林和街\", \"room\": 10}');
INSERTINTO`customer`VALUES(15,'he','123456','何彬彬','M','2021-01-05','ball,art',143.30,0.00,NULL,'xy@163.com','{\"city\": \"广州\", \"road\": \"林和\", \"province\": \"广东省\"}');
INSERTINTO`customer`VALUES(16,'he2','123456','何小','M','2021-01-06','art',0.00,0.00,NULL,'','{\"city\": \"广州\", \"road\": \"石牌\", \"province\": \"广东省\"}');
INSERTINTO`customer`VALUES(17,'hh','123456','周颜色','F','2020-09-30','ball,art',62.50,0.00,NULL,'hh123@163.com','{\"city\": \"广州\", \"road\": \"广州石牌\", \"province\": \"广东省\"}');
INSERTINTO`customer`VALUES(18,'wu','123456','王五','F','2020-12-31','ball,art',0.00,0.00,NULL,'wu123@163.com','{\"city\": \"广州\", \"road\": \"广州林和\", \"province\": \"广东省\"}');
INSERTINTO`customer`VALUES(19,'kk','123456','凯凯','F','2019-12-04','ball,art',52.00,0.00,NULL,'hh123@163.com','{\"city\": \"广州\", \"road\": \"广州林和\", \"province\": \"广东省\"}');

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROPTABLEIFEXISTS`orders`;
CREATETABLE`orders`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `customer_id`int(11)NOTNULLCOMMENT'顾客标识',
  `create_time`datetime(0)NOTNULLCOMMENT'订单生成时间',
  `amount`decimal(11,2)UNSIGNEDNOTNULLDEFAULT0.00COMMENT'订单应付金额',
  `paid_day`datetime(0)NULLDEFAULTNULLCOMMENT'支付时间',
  `status`int(11)NOTNULLCOMMENT'支付状态',
  `memo`varchar(100)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'备注',
  PRIMARYKEY(`id`)USINGBTREE,
  INDEX`customer_id`(`customer_id`)USINGBTREE,
  CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`customer_id`)REFERENCES`customer`(`id`)ONDELETERESTRICTONUPDATERESTRICT
)ENGINE=InnoDBAUTO_INCREMENT=11CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='订单项目表'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERTINTO`orders`VALUES(1,2,'2018-09-01 00:00:00',234.50,NULL,0,NULL);
INSERTINTO`orders`VALUES(2,2,'2019-06-11 00:00:00',271.50,NULL,0,NULL);
INSERTINTO`orders`VALUES(3,3,'2019-08-01 00:00:00',111.50,NULL,0,NULL);
INSERTINTO`orders`VALUES(4,3,'2019-10-01 00:00:00',61.50,NULL,0,NULL);
INSERTINTO`orders`VALUES(5,15,'2021-01-13 14:52:53',45.20,'2021-01-13 14:53:00',1,'');
INSERTINTO`orders`VALUES(6,15,'2021-01-13 15:00:09',29.20,'2021-01-13 15:00:13',1,'');
INSERTINTO`orders`VALUES(7,15,'2021-01-13 15:09:09',68.90,'2021-01-13 15:09:18',1,'');
INSERTINTO`orders`VALUES(8,17,'2021-01-13 15:24:50',29.20,'2021-01-13 15:24:54',1,'');
INSERTINTO`orders`VALUES(9,17,'2021-01-13 15:25:19',33.30,'2021-01-13 15:25:23',1,'');
INSERTINTO`orders`VALUES(10,19,'2021-01-13 18:12:28',32.70,'2021-01-13 18:12:34',1,'');
INSERTINTO`orders`VALUES(11,19,'2021-01-13 18:13:06',19.30,'2021-01-13 18:13:09',1,'');

-- ----------------------------
-- Table structure for item
-- ----------------------------
DROPTABLEIFEXISTS`item`;
CREATETABLE`item`  (
  `id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键',
  `order_id`int(11)NULLDEFAULTNULLCOMMENT'订单标识',
  `goods_id`int(11)NULLDEFAULTNULLCOMMENT'商品标识',
  `supplier_id`int(11)NULLDEFAULTNULLCOMMENT'供应商标识',
  `quantity`int(11)NULLDEFAULTNULLCOMMENT'单个订单商品数量',
  `total_price`decimal(11,2)UNSIGNEDNULLDEFAULT0.00COMMENT'单个订单商品总额',
  PRIMARYKEY(`id`)USINGBTREE,
  INDEX`goods_id`(`goods_id`)USINGBTREE,
  INDEX`supplier_id`(`supplier_id`)USINGBTREE,
  INDEX`order_id`(`order_id`)USINGBTREE,
  CONSTRAINT`item_ibfk_1`FOREIGNKEY(`goods_id`)REFERENCES`goods`(`id`)ONDELETERESTRICTONUPDATERESTRICT,
  CONSTRAINT`item_ibfk_2`FOREIGNKEY(`supplier_id`)REFERENCES`supplier`(`id`)ONDELETERESTRICTONUPDATERESTRICT,
  CONSTRAINT`item_ibfk_3`FOREIGNKEY(`order_id`)REFERENCES`orders`(`id`)ONDELETERESTRICTONUPDATERESTRICT
)ENGINE=InnoDBAUTO_INCREMENT=33CHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='订单详细表'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of item
-- ----------------------------
INSERTINTO`item`VALUES(4,1,1,1,10,123.00);
INSERTINTO`item`VALUES(5,1,2,1,5,111.50);
INSERTINTO`item`VALUES(6,2,1,3,5,61.50);
INSERTINTO`item`VALUES(7,2,3,2,10,210.00);
INSERTINTO`item`VALUES(8,3,2,2,5,111.50);
INSERTINTO`item`VALUES(9,4,1,3,5,61.50);
INSERTINTO`item`VALUES(10,1,2,1,10,0.00);
INSERTINTO`item`VALUES(11,1,2,1,17,0.00);
INSERTINTO`item`VALUES(13,2,1,2,8,0.00);
INSERTINTO`item`VALUES(14,5,4,4,2,24.20);
INSERTINTO`item`VALUES(15,5,3,5,1,21.00);
INSERTINTO`item`VALUES(16,6,4,4,2,24.20);
INSERTINTO`item`VALUES(17,6,6,1,2,3.00);
INSERTINTO`item`VALUES(18,6,9,3,1,2.00);
INSERTINTO`item`VALUES(19,7,3,5,2,42.00);
INSERTINTO`item`VALUES(20,7,2,1,1,22.30);
INSERTINTO`item`VALUES(21,7,5,2,2,4.60);
INSERTINTO`item`VALUES(22,8,4,4,2,24.20);
INSERTINTO`item`VALUES(23,8,6,1,2,3.00);
INSERTINTO`item`VALUES(24,8,9,3,1,2.00);
INSERTINTO`item`VALUES(25,9,3,5,1,21.00);
INSERTINTO`item`VALUES(26,9,1,1,1,12.30);
INSERTINTO`item`VALUES(27,10,4,4,1,12.10);
INSERTINTO`item`VALUES(28,10,1,1,1,12.30);
INSERTINTO`item`VALUES(29,10,8,6,2,6.00);
INSERTINTO`item`VALUES(30,10,5,2,1,2.30);
INSERTINTO`item`VALUES(31,11,9,3,2,4.00);
INSERTINTO`item`VALUES(32,11,7,2,2,13.00);
INSERTINTO`item`VALUES(33,11,5,2,1,2.30);

SET FOREIGN_KEY_CHECKS =1;

运行后查看有哪些库了

SHOWDATABASES;

一、查看数据表与数据类型

1.选择mysql这个库，查看这个库有哪些表

USE mysql;
SHOWTABLES;

2.查看系统数据库mysql中的user表结构定义信息

USE mysql;
DESCuser;

Field:表示表的字段名称；

Type：表示字段值的数据类型；

Null：表示字段值是否允许空值；

Key：表示字段是否为键，比如主键，外键，唯一键；

Default：表示默认值；

Extra：表示其他额外信息，比如自增字段标识等。

3.查看mysql系统数据库的user表定义脚本

USE mysql;
SHOWCREATETABLEuser\G

4.查看MySQL数据类型

查看MySQL支持的所有数据类型名称

HELPDATA TYPES;

4.1查看INT数据类型

可以查看到类型说明和存储数值范围

HELPINT;

5.查看db_shop数据库的department表的全部记录(以下是使用Navicat软件进行运行)

USE db_shop;
SELECT*FROM department;

二、数据类型

1.数字类型

2.字符串类型

3.日期和时间类型

4.JSON类型

三、数据表和键

1.数据表

2.主键

3.唯一键

4.外键

四、关系数据完整性约束

1.域完整性

2.实体完整性

3.参照完整性

4.用户定义的完整性

五、创建数据表，建立数据表和数据完整性

1.CREATE TABLE 语句

2.设置表的主键和唯一键

3.设置外键、检查约束和默认值约束

4.设置表的存储引擎、字符集

5.使用字符串特殊类型和JSON类型

六、复制数据表

1.LIKE复制表结构

USE db_shop;
CREATETABLEIFNOTEXISTS customer_cp LIKE customer;

复制出来的表结构与原表一模一样，完整性约束也相同，但里面的数据不会被复制。可以通过下面语句来查询数据表和表结构。

SHOWTABLES;
DESC customer_cp;

2.AS复制表结构和记录

此语法可复制表结构字段定义及其检索出来的记录，但不复制主键、索引、自动编号等。

该语句相当于把检索出来的记录作为一个新表保存。

USE db_shop;
CREATETABLEIFNOTEXISTS customer_cp2 ASSELECT*FROM customer;

可以看到已经复制的新表结构与原来的一致，但没有复制到完整性约束和自增字段设置，并且AS检索记录也被复制过来。查看语句如下

SHOWTABLES;
DESC customer_CP2;
SELECT*FROM customse_cp2;

七、修改数据表

1.ALTER TABLE语句用来修改数据表的定义和数据完整性约束信息

2.添加表字段

FIRST|AFTER [字段名] 表示添加的字段需要放到表的哪一列的前面或者后面，省略则添加到表的最后，列的顺序没有关系，显示顺序可以用后面学习的检索语句来实现。

当表中已经有记录时，添加的字段必须是允许空的。

USE db_shop;
ALTERTABLE customer_cp2 ADD name1 DATENULL;

使用DESC customer_cp2;来查看增加的字段

3.修改字段类型

在customer_cp2表上修改字段birthday的类型为datetime(6)

AFTER hobby,修改后放到hobby字段的后面

USE db_shop;
ALTERTABLE customer_cp2 MODIFY birthday datetime(6)AFTER hobby;
DESC customer_cp2;

4.修改自增类型字段

说明：自增字段的初始值默认是1，如果不是1，则可以在创建时设置初始值或通过修改表自增值

ALTER TABLE customer_cp2 AUTO_INCREMENT=初始值；

USE db_shop;
ALTERTABLE customer_cp2 AUTO_INCREMENT=100501;

5.添加字段约束条件

给customer的复制表customer_cp2添加主键

USE db_shop;
ALTERTABLE customer_cp2 ADDPRIMARYKEY(id);
DESC customer_cp2;

5.1给customer的复制表customer_cp2的username添加唯一键

USE db_shop;
ALTERTABLE customer_cp2 ADDUNIQUE(username);
DESC customer_cp2;

5.2给customer的复制表customer_cp2的name1添加外键(customer_cp的id)

USE db_shop;
ALTERTABLE customer_cp2 ADDFOREIGNKEY(name1)REFERENCES customer_cp(id);
DESC customer_cp2;

设置外键的时候需要注意以下几点：（1）外键是用于两个表的数据之间建立连接，可以是一列或者多列，即一个表可以有一个或多个外键。

（2）这个表里面设置的外键必须是另外一个表的主键！

（3）外键可以不是这个表的主键，但必须和另外一个表的主键相对应（字段的类型和值必须一样）。

（4）带有主键的那张表称为父表，含外键的是子表，必须先删除外键约束才能删除父表。

（5）另一个表的主键没有空值，必须是主键，这个表的外键跟另一个表的主键数据类型要一样。

6.删除字段约束

删除customer_cp2表的主键

USE db_shop;
ALTERTABLE customer_cp2 DROPPRIMARYKEY;
DESC customer_cp2;

6.1删除customer_cp2表的唯一键

第一步：查看表定义语句，找到唯一键的索引名。

这里在终端里运行，因为navicat显示不完全，navicat不用＋\G,直接使用；号

SHOWCREATETABLE customer_cp2 \G

这里可以看到唯一键，主键跟外键

第二步：使用索引名删除唯一键设置

ALTERTABLE customer_cp2 DROPKEY username;
DESC customer_cp2;

6.2删除customer_cp2表的外键

第一步：查看customer_cp2的信息

SHOWCREATETABLE customer_cp2\G

第二步：通过外键标识删除外键（删除外键标识）

ALTERTABLE customer_cp2 DROPFOREIGNKEY customer_cp2_ibfk_1;

第三步：删除外键上建立的索引（KEY）(删除外键字段的外键)

DESC customer_cp2;
ALTERTABLE customer_cp2 DROPKEY name1;
DESC customer_cp2;

7.删除表字段

删除customer_cp2表中的name1字段

USE db_shop;
ALTERTABLE customer_cp2 DROPCOLUMN name1;
DESC customer_cp2;

8.修改表的存储引擎

USE db_shop;
ALTERTABLE customer_cp2 ENGINE=MyISAM;
SHOWCREATETABLE customer_cp2\G

改回去

USE db_shop;
ALTERTABLE customer_cp2 ENGINE=InnoDB;
SHOWCREATETABLE customer_cp2\G

9.修改表的字符集

修改customer_cp2表的字符集为GBK

USE db_shop;
ALTERTABLE customer_cp2 DEFAULTCHARSET= GBK;
SHOWCREATETABLE customer_cp2\G

八、删除数据表

1.删除db_shop数据库中的表customer_cp2

USE db_shop;
DROPTABLE customer_cp2;

#方法二：DROP TABLE IF EXISTS customer_cp2;

九、任务实施

1.完成db_shopping数据库的部门表、职员表的创建

/*
 Navicat Premium Data Transfer

 Source Server         : 33
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : db_shopping

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 24/04/2022 09:22:41
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS =0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROPTABLEIFEXISTS`department`;
CREATETABLE`department`  (
  `id`int(0)NOTNULLAUTO_INCREMENTCOMMENT'部门编号(定义主键)',
  `dept_name`varchar(20)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'部门名称(定义唯一键)',
  `dept_phone`char(13)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'部门电话',
  `dept_memo`varchar(100)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'备注',
  PRIMARYKEY(`id`)USINGBTREE,
  UNIQUEINDEX`dept_name`(`dept_name`)USINGBTREE
)ENGINE=InnoDBCHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='部门信息'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of department
-- ----------------------------

-- ----------------------------
-- Table structure for staffer
-- ----------------------------
DROPTABLEIFEXISTS`staffer`;
CREATETABLE`staffer`  (
  `id`int(0)NOTNULLAUTO_INCREMENTCOMMENT'职员编号',
  `staff_name`varchar(10)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOTNULLCOMMENT'职员姓名',
  `dept_id`int(0)NOTNULLCOMMENT'部门编号',
  `sex`enum('F','M')CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULT'F'COMMENT'性别',
  `birthday`dateNULLDEFAULTNULLCOMMENT'生日日期',
  `phone`char(11)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'电话',
  `salary`decimal(8,2)UNSIGNEDNULLCOMMENT'薪水',
  `staff_memo`varchar(100)CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULLCOMMENT'职员备注',
  PRIMARYKEY(`id`)USINGBTREE,
  UNIQUEINDEX`staff_name`(`staff_name`)USINGBTREE,
  INDEX`dept_id`(`dept_id`)USINGBTREE,
  CONSTRAINT`staffer_ibfk_1`FOREIGNKEY(`dept_id`)REFERENCES`department`(`id`)ONDELETERESTRICTONUPDATERESTRICT
)ENGINE=InnoDBCHARACTERSET= utf8mb4 COLLATE= utf8mb4_0900_ai_ci COMMENT='职员信息'ROW_FORMAT= Dynamic;

-- ----------------------------
-- Records of staffer
-- ----------------------------

SET FOREIGN_KEY_CHECKS =1;

2.查看db_shop数据库当前的所有表

SHOWTABLES;

3.查看部门表department的表结构

DESC department;

4.复制department表结构，命名为department_bak

CREATETABLE department_bak LIKE department;

5.查看department_bak表结构

DESC department_bak;

十、课后练习

supplier表

goods表

orders表

item表

/***
 *             ,%%%%%%%%,
 *           ,%%/\%%%%/\%%
 *          ,%%%\c "" J/%%%
 * %.       %%%%/ o  o \%%%
 * `%%.     %%%%    _  |%%%
 *  `%%     `%%%%(__Y__)%%'
 *  //       ;%%%%`\-/%%%'
 * ((       /  `%%%%%%%'
 *  \\    .'          |
 *   \\  /       \  | |
 *    \\/         ) | |
 *     \         /_ | |__
 *     (___________))))))) 攻城湿
 */

鸷鸟之不群

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
项目三，创建和管理数据表

FIRST|AFTER [字段名] 表示添加的字段需要放到表的哪一列的前面或者后面，省略则添加到表的最后，列的顺序没有关系，显示顺序可以用后面学习的检索语句来实现。设置外键的时候需要注意以下几点：（1）外键是用于两个表的数据之间建立连接，可以是一列或者多列，即一个表可以有一个或多个外键。可以通过下面语句来查询数据表和表结构。（3）外键可以不是这个表的主键，但必须和另外一个表的主键相对应（字段的类型和值必须一样）。（5）另一个表的主键没有空值，必须是主键，这个表的外键跟另一个表的主键数据类型要一样。
复制链接

扫一扫