5分钟学会MySQL

神也小忐忑

已于 2023-10-11 21:47:50 修改

阅读量1.7k

点赞数 4

分类专栏： mysql 文章标签： mysql 数据库 sql

于 2021-09-08 16:07:11 首次发布

本文链接：https://blog.csdn.net/qq_45824463/article/details/118631142

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、数据库操作

登录mysql

mysql  -uroot -p123456

查看所有数据库

show databases;

创建数据库

create database xtt;

创建数据库并指定使用utf-8字符

create database test default character set utf8;

选择数据库

use test;

查看当前数据库所有表

show tables;

创建表

 create table girlfriend(
    id int,
    name varchar(20),
    phone varchar(20),
    can_mysql varchar(20));

注意事项:
 1:var()与varchar()的区别在于var()是定常的,哪怕存储的字符串没有达到"()"中数字的上限,var()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到"()"中的上限则会自动去掉后面的空格;
 2:性别不要用:sex 要用:gender  一个是性 一个是性别;
 3:定义最后一个字段的时候不要加",";
 4:上面的"VAR","VARCHAR","DATE"可以用小写.不过最好用大写来表示区分关键字,若不然也许写到后面你自己都不知道这个词是数据库中的关键字还是你自己自定义的一些数据,同时一定要用英文的标点符号也必须半角输入

查看表结构

desc girlfriend;

 Field    :    字段的名称
 Type     :    字段的类型,可以有int    var    varchar    
 Key      :    是否是关键字 如可以定义为:  primary key 或者 unique key   ...
 Default: :    若是该字段没有主动设置值的时候,该字段的默认值是什么

插入数据

insert into girlfriend values(1,'xiaofang','123456789','yes');

查询表

select * from girlfriend;

删除数据

delete from girlfriend where  id=1;

修改数据

update girlfriend set name='xiaofangfang' where id=1;

主键约束
它能够唯一确定一张表中的一条记录,增加主键约束之后,就可以使得字段不重复而且不为空

create table user(
     id int primary key,
     name varchar(20),
     sex varchar(20));
 运行DESCRIBE user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
发现 id是不可以为null 而且 key的值 也变为:PRI(primary)

复合主键
不完全相同就行

CREATE TABLE user2(
         id INT,
         name VARCHAR(20),
         sex VARCHAR(20),
         PRIMARY key(id,name)
     );

运行DESCRIBE user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

INSERT INTO user2 VALUES (1,'老王','123456');
INSERT INTO user2 VALUES (2,'老王','123456');

+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 老王 | 123456   |
|  2 | 老王 | 123456   |
+----+------+----------+
说明了复合主键只要所有的字段都不是相同的情况下可以允许其中的字段重复:
INSERT INTO user2 VALUES (1,'老李','123456');

SELECT * FROM user2;
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | 老李 | 123456   |
|  1 | 老王 | 123456   |
|  2 | 老王 | 123456   |
+----+------+----------+
场景:表中有班级号以及学生座位号,我们可以用班级号+学生的座位号可以准确的定位一个学生,如:(1班5号可以准确的确定一个学生)

自增约束

 create table zz(
     id int primary key auto_increment,
     name varchar(20));
运行DESCRIBE user3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

INSERT INTO user3(name) VALUES('张三');
INSERT INTO user3(name) VALUES('李四');
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
+----+------+
没有自定义id值 但是自动生成了id

唯一约束

