Chapter III : advanced Query (-)
3.1: Modifying a table
3.1.1: Modifying a table
Syntax :
Alter table < old table name > Rename [to] < new table name >;
example : Alter table ' demo01 ' rename ' demo02 ';
3.1.2: Adding fields
Syntax :
Alter table name ADD field name data type [ properties ];
example : Alter Table ' Demo02 ' ADD ' passWord ' varchar (not NULL);
3.1.3: Modifying Fields
Syntax :
Alter table name change original field name new field name data type [ properties ];
% Modified field name %
Alter Table ' Demo02 ' change ' name ' username ' char (ten) not NULL;
3.1.4 Delete a field
Deleting a field removes a field from the table in the data table :
syntax : ALTER table name DROP field name ;
3.1.5 Adding a primary foreign key
Add a PRIMARY KEY constraint
syntax : ALTER table name ADD CONSTRAINT primary Key name PRIMARY key table name ( primary key field );
Add a FOREIGN KEY constraint
syntax : ALTER table name ADD CONSTRAINT foreign key name FOREIGN key ( foreign key field ) REFERENCES The associated table name ( Association field );
3.2 DML statement review
3.2.1 inserting data records
Inserting a single row of data
syntax : INSERT into table name [( field List )] VALUES ( List of values );
Inserting multiple rows of data
syntax : INSERT into new table ( field list ) VALUES ( Value list 1), ( Value List 2), ..., ( Value list n);
Note:in the YesINSERTstatement when you insert a record,If the field name is not included, VALUESThe order of the fields in the value list must be the same as the order of the fields in the table definition,If the table structure changes(If you performed the Add data operation) the list of values also needs to change,Otherwise there will be an error.If an inserted field name is specified,would have avoided the problem.,so,It is recommended that you specify a specific field name when inserting data.
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 );
3.2.2 updating data records
syntax : UPDATE table name SET field 1= value 1, field 2= Value 2,........ Field n= value N [WHERE condition ];
3.2.3 deleting data records
syntax : DELETE from table name [WHERE condition ];
syntax : TRUNCATE table name ;
3.3 data query
3.3.1 DQL Statement Review
data query statements in SQL Server :
syntax : select< List of field names >
from< table name or view >
[WHERE < query conditions ;]
[GROUP BY < Group field name ;]
[Having < conditions ;]
[ORDER BY < sorted field name > [ASC or DESC]];
3.3.2 LIMIT clause
syntax : select< List of 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 ];
of which : 1. position offset refers to starting from the first few data in the result set ( The position offset of the first record is 0, and section 2 is 1,....), and is displayed when omitted from the first record .
Row value shows the number of records in the record
3.3.3 Common Functions
Common aggregate functions :
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 |
Name of function |
Role |
Commonly used String functions
Name of function |
Role |
Example |
CONCAT (str1,str2,..., strn) |
Connection string Span style= "Font-family:calibri" >str1,str2,..., strn |
Select CONCAT (' My ', ' S ', ' QL '); Return :mysql |
INSERT (str,pos,len,newstr) |
String Span style= "Font-family:calibri" >str from pos position start ,len characters long substring replaced with string newstr |
SELECT INSERT (' This is sql SERVER database ', 3,10, ' MySQL '); Return : This is 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 |
SUBSTRING (Str,num,len) |
Returns a substring of the first num position of the string str with a length of Len |
|
3. time-Date function
Name of function |
Role |
Example |
Curdate () |
Get Current date |
SELECT curdate (); |
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
Function name |
Effect |
example |
Ceil (x) |
" Returns the smallest integer greater than or equal to the value x |
SELECT Ceil (2.3) Return :3 |
Floor (x) |
Returns less than or equal to the value x |
|
RAND () |
return 0~1 |
|
3.4 Sub-query
3.4.1 Simple subquery
syntax : SELECT ... From table 1 WHERE field 1 comparison operator ( subquery );
Use As keyword , ANSI compliant
The alias of the SELECT field List from the table name as table ;
a simple way to use spaces
The alias of the SELECT field list from the table name table ;
3.5 in and not in subqueries
3.5.1 in sub-query
working with sub-queries when multiple records are returned for a result , the = of the condition is changed to In
The in subquery can follow a subquery that returns multiple records to detect whether a field's value exists in a range .
3.5.2 Not in sub-query
not in other data within the range of the subquery .
MYSQL_ Chapter III