MySQL Common commands and statements

1. Common SQL statements

1) Common functions
/*
The value of type is:
Microsecond
SECOND
MINUTE
HOUR
Day
WEEK
MONTH
QUARTER
Year
Second_microsecond
Minute_microsecond
Minute_second
Hour_microsecond
Hour_second
Hour_minute
Day_microsecond
Day_second
Day_minute
Day_hour
Year_month
*/

/* Returns the current time as a UNIX timestamp */
Select Unix_timestamp ();

/* Returns the current time as a UNIX timestamp */
Select Unix_timestamp (now ());

/* Returns the current time as a Unix timestamp to the normal date time */
Select From_unixtime (Unix_timestamp (now ()));

/* Returns the number of days between the date and the 0 January 1 */
Select To_days (now ());

/* Returns the date of N days after 0 years of January 1 */
Select From_days (1000);

/* Returns a DateTime string in the specified format */
Select Str_to_date (' 2013-09-15 09:00:00 ', '%y-%m-%d%h:%i:%s ');

/* returns UTC date 2016-08-23*/
SELECT utc_date ();

/* return UTC time 17:55:19*/
Select Utc_time ();

/* Returns the time number corresponding to the date 64519*/
Select Time_to_sec (Utc_time ());
Select Time_to_sec (Curtime ());
Select Time_to_sec (current_time);

/* Returns the time number corresponding to the time 01:55:19 AM 01:55:19 am*/
Select Time_format (Utc_time (), '%r ');
Select Time_format (Curtime (), '%r ');
Select Time_format (current_time, '%r ');

/* Return time 01:00:00*/
Select Sec_to_time (3600);

/* Return time 838:59:59*/
Select Sec_to_time (Unix_timestamp ());
Select Sec_to_time (Unix_timestamp (now ()));

/* Return time 17:57:26*/
Select Sec_to_time (Time_to_sec (Utc_time ()));
Select Sec_to_time (Time_to_sec (Curtime ()));
Select Sec_to_time (Time_to_sec (current_time));

/* Returns date and time after 1 days of the specified date 2016-08-25 01:58:37*/
Select Adddate (now (), 1);

/* Returns the date time after the specified date plus 1 seconds 2016-08-24 01:58:38*/
Select Adddate (Current_timestamp (), INTERVAL 1 second);

/* Returns the date time of the specified date plus 1 minutes 2016-08-24 01:59:37*/
Select Date_add (localtime (), INTERVAL 1 minute);

/* Returns date time minus 1 days after the specified date 2016-08-23 01:58:37*/
Select Subdate (Sysdate (), INTERVAL 1 day);

/* Returns the DateTime after the specified time plus 5 seconds 01:58:42*/
Select Addtime (Curtime (), 5);

/* Returns the DateTime after the specified time minus 10 seconds 01:58:27*/
Select Subtime (current_time,10);

/* Returns the number of days that specify 2 date and time differences 11*/
Select DATEDIFF (Current_date () +1,current_date ()-10);

/* Returns the date time of the specified format 2016-08-24 01:58:37*/
Select Date_format (Localtimestamp (), '%y-%m-%d%h:%i:%s ');

/* Returns the number of characters in a string */
SELECT char_length (' MySQL data function ');

/* Returns the number of bytes in the string */
SELECT LENGTH (' MySQL data function ');

Returns the position of the 1th argument string in the 2nd argument string 3
SELECT LOCATE (' sq ', ' MySQL ');

Returns the position of the 1th argument string in the 2nd argument string 3
SELECT POSITION (' s ' in ' MySQL ');

Returns the position of the 2nd argument string in the 1th argument string 3
SELECT INSTR (' mysql ', ' sq ');

Returns a position that matches the string of the 1th argument
SELECT FIELD (' My ', ' mql ', ' ysql ', ' my ', ' MySQL ', ' my ');

Returns a string that replaces the 2nd argument with the specified 3rd parameter. mysql Data statement
SELECT REPLACE (' MySQL data function ', ' function ', ' statement ');

Returns a string that specifies the length of the 3rd argument, starting from the value specified by the 2nd parameter. QL Data Letter
SELECT SUBSTRING (' MySQL data function ', 4,5);

Returns a string that specifies the length of the 3rd parameter, starting with the value specified by the 2nd parameter, sq
SELECT MID (' MySQL data function ', 3,2);

Returns the string to the left or right of the position where the specified spacer appears, the 3rd argument is an integer, or to the left, or to the right
SELECT substring_index (' [email protected]@ number @ According to function @ command ', ' @ ', 4);

/* Returns the concatenated 2 strings */
SELECT CONCAT (' MySQL ', ' data functions ');

/* Returns 2 strings connected with the @ symbol */
SELECT concat_ws (' @ ', ' MySQL ', ' data functions ');

SELECT INSERT (' MySQL data function ', 1, 4, ' very practical ');

/* Returns a 2-length string from the left side */
SELECT left (' MySQL data function ', 2);

/* Return 2-length string from right start */
SELECT right (' MySQL data function ', 2);

/* Returns the string with the 3rd argument starting from the left, with a length of 10 good MySQL data function/*
SELECT lpad (' MySQL data function ', 10, ' good ');

/* Returns the string with the 3rd argument starting from the left and right, with a length of ten MySQL data function good/*
SELECT rpad (' MySQL data function ', 10, ' good ');

/* Returns the number of strings after the flipped string lqsym/*
SELECT REVERSE (' MySQL data function ');

/* Returns string data at the specified location/*
SELECT ELT (2, ' MySQL ', ' data ', ' functions ');

/* Returns a string compared to 1 0 equal/*
Select STRCMP (' MySQL data function ', ' MySQL data function ');

/* Returns a String specifying the number of repetitions MySQL data function MySQL data function MySQL data function/*
SELECT REPEAT (' MySQL data function ', 3);

/* Return 100 spaces/*
SELECT SPACE (100);

/* Convert IP address to digital 2130706433*/
Select Inet_aton (' 127.0.0.1 ');

