Authorization statements are used to manage the permissions of database users. Common authorization statements are as follows:
1. Authorize the user's SELECT permissions on the table
GRANT SELECT ON TO username; Authorization
grant select on test_db.students to new_user;
2. Type conversion
cast(col AS INTEGER); to integer
to_char(now()-interval '1 day','yyyymmdd'); Time to string
NVL(to_char(to_char(col,'YYYYMMDDHH24MISS')))
3. PGV database: case sensitive, library table column names are lowercase, commonly used types are: bigint, numeric, date, timestamp, time
string_agg(column_name, separator) concatenates all lines of a field in the result set into a string
substr("original character", "specified character", boolean value) Gets all characters after or before the specified string
SUBSTRING(string FROM start [FOR length]) Intercepts characters at the specified position in the string
LEFT(string, length) intercepts the specified number of characters on the left side of the string
RIGHT(string, length) Intercepts the specified number of characters on the right side of the string
CONCAT(string1, string2, ...) Concatenate multiple strings
replace(uuid_generate_v4()::text,'-','') Get uuid: sys_uuid, gen_random_uuid, uuid_generate_v5
cast('123' as bigint) '123'::numeric bigint '123'
row_number() over( [ partition by col1] order by col2[ desc ] )
select max(length(colname)) from tbname; Get the maximum column length
NVL(expr1,expr2); If expr1 is empty, it returns expr2; otherwise it returns expr1 (the function needs to be created first)
COALESCE(expr1, expr2, ...); Returns a value that is not empty one by one. If all expressions are empty, return NULL
md5(str)
pgv3 cannot be used with NOT IN, but with LEFT JOIN or NOT EXISTS
SELECT ID FROM A WHERE NOT EXISTS IN(SELECT 1 FROM B WHERE =)
Time operation:
select now() ; current time /* timestamp format 'yyyyMMdd hh:mm:ss' */
select current_date ;current time /* date format ‘yyyyMMdd’ */
select now() - interval '1 week';
select to_char(now()-interval '1 month','yyyymm'); Get the date of last month
select date_trunc('day',now()::TIMESTAMP) Get the time of the day
select date_trunc('month',now()::TIMESTAMP) Get the 1st of the month
select trunc(now(),'mm') Get the 1st of the month
select date_trunc('month',now()) + '1month -1day'; the end of the month of the current date
select (date_trunc('month',now()) +'-1 day')::date ; the end of the previous month of the current date
select (date_trunc('month',now()) +'-1 month')::date ; the 1st of the previous month of the current date
select add_months(trunc(now(),'mm'),-1) The 1st of the current date
select date_trunc('quarter', current_date) Gets the current quarterly start date
select date_trunc('quarter', current_date) + '3 month' - interval '1 day' Get the current quarter end date
select date_trunc('year',now())::date; the beginning of the year of the current date
select date_trunc('year',now()) + '1year - 1 day' ::date ; the end of the current date
select extract(month from now()+'-1 month'); Get the number of months in the previous month
select extract(month from date('202402'||'01')); Get the number of months
select extract(year from now()+'-1 month'); Get the number of years in the previous year
select to_char(to_timestamp('2024-2-26', 'YYY-MM-DD'),'YYYY-MM-DD'); Date to string
select to_char(now(), 'YYYY-MM-DD HH24:MI:SS') Get the date and time string
select to_date('2024-2-26', 'YYY-MM-DD'); string to date
select date_part('month',now()) gets the number of months
EXCEPT compares the differences between the two tables: the first table appears in the data, but not in the second table. outer join add primary key to compare data from two tables
select * from pg_tables where TABLENAME='tbname'; query table attribute
select * from information_schema_table_privileges where table_name='tbname'; Query table permissions
select 'grant select on schN.'||tablename||' to newUN;' from pg_tables
where schemaName='schN' and tableowner='oldUN'; #Batch empowerment
select pid, query from pg_stat_cluster_activity where query like '%tbname%' and nodename like '%cn%'; #Query deadlock
select pid, query from pg_stat_cluster_activity where queryid='qid'; #View parallel execution
select pg_terminate_backend('process pid'); kill process pid
select pg_cancel_backend('process pid'); kill process pid
4. ORACLE database: Strictly case sensitive (19c table alias can be added without AS), spliced single quotes ''',''' Special types include CLOB
LISTAGG(XXX,',') WITHIN GROUP( ORDER BY XXX) over(partition by XXX) rank row to column splicing string
SELECT SUBSTR(CAST(LISTAGG(COL,''',''') WITHIN GROUP(ORDER BY KW) AS VARCHAR2(1000)),1,1000) AS LTAG
RTRIM(xmlagg(xmlparse(content col || ','wellformed) order by col).getclobval(),',')
RTRIM(xmlagg(XMLELEMENT(E, col,',').EXTRACT('//text()') order by col).getclobval(),',')
SUBSTR(string, start, [length]); Intercept string
months_between('2024-02-11',sysdate-1); Get month difference
select trunc(start-end) from dual; get the time difference day floor(sta-end); get the date difference
add_months(trunc(sysdate,'mm'),-1) Get the 1st of the previous month
add_months(sysdate,-1) Get the date of last month
SELECT add_months(SYSDATE, 1) FROM dual; Date after one month
SELECT add_months(TRUNC(SYSDATE, 'YYYY'), 12) - TRUNC(SYSDATE, 'YYYY') days FROM dual; days in that year
-- minus 10 minutes of the current time, and change it to year (year), month (month), day (day), hour (hour), second (second)
select sysdate,sysdate - interval '10' minute from dual; Get ten minutes ago time
select sysdate - interval '10' day as ten_days_ago,sysdate -10 from dual; Get ten days ago time
to_date('202402'||'01','yyyymmdd') string to format date
to_char(add_months(sysdate,-1),'mm') Get the number of months in the previous month
to_char(sysdate, 'YYYY') Get the number of years of this year
cast(to_date('2024/2/26','yyyy-mm-dd,hh24:mi:ss') as timestamp) string to timestamp
to_char(to_timestamp('2024-2-26','yyyy-mm-dd hh24:mi:ss') ,'YYYY-MM-DD HH24:MI:SS') Special format: DD-MON-YY AM
NVL(expr1,expr2); if expr1 is empty, it returns expr2; otherwise it returns expr1
COALESCE(expr1, expr2, ...); Returns a value that is not empty one by one. If all expressions are empty, return NULL.
select ROW_NUMBER() OVER (PARTITION BY uuid ORDER BY dt DESC) AS rn FROM tbname where rn=1; Group sorting takes the first one
select SYS_GUID() from dual; get database uuid
DBMS_RANDOM.VALUE Gets the random number RANDOM()
MINUS compares the differences between the two tables. You can also use NOT IN subquery, NOT EXISTS subquery, LEFT JOIN and IS NULL to judge.
select * from all_tables t where t.table_name like '%tbname%' and like '%user%'; Check the table
select * from all_objects t where t.object_name like '%tbname%' and like '%user%';
select * from dba_segments t where t.segment_name like '%tbname%' and like '%user%';
select * from all_col_comments t where t.table_name like '%tbname%' and t.column_name like '%comn%';
SELECT Last_Day FROM(SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-24)+LEVEL-1),'YYYY-MM-DD') AS Last_Day -- Get the last day of each month in the last two years
SELECT FROM DUAL CONNECT BY LEVEL<=ADD_MONTHS(SYSDATE,-0)-ADD_MONTHS(SYSDATE,-24)+1)aa GROUP BY Last_Day;
SELECT CASE WHEN REGEXP_LIKE(col,'^(-)*[[:digit:]]+(\.[[:digit:]]+)*$')
THEN 'Number' ELSE 'Non-Number' END AS rt FROM tbname; Find number
5. Vertical database: Strictly case-sensitive, commonly used functions are similar to pg, and some functions are similar to Oracle
row_number() over (partition by colkey order by col) as rnk Group sort number
rank() over (partition by colkey order by col) as rnk Grouping sorting Continue numbering
GETDATE() NOW() Get the current time
MONTH(now())-1 Get the number of months in the previous month
last_day(date('202402'||'01')) The end of the month of the current date
substr(cast(listagg(col USING parameters max_length=3000,on_overflow='TRUNCATE') AS varchar),1,1199); Row to column splicing string, intercept 1199 segments
REPLACE(CAST(UUID_GENERATE() AS VARCHAR),'-','') Get the database uuid
SELECT MONTHS_BETWEEN('2023-03-01'::DATE,'2023-01-01'::DATE) AS MONTHS_DIFFERENCE; Get month difference
DROP TABLE tbname CASCADE Delete tables with dependencies
alter table alter column colname set DATA TYPE ${dataType}; Modify field type
alter table rename column colname to field2; modify column name
alter table alter column colname drop not null; delete field without empty constraints
select table_name,owner_name from tables where table_name='tbname'; View table owner
6. MYSQL database: Strictly case sensitive
SELECT DATE_FORMAT(NOW(),'%Y-%M-%D %H:%I:%S'); Get the date string
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); add 1 day DAY/HOUR/WEEK/MONTH/QUARTER/YEAR
SELECT DATE_SUB(NOW(),INTERVAL 30 DAY);
The above are common operations in different databases, including authorization statements, data type conversion, time operations, string operations, NULL value processing, window functions, database specific functions, table and permission management, and comparison table differences, etc., which facilitates the processing of data in different databases in daily life.