数据库LAB2

news/2025/3/18 14:33:49/

在这里插入图片描述
在这里插入图片描述

show databases;
use dreamhome2;
show tables;create database test;
create database test2;-- 这个是副本 use test;CREATE TABLE IF NOT EXISTS Branch (branchNo VARCHAR(4) NOT NULL,street VARCHAR(40) NOT NULL,city VARCHAR(20) NOT NULL,postcode VARCHAR(10) NOT NULL,PRIMARY KEY (branchNo)
);-- drop table branch;INSERT INTO Branch (branchNo, street, city, postcode)
VALUES ("B005", "22 Deer Rd", "London", "SW1 4EH"),("B007", "16 Argyll St", "Aberdeen", "AB2 3SU"),("B003", "163 Main St", "Glasgow", "G11 9QX"),("B004", "32 Manse Rd", "Bristol", "BS99 1NZ"),("B002", "56 Clover Dr", "London", "NW10 6EU");CREATE TABLE IF NOT EXISTS PrivateOwner (ownerNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,address VARCHAR(40) NOT NULL,telNo VARCHAR(20) NOT NULL,eMail VARCHAR(40) NOT NULL,password VARCHAR(20) NOT NULL,PRIMARY KEY (ownerNo)
);INSERT INTO PrivateOwner ()
VALUES ("CO46", "Joe", "Keogh", "2 Fergus Dr, Aberdeen AB2 7SX", "01224-861212", "jkeogh@lhh.com", "12345678" ),("CO87", "Carol", "Farrel", "6 Achray St, Glasgow G32 9DX", "0141-357-7419", "cfarrel@gmail.com", "12345678" ),("CO40", "Tina", "Murphy", "63 Well St, Glasgow G42", "0141-943-1728", "tinam@hotmail.com", "12345678" ),("CO93", "Tony", "Shaw", "12 Park Pl, Glasgow G4 0Qr", "0141-225-7025", "tony.shaw@ark.com", "12345678" );CREATE TABLE IF NOT EXISTS Client (clientNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,telNo VARCHAR(20) NOT NULL,prefType VARCHAR(10) NOT NULL,maxRent INT UNSIGNED NOT NULL,eMail VARCHAR(40) NOT NULL,PRIMARY KEY (clientNo)
);INSERT INTO Client (clientNo, fName, lName, telNo, prefType, maxRent, eMail)
VALUES ("CR76", "John", "Kay", "0207-774-5632", "Flat", 425, "john.kay@gmail.com" ),("CR56", "Aline", "Stewart", "0104-848-1825", "Flat", 350, "astewart@hotmail.com" ),("CR74", "Mike", "RitChie", "01475-392178", "House", 750, "mritchie01@yahoo.co.uk" ),("CR62", "Mary", "Tregear", "01224-196720", "Flat", 600, "maryt@hotmail.co.uk" );select * from branch;
select * from privateowner;
select * from client;-- 先创建三个主关系 ,后再创建staff？因为其外键是branch的主键CREATE TABLE IF NOT EXISTS Staff (staffNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,position VARCHAR(20) NOT NULL, sex VARCHAR(1) NOT NULL, DOB DATE NOT NULL,salary INT UNSIGNED,branchNo VARCHAR(4) NOT NULL,PRIMARY KEY (staffNo),FOREIGN KEY(branchNo)REFERENCES Branch(branchNo)ON UPDATE CASCADEON DELETE CASCADE
);INSERT INTO Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
VALUES ("SL21", "John", "White", "Manager", "M", '1945-10-01', 30000, "B005"),("SG37", "Ann", "Beech", "Assistant", "F", '1960-11-10', 12000, "B003"),("SG14", "David", "Ford", "Supervisor", "M", '1958-03-24', 18000, "B003"),("SA9", "Mary", "Howe", "Assistant", "F", '1970-02-19', 9000, "B007"),("SG5", "Susan", "Brand", "Manager", "F", '1940-06-03', 24000, "B003"),("SL41", "Julie", "Lee", "Assistant", "F", '1965-06-13', 9000, "B005");select * from Staff;
select * from branch;
-- 不确定！！！！！！！！！！！！！！！！！！！！！！！！！ 
CREATE TABLE IF NOT EXISTS PropertyForRent (propertyNo VARCHAR(4) NOT NULL,street VARCHAR(40) NOT NULL,city VARCHAR(20) NOT NULL,postcode VARCHAR(10) NOT NULL,type VARCHAR(10) NOT NULL,rooms TINYINT UNSIGNED NOT NULL,rent INT UNSIGNED NOT NULL,ownerNo VARCHAR(4) NOT NULL,staffNo VARCHAR(4),branchNo VARCHAR(4) NOT NULL,PRIMARY KEY (propertyNo),FOREIGN KEY(ownerNo) REFERENCES privateowner(ownerNo) ON UPDATE CASCADE ON DELETE CASCADE,foreign key(staffNo) references staff(staffNo) on update cascade on delete cascade,foreign key(branchNo) references branch(branchNo) on update cascade on delete cascade
);INSERT INTO PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
VALUES ("PA14", "16 Holhead", "Aberdeen", "AB7 5SU", "House", 6, 650, "CO46", "SA9", "B007"),("PL94", "6 Argyll St", "London", "NW2", "Flat", 4, 400, "CO87", "SL41", "B005"),("PG4", "6 Lawrence St", "Glasgow", "G11 9QX", "Flat", 3, 350, "CO40", NULL, "B003"),("PG36", "2 Manor Rd", "Glasgow", "G32 4QX", "Flat", 3, 375, "CO93", "SG37", "B003"),("PG21", "18 Dale Rd", "Glasgow", "G12", "House", 5, 600, "CO87", "SG37", "B003"),("PG16", "5 Navor Dr", "Glasgow", "G12 9AX", "Flat", 4, 450, "CO93", "SG14", "B003");select * from propertyforrent;-- drop table Viewing;CREATE TABLE IF NOT EXISTS Viewing (clientNo VARCHAR(4) NOT NULL,propertyNo VARCHAR(4) NOT NULL,viewDate DATE NOT NULL,comment VARCHAR(40),primary key(clientNo,propertyNo,viewDate),foreign key(propertyNo) references propertyforrent(propertyNo) on update cascade on delete no action,foreign key(clientNo) references Client(ClientNo) on update cascade on delete no action
);INSERT INTO Viewing (clientNo, propertyNo, viewDate, comment)
VALUES ("CR56", "PA14", '2013-03-24', "too small" ),("CR76", "PG4", '2013-04-20', "too remote" ),("CR56", "PG4", '2013-05-26', NULL ),("CR62", "PA14", '2013-05-14', "no dining room" ),("CR56", "PG36", '2013-04-28', NULL );select * from Viewing;-- drop table Registration; CREATE TABLE IF NOT EXISTS Registration (clientNo VARCHAR(4) NOT NULL,branchNo VARCHAR(4) NOT NULL,staffNo VARCHAR(4) NOT NULL,dateJoined DATE NOT NULL,primary key(clientNo,branchNo,staffNo),foreign key(clientNo) references client(clientNo) on update cascade on delete no action,foreign key(branchNo) references branch(branchNo) on update cascade on delete no action,foreign key(staffNo) references staff(staffNo) on update cascade on delete no action
);INSERT INTO Registration (clientNo, branchNo, staffNo, dateJoined)
VALUES ("CR76", "B005", "SL41", '2013-01-02' ),("CR56", "B003", "SG37", '2012-04-11' ),("CR74", "B003", "SG37", '2011-11-16' ),("CR62", "B007", "SA9", '2012-05-07' );select * from registration;
-- 第三题的（a）和（b） 
select * from branch;
select * from staff;
delete from branch where branchNo='B003';
update branch set branchNo='B003' where branchNo='B010';-- 第二问 
INSERT INTO branch(branchNo, street, city, postcode)
VALUES(NULL,"22 Deer Rd", "London", "SW1 4EH");INSERT INTO staff(staffNo, fName, lName, position, sex, DOB, salary, branchNo)
VALUES("Li1", "John", "White", "Manager", "M", '1945-10-01', 30000, "B001");
--- 第四问 
alter table staff drop sex;
alter table staff add sex VARCHAR(20) NOT NULL;
-- 第五问(a)
SELECT  * FROM staff;
UPDATE staff SET salary = salary * 0.909090 where position = 'Manager';-- !!!!SET SQL_SAFE_UPDATES = 0;-- 第五问 (b)use test2;CREATE TABLE IF NOT EXISTS Branch (branchNo VARCHAR(4) NOT NULL,street VARCHAR(40) NOT NULL,city VARCHAR(20) NOT NULL,postcode VARCHAR(10) NOT NULL,PRIMARY KEY (branchNo)
);-- drop table branch;INSERT INTO Branch (branchNo, street, city, postcode)
VALUES ("B005", "22 Deer Rd", "London", "SW1 4EH"),("B007", "16 Argyll St", "Aberdeen", "AB2 3SU"),("B003", "163 Main St", "Glasgow", "G11 9QX"),("B004", "32 Manse Rd", "Bristol", "BS99 1NZ"),("B002", "56 Clover Dr", "London", "NW10 6EU");CREATE TABLE IF NOT EXISTS PrivateOwner (ownerNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,address VARCHAR(40) NOT NULL,telNo VARCHAR(20) NOT NULL,eMail VARCHAR(40) NOT NULL,password VARCHAR(20) NOT NULL,PRIMARY KEY (ownerNo)
);INSERT INTO PrivateOwner ()
VALUES ("CO46", "Joe", "Keogh", "2 Fergus Dr, Aberdeen AB2 7SX", "01224-861212", "jkeogh@lhh.com", "12345678" ),("CO87", "Carol", "Farrel", "6 Achray St, Glasgow G32 9DX", "0141-357-7419", "cfarrel@gmail.com", "12345678" ),("CO40", "Tina", "Murphy", "63 Well St, Glasgow G42", "0141-943-1728", "tinam@hotmail.com", "12345678" ),("CO93", "Tony", "Shaw", "12 Park Pl, Glasgow G4 0Qr", "0141-225-7025", "tony.shaw@ark.com", "12345678" );CREATE TABLE IF NOT EXISTS Client (clientNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,telNo VARCHAR(20) NOT NULL,prefType VARCHAR(10) NOT NULL,maxRent INT UNSIGNED NOT NULL,eMail VARCHAR(40) NOT NULL,PRIMARY KEY (clientNo)
);INSERT INTO Client (clientNo, fName, lName, telNo, prefType, maxRent, eMail)
VALUES ("CR76", "John", "Kay", "0207-774-5632", "Flat", 425, "john.kay@gmail.com" ),("CR56", "Aline", "Stewart", "0104-848-1825", "Flat", 350, "astewart@hotmail.com" ),("CR74", "Mike", "RitChie", "01475-392178", "House", 750, "mritchie01@yahoo.co.uk" ),("CR62", "Mary", "Tregear", "01224-196720", "Flat", 600, "maryt@hotmail.co.uk" );select * from branch;
select * from privateowner;
select * from client;-- 先创建三个主关系 ,后再创建staff？因为其外键是branch的主键CREATE TABLE IF NOT EXISTS Staff (staffNo VARCHAR(4) NOT NULL,fName VARCHAR(20) NOT NULL,lName VARCHAR(20) NOT NULL,position VARCHAR(20) NOT NULL, sex VARCHAR(1) NOT NULL, DOB DATE NOT NULL,salary INT UNSIGNED,branchNo VARCHAR(4) NOT NULL,PRIMARY KEY (staffNo),FOREIGN KEY(branchNo)REFERENCES Branch(branchNo)ON UPDATE CASCADEON DELETE CASCADE
);INSERT INTO Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
VALUES ("SL21", "John", "White", "Manager", "M", '1945-10-01', 30000, "B005"),("SG37", "Ann", "Beech", "Assistant", "F", '1960-11-10', 12000, "B003"),("SG14", "David", "Ford", "Supervisor", "M", '1958-03-24', 18000, "B003"),("SA9", "Mary", "Howe", "Assistant", "F", '1970-02-19', 9000, "B007"),("SG5", "Susan", "Brand", "Manager", "F", '1940-06-03', 24000, "B003"),("SL41", "Julie", "Lee", "Assistant", "F", '1965-06-13', 9000, "B005");select * from Staff;
select * from branch;
-- 不确定！！！！！！！！！！！！！！！！！！！！！！！！！ 
CREATE TABLE IF NOT EXISTS PropertyForRent (propertyNo VARCHAR(4) NOT NULL,street VARCHAR(40) NOT NULL,city VARCHAR(20) NOT NULL,postcode VARCHAR(10) NOT NULL,type VARCHAR(10) NOT NULL,rooms TINYINT UNSIGNED NOT NULL,rent INT UNSIGNED NOT NULL,ownerNo VARCHAR(4) NOT NULL,staffNo VARCHAR(4),branchNo VARCHAR(4) NOT NULL,PRIMARY KEY (propertyNo),FOREIGN KEY(ownerNo) REFERENCES privateowner(ownerNo) ON UPDATE CASCADE ON DELETE CASCADE,foreign key(staffNo) references staff(staffNo) on update cascade on delete cascade,foreign key(branchNo) references branch(branchNo) on update cascade on delete cascade
);INSERT INTO PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
VALUES ("PA14", "16 Holhead", "Aberdeen", "AB7 5SU", "House", 6, 650, "CO46", "SA9", "B007"),("PL94", "6 Argyll St", "London", "NW2", "Flat", 4, 400, "CO87", "SL41", "B005"),("PG4", "6 Lawrence St", "Glasgow", "G11 9QX", "Flat", 3, 350, "CO40", NULL, "B003"),("PG36", "2 Manor Rd", "Glasgow", "G32 4QX", "Flat", 3, 375, "CO93", "SG37", "B003"),("PG21", "18 Dale Rd", "Glasgow", "G12", "House", 5, 600, "CO87", "SG37", "B003"),("PG16", "5 Navor Dr", "Glasgow", "G12 9AX", "Flat", 4, 450, "CO93", "SG14", "B003");select * from propertyforrent;-- drop table Viewing;CREATE TABLE IF NOT EXISTS Viewing (clientNo VARCHAR(4) NOT NULL,propertyNo VARCHAR(4) NOT NULL,viewDate DATE NOT NULL,comment VARCHAR(40),primary key(clientNo,propertyNo,viewDate),foreign key(propertyNo) references propertyforrent(propertyNo) on update cascade on delete no action,foreign key(clientNo) references Client(ClientNo) on update cascade on delete no action
);INSERT INTO Viewing (clientNo, propertyNo, viewDate, comment)
VALUES ("CR56", "PA14", '2013-03-24', "too small" ),("CR76", "PG4", '2013-04-20', "too remote" ),("CR56", "PG4", '2013-05-26', NULL ),("CR62", "PA14", '2013-05-14', "no dining room" ),("CR56", "PG36", '2013-04-28', NULL );select * from Viewing;-- drop table Registration; CREATE TABLE IF NOT EXISTS Registration (clientNo VARCHAR(4) NOT NULL,branchNo VARCHAR(4) NOT NULL,staffNo VARCHAR(4) NOT NULL,dateJoined DATE NOT NULL,primary key(clientNo,branchNo,staffNo),foreign key(clientNo) references client(clientNo) on update cascade on delete no action,foreign key(branchNo) references branch(branchNo) on update cascade on delete no action,foreign key(staffNo) references staff(staffNo) on update cascade on delete no action
);INSERT INTO Registration (clientNo, branchNo, staffNo, dateJoined)
VALUES ("CR76", "B005", "SL41", '2013-01-02' ),("CR56", "B003", "SG37", '2012-04-11' ),("CR74", "B003", "SG37", '2011-11-16' ),("CR62", "B007", "SA9", '2012-05-07' );select * from staff;update staff set position='Supervisor', salary=1.05*salary where fName='Julie' and lName='Lee';select * from propertyforrent;
update propertyforrent set rent=rent*1.02 where city='Glasgow';

