[mysql]mysql 数据库高级总结

1. 变量

用户变量
用户变量的形式为@var_name，其中变量名var_name可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。 默认字符集是cp1252 (Latin1)。可以用mysqld的--default-character-set选项更改字符集


设置用户变量的一个途径是执行SET语句：
SET @var_name = expr [, @var_name = expr] ...


对于SET，可以使用=或:=作为分配符。分配给每个变量的expr可以为整数、实数、字符串或者NULL值。


也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符：
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;


系统变量
通过连接服务器并执行SET GLOBAL var_name语句，可以动态更改这些全局变量。要想更改全局变量，必须具有SUPER权限
要想设置一个GLOBAL变量的值，使用下面的语法：
mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;
要想设置一个SESSION变量的值，使用下面的语法：
mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;
LOCAL是SESSION的同义词。
如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION

2. 选择结构
（https://blog.csdn.net/csdn_wangqi/article/details/53218782）
if结构
IF search_condition THEN
statement_list
[ELSEIF search_condition THEN]
statement_list ...
[ELSE
statement_list]
END IF
case - when - then - else
SELECT            
   case                   -------------如果
   when sex='1' then '男' -------------sex='1'，则返回值'男'
   when sex='2' then '女' -------------sex='2'，则返回值'女'  
   else 0                 -------------其他的返回'其他’
   end                    -------------结束
from   sys_user            --------整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’

3. 循环结构
while、repeat、loop循环
循环一般在存储过程和存储函数中使用频繁，这里只给出最简单的示例
while

delimiter $$

create procedure test_while()

begin

declare sum int default 0;

declare t int default 5;

while t>0 do

set sum=sum+1;

set t=t-1;

end while;

select sum;

end $$

delimiter ;

repeat

delimiter $$

create procedure _repeat()

begin

declare a int default 10;

repeat

set a=a-1;

until a<5

end repeat;

select a;

end $$

delimiter ;

注意使用repeat的时候，在判断条件(until 条件)的那一行句末不加分号，这个很容易出错！

loop

delimiter $$

create procedure test_loop()

begin

declare t int default 0;

label:loop

set t=t+1;

if t>10 then leave label;

end if;

end loop label;

select t;

end $$

delimiter ;

loop 一般要和一个标签(此处为label，名称可以自定义，不过要保证前后一致)一起使用，且在 loop 循环中一定要有一个判断条件，能够满足在一定的条件下跳出 loop 循环（即 leave )!

4. 函数 (mysql常用函数 -https://blog.csdn.net/sugang_ximi/article/details/6664748)

类型转化函数
为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
示例：
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT 'f'=BINARY 'F','f'=CAST('F' AS BINARY);

参考：
https://blog.csdn.net/wuyujian11/article/details/53337416
https://blog.csdn.net/baidu_20876831/article/details/79756865


一、什么是视图
       视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。简单的来说视图是由其定义结果组成的表；


例子：定一班级表class（ID，name） 学生表 student（id，class_id，name）；
当数据表结构很复杂，但我们只关心其中一部分数据的时候就可以使用视图，定义关心的数据
创建视图：
                create view v_stu as select  c.name as c_name ,s.name as stu_name from student  s,class  c where c.id = s.class_id
查询视图 v_stu
                select * from v_stu


视图的工作机制:当调用视图的时候，才会执行视图中的sql，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。视图这样设计有什么好处？节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维护好真实表的内容，就可以保证视图的完整性了。


语法：
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition


注：我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

https://www.cnblogs.com/mark-chan/p/5384139.html
https://www.cnblogs.com/geaozhang/p/6797357.html


MySQL的存储过程
存储过程是数据库的一个重要的功能，MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。


语法
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER //
CREATE PROCEDURE myproc(OUT s int)
BEGIN
SELECT COUNT(*) INTO s FROM students;
END
//
DELIMITER ;
分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。
参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回
过程体
过程体的开始与结束使用BEGIN与END进行标识。

https://blog.csdn.net/u014796999/article/details/52970293
事务的特性：
事务有以下四个标准属性的缩写ACID，通常被称为：
原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
一致性: 确保数据库正确地改变状态后，成功提交的事务。
隔离性: 使事务操作彼此独立的和透明的。
持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。
事务 ACID Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
1、事务的原子性
一组事务，要么成功；要么撤回。
2、稳定性
有非法数据（外键约束之类），事务撤回。
3、隔离性
事务独立运行。
一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。
事务的100%隔离，需要牺牲速度。
4、可靠性
软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。
可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。
开启事务
START TRANSACTION 或 BEGIN
提交事务（关闭事务）
COMMIT
放弃事务（关闭事务）
ROLLBACK

<触发器作用>
当在SQL、MySQL数据库中一张表中插入一条记录时，触动触发器，使同一数据库的另一张表插入相同记录。
在学习的过程中，发现两者的写法是不同的。
<触发器作用>

1. MYSQL SHOW 命令
desc 表名;       // 表信息
show columns from 表名;       // 表字段
describe 表名;       // 表信息
show create table 表名;        // 表创建语句
show create database 数据库名;        // 显示数据库 信息
show table status from 数据库名;        // 数据库状态
show tables或show tables from database_name;       // 显示当前数据库中所有表的名称
show databases;       // 显示mysql中所有数据库的名称
show processlist;       // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
show table status;       // 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
show columns from table_name from database_name;        // 显示表中列名称
show columns from database_name.table_name;        // 显示表中列名称
show grants for user_name@localhost;        // 显示一个用户的权限，显示结果类似于grant 命令
show index from table_name;        // 显示表的索引 show status;解释：显示一些系统特定资源的信息，例如，正在运行的线程数量
show variables;        // 显示系统变量的名称和值 show privileges;解释：显示服务器所支持的不同权限
show create database database_name ;       // 显示create database 语句是否能够创建指定的数据库
show create table table_name;       // 显示create database 语句是否能够创建指定的数据库
show engies;        // 显示安装以后可用的存储引擎和默认引擎。
show innodb status ;        // 显示innoDB存储引擎的状态
show logs;        // 显示BDB存储引擎的日志
show warnings;       //显示最后一个执行的语句所产生的错误、警告和通知
show errors;       // 只显示最后一个执行语句所产生的错误