编辑代码

CREATE DATABASE TEST;
USE TEST;
CREATE TABLE Student(
    Sno CHAR(4) PRIMARY KEY,
    Sname VARCHAR(10),
    Ssex CHAR(2),
    Sage INT(3),
    Sdept VARCHAR(20));

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES
('0001', '张梨花', '女', 22, '软件'),
('0002', '李刚', '男', 17, '计算机'),
('0003', '王军', '男', 19, '经济'),
('0004', '李海红', '女', 18, '软件');

CREATE TABLE SG(
    Sno CHAR(4),
    Cno CHAR(3),
    Grade INT(3),
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno),
    index ind_g(Grade)
    );

INSERT INTO SG (Sno, Cno, Grade) VALUES
('0001', '101', 95),
('0001', '103', 85),
('0002', '103', 90),
('0003', '101', 55),
('0004', '103', 67);

UPDATE Student SET Sdept='经济' WHERE Sname='李刚';
DELETE FROM SG WHERE Sno='0002' AND Cno='103';
SELECT * FROM SG WHERE GRADE BETWEEN 60 AND 80;

CREATE USER 'wang'@'localhost' IDENTIFIED BY '123456';
GRANT UPDATE,DELETE ON TEST.Student TO 'wang'@'localhost';
GRANT SELECT ON TEST.SG TO 'wang'@'localhost';