CREATE TABLE user5(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
运行 DESCRIBE user5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

新增name为唯一约束:
ALTER TABLE user5 ADD UNIQUE(name);
运行 DESCRIBE user5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
测试:插入数据
INSERT INTO user5(name) VALUES ('cc');
运行 SELECT * FROM user5; 查看结果:
+----+------+
| id | name |
+----+------+
|  1 | cc   |
+----+------+
再次插入INSERT INTO user5(name) VALUES ('cc');
出现:ERROR 1062 (23000): Duplicate entry 'cc' for key 'name'

换个试试 INSERT INTO user5(name) VALUES ('aa');
运行 SELECT * FROM user5; 查看结果:
+----+------+
| id | name |
+----+------+
|  3 | aa   |
|  1 | cc   |
+----+------+
总结一下:
    主键约束(primary key)中包含了唯一约束
场景:业务需求:设计一张用户注册表,用户姓名必须要用手机号来注册,而且手机号和用户名称都不能为空,那么:
CREATE TABLE user_test(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
    name VARCHAR(20) NOT NULL COMMENT'用户姓名,不能为空',
    phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'
);
运行 DESCRIBE user_test;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20) | NO   |     | NULL    |                |
| phone_number | int(11)     | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.
用户姓名可以重复,但是手机号码却不能重复,复合正常的逻辑需求

非空约束

CREATE TABLE user_test(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
    name VARCHAR(20) NOT NULL COMMENT'用户姓名,不能为空',
    phone_number VARCHAR(20) UNIQUE NOT NULL COMMENT'用户手机,不能重复且不能为空'
);
name和phone_number都设置了非空,先只设置name参数不设置phone_number参数试一试
INSERT INTO user_test (name) VALUES ('张三');
会出现Field 'phone_number' doesn't have a default value

两个非空参数一起设置:
INSERT INTO user_test (name,phone_number) VALUES ('张三','12345678901');
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
|  1 | 张三 | 12345678901  |

默认约束

CREATE TABLE user6(
   id int PRIMARY KEY AUTO_INCREMENT COMMENT'主键id',
    name VARCHAR(20) NOT NULL COMMENT'用户姓名不能为空',
    phone_number VARCHAR(20) NOT NULL COMMENT'用户手机号,不能为空',
    status INT DEFAULT 0 COMMENT'用户状态0:启用 1:禁封 默认:0'
);
运行DESCRIBE user6;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(11)     | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20) | NO   |     | NULL    |                |
| phone_number | varchar(20) | NO   |     | NULL    |                |
| status       | int(11)     | YES  |     | 0       |                |
+--------------+-------------+------+-----+---------+----------------+
插入数据:
INSERT INTO user6(name,phone_number) VALUES ('aa','123');
INSERT INTO user6(name,phone_number) VALUES('bb','1234');
INSERT INTO user6(name,phone_number) VALUES('cc','1263456');

查看数据:SELECT * FROM user6;
+----+------+--------------+--------+
| id | name | phone_number | status |
+----+------+--------------+--------+
|  1 | aa   | 123          |      0 |
|  2 | bb   | 1234         |      0 |
|  3 | cc   | 1263456      |      0 |
+----+------+--------------+--------+
我们没有设置status的值,但是给我们创建了默认值 0.

应用场景:
业务需求:找正常的用户,对这些正常用户进行发放优惠卷或者积分之类的东西,而被禁封的用户我们不让其参加多动.
我们想要封用户只要将status的值从0改为1就行了,当然我们取用户的时候必须要先判断status是否是0.若是1.说明该用户已经被禁封.
先封手机号为'1234'的用户:
UPDATE user6 SET status = 1 WHERE phone_number= '1234';
SELECT * FROM user6;
+----+------+--------------+--------+
| id | name | phone_number | status |
+----+------+--------------+--------+
|  1 | aa   | 123          |      0 |
|  2 | bb   | 1234         |      1 |
|  3 | cc   | 1263456      |      0 |
+----+------+--------------+--------+
status为1,说明用户已经被封,该用户不可以参加活动

我们取用户的时候加上status的判断,如:
SELECT * FROM user6 WHERE status = 0;
+----+------+--------------+--------+
| id | name | phone_number | status |
+----+------+--------------+--------+
|  1 | aa   | 123          |      0 |
|  3 | cc   | 1263456      |      0 |
+----+------+--------------+--------+

外键约束

