mysql 综合练习

   
-- 设环境如下 ： 一个系有多个专业，一个专业一年只招一个班，一个班有多个学生
-- 建立 系，班级，学生的数据库
-- 关系模式为：班：CLASS （班号classid,专业名subject,系名deptname
-- 入学年份enrolltime,人数num）
-- 学生：STUDENT (学号studentid,姓名name，年龄age，班号classid)
-- 系：DEPARTMENT（系号departmentid,系名deptname）
-- 完成以下功能
-- 建表声明主外码，department是唯一约束，学生姓名不能空，自行插入数据
CREATE TABLE department (
    departmentid VARCHAR(32) PRIMARY KEY ,
    deptname VARCHAR(32)  UNIQUE NOT NULL);
DROP TABLE department

CREATE TABLE class(
    classid INT PRIMARY KEY ,
    `subject` VARCHAR(32) NOT NULL,
    deptname VARCHAR(32),
    enrolltime YEAR,
    num INT NOT NULL DEFAULT 0,
FOREIGN KEY (deptname) REFERENCES department(deptname));
DROP TABLE class

CREATE TABLE student (
    studentid INT PRIMARY KEY ,
    `name` VARCHAR(32) NOT NULL DEFAULT '',
    age INT,
    classid INT ,
FOREIGN KEY(classid) REFERENCES class(classid));
DROP TABLE student

INSERT INTO class VALUES
    (101,'软件','计算机','1995',20),
    (102,'微电子','计算机','1996',30),
    (111,'无机化学','化学','1995',29),
    (112,'高分子化学','化学','1996',25),
    (121,'统计数学','数学','1995',20),
    (131,'现代语言','中文','1996',20),
    (141,'国际贸易','经济','1997',30),
    (142,'国际金融','经济','1996',14);
    

INSERT INTO student VALUES
    (8101,'张三',18,101),
    (8102,'钱四',16,121),
    (8103,'王玲',17,131),
    (8105,'李飞',19,102),
    (8109,'赵四',18,141),
    (8110,'李可',20,142),
    (8201,'张飞',18,111),
    (8302,'周瑜',16,112),
    (8203,'王亮',17,111),
    (8305,'董庆',19,102),
    (8409,'赵龙',18,101),
    (8510,'李丽',20,142);

INSERT INTO department VALUES
    ('001','数学'),('002','计算机'),
    ('003','化学'),('004','中文'),
    ('005','经济');
    
-- 找出所有姓李的学生
SELECT * FROM student
    WHERE `name` LIKE '李%';
-- 列出所有开设超过一个专业的系的名字
SELECT deptname,COUNT(*) AS c FROM class
    GROUP BY deptname
    HAVING c > 1;
-- 列出人数>=30的系的编号和名字(先查各个系多少人做成临时表，使用联合查询)
SELECT departmentid,department.deptname ,tem.nums
    FROM department ,(SELECT deptname,SUM(num)AS nums FROM class
                GROUP BY deptname
                HAVING nums >= 30) tem
    WHERE department.deptname = tem.deptname

-- 学校又新增加了一个物理系，编号为006
INSERT INTO department VALUES(006,'物理');
-- 张三退学，更新相关表 人数减一，两个语句，用事务保持一致
START TRANSACTION ;#开始事务
# 人数减少
UPDATE  class SET num = num - 1
    WHERE classid = (SELECT classid FROM student

#删除学生                where `name` = '张三');
DELETE FROM student
    WHERE `name` = '张三';
SELECT * FROM class
SELECT * FROM student
COMMIT;#提交事务