- 论坛徽章:
- 0
|
3、带子查询的更新语言
例子3:延长张三借阅的数据结构的借阅期为180天。
UPDATE temp_record
SET borrow_term=180
WHERE record_id=
(SELECT record_id FROM temp_record,browser_info,book_info
WHERE browser_info.browser_name=’张三’ AND book_name=’数据结构’
AND temp_record.book_id= book_info.book_id
AND temp_record.browser_id= browser_info.browser_id
)
注解:在mysql中update不支持where后面的select语句。
在MSsql可以执行以上语句。
4、使用连接信息更新数据
例子4:延长张三借阅的“数据结构”的借阅期为180天(例子3的另外一种写法)。
UPDATE temp_record
SET borrow_term=180
FROM temp_record,browser_info,book_info
WHERE browser_name='张三' AND book_name='数据结构'
AND temp_record.book_id= book_info.book_id
AND temp_record.browser_id= browser_info.browser_id
注解:在mysql中不支持此中写法。在MSsql可以执行以上语句。
相关数据表:
CREATE TABLE temp_record(
record_id timestamp NOT NULL,
browser_id char (12) NOT NULL ,
book_id char (12) NOT NULL ,
borrow_time datetime NOT NULL ,
return_time datetime,
borrow_ term smallint default 60,
PRIMARY KEY(record_id),
FOREIGN KEY( browser_id) REFERENCES browser_info(browser_id),
FOREIGN KEY(book_id) REFERENCES book_info(book_id)
)
-------------------
CREATE TABLE book_info (
book_id char (12) NOT NULL ,
book_name varchar (100) NOT NULL ,
type_id char( NOT NULL ,
book_status int NOT NULL,
book_isbn varchar (50) NOT NULL ,
book_author varchar (50) NOT NULL ,
book_publisher varchar (100) NOT NULL ,
book_price decimal(8, 2) NOT NULL ,
book_counts int NOT NULL ,
book_description text,
book_foreword text ,
book_catalog text,
book_cover image ,
book_publishdate datetime NOT NULL ,
book_hits int NOT NULL ,
PRIMARY KEY(book_id),
FOREIGN KEY(type_id) REFERENCES book_type(type_id)
)
---------------------------
CREATE TABLE browser_info(
browser_id char (12) NOT NULL ,
browser_name char (12) NOT NULL ,
browser_pwd char (12) NOT NULL ,
browser_popedom int NOT NULL,
PRIMARY KEY(browser_id )
) |
|