MySQL-视图与存储过程

一、MySQL基础知识

增：增加单条数据：insert into 表 (列名,列名...) values (值,值,值...)增加多条数据：insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)导入其他表的数据：insert into 表 (列名,列名...) select 列名,列名... from 表删：删除具体数据：delete from 表 where 条件改：修改表中具体数据：update 表 set 列名＝ 值 where 条件查：查询具体内容：select 列名01 as 别名 , 列名02 from 表 where 条件

常见条件：
1、逻辑运算符
and：与；or：或；not：非
2、比较运算符
等于=；大于>；大于等于>=；小于<；小于等于<=；不等于!=或<>

示例：select * from test where id > 5 and num > 60;

3、范围
in：在其中；not in：不在其中；between：在区间内

示例：select * from test where nid between 5 and 10;

4、通配符
%：匹配任意零个字符或者任意多个字符；
_ ：匹配任意一个字符

示例：select * from test where name like '李%';

5、空值
is null：空值
is not null：非空

6、限制条件
limit 3：取前三行
limit 3,5：从第三行开始取5行
limit 3 offset 5：从第五行开始取3行

7、排序
order by 列 asc：从小到大排序
order by 列 desc：从大到小排序
order by 列1 desc，列二 asc：根据列1从大到小排序，如果相同则按列二从小到大排序

8、分组
select count(列名),sum(列名),max(列名),min(列名) from 表 where 条件 group by 列名01,列名02 order by 列名
特别注意：group by 必须在where之后，order by之前

9、连表
无对应关系则不显示：
select A.xx B.oo from A, B where A.x=B.o 没有A.x=B.o对应的数据则不显示任何结果

示例：select  tb1.num,tb2.name from tb1,tb2 where tb1.sid = tb2.sid

无对应关系则不显示:
select A.xx B.oo from A inner join B where A.x=B.o A和B具有对等位置, 没有A.x=B.o对应的数据则不显示任何结果

A表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name from A left join B on A.nid = B.nid

select score.sid,score.course_id,score.num,student.sname from score left join student on score.student_id = student.sid 

B表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name from A right join B on A.nid = B.nid

select score.sid,score.course_id,score.num,student.sname from studentright join score on score.student_id = student.sid

10、导入与导出数据库

导出现有数据库数据    mysqldump -u 用户名 -p 密码 数据库名 > 导出文件路径    #结构+数据    mysqldump -u 用户名 -p 密码 -d 数据库名 > 导出文件路径    #结构导入现有数据mysqldump -u root -p 密码 数据库名 < 文件路径

二、视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。

1、创建视图

格式：CREATE VIEW 视图名称 AS  SQL语句示例：    create view temp1 as     select score.sid,score.course_id,score.num,student.sname     from score     left join student on score.student_id = student.sid;

2、修改视图

格式：ALTER VIEW 视图名称 AS SQL语句示例：    alter view temp2 as     select score.sid,score.course_id,score.num,student.sname     from score     left join student on score.student_id = student.sid     where course_id in (1,2);

3、使用视图

格式：SELECT * FROM 视图名称示例：    select * from temp2;

4、删除视图

格式：DROP VIEW 视图名称示例：    drop view temp2;

三、存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
1、创建无参数存储过程

格式：CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体示例：        delimiter //        create procedure p1()        BEGIN            select * from score;        END//        delimiter;

2、创建带参数的参数过程
存储过程可以接收的参数有三种，in：仅参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值，out：该值可在存储过程内部被改变，并可返回，inout：调用时指定，并且可被改变和返回。

示例：        delimiter //        create procedure p2(            in i1 int,            out r1 int,            inout i2 int        )        BEGIN            DECLARE temp1 int;            DECLARE temp2 int default 5;            set temp1 = 10;            set i2 = i1 + 100;            set r1 = i1 + temp1 + temp2;        END//        delimiter;

3、执行存储过程
无参数：

call p1()

有参数：

set @t1 = 0;set @t2 = 1;call p2(1,@t1,@t2);select @t1,@t2;

4、删除存储过程

