一、在数据库中创建一个表student,用于存储学生信息
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
grade FLOAT
);
1、向student表中添加一条新记录
记录中id字段的值为1,name字段的值为"monkey",grade字段的值为98.5
mysql> 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
mysql> insert into student (id,name,grade)
-> values(2,'bob',95.5)
-> ,(3,'john',90.0),
-> (4,'smith',88.5);
3、向student表中添加一条新记录,部分数据插入
5,"jone"
mysql> insert into student(id,name)
-> values(5,'jone');
4、更新表,grade 大于90的加0.5
mysql> update student set
-> grade=grade+0.5
-> where grade >90;
5、删除成绩为空的记录
delete from student where grade is null;
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限。
create user test1 identified by "password";
grant select on student to test1;
2、查询用户test1的权限。
SELECT * FROM USER_SYS_PRIVS WHERE GRANTEE = 'TEST1';
3、删除用户test1.
DROP USER test1;