数据库操作-2(生成随机数据)

1、SQL索引的建立:

CREATE INDEX age_index
ON Student_2(age)

DROP INDEX age_index on Student_2;

--
SELECT sno,sname 
FROM Student_2
WHERE age=19;

2、随机生成学生

SET GLOBAL log_bin_trust_function_creators=TRUE;
DROP TABLE IF EXISTS Student;
CREATE TABLE Student( 
  sno char(5) PRIMARY KEY, 
  sname char(8) not null, 
  sex char(2) CHECK(sex in ('M','F')), 
  age SMALLINT, dept char(20) 
);

DROP FUNCTION IF EXISTS rand_sex;
DELIMITER $$ 
CREATE FUNCTION rand_sex()
RETURNS VARCHAR(2)
BEGIN
    RETURN ELT(CEILING(RAND()*2), "M","F");
END $$
DELIMITER;

DROP FUNCTION IF EXISTS rand_age;
DELIMITER $$ 
CREATE FUNCTION rand_age()
RETURNS INT(5)
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(18+rand()*4);
RETURN i;
END$$
DELIMITER;

DROP FUNCTION IF EXISTS rand_dept;
DELIMITER $$ 
CREATE FUNCTION rand_dept()
RETURNS VARCHAR(10)
BEGIN 
	RETURN ELT(CEILING(RAND()*4), "CS","SE", "IoT", "AI");
END$$
DELIMITER;

DROP PROCEDURE IF EXISTS initStu;
DELIMITER $$  
CREATE PROCEDURE initStu()  
BEGIN  
    DECLARE i INT DEFAULT 1;
		WHILE i < 20 DO	
				INSERT INTO Student(sno, sname,sex, age, dept) 
				VALUES(i,'stu_name', rand_sex(), rand_age(), rand_dept());
        SET i = i + 1;
		END WHILE;
END$$ 
CALL initStu(); 

3、随机生成课程表

SET GLOBAL log_bin_trust_function_creators=TRUE;
DROP TABLE IF EXISTS Course;
CREATE TABLE Course(
	cno char(4) PRIMARY KEY,
	cname char(10) not null,
	pcno char(4),
	credit SMALLINT
-- 	FOREIGN KEY(pcno) REFERENCES Course(cno)
);

DROP FUNCTION IF EXISTS rand_pcno;
DELIMITER $$ 
CREATE FUNCTION rand_pcno()
RETURNS INT(5)
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(1+rand()*3);
RETURN i;
END$$
DELIMITER;

DROP FUNCTION IF EXISTS rand_credit;
DELIMITER $$ 
CREATE FUNCTION rand_credit()
RETURNS INT(5)
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(1+rand()*4);
RETURN i;
END$$
DELIMITER;

DROP PROCEDURE IF EXISTS initCourse;
DELIMITER $$  
CREATE PROCEDURE initCourse()  
BEGIN  
    DECLARE i INT DEFAULT 5;
		WHILE i < 20 DO	
				INSERT INTO Course(cno, cname, pcno, credit) 
				VALUES(i,'c_name', rand_pcno() ,rand_credit());
        SET i = i + 1;
		END WHILE;
END$$ 

INSERT INTO Course(cno, cname, pcno, credit) VALUES(1,'math','',5);
INSERT INTO Course(cno, cname, pcno, credit) VALUES(2,'C','',3);
INSERT INTO Course(cno, cname, pcno, credit) VALUES(3,'Cpp','',3);
INSERT INTO Course(cno, cname, pcno, credit) VALUES(4,'Java','',4);

CALL initCourse(); 

4、随机生成SC(不加主码、外码)

SET GLOBAL log_bin_trust_function_creators=TRUE;
DROP TABLE IF EXISTS SC2;
CREATE TABLE SC2(
	id INT,
	sno CHAR(4),
	cno CHAR(10),
	grade INT
);

DROP FUNCTION IF EXISTS rand_sno;
DELIMITER $$ 
CREATE FUNCTION rand_sno()
RETURNS INT(5)
BEGIN 
	RETURN ELT(CEILING(RAND()*6), "001","002", "003", "004", "005", "006");
RETURN i;
END$$
DELIMITER;

DROP FUNCTION IF EXISTS rand_cno;
DELIMITER $$ 
CREATE FUNCTION rand_cno()
RETURNS INT(5)
BEGIN 
	RETURN ELT(CEILING(RAND()*3), "001","002", "003");
RETURN i;
END$$
DELIMITER;

DROP FUNCTION IF EXISTS rand_grade;
DELIMITER $$ 
CREATE FUNCTION rand_grade()
RETURNS INT(5)
BEGIN 
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(50+rand()*40);
RETURN i;
END$$
DELIMITER;

DROP PROCEDURE IF EXISTS initSC;
DELIMITER $$  
CREATE PROCEDURE initSC()  
BEGIN  
    DECLARE i INT DEFAULT 1;
		WHILE i < 20 DO	
				INSERT INTO SC2(id, sno, cno, grade)
				VALUES(i , rand_sno(),  rand_cno() ,rand_grade());
        SET i = i + 1;
		END WHILE;
END$$ 
CALL initSC();