执行顺序
-
示例代码
select * from test inner join.... where... group by... having... order by...-- 先根据英语成绩排序，再根据数学成绩排序，当英语成绩相同再按照数学成绩排序 select * from TbScore order by tEnglish desc,tmath desc
存储过程
-
普通创建
CREATE PROCEDURE p_hello() BEGIN SELECT 'Hello'; SELECT 'world'; END -- 调用 call p_hello -
变量声明
declare 变量名 数据类型 default 默认值CREATE PROCEDURE p_vartest1() BEGIN DECLARE a VARCHAR(20) DEFAULT 'abc'; SELECT a; END -- 调用 call p_vartest1 -
变量赋值
CREATE PROCEDURE p_vartest2() BEGIN DECLARE a INT; SET a = 10; SELECT a; END -- 调用 CALL p_vartest2 -
输入参数
-
in：输入参数，表示此参数必须再调用存储过程之前就已经被定义，定义的值就算在存储过程中被修改了，也不会改变其初始值
CREATE PROCEDURE p_vartest3(in p_int INT) BEGIN SELECT p_int; set p_int = p_int + 1; SELECT p_int; END -- 调用 set @p_int = 3; CALL p_vartest3(@p_int) -
输出参数
-
out：输出参数，该值在调用存储过程已经被定义，定义的值即使有初始值，在存储过程中也是不认可的，首次打印会是 null，如果重新赋值那么在存储过程结束之后，外部打印之前的定义的值也将会改变
CREATE PROCEDURE p_vartest4(OUT v_out_int INT) BEGIN SELECT v_out_int; set v_out_int = 15; SELECT v_out_int; END -- 调用 SET @v_out_int = 10; CALL p_vartest4(@v_out_int)； SELECT @v_out_intCREATE PROCEDURE p_vartest5(OUT v_out_int INT) BEGIN SELECT COUNT(*) INTO v_out_int FROM comments; END -- 调用 SET @v_out_int = 0; CALL p_vartest5(@v_out_int); SELECT @v_out_int -
输入输出参数
-
inout：综合以上两个参数的特点，即认可传入参数的初始值，也可改变初始值，外部打印会得到改变之后的值
CREATE PROCEDURE p_vartest6(INOUT v_inout INT) BEGIN SELECT v_inout; SET v_inout = 10; SELECT v_inout; END -- 调用 SET @v_inout = 15; CALL p_vartest6(@v_inout); SELECT @v_inout -
if else
CREATE PROCEDURE p_test1(in age int) BEGIN IF age >= 18 THEN SELECT '成年人'; ELSE SELECT '未成年人'; END IF; END -- 调用 set @age= 19; CALL p_test1(@age);CREATE PROCEDURE p_test2(in age int) BEGIN IF age > 60 THEN SELECT '老年人'; ELSEIF 18 < age <60 THEN SELECT '中年人'; ELSE SELECT '未成年'; END IF; END -- 调用 set @age = 50; CALL p_test2(@age) -
case：类似 switch case
CREATE PROCEDURE p_test3(in v int) BEGIN DECLARE adds int; CASE v WHEN 1 THEN SET adds = 1000; WHEN 2 THEN SET adds = 2500; ELSE SET adds = 3000; END CASE; UPDATE comments set user_id = adds WHERE id = 2; END -- 调用 SET @v = 2; CALL p_test3(@v) -
还可以直接在查询语句中使用
-
https://www.cnblogs.com/raobenjun/p/7998467.html
-- 如果参数1为true，则返回参数2，否则返回参数3 SELECT IF(`user`.`name`>23,`user`.`name`,1000) FROM `user` WHERE id = 83SELECT topic_id, (case user_id WHEN 2500 THEN 8888 ELSE 7777 END) as user_id FROM comments -
ifnull(exp1, exp2)：exp1 如果是空值，那么返回结果就是 exp2，如果 exp1 不是控制，那么就返回 exp1
SELECT IFNULL(NULL,'not null') as result -
while：循环语句
-- 1——100累加 CREATE PROCEDURE p_test4() BEGIN DECLARE i int DEFAULT 1; DECLARE adds int DEFAULT 0; WHILE i <= 100 DO set adds = adds + i; set i = i + 1; END WHILE; SELECT adds; END -- 调用 CALL p_test4()-- 更改表数据 CREATE PROCEDURE p_test5() BEGIN DECLARE i int DEFAULT 0; DECLARE maxV int DEFAULT 0; WHILE i <= 100 DO SELECT MAX(id) into maxV FROM comments; SET maxV = maxV + 1; INSERT INTO comments (commet, user_id, topic_id) VALUES (maxV, maxV, maxV); SET i = i + 1; END WHILE; END -- 调用 CALL p_test5() -
repeat：循环语句
-- 偶数行改变 CREATE PROCEDURE p_test6() BEGIN DECLARE minV int DEFAULT 1; DECLARE maxV int DEFAULT 2; SELECT min(id) into minV FROM comments; SELECT max(id) into maxV FROM comments; REPEAT IF minV % 2 = 0 THEN UPDATE comments SET user_id = 999 WHERE id = minV; END IF; SET minV = minV +1; UNTIL minV > maxV END REPEAT; END -- 调用 CALL p_test6() -
loop：循环语句
-- 奇数行改变 CREATE PROCEDURE p_test7() BEGIN DECLARE minV INT DEFAULT 1; DECLARE maxV INT DEFAULT 1; SELECT min(id) INTO minV FROM comments; SELECT max(id) INTO maxV FROM comments; myloop: LOOP IF minV % 2 = 1 THEN UPDATE comments SET user_id = 555 WHERE id = minV; END IF; SET minV = minV + 1; IF minV > maxV THEN LEAVE myloop; END IF; END LOOP; END -- 调用 CALL p_test7() -
continue handler：捕获存储过程中的错误并继续执行，不常用
declare continue handler for sqlstate '23000' set @x=1; -
查看存储过程
-- 查看数据库下存储过程 show procedure status where db='数据库' -
删除存储过程
drop procedure if exists 存储过程名字 drop procedure if exists p_hello
自定义函数
-
开启服务：必须先确认是否有设置自定义函数权限
-- 查看是否有权限 show variables like '%fun%'; -- 手动开启权限 set global log_bin_trust_function_creators = 1; -
创建函数
create function fun_add( a int, b int) returns int begin return a+b; end; -- 调用 select fun_add(3,4)
触发器
-
触发器是特殊的存储过程，表示的是对表进行插入、修改、删除操作时候触发的一个钩子
-
监视动作：update、delete、insert
-
触发时间：after、before
-- 语法 create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt-- new 代表刚插入一行的信息 create triger tri_insertComment after insert on comments for each row begin insert monitor values (new.content, new.createAt); end;
事务
-
是否支持
-- InnoDB 才支持事务操作 show variables like '%storage_engine%' -
开始事务
start transaction; insert into comments (user_id, topic_id) values (123,123); insert into user (name) values ('Judy'); commit;-- commit and chain 表示提交之后再次开启新的事务 start transaction; insert into comments (user_id, topic_id) values (123,123); commit and chain; insert into user (name) values ('Judy'); commit;-- savepoint s1 表示设置还原点 start transaction; insert into comments (user_id, topic_id) values (123,123); savepoint s1; insert into comments (user_id, topic_id) values (123,124); savepoint s2; insert into comments (user_id, topic_id) values (123,125); savepoint s3; insert into comments (user_id, topic_id) values (123,126); savepoint s4; rollback to savepoint s2; commit;-- 事务中只要出现错误就回滚到原始状态 CREATE PROCEDURE p_tran() BEGIN DECLARE t_error INT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1; START TRANSACTION; INSERT INTO comments (user_id,topic_id) VALUES (456,457); INSERT INTO comments (user_id,ddddddddd) VALUES (458,459); IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; END IF; END -- 调用 CALL p_tran() -
rollback and release：表示事务回滚之后端口与客户端连接
游标
-
游标帮助我们遍历表中的每一行数据
-
帮助文档：https://www.cnblogs.com/lyhc/p/5760164.html
CREATE PROCEDURE p_cur() BEGIN -- 连接字符串 DECLARE str VARCHAR(100) DEFAULT 'start'; -- 存储遍历的一列数据 DECLARE topic_id_c INT(11); -- 遍历结束标志 DECLARE done INT DEFAULT FALSE; -- 声明游标 DECLARE cur_account CURSOR FOR SELECT topic_id FROM comments where user_id = 123; -- 将结束标识进行绑定，游标报错一般就是：NOT FOUND DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 打开游标 OPEN cur_account; -- 遍历 read_loop: LOOP -- 抓取每一行数据赋值到对应字段 FETCH NEXT FROM cur_account into topic_id_c; IF done THEN LEAVE read_loop; END IF; -- 做自己想做的事 SELECT CONCAT(str,'|',topic_id_c) INTO str; END LOOP; CLOSE cur_account; SELECT str; END -- 调用 CALL p_cur()
数据转换
-
CONVERT(COUNT(*) ,SIGNED)：转成字符串
合并数据
-
多表联合查询，特别是一对多的情况下，需要将外键表进行有效压缩
-
GROUP_CONCAT：多行数据合并一行，也可以将多列数据合并成一列，指定分割符号，GROUP_CONCAT(r.role_name SEPARATOR '|')，这样的方式同样可以删除连接符
-
CONACT：多列数据合并成一列，需要配个 IFNULL 使用，因为只要其中有一列是空值，合并就失败了
-
列1+列2：计算两个列的合，注意其中有一列为NULL，那么结果都为NULL，所以计算前最好用 IFNULL 进行判断
-
SELECT GREATEST(1,NULL)：判断并返回最大值，同样也有上述问题，最好使用 IFNULL 进行判断 -
帮助文档
-
示例代码
SELECT tc.id, tc.title, tc.content, GROUP_CONCAT(cm.user_id) as users FROM topics as tc inner join comments as cm on tc.id = cm.topic_id GROUP BY id ORDER BY tc.id-- concat 是简单的多列合并，group_concat 是进行依据分组进行纵向（行）合并 SELECT topics.id, topics.title, topics.content, GROUP_CONCAT(CONCAT(topics.user_id,'|',CASE `user`.`avater` WHEN '' THEN 'null' ELSE `user`.`avater` END)) as info FROM topics INNER JOIN comments ON topics.id = comments.topic_id INNER JOIN `user` ON topics.user_id = `user`.id GROUP BY topics.id-- 最保险的写法，将查询结果作为连接查询的表，结构清晰 SELECT topics.*, users.username, users.avatar, new_topics_like.like_user_id, new_topics_collection.collection_user_id FROM topics INNER JOIN users ON topics.user_id = users.id LEFT JOIN (SELECT topics_like.topic_id, GROUP_CONCAT(topics_like.user_id) as like_user_id FROM topics_like WHERE topics_like.deletedAt is NULL GROUP BY topics_like.topic_id) as new_topics_like ON topics.id = new_topics_like.topic_id LEFT JOIN (SELECT topics_collection.topic_id, GROUP_CONCAT(topics_collection.user_id) as collection_user_id FROM topics_collection WHERE topics_collection.deletedAt is NULL GROUP BY topics_collection.topic_id) as new_topics_collection ON topics.id = new_topics_collection.topic_id WHERE topics.id = 38 AND topics.deletedAt is null LIMIT 1
联表查询
-
inner join：适合严格一对一，虽然是一个表依赖另一个表（外键），但是并没有构成一对多的关系
-
left join：当查询表是主表，对副表进行连接查询，构成一对多的关系，适合使用左联，因为情况往往是主表是肯定存在的，但是副表不一定有对应数据，此时还使用 inner join 将不会查询到任何结果，而且遗漏了主表数据；如果是多对一的话，还是适合 inner join 进行查询
-
个人觉的多表联合查询，可以借用变量。例如，借用 JS 变量存储某个表格，最后用一个总的变量去连接上述已经存储表的变量
// -- users var users = "select * from users where deletedAt is null" var topics = "select * from topics where deletedAt is null" // -- comments var comments = "select * from comments where deletedAt is null and user_id =88" // -- comments inner join user var result_c = `select new_comments.id, left(new_comments.content, 100) as content, new_comments.createdAt, new_users.avatar, new_users.username, new_comments.topic_id, new_topics.title as targetContent, 'parent' as targetCategory from (${comments}) as new_comments inner join (${users}) as new_users on new_users.id = new_comments.user_id inner join (${topics}) as new_topics on new_topics.id = new_comments.topic_id` // -- comments_son var comments_son = "select * from comments_son where deletedAt is null and user_id = 88" // -- comments_son inner join users | comments_son inner join comments var result_cs = `select new_comments_son.parent_comment_id as id, left(new_comments_son.content,100) as content, new_comments_son.createdAt, new_users.avatar, new_users.username, new_comments.topic_id, left(new_comments.content,100) as targetContent, 'son' as targetCategory from (${comments_son}) as new_comments_son inner join (${users}) as new_users on new_users.id = new_comments_son.user_id inner join (${comments}) as new_comments on new_comments.id = new_comments_son.parent_comment_id` var result = `(${result_c}) union (${result_cs})` console.log(result)
外键设置
- navicat 如何设置外键：https://blog.csdn.net/u013215018/article/details/54981216