sql知识

mysql

• 连接命令
  mysql -h<host> -u<user> -p<passwd>

• mysql默认仅允许本地查询，需开权限

    #授权（命令行执行）
    GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "<root用户密码>";
    flush privileges;

• sql

  • 示例
    SELECT * FROM [WHERE <条件语句>] [GROUP BY <列名> HAVING <分组后条件>] [ORDER BY <列名> [ASC/DESC]] [LIMIT <单页条数> OFFSET <起始序号>]
    JOIN
    SELECT * FROM <table1> AS t1 INNER/LEFT/RIGHT JOIN <table2> AS t2 ON t1.<列名> = t2.<列名> [过滤/聚合/排序语句]</table2></table1>
    表连接比子表查询性能高
    去重查询
    SELECT DISTINCT column_name,column_name FROM table_name;
    插入
    INSERT INTO table_name VALUES (value1,value2,value3,...);
    INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
    更新
    UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
    删除
    DELETE FROM table_name WHERE some_column=some_value;
    字符串截取
    substring(str,index,len) 截取str,从index开始（从1开始），截取len长度
    SELECT SUBSTRING(str,index,len) FROM table_name;

    存储过程

    • 概念：
      • 存储过程（Stored Procedure）为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
      • 存储过程可封装，并隐藏复杂的商业逻辑。
      • 存储过程可以回传值，并可以接受参数
    • 声明存储过程
      CREATE PROCEDURE demo_in_parameter(IN p_in int)
    • 存储过程参数说明
      • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
      • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
      • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
    • 开始和结束
      BEGIN .... END
    • 变量定义
      DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
    • 变量赋值
      SET @变量名 = 表达式值 [,variable_name = expression ...]
    • 条件语句
      • if-then-else-end if

        mysql > DELIMITER //  
        mysql > CREATE PROCEDURE proc2(IN parameter int)  
        -> begin 
        -> declare var int;  
        -> set var=parameter+1;  
        -> if var=0 then 
        -> insert into t values(17);  
        -> end if;  
        -> if parameter=0 then 
        -> update t set s1=s1+1;  
        -> else 
        -> update t set s1=s1+2;  
        -> end if;  
        -> end;  
        -> //  
        mysql > DELIMITER ;

      • case

        mysql > DELIMITER //  
        mysql > CREATE PROCEDURE proc3 (in parameter int)  
        -> begin 
        -> declare var int;  
        -> set var=parameter+1;  
        -> case var  
        -> when 0 then   
        -> insert into t values(17);  
        -> when 1 then   
        -> insert into t values(18);  
        -> else   
        -> insert into t values(19);  
        -> end case;  
        -> end;  
        -> //  
        mysql > DELIMITER ; 

    • 循环语句
      • while

        while 条件 do
        循环体
        end while;

      • repeat

        repeat
        循环体
        until 循环条件  
        end repeat;

      • loop

        loop
        循环体
        leave
        end loop;

    • 示例

      mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
      mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
        -> BEGIN
        -> 　 DELETE FROM MATCHES
        ->    WHERE playerno = p_playerno;
        -> END$$
      Query OK, 0 rows affected (0.01 sec)
      mysql> delimiter;　　#将语句的结束符号恢复为分号

      注：使用DELIMITER $$命令将语句的结束符号从分号';'临时改为两个美元符，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。