MySQL——第三次作业

一、在数据库中创建一个表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);

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |  98.5 |
+----+--------+-------+
1 row in set (0.00 sec)

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)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |  98.5 |
|  2 | bob    |  95.5 |
|  3 | john   |    90 |
|  4 | smith  |  88.5 |
+----+--------+-------+
4 rows in set (0.00 sec)

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

mysql> insert into student(id,name) values(5,"jone");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |  98.5 |
|  2 | bob    |  95.5 |
|  3 | john   |    90 |
|  4 | smith  |  88.5 |
|  5 | jone   |  NULL |
+----+--------+-------+
5 rows in set (0.00 sec)

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

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

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |    99 |
|  2 | bob    |    96 |
|  3 | john   |    90 |
|  4 | smith  |  88.5 |
|  5 | jone   |  NULL |
+----+--------+-------+
5 rows in set (0.00 sec)

5、删除成绩为空的记录

mysql> delete from student
    -> where grade <=> null;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |    99 |
|  2 | bob    |    96 |
|  3 | john   |    90 |
|  4 | smith  |  88.5 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> delete from student
    -> where grade is null;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+--------+-------+
| id | name   | grade |
+----+--------+-------+
|  1 | monkey |    99 |
|  2 | bob    |    96 |
|  3 | john   |    90 |
|  4 | smith  |  88.5 |
+----+--------+-------+
4 rows in set (0.00 sec)

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

mysql> create user 'test1'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on school.student to 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)


GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口
令’];#授予权限的命令

2、查询用户test1的权限。

mysql> show grants for 'test1'@'localhost';
+-----------------------------------------------------------+
| Grants for test1@localhost                                |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost`                 |
| GRANT SELECT ON `school`.`student` TO `test1`@`localhost` |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

3、删除用户test1.

mysql> drop user 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)