SQRT(POWER(' . $lat . ' - p.lat, 2) + POWER(' . $lng . ' - p.lng, 2)) asc
MySQL排除查询-排除查询一个表在另一个表中存在的数据
排除查询一个表在另一个表中存在的数据
例如:
- 任务完成后列表中不再显示该任务
- 任务领取后的记录表中存在的数据,在任务列表里不在显示
- 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 则条件刚好相反
一条MySQL语句统计多个字段
测试数据表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;
运行截图
用Navicat导出数据库的数据字典
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='表名称'
查询出的结果可以导出
Mysql权限处理
查看用户信息
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>