CREATE TABLE classes(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT'班级表id',
    name VARCHAR(20) COMMENT'班级名称'
);
运行DESCRIBE classes;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

CREATE TABLE student(
   id INT PRIMARY KEY AUTO_INCREMENT COMMENT'学生表id',
   name VARCHAR(20) COMMENT'学生姓名',
    class_id int COMMENT'教室id,这张表中的class_id是classes表中id的值',
    FOREIGN KEY (class_id) REFERENCES classes(id)
);
//FOREIGN :外来  REFERENCES:应用,参考
运行DESCRIBE student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| class_id | int(11)     | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

班级插入数据:
INSERT INTO CLASSES (name) VALUES ('一班');
INSERT INTO CLASSES (name) VALUES ('二班');
INSERT INTO CLASSES (name) VALUES ('三班');
INSERT INTO CLASSES (name) VALUES ('四班');
查看数据 SELECT * FROM classes;
+----+------+
| id | name |
+----+------+
|  1 | 一班 |
|  2 | 二班 |
|  3 | 三班 |
|  4 | 四班 |
+----+------+

学生插入数据:
INSERT INTO student (name,class_id) VALUES ('小赵',1);
INSERT INTO student (name,class_id) VALUES ('小钱',2);
INSERT INTO student (name,class_id) VALUES ('小孙',3);
INSERT INTO student (name,class_id) VALUES ('小李',4);
查看数据 SELECT * FROM student;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 小赵 |        1 |
|  2 | 小钱 |        2 |
|  3 | 小孙 |        3 |
|  4 | 小李 |        4 |
+----+------+----------+
若是像插入班级为5的数据 如:
INSERT INTO student (name,class_id) VALUES ('小周',5);
报错: Cannot add or update a child row

我们删除正在被学生表引用的'四班'试试:
DELETE classes WHERE name = '四班';
出现:Cannot delete or update a parent row:不能删除主表中的行

我们先删除学生表中的 '小李'从而解除班级中'四班'的外键约束,再来删除'四班'(因为小李引用了四班)
DELETE FROM student WHERE name = '小李';
再次删除classes表中的'四班';
DELETE FROM classes WHERE name = '四班';
最后: SELECT * FROM classes;
+----+------+
| id | name |
+----+------+
|  1 | 一班 |
|  2 | 二班 |
|  3 | 三班 |
+----+------+
'四班'被成功删除!

总结:
1.主表中没有的数据,在附表中,是不可以使用的.
2.主表中记录的数据现在正在被附表所引用,那么主表中正在被引用的数据不可以被删除
3.若要想删除,先将附表中的数据删除在删除主表数据
4.对于外键约束大家可以联想 省,市 来进行联想 (市必须要依赖于省,只要省还有一个市在引用,那么就不可以删除省,要不然市就没有省了. 那么我们想删除省,必须要将该省下所有的市全部删除之后,才可以删除这个省)

如何建表之后添加主键约束

CREATE TABLE user4(
    id INT,
    name VARCHAR(20)
);
运行DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

加入主键约束:
ALTER TABLE user4 add PRIMARY KEY(id);
再次运行DESCRIBE user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除主键约束:
ALERT TABLE user4 DROP PRIMARY KEY;
运行DESCRIBE user4查看表结构:
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

使用modify 修改字段.添加约束:
ALTER TABLE user4 MODIFY id INT PRIMARY key;
使用DESCRIBE user4 查看表结构:
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

给主键设置自增长:
ALTER TABLE user4 MODIFY id INT AUTO_INCREMENT;
运行 DESCRIBE user4 查看表结构:
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

退出

exit;

二、数据库查询

创建数据库

create database xtt default character set utf8;

使用数据库

use xtt;

学生表:
student
学号
姓名
性别
出生日期
所在班级

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);

教师表:
teacher
教师编号
教师名字
教师性别
出生日期
职称
所在部门

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);

课程表:
course
课程号
课程课程名称
教师编号

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
    FOREIGN KEY(t_no) references teacher(t_no)
);

