题面
- customer(uID,uName)
- hotel(hotel_id,hotel_name)
- order(order_id,room_id,start_date,leave_date,amount,payment,create_date,custom_id)
- rating(rID,stars,order_id,uID,rate_date)
- room_info(info_id,date,price,remain,room_id)
- room_type(room_id,room_name,hotel_id)
12道SQL题
1.查询所有房型的具体信息，包括room_id, room_name, hotel_id。
SELECT * FROM room_type
2.查询所有酒店名称中包含“希尔顿”的酒店，返回酒店名称和酒店id。
SELECT hotel_name,hotel_id FROM hotel WHERE hotel_name LIKE '%希尔顿%'
3.查询订单总价在10000元及以上的所有订单详情，包括订单编号、酒店编号、房型编号及居住时长。
SELECT order_id,hotel_id,room_id, leave_date-start_date+1 AS time
FROM hotel NATURAL JOIN `order` NATURAL JOIN room_type
WHERE payment>=10000
4.查询所有房型的订单情况，包括房型编号，房型名称，订单编号、价格。
SELECT room_type.room_id,room_type.room_name,`order`.order_id,`order`.payment
FROM `order` RIGHT JOIN room_type ON `order`.room_id=room_type.room_id
5.创建启悦酒店的订单视图。
CREATE VIEW qiyue_hotel
AS
SELECT `order`.*
FROM `order` NATURAL JOIN hotel NATURAL JOIN room_type
WHERE hotel_name LIKE '%启悦%'
6.在订单表的总价字段上 创建降序的普通索引。索引名为orderpayment。
CREATE UNIQUE INDEX orderpayment ON `order`(payment DESC)
7.查询所有酒店2020-11-14所有房型的平均价格并从低到高排序。
SELECT hotel_id,hotel_name,AVG(price) AS average_price
FROM hotel NATURAL JOIN room_type NATURAL JOIN room_info
WHERE date='2020-11-14' GROUP BY hotel_id ORDER BY average_price
8.从订单表中统计一个酒店在指定日期的各种房型的预订情况。例如统计希尔顿大酒店2020-11-14当天各个房型预定情况，返回酒店名，房型，预定数量。
SELECT hotel_name,room_name,amount
FROM hotel NATURAL JOIN room_type NATURAL JOIN `order`
WHERE hotel_name LIKE '希尔顿大酒店' AND start_date<='2020-11-14' AND start_date>='2020-11-14'
9.查找同时评价了2次及以上的用户信息。
SELECT uID,uName
FROM customer
WHERE uID NOT IN (SELECT uID FROM rating GROUP BY uID HAVING COUNT(uID)=1)
10.查询评价过所有总统套房的顾客姓名。
SELECT DISTINCT uName
FROM customer,rating,`order`,room_type
WHERE customer.uID = rating.uID AND rating.order_id=order.order_id
AND room_type.room_id=order.room_id AND room_name="总统套房"
GROUP BY rating.uID
HAVING COUNT(DISTINCT room_type.room_id)>=(SELECT COUNT(*) FROM room_type
WHERE room_name="总统套房")
11.指定时间区间和每天要预定的房间数量，查询满足条件（时间区间，将预定房间数）的房型及其平均价格，并按平均价格从低到高进行排序。查询结果应包含酒店，房型及平均价格信息。例如预定11.14-16日每天房间数量4间。
SELECT hotel_name,room_name,AVG(price) AS average_price
FROM hotel NATURAL JOIN room_type NATURAL JOIN room_info
WHERE date<='2020-11-16' AND date>='2020-11-14'
GROUP BY room_id HAVING min(remain)>=4 ORDER BY average_price
12.完成预订房间，包括更新房型信息和创建订单。例如订单为预订11月14号-15号 4号房型 4间。
INSERT INTO `order`
VALUES (null,4,'2020-11-14','2020-11-15',4,
4*(SELECT price FROM room_info WHERE room_id=4 AND date='2020-11-14'),'2020-12-07',201901);
UPDATE room_info SET remain=remain-4
WHERE room_id=4 AND date BETWEEN '2020-11-14' AND '2020-11-15';
附加题
附加题的前2题，我选择在Navicat上操作。
请用存储过程完成第8题，第11题。
操作方法：使用Navicat 15建立并调用MySQL存储过程
第8题：
第11题：
请用触发器完成第12题。
操作方法：使用Navicat 15建立触发器
先用存储过程向order表插入新数据，再用order表上的触发器修改room_info表上的相关数据。直接插入也可以。
存储过程：
触发器：
请设计应用程序（java、python等，编程语言不限）实现11题第12题进行房型查找及房间预订。
我选择Java的JDBC。
第11题代码：
import sun.applet.Main;
import java.io.IOException;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.Scanner;
import java.util.concurrent.ScheduledExecutorService;
/**
* @BelongsProject:SQLexperiment
* @BelongsPackage:PACKAGE_NAME
* @Author:Uestc_Xiye
* @CreateTime:2020-12-07 19:56:16
*/
public class SearchRoom {
public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://localhost:3306/dbexperiment?&useSSL=false&serverTimezone=UTC";
public static final String DB_USER = "root";
public static final String DB_PASSWORD = "12138";
public static void main(String[] args) throws Exception {
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException cne) {
cne.printStackTrace();
}
Scanner scanner=new Scanner(System.in);
String BeginDate=scanner.next();
String EndDate=scanner.next();
int RoomNum=scanner.nextInt();
/*
String BeginDate="2020-11-14";
String EndDate="2020-11-16";
int RoomNum=4;
*/
try {
Connection conn=DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
/*
String procedureSQL="CREATE PROCEDURE `SearchRoom`(IN `BeginDate` date,IN `EndDate` date,IN `RoomNum` int)\n" +
"BEGIN" +
"\tSELECT hotel_name AS HotelName,room_name AS RoomName,AVG(price) AS AveragePrice\n" +
"\tFROM hotel NATURAL JOIN room_type NATURAL JOIN room_info\n" +
"\tWHERE date<=EndDate AND date>=BeginDate GROUP BY room_id HAVING min(remain)>=RoomNum ORDER BY AVG(price);\n" +
"END";
PreparedStatement ps=conn.prepareStatement(procedureSQL);
ps.execute();
*/
CallableStatement cs=conn.prepareCall("{CALL SearchRoom(?,?,?)}");
cs.setString(1,BeginDate);
cs.setString(2,EndDate);
cs.setInt(3,RoomNum);
boolean hasResult=cs.execute();
if(hasResult)
{
ResultSet rst=cs.getResultSet();
while (rst.next()) {
System.out.println(
rst.getString(1) + "\t"
+ rst.getString(2) + "\t"
+ rst.getString(3)
);
}
}
//ps.close();
cs.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
第一次运行，先不注释掉创建存储过程的代码，等存储过程创建好后，注释掉，不然会报错。就可以输入数据并得到结果了。
第12题代码：
import org.omg.Messaging.SyncScopeHelper;
import java.sql.*;
import java.util.Scanner;
/**
* @BelongsProject:SQLexperiment
* @BelongsPackage:PACKAGE_NAME
* @Author:Uestc_Xiye
* @CreateTime:2020-12-08 12:37:11
*/
public class BookingHotel {
public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://localhost:3306/dbexperiment?&useSSL=false&serverTimezone=UTC";
public static final String DB_USER = "root";
public static final String DB_PASSWORD = "12138";
public static void main(String[] args) throws Exception {
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException cne) {
cne.printStackTrace();
}
Scanner scanner=new Scanner(System.in);
String StartDate=scanner.next();
String EndDate=scanner.next();
int RoomStyle=scanner.nextInt();
int RoomNum=scanner.nextInt();
String CreateDate=scanner.next();
int CustomID=scanner.nextInt();
/*
String StartDate="2020-11-14";
String EndDate="2020-11-15";
int RoomStyle=4;
int RoomNum=4;
String CreateDate="2020-12-08";
int CustomID=201901;
*/
try {
Connection conn= DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
PreparedStatement pst = conn.prepareStatement("INSERT INTO `order` VALUES (null,?,?,?,?,?*(SELECT SUM(price) FROM room_info WHERE room_id=? AND date BETWEEN ? AND ?),?,?);");
pst.setInt(1,RoomStyle);
pst.setString(2,StartDate);
pst.setString(3,EndDate);
pst.setInt(4,RoomNum);
pst.setInt(5,RoomNum);
pst.setInt(6,RoomStyle);
pst.setString(7,StartDate);
pst.setString(8,EndDate);
pst.setString(9,CreateDate);
pst.setInt(10,CustomID);
pst.execute();
PreparedStatement p = conn.prepareStatement("UPDATE room_info SET remain=remain-? WHERE room_id=? AND date BETWEEN ? AND ?;");
p.setInt(1,RoomNum);
p.setInt(2,RoomStyle);
p.setString(3,StartDate);
p.setString(4,EndDate);
p.execute();
pst.close();
p.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
请编写GUI实现附加题第3题。
我选择Java的JFrame。
第11题代码：
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
/**
* @BelongsProject:SQLexperiment
* @BelongsPackage:PACKAGE_NAME
* @Author:Uestc_Xiye
* @CreateTime:2020-12-08 17:03:54
*/
public class SearchRoomGUI {
public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://localhost:3306/dbexperiment?&useSSL=false&serverTimezone=UTC";
public static final String DB_USER = "root";
public static final String DB_PASSWORD = "12138";
public static void main(String[] args) {
JFrame f=new JFrame("SearchRoomGUI");
f.setSize(400,250);
JPanel pInput = new JPanel();
pInput.setBounds(10, 10, 375, 120);
pInput.setLayout(new GridLayout(3,2,10, 10));
JLabel BeginDate = new JLabel("BeginDate:");
JTextField BeginDateText = new JTextField();
JLabel EndDate = new JLabel("EndDate:");
JTextField EndDateText = new JTextField();
JLabel RoomNum = new JLabel("RoomNum");
JTextField RoomNumText = new JTextField();
JButton jButton = new JButton("Search");
pInput.add(BeginDate);
pInput.add(BeginDateText);
pInput.add(EndDate);
pInput.add(EndDateText);
pInput.add(RoomNum);
pInput.add(RoomNumText);
JTextArea jTextArea = new JTextArea();
jTextArea.setLineWrap(true);
jButton.setBounds(150, 120 + 30, 80, 30);
jTextArea.setBounds(10, 150 + 60, 375, 120);
f.add(pInput);
f.add(jButton);
f.add(jTextArea);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setLocationRelativeTo(null);
f.setVisible(true);
jButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String BeginDate=BeginDateText.getText();
String EndDate=EndDateText.getText();
int RoomNum=Integer.parseInt(RoomNumText.getText());
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException cne) {
cne.printStackTrace();
}
try {
Connection conn= DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
String sql = "\tSELECT hotel_name,room_name,AVG(price)\n" +
"\tFROM hotel NATURAL JOIN room_type NATURAL JOIN room_info\n" +
"\tWHERE date BETWEEN ? AND ? GROUP BY room_id HAVING min(remain)>=? ORDER BY AVG(price);";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,BeginDate);
pst.setString(2,EndDate);
pst.setInt(3,RoomNum);
pst.execute();
ResultSet rst = pst.getResultSet();
String result = "";
while (rst.next()) {
/*
System.out.println(
rst.getString(1) + "\t"
+ rst.getString(2) + "\t"
+ rst.getString(3)
);
*/
result += rst.getString(1) + "\t"
+ rst.getString(2) + "\t"
+ rst.getString(3) + "\n";
}
jTextArea.setText("");
jTextArea.append(result);
pst.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
});
}
}
界面如下所示：
第12题代码：
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
/**
* @BelongsProject:SQLexperiment
* @BelongsPackage:PACKAGE_NAME
* @Author:Uestc_Xiye
* @CreateTime:2020-12-08 19:33:36
*/
public class BookingHotelGUI {
public static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
public static final String DB_URL = "jdbc:mysql://localhost:3306/dbexperiment?&useSSL=false&serverTimezone=UTC";
public static final String DB_USER = "root";
public static final String DB_PASSWORD = "12138";
public static void main(String[] args) {
JFrame f=new JFrame("BookingHotelGUI");
f.setSize(400,380);
JPanel pInput = new JPanel();
pInput.setBounds(10, 10, 375, 240);
pInput.setLayout(new GridLayout(6,3,10, 10));
JLabel BeginDate = new JLabel("BeginDate:");
JTextField BeginDateText = new JTextField();
JLabel EndDate = new JLabel("EndDate:");
JTextField EndDateText = new JTextField();
JLabel RoomStyle = new JLabel("RoomStyle:");
JTextField RoomStyleText = new JTextField();
JLabel RoomNum = new JLabel("RoomNum:");
JTextField RoomNumText = new JTextField();
JLabel CreateDate = new JLabel("CreateDate:");
JTextField CreateDateText = new JTextField();
JLabel CustomID = new JLabel("CustomID:");
JTextField CustomIDText = new JTextField();
JButton jButton = new JButton("Submit");
pInput.add(BeginDate);
pInput.add(BeginDateText);
pInput.add(EndDate);
pInput.add(EndDateText);
pInput.add(RoomStyle);
pInput.add(RoomStyleText);
pInput.add(RoomNum);
pInput.add(RoomNumText);
pInput.add(CreateDate);
pInput.add(CreateDateText);
pInput.add(CustomID);
pInput.add(CustomIDText);
JTextArea jTextArea = new JTextArea();
jTextArea.setLineWrap(true);
jButton.setBounds(150, 240 + 30, 80, 30);
jTextArea.setBounds(10, 150 + 60, 375, 120);
f.add(pInput);
f.add(jButton);
f.add(jTextArea);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f.setLocationRelativeTo(null);
f.setVisible(true);
jButton.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String BeginDate=BeginDateText.getText();
String EndDate=EndDateText.getText();
int RoomStyle=Integer.parseInt(RoomStyleText.getText());
int RoomNum=Integer.parseInt(RoomNumText.getText());
String CreateDate=CreateDateText.getText();
int CustomID=Integer.parseInt(CustomIDText.getText());
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException cne) {
cne.printStackTrace();
}
try {
Connection conn= DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
String InsertSQL = "INSERT INTO `order` VALUES (null,?,?,?,?,?*(SELECT SUM(price) FROM room_info WHERE room_id=? AND date BETWEEN ? AND ?),?,?);";
PreparedStatement pst = conn.prepareStatement(InsertSQL);
pst.setInt(1,RoomStyle);
pst.setString(2,BeginDate);
pst.setString(3,EndDate);
pst.setInt(4,RoomNum);
pst.setInt(5,RoomNum);
pst.setInt(6,RoomStyle);
pst.setString(7,BeginDate);
pst.setString(8,EndDate);
pst.setString(9,CreateDate);
pst.setInt(10,CustomID);
pst.execute();
String UpdateSQL = "UPDATE room_info SET remain=remain-? WHERE room_id=? AND date BETWEEN ? AND ?;";
PreparedStatement p = conn.prepareStatement(UpdateSQL);
p.setInt(1,RoomNum);
p.setInt(2,RoomStyle);
p.setString(3,BeginDate);
p.setString(4,EndDate);
p.execute();
pst.close();
p.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
});
}
}
界面如下所示：
代码地址
Github：Uestc-xiye/Database-And-Application
码云：Database-And-Application
CSDN：《数据库及其应用》实验
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
