生活随笔
收集整理的這篇文章主要介紹了
Mysql 小练习
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
設學校環境如下:一個系有若干個專業，每個專業一年只招一個班，每個班有若干個學生。 現要建立關于系、學生、班級的數據庫，關系模式為： 班CLASS （班號classid，專業名subject，系名deptname，入學年份enrolltime，人數num） 學生STUDENT （學號studentid，姓名name，年齡age，班號classid） 系 DEPARTMENT （系號departmentid，系名deptname） 試用SQL語言完成以下功能：??
--(1) 建表，在定義中要求聲明：
-- （1）每個表的主外碼。
-- （2）deptname是唯一約束。
-- （3）學生姓名不能為空。-- 創建表 系 DEPARTMENT （系號departmentid，系名deptname）
CREATE TABLE DEPARTMENT (departmentid VARCHAR(32) PRIMARY KEY,deptname VARCHAR(32) UNIQUE NOT NULL);-- 班CLASS （班號classid，專業名subject，系名deptname，入學年份enrolltime，人數num）
CREATE TABLE `class` (classid INT PRIMARY KEY,`subject` VARCHAR(32) NOT NULL DEFAULT '',deptname VARCHAR(32) , -- 外鍵字段，在表定義后指定enrolltime INT NOT NULL DEFAULT 2000,num INT NOT NULL DEFAULT 0,FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname));-- 學生STUDENT （學號studentid，姓名name，年齡age，班號classid）
CREATE TABLE hsp_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));-- 添加測試數據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 department
SELECT * FROM class
SELECT * FROM student-- (3) 完成以下查詢功能-- 3.1 找出所有姓李的學生。
-- 查表 student , like
SELECT * FROM studentWHERE `name` LIKE '李%' -- 3.2 列出所有開設超過1個專業的系的名字。
-- 1. 先查詢各個系有多少個專業
SELECT COUNT(*) AS nums, deptname FROM classGROUP BY deptname HAVING nums > 1-- 3.3 列出人數大于等于30的系的編號和名字。
-- 1. 先查出各個系有多少人, 并得到 >= 30 的系SELECT SUM(num) AS nums, deptname FROM class GROUP BY deptname HAVING nums >= 30-- 2. 將上面的結果看成一個臨時表 和 department 聯合查詢即可SELECT tmp.*, department.departmentidFROM department , (SELECT SUM(num) AS nums, deptname FROM class GROUP BY deptname HAVING nums >= 30) tmp WHERE department.deptname = tmp.deptname;-- (4) 學校又新增加了一個物理系，編號為006
-- 添加一條數據
INSERT INTO department VALUES('006','物理系');
-- (5) 學生張三退學，請更新相關的表-- 分析：1. 張三所在班級的人數-1 2. 將張三從學生表刪除 3. 需要使用事務控制-- 開啟事務
START TRANSACTION;
-- 張三所在班級的人數-1
UPDATE class SET num = num - 1WHERE classid = (SELECT classid FROM student WHERE NAME = '張三');DELETE FROM studentWHERE NAME = '張三';-- 提交事務
COMMIT;SELECT * FROM student;
SELECT * FROM class
