MySQL common syntax commands and functions

Time:2019-11-7

Create databaseCreate database database name;
view the database show databases;

Select databaseUse database name;

Delete databaseDrop database database name;

Create tableCreate table table name (property name 1 data type, property name 2 data type…)…. );

View table structureDesc table name;

View table building languageShow create table table name;

New fields in the tableAlter table table name add (attribute name 1 data type, attribute name 2 data type…..);

Add fields in the first position of the tableAlter table table name add attribute name data type first;

Add a field after the specified fieldAlter table table name add add attribute name data type after attribute name;

Delete fields in tableAlter table table name DROP attribute name;

Delete tableDrop table table name;

Modify table nameAlter table old table name rename new table name or alter table old table name rename to new table name;

Modify the data type of fields in the tableAlter table table name modify attribute name to be modified data type to be modified;

Modify the field name in the tableAlter table table name change old property name new property name old data type;

Modify the field name and data type in the tableAlter table table name change old property name new property name new data type;

Modify the fields in the table as header fieldsAlter table table name modify attribute name data type first;

Change field 1 to order, after field 2Alter table table table name modify attribute name 1 data type after attribute name 2;

Insert data recordInsert into table name (field 1, field 2,… Or not) values (a piece of data), (a piece of data);

Insert query resultsInsert into to insert the table name (insert field 1 in the table, insert field 2 in the table,…) select (query field 1 in the table, query field 2 in the table,…) from to query the table name query statement;

Update data recordsUpdate table name set field 1 = new value, field 2 = new value……. Where query statement;

Delete data recordDelete from table name where query statement;

Query some data recordsSelect field 1, field 2 From the table to query;

Avoid duplicate queriesSelect distinct field 1, field 2 From the table to query;

Alias query resultsSelect field 1 as or do not write alias 1, field 2 as or do not write alias 2 From the table to query;

Set display format data querySelect concat (field 1, ‘prompt statement’, field 2…) From the table to query;

Where condition querySelect * from table name where field = value;

Between and keyword range querySelect * from table name where field between value 1 and value 2;

The keyword between and is no longer in rangeSelect * from table name where field not between value 1 and value 2;

Empty query with null keyword select From table name where field is not null; or selectFrom table name where field is not null;

Set query with in keywordSelect field 1, field 2….. From table name where field n in ;

Fuzzy query with like keyword select From table name where field like ‘field string%’; or selectFrom table name where field like ‘field string’; or select * from table name where not field like ‘field string’;

Sort data querySelect * from table name order by the sorted field names ASC (ascending) or desc (descending);

Multi field sortingSelect * from table name order by field 1 ASC, field 2 desc…. ;

Limited queries with limit keywordSelect * from table name where limit start number position, display the number of items;

Group data querySelect * from table name group by field;A piece of data of each group is randomly displayed. Generally speaking, the combination of grouping and statistics makes sense

Group Limited query with havingSelect * from table name group by field having condition;

Inner join onSelect * from table 1 inner join table 2 on condition;

Self joinSelect E. field as alias 1, F. field as alias 2…. From table name as e inner join table as f on condition;

From multi table connectionSelect * from table 1, table 2 Where condition;

Left outer joinSelect * from table 1 left join table 2 on condition;

Right outer joinSelect * from table 1 right join table 2 on condition;

Allow merge queries with duplicate valuesSelect * from table 1 union all table 2 on condition;

Merge query without duplicate valuesSelect * from table 1 union Table 2 on condition;

Where type subquerySelect * from table name where (field 1, field 2) = (select field 1, field 2 from table name where…) ;

Subquery of in keywordSelect * from table name where field in (select…. Query statement);

Subquery of any keywordSelect * from table name where field > = any (select…. Query statement);

Subquery of all keywordSelect * from table name where field < = all (select…. Query statement);

Subquery of exists keywordSelect * from table name where not exists (select…. Query statement);

Regexp regular expression operator select ‘chshs’ rehexp ‘c.’ ;

Counting function count();

Find the average function avg();

Summation function sum();

Take the maximum function max();

Take the minimum functionmin();

Absolute valueabs();

Take the maximum integer greater than xcell(x);

Take the largest integer less than xfloor(x);

Take the value x and round it with y as the decimalround(x,y);

Directly cut x as a decimal with y digitstruncate(x,y);

Returns a random number between 0 and 1rand();

