【MySQL基础语法】

最新推荐文章于 2024-07-26 09:40:31 发布

.陌路

最新推荐文章于 2024-07-26 09:40:31 发布

阅读量649

点赞数 1

分类专栏： mysql linux 文章标签： mysql 数据库 sql database

本文链接：https://blog.csdn.net/qq_51076413/article/details/122023535

版权

mysql 同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

linux

5 篇文章 0 订阅

订阅专栏

MySQL的约束

> > > MySQL基础

> > > MySQL的多表操作查询

> > > MySQL多表操作练习

> > > MySQL视图、存储过程、函数、触发器

👇👇👇创建表结构和添加数据的SQL在底部👇👇👇

--  MySQL约束  --
--  对表中的数据进行限定,保证数据的正确性,完整性,有效性
-- PRIMARY KEY						主键约束
-- PRIMARY KEY AUTO_INCREMENT		主键自增
-- UNIQUE							唯一约束
-- NOT NULL							非空约束
-- FOREIGN KEY						外键约束
-- FOREIGN KEY ON UPDATE CASCADE	外键级联更新
-- FOREIGN KEY ON DELETE CASCADE	外键级联删除

PRIMARY KEY：主键约束

主键约束特点: 默认包含非空和唯一
一张表只能有一个主键主键一般是表中数据的唯一标识

建表时添加主键约束

CREATE TABLE 表名(列名 数据类型 PRIMARY KEY...);
CREATE TABLE tb_user(
	id  int  PRIMARY KEY, -- id主键约束
	....
);

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY; – 删除主键

AFTER TABLE tb_user DROP PRIMARY KEY;

建表完成后，给表单独添加主键约束

ALTER TABLE 表名 MODIFY 列名数据类型 PRIMARY KEY;

ALTER TABLE tb_user MODIFY id int PRIMARY KEY;

AUTO_INCREMENT：主键自增约束

主键自增约束 ** MySQL中自增约束必须配合主键约束一起使用.

CREATE TABLE tb_user(
	ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增约束
	.....
);

删除主键自增约束

ALTER TABLE 表名 MODIFY 列名数据类型;

AFTER TABLE TB_USER MODIFY ID INT;

单独添加主键自增约束

ALTER TABLE 表名 MODIFY 列名数据类型 AUTO_INCREMENT;

ALTER TABLE TB_USER MODIFY ID INT AUTO_INCREMENT;

UNIQUE：唯一约束

CREATE TABLE TB_USER (
	ID INT UNIQUE,   -- 唯一约束(列名  数据类型  UNIQUE)
    ......
);

删除唯一约束

ALTER TABLE 表名 DROP INDEX 列名;

ALTER TABLE TB_USER DROP INDEX ID;

单独添加唯一约束

ALTER TABLE 表名 MODIFY 列名数据类型 UNIQUE;

ALTER TABLE TB_USER MODIFY ID INT UNIQUE;

NOT NULL：非空约束

CREATE TABLE TB_USER(
	ID INT NOT NULL,  -- 非空约束
    ......
);

删除非空约束

ALTER TABLE 表名 MODIFY 列名数据类型;

ALTER TABLE TB_USER MODIFY ID INT;

单独添加非空约束

ALTER TABLE 表名 MODIFY 列名数据类型 NOT NULL;

ALTER TABLE TB_USER MODIFY ID INT NOT NULL;

FOREIGN KEY：外键约束

外键约束作用: 让表与表之间产生关联关系,从而保证数据准确性
表与表之间相关联的时候,若无相关约束,则无法保证数据的准确性

CREATE TABLE TB_USER_INFO(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	....
	U_ID INT,
	-- CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
	CONSTRAINT OU_FK1 FOREIGN KEY (U_ID) REFERENCES USER(ID) -- U_ID：外键名，USER(ID)：user表中的主键id
);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

ALTER TABLE TB_USER DROP FOREIGN KEY OU_FK1;

单独添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(本表外键列名) REFERENCES 主表名(主键列名);

ALTER TABLE TB_USER ADD CONSTRAINT OU_FK1 FOREIGN KEY U_ID REFERENCES TB_USER(ID);

FOREIGNKEY ON UPDATE CASCADE 外键级联更新

FOREIGN KEY ON DELETE CASCADE 外键级联删除

外键的级联更新和级联删除
级联更新：修改主表数据时,从表中有关联的数据也随之修改
级联删除：删除主表数据时,从表中有关联的数据也随之删除

添加级联更新

ALTER TABLE TB_USER ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名)
REFERENCES 主表名(主键列名) ON UPDATE CASCADE;

添加级联删除

ALTER TABLE TB_USER ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名)
REFERENCES 主表名(主键列名) ON DELETE CASCADE;

同时添加级联更新和级联删除

ALTER TABLE TB_USER ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名)
ON UPDATE CASCADE ON DELETE CASCADE;

👇👇👇创建表结构和添加数据👇👇👇

--  创建TB_TEST数据库

CREATE DATABASE TB_TEST;
USE TB_TEST;

