Chapter III MySQL advanced query (i) I. Four classifications for SQL language
- 1. DML(data manipulation Language): Used to insert, modify, and delete data in a table, such as insert,update,delect .
- 2. DDL(data definitionLanguage): Creating or deleting Database object operations, with Create , Drop,alter three grammatical components.
- 3. DQL (Structured query Language)(data Query Language): Used to query the language in the database, refers to the select Statement.
- 4. DCL (Data Control Language): Used to control database build access permissions, access rights, etc., such as Grant,revoke. <grant Authorization REVOKE Cancel authorization >
Two. Modify the table
MySQL is implemented using the ALTER keyword, and note that the show tables statement is used to query the database for the existence of the table before modifying it.
1. Modify the table name
Syntax: ALTER table < old table name > RENAME [to] < new table name >;
where [to] is an optional parameter and does not affect the result, only the table name is modified and the table structure is unchanged.
2. Add Fields < columns >
Syntax: ALTER table name ADD field name data type [Properties];
Example: ALTER table ' student ' Add ' password ' VARCHAR (+) not NULL; A field that adds a password to the student table.
3. Modify Fields < columns >
Syntax: ALTER table name change original field name new field name data type [Properties];
Example: ALTER TABLE ' student ' change ' password ' pwd ' CHAR (notnull); Modify the name and properties of the password field in the Student table.
4. Delete a field
Syntax: ALTER table name DROP field name;
Example: ALTER TABLE ' student ' DROP ' pwd '; Delete a password field from the student table
5. Add a PRIMARY key
Syntax: ALTER table name ADD CONSTRAINT PRIMARY Key name peimary key table name (primary key field);
Example: ALTER table ' student ' ADD CONSTRAINT ' pk_student ' PRIMARY key ' student ' (' Studentno '); Sets the number of students in the student table as the primary key column
6. Add a FOREIGN KEY constraint
Syntax: ALTER table Main Table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES Association from table name (associated field);
Example: ALTER TABLE ' student ' ADD CONSTRAINT ' Fk_student_grade ' FOREIGN KEY (' Gradeid ') REFERENCES ' Grade ' (' Gradeid '); The Grade field in the Student table and grade table is set as the primary foreign key relationship.
Three. Data manipulation (DML) statements 1. Inserting a single row of data
Syntax: INSERT into table name [(field list)] VALUES (list of values);
Considerations for inserting data:
1) The table field name is optional, and if omitted, all fields in the table will be inserted.
2) separate multiple lists and multiple values using the comma ",".
3) The value list must correspond to the field list one by one, and the value type is the same.
4) If you insert part of the data in the table, the field name list must be filled in and followed by 3.
2. Inserting multiple rows of data
Syntax: INSERT into table name [(field list)] VALUES (Value list 1), (Value List 2) ..... (Value list n);
3. Inserting query results into a new table
Syntax One: You need to create a table before inserting data by the type, order, and number of fields you insert
SELECT field 1, field 2.......INTO new table name from the original table name;
Syntax Two: You don't need to create a table first
CREATE Table New Table (SELECT field 1, Field 2 ...). From the original table);
4. Updating data records
Syntax: UPDATE table name SET field 1= value 1, field 2= value 2 ... field n= value n WHERE condition;
5. Delete data
Syntax one: DELETE from table name where condition;
Syntax two: TRUNCATE table name;
The difference is that delete conditionally deletes the data in the table, which is more accurate; Truncate is more efficient when you delete an entire table, and you can reset the self-increment column after you delete it.
Four. Data query Statement (DQL) 1. SQL Server Statement syntax
SELECT < List of field names >
From < table name or view >
WHERE Query Criteria
GROUP by< field names for grouping >
having< criteria for filtering groups >
ORDER by< sorted field name >[asc ascending/desc Descending]
2. The limit clause for MySQL
SELECT < List of field names >
From < table name or view >
WHERE Query Criteria
GROUP by< field names for grouping >
ORDER by< sorted field name >[asc ascending/desc Descending]
LIMIT position offset, the number of rows to display the data;
the offset of the position is the beginning of the first few data in the query result set, the position of the first record is 0, This parameter is optional, The default is shown from the first bar.
3. Commonly used functions 1. Aggregation functions
AVG () average; count () The number of rows in the statistics field; Max () Max; Min () min value; sum () sum
2. String functions
Function name, |
Role |
Example |
CONCAT (Str1,str2 ...) |
Concatenate strings in parentheses |
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) |
Turn string str all to lowercase |
|
UPPER (str) |
Capitalize the string str all |
|
SUBSTRING (Str,num,len) |
Returns a substring of the first num position of the string str with a length of Len |
SELECT SUBSTRING (' javamysqlorcle ', 5,5); Back to: MySQL |
3. Time-Date function
Name of function |
Role |
Example |
Curdate () |
Get the current date |
SELECT curdate (); return: 2017-09-23 |
Curtime () |
Gets the current time |
SELECT Curtime (); return: 12:25:00 |
Now () |
Gets the current date and time |
SELECT now (); return: 2017-09-23 12:25:01 |
WEEK (date) |
Return date is the week ordinal of the year |
SELECT WEEK (now ()); Returns: 26 |
Year (date) |
Returns the year of the date |
SELECT year (now ()); Returns: 20017 |
HOUR (Time) |
Returns the hour value of the event time |
SELECT HOUR (now ()); Returns: 12, returns the current time |
MINUTE (Time) |
Returns the minute value of a time |
|
DATEDIFF (DATE1,DATE2) |
return date parameter date 1 and Date2 number of days separated from each other |
SELECR DATEDIFF (now (), 2009-09-1); Returns: 2281 |
Adddate (Date,n) |
Date Calculated plus days n days after the date |
SELECT Adddate (Now (), 5); return: 2017-09-28 12:34:00 |
4. Mathematical Functions
Name of function |
Role |
Example |
CELT (x) |
Returns the smallest integer greater than or equal to X |
SELECT CELT (2.3); Returns: 3 |
Floor (x) |
Returns the smallest integer less than or equal to X |
SELECR Floor (2.3); Returns: 2 |
RAND () |
Returns the number of random numbers between 0~1 |
SELECT RAND (); Returns: 0.551111245245016 |
Five. Sub-query 1. Simple sub-query
definition: A subquery is a nested in a SELECT , INSERT , UPDATE or DELETE a query in a statement or other subquery
sub-query in where general usage in the statement:
using subqueries and comparison operators together, you must ensure that the subquery returns no more than one value
Example: Query "Java Logic" course at least one test is exactly equal to 60 students
First step: Query the course number of the Java Logic course
The second step: according to the course number to check the score is 60 students of the school number
The third step: according to the number of students to find the name
Method one: Using Table connection
SELECT ' studentname ' from ' Student ' Stu
INNER JOIN ' Result ' R on Stu. Studentno = R.studentno
INNER JOIN ' Subject ' sub on r.subjectno = Sub. Subjectno
WHERE ' studentresult ' = $ and ' subjectname ' = ' Java Logic '
GO
Implementation method Two: using sub-query
SELECT ' studentname ' from ' Student ' WHERE ' studentno ' = (
SELECT ' Studentno ' from ' Result '
INNER JOIN ' Subject ' on result.subjectno= subject.subjectno
WHERE studentresult=60 and Subjectname= ' Java Logic '
)
GO
1 In General, table joins can be replaced with subqueries, but some subqueries cannot be replaced with table joins
2 Sub-query is more flexible and convenient, often as a filter to delete and modify the criteria, suitable for manipulating a table of data
3 table joins are more suitable for viewing data from multiple tables
2.IN Sub-query
using the In keyword enables a parent query to match a subquery to return multiple field values.
Example: The query participates in the Java Logic "The course of the most recent examination on the student list
First step: Get Java Logic Course number of the program
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic '
Step Two: Query the course number to get Java Logic the last Test date of the course
SELECT MAX (examdate) from Result WHERE subjectno= (
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic ')
Step three: Check the student information according to the course number and the last Test date
SELECT Studentno, studentname from Student
WHERE Studentno in (
SELECT Studentno from Result
WHERE Subjectno in (
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic '
) and examdate = (
SELECT MAX (examdate) from Result
WHERE Subjectno = (
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic '
)
)
)
3.NOT in sub-query
Example: Querying a student's list of the most recent exams not taking the Java Logic course
SELECT Studentno, studentname from Student
WHERE Studentno not in (
SELECT Studentno from Result
WHERE Subjectno = (
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic '
) and examdate = (
SELECT MAX (examdate) from Result
WHERE Subjectno = (
SELECT Subjectno from Subject
WHERE subjectname= ' Java Logic ')))
and Gradeid = (
SELECT Gradeid from Subject WHERE subjectname = ' Java Logic '
)
Six. Summary
There are three ways to query
Union--merging rows from multiple datasets
Subquery-Nesting a query in another query
Join--merge columns from multiple data tables
the subquery after the comparison operator can only return a single numeric value .
The in subquery can follow a subquery that returns multiple records to detect whether a column's value exists in a range.
Use the EXISTS clause in a subquery to check whether a row exists in a subquery. A subquery can appear anywhere that an expression appears.
Chapter III MySQL advanced query (i)