7.7作业

最新推荐文章于 2024-10-10 18:58:22 发布

NewYui

最新推荐文章于 2024-10-10 18:58:22 发布

阅读量144

点赞数 2

文章标签： adb

本文链接：https://blog.csdn.net/NewYui/article/details/140276846

版权

一、在数据库中创建一个表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,grede) values (1,'monkey',98.5);
Query OK, 1 row affected (0.01 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| grede | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

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

mysql> insert into student values(2,'bob',95.5),(3,'join',90.0),(4,'smith',88.5);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+--------+-------+
| id | name   | grede |
+----+--------+-------+
|  1 | monkey |  98.5 |
|  2 | bob    |  95.5 |
|  3 | join   |    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.01 sec)

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

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

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

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

5、删除成绩为空的记录

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

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

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

create user 'test1'@'localhost' identified by '12345678';
grant select on student.* to 'test1'@'localhost';
mysql> show grants for 'test1'@'localhost';
+----------------------------------------------------------+
| Grants for test1@localhost                               |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost`                |
| GRANT SELECT ON `student`.* TO `test1`@`localhost`       |
| GRANT SELECT ON `mysql`.`student` TO `test1`@`localhost` |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

2.删除用户test1

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

mysql> use mysql
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)