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';