时间:2021-07-01 10:21:17 帮助过:24人阅读
1 创建视图
View Code
- <span style="color: #008000">#</span><span style="color: #008000">语法:CREATE VIEW 视图名称 AS SQL语句</span>
- create view teacher_view as select tid <span style="color: #0000ff">from</span> teacher where tname=<span style="color: #800000">‘</span><span style="color: #800000">李平老师</span><span style="color: #800000">‘</span><span style="color: #000000">;
- </span><span style="color: #008000">#</span><span style="color: #008000">于是查询李平老师教授的课程名的sql可以改写为</span>
- mysql> select cname <span style="color: #0000ff">from</span> course where teacher_id = (select tid <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view);
- </span>+--------+
- | cname |
- +--------+
- | 物理 |
- | 美术 |
- +--------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- </span><span style="color: #008000">#</span><span style="color: #008000">!!!注意注意注意:</span><span style="color: #008000">
- #</span><span style="color: #008000">1. 使用视图以后就无需每次都重写子查询的sql,但是这么效率并不高,还不如我们写子查询的效率高</span>
- <span style="color: #008000">#</span><span style="color: #008000">2. 而且有一个致命的问题:视图是存放到数据库里的,如果我们程序中的sql过分依赖于数据库中存放的视图,那么意味着,一旦sql需要修改且涉及到视图的部分,则必须去数据库中进行修改,而通常在公司中数据库有专门的DBA负责,你要想完成修改,必须付出大量的沟通成本DBA可能才会帮你完成修改,极其地不方便</span>
2 使用视图
View Code
- <span style="color: #008000">#</span><span style="color: #008000">修改视图,原始表也跟着改</span>
- mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course;
- </span>+-----+--------+------------+
- | cid | cname | teacher_id |
- +-----+--------+------------+
- | 1 | 生物 | 1 |
- | 2 | 物理 | 2 |
- | 3 | 体育 | 3 |
- | 4 | 美术 | 2 |
- +-----+--------+------------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- mysql</span>> create view course_view as select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">创建表course的视图</span>
- Query OK, 0 rows affected (0.52<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> course_view;
- </span>+-----+--------+------------+
- | cid | cname | teacher_id |
- +-----+--------+------------+
- | 1 | 生物 | 1 |
- | 2 | 物理 | 2 |
- | 3 | 体育 | 3 |
- | 4 | 美术 | 2 |
- +-----+--------+------------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
- mysql</span>> update course_view set cname=<span style="color: #800000">‘</span><span style="color: #800000">xxx</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">更新视图中的数据</span>
- Query OK, 4 rows affected (0.04<span style="color: #000000"> sec)
- Rows matched: </span>4 Changed: 4<span style="color: #000000"> Warnings: 0
- mysql</span>> insert into course_view values(5,<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span>,2); <span style="color: #008000">#</span><span style="color: #008000">往视图中插入数据</span>
- Query OK, 1 row affected (0.03<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span> course; <span style="color: #008000">#</span><span style="color: #008000">发现原始表的记录也跟着修改了</span>
- +-----+-------+------------+
- | cid | cname | teacher_id |
- +-----+-------+------------+
- | 1 | xxx | 1 |
- | 2 | xxx | 2 |
- | 3 | xxx | 3 |
- | 4 | xxx | 2 |
- | 5 | yyy | 2 |
- +-----+-------+------------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
我们不应该修改视图中的记录,而且在涉及多个表的情况下是根本无法修改视图中的记录的,如下图
3 修改视图
View Code
- <span style="color: #000000">语法:ALTER VIEW 视图名称 AS SQL语句
- mysql</span>> alter view teacher_view as select * <span style="color: #0000ff">from</span> course where cid>3<span style="color: #000000">;
- Query OK, 0 rows affected (</span>0.04<span style="color: #000000"> sec)
- mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> teacher_view;
- </span>+-----+-------+------------+
- | cid | cname | teacher_id |
- +-----+-------+------------+
- | 4 | xxx | 2 |
- | 5 | yyy | 2 |
- +-----+-------+------------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
4 删除视图
View Code
- <span style="color: #000000">语法:DROP VIEW 视图名称
- DROP VIEW teacher_view</span>
二、触发器(不推荐使用)
1).使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为,注意:没有查询
一 创建触发器
View Code
- <span style="color: #008000">#</span><span style="color: #008000"> 插入前</span>
- <span style="color: #000000">CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
- BEGIN
- ...
- END
- </span><span style="color: #008000">#</span><span style="color: #008000"> 插入后</span>
- <span style="color: #000000">CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
- BEGIN
- ...
- END
- </span><span style="color: #008000">#</span><span style="color: #008000"> 删除前</span>
- <span style="color: #000000">CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
- BEGIN
- ...
- END
- </span><span style="color: #008000">#</span><span style="color: #008000"> 删除后</span>
- <span style="color: #000000">CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
- BEGIN
- ...
- END
- </span><span style="color: #008000">#</span><span style="color: #008000"> 更新前</span>
- <span style="color: #000000">CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
- BEGIN
- ...
- END
- </span><span style="color: #008000">#</span><span style="color: #008000"> 更新后</span>
- <span style="color: #000000">CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
- BEGIN
- ...
- END</span>
View Code
- <span style="color: #008000">#</span><span style="color: #008000">准备表</span>
- <span style="color: #000000">CREATE TABLE cmd (
- id INT PRIMARY KEY auto_increment,
- USER CHAR (</span>32<span style="color: #000000">),
- priv CHAR (</span>10<span style="color: #000000">),
- cmd CHAR (</span>64<span style="color: #000000">),
- sub_time datetime, </span><span style="color: #008000">#</span><span style="color: #008000">提交时间</span>
- success enum (<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span>) <span style="color: #008000">#</span><span style="color: #008000">0代表执行失败</span>
- <span style="color: #000000">);
- CREATE TABLE errlog (
- id INT PRIMARY KEY auto_increment,
- err_cmd CHAR (</span>64<span style="color: #000000">),
- err_time datetime
- );
- </span><span style="color: #008000">#</span><span style="color: #008000">创建触发器</span>
- delimiter //<span style="color: #000000">
- CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
- BEGIN
- IF NEW.success </span>= <span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span> THEN <span style="color: #008000">#</span><span style="color: #008000">等值判断只有一个等号</span>
- INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
- END IF ; <span style="color: #008000">#</span><span style="color: #008000">必须加分号</span>
- END//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">往表cmd中插入记录,触发触发器,根据IF的条件决定是否插入错误日志</span>
- <span style="color: #000000">INSERT INTO cmd (
- USER,
- priv,
- cmd,
- sub_time,
- success
- )
- VALUES
- (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ls -l /etc</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">cat /etc/passwd</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">useradd xxx</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">no</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">0755</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">ps aux</span><span style="color: #800000">‘</span>,NOW(),<span style="color: #800000">‘</span><span style="color: #800000">yes</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span><span style="color: #008000">#</span><span style="color: #008000">查询错误日志,发现有两条</span>
- mysql> select * <span style="color: #0000ff">from</span><span style="color: #000000"> errlog;
- </span>+----+-----------------+---------------------+
- | id | err_cmd | err_time |
- +----+-----------------+---------------------+
- | 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
- | 2 | useradd xxx | 2017-09-14 22:18:48 |
- +----+-----------------+---------------------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
二 使用触发器
触发器无法由用户直接调用,而知由于对表的【增/删/改】操作被动引发的。
三 删除触发器
View Code
- drop trigger tri_after_insert_cmd;
三 事务(要会)
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。
View Code
- <span style="color: #000000">create table user1(
- id int primary key auto_increment,
- name char(</span>32<span style="color: #000000">),
- balance int
- );
- insert into user1(name,balance) values
- (</span><span style="color: #800000">‘</span><span style="color: #800000">庞文废</span><span style="color: #800000">‘</span>,200<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">萧pt</span><span style="color: #800000">‘</span>,200<span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">八哥</span><span style="color: #800000">‘</span>,200<span style="color: #000000">);
- start transaction;
- update user1 set balance</span>=100 where name=<span style="color: #800000">‘</span><span style="color: #800000">庞文废</span><span style="color: #800000">‘</span><span style="color: #000000">;
- update user1 set balance</span>=210 where name=<span style="color: #800000">‘</span><span style="color: #800000">萧pt</span><span style="color: #800000">‘</span><span style="color: #000000">;
- updsate user1 set balance</span>=290 where name=<span style="color: #800000">‘</span><span style="color: #800000">八哥</span><span style="color: #800000">‘</span><span style="color: #000000">;
- rollback; </span><span style="color: #008000">#</span><span style="color: #008000">如果任意一条sql出现异常都应该回滚到初始状态</span>
- commit; <span style="color: #008000">#</span><span style="color: #008000">如果所有的sql都正常,应该执行commit</span>
疑问:如何捕捉异常,在下方存储过程中解决
四 存储过程(不推荐使用)
一 介绍
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点:
- <span style="color: #008000">#<span style="color: #008000">1. 用于替代程序写的SQL语句,实现程序与sql解耦
- <span style="color: #008000">#<span style="color: #008000">2. 基于网络传输,传别名的数据量小,而直接传sql数据量大</span></span></span></span>
使用存储过程的缺点:
- <span style="color: #008000">#<span style="color: #008000">1. 执行效率低
- <span style="color: #008000">#<span style="color: #008000">2. 程序员扩展功能不方便</span></span></span></span>
补充:程序与数据库结合使用的两种方式
- <span style="color: #008000">#<span style="color: #008000">方式一:
- <span style="color: #000000"> MySQL:存储过程
- 程序:调用存储过程
- <span style="color: #008000">#<span style="color: #008000">方式二:
- <span style="color: #000000"> MySQL:
- 程序:纯SQL语句
- </span></span></span></span></span></span>
二 创建简单存储过程(无参)
View Code
- delimiter //<span style="color: #000000">
- create procedure p1()
- BEGIN
- select </span>* <span style="color: #0000ff">from</span><span style="color: #000000"> blog;
- INSERT into blog(name,sub_time) values(</span><span style="color: #800000">"</span><span style="color: #800000">xxx</span><span style="color: #800000">"</span><span style="color: #000000">,now());
- END </span>//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
- <span style="color: #000000">call p1()
- </span><span style="color: #008000">#</span><span style="color: #008000">在python中基于pymysql调用</span>
- cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p1</span><span style="color: #800000">‘</span><span style="color: #000000">)
- </span><span style="color: #0000ff">print</span>(cursor.fetchall())
三 创建存储过程(有参)
View Code
- <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
- #</span><span style="color: #008000">创建有参存储过程之in的使用</span>
- delimiter //<span style="color: #000000">
- create procedure p2(
- </span><span style="color: #0000ff">in</span><span style="color: #000000"> m int,
- </span><span style="color: #0000ff">in</span><span style="color: #000000"> n int,
- )
- begin
- select </span>* <span style="color: #0000ff">from</span> test where id between m <span style="color: #0000ff">and</span><span style="color: #000000"> n;
- end </span>//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">调用存储过程</span>
- call p2(3,7); <span style="color: #008000">#</span><span style="color: #008000">在mysql中调用</span>
- cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p2</span><span style="color: #800000">‘</span>,args=(3,7)) <span style="color: #008000">#</span><span style="color: #008000">在python中通过pymysql模块调用</span>
- <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
- #</span><span style="color: #008000">创建有参存储过程之out的使用</span>
- delimiter //<span style="color: #000000">
- create procedure p3(
- </span><span style="color: #0000ff">in</span><span style="color: #000000"> m int,
- </span><span style="color: #0000ff">in</span><span style="color: #000000"> n int,
- out res int
- )
- begin
- select </span>* <span style="color: #0000ff">from</span> test where id between m <span style="color: #0000ff">and</span><span style="color: #000000"> n;
- set res</span>=1<span style="color: #000000">;
- end </span>//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">调用存储过程</span><span style="color: #008000">
- #</span><span style="color: #008000">在mysql中</span>
- set @x=11111111111<span style="color: #000000">
- call p3(</span>3,7,@x); <span style="color: #008000">#</span><span style="color: #008000">在mysql中调用, 查看结果:select @x;</span>
- <span style="color: #008000">#</span><span style="color: #008000">在python中</span>
- res=cursor.callproc(<span style="color: #800000">‘</span><span style="color: #800000">p3</span><span style="color: #800000">‘</span>,args=(3,7,123)) <span style="color: #008000">#</span><span style="color: #008000">@_p3_0=3,@_p3_1=7,@_p3_2=123</span>
- <span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">只是拿到存储过程中select的查询结果</span>
- cursor.execute(<span style="color: #800000">‘</span><span style="color: #800000">select @_p3_0,@_p3_1,@_p3_2</span><span style="color: #800000">‘</span><span style="color: #000000">)
- </span><span style="color: #0000ff">print</span>(cursor.fetchall()) <span style="color: #008000">#</span><span style="color: #008000">可以拿到的是返回值</span>
- <span style="color: #008000">#</span><span style="color: #008000">===============================================</span><span style="color: #008000">
- #</span><span style="color: #008000">创建有参存储过程之inout的使用</span>
- <span style="color: #000000">
- delimiter </span>//<span style="color: #000000">
- create procedure p4(
- inout m int
- )
- begin
- select </span>* <span style="color: #0000ff">from</span> test where id ><span style="color: #000000"> m;
- set m</span>=1<span style="color: #000000">;
- end </span>//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">在mysql中</span>
- set @x=2<span style="color: #000000">;
- call p4(@x);
- select @x;</span>
四 删除存储过程
View Code
- drop procedure proc_name;
五 捕捉异常+事务
引子
- delimiter //<span style="color: #000000">
- create procedure p5(
- inout m int
- )
- begin
- select </span>* <span style="color: #0000ff">from</span> test11111111 where id ><span style="color: #000000"> m;
- set m</span>=1<span style="color: #000000">;
- end </span>//<span style="color: #000000">
- delimiter ;
- set @x</span>=2<span style="color: #000000">;
- call p5(@x);
- select @x;</span>
View Code
- <span style="color: #008000">#</span><span style="color: #008000">====================捕捉异常+事务===========================</span>
- delimiter //<span style="color: #000000">
- create PROCEDURE p6(
- OUT p_return_code tinyint
- )
- BEGIN
- DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlexception
- BEGIN
- </span>--<span style="color: #000000"> ERROR
- set p_return_code </span>= 1<span style="color: #000000">;
- rollback;
- END;
- DECLARE exit handler </span><span style="color: #0000ff">for</span><span style="color: #000000"> sqlwarning
- BEGIN
- </span>--<span style="color: #000000"> WARNING
- set p_return_code </span>= 2<span style="color: #000000">;
- rollback;
- END;
- START TRANSACTION;
- insert into test(username,dep_id) values(</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1<span style="color: #000000">);
- DELETE </span><span style="color: #0000ff">from</span> tb1111111; <span style="color: #008000">#</span><span style="color: #008000">执行失败</span>
- <span style="color: #000000"> COMMIT;
- </span>--<span style="color: #000000"> SUCCESS
- set p_return_code </span>= 0; <span style="color: #008000">#</span><span style="color: #008000">0代表执行成功</span>
- <span style="color: #000000">
- END </span>//<span style="color: #000000">
- delimiter ;
- </span><span style="color: #008000">#</span><span style="color: #008000">用python模拟</span>
- <span style="color: #0000ff">try</span><span style="color: #000000">:
- START TRANSACTION;
- DELETE </span><span style="color: #0000ff">from</span> tb1; <span style="color: #008000">#</span><span style="color: #008000">执行失败</span>
- insert into blog(name,sub_time) values(<span style="color: #800000">‘</span><span style="color: #800000">yyy</span><span style="color: #800000">‘</span><span style="color: #000000">,now());
- COMMIT;
- set p_return_code </span>= 0; <span style="color: #008000">#</span><span style="color: #008000">0代表执行成功</span>
- <span style="color: #0000ff">except</span><span style="color: #000000"> sqlexception:
- set p_return_code </span>= 1<span style="color: #000000">;
- rollback;
- </span><span style="color: #0000ff">except</span><span style="color: #000000"> sqlwaring:
- set p_return_code </span>= 2<span style="color: #000000">;
- rollback;</span>
五、函数 (注意,必须在sql语句中执行函数)
MySQL中提供了许多内置函数,例如:
View Code
- <span style="color: #000000">CHAR_LENGTH(str)
- 返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
- 对于一个包含五个二字节字符集, LENGTH()返回值为 </span>10<span style="color: #000000">, 而CHAR_LENGTH()的返回值为5。
- CONCAT(str1,str2,...)
- 字符串拼接
- 如有任何一个参数为NULL ,则返回值为 NULL。
- CONCAT_WS(separator,str1,str2,...)
- 字符串拼接(自定义连接符)
- CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
- CONV(N,from_base,to_base)
- 进制转换
- 例如:
- SELECT CONV(</span><span style="color: #800000">‘</span><span style="color: #800000">a</span><span style="color: #800000">‘</span>,16,2<span style="color: #000000">); 表示将 a 由16进制转换为2进制字符串表示
- FORMAT(X,D)
- 将数字X 的格式写为</span><span style="color: #800000">‘</span><span style="color: #800000">#,###,###.##</span><span style="color: #800000">‘</span><span style="color: #000000">,以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点,或不含小数部分。
- 例如:
- SELECT FORMAT(</span>12332.1,4); 结果为: <span style="color: #800000">‘</span><span style="color: #800000">12,332.1000</span><span style="color: #800000">‘</span><span style="color: #000000">
- INSERT(str,pos,len,newstr)
- 在str的指定位置插入字符串
- pos:要替换位置其实位置
- len:替换的长度
- newstr:新字符串
- 特别的:
- 如果pos超过原字符串长度,则返回原字符串
- 如果len超过原字符串长度,则由新字符串完全替换
- INSTR(str,substr)
- 返回字符串 str 中子字符串的第一个出现位置。
- LEFT(str,len)
- 返回字符串str 从开始的len位置的子序列字符。
- LOWER(str)
- 变小写
- UPPER(str)
- 变大写
- LTRIM(str)
- 返回字符串 str ,其引导空格字符被删除。
- RTRIM(str)
- 返回字符串 str ,结尾空格字符被删去。
- SUBSTRING(str,pos,len)
- 获取字符串子序列
- LOCATE(substr,str,pos)
- 获取子序列索引位置
- REPEAT(str,count)
- 返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
- 若 count </span><=<span style="color: #000000"> 0,则返回一个空字符串。
- 若str 或 count 为 NULL,则返回 NULL 。
- REPLACE(str,from_str,to_str)
- 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
- REVERSE(str)
- 返回字符串 str ,顺序和字符顺序相反。
- RIGHT(str,len)
- 从字符串str 开始,返回从后边开始len个字符组成的子序列
- SPACE(N)
- 返回一个由N空格组成的字符串。
- 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</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratically</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">foobarbar</span><span style="color: #800000">‘</span> FROM 4<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barbar</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Quadratically</span><span style="color: #800000">‘</span>,5,6<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ratica</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -3<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ila</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span>, -5, 3<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">aki</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT SUBSTRING(<span style="color: #800000">‘</span><span style="color: #800000">Sakila</span><span style="color: #800000">‘</span> FROM -4 FOR 2<span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">ki</span><span style="color: #800000">‘</span><span style="color: #000000">
- TRIM([{BOTH </span>| LEADING |<span style="color: #000000"> TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
- 返回字符串 str , 其中所有remstr 前缀和</span>/<span style="color: #000000">或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。
- mysql</span>> SELECT TRIM(<span style="color: #800000">‘</span><span style="color: #800000"> bar </span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">bar</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT TRIM(LEADING <span style="color: #800000">‘</span><span style="color: #800000">x</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">xxxbarxxx</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barxxx</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT TRIM(BOTH <span style="color: #800000">‘</span><span style="color: #800000">x</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">xxxbarxxx</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">bar</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT TRIM(TRAILING <span style="color: #800000">‘</span><span style="color: #800000">xyz</span><span style="color: #800000">‘</span> FROM <span style="color: #800000">‘</span><span style="color: #800000">barxxyz</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">barx</span><span style="color: #800000">‘</span>
需要掌握函数:date_format
- <span style="color: #008000">#</span><span style="color: #008000">1 基本使用</span>
- mysql> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2009-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%W %M %Y</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">Sunday October 2009</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2007-10-04 22:23:00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%H:%i:%s</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1900-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">%D %y %a %d %m %b %j</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">4th 00 Thu 04 10 Oct 277</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1997-10-04 22:23:00</span><span style="color: #800000">‘</span><span style="color: #000000">,
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">%H %k %I %r %T %S %w</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">22 22 10 10:23:00 PM 22:23:00 00 6</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">1999-01-01</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%X %V</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">1998 52</span><span style="color: #800000">‘</span><span style="color: #000000">
- mysql</span>> SELECT DATE_FORMAT(<span style="color: #800000">‘</span><span style="color: #800000">2006-06-00</span><span style="color: #800000">‘</span>, <span style="color: #800000">‘</span><span style="color: #800000">%d</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span>-> <span style="color: #800000">‘</span><span style="color: #800000">00</span><span style="color: #800000">‘</span>
- <span style="color: #008000">#</span><span style="color: #008000">2 准备表和记录</span>
- <span style="color: #000000">CREATE TABLE blog (
- id INT PRIMARY KEY auto_increment,
- NAME CHAR (</span>32<span style="color: #000000">),
- sub_time datetime
- );
- INSERT INTO blog (NAME, sub_time)
- VALUES
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第1篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2015-03-01 11:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第2篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2015-03-11 16:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第3篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-01 10:21:31</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第4篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-22 09:23:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第5篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-23 10:11:11</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第6篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2016-07-25 11:21:31</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第7篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 15:33:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第8篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 17:32:21</span><span style="color: #800000">‘</span><span style="color: #000000">),
- (</span><span style="color: #800000">‘</span><span style="color: #800000">第9篇</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">2017-03-01 18:31:21</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span><span style="color: #008000">#</span><span style="color: #008000">3. 提取sub_time字段的值,按照格式后的结果即"年月"来分组</span>
- SELECT DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span>),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span><span style="color: #000000">);
- </span><span style="color: #008000">#</span><span style="color: #008000">结果</span>
- +-------------------------------+----------+
- | DATE_FORMAT(sub_time,<span style="color: #800000">‘</span><span style="color: #800000">%Y-%m</span><span style="color: #800000">‘</span>) | COUNT(1) |
- +-------------------------------+----------+
- | 2015-03 | 2 |
- | 2016-07 | 4 |
- | 2017-03 | 3 |
- +-------------------------------+----------+<span style="color: #000000">
- rows </span><span style="color: #0000ff">in</span> set (0.00 sec)
更多:
一 自定义函数
View Code
- <span style="font-size: 15px">delimiter //<span style="color: #000000">
- create function f1(
- i1 int,
- i2 int)
- returns int
- BEGIN
- declare num int;
- set num </span>= i1 +<span style="color: #000000"> i2;
- </span><span style="color: #0000ff">return</span><span style="color: #000000">(num);
- END </span>//<span style="color: #000000">
- delimiter ;</span></span>
二 删除函数
View Code
- <span style="font-size: 15px">drop function func_name;</span>
三 执行函数
View Code
- <span style="font-size: 15px"><span style="color: #008000">#</span><span style="color: #008000"> 获取返回值</span>
- select UPPER(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">) into @res;
- SELECT @res;
- </span><span style="color: #008000">#</span><span style="color: #008000"> 在查询中使用</span>
- select f1(11,nid) ,name <span style="color: #0000ff">from</span> tb2;</span>
六、流程控制
一 条件语句
if条件语句
- <span style="font-size: 15px">delimiter //<span style="color: #000000">
- CREATE PROCEDURE proc_if ()
- BEGIN
- declare i int default 0;
- </span><span style="color: #0000ff">if</span> i = 1<span style="color: #000000"> THEN
- SELECT </span>1<span style="color: #000000">;
- ELSEIF i </span>= 2<span style="color: #000000"> THEN
- SELECT </span>2<span style="color: #000000">;
- ELSE
- SELECT </span>7<span style="color: #000000">;
- END IF;
- END </span>//<span style="color: #000000">
- delimiter ;</span></span>
二 循环语句
while循环
- delimiter //<span style="color: #000000">
- CREATE PROCEDURE proc_while ()
- BEGIN
- DECLARE num INT ;
- SET num </span>=<span style="color: #000000"> 0 ;
- WHILE num </span>< 10<span style="color: #000000"> DO
- SELECT
- num ;
- SET num </span>= num + 1<span style="color: #000000"> ;
- END WHILE ;
- END </span>//<span style="color: #000000">
- delimiter ;</span>
python-day48--mysql之视图、触发器、事务、存储过程、函数
标签:width title splay closed 流程控制 基本 tran values row