mysql测试小例2

CREATE TABLE CLASS(
     classid INT PRIMARY KEY,
     ‘subject’ VARCHAR(32) NOT NULL DEFAULT '',
     deptname VARCHAR(32),
     enrooltime INT NOT NULL DEFAULT 2000,
     num INT NOT NULL DEFAULT 0,
     FOREIGN KEY(deptname) REFERENCES DEPARTMENT(deptname)
); 
 
 CREATE TABLE STUDENT(
     studentid INT PRIMARY KEY,
     ‘name’ VARCHAR(32) NOT NULL DEFAULT '',
     age INT NOT NULL DEFAULT 0,
     classid INT,
     FOREIGN KEY(classid) REFERENCES CLASS(classid)
); 

CREATE TABLE DEPARTMENT(
       departmentid VARCHAR(32) PRIMARY KEY,
       deptname VARCHAR(32) UNIQUE NOT NULL
);

INSERT INTO DEPARTMENT VALUES('001','数学');
INSERT INTO DEPARTMENT VALUES('002','计算机');
INSERT INTO DEPARTMENT VALUES('003','化学');
INSERT INTO DEPARTMENT VALUES('004','中文');
INSERT INTO DEPARTMENT VALUES('005','经济');

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

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

SELECT * FROM STUDENT
SELECT * FROM CLASS
SELECT * FROM DEPARTMENT

SELECT * FROM STUDENT
WHERE ‘name’ LIKE '李%'

SELECT COUNT(*) AS nums,deptname FROM CLASS
GROUP BY deptname HAVING nums>1

SELECT SUM(num),deptname FROM CLASS 
GROUP BY deptname;

SELECT departmentid,tmp.deptname FROM DEPARTMENT,(SELECT SUM(num) AS nums,deptname FROM CLASS 
GROUP BY deptname) tmp
WHERE tmp.deptname = DEPARTMENT.deptname AND nums>=30

INSERT INTO DEPARTMENT VALUES('006','物理');

-- 学生张三退学
-- 1张三所在班级人数-1 将张三从学生表删除 3 使用事务控制

START TRANSACTION;
UPDATE CLASS SET num= num-1
WHERE classid=(SELECT classid FROM STUDENT WHERE ‘name’='张三')
DELETE FROM STUDENT WHERE ‘name’='张三';
COMMIT
SELECT * FROM STUDENT
SELECT * FROM CLASS