数据库的建表以及增删改查

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