XiaoYouShan

一千行 MySQL 学习笔记【下】

时间:2017-05-16 作者:Jioby 浏览:336

话接上篇




SQL编程 

-- 局部变量 

-- 变量声明

   declare var_name[,...] type [default value]
   这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。
-- 赋值
   使用 set 和 select into 语句为变量赋值。    

- 注意：在函数内是可以使用全局变量（用户自定义的变量）




-- 全局变量 

定义、赋值set 语句可以定义并为变量赋值。

set @var = value;

也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。


还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。

select @var:=20;

select @v1:=id, @v2=name from t1 limit 1;select * from tbl_name where @var:=30;

select into 可以将表中查询获得的数据赋给变量。    

-| select max(height) into @max_height from tb;




-- 自定义变量名

为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。@var=10;    

- 变量被定义后，在整个会话周期都有效（登录到退出）




-- 控制结构  

-- if语句

if search_condition then

   statement_list   
[elseif search_condition then
   statement_list]...[else
   statement_list]end if;




-- case语句

CASE value WHEN [compare-value] THEN result

[WHEN [compare-value] THEN result ...]

[ELSE result]

END




-- while循环

[begin_label:] while search_condition do
   statement_listend while 

[end_label];

- 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。 

   

-- 退出循环        

退出整个循环 leave

退出当前循环 iterate

通过退出的标签决定退出哪个循环




-- 内置函数 

-- 数值函数

abs(x)            -- 绝对值 abs(-10.9) = 10

format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46

ceil(x)            -- 向上取整 ceil(10.1) = 11

floor(x)        -- 向下取整 floor (10.1) = 10

round(x)        -- 四舍五入去整

mod(m, n)        -- m%n m mod n 求余 10%3=1

pi()            -- 获得圆周率

pow(m, n)        -- m^n

sqrt(x)            -- 算术平方根

rand()            -- 随机数

truncate(x, d)    -- 截取d位小数




-- 时间日期函数

now(), current_timestamp();     -- 当前日期时间

current_date();             -- 当前日期

current_time();             -- 当前时间

date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分

time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分

date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j');    -- 格式化时间

unix_timestamp();                -- 获得unix时间戳

from_unixtime();                -- 从时间戳获得时间




-- 字符串函数

length(string)            -- string长度，字节

char_length(string)        -- string的字符个数

substring(str, position [,length])        -- 从str的position开始,取length个字符

replace(str ,search_str ,replace_str)    -- 在str中用replace_str替换

search_strinstr(string ,substring)    -- 返回substring首次在string中出现的位置

concat(string [,...])    -- 连接字串

charset(str)            -- 返回字串字符集

lcase(string)            -- 转换成小写

left(string, length)    -- 从string2中的左边起取length个字符

load_file(file_name)    -- 从文件读取内容

locate(substring, string [,start_position])    -- 同instr,但可指定开始位置

lpad(string, length, pad)    -- 重复用pad加在string开头,直到字串长度为

lengthltrim(string)            -- 去除前端空格

repeat(string, count)    -- 重复count次

rpad(string, length, pad)    --在str后用pad补充,直到长度为

lengthrtrim(string)            -- 去除后端空格

strcmp(string1 ,string2)    -- 逐字符比较两字串大小




-- 流程函数

case when [condition] then result 

[when [condition] then result ...] 

[else result] 

end  

 

多分支if(expr1,expr2,expr3)  双分支。




-- 聚合函数

count()sum();

max();

min();

avg();

group_concat()




-- 其他常用函数

md5();

default();




-- 存储函数，自定义函数 

新建

   CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
       函数体    

- 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。    

- 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。    

- 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。    

- 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。    

- 多条语句应该使用 begin...end 语句块包含。    

- 一定要有 return 返回值语句。




-- 删除
   DROP FUNCTION [IF EXISTS] function_name;




-- 查看
   SHOW FUNCTION STATUS LIKE 'partten'
   SHOW CREATE FUNCTION function_name;




-- 修改
   ALTER FUNCTION function_name 函数选项




-- 存储过程，自定义功能 

定义存储存储过程 是一段代码（过程），存储在数据库中的sql组成。
一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。
而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。

-- 创建CREATE PROCEDURE sp_name (参数列表)
   过程体

参数列表：不同于函数的参数列表，需要指明参数类型IN，表示输入型
OUT，表示输出型
INOUT，表示混合型

