oracle笔记_create view schoolboy(sno,sname,sage) as select sn-CSDN博客

本文链接：https://blog.csdn.net/weixin_43025217/article/details/122953361

# Oracle笔记

## 一、安装数据库

创建账号：

-- 创建用户
CREATE USER zw IDENTIFIED BY 123;

--用户授权
GRANT CREATE SESSION,CREATE TABLESPACE,DBA TO zw;
```

数据库本身是不区分大小写，所以可以忽略

建议：关键字大写，自己取名的小写

## 二、数据定义语言DDL

负责定义，负责创建或者修改数据库中的对象（表、视图、索引等）

关键字：

- CREATE
- ALTER
- DROP

### 2.1 create创建表

基本语法：

``` sql
CREATE TABLE 表名
(
字段名1 数据类型 [属性],
字段名1 数据类型,
字段名1 数据类型,
字段名1 数据类型
)

```

常见的数据类型：

| **类型** | **最大长度** | **描述** |
| ----------- | ------------------------------ | ---------------------- |
| CHAR | 固定长度字符串 | 最大长度2000bytes |
| VARCHAR2 | 可变长度的字符串, | 最大长度4000bytes |
| NCHAR | 根据字符集而定的固定长度字符串 | 最大长度2000bytes |
| NVARCHAR2 | 根据字符集而定的可变长度字符串 | 最大长度4000bytes |
| DATE | 日期（日-月-年） | DD-MM-YY(HH-MI-SS) |
| TIMESTAMP | 日期（日-月-年） | DD-MM-YY(HH-MI-SS:FF3) |
| NUMBER(P,S) | 数字类型 | P为整数位，S为小数位 |
| BLOB | 二进制数据 | 最大长度4G |
| CLOB | 字符数据 | 最大长度4G |
| RAW | 固定长度的二进制数据 | 最大长度2000bytes |
| LONG RAW | 可变长度的二进制数据 | 最大长度2G |

## 三、数据操作语言DML

负责操作**具体的数据**，而这个数据存在与表中

关键字：

- INSERT
- UPDATE
- DELETE

## 四、数据查询语言DQL

数据查询语言，负责查询具体的数据

关键字：

- SELECT :最重要，出现频率最高的语句。
- select * from xxx;

## 五、数据控制语言DCL(了解)

负责控制事务、权限等等

关键字：

- COMMIT
- ROLEBACK
- GRANT

-- primary key:主键
-- 1）主键不能为空，必须要存在数据库
-- 2）主键不能出现重复值，只能是唯一的。主键其实就是一个特殊唯一索引

create table dept(
deptno varchar2(20) primary key,
dname varchar2(64),
loc varchar(128)
)

-- alter修改表结构:添加一个字段
alter table dept add(phone varchar2(11));

-- 删除表结构 drop
drop table dept;

select * from dept;

-- 添加测试数据：DML的语法

-- DML数据操作语言
-- INSERT 添加数据

-- 1）给指定的列添加数据非空并且没有默认值字段，必须要指定
-- 指定字段：字段的顺序没有要求的
INSERT INTO dept(deptno,dname,loc) VALUES('1','研发部','深圳');
INSERT INTO dept(dname,deptno,loc) VALUES('财务部','2','深圳');

select * from dept;

--2）添加所有的列的数据，忽略字段名。但是数据值顺序就必须是创建表时候字段的顺序
INSERT INTO dept VALUES('3','后勤部','深圳','18812345677');

-- update修改数据，如果不给条件，修改的就是整个表中的数据

-- 语法：UPDATE 表 SET 字段名=新值 [,字段名2=新值2,...] [WHERE 条件]

-- 推荐:所有修改或者删除的时候，可以先查询一下，看条件是否正确

UPDATE dept SET loc='深圳华美居'

select * from dept WHERE dname ='研发部'

UPDATE dept SET loc='深圳华美居' WHERE dname ='研发部'

-- 修改多个字段

UPDATE dept SET loc='深圳宝安华美居D区',phone='13812345678' WHERE dname ='研发部';
commit;

-- 删除
-- DELETE :常用
-- TRUNCATE :截断表，删除快，但是删了就不能反悔；不能给条件，只能删除整个表

TRUNCATE table dept where dname ='后勤部';

select * from dept;

update dept set dname='' where deptno=1

alter table dept add (created date);

insert into dept values('5','研发部','华美居','18812345666',to_date('2022-2-14 15:37:31','yyyy-MM-dd HH24:mi:ss'));
insert into dept values('4','研发部','华美居','18812345666',sysdate);

CREATE TABLE STUDENT
( SNO VARCHAR2(3) PRIMARY KEY,
SNAME VARCHAR2(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATE,
CLASS VARCHAR2(5) NOT NULL
)

CREATE TABLE COURSE
( CNO VARCHAR2(5) PRIMARY KEY,
CNAME VARCHAR2(10) NOT NULL,
TNO VARCHAR2(10) NOT NULL
)

CREATE TABLE SCORE
( SNO VARCHAR2(3) NOT NULL,
CNO VARCHAR2(5) NOT NULL,
DEGREE NUMBER(10,1) NOT NULL
)

CREATE TABLE TEACHER
( TNO VARCHAR2(3) PRIMARY KEY,
TNAME VARCHAR2(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATE,
PROF VARCHAR2(6),
DEPART VARCHAR2(10)
)

INSERT INTO STUDENT VALUES('108','曾华','男',to_date('1977-09-01','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('105','匡明','男',to_date('1975-10-02','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('107','王丽','女',to_date('1976-01-23','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('101','李军','男',to_date('1976-02-20','yyyy-mm-dd'),'95033');
INSERT INTO STUDENT VALUES('109','王芳','女',to_date('1975-02-10','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('103','陆君','男',to_date('1974-06-03','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('110','王大锤','男',to_date('1974-06-03','yyyy-mm-dd'),'95031');
INSERT INTO STUDENT VALUES('112','老王','男',null,'95031');

INSERT INTO COURSE VALUES('3-105','计算机导论','825');
INSERT INTO COURSE VALUES('3-245','操作系统','804');
INSERT INTO COURSE VALUES('6-166','数据电路','856');
INSERT INTO COURSE VALUES('9-888','高等数学','831');

INSERT INTO SCORE VALUES('103','3-245','86');
INSERT INTO SCORE VALUES('105','3-245','75');
INSERT INTO SCORE VALUES('109','3-245','68');
INSERT INTO SCORE VALUES('103','3-105','92');
INSERT INTO SCORE VALUES('105','3-105','88');
INSERT INTO SCORE VALUES('109','3-105','76');
INSERT INTO SCORE VALUES('101','3-105','64');
INSERT INTO SCORE VALUES('107','3-105','91');
INSERT INTO SCORE VALUES('108','3-105','78');
INSERT INTO SCORE VALUES('101','6-166','85');
INSERT INTO SCORE VALUES('107','6-106','79');
INSERT INTO SCORE VALUES('108','6-166','81');
INSERT INTO SCORE VALUES('999','6-166','81');
INSERT INTO SCORE VALUES('999','6-166','81');

INSERT INTO TEACHER VALUES('804','李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
INSERT INTO TEACHER VALUES('856','张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
INSERT INTO TEACHER VALUES('825','王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
INSERT INTO TEACHER VALUES('831','刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');

-- 不推荐使用星号：1）可读性差 2）性能差，最终会将星号重新转为所有的字段
select * from student;

select sno,sname,ssex from student;

-- 指定查询条件
select * from student where ssex='女';

-- 查询ID 101 - 105之间的学生
select * from student where sno>=101 and sno<=105;
select * from student where sno between 101 and 105 and ssex='男';

-- 排序 order by ，默认升序（ASC），降序(DESC)
SELECT * FROM score order by degree desc;

-- 按照班级升序，按照成绩降序
SELECT * FROM score order by cno asc,degree desc;

-- 模糊查询：关键字 like
-- 配合： %:任意多个字符 _：单个字符

-- 查询所有姓王的学生

select * from student where sname like '王%'
select * from student where sname like '%王%'

select * from student where sname like '王__'

-- 查询所有生日为空的数据
-- null:不能使用 = 等号，使用 is
select * from student where sbirthday is null;
select * from student where sbirthday is not null;

-- 常用聚合函数
MAX：    最大值
MIN：   最小值
AVG：    平均值
SUM：   求和
COUNT：计数

-- as：取别名但是可以省略
-- count：()里面的值，可以是普通字段，也可以是主键，也可以是常量，例如：1
SELECT max(degree) as "最大值",min(degree) "最小值",avg(degree) "平均分",sum(degree) "总分数",count(1) "总人数" FROM score;

-- 按照班级统计每个班级的情况
select * from score;
-- 分组：group by
SELECT cno, max(degree) as "最大值",min(degree) "最小值",avg(degree) "平均分",sum(degree) "总分数",count(1) "总人数" FROM score group by cno;

-- having：分组之后筛选数据
-- 统计班级人数，大于等于 3 的信息

SELECT cno, max(degree) as "最大值",min(degree) "最小值",avg(degree) "平均分",sum(degree) "总分数",count(1) "总人数" FROM score
group by cno having count(1)>=3;

-- 统计班级人数，大于等于 3 的信息,班级名称必须3-2开头
SELECT cno, max(degree) as "最大值",min(degree) "最小值",avg(degree) "平均分",sum(degree) "总分数",count(1) "总人数" FROM score
group by cno having count(1)>=3 and cno like '3-2%';

-- where 和 having：都是用来筛选数据，having必须分组以后的数据筛选，如果where能做的事情就where处理，效率更高

SELECT cno, max(degree) as "最大值",min(degree) "最小值",avg(degree) "平均分",sum(degree) "总分数",count(1) "总人数" FROM score
where cno like '3-2%'
group by cno having count(1)>=3;

-- 嵌套查询（子查询）：一个SQL包含多个SELECT查询；一个SQL的查询结果，是另一个SQL语句的查询条件

-- 查询名称为曾华的学生的成绩

-- 返回的结果有多条记录？
-- in:关键字
select * from score where sno in (select sno from student where sname='曾华' or sname='匡明');

select * from student where sno =101 or sno=103 or sno=105 or sno=108;
select * from student where sno in(101,103,105,108);

--

-- s

select * from score where degree>any(select degree from score where sno =105);

-- 多表查询（联合查询）:注意，和主外键没有关系
-- 查询所有学生信息以及学生对应的成绩
-- oracle提供了4种联合查询
-- INNER JOIN ：交集，查询的是两个表共同的数据
-- LEFT JOIN ：左表为准，左表中所有的数据都会查询出来，右表有数据就显示，没有数据使用 null替代
-- RIGTH JOIN ：右表为准，右表中所有的数据都会查询出来，左表有数据就显示，没有数据使用 null替代
-- FULL JOIN(mysql没有)：两边表的数据都会查询出来

-- PS:关键字的左边就是左表，右边就是右表

-- 语法：SELECT * FROM 表1 [inner|left|right|full] join 表2 ON 表1.id = 表2.id [where 条件]

SELECT * FROM student stu INNER JOIN score sc ON stu.sno = sc.sno WHERE stu.ssex='女';

SELECT * FROM student LEFT JOIN score ON student.sno = score.sno;
SELECT * FROM student RIGHT JOIN score ON student.sno = score.sno;
SELECT * FROM score RIGHT JOIN student ON student.sno = score.sno;
SELECT * FROM student FULL JOIN score ON student.sno = score.sno;

select * from student;
select * from score;

-- 集合操作：UNION 去重
-- UNION ALL：不去重
select sno,sname,ssex from student UNION select tno,tname,tsex from teacher;

select sname from student UNION all select tname from teacher;

-- 字符相关函数
函数名参数作用
LENGTH (CHAR) 获取指定字符串长度
select stu.*,length(stu.sname) from student stu;

SUBSTR (CHAR,NUMBER1,NUMBER2) 从指定字符串数字1位置截取数字2长度字符串
select stu.*,SUBSTR(stu.sname,0,1) from student stu;

UPPER LOWER (CHAR) 大小写转换函数
REPLACE (CHAR1,CHAR2,CHAR3) 将字符串1中字符串2的值替换为字符串3
select stu.*,REPLACE(stu.sname,'王','李') from student stu;

TRIM (CHAR) 删除字符串前后空格，中间不会去掉

CONCAT (CHAR1,CHAR2) 连接两个字符串
select stu.*,CONCAT(stu.sname,stu.ssex) from student stu;

-- 日期函数
函数名参数作用
SYSDATE 无获取当前系统时间

SYSTIMESTAMP 无包含时区信息的系统时间

ADD_MONTHS (DATE,INTEGE) 为指定日期月份加1

select stu.*,ADD_MONTHS(sbirthday,12) from student stu

-- dual:是oracle数据库中，最小的表，没有实际含义，只是负责完成语法规定
select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual;

-- select extract (year from sysdate) year, extract (month from sysdate) month, extract (day from sysdate) day from dual;
EXTRACT (DATETIME) 从指定时间提取指定日期部分
select stu.*,EXTRACT(month from sbirthday) from student stu

MONTHS_BETWEEN (DATE1,DATE2) 返回日期1与2之间相差的月份值
select stu.*,sysdate,MONTHS_BETWEEN(sysdate,sbirthday) from student stu

-- 分页查询
--mysql:关键字 limit
--sqlserver: 关键字 top
--oracle:关键字 rownum 伪列

select stu.*,rownum from student stu;

-- 伪列：在查询的结果上添加序号。

--:page 页码
--:pageSize 大小

-- 每页3条记录

-- 第1页：1-3
-- 第2页：4-6
-- 第3页：7-9

第一个：(page-1)*pageSize+1
第二个：page*pageSize

2、查询教师所有的单位即不重复的Depart列。
3、查询Student表的所有记录。
4、查询Score表中成绩在60到80之间的所有记录。
5、查询Score表中成绩为85，86或88的记录。
6、查询Student表中“95031”班或性别为“女”的同学记录。
7、以Class降序查询Student表的所有记录。
8、以Cno升序、Degree降序查询Score表的所有记录。
9、查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。

create table dept(
deptno varchar2(20),
deptname varchar2(10),
deptloc varchar2(20)
);

select * from dept;

alter table dept add(phone varchar2(11));

create table student(
sno varchar2(3) not null primary key,
sname varchar2(4) not null,
ssex varchar(2) not null,
sbirthday date,
class varchar2(5) not null
);

select * from student;

create table course(
cno varchar2(5) not null primary key,
cname varchar2(10) not null,
tno varchar(10) not null
);

select * from course;

create table score(
sno varchar2(3) not null ,
cno varchar2(5) not null,
degree number(10,1) not null
);

select * from score;

create table teacher(
tno varchar2(3) not null primary key,
tname varchar2(4) not null,
tsex varchar(2) not null,
tbirthday date not null,
prof varchar2(6)
);

select * from teacher;

alter table teacher add(depart varchar2(10));

insert into student values('108','曾华','男',to_date('1977-09-01','yyyy-MM-dd HH24:mi:ss'),'95033');
insert into student values('105','匡明','男',to_date('1975-10-02','yyyy-MM-dd HH24:mi:ss'),'95031');
insert into student values('107','王丽','女',to_date('1976-01-23','yyyy-MM-dd HH24:mi:ss'),'95033');
insert into student values('101','李军','男',to_date('1976-02-20','yyyy-MM-dd HH24:mi:ss'),'95033');
insert into student values('109','王芳','女',to_date('1977-02-10','yyyy-MM-dd HH24:mi:ss'),'95031');
insert into student values('103','陆君','男',to_date('1974-06-03','yyyy-MM-dd HH24:mi:ss'),'95031');

insert into course values('3-105','计算机导论','825');
select * from course;
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数据电路','856');
insert into course values('9-888','高等数学','831');

insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('101','3-105','64');
insert into score values('107','3-105','91');
insert into score values('108','3-105','78');
insert into score values('101','6-166','85');
insert into score values('107','6-106','79');
insert into score values('108','6-166','81');
delete from score;
select * from score;

insert into teacher values('804','李诚','男',to_date('1958-12-02','yyyy-MM-dd HH24:mi:ss'),'副教授','计算机系');
insert into teacher values('856','张旭','男',to_date('1969-03-12','yyyy-MM-dd HH24:mi:ss'),'讲师','电子工程系');
insert into teacher values('825','王萍','女',to_date('1972-05-05','yyyy-MM-dd HH24:mi:ss'),'助教','计算机系');
insert into teacher values('831','刘冰','女',to_date('1977-08-14','yyyy-MM-dd HH24:mi:ss'),'助教','电子工程系');

select * from teacher;

delete from teacher;
delete from course;
select * from course;
select * from score;

alter table course add CONSTRAINT fk_course foreign key(tno) references teacher(tno);

alter table score add CONSTRAINT fk_score foreign key(sno) references student(sno);

alter table score add CONSTRAINT fk_cou foreign key(cno) references course(cno);

--1、查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student ;

--2、查询教师所有的单位即不重复的Depart列。
select distinct Depart from teacher;

--3、查询Student表的所有记录。
select * from student;

--4、查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;

--5、查询Score表中成绩为85，86或88的记录。
select * from score where degree='85' or degree='86'or degree='88';

--6、查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class='95031' or ssex='女';

--7、以Class降序查询Student表的所有记录。
select * from student order by class desc;

--8、以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno , degree desc;

--9、查询“95031”班的学生人数。
select count(class) from student where class='95031';

--10、查询Score表中的最高分的学生学号和课程号。
select sno,cno from score where degree=(select max(degree) from score) ;

--11、查询‘3-105’号课程的平均分。
select avg(degree) from score where cno='3-105';

--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree)"平均分" from score where cno like'3%' group by sno having count(sno)>=5;

--13、查询最低分大于70，最高分小于90的Sno列。
select sno from score where degree>'70' and degree<'90';

--14、查询所有学生的Sname、Cno和Degree列。
select sname,cno,degree from student full join score on student.sno=score.sno;

--15、查询所有学生的Sno、Cname和Degree列。
select sno,cname,degree from course full join score on course.cno=score.cno;

--16、查询所有学生的Sname、Cname和Degree列。
select sname,cname,degree from student full join score on student.sno = score.sno
full join course on course.cno = score.cno;

--17、查询“95033”班所选课程的平均分。

select cname,avg(degree) from student inner join score on student.sno = score.sno
inner join course on course.cno = score.cno where class = '95033' group by cname;

--18、假设使用如下命令建立了一个grade表：
create table grade(low NUMERIC(3,0),upp NUMERIC(3,0),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
commit;
select * from grade;

现查询所有同学的Sno、Cno和rank列。
select sno,cno,rank from score,grade where degree between low and upp;

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select sname,degree,cno from student left join score on student.sno=score.sno where cno='3-105' and degree>
(select degree from score where sno='109' and cno='3-105');

--20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select cno,sno,degree from score where degree!=(select max(degree) from score ) group by cno,sno,degree;

--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score where degree>(select degree from score where sno='109' and cno='3-105' );

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname,sbirthday from student where
extract (year from sbirthday)=(select extract (year from sbirthday) from student where sno='109')

--23、查询“张旭“教师任课的学生成绩。
select student.sno ,sname,ssex,degree,cname,tname from student inner join
score on student.sno=score.sno inner join course on score.cno=course.cno
inner join teacher on teacher.tno=course.tno where tname='张旭';

--24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher inner join course on teacher.tno=course.tno where cno in
(select cno from score group by cno having count(sno)>5);

--25、查询95033班和95031班全体学生的记录。
select * from student where class='95033'or class='95031';

--26、查询存在有85分以上成绩的课程Cno.
select cno from score where degree>85 group by cno;

--27、查询出“计算机系“教师所教课程的成绩表。
select score.cno,degree from score inner join course on score.cno = course.cno
where course.tno in (select tno from teacher where depart = '计算机系');

--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname,prof from teacher where depart = '计算机系'
and prof not in (select prof from teacher where depart = '电子工程系');

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno,sno,degree from score where cno='3-105'
and degree>any(select degree from score where cno='3-245') order by degree desc;

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select cno,sno,degree from score where cno='3-105' and degree>all(select degree from score where cno='3-245');

--31、查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from student UNION select tname,tsex,tbirthday from teacher;

--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname name,ssex sex,sbirthday birthday from student where ssex='女' UNION select tname,tsex,tbirthday
from teacher where tsex='女' ;

--33、查询成绩比该课程平均成绩低的同学的成绩表。--自连接
select * from score where degree<(select avg(degree) from score where score.cno=score.cno)

select * from score group by degree having degree<(select avg(degree) from score group by cno )
--34、查询所有任课教师的Tname和Depart.
select tname,depart from teacher;

--35 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in (select tno from course);

--36、查询至少有2名男生的班号。
select class from student where ssex = '男' group by class having count(ssex)>=2 ;

--38、查询Student表中每个学生的姓名和年龄。
select sname,extract (year from sysdate)-extract (year from sbirthday) age from student;

--39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student;

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select sno,sname,ssex,sbirthday,class,extract (year from sysdate)-extract (year from sbirthday) age
from student order by class asc,age asc;

--41、查询“男”教师及其所上的课程。
select tname cname from teacher inner join course on teacher.tno =course.tno
where tsex='男';

--42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score where degree=(select max(degree) from score);

--43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex=(select ssex from student where sname='李军');

--44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex=(select ssex from student where sname='李军')and
class=(select class from student where sname='李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表
法一；
select * from score inner join student on student.sno=score.sno
inner join course on course.cno=score.cno where ssex='男' and cname='计算机导论';

法二：
select * from score inner join student on student.sno = score.sno
where ssex = '男' and cno = (select cno from course where cname = '计算机导论');