MySQL update 更新主键id mysql修改主键值

转载

陌陌香阁 2024-02-09 16:59:17

文章标签 MySQL update 更新主键id mysql 数据库字段主键 文章分类 MySQL 数据库

文章学习 —— 极客时间的《 MySQl必知必会》、

文章目录

插入数据
插入查询结果
删除数据
修改数据
查询数据
增删改查总结
主键

业务字段做主键

使用自增字段做主键
手动赋值字段做主键
主键总结

插入数据

MySQL update 更新主键id mysql修改主键值_数据库

INSERT INTO demo.goodsmaster (
itemnumber, barcode, goodsname, specification,unit, price
) VALUES (
4, '0003', '尺子', '三角型', '把', 5
);

select * from demo.goodsmaster;

INSERT INTO demo.goodsmaster (    -- 只添加部分的值
barcode, goodsname, price
) VALUES (
'0004', '测试', 10
);

我们在插入一条数据记录的时候，必须要考虑字段约束的 3 种情况。
第一种情况是，如果字段允许为空，而我们没有给它赋值，那么 MySQL 会自动给它们赋予空值。
第二种情况是，如果字段是主键，就不能为空，这个时候，MySQL 会按照我们添加的约束进行处理。
第三种情况是，如果有一个字段定义不能为空，又不是主键，当你插入一条数据记录的时候，就必须给这个记录赋值。

添加数据INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);

我们的操作违反了字段的约束限制我们尝试把表 demo.goodsmaster 的字段“speicification”改为不能为空

ALTER TABLE demo.goodsmaster MODIFY specification TEXT NOT NULL;

原因就是我们刚才部分插入了一条数据记录，没有给字段“specification”赋值，这跟我们给字段“specification”添加非空约束的操作冲突了

DELETE FROM demo.goodsmaster WHERE itemnumber=5;

ALTER TABLE demo.goodsmaster MODIFY specification TEXT NOT NULL;

删除了就没有问题了
部分插入一条数据记录是可以的，但前提是，没有赋值的字段，一定要让 MySQL 知道如何处理，比如可以为空、有默认值，或者是自增约束字段，等等，否则，MySQL 会提示错误的。

插入查询结果

效率比较高的插入语句

INSERT INTO 表名 （字段名） 
SELECT 字段名或值 
FROM 表名 
WHERE 条件


-- 再一个旧表中插入一个新表并且增加旧表中的元素
INSERT INTO 历史流水表 （日结时间字段，其他字段）  -- 历史流水表增加了一些字段来标识历史流水的状态
SELECT 获取当前时间函数，其他字段 
FROM 流水表

删除数据

DELETE FROM 表名 
WHERE 条件


DELETE FROM demo.goodsmaster;  -- Workbench 自动处于安全模式
-- WHERE 条件中，必须用到主键约束或者唯一性约束的字段。 MySQL 的这种安全性设置，主要就是为了防止删除或者修改数据时出现误操作，导致删除 或修改了不相关的数据。我们要习惯在删除数据的时候，添加条件语句 WHERE，防止误操作。

DELETE FROM demo.goodsmaster 
WHERE itemnumber > 1;

修改数据

UPDATE 表名 
SET 字段名=值 
WHERE 条件
-- 不要修改主键字段的值。因为主键是数据记录的唯一标 识，如果修改了主键的值，就有可能会破坏数据的完整性。
-- 如果我们修改了主键的值，就可能会改变刚才的查询结果：查询结果是空 因为商品编号是 3 的数据记录，已经不存在了。

查询数据

查询可以多次

SELECT *|字段列表    -- 是查询关键字，表示我们要做一个查询  *通配符
FROM 数据源   -- 查询数据源
WHERE 条件   -- 表示查询条件。  
GROUP BY 字段  -- 查询结果要如何分组  经常与 MySQL 的聚合函数 一起使用。
HAVING 条件   -- 经常与 MySQL 的聚合函数 一起使用。
ORDER BY 字段   -- 排序
LIMIT 起始点，行数

