mysql commands executed on the mysql client:
1. Use the value "where" to filter and display some selected rows.
SELECT * FROM [table name] WHERE [field name] = "whatever";
Display all records with name "Bob" and phone number "3444444".
SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
2. Use like matching.
Display all records whose name starts with the letter "bob" and whose phone number is "3444444".
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
3. Query the specified records.
The first to fifth records with name starting with the letter "bob" and phone number "3444444" are displayed.
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;
4. Use regular expressions to find records.
Use regular expression binary to enforce case sensitivity. This command finds any records that begin with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a";
5. Display the selected records in ascending or descending order. The default order is ascending, and desc is used for descending
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
6. Return the only different record.
SELECT DISTINCT [column name] FROM [table name];
7. Count the number of specified columns.
SELECT COUNT(*) FROM [table name]; SELECT COUNT(NAME) FROM dt_areaconfig;
8. Count the sum of the specified column values.
SELECT SUM(*) FROM [table name];
9. Check whether the data in the table is duplicate.
Example: query TB_ dt_ area_ Are there any duplicate values in the name and code columns in the config table
SELECT * FROM tb_dt_area_config WHERE (CODE,NAME) IN(SELECT CODE,NAME FROM tb_dt_area_config GROUP BY CODE,NAME HAVING COUNT(*)>1);
10. Query the data in table A but not in table B.
Example: query the new product type data in digital_inspection_device.tb_dt_product_for_sealer table but not in digital_inspection_device.tb_product_type table
SELECT * FROM tb_dt_product_for_sealer WHERE NAME NOT IN (SELECT tb_product_type.NAME FROM tb_product_type WHERE tb_dt_product_for_sealer.NAME=tb_product_type.NAME);
11. Associated table query
11.1 concept:
In the SQL standard, the planned Join can be roughly divided into the following four types:
1. Inner join: join the records that match the join relationship of the fields in the two tables to form the join of the record set.
The inner join query uses the keyword join or cross join or inner join, and then joins the conditions between tables through on
Syntax:
SELECT field,Field 1,.. FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
2. External connection: it is divided into external left connection and external right connection.
The left join table A and B means to join all the records in table A and the fields joined in table B with the record sets formed by those records whose join fields in table A meet the join conditions. Note here that the last record set includes all the records in table A.
The results of Right Join tables A and B are the same as those of Left Join tables B and A, that is, the results of Select A.name B.name From A Left Join b on A.id=B.id and Select A.name B.name From b Right Join A on B.id=A.id are the same
The difference between internal and external connections is that the internal connection will remove all unqualified records, while the external connection will retain some of them. The difference between the outer left connection and the outer right connection is that if A is used to connect B left, all records in A will be retained in the result. At this time, there are only records in B that meet the connection conditions, while the right connection is the opposite.
The left outer join uses the keyword left join, and then connects the conditions between tables through on
Note: left join will query all the data in the left table of left join, even if there is no match in the right table
Syntax:
SELECT field,Field 1,... FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
The right outer join uses the keyword right join, and then connects the conditions between tables through on
Note: even if there is no match in the left table, all rows are returned from the right table
Syntax:
SELECT field,Field 1,.... FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
3. Full join: take out all records of the fields in the two tables that have a join relationship to form a join of the record set.
All records in the left table (TESTA table) and all records in the right table (TESTB table) are displayed. The data not in the two tables is NULL.
Syntax:
SELECT field,Field 1,.... FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name;
4. No connection: there is no connection function, and there is also the saying of self connection.
11.2 MySQL sub query:
Subquery is to nest one query in another query.
The subquery can contain ordinary select and can include any clause, such as: distinct,group by, order by,limit,join, etc
The sub query executes the inner SQL statement first, and then the outer SQL statement;
The efficiency of sub query is relatively low. It is generally recommended to use join to replace sub query;
During sub query, MySQL needs to establish a temporary table for the query results of the inner query statement, and then the outer query statement. Refer to example 3;
11.3 some parameters used in associated table query
1.USING (column_list):
Its function is to facilitate the writing of multiple correspondences of the connection. In most cases, the USING statement can be replaced by the ON statement, as shown in the following example:
a # LEFT # JOIN # b # USING (c1,c2,c3), which is equivalent to
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
2.STRAIGHT_JOIN:
By default, MySQL will first read the left table when joining tables. When this parameter is used, MySQL will first read the right table. This is a built-in optimization parameter of MySQL, which should be used under specific circumstances. For example, it has been confirmed that the number of records in the right table is small, which can greatly improve the query speed after filtering.
11.4 example of joint table query
Example 1:
SELECT COUNT(1) FROM (SELECT IFNULL(tr.id, '') AS regionId, IFNULL(tpt.id, '') AS productTypeId, IFNULL(tp.sale_area, '') AS regionName, IFNULL(tpt.name, '') AS productTypeName, COUNT(1) total FROM tb_product tp INNER JOIN `tb_product_type` tpt ON tpt.id = tp.product_type_id INNER JOIN `tb_region` tr ON tr.name = tp.sale_area WHERE tp.create_time >= startTime AND tp.create_time < endTime GROUP BY regionId, productTypeId, regionName, productTypeName) AS temp;
Examples 2-6 are cited from Joint table query of mysql database - Captain Xi Xi - blog Garden (cnblogs.com)
Example 2: query the names of all courses and the corresponding teachers
Analysis requirements: we need to use the course and teacher tables: we need to get both the course name and the teacher's name, and then look at the table structure model. We can know that course has a foreign key field, teacher_id refers to the teacher table id, so we can use the inner join to join the two tables together, and then take the field course CNAME and teacher Tname can get the data we want. The SQL statement is as follows:
SELECT cname,tname FROM teacher INNER JOIN course ON course.teacher_id = teacher.tid;
Example 3: query the names and average scores of students with an average score of more than 80
Demand analysis: we need to use the score table and shtudent table to get both student names and grades. Of course, we need to link the two tables or make sub connections, and then the average needs to be used in the demand. Then we should think of using the aggregation function avg(), However, the premise of using aggregation function is group by (the whole table is a group by default when there is no artificial grouping). Let's write the sql statement below:
First, students can be grouped through the score table before joining the table or sub connection_ ID and GPA:
select student_id,avg(num) as avg_score from score group by student_id having avg(num) >80;
Then, based on the above virtual table, through student_id splice student table, take student Sname and avg_score is enough
SELECT student.sname,k.avg_score FROM student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_score FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS k ON k.student_id = student.sid;Example 4: query the names of students who did not report teacher Li Ping's class
Demand analysis: according to the table structure, we know that we need to use the four tables of student, score, course and teacher. It is difficult to directly obtain the students who have not applied for Teacher Li Ping's courses, so we think in turn, which have applied for Teacher Li Ping's courses, and then delete them from the student table:
First of all, we can get which courses Mr. Li Ping taught. Use course and teacher to form:
select course.cid,course.cname from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "Miss Li Ping";
According to the table structure, we can know that the course table and the score table are connected by foreign keys, so we can subquery the virtual table and the score table and get the field score student_id can get the id of all students who have selected Mr. Li Ping's course, and then according to the student id_ id grouping or de duplication can get the student id without duplicate name. Choose the virtual table of Teacher Li Ping's course:
select score.student_id from score where course_id in ( select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "Miss Li Ping" ) ;
Then, make a sub connection between the above virtual table and the student table to get the name of the student who chose Mr. Li Ping's course, and then we can get the demand not in
select sname from student where sid not in ( select score.student_id from score where course_id in ( select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "Miss Li Ping" ) );
Example 5: query the names and classes of students who have failed more than two courses (including two courses)
Requirement analysis: this requirement is similar to the fourth requirement. We can first get the student IDs of all failed subjects, then group them, filter out some student IDS with the number of failed subjects greater than or equal to 2, and then take the obtained id as the query condition of the student table to get the student name. The sql statement is as follows:
SELECT
caption,
k.sname
FROM
class
INNER JOIN (
SELECT
sname,
class_id
FROM
student
WHERE
sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( student_id ) >= 2 )
) k ON k.class_id = class.cid;Example 6. Find out the class and name of students who have selected all classes of Mr. Li Ping at the same time
Demand analysis: we need classes and teachers, so all five tables need to be used. We still apply the previous method. First find the id of Teacher Li Ping through the teacher table, find the id of Teacher Li Ping's curriculum in the course table through this id, and then take this id to find the student of Teacher Li Ping's curriculum in the score table_ id, at this time, we need to talk to the student_id is grouped and more than 2 students are selected_ id, and then get the student through_ id to student_ Find the corresponding name and class in the table_ id, and then associate the generated virtual table with the class table to get the class CNAME and student Sname is the data we want. The sql statement is as follows:
SELECT
class.caption,
n.sname
FROM
class
INNER JOIN (
SELECT
class_id,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
(
SELECT
student_id,
course_id
FROM
score
WHERE
course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON course.teacher_id = teacher.tid WHERE teacher.tid = 2 )
) AS k
GROUP BY
student_id
HAVING
count( student_id ) = 2
)
) AS n ON class.cid = n.class_id ORDER BY n.sname;12. Update the data of existing tables.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
13. Delete the specified line
Delete the row with [field name] = 'whatever' in the table.
DELETE from [table name] where [field name] = 'whatever';
14. Add column to db
alter table [table name] add column [new column name] varchar (20);
15. Change column name
alter table [table name] change [old column name] [new column name] varchar (50);