SQL语言基础

SQL基础

　　标识符、注释、数据类型、常量与变量、操作符、表达式

函数

　　系统函数

　　存储函数

流程控制语句

游标

存储过程

 

SQL基础

SQL标识符

@：以@开头的标识符表示一个局部变量或者一个函数的参数

@@：以@@开头的标识符表示一个全局变量

注释

单行注释：##

多行注释：/*    */

数据类型

在SQL中，表和视图的列、局部变量、函数的参数和返回值、存储过程的参数和返回值（具有返回代码）和表达式等都具有相关的数据类型。

常量与变量

常量：常量也称为文字值或标量值，是指程序运行中值始终不改的量。

变量：变量就是在程序执行过程中，其值是可以改变的量。

　　系统定义和维护的全局变量：

　　　　系统全局变量是MySQL系统提供并赋值的变量。全局变量以两个@符号开头。

# 使用系统全局变量@@VERSION查看当前使用的MySQL的版本信息
select  @@version;

　　用户定义用来保存中间结果的局部变量：

　　　　局部变量是作用域局限在一定范围内的SQL对象。局部变量被引用时要在其名称前加上标志@。

　　　　局部变量的声明和赋值

　　　　　　使用DECLAREA语句声明局部变量，局部变量的作用范围在它被声明的BEGIN…END复合语句内。　　　　　　　　

　　　　　　DECLAREA语句的语法格式如下：

　　　　　　　　DECLARE 变量名[,…] 数据类型 [DEFAULT 默认值];

　　　　　　声明局部变量后要给局部变量赋值，直接给变量赋值可以使用SET语句。SET语句的语法格式如下：

　　　　　　　　SET 变量名=表达式[,变量名=表达式,…];

## 将局部变量var1声明为char类型，长度值为10，并为其赋值为“程菲”，SQL语句如下：
begin
declare var1 char(10);
set @var1='程菲';
end


##通过局部变量查看D_sample数据库中的学生信息，条件是查看student表中“政治面貌”为“中共党员”的学生信息。SQL语句如下：
use D_sample;
set @政治面貌='中共党员';
select * from student
where 政治面貌=@政治面貌;

　　　　在MySQL中，还可以使用SELECT…INTO语句把数据表中选定的字段值直接存储到变量中。

　　　　其语法格式如下：

　　　　　　SELECT <字段名>[,...] INTO <变量名>[,...] FROM <表名>;

## 通过局部变量查看D_sample数据库中的学生信息，条件是查看student表中学号”为“201507002”的学生姓名和性别信息。SQL语句如下：
use D_sample;
select 姓名,性别 into @name,@sex from student
where 学号='201507002';
select @name,@sex;

注意：局部变量分为存储变量和会话变量。

　　　　存储变量就是使用DECLAREA语句创建的变量，并且初值为NULL的变量。

　　　　会话变量就是直接使用set 或者select into语句赋值，不进行定义的变量，这个变量在这次会话框中都有效，并且初值即为赋的值。

操作符

SQL的操作符和其它高级语言的操作符类似，由变量、常量和函数连接起来并指定在一个或多个表达式中执行的操作。

表达式

 在SQL语言中，表达式由变量、常量、操作符、函数等元素组成。

## 在D_sample数据库中，查询一个按平均成绩降序排列的结果集，包括学生“学号”、“平均成绩”及“考生信息”3列，其中考生信息列又由学生“姓名”、“性别”这些来自student表的数据组成。


use D_sample;
-- concat是一个函数，连接函数。space是表示空格函数。产生参数个空格。
select a.学号,avg(成绩) as '平均成绩',concat(姓名,space(6),性别) as '考生信息'  
from sc a inner join student b on a.学号=b.学号
group by a.学号   -- 分组后才有平均成绩那一项。
order by 平均成绩 desc;

函数

系统函数

数学函数

数学函数对数值表达式进行数学运算，并将运算结果返回给用户。

ABS(数值表达式)函数用来获得一个数的绝对值。

select  abs(-876),abs(-2.345);

FLOOR(数值表达式)函数用于获得小于一个数的最大整数值，CEILING(数值表达式)函数用于获得大于一个数的最小整数值。

select  floor(-1.2), ceiling(-1.2), floor(9.9), ceiling(9.9);

ROUND(数值表达式)函数用于获得一个数的四舍五入的整数值。

select round(34.567,2), round(19.8,0); 

SIGN(数值表达式)函数返回数字的符号，返回的结果是正数（1）、负数（-1）或者零（0）。

select sign(-2), sign(2), sign(0);

