DML and DQL in MySQL

Source: Internet
Author: User
Tags mathematical functions

DML statements in MySQL

Insert data record: Insert INSERT INTO ... values ... Multiple records can be inserted at the same time;

Update data record: Update

Delete data record: Delete/truncate

The SELECT statement in MySQL

Select syntax: query all, query parts, queries using aliases, query null values, queries using constants

WHERE clause: Floss the result of the split.

Limit clause: Limit the results of a query

Common function Categories: aggregate functions, String functions, time-date functions, mathematical functions

Subquery: Nest a query in another query.

1, simple sub-query

For example, look for students younger than "Lis Wen".

Analysis: First find out the date of birth of "Lis Wen"; Then, use the where statement to filter the birth date to a student who is older than "Lis Wen".

SELECT  from Student where birthday>(SELECTfromWHERE studentname= ' Lis Wen ');

Here, the subquery in parentheses returns Lis Wen's birthday: 1993-07-23--data of one type.

The comparison operators used by the subquery have >, =, <, >=, <=, execute the subquery first, return the value, and then execute the entire parent query, returning the final result. (a subquery after a comparison operator can only return a single value)

Subqueries can also be used with update, INSERT, and delete as part of the Where condition.

It is recommended that you specify the column names to display directly when writing query statements, rather than *, because the latter occupies a large resource and is less maintainable.

2. Use subqueries to query for eligible data across multiple tables

For example, check the logic Java course at least once for a student list that is exactly equal to 60 points.

Analysis:

(1) Query the subject form and obtain the course ID of the course;

(2) According to the course ID, the number of students who score 60 points in the result table is queried;

(3) According to, study number, query student form to obtain the student's name;

SelectStudentname fromStudentwhereStudentno=    (SelectStudentno fromresultInner JoinSubject onResult.subjectno=Subject.subjectnowhereStudentresult= -  andSubjectname='Logic Java');

3, in, and not in subqueries: You can follow a subquery that returns multiple records to detect whether a field's value exists in a range.

Now, there are multiple students in the Logic Java Course Test score of 60 points, the use of a simple subquery will appear compile error "subquery returns more than 1 row", that is, the subquery return value is not unique.

Now use the in subquery:

SelectStudentname fromStudentwhereStudentnoinch    (SelectStudentno fromResultwhereSubjectno=        (SelectSubjectno fromSubjectwhereSubjectname='Logic Java')             andStudentresult=  -);

Another pear example, check the student list (four tiers nested) for the last exam in logic Java course:

(1) Obtain the course number;

(2) The date of the latest course examination according to the course number;

(3) The student information is queried according to the course number and the last Test date;

SELECTStudentno,studentname fromStudentwhereStudentnoinch(        SelectStudentno fromResultwhereSubjectno=            (SelectSubjectno fromSubjectwhereSubjectname='Logic Java')             andExamdate=                 (Select MAX(examdate) fromResultwhereSubjectno=                    (SelectSubjectno fromSubjectwhereSubjectname='Logic Java')                  )        );

4, not in query

How do I get a list of students who have not participated in the logic Java course last exam?

SELECTStudentno,studentname fromStudentwhereStudentno not inch(        SelectStudentno fromResultwhereSubjectno=            (SelectSubjectno fromSubjectwhereSubjectname='Logic Java')             andExamdate=                 (Select MAX(examdate) fromResultwhereSubjectno=                    (SelectSubjectno fromSubjectwhereSubjectname='Logic Java'))) and gradeid= (...);

You can also continue and () to add conditions outside of the not in condition ~ ~

DML and DQL in MySQL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.