一周学会MySQL

Geeker工作坊 2021-03-21

266

一周学会MySQL

数据库基本操作

1.如何查看有什么数据库?

show databases;

2.如何选择数据库?

use databasesName;

3.如何查看该数据库中有哪些表?

show tables;

4.如何查询表中的数据?

select * from tableName;

5.如何在数据库服务器中创建自己的数据库?

create database databaseName;

6.如何创建一个数据表? 创建一个pet表

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

7.如何查看数据表的架构?

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

8.如何插入数据?

INSERT INTO pet VALUES('kk','cc','dog','1','1998-8-2',null);

9.删除语句:

DELETE FROM tablesName WHRER 条件;

10.修改数据

UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;

MySQL建表中的约束

主键约束

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

create table user(
    id int PRIMARY KEY,
    name VARCHAR(20)
);
INSERT INTO user VALUES (1,'张三');


+----+------+
| id | name |
+----+------+
|  1 | 张三 |
+----+------+


运行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),
    password 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号可以准确的确定一个学生)

番外：如果忘记添加主键约束可以通过以下方式添加：

// 通过add
alter table tablename add primary key (key);
// 通过modify
alter table tablename modify id int primary key;

番外：如果想删除主键约束可以通过以下方式删除

alter table tablename drop primary key;

####

自增约束

CREATE TABLE user3(
    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    |                |
+--------------+-------------+------+-----+---------+----------------+
这样的话就达到了每一个手机号都只能出现一次,达到了每个手机号只能被注册一次.
用户姓名可以重复,但是手机号码却不能重复,复合正常的逻辑需求

番外: 上面是通过ALTER
来设置的唯一值，也可以在建表之初就设置唯一值

create table user5(
    id int,
    name varchar(20),
    unique(id,name),//两个键联合不重复即可
);
mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


create table user6(
    id int UNIQUE PRIMARY KEY, 每一个都不重复
    name nvarchar(20) UNIQUE,
);
mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

番外：如何删除唯一约束

mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> alter table user3 drop index id
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

番外：如何添加唯一性约束

//通过add添加
alter table user4 add unique(id);
//通过modify添加
alter table user4 modify name nvarchar(20) unique;

非空约束

在上面的例子中已经添加了非空约束: NOT NULL;

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.对于外键约束大家可以联想 省,市 来进行联想 (市必须要依赖于省,只要省还有一个市在引用,那么就不可以删除省,要不然市就没有省了. 那么我们想删除省,必须要将该省下所有的市全部删除之后,才可以删除这个省)

数据库查询练习

数据准备

新建一个查询用的数据库: student_management_system

CREATE DATABASE student_management_system;

选择该数据库: USE student_management_system;

建表

学生表: 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'学生所在的班级'
);

课程表: 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)
);


报错:Failed to open the referenced table 'teacher'
我们先创建teacher表

教师表: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'教师所在的部门'
);

成绩表:score

学号-课程号-成绩

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;
+------------------------------------------+
| Tables_in_student_info_management_system |
+------------------------------------------+
| course                                   |
| score                                    |
| student                                  |
| teacher                                  |
+------------------------------------------+


查看student表结构    DESCRIBE student;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no       | varchar(20) | NO   | PRI | NULL    |       |
| s_name     | varchar(20) | NO   |     | NULL    |       |
| s_sex      | varchar(10) | NO   |     | NULL    |       |
| s_birthday | datetime    | YES  |     | NULL    |       |
| s_class    | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+


查看teacher表结构    DESCRIBE teacher;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no       | varchar(20) | NO   | PRI | NULL    |       |
| t_name     | varchar(20) | NO   |     | NULL    |       |
| t_sex      | varchar(20) | NO   |     | NULL    |       |
| t_birthday | datetime    | YES  |     | NULL    |       |
| t_rof      | varchar(20) | NO   |     | NULL    |       |
| t_depart   | varchar(20) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+


查看course表结构    DESCRIBE course;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no   | varchar(20) | NO   | PRI | NULL    |       |
| c_name | varchar(20) | NO   |     | NULL    |       |
| t_no   | varchar(20) | NO   | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+


查看score表结构    DESCRIBE score;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no      | varchar(20)   | NO   | PRI | NULL    |       |
| c_no      | varchar(20)   | NO   | MUL | NULL    |       |
| sc_degree | decimal(10,0) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

数据准备

--学生表数据
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 |
+------+-------+-----------+

查询联系

1.查询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   |
+------+--------+-------+---------------------+---------+

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

SELECT s_name,s_sex,s_class FROM student;
+--------+-------+---------+
| s_name | s_sex | s_class |
+--------+-------+---------+
| 曾华   | 男    | 95033   |
| 匡明   | 男    | 95031   |
| 王丽   | 女    | 95033   |
| 李军   | 男    | 95033   |
| 王芳   | 女    | 95031   |
| 陆军   | 男    | 95031   |
| 王尼玛 | 男    | 95033   |
| 张全蛋 | 男    | 95031   |
| 赵铁柱 | 男    | 95031   |
+--------+-------+---------+

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

