SQL 使用总结分享（包含部分存储过程）

您所在的位置：网站首页 › sql语法规则和特点 › SQL 使用总结分享（包含部分存储过程）

SQL 使用总结分享（包含部分存储过程）

2023-06-07 04:36| 来源: 网络整理| 查看: 265

查询语句普通查询 select * from SUSer --特调系统用户表优先查询某一列 select vcUser_id,* from SUSer --把想要查询的某一列放在最前面查询查询取唯一值 select distinct vcUserName from SUSer -- 一个列可能会包含多个重复值，DISTINCT 关键词用于返回唯一不同的值条件语句 where 条件 select * from SUSer where iAuto_id='001' --where 后面跟需要进行判断的条件 --该语句即以iAuto_id='001' 进行条件判断 AND & OR 运算符 SELECT * FROM Websites WHERE country='CN' AND alexa > 50 --and运算符，必须符合and前后的条件,才可以进行查询 SELECT * FROM Websites WHERE country='USA' OR country='CN' --or运算符，or是或者的意思，or前后只要满足一个条件就可以查询 SELECT * FROM Websites WHERE alexa > 15 AND (country='CN' OR country='USA') --and和or结合起来使用 order by 排序

查询大量数据时，为了阅读便利，排序一下，可以通过时间，序号等条件排序

SELECT * FROM Websites ORDER BY alexa DESC --alexa 降序排列 SELECT * FROM Websites ORDER BY alexa ASC --alexa 升序排列 top查询 select top(100)* from SUSer --top(100)查询一个表前100行 like 通配符 select * from suser like '%星%' --找一个名字中含有“星”字的人 in 查询 select * from suser where iAuto_id in ('001','002') -- in 是“包含”的意思条件取iauto_id 包含‘001’，‘002’ 的行 between 查询 SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20 --alexa列介于1到20之间的行数别名 as用法 select vcUser_id as user1,* from SUSer --as 后跟一个新起的别名（给列起一个别名） select * from tDepartmentStaff as td,SUSer as su where td.vcStaff=su.vcUser_id --这个是给表名起一个别名插入语句 insert into SPartTimeDep(vcUser_id,vcDepartment_id,vcDepartment_id_new,vcJob_id,vcCreater_id,dCreateDate) values('CF10331','B80','014003027','ZDD','sys13', GETDATE()) --增加单个用value,增加多个用values --GETDATE()实时获取时间删除语句

delete、drop、truncate三种删除方式

速度：一般来说：drop > truncate > delete

DELETE FROM table_name WHERE condition; --delete删除不影响表结构 --删除语句后面一定要跟where条件，否则删除的就是整个表 DROP TABLE table_name --drop用于删除表 --删除内容和定义，并释放空间，执行drop语句，将使此表的结构一起删除 TRUNCATE TABLE table_name --清空表，但不删除表结构以及表本身更改语句

涉及到更新、删除、增加的语句一定要加where条件！

update tContractPay_bmBudget set vcBudgetCategory='经费' where iAuto_id='118993' --原有的字段是“固定资产”更新成”经费” JOIN连接

示例图：七种连接方法（连接名在个图的第三行）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RXNq4qw7-1686038782042)(C:\Users\muxi\AppData\Roaming\Typora\typora-user-images\image-20230530154727459.png)]

left join 左连接

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

举例：

合同表 :有依赖号有合同号无事业体

依赖表: 有依赖号无合同号有事业体

需求：需要同时看到依赖号、合同号、事业体

select top(100) a.vcYLNo,a.vcContractNo,b.vcUnitEnShort,* from ( select * from tContract ) a left join ( select * from tYLS ) b on a.vcYLNo=b.vcYLNo --tcontract 合同表 tyls 依赖表 --以左表为主表，在右表中没有匹配上的话，就会是NULL right join 右连接

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL

使用方法与left join 类同，这两个熟练使用一个即可，换个位置就是左连接

select top(100) a.vcYLNo,a.vcContractNo,b.vcUnitEnShort,* from ( select * from tContract ) a right join ( select * from tYLS ) b on a.vcYLNo=b.vcYLNo --tcontract 合同表 tyls 依赖表 --以右表为主表，在左表中没有匹配上的话，就会是NULL inner join 连接

取两个集合的并集

select top(100) a.vcYLNo,a.vcContractNo,b.vcUnitEnShort,* from ( select * from tContract ) a inner join ( select * from tYLS ) b on a.vcYLNo=b.vcYLNo --tcontract 合同表 tyls 依赖表 --以两个表只取并集，这条语句只显示了100条，如果想看更多，取消掉top(100)即可,只查询100条，本意是这两个表数据量较大，如果全部查询，影响效率。 full out join 连接

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行

select top(100) a.vcYLNo,a.vcContractNo,b.vcUnitEnShort,* from ( select * from tContract ) a full outer join ( select * from tYLS ) b on a.vcYLNo=b.vcYLNo UNION操作符 UNION操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