Get the n-th group and return the random number with the same value rand(n);

Encrypt string password();

String concatenation functionConcat (string 1, string 2.) ;

Delimited string mergeConcat (separator, string 1, string 2,…) ;

Return in string STR2… String position matching STR1 in find_in_set(‘str1’,‘str2,str3.。。。。’);

Returns the first occurrence of string STR1 field(‘str1’,‘str2’,‘str3’。。。。);

Returns the position where the substring STR matches the beginning in string STR1Locate (STR, STR1); or position (STR in STR1); or instr (STR1, STR);

Returns the nth string elt(n,str1,str2.。。strn);

Intercepts a string of a specified location and lengthSubstring (STR, num, length); or mid (STR, num, length);

Replace the string STR from position x with the string STR2 for the y-character string insert(str , x ,y,str2);

Change character to lowercaseLower (STR); or lcase (STR)

Change characters to uppercaseUpper (STR); or ucase (STR)

Get string lengthlength(str);

Get character number functionchar_length(str);

Returns the leftmost x characters in the string str left(str,x);

Returns the rightmost x characters in the string str right(str,x);

Use the string pad to fill in the leftmost side of the string STR until the length is n characters lpad(str,n,pad);

Use the string pad to fill the rightmost side of the string STR, knowing that the length is n characters rpad(str,n,pad);

Remove the space to the left of string str ltrim(str);

Remove the space to the right of string str rtrim(str);

Returns the result of the string STR repeated x times repeat(str,x);

Use string B instead of all string a in string str replace(str,a,b);

Compare strings STR1 and STR2 strcmp(str1,str2);

Remove spaces at the beginning and end of string STR lines trim(str);

Returns the y-length string from position X in string str substring(str,x,y);

Get current dateCurrent(); or current UU date();

Get current timeCurrent time(); or current time();

Get current date and timeNow(); or current UU timestamp(), localtime(), or systemdate();

Get UNIX timestamp of date unix_timestamp(date);

Get date value of UNIX timestamp from_unixtime();

The return date is the week of the yearWeek (date); or week of year (time);

What day of the week is the return date in English dayname(time);

What day of the week is the return date and time (1: Sunday, 2: Monday) dayofweek();

What day of the week is the return date and time (0: Monday, 1: Tuesday) weekday();

Return to the day of the yeardayofyear(time);

Return to the day of the monthdayofmonth(time);

Returns the year of the date year(date);

Returns the hour value of time time hour(time);

Returns the minute value of time time minute(time);

Returns the month value of time timeMonth name (date); or month (time)

Value of each part in the cut-off dateExtrcat (year, month, day, hour, minute or second from time);

Calculate the number of days between date1 and date2 datediff(date1,date2);

Calculate date plus N days adddate(date,n);

Calculate date minus n days subdate(date,n);

Calculate time plus N seconds adddate(time,n);

Calculate time minus N seconds subdate(time,n);

Return database version number version();

Returns the current database name database();

Return to current user user();

Convert IP address to number inet_aton(ip);

Convert numbers to IP addresses inet_ntoa(x);

Create a lock named name with a duration of time cet_loct(name,time);

Unlock the lock named namerelease_loct(name);

Repeat the expression count timesBenchmark (count, expression);

Change x to type convert(x,type);

Set non empty constraints on fieldsCreate table table name (property name data type is not null);

Set default values for fieldsCreate table table name (default value of attribute name data type);

Set unique constraints on fieldsCreate table table name (attribute name data type unique);

Setting a unique constraint on a field does not constrain namingCreate table table name (property name 1 data type, property name 2 data type……… Constraint constraint name unique (property name 1, property name 2,…));

Set single field as primary key constraintCreate table table name (attribute name 1 data type primary key…);

