查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息create table Student(s_id varchar(100),s_name varchar(200),s_birth date,s_sex varchar(100)); create table Course(c_id varchar(100),c_name varchar(200),t_id varchar(100)); insert into Course values('01','语文','02'); insert into Course values('02','数学','01'); insert into Course values('03','英语','03'); insert into student values('01','赵雷','1990-01-01','男'); insert into student values('02','钱电','1990-12-21','男'); insert into student values('03','孙风','1990-05-20','男'); insert into student values('04','李云','1990-08-06','男'); insert into student values('05','周梅','1991-12-01','女'); insert into student values('06','吴兰','1992-03-01','女'); insert into student values('07','郑竹','1989-07-01','女'); insert into student values('08','王菊','1990-01-20','女'); create table Teacher(t_id varchar(100),t_name varchar(100)); create table Score (s_id varchar(100), c_id varchar(100),s_score int); insert into score values('01','01',80); insert into score values('01','02',90); insert into score values('01','03',99); insert into score values('02','01',70); insert into score values('02','02',60); insert into score values('02','03',80); insert into score values('03','01',80); insert into score values('03','02',80); insert into score values('03','03',80); insert into score values('04','01',50); insert into score values('04','02',30); insert into score values('04','03',20); insert into score values('05','01',76); insert into score values('05','02',87); insert into teacher values('01','张三'); insert into teacher values('02','李四'); insert into teacher values('03','王五');
时间: 2023-03-29 21:04:16 浏览: 80
查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:
SELECT s_id, s_name, s_birth, s_sex FROM Student WHERE s_id IN
(SELECT s_id FROM Score WHERE c_id='01' AND s_id NOT IN
(SELECT s_id FROM Score WHERE c_id='02'));
相关问题
create table student( s_id VARCHAR(50) PRIMARY KEY AUTO_INCREMENT, s_gender CHAR(2) UNIQUE NOT NULL, s_birthday datetime DEFAULT '1970-01-01', s_degree VARCHAR(12), s_graduate_date datetime DEFAULT '1970-01-02', s_specialty VARCHAR(50), s_phone VARCHAR(200), s_salary SMALLINT, s_allowance DECIMAL(18,2), s_skill VARCHAR(2000) )为什么会报1063 - Incorrect column specifier for column 's_id'错误
这是因为在MySQL中,VARCHAR类型不能使用AUTO_INCREMENT属性,只有整数类型才能使用AUTO_INCREMENT。因此,将s_id列的类型更改为INT或BIGINT,并将其AUTO_INCREMENT属性设置为主键即可,例如:
```
CREATE TABLE student (
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_gender CHAR(2) UNIQUE NOT NULL,
s_birthday datetime DEFAULT '1970-01-01',
s_degree VARCHAR(12),
s_graduate_date datetime DEFAULT '1970-01-02',
s_specialty VARCHAR(50),
s_phone VARCHAR(200),
s_salary SMALLINT,
s_allowance DECIMAL(18,2),
s_skill VARCHAR(2000)
);
```
这样就可以成功创建表了。
create table Course( C_ID varchar(10) primary key,-- 课程编号 C_Name varchar(20) not null unique-- 课程名称 ) comment '课程表'; create table SC( S_ID varchar(20),-- 学号 C_ID varchar(10),-- 课程编号 Score int check(Score>=0 and Score<=100)-- 成绩 ) comment '成绩表'; create table Student( S_ID varchar(20) primary key,-- 学生编号 S_Name varchar(20) not null,-- 姓名 S_Age int,-- 年龄 S_Sex enum('男','女') default '男'-- 性别 ) comment '学生表';
感谢提供数据表结构,以下是查询各个课程及相应的选修人数、课程表、学生表和成绩表的 SQL 查询语句:
查询各个课程及相应的选修人数:
```
SELECT C_Name, COUNT(*) AS Num
FROM Course
JOIN SC ON Course.C_ID = SC.C_ID
GROUP BY Course.C_ID;
```
查询课程表:
```
SELECT *
FROM Course;
```
查询学生表:
```
SELECT *
FROM Student;
```
查询成绩表:
```
SELECT *
FROM SC;
```
希望以上查询语句能够对您有所帮助。需要注意的是,以上查询语句仅供参考,具体实现需要根据实际情况进行调整和修改。
相关推荐
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.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)
![](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)