SQL热身测试

#创建数据库
CREATE DATABASE test;#切换数据库
USE test;
#在test数据库中创建一个student的表格
CREATE TABLE student (
    `Id` INT ( 10 ) PTIMARY KEY NOT NULL AUTO_INCTRMENT COMMENTT `学号`,
    `Name` VARCHAR ( 20 ) NOT NULL COMMENT `姓名`， `Sex` VARCHAR ( 4 ) COMMENT '性别',
    `Birth` YEAR COMMENT '出生年月',
    `Department` VARCHAR ( 20 ) NOT NULL COMMENT '院系',
    `Address` VARCHAR ( 21 ) COMMENT '家庭住址' 
);
CREATE TABLE Scroe (
    `Id` INT ( 10 ) PRIMARY KEY NOT NULL auto_increment COMMENT '编号',
    `stu_id` INT ( 10 ) NOT NULL COMMENT '学号',
    `c_name` VARCHAR ( 20 ) COMMENT '课程名',
    `grade` INT ( 10 ) COMMENT '分数' 
);
#向student表中添加数据
INSERT INTO student
VALUES
    ( 901, '张老大', '男', 1985, '计算机系', '北京市海淀区' );
INSERT INTO student
VALUES
    ( 902, '张老二', '男', 1986, '中文系', '北京市昌平区' ),
    ( 903, '张三', '女', 1990, '中文系', '湖南省永州市' ),
    ( 904, '李四', '男', 1990, '英语系', '辽宁省阜新市' ),
    ( 905, '王五', '女', 1991, '英语系', '福建省厦门市' ),
    ( 906, '王六', '男', 1988, '计算机系', '湖南省衡阳市' );#向score表里面添加数据
INSERT INTO scroe
VALUES
    ( NULL, 901, '计算机', 98 ),
    ( NULL, 901, '英语', 80 ),
    ( NULL, 902, '计算机', 65 ),
    ( NULL, 902, '中文', 88 ),
    ( NULL, 903, '中文', 95 ),
    ( NULL, 904, '计算机', 70 ),
    ( NULL, 904, '英语', 92 ),
    ( NULL, 905, '英语', 94 ),
    ( NULL, 906, '计算机', 90 ),
    ( NULL, 906, '英语', 85 );
        #2、查询student表中的第2到第4条数据
SELECT
    * 
FROM
    student 
WHERE
    id BETWEEN 902 
    AND 904;
    
#3、从Student表查询所有学生的学号，姓名和院系
SELECT
    id,
    `name`,
    Department 
FROM
    student;
    
#4、从Student表中查询计算机系和英语系的学生
SELECT
    * 
FROM
    student 
WHERE
    Department IN ( '计算机系', '英语系' );
    
#5、从Student表中查询年龄在18~32岁的学生信息
SELECT
    * 
FROM
    student 
WHERE
    ( YEAR ( NOW( ) ) - Birth ) BETWEEN 18 
    AND 32;
    
#6、从student表中查询每个院系有多少人
SELECT
    Department AS '院系',
    COUNT( Id ) AS '人数' 
FROM
    student 
GROUP BY
    Department;
    
#7、从Score表中查询每个科目的最高分
SELECT
    c_name AS '科目',
    MAX( grade ) AS '最高分' 
FROM
    scroe 
GROUP BY
    c_name;
    
#8、查询李四的考试科目
SELECT
    `Name` AS '姓名',
    c_name AS '考试科目' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id 
WHERE
    student.`Name` = '李四';


#9、用连接的方式查询所有学生的姓名、院系、科目和考试成绩
SELECT
    `Name` AS '姓名',
    Department AS '院系',
    c_name AS '科目',
    grade AS '考试成绩' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id;#10、计算每个学生的总成绩
SELECT
    `Name` AS '姓名',
    SUM( grade ) AS '总成绩' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id 
GROUP BY
    `Name`;
    
#11、计算每个考试科目的平均成绩
SELECT
    c_name AS '科目',
    AVG( grade ) AS '平均成绩' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id 
GROUP BY
    c_name;
    
#12、查询计算机成绩低于95分的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN ( SELECT stu_id FROM scroe WHERE grade < 95 AND c_name = '计算机' );
    
#13、查询同时参加计算机和英语考试的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN (
    SELECT
        stu_id 
    FROM
        scroe 
    WHERE
        stu_id IN (
        SELECT
            x 
        FROM
            ( SELECT stu_id AS 'x' FROM scroe WHERE c_name = '英语' ) AS a
            JOIN ( SELECT stu_id AS 'y' FROM scroe WHERE c_name = '计算机' ) AS b ON a.x = b.y 
        ) #使用自连接的方式，查找交集
        
    );
    
#14、将计算机考试成绩按照从高到低进行排序
SELECT
    `Name` AS '姓名',
    grade AS '成绩' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id 
WHERE
    c_name = '计算机' 
ORDER BY
    grade DESC;
    
#15、从student表和score表中查询出学生的学号，然后合并查询结果
SELECT DISTINCT
    student.Id AS '学号',
    `Name` AS '姓名' 
FROM
    student
    LEFT JOIN scroe ON scroe.stu_id = student.Id;

#16、查询姓张或者姓王的同学的姓名、院系和考试科目以及成绩（提示，模糊查询关键字like 例如查询以A开头的姓名  selec * from 表名 where name like ‘A%’）
SELECT
    `Name` AS '姓名',
    Department AS '院系',
    c_name AS '考试科目',
    grade AS '成绩' 
FROM
    student
    JOIN scroe ON scroe.stu_id = student.Id 
WHERE
    student.`name` LIKE '张%' 
    OR student.`name` LIKE '王%';#17、查询都是湖南的学生的姓名、年龄、院系和考试科目以及成绩
SELECT
    `NAME` AS '姓名',
    ( YEAR ( NOW( ) ) - Birth ) AS '年龄',
    Department AS '院系',
    c_name AS '考试科目',
    grade AS '成绩' 
FROM
    scroe
    JOIN student ON scroe.stu_id = student.Id 
WHERE
    Address LIKE '%湖南%';
    
#14题的另一种做法：查询同时参加计算机和英语考试的学生信息
SELECT
    * 
FROM
    student 
WHERE
    id IN ( SELECT stu_id FROM scroe WHERE stu_id IN ( SELECT stu_id FROM scroe WHERE c_name = '英语' ) AND c_name = '计算机' );#使用and求交集