实验目的:
一、在数据库中创建一个表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、删除成绩为空的记录
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限。
2、查询用户test1的权限。
3、删除用户test1.
一、在数据库中创建一个表student,用于存储学生信息
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
grade FLOAT
);
#先创建一个数据库st,用于存储student表
mysql> create database st;
Query OK, 1 row affected (0.00 sec)
#建表
mysql> use st;
Database changed
mysql> create table student(
-> id int primary key,
-> name varchar(20) not null,
-> grade float);
Query OK, 0 rows affected (0.02 sec)
查看建表结果
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| grade | float | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 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)
#查看结果
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> 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)
#更新以及成绩加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、删除成绩为空的记录
#删除grade为空的
mysql> delete from student where grade is 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)
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限。
#创建一个用户test1,并设置密码为123456
mysql> create user 'test1'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
#使用户test1只能本地登录拥有查询student表的权限
mysql> grant select on st.student to test1@localhost;
Query OK, 0 rows affected (0.01 sec)
#刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
必须刷新,不然不生效!!!!!!!!!!!
2、查询用户test1的权限。
mysql> show grants for 'test1'@'localhost';
+-------------------------------------------------------+
| Grants for test1@localhost |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost` |
| GRANT SELECT ON `st`.`student` TO `test1`@`localhost` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
3、删除用户test1.
#删除用户
mysql> drop user 'test1'@'localhost';
Query OK, 0 rows affected (0.01 sec)
#查询结果
mysql> show grants for 'test1'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test1' on host 'localhost'