mysql笔记
1.substring_index
- substring_index(str,delim,count)
- str:要处理的字符串
- delim:分隔符
- count:计数，截取字符的位置
- 如果count是正数时候，substring_index是从左到右的顺序检查分隔符delim所在的位置。
- 如果count是负数时候，substring_index是从右到左的顺序检查分隔符delim所在的位置，返回的是从字符串str左边下标1开始的下标位置。
- count(expression)：如果expression全为null，在mysql的结果表示为0，但是如果使用ifnull(count(expression))执行速度更快
str = www.wikibt.com
substring_index(str,'.',1) # 从左边数的第一个.截取字符串 www
substring_index(str,'.',2) # 从左边数的两个字符串 www.wikibt
substring_index(str,'.',-2) #从右边数的两个字符串 wikibt.com
substring_index（substring_index(str,'.',2),'.',1) #wikibt
2. substr
- substr(string,start_position,length)
- string 源字符串,即被截取的字符串.
- start_position 字符截取的开始位置.start_position大于0时,从左边算起,小于0时,从右边查起
- length 截取字符的个数.默认截取到最后一位
select substr('www.baidu.com',0,4) from dual--空白字符串
select substr('www.baidu.com',1,4) from dual--www.
select substr('我是中文_测试_数据',1) from dual--我是中文_测试_数据
select substr('我是中文_测试_数据',1,22) from dual--我是中文_测试_数据
select substr('我是中文_测试_数据',-1) from dual--据
select substr('我是中文_测试_数据',-1,4) from dual--据
select substr('我是中文_测试_数据',-1,22) from dual--据
3. instr
- instr( string1, string2 [, start_position [, nth_appearance ] ] )
- string1 源字符串
- string2 目标字符串，在str中不存在时候或者出现次数小于给定的次数时，返回的都是0.
- start_position 从string1 的哪个位置开始查找。默认为1. 字符串索引从1开始。为正，从左到右开始检索，为负，从右到左检索。
- nth_appearance 代表要查找第几次出现的string2. 此参数可选，默认为 1.不能为负数
- 返回要查找的字符串string2在源字符串string1中的符合条件的开始索引
select instr('www.baidu.com', '.',1,1) from dual--4
select instr('www.baidu.com', '-',1,1) from dual--0
select instr('www.baidu.com', '.',1,2) from dual--10
select instr('www.baidu.com', '-',1,2) from dual--0
4. format
-
FOMRAT(N,D,locale)
FORMAT函数将数字N格式化为格式，如"#,###,###.##"，舍入到D位小数。它返回一个值作为字符串。N是要格式化的数字D是要舍入的小数位数locale是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符，MySQL将默认使用en_US。- 输出结果为字符串，而非数值
-
FORMAT(14500.2018, 2) # 14,500.20
5. 开窗函数
-
函数名(列) over(选项) 选项可以为 partition by 列 order by 列
- over() 按所有行进行分组
- over(partition by xxx) 按xxx分组的所有行进行分组
- over(partition by xxx order by aaa) 按列xxx分组，按列aaa排序
-
聚合开窗函数
- 函数名如果是聚合函数，则成为聚合开窗函数
- 语法：聚合函数(列) over(partition by 列 order by 列)
- 常见的聚合函数有：sum() count() average() max() min()
- 需求：计算每个学生的及格科目数
-
开窗函数不会修改源数据表的结构，也是在表的最后一列添加想要的结果，如果分组存在多行数据，则重复显示，因此对于既想要分组结果，又不想改变数据表的结构时，使用开窗函数效果非常好，
-
group by与over开窗函数的区别
- group by 主要用以结果是一个分组列，以及其他未聚合函数，不能呈现出两个完全的分组列，即age，min(num),max(mun)，count(),stddev()【标准差】,variance()【方差】,percentile(col,p)[分组后组内记录的p分位数]
- over可以显示多列
- age，gender,min(a),max(b)
-
排序开窗函数
- row_number：显示的是索引即行号1,2,3,4
- 根据课程进行分组，然后对每组内的成绩进行降序排序，不要放在where后面
- row_number 常用于按照某列生成连续序号，例如web程序的分页等等
- rank：排名，考虑到分数相同的情况1,1,3,3,5
- dense_rank：密集排名1,1,2,2,3,3,4
- ntile：分组排名
- 首先，ntile会先根据你的分组依据，本题中是课程名称，然后把每个组的总记录数进行按照你给的ntile()里的数字进行，这个数字就是桶数，相当于是把体育课程总共12条记录，尽量等划分成5桶，然后按照num的排序等级划分，每个桶两条记录，也就是112233445566的排序结果了，很显然，这个排序结果的数字大小只能用于桶与桶之间，而桶内部记录虽然序号相同，但是num不一定相同。
select s.sid,s1.sname,s1.gender,c.cname,s.num,row_number() over (partition by c.cname order by num desc) as row_number排名, rank() over (partition by c.cname order by num desc) as rank排名, dense_rank() over (partition by c.cname order by num desc) as dense_rank排名, ntile(6) over (partition by c.cname order by num desc) as ntile排名 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid - row_number：显示的是索引即行号1,2,3,4
-
lag(col,n)
- 用于统计窗口内往上第n行值
- 这两个函数可以用于同列中相邻行的数据相减操作
-
lead(col,n)
- 用于统计窗口内往下第n行值
- 这两个函数可以用于同列中相邻行的数据相减操作
-
first_value(column)取分组内排序后，截止到当前行，第一个值
-
根据分组排序后，每组按照排序后第一个值进行显示
-
select s.sid,s1.sname,s1.gender,c.cname,s.num, first_value(num) over(partition by c.cname order by num desc) as first_value用法 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid
-
-
last_value(column)
-
取分组内排序后，截止到当前行，最后一个值
-
select s.sid,s1.sname,s1.gender,c.cname,s.num, last_value(num) over(partition by c.cname ) as last_value用法 from score s join student s1 on s.student_id = s1.sid left join course c on s.course_id = c.cid -
-
为啥这里的last_value的用法不是按照每个组的最后一个值，也就是所谓的最小值来取值的呢？
- last_value()默认统计范围是 rows between unbounded preceding and current row，也就是取当前行数据与当前行之前的数据的比较。
- 在order by 条件的后面加上语句：rows between unbounded preceding and unbounded following
-
6. 日期函数
6.1 timestampdiff
- timestampdiff(SECOND/HOUR/MINUTE/DAY,start_time,end_time)start_time与end_time之间的相差时间
6.2 datediff
-
datediff(end_time,start_time) 两个日期之间相差的天数[end_time-start_time]
-
使用时的注意事项：
还需要设置下限datediff(‘2019-07-27’,activity_date)>0，否则会存在大于2019-07-27的数据，如果该值小于0，表示activity_date为截止日期，大于2019-07-27、select activity_date as day,count(distinct user_id) as active_users from Activity where datediff('2019-07-27',activity_date)<30 and datediff('2019-07-27',activity_date)>0 group by activity_date;
6.3 getdate（）
-
当前系统日期、时间
-
select GetDate() select video_id,round((100*完播率+5*点赞数+3*评论数+2*转发数)/(1+最近无播放天数),1) as hot_index from (select video_id,sum(完播数)/count(完播数) as 完播率,sum(if_like) as 点赞数,count(comment_id) as 评论数,sum(if_retweet) as 转发数,datediff(max(end_time),(select max(end_time) from tb_user_video_log)) as 最近无播放天数 from (select t1.video_id,(case when timestampdiff(SECOND,t1.start_time,t1.end_time)>=t2.duration then 1 else 0 end) as 完播数,t1.if_follow,t1.if_like,t1.if_retweet,t1.comment_id,t2.release_time,t1.end_time from tb_user_video_log as t1 left join tb_video_info as t2 on t1.video_id=t2.video_id where timestampdiff(DAY,release_time,(select max(end_time) from tb_user_video_log))<=29) as t group by video_id) as s order by hot_index desc
6.4 dateadd()
-
dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值
-
例如：向日期加上2天
-
select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000
6.5 datepart()
- datepart 返回代表指定日期的指定日期部分的整数。
- SELECT DATEPART(month, ‘2004-10-15’) --返回 10
6.6 datename()
- 返回代表指定日期的指定日期部分的字符串
- SELECT datename(weekday, ‘2004-10-15’) --返回：星期五
6.7 day(), month(),year()
6.8 date_add()
- date_add(orderdate,INTERVAL 1 DAY)
- 向orderdate添加1天的日期
6.9 DATE_FORMAT（）
date_format(tb_get_car_order.order_time,"%Y%m%d") between '20211001' and '20211007'
-- 查找出时间在20211001到20211007之间的数据
6.10 date_sub
- date_sub(orderdate,INTERVAL 1 DAY)
- 向orderdate减去1天的日期
6.11 b e g a i n d a t e begaindate begaindateenddate
- 导入时间变量
- $$begaindate{-4d} 开始时间前推4天
- $$begaindate{-2m} 开始时间前推2个月
7. if函数
- sum(if(result=‘right’, 1, 0)) 如果result为right,赋值为1，反之为0
8. concat相关
8.1 concat()
8.2 concat_ws()
- 一次性指定分隔符
- 语法：concat_ws(separator,str1,str2,…)
- 说明：第一个参数指定分隔符 分隔符不能为空 如果为NULL 则返回值NULL
8.3 group_concat()函数
-
功能：将group by产生的同一个分组中的值连接起来 返回一个字符串结果
-
语法 group_concat([distinct]要连接的字段 [order by 排序字段 asc/desc][separator’分隔符’])
-
说明：通过使用distinct可以排除重复值 如果希望对结果中的值进行排序 可以使用order by子句 separator是一个字符串值
-
缺省为一个逗号
-
使用group_concat()和group by显示相同名字的人的id号
-
将上面的id号从小到大排序 且用’_'作为分隔符：
-
举例：以name分组的所有组id和score
-
id和score连在一起 看的不是那么直观 使用concat_ws() 对每组中的id,score进行分割
-
如果连接的部分有重复的连接部分，可以采用distinct
select uid,sum(un_finish) as incomplete_cnt,sum(finish) as complete_cnt , group_concat(distinct concat_ws(':',date(start_time),tag) order by date(start_time) separator ';') from (select uid,tag,start_time,(case when submit_time is null then 0 else 1 end)as finish,(case when submit_time is not null then 0 else 1 end)as un_finish from exam_record left join examination_info on exam_record.exam_id=examination_info.exam_id where year(start_time)=2021) as t group by uid having incomplete_cnt<5 and complete_cnt>=1 and incomplete_cnt>1 order by uid desc
9. 视图
- 视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。
- 创建视图
create algorithm= temptable view 视图名 as select 语句；- 视图的 algorithm=merge/temptable/undefined
- merge：当引用视图时，引用视图的语句与定义视图的语句合并。merge意味着视图只是一个规则，语句规则，当查询视图时，把查询视图的语句与创建时的语句where子句等合并，分析形成一条select语句。
- templtable：当引用视图时，根据视图的创建语句建立一个临时表
- undefined：未定义，自动、让系统帮你选。
- 视图的 algorithm=merge/temptable/undefined
create view v1 as select id,name from d1 where id > 1;
-
使用视图
select * from v1; -- select * from (select id,name from d1 where id > 1) as v1; -
删除视图
drop view v1; -
修改视图
alter view v1 as SQL语句 -
视图是表的查询结果，自然表的数据改变了，影响视图结果；
-
视图改变了，影响表，但是视图并不总是能增删改，只有当视图的数据与表的数据一一对应时才可以更改
注：插入视图时，视图必须包含表中没有默认值的列
注意：
- 基于视图只能查询，针对视图不能执行 增加、修改、删除。 如果源表发生变化，视图表也会发生变化。
- 视图是虚拟表，是一个依赖于别的表上的寄生表，其本身不存储数据
- 优点
- 提高重用性，创建一张虚表，有需要的地方都可以使用这张表；
- 当需要重构数据库时，不需要修改程序中的sql语句；
- 不同用户使用不同的视图，提高数据安全性；
- 将多张基础表的数据整理到一张表，可直观查看数据。
10.触发器
- 对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
DROP TRIGGER tri_after_insert_tb1;
-
实例
- 在 t1 表中插入数据之前，先在 t2 表中插入一行数据。
delimiter $$ CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN -- NEW.id NEW.name NEW.email -- INSERT INTO t2 (name) VALUES(); IF NEW.name = 'alex' THEN INSERT INTO t2 (name) VALUES(NEW.id); END IF; END $$ delimiter ;insert into t1(id,name,email)values(1,"alex","xxx@qq.com") -
在t1表中删除数据之后，再在t2表中插入一行数据。
delimiter $$
CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
BEGIN
IF OLD.name = 'alex' THEN
INSERT INTO t2 (name) VALUES(OLD.id);
END IF;
END $$
delimiter ;
- 特别的：NEW表示新数据，OLD表示原来的数据。
- 一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。
11. 模糊查询
- “Select column FROM table Where column LIKE ‘pattern’”
- % 表示任意0个或多个字符
- _ 表示任意单个字符
- []表示括号内所列字符中的一个（类似与正则表达式）
- [^ ] 表示不在括号所列之内的单个字符
- * 表示查找的是所有信息,例如select * from tbl_user
- ^起始符号，&终止标识
12. 锁
可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：
-
表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
-
行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。
-
排它锁：可以用于数据修改操作，确保不会同时对同一资源进行多重更新
MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。
即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。
所以，一般情况下我们会选择使用innodb引擎，并且在 搜索 时也会使用索引（命中索引）。
接下来的操作就基于innodb引擎来操作：
CREATE TABLE `L1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`count` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。
所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。
如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。
-
for update`，排它锁，加锁之后，其他不可以读写。
begin; select * from L1 where name="武沛齐" for update; -- name列不是索引（表锁） commit;begin; -- 或者 start transaction; select * from L1 where id=1 for update; -- id列是索引（行锁） commit;- 应用场景：总共100件商品，每次购买一件需要让商品个数减1 。
A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100
此时 A、B 同时下单，那么他们同时执行SQL：
update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。
但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1
此时 A、B 同时下单，那么他们同时执行SQL：
update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？
这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行：
begin; -- start transaction;
select count from goods where id=3 for update;
-- 获取个数进行判断
if 个数>0:
update goods set count=count-1 where id=3;
else:
-- 已售罄
commit;
基于Python代码示例：
import pymysql
import threading
def task():
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor = conn.cursor()
# 开启事务
conn.begin()
cursor.execute("select id,age from tran where id=2 for update")
# fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10))
# {"id":1,"age":10} (1,10)
result = cursor.fetchone()
current_age = result['age']
if current_age > 0:
cursor.execute("update tran set age=age-1 where id=2")
else:
print("已售罄")
conn.commit()
cursor.close()
conn.close()
def run():
for i in range(5):
t = threading.Thread(target=task)
t.start()
if __name__ == '__main__':
run()
-
lock in share mode，共享锁，加锁之后，其他可读但不可写。begin; select * from L1 where name="武沛齐" lock in share mode; -- 假设name列不是索引（表锁） commit;begin; -- 或者 start transaction; select * from L1 where id=1 lock in share mode; -- id列是索引（行锁） commit;
13. 游标
- 游标是一个存储在DBMS服务器上的数据库查询，他不是一条select语句，而是被该语句检索出来的结果集
- 能够标记游标为已读，使得数据能读取，但不能更新和删除
- 在使用游标前，必须声明（定义）它。在结束游标使用时，必须关闭游标，可能的话，释放游标。
- 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持
- 主要用于交互式应用，其他用户需要滚动屏幕上的数据，并对数据进行浏览或做出改变
14 . 字符串相关
1 CHARSET(str)
- 返回子串字符集
2. CONCAT
- 连接子串，将多个列拼接成一列
3. INSTR
INSTR(string,substring)返回substring在string中出现的位置
4. UCASE(string2)
- 转换成大写
5. LCASE(string2)
- 转换为小写
6. LEFT
- LEFT(string2,length)
- 表示从string2中的左边取length个字符
7. RIGHT
- RIGHT(string2,length)
- 表示从string2中的右边取length个字符
8. LENGTH
- LENGTH(string)
- string长度[按照字节]
9. REPLACE
- REPLACE (str,search_str,replace_str)
- str: 列名
search_str： 查找的文本
replace_str： 代替的文本
10. STRCMP
- STRCMP(string1,string2)
- 逐字比较两字串大小：如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1
11. SUBSTRING
- SUBSTRING(str,poisition,length)
- 从str的position开始【从1开始计算】，取length个字符
12, LTRIM
- LTRIM(string2)
- 去除前端空格
13. RTRIM
- RTRIM(string2)
- 去除后端空格
14. TRIM
- TRIM(string)
- 去除前后端空格
15. stuff
-
STUFF(原字符, 开始位置, 删除长度, 插入字符)
-
从指定的起点处开始删除指定长度的字符，并在此处插入另一组字符
15. 数据语言区别（结构化查询语言）
- 数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块
- 数据操纵语言（DML）: SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）
- 数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）
- 数据控制语言（DCL）：GRANT(赋予用户权限） REVOKE(收回权限） DENY(禁止权限)
- 事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) COMMIT(提交)
- 事务处理语言（TPL）
- 指针控制语言（CCL）
16. count()
- count（）函数有两种使用方式：
- 使用count(*)对表中行的数目进行计数，不管表列中包含的是空值（null）还是非空值；
- 使用count（column）对特定列中具有值得行进行计数，忽略null值。
17. 存储过程
- 存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。
-
适用情形
- 处理订单，必须核对保证库存中有相应的物品
- 库存中没有的无病需要订购，需要与供应商进行某种交互
-
创建存储过程
delimiter $$
create procedure p1()
BEGIN
select * from d1;
END $$
delimiter ;
- 执行存储过程
call p1()
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1')
result = cursor.fetchall()
cursor.close()
conn.close()
print(result)
- 删除存储过程
drop procedure proc_name;
1 参数类型
存储过程的参数可以有如下三种：
- in，仅用于传入参数用
- out，仅用于返回值用
- inout，既可以传入又可以当作返回值
delimiter $$
create procedure p2(
in i1 int,
in i2 int,
inout i3 int,
out r1 int
)
BEGIN
DECLARE temp1 int;
DECLARE temp2 int default 0;
set temp1 = 1;
set r1 = i1 + i2 + temp1 + temp2;
set i3 = i3 +100
end $$
delimiter;
set @t1 =4; # @表示声明一个局部变量
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p2',args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# {"@_p2_0":11 }
cursor.close()
conn.close()
print(result)
2 返回值&结果集
3 事务&异常
事务，成功都成功，失败都失败。来维护数据的完整性
-
事务（transaction）：指一组SQL语句
-
回退（rollback）:指撤销指定SQL语句的过程
- 事务处理可以用来管理insert,update,delete语句进行回退操作，不能回退select、drop、create操作
-
提交（commit）：指未将存储的SQL语句结果写入数据库表
-
保留点（savepoint）:指事务处理中设置的临时占位符，可以对他发布会退
-
innodb引擎中支持事务，myisam不支持。
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(32) DEFAULT NULL, `amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -
事务的四大特征（ACID）：
-
原子性（Atomicity）
原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。 -
一致性（Consistency）
执行的前后数据的完整性保持一致。 -
隔离性（Isolation）
一个事务执行的过程中,不应该受到其他事务的干扰。 -
持久性（Durability）
事务一旦结束,数据就持久到数据库
-
-
静态游标
- 表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。 - 当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
- 在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
- 使用OUTER JOIN；
- 所选取的任意表没有索引；
- 将实数值当作选取的列。
- 表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
delimiter $$
create PROCEDURE p4(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception # sqlexceptio表示定义了一个异常
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION; -- 开启事务
delete from d1;
insert into tb(name)values('seven');
COMMIT; -- 提交事务
--要成功都成功，要失败都失败，如果delete成功，insert失败，那么删除的值也会进行回滚
-- SUCCESS
set p_return_code = 0;
END $$
delimiter ;
set @ret =100;
CALL p4(@ret);
SELECT @ret;
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))
# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()
cursor.close()
conn.close()
print(table)
print(rets)
4 游标
- 游标效率较低
- 游标是一个存储在DBMS服务器上的数据库查询，他不是一条select语句，二是被该语句检索出来的数据集。在存储了游标之后，应用程序可以根据需要滚动或浏览器中的数据
- 特征：
- 能标记游标为已读，使得数据可以进行读取，但是不能更新或删除
- 能标记某些列是可编辑的，哪些是不可编辑的
- 是数据在游标打开或访问期间不变化
- 使用步骤
- 使用游标必须先声明：表示定义要是用select语句和游标选项
- 一旦声明，就必须打开游标以供使用
- 对于填有数据的游标，根据需要取出各行
- 在结束游标使用时，必须关闭游标，可能的话，释放游标
创建游标
DELARE CustCursor CURSOR
FOR
SELECT * from Customers
where cust_email IS NULL;
delimiter $$
create procedure p5()
begin
declare sid int;
declare sname varchar(50);
declare done int default false; -- mysql没有布尔类型，相当于0-1代指FALSE或者TRUE
declare my_cursor CURSOR FOR select id,name from d1; -- 创建游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open my_cursor; -- 使用游标
xxoo: LOOP -- xxoo表示游标名称
fetch my_cursor into sid,sname; --表示获取每一行的数据
IF done then
leave xxoo;
END IF;
insert into t1(name) values(sname);
end loop xxoo;
close my_cursor;
end $$
delimiter ;
18. 关系型数据库的缺点
1. 难以应付高并发数据写入
2. 海量数据的查询,效率低
3. 数据量达到一定规模后,会遇到瓶颈,难以扩展
4. 表结构修改困难, 难以适应经常变更的业务需求
5. 许可费用,扩展费用高昂
19. 执行顺序
- 写法顺序：
select–from–where–group by–having–order by - 执行顺序：
from–where–group by–having–select–order by - 关键在于select
20.数据库更改操作
1. 数据表操作
修改表
-
添加列
alter table 表名 add 列名 类型; alter table 表名 add 列名 类型 DEFAULT 默认值; alter table 表名 add 列名 类型 not null default 默认值; alter table 表名 add 列名 类型 not null primary key auto_increment; -
删除列
alter table 表名 drop column 列名; -
修改列 类型
alter table 表名 modify column 列名 类型; -
修改列 类型 + 名称
alter table 表名 change 原列名 新列名 新类型;alter table tb change id nid int not null; alter table tb change id id int not null default 5; alter table tb change id id int not null primary key auto_increment; alter table tb change id id int; -- 允许为空，删除默认值，删除自增。 -
修改列 默认值
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 1000; -
删除列 默认值
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT; -
添加主键
alter table 表名 add primary key(列名); -
删除主键
alter table 表名 drop primary key;
2. 操作行
数据行操作的相关SQL语句（指令）如下：
-
新增数据
insert into 表名 (列名,列名,列名) values(对应列的值,对应列的值,对应列的值);insert into tb1(name,password) values('武沛齐','123123'); insert into tb1(name,password) values('武沛齐','123123'),('alex','123'); insert into tb1 values('武沛齐','123123'),('alex','123'); -- 如果表中只有2列 -
删除数据
delete from 表名; delete from 表名 where 条件;delete from tb1; delete from tb1 where name="wupeiqi"; delete from tb1 where name="wupeiqi" and password="123"; delete from tb1 where id>9; -
修改数据
update 表名 set 列名=值; update 表名 set 列名=值 where 条件;update tb1 set name="wupeiqi"; update tb1 set name="wupeiqi" where id=1; update tb1 set age=age+1; -- 整型 update tb1 set age=age+1 where id=2; update L3 set name=concat(name,"db"); update L3 set name=concat(name,"123") where id=2; -- concat一个函数，可以拼接字符串 -
查询数据
select * from 表名; select 列名,列名,列名 from 表名; select 列名,列名 as 别名,列名 from 表名; select * from 表名 where 条件;select * from tb1; select id,name,age from tb1; select id,name as N,age, from tb1; select id,name as N,age, 111 from tb1; select * from tb1 where id = 1; select * from tb1 where id > 1; select * from tb1 where id != 1; select * from tb1 where name="wupeiqi" and password="123";
21. 运算符
1. 位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。MySQL支持的位运算符如下
-
与运算符&
-
按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。
mysql> SELECT 1 & 10, 20 & 30; +--------+---------+ | 1 & 10 | 20 & 30 | +--------+---------+ | 0 | 20 | +--------+---------+ 1 row in set (0.00 sec) -
1的二进制数为0001，10的二进制数为1010，所以1 & 10的结果为0000，对应的十进制数为0。20的二进制数为10100，30的二进制数为11110，所以20 & 30的结果为10100，对应的十进制数为20。
-
取计数的数值： num&1=1
-
-
或运算符|
-
按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。
mysql> SELECT 1 | 10, 20 | 30; +--------+---------+ | 1 | 10 | 20 | 30 | +--------+---------+ | 11 | 30 | +--------+---------+ 1 row in set (0.00 sec) -
1的二进制数为0001，10的二进制数为1010，所以1 | 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 | 30的结果为11110，对应的十进制数为30。
-
-
异或运算符^
-
按位异或（^）运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该位返回1，否则返回0。
mysql> SELECT 1 ^ 10, 20 ^ 30; +--------+---------+ | 1 ^ 10 | 20 ^ 30 | +--------+---------+ | 11 | 10 | +--------+---------+ 1 row in set (0.00 sec) -
1的二进制数为0001，10的二进制数为1010，所以1 ^ 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 ^ 30的结果为01010，对应的十进制数为10。
-
-
按位取反运算符
-
按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变为1
mysql> SELECT 10 & ~1; +---------+ | 10 & ~1 | +---------+ | 10 | +---------+ 1 row in set (0.00 sec) -
由于按位取反（~）运算符的优先级高于按位与（&）运算符的优先级，所以10 & ~1，首先，对数字1进行按位取反操作，结果除了最低位为0，其他位都为1，然后与10进行按位与操作，结果为10。
-
-
按位右移运算符
-
按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐
mysql> SELECT 1 << 2, 4 << 2; +--------+--------+ | 1 << 2 | 4 << 2 | +--------+--------+ | 4 | 16 | +--------+--------+ 1 row in set (0.00 sec) -
1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。
-
按位左移运算符
-
按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。每移一位就乘以2。
mysql> SELECT 1 << 2, 4 << 2; +--------+--------+ | 1 << 2 | 4 << 2 | +--------+--------+ | 4 | 16 | +--------+--------+ 1 row in set (0.00 sec) -
1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。
-
2. 算术运算符
算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算。
3. 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
-
LIKE运算符 LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
- 通配符
- % ：匹配0个或多个字符。
- _ ：只能匹配一个字符。
- 通配符
-
REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。REGEXP运算符在进行匹配时，常用的有下面几种通配符：
（1）‘^’匹配以该字符后面的字符开头的字符串。 （2）‘$’匹配以该字符前面的字符结尾的字符串。 （3）‘.’匹配任何一个单字符。 （4）“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。 （5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。mysql> SELECT 'sheachk' REGEXP '^s', 'sheachk' REGEXP 't$', 'sheachk' REGEXP 'hk'; +------------------------+------------------------+-------------------------+ | 'sheachk' REGEXP '^s' | 'sheachk' REGEXP 't$' | 'sheachk' REGEXP 'hk' | +------------------------+------------------------+-------------------------+ | 1 | 1 | 1 | +------------------------+------------------------+-------------------------+ 1 row in set (0.01 sec)
4. 逻辑运算符
逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。MySQL中支持4种逻辑运算符如下：
-
逻辑异或运算符
-
逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
-
mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0; +----------+---------+---------+------------+---------------+---------------+ | 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 | +----------+---------+---------+------------+---------------+---------------+ | 0 | 1 | 0 | NULL | 1 | 0 | +----------+---------+---------+------------+---------------+---------------+ 1 row in set (0.00 sec) select last_name,department_id,salary from employees where department_id in (10,20) XOR salary > 8000;
-
5. 运算符的优先级
- 数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。
22. 批处理
- 修改一个表中的字段名后，不可以在同一个批处理中引用这个新字段
- 即批处理不能立即使用
23. COALESCE
- COALESCE ( expression,value1,value2……,valuen)
- 如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果所有的表达式都为空值，则返回NULL。
24. ISNULL/IFNULL
- isnull(exper) 判断exper是否为空，是则返回1，否则返回0
- ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替
- nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。
25. left join区别
- left join on and 两个条件，查询的结果是t1表的全部记录，不满足t.p_id = f.p_id的，用null显示。
- left join on where 两个条件，查询的结果是t1表的全部记录，但会根据t.p_id = f.p_id进行筛选过滤。
- 左连接一定要关注右表是否唯一
SELECT
t.*, f.*
FROM
`t1` t
LEFT JOIN t2 f ON t.trade_id = f.trade_id
AND t.p_id = f.p_id;
- mysql 不支持full join
26. 对于数据行的增删改查
1. insert
-
向表格中插入新的行
- INSERT INTO table_name VALUES (值1, 值2,…)
- INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
-
从一个表中选取数据，然后把数据插入另一个表中。常用于创建表的备份复件或者用于对记录进行存档
-
把所有的列插入新表
SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename -
只把希望的列插入新表
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename WHERE 条件（old_tablename）；
-
2. UPDATE
- 更新数据
单独更新一个字段
语法:
update 表名 set 字段1 = 新值1,
where 条件;
实例:
update s1 set name = 'ailsa',
where id = 1;
更新多个字段
语法:
update 表名 set 字段1 = 新值1,
字段2 = 新值2
where 条件;
实例:
update s1 set name = 'ailsa',
gender = 'female',
where id = 1;
3. delete
-
delete和truncate区别
-
处理效率：drop>trustcate>delete
-
drop删除整个表；
trustcate删除全部记录，但不删除表；
delete删除部分记录（一行一行删除） -
delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位。
-
-
语法
语法: delete from 表名 where 条件; delete from s1 where id = 2; 注: 不加where条件就是清空表,一定要慎重使用delete 清空表： delete from t1; # 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;# 数据量大，删除速度比上一条快，且直接从零开始，- drop table 表名 是把整个表删除，在数据库中找不到该表，而delete只是把表中的数据清除，表结构还在。
27.累计小数
1 round
- round(x,d) ，x指要处理的数，d是指保留几位小数
- 这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0
- round(x) ,其实就是round(x,0),也就是默认d为0，用于数据的四舍五入；
2 truncate（）
-
TRUNCATE(X,D) 是MySQL自带的一个系统函数
-
其中，X是数值，D是保留小数的位数
-
其作用就是按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，*没有四舍五入）。
-
规则如下：
1）当 D 大于0，是对数值 X 的小数位数进行操作；
2）当 D 等于0，是将数值 X 的小数部分去除，只保留整数部分；
3）当 D 小于0，是将数值 X 的小数部分去除，并将整数部分按照 D 指定位数，用 0 替换。
-
28. 子查询
-
子查询必须写表名 ，将这个子查询赋予一个新的表明
此处为错误的，因为没有写表名
29. 索引
- 好处：
- 通过创建索引,可以在查询的过程中,提高系统的性能
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
- 坏处
- 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
- 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
- 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护
30.where与having
- where条件不能是select中新创建的列，只能是from表中包含的列，可以用函数
- having可以
31. 数字类型转换
cast()和convert()函数比较：
(1)cast一般更容易使用，convert的优点是可以格式化日期和数值；
(2)convert一般用于日期和时间类型以及小数之间转换，而cast一般用于小数转数值和字符型；
(3)convert只是显示转换，而cast是强制转换。
1. cast（）
cast(字段名 as 转换的类型 )
select cast(123.45 as int) --123
select cast('123.447654' as decimal) --123 decimal只保留整数部分
select cast('123.447654' as decimal(5,2)) -- 123.45除了小数点外，最长为5，小数部分为两个小数
select cast('123.447654' as decimal(5,3)) --会报错
select cast('20210922' as date) --
--2021-09-22，将char类型转换为日期类型
select cast(getdate() as time)
--22:07:08.4200000，取当前的时间信息，不保留日期
2. convert（）
对于日期格式的转换更加灵活
- 语法：
convert(data_type(length),expression,style)
style：
常用：
SELECT CONVERT(INT, 25.65) --25
select CONVERT(decimal, '123.45') -- 123
select CONVERT(decimal(9,2), '123.45') -- 123.45
SELECT CONVERT(VARCHAR(25),GETDATE()) --09 22 2021 10:31PM
SELECT CONVERT(VARCHAR(24),GETDATE(),120) --2021-09-22 22:32:43，最常用，保留日期时间信息
SELECT CONVERT(VARCHAR(10),GETDATE(),102) --2021.09.22，只保留日期信息
SELECT CONVERT(VARCHAR(10),GETDATE(),111) --2021/09/22，只保留日期信息
SELECT CONVERT(VARCHAR(10),GETDATE(),108) --22:34:06，只保留时间信息
32 最大最小
- max（）：聚集函数，某列的最大值
- min（）：某列最小值
1. least(chinese, math, english) 某一行的最小值
2. greatest (chinese, math, english) 某一行的最大值
二进制比较
- 最方便的方式是expr全部是数值型：
- 全部是数值型
- 部分数值型，其余是数值字符串，可以通过隐式类型转换，将字符串变为数值型
- 部分数值型，其余是字母类字符串，没有输出。
- 字符串相关：
- 全部为字符型，最初字母最大的那一个,如果是length>1,则会进行一一对比，第一个最大的就是最大的，第一个相同，在比较第二个，大小写中：a<A
- 部分为字符型，会把非字符型转换成字符型：
SQL>
SELECT GREATEST('A', 'B', 'C', 'D', 'E', 'F','G') A FROM DUAL;
G
- 只要GREATEST的expr有一个为NULL，都会返回NULL。
-- expr_1为NULL时：
SELECT GREATEST(NULL, 'B', 'C', 'D', 'E','GA', 'GAB') A FROM DUAL;
A
-- expr_1不为NULL时，其它的expr为NULL时：
SQL>SELECT GREATEST('A', 'B', 'C', 'D', 'E',NULL, 'GAB') A FROM DUAL;
A
- 为了避免null，可以采用和ifnull相结合
SQL>SELECT GREATEST('A', 'B', 'C', 'D', 'E',ifnull(NULL,0), 'GAB') A FROM DUAL;
A
- 时间类型相关：
- 部分为时间类型，不能进行隐式类型转换：
- 全部为时间类型，则会选出时间最大的那个
33. with …as
- with … as也叫子查询，用来定义一个sql片段，且该片段会被整个sql语句反复使用多次，这个sql片段就相当于是一个公用临时表
with tmp as （select * from B）
select * from tmp
- with as 与select换行，之间没有任何间隔，直接select即可
- mysql 8.0以上版本
- 适用场景：因with as 子查询仅执行一次，将结果存储在用户临时表中，提高查询性能，所以适合多次引用的场景，如：复杂的报表统计，分页查询，且需要拿到sum、count、avg这类结果作为筛选条件，对查询出的结果进行二次处理！
- 语法
-- 针对一个别名
with tmp as (select * from tb_name)
-- 针对多个别名(以逗号为间隔)
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
-- 相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
34 in多字段用法
- 单个字段
select id from table where name in('name1','name2')
- 多字段
select id from table where (num,name) in ((num1,'name1'),(num2,'name2'))
表示num,name是否在(num1,‘name1’)或者(num2,‘name2’)两者中的一个。
35 union all&union
union 为组合查询
- 使用情况：
- 在一个查询中从不同的表中返回结构数据
- 对一个表执行多个查询，按一个查询返回数据
- union（合并重复项），union（不合并重复项）
- 注意事项：
- union中的每个查询必须包含相同的列，表达式或聚集函数（但是不需要按相同的顺序列出）
- 数据类型不必完全相同，但是必须兼容，即可以进行隐形转换
- 遇到列名不同的情况，会返回第一个列名，需要注意的是，此时如果使用order by语句只能用第一个列名，二不能是第二个列名，不然会出错，order by在select之后执行，但是执行结果中并不包含第二个列名
- 当A union B中的A或B包含至少有一个含有order by或者limit是，需要将union前后的sql加上括号：（A）union （B），否则会报错
36 字段重命名
- 重命名为关键字，需要加‘’，‘rank’
37 自定义函数
-
创建函数
-- delimiter 表示将结束符设为$$ delimiter $$ create function f1( i1 int, i2 int) returns int BEGIN declare num int; declare maxId int; select max(id) from big into maxId; set num = i1 + i2 + maxId; return(num); END $$ delimiter ; -
执行函数
select f1(11,22); select f1(11,id),name from d1; -
删除函数
drop function f1; -
例题： 第N高的薪水，来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。
-
因为没有具体指出N是多少，该题表示自定义函数
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
- 主体事项：
- begin和end之间的每一行都要有分号
- limit不能进行运算，所以不能写成limit N-1,1
440
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
