Summary of common operation skills of MySQL database

This article summarizes common operating techniques for MySQL database. Share with you for your reference, the details are as follows:

1. Query fields with the same name in different tables (table connection query condition artifact)

use information_schema;
select * from columns where column_name='field name';

2. Total number of query records

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE 1=1;

The total number of data rows can be obtained

SET @RowCount=found_rows();

3. Stored procedure data query paging

Predefined variables:

/*Error code*/
SET @RetCode='1';
/*Error message*/
SET @RetVal='1';
/*Return the number of rows*/
SET @RowCount='0';
/*page number*/
SET @PageCount='0';
/*Number of displays per page*/
SET @CurrentItem=0;
/*Number of displays per page*/
SET @PageSize=arg_page_size;
/*page number*/
SET @PageCurrent=arg_page_current;
SET @SqlExe='select * from table where 1=1';
Input parameters: arg_page_size int, arg_page_current int
IF(@PageCurrent IS NOT NULL && @PageSize IS NOT NULL) THEN
SET @CurrentItem = (@PageCurrent-1)*@PageSize;
SET @SqlExe=CONCAT(@SqlExe,'LIMIT ', @PageSize,' OFFSET ', @CurrentItem);
ELSE
SET @SqlExe=CONCAT(@SqlExe,' ');
END IF;
prepare stmt from @SqlExe;
execute stmt;
deallocate prepare stmt;
IF(@RowCount IS NOT NULL && @RowCount != 0) THEN
IF(@PageSize is null)then
SET @PageSize = @RowCount;
END IF;
SET @PageCount = ceiling(@RowCount/@PageSize);
ELSE
SET @RowCount = 0;
SET @PageCount = 1;
END IF;

4. String related operations

1. Intercept the string from the left

left(str, length)

Description: left (truncated field, truncated length)

example:

select left(content,200)as abstract from my_content_t

2. Intercept the string from the right

right(str, length)

Description: right (truncated field, truncated length)

example:

select right(content,200)as abstract from my_content_t

3. Intercept string

substring(str, pos)

substring(str, pos, length)

illustrate:

substring (the intercepted field, from which position the interception starts)

substring (the intercepted field, the number from which to start intercepting, and the interception length)

example:

select substring(content,5)as abstract from my_content_t
select substring(content,5,200)as abstract from my_content_t

(Note: If the number of digits is a negative number such as -5, it is the length from the last digit to the end of the string or the truncated length)

4. Intercept string by keyword

substring_index(str,delim,count)

Description: substring_index (the intercepted field, keyword, the number of times the keyword appears)

example:

select substring_index("blog.csdn.net",".",2)as abstract from my_content_t

result:

blog.csdn

(Note: If the number of times a keyword appears is a negative number such as -2, it will count backwards to the end of the string)

Function introduction:

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

The format without a len argument returns a substring from string str, starting at position pos. The format with a len argument returns a substring of length len characters from the string str, starting at position pos. The format of using FROM is standard SQL syntax. It is also possible to use a negative value for pos. If this is the case, the substring position starts at the pos character at the end of the string, not at the beginning of the string. A negative value can be used for pos in functions of the following format.

MySQL string case conversion

There are two pairs of deMySQL string case conversion functions: lower() , uppper() and lcase() , ucase()

mysql>select lower('DDD');
Result: ddd
mysql> select upper('ddd');
Result: DDD
mysql> select lcase('DDD');
Result: ddd
mysql> select ucase('ddd');
Result: DDD

Normally, I choose lower() , upper() to convert string case because this is compatible with other database functions.

5. Time acquisition related operations

declare _LastWeekStartTime date; -- start time of last week declare _LastWeekEndTime date; -- end time of last week

-- Because the system uses Sunday as the first day of the week, you need to subtract 1

set @A = date_add(curdate(), interval -1 day);

-- Calculate the date of Monday this week

set @B = subdate( @A,date_format(@A,'%w')-1);

-- Because the system uses Sunday as the first day of the week, we need to subtract 2 to get the last day of the previous week (Saturday of the previous week)

set @C = date_add(@B, interval -2 day);
set _LastWeekStartTime = ( subdate( @C,date_format( @C,'%w')-1));
set _LastWeekEndTime = ( date_add(subdate( @C,date_format( @C,'%w')-1), interval 6 day));

-- Get the current date. 1 is Sunday, 2 is Monday, and so on.

SET @nowdate=(select DAYOFWEEK(NOW()));
SET @NowD=case @nowdate
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
end;

-- Sunday time needs to be recalculated

if (DATE_FORMAT(CURDATE(),'%w')=0) then
SET @curmon=(SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')+6));
SET @cursun=(SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')));
else

-- Start time (Monday)

SET @curmon=(SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1));

-- End time (Sunday)

SET @cursun=(SELECT SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7));

6. Sorting skills

CREATE TABLE test (
id INT PRIMARY KEY not null auto_increment,
val nvarchar (50) NOT NULL
);
INSERT INTO test(val) VALUES ('0');
INSERT INTO test(val) VALUES ('1');
INSERT INTO test(val) VALUES ('2');
INSERT INTO test(val) VALUES ('3');
SELECT * from test;
-- Customize the order based on the priority of id 2 0 1 3 SELECT * FROM test ORDER BY instr('2013',id);
-- Principle example SET @ORDBYD='2013';
SELECT '2',instr(@ORDBYD,'2') as `INDEX`
union
SELECT '0',instr(@ORDBYD,'0') as `INDEX`
union
SELECT '1',instr(@ORDBYD,'1') as `INDEX`
union
SELECT '3',instr(@ORDBYD,'3') as `INDEX`;

instr function (from Baidu Encyclopedia instr function syntax)

instr( string1, string2, start_position, nth_appearance )

string1 : The source string to search in.
string2 : The string to search for in string1.
start_position : represents the position of string1 where the search starts. This parameter is optional and defaults to 1 if omitted. String indices start at 1. If this parameter is positive, the search starts from left to right. If this parameter is negative, the search starts from right to left and returns the starting index of the string to be searched in the source string.
nth_appearance : represents the nth appearance of string2 to be found. This parameter is optional. If omitted, the default value is 1. If it is a negative number, the system will report an error.

Notice:

The position index starts at 1.

If String2 is not found in String1, the instr function returns 0.

Example:

SELECT instr('syranmo','s') FROM dual; -- returns 1
SELECT instr('syranmo','ra') FROM dual; -- returns 3
SELECT instr('syran mo','at',1,2) FROM dual; -- returns 0

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.