2018-10-31

数据库系统

数据库学习记录

创建表

CREATE TABLE STUDENT( Sno INT PRIMARY KEY, Sname CHAR(10), Sage INT, Sdept CHAR(10), Classno CHAR(15), Comid INT )
CREATE TABLE Class(
  Classno CHAR(15) PRIMARY KEY,
  Domainid CHAR(10), 
  inyear INT,
  Sdept CHAR(10),
  Snum INT
)
CREATE TABLE department(
  Sdepno INT PRIMARY KEY,
  Sdept CHAR(10),
  Place CHAR(20),
  Snum INT
)
CREATE TABLE Association( Asid CHAR(15) PRIMARY KEY, Asname CHAR(10), Year INT, Place CHAR(20) )

生成随机的数据

# -*- coding: utf-8 -*-
"""
Created on Wed Nov 21 19:25:08 2018
@author: 宝宝天龙
"""
import random
import xlrd
import xlwt
from math import *
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet('test', cell_overwrite_ok=True)
xing = '赵钱孙李周吴郑王田赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤'
ming1 = '黄初三年余朝京师还济洛川古人有言斯水之名曰宓妃感宋玉对楚王神女之事遂作斯赋其辞曰'
ming2 = '余从京域言归东藩背伊阙越轘辕经通谷陵景山日既西倾车殆马烦尔乃税驾乎蘅皋秣驷乎芝田容与乎阳林流眄乎洛川于是精移神 骇忽焉思散俯则未察仰以殊观睹一丽人于岩之畔乃援御者而告之曰尔有觌于彼者乎彼何人斯若此之艳也御者对曰臣闻河洛之神名曰宓妃然则君王所见无乃是乎其状若何臣愿闻之'
dept = ["CS", "EE", "SE", "MA", ]
commity = ["zhu1", "zhu2", "zhu3", "zhu4"]
length = 10000
for i in range(1000):
    id = random.randint(16030100000, 16030190000)
    sheet.write(i, 1, id)
    sheet.write(i, 2, random.choice(xing)+random.choice(ming1)+random.choice(ming2))
    sheet.write(i, 3, random.randint(18, 23))
    sheet.write(i, 4, random.choice(dept))
    sheet.write(i, 5, floor(id/10000)%100)
    sheet.write(i, 6, random.choice(commity))
book.save(r'E:\database\stu.xls')
``` 
# 创建表
``` sql
CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2),NUM INT );
CREATE TRIGGER ins_sum BEFORE INSERT 
ON account FOR EACH ROW 
UPDATE 
  `employee` 
SET
  `Age` = new.acct_num + 1 
WHERE `ID` = new.acct_num ;
INSERT INTO account(acct_num,amount) VALUES(1,2.00);
SELECT * FROM `employee`
create table dept (deptno char(4) primary key, deptname char(4), deptp char(20), deptnum int)
create table dept_major( deptno char(4) , majno char(4) primary key, foreign key(deptno) references dept(deptno))
create table dept_apart(deptno char(4) primary key , apartno varchar(6))
create table association(assno char(10) primary key, assname varchar(20), assyear int, assp varchar(20))
create table class(classno varchar(10) primary key, inyear int, deptno char(4), classnum int, foreign key(deptno) references dept(deptno))
//添加约束条件
create table student(sno varchar(20) primary key, sname varchar(10), sage int, sex ENUM('男', '女') COLLATE utf8_estonian_ci, classno varchar(10), foreign key(classno) references class(classno) on UPDATE CASCADE)
ALTER TABLE `student` ADD UNIQUE(`sno`);
create table stu_ass(sno varchar(20), assno char(10), sinyear int, foreign key(sno) references student(sno), foreign key(assno) references association(assno))

创建存储过程

BEGIN
	DECLARE local_sum1 int;
	DECLARE Deptno1 char(4);
	DECLARE Deptname1 char(4);
	DECLARE stu_sum1 int;
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE My_Cursor CURSOR FOR (SELECT deptno,deptname,deptnum FROM dept);
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN My_Cursor;
	DROP TABLE IF EXISTS temp;
    CREATE TABLE temp(
      deptno CHAR(4),
      deptname CHAR(4),
      old_num INT,
      real_num INT
    );
		myLoop:LOOP
			FETCH My_Cursor into Deptno1,Deptname1,local_sum1;
			IF(done) THEN
				LEAVE myLoop;
			END IF;
			SELECT COUNT(sno) INTO stu_sum1 
			FROM student,class 
			WHERE student.classno = class.classno
				 AND class.deptno = Deptno1 
			GROUP BY class.deptno;
			IF(stu_sum1<>local_sum1) THEN
				UPDATE dept SET deptnum = stu_sum1;
				INSERT INTO temp(deptno,deptname,old_num,real_num) VALUES (Deptno1,Deptname1,local_sum1,stu_sum1);
				
			END IF;
		END LOOP myLoop;
		CLOSE My_Cursor;
END

php中制作html表格

php制作数据表格

未解决的问题

本文标题:数据库系统

文章作者:Babydragon

发布时间:2018-10-31, 13:12:19

最后更新:2018-12-09, 17:36:56

原始链接:http://baolintian.github.io/2018/10/31/数据库系统/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。