学生(学号,年龄,性别,系名) 教材(编号,书名,出版社编号,价格) 订购(学号,书号,数量) 出版社(编号,名称,地址)代码实现sql
时间: 2024-04-30 13:18:33 浏览: 104
创建表格:
```
CREATE TABLE student (
id INT PRIMARY KEY,
age INT,
gender VARCHAR(10),
department VARCHAR(20)
);
CREATE TABLE textbook (
id INT PRIMARY KEY,
name VARCHAR(50),
publisher_id INT,
price DECIMAL(10, 2)
);
CREATE TABLE orders (
student_id INT,
textbook_id INT,
quantity INT,
PRIMARY KEY (student_id, textbook_id)
);
CREATE TABLE publisher (
id INT PRIMARY KEY,
name VARCHAR(50),
address VARCHAR(100)
);
```
插入数据:
```
INSERT INTO student (id, age, gender, department)
VALUES
(1, 18, 'male', 'Computer Science'),
(2, 20, 'female', 'Mathematics'),
(3, 19, 'male', 'Physics');
INSERT INTO textbook (id, name, publisher_id, price)
VALUES
(1, 'Introduction to Algorithms', 1, 99.99),
(2, 'Linear Algebra and Its Applications', 2, 79.99),
(3, 'Classical Mechanics', 3, 69.99),
(4, 'Introduction to Probability', 1, 59.99);
INSERT INTO orders (student_id, textbook_id, quantity)
VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 1),
(3, 4, 3);
INSERT INTO publisher (id, name, address)
VALUES
(1, 'MIT Press', 'Cambridge, MA'),
(2, 'Wiley', 'Hoboken, NJ'),
(3, 'Pearson', 'London');
```
查询数据:
1. 查询所有学生的信息
```
SELECT * FROM student;
```
2. 查询所有教材的信息
```
SELECT * FROM textbook;
```
3. 查询所有出版社的信息
```
SELECT * FROM publisher;
```
4. 查询所有订单的信息
```
SELECT * FROM orders;
```
5. 查询学号为1的学生订购的所有教材及数量
```
SELECT textbook.name, orders.quantity
FROM textbook
JOIN orders ON textbook.id = orders.textbook_id
WHERE orders.student_id = 1;
```
6. 查询订购了教材编号为2的教材的学生的信息
```
SELECT student.*
FROM student
JOIN orders ON student.id = orders.student_id
WHERE orders.textbook_id = 2;
```
7. 查询出版社名称为“MIT Press”的所有教材
```
SELECT textbook.*
FROM textbook
JOIN publisher ON textbook.publisher_id = publisher.id
WHERE publisher.name = 'MIT Press';
```
相关推荐
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)