Chapter III MySQL advanced query (i)

Chapter III MySQL advanced query (i) I. Four classifications for SQL language

1. 1. DML(data manipulation Language): Used to insert, modify, and delete data in a table, such as insert,update,delect .
2. 2. DDL(data definitionLanguage): Creating or deleting Database object operations, with Create , Drop,alter three grammatical components.
3. 3. DQL (Structured query Language)(data Query Language): Used to query the language in the database, refers to the select Statement.
4. 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)