1, Computer purpose

1. Master functions and user-defined functions;

2. Master the storage process;

3. Master trigger;

4. Master events;

5. Master preprocessing statements.

2, Computer content

(1) Create a user-defined function named MyFun without parameters. The function is to query the course name of C3 from data table C and call the function.

DELIMITER $$
CREATE FUNCTION MyFun() RETURNS VARCHAR(20)
BEGIN
    RETURN(SELECT CN FROM C WHERE CNo='C3');
END
$$
DELIMITER ;

SELECT MyFun();

(2) Create a user-defined function named InsertFun with parameters. The function is to insert course information into data table C and call the function.

DELIMITER $$
CREATE FUNCTION insertFun(CnName VARCHAR(20),CNo VARCHAR(10),CT INT))
RETURNS VARCHAR(20)
BEGIN
    INSERT C(CN,CNo,CT) VALUES(CnName,CNo,CT);
    RETURN ('OK')
END
$$
DELIMITER ;
SELECT insertFun('ROSE','C8',64);

(3) Create a stored procedure named MyProc without parameters. The function of the stored procedure is to query the information of all male students from the data table S and execute the stored procedure.

DELIMITER $$
CREATE PROCEDURE MyProce()
BEGIN
    SELECT * FROM S WHERE Sex='male';
END
$$
DELIMITER ;
CALL MyProce();

(4) Create a stored procedure with parameters named InsertRecord. The function of the stored procedure is to query the information of a classmate according to the student number from the data table S. The value of the student number is provided by the parameters and execute the stored procedure.

DELIMITER $$
CREATE PROCEDURE insertRecord(IN Ssno VARCHAR(20))
BEGIN
    SELECT SN,Dept FROM S WHERE Sno IN(Ssno);
END
$$
DELIMITER ;
CALL insertRecord('S1');

(5) Create a trigger. The function of the trigger is: when a student is deleted according to the student number in the student table S, all the course selection records in the student course selection table SC are also deleted.

DELIMITER $$
CREATE TRIGGER delete_stu BEFORE DELETE
ON S FOR EACH ROW
BEGIN
 DELETE FROM SC WHERE Sno=OLD.Sno;
END
$$
DELIMITER ;
DELETE FROM S WHERE Sno='S1';

(6) Create a trigger that ensures that when a new record is added to the student course selection table SC, the student number Sno of the new student must already exist in the student table S. if it does not exist, an error will be prompted: "there is no basic information of the student in the student table s, refuse to insert!".

DELIMITER $$
CREATE TRIGGER insert_sc_data BEFORE insert
ON SC FOR EACH ROW
BEGIN
 IF NOT EXISTS (SELECT * FROM S WHERE  Sno=NEW.Sno)then
 SELECT 'There is no basic information of the student in the student table, and the insertion is refused'into @msg;
 else
    select 'Insert successful'into @msg;
end if;
end 
$$
SELECT @msg;

INSERT INTO SC VALUES('S7','C5','55');
SELECT @msg;

INSERT INTO SC VALUES('S5','C3','99');
SELECT @msg;
SELECT * FROM SC;

(7) Create a one-time event, add a student record to the teacher table T after 2 minutes, and the event will not be deleted after execution;

CREATE EVENT INSERT_DATA_EVENT
ON SCHEDULE
AT current_timestamp + interval 2 minute
on completion preserve
do insert into t values('T6','Cai Lin','female',26,'lecturer',1300,1500,'Foreign Languages');

select * from t;

SHOW CREATE EVENT insert_data_event/G

(8) Create a circular event. From now on, the teacher information queried from data table T is stored in a new table TempTeach every minute, and expires 1 hour after its creation time.

create table TempTeach select * from t limit 0,0;
select * from TempTeach;
create event if not exists delete_event
on schedule every 1 minute
ends current_timestamp + interval 1 hour
on completion preserve
do insert into TempTeach select * from t limit 1,1;