Manjaro 使用MariaDB

参考博客：https://www.cnblogs.com/guochaoxxl/p/14038413.html

参考博客：https://www.cnblogs.com/guochaoxxl/p/14028827.html

一、创建普通数据库用户并添加权限

创建一个普通用户可以通过以下三种方法

方法一：

MariaDB [(none)]> create user newuser@localhost identified by '1234‘//newuser为用户名，’1234‘为密码

MariaDB [(none)]> select user from mysql.user;

方法二：
MariaDB [(none)]>insert into mysql.user(user,host,password) values('newuser','localhost',password('1234'));

MariaDB [(none)]> flush privileges;//刷新系统权限表

这两种方法直接创建了用户，但没有给他们权限，无法进行创建表。只需给对应账户，赋予所有的权限即可：

可通过以下方法给予权限

mysql>grant all privileges on xxx_info.* to newuser; //xxx_info是数据库，newuser是操纵xxx_info的用户
mysql>flush privileges; //刷新系统权限表

或直接在终端添加权限

mysql -u root -p -e "GRANT SELECT ON *.* TO 'newuserl'@'localhost';"//使用命令赋予用户select权限：

mysql -u root -p -e "GRANT ALL ON *.* TO 'sqlxxl'@'localhost';"//使用命令赋予用户所有权限：

方法三：直接创建普通用户并基于权限

mysql -u root -p -e "GRANT USAGE ON *.* to 'newuser'@'localhost' identified by '1234';"
//mysql -u root -p：sql使用root登陆
//-e：执行后面的命令
//GRANT USAGE ON *.*：赋予所有权限
//to  'newuser'@'localhost' ：标识本地用户名称为newuser
//identified by '1234';"：用户密码为1234

二、登陆mysql账号进行建表

mysql -unewuser -p 

/*如果之前退出了数据库，需要使用命令systemctl start mariadb　来开启mysql服务，
否则回出现错误ERROR 2002 (HY000): Can't connect to local MySQL server throug
h socket '/run/mysqld/mysqld.sock' (2)*/

输入密码后可进入账号进而操作

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Public_bus_info    |
| birdWatchers       |
| book_dinner_info   |
| information_schema |
| menu               |
| mysql              |
| performance_schema |
| rookery            |
| student_info       |
+--------------------+
9 rows in set (0.001 sec)


MariaDB [student_info]> drop database menu; //删除数据库
Query OK, 0 rows affected (0.007 sec)

MariaDB [student_info]> show databases; 
+--------------------+

| Database           |

+--------------------+

| Public_bus_info    |

| birdWatchers       |

| book_dinner_info   |

| information_schema |

| mysql              |

| performance_schema |

| rookery            |

| student_info       |

+--------------------+

8 rows in set (0.001 sec)

MariaDB [student_info]> use student_info; //应用数据库
Database changed

数据库的基本操作

　  SELECT - 从数据库表中获取数据  

　　UPDATE - 更新数据库表中的数据  

　　DELETE - 从数据库表中删除数据   

　　INSERT INTO - 向数据库表中插入数据   

　　CREATE DATABASE - 创建新数据库   

　　ALTER DATABASE - 修改数据库   

　　CREATE TABLE - 创建新表   

　　ALTER TABLE - 变更（改变）数据库表   

　　DROP TABLE - 删除表   

　　CREATE INDEX - 创建索引（搜索键）   

　　DROP INDEX - 删除索引

MariaDB [student_info]> show tables;//查看数据库中的表
Empty set (0.001 sec)

MariaDB [student_info]> create table stu_info(id INT, name TEX
T, age INT);  //创建表stu_info                            
Query OK, 0 rows affected (0.013 sec)



MariaDB [student_info]> show tables;//查看数据库中的表

+------------------------+

| Tables_in_student_info |

+------------------------+

| stu_info               |

+------------------------+

1 row in set (0.001 sec)



MariaDB [student_info]> describe stu_info;//查看表中的详细结构

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | YES  |     | NULL    |       |

| name  | text    | YES  |     | NULL    |       |

| age   | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

3 rows in set (0.001 sec)

MariaDB [student_info]> insert into stu_info values(22, "zhang
san", 11);　//向表中插入数据  
Query OK, 1 row affected (0.007 sec)

MariaDB [student_info]> insert into stu_info values(22, "zhang
san", 11);　   
Query OK, 1 row affected (0.007 sec)

MariaDB [student_info]> select * from stu_info; //查询表的内容
+------+----------+------+

| id   | name     | age  |

+------+----------+------+

|   22 | zhangsan |   11 |

|   22 | zhangsan |   11 |

+------+----------+------+

2 rows in set (0.000 sec)

MariaDB [student_info]> select * from stu_info where age = 11
G;//查询表，改变显示方式

*************************** 1. row ***************************

id: 22

name: zhangsan

age: 11

*************************** 2. row ***************************

id: 22

name: zhangsan

age: 11

2 rows in set (0.006 sec)



ERROR: No query specified


MariaDB [student_info]> update stu_info set age = 11 where id 
= 22;//更新表信息

Query OK, 0 rows affected (0.006 sec)

Rows matched: 2  Changed: 0  Warnings: 0

MariaDB [student_info]> select * from stu_info where age = 11; //条件查询表的内容
+------+----------+------+

| id   | name     | age  |

+------+----------+------+

|   22 | zhangsan |   11 |

|   22 | zhangsan |   11 |

+------+----------+------+

2 rows in set (0.001 sec)