常用sql记录

1.添加字段(mysql)

ALTER TABLE `mydb`.`sys_user` 
ADD COLUMN `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' AFTER `user_name`;

2.修改字段(mysql)

ALTER TABLE `mydb`.`sys_user` 
CHANGE COLUMN `u_sex` `u_gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别' AFTER `pass_word`;

3.获取某张表中的所有字段

<!-- oracle -->
select t.column_name field from user_col_comments t where t.table_name = 'sys_user'

<!-- mysql -->
show full columns from sys_user

4.分页

// mysql   limit 开始行, 数据条数
select *  from sys_user limit 0,15

// postgres  limit 数据条数 offset 开始行
select *  from sys_user limit 15 offset 0

// oracle
select * from(
SELECT t.*,ROWNUM rn from sys_user t where rownum <= 20
) tt where tt.rn >10

5 判断空

// mysql
SELECT IFNULL(t.phone,'110')  from sys_user t

// postgres
SELECT COALESCE(t.phone,'110') from sys_user

// oracle
select NVL(t.phone,'110') from sys_user t

6.查看数据库版本信息

// oracle
select * from v$version;

// mysql postgres
SELECT version()

7.ifnull使用

-- mysql
select IFNULL(t.user_name,"jack") from sys_user t
-- oracle
select NVL(t.user_name,"jack") from sys_user t
-- postgres
select coalesce(t.username ,'jack') from sys_users t 

8 查询一段时间内连续天数

-- postgres
SELECT to_char(date,'yyyy-mm-dd')
FROM generate_series(
    '2025-05-19 00:00:00'::date,
    '2025-05-25 23:59:59'::date,
    '1 day'::interval
) AS date;