【数据库】【MySQL】操作题 练习

前言-语法

创建表格的语法为：CREATE TABLE 表名(字段的定义，各字段用逗号分隔);

插入记录的语法为：INSERT INTO 表名 VALUES(插入的信息，各信息用逗号分隔);

查询所有记录的语法为：SELECT*FROM 表名;

查询部分记录的语法为（一）：SELECT*FROM 表名 LIMIT 索引区间;

注意：索引区间从0开始。

查询部分记录的语法为（二）：SELECT 字段名集合，各字段用逗号分隔 FROM 表名;

说明：其实就是最后输出的字段列可选，不过如果想显示全部就用星号*。

查询指定信息的记录的语法为（三）：SELECT * FROM 表名 WHERE 字段名 IN (字段包含的信息集合，各指定信息用逗号分隔);

查询指定区间信息的记录的语法为（四）：SELECT * FROM 表名 WHERE 字段名 BETWEEN 左区间 AND 右区间;

字段重命名使用关键字：AS。

计算有多少(统计数据)：关键字GROUP BY。语法：SELECT 字段集合 FROM 表名 GROUP BY 要统计的那个字段名;

说明：一般会对字段集合中的某字段进行COUNT或其它操作，关键字GROUP BY与关键字COUNT往往会并用。

对数值型记录可使用函数直接找出最大最小值：MAX(字段名)，即可找出该字段中的最大值。

数据库查询中，GROUP BY 子句通常与聚合函数一起使用，以便对一组数据进行汇总。聚合函数可以对一组值执行计算，并返回单个值。以下是一些常用的聚合函数及其用法：

1. COUNT：计算行数。例如：SELECT COUNT(*) FROM table_name; 计算表中的总行数。

2. SUM：计算数值列的总和。例如：SELECT SUM(column_name) FROM table_name; 计算某列的总和。

3. AVG：计算数值列的平均值。例如：SELECT AVG(column_name) FROM table_name; 计算某列的平均值。

4. MIN：找出数值列的最小值。例如：SELECT MIN(column_name) FROM table_name; 找出某列的最小值。

5. MAX：找出数值列的最大值。例如：SELECT MAX(column_name) FROM table_name; 找出某列的最大值。

开始练习

---

 1.创建student和score表

CREATE TABLE student(
id INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(20),
birth YEAR,
department VARCHAR(20) NOT NULL,
address VARCHAR(50)
);CREATE TABLE score(
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
stu_id INT(10) NOT NULL,
c_name VARCHAR(20),
grade INT(10)
);

2.为student和score表增加记录

INSERT INTO student VALUES(901, '张老大', '男', 1985, '计算机系', '北京市海淀区');
INSERT INTO student VALUES(902, '张老二', '男', 1986, '中文系', '北京市昌平区');
INSERT INTO student VALUES(903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES(904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES(905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES(906,'王六', '男',1988,'计算机系', '湖南省衡阳市');INSERT INTO score VALUES(NULL,901,'计算机',98);
INSERT INTO score VALUES(NULL,901,'英语',80);
INSERT INTO score VALUES(NULL,902,'计算机',65);
INSERT INTO score VALUES(NULL,902,'中文',88);
INSERT INTO score VALUES(NULL,903,'中文',95);
INSERT INTO score VALUES(NULL,904,'计算机',70);
INSERT INTO score VALUES(NULL,904,'英语',92);
INSERT INTO score VALUES(NULL,905,'英语',94);
INSERT INTO score VALUES(NULL,906,'计算机',90);
INSERT INTO score VALUES(NULL,906,'英语',85);

 3.查询student表的所有记录

SELECT * FROM student;

4.查询student表的第2条到第4条记录

注意：第一条记录的索引为0，所以第2条到第4条记录索引区间为1到3。

SELECT * FROM student LIMIT 1,3;

5.从student表查询所有学生的学号id、姓名name和院系department的信息

SELECT id,name,department FROM student;

6.从student表中查询计算机系和英语系的学生信息

SELECT * FROM student WHERE department IN ('计算机系', '英语系')

7.从student表中查询年龄18~22岁的学生信息

说明：2013-birth（2013减出生年份）表示计算每位学生在2013年时的年龄。

写法一：

SELECT id,name,sex,2013-birth AS age,department,address FROM student WHERE 2013-birth BETWEEN 18 AND 22;

写法二：

SLECT id,name,sex,2013-birth AS sge,department,address FROM student WHERE 2013-birth>=18 AND 2013-birth<=22;

8.从student表中查询每个院系有多少人

SELECT department,COUNT(id) FROM student GROUP BY department;

9.从score表中查询每个科目的最高分

SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

说明：这里是函数MAX()和关键字GROUP BY 的并用。

10.查询李四的考试科目c_name和考试成绩grade

SELECT c_name,grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');

说明：该语句的逻辑是

1. 子查询 (SELECT id FROM student WHERE name = '李四')：首先从student表中找出名为“李四”的学生的学号（id）。
2. 外层查询：使用子查询得到的学生学号，从score表中查询对应的考试科目（c_name）和成绩（grade）。

11.用连接的方式查询所有学生的信息和考试信息

SELECT student.id,name,sex,birth,department,address,c_name,grade
FROM student,score WHERE student.id=score.stu_id;

12.计算每个学生的总成绩

SELECT student.id,name,SUM(grade) FROM student,score
WHERE student.id=score.stu_id GROUP BY id;

13.计算每个考试科目的平均成绩

SELECT c_name,AVG(grade) FROM score GROUP BY c_name;

14.查询计算机成绩低于95的学生信息

SELECT * FROM student WHERE id IN 
(SELECT stu_id FROM score WHERE c_name='计算机' and grade<95)

15.查询同时参加计算机和英语考试的学生的信息

16.将计算机考试成绩按从高到低进行排序

17.从student表和score表中查询出学生的学号，然后合并查询结果

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