mysql> SELECT DISTINCT(t_depart) from teacher;
+------------+
| t_depart   |
+------------+
| 计算机系   |
| 电子工程系 |
+------------+

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

mysql> SELECT * FROM score WHERE sc_degree BETWEEN 60 AND 80;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
+------+-------+-----------+


mysql> SELECT * FROM score WHERE sc_degree > 60 AND sc_degree < 80;
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 105  | 3-245 |        75 |
| 105  | 6-166 |        79 |
| 109  | 3-105 |        76 |
| 109  | 3-245 |        68 |
+------+-------+-----------+


注意：在sql中，BETWEEN操作符用于选取介于两个值之间的数据范围内的值。其中，“between and”是包括边界值的，即包括两端；“not between”是不包括边界值的。

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

mysql> 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'班或者性别为'女'的同学记录

mysql> 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表中所有的记录

mysql> SELECT * FROM student ORDER BY s_class DESC;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 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 表中所有的数据

mysql> 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 |
+------+-------+-----------+
注：先以c_no进行升序,若c_no相同,则以sc_degree降序

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

mysql> SELECT COUNT(s_no) as total FROM student WHERE s_class = '95031';
+-------+
| total |
+-------+
|     5 |
+-------+

10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)

--查询步骤
1.先找到最高分
最高分: SELECT MAX(sc_degree) FROM score;
2.通过我们找到的最高分的分数来从score中找到我们需要的学生号和课程号
SELECT c_no, s_no FROM score WHERE sc_degree = (最高分)


mysql> SELECT s_no,c_no FROM SCORE WHERE sc_degree
    -> =(SELECT MAX(sc_degree) FROM score);
+------+-------+
| s_no | c_no  |
+------+-------+
| 103  | 3-105 |
+------+-------+


排序 最高分有多个的情况下可能有数据问题
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  |
+-------+------+
有两条数据但是只显示一条,有问题

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

mysql> SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no;
+-------+----------------+
| c_no  | AVG(sc_degree) |
+-------+----------------+
| 3-105 |        85.3333 |
| 3-245 |        76.3333 |
| 6-166 |        81.6667 |
+-------+----------------+

12.查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

mysql> SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no HAVING c_no LIKE '3%' AND COUNT(s_no) > 1;
+-------+----------------+
| c_no  | AVG(sc_degree) |
+-------+----------------+
| 3-105 |        85.3333 |
| 3-245 |        76.3333 |
+-------+----------------+

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

mysql> 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列

mysql> 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列

mysql> SELECT s_no,c_name,sc_degree FROM course,score WHERE course.c_no = score.c_no;
+------+------------+-----------+
| s_no | c_name     | sc_degree |
+------+------------+-----------+
| 103  | 计算机导论 |        92 |
| 105  | 计算机导论 |        88 |
| 109  | 计算机导论 |        76 |
| 103  | 操作系统   |        86 |
| 105  | 操作系统   |        75 |
| 109  | 操作系统   |        68 |
| 103  | 数字电路   |        85 |
| 105  | 数字电路   |        79 |
| 109  | 数字电路   |        81 |
+------+------------+-----------+

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

mysql> SELECT s_name,c_name,sc_degree FROM student,course,score WHERE score.s_no = student.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"还要高的学生信息)

mysql> SELECT s.s_name, sc.sc_degree FROM student AS s,score AS sc WHERE sc.c_no = '3-105' AND sc.s_no = s.s_no AND sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');
+--------+-----------+
| s_name | sc_degree |
+--------+-----------+
| 王丽   |        92 |
| 王芳   |        88 |
+--------+-----------+

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

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

mysql> SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no = '108' OR s_no = '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   |
+------+--------+-------+---------------------+---------+
4 rows in set (0.00 sec)

21.查询张旭教师任课的学生的成绩

mysql> select s.s_no, s.s_name, sc.sc_degree from student as s,score as sc where c_no in (select c_no from teacher,course where t_name = '张旭' and teacher.t_no = course.t_no) and s.s_no = sc.s_no;
+------+--------+-----------+
| s_no | s_name | sc_degree |
+------+--------+-----------+
| 103  | 王丽   |        85 |
| 105  | 王芳   |        79 |
| 109  | 赵铁柱 |        81 |
+------+--------+-----------+

22.查询选修课程的同学人数多余 3 人的教师姓名

1.查看数据
mysql> 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 |
+------+-------+-----------+
2 查看大于5的
mysql> select c_no,count(s_no) from score group by c_no having count(s_no) > 5;
Empty set (0.00 sec)
3 查询选修课程的同学人数多余 5 人的教师姓名
mysql> 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)

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