SQRT(数值表达式)函数返回一个数的平方根。

select sqrt(25), sqrt(15), sqrt(1);

字符串函数

字符串函数是对字符串(char 或varchar数据类型)输入值执行操作，并返回一个字符串或数字值。

ASCII(字符表达式) 函数可返回字符表达式中最左侧字符的ASCII码值。

select  ascii('A'), ascii('a'), ascii('中文');

CHAR(整型表达式)函数将整型的ASCII码转换为字符。

select char(65),char(97);

LEFT(字符表达式,整型表达式)函数返回字符串中从左边开始的指定个数的字符。RIGHT(字符表达式,整型表达式) 函数返回字符串从右边开始的指定个数的字符。

select left('数据库应用技术',3), right('数据库应用技术',2);

LENGTH(字符表达式)函数返回某个指定字符串的长度，不计字符串后的空格。

select length('中华人民共和国'), length('Tsinghua University press');

LOWER(字符表达式)函数将字符表达式中的大写字母转换为小写字母。UPPER(字符表达式) 函数将字符表达式中的小写字符转换为大写字符。

select lower('WonDERful'), upper('Tsinghua University press');

LTRIM(字符表达式)函数返回删除了前导空格字符后的字符表达式。RTRIM(字符表达式)函数截断所有尾部空格后返回一个字符串。

select concat(rtrim('计算机应用技术专业    '),ltrim('   数据库应用技术'));

CONCAT(字符串1,字符串2,...)函数返回连接参数产生的字符串。

select concat(‘MySQL’,’数据库管理系统’);

SUBSTRING(字符表达式,起始点,n)函数返回字符表达式中从“起始点”开始的n个字符。

use D_sample;
select substring(学号,5,2) as 专业大类代码
from student;

时间日期函数

日期时间函数对日期和时间输入值执行操作，将返回一个字符串、数字或日期和时间值。

CURDATE()函数返回当前日期。YEAR(日期)、MONTH(日期)和DAY(日期)函数返回日期的年、月和日。

select curdate(),year('2016-1-12'), month('2016-1-12'),day('2016-1-12');

ADDDATE(日期,INTERVAL 数值 日期元素)函数可按照“日期元素”给定的日期单位，返回“日期”加上“数值”的新日期。

select adddate('2016-1-12',interval -2 year), adddate('2016-1-12',interval 3  month), adddate('2016-1-12',interval 60 day);

DAYNAME(日期)函数返回日期对应的工作日名称。

select dayname('2015-03-01');

DATEDIFF(日期1,日期2)函数返回起始时间日期1和结束时间日期2之间的天数。

select datediff('2011-3-3', '2014-4-6');

数据类型转换函数

数据类型转换函数就是把一个值转换为指定的数据类型。

CAST(表达式 AS 数据类型)函数将表达式的类型转换为指定的数据类型。

select concat('考试成绩是：',cast(90 as char(5)));

CONVERT(表达式,数据类型(长度))函数将表达式的类型转换为指定的数据类型。

select concat(‘考试成绩是：’,convert(90,char(5)));

聚合函数

聚合函数常用于对一组值进行计算，然后返回单个值。聚合函数主要用于select 语句的group by子句、having子句。

　　count(字段)：计算个数

　　max(字段)：计算该字段的最大值

　　min(字段)：计算字段的最小值

　　sum(字段)：计算该字段的和

　　avg(字段)：计算字段的平均值

控制流程函数

控制流程函数的作用是进行条件判断。根据判断条件，执行不同的分支并将运算结果返回给用户。

IF(表达式1,表达式2,表达式3)函数表达式1为真将返回表达式2的值，否则返回表达式3的值。

select if(23%2=0,’是偶数’,’是奇数’);

CASE 输入值 WHEN 匹配值1 THEN 结果1 [WHEN 匹配值2 THEN 结果2...] [ELSE 其它结果] END函数输入值与哪个匹配值匹配则返回相应的结果值。

SELECT 学号,                                      
    CASE FLOOR(成绩/10)                    
        WHEN 10 THEN '优秀'              
        WHEN 9 THEN '优秀'                 
        WHEN 8 THEN '良好'              
        WHEN 7 THEN '中等'
        WHEN 6 THEN '及格'
        ELSE '不及格'
        END  AS '成绩等级'
FROM sc;

其它函数

1）加密函数

MD5(字符串)函数以32位十六进制数字的形式返回为字符串算出一个 MD5 128比特校验和。

select md5(‘MySQL’);

PASSWORD(字符串)函数从原文密码字符串中计算并返回密码字符串。