select vcYLNo from tContract union select vcYLNo from tYLS --使用union的时候注意要两个表有共同的列 UNION ALL操作符

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

select vcYLNo from tContract union select vcYLNo from tYLS --使用union all是取包括NULL的值约束 NOT NULL约束

指示某列不能存储 NULL 值。

UNIQUE约束

保证某列的每行必须有唯一的值。

PRIMARY KEY约束

NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录

FOREIGN KEY约束

保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK约束

保证列中的值符合指定的条件。

DEFAULT约束

规定没有给列赋值时的默认值。

用法如下

CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, PRIMARY KEY (Id_P) //PRIMARY KEY约束 ) CREATE TABLE Persons ( Id_P int NOT NULL PRIMARY KEY, //PRIMARY KEY约束 LastName varchar(255) NOT NULL, ) --在建表时，需要添加约束，上述包括主键约束，非空约束 AUTO INCREMENT 字段

在建表时，自动生成一个唯一的数字

CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, ) --ID列即可自动生成索引 CREATE INDEX 语法

在表上创建一个简单的索引。允许使用重复的值：

CREATE INDEX index_name ON table_name (column_name) --创建一个索引，名字叫”index_name“ CREATE INDEX PIndex ON Persons (LastName, FirstName) --这个用法是索引多个列 CREATE UNIQUE INDEX 语法 CREATE UNIQUE INDEX index_name ON table_name (column_name) --这个用法是索引必须是唯一的用unique表示唯一函数 AVG() 函数

AVG() 函数返回数值列的平均值

SELECT AVG(column_name) FROM table_name COUNT() 函数

返回匹配指定条件的行数

SELECT COUNT(column_name) FROM table_name MAX() 函数

MAX() 函数返回指定列的最大值

SELECT MAX(column_name) FROM table_name MIN() 函数

MIN() 函数返回指定列的最小值

SELECT MIN(column_name) FROM table_name SUM() 函数

SUM() 函数返回数值列的总数。

SELECT SUM(column_name) FROM table_name GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用

HAVING 子句可以让我们筛选分组后的各组数据

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count) > 200 --having 在这里可以做筛选条件 UCASE() 函数

UCASE() 函数把字段的值转换为大写

SELECT UCASE(column_name) FROM table_name --比如碰到需求，需要将某一列的数据全部从小写转换为大写，就可以用该函数 LCASE() 函数

LCASE() 函数把字段的值转换为小写

SELECT LCASE(column_name) FROM table_name --将某一列的数据全部从大写转换为小写,用法同上面一样 MID() 函数

MID() 函数用于从文本字段中提取字符

SELECT MID(name,1,4) AS ShortTitle FROM Websites --提取该列的前四个字符 NOW() 函数

NOW() 函数返回当前系统的日期和时间

SELECT NOW() FROM table_name --返回当前数据库的时间 FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date FROM Websites 存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

以下存储过程知识的总结就不拿特调系统举例了，特调系统涉及到的存储过程实例较多逻辑性较强。下面涉及到的sql可在该网址中查看

附一个学习存储过程的网址：

https://www.php.cn/faq/486920.html

本次笔记是在学习总结该网站案例中总结的一些使用例子，如需学习其他，可参考该网址。

存储过程特点

1、能完成较复杂的判断和运算 2、可编程行强，灵活 3、SQL编程的代码可重复使用 4、执行的速度相对快一些 5、减少网络之间的数据传输，节省开销

创建简单存储过程

存储过程语法

create procedure 名称() begin ......... end --基本使用语法

创建一个简单的存储过程

create procedure testa() begin select * from users; select * from orders; end

调用存储过程

call testa(); 存储过程变量

变量声明和对变量赋值

create procedure test2() begin -- 使用 declare语句声明一个变量 declare username varchar(32) default ''; -- 使用set语句给变量赋值 set username='xiaoxiao'; -- 将users表中id=1的名称赋值给username select name into username from users where id=1; -- 返回变量 select username; end;

概括 (1)、变量的声明使用declare,一句declare只声明一个变量，变量必须先声明后使用； (2)、变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等； (3)、变量可以通过set来赋值，也可以通过select into的方式赋值； (4)、变量需要返回，可以使用select语句，如：select 变量名。

变量的作用域 create procedure test3() begin begin declare userscount int default 0; -- 用户表中的数量 declare ordercount int default 0; -- 订单表中的数量 select count(*) into userscount from users; select count(*) into ordercount from orders; select userscount,ordercount; -- 返回用户表中的数量、订单表中的数量 end; begin declare maxmoney int default 0; -- 最大金额 declare minmoney int default 0; -- 最小金额 select max(money) into maxmoney from orders; select min(money) into minmoney from orders; select maxmoney,minmoney; -- 返回最金额、最小金额 end; end;

1、变量作用域说明： (1)、存储过程中变量是有作用域的，作用范围在begin和end块之间，end结束变量的作用范围即结束。 (2)、需要多个块之间传值，可以使用全局变量，即放在所有代码块之前 (3)、传参变量是全局的，可以在多个块之间起作用 2、通过一个实例来验证变量的作用域

