MySQL Common date string processing function command

The functions are as follows:
Left,right string Intercept
From_unixtime Format Unix Timestamp
Concat string Join function
Max takes a column's maximum value
Min takes a column minimum value
Sum calculates a column's sum
Count Statistics Bar
MD5 returns a string of MD5 plus passwords
Format formats numbers as xx,xxx,xxx.xxxx formats such as 1,1000.123
Length evaluates a string
Distinct to repeat
Replace replacement string
In specifies a record to query for a value
Like fuzzy query
Is null query a condition is null (NULL), note: null is not equal to ""
is not NULL query for a condition NOT null (NULL)
MATCH ... Against ... MySQL full-text indexing query

MySQL left,right function
Left and right are a pair of truncated strings, the first few can be the last several of the functions, the left-hand is from left-to-right start to calculate the opposite is right-to-left calculation
Cases:
Select Left (name,10) as name from user; Displays the first 10 digits of the user name
Select Right (name,10) as name from user; Displays the latter 10 digits of the user name
SELECT * from user where Left (datetime,10) = "2011-12-02″ to take out 2011-12-02-day registered user
SELECT * from user where Left (datetime,7) = "2011-12″ Remove user registered in 20 November-December
Left,right can be used not only for intercepting strings, but also for where conditions. Especially in terms of the query he has a very large role.

MySQL from_unixtime function
The From_unixtime function is used to format the UNIX timestamp and format it into our readable date-time format.
Cases:
Select From_unixtime (Time, "%y-%m-%d%h:%i:%s") as the datetime from table; Format the Time field as an easy to read datetime display (time is a UNIX timestamp)
SELECT * FROM table where left (From_unixtime (Time, "%y-%m-%d") = ' 2011-12-02′ take out 2011-12-02th Records

MySQL concat function
The CONCAT function can be used to connect a two character to a query or display, or to connect a field to a string.
Cases:
Select Concat (Year, "-", Month, "-", day) as datetime from table; Connect the Year,month,day fields in the table to show
Select Concat ("My name was:", name) as name from table; Connect strings and fields to show
Update software set Icon=concat ("Http://111cn.net", icon); Update the icon in the database in batches and add the domain name before the original icon 111cn.net

MySQL max,min function
As the name suggests, the Max function is used to query for the maximum value in a field
Cases:
Select Max (age) from user; Return to the maximum age
Select min (age) from user; Return to the minimum age

MySQL Sum function
The SUM function can sum a character (int type)
Cases:
Select sum from user calculates the total amount of money for everyone
Select SUM (Money), region from user group by area calculates the total number of people in each area

MySQL count function
Statistical aggregate functions that can be used to count the results of SQL queries
Cases:
Select COUNT (*) as total from user calculates 111cn.net of a member of the Association

MySQL MD5 function
Encrypt a string like the MD5 in PHP
Cases:
Select MD5 (password) as password from user MD5 encrypt the password to display the plaintext
Insert into User (Name,password) VALUES ("ABC", MD5 ("abc")); Write the user table to encrypt the password after MD5 the storage

MySQL Format function
Used to format numbers in XX,XXX.XXX format
Cases:
Select Format (downloads) as download from software; Format the download to xx,xxx format such as: 12,000
Select Format (money,2) as money from user; Format user Money in xx,xxx.xx format, parameter 2 is the exact number of decimal places such as: 12,000.05

MySQL length function
Calculate the length of a field value
Cases:
Select Length (name) as length from user; Displays the length of the user name
SELECT * FROM table where length (aa) > 10; Query for records with a field length greater than 10 PHP programmer station

MySQL distinct function
Repeat for a field (at some point group by can also do)
Cases:
Select DISTINCT (area) from user; Repeat the area.
Select Area,count (*) from the user group by area; To aggregate and count the regions

MySQL Replace function
Find a string and replace it
Cases:
Select Replace (icon, "Www.111cn.net", "Img.111cn.net") from software; Replace the www.111cn.net in the icon with the img.111cn.net display
Update software set Icon=replace (icon, "Www.111cn.net", "img.111cn.net"); The domain name of the icon in the database in batches to find replacement www.111cn.net

MySQL in function
Several values can be specified in batches as query criteria
Cases:
SELECT * from user where user_id in (1,2,3,4,5,100,200,333)
SELECT * from user where user_name in ("A", "B", "D")

MySQL like function
You can make a fuzzy query on a field, and the "%" number is used to match any character
Cases:
SELECT * from user where name is like "% King%"; Query for all user names with "King" characters
SELECT * from user where name is like "% king"; Query for all user names with the first character "King"

MySQL is null function
Matches a record with a null value for a character, note: null does not represent the null string ""
Cases:
SELECT * from user where A is null; Query for a NULL user
Select a.* from user as a left join Add_user as B in a.user_id=b.user_id where b.user_id is null; Linked Table Query additional table Add_user users with no additional user information data

MySQL is not null function
Matches a record that is not NULL, as is NULL usage

MySQL MATCH ... Against full-text matching function
MySQL full-text matching function, to use this function query characters must increase the Full-text index, in addition to MySQL does not support Chinese full-text indexing, so people in the development of estimates rarely used in this function.
Match contains fields to be full-text matched, and multiple fields are separated by "," against to match strings
Cases:
SELECT * From software where match (title,body) against ("PHP"); Full-text matching records in title and body fields that contain "PHP"
SELECT * From software where match (title) against ("PHP MySQL"); Full-text matches the records in the title field that contain "PHP MySQL".

MySQL Common commands
1, the MySQL service starts and stops
Service mysqld Start
Service Mysqld Stop

2, login MySQL
The syntax is as follows: Mysql-u user name-p user Password
Type the command mysql-u root-p, after the carriage return prompts you to enter the password, for example enters 123456, then enters the car to enter into the MySQL.
The MySQL prompt is:mysql>

3. Display Database list
show databases;

There are two databases by default: MySQL and test. MySQL inventory with MySQL system and user rights information, we change the password and new users, is actually the library to operate.

4, display the data table in the library
Use MySQL; Select a database
Show tables;

5, display the structure of the data table
describe table name;

Shows which fields the data table consists of and what the data type is respectively.

6, display the records in the table
SELECT * from table name;

7, give users the right to add new users at the same time
Format: Grant permission on database. * To User name @ login host identified by "password"
For example, add a user user1 password to Password1, so that it can log on the computer, and all databases have query, insert, modify, delete permissions. First connect the root user to MySQL, and then type the following command:
Grant Select,insert,update,delete on *.* to User1@localhost identified by "Password1";
Give all permissions
Grant all on *.* to User1@localhost identified by "Password1";

If you want the user to be able to log on to MySQL on any machine, change the localhost to "%".

8. Reload Authorization Form
Flush privileges;

9, delete the user in the database
Use MySQL;
Delete from user where user= "user1" and host= "localhost";

10. Building and deleting the library
Create database library name;
drop Database library name;

11, the establishment of the table and delete table
Use library name;
CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];
The drop table table name;

For example:
CREATE TABLE class (ID int (4) Not null primary key, name char (a) not null,degree double (16,2));
When you build a table, you design a field data type, whether it's a primary key, or more.

12, add the record to the table
INSERT into class values ("0001", "Zhang", "99.8");

13, update the data in the table
Update class set degree= "100.1" where id= "0001";

14, clear the table record
Delete from table name;

15, Exit MySQL
Exit