“This is the 15th day of my participation in the First Challenge 2022. For details: First Challenge 2022”

Mysql > select * from rownum

Mysql > select rownum from rownum (); rownum (); rownum ();

SELECT
	(@rownum := @rownum + 1) AS rownum,
	USER.* 
FROM
	USER,
	( SELECT @rownum := 0 ) r;
Copy the code

Effect:Because it is implemented through the curve save way, so it is certainly not as efficient as Oracle’s own implementation; I hope in the future update, Mysql can own this function, here is just a way to implement, not considering the efficiency, just can be used, if I have time in the future, I can implement in a variety of ways, compare the efficiency of each implementation.

SELECT
	(@rownum := @rownum + 2) AS rownum,
	USER.* 
FROM
	USER,
	( SELECT @rownum := 1 ) r;
Copy the code

Effect:An explanation and use of Oracle rownum can be found in this article:Blog.csdn.net/weter_drop/…

Mysql > determine whether a table exists SQL statement and method

There are five ways to judge:

-- In the middle of the percent sign is the name of the table to be queried
 SHOW TABLES LIKE ‘%user%";Copy the code

Dbname indicates the name of the database. Tablename indicates the name of the table to be queried. Note that TEMPORARY tables created using the TEMPORARY keyword cannot be queried.

select TABLE_NAME from INFORMATION_SCHEMA.TABLES whereTABLE_SCHEMA='the dbname'and TABLE_NAME='tablename;Copy the code

  1. If a table does not exist, it can be created using a directive like create Table if not exists tablename without checking whether the table exists first.

  2. Create table if not exists like old_table_name;

  3. Select * from tablename; An error is reported if the table does not exist.

3. Limit = limit

Application scenario: If the data to be displayed is incomplete on one page, you need to submit SQL requests on separate pages

Grammar:

	selectQuery listfromThe table"join type join 表2
	onJoin conditionwherefiltergroup byThe grouping fieldhavingFiltering after groupingorder bySelect * from 'limit';offset】 the size;offsetTo display the entry's starting index (starting index from0Size indicates the number of entries on a page, size indicates the number of entries on a page, size indicates the number of entries on a pageselectQuery listfromThe table limit (page- 1)*size,size;
	
	size=10
	page  
	1	0
	2  	10
	3	20
Copy the code

* /

Case 1: Query the information of the first five employees (if the query starts from zero, the offset parameter can be omitted)

SELECT * FROM  employees LIMIT 0.5;
SELECT * FROM  employees LIMIT 5;
Copy the code

Case 2: Query articles 11-25

SELECT * FROM  employees LIMIT 10.15;
Copy the code