需求: 创建一个存储过程，用来统计表users、orders表中行数数量和orders表中的最大金额和最小金额

存储过程参数 create procedure 名称([IN|OUT|INOUT] 参数名参数数据类型 ) begin ......... end

概括： 1、可变变量INOUT:调用时可传入值，在调用过程中，可修改其值，同时也可返回值； 2、INOUT参数集合了IN和OUT类型的参数功能； 3、INOUT调用时传入的是变量，而不是常量；

存储过程条件语句

条件语句基本结构

if() then...else...end if;

多条件判断语句

if() then... elseif() then... else ... end if;

举例：

编写存储过程，如果用户userId是偶数则返回username,否则返回userId

create procedure test7(in userId int) begin declare username varchar(32) default ''; if(userId%2=0) then select name into username from users where id=userId; select username; else select userId; end if; end;

存储过程的多条件语句应用示例

需求：根据用户传入的uid参数判断

（1）、如果用户状态status为1，则给用户score加10分；

（2）、如果用户状态status为2，则给用户score加20分；

（3）、其他情况加30分

create procedure test8(in userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; if(my_status=1) then update users set score=score+10 where id=userid; elseif(my_status=2) then update users set score=score+20 where id=userid; else update users set score=score+30 where id=userid; end if; end; 存储过程循环语句

while语句

需求：使用循环语句，向表test1(id)中插入10条连续的记录

create procedure test9() begin declare i int default 0; while(i=10 -- 如果i>=10,则跳出循环 end repeat; end; 存储过程游标的使用

游标是保存查询结果的临时区域

需求：编写存储过程，使用游标，把users表中 id为偶数的记录逐一更新用户名

create procedure test11() begin declare stopflag int default 0; declare username VARCHAR(32); -- 创建一个游标变量，declare 变量名 cursor ... declare username_cur cursor for select name from users where id%2=0; -- 游标是保存查询结果的临时区域 -- 游标变量username_cur保存了查询的临时结果，实际上就是结果集 -- 当游标变量中保存的结果都查询一遍(遍历),到达结尾，将变量stopflag设置为1，用于循环中判断是否结束 declare continue handler for not found set stopflag=1; open username_cur; -- 打开游标 fetch username_cur into username; -- 游标向前走一步，取出一条记录放到变量username中 while(stopflag=0) do -- 如果游标还没有结尾，就继续 begin -- 在用户名前门拼接 '_cur' 字符串 update users set name=CONCAT(username,'_cur') where name=username; fetch username_cur into username; end; end while; -- 结束循环 close username_cur; -- 关闭游标 end; 自定义函数

创建一个简单的函数

create function getusername(userid int) returns varchar(32) reads sql data -- 从数据库中读取数据，但不修改数据 begin declare username varchar(32) default ''; select name into username from users where id=userid; return username; end;

概括：

1.创建函数使用create function 函数名(参数) returns 返回类型； 2.函数体放在begin和end之间； 3.returns指定函数的返回值； 4.函数调用使用select getusername()。

触发器

触发器与函数、存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作。

需求：出于审计目的，当有人往表users插入一条记录时，把插入的userid,username,插入动作和操作时间记录下来

create trigger tr_users_insert after insert on users for each row begin insert into oplog(userid,username,action,optime) values(NEW.id,NEW.name,'insert',now()); end;

创建成功后，给uses表中插入一条记录：

insert` `into` `users(id,``name``,age,status,score,accontid) ``values``(6,``'小周'``,23,1,``'60'``,``'10001'``)；

总结

1、创建触发器使用create trigger 触发器名 2、什么时候触发？after insert on users,除了after还有before,是在对表操作之前（before）或者之后（after）触发动作的。 3、对什么操作事件触发？ after insert on users,操作事件包括insert,update,delete等修改操作； 4、对什么表触发? after insert on users 5、影响的范围？for each row

流程控制

case分支

基本语法结构

case ... when ... then.... when.... then.... else ... end case;

users表中，根据userid获取status值，如果status为1，则修改score为10；如果status为2，则修改为20，如果status3，则修改为30；否则修改为40。

create procedure testcate(userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; case my_status when 1 then update users set score=10 where id=userid; when 2 then update users set score=20 where id=userid; when 3 then update users set score=30 where id=userid; else update users set score=40 where id=userid; end case; end; 存储过程+event（事件）

举例：

需求：设计一个福彩的开奖过程，没3分钟开奖一次第一步：先编写一个存储过程open_lottery,产生3个随机数，生成一条开奖记录第二步：编写一个时间调度器，每3分钟调用一次这个过程

create procedure open_lottery() begin insert into lottery(num1,num2,num3,ctime) select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now(); end; create event if not exists lottery_event -- 创建一个事件 on schedule every 3 minute -- on schedule 什么时候来执行，没三分钟执行一次 on completion preserve do call open_lottery;

【本文地址】

公司简介

联系我们