Mariadb监视器

[root@localhost ~]# mysql -uroot -pflectrag  #登录root账户
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;#展示所有数据库
+--------------------+
| Database |
+--------------------+
| acme |
| information_schema |
| mariadb |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> drop database acme;#删除acme数据库
Query OK, 5 rows affected (0.015 sec)

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

MariaDB [(none)]> use acme;#选择acme数据库
Database changed
MariaDB [acme]> source /root/acmeDB.dump;#编译脚本
Query OK, 0 rows affected, 1 warning (0.000 sec)

Query OK, 0 rows affected (0.004 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 1 row affected (0.001 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 0 rows affected (0.004 sec)

Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)

MariaDB [acme]> grant select on acme.* to a@'localhost' identified by '123456';#创建一个只能本地登录的a用户对acme只有查询权限，密码123456
Query OK, 0 rows affected (0.005 sec)

MariaDB [acme]> \q#退出
Bye
[root@localhost ~]# mysql -ua -p#a用户登录
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
MariaDB [(none)]> show tables;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| acme |
| information_schema |
+--------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> use acme ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [acme]> show tables;
+----------------+
| Tables_in_acme |
+----------------+
| dept |
| tb_record |
| tb_replaied |
| tb_sureRecord |
| userinfo |
+----------------+
5 rows in set (0.001 sec)

MariaDB [acme]> desc dept;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| deptId | int(10) | NO | PRI | 0 | |
| deptNumber | varchar(100) | NO | | | |
| deptName | varchar(100) | NO | | NULL | |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

MariaDB [acme]> select * from dept;
+--------+--------------------+---------------------------+
| deptId | deptNumber | deptName |
+--------+--------------------+---------------------------+
| 1 | P14464778285479397 | 刑大-合成作战中队 |
| 2 | P14464561185246144 | 配侦部 |
| 3 | P14464561285246244 | 网监部 |
+--------+--------------------+---------------------------+
3 rows in set (0.000 sec)

MariaDB [acme]> insert into acme (deptID,deptNumber,deptName) VALUES (4,ppp,jinitaimei);#插入一条数据
ERROR 1142 (42000): INSERT command denied to user 'a'@'localhost' for table 'acme'#没有插入权限
MariaDB [acme]> updata dept set deptId=6 where deptId=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'updata dept set deptId=6 where deptId=1' at line 1#语法错误
MariaDB [acme]> update dept set deptId=6 where deptId=1;
ERROR 1142 (42000): UPDATE command denied to user 'a'@'localhost' for table 'dept'
MariaDB [acme]> delete form dept where deptId=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'dept where deptId=1' at line 1
MariaDB [acme]> delete from dept where deptId=1;
ERROR 1142 (42000): DELETE command denied to user 'a'@'localhost' for table 'dept'
MariaDB [acme]> \q
Bye
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.5.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use acme;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [acme]> use dept;
ERROR 1049 (42000): Unknown database 'dept'
MariaDB [acme]> insert into acme (deptID,deptNumber,deptName) VALUES (4,ppp,jinitaimei);
ERROR 1146 (42S02): Table 'acme.acme' doesn't exist
MariaDB [acme]> insert into dept (deptID,deptNumber,deptName) VALUES (4,ppp,jinitaimei);
ERROR 1054 (42S22): Unknown column 'ppp' in 'field list'
MariaDB [acme]> insert into dept (deptID,deptNumber,deptName) VALUES (4,'ppp','jinitaimei');
Query OK, 1 row affected (0.001 sec)

MariaDB [acme]> select * from dept;
+--------+--------------------+---------------------------+
| deptId | deptNumber | deptName |
+--------+--------------------+---------------------------+
| 1 | P14464778285479397 | 刑大-合成作战中队 |
| 2 | P14464561185246144 | 配侦部 |
| 3 | P14464561285246244 | 网监部 |
| 4 | ppp | jinitaimei |
+--------+--------------------+---------------------------+
4 rows in set (0.000 sec)

MariaDB [acme]> updata dept set deptId=6 where deptId=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'updata dept set deptId=6 where deptId=1' at line 1
MariaDB [acme]> updata dept set deptId=6 where deptId==1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'updata dept set deptId=6 where deptId==1' at line 1
MariaDB [acme]> update dept set deptId=6 where deptId=1;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [acme]> select * from dept;
+--------+--------------------+---------------------------+
| deptId | deptNumber | deptName |
+--------+--------------------+---------------------------+
| 2 | P14464561185246144 | 配侦部 |
| 3 | P14464561285246244 | 网监部 |
| 4 | ppp | jinitaimei |
| 6 | P14464778285479397 | 刑大-合成作战中队 |
+--------+--------------------+---------------------------+
4 rows in set (0.000 sec)

MariaDB [acme]> delete from dept where deptId=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [acme]> select * from dept;
+--------+--------------------+---------------------------+
| deptId | deptNumber | deptName |
+--------+--------------------+---------------------------+
| 2 | P14464561185246144 | 配侦部 |
| 3 | P14464561285246244 | 网监部 |
| 4 | ppp | jinitaimei |
| 6 | P14464778285479397 | 刑大-合成作战中队 |
+--------+--------------------+---------------------------+
4 rows in set (0.000 sec)

MariaDB [acme]>\q

Bye
[root@localhost ~]#