form : 关键字表示查询的数据源 , 数据源也不一定是表，也可以是一个查询的结果。

MySQL update 更新主键id mysql修改主键值_mysql_02

红色框里的部分叫做派生表（derived table），或者子查询（subquery），意思是我们把一个查询结果数据集当做一个虚拟的数据表来看待。 MySQL 规定，必须要用 AS 关键字给这个派生表起一个别名。在这张图中，我给这个派生表起了个名字，叫做“a”

ORDER BY 的作用，是告诉 MySQL，查询结果如何排序。ASC 表示升序，DESC 表示降序。

SELECT * FROM demo.goodsmaster 
ORDER BY barcode ASC,price DESC;

LIMIT 的作用是告诉 MySQL 只显示部分查询的结果.

SELECT * FROM demo.goodsmaster 
LIMIT 1,2;  
-- “1”表示起始位置，MySQL 中，起始位置的起点是 0，1 表示 从第 2 条记录开始；“2”表示 2 条数据。因此，“LIMIT 1,2”就表示从第 2 条数据开 始，显示 2 条数据，也就是显示了第 2、3 条数据。

如果我们把查询的结果插入到表中时，导致主键约束或者唯一性约束被破坏了，就可以用“ON DUPLICATE”关键字进行处理。这个关键字的作用是，告诉MySQL，如果遇到重复的数据，该如何处理。

MySQL update 更新主键id mysql修改主键值_mysql_03

INSERT INTO demo.goodsmaster 
SELECT 
* FROM demo.goodsmaster1 as a 
ON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;

增删改查总结

INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);

INSERT INTO 表名 （字段名）
SELECT 字段名或值 
FROM 表名 
WHERE 条件

DELETE 
FROM 表名 
WHERE 条件

UPDATE 表名 
SET 字段名=值 
WHERE 条件


SELECT *|字段列表 
FROM 数据源 
WHERE 条件 
GROUP BY 字段 
HAVING 条件 
ORDER BY 字段 
LIMIT 起始点，行数

主键

MySQL update 更新主键id mysql修改主键值_数据库_04

三种设置主键的思路：业务字段做主键、自增字段做主键和手动赋值字段做主键

业务字段做主键

会员卡号（cardno）看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录。

CREATE TABLE demo.membermaster  
(  
    cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键  
    membername TEXT,  
    memberphone TEXT,  
    memberpid TEXT,  
    memberaddress TEXT,  
    sex TEXT,  
    birthday DATETIME  
);
DESCRIBE demo.membermaster;  -- 除了字段 cardno，所有 的字段都允许为空。这是因为，这些信息有可能当时不知道，要稍后补齐

-- 如果想把主键的信息 除了主键之外其他信息被了一个 对象取代  做法如下:\
实现一波  看文档
补充


-- 建议尽量不要用业务字段，也就是跟业务有关的字段做主键。毕竟，作为项目 设计的技术人员，我们谁也无法预测在项目的整个生命周期中，哪个业务字段会因为项目 的业务需求而有重复，或者重用之类的情况出现

使用自增字段做主键

删除主键约束，并不会删除字段。

//修改会员信息表，删除表的主键约束
ALTER TABLE demo.membermaster
DROP PRIMARY KEY;

//修改会员信息表，添加字段“id”为主键
ALTER TABLE demo.membermaster
ADD id INT PRIMARY KEY AUTO_INCREMENT;

//添加新的字段 memberid，对应会员信息表中的主键
ALTER TABLE demo.trans ADD memberid INT;

给新添加的字段“memberid”赋值，让它指向对应 的会员信息：
UPDATE demo.trans AS a,demo.membermaster AS b  
SET a.memberid=b.id  
WHERE a.transactionno > 0 
AND a.cardno = b.cardno; -- 这样操作可以不用删除trans的内容，在实际工作中更适合

手动赋值字段做主键

如果有俩个表重复的话，可以弄一个idx在总表，每次取idx作为编号，就不会导致俩个表插入到总表发生重复的问题了。

