1.视图
视图： 是一个虚拟表，其内容由查询定义；
视图有如下特点;
1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
2. 视图是由基本表（实表）产生的表（虚表）。
3. 视图的建立和删除不影响基本表。
4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
5. 当视图来自多个基本表时，不允许添加和删除数据。
#视图
create view 视图名称 as sql查询语句
-- ALTER VIEW views as select name,num,id from goods #更新视图
-- drop VIEW views
#使用视图，就跟正常的表一样的查询;
2.触发器-trigger
触发器：监视某种情况，并触发某种操作；
触发器四要素：
1.监视地点（on table）
2.监视事件（insert, update, delete）
3.触发时间（after, before）
4.触发事件（insert, update, delete）
create trigger triggerName after/before insert/update/delete
on 表名 for each row #这句话是固定的
begin
#需要执行的sql语句
end
注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发
注意2:insert/update/delete:只能选一个
#触发器
#三种监听的方式，INSERT，update,delete
-- INSERT into order_table VALUES(1,1,5); #买了东西，产生了一个订单；
-- #即原商品的数量要对应订单的数量而变化；
-- create TRIGGER tg1 AFTER INSERT ON order_table for EACH ROW
-- BEGIN
-- UPDATE goods SET num = num -new.much where id=new.gid;
-- # new就是新添加的数据，而old是以前旧的数据
-- END
-- UPDATE goods SET num = num -5 where id=1
-- UPDATE order_table SET much=much -3 where oid=1;
-- #商品一退还3个，即订单的数量减3，原商品的数量加3 -- #三种监听的方式，INSERT，update,delete
-- create TRIGGER tg2 AFTER UPDATE ON order_table for EACH ROW
-- BEGIN
-- UPDATE goods SET num = num + old.much - new.much where id=new.gid;
-- # new就是新添加的数据，而old是以前旧的数据
-- END -- delete from order_table WHERE gid=1;
--
-- CREATE TRIGGER tg3 AFTER DELETE ON order_table for each ROW
-- BEGIN
-- UPDATE goods SET num= num + old.much where id= old.gid;
-- END
3.存储过程
存储过程
-- CREATE PROCEDURE p()
-- BEGIN
-- SELECT * from goods;
-- END
--
-- CALL p()
#存储过程中有3种参数，in(入参类型) out(出参类型 ) INOUT(出入参类型 )
#存储过程就类似把很多的操作封装为一个函数了，然后用 call 来调用函数。
-- CREATE PROCEDURE p1(in i INT,OUT s VARCHAR(20))
-- BEGIN
-- SELECT name INTO s from goods where id=i;
-- #into 关键字 可以将前面的字段的查询结果 执行INTO 赋值于后面的变量。
-- END
--
-- set @n = null; #@为设置一个变量
-- call p1(2,@n)
-- SELECT @n #查看现有的存储过程；
SHOW procedure status;
#删除存储过程
drop procedure #计算1到100累加的和，并且返回计算结果； CREATE PROCEDURE p4(INOUT n INT)
BEGIN
DECLARE sum INT DEFAULT 0; #先声明一个变量，用作接收返回值
DECLARE i INT;
set i = 0;
while i<=n DO #DO 循环开始
SET sum = sum + i;
set i=i+1;
end WHILE; #结束循环 -- SELECT sum;
set n = sum; #将结果赋值于 出参数 n ，把结果输出出去
END; SET @n =100; #设置变量 call p4(@n) #调用这个存储过程 SELECT @n #查询存储过程返回回来的结果
存储过程优点：
1、存储过程增强了SQL语言灵活性。
存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；
2、减少网络流量，降低了网络负载。
存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行
3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译。
一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
存储过程缺点：
1、扩展功能不方便
2、不便于系统后期维护
函数
MySQL提供的内建函数:
一、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值 RAND()
返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 三、字符串函数 CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000' INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
例如:
SELECT INSERT('abcd',1,2,'tt'); 结果为: 'ttcd'
SELECT INSERT('abcd',1,4,'tt'); 结果为: 'tt'
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换 INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。 LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
例如:
SELECT INSTR('abc','c'); 结果为： 3
SELECT INSTR('abc','d'); 结果为： 0 LOWER(str)
变小写 UPPER(str)
变大写 REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
例如:
SELECT REVERSE('1234567') 结果为：7654321 SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。 mysql> SELECT SUBSTRING('Quadratically',5); -- 从第5位开始截取
-> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -- 从第4位开始截取
-> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); --从第5位开始截取,截取6个长度
-> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -- 从倒数第3位开始截取
-> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -- 从倒数第5位开始截取,截取3个长度
-> 'aki' 四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999) 重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值 mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00' 五、加密函数
MD5()
计算字符串str的MD5校验和
例如:
SELECT MD5('1234') 结果为：81dc9bdb52d04dc20036dbd8313ed055
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的
例如:
SELECT PASSWORD('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF 六、控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等，则返回resultN，否则返回default IF(test,t,f)
如果test是真，返回t；否则返回f IFNULL(arg1,arg2)
如果arg1不是空，返回arg1，否则返回arg2
例如:
SELECT IFNULL('bbb','abc'); 结果为: bbb
SELECT IFNULL(null,'abc'); 结果为: abc NULLIF(arg1,arg2)
如果arg1=arg2返回NULL；否则返回arg1
例如:
SELECT NULLIF('bbb','bbb');结果为： null
SELECT NULLIF('aaa','bbb');结果为： aaa MySQL内建函数
事物
事物： 一组sql语句批量执行,要么全部执行成功,要么全部执行失败。
事物的特性：
1.原子性： 对于其数据修改，要么全部执行，要么全部都不执行；
2.一致性： 事物执行前后，约束没有变化；
3.隔离性： 多个事物之前没有影响
4.持久性：即使出现致命的系统故障也将一直保持
另外需要注意:
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
#开启一个事物的时候，系统就不会自动的提交数据了，只有commit后才提交；即把缓存区的数据存放在物理区；
#执行转账，鲁班转账给后羿，首先应该是 减钱，而不是先加钱；
#不开启事物
#后羿 转 1000 给鲁班，分两部；
#1.先是在后羿的账户上减1000，然后在给鲁班的账户上加1000；这样的顺序保证了在突发的情况下，公司不会亏损；
#1
-- select * from account where id=2;
-- UPDATE account SET money = money -1000 where id =2;
-- select money from account where id=2;
#2
-- select * from account where id=1;
-- UPDATE account SET money = money +1000 where id =1;
-- select money from account where id=1; #但是如果中途发生了特殊的情况，导致转账失败了，这样先减的1000，要在给用户加上，这样就显得很繁琐了。
#因此 有了事物这一概念，事物： 一直sql语句批量的执行，要么全部执行成功，要么全部执行失败； -- START TRANSACTION; #开启一个事物；后就不会自动帮你把数据提交给物理区了，需要手动commit;
-- UPDATE account SET money = money -100 where id =2 ;
-- SAVEPOINT s1 #设置保存点；
-- UPDATE account SET money = money +100 where id =1;
-- #mysql数据库有两个部分，一个是物理区，一个是缓存区，正常执行sql语句后的结果在缓存区，
-- # 然后 数据库 内部 自动的把 缓存区里面的数据copy一份给物理区（真实的表），执行完后，缓存区里面的数据就消失了。
-- SELECT * from account; #执行事物的时候，数据的变化存放 在 缓存区了
-- commit; #要手动的提交事物，给物理区；
-- ROLLBACK to s1; #回滚事物，就是回到执行事情之前的 缓存区的 数据
-- SELECT * from account;
数据锁
在并发的时候，同一个数据可以会被多个人同时进行修改，即会造成数据的不安全；因此需要加锁；
在实际开发中有两种锁；
1.悲观锁，即只能在同一时间 内一个人处理数据；就是在查询语句后面加上 for update;
注意：1.在使用悲观锁的时候，需要指定主键，不然就会锁整个表，造成死锁；
2.悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。
2.乐观锁
使用数据版本（Version）来记录机制实现；当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。
当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，
如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
#乐观锁：是用数据的版本（Version)记录机制实现；
set @m = 0; -- 账户余额
select money into @m from account where id = 1 ;
select @m;
-- 2.查询版本号
set @version = 0; -- 版本号
select version into @version from account where id = 1 ;
select @version; -- 3.修改账户余额
update account set money = @m -100,version=version+1 where id = 1 and version = @version; select * FROM account where id = 1;
悲观锁与乐观锁的优缺点:
两种锁各有其有点缺点,不能单纯的讲哪个更好.
乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果经常产生冲突，上层应用会不断的进行重试操作，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.
数据库备份
mysqldump 命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql #示例：
#单库备份
mysqldump -uroot -p123456 db1 > c:/db1.sql
mysqldump -uroot -p123456 db1 table1 table2 > c:/db1-table1-table2.sql #多库备份
mysqldump -uroot -p123456 --databases db1 db2 mysql db3 > c:/db1_db2_mysql_db3.sql #备份所有库
mysqldump -uroot -p123456 --all-databases > c:/all.sql