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)

 

上一篇:manjaro安装qt


下一篇:Manjaro下安装配置NPM源 Registry的正确姿势