Use Database -- Use Database
Current_date -- current date
Current_time -- current time
Show databases -- display a database
Show tables -- display table
Describe table name -- display table structure
Load data local infile file path into Table lines terminated '\ r \ n' -- import local files to table, and separate fields by Tab
And priority is greater than or
Select distinct field from table -- Unique Query
Select * from Table order by field 1 ASC, Field 2 desc -- sort by field 1 in ascending order, Field 2 in descending order
Year () year month dayofmonth hour minute second date () current year month day time () current hour minute second now () current detailed time curdate () current year month day curtimr () current hour minute second
Right (field, n) -- Return n characters on the right of the field left in turn
Return Boolean expression true return 1 false return 0 (mathematical operations such as addition and subtraction can be performed)
Is null is not null -- null is not null
Date_add (curdate (), interval 1 month) -- add a month to the current date. It can be used for all date functions and can be negative.
MoD (Bar, n) -- Return bar modulo by N
Regexp -- Regular Expression matching
Count () group by--1: only query the total number does not need GB 2: select other fields must be group by, can be plural
Joint query can be performed on the same table from Table T1, table T2 where t1.id = t2.id
Select database (); query the name of the currently used database
Show index from table -- returns the index of the table)
Max () maximum value can be group by; select * from Table B1 where field = (select max (field) from Table B2 wehre b1 = b2) query the column where the maximum value is located, minimum min () sequential
Use the user variable @ A select @ A: = min (field), @ B: = max (field) from table; select * from table where field = @ A or field = @ B
When creating a table, numeric fields: auto_increment automatically increases unsigned/unsigned (non-negative) zerofill. Fill in the front of zero and fill in the empty spaces with 0, such as 001.
×× Select year, month, bit_count (bit_or (1 <day) as days from T1 group by year, month; this query calculates the number of days in the table by year/month combination. Duplicate queries can be automatically removed. Bit_count () returns the number of occurrences of '1' in binary. bit_or () binary performs the 'or '1' <day shift operation. Or when the operation is performed on the same day, that is, the two operations on the same bit are 'or', or one '1', classic Algorithm
Last_insert_id (): The last inserted ID; the first inserted ID is returned when multiple rows are inserted; alter table auto_increment = n changes the table's auto-growth base to n; when one of the composite primary keys is auto-increment, the auto-increment primary key increases relative to the other primary key instead of the global one.
Concat (N parameters) concatenates multiple fields. If null exists in the middle, null is returned. If bit exists in the middle, bit is returned. Concat (N parameters) + 0 returns a number.
\ Escape Character \ 0 ASCII; \ '\ "\ % \ _ \ B Return character \ t Tab character \ r press enter \ n line feed
Select 0 xnnn hexadecimal NNN conversion string, convert numeric cast (0 xnnn, unsigned); hex (0x) String Conversion hexadecimal number
True, 1, false, and 0 can be automatically converted.
You can use the B 'value' symbol to write the bit field value. Value is a binary value written as 0 and 1. (B bit (8): B + 0 decimal | bin (B + 0) binary | OCT (B + 0) octal | hex (B + 0) hexadecimal
Variable definition: Set
System variables: System variables are classified into global variables and session variables. Set session [Global] Param = 'value' or set @ SESSION (@ Global ). param = 'value' if not specified, the default value is session. Query: Select @ session. param; show session variables like 'param'; For show variables, select @ session MySQL Returns the session value (if any). Otherwise, the global value is returned. Show session variables if global, session, or local is not specified, MySQL Returns the session value.
Greatest () Maximum rand () Random Number least () Minimum value
A xor B -- only one of A or B is true.
Case value/expression when result 1 then execute 1 when result 2 then execute 2 else execute 3 end similar to switch case
Case when expression 1 then result 1 when expression 2 then execution 2 else execution 3 end
Is the IF (expression, result a, and Result B) expression true? Set to result a, not set to B
Strcmp (str1, str2) compares strings, case insensitive
If ifnull (expr1, expr2) expr1 is null, nll returns expr2, not expr1
The result of dividing 1/0 by zero is null.
Nullif (expr1, expr2) -- whether expr2 and expr1 are equal. If they are equal, null is returned. Otherwise, expr1 is returned.
ASCII () -- returns the ASCII character. If it is a string, the first character is returned. The Null String is 0, and null is returned.
Bit_length () -- bit length
Char (N1, N2 ......) -- Converts a number to a character based on ASCII. N is an integer. Over 255 characters are considered to be two characters.
Length (), char_length () -- length is the byte length, char_length is the character Length
Concat_ws (separator, str1, str2 ......) Link string, separated by a separator, will be slightly null
ELT (M, S1, S2 ,...... SN) -- returns the MTH value in the S array. If the range is exceeded, null is returned.
Field (R, S1, S2 ,...... SN) -- returns the r position in the S array, starting from 1. If it is repeated, the first position is returned.
Find_in_set (STR, strlist) -- returns the position of STR in strlist. If it is repeated, the first position is returned.
Instr (STR, sustr) -- returns the position where STR appears for the first time in sustr.
Insrty (the inserted string, start position, length, and inserted string) -- inserts another string into the string
Lower () -- convert lowercase -- upper () to uppercase
Load_file () -- read an object
Locate (STR, substr) returns the location where STR first appears in substr
Locate (STR, substr, begin) returns the position where STR appears for the first time in substr starting from the position of begin. The returned value is relative to the entire substr.
Lpad (STR, Len, sub) adds sub to the left side of STR, and the length to STR is equal to Len. If the start length of STR is smaller than Len, remove the difference value from the left side of str. rpad is added to the right side. The rest are the same.
Ltrim (), rtrim (), trim () -- Remove left, right, front and back spaces respectively
Substring (STR, begin, Len)/mid (STR, begin, Len) -- truncates the string substring (STR, POS), substring (STR from POS) substring (STR, POs, len), substring (STR from POS for Len)
Repeat (STR, n); -- returns STR multiple times, n <1 returns''
Replace (STR, from_str, to_str) -- replace each from_str in STR with to_str
Reverse (STR) STR reverse arrange 'abc'-> 'CBA'
××× Substring _ index (STR, delim, count) -- returns the string after the count of delim appears. If it is a negative number, it is calculated from the right.
In addition to spaces, the *** trim can also remove characters. The format is trim (Leading/trailing/both to remove characters from string). The leading position is both by default.
CRC32 (STR) -- Return CRC32
To obtain a random integer r in the range of I ≤ r ≤ j, use the expression floor (I + rand () * (J-I + 1 )).
××××× ??? Select * from table where date_sub (curdate (), interval 1 month) <= Date (column_time); query fields in the last month
Dayname (datetime) returns the day of the week
The sequence of dayofweek () weeks is Sunday = 1 !!!! Dayofyear (date)
From_days (n) returns a date value for a given number of days. The values below 365 are 0000-00-00. You calculate to_days (date)
Last_day () returns the date of the last day of the month
Makedate (year, dayofyear) indicates the year value and the day value in the year. A date is returned. Dayofyear must be greater than 0; otherwise, the result is null.
Maketime (hour, minute, second)
Monthname (date) returns the full name of the month corresponding to date.
MySQL Period -- yyyymm or yymm period_add (p, n) period P increases by N months period_diff (P1, P2) P1, P2 difference month
Quarter (date) returns quarter: 1234 in spring, summer, autumn and winter
Sec_to_time (seconds) converts seconds to time
The week of week (date, model). The model cannot be written, but the processing is performed in the Cross-year week ...... Note parameter troubles-0 There is one Sunday in the current year 1 There are 3 days or more in the current year 2 There is one Sunday in the current year 3 There are 3 days or more in the current year 4 there are 3 days or more in the current year 5 in the current year one has Monday 6, three days or more, and one has Monday in the current year.
------------ Full-text retrieval -------------------
-- The table must be MyISAM (engine = MyISAM)
-- Create an index Fulltext (Fulltext key 'title' ('title', 'body '))
-- Select * from Table wehre match (title, body) against ('keyword') the match parameter must correspond to all index fields
-- If you query manth (), the returned results are correlated, and the results are sorted by similarity.
-- Select ID, body, match (title, body)
Against ('security implications of Running MySQL As root ') as score
From articles where match (title, body) against
('Security implications of Running MySQL As root ') order by score DESC;
Note: The where score is not supported! = 0
-- The keyword cannot be less than 4 characters (default, changeable)
-- The stopword cannot be searched by default (default, changeable)
-- If the frequency is too high, it is considered as a stop word. The query result is 0 (50%)
------- Boolean full-text retrieval ---------
-- Column: Select * from articles match (title, body) against ('+ MySQL -Yoursql 'in Boolean Mode)
-- Do not use the 50% limit
-- The correlation strength is not used. The correlation is 1, and the correlation is 0.
-- The keyword length and the default stop limit are still available.
-- '+': A required keyword
'-' Is not a keyword
''Unsigned indicates that it is dispensable, but the priority is high when it appears, similar to the similarity of common search. When an unsigned connection occurs simultaneously, at least one
'> ''<'Priority is like' + (> AAAAA <bbbbb )'. Query fields with AAAAA or bbbbb, but the priority of AAAA is high.
'~ 'Contains lower priority, which is equivalent to a weak level '-'
'*' Wildcard
'""' Phrase
-- Against (''with query expansion) extended query. For example, the query database will also find MySQL Oracle
----------------- End --------------------------
Benchmark (count, expr) repeats count execution expression expr
Charset (STR) returns the character set of the string independent variable.
Row_count () -- number of rows affected by the operation
Get_lock (name, time) create a lock; is_free_lock (name) whether there is a lock; release (name) Unlock
Is_used_lock (STR) checks whether the lock named STR is in use (in other words, blocked ). If the lock is blocked, the connection identifier of the client that uses the lock is returned. Otherwise, null is returned.
UUID () Unique ID is a 128-bit number represented by a 5-bit hexadecimal string in the format of aaaaaaaaaa-Bbbb-CCCC-dddd-eeeeeeeeeeeeee:
Select a, group_concat (B) from T group by a; Connect the queried Fields
When group by is used, with rollup is added to indicate that there are summary items at the end. And order by cannot be used at the same time
---------- Trigger --------------
Delimiter | -- defines the Terminator. |
Create trigger name before/after insert/delete/update on table
For each row
Begin
SQL statements -- old and new: insert contain only new, delete, and update.
End
| -- End
Delimiter; -- change the terminator back
----------- View --------------
Create view select * from table
------------ Stored Procedure ----------------
To create a stored procedure, you must have a proc table.
Create:
Delimiter | -- defines the Terminator. |
Create proceduce my_proc (Out param1 INT) -- In indicates input, out indicates return, and inout indicates both
Beginlabel: Begin -- block. beginlabel is an alias and is optional.
Declare a varchar (5) default 'bob'; -- Define parameters
Declare B INT;
Declare C datetime;
Select T. Cola, T. COLB, T. colc into A, B, C from table t;
If date = now () then -- condition
elseif 2> 0 then
else
end if
while 2 <2 do -- loop
end while
end beginlabel
run:
call my_proc (@ );
select @;
| -- end the Stored Procedure
delimiter; -- change the terminator back
