MySQL common functions and how to manipulate data

--"Composition of SQL statements"

--DML data manipulation language (inserting, modifying, and deleting data in a database) insert UPDATE Delete

--DQL Data Query Language (used to query data in the database) SELECT

--DCL Data Control language (used to control access permissions, access rights, etc.) GRANT REVOKE

--DDL Data Definition language (columns used to build databases, database objects, and definition tables) Create DROP

Use MyDB;

CREATE TABLE IF not EXISTS tb2 (id INT UNSIGNED PRIMARY KEY auto_increment,

Username VARCHAR (255) is not NULL,

Age INT DEFAULT 0,

Sex enum ("Male", "female") DEFAULT "male"

);

SHOW COLUMNS from TB2;

--Data INSERT statement Insert--insert[into] Table name (column name) VALUES (column value) [, (column value), ...];

--① If the column name is omitted, subsequent column values must be assigned to all columns, including the self-increment column and the default column.

--② If column names are not omitted, subsequent column values need to correspond to the preceding column name one by one, and all non-null columns need to be assigned the INSERT into TB2 (username,age,sex) VALUES ("Zhang San", 12, "female"); INSERT TB2 VALUES (7, "Zhang San", 12, "female"); INSERT into TB2 (username,age,sex) VALUES ("Zhang San", 12, "female"), ("Zhang San", 12, "female"), ("Zhang San", 12, "female"), ("Zhang San", 12, "female");

--Data UPDATE statement

--UPDATE table name set column name = column value [, Column name = column value,......] [WHERE condition]

The--①where condition can be omitted, but indicates that all rows in the modified table UPDATE tb2 SET sex= "male"; UPDATE tb2 SET username= "Li Xiao", age=18,sex= "woman" WHERE id=8

/*[commonly used where condition to judge]

1. Relational operations > < = = = <> >= <=

2. Logical operation not and OR

3, ISNULL (field) detection is empty SELECT * from TB2 WHERE is not ISNULL (age);

--All users with an empty age

4. Between ... And ... Between two values SELECT * from TB2 WHERE age between and 14;

--equivalent to age>=10 and age<=14

5, in data between some values SELECT * from TB2 where ID in (1,3,5,7,9);

6, like similar matching

① similar matches, use% to denote any number of characters (0 to more);

SELECT * from TB2 WHERE username like "% Zhang%"; ---"Zhang%" with "Zhang" beginning ("% Zhang", "with" end "% of"), any position including "Zhang")

② a similar match, use _ to denote the number of characters;

SELECT * from TB2 WHERE username like "_ Two"; --The second one is "two".

7, EXISTS (sub-query statement):

If the subquery >=1 rows with the returned data, exists returns TRUE, otherwise false is returned.

SELECT * FROM TB2 WHERE EXISTS (SELECT * from TB3);

--If TB3 returns at least one row of data, the condition is set

8, all (sub-query): Sub-query returned data, need to meet all, in order to establish:

SELECT * from TB1 where username!=all (select username from tb1 where id>7);

--The user name cannot be equal to the subquery returning any one of the list of user names;

9. Any (sub-query): The array returned by the subquery satisfies one of them and can be established:

SELECT * from TB1 where username =any (select username from tb1 where id>7);

--User name equals subquery returns any one of the list of user names; */

--delete data from a table

--delete [from] table name [where condition] DELETE from TB1 WHERE id>11;

SELECT * from TB2 where EXISTS (SELECT * from TB2 where username= "Li Xiao");

SELECT * FROM TB2 WHERE username like "__ Two";

SELECT * from TB2 WHERE username like "% Zhang%";

SELECT * FROM TB2 WHERE ID in (1,3,5,7,9);

SELECT * from TB2 WHERE age between and 14;

SELECT * from TB2 WHERE not ISNULL (age);

SELECT * from TB2;

--Data query statements

--SELECT column name, [column name, ...] From table name WHERE condition ORDER by sort column name Asc/desc

--①select * Indicates that all fields are queried;

--②order by means that the detected data is sorted and must be behind the where;

--ASC indicates ascending desc in descending order SELECT * from TB2 ORDER BY id DESC

--Column expression

--based on the existing columns, the query results, we use the case structure of the virtual column, called the column expression

--As keyword

--Used to alias the column name, as the keyword can also be omitted. SELECT username as ' name ', sex ' sex ', age,

--Give username the nickname "name", Sex alias "Gender"