select password(‘newpwd’);

2）信息函数

USER()函数返回当前登录的用户名。

select user();

DATABASE()函数返回当前数据库名。

select database();

存储函数(自定义函数)

创建存储函数前提：

　　在MySQL中，服务器处理语句的时候是以分号为结束标志的。使用DELIMITER语句将MySQL语句的结束标志修改为其他符号。
　　DELIMITER语法格式如下：
　　　　DELIMITER $$
　　要想恢复使用分号作为结束符，执行下面语句即可：
　　DELIMITER ;

存储函数的创建

创建存储函数语法格式如下：

　　CREATE FUNCTION 存储函数名([参数名 参数的数据类型[,…]])
　　RETURNS  函数返回值的数据类型
　　BEGIN
　　函数体;
　　RETURN 表达式;
　　END

创建一个存储函数，返回两个参数中的最大值。

delimiter $$
create function max1(i int,j int)
returns int
begin
declare k int;
if i>j then
set k=i;
else
set k=j;
        end if;
return k;
end$$
delimiter ;

存储函数的调用

当调用创建的存储函数时，可以利用SELECT语句调用函数。
使用SELECT语句调用max1函数。

select max1(2,7);

流程控制语句

顺序控制语句

BEGIN…END可以定义SQL语句块，其语法格式如下：

BEGIN
SQL语句|SQL语句块;
END

在D_sample数据库中创建一个存储函数，返回指定学号的学生信息：

use D_sample;
delimiter $$
create function search1(xh char(9))
returns char(9)
begin
    return (select * from student where 学号=xh);
end$$      
delimiter ;

分支控制语句

IF…ELSE语句

用于指定 SQL 语句的执行条件。其语法格式如下：

IF <逻辑表达式> THEN
<SQL语句|SQL语句块>;
[ELSE
< SQL语句|SQL语句块>;]
    END IF;

创建一个存储函数f1，输入一个数判断其奇偶数。如果是偶数输出0，否则输出1。

delimiter $$
create function f1(j int)
returns int
begin
declare i int;
if i%2=0 then
return 0;
else
return 1;
end if;
end$$
delimiter ;

调用函数f1。SQL语句如下：

select f1(23);

CASE语句

CASE关键字可根据表达式的真假来确定是否返回某个值，可以允许使用表达式的任何位置使用这一关键字。使用CASE语句可以进行多个分支的选择。CASE语句具有如下两种格式。
CASE表达式是将某个表达式与一组简单表达式进行比较以确定结果。其语法格式如下：

CASE <输入表达式>
WHEN <表达式> THEN <语句>;
[WHEN <表达式> THEN <语句>[...]];
[ELSE <语句>];
END CASE;

第二种格式：

CASE
WHEN <表达式> THEN <语句>;
[WHEN <表达式> THEN <语句>[...]];
[ELSE <语句>];
END CASE;

输入学生的考试成绩，按照优秀、良好、中等、合格及不合格显示成绩。SQL语句如下：

delimiter $$
create function score1(i int)
returns char(6)
begin
    declare j int;
declare cj char(6);
case 
when  j>=90 then set cj='优秀';
when  j>=80 and j<90 then set cj='良好';
when  j>=70 and j<80 then set cj='中等';
when  j>=60 and j<70 then set cj='合格';
else set cj='不合格';
end case;
return cj;
end$$    
delimiter ;

循环控制语句

WHILE语句是设置重复执行SQL语句或语句块的条件。其语法格式如下：

WHILE <逻辑表达式> DO
<SQL语句|SQL语句块>;
END WHILE;

使用WHILE语句计算1～100之和。

delimiter ##
create function sum1()             
returns int                                    
begin                                             
declare s int default 0;               
declare i int default 1;              
while i<=100 do                           
    set s=s+i;
    set i=i+1;
end while;
return s;
end##
delimiter ;

游标

游标概念：游标是类似于C语言指针一样的结构，在MySQL中，它是一种数据访问机制，允许用户访问单独的数据行，而不是对整个行集进行操作。

游标的使用步骤：

1.声明游标

声明游标的语法格式如下：

DECLARE <游标名> CURSOR
FOR SELECT语句;

在D_sample数据库中为了student表创建一个普通的游标，定义名称为stu_cursor。

use D_sample;
declare stu_cursor cursor
for select * from student;

2.打开游标

打开游标的语法格式如下：

OPEN <游标名>;

打开前面创建的stu_cursor游标。

open stu_cursor;

3.检索游标

其语法格式如下：

FETCH <游标名> INTO <变量名>[，变量名]...;

