[MariaDB] MariaDB/MySQL queries I learned when I came here with Oracle

When I tried to use MariaDB with Oracle for more than a decade, it wasn’t just one or two stifling things (I’m sure there are…).. As soon as I knew it, I didn’t know, so I kept looking for it.).Here are some of the key MariaDB queries we’ve seen on the project in recent months.1. When viewing DUALDummy information, you don’t have to write ~FROM DUAL on Oracle or use it.

Select NOW(); Dual to [Select Now()];

2. There are too many LIMIT {number} queries, so it is used to view only a part of them.I’ll just bring 10 of them

Select…from…WHERE 1=1…Limit 10;

I use ROWNUM in Oracle, but.. It’s a little different.See how to write ROWNUM like MariaDB/MySQL LIMIT in Oracle: https://gent.tistory.com/254

[Oracle]Oracle limitations are used in Oracle, browse to the LIMIT Section, browse to the LIMIT Section.But I regrettable, I cannot use LIMIT.The LIMIT and ROI is better to use ROMNIMIT and ROI, but the ROI of using the LIMIT and ROI.LIMIT has been executed until the query is executed until the query is executed, and the result of the data that is not performed by the query is completely performed. ※ ※ ※ …gent.tistory.com

3. Similar to NVL in IFNULL Oracle.Example) If COL is NULL, convert to blank (‘) and compare

Select…from…where 1=1 and… and IFNULL({COL}, ”) = ”

– Similarly, NULLIF.Why do you get a mistake?I’ve got NULLIF is NULUM, but I’m looking for the Oracle.I found the project source, 817 and the NULLIF is 472 cases (not a little more NULLIF is 472 (a less than I thought of the NLIF is a little more than I thought.4. The LEGOIN Oracle, but I didn’t have a little awkward when you use this.I knew that I was a little confused, but I used to use the MariaDB environment.Example) ABCD table conditions are always satisfied, and the EGH table conditions are always satisfied

ABCD の ABCD Left Outer Join EFGH から * Select the .IDX = EFGH.IDX;

5. I would like to ask for a difference in the date from today.Use the DATEDIFF function, e.g

DATEDIFF(A.) Select the .CRT_DT、B。CRT_DT) DDCNTFROM …

6. Use the date format DATE_FORMAT() function.Example 1) Compare to Year, Month, Day only column (END_DT)

Select… From… WHERE 1=1… END_DT = = DATE_FORMAT(NOW(), ‘%Y%m%d’)

 

