本篇文章记录MySQL相关知识点,包括基础语法以及个人扩展。
基础知识点
前引:本篇知识小结是本人总结《MySQL必知必会》一书的笔记,如有内容不符的内容,烦请联系。内容较少的章节合并到了相邻章节。扩充内容补充到了相似章节。
第一章 了解SQL:概念篇
数据库:是一个以某种有组织的方式存储的数据的集合。(通常理解为保存有组织的数据的容器)
数据库软件应该称为DBMS(数据库管理系统),数据库是通过DBMS创建和操作的容器。
表:是一种结构化的文件,可以用来存储某种特定类型的数据。
SQL指结构化查询语言,是一种专门用来与数据库通信的语言。
SQL使我们有能力访问数据库
SQL是一种ANSI的标准计算机语言
RDBMS:指的是关系型数据库管理系统,是SQL的基础,同样是所有现代数据库的基础,比如:SQL server,DB2,Oracle,MySQL等
RDBMS中的数据存储在被称为表的数据库对象中,表是相关数据项的集合,它由列和行组成。
现代的SQL服务器构建在rdbms之上
DBMS数据库管理系统:是一种可以访问数据库中数据的计算机程序,使我们有能力在数据库中提取,修改或者存储信息,不同的dbms提供不同的函数供查询,提交以及修改数据。
关系型数据库管理系统也是一种数据库管理系统,其数据库是根据数据间的关系来组织和访问数据的。
可以把SQL分为两个部分:数据操作语言DML 和数据定义语言DDL
DML:select update delete insert into
DDL:create alter database/table drop table create index drop index
第二章 什么是MySQL:
数据的所有存储,检索,管理和处理实际上是由数据库软件DBMS完成的。
DBMS可分为两类:一类是基于共享文件系统的DBMS,另一类为基于客户机-服务器的DBMS。(MySQL,Oracle,SQLserver等),这里客户机可以理解为:安装有sql yog的计算机(本人选的是这个),服务器是指在电脑上安装的有MySQL管理系统的计算机(自己测试通常是在一台电脑上)。
命令行登录:mysql -hlocalhost -u root -p 之后键入密码。(注意已经配置过环境变量了,否则需要找到安装路径bin目录下执行该命令)
第三章 使用MySQL
use 数据库名
show databases;
show tables;
show columns from 表名;等同于:describe customers
show create customers
show create database newdb 显示创建语句。show create table 表名;
第四章 检索数据
select *
from products;一般来说除非确实需要每个列,否则最好别使用通配符 *
select distnct id
from table;
distinct 关键字应用于所有列而不是他的前置列,Distinct 后面可以跟多个字段滤重,跟单个一样也是保证唯一性。
limit 检索的时候是从第0行开始的,不是第一行。
count(),count*和1的区别:没有区别,count(列名)和count1有些区别,有主键,count(主键)快,没有主键count1快。Count(列名)会去除空值,要注意
第五章 排序检索数据--order by
通常排序的列是显示所选择的列,但是并不一定是这样,用非检索的列排序数据也是合法的。
默认是升序排列 ASC ,降序 DESC 需要指定。升序降序结合使用的时候是不一样的,要注意。
第六章 过滤数据--where
= <> != < <= > >= between 常用操作符,MySQL的between是包含两端的,有的不包括,请注意。
空值 is not null is null
And :第一个和第二个条件都成立
Or:第一个和第二个只要成立一个条件即可成立
and 用来指示检索所有给定的条件。
or 任意条件满足即可成立。注意:and的优先级要比or高,当组合使用的时候记得加上括号。
where (id=12 or id=13)and salary=20
in 在某个范围之内,与or差不多,
两者的差别:
当使用多个候选清单的时候 in 更直观,
in的操作符更少,计算次序更容易管理
in最大的优点是可以包含子查询
第八章 通配符过滤数据-- like _
like 匹配多个,_ 下划线匹配一个,最好不要把like放在开头位置
第九章 正则表达式
正则表达式的作用是匹配文本,将一个正则表达式与一个文本串进行比较。
where name regexp '100'
where name regexp '.100' .表示匹配任意一个字符。
请注意:like '100' 和 regexp '100'
like 表示匹配整个列,如果被匹配的文本在列值中出现则like不会返回它。
后者会在列值中进行匹配。
默认不区分大小写 如果要区分 where name regexp binary '100'
or 匹配:| regexp '100|200'
[]匹配:regexp '[123] ton' 123任意一个都可以。等同于[1|2|3],[^123]匹配除了123之外的字符
where name regexp '[1-5] ton' 范围性匹配。
注意当匹配特殊字符的时候需\\ 为前导。where ’\\.‘
转义字符多数使用 单个\ ,MySQL本身使用两个\\
第十章 计算字段--
concat(name,'hh',salary)
trim rtrim ltrim 去掉左右两边的空格。
第十一章 使用数据处理函数--
length() 返回长度
lower() 小写
upper()大写
sunstring()截取
编辑
切记日期要为 yyyy-MM-dd 格式的
第十二章 汇总数据聚合函数
AVG()
count():count(*)对表中的数据计数,不管是不是包含空值,count(列名)计数会忽略空值。
max()
min()
sum():会忽略列值为 null 的行。
group by having where 和 having 的 区别:where 在数据分组前过滤,having 在数据分组后过滤。
order by
第十五章 表连接--
笛卡尔积:表连接没有关联条件时候会产生。
常用 inner join left join right join
组合查询:union 使用场景
在单个查询中从不同的表中返回类似结构的组合查询
对单个表执行多个查询,按单个查询返回数据。
union 会自动去除重复行 ,union all 不会
第十八章 全文本搜索-
innodb不支持全文本搜索,myisam支持。
数据插入:
insert into table
values('11','aa','2000'),('12','ab','3000')
insert into table (id,name,salary)
values('11','aa','2000'),('12','ab','3000')
插入的时候如果带有列名 可不按顺序也不影响,表结构改变也不影响。最好不要使用不带列名的插入。
insert into newtable (cust_id,cust_name,cust_aa)
select cust_id,cust_name,cust_aa from table;
列名不一定要一样,因为插入只是负责把新数据插入到指定的列,跟别的没有关系。
更新数据:update
update table
set id=99
where name='aa'
更新多个列:
update table
set id=99,
salary='50'
where name='aa';
ignore 关键字,当要更新多行的时候,如果其中的一行或者多行出现一个错误,则整个更新被取消,包括之前更新过的值。 发生错误也要更新可使用 update ignore table 。
如果想删除整列数据 可以把该列置为空 null
删除一行 :delete from table where id='20' delete删除表中数据而不删除表本身。
如果要删除所有行 可使用 truncate table ,他的速度更快。(它实际上是删除原来的表并重新创建一个新表,而不是逐行删除表中数据。)
Delete from table where 列名称=值
DELETE FROM orders WHERE company='nw'
删除所有行:delete from table 保留表结构
Truncate table 表名
Drop table 表名
区别:
truncate 是整体删除(速度快),delete是逐条删除(数度慢)
truncate 删除不记录MySQL日志,不可恢复数据,delete记录服务器日志,这也是truncate比delete效率高的原因。
Truncate不激活trigger触发器,但是会重置identity(标识列,自增字段)相当于自增列会被置为初始值,
Delete identity 还是会累加。
第二十一章 创建和操纵表-
create table if not exists customers
create table customers
(
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_address char(50) null,
primary key (cust_id)
)ENGINE=InnoDB;
不要把null与空串混淆,null是没有值,他不是空串,,如果指定‘ ’,(两个单引号,其间没有字符),这在not null中是允许的,,空串是一个有效的值,他不是无值。
创建由多个列组成的主键:create table 表名
order_num int not null,
item_price decimal(8,2) not null,
primary key(order_num,order_item)
)ENGINE=InnoDB;
AUTO_INCREMENT:代表自动增量,每插入一行会自动增加。
每个表只允许一个AUTO_INCREMENT,而且它必须被索引,
如果在插入值的时候指定这个值,那么后续的增量将从这个值开始。后面你可能不知道最后这个值是谁,可用 select last_insert_id() 查询最后一个 AUTO_INCREMENT值。
指定默认值:prod_id int not null default 1
mysql有多种引擎,这些引擎都隐藏在MySQL服务器内,不同的存储引擎具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
innodb 是一个可靠的事物处理引擎,它不支持全文本搜索。
memory在功能上等同于myisam,但是由于数据是存储在内存而不是磁盘中,速度很快(特别适合临时表);
mysiam是一个性能及高的引擎,它支持全文本搜索,但不支持事物处理。
所支持的引擎的完整列表以及不同请参阅:http://dev.mysql.com/doc/refman/5.0/en/storage_engines.html
引擎类型可以混用,但是有一个大缺陷,外键不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
更新表:
alter table 表名 add 列名 char(20);
alter table 表名 drop column 列名;
添加外键:
alter table 表名
add constraint 外键名
foreign key (order_num) references orders (order_num);
重命名表:rename table customer to newtables
多个表重新命名:中间用逗号隔开即可。
第二十二章 使用视图-
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
视图重用sql语句。
简化复杂的SQL操作。
使用表的部分数据而不是整个表。
保护数据,可以给用户授予表的特定部分的访问权限,而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。视图本身不包含数据。
视图的规则和限制:
与表名一样,视图必须唯一命名。
创建的视图数目没有限制
视图可以嵌套
视图不能索引,也不能有关联的触发器或默认值。
查询时视图可以和表一起使用。
create view
show create view viewname
更新视图:先删除 drop view viewname,再创建,或者 直接用 create or replace view
视图可以更新基本的表,但是基本上不建议这样使用。
第二十三章 使用存储引擎-
存储过程:简单来说就是为以后的使用而保存的一条或者多条MySQL语句的集合。
使用的理由:
通过把处理封装在容易使用的单元中,简化了复杂的操作。
由于不要求反复建立一系列处理步骤,保证了数据的完整性。
简化对变动的管理,如果表名,列名或者业务逻辑有变化,只需要更改存储过程的代码,使用他们的人员甚至不需要知道这些变化。
提高性能,存储过程要比使用单独的SQL语句要快。
但是存储过程的编写要比基本的sql语句复杂。
执行存储过程:
call 存储过程名(@pricelow,
@pricehigh,
@priceaverage);
存储过程可以显示结果也可以不显示。
create procedure 存储过程名()
begin
select avg(prod_price) as 别名
from table;
end;
可以变更分隔符:delimiter //
create procedure 存储过程名()
begin
select avg(prod_price) as 别名
from table;
end//
delimiter ;
使用存储过程:call 存储过程名();
实际上存储过程是一种函数。
删除:drop procedure 存储过程名 不加括号
如果在删除的过程中不存在会报错,可用 drop procedure if exists
变量:内存中一个特定的位置,用来临时存储数据。
create procedure 名(
out p1 declmal(8,2),
out p2 decimal(8,2)
)
begin
select min(prod-price)
into p1
from products;
select max(prod-price)
into p2
from products;
end;
执行:call 名(@pricelow,
@pricehigh);
所有MySQL变量都必须以@开始。
select @pricelow;
--
create procedure 名(
in onumber int,
out ototal decimal(8,2)
)
begin
select sum(iten*quantity)
from tabel
where order_num=onumber
into ototal;
end;
调用:call ordertotal(2005,@total);
select @total
显示存储过程名:show create procedure 名;
show procedure status ;显示所有的存储过程列表。
可加过滤条件:show procedure status like '名'
游标:有时,需要在检索出来的行中前进或者后退一行或者多行,这就是使用游标的原因。游标是一个存储在MySQL服务器上的数据库查询,他不是一条select语句,而是被该语句检索出来的结果集。MySQL游标只能用于存储过程和函数。
创建游标:create procedure 名()
begin
declare 游标名 cursor
for
select order_num from orders;
end;存储过程完成后游标就消失了,因为他局限于存储过程。
打开:open 游标名
close 游标名;
触发器:是MySQL响应一下任意语句而执行的一条MySQL语句。delete insert update,其他不支持。
创建触发器:唯一的触发器名
触发器关联的表
触发器应该响应的活动(delete,insert,update)
触发器核实执行(处理之前或者之后)
create trigger 触发器名 after insert on 表名
for each row select 'aaaaa'
只有表才支持触发器,视图临时表不支持。
触发器按每个事件每次定义,每个表每个事件每次只允许一个触发器,每个表最多支持6个,之前和之后。单一触发器不能和多个事件或者多个表关联。
drop trigger 触发器名。触发器不能更新或覆盖,为了修改一个触发器,必须先删除然后再创建。
第二十六章 管理事物处理
事务处理:并非所有的引擎都支持事物处理,它用来维护数据库的完整性,它保证成批的sql操作要么完全执行,要么完全不执行。
事务:指一组SQL语句。
回退:指撤销指定SQL语句的过程
提交:指将未存储的SQL语句结果写入数据库表
保留点:指事务处理中设置的临时占位符,你可以对它发布回退。(与回退整个事务处理不同)。
管理事务处理的关键在于将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。
start transaction
rollback;
commit;
事务处理块中,提交不会隐含的进行,未进行明确的提交,使用commit语句。
start transaction;
delete from 表 where order_num=20010;
delete from 表2 where order_num=20010;
commit;
隐含事务关闭 当commit 或者 rollback语句执行后,事务会自动关闭。
使用保留点:复杂的事务会使用,复杂的事务处理可能需要部分提交或回退。
创建占位符:savepoint deletel;
每个保留点都取标识它的唯一名字,以便在回退时,MySQL知道回退到何处,
rollback to delete1;
保留点越多越好,更加的灵活。保留点在事务处理完成后自动释放,也可以用 release savepoint 明确的释放保留点。
修改是否自动提交:set autocommit=0; 他是针对每个连接的而不是服务器。
第二十七章 全球化和本地化
字符集:为字母和符号的集合。
编码:为某个字符集成员的内部表示。
校对:为规定字符如何比较的指令。
使用何种字符集和校对的决定在服务器,数据库和表级进行。
show character set ; 查看所有可用的字符集。和默认的校对。
show collation;显示所有的校对,以及他们适用的字符集。
create table table
(
lie int,
lie varchar(20)
)default character set hebrew
collate hebrew_general_ci;
第二十八章 安全管理-
USE mysql;
SELECT USER FROM USER;
CREATE USER ben IDENTIFIED BY 'root'
RENAME USER ben TO benben;
set password for ben=password('new')
set password =password('new') 不指定用户名时更新当前用户的口令。
DROP USER benben;
SHOW GRANTS FOR ben;
GRANT SELECT ON newdb.* TO ben;
SHOW GRANTS FOR ben;
REVOKE SELECT ON newdb.* TO ben;
新建连接 :IP 可以用本机local host 或者通过ipconfig 查看即可。
grant 和 revoke可在几个层次上控制访问权限:
整个服务器,使用grant all 和 revoke all;
整个数据库:使用 on databases.*
特定的表:on database.table
特定的列
特定的存储过程
grant select ,insert on 库名.* to 用户名;
第二十九章 数据库维护-
首先刷新未写入数据 flush tables
ANALYZE TABLE orders 检查表键是否正常
CHECK TABLE orders,orderitems;--检查
mysql -u root -p
show global variables like "%datadir%"; 查看安装目录
链接补充:MySQL :: MySQL Documentation
以下内容是一些基础的补充:仅供参考
可以把SQL分为两个部分:数据操作语言DML 和数据定义语言DDL
DML:select update delete insert into
DDL:create alter database/table drop table create index drop index
Distinct 后面可以跟多个字段滤重,跟单个一样也是保证唯一性。
第三十章 约束-
约束:用于限制表的数据的类型,在创建表的时候添加,或者建表之后添加。
Not null,nuique,primary key,foreign key,check,default
Not null约束:不能为空值
Create table ss
(
Id int ,
Nam varchar(255) not null
)
Unique约束:唯一标识数据库表中的每条记录
Unique和主键约束均为列或列集合提供了唯一性的保证,主键拥有自动定义的unique约束。每个表可以有多个unique约束,但是每个表只能有一个主键约束。
Create table sd
(
Id int not null,
Lastname varchar(20)
Unique (int)
)
如果需要命名unique约束,以及定义多个unique约束:有重复值的时候无法给该字段添加唯一约束
Create table zs1
(
Id int,
Af varchar(23) not null,
Adddd varchar(25)
Constraint 约束名 unique (id,af)
)
表创建完成的时候添加约束:(多个)
Alter table zs1 add unique(id)
Alter table zs1 add constraint 约束名 unique (id,af)
撤销约束:
Alter table zs1 drop index 约束名
Alter table zs1 drop constraint 约束名
单个约束撤销的时候直接加上约束的字段即可。
Primary key 约束:标识唯一记录,不包含空值,和unique的区别是 主键不能为空值,nuique可以。
Create table zs1 mysql
(
Id int,
Nnn varchar(25)
Primary key (id)
)
Create table zs1
(
Id int,
Nn varchar(25) primary key
)
主键包含多个字段:
Create table zs1
(
Id int,
Nn varchar(25)
Ab int
Constraint 约束名 primary key (id,nn)
)
建表之后创建:
Alter table zs1 add primary key (id)
Alter table zs1 add constraint 约束名 primary key (d,nn)
如果在建表之后添加主键,必须把主键列声明为不包含null(在表首次创建的时候)
撤销主键约束:
Alter table zs1 drop primary key --mysql
Alter table zs1 drop constraint 约束名 –oracle
Foreign key:一个表中的字段是主键,在另一个表中是外键,外键约束用于预防破坏表之间连接的动作。也能防止非法数据插入外键列,因为它必须指向那个表中的值之一。
Create table zs2 --mysql
(
Id int ,
Orid int,
Idp int,
Primary key (id),
Foreign key (idp) referenecs zs1(id)
)
Create table zs3 – oracle
(
Id int primary key,
Idd int ,
Ida int froeign key references zs1(id)
)
外键包含多个:
Create table zs4
(
Id int ,
Idd int ,
Qq int,
Primary key (id),
Constraint 约束名 foreign key (idd)
References zzs1(id)
)
Alter table zs1 add froeign key(id) references zs2(idp)
Alter table zs1 add constraint 约束名 foreign key (id) references zs2(idp)
撤销外键:
Alter table zs1 drop foreign key 约束名
Alter table zs1 drop constraint 约束名
Check约束:用于限制列中的值的范围,如果对单个列定义check约束,那么该列只允许特定的值,对一个表定义check约束,那么此约束会在特定的列中对值进行限制。
Create table zs1 --mysql
(
Id int,
Na varchar(25),
Check (id>0)
)
Create table zs1 --oracle
(
Id int check (id>0),
Na varchar(25),
)
多个check约束:
Create table zs1
(
Id int,
Nn int,
City int,
Constraint 约束名 check (id>0 and city=’assa’)
)
撤销约束:
Alter table zs1 drop constraint 约束名
Alter table zs1 drop check 约束名
Default约束:
Create table zs1
(
Id int,
Nn int,
Nam varchar(25) default ‘asasa’
)
Alter table zs1 --mysql
Alter city set default ‘aaa’
Alter table zs1 --oracle
Alter column city set default ‘saaa’
撤销default约束:
Alter table zs1 -- mysql
Alter city drop default
Alter table zs1 --oracle
Alter column city drop default
第三十一章 索引
index---索引是对数据库表中一列或者多列的值进行排序的结构。缩短查询的时间。 主键,唯一,联合索引。
创建索引,在不读取整个表的情况下,索引使数据库应用程序可以更快的查找数据。
索引会加速查询,但是更新数据慢。理想的做法是在常常被搜索的列(以及表)上面创建索引。
Create table zs1 --建表时侯
(
Id int,
Name int,
Asas int
Index(id)
);
Create index 索引名
On zs1 (id)
Create index 索引名
On zs1 (id desc)
索引不止一个:
Create index 索引名
On zs1 (id ,nn)
删除索引:不同的数据库可能不一样
Drop index 索引名 on zs1
Drop index zs1.id --sqlserver
Drop index id –oracle
Alter table zs1 drop index 索引名
--删除表--
Drop table zs1 表的一切都会删除
Drop database 数据库名
Truncate table zs1 仅仅删除表中的数据,保留结构之类的
--修改表—
添加列
Alter table zs1 add 列名 数据类型
删除列:alter table zs1 drop column 列名 –注意某些数据库不支持这种写法
改变列的数据类型:alter table zs1 alter column 列名 数据类型 --MySQL要用modify
--auto-increment会在新纪录插入表中时生成一个唯一的数字
我们通常希望在每次插入新纪录时,自动的创建主键字段的值,我们可以在表中创建一个auto-increment字段,
Create table zs1 主键自增 –MySQL语法
(
Id int not null auto_increment,
Nn varchar(25),
Add varchar(25)
Primary key (id)
)
Alter table zs1 auto_increment =100 更改起始值
当插入数据的时候手动更改自增的值,后面再次插入以上一条数据的序列值为准累加。
SQL server 语法:
Create table zs1
(
Id int primary key identity,
Lastname int
)
Access的语法:
Create table zs1
(
Id int primary key autoincrement,
Laname varchar(255)
)
要规定 "P_Id" 列以 20 起始且递增 10,请把 autoincrement 改为 AUTOINCREMENT(20,10)
Oracle语法:
Create sequence sqq
Minvalue 1
Start 1
Increment by 1
Cache 10
Insert into table zs1 (id,name,sex) values (sqq.nextval,’aa’,’man’) 这个相对复杂一下,需要注意
--date函数---
--date 函数—
编辑
编辑
MySQL日期类型:
Date 格式:YYYY-MM-DD
Datetime 格式:YYYY-MM-DD HH:MM:SS
Timestamp 格式:YYYY-MM-DD HH:MM:SS
Year 格式:YYYY或者YY
Sql server日期格式:
- DATE - 格式 YYYY-MM-DD
- DATETIME - 格式: YYYY-MM-DD HH:MM:SS
- SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - 格式: 唯一的数字
----null 值---
Null 值是遗漏的未知数据,默认的,表可以存放null值,null值用作未知的或不适用的值的占位符。无法比较null和0;他们是不等价的。
无法用运算符来比较测试空值,必须使用 is null 和 is not null
关于 null 的函数:
Isnull(),nvl(),ifnull(),coalesce()
Isnull(列,0) nvl(列,0) ifnull(列,0) coalesce(列,0)
--数据类型---
Char(size):保存固定长度的字符串(可包含字母,数字以及特殊字符),在括号中指定字符串的长度,最多255个字符。
Varchar(size):保存可变长度---------指定字符串的最大长度,最多255个字符。
Tinytext:存放最大长度为255个字符的字符串
Text: 存放最大长度为65535个字符的字符串
Int(size)
Tinyint:-128-127
Float(size,d):带有浮动小数点的小数字,括号中规定最大位数,d规定小数点最大位数。
Decimal(size,d):作为字符串存储的double类型,允许固定的小数点。
Date() 不带时间
Datetime():带有时间
Timestamp:时间戳 带有时间,
Time:时间
Year:年
---本书实例建表语句如下:
########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################
########################
# Create customers table
########################
CREATE TABLE customers
(
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL ,
cust_address CHAR(50) NULL ,
cust_city CHAR(50) NULL ,
cust_state CHAR(5) NULL ,
cust_zip CHAR(10) NULL ,
cust_country CHAR(50) NULL ,
cust_contact CHAR(50) NULL ,
cust_email CHAR(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=INNODB;
#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
order_num INT NOT NULL ,
order_item INT NOT NULL ,
prod_id CHAR(10) NOT NULL ,
quantity INT NOT NULL ,
item_price DECIMAL(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=INNODB;
#####################
# Create orders table
#####################
CREATE TABLE orders
(
order_num INT NOT NULL AUTO_INCREMENT,
order_date DATETIME NOT NULL ,
cust_id INT NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=INNODB;
#######################
# Create products table
#######################
CREATE TABLE products
(
prod_id CHAR(10) NOT NULL,
vend_id INT NOT NULL ,
prod_name CHAR(255) NOT NULL ,
prod_price DECIMAL(8,2) NOT NULL ,
prod_desc TEXT NULL ,
PRIMARY KEY(prod_id)
) ENGINE=INNODB;
######################
# Create vendors table
######################
CREATE TABLE vendors
(
vend_id INT NOT NULL AUTO_INCREMENT,
vend_name CHAR(50) NOT NULL ,
vend_address CHAR(50) NULL ,
vend_city CHAR(50) NULL ,
vend_state CHAR(5) NULL ,
vend_zip CHAR(10) NULL ,
vend_country CHAR(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=INNODB;
###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
note_id INT NOT NULL AUTO_INCREMENT,
prod_id CHAR(10) NOT NULL,
note_date DATETIME NOT NULL,
note_text TEXT NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MYISAM;
#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
--------基础篇完结:内容穿插可能不规范后续会优化整理
扩展篇:
扩展一:mysql_存储过程+重点sql记录
一: 存储过程例题
-- 用户变量只在当前链接有效
SET @aqq=10;
SELECT @aqq
-- 局部变量,仅仅在begin end中有效
DECLARE 变量名 类型;
-- 创建:create procedure 存储过程名(参数列表)
BEGIN
存储过程体
END
注意参数包含3部分:
参数模式 参数名 参数类型
IN aa VARCHAR
参数模式:
INT 输入
OUT 输出
INOUT 输入输出
如果过程体只有一句话,BEGIN END 可以省略
每个SQL语句的结尾必须加分号。
存储过程的结尾可以使用 DELIMITER 重新设置
-- 调用 call 存储过程名(参数)
-- 第一种 空参列表
SELECT * FROM aa01
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO aa01(a1,a2)
VALUES(55,55),(66,66);
END $
mysql -uroot -proot
-- 调用
CALL myp1()$
-- 第二种 in 模式参数列表
DELIMITER $
CREATE PROCEDURE myp2( IN tt INT)
BEGIN
SELECT * FROM aa01
WHERE a1=tt;
END $
CALL myp2(11)
DELIMITER $
CREATE PROCEDURE myp44( IN t1 INT,IN t2 INT)
BEGIN
DECLARE t3 VARCHAR(20) DEFAULT '';#声明并初始化
SELECT COUNT(*) INTO t3
FROM aa01
WHERE a1=t1 AND a2=t2;
SELECT IF(t3>0,100,200);#使用
END $
-- 3 out 模式的
DELIMITER $
CREATE PROCEDURE myp5( IN t1 INT,OUT t2 INT)
BEGIN
SELECT a2 INTO t2
FROM aa01
WHERE a1=t1 ;
END $
CALL myp5(33,@tt)$
SELECT @tt$
-- inout模式的
DELIMITER $
CREATE PROCEDURE myp6( INOUT t1 INT,INOUT t2 INT)
BEGIN
SET t1=t1*2;
SET t2=t2*2;
END $
SET @m=10$
SET @n=10$
CALL myp6(@m,@n)$
SELECT @m,@n$
-- 例题
-- 删除
DROP PROCEDURE fms_p2;
-- 查看
DESC myp2;
SHOW CREATE PROCEDURE myp2;
-- 分组拼接函数记录
SELECT * FROM aa01
INSERT INTO aa01 VALUES(11,22),(11,33),(22,11),(22,22),(33,22)
SELECT a1,GROUP_CONCAT('_' ,a2)
FROM aa01
GROUP BY a1
-- 游标测试
DELIMITER $
CREATE PROCEDURE myy1()
BEGIN
DECLARE done INT ;
DECLARE a1 VARCHAR(100);
DECLARE a2 VARCHAR(100);
DECLARE a3 VARCHAR(100);
DECLARE a4 VARCHAR(100);
DECLARE a5 VARCHAR(100);
#定义游标
DECLARE my_cur CURSOR FOR
SELECT n_nu,ods_name,stg_name,prov_name,serv_name FROM fms_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#打开游标
OPEN my_cur;
#遍历游标
read_loop:LOOP
FETCH my_cur INTO a1,a2,a3,a4,a5;
IF done=1 THEN
LEAVE read_loop;
END IF;
CALL fms_p3 (a1,a2,a3,a4,a5,'fms_table','fms_result_01');
END LOOP;
CLOSE my_cur;
END $
DELIMITER ;
CALL myy1()$
DROP PROCEDURE myy1;
SELECT * FROM fms_result_01
二:用存储过程实现表之间数据流转练习
CREATE TABLE fms_01( #定义临时表
n_id INT,
n_name VARCHAR(20),
n_date VARCHAR(20),
n_pro VARCHAR(20)
)
CREATE TABLE fms_table( #定义接口清单表
n_nu VARCHAR(100),
stg_name VARCHAR(100),
prov_name VARCHAR(100),
serv_name VARCHAR(100)
)
INSERT INTO fms_table VALUES('001','fms_01','fms_02','fms_03') #接口清单数据
DROP TABLE fms_result_01
CREATE TABLE fms_result_01( #定义接口入库结果清单表
n_nu VARCHAR(100),
stg_name VARCHAR(100),
prov_name VARCHAR(100),
serv_name VARCHAR(100),
n_sum INT
)
SELECT * FROM fms_result_01
INSERT INTO fms_result_01 (stg_name) -- where n_nu='001'
SELECT 'wewe'
INSERT INTO fms_02
SELECT * FROM fms_02
INSERT INTO fms_02 VALUES(01,'aa','20231005','10000'),(02,'bb','20231005','10000'),(03,'bb','20231008','10000')
CREATE TABLE fms_12 LIKE fms_02; #复制一个空表
SELECT * FROM fms_01;
SELECT * FROM fms_04;
SELECT * FROM fms_log_01;
TRUNCATE TABLE fms_02
-- 1,实现表数据传输
DELIMITER $
CREATE PROCEDURE fms_p1( IN tname_0 VARCHAR(100),IN tname_1 VARCHAR(100))
BEGIN
DECLARE su_m INT DEFAULT -1;#声明并初始化
DECLARE aa VARCHAR(200);
-- set aa=tname_1;
SET @sql1 = CONCAT( ' insert into ' ,tname_1, ' SELECT * FROM ' ,tname_0);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
END $
DROP PROCEDURE fms_p1;
CALL fms_p1 ('fms_01','fms_02')$
-- 2.实现表数据传输,写入日志表
DELIMITER $
CREATE PROCEDURE fms_p2( IN tname_0 VARCHAR(100),IN tname_1 VARCHAR(100),IN tname_2 VARCHAR(100))
BEGIN
DECLARE su_m INT DEFAULT -1;#声明并初始化
DECLARE aa VARCHAR(200);
-- set aa=tname_1;
SET @sql1 = CONCAT( ' insert into ' ,tname_1, ' SELECT * FROM ' ,tname_0);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
SET @sql2 = CONCAT( ' insert into ', tname_2, ' (su_m) ',' select count(*) from ' ,tname_1);
PREPARE stmt FROM @sql2;
EXECUTE stmt;
END $
DROP PROCEDURE fms_p2;
CALL fms_p2 ('fms_01','fms_02','fms_log_01')$
INSERT INTO fms_log_01 (su_m) SELECT COUNT(*) FROM fms_log_01
SELECT * FROM fms_log_01
-- 3.实现三个表表数据传输,写入日志表并记录
DELIMITER $
CREATE PROCEDURE fms_p3(IN n_nu VARCHAR(100),IN tname_1 VARCHAR(100),IN tname_2 VARCHAR(100),IN tname_3 VARCHAR(100),IN tname_4 VARCHAR(100),IN tname_5 VARCHAR(100),IN tname_6 VARCHAR(100))
BEGIN
DECLARE aa INT DEFAULT -1;
SET @sql0 = CONCAT( ' insert into ' ,tname_6, ' (n_nu,stg_name,prov_name,serv_name) ', ' SELECT n_nu,stg_name,prov_name,serv_name FROM ' ,tname_5);
PREPARE stmt FROM @sql0;
EXECUTE stmt;
SET @sql1 = CONCAT( ' insert into ' ,tname_2, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
SET @sql2 = CONCAT( ' insert into ' ,tname_3, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql2;
EXECUTE stmt;
SET @sql3 = CONCAT( ' insert into ' ,tname_4, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql3;
EXECUTE stmt;
SET @sql4 = CONCAT(' SELECT count(*) into @aa FROM ' ,tname_4);
PREPARE stmt FROM @sql4;
EXECUTE stmt;
SET @sql5 = CONCAT( ' update ',tname_6, ' set n_sum= ' ,@aa , ' where n_nu= ',n_nu);
PREPARE stmt FROM @sql5;
EXECUTE stmt;
END $
-- 4.实现三个表表数据传输,写入日志表并记录,接着循环
DELIMITER $
CREATE PROCEDURE fms_p3(IN n_nu VARCHAR(100),IN tname_1 VARCHAR(100),IN tname_2 VARCHAR(100),IN tname_3 VARCHAR(100),IN tname_4 VARCHAR(100),IN tname_5 VARCHAR(100),IN tname_6 VARCHAR(100))
BEGIN
DECLARE aa INT DEFAULT -1;
DECLARE bb INT DEFAULT 0;
SET @sql0 = CONCAT( ' insert into ' ,tname_6, ' (n_nu,ods_name,stg_name,prov_name,serv_name,n_num) ', ' SELECT n_nu,ods_name,stg_name,prov_name,serv_name,-1 FROM ' ,tname_5,' where n_nu= ',n_nu);
PREPARE stmt FROM @sql0;
EXECUTE stmt;
SET @sql1 = CONCAT( ' insert into ' ,tname_2, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
SET @sql2 = CONCAT( ' insert into ' ,tname_3, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql2;
EXECUTE stmt;
SET @sql3 = CONCAT( ' insert into ' ,tname_4, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql3;
EXECUTE stmt;
SET @sql4 = CONCAT(' SELECT count(*) into @aa FROM ' ,tname_4);
PREPARE stmt FROM @sql4;
EXECUTE stmt;
SET @sql5 = CONCAT( ' update ',tname_6, ' set n_sum= ' ,@aa , ' where n_nu= ',n_nu);
PREPARE stmt FROM @sql5;
EXECUTE stmt;
END $
SELECT * FROM fms_result_01
TRUNCATE TABLE fms_result_01
UPDATE fms_result_01 SET n_sum=10
WHERE n_nu='001'
DROP PROCEDURE fms_p3;
CALL fms_p3 ('001','fms_01','fms_02','fms_03','fms_04','fms_table','fms_result_01')$
SELECT * FROM fms_result_01
SELECT * FROM fms_01
TRUNCATE TABLE fms_result_01
ALTER TABLE fms_result_01 ADD ods_name VARCHAR(50) AFTER n_nu;
INSERT INTO fms_table VALUES('011','fms_11','fms_12','fms_13','fms_14')
CREATE TABLE fms_11 SELECT * FROM fms_01;
-- 5 游标实现循环读取然后执行存储过程
DELIMITER $
CREATE PROCEDURE myy1()
BEGIN
DECLARE done INT ;
DECLARE a1 VARCHAR(100);
DECLARE a2 VARCHAR(100);
DECLARE a3 VARCHAR(100);
DECLARE a4 VARCHAR(100);
DECLARE a5 VARCHAR(100);
#定义游标
DECLARE my_cur CURSOR FOR
SELECT n_nu,ods_name,stg_name,prov_name,serv_name FROM fms_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#打开游标
OPEN my_cur;
#遍历游标
read_loop:LOOP
FETCH my_cur INTO a1,a2,a3,a4,a5;
IF done=1 THEN
LEAVE read_loop;
END IF;
CALL fms_p3 (a1,a2,a3,a4,a5,'fms_table','fms_result_01');
END LOOP;
CLOSE my_cur;
END $
DELIMITER ;
CALL myy1()$
DROP PROCEDURE myy1;
-- 6 增加健壮性 ,结果表添加入出接口数量,初始数量标识,时间戳。
ALTER TABLE fms_result_01 MODIFY r_num INT;
ALTER TABLE fms_result_01 ADD d_date DATETIME ;
SELECT * FROM fms_result_01
DESC fms_result_01
-- 6
DELIMITER $
CREATE PROCEDURE fms_p3(IN n_nu VARCHAR(100),IN tname_1 VARCHAR(100),IN tname_2 VARCHAR(100),IN tname_3 VARCHAR(100),IN tname_4 VARCHAR(100),IN tname_5 VARCHAR(100),IN tname_6 VARCHAR(100))
BEGIN
DECLARE aa INT DEFAULT -1;
DECLARE bb INT DEFAULT -1;
SET @sql0 = CONCAT( ' insert into ' ,tname_6, ' (n_nu,ods_name,stg_name,prov_name,serv_name,d_date) ', ' SELECT n_nu,ods_name,stg_name,prov_name,serv_name,now() FROM ' ,tname_5,' where n_nu= ',n_nu);
PREPARE stmt FROM @sql0;
EXECUTE stmt;
SET @sql1 = CONCAT( ' insert into ' ,tname_2, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql1;
EXECUTE stmt;
SET @sql2 = CONCAT( ' insert into ' ,tname_3, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql2;
EXECUTE stmt;
SET @sql3 = CONCAT( ' insert into ' ,tname_4, ' SELECT * FROM ' ,tname_1);
PREPARE stmt FROM @sql3;
EXECUTE stmt;
SET @sql4 = CONCAT(' SELECT count(*) into @aa FROM ' ,tname_2);
PREPARE stmt FROM @sql4;
EXECUTE stmt;
SET @sql5 = CONCAT( ' update ',tname_6, ' set r_num= ' ,@aa , ' where n_nu= ',n_nu);
PREPARE stmt FROM @sql5;
EXECUTE stmt;
SET @sql6 = CONCAT(' SELECT count(*) into @bb FROM ' ,tname_4);
PREPARE stmt FROM @sql6;
EXECUTE stmt;
SET @sql7 = CONCAT( ' update ',tname_6, ' set n_sum= ' ,@bb , ' where n_nu= ',n_nu);
PREPARE stmt FROM @sql7;
EXECUTE stmt;
END $
DROP PROCEDURE fms_p3
-- 7 游标遍历调用上面存储过程
DELIMITER $
CREATE PROCEDURE myy1()
BEGIN
DECLARE done INT ;
DECLARE a1 VARCHAR(100);
DECLARE a2 VARCHAR(100);
DECLARE a3 VARCHAR(100);
DECLARE a4 VARCHAR(100);
DECLARE a5 VARCHAR(100);
#定义游标
DECLARE my_cur CURSOR FOR
SELECT n_nu,ods_name,stg_name,prov_name,serv_name FROM fms_table;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
#打开游标
OPEN my_cur;
#遍历游标
read_loop:LOOP
FETCH my_cur INTO a1,a2,a3,a4,a5;
IF done=1 THEN
LEAVE read_loop;
END IF;
CALL fms_p3 (a1,a2,a3,a4,a5,'fms_table','fms_result_01');
END LOOP;
CLOSE my_cur;
END $
DELIMITER ;
CALL myy1()$
DROP PROCEDURE myy1;
三:二例题改写成Gbase存储过程语法
-- 1.1创建清单表
CREATE TABLE prod.dataman.fms_list(
n_id int PRIMARY KEY COMMENT '优先级编号',
n_nu VARCHAR(64) DEFAULT NULL COMMENT '接口号',
ods_name VARCHAR(100) DEFAULT NULL COMMENT '临时表表名'
stg_name VARCHAR(100) DEFAULT NULL COMMENT 'stg表名',
prov_name VARCHAR(100) DEFAULT NULL COMMENT 'prov表名',
serv_name VARCHAR(100) DEFAULT NULL COMMENT 'serv表名',
sys_flag VARCHAR(100) DEFAULT NULL COMMENT '系统标识'
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' COMMENT = '定义资金清单表';
-- 清单表插入数据
insert into fms_list values
(1,'fms08085','prod.boncres.a_70005_fms_08085','prod.stgdb.TSFMS_D_M_FI_FD_A08085_DEPO_ACCT_CHK_MGT_TRI_10000','prod.provdb.TO_D_FI_FD_DEPO_ACCT_CHK_MGT_TRI_10000','prod.servdb.TO_D_FI_FD_DEPO_ACCT_CHK_MGT_TRI','fms'),
(2,'fms08073','prod.boncres.a_70005_fms_08073','prod.stgdb.TSFMS_D_M_FI_FD_A08073_INTERE_ACCRUED_APPLY_10000','prod.provdb.TO_D_FI_FD_INTERE_ACCRUED_APPLY_10000','prod.servdb.TO_D_FI_FD_INTERE_ACCRUED_APPLY','fms')
call prod.dataman.test_fms_p3('fms08073','prod.boncres.a_70005_fms_08073','prod.stgdb.TSFMS_D_M_FI_FD_A08073_INTERE_ACCRUED_APPLY_10000','prod.provdb.TO_D_FI_FD_INTERE_ACCRUED_APPLY_10000','prod.servdb.TO_D_FI_FD_INTERE_ACCRUED_APPLY','prod.dataman.fms_list','prod.dataman.fms_result')
-- 1.2创建结果表
CREATE TABLE prod.dataman.fms_result(
n_nu VARCHAR(64) COMMENT '接口号',
ods_name VARCHAR(64) DEFAULT NULL COMMENT '临时表表名',
r_num int COMMENT '总数',
stg_name VARCHAR(100) DEFAULT NULL COMMENT 'stg表名',
prov_name VARCHAR(100) DEFAULT NULL COMMENT 'prov表名',
serv_name VARCHAR(100) DEFAULT NULL COMMENT 'serv表名',
n_sum int COMMENT 'serv总数',
d_date VARCHAR(100) DEFAULT NULL COMMENT '执行完成时间戳'
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' COMMENT = '定义资金结果表';
-- 2.1 创建数据流转的存储过程
delimiter //
CREATE DEFINER = "df_lisj" @"%" PROCEDURE "test_fms_p3"(
IN nn_nu VARCHAR,
- - 传 入 接 口 号 IN tname_ods VARCHAR,
- - 传 入 临 时 表 表 名 IN tname_stg VARCHAR,
- - 传 入 stgdb 正 式 表 表 名 IN tname_prov VARCHAR,
- - 传 入 provdb 表 表 名 IN tname_serv VARCHAR,
- - 传 入 servdb 表 表 名 IN tname_list VARCHAR,
- - 传 入 清 单 表 表 名 IN tname_result varchar - - 传 入 结 果 日 志 表 表 名
) BEGIN
/*-------------------------------------------------------------------- 该存储过程实现从临时表到正式表三层的功能 --------------------------------------------------------------------*/
DECLARE aa INT DEFAULT -1;
- - 定 义 变 量 接 收 入 接 口 的 数 据 总 量 DECLARE bb INT DEFAULT -1;
- - 定 义 变 量 接 收 出 接 口 的 数 据 总 量 DECLARE V_SQL TEXT;
- - 定 义 变 量 接 收 拼 接 后 的 sql 语 句 # 将 清 单 表 插 入 结 果 表
set
V_SQL = ' insert into ' || tname_result || '(n_nu,ods_name,stg_name,prov_name,serv_name,d_date,is_flag)' || ' SELECT n_nu,ods_name,stg_name,prov_name,serv_name,now(),1 FROM ' || tname_list || ' where n_nu= ''' || nn_nu || '''';
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
# 从 临 时 表 插 入 数 据 到 stg 层
SET
V_SQL = 'insert into ' || tname_stg || ' SELECT * FROM ' || tname_ods;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
# 从 临 时 表 插 入 数 据 到 prov 层
SET
V_SQL = 'insert into ' || tname_prov || ' SELECT * FROM ' || tname_ods;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
# 从 临 时 表 插 入 数 据 到 serv 层
SET
V_SQL = 'insert into ' || tname_serv || ' SELECT * FROM ' || tname_ods;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
#
set
ttt = 'lihao';
# stg 表 条 数 记 录
SET
V_SQL = 'SELECT count(*) into @aa FROM ' || tname_stg;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
set
aa = @aa;
# 条 数 插 入 到 结 果 表 中 # V_SQL = 'update ' || tname_6 || 'set r_num= ' || @aa || ' where n_nu= ''' || n_nu || '''';
#
set
ttt = 'lihao';
set
V_SQL = 'update prod.dataman.fms_result set r_num= ' || aa || ' where n_nu= ''' || nn_nu || '''';
# V_SQL = 'update ' || tname_6 || 'set r_num= ' || @aa || '' || 'where n_nu= ''' || nn_nu || '''';
#
set
ttt = V_SQL;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
# serv 表 条 数 记 录
SET
V_SQL = 'SELECT count(*) into @bb FROM ' || tname_serv;
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
set
bb = @bb;
# 条 数 插 入 到 结 果 表 中
set
# V_SQL = 'update prod.dataman.fms_result set r_num= ' || aa || ' where n_nu= ''' || nn_nu || '''';
V_SQL = 'update prod.dataman.fms_result set n_sum= ' || bb || ' where n_nu= ''' || nn_nu || '''';
SET
@SQL_STR = V_SQL;
PREPARE STMT
FROM
@SQL_STR;
EXECUTE STMT;
end //
delimiter ;
-- 2.2 创建遍历清单表的存储过程
delimiter //
CREATE DEFINER = "df_lisj" @"%" PROCEDURE "test_fms_p4"()
BEGIN DECLARE v_done INT default 0;
DECLARE a1 VARCHAR(100);
DECLARE a2 VARCHAR(100);
DECLARE a3 VARCHAR(100);
DECLARE a4 VARCHAR(100);
DECLARE a5 VARCHAR(100);
DECLARE v_error INT default 0;
DECLARE my_cur CURSOR FOR
SELECT
n_nu,
ods_name,
stg_name,
prov_name,
serv_name
FROM
prod.dataman.fms_list;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
set
v_done = 1;
declare continue HANDLER FOR sqlexception
set
v_error = 1;
update
prod.dataman.fms_result
set
is_flag = 0;
OPEN my_cur;
repeat FETCH my_cur INTO a1,
a2,
a3,
a4,
a5;
IF not v_done THEN CALL test_fms_p3 (
a1,
a2,
a3,
a4,
a5,
'prod.dataman.fms_list',
'prod.dataman.fms_result'
);
END IF;
until v_done
end repeat;
CLOSE my_cur;
end //
delimiter ;
扩展二:MySQL存储引擎
1--MySQL存储引擎
存储引擎是MySQL中具体与文件打交道的子系统,它是根据根据MySQL公司提供的文件访问层抽象接口定制的一种文件访问机制,这种机制就叫做存储引擎。
简单来说:存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。它决定着一个表是如何存储和索引数据,以及是否支持事务。(扩充:存储引擎就是表类型,或者数据的存储结构,由实际业务决定。)
SHOW ENGINES 查看所有支持的存储引擎
SHOW TABLE STATUS FROM dazs_01 WHERE NAME='表名' 查看表的存储引擎。
定义存储引擎:engine=memory
修改:alter table 表名 engine=myisam;
通过修改 /etc/my.cnf 配置文件,指定默认存储引擎并重启服务,但是之前的存储引擎不会改变。windows在my.ini中修改配置。
存储引擎:是MySQL中特有的一个术语,实际上是一个表存储或组织数据的方式
不同的存储引擎表的存储数据的方式不同。
mysql默认的存储引擎是:InnoDB
默认的字符编码是:utf-8
支持9大引擎 常用3个。
innoDB :
默认的存储引擎,同时又是一个重量级的存储引擎
支持事务,支持数据库崩溃后自动恢复机制
非常安全
最大的特点就是支持事务,已保证数据安全。
Myisam :
它管理的表有以下特征:
使用三个文件表示每个表:
格式文件-存储表结构的定义
数据文件:存储表行的内容
索引文件:存储表上索引(索引:是一本书的内容,缩小扫描范围,)
特点:可被转换为压缩,只读表来节省空间
不支持事务,安全性低
memory:
查询效率高,不需要和硬盘交互,表数据和索引都是在内存之中
不安全,关机后数据消失
innodb存储引擎的具体架构如下:上半部分是实例层(计算层),位于内存中,下半部分是物理层,位于文件系统中。
实例层分为线程和内存。innodb重要的线程有master thread,是innodb 的主线程,复负责调度其它各线程。
buf_dump_thread 负责将buffer pool 中的内容dump到物理文件中,以便再次启动MySQL时,可以快速加热数据。
page_cleaner_thread 负责将buffer pool 中的脏页刷新到磁盘。可以通过参数设置开启多个 page_cleaner_thread.
purge_thread 负责将不再使用的undo日志进行回收。
read_thread 处理用户的读请求,并负责将数据页从磁盘上读取出来,可以通过参数设置线程数量。
write_thread 负责将数据页从缓冲区写入磁盘,也可以通过参数设置线程数量,page_cleaner线程发起刷脏页操作后,write_thread 就开始工作了。
redo_log_thread 负责把日志缓冲中的内容刷新到redo log 文件中。
insert_buffer_thread 负责把insert 中内容刷新到磁盘。
innodb 的内存架构主要分为三大块,缓冲池(buffer pool),重做缓冲池(redo log buffer)和额外内存池。
缓冲池
InnoDB为了做数据的持久化,会将数据存储到磁盘上。但是面对大量的请求时,CPU的处理速度和磁盘的IO速度之间差距太大,为了提高整体的效率, InnoDB引入了缓冲池。
当有请求来查询数据时,如果缓存池中没有,就会去磁盘中查找,将匹配到的数据放入缓存池中。同样的,如果有请求来修改数据,MySQL并不会直接去修改磁盘,而是会修改已经在缓冲池的页中的数据,然后再将数据刷回磁盘,这就是缓冲池的作用,加速读,加速写,减少与磁盘的IO交互。
缓冲池说白了就是把磁盘中的数据丢到内存,那既然是内存就会存在没有内存空间可以分配的情况。所以缓冲池采用了LRU算法,在缓冲池中没有空闲的页时,来进行页的淘汰。但是采用这种算法会带来一个问题叫做缓冲池污染。
当你在进行批量扫描甚至全表扫描时,可能会将缓冲池中的热点页全部替换出去。这样以来可能会导致MySQL的性能断崖式下降。所以InnoDB对LRU做了一些优化,规避了这个问题。
MySQL采用日志先行,在真正写数据之前,会首先记录一个日志,叫Redo Log,会定期的使用CheckPoint技术将新的Redo Log刷入磁盘,这个后面会讲。
除了数据之外,里面还存储了索引页、Undo页、插入缓冲、自适应哈希索引、InnoDB锁信息和数据字典。
扩展三:重点sql记录
hive int转varchar cast(cast(1 as string) as varchar)
insert overwrite 后面不能跟字段
statis_date='${taskid}'
--截取拼接字符串
'h'||substring_index(subsstring_index(sms_zd,'?',1),'https',-1)asnewl
--where后面跟两个条件
where(sms_zdlike'channelCode%转换短链%'orsms_dxlike'from%转换短链%')
--casewhen
nvl(count(casewhensubstr(head_flag,1,4)='ngsh'andreco_acti_amt>0thenreco_serial_numberelsenullend),0)
--百分比
round(sum(nvl(a.day_contact_count,0))/sum(nvl(a.day_target_cust_count,0)),4)*100
一次写入,每次跑都清空表然后新建,之后插入数据:
drop table if exists temp_mk_ceshi1202;
create table temp_mk_ceshi1202(
prov_code varchar(20),
ct int)
;
insert into table csap700.temp_mk_ceshi1202
select
'200'asprov_code,
count(*)cnt
frommmmmm
;
insert into table csap700.temp_mk_ceshi1202
select
'280'asprov_code,
count(*)cnt
frommmmmm
;
带分区的,先删除分区,再添加分区,之后写入。
alter table csap700.ssss drop if exists partition(statis_date='变量');
alter table csap700.ssss add if not exists partition(statis_date='变量');
insert into table csap700.ssss partition(statis_date='变量')
select......
--或者直接按照下面这样写
insert overwrite table csap700.ssss partition(statis_date='变量');
--正则表达式
select *
from ( select staff_id,caller_num,case when caller_num like '%%\_%%' then 1
when caller_num rlike '.*[a-zA-Z]+.*' then 2
when caller_num rlike '[0-9]+' then 3
when caller_num is null then 4 else 5 end aa
from csap.tb_dwd_mk_itmk_contact_dtl_day
where statis_date='20230128'
and head_flag like 'ngsh%'
) a
where aa=2
selectsubstring_index(substring_index(ewrew#sfsdf#https://dev.coc.10086.cn/coc3/canvas/rightsmarket-h5-canvas/online/
zero?channelCode=渠道码
,?,1),#,-2);
SELECTSUBSTRING_INDEX(SUBSTRING_INDEX(aa,?,1),#,-1)ASs
FROMas1114
replace(replace(ziduan,char(10),),char(13),)
selectreplace(replace(fusion_strategy_id,char(10),''),char(13),''),
replace(replace(fusion_strategy_name,char(10),''),char(13),''),
replace(replace(fusion_strategy_name_css,char(10),''),char(13),''),
replace(replace(prov_code,char(10),''),char(13),''),
selectreplace(replace(id,char(10),''),char(13),''),
replace(replace(fusion_strategy_id,char(10),''),char(13),''),
insert into csap700.tb_dim_mk_quanyi_all_day values
('571','浙江','20220606164227504583'),
('571','浙江','20220513113906133894'),
('971','青海','20221104170708652519'),
case when substr(staffid,1,2)='BJ' then '100'
when substr(staffid,1,2)='GD' then '200'
when substr(staffid,1,2)='SH' then '210'
when substr(staffid,1,2)='TJ' then '220'
when substr(staffid,1,2)='CQ' then '230'
when substr(staffid,1,2)='LN' then '240'
when substr(staffid,1,2)='JS' then '250'
when substr(staffid,1,2)='HB' then '270'
when substr(staffid,1,2)='SC' then '280'
when substr(staffid,1,2)='SN' then '290'
when substr(staffid,1,2)='HE' then '311'
when substr(staffid,1,2)='SX' then '351'
when substr(staffid,1,2)='HA' then '371'
when substr(staffid,1,2)='JL' then '431'
when substr(staffid,1,2)='HL' then '451'
when substr(staffid,1,2)='NM' then '471'
when substr(staffid,1,2)='SD' then '531'
when substr(staffid,1,2)='AH' then '551'
when substr(staffid,1,2)='ZJ' then '571'
when substr(staffid,1,2)='FJ' then '591'
when substr(staffid,1,2)='HN' then '731'
when substr(staffid,1,2)='GX' then '771'
when substr(staffid,1,2)='JX' then '791'
when substr(staffid,1,2)='GZ' then '851'
when substr(staffid,1,2)='YN' then '871'
when substr(staffid,1,2)='XZ' then '891'
when substr(staffid,1,2)='HI' then '898'
when substr(staffid,1,2)='GS' then '931'
when substr(staffid,1,2)='NX' then '951'
when substr(staffid,1,2)='QH' then '971'
when substr(staffid,1,2)='XJ' then '991' else '000' end ppp
扩展四:MySQL性能优化
第六节schema设计与管理:
6.1选择优化的数据类型:
MySQL支持的数据类型很多,选择正确的数据类型对于获得高性能至关重要。
--更小的通常更好:因为占用磁盘,内存和cpu缓存的空间更少,并且处理时需要的CPU周期也更少。
--简单为好:简单数据类型的操作通常需要更少的cpu周期,整形要比字符类型数据操作代价更低,因为字符集和排序规则是字符型数据的比较更为复杂。
--尽量避免存储null:因为可以为null的列使得索引,索引统计和值的比较都更为复杂。可为空的null的列会使用更多的存储空间,在MySQL里面也需要特殊处理。通常把null改为not null的提升较小,(调优时),所以不是首选。
6.2整数类型:
整数,实数(带小数),如果存储整数可用:tinyint,smallint,mediumint,int或者bigint,他们分别使用 8,16,24,32,64位存储空间,可以存储的值的范围从-2(n--1)到2(n-1)-1次方,N代表存储空间的位数。
整数类型有可选的unsigned属性,表示不允许负值,这大致可以使正数的上限提高一倍。tinyint unsigned 范围 0-255,tinyint 范围 -128-127.有符号类型和无符号类型使用相同的存储空间,并具有相同的性能,可根据数据实际情况选择合适的类型。
6.3实数类型:
浮点类型通常比decimal使用更少的空间来存储相同范围的值。float使用4字节的存储空间,double占用8字节,比float具有更大对的精度和更大的值范围。与整数类型一样,你只能选择存储类型,MySQL会使用double进行浮点类型的内部计算。尽量只对小数进行精确计算时才使用decimal,或者用bigint代替,然后根据小数位数乘以10的倍数,然后将结果存储在bigint里面,这样可以同时避免浮点存储计算不精确和decimal精确计算代价高的问题。
6.4字符串类型
varchar char ,很难解释这些值是如何存储在磁盘和内存中的,因为这跟存储引擎的具体实现与关。
varchar存储可变长长度的字符串,他比固定长度更节省空间,因为他仅使用必要的空间。它需要额外使用一到两个字节存储字符串长度信息。缺点是由于行是可变长度的,在更新时可能会增长,这会导致额外的工作。
适合使用:列的最大长度远远大于平均长度,列的更新很少。
char:固定长度,适合存储非常短的字符串,或者长度都差不多的字符串。经常修改的数据,因为固定长度的行不容易出现碎片。对于非常短的列,插char比V更高效;
与他们类似的还有 binary 和 varbinary。
varchar 10 和 1000 空间开销是一样的,10有什么优势:节省内存。
BLOB和TEXT类型:都是为了存储数据很大的数据来设计的,前者二进制,没有排序规则或者字符集,后者有的。该类型不能把完整字符串放入索引,也不能使用索引进行排序。
6.5日期和时间类型:
datetime:范围 1000-9999 精度为 1毫秒,以 YYYYMMDDHHMMSS格式压缩成整数的时间和日期和时间,且与时区无关。这需要8字节的存储空间。默认情况下,MySQL可以排序,无歧义的格式显示datetime值。
timestamp:使用 4字节的存储空间,他的范围比前者小很多只能表示1970-20380119范围。比前者的特殊性:当插入数据的时候没有指定timestamp的值,MySQL会将该列的值设置为当前时间。
6.6位压缩数据类型--暂不记录
6.7 json数据类型--暂不记录
第7章创建高性能的索引:
索引:在MySQL中也叫做键,是存储引擎用于快速找到记录的一种数据结构。
优点:索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O 变为顺序I/O。
索引的选择性是指索引的基数比上总行数。0-1之间,索引的选择性越高查询效率越高,因为选择性高可以过滤更多的行。唯一索引的选择性就是1,这是最好的索引选择性,性能也是最好的。
前缀索引:只对字段的前一部分字符进行索引,缺点是会降低索引的选择性。
多列索引:把多个列当成一个索引来使用。
聚簇索引:并不是一种单独的索引类型,而是一种数据存储方式,一个表只能有一个聚簇索引。
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为覆盖索引。