/* Convert a number to an IP address 127.0.0.1*/
Select Inet_ntoa (Inet_aton (' 127.0.0.1 '));

/* Returns the ASCII code for the 1th character */
Select ASCII (' myql ');

/* Returns the 2 binary of the number */
Select BIN (10);

/* Returns the 8 binary of the number */
Select HEX (10);

/* Returns the 16 binary of the number */
Select OCT (10);

/* Returns the number P1, converted from P2 to P3 */
Select CONV (10,10,2);

/* number reserved 2 decimal places */
Select FORMAT (1949001,2);

/* Encode the string with the encode encoded password as encode*/
Select ENCODE (' 123456 ', ' ENCODE ');

/* Dencode encode the string to decode the password as encode*/
Select DECODE (' 123456 ', ' encode ');

/* Returns the MD5 code of the string dad661767e9d552e4b689cd98536b8f7*/
Select MD5 (User ());

/* Returns the encoded *3f81b1f810ff5efef95211c8034bf1f0c1eab7a8*/after the encrypted string
Select PASSWORD (User ());

/* function applies To: BINARY, CHAR, DATE, DATETIME, Time, signed INTEGER, UNSIGNED integer*/

/* Convert the specified field to date type */
Select CAST (now () as date);

/* Convert the specified field to date type */
Select CONVERT (now (), date);

/* Specify the character set of the database */
Select CONVERT (' database ' USING utf8mb4);

/* Returns the specified time after the run now () 100 times */
Select BENCHMARK (100,now ());

/* View the character set of the current database */
Select CHARSET (CONVERT (' database ' USING utf8mb4);

/* Returns the version version number of the data 5.5.38-log 24*/
Select version (), connection_id ();

/* Returns the currently used database name mysql*/
Select DATABASE (), SCHEMA;

/* Returns the currently used database user [email protected] [email protected] [email protected] [email protected]% [Email protected]% * *
Select USER (), System_user (), Session_user (), Current_User (), Current_User;

/* Returns the character set sort method used by the currently used database self-growing ID value UTF8MB4 utf8mb4_general_ci 1*/
Select CHARSET (' database '), COLLATION (' database '), last_insert_id ();

/* Parameter 1 is true, return the value of parameter 1, or return the value of parameter 2 */
Select if (2=1, ' established ', ' not established ');

/* Parameter 1 is not NULL, return the value of parameter 1, or return the value of parameter 2 */
Select Ifnull (' Non-empty ', ' empty ');

/* CREATE TABLE and insert data */
CREATE TABLE Person (
ID smallint (5) unsigned auto_increment primary key not NULL comment ' primary key ID ',
Name varchar () NOT NULL comment ' username ',
Sex tinyint (2) not null comment ' sex ',/*1 male 2 female 3 Unknown */
Age tinyint (2) is not null comment ' ages ',
Phone varchar (one) Comment ' mobile number ',
Address varchar comment ' Current address ',
StatusInfo varchar Comment ' Various states: marital income, professional beliefs, hobbies, credit status, assets, etc. ',
Describes varchar (1024x768) Comment ' Current status description '
) Engine=innodb default Charset=utf8;

Insert into person (name,sex,age,phone,address,statusinfo,describes)
VALUES (1, ' land * * ', 1, 32, ' 150****9871 ', ' a city of a certain building east of a certain room ', ' unmarried, * * million/month, technical engineer, no, shopping, good, have a mortgage without car loan, have * * million deposit, * * million investment '),
(2, ' land * * ', 1, 32, ' 150****9871 ', ' a city of a certain building east of a certain room ', ' unmarried, * * million/month, technical engineer, no, shopping, good, there is no car loans, there are * * million deposits, * * million investment ');
Commit

/* Modify column name and column type */
ALTER TABLE person change address personaddress varchar (+) default ' None ';
ALTER TABLE person modify StatusInfo varchar ("n") default ' various status brief;

/* Create PRIMARY KEY index */
ALTER TABLE person add primary key (ID);

/* Create a normal index */
Create index idx_phone_00 on person (phone);
ALTER TABLE person Add index idx_phone_01 (phone);

/* Create a unique index */
Create unique index idx_phone_02 on person (phone);
ALTER TABLE person add unique index idx_phone_03 (phone);

/* Query all indexes in table */
Show index from person;

/* Delete the specified index */
Drop index idx_phone_00 on person;

2) Common commands
1) View the storage engines supported by the database
Show engines;

2) View the current storage engine for the database
Show variables like '%storage_engine% ';

3) View the SQL statement that created the table
Show CREATE TABLE comment\g;

4) View stored procedure simple information
Show CREATE PROCEDURE P1;

5) View stored procedure details
SELECT * from Mysql.proc where db= ' MySQL ';
Show procedure status where db= ' MySQL ';

6) Display table structure
DESC person;

7) See if the database supports partitioning
Show plugins;

8) Grant Permissions
Grant all privileges on * * to ' root ' @ '% ' identified by password ' *996dc2ef930ae7bc46f2fe0040a42eb7bbea1b3d ' with Grant op tion;

9) Backing Up the database
Mysqldump-u Administrator-P back up the database > Back up the file, ending with SQL
Mysqldump-u root-p persondb > Persondb_2017-04-03.sql

10) Restore the database
Mysql-u Administrator-P Restore the database < restore files to the end of SQL
Mysql-u root-p persondb <persondb_2017-04-03.sql

MySQL Common commands and statements