数据库第三次作业

一、在数据库中创建一个表student，用于存储学生信息

CREATE TABLE student(
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    grade FLOAT);

mysql> create database student；
mysql> use student;
mysql> create table student(
    -> id int primary key auto_increment,
    -> name  varchar(20)  not null,
    -> grade float);
Query OK, 0 rows affected (0.02 sec)

1、向student表中添加一条新记录
    记录中id字段的值为1，name字段的值为"monkey"，grade字段的值为98.5

mysql> insert into student values('1','monkey','98.5');
Query OK, 1 row affected (0.01 sec)

2、向student表中添加多条新记录
    2,"bob",95.5
    3,"john",90.0
    4,"smith",88.5

mysql> insert into student(name,grade)values('bob','95.5'),('john','90.0'),('smith','88.5');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

3、向student表中添加一条新记录，部分数据插入
    5,"jone"

mysql> insert into student(name)values('jone');
Query OK, 1 row affected (0.01 sec)

4、更新表，grade 大于90的加0.5

mysql> update student set grade=grade+0.5 where grade>90;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

5、删除成绩为空的记录

mysql> delete from student where grade is null;
Query OK, 1 row affected (0.01 sec)

二、用户权限部分

1、创建一个用户test1使他只能本地登录拥有查询student表的权限。

mysql> grant select on student.* to 'test1'@'localhost' identified by '1234567';
    -> flush privileges;

2、查询用户test1的权限。

mysql> show grants for 'test1'@'localhost';

3、删除用户test1.

mysql> drop user test1;
    -> flush privileges;