MySQL Basics 2

Empty a table, self-increment ID starting from 1
TRUNCATE TABLE name;

Inquire
Select Column name from table name where condition order by column name [DESC|ASC] Limit skips the number of bars

As Alias
Column name as new column name note As can be omitted without writing
Null value query
The SELECT * FROM table1 where field is null;
Combining columns
Select Concat (' No. ', id) from Stu;
To repeat
Select DISTINCT column name from table name; Note: column names can only be followed by one
Sort
SELECT * FROM table name order BY column name 1 ASC, column name 2 desc;
Sorting of information after processing
Select Concat (' No. ', id) from Stu Order by Concat (' No. ', id);
Common functions
Select Concat (' My ', ' S ', ' QL '); connection
Select lower (' PHP '); turn lowercase
Upper () Turn capital
Select replace (' www.mysql.com ', ' www ', ' http://www '); Replace
Update stu Set name = replace (replace (name, ' o ', ' 0 '), ' I ', ' 1 '); Change all o in the name field to 0, all I to 1

Trim ()
LTrim ()
RTrim ()
Select ABS (-5) absolute value
Select Adddate (' 2013-02-28 ', 1); 2013-03-01
Select Now ();

Wildcard "%" any "_" single
SELECT * from Stu where name is like ' Zhang _ ';

Regular does not support \w ...
SELECT * from Stu where name RegExp ' ^a[a-z][a-z]$ ';

Interval operation [NOT] between ...
SELECT * from Stu where age >=18 and <=19;
SELECT * from Stu where age between and 19;
SELECT * from Stu where age not between and 19;

[NOT] in (value 1, value 2,...)

Sum sum
AVG Avg
COUNT (*) counts
The parentheses can be either * or column names
If the column name is in parentheses, the column data is not counted as a null value.
Max Maximum Value
Select Max (age) from Stu;
Min min value

* Group Query
How many men and women are there in a sentence?
Select Sex,count (*) from Stu Group by sex; #分组后统计
Note: Fields can only be fields used for grouping (field after group by) or aggregate functions

Group statistics by account average score

Grouping with sorting
Select Sex,avg (age) as Avg. from Stu GROUP by sex ORDER by avg desc;

* Group after filter having
GROUP by sex, average age greater than or equal to 6
Select Sex,avg (age) as Avg. from Stu GROUP by sex have avg>=6;
GROUP by sex, average age greater than or equal to 6, arranged by average age from large to small
Select Sex,avg (age) as Avg. Stu GROUP BY sex has a avg>=6 order by avg Desc;

Where and having can appear at the same time, where is executed before filtering, and having a group before executing

Classes with more than 15 participants, sorted by class size
Select Grade_id,count (*) from Stu GROUP by GRADE_ID have Count (*) >15 ORDER by COUNT (*) desc;
Select Grade_id,count (*) Total from Stu Group by GRADE_ID have total>15 order by total desc;

WHERE, GROUP by, have->order by


Score Table
CREATE TABLE Score (
ID int auto_increment PRIMARY key,
stu_id int,
Subject varchar (50),
Score int
) Engine=myisam default Charset=utf8;

INSERT into score (Stu_id,subject,score) VALUES (1, ' php ', +), (1, ' MySQL ', '), (1, ' Linux ', '), (2, ' php ', +), (2, ' MySQL ', (2, ' Linux ', 70);

Statistics on the average number of passing and passing persons
Select Subject, COUNT (*), AVG (score) from score where score>=70 group Bysubject;
Statistics of the total number of passing and the average of more than 80


Classes with no less than 90 students
Select class from table name where score >=90 group by class having count (*) >1


Display: ID, name, class name
Select Stu.id,stu.name,grade.name from Stu,grade where stu.grade_id=grade.id;
Display: ID, Name, class name, age greater than 10
Select Stu.id,stu.name,grade.name,age from Stu,grade where Stu.grade_id=grade.id and age>10;

Select Stu.id,stu.name,grade.name,age from Stu
Left join grade on grade.id=stu.grade_id
where age>10;

Left Join
The data in the left table, all out, even if the right table does not have the corresponding data
Select Stu.id,stu.name,grade.name,age from Stu
Left join grade on grade.id=stu.grade_id

Right Join
Select Stu.id,stu.name,grade.name,age from Stu
Right join grade on grade.id=stu.grade_id

Use right connection to achieve the same effect as before
Select Stu.id,stu.name,grade.name,age from Grade
Right join Stu on grade.id=stu.grade_id

Inline inner can dispense
Select Stu.id,stu.name,grade.name,age from Stu
Inner JOIN grade on grade.id=stu.grade_id
Inline is the same as the result: SELECT * Table 1, table 2 where condition


Show the scores of all the boys taking the exams
Select Score.*,stu.name from Stu join score on score.stu_id=stu.id where sex=1;
Select Score.*,stu.name from Stu, score where stu.id=score.stu_id and sex=1;
First check the boy's ID, then through the ID list, go to the score table query
Select ID from Stu where sex=1; SELECT * FROM score where stu_id in (1,6,8);
SELECT * FROM score where stu_id in (select ID from Stu where sex=1);


Check the number of subjects and the number of failed to pass:
Number of people failing to pass the course
phy201
mysql262

Select Subject,sum (Case if score<60 then 1 else 0 end) as no,
SUM (case if score>=60 then 1 else 0 end) Pass
From score group by subject;

MySQL Basics 2