MySQL DML (SELECT DELETE INSERT UPDATE)

DML: Data Manipulation language
INSERT
DELETE
SELECT
UPDATE

SELECT:
SELECT Select-list from tbname| tbnames| SELECT ACTION Qualification

Select-list Search List
DISTINCT, the same value is displayed only once.
As Alias Field alias
* All content
Field fields Name
Aggregate calculation functions:
AVG (FIELD) average
Min (FIELD) Minimum value
Max (FIELD) maximum value
SUM (FIELD) sum
Number of Count (FIELD)


FROM clause: The relationship to query, which can be a single table, multiple tables, or other SELECT statements.

Qualification search code, search for keywords.

ACTION specifies the qualification or operation for the search code:
WHERE clause: An expression that specifies a Boolean relationship.
Arithmetic operations: + 、-、 *,/,% (take surplus)
Comparison operations: =, >, <, >=, <=, <> (Not equal),! = (not equal to), <=> (nullsafe null security equals, even null for safe equality comparisons)
Logical operations: and (&&), or (| | ), NOT (! , XOR (XOR)
Special operation:
Between between 2 people
Like ' pattern ' comparison operation, pattern-compliant.
% any character of any length
_ Any single character
regexp| Rlike ' PATTERN ' supports the like of regular expressions, note that using this is not an index
[NOT] In (list) is consistent with the
is [not] null and NULL comparison

ORDER by FIELDNAME [asc| DESC] Sorts the results of the query according to a field, which is the ascending (ASC) arrangement by default.

LIMIT [Offset,] count displays only count results, or starting with offset (offsets), showing
Conut a result.

GROUP by qualification
According to the search code to group, the main purpose is to seek the aggregation function.
Having qualification filtering, which is used to filter the results of group by again. Can only be used in conjunction with GROUP by.

Query execution Order:
--------------------------

Single-Table query:
SELECT * from Tbname Show all content
SELECT field1,field2,... From Tbname projection
SELECT * FROM Tbname WHERE Qualification Select

Multi-Table query:
Connection relationships for tables:
Cross join: Cartesian product
Natural joins: establishing equivalent relationships in a table field

Internal connection:
TBNAME1 [NEER] JOIN TBNAME2 on qualification
There is at least one match in two tables, which returns the row.

External connection:
Left outer connection: TBNAME1 ieft JOIN TBNAME2 on qualification
Returns the row specified in the table on the left, regardless of whether there is a match in the right table.

Right outer join: TBNAME1
TBNAME2 on qualification returns the row specified in the table on the right, regardless of whether there is a match in the left table.

Fully connected: TBNAME1 full join TBNAME2 on qualification
In two tables, the corresponding row is returned as long as there is a match.


Self-Connection: Connect yourself to yourself and implement it through as alias.

subquery (nested query):
Comparison operation: only single-line values can be returned
Using in ()
Use in from

Federated query:
UNION:
Merges the result set of multiple SELECT statements.

Note: The data type of the field must be the same.

By default, only different values are displayed, and if you have duplicate values you want to show all using UNION ALL.
(SELECT statement 1) UNION (SELECT statement 2)
Or
(SELECT statement 1) UNION All (SELECT statement 2)



Instance:
1. Display all the contents of the Courese table in the test library:
#mysql-uroot-p
Mysql>use test;
Mysql>select * from courses;

2. Use the projection mode to display the name and age of the student table:
Mysql>select * from Studnets;
Mysql>select name,age from students;

3. Use the selection method to display the student's table in the age of more than 20
Mysql>select * FROM students WHERE age>=20;

4. Comprehensive use of projections and selection. Displays the name and age of the male in the students table.
Mysql>select name,age from students WHERE gender= ' M ';

5. Display the gender in the students table:
Mysql>select disinct Gender from students;

6. Display the name and age of age +1 greater than 20 in students:
Note Such direct use may not work with the index.
Mysql>select name,age from students WHERE age+1>20;

7. Show males older than 20 in students:
Mysql>select name,age from students WHERE age>20 and gender= ' M ';

8. Show that the age of students is not more than 20 and gender is not male:
Mysql>select Name,age,gender from students WHERE not (age>20 OR gender= ' M ');

9. Show students aged between 20-25 in students:
Mysql>select Name,age,gender from students WHERE age>=20 and age<=25;
Msyql>select Name,age,gender from students WHERE age between and 25;

10. Display the name of the students beginning with Y:
Mysql>select name,age from students WHERE Name is like ' Y% ';

11. Displays the name of the students beginning with Y, with a total length of 5 characters:
Mysql>select name,age from students WHERE Name is like ' y____ ';

12. Display the name of students with ING:
Mysql>select name,age from students WHERE Name is like '%ing% ';

13. Display the names in students beginning with M or N or y:
Mysql>select name,age from students WHERE Name rlike ' ^[mny].* ';

14. Show the age of 18 20 25 in students:
Mysql>select Name,age from Students WHERE age in (18,20,25);

15. Displays the name of the students in CID2 empty:
Mysql>select Name from students WHERE CID2 is NULL;

15. Display the user's name and sort:
Mysql>select name from students ORDER by Name;

16. Aliases Display:
Mysql>select Name as studname from students;

17. Show only the first 3 results:
Mysql>select Name from Students LIMIT 3;

18. Starting from the 2nd result, a total of 3 displays:
Mysql>select Name from Students LIMIT 2, 3;

19. Aggregation Functions:
Mysql>selecet avg (age) from sutdents WHERE gender= ' M ' male classmate average
Mysql>selecet COUNT (age) from sutdents; The number of students ' age

20. The average age is calculated according to the Gender subgroup:
Mysql>select AVG (age) from students GROUP by Gender;

21. The number of elective courses for students according to CID:
Mysql>select COUNT (CID) as persons,cid from students GROUP by CID;

22. Show the number of elective courses more than 2:
Mysql>select COUNT (CID) as persons,cid from students GROUP by CID have persons>=2;

23. Multi-table query (cross-connect):
Mysql>select * from Students,courses;

24. Multi-table query (natural connection):
Suppose there are students tables and courses tables, query the student name and the corresponding course name from 2 tables:
Mysql>select students. Name,courses. Cname from Sutdents,course WHERE sutdents. Cid=courses. CID;

25. Use aliases:
Mysql>select s.name,c.cname FORM studnets as s,courses as C WHERE s.cid=c.cid;


26. Multi-table query (external connection) shows the class selected by the classmate, with the name showing the name, not shown as empty. CID1 is the first course of choice.
Mysql>select S.name,c.cname from students as s left joins courses as C on S.cid1=c.cid

27. Answer the question, show the class corresponding to the name of the students, there is a name display name, not shown as empty.
Mysql>select S.name,c.cname from students as S right joincourses as C on S.cid1=c.cid;

28. Self-linking, assuming that there is a TID in the students table, indicating the tutor number of the course, the tutor number is SID. Show student name and corresponding tutor name
Mysql>select S.name as stu,c.name as teacher from students as s,students as C WHERE s.sid=c.sid;

29. Show students who are older than the average age in the table
Mysql>select Name from Students WHERE age> (SELECT AVG (age) from students);

30. Show the students of the same age as the teacher
Mysql>select Name from Students WHERE-in (SELECT-age from Tutor);

31. Display the name and age of all students and show age greater than or equal to 20
Mysql>select name,age from (SELECT name,age from students) as T WHERE t.age >=20;

32. Merge the name and age queried in the students table and the tutor table
Mysql> (select Name,age from students) UNION (select Tname,age from tutor);

33. Displays the name of the course in the courses table that does not appear in the students table in CID2:
Mysql>select Cname from Courses The where CID not in (SELECT DISTINCT CID2 from students WHERE CID2 are not NULL);

34. Non-substitute teacher's name displayed in the Courses table, CID course number CNAME course name and TID teacher number, tutors table for teacher name Tname and Tid
Mysql>select tname from Tutors whrer tid No in (SELECT DISTINCT TID from courses);

35. Display the name of the course CID1 2 or more than 2 students in the students table
Mysql>select Cname from Courses whrer CID in (SELECT CID1 from students GROUP by CID1 have COUNT (CID1) >=2);

36. Show the course of each teacher and his professor, the course without the professor remains null
Mysql>select T.tname,c.cname from tutors as T left joins courses as C on T.tid=c.tid;

37. Show each course and its associated teacher, no teacher taught the course to show his teacher empty
Mysql>select T.tname,c.cname from tutors as T right joins courses as C on T.tid=c.tid;

38. Show each student's CID1 course and teacher's name
Mysql>select name,cname,tname from Stutdents,courses,tutors WHERE students. Cid=courses. CID and courses. Tid=tutors. TID

39. Display the last line of data in tutors:
Mysql>select * from Tutors ORDER by TID DESC LIMIT 1;

INSERT:

INSERT into Tbname (col1,col2,...) VALUES (Val1,val
2 ...) [, (VAL3,VAL4),...] can insert multiple lines

INSERT into Tbname SET col1=val1,...; Insert a row

INSERT into Tbname (COL1,...) SELECT statment Inserts the contents of the search, noting that the format is exactly the same.


Replace into is similar to insert and provides replacement functionality.


Type of data inserted:
Character type: Single quotation mark
Numeric type:
Date-Time Type
Null value: null
Empty string: '

Instance:
1. Insert the new data into the tutors table:
Mysql>insert into Tutors (tname,gerder,age) VALUES (Tom, ' M ', +), (Jerry, ' F ', 33);

2. Insert a single row of data into the Totors table:
Mysql>insert into tutors SET tname= ' Mike ', gender= ' F ', age=22;

3. Insert the Tutors table with an age greater than 20 in the students table:
Mysql>insert into Tutors (tname,gernder,age) SELECT nname,gender,age FORM students WHERE age>20;


DELETE:
DELETE from Tbname1,tbname2,... [where]| [ORDER by] | [LIMIT] Delete table

TRUNCATE Tbname clears the table and resets the auto_increment counter.

Instance:
1. Delete the students table
Mysql>delete from students;

2. Delete rows in the students table that are not older than 18:
Msyql>delete from students WHERE age<=18;

3. Display and empty the students table and reset the counter

Mysql>show TABLE STATUS like ' students ' \g;
Mysql>show VARIABLES like '%last_insert_id%
Mysql>truncate students;


UPDATE
UPDATE tbnmae SET Col=value [where]| [ORDER by] | [LIMIT]

Instance:
1. Modify the age and gender of Tom in the students table:
Mysql>updtae students SET age=30,gernder= ' F ' WHERE name= ' Tom ';

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1680849

MySQL DML (SELECT DELETE INSERT UPDATE)