一、在数据库中创建一个表student,用于存储学生信息
CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
grade FLOAT
);
create table student(
id int primary key,
name varchar(20) not null,
grade float
);
1、向student表中添加一条新记录
记录中id字段的值为1,name字段的值为"monkey",grade字段的值为98.5
insert into student values(1,'monkey',98.5);
2、向student表中添加多条新记录
2,"bob",95.5
3,"john",90.0
4,"smith",88.5
insert into student values(2,'bob',95.5),(3,'john',90.0),(4,'smith',88.5);
3、向student表中添加一条新记录,部分数据插入
5,"jone"
insert into student(id,name)values(5,'jone');
4、更新表,grade 大于90的加0.5
update student
set grade=grade+0.5
where grade>90;
5、删除成绩为空的记录
delete from student
where grade is null;
select * from student;
+----+--------+-------+
| id | name | grade |
+----+--------+-------+
| 1 | monkey | 99 |
| 2 | bob | 96 |
| 3 | john | 90 |
| 4 | smith | 88.5 |
+----+--------+-------+
二、用户权限部分
1、创建一个用户test1使他只能本地登录拥有查询student表的权限。
create user test1@localhost identified by 'exaid@';
grant select on student.* to test1@localhost;
2、查询用户test1的权限。
show grants for 'test1'@localhost;
+----------------------------------------------------+
| Grants for test1@localhost |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`localhost` |
| GRANT SELECT ON `student`.* TO `test1`@`localhost` |
+----------------------------------------------------+
3、删除用户test1.
DELETE FROM mysql.user WHERE Host='localhost' AND User='test1';
FLUSH PRIVILEGES;