http://www.ppmy.cn/news/488361.html

相关文章

使用dbvisualizer等工具连接DB2报错 -4222, 必须属性是未知主机

使用dbvisualizer等工具连接DB2报错 -4222, 必须属性是未知主机

问题描述： 使用工具，比如dbvisualizer、aqua data studio等，连接DB2数据库时报错如下： An error occurred whild establishing the connection: Type: com.ibm.db2.jcc.b.qm Error Code: -4222 SQL State: 08001 Message: [jc…
阅读更多...
hp服务器装无线网卡,已解决: 更换无线网卡 - 惠普支持社区 - 843969

hp服务器装无线网卡,已解决: 更换无线网卡 - 惠普支持社区 - 843969

楼主你好！ 已核实，理论上支持7260无线网卡，但是需要看机器本身的无线网卡是否是双天线的，如果不是双天线的话，需要改成双天线的配置，才能支持 支持的无线网卡信息如下 Intel Dual Band Wireless-AC 3160 80…
阅读更多...
G42 Healthcare帮助车手恢复正常生活

G42 Healthcare帮助车手恢复正常生活

阿联酋阿布扎比--(美国商业资讯)--G42 Healthcare在阿联酋开展的#4Humanity试验成功之后，2020年环法自行车赛冠军车队阿联酋队的车手和工作人员接种了阿联酋卫生与预防部核准的COVID-19疫苗，该疫苗由中国医药集团子公司中国生物技术集团公司(CNBG)开发。…
阅读更多...
Kubernetes K8S之affinity亲和性与反亲和性详解与示例

Kubernetes K8S之affinity亲和性与反亲和性详解与示例

Kubernetes K8S之Node节点亲和性与反亲和性以及Pod亲和性与反亲和性详解与示例 主机配置规划 服务器名称(hostname)系统版本配置内网IP外网IP(模拟)k8s-masterCentOS7.72C/4G/20G172.16.1.11010.0.0.110k8s-node01CentOS7.72C/4G/20G172.16.1.11110.0.0.111k8s-node02CentOS7.…
阅读更多...
k8s_难产的ingress架构初体验（二）

k8s_难产的ingress架构初体验（二）

之前曲折的铺路 https://blog.csdn.net/cds992/article/details/106170316 https://blog.csdn.net/cds992/article/details/106246616 环境说明 腾讯云环境, 新搭的环境。 [roottest service]# kubectl get svc -n ingress-nginx NAME TYPE…
阅读更多...
高德一直显示服务器繁忙,全国春运TOP10繁忙服务区 无锡京沪高速梅村服务区排第二...

高德一直显示服务器繁忙,全国春运TOP10繁忙服务区 无锡京沪高速梅村服务区排第二...

我苏网讯今天(2月7日)，高德地图携手中国气象局公共气象服务中心、全国80多家交通管理部门联合发布了《2018春运安全出行指南》。 报告显示，2018年春运期间全国高速交通将迎来两个高峰期，分别是腊月二十五返乡高峰和正月初六返程高峰。其中长三角、珠三角区域高速公路压力最大…
阅读更多...
数控技术复习(二):数控编程必备的基础知识

数控技术复习(二):数控编程必备的基础知识

文章首发于个人博客，欢迎访问：数控技术复习(二):数控编程必备的基础知识 数控机床加工零件：零件图代码->程序单->控制介质->数控装置->伺服电机->机床自动加工。从零件图纸到编制零件加工程序和制作控制介质的全过程称为数控…
阅读更多...
strlen和sizeof有什么区别?http://zhidao.baidu.com/link?url=G42pKaVdJNJXS5pTgv0tzrKUr6uQ4qsEI-yFy3pvIa0g6NNd

strlen和sizeof有什么区别?http://zhidao.baidu.com/link?url=G42pKaVdJNJXS5pTgv0tzrKUr6uQ4qsEI-yFy3pvIa0g6NNd

http://zhidao.baidu.com/link?urlG42pKaVdJNJXS5pTgv0tzrKUr6uQ4qsEI-yFy3pvIa0g6NNdShDWXwFwmdna7OEXg3enNR_3l1vvIN1LMgiiQa strlen和sizeof有什么区别? 分享| 2006-08-31 17:43 abelking | 浏览 329033 次 举报| 2006-08-31 18:38 #行家成长训练营，闯关拿奖欢…
阅读更多...
最新文章

Copyright @ 2022~2023