Skip to content

Mysql

mysql创建时间字段

CREATE TABLE `proxy`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proxy_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `proxy_pwd` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `proxy_mac` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

create_time表示新增一条数据的时候, 显示当前的时间, 也就是说, 默认显示的是创建此条数据的时间 update_time表示更新此条数据数据的时间, 默认显示的是更新数据的时间

mysql json查询包含在某个数组内的数据

SELECT * FROM user_info a WHERE JSON_CONTAINS(userHobby,'"王者荣耀"');

SELECT * FROM user_info a WHERE JSON_CONTAINS(userHobby,'["王者荣耀","羽毛球"]');

mysql json对象选择

SELECT * FROM user_info a WHERE userInfo -> '$.gender' = '女';

mysql创建并复制表

CREATE TABLE good_stock_copy LIKE good_stock;
INSERT INTO good_stock_copy SELECT * FROM good_stock;

mysql创建触发器

当插入数据时, 更新库存数量

CREATE TRIGGER consume_copy_insert AFTER INSERT ON consume_copy FOR EACH ROW
BEGIN
    UPDATE good_stock_copy a SET goodCount=goodCount-NEW.consumeCount WHERE goodId = NEW.consumeGoodId;
END;

当删除数据时, 更新库存数量

DROP TRIGGER IF EXISTS consume_copy_delete;
CREATE TRIGGER consume_copy_delete AFTER DELETE ON consume_copy FOR EACH ROW
BEGIN
    UPDATE good_stock_copy a SET goodCount=goodCount+OLD.consumeCount WHERE goodId = OLD.consumeGoodId;
END;

mysql排名

rank() over,dense_rank() over,row_number() over的区别

https://www.cnblogs.com/scwbky/p/9558203.html

SELECT d.`name` Department,s.`name` Employee,s.salary Salary FROM (SELECT *,RANK() OVER(PARTITION BY e.departmentId ORDER BY e.salary DESC) rank1 FROM employee e) s,department d WHERE s.rank1 = 1 AND s.departmentId = d.id;

oracle查询某月最后一天日期

select to_char(last_day(to_date('2022-04','yyyy-mm')),'yyyy-mm-dd') from DUAL

判断是否为空

不为空则返回0

coalesce(travelled_distance,0)

COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提供了它的简化版，这些简化版中只接受两个变量，其参数格式如下：

MYSQL: 

IFNULL(expression,value)

MSSQLServer: 

ISNULL(expression,value)

Oracle: 

NVL(expression,value)

日期差

between subdate('2019-07-27',30) and '2019-07-27' group by activity_date

linux mysql安装

设置所有IP访问

vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address            = 0.0.0.0

sqlite字符串拼接

select 'a'||'b'