注意，没有返回值。




存储过程




存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。
调用：CALL 过程名

-- 注意- 没有返回值。

- 只能单独调用，不可夹杂在其他语句中

-- 参数IN|OUT|INOUT 参数名 数据类型IN        

输入：在调用过程中，将数据输入到过程体内部的参数
OUT        输出：在调用过程中，将过程体处理完的结果返回到客户端
INOUT    输入输出：既可输入，也可输出-- 语法CREATE PROCEDURE 过程名 (参数列表)BEGIN
   过程体END




用户和权限管理

用户信息表：mysql.user

-- 刷新权限FLUSH PRIVILEGES

-- 增加用户CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)    

- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。    

- 只能创建用户，不能赋予权限。    

- 用户名，注意引号：如 'user_name'@'192.168.1.1'

- 密码也需引号，纯数字密码也要加引号    

- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户RENAME USER old_user TO new_user

-- 设置密码SET PASSWORD = PASSWORD('密码')    

-- 为当前用户设置密码SET PASSWORD FOR 用户名 = PASSWORD('密码')    

-- 为指定用户设置密码

-- 删除用户DROP USER 用户名




-- 分配权限/添加用户GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']    

- all privileges 表示所有权限    

- *.* 表示所有库的所有表    

- 库名.表名 表示某库下面的某表

-- 查看权限SHOW GRANTS FOR 用户名    

-- 查看当前用户权限
   SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限REVOKE 权限列表 ON 表名 FROM 用户名REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    

-- 撤销所有权限

-- 权限层级

-- 要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。

全局层级：全局权限适用于一个给定服务器中的所有数据库，mysql.user
   GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级：数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.host    GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级：表权限适用于一个给定表中的所有列，mysql.talbes_priv    GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级：列权限适用于一个给定表中的单一列，mysql.columns_priv
   当使用REVOKE时，您必须指定与被授权列相同的列。




-- 权限列表ALL [PRIVILEGES]    

-- 设置除GRANT OPTION之外的所有简单权限ALTER    

-- 允许使用ALTER TABLEALTER ROUTINE    

-- 更改或取消已存储的子程序CREATE    

-- 允许使用CREATE TABLECREATE ROUTINE    

-- 创建已存储的子程序CREATE TEMPORARY TABLES        

-- 允许使用CREATE TEMPORARY TABLECREATE USER        

-- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。CREATE VIEW        

-- 允许使用CREATE VIEWDELETE    

-- 允许使用DELETEDROP    

-- 允许使用DROP TABLEEXECUTE        

-- 允许用户运行已存储的子程序FILE    

-- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILEINDEX     

-- 允许使用CREATE INDEX和DROP INDEXINSERT    

-- 允许使用INSERTLOCK TABLES        

-- 允许对您拥有SELECT权限的表使用LOCK TABLESPROCESS     

-- 允许使用SHOW FULL PROCESSLISTREFERENCES    

-- 未被实施RELOAD    

-- 允许使用FLUSHREPLICATION CLIENT    

-- 允许用户询问从属服务器或主服务器的地址REPLICATION SLAVE    

-- 用于复制型从属服务器（从主服务器中读取二进制日志事件）SELECT    

-- 允许使用SELECTSHOW DATABASES    

-- 显示所有数据库SHOW VIEW    

-- 允许使用SHOW CREATE VIEWSHUTDOWN    

-- 允许使用mysqladmin shutdownSUPER    

-- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。UPDATE    

-- 允许使用UPDATEUSAGE    

-- “无权限”的同义词GRANT OPTION    

-- 允许授予权限




表维护

-- 分析和存储表的关键字分布ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...

-- 检查一个或多个表是否有错误CHECK TABLE tbl_name [, tbl_name] ... [option] ...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

-- 整理数据文件的碎片OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...




杂项

1. 可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！

2. 每个库目录存在一个保存当前数据库的选项文件db.opt。

3. 注释：
   单行注释 # 注释内容
   多行注释 /* 注释内容 */
   单行注释 -- 注释内容        (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)

4. 模式通配符：
   _    任意单个字符    %    任意多个字符，甚至包括零字符
   单引号需要进行转义 \'

5. CMD命令行内的语句结束符可以为 ";", "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。
6. SQL对大小写不敏感
7. 清除已有语句：\c





原文地址：https://shockerli.net/post/1000-line-mysql-note/