分类 Mysql 下的文章

查询正打开的表

show OPEN TABLES where In_use > 0;

查询正在进行的进程

show processlist;

查看当前运行的所有事务

select * from information_schema.INNODB_TRX;

查看存储引擎的状态

show engine innodb status

查询死锁表

-- 查询死锁表 8.0之前
SELECT * from INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查询死锁等待时间 8.0之前
select * from information_schema.INNODB_LOCK_waits;

-- 查询死锁表 8.0之后
select * from performance_schema.data_locks;
-- 查询死锁等待时间 8.0之后
select * from performance_schema.data_lock_waits;

SQRT(POWER(' . $lat . ' - p.lat, 2) + POWER(' . $lng . '  - p.lng, 2)) asc

排除查询一个表在另一个表中存在的数据
例如：

• 任务完成后列表中不再显示该任务
• 任务领取后的记录表中存在的数据，在任务列表里不在显示
• task 任务表、task_sign 任务记录表

SELECT
*
FROM
    `sz_task`
WHERE
    AND (
        SELECT 
            count( * ) AS num 
        FROM 
            sz_task_sign 
        WHERE 
            `sz_task`.id = `sz_task_sign`.task_id AND STATUS = '3'
        AND user_id = 1
    ) = 0 

注意：

• 如果子查询中的 ( SELECT count( * ) AS num FROM sz_task_sign WHERE sz_task.id = sz_task_sign.task_id AND STATUS = '3' AND user_id = 1 ) = 1 则条件刚好相反

测试数据表demo

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_order
-- ----------------------------
DROP TABLE IF EXISTS `test_order`;
CREATE TABLE `test_order` (
  `id` int(11) NOT NULL,
  `order_no` varchar(32) DEFAULT NULL COMMENT '订单号',
  `user_id` int(11) DEFAULT NULL COMMENT '用户id',
  `shop_id` int(11) DEFAULT NULL COMMENT '商家id',
  `status` tinyint(1) DEFAULT NULL COMMENT '订单状态 0待发货 1已发货 2已收货 3已评论 4申请退款',
  `create_time` int(10) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_order
-- ----------------------------
INSERT INTO `test_order` VALUES ('1', 'aaaaaaaaa', '11', '111', '1', '1573041313');
INSERT INTO `test_order` VALUES ('2', 'bbbbbbbbb', '11', '222', '1', '1573041313');
INSERT INTO `test_order` VALUES ('3', 'ccccccccc', '11', '333', '2', '1573041313');
INSERT INTO `test_order` VALUES ('4', 'ddddddddd', '11', '222', '3', '1573041313');
INSERT INTO `test_order` VALUES ('5', 'eeeeeeeee', '11', '111', '4', '1573041313');
INSERT INTO `test_order` VALUES ('6', 'fffffffff', '11', '111', '3', '1573041313');
INSERT INTO `test_order` VALUES ('7', 'ggggggggg', '11', '222', '4', '1573041313');
INSERT INTO `test_order` VALUES ('8', 'hhhhhhhhh', '11', '111', '0', '1573041313');
INSERT INTO `test_order` VALUES ('9', 'iiiiiiiii', '11', '333', '0', '1573041313');
INSERT INTO `test_order` VALUES ('10', 'jjjjjjjjj', '11', '222', '1', '1573041313');

表截图

查询语句

SELECT
    count( CASE WHEN STATUS = 0 THEN 1 END ) AS '待发货',
    count( CASE WHEN STATUS = 1 THEN 1 END ) AS '已发货',
    COUNT( CASE WHEN STATUS = 2 THEN 1 END ) AS '已收货',
    count( CASE WHEN STATUS = 3 THEN 1 END ) AS '已评论',
    count( CASE WHEN STATUS = 4 THEN 1 END ) AS '申请退款' 
FROM
    test_order;

运行截图

USE information_schema;


SELECT
    C.COLUMN_NAME AS '字段名',
    C.COLUMN_TYPE AS '数据类型',
    C.IS_NULLABLE AS '允许为空',
    C.EXTRA AS 'PK',
    C.COLUMN_COMMENT AS '字段说明',
        C.TABLE_NAME AS '表名'
FROM
    COLUMNS C
INNER JOIN TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
WHERE
    T.TABLE_SCHEMA = '数据库名称'   

也可以在where条件后面指定表名称

and T.TABLE_NAME='表名称'

查询出的结果可以导出

查看用户信息

select `Host`,`user` from user;

mysql> select `Host`,`user` from user;
+-----------+------------+
| Host      | user       |
+-----------+------------+
| 127.0.0.1 | root       |
| ::1       | root       |
| localhost | root       |
+-----------+------------+
4 rows in set (0.00 sec)

创建一个用户
示例：create user '用户名'@'host权限' identified by '密码';

mysql> create user 'test'@'%' identified by 'MV5vVQUu';
Query OK, 0 rows affected (0.00 sec)

授予用户权限
示例：grant 权限类型(select、update、delete可多个，all 表示所有) privileges on 表名.操作权限(*表示所有) to '用户名'@'host权限'

mysql> grant all privileges on test_db.* to 'test'@'%';
Query OK, 0 rows affected (0.00 sec)

查询用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'test'@'%'                                                 |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

取消用户权限
示例：revoke all on 表名.操作权限 from test@'%'

mysql> revoke all on test_db.* from test@'%';
Query OK, 0 rows affected (0.00 sec)

再次查看用户权限

mysql> show grants for test@'%';
+-----------------------------------------------------------------------------------------------------------+
| Grants for test@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*3EB9B6C888B00C67DA138D280D13B8C5129A1DD2' |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改用户密码

//update 需要指定密码和password函数
//推荐方法 
ALTER USER test@'%' IDENTIFIED BY '123456';

最后别忘了刷新权限

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>