MySQL 基本语句

一、基本语句

/*MySQL 版本查询*/
SELECT VERSION();
/*MySQL 连接数*/
SHOW FULL PROCESSLIST ;
/*MySQL 最大连接数*/
SHOW VARIABLES LIKE "%max_connections%";
/*结束进程*/
KILL  1876878;
/*清除缓存*/
FLUSH HOSTS; 
/*设置root密码*/
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
/*查询表的所有字段*/
SHOW COLUMNS FROM `test_db` ;
/*查询表的所有字段*/
SELECT GROUP_CONCAT(COLUMN_NAME)  FROM information_schema.columns WHERE table_name='test_table';
/*查询数据库的所有表名*/
SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='test_db';

 

二、写入数据

/*基本插入*/
INSERT INTO `test_table` (`name`,`age`) VALUES("李四", 20);
/*基于某些字段避免重复插入*/
INSERT INTO `test_table` (`name`,`age`) SELECT '张三', 18 FROM 
DUAL WHERE NOT EXISTS (SELECT `name`,`age` FROM `test_table` WHERE `name`='张三' AND age=18);
/*
数据存在则替换插入 基于唯一字段
如：name为唯一字段则生效 
原理：删除旧数据，添加新数据 如果设置自增Id 可看出明显区别
*/
REPLACE INTO `test_table`(`name`,`age`) VALUES('张三', 28)
/*
数据存在则跳过 基于唯一字段
如：name为唯一字段则生效 
*/
INSERT IGNORE INTO `test_table`(`name`,`age`) VALUES('张三', 28)

三、更新

/*更新*/
UPDATE `test_table` SET `name`='王五',`age`=22 WHERE `id` = 1;

四、查询

/*排序时null在最后*/
SELECT * FROM `test_table` ORDER BY ISNULL(`age`);
/*字段去重*/
SELECT DISTINCT `name` FROM `test_table`;
/*合并行*/
SELECT *,GROUP_CONCAT(`name` SEPARATOR ',') AS `name` FROM `test_table` GROUP BY `name`;
/*去重合并行*/
SELECT GROUP_CONCAT(DISTINCT `name` ORDER BY `name` SEPARATOR ',') AS `name` FROM `test_table` GROUP BY `name`;
/*随机排序*/
SELECT * FROM `test_table` ORDER BY RAND() 

/* @r 为查询的Id 字符串Id 为is not null 数字id 为 != 0 */
SELECT T2.name
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM context WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := '05694bf2-3092-11e9-ab3a-005056a2e4ff', @l := 0) vars,
        context h
    WHERE @r IS NOT NULL) T1
JOIN context T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;