【MySQL数据库小白入门基础0614】

最新推荐文章于 2024-09-17 13:42:29 发布

皮嘎嘎帅~u_u

最新推荐文章于 2024-09-17 13:42:29 发布

阅读量109

点赞数

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

本文链接：https://blog.csdn.net/qq_36221371/article/details/125275436

版权

sql 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据库

6 篇文章 0 订阅

订阅专栏

5.3 单表数据更新

请添加图片描述

-- update语句
/*语法格式：
update table_name
set column_name1 = value1,
column_name2 = calune2,
where(condition);
创建数据，实现上方图片要求*/
DROP TABLE bookcategory;	/*删除表*/
CREATE TABLE bookcategory(
	category_id INT PRIMARY KEY,
	category VARCHAR(20),
	parent_id INT
);					/*创建表*/
ALTER TABLE bookcategory
MODIFY category_id INT AUTO_INCREMENT;	/*添加自动编号*/

ALTER TABLE bookinfo 
ADD CONSTRAINT fk_bcid 
FOREIGN KEY(book_category_id)REFERENCES bookcategory(category_id);
/*bookinfo表中的book_category_id关联bookcategory表中的category_id*/
/*图书类别表*/
INSERT INTO bookcategory(category,parent_id)VALUES('计算机',0),('医学',0),('编程语言',1),('数据库',1),('儿科学',2);
DROP TABLE bookinfo;	/*删除bookinfo*/
CREATE TABLE bookinfo(	/*创建bookinfo数据表*/
	book_id INT,
	book_category_id INT,
	book_name VARCHAR(20) NOT NULL UNIQUE,
	author VARCHAR(20) NOT NULL,
	price FLOAT(5,2) NOT NULL,
	press VARCHAR(20) DEFAULT '机械工业出版社',
	pubdate DATE NOT NULL,
	store INT NOT NULL,
	CONSTRAINT fk_bcid FOREIGN KEY(book_category_id)
	REFERENCES bookcategory(category_id)
);
DESC bookinfo;	/*查看表结构*/
/*图书信息表*/
INSERT INTO bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
VALUES
(20150201,3, 'Java编程思想', '(美)埃克尔', 79.8, DEFAULT, '2007-04-01', 5),
(20150202,4, 'PHP和MySQL Web开发', 'Luke Welling等',95, DEFAULT, '2009-04-01', 2),
(20150301,3, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3),
(20160801,5, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1),
(20170401,5, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4);
/*图书信息表*/
INSERT INTO bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
VALUES
(20150201,3, 'Java编程思想', '(美)埃克尔', 79.8, DEFAULT, '2007-04-01', 5),
(20150202,4, 'PHP和MySQL Web开发', 'Luke Welling等',95, DEFAULT, '2009-04-01', 2),
(20150301,3, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3),
(20160801,5, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1),
(20170401,5, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4);