数据库上手避坑之--赋予非root用户操作数据库的所有权限

mariadb数据库中终于建了一个普通账号gsc，密码是个秘密，反正我知道。登陆mysql账号进行建表，报了个错误，CREATE command denied to user 'gsc'@'localhost' for table 'stu_info，没有权限建表；

数据库中普通用户权限设置的问题，无法进行创建表。只需给对应账户，赋予所有的权限即可：
1、给用户权限：

　　mysql>grant all privileges on student_info.* to gsc; //student_info是数据库，gsc是操纵student_info的用户

2、刷新系统设置：

　　mysql>flush privileges; //刷新系统权限表。

新开一个终端：

1、mysql登陆数据库：

　　mysql -ugsc -p
　　Enter password:

2、创建数据库并使用数据库：

MariaDB [(none)]> show databases;　　　　　　　　//查看数据库            
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> create database student_info;　　//创建数据库student_info
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;              
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student_info       |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> use student_info;　　　　　　　　//使用数据库student_info
Database changed

 3、在数据库student_info中创建表：

MariaDB [student_info]> create table stu_info(　　　　//创建表
    ->     stuId int(11),　　　　　　　　　　　　　　　　 //->是sql语句的提示符号，不是输入命令的一个部分
    ->     stuName varchar(40),
    ->     gender varchar(10),
    ->     phone int(11),
    ->     email varchar(40),
    ->     account varchar(40),
    ->     code varchar(40),
    ->     adminId int(1));
Query OK, 0 rows affected (0.166 sec)

MariaDB [student_info]> show tables;　　　　　　　　　　//查看数据student_info中表
+------------------------+
| Tables_in_student_info |
+------------------------+
| stu_info               |
+------------------------+
1 row in set (0.001 sec)

MariaDB [student_info]> describe stu_info;　　　　　　//查看表stu_info基本结构
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stuId   | int(11)     | YES  |     | NULL    |       |
| stuName | varchar(40) | YES  |     | NULL    |       |
| gender  | varchar(10) | YES  |     | NULL    |       |
| phone   | int(11)     | YES  |     | NULL    |       |
| email   | varchar(40) | YES  |     | NULL    |       |
| account | varchar(40) | YES  |     | NULL    |       |
| code    | varchar(40) | YES  |     | NULL    |       |
| adminId | int(1)      | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
8 rows in set (0.002 sec)

MariaDB [student_info]> show create table stu_info;　　//查看表stu_info详细结构语句
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu_info | CREATE TABLE `stu_info` (
  `stuId` int(11) DEFAULT NULL,
  `stuName` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  `email` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `code` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `adminId` int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

C语言代码获取stu_info的表结构信息： Makefile

sqlGetHead:sqlGetHead.c
     gcc -Wall -g -I/usr/include/mariadb -o sqlGetHead sqlGetHead.c -lmysqlclient 

c代码sqlGetHead.c

 1 //This is c program code!                                                               
 2 /* *=+=+=+=+* *** *=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=
 3   * 文档信息: *** :~/WORKM/sqlGetHead.c
 4   * 版权声明: *** :(魎魍魅魑)MIT
 5   * 联络信箱: *** :guochaoxxl@163.com
 6   * 创建时间: *** :2020年11月23日的下午02:37
 7   * 文档用途: *** :数据结构与算法分析-c语言描述
 8   * 作者信息: *** :guochaoxxl(http://cnblogs.com/guochaoxxl)
 9   * 修订时间: *** :2020年第47周 11月23日 星期一 下午02:37 (第328天)
10   * 文件描述: *** :自行添加
11  * *+=+=+=+=* *** *+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+*/
12 #include <stdio.h>
13 #include <stdlib.h>
14 #include <mysql/mysql.h>
15 #include <string.h>
16 
17 #define USER "gsc"
18 #define PWD "******"
19 #define DATABASENAME "student_info"
20 #define TABLENAME "stu_info"
21  
22 int main(int argc, char** argv)
23 {
24     MYSQL conn;
25     MYSQL_RES res; 
26 
27     char tableName[30];
28     char query[1024];
29     unsigned short numFields;
30     char column[30][40];
31 
32     //1.初始化
33     mysql_init(&conn);
34  
35     //2.连接数据库
36     if(!mysql_real_connect(&conn, "localhost", USER, PWD,  DATABASENAME, 0, NULL, 0))
37     {
38         fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&conn)   );
39         return -1;
40     }
41     //3.选择表stu_info
42     strcpy(tableName, TABLENAME);
43         sprintf(query, "select * from %s", tableName);
44     mysql_query(&conn, query);
45     
46     //4.取出表stu_info中选择的内容
47     res = *mysql_store_result(&conn);
48     printf("%s\n", &res);
49     
50     //5.统计表stu_info字段
51     numFields = mysql_num_fields(&res);
52     printf("%d\n", numFields);
53     
54     //6.打印表stu_info字段
55     for(int i = 0; i < numFields; ++i)
56     {
57         strcpy(column[i], mysql_fetch_field(&res)->name);
58         printf("%s\n", column[i]);
59     }   
60     
61     return 0;
62 }

　　make结果：

gcc -Wall -g -I/usr/include/mariadb -o sqlGetHead sqlGetHead.c -lmysqlclient

　　执行

sqlGetHead 

8
stuId
stuName
gender
phone
email
account
code
adminId

 代码简单，不再啰嗦