mysql> 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   |
+------+--------+-------+---------------------+---------+

我在写的时候,写成了:查询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

mysql> SELECT DISTINCT(c_no) FROM score WHERE sc_degree > 85;
+-------+
| c_no  |
+-------+
| 3-105 |
| 3-245 |
+-------+


升级版:知道c_no还不行,我们还需要看到c_name 以及对应的老师
mysql> SELECT course.c_no, t_name,c_name FROM teacher,course WHERE teacher.t_no = course.t_no AND course.c_no IN (SELECT DISTINCT(c_no) FROM score WHERE sc_degree > 85);
+-------+--------+------------+
| c_no  | t_name | c_name     |
+-------+--------+------------+
| 3-105 | 王萍   | 计算机导论 |
| 3-245 | 李诚   | 操作系统   |
+-------+--------+------------+

25.查出所有'计算机系' 教师所教课程的成绩表

mysql> SELECT t.t_no,t.t_name,c.c_name, sc.sc_degree FROM teacher AS t, course AS c, score AS sc WHERE t.t_depart = '计算机系' AND t.t_no = c.t_no AND c.c_no = sc.c_no;
+------+--------+------------+-----------+
| t_no | t_name | c_name     | sc_degree |
+------+--------+------------+-----------+
| 804  | 李诚   | 操作系统   |        86 |
| 804  | 李诚   | 操作系统   |        75 |
| 804  | 李诚   | 操作系统   |        68 |
| 825  | 王萍   | 计算机导论 |        92 |
| 825  | 王萍   | 计算机导论 |        88 |
| 825  | 王萍   | 计算机导论 |        76 |
+------+--------+------------+-----------+

26.查询'计算机系'与'电子工程系' 不同职称的教师的name和rof

1. 查询计算机系与电子工程系职称不同的老师
mysql>  SELECT * FROM teacher as t 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 | 副教授 | 计算机系 |
+------+--------+-------+---------------------+--------+----------+
2. 查询电子工程系与计算机系职称不同的老师
mysql> 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   |
+------+--------+-------+---------------------+-------+------------+
| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师  | 电子工程系 |
+------+--------+-------+---------------------+-------+------------+
3 查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
SELECT * FROM teacher as t 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从高到地次序排序

1.查询选修编号为 3-105 的课程
mysql> SELECT * FROM score WHERE c_no = '3-105';
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-105 |        92 |
| 105  | 3-105 |        88 |
| 109  | 3-105 |        76 |
+------+-------+-----------+
2. 查询选修编号为 3-245 的课程
mysql> SELECT * FROM score WHERE c_no = '3-245';
+------+-------+-----------+
| s_no | c_no  | sc_degree |
+------+-------+-----------+
| 103  | 3-245 |        86 |
| 105  | 3-245 |        75 |
| 109  | 3-245 |        68 |
+------+-------+-----------+
3. 至少使用ANY关键词进行查询
mysql> 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 |
| 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 |
| 李诚   | 男  | 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.查询成绩比该课程平均成绩低的同学的成绩表

mysql> SELECT ssc.s_no,sc.c_no, ssc.sc_degree FROM (SELECT c_no, AVG(sc_degree) as avgscore FROM score GROUP BY c_no) as sc, score as ssc WHERE ssc.c_no = sc.c_no AND  ssc.sc_degree < sc.avgscore;
+------+-------+-----------+
| 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 |
+------+-------+-----------+

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名男生的班号

mysql> SELECT s_class,COUNT(s_no) FROM student WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_no) > 1;
+---------+-------------+
| s_class | COUNT(s_no) |
+---------+-------------+
| 95033   |           3 |
| 95031   |           4 |
+---------+-------------+

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

mysql> 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   |
+------+--------+-------+---------------------+---------+

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

mysql> SELECT s_name,YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;
+--------+------+
| s_name | age  |
+--------+------+
| 曾华   |   44 |
| 匡明   |   46 |
| 王丽   |   45 |
| 李军   |   45 |
| 王芳   |   46 |
| 陆军   |   47 |
| 王尼玛 |   45 |
| 张全蛋 |   46 |
| 赵铁柱 |   47 |
+--------+------+

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

mysql> 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表中的全部记录

mysql> SELECT * FROM student ORDER BY s_class DESC, s_birthday;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday          | s_class |
+------+--------+-------+---------------------+---------+
| 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

mysql> 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.查询所有选修'计算机导论'课程的'男'同学的成绩表

mysql> 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 |
+------+-------+-----------+
| 109  | 3-105 |        76 |
+------+-------+-----------+
进阶:显示出s_name,c_name 替代之前的 s_no and c_no
mysql> 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 |
+--------+------------+-----------+
| 赵铁柱 | 计算机导论 |        76 |
+--------+------------+-----------+

mysql

文章转载自Geeker工作坊，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

一周学会MySQL