--  创建orderlist表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for orderlist
-- ----------------------------
DROP TABLE IF EXISTS `orderlist`;
CREATE TABLE `orderlist`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单主键id',
  `number` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '订单编号',
  `uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_orderlist_tb_user_1`(`uid`) USING BTREE,
  CONSTRAINT `fk_orderlist_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orderlist
-- ----------------------------
INSERT INTO `orderlist` VALUES (1, ' BM0001', 1);
INSERT INTO `orderlist` VALUES (2, 'BM0002', 1);
INSERT INTO `orderlist` VALUES (3, 'BM0003', 2);
INSERT INTO `orderlist` VALUES (4, 'BM0004', 2);
INSERT INTO `orderlist` VALUES (5, 'BM0005', 3);
INSERT INTO `orderlist` VALUES (6, 'BM0006', 3);
INSERT INTO `orderlist` VALUES (7, 'BM0007', 4);
INSERT INTO `orderlist` VALUES (8, 'BM0008', NULL);

SET FOREIGN_KEY_CHECKS = 1;

--  创建user表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '用户年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 23);
INSERT INTO `user` VALUES (2, '李四', 24);
INSERT INTO `user` VALUES (3, '王五', 25);
INSERT INTO `user` VALUES (4, '赵六', 26);
INSERT INTO `user` VALUES (5, '邹七', 27);

SET FOREIGN_KEY_CHECKS = 1;

--  创建product表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `cid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_product_category_1`(`cid`) USING BTREE,
  CONSTRAINT `fk_product_category_1` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, '华为手机', 1);
INSERT INTO `product` VALUES (2, '小米手机', 1);
INSERT INTO `product` VALUES (3, '联想电脑', 2);
INSERT INTO `product` VALUES (4, '苹果电脑', 2);
INSERT INTO `product` VALUES (5, '中华香烟', 3);
INSERT INTO `product` VALUES (6, '玉溪香烟', 3);
INSERT INTO `product` VALUES (7, '计生用品', NULL);

SET FOREIGN_KEY_CHECKS = 1;

--  创建us_pro中间表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for us_pro
-- ----------------------------
DROP TABLE IF EXISTS `us_pro`;
CREATE TABLE `us_pro`  (
  `upid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
  `pid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`upid`) USING BTREE,
  INDEX `fk_us_pro_tb_user_1`(`uid`) USING BTREE,
  INDEX `fk_us_pro_product_1`(`pid`) USING BTREE,
  CONSTRAINT `fk_us_pro_product_1` FOREIGN KEY (`pid`) REFERENCES `product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_us_pro_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of us_pro
-- ----------------------------
INSERT INTO `us_pro` VALUES (1, 1, 2);
INSERT INTO `us_pro` VALUES (2, 2, 1);
INSERT INTO `us_pro` VALUES (3, 3, 1);
INSERT INTO `us_pro` VALUES (4, 3, 3);
INSERT INTO `us_pro` VALUES (5, 2, 3);
INSERT INTO `us_pro` VALUES (6, 4, 2);
INSERT INTO `us_pro` VALUES (7, 3, 2);
INSERT INTO `us_pro` VALUES (8, 4, 1);
INSERT INTO `us_pro` VALUES (9, 5, 2);
INSERT INTO `us_pro` VALUES (10, 5, 5);

SET FOREIGN_KEY_CHECKS = 1;

-- 创建account表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 账号id',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名',
  `money` double NULL DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, '张三', 1000);
INSERT INTO `account` VALUES (2, '李四', 1000);
INSERT INTO `account` VALUES (3, '王五', 5000);

-- 创建account_log

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for account_log
-- ----------------------------
DROP TABLE IF EXISTS `account_log`;
CREATE TABLE `account_log`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志id',
  `operation` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型(INSERT,UPDATE,DELETE)',
  `operation_time` timestamp NULL DEFAULT NULL COMMENT '操作时间',
  `operation_id` int(11) NULL DEFAULT NULL COMMENT '操作表的id',
  `operation_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作参数',
  `bf_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作前的参数',
  `af_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作后的参数',
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型名(新增,更新,删除)',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of account_log
-- ----------------------------
INSERT INTO `account_log` VALUES (1, 'INSERT', '2021-12-18 15:27:58', 3, '{id=3,name=王五,money=3000}', '', '插入的值{id=3,name=王五,money=3000}', '新增');
INSERT INTO `account_log` VALUES (2, 'UPDATE', '2021-12-18 15:40:50', 3, '{id=3,name=王五,money=5000}', '修改前的值{id=3,name=王五,money=3000}', '修改后的值{id=3,name=王五,money=5000}', '更新');

SET FOREIGN_KEY_CHECKS = 1;

> > > MySQL基础

MySQL基础：https://blog.csdn.net/qq_51076413/article/details/122023535

> > > MySQL的多表操作查询

多表操作查询：https://blog.csdn.net/qq_51076413/article/details/122024324

> > > MySQL多表操作练习

MySQL多表操作练习：https://blog.csdn.net/qq_51076413/article/details/122024838

> > > MySQL视图、存储过程、函数、触发器

MySQL视图、存储过程、函数、触发器：https://blog.csdn.net/qq_51076413/article/details/122025461

.陌路

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【MySQL基础语法】

MySQL的约束-- MySQL约束 ---- 对表中的数据进行限定,保证数据的正确性,完整性,有效性-- PRIMARY KEY 主键约束-- PRIMARY KEY AUTO_INCREMENT 主键自增-- UNIQUE 唯一约束-- NOT NULL 非空约束-- FOREIGN KEY 外键约束-- FOREIGN KEY ON UPDATE CASCADE 外键级联更新-- FOREIGN KEY ON DELETE CASC
复制链接

扫一扫

专栏目录