drop procedure 存储过程名;

5、pymysql执行存储过程

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p1', args=(1, 22, 3, 4))# 获取执行完存储的参数cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")result = cursor.fetchall()conn.commit()cursor.close()conn.close()print(result)

6、条件语句
IF-THEN-ELSE语句

delimiter \\CREATE PROCEDURE p3 (        in i1 int)BEGIN    IF i1 = 1 THEN        select * from t1;    ELSEIF i = 2 THEN        select * from t2;    ELSE        select * from t3;    END IF;END\\delimiter ;

CASE-WHEN-THEN-ELSE语句

delimiter \\CREATE PROCEDURE p3 (        in i1 int)BEGIN    CASE i1          WHEN    1   THEN        select * from t1;    WHEN    2   THEN        select * from t2;    ELSE        select * from t3;    END CASE;END\\delimiter ;

7、循环语句
WHILE-DO…END-WHILE

delimiter \\CREATE PROCEDURE p4 ()BEGIN    DECLARE i int;        set i = 1;        WHILE i < 3 DO            select i;            set i = i + 1;        END WHILE;END\\delimiter ;

REPEAT...END REPEAT
此语句的特点是执行操作后检查结果

delimiter \\CREATE PROCEDURE p5 ()BEGIN    DECLARE i int;        set i = 1;        REPEAT            select i;            set i = i + 1;            until i > 3        END REPEAT;END\\delimiter ;

LOOP...END LOOP

delimiter \\CREATE PROCEDURE p5 ()BEGIN    DECLARE i int default 0;        loop_lable: loop            set i = i + 1;            if i < 3 then                iterate loop_lable;            end if;            if i > 5 then                leave loop_lable;            end if;            select i;        END loop loop_lable;END\\delimiter ;

8、存储过程的基本函数
字符串类

CHARSET(str) //返回字串字符集CONCAT (string2 [,... ]) //连接字串INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) //转换成小写LEFT (string2 ,length ) //从string2中的左边起取length个字符LENGTH (string ) //string长度LOAD_FILE (file_name ) //从文件读取内容LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为lengthLTRIM (string2 ) //去除前端空格REPEAT (string2 ,count ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_strRPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为lengthRTRIM (string2 ) //去除后端空格STRCMP (string1 ,string2 ) //逐字符比较两字串大小,SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符UCASE (string2 ) //转换成大写RIGHT(string2,length) //取string2最后length个字符SPACE(count) //生成count个空格

数学类

ABS (number2 ) //绝对值BIN (decimal_number ) //十进制转二进制CEILING (number2 ) //向上取整CONV(number2,from_base,to_base) //进制转换FLOOR (number2 ) //向下取整FORMAT (number,decimal_places ) //保留小数位数HEX (DecimalNumber ) //转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 [,..]) //求最小值MOD (numerator ,denominator ) //求余POWER (number ,power ) //求指数RAND([seed]) //随机数ROUND (number [,decimals ]) //四舍五入,decimals为小数位数SIGN (number2 ) // 正数返回1，负数返回-1

日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区CURRENT_DATE ( ) //当前日期CURRENT_TIME ( ) //当前时间CURRENT_TIMESTAMP ( ) //当前时间戳DATE (datetime ) //返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差DAY (date ) //返回日期的天DAYNAME (date ) //英文星期DAYOFWEEK (date ) //星期(1-7) ,1为星期天DAYOFYEAR (date ) //一年中的第几天EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串MAKETIME (hour ,minute ,second ) //生成时间串MONTHNAME (date ) //英文月份名NOW ( ) //当前时间SEC_TO_TIME (seconds ) //秒数转成时间STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) //两个时间差TIME_TO_SEC (time ) //时间转秒数]WEEK (date_time [,start_of_week ]) //第几周YEAR (datetime ) //年份DAYOFMONTH(datetime) //月的第几天HOUR(datetime) //小时LAST_DAY(date) //date的月的最后日期MICROSECOND(datetime) //微秒MONTH(datetime) //月MINUTE(datetime) //分返回符号,正负或0SQRT(number2) //开平方