在打开的stu_cursor游标之后，使用FETCH语句来检索游标中的可用的数据。

fetch stu_cursor into xh;
while found do
set i=i+1;
fetch stu_cursor into xh;
end while;

4.关闭游标

关闭游标的语法格式如下：

CLOSE <游标名>;

在检索游标stu_cursor后可用CLOSE语句来关闭它。

close stu_cursor;

存储过程

存储过程概述

存储过程：存储过程(Stored Procedure)是一组完成特定功能的SQL语句集，经编译后存储在数据库中。

优点：

(1)存储过程在服务器端运行，执行速度快。

(2)存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

(3)存储过程提供了安全机制。

(4)存储过程允许模块化程序设计。

(5)存储过程可以减少网络通信流量。

创建和调用存储过程

创建

在MySQL系统中，可以使用CREATE PROCEDURE语句创建存储过程。其语法格式如下：

CREATE PROCEDURE <存储过程名称> ([IN|OUT|INOUT] 参数 数据类型[,...])
BEGIN
过程体;
END

创建一个存储过程，在数据库D_sample的student表中查询政治面貌为“共青团员”的学生的学号、姓名、性别及政治面貌信息。

use D_sample;
delimiter $$
create procedure cp_student(in zzmm char(8))
begin
select 学号,姓名,性别,政治面貌
from student
where 政治面貌=zzmm
order by 学号;
end$$
delimiter ;

调用

可以利用CALL语句调用存储过程，其语法格式如下：

CALL [数据库名.]<存储过程名称>([参数[,...]]);

执行上例“cp_student”存储过程代码：

call cp_student(‘共青团员’);

在D_sample数据库的student表中创建一个存储过程，通过建立一个性别参数为同一存储过程指定不同的性别，用于返回不同性别的学生信息，

# #建立存储过程
use D_sample;
delimiter @@
create procedure cp_sex(in xb char(2))
begin
select * from student    where 性别=xb;
end@@
delimiter ;

##执行带有输入参数的存储过程“cp_sex”
call cp_sex('女');

创建了一个名为“cp_score”的存储过程。它使用两个参数：“p_姓名”为输入参数，用于指定要查询的学生姓名，“p_成绩”为输出参数，用来返回学生的成绩

## 创建存储过程
use D_sample;
delimiter $$
create procedure cp_score(in p_name char(10),out p_score decimal(4,1))
begin
select b.成绩 into p_score from student a,sc b
where a.学号=b.学号  and 姓名=p_name;
end$$
delimiter ;


## 调用存储过程
以上代码为了接收某一存储过程的返回值，需要一个变量来存放返回参数的值。
call cp_score('高峰',@p_score);
select concat('高峰','的成绩是：',@p_score) as 结果为：;

注意：如果要使用带参数的存储过程，需要在执行过程中提供存储过程的参数值。可以使用两种方式来提供存储过程的参数值。

　　(1)直接方式：该方式在CALL语句中直接为存储过程的参数提供数据值，并且这些数据值的数量和顺序与定义存储过程时参数的数据和顺序相同。

　　(2)间接方式：该方式是指在执行CALL语句之前，声明参数并且为这些参数赋值，然后在CALL语句中引用这些已经获取数据值的参数名称。

管理存储过程

修改存储过程

使用ALTER PROCEDURE语句来修改现有存储过程的特征。修改存储过程的基本语法格式如下：

ALTER PROCEDURE <存储过程名称> 
[{{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT '注释内容'}];

修改存储过程“cp_student”的定义。具有写数据权限，并且调用者可以执行。

alter procedure cp_student
modifies sql data
sql security invoker;

删除存储过程

可使用DROP PROCEDURE语句从当前的数据库中删除用户定义的存储过程。删除存储过程的基本语法格式如下：

DROP PROCEDURE [IF EXISTS] <存储过程名称>

删除“cp_student”存储过程。

drop procedure if exists cp_student;

查看存储过程

查看存储过程的定义信息

使用SHOW PROCEDURE STATUS语句。查看存储过程的定义信息基本语法格式如下：

SHOW PROCEDURE STATUS [LIKE '参数'];

查看“cp_score”存储过程的定义文本信息。

show procedure status like 'cp_score';

查看存储过程的详细信息

使用SHOW CREATE PROCEDURE语句。查看存储过程的详细信息基本语法格式如下：

SHOW CREATE PROCEDURE <存储过程的名称>;

查看“cp_score”存储过程的详细文本信息。

show create procedure cp_score;

存储过程的异常处理

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

-------------------------