Examples of MySQL extensions

Extension examples

Insert a record

INSERT into table name [(Field 1,..., field N)] VALUES (value 1,..., value N)

Insert the results of a query

INSERT into table name (Field 1,..., field N) VALUES subquery

Example: INSERT into Scores VALUES ("990301", "University computer Fundamentals", 98)

DELETE from table [WHERE condition]

NOTE: the WHERE clause defaults to delete all records in the table (the table is still in)

For example:: DELETE from Scores WHERE score <70

UPDATE table SET Field 1 = Expression 1, ..., field n= expression n [WHERE condition]

NOTE: the WHERE clause defaults to modify all records in the table

Example students the name of Wang Tao, a middle school student, to Bao Pao ball

UPDATE Students SET name = "Vang Pao" WHERE name = "Wang Tao"

Example of a student who grants less than 200 in table students plus 30 yuan

UPDATE Students SET bursary = bursary +30 WHERE bursary <200

UPDATE statement can only modify one table at a time

Select a field:

Example 7.10 Query all majors, no duplicate records in query results

SELECT DISTINCT Professional from Students;

Example 7.11 using the aggregate function, check the number of students, minimum grants, maximum grants and average grants

SELECT Count (*) as number, min (bursary) as minimum bursary, max (bursary) as maximum Grant, AVG (bursary) as average grant

From Students

Example 7.12 querying the student's name and age

SELECT name, year (Date ())-year (birth date) as age from Students

Select Record:

Example 7.13 shows the student number, name and age of all non-computer majors

Select study number, name, year (Date ())-year (birth date) as age from Students WHERE professional <> "Computer"

Example 7.14 the name and date of birth of a woman born before 1981 years (including 1981).

SELECT name, date of birth from Students WHERE Birth date < #1/1/1982# and sex = "female"

Sort:

Example 7.15 inquires the student number and name of all the party members, and according to the financial aid from small to large order.

SELECT student number, name from Students WHERE Party member =true ORDER by Grant

You can sort by more than one keyword. For example, ORDER by professional ASC, bursary DESC

ASC: Indicates ascending, DESC: Indicates descending order.

Group query:

Example 7.16 Query the number of students per professional. (Simple grouping)

SELECT Professional, COUNT (*) as number from students GROUP by professional;

Example 7.17 enquiries on average scholarships for male and female students in various professions (complex subgroup)

SELECT Professional, Gender, AVG (bursary) as average bursary from Students GROUP by professional, gender

Keep Records of the same professional and gender in one group.

Example 7.18 for the scores table, there are 2 courses for students with a score of 75 or more and the number of courses. Post-grouping filtering

SELECT study number, COUNT (*) As course number from Scores WHERE score >=75 GROUP by study number

Having Count (*) >=2

Connection query:

Example 7.19 query all student's number, name, course and score

SELECT Students, Students. Name, Scores. Course, Scores. Score from Students, Scores

WHERE Students = Scores. School Number

Automatically generate statements:

SELECT Students, Students. Name, Scores. Course, Scores. Score from Students INNER JOIN Scores on Students. Study number = Scores.

Example 7.20 inquires the student's number, name and result of the "advanced mathematics" course.

SELECT Students, Students. Name, Scores. Score from Students, Scores

WHERE Students. =scores. School number and Scores. Course = "Advanced mathematics"

The above statement can be rewritten as:

SELECT Students, Students. Name, Scores. Score from Students INNER JOIN Scores

On Students = Scores. School Number WHERE Scores. Course = "Advanced mathematics"

nested queries: in SQL, a SELECT statement query block is nested in the WHERE clause of another SELECT statement or in a HAVING clause called a nested query

Example 7.21 Query the student's number, name, and profession who have not studied the foundation of the university computer

SELECT Students, Students. Name, Students. Professional from Students

WHERE Students. Study No in

(select Scores. number from Scores WHERE Scores. Course = "University Computer Foundation")

Example 7.22 query with "Deng Jinmei" student's number and name in the same profession

SELECT Students, Students. Name from Students WHERE Professional in

(select Students. Professional from Students WHERE Students. Name = "Deng Jinmei")

Examples of MySQL extensions