MySQL——基础知识

执行顺序

• 示例代码

  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_int
  

  CREATE 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 = 83
  

  SELECT  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)：转成字符串

• https://www.cnblogs.com/emanlee/p/5998683.html

合并数据

• 多表联合查询，特别是一对多的情况下，需要将外键表进行有效压缩

• GROUP_CONCAT：多行数据合并一行，也可以将多列数据合并成一列，指定分割符号，GROUP_CONCAT(r.role_name SEPARATOR '|')，这样的方式同样可以删除连接符

• CONACT：多列数据合并成一列，需要配个 IFNULL 使用，因为只要其中有一列是空值，合并就失败了

• 列1+列2：计算两个列的合，注意其中有一列为NULL，那么结果都为NULL，所以计算前最好用 IFNULL 进行判断

• SELECT GREATEST(1,NULL)：判断并返回最大值，同样也有上述问题，最好使用 IFNULL 进行判断

• 帮助文档

  • https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc
  • https://www.cnblogs.com/zhengah/p/5199843.html
  • https://www.cnblogs.com/thomas12112406/p/5813394.html
  • https://blog.csdn.net/icecoola_/article/details/78074773

• 示例代码

  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

帮助文档

• https://www.cnblogs.com/rooney/archive/2012/03/09/2387732.html

• https://blog.csdn.net/superit401/article/details/78056654?locationNum=8&fps=1