MySQL some little knowledge

1. About multi-table queries

I understand: Since MySQL multi-table query when the connection between the table is a Cartesian product, so as little as possible to use a multi-table query, if used to use nested statements

Example: Description: ' tb_notice_message ' table number millions above, alias is WM table 1000 or so

1 SELECT COUNT(1) asTotalnum2  from' Tb_notice_message ' nm, (SELECTU. ' UserID ' from' Xiaoxin '. ' Tb_ios_whitemobile ' mINNER JOIN' User_center '. ' Tb_user ' u onM. ' UserID '=U. ' UserID 'WHEREU. 'Identity`= 1) asWM3 WHERENm. ' Deleted '= 0  andNm. ' State '= 1  andNm. ' Sender '<>Wm. ' UserID ' andNm. ' Sid '= 4001  andNm.sendtime>= '2014-10-22'  andNm.sendtime< '2014-11-1'  andNm. ' Noticetype '= 1

View Code

Results: No query results within half an hour (using inner join, also not), do not know the efficiency of <> query, but also please the Great God Advice

Do not use multiple table queries, use nested

1 SELECT COUNT(1) asTotalnum2  from' Tb_notice_message ' nm3 WHERENm. ' Deleted '= 0  andNm. ' State '= 1  andNm. ' Sender ' not inch(SELECTU. ' UserID ' from' Xiaoxin '. ' Tb_ios_whitemobile ' mINNER JOIN' User_center '. ' Tb_user ' u onM. ' UserID '=U. ' UserID 'WHEREU. 'Identity`= 1)

View Code

Result: Query results are generally within 10 seconds

2. How to insert multiple data at once (as many people know here), and SQL Server, and Oracle Compare

1Mysql:INSERT  into Table VALUES(Col1,col2 ...), (Col1,col2 ...) ......2SQL Server:INSERT  into Table VALUES SELECT "',"',"' UNION  All SELECT "',"',"'.......3Oracle:INSERT  into Table SELECT '1'  fromDUALUNION  All SELECT '2'  fromDUAL

3. Update MySQL is not allowed to use the update SELECT statement instead of using other statements

1 update a inner join (select yy from B) C on a.id =b.id set a.xx = c.yy

Cases:

1 UPDATE' Tb_user ' uINNER JOIN2  (3 SELECTse. ' StudentID ', Gg. ' Guardian '4  from' Tb_user ' UUINNER JOIN' Tb_guardian ' GG onUU. ' UserID '=Gg. ' Guardian 'JOIN' Tb_student_ext ' SE onSe. ' userid '=Gg. ' Child 'WHEREUU. ' Account '= "'  andUU. 'Identity`= 35) asb6   onU. ' UserID '=B. ' Guardian 'SETU. ' Account '=B. ' StudentID '7 WHERE8U. ' Account '= "'  andU. 'Identity`= 3 

4. mysql Remove tab and space

1 Update Set = Trim (name); 2 Update Set =Trim (replace"));

Cases:

1 SELECTSe. ' StudentID ' as 'School Number',REPLACE(REPLACE(U. ' Name ','"',"'),'    ',"') as 'name',REPLACE(REPLACE(S. ' Name ','N',"'),'    ',"') as 'School', a. ' Name ' as 'Area'2  from3' Tb_student_ext ' SEINNER JOIN' Tb_user ' u onU. ' UserID '=se. ' userid '4  INNER JOIN' Tb_school ' s onSe. ' Sid '=S. ' Sid '5  INNER JOIN' Tb_area ' A onA. ' Aid '=S. ' Aid '6 WHERE7U. ' Deleted '= 0  andS. ' Deleted '= 0  and8U. ' State '= 0  and    --Not activated9A.aid=  -

5. Group_concat keyword (query for all values in a field, split with commas)

1 SELECT group_concat (' ClassId ') cids from ' Tb_xa_class '

Query Result:

2804,2904,3004,3104,3204,3206.....

6. insert or update use single quotation marks (if the field is a string type)

Cases:

1StringBuilder SB=new StringBuilder ();2Sb. Append (@ "INSERT  intoTb_school (name, aid, Stid, State, Xaschoolid)VALUES ");3foreach (varTinchsqlist)4                 {5Sb. AppendFormat ("('{0}',{1},'{2}',{3},'{4}'),",6 T.name, T.aid, T.stid, T.state, t.xaschoolid);7                 }8String sql=Sb. ToString (). TrimEnd (',');

7. How stored procedure variables are assigned

1 DECLARECntINT DEFAULT 0;2 Way One:3 Select Count(*) intoCnt fromtest_tbl;4 Way Two:5 Set @cnt =(Select Count(*) fromtest_tbl);6 Way Three:7 Select Count(*) into @cnt1  fromtest_tbl;8 mode four:9 SELECT @Num:= COUNT(*) from' Tb_class_student_relation ';

View Code

8. Function CONCAT and concat_ws for stitching strings

Concat:
CONCAT (STR1,STR2,...)

returns the string that results from the connection parameter. If any one of the arguments is NULL, the return value is null. If all parameters are non-binary strings, the result is a non-binary string. If the argument contains any twos binary string, the result is a binary string. A numeric parameter is converted to the equivalent binary string format; To avoid this, an explicit type cast is used, for example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col).

CONCAT_WS:

Concat_ws (SEPARATOR,STR1,STR2,...)

Concat_ws () represents CONCAT with Separator, which is a special form of the CONCAT (). The first parameter is the delimiter for the other parameter. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string, or it can be another parameter. If the delimiter is null, the result is null. The function ignores NULL values after any delimiter parameters.

Note:Concat_ws () does not ignore any empty strings. (All NULL is ignored, however) .

9. mysql scheduled backup

Profile: http://www.pc6.com/infoview/Article_53398.html

Ten. MySQL API

Download

MySQL some little knowledge