关系型数据库
一、终端操作数据库
1、登录：mysql-uroot-p
2、查询数据库中的所有数据库
输入：mysql> show databases;
显示：
±-------------------+
| Database |
±-------------------+
| information_schema |
| gene |
| genee |
| mysql |
| performance_schema |
| sys |
±-------------------+
6 rows in set (0.01 sec)
mysql>
3、如何选中一个数据库进行操作
mysql> select * from admin;
ERROR 1046 (3D000): No database selected
mysql>
未选中具体数据库报错
mysql> use gene;
Database changed
选中gene数据库
mysql> select * from gegnggg;
选中gene数据库中的gegnggg表
Empty set (0.00 sec)
查询表中id为2的那一栏
mysql> selectfrom ggg where ggg_id=2;
ERROR 1054 (42S22): Unknown column ‘ggg_id’ in ‘where clause’
提示错误
mysql> selectfrom ggg where id=2;
±—±----±-----+
| id | nam | year |
±—±----±-----+
| 2 | 55 | 11 |
±—±----±-----+
1 row in set (0.00 sec)
4、退出数据库
输入：
mysql> exit;
输出：
Bye
5、创建数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
6、查看数据库中的数据表
mysql> show tables;
Empty set (0.00 sec)
7、创建数据表
mysql> CREATE TABLE pet(
-> name VARCHAR(20),
-> species VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> death DATE);
Query OK, 0 rows affected (0.01 sec)
8、查看数据表结构
describe pet;
9、查看数据表中的记录
mysql> select*from pet;
Empty set (0.04 sec)
10、表中添加数据记录
mysql> INSERT INTO pet
-> VALUES(‘wangcai’,‘kaijfh’,‘dog’,‘f’,‘166’,NULL);
11、mysql常用数据类型有哪些
数值、日期时间、字符串类型
mysql提供了5种整型： tinyint、smallint、mediumint、int和bigint(字节数1、2、3、4、8)，这些类型在可表示的取值范围上是不同的。整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。
mysql 提供三种浮点类型： float、double和decimal。与整型不同，浮点类型不能是unsigned的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的（当然，也有负的最大和最小值）
日期类型，字符串类型。。。。。。自己百度
12、如何删除数据
mysql> delete from pet where name=‘laifu’;
13、如何修改数据
update pet set name=‘wangwangcai’ where owner=‘wnagcai’;
总结：增加insert，删除delete，修改update，查询select
14、mysql建表约束，sql
主键约束
它能够唯一确定一张表的一条记录，通过某个字段添加约束，是的该字段不重复且不为空
mysql> create table user(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
联合主键
create table user2(id int,name varchar(20),password varchar(20),primary key(id,name));
自增约束
mysql> create table user3(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
添加主键约束
alter table user4 add primary key（id）；
删除主键约束
alter table user4 drop primary key；
唯一约束
约束修饰的字段的值不可以重复
alter table user5 add unique （name）；
modify添加
alter table user7 modify name varchar（20） unique；
非空约束
create table user9 （ID int ，name varchar（20）not null）；
默认约束
当插入字段值时，没有传值就使用默认值
create table user10 （ID int ，name varchar（20），age int default 10）；
外键约束
涉及两个表：父表，子表
班级：create table classes（id
int primary key，name varchar（20））；
学生：create table cstudents（id
int primary key，name varchar（20），class-id int，foreign key （class-id）references classes（id ）；
15、数据库的设计范式
第一范式
数据表中的所有字段都是不可分割的原子值
create table student2（id
int primary key，name varchar（20），adress varchar（30））；
insert into student2 values（1，‘张三’，‘中国四川省成都市武侯大道100号’）
-4=4===字段值还可以继续拆分的不满足第一范式
create table student2（id
int primary key，name varchar（20），country varchar(30）privence varchar （30））；
范式，设计的越详细，对某些实际操作更好
第二范式
必须满足第一范式，第二范式要求：除主键外的每一列都必须完全依赖与主键
如果出现不玩全依赖，只可能发生在联合主键的情况下
create table myorder (product_id int,customer_id int,product_name varchar(20),customer_name varchar(20),primary key(product_id,customer_id);
拆表
create table myorder(order_id int primary key,product_id int,customer_id int);
create table product(id int primary key,name varchar(20));
create table customer(id int primary key,name varchar(20));
分成三个表之后满足第二范式
第三范式
必须先满足第二范式，除开主键之外其他列之间不能有传递依赖关系
create table myorder(order_id int primary key,product_id int,customer_id int,customer_phone varchar(15));
应该：create table myorder(order_id int primary key,product_id int,customer_id int);
create table customer(id int primary key,name varchar(20),phone varchar(15));
16、查询练习
学生表
student 学号，姓名，性别，出生，班级
create table studnet(sno varchar(20) primary key,sname varchar(20) not null,ssex varchar(10) not null,sbirthday datetime,class varchar(20);
课程表
couser 课程号，课程名称，教师编号
create table course(cno varchar(20) primary key,cname varchar(20) not null,tno varchar(20) not null,foreign key(tno) references teacher(tno));
成绩表
score 学号，课程号，成绩
create table score(sno varchar(20) primary key,cno varchar(20) not null,degree decimal,foreign key(sno) references studnet(sno),foreign key(cno) references course(cno));
教师表
teacher 教师编号，教师名字，性别，出生，职称
create table teacher(tno varchar(20)primary key,tname varchar(20) not null,tsex varchar(20) not null,tbirthday datetime,prof varchar(20) not null,depart varchar(20) not null);
distinct查询排重
查询区间：select*from score where degree between 60 and 80;
表示或者关系的查询：in
降序：desc 升序：asc
统计：count
子查询：
select max (degree)from scorre;
select sno,cno from score where degree=(select max (degree)from scorre);
查每门课的平均成绩 avg（）
select avg(degree) from score where cno=‘3-105’;
select avg(degree) from score grope by cno’;所有课的平均成绩
向表中添加数据
–学生表数据
INSERT INTO student VALUES(‘101’,‘曾华’,‘男’,‘1977-09-01’,‘95033’);
INSERT INTO student VALUES(‘102’,‘匡明’,‘男’,‘1975-10-02’,‘95031’);
INSERT INTO student VALUES(‘103’,‘王丽’,‘女’,‘1976-01-23’,‘95033’);
INSERT INTO student VALUES(‘104’,‘李军’,‘男’,‘1976-02-20’,‘95033’);
INSERT INTO student VALUES(‘105’,‘王芳’,‘女’,‘1975-02-10’,‘95031’);
INSERT INTO student VALUES(‘106’,‘陆军’,‘男’,‘1974-06-03’,‘95031’);
INSERT INTO student VALUES(‘107’,‘王尼玛’,‘男’,‘1976-02-20’,‘95033’);
INSERT INTO student VALUES(‘108’,‘张全蛋’,‘男’,‘1975-02-10’,‘95031’);
INSERT INTO student VALUES(‘109’,‘赵铁柱’,‘男’,‘1974-06-03’,‘95031’);
–教师表数据
INSERT INTO teacher VALUES(‘804’,‘李诚’,‘男’,‘1958-12-02’,‘副教授’,‘计算机系’);
INSERT INTO teacher VALUES(‘856’,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’);
INSERT INTO teacher VALUES(‘825’,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’);
INSERT INTO teacher VALUES(‘831’,‘刘冰’,‘女’,‘1977-08-14’,‘助教’,‘电子工程系’);
–添加课程表
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(‘103’,‘6-166’,‘85’);
INSERT INTO score VALUES(‘105’,‘6-166’,‘79’);
INSERT INTO score VALUES(‘109’,‘6-166’,‘81’);
几张表的数据展现
student SELECT * FROM student;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
teacher SELECT * FROM teacher;
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+
score SELECT * FROM score;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+
course SELECT * FROM course;
±------±-----------±-----+
| c_no | c_name | t_no |
-------±-----------±-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
±------±-----------±-----+
练习题
– 1.查询student表中所有的记录
SELECT * FROM student;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 1 | admin | 男 | 1999-09-09 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 2.查询student表中所有记录的s_name,s_sex和s_class列
SELECT s_no,s_name,s_class FROM student;
±-----±-------±--------+
| s_no | s_name | s_class |
±-----±-------±--------+
| 1 | admin | 95033 |
| 101 | 曾华 | 95033 |
| 102 | 匡明 | 95031 |
| 103 | 王丽 | 95033 |
| 104 | 李军 | 95033 |
| 105 | 王芳 | 95031 |
| 106 | 陆军 | 95031 |
| 107 | 王尼玛 | 95033 |
| 108 | 张全蛋 | 95031 |
| 109 | 赵铁柱 | 95031 |
±-----±-------±--------+
– 3.查询教师所有的单位但是不重复的t_depart列
SELECT distinct (t_depart) FROM teacher;
±-----------+
| t_depart |
±-----------+
| 计算机系 |
| 计算机机系 |
| 电子工程系 |
±-----------+
– 4.查询score表中成绩在60-80之间所有的记录(sc_degree)
注意:BETWEEN… ADN… 是包含边界的
SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
±-----±------±----------+
– 5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
SELECT * FROM score WHERE sc_degree IN(85, 86, 88);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
±-----±------±----------+
– 6.查询student表中’95031’班或者性别为’女’的同学记录
SELECT * FROM student WHERE s_class = ‘95031’ OR s_sex = ‘女’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 7.以class降序查询student表中所有的记录
SELECT * FROM student ORDER BY s_class desc;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 1 | admin | 男 | 1999-09-09 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 8.以c_no升序.sc_degree降序插叙score表中所有的数据
先以c_no进行升序,若c_no相同,则以sc_degree降序
SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
±-----±------±----------+
– 9.查询’95031’班的学生人数
SELECT COUNT(s_no) FROM student WHERE s_class = ‘95031’;
±------------+
| COUNT(s_no) |
±------------+
| 5 |
±------------+
– 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
–查询步骤
1.先找到最高分
最高分: SELECT MAX(sc_degree) FROM score;
2.通过我们找到的最高分的分数来从score中找到我们需要的学生号和课程号
SELECT c_no, s_no FROM score WHERE sc_degree = (最高分)
SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);
±------±-----+
| c_no | s_no |
±------±-----+
| 3-105 | 103 |
±------±-----+
排序 最高分有多个的情况下可能有数据问题
limit x,y (x:表示从X条数据开始 y:需要查出多少条)
SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;这个OK
但是我们再插入一条数据:
INSERT INTO score VALUES(‘101’,‘9-888’,‘92’);
再用排序法去查得到:
±------±-----+
| c_no | s_no |
±------±-----+
| 9-888 | 101 |
±------±-----+
有两条数据但是只显示一条,有问题
若我们用子查询的方法来查询的话会得到:
±------±-----+
| c_no | s_no |
±------±-----+
| 9-888 | 101 |
| 3-105 | 103 |
±------±-----+
完全没有问题.
最后为了和视频数据一致删除刚刚插入的数据:
DELETE FROM score WHERE c_no = ‘9-888’ AND s_no = ‘101’;
– 11.查询每门课的平均成绩
SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;
±------±---------------+
| c_no | AVG(sc_degree) |
±------±---------------+
| 3-105 | 87.6667 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±---------------+
– 12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
SELECT AVG(sc_degree),c_no from score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no like ‘3%’ ;
±---------------±------+
| AVG(sc_degree) | c_no |
±---------------±------+
| 85.3333 | 3-105 |
| 76.3333 | 3-245 |
±---------------±------+
– 13.查询分数大于70但是小于90的s_no列:
SELECT s_no,sc_degree FROM score WHERE sc_degree BETWEEN 71 AND 89;
±-----±----------+
| s_no | sc_degree |
±-----±----------+
| 103 | 86 |
| 103 | 85 |
| 105 | 88 |
| 105 | 75 |
| 105 | 79 |
| 109 | 76 |
| 109 | 81 |
±-----±----------+
进阶:显示s_name,c_name
select s_name,sc_degree,c_name FROM score,student,course WHERE score.s_no = student.s_no AND score.c_no = course.c_no AND sc_degree BETWEEN 71 AND 89;
±-------±----------±-----------+
| s_name | sc_degree | c_name |
±-------±----------±-----------+
| 王丽 | 86 | 操作系统 |
| 王丽 | 85 | 数字电路 |
| 王芳 | 88 | 计算机导论 |
| 王芳 | 75 | 操作系统 |
| 王芳 | 79 | 数字电路 |
| 赵铁柱 | 76 | 计算机导论 |
| 赵铁柱 | 81 | 数字电路 |
±-------±----------±-----------+
- 14.查询所有的学生 s_name , c_no, sc_degree列
SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;
±-------±------±----------+
| s_name | c_no | sc_degree |
±-------±------±----------+
| 王丽 | 3-105 | 92 |
| 王丽 | 3-245 | 86 |
| 王丽 | 6-166 | 85 |
| 王芳 | 3-105 | 88 |
| 王芳 | 3-245 | 75 |
| 王芳 | 6-166 | 79 |
| 赵铁柱 | 3-105 | 76 |
| 赵铁柱 | 3-245 | 68 |
| 赵铁柱 | 6-166 | 81 |
±-------±------±----------+
– 15.查询所有学生的s_no, c_name, sc_degree列
SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;
±-----±-----------±----------+
| s_no | c_name | sc_degree |
±-----±-----------±----------+
| 103 | 计算机导论 | 92 |
| 103 | 操作系统 | 86 |
| 103 | 数字电路 | 85 |
| 105 | 计算机导论 | 88 |
| 105 | 操作系统 | 75 |
| 105 | 数字电路 | 79 |
| 109 | 计算机导论 | 76 |
| 109 | 操作系统 | 68 |
| 109 | 数字电路 | 81 |
±-----±-----------±----------+
– 16.查询所有的学生 s_name , c_name, sc_degree列
将上面的c_no 的值换位c_name
SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;
±-------±-----------±----------+
| s_name | c_name | sc_degree |
±-------±-----------±----------+
| 王丽 | 计算机导论 | 92 |
| 王丽 | 操作系统 | 86 |
| 王丽 | 数字电路 | 85 |
| 王芳 | 计算机导论 | 88 |
| 王芳 | 操作系统 | 75 |
| 王芳 | 数字电路 | 79 |
| 赵铁柱 | 计算机导论 | 76 |
| 赵铁柱 | 操作系统 | 68 |
| 赵铁柱 | 数字电路 | 81 |
±-------±-----------±----------+
– 17.查询班级是’95031’班学生每门课的平均分
select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = ‘95031’) GROUP BY c_no;
select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = ‘95031’ GROUP BY sc.c_no;
SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC WHERE s.s_class = ‘95031’ AND s.s_no = sc.s_no GROUP BY sc.c_no ;
±------±------------------+
| c_no | AVG(sc.sc_degree) |
±------±------------------+
| 3-105 | 82.0000 |
| 3-245 | 71.5000 |
| 6-166 | 80.0000 |
±------±------------------+
进阶,加入课程名称:
SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = ‘95031’ AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;
±------±-----------±------------------+
| c_no | c_name | AVG(sc.sc_degree) |
±------±-----------±------------------+
| 3-105 | 计算机导论 | 82.0000 |
| 3-245 | 操作系统 | 71.5000 |
| 6-166 | 数字电路 | 80.0000 |
±------±-----------±------------------+
– 18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录
(在大家都在选修3-105的背景下 查询 所有 分数 比 学号为"109"还要高的学生信息)
SELECT * FROM student AS s, score AS sc WHERE sc.c_no = ‘3-105’ AND sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’) AND s.s_no = sc.s_no;
±-----±-------±------±--------------------±--------±-----±------±----------+
| s_no | s_name | s_sex | s_birthday | s_class | s_no | c_no | sc_degree |
±-----±-------±------±--------------------±--------±-----±------±----------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 |
±-----±-------±------±--------------------±--------±-----±------±----------+
– 19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录
SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
±-----±------±----------+
不过视频中仅仅查出来了score记录,但是并没有学生的信息,按照上面的来修改:
SELECT * FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’) AND s.s_no = sc.s_no;
还可以再进一步:
SELECT s.s_no AS’学生学号’, s.s_name AS’学生姓名’, s_sex AS’性别’, s_class AS’班级’, c.c_no AS’课程编号’, c.c_name AS’课程名称’ ,sc.sc_degree AS’分数’ FROM student AS s, score AS sc ,course AS c WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = ‘109’ AND c_no = ‘3-105’) AND s.s_no = sc.s_no AND sc.c_no = c.c_no;
±---------±---------±-----±------±---------±-----------±-----+
| 学生学号 | 学生姓名 | 性别 | 班级 | 课程编号 | 课程名称 | 分数 |
±---------±---------±-----±------±---------±-----------±-----+
| 103 | 王丽 | 女 | 95033 | 3-105 | 计算机导论 | 92 |
| 103 | 王丽 | 女 | 95033 | 3-245 | 操作系统 | 86 |
| 103 | 王丽 | 女 | 95033 | 6-166 | 数字电路 | 85 |
| 105 | 王芳 | 女 | 95031 | 3-105 | 计算机导论 | 88 |
| 105 | 王芳 | 女 | 95031 | 6-166 | 数字电路 | 79 |
| 109 | 赵铁柱 | 男 | 95031 | 6-166 | 数字电路 | 81 |
±---------±---------±-----±------±---------±-----------±-----+
– 20.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN(‘108’,‘101’));
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 21.查询 张旭 教师任课的学生的成绩
select * from student where s_no IN (SELECT s_no FROM score WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name=‘张旭’)));
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 22.查询选修课程的同学人数多余 5 人的教师姓名
SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(s_no) > 5));
Empty set (0.00 sec) 没有符合条件的
为了效果,添加数据:
INSERT INTO score VALUES(‘101’,‘3-105’,‘90’);
INSERT INTO score VALUES(‘102’,‘3-105’,‘91’);
INSERT INTO score VALUES(‘104’,‘3-105’,‘89’);
再次查询可得:
±-----±-------±------±--------------------±------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±------±-----------+
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机机系 |
±-----±-------±------±--------------------±------±-----------+
注意:视频中用 ‘=’ 是不严谨的,实际中你根本不知道有多少条件是符合的,要用IN
– 23.查询95033班和95031班全体学生的记录
SELECT * FROM student WHERE s_class IN(‘95031’,‘95033’) ORDER BY s_class ;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
±-----±-------±------±--------------------±--------+
由于视频中就只有这两个班,所以要插入数据:
INSERT INTO student VALUES(‘110’,‘张飞’,‘男’,‘1974-06-03’,‘95038’);
再次查询,还是上面那些
我在写的时候,写成了:查询95033班和95031班全体学生每门课的成绩以及负责该课程的老师,最后以class来排序
写都写了,那就放出来:
SELECT s.s_no, s.s_name,s.s_birthday,s.s_class, c.c_no, c.c_name, sc.sc_degree , t.t_name FROM student AS s, course AS c, score AS sc,teacher AS t WHERE s.s_class IN(‘95031’,‘95033’) AND s.s_no = sc.s_no AND sc.c_no = c.c_no AND c.t_no = t.t_no;
±-----±-------±--------------------±--------±------±-----------±----------±-------+
| s_no | s_name | s_birthday | s_class | c_no | c_name | sc_degree | t_name |
±-----±-------±--------------------±--------±------±-----------±----------±-------+
| 102 | 匡明 | 1975-10-02 00:00:00 | 95031 | 3-105 | 计算机导论 | 91 | 王萍 |
| 105 | 王芳 | 1975-02-10 00:00:00 | 95031 | 3-105 | 计算机导论 | 88 | 王萍 |
| 105 | 王芳 | 1975-02-10 00:00:00 | 95031 | 3-245 | 操作系统 | 75 | 李诚 |
| 105 | 王芳 | 1975-02-10 00:00:00 | 95031 | 6-166 | 数字电路 | 79 | 张旭 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 | 95031 | 3-105 | 计算机导论 | 76 | 王萍 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 | 95031 | 3-245 | 操作系统 | 68 | 李诚 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 | 95031 | 6-166 | 数字电路 | 81 | 张旭 |
| 101 | 曾华 | 1977-09-01 00:00:00 | 95033 | 3-105 | 计算机导论 | 90 | 王萍 |
| 103 | 王丽 | 1976-01-23 00:00:00 | 95033 | 3-105 | 计算机导论 | 92 | 王萍 |
| 103 | 王丽 | 1976-01-23 00:00:00 | 95033 | 3-245 | 操作系统 | 86 | 李诚 |
| 103 | 王丽 | 1976-01-23 00:00:00 | 95033 | 6-166 | 数字电路 | 85 | 张旭 |
| 104 | 李军 | 1976-02-20 00:00:00 | 95033 | 3-105 | 计算机导论 | 89 | 王萍 |
±-----±-------±--------------------±--------±------±-----------±----------±-------+
– 24.查询存在85分以上成绩的课程c_no
SELECT * FROM score where sc_degree > 85;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±-----±------±----------+
升级版:知道c_no还不行,我们还需要看到c_name 以及对应的老师
SELECT sc.c_no,c.c_name, t.t_name FROM score AS sc, course AS c, teacher AS t WHERE sc.c_no IN(SELECT c_no FROM score where sc_degree > 85) AND sc.c_no = c.c_no AND c.t_no = t.t_no GROUP BY c.c_name;
±------±-----------±-------+
| c_no | c_name | t_name |
±------±-----------±-------+
| 3-105 | 计算机导论 | 王萍 |
| 3-245 | 操作系统 | 李诚 |
±------±-----------±-------+
这两位老师教出来的学生都有85分以上的
– 25.查出所有’计算机系’ 教师所教课程的成绩表
SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_depart = ‘计算机系’));
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+
进阶:
SELECT t.t_name,t.t_depart,c.c_name,s.s_name,s_class,sc.sc_degree FROM course AS c, score AS sc, teacher AS t,student AS s WHERE c.t_no IN (select t_no FROM teacher WHERE t_depart = ‘计算机系’) AND c.t_no = t.t_no AND c.c_no = sc.c_no AND sc.s_no = s.s_no ;
教师名称 部门 课程名称 学生名称 班级 分数
±-------±---------±-----------±-------±--------±----------+
| t_name | t_depart | c_name | s_name | s_class | sc_degree |
±-------±---------±-----------±-------±--------±----------+
| 李诚 | 计算机系 | 操作系统 | 王丽 | 95033 | 86 |
| 李诚 | 计算机系 | 操作系统 | 王芳 | 95031 | 75 |
| 李诚 | 计算机系 | 操作系统 | 赵铁柱 | 95031 | 68 |
| 王萍 | 计算机系 | 计算机导论 | 曾华 | 95033 | 90 |
| 王萍 | 计算机系 | 计算机导论 | 匡明 | 95031 | 91 |
| 王萍 | 计算机系 | 计算机导论 | 王丽 | 95033 | 92 |
| 王萍 | 计算机系 | 计算机导论 | 李军 | 95033 | 89 |
| 王萍 | 计算机系 | 计算机导论 | 王芳 | 95031 | 88 |
| 王萍 | 计算机系 | 计算机导论 | 赵铁柱 | 95031 | 76 |
±-------±---------±-----------±-------±--------±----------+
– 26.查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof
SELECT * FROM teacher WHERE t_depart = ‘计算机系’ AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = ‘电子工程系’)
UNION
SELECT * FROM teacher WHERE t_depart = ‘电子工程系’ AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = ‘计算机系’);
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+
– 27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
select * from score where c_no = ‘3-105’ AND sc_degree > ANY(SELECT sc_degree FROM score WHERE c_no = ‘3-245’ ) ORDER BY sc_degree desc ;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 101 | 3-105 | 90 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
±-----±------±----------+
– 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
SELECT * FROM score WHERE sc_degree > ALL (select sc_degree from score WHERE c_no = ‘3-245’) AND c_no = ‘3-105’;
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
±-----±------±----------+
进阶:查出学生的信息,课程名称,分数(s_name c_name,sc_degree)
SELECT s.s_name , c.c_name ,sc.sc_degree FROM score AS sc, student AS s,course AS c WHERE sc_degree > ALL (select sc_degree from score WHERE c.c_no = ‘3-245’) AND c.c_no = ‘3-105’ AND sc.s_no = s.s_no AND sc.c_no = c.c_no ;
±-------±-----------±----------+
| s_name | c_name | sc_degree |
±-------±-----------±----------+
| 曾华 | 计算机导论 | 90 |
| 匡明 | 计算机导论 | 91 |
| 王丽 | 计算机导论 | 92 |
| 李军 | 计算机导论 | 89 |
| 王芳 | 计算机导论 | 88 |
| 赵铁柱 | 计算机导论 | 76 |
±-------±-----------±----------+
总结: ANY 和 ALL
ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了.
ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行.
– 29. 查询所有教师和同学的 name ,sex, birthday
SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday FROM student
UNION
SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher;
±-------±----±--------------------+
| name | sex | birthday |
±-------±----±--------------------+
| 曾华 | 男 | 1977-09-01 00:00:00 |
| 匡明 | 男 | 1975-10-02 00:00:00 |
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 李军 | 男 | 1976-02-20 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 陆军 | 男 | 1974-06-03 00:00:00 |
| 王尼玛 | 男 | 1976-02-20 00:00:00 |
| 张全蛋 | 男 | 1975-02-10 00:00:00 |
| 赵铁柱 | 男 | 1974-06-03 00:00:00 |
| 张飞 | 男 | 1974-06-03 00:00:00 |
| 李诚 | 男 | 1958-12-02 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
| 张旭 | 男 | 1969-03-12 00:00:00 |
±-------±----±--------------------+
– 30.查询所有’女’教师和’女’学生的name,sex,birthday
SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday FROM student WHERE s_sex = ‘女’
UNION
SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher WHERE t_sex = ‘女’;
±-----±----±--------------------+
| name | sex | birthday |
±-----±----±--------------------+
| 王丽 | 女 | 1976-01-23 00:00:00 |
| 王芳 | 女 | 1975-02-10 00:00:00 |
| 王萍 | 女 | 1972-05-05 00:00:00 |
| 刘冰 | 女 | 1977-08-14 00:00:00 |
±-----±----±--------------------+
– 31.查询成绩比该课程平均成绩低的同学的成绩表
注意:我的数据和视频中的数据有点不一样,所以查询结果有点区别
视频中score:
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 68 | 我数据库中: 109 | 6-166 | 81
±-----±------±----------+
SELECT * FROM score AS sc1 WHERE sc1.sc_degree < (SELECT AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
±-----±------±----------+
进阶,显示出学生name,课程name以及分数
SELECT s.s_name ,sc1.c_no,c.c_name, sc1.sc_degree FROM score AS sc1,student AS s,course AS c WHERE sc1.sc_degree < (SELECT AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no) AND sc1.s_no = s.s_no AND sc1.c_no = c.c_no ;
±-------±------±-----------±----------+
| s_name | c_no | c_name | sc_degree |
±-------±------±-----------±----------+
| 赵铁柱 | 3-105 | 计算机导论 | 76 |
| 王芳 | 3-245 | 操作系统 | 75 |
| 赵铁柱 | 3-245 | 操作系统 | 68 |
| 王芳 | 6-166 | 数字电路 | 79 |
| 赵铁柱 | 6-166 | 数字电路 | 81 |
±-------±------±-----------±----------+
– 32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)
SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course);
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+
注意:我个人是从score表中查出有过考试成绩的课程,再用该课程查出教师的,因为当时我个人认为只有有考试成绩才算"任课",既然我写出来了,那我就放出来:(根据具体业务)
SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no from score));
±-----±-------±------±--------------------±-------±-----------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
±-----±-------±------±--------------------±-------±-----------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
±-----±-------±------±--------------------±-------±-----------+
– 33.查出至少有2名男生的班号
SELECT s_class FROM student WHERE s_sex = ‘男’ GROUP BY s_class HAVING COUNT(s_no) > 1;
±--------+
| s_class |
±--------+
| 95033 |
| 95031 |
±--------+
– 34.查询student 表中 不姓"王"的同学的记录
SELECT * FROM student WHERE s_name NOT LIKE ‘王%’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
±-----±-------±------±--------------------±--------+
– 35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)
SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;
±-------±-----+
| s_name | age |
±-------±-----+
| 曾华 | 42 |
| 匡明 | 44 |
| 王丽 | 43 |
| 李军 | 43 |
| 王芳 | 44 |
| 陆军 | 45 |
| 王尼玛 | 43 |
| 张全蛋 | 44 |
| 赵铁柱 | 45 |
| 张飞 | 45 |
±-------±-----+
– 36. 查询student中最大和最小的 s_birthday的值
SELECT MAX(s_birthday),MIN(s_birthday) FROM student;
±--------------------±--------------------+
| MAX(s_birthday) | MIN(s_birthday) |
±--------------------±--------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
±--------------------±--------------------+
– 37.以班级号和年龄从大到小的顺序查询student表中的全部记录
SELECt * FROM student ORDER BY s_class DESC, s_birthday;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
– 38.查询"男"教师 及其所上的课
SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = ‘男’);
±------±---------±-----+
| c_no | c_name | t_no |
±------±---------±-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
±------±---------±-----+
进阶:查出教师名称,教师性别, 课程名字
SELECT t.t_name,t.t_sex ,c.c_name FROM teacher t ,course c WHERE t_sex = ‘男’ AND t.t_no = c.t_no;
±-------±------±---------+
| t_name | t_sex | c_name |
±-------±------±---------+
| 李诚 | 男 | 操作系统 |
| 张旭 | 男 | 数字电路 |
±-------±------±---------+
-- 39.查询最高分同学的s_no c_no 和 sc_degree;
SELECT * FROM score WHERE sc_degree = (select MAX(sc_degree) AS sc_degree FROM score);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 103 | 3-105 | 92 |
±-----±------±----------+
– 40. 查询和"李军"同性别的所有同学的s_name
SELECT s_name, s_sex FROM student WHERE s_SEX = (SELECT s_sex FROM student WHERE s_name = ‘李军’);
±-------±------+
| s_name | s_sex |
±-------±------+
| 曾华 | 男 |
| 匡明 | 男 |
| 李军 | 男 |
| 陆军 | 男 |
| 王尼玛 | 男 |
| 张全蛋 | 男 |
| 赵铁柱 | 男 |
| 张飞 | 男 |
±-------±------+
– 41.查询和"李军"同性别并且同班的所有同学的s_name
SELECT s_name, s_sex FROM student WHERE s_sex = (SELECT s_sex FROM student WHERE s_name = ‘李军’) AND s_class = (SELECT s_class FROM student WHERE s_name = ‘李军’);
±-------±------+
| s_name | s_sex |
±-------±------+
| 曾华 | 男 |
| 李军 | 男 |
| 王尼玛 | 男 |
±-------±------+
SELECT s_name, s_sex FROM student s1 WHERE s_sex = (SELECT s_sex FROM student s2 WHERE s_name = ‘李军’ AND s1.s_class = s2.s_class);
±-------±------+
| s_name | s_sex |
±-------±------+
| 曾华 | 男 |
| 李军 | 男 |
| 王尼玛 | 男 |
±-------±------+
– 42. 查询所有选修’计算机导论’课程的’男’同学的成绩表
SELECT * FROM score WHERE c_no = (SELECT c_no FROM course WHERE c_name = ‘计算机导论’ ) AND s_no IN(SELECT s_no FROM student WHERE s_sex = ‘男’);
±-----±------±----------+
| s_no | c_no | sc_degree |
±-----±------±----------+
| 101 | 3-105 | 90 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
±-----±------±----------+
进阶:显示出s_name,c_name 替代之前的 s_no and c_no
SELECT s_name,c_name,sc_degree FROM score,student,course WHERE score.c_no = (SELECT c_no FROM course WHERE c_name = ‘计算机导论’ ) AND score.s_no IN(SELECT s_no FROM student WHERE s_sex = ‘男’) AND score.s_no = student.s_no AND score.c_no = course.c_no ;
±-------±-----------±----------+
| s_name | c_name | sc_degree |
±-------±-----------±----------+
| 曾华 | 计算机导论 | 90 |
| 匡明 | 计算机导论 | 91 |
| 李军 | 计算机导论 | 89 |
| 赵铁柱 | 计算机导论 | 76 |
±-------±-----------±----------+
– 43. 假设使用了以下命令建立了一个grade表
CREATE TABLE grade(
low INT(3),
upp INT(3),
grade 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’);
– 查询所有同学的s_no , c_no 和grade列
SELECT s_no, c_no , grade FROM score, grade WHERE sc_degree BETWEEN low and upp;
±-----±------±------+
| s_no | c_no | grade |
±-----±------±------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | c |
| 105 | 6-166 | c |
| 109 | 3-105 | c |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
±-----±------±------+
进阶:显示学生名字和课程名称
SELECT s.s_no, s.s_name, c.c_name ,c.c_no , grade FROM student s, course c ,score sc, grade WHERE sc_degree BETWEEN low and upp AND sc.s_no = s.s_no AND sc.c_no = c.c_no;
±-----±-------±-----------±------±------+
| s_no | s_name | c_name | c_no | grade |
±-----±-------±-----------±------±------+
| 101 | 曾华 | 计算机导论 | 3-105 | A |
| 102 | 匡明 | 计算机导论 | 3-105 | A |
| 103 | 王丽 | 计算机导论 | 3-105 | A |
| 103 | 王丽 | 操作系统 | 3-245 | B |
| 103 | 王丽 | 数字电路 | 6-166 | B |
| 104 | 李军 | 计算机导论 | 3-105 | B |
| 105 | 王芳 | 计算机导论 | 3-105 | B |
| 105 | 王芳 | 操作系统 | 3-245 | c |
| 105 | 王芳 | 数字电路 | 6-166 | c |
| 109 | 赵铁柱 | 计算机导论 | 3-105 | c |
| 109 | 赵铁柱 | 操作系统 | 3-245 | D |
| 109 | 赵铁柱 | 数字电路 | 6-166 | B |
137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