主键总结

用业务字段做主键，看起来很简单，但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况。
自增字段做主键，对于单机系统来说是没问题的。但是，如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。
我们可以采用手动赋值的办法，通过一定的逻辑，确保字段值在全系统的唯一性，这样就可以规避主键重复的问题了

如果你的系统比较复杂，尽量给表加一个字段做主键，采用手动赋值的办法，虽然系统开发的时候麻烦一点，却可以避免后面出大问题。

书上代码

INSERT INTO demo.goodsmaster (
barcode, goodsname, specification,unit, price
) VALUES (
 '0003', '尺子', '三角型', '把', 5
);

select * from demo.goodsmaster;

truncate table demo.goodsmaster;
INSERT INTO demo.goodsmaster ( 
barcode, goodsname, price
) VALUES (
'0004', '三角', 10
);

alter table table_name add column itemnumber int primary key auto_increment not null;


ALTER TABLE demo.goodsmaster MODIFY specification TEXT NOT NULL;

DELETE FROM demo.goodsmaster WHERE itemnumber=0001;

DELETE FROM demo.goodsmaster;

DELETE FROM demo.goodsmaster WHERE itemnumber > 1;
select * from demo.goodsmaster;
CREATE TABLE demo.goodsmaster2 AS ( SELECT DISTINCT * FROM demo.goodsmaster );

select a.goodsname,a.price 
from (
select * from demo.goodsmaster
)AS a;

select * from demo.membermaster;
delete from demo.membermaster where cardno = NULL;

describe demo.membermaster;


CREATE TABLE demo.membermaster  ( cardno CHAR(8) PRIMARY KEY, 
membername TEXT, memberphone TEXT, 
memberpid TEXT, 
memberaddress TEXT, 
sex TEXT, birthday DATETIME );

DESCRIBE demo.membermaster;

INSERT INTO demo.membermaster 
( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) 
VALUES 
( '10000001', '张三', '13812345678', '110123200001017890', '北京', '男', '2000-01-01' );

INSERT INTO demo.membermaster 
( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) 
values
( '10000002', '李四', '13512345678', '123123199001012356', '上海', '女', '1990-01-01'  );

select * from demo.membermaster;


create table demo.trans
(
	transactionno INT, 
    itemnumber INT, -- 为了引用商品信息 
    quantity DECIMAL(10,3), 
    price DECIMAL(10,2), 
    salesvalue DECIMAL(10,2), 
    cardno CHAR(8), -- 为了引用会员信息 
    transdate DATETIME
);


insert into demo.trans
(
	transactionno , 
    itemnumber , 
    quantity , 
    price , 
    salesvalue , 
    cardno ,
    transdate 
)
values
(
	1,
    1,
    1,
    89,
    89,
    '10000001',
    '2020-12-01'
);

select * from demo.trans;

describe demo.trans;

SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate 
FROM demo.trans AS a 
JOIN demo.membermaster AS b 
JOIN demo.goodsmaster AS c
ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);

UPDATE demo.membermaster 
SET membername = '王五', 
memberphone = '13698765432', 
memberpid = '475145197001012356', 
memberaddress='天津', 
sex='女', 
birthday = '1970-01-01' 
WHERE cardno = '10000001';

ALTER TABLE demo.membermaster DROP PRIMARY KEY;

ALTER TABLE demo.membermaster ADD id INT PRIMARY KEY AUTO_INCREMENT;

alter table demo.trans add memberid INT;

ALTER TABLE `demo`.`trans` 
CHANGE COLUMN `transactionno` `transactionno` INT NOT NULL ,
ADD PRIMARY KEY (`transactionno`);

update demo.trans as a,demo.membermaster as b
set a.memberid=b.id
where a.transactionno > 0
and a.cardno = b.cardno;

describe demo.trans;


INSERT INTO demo.membermaster 
( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) 
VALUES 
( '10000001', '王五', '13698765432', '475145197001012356', '天津', '女', '2021-01-01' );

select * from demo.membermaster;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。