Shulou(Shulou.com)06/01 Report--

Let's talk about the basic syntax of mysql. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on the basic grammar of mysql.

-- add, delete or change insert delete update

-- adding must populate all columns with data, except (self-incrementing column, with default value column, allowed to be empty).

INSERT [INTO] table (column list) values (values list)

-- delete

DELETE from table [where condition]

DELETE from student

-- change

UPDATE set column = value, column = value [where condition]

Update student set name = 'Zhang Liang', set sex = 'female' where studentno ='4'

-- query fuzzy query paging

Like between in is null

-- query sort grouping join

-- sorting order by defaults to ascending order: asc descending order: desc

Sort by multiple columns, first by the first field, and then by the second field.

Select * from student order by age,studentno

-- grouping aggregate function sum avg max min count

Select sum (age), avg (age), max (age), min (age) from student

-- count counts how many rows of data there are. If you count a column, the null value in the column is ignored.

Select count (email) from student

-- count how many students have not entered mailbox information?

Select count (*) from student where email is null

-- grouping. Group by classifies and then aggregates the data, which must be used with the aggregate function.

Key points: what groups are grouped and what aggregate functions are used for statistics.

If a list is now preceded by the from keyword and is not included in the aggregate function, this column must appear in the group by clause

How many students are there in each grade?

Select gradeId,count (*) from student group by gradeId

-- how many boys and girls are there in each grade? Grouped by grade and gender, using count function

Select gradeid,sex,count (*) from student group by sex,gradeId

How many class hours are there in each grade?

Select gradeid,sum (classHours) from subject group by gradeid

Statistics on how many courses are there in each grade?

Select gradeid,count (*) from subject group by gradeid

-- Statistics of each student's total grade point and average grade?

Select studentno,sum (result), avg (result) from score group by studentno

-- join query internal connection, external connection, cross connection

Join queries are used only when the data comes from two or more tables.

The where condition is that the primary key columns of two tables are equal.

Select * from student sparry grade g where s.gradeid=g.gradeid

It is recommended to use the following writing method for better performance.

Select * from student s inner join grade g on s.gradeid=g.gradeid

-- query name, student number, course name and score data from three tables?

Select name,s.studentno,subjectname,result from student s

Inner join score c on s.studentno = c.studentno

Inner join subject j on c. Roomtno = j.subjectno

-- external connection left external connection right external connection

/ * left outer connection. The front table is the main table, and the latter table is the child table. All the data of the main table is displayed.

Then fill it with the data of the child table, and if there is no corresponding data in the child table, use NULL to fill * /

Select * from student s

Left join score c on s.studentno = c.studentno

To find out which students have not taken the exam, use the left outer link to achieve?

Select * from student s

Left join score c on s.studentno = c.studentno

Where c.studentno is null

To find out which students did not take the exam, use a subquery to implement it?

-- the result of a subquery can only return a list of values. If there are multiple values, you can only use in instead of =.

Select * from student where studentno

Not in (select studentno from score)

What are the basic grammar-related contents of the above mysql, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.