SQL基础
SQL标识符
@：以@开头的标识符表示一个局部变量或者一个函数的参数
@@：以@@开头的标识符表示一个全局变量
注释
单行注释：##
多行注释：/* */
数据类型
在SQL中，表和视图的列、局部变量、函数的参数和返回值、存储过程的参数和返回值（具有返回代码）和表达式等都具有相关的数据类型。
常量与变量
常量：常量也称为文字值或标量值，是指程序运行中值始终不改的量。
变量：变量就是在程序执行过程中，其值是可以改变的量。
系统定义和维护的全局变量：
系统全局变量是MySQL系统提供并赋值的变量。全局变量以两个@符号开头。
1 # 使用系统全局变量@@VERSION查看当前使用的MySQL的版本信息 2 select @@version;
用户定义用来保存中间结果的局部变量：
局部变量是作用域局限在一定范围内的SQL对象。局部变量被引用时要在其名称前加上标志@。
局部变量的声明和赋值
使用DECLAREA语句声明局部变量，局部变量的作用范围在它被声明的BEGIN…END复合语句内。
DECLAREA语句的语法格式如下：
DECLARE 变量名[,…] 数据类型 [DEFAULT 默认值];
声明局部变量后要给局部变量赋值，直接给变量赋值可以使用SET语句。SET语句的语法格式如下：
SET 变量名=表达式[,变量名=表达式,…];
1 ## 将局部变量var1声明为char类型，长度值为10，并为其赋值为“程菲”，SQL语句如下： 2 begin 3 declare var1 char(10); 4 set @var1=‘程菲‘; 5 end 6 7 8 ##通过局部变量查看D_sample数据库中的学生信息，条件是查看student表中“政治面貌”为“中*员”的学生信息。SQL语句如下： 9 use D_sample; 10 set @政治面貌=‘中*员‘; 11 select * from student 12 where 政治面貌=@政治面貌;
在MySQL中，还可以使用SELECT…INTO语句把数据表中选定的字段值直接存储到变量中。
其语法格式如下：
SELECT <字段名>[,...] INTO <变量名>[,...] FROM <表名>;
1 ## 通过局部变量查看D_sample数据库中的学生信息，条件是查看student表中学号”为“201507002”的学生姓名和性别信息。SQL语句如下： 2 use D_sample; 3 select 姓名,性别 into @name,@sex from student 4 where 学号=‘201507002‘; 5 select @name,@sex;
注意：局部变量分为存储变量和会话变量。
存储变量就是使用DECLAREA语句创建的变量，并且初值为NULL的变量。
会话变量就是直接使用set 或者select into语句赋值，不进行定义的变量，这个变量在这次会话框中都有效，并且初值即为赋的值。
操作符
SQL的操作符和其它高级语言的操作符类似，由变量、常量和函数连接起来并指定在一个或多个表达式中执行的操作。
表达式
在SQL语言中，表达式由变量、常量、操作符、函数等元素组成。
1 ## 在D_sample数据库中，查询一个按平均成绩降序排列的结果集，包括学生“学号”、“平均成绩”及“考生信息”3列，其中考生信息列又由学生“姓名”、“性别”这些来自student表的数据组成。 2 3 4 use D_sample; 5 -- concat是一个函数，连接函数。space是表示空格函数。产生参数个空格。 6 select a.学号,avg(成绩) as ‘平均成绩‘,concat(姓名,space(6),性别) as ‘考生信息‘ 7 from sc a inner join student b on a.学号=b.学号 8 group by a.学号 -- 分组后才有平均成绩那一项。 9 order by 平均成绩 desc;
函数
系统函数
数学函数
数学函数对数值表达式进行数学运算，并将运算结果返回给用户。
ABS(数值表达式)函数用来获得一个数的绝对值。
1 select abs(-876),abs(-2.345);
FLOOR(数值表达式)函数用于获得小于一个数的最大整数值，CEILING(数值表达式)函数用于获得大于一个数的最小整数值。
1 select floor(-1.2), ceiling(-1.2), floor(9.9), ceiling(9.9);
ROUND(数值表达式)函数用于获得一个数的四舍五入的整数值。
1 select round(34.567,2), round(19.8,0);
SIGN(数值表达式)函数返回数字的符号，返回的结果是正数（1）、负数（-1）或者零（0）。
1 select sign(-2), sign(2), sign(0);
SQRT(数值表达式)函数返回一个数的平方根。
1 select sqrt(25), sqrt(15), sqrt(1);
字符串函数
字符串函数是对字符串(char 或varchar数据类型)输入值执行操作，并返回一个字符串或数字值。
ASCII(字符表达式) 函数可返回字符表达式中最左侧字符的ASCII码值。
1 select ascii(‘A‘), ascii(‘a‘), ascii(‘中文‘);
CHAR(整型表达式)函数将整型的ASCII码转换为字符。
1 select char(65),char(97);
LEFT(字符表达式,整型表达式)函数返回字符串中从左边开始的指定个数的字符。RIGHT(字符表达式,整型表达式) 函数返回字符串从右边开始的指定个数的字符。
1 select left(‘数据库应用技术‘,3), right(‘数据库应用技术‘,2);
LENGTH(字符表达式)函数返回某个指定字符串的长度，不计字符串后的空格。
1 select length(‘*‘), length(‘Tsinghua University press‘);
LOWER(字符表达式)函数将字符表达式中的大写字母转换为小写字母。UPPER(字符表达式) 函数将字符表达式中的小写字符转换为大写字符。
1 select lower(‘WonDERful‘), upper(‘Tsinghua University press‘);
LTRIM(字符表达式)函数返回删除了前导空格字符后的字符表达式。RTRIM(字符表达式)函数截断所有尾部空格后返回一个字符串。
1 select concat(rtrim(‘计算机应用技术专业 ‘),ltrim(‘ 数据库应用技术‘));
CONCAT(字符串1,字符串2,...)函数返回连接参数产生的字符串。
1 select concat(‘MySQL’,’数据库管理系统’);
SUBSTRING(字符表达式,起始点,n)函数返回字符表达式中从“起始点”开始的n个字符。
1 use D_sample; 2 select substring(学号,5,2) as 专业大类代码 3 from student;
时间日期函数
日期时间函数对日期和时间输入值执行操作，将返回一个字符串、数字或日期和时间值。
CURDATE()函数返回当前日期。YEAR(日期)、MONTH(日期)和DAY(日期)函数返回日期的年、月和日。
1 select curdate(),year(‘2016-1-12‘), month(‘2016-1-12‘),day(‘2016-1-12‘);
ADDDATE(日期,INTERVAL 数值 日期元素)函数可按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期。
1 select adddate(‘2016-1-12‘,interval -2 year), adddate(‘2016-1-12‘,interval 3 month), adddate(‘2016-1-12‘,interval 60 day);
DAYNAME(日期)函数返回日期对应的工作日名称。
1 select dayname(‘2015-03-01‘);
DATEDIFF(日期1,日期2)函数返回起始时间日期1和结束时间日期2之间的天数。
1 select datediff(‘2011-3-3‘, ‘2014-4-6‘);
数据类型转换函数
数据类型转换函数就是把一个值转换为指定的数据类型。
CAST(表达式 AS 数据类型)函数将表达式的类型转换为指定的数据类型。
1 select concat(‘考试成绩是：‘,cast(90 as char(5)));
CONVERT(表达式,数据类型(长度))函数将表达式的类型转换为指定的数据类型。
1 select concat(‘考试成绩是：’,convert(90,char(5)));
聚合函数
聚合函数常用于对一组值进行计算，然后返回单个值。聚合函数主要用于select 语句的group by子句、having子句。
count(字段)：计算个数
max(字段)：计算该字段的最大值
min(字段)：计算字段的最小值
sum(字段)：计算该字段的和
avg(字段)：计算字段的平均值
控制流程函数
控制流程函数的作用是进行条件判断。根据判断条件，执行不同的分支并将运算结果返回给用户。
IF(表达式1,表达式2,表达式3)函数表达式1为真将返回表达式2的值，否则返回表达式3的值。
1 select if(23%2=0,’是偶数’,’是奇数’);
CASE 输入值 WHEN 匹配值1 THEN 结果1 [WHEN 匹配值2 THEN 结果2...] [ELSE 其它结果] END函数输入值与哪个匹配值匹配则返回相应的结果值。
1 SELECT 学号, 2 CASE FLOOR(成绩/10) 3 WHEN 10 THEN ‘优秀‘ 4 WHEN 9 THEN ‘优秀‘ 5 WHEN 8 THEN ‘良好‘ 6 WHEN 7 THEN ‘中等‘ 7 WHEN 6 THEN ‘及格‘ 8 ELSE ‘不及格‘ 9 END AS ‘成绩等级‘ 10 FROM sc;
其它函数
1）加密函数
MD5(字符串)函数以32位十六进制数字的形式返回为字符串算出一个 MD5 128比特校验和。
1 select md5(‘MySQL’);
PASSWORD(字符串)函数从原文密码字符串中计算并返回密码字符串。
1 select password(‘newpwd’);
2）信息函数
USER()函数返回当前登录的用户名。
1 select user();
DATABASE()函数返回当前数据库名。
1 select database();
存储函数(自定义函数)
创建存储函数前提：
在MySQL中，服务器处理语句的时候是以分号为结束标志的。使用DELIMITER语句将MySQL语句的结束标志修改为其他符号。
DELIMITER语法格式如下：
DELIMITER $$
要想恢复使用分号作为结束符，执行下面语句即可：
DELIMITER ;
存储函数的创建
创建存储函数语法格式如下：
1 CREATE FUNCTION 存储函数名([参数名 参数的数据类型[,…]]) 2 RETURNS 函数返回值的数据类型 3 BEGIN 4 函数体; 5 RETURN 表达式; 6 END
创建一个存储函数，返回两个参数中的最大值。
1 delimiter $$ 2 create function max1(i int,j int) 3 returns int 4 begin 5 declare k int; 6 if i>j then 7 set k=i; 8 else 9 set k=j; 10 end if; 11 return k; 12 end$$ 13 delimiter ;
存储函数的调用
当调用创建的存储函数时，可以利用SELECT语句调用函数。
使用SELECT语句调用max1函数。
1 select max1(2,7);
流程控制语句
顺序控制语句
BEGIN…END可以定义SQL语句块，其语法格式如下：
1 BEGIN 2 SQL语句|SQL语句块; 3 END
在D_sample数据库中创建一个存储函数，返回指定学号的学生信息：
1 use D_sample; 2 delimiter $$ 3 create function search1(xh char(9)) 4 returns char(9) 5 begin 6 return (select * from student where 学号=xh); 7 end$$ 8 delimiter ;
分支控制语句
IF…ELSE语句
用于指定 SQL 语句的执行条件。其语法格式如下：
1 IF <逻辑表达式> THEN 2 <SQL语句|SQL语句块>; 3 [ELSE 4 < SQL语句|SQL语句块>;] 5 END IF;
创建一个存储函数f1，输入一个数判断其奇偶数。如果是偶数输出0，否则输出1。
1 delimiter $$ 2 create function f1(j int) 3 returns int 4 begin 5 declare i int; 6 if i%2=0 then 7 return 0; 8 else 9 return 1; 10 end if; 11 end$$ 12 delimiter ;
调用函数f1。SQL语句如下：
1 select f1(23);
CASE语句
CASE关键字可根据表达式的真假来确定是否返回某个值，可以允许使用表达式的任何位置使用这一关键字。使用CASE语句可以进行多个分支的选择。CASE语句具有如下两种格式。
CASE表达式是将某个表达式与一组简单表达式进行比较以确定结果。其语法格式如下：
1 CASE <输入表达式> 2 WHEN <表达式> THEN <语句>; 3 [WHEN <表达式> THEN <语句>[...]]; 4 [ELSE <语句>]; 5 END CASE;
第二种格式：
1 CASE 2 WHEN <表达式> THEN <语句>; 3 [WHEN <表达式> THEN <语句>[...]]; 4 [ELSE <语句>]; 5 END CASE;
输入学生的考试成绩，按照优秀、良好、中等、合格及不合格显示成绩。SQL语句如下：
1 delimiter $$ 2 create function score1(i int) 3 returns char(6) 4 begin 5 declare j int; 6 declare cj char(6); 7 case 8 when j>=90 then set cj=‘优秀‘; 9 when j>=80 and j<90 then set cj=‘良好‘; 10 when j>=70 and j<80 then set cj=‘中等‘; 11 when j>=60 and j<70 then set cj=‘合格‘; 12 else set cj=‘不合格‘; 13 end case; 14 return cj; 15 end$$ 16 delimiter ;
循环控制语句
WHILE语句是设置重复执行SQL语句或语句块的条件。其语法格式如下：
1 WHILE <逻辑表达式> DO 2 <SQL语句|SQL语句块>; 3 END WHILE;
使用WHILE语句计算1～100之和。
1 delimiter ## 2 create function sum1() 3 returns int 4 begin 5 declare s int default 0; 6 declare i int default 1; 7 while i<=100 do 8 set s=s+i; 9 set i=i+1; 10 end while; 11 return s; 12 end## 13 delimiter ;
游标
游标概念：游标是类似于C语言指针一样的结构，在MySQL中，它是一种数据访问机制，允许用户访问单独的数据行，而不是对整个行集进行操作。
游标的使用步骤：
1.声明游标
声明游标的语法格式如下：
1 DECLARE <游标名> CURSOR 2 FOR SELECT语句;
在D_sample数据库中为了student表创建一个普通的游标，定义名称为stu_cursor。
1 use D_sample; 2 declare stu_cursor cursor 3 for select * from student;
2.打开游标
打开游标的语法格式如下：
1 OPEN <游标名>;
打开前面创建的stu_cursor游标。
1 open stu_cursor;
3.检索游标
其语法格式如下：
1 FETCH <游标名> INTO <变量名>[，变量名]...;
在打开的stu_cursor游标之后，使用FETCH语句来检索游标中的可用的数据。
1 fetch stu_cursor into xh; 2 while found do 3 set i=i+1; 4 fetch stu_cursor into xh; 5 end while;
4.关闭游标
关闭游标的语法格式如下：
1 CLOSE <游标名>;
在检索游标stu_cursor后可用CLOSE语句来关闭它。
1 close stu_cursor;
-------------------------