Linux学习87 MariaDB高级操作实战

一、DML:INSERT,DELETE,UPDATE,SELECT

  1、INSERT INTO:

    a、INSERT [INTO] tbl_name [col1,..)] {VALUES|VALUE} (val1,...)(...),...

    b、注意:

      字符型:引号

      数值型:不能用引号

  2、SELECT:

    a、SELECT * FROM tbl_name1,tbl_name2:返回指定表的所有数据。如果是大表的话就是一场噩梦。

    b、SELECT col1,col2,... FROM tbl_name;

      (1)、显示时,字段可以显示为别名

        col_name AS col_alias

        

        

    c、SELECT col1,... FROM tbl_name WHERE clause;

      (1)、WHERE clause:用于指明挑选条件

        1)、col_name 操作符 value:

          age > 30

        2)、操作符(1):

          >,<,>=,<=,==,!=

        3)、组合条件

          and

          or

          not

        4)、判断

      

      

      

      

       

      

      正则:

      

      5)、操作符(2)

        BETWEEN ... AND ...

        LIKE 'PATTERN'

          通配符:

            %:任意长度的任意字符

            _:任意单个字符

        PLIKE 'PATTERN'

          正则表达式对字符串做模式匹配

        ISNULL

        IS NOT NULL

    d、SELECT col1,... FROM tbl_name [WHERE clause] ORDER BY col_name,col_name2,...[ASC|DESC];

      (1)、ASC:升序

      (2)、DESC:降序

        

        

     e、分组:

      (1)、GROUP BY,为了聚合:

        1)、count(),sum(),avg(),max(),min()

        

        2)、HAVING:对聚合的结果做条件过滤

        

  3、DELETE:

    a、DELETE FROM tbl_name [WHERE where] [ORDER BY ...] [LIMIT row_count]

    b、DELETE FROM tbl_name WHERE where_condition

      

    c、DELETE FROM tbl_name [ORDER BY ...] [LIMIT row_count]

      

  4、UPDATE:

    a、

 

    b、示例

      

 

二、用户账号及权限管理

  1、用户账号:'username'@'host'

    a、host:此用户访问当前mysql服务器时,允许其通过哪些主机远程创建连接

      (1)、表示方式:IP,网络地址,主机名,通配符(%和_)

    b、禁止检查主机名:my.cnf

      [mysqld]

      skip_name_resolve = ON

  2、创建用户账号:

    a、CREATE USER 'username'@'ho st' [IDENTIFIED BY 'password']

MariaDB [(none)]> CREATE USER 'wohaoshuai'@'192.168.10.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> \q
Bye
[root@www ~]# mysql -uwohaoshuai -p123456 -h 192.168.10.13
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

  3、删除用户账号

    b、DROP USER 'user'@'host' [,user@host] ...

  4、授权

    a、权限级别:管理权限,数据库,表,字段,存储例程

    b、GRANT priv_type,... ON [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

      

 

      (1)、priv_type: ALL [PRIVILEGES]

      (2)、db_name.tbl_name:

        1)、*.*:所有库的所有表

        2)、db_name.*:指定库的所有表

        3)、db_name.tbl_name:指定库的特定表

        4)、db_name.routine_name:指定库上的存储过程或存储函数

      (3)、[object_type]

        1)、TABLE

        2)、FUNCTION

        3)、PROCEDURE

    c、查看指定用户所获得的授权

      (1)、SHOW GRANTS FOR 'user'@'host'

MariaDB [(none)]> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR wohaoshuai;
ERROR 1141 (42000): There is no such grant defined for user 'wohaoshuai' on host '%'
MariaDB [(none)]> SHOW GRANTS FOR 'wohaoshuai'@'192.168.10.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for wohaoshuai@192.168.10.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wohaoshuai'@'192.168.10.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

      (2)、SHOW GRANTS FOR CURRENT_USER;

    d、回收权限:

      (1)、REVOKE priv_type,... ON db_name.tbl_name FROM 'user'@'host';

        

      (2)、示例

        1)、假如我们现在tom账号是ALL PRIVILEGES,现在我们REVOKE 掉DELETE和UPDATE权限,然后他会自动在所有权限中减掉这两个权限

          

    e、注意:MariaDB服务进程启动时,会读取mysql库的所有授权表至内存中

      (1)、GRANT或REVOKE命令等执行的权限操作会保存于表中,MariaDB此时一般会自动重读授权表,权限修改会立即生效

      (2)、其它方式实现的权限修改,要想生效,必须手动运行FLUSH PRIVILEGES命令方可。

三、练习题

 

posted @ 2020-05-21 20:01  Presley  阅读(249)  评论(0编辑  收藏  举报