Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you MySQL database common operation example analysis, I believe most people do not know how, so share this article for your reference, I hope you read this article after a lot of gains, let us go to understand it!

The details are as follows:

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

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

II. Total number of query records

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE 1=1;

This gives you the total number of rows.

SET @RowCount=found_rows();

III. Storage process data query paging

Predefined variables:

/* Error code */SET @RetCode='1';/* Error prompt */SET @RetVal=' 1';/* Number of rows returned */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'; Parameters: arg_page_size int,arg_page_current intIF (@PageCurrent IS NOT NULL && @PageSize IS NOT NULL) THENSET @CurrentItem =(@PageCurrent-1)*@PageSize;SET @SqlExe=CONCAT (@SqlExe,'LIMIT ', @PageSize,' OFFSET ', @CurrentItem);ELSESET @SqlExe=CONCAT (@SqlExe,' ');END IF;prepare stmt from @SqlExe;execute stmt;deallocate prepare stmt;IF(@RowCount IS NOT NULL && @RowCount != 0) THENIF(@PageSize is null)thenSET @PageSize= @RowCount;END IF;SET @PageCount = ceiling(@RowCount/@PageSize);ELSESET @RowCount = 0;SET @PageCount = 1;END IF;

IV. 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 a string

substring(str, pos)

substring(str, pos, length)

Description:

substring(truncated field, starting from the digit)

substring(truncated field, from which digit to truncate, truncated length)

Example:

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

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

4. Intercept strings by keyword

substring_index(str,delim,count)

Description: substring_index(truncated field, keyword, number of keyword occurrences)

Example:

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

Results:

blog.csdn

(Note: If the keyword appears a negative number of times, such as-2, it is from the back 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)

Format without len parameter returns a substring from string str starting at position pos. Format with len parameter returns a substring of the same length as len character from string str, starting at position pos. The format used for FROM is standard SQL syntax. It is also possible to use a negative value for pos. In this case, the position of the substring 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

deMySQL string case conversion functions have two pairs: lower(), upper () and lcase(), ucase()

mysql>select lower ('DDD '); result: dddmysql> select upper ('ddd'); result: DDDmysql> select lcase ('DDD '); result: dddmysql> select ucase ('ddd'); result: DDD

In general, I choose lower(), upper() to convert string case because it is compatible with functions in other databases.

V. Time acquisition related operations

declare _LastWeekStartTime date; --Last week start time declare _LastWeekEndTime date; --Last week end time

--Because the system is Sunday for the first day of the week, you need to subtract 1

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

Calculate the date of Monday of the week

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

--Because the system is Sunday for the first day of the week, you need to subtract 2 to get the last day of the previous week (last Saturday)

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 day of the week 1 is Sunday 2 is Monday and so on

SET @nowdate=(select DAYOFWEEK(NOW()));SET @NowD=case @nowdatewhen 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 recalculation

if (DATE_FORMAT(CURDATE(),'%w')=0) thenSET @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));

VI. 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;--Custom sort according to priority id = 2 0 1 3 SELECT * FROM TEST ORDER BY instr ('2013 ',id);--Principle Sample SET @ORDBYD ='2013'; SELECT '2',instr(@ORDBYD,'2') as `INDEX`unionSELECT '0',instr(@ORDBYD,'0') as `INDEX`unionSELECT '1',instr(@ORDBYD,'1') as `INDEX`unionSELECT '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 in which to look.

string2: The string to be found in string1.

start_position: Represents the position of string1 from which to start searching. This parameter is optional. If omitted, it defaults to 1. String index starts at 1. If this parameter is positive, search from left to right, if this parameter is negative, search from right to left, returning the starting index of the string to be found in the source string.

nth_appearance: represents the number of times to find string2. This parameter is optional. If omitted, it defaults to 1. If it is negative, the system will report an error.

Note:

Position index numbers start with 1.

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

Examples:

SELECT instr ('syranmo ',' s') FROM dual; --returns 1SELECT instr ('syranmo ',' ra') FROM dual; --returns 3 SELECT instr ('syranmo ',' at', 1,2) FROM dual; --returns 0 Above is "Sample analysis of common MySQL database operations" All the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.