MySQL advanced query (i)

Modify Table name Modification table

Syntax:

ALTER table< old table name > RENAME [to] < new table name >;

Add Field

Grammar:

ALTER table name ADD field name data type [Properties];

modifying fields

Grammar:

ALTER Table name change original field name new field name data type [Properties];

Delete a field

Syntax:

ALTER table name DROP field name;

Add primary foreign key

1. Add a PRIMARY KEY constraint

Syntax:

ALTER table name ADD CONSTRAINT PRIMARY Key name PRIMARY key table name (primary key field);

2. Add a foreign KEY constraint

Grammar:

ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES associated table name (associated field);

DML statement review Insert data record

1. inserting a single row of data

Syntax: INSERT into table name [(List of field names)] Values (value lists);

2. inserting multiple rows of data

Syntax: INSERT into new Table (field list) VALUES (Value List 1), (Value List 2), ..., (value list n);

3. inserting a query structure into a new table

Syntax 1:

INSERT into new Table (Field 1, Field 2,......)

SELECT Field 1, Field 2,......

from the original table;

Syntax 2:

CREATE Table New Table (SELECT field 1, Field 2, ...). From the original table);

Update data records

Grammar:

UPDATE table name SET field 1= value 1, field 2= value 2,........ field n= value n [WHERE condition];

Delete data records

Syntax: 　　 DELETE from table name [where condition]; -------Delete a single record

Syntax:                               TRUNCATE table name; ------Delete all records

Data Query DQL statement review

Limit clause

Grammar:

List of select< field names >

from< table name or view >

[WHERE < query conditions;]

[GROUP by < Group field name;]

[Order by < sorted field name > [ASC or DESC]]

[LIMIT [position offset,] number of rows];

Common functions

Common aggregate functions:

Name of function	Role
AVG ()	Returns the average of a field
COUNT ()	Returns the number of rows in a field
MAX ()	Returns the maximum value of a field
MIN ()	Returns the minimum value of a field
SUM ()	Returns the and of a field

Commonly used String functions

Name of function	Role	Example
CONCAT (str1,str2,..., strn)	Connection string STR1,STR2,..., strn as a full string	SELECT CONCAT (' My ', ' S ', ' QL '); Back to: MySQL
INSERT (STR,POS,LEN,NEWSTR)	Starts the string str from the POS position, and the Len characters are replaced with a string newstr	SELECT INSERT (' This is SQL Server database ', 3,10, ' MySQL '); Back: This is the MySQL database
LOWER (str)	Turns all characters in the string str to lowercase
UPPER (str)	Capitalize all strings in the string str	SELECT UPPER (' MySQL '); Back to: MYSQL

Time-Date function

Name of function	Role
Curdate ()	Get Current date
Curtime ()	Get current time
Now ()	Get the current date and time
WEEK (date)	Return date is the week ordinal of a year
Year (date)	Returns the year of the date
HOUR (Time)	Returns the hour value of time
MINUTE (Time)	Returns the minute value of time
DATEDIFF (DATE1,DATE2)	Returns the number of days separated by the date parameter Date1 and Date2
Adddate (Date,n)	Calculate date parameter date plus n days of day

numeric functions

Name of function	Role
Ceil (x)	Returns the smallest integer greater than or equal to the number X
Floor (x)	Returns the largest integer less than or equal to the number X
RAND ()	Returns the random number between 0~1

MySQL advanced query (i)