(Case--the beginning of the judgment when sex= "male"

Then 1--if the query out sex is male, then the value of the virtual column expression is 1

When sex= "female" then 2 ELSE 3 – When none of the above is true, the value of the column expression is 3

End--to indicate the conclusion of judgment)

As Sexno--to the virtual out of this column, from the name of Sexno

From TB2;

--DISTINCT to the results after the query.

* ABS (x) returns the absolute value of X

Ceil (X) rounding up 1.5->2

Floor (X) rounding down -1.5->-2 1.5->1

ROUND (x) rounding up rounding ROUND (x,d) keeps X D decimal, rounded

TRUNCATE ' (x,d) retains X D decimal, not rounded

sign (x) x is negative returns-1 positive returns 1,0 returns 0

POW (x, y) returns the X-Power Pi () returns PI */

Select Ceil (1.5) Select ABS (-1)

SELECT * from TB1

/* Length (STR): Returns the lengths of the strings. One of the Chinese accounts for 3 of the length;

Char_length (str): Returns the number of characters in a string. One of the Chinese only takes one character;

CONCAT (str1,str2,...) stitching multiple strings

UPPER (str): Converts all strings to uppercase;

LOWER (str): Converts all strings to lowercase;

Left (Str,n): Returns the first n characters of a string;

Right (Str,n): Returns the second n characters of a string;

TRIM (str): Remove the left and right spaces on both Str

Replace (STR,S1,S2): Converts all S1 in the string str to s2

SUBSTRING (Str,pos,len): intercepts the string str, starting with a POS character, and intercepting Len. If you omit Len, the position starts at the end

INSTR (STR,S1): returns s1 subscript in Str

INSTR ("12-23", "-")-->3 subscript starting from 1

"String to number format"

Select CAST (" -123" as signed);

Select CONVERT ("123", signed);

Select "123" +0; */

Select CONCAT ("123", "456", "789");

/*[Common Date Function]

Curdate (): Returns the current date. 2016-06-20

Curtime (): Returns the current time. 09:52:52

Now (): Returns the current date and time

DATEDIFF (DATE1,DATE2): Returns the number of days between Date1 and Date2

Adddate (Datetime1,days): Returns the date of dateTime1 plus days

Year (date): Returns the years in a date

Month (date): Returns the months in a date

Day: Returns the days of the one month in a date

WEEK (Date[,mode]): Returns the day of the week 0-> Monday

HOUR (Time): Returns the number of hours

MINUTE (Time): Returns the number of minutes

SECOND (Time): Returns the number of seconds

Date_format (Date1,format): Displays date1 in a format that is required by format.

Placeholder formats available in format:

%y year, digital form, 4 digits

%y year, digital form (2 digits)

%m Month name (January: December)

%m Month, digital form (00..12)

Abbreviated name of the%b month (Jan: DEC)

%c month, digital form (0..12)

%d date of the month with English suffix (0th, 1st, 2nd, 3rd, ...)

%d date of the month, in digital form (00..31)

%e date of the month, number form (0..31)

%h hours (00..23)

%h hours (01..12)

%k hours (0..23)

%l hours (1..12)

%i minutes, digital form (00..59)

%s seconds (00..59)

%s seconds (00..59)

%w weekday name (Sunday). Saturday

%w per day of the week (0= Sunday). 6= Saturday)

*/SELECT curdate ();

SELECT

/*[System Information Function]

Version (): Takes the revision number to the current database

Database (): Returns the names of the current databases

User (): Returns the users of the current database

CHARSET (str): Character set that returns a string

LAST_INSERT_ID (): Returns the value of the last Auto_increment

*/

/*[encryption Function]

1, PASSWORD (str): The string is encrypted, often used to encrypt the password;

2, MD5 (str): MD5 hash string encryption, often used for data that do not need to decrypt;

3. ENCODE (STR,PWD): Encrypt the string str using the encrypted password pwd; The result after encryption is binary, which needs to be saved with the BLOB type.

DECODE (MISTR,PWD): Decrypts the Encode encrypted binary, decrypting the password to be consistent with the Encode encryption password. */

Select PASSWORD ("Lalalal");

/*[multi-line function]

1. SUM (): Sum SELECT sum (age) from TB2;

2, AVG (): Averaging SELECT AVG (age) from TB2;

3, Min (): To find the minimum value

4. Max (): Max value

5, COUNT (): To find the total number

SELECT COUNT (username) from TB2;

MySQL common functions and how to manipulate data