MySQL——基础知识

2018/08/05 10:49
阅读数 12

执行顺序

  • 示例代码

    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()
    

数据转换

合并数据

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

  • 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)
    

外键设置

帮助文档

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部