sql (以myql 为例)
DQL语言
基础查询
语法
select 查询列表 from 表名;
特点
1、查询列表可以是字段、常量、表达式、函数，也可以是多个查询列表
2、查询结果是一个虚拟表
3、from 后的表可以是 虚拟表，这也以为这可以嵌套子句。
例如：select count(1) from (select distinct name,sex from student)
起别名： 字段 as 空格 ‘别名’ 注意：别名不要写 ' '好像会报错 例如： select sex '性别' from student (×) select sex 性别 from student (√)
去重 select distinct 字段名 from 表名;
注意：
表达式中
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;结果都为null
条件查询
语法：
select 查询列表
from 表名
where 筛选条件
筛选条件的分类
1、简单条件运算符 > < = <> != >= <= <=>安全等于
2、逻辑运算符 && and 、 || or 、 ! not
3、模糊查询
like: 一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符
4、between and
select * from student where sno between 100 and 120； //注意它包括100和120
5、in/not in/exists/not exists / some/all/any/exists 后加虚拟表
6、is null /is not null：用于判断null值 例如 select * from student where name is null
in / not in
SELECT NAME, SNO FROM STUDENT WHERE SUBSTR(NAME,0,1) IN ('李','王','赵' )
SELECT NAME SNO FROM STUDENT WHERE SNO IN (SELECT SNO FROM SCORE WHERE record >97)
注意：in的子句只能返回一个字段
all/some/any
select * from score1 where record >all (select record from score2) //查询表score1 中record 都比表score2的大的
select * from score1 where record > any (select record from score2) //查询表score1 中record 存在比表score2大的。
1、any和some等价
2、=any 等价于 in
3、<>any 等价于 not in
exists/not exists
exists：强调的是，是否有返回集，有则返回true,反之返回false。
例如： select * from student where exists（select * from class where classId=‘001’）
如果不存在课程001，则整个where都是false。 在遍历student表时，每条记录都放不进虚拟表中，故查不到结果。（select * from student where sno=‘001’，将整个student一行一行的遍历，当该行中的sno=‘001’时，即为true时，将该行（记录）放入 虚拟表中）。
例如：select * from student s where EXISTS（select * from class where s.name='李） 理解 参考相关子查询。
只有当student记录代入符合等于 ‘李’时，where才为真，记录才会被放入虚拟表中
not exist 是否有返回集，有则返回true,反之返回false。
exists和in区别
1. exists适用于内表数据量大于外表的情况，而in 适用于外表数据量大于内表数据量。
2. in引导的子句只能返回一个字段，exists可以返回多个字段
排序查询
一、语法
select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc desc】
二、特点
1、asc ：升序，如果不写默认升序
desc：降序
2、排序列表 支持 单个字段、多个字段、函数、表达式、别名。（只能从查询结果的模拟表中经过加工处理得到的就行）
3、order by的位置一般放在查询语句的最后（除limit语句之外）
分组查询
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意：
这里的字段是分组后的字段，即group by 后的字段。 否则会报错。
例如：SELECT NAME FROM STUDENT GROUP BY SNO HAVING NAME like '李%'； // 很显然是错误的，因为分组后的字段只有 sno。
where是筛选条件还是表字段
连接查询
按功能分：
内连接（连接/普通连接/自然连接） 注意：内连接会忽略连接字段为null的
等值（丢弃不相等的数据）
非等值
自连接
外连接
左外（左连接） 以join左表的为主表，后等值连接。没有的null补上
右外（右连接）
全外（mysql不支持）
交叉连接（笛卡尔积）
全连接等于左连接并右连接
年代分：sql92（内联） sql99（所有）
sql92内连接的语法
select 查询列表
from 表 别名1,表 别名2
where 等值的连接条件/不等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
sql99内连接的语法
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
外连接的语法：
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
子查询
嵌套在其他语句内部的select语句称为子查询或内查询，
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果为select语句，则此语句称为外查询或主查询
(相关、不相关)子查询 按结果集的行列 分类：
标量子查询（单行子查询）：结果集为一行一列一个值
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为 1行多列
表子查询：结果集为多行多列
总结：一个子查询会返回一个标量（就一个值）、一个行、一个列或一个表，这些子查询称之为标量、行、列和表子查询
不相关子查询
select * from student where sno in ( select sno from sc where score > 97)
像这种类型的查询是 先执行子查询，得到一个集合（或值），然后将这个集合（或值）作为一个常量带入到父查询的 WHERE 子句中去。如果单纯地执行子查询，也是可以成功的。这种类型的查询，叫做 ”不相关子查询“。
相关子查询
select cno, sno from sc x where grade >=(select AVG(grade) from sc y where y.sno=x.sno); // 查询每个学生超过他所选课程的平均成绩的课程和学号
像这种子查询需要用到父查询中的值的查询叫做 相关子查询。
解释执行过程 : 先扫描父表 x，将当前记录中的子句需要的字段 的值， 待入子句查询中。比如: 父表扫描到记录： sc{ ‘sno’：‘s009’, ‘cno’:'c002', grade: 96 }。则将记录中的 's009' 代入子句中 即子句变为 select AVG(grade) from sc y where y.sno=‘s009’。然后将子句中查出来的结果 代入回 父查询中。比如子查询结果为 60。则父查询的where 语句为 grade>= 60。 然后再将 记录中96和子查询结果60 比较，结果为true。则将结果放入虚拟表中。以此类推，去遍历整张父表。最终得到结果。
各个子查询出现的位置
select后面：
仅仅支持标量子查询
例如： 显示 班级信息以及班级人数 select class.* , (select count(*) from student where student.classId= class.Id ) 人数 from class
from后面：
表子查询
where或having后面：
标量子查询
列子查询
行子查询
exists后面：
标量子查询
列子查询
行子查询
表子查询
分页查询
select 查询列表
from 表
limit 【offset，】size;
注意：
offset代表的是起始的条目索引，默认从0卡死。（包括offset）
size代表的是显示的条目数
SqlServer、oracle是没有limit 的，oracle可以通过 rownum实现。但rownum不是表里的字段， 会变 （坑）
例如 select * from student where rownum>10 是查不出来的 原因是：遍历到第一条记录时，判断rownum>10 为false。移动到第二条记录时，第二条记录的rownum会变为 1，接着代入判断。以此类推，是那不到数据的。
所以要将rownum表 转为 字段才行， select * from ( select rownum, student.* from student ) where rownum>10 (√) 但 select * from student where rownum < 10 (√)
sqlServer可以通过over_number()
联合查询
将多次查询结果合并成一个结果， 相当于 "并"
要求： 字段名称可以不同，字段数量、数据类型、顺序必须相同。
例如：
select sno,name from student
union
select cno,name from course
查出来的虚拟表字段名是第一个
DML语言
插入
insert into 表名(字段名,...) values(值,...)……; //oracle不支持多列
insert into 表名(字段名,...) 查询语句; 例：select course(cno,name) select sno,name from student;
或
insert into 表名 set 字段=值,字段=值,...; 例：insert into student set sno='05', name='赵四'
修改
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
update t1,t2 set t1.age=2000, t2.age=3000 where t1.id=1 and t2.id=1; //修改多个表
删除
单表删除
delete from 表名 【where 筛选条件】【limit 条目数】
或
truncate table 表名
例子： delete student,course.* from student,course where student.sno='01'; // 清空表course,删除student中sno='01'的记录
区别：
1. truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2. delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4. truncate没有返回值
delete可以返回受影响的行数
5. truncate不可以回滚
delete可以回滚
级联删除
语法：
delete 别名1,别名2 from 表1 别名
inner|left|right join 表2 别名
on 连接条件
【where 筛选条件】
DDL语言
库的管理
一、创建库
create database 【if not exists】 库名【 character set 字符集名】;
二、修改库
alter database 库名 character set 字符集名;
三、删除库
drop database 【if exists】 库名;
表的管理
一、创建表
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
....
字段名 字段类型 【约束】
)
二、修改表
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
三、删除表
drop table【if exists】 表名;
四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
常见的约束
NOT NULL：非空，该字段的值必填
UNIQUE：唯一，该字段的值不可重复
DEFAULT：默认，该字段的值不用手动插入有默认值
CHECK：检查，mysql不支持
PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null
FOREIGN KEY：外键，该字段的值引用了另外的表的字段
主键和唯一
1、区别：
1. 一个表至多有一个主键，但可以有多个唯一
2. 主键不允许为空，唯一可以为空
2、相同点
都具有唯一性
都支持组合键，但不推荐
外键：
1、用于限制两个表的关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般就是主键）
4、插入数据，先插入主表
删除数据，先删除从表
可以通过以下两种方式来删除主表的记录
方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
二、创建表时添加约束
create table 表名(
字段名 字段类型 not null, --非空
字段名 字段类型 primary key, --主键
字段名 字段类型 unique, --唯一
字段名 字段类型 default 值, --默认
constraint 约束名 foreign key(字段名) references 主表（被引用列）
)
注意：
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以，但对主键无效
列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
三、修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;
四、自增长列
特点：
1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment
如果要更改起始值：手动插入值
如果要更改步长：更改系统变量
set auto_increment_increment=值;
2、一个表最多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束
TCL语言
事务
含义
事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行
特点（ACID）
A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的
D 持久性：一个事务一旦提交了，则永久的持久化到本地
三、事务的使用步骤
了解：
隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
显式事务：具有明显的开启和结束
使用显式事务：
①开启事务
set autocommit=0;
start transaction;#可以省略
②编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete
设置回滚点：
savepoint 回滚点名;
③结束事务
提交：commit;
回滚：rollback;
回滚到指定的地方：rollback to 回滚点名;
四、并发事务
1、事务的并发问题是如何发生的？
多个事务 同时 操作 同一个数据库的相同数据时
2、并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据
3、如何解决并发问题
通过设置隔离级别来解决并发问题
4、隔离级别
数据库提供的4 种事务隔离级别:
Oracle 支持的2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为:READ COMMITED
Mysql 支持4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
每启动一个mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量@@tx_isolation, 表示当前的事务隔离级别.
查看当前的隔离级别:
SELECT @@tx_isolation;
设置当前mySQL 连接的隔离级别:
set transaction isolation level read committed;
设置数据库系统的全局的隔离级别:
set globaltransaction isolation level read committed;
测试：
select @@tx_isolation #查看隔离级别 set transaction isolation leve read uncommtted #更改隔离级别 use database D select * from T; #看信息 set autocommit=0; #开启事务 #--------开启另一个窗口 ，窗口2 select @@tx_isolation #查看隔离级别 set transaction isolation leve read uncommtted #更改隔离级别 use database D select * from T; #看信息 set autocommit=0; #开启事务 #-------回窗口1 updata T set name=‘12’ where id=1; #----回窗口2 select * from T; #能查看到窗口1改的数据，出现脏读， #理论上是两个窗口提交事务后，重新开才能读到 commit #提交事务 #----窗口1 rollback #回滚 commit #提交 select * from T; #看是否回滚 set transaction isolation leve read commtted #改隔离级别 #---窗口2 set transaction isolation leve read commtted; select * from T; #----窗口1 updata T set name=‘12’ where id=1; #---窗口2 select * from T; #没有脏读 #--窗口1 commit #提交 #---窗口2 select * from T; #读到了窗口1改的数据出现重复读
#窗口1 set transaction isolation leve repeatable read; set autocommit=0; select * from T; #窗口2 set transaction isolation leve repeatable read; set autocommit=0; insert into T values('aa','aa'); commit; #窗口1 select * from T; /*没有添加*/ update T set name=‘12’; select * from T; /*窗口2的数据添加进来了并修改了，出现幻读*/
设置回滚点
savepoint 回滚点名;
回滚到指定的地方：rollback to 回滚点名;
MySql中的存储引擎
在mysql中的数据用各种不同的技术存储在文件（或内存）中。
通过show engines；来查看mysql支持的存储引擎。
视图
mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
视图就是由SELECT语句组成的查询定义的虚拟表，是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张数据表一样，对表能够进行的一般操作都可以应用于视图。
好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性
二、创建
create view 视图名 as 查询语句;
create view test as
select sno, name from student union select * from course //这样就创建了一个视图。 同时这个test可以相当于table使用
三、修改
方式一：
create or replace view 视图名 as 查询语句;
方式二：
alter view 视图名 as 查询语句
四、删除
drop view 视图1，视图2,...;
五、查看
desc 视图名;
show create view 视图名;
六、使用
1.插入
insert
2.修改
update
3.删除
delete
4.查看
select
注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图
七、视图和表的对比
| 关键字 | 占用物理空间 | 使用 | |
| 视图 | view | 占用较小，只保存sql逻辑 | 一般用于查询 |
| 表 | table | 保存实际的数据 | 增删改查 |
变量
分类
一、系统变量
说明：变量由系统提供的，不用自定义
语法：
①查看系统变量
show 【global|session 】variables like ''; 如果没有显式声明global还是session，则默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session
③为系统变量赋值
方式一：
set 【global|session 】 变量名=值; 如果没有显式声明global还是session，则默认是session
方式二：
set @@global.变量名=值;
set @@变量名=值；
1、全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效
2、会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
自定义变量
用户变量
作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面
使用：
①声明并赋值：
set @变量名 = 值;
或
set @变量名 := 值;
或
select @变量名 := 值;
②更新值
方式一：
set @变量名 = 值;
或
set @变量名 := 值;
或
select @变量名 := 值;
方式二：
select xx into @变量名 from 表;
③使用
select @变量名;
局部变量
作用域：仅仅在定义它的begin end中有效
位置： 只能放在begin end中，而且只能放在第一句
使用：
①声明
declare 变量名 类型 【default 值】;
②赋值或更新
方式一：
set 变量名 = 值;
或
set 变量名 := 值;
或
select @变量名 := 值;
方式二：
select xx into 变量名 from 表;
③使用
select 变量名;
存储过程和函数
MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。
好处：
通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
复用：存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
安全：存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
缺点：
1. 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不偏于逻辑运算。
2. 很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
很少使用的原因：
1. 存储过程本身的缺点
2. 分析三层架构可知，业务逻辑应该放到业务层，也就Tomcat，而不是把业务滞留到数据库来处理，将业务和数据库严重耦合在一起了！
3.一般对业务性能进行扩容非常好，搭建集群、使用缓存提高响应速度等等。总之，大多数情况下并不是业务层是整个项目性能的瓶颈，而是数据库！我们应该尽可能的优化数据库方面的性能，而且业务层性能扩容相对于数据库性能扩容要方便的多。因此我们应该尽可能的优化数据库方面的性能，降低数据层的压力，把所有压力能分单到其他地方就分担，而不是让数据库增加压力！
存储过程
一、创建
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end
注意：
1.参数模式：in、out、inout，其中in可以省略，in表示必须要作为参数传进来，作为计算。out表示可以作为返回值。inout，可以作为参数，也可以作为返回值
2.存储过程体的每一条sql语句都需要用分号结尾，而;默认是结束。所以要更改结束符
delimiter $ /*设置结束符*/ create procedure mypl() begin insert into admin(username,'password') values('aa','aaa'),('as','as'); end $ /*结束*/
二、调用
call 存储过程名(实参列表)
举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; /*定义变量 */ call sp1(@name); /*调用存储*/ select @name; /*打印存储*/
调用inout模式的参数：set @name=值; call sp1(@name); select @name;
三、查看
show create procedure 存储过程名;
四、删除
drop procedure 存储过程名;
五、例子
-- if语句 带有传参的 create procedure test1(in id varchar(12), out result varchar(12) ) begin select grade into id from sc where sno=id; if grade > 90 then set result='厉害'； elseif grade>60 then set result='及格了'； else select result ='不及格'; end if; end ; call test1( '01' , @Result ); --创建会话变量 select @Result; --打印结果 -- case 语句 根据月份打印季节 带有局部变量的 CREATE PROCEDURE test2 (in month int) begin declare season varchar(6) case when month >=1 and month<=3 then set season='spring'; when month >=4 and month<=6 then set season='summer'; when month >=7 and month<=9 then set season='autumn'; when month >=10 and month<=12 then set season='winter'; end case; end ; call test2(2)； -- while循环 1+……+n create procedure test3 (in n int) begin declare total int default 0; declare i int default 1; while i<= n do set total = total+i; set i=i+1; end while; select total; end; -- repeat循环 类似do-while create procedure test4 (n int) begin declare total int default 0; repeat set total = total+n; set n=n-1; until n=0 --结束条件，后面不要加分号 end repeat; select total; end; -- loop循环 create procedure test5 (n int) begin declare total int default 0; sum:loop -- 定义循环标识 set total = total+n; set n=n-i; if n<1 then leave sum; -- 跳出循环 end if; end loop; -- 标识循环结束 select total; end;
函数
一、创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end
注意：函数体中肯定需要有return语句
二、调用
select 函数名(实参列表);
三、查看
show create function 函数名;
四、删除
drop function 函数名；
五、存储过程和存储函数的区别
- 存储函数的限制比较多，例如不能用临时表,只能用表变量，而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
- 返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out，inout)。
- 调用时的不同。存储函数嵌入在SQL中使用，可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。
- 参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，in、out和inout：
- in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
- out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
- inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。
六、例子
-- 创建存储函数 drop function if exists fun_stu; create function fun_stu (id varchar(6)) return varchar(3) begin declare scope int; select sno into scope from student where sno=id; return scope; end； --调用 select fun_stu('01')； -- 删除 drop function if existd fun_stu;
游标
游标是用来存储查询结果集的数据类型，可以帮我们保存多条行记录结果，我们要做的操作就是读取游标中的数据获取每一行的数据；
一、声明游标
declare cursor_name cursor for statement;
二、打开游标
open cursor_name;
三、关闭游标
close cursor_name;
例子：
create procedure test1() -- 默认是输入(in)参数 begin declare sno int(11); declare `name` varchar(20); declare classId int(11); declare has_data int default 1; -- 定义游标结束标识符 declare stu_result cursor for select * from student; declare exit handler for not FOUND set has_data=0; -- 监测游标结束 open stu_result; -- 打开游标 repeat fetch stu_result into sno,`name`,class_id; select concat('id: ',id,';name: ',`name`,';class_id',class_id); until has_data=0 -- 退出条件,注意不要打分号 end repeat; close stu_result; -- 关闭游标 end ; call test1();
临时表
临时表顾名思义就是临时要用创建的表，临时表的作用仅限于本次会话，等连接关闭后重新打开连接临时表将不存在;
create temporary table temp_table( id int, name varchar(10) ); insert into temp_table values (1,'1'); select * from temp_table ; show tables; -- 不会显示临时表的存在 //存储过程创建临时表 create procedure pro1() begin create temporary table temp_table( id int ); insert into temp_table values(1); select * from temp_table; end; call pro1();
流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或者多条路径中选择一条去执行
循环结构：程序在满足一定条件的基础上，重复执行一段代码-- if语句 带有传参的 create procedure test1(in id varchar(12), out result varchar(12) ) begin select grade into id from sc where sno=id; if grade > 90 then set result='厉害'； elseif grade>60 then set result='及格了'； else select result ='不及格'; end if; end ; call test1( '01' , @Result ); --创建会话变量 select @Result; --打印结果 -- case 语句 根据月份打印季节 带有局部变量的 CREATE PROCEDURE test2 (in month int) begin declare season varchar(6) case when month >=1 and month<=3 then set season='spring'; when month >=4 and month<=6 then set season='summer'; when month >=7 and month<=9 then set season='autumn'; when month >=10 and month<=12 then set season='winter'; end case; end ; call test2(2)； -- while循环 1+……+n create procedure test3 (in n int) begin declare total int default 0; declare i int default 1; while i<= n do set total = total+i; set i=i+1; end while; select total; end; -- repeat循环 类似do-while create procedure test4 (n int) begin declare total int default 0; repeat set total = total+n; set n=n-1; until n=0 --结束条件，后面不要加分号 end repeat; select total; end; -- loop循环 create procedure test5 (n int) begin declare total int default 0; sum:loop -- 定义循环标识 set total = total+n; set n=n-i; if n<1 then leave sum; -- 跳出循环 end if; end loop; -- 标识循环结束 select total; end;
常见的内置函数
concat函数
功能：拼接字符 select concat(字符1，字符2，字符3,...);
ifnull函数
功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;
isnull函数
功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0
数值函数
abs(x): 返回绝对值
ceil(x)/floor(x): 向上/下 取整
mod(x,y):返回x与y的模
rand():返回0-1之间的随机数 select round(rand()*100);
round(x,y):返回参数x的y位小数的四舍五入结果
truncate(x,y):返回数字x截断为y位小数的结果
字符函数
concat(s1,s2....sn) 把传入的参数连接成一个字符串
substr(str,start, len) 返回字符str从位置start起，长度为 len 的子字符串
upper( str )/ lower(str ) 转换大小写
replace( str, a,b)：在字符串中用字符串b替换掉所有的字符串a
length(str) ：返回字节长度
left(str，len)/ right( str，len) 返回从左边/右边起 长度为len的字符串 例：select right('abcdef',3); // def
trim(str)/ ltrim(str) /rtrim(str): 去前后/ 左边/ 右边 空格
lpad(str1, len,str2)/rpad(str1,len,str2 )：在str1中 左/右填充 str2。填到总长度为len 为止 例：rpad('12',4,'a');// 12aa rpad('12',4,'abc') ; //12ab (没有c)
instr(str1,str2): 获取子串第一次出现的索引 例：select instr('0123456','5'); //6
insert(str1, start, len, str2 ) 从str1的start位置开始，删除 len 长度的字符串，并在此插入 str2 例：select insert('12345','2','3','a'); // 1a5
strcmp(s1,s2):如果S1比S2小，返回-1；如果S1比S2大则返回1；如果相等则返回0（比较的是ASC2码）
日期函数
now()：返回当前日期+时间
curdate:返回当前日期
year( date)/ month(date) / day(date) 返回指定日期的 年/月/日
curtime()：返回当前时间
hour(time )/ minute( time ) / second(time ) 返回指定时间的 时/分/秒
date_format( time, ftp ): 将日期转换成字符 例： select date_format(now(),"%Y-%m-%d %H:%i:%s");
str_to_date(str, ftp ): 将字符转换成日期 例：select str_to_date('1999,11,1,11,1,1', '%Y,%m,%d,%H,%i,%s' ); //ftp 是字符格式，非转后的日期格式
datediff(str1,str2 )/ datediff(data1,data2 ): 返回两个日期相差的天数 例：SELECT DATEDIFF('2017-08-17','2017-08-17');
monthname:以英文形式返回月
其他函数
version() 当前数据库服务器的版本
database() 当前打开的数据库
user() 当前用户
password('字符')：返回该字符的密码形式
md5('字符') 返回该字符的md5加密形式
分组函数
max 最大值 min 最小值
sum 和 avg 平均值
count 计算个数
注意：
分组函数都忽略null
都可以搭配distinct使用，实现去重的统计
效率上：
MyISAM存储引擎，count(*)最高
InnoDB存储引擎，count(*)和count(1)效率>count(字段)
和分组函数一同查询的字段，要求是group by后出现的字段（原因不是一对一的关系）
例如：select avg(scope), sno from student 这相当于整体作为组，求平均值是唯一的，但学号是众多的。所以一同查询的字段要是group by后的
with as
with as短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。 mysql8.0引入的。
-- with as 形成虚拟表，提供部分数据 with tmp(x,y,z) as (select * from tb_name), -- 可以写虚拟表的字段 tmp2 as (select * from tb_name2) select * from tmp1，tmp2 where tmp.x=tmp.x; with stu as( select * from student ) -- stu 可以当表用 select * from stu,sc where stu.sno=sc.sno
想玩的
leetcode https://leetcode-cn.com/problemset/database/
浙公网安备 33010602011771号