一、在数据库中创建一个表student,用于存储学生信息
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
grade FLOAT
);
1、向student表中添加一条新记录
记录中id字段的值为1,name字段的值为"monkey",grade字段的值为98.5
2、向student表中添加多条新记录
2,"bob",95.5
3,"john",90.0
4,"smith",88.5
3、向student表中添加一条新记录,部分数据插入
5,"jone"
4、更新表,grade 大于90的加0.5
5、删除成绩为空的记录
实现:
1、向student表中添加一条新记录
记录中id字段的值为1,name字段的值为"monkey",grade字段的值为98.5
INSERT INTO student (id, name, grade) VALUES (1, 'monkey', 98.5);
2、向student表中添加多条新记录
2,"bob",95.5
3,"john",90.0
4,"smith",88.5
INSERT INTO student (id, name, grade) VALUES
(2, 'bob', 95.5),
(3, 'john', 90.0),
(4, 'smith', 88.5);
3、向student表中添加一条新记录,部分数据插入5,"jone"
INSERT INTO student (id, name) VALUES (5, 'jone');
4、更新表,grade 大于90的加0.5
UPDATE student SET grade = grade + 0.5 WHERE grade > 90;
5、删除成绩为空的记录
DELETE FROM student WHERE grade IS NULL;
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限
2、查询用户test1的权限
3、删除用户test1
实现:
1、创建一个用户test1使他只能本地登录拥有查询student表的权限
CREATE USER 'test1'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON db_system.student TO 'test1'@'localhost';
FLUSH PRIVILEGES;
2、查询用户test1的权限
SHOW GRANTS FOR 'test1'@'localhost';
3、删除用户test1
DROP USER 'test1'@'localhost';