Set multi field as primary key constraintCreate table table name (attribute name 1 data type, attribute name 2 data type….. Constraint constraint name primary key (attribute name 1, attribute name 2…);

Set field auto incrementCreate table table name (property name data type auto_increment………);

Set foreign key constraintsCreate table table name (attribute name 1 data type, attribute name 2 data type……. Constraint foreign key constraint name foreing key (foreign key attribute name 1) references table name (primary key attribute name 2));

Create a normal indexCreate table table name (attribute name data type, attribute name data type….. Index or key index name (can be omitted) (attribute name (length (can be omitted)) ASC or DESC);

Create a normal index on an existing tableCreate index index name on table name (attribute name (length or not write) ASC or desc or not write); or alter table table name add index or key index name (attribute name (length or not write) ASC or desc or not write);

Create unique indexCreate table table name (attribute name data type, attribute name data type….. Unique index or key index name (can be omitted) (attribute name (length (can be omitted)) ASC or DESC);

Create a unique index on an existing tableCreate unique index index name on table name (attribute name (length or not write) ASC or desc or not write); or alter table table name add unique index or key index name (attribute name (length or not write) ASC or desc or not write);

Create full text indexCreate table table name (property name data type, property name data type….. Fulltext index or key index name (can be omitted)) (property name (length (can be omitted)) ASC or DESC);

Create a full-text index on an existing tableCreate fulltext index index name on table name (attribute name (length or not write) ASC or desc or not write); or alter table table name add fulltext index or key index name (attribute name (length or not write) ASC or desc or not write);

Create multi column indexCreate table table name (attribute name data type, attribute name data type….. Index or key index name (can be omitted) (attribute name 1 (can be omitted)) ASC or DESC, attribute name 2 (can be omitted)) ASC or desc………);

Create a multi column index on an existing tableCreate index index name on table name (attribute name 1 (length (can be omitted)) ASC or DESC, attribute name 2 (length (can be omitted)) ASC or desc………); or alter table table name add index or key index name (attribute name 1 (length (can be omitted)) ASC or DESC, attribute name 2 (length (can be omitted)) ASC or desc………);;

Check whether index is usedExplain select * from table name where;

Delete indexDrop index index name on table name;

Create viewCreate view view name as query statement;

View view detailsShow table status from database name like ‘view name’, or show table status

View view definition informationShow create view view name;

View view design informationDesc view name;

View view information through system tableUse information menu schema; select * from views where table menu name = ‘view name’ g;

Delete viewDrop view view name;

Modify viewCreate or replace view name as query statement; or alter view name as query statement;

Create triggerCreate trigger trigger name before or after trigger condition (delete, insert, update) on trigger condition operation table name for each row trigger statement;

Create a trigger with multiple statementsDelete $$create trigger trigger name before or after trigger condition (delete, insert, update) on trigger condition operation table name for each row begin trigger statement 1; trigger statement 2;…; end $$delete;

View triggers show triggersG

View trigger information by viewing system tableuse information_schema; select * from triggersG

Delete trigger Drop trigger trigger name

View error messages show warnings;

View supported storage enginesShow engines; or show variables like ‘have%’;

View the default storage engine show variables like ‘storage_engine% ‘;

View the MySQL help document directory listhelp contents;

View data typeshelp data types;

Display the current dateselect curdate();

Display the current date and time select now();

Show current timeselect time(now());

Display the current date select year (now()) ;

Create stored procedureCreate procedure the name of the stored procedure (stored procedure parameters: input / output type, parameter name, parameter type) the stored procedure feature or the statement that does not write the stored procedure;

Create function procedureCreate function function name (function parameter: parameter name, parameter type) function feature or do not write function procedure statement;

View stored proceduresShow procedure status like ‘stored procedure name’ g or use information menu schema; select * from routes where specific menu name = ‘stored procedure name’ g

View function procedureShow function status like ‘function procedure’ g or use information_schema; select * from routes where specific_name = ‘function procedure name’ g

View stored procedure definition informationShow create procedure stored procedure name G

View function procedure definition informationShow craft function function name G

Delete stored procedureDrop procedure stored procedure name G

Delete functionDrop function function name G

Create a general user accountCreate user username identified by ‘password’;

Create a normal user account with permissionGrant permission parameters: select, create, drop and other on library. Table (permission range) to user name identified by ‘password’;

Change the password of rootSet password = password (“new password”);

Using root user to modify the password of ordinary user with permissionGrant permission parameters: select, create, drop and other on library. Table (permission range) to user name identified by “new password”;

Using root user to change password of common userSet password for user name = password (“new password”);

Ordinary users change their own passwordSet password = password (“new password”);

Delete normal userDrop user user name; or delete from user where user = “user name”;

Authorize ordinary usersGrant permission parameters: select, create, drop and other on library. Table (permission range) to user name;

Recall authorityRevoke permission parameters: select, create, drop and other on library. Table (permission range) from user name;

Withdraw all rightsRevoke all privileges, grant option from user name;