Example 2) Convert data in DATE format to STRING format (date, month, minute and second)https://ko.linux-console.net/common-images/check-mysql-database-size-in-linux/Check-Size-of-MySQL-Database.png選択 NOW(), DATE_FORMAT(NOW(), ‘%Y%m%d%H%i%s’) AS NOW_STR;+——————— ++|NOW() | | NOW_STR |+—————– +—–+|2022-11-14 16:36:45 | 20221114163645|+ —— + —— +7. I just wanted to check the date calculation for the last 5 days.ADDATE and DATE_ADD perform the same functions, SUBDATE and DATE_SUB perform the same functions.Negative numbers in the DATE_ADD function are the same function as DATE_SUB.Among existing sources, DATE_ADD was overwhelmingly high.- 37 ADDates, – 1260 ADDs, – 0 SUBDATE, – 476 DATE_SUBExample 1) Enquire only if the registration date (RGST_DTM) is within the last 5 daysUS>Select…from…WHERE 1=1…AND RGST_DTM > DATE_ADD(NOW(), Interval – 5 days)Example 2. Inquire about the previous monthSelect…from…WHERE 1=1…SVC_DT LIKE CONCAT (DAT_FORMAT(DAT_FORMAT(NOW(), interval -1 month), ‘%Y%m’, ‘%’)@+: @+: @+: https://m2seo.tistory.com/31Mysql/Mariadb DATE_ADDATE_UB ( date/hour) date/DATE_UB ( date/hour). interval:seconde(秒) minute(分)Source (Sunday)Use the monitoring of the monitoring date/hour and time to add a reference time, and interval for the reference time.If you enter the minus (-) to the current time. – query the current time. – query the current time mysql> select now() now, date_add(n···m2seo.tistory.com8. Use the STR_TO_DATE function to convert a string to a date.Example)SELECT STR_TO_DATE(‘20221101 23:59:59’, ‘%Y%m%d %H:%i:%S’)+————————- +|STR_TO_DATE(‘20221101 23:59:59’, ‘%Y%m%d %H:%i:%S’) |+——————————— ++|2022-11-01 23:59:59 |+ —— —— —— ++Go to https://mariadb.com/kb/en/str_to_date/ .Convert STR_TO_DATE string to date.mariadb.com9. Re- returns to query (WITH RECURSIVE) The project was created by the PREVSIVE section.I realized that I tried to Java, I tried to be a WITH RECURSIVE.If you know, WITH, WITH, WITH, WITH, WITH, WITH, it was also an Oracle.I thought that I was not too much, so I thought it is only a lot of money.WITH was called CTE (CommonTable Expressions).N-RREVENTIVE and security.참고 자료:https://mariadb.com/kb/en/common-table-expressions/共通表 Expressionsmariadb.comgrammar.WITH [RECURSIVE] TABLE Name AS (SELECT – #Non-RepeatDoor. Absolutely required [UNION ALL] # Required when using RECURSIVE. If you want to continue, SELECT – [WHERE – ] # Required when using RECURSIVE. Used when stop conditions are required)References: https://horang98.tistory.com/10MySQL hierarchy query – WITH, WITH, WITH, WITH, WITH syntax is saved on memory.A re- returns by two ways of RECURSIVE, and the non-release. WITH [RECURSIVE]TABLE name ASELL (SELECT – # non-pyrip door.absolutely required [UNCTION ALL] # RECURSIVE USIVE.If the following is:SELECT – [WHERE -] # Required when RECURSIVE use RECURSIVE.Query… horang98.tistory.comSee examples from table generation.Insert 1 (from_int, and select 1 value (11,2,1) and selectively) (1, 2,3, paragraph 1, 2,3, paragraph 1, 2,3) and (1, paragraph 1, 2,3) (1, paragraph (1)) * * t1;+————–+| from_ | to_ | +———+| 1 | 2 || 1 | 100 || 2 || 3 || 3 | 4 | + —— + —+ —+ –+Example 1) This is an example of using WITHUNDER_TENAS (ELECTROM T1 WHERE TO_<10)[* From UNDER_TEN;+———+| from_ | to_ +———+| 1 | 2 | 3 | 3 | 4 | 4 | 1 | + ——-+]Example 2) Use case of WITH RECURSIVE再帰的cte (深さ、from_、to_) AS ( SELECT 0,1,1,1 UNION DISTINCT SELECT depth+1, t1.from_, t1.to _FROM t1, cte WHERE t1.from_ = cte 。[1]depth<10)Select * Select e.——————————————————depth | from_ | | | +— +— +— + | + | + | + | + | + | + | + | + | 0 | 1 || 1 || 1 || 1 || 2 || 2 || 3 | 4 || 4 || 5 | . . / / / / ./ – – — / — — — –> — > 10 | 2 | ./4 { { 5, 5, 1, 100, 6, 6, 7, 9、4、1、1、4、910. NOW(), SYSDATE(), and CURDATE()Oracle only used SYSDATE, but MariaDB had various dates.NOW()、SYSDATE()==> Year, Date, Minutes and Seconds Output CURDATE()==> Date OutputSELECT NOW();+—————–+|NOW() |+————-+|2022-11-16 09:42:01|+———+SELECT SYSDATE(); +———++|SYSDATE() | +————-++|2022-11-16 09:48:05 | —— —— + CUR(CUR) |+DATE — (CUR) | CURE(CUL)When you see existing sources, the most NOW() is used.NOW()—87 times using SYSDATE() is the same as a https://riptutorial.com/mysql/example/14008/sysdate—-now—-curdate– :3 times the same as a DATE() and SYSDATE()Looking at existing sources, NOW() was used the most.NOW(): Used 8,887 times SYSDATE(): Used 73 times CURDATE(): Apparently NOW() and SYSDATE() are the same in 131 used documents… This function is a synonym for SYSDATE().https://riptutorial.com/mysql/example/14008/sysdate—-now—-curdate–11. Char_length() Measure the length of a string by calculating in English 1 byte and Hangul 2 bytes.SELECT CHAR_LENGTH(‘글글’);–> 4 SELECT CHAR_LENGTH(‘ABCDE’);–> 5 SELECT CHAR_LENGTH(‘ABCDE’);–> 7SELECT CHAR_LENGTH(‘글글’);–> 4 SELECT CHAR_LENGTH(‘ABCDE’);–> 5 SELECT CHAR_LENGTH(‘ABCDE’);–> 7SELECT CHAR_LENGTH(‘글글’);–> 4 SELECT CHAR_LENGTH(‘ABCDE’);–> 5 SELECT CHAR_LENGTH(‘ABCDE’);–> 7