数据库命令作业3

我怎么会咕咕咕呢

于 2024-07-06 17:08:38 发布

阅读量330

点赞数 2

文章标签：数据库

本文链接：https://blog.csdn.net/2201_75364744/article/details/140232207

版权

一、在数据库中创建一个表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、删除成绩为空的记录

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE student(
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(20) NOT NULL,
    ->     grade FLOAT
    -> );
ERROR 1046 (3D000): No database selected
mysql> create database nnx
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> use database nnx;
ERROR 1049 (42000): Unknown database 'database'
mysql> use nnx;
Database changed
mysql> CREATE TABLE student(
    ->     id INT PRIMARY KEY,
    ->     name VARCHAR(20) NOT NULL,
    ->     grade FLOAT
    -> );
Query OK, 0 rows affected (0.04 sec)

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

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

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

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

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

在这里插入图片描述
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限。
2、查询用户test1的权限。
3、删除用户test1.

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

mysql> GRANT select on student .*  TO  test1@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR test1@localhost;
+----------------------------------------------------+
| Grants for test1@localhost                         |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost`          |
| GRANT SELECT ON `student`.* TO `test1`@`localhost` |
+----------------------------------------------------+
2 rows in set (0.00 sec)
mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='test1';
Query OK, 1 row affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)