SQL-变量(16)

作者: 小白201808 | 来源:发表于2018-09-01 07:54 被阅读18次

    变量

    1.系统变量:
            全局变量:作用域:服务器每次启动将为所有的全局变量赋初始值,
                   针对于所有的的会话(连接)有效,但不垮重启。
            
            会话变量:仅仅针对当前的会话(连接)有效
    2.自定义变量:
            用户变量
            局部变量
    

    一. 系统变量

    即该变量由系统提供,不是用户定义,属于服务层面            
    

    1. 使用语法:

    1.查看所有的系统的变量
    show grobal|[session] variables;
    
    注意:如果是全局级别,则需要要加global,如果是会话级别,则需要加session,如果不写,则默认session。
    mysql> show global variables;
    +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name                                            | Value                                                                                                                                                                                                                                                                                                                                                                                                                                      |
    +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | activate_all_roles_on_login                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                        |
    | auto_generate_certs                                      | ON                                                                                                                                                                                                                                                                                                                                                                                                                                         |
    | auto_increment_increment                                 | 1                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    | auto_increment_offset                                    | 1                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    | autocommit                                               | ON 
    ...
    ...
    531 rows in set (0.01 sec)
    
    mysql> show session variables;
    +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Variable_name                                            | Value                                                                                                                                                                                                                                                                                                                                                                                                                                      |
    +----------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | activate_all_roles_on_login                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                        |
    | auto_generate_certs                                      | ON                                                                                                                                                                                                                                                                                                                                                                                                                                         |
    | auto_increment_increment                                 | 1                                                                                                                                                                                                                                                                                                                                                                                                                                          |
    | auto_increment_offset                                    | 1                                                                                                                                                                                                                                                                                                                                                             
    ...
    ...
    548 rows in set (0.00 sec)                                                                                                                                                                 
    
    2.查看满足条件的部分系统变量
    show global | [session] variables like '要查看变量的部分字符' ;
    
    mysql> show global variables like '%char%';
    +--------------------------+-----------------------------------------------------------+
    | Variable_name            | Value                                                     |
    +--------------------------+-----------------------------------------------------------+
    | character_set_client     | utf8mb4                                                   |
    | character_set_connection | utf8mb4                                                   |
    | character_set_database   | utf8mb4                                                   |
    | character_set_filesystem | binary                                                    |
    | character_set_results    | utf8mb4                                                   |
    | character_set_server     | utf8mb4                                                   |
    | character_set_system     | utf8                                                      |
    | character_sets_dir       | /usr/local/mysql-8.0.11-macos10.13-x86_64/share/charsets/ |
    +--------------------------+-----------------------------------------------------------+
    8 rows in set (0.01 sec)
    
    
    
    3.查看指定的某个系统变量的值
    select @@global | [session] .系统变量名
    
    
    mysql> select @@global.character_set_system;
    +-------------------------------+
    | @@global.character_set_system |
    +-------------------------------+
    | utf8                          |
    +-------------------------------+
    1 row in set (0.00 sec)
    
    4.为某个系统变量赋值
      方式一:
      set global|[session] 系统变量名=值;
      方式二:
      set @@global|[session].系统变量名=值;                     
    
    mysql> show global variables like '%isolation%';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set (0.00 sec)
    mysql> set global transaction_isolation ='read-committed';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show global variables like '%isolation%';
    +-----------------------+----------------+
    | Variable_name         | Value          |
    +-----------------------+----------------+
    | transaction_isolation | READ-COMMITTED |
    +-----------------------+----------------+
    1 row in set (0.00 sec)
    
    
    mysql> set @@global.transaction_isolation='REPEATABLE-READ';
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> show global variables like '%isolation%';
    +-----------------------+-----------------+
    | Variable_name         | Value           |
    +-----------------------+-----------------+
    | transaction_isolation | REPEATABLE-READ |
    +-----------------------+-----------------+
    1 row in set (0.00 sec)
    
    
    

    二. 自定义变量

    (1)用户变量

    1.即用户自己定义的,不是用系统提供的。
    2.使用步骤:
      声明
      赋值
      使用(查看,比较,运算等)
    3.作用域:
      针对当前会话(连接)有效,同于会话变量的作用域 
    
    赋值操作符:= 或 := 
    
    (1)声明并初始化
     set @用户变量名=值; 或
     set @用户变量名 := 值;或
     select @用户变量名 := 值;
    (2)赋值(更新用户变量的值)
     方式一(声明并初始化一样):通过set或select
           set @用户变量名=值;或
           set @用户变量名 := 值;或
           select @用户变量名 := 值;
    方式二:通过select into 
          select 字段 into 变量名 from 表;
    (3)使用:select  @用户变量名;           
      
    

    案例

    mysql> set @name ='keen';
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> set @name =22;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @count =1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> #赋值
    mysql> use myemployees;
    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
    mysql> select count(*) into @count from employees;
    Query OK, 1 row affected (0.06 sec)
    
    mysql> #查看
    mysql> select @count;
    +--------+
    | @count |
    +--------+
    |    107 |
    +--------+
    1 row in set (0.00 sec)
    
    mysql> select @name;
    +-------+
    | @name |
    +-------+
    |    22 |
    +-------+
    1 row in set (0.00 sec)
    
    mysql> set @name='keen';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @name;
    +-------+
    | @name |
    +-------+
    | keen  |
    +-------+
    1 row in set (0.00 sec)
    
    
    

    2.局部变量

     作用域:仅仅在定义它的 begin end 中有效
     应用在begin end 中的第一句话
    
     (1)声明:
        declare 变量名 类型;
        declare 变量名 类型 default 值;
    (2)赋值
       方式一:
        通过set或select
        set 局部变量名=值;或
        set 局部变量名 := 值;或
        select @局部变量名 := 值;
       方式二:通过select into 
        select 字段 into 局部变量名 from 表;    
    (3)使用
      select 局部变量名;
    

    练习:声明两个变量并赋初始值,求和,并打印;

    @用户变量名
    set @n=1;
    set @m=2;
    set @sum =@n+@m;
    select @sum;
    
    
    #2.局部变量
    declare m int default 1;
    declare n  int default 2;
    declare sum int;
    set sum=m+n;
    select sum;
    //declare is not valid at this position. 因为它得在begin end 里
          
    

    注:这是本人的学习笔记及练习,如果有错误的地方望指出一起讨论,谢谢!

    相关文章

      网友评论

        本文标题:SQL-变量(16)

        本文链接:https://www.haomeiwen.com/subject/ddcrwftx.html