成绩表:
srore
学号
课程号
成绩

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
        c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal,
    foreign key(s_no) references student(s_no),
    foreign key(c_no) references course(c_no),
    PRIMARY KEY(s_no,c_no)
);

查看有哪些表

SHOW TABLES;

查看student表结构

DESCRIBE student;

查看teacher表结构

DESCRIBE teacher;

查看course表结构

DESCRIBE course;

查看score表结构

DESCRIBE score;

添加学生表数据

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');

查看各表数据

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM score;
SELECT * FROM course;

1.查询student表中所有的记录

SELECT * FROM student;

2.查询student表中所有记录的s_name,s_sex和s_class列

SELECT s_no,s_name,s_class FROM  student;

3.查询教师所有的单位但是不重复的t_depart列

SELECT distinct (t_depart) FROM teacher;

4.查询score表中成绩在60-80之间所有的记录(sc_degree)

SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ;

5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)

SELECT * FROM score WHERE sc_degree IN(85, 86, 88);

6.查询student表中’95031’班或者性别为’女’的同学记录

SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';

7.以class降序查询student表中所有的记录

SELECT * FROM student ORDER BY s_class desc;

8.以c_no升序.sc_degree降序查询score表中所有的数据
先以c_no进行升序,若c_no相同,则以sc_degree降序

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

9.查询’95031’班的学生人数

SELECT COUNT(s_no) FROM student WHERE s_class = '95031';

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);

排序
最高分有多个的情况下可能有数据问题
limit x,y (x:表示从X条数据开始 y:需要查出多少条)

SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;

11.查询每门课的平均成绩

SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;

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%'  ;

13.查询分数大于70但是小于90的s_no列

SELECT s_no,sc_degree  FROM score WHERE sc_degree BETWEEN 71 AND 89;

进阶:显示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;

14.查询所有的学生 s_name , c_no, sc_degree列

SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;

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 ;

16.查询所有的学生 s_name , c_name, sc_degree列

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;

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 ;

进阶,加入课程名称

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 ;

18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩的所有同学的记录

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;

19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录

SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');

进阶

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;

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'));

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='张旭')));

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));

23.查询95033班和95031班全体学生的记录

SELECT * FROM student WHERE s_class IN('95031','95033') ORDER BY s_class ;

24.查询存在85分以上成绩的课程c_no

SELECT * FROM score where sc_degree > 85;

升级版:知道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;

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 = '计算机系'));

进阶

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 ;

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 = '计算机系');

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 ;

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_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 ;

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;

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 = '女';

31.查询成绩比该课程平均成绩低的同学的成绩表

SELECT * FROM score AS sc1 WHERE sc1.sc_degree < (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);

进阶,显示出学生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 ;

32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)

SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course);

33.查出至少有2名男生的班号

SELECT s_class FROM student WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_no) > 1;

34.查询student 表中不姓"王"的同学的记录

SELECT * FROM student WHERE s_name NOT LIKE '王%';

35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)

SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;

36. 查询student中最大和最小的 s_birthday的值

SELECT MAX(s_birthday),MIN(s_birthday) FROM student;

37.以班级号和年龄从大到小的顺序查询student表中的全部记录

SELECt * FROM student ORDER BY s_class DESC, s_birthday;

38.查询"男"教师及其所上的课

SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = '男');

进阶:查出教师名称,教师性别, 课程名字

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;

39.查询最高分同学的s_no c_no 和 sc_degree;

SELECT * FROM score WHERE sc_degree = (select MAX(sc_degree) AS sc_degree FROM score);

40. 查询和"李军"同性别的所有同学的s_name

SELECT s_name, s_sex FROM student WHERE s_SEX = (SELECT s_sex FROM student WHERE s_name = '李军');

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 = '李军');

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);

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_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 ;

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;

进阶:显示学生名字和课程名称

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;

根据B站视频做的笔记
点击跳转