mysql basic syntax

Today we will talk about the basic syntax of mysql. By learning and mastering these syntaxes, we can do some simple basic operations on the database.

-- Add, delete, modify insert delete update

-- Add data must be filled in all columns, except (auto-increment column, There is a default value column, which is allowed to be empty) and can not be filled
INSERT [INTO] table (column list) values ​​(value list)

-- Delete
DELETE from table [where condition ]
DELETE from student

-- Change
UPDATE table set column = value, column = value [where condition]
update student set name = 'Zhang Liang',set sex = ' Female' where studentno = '4'

-- Query Fuzzy Query Pagination
like between in is null

-- Query Sorting Grouping Connection
- - Sort by order by default is ascending order: asc Descending order: desc
-- Sort by multiple columns, first sort by the first field, and then sort by the second field.
select * from student order by age,studentno
-- Grouping aggregation function sum avg max min count
select sum(age),avg(age),max(age),min(age) from student;
- - count is a count of how many data rows there are. If a column is counted, NULL values ​​in the column will be ignored.
select count(email) from student
-- Count how many students have not entered their email information??
select count(*) from student where email is null

-- Grouping, group by is to classify and then summarize the data. It must be used with aggregation functions.
-- Key points: What to group by and what aggregation function to use for statistics.
-- If a column appears before the from keyword and is not included in the aggregate function, then this column must appear in the group by clause
-- Count how many students are there in each grade?
select gradeId,count(*) from student group by gradeId
-- Count how many male and female students there are in each grade? Group by grade and gender, use the count function
select gradeid,sex,count(*) from student group by sex,gradeId;
-- Count how many classes are in each grade?
select gradeid,sum(classHours) from subject group by gradeid
-- Count how many courses are in each grade?
select gradeid,count(*) from subject group by gradeid
-- Statistics of each student's total score and average score?
select studentno,sum(result),avg(result) from score group by studentno

-- Join query Inner join Outer join Cross join
-- When data comes from two or more tables, join query is used to achieve .
-- where condition is that the primary key columns of the two tables are equal.
select * from student s,grade g where s.gradeid=g.gradeid
-- It is recommended to use the following writing method, which has better performance.
select * from student s inner join grade g on s.gradeid=g.gradeid
-- Query name, student number, course name, score The data comes from 3 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.subjectno= j.subjectno

-- Outer join Left outer join Right outer join
/* Left outer join, the front table is the main table, the back table is the sub-table, all the data of the main table is displayed,
Then use the sub-table The data in the table is filled. If there is no corresponding data in the sub-table, NULL is used to fill it */
select * from student s
left join score c on s.studentno = c.studentno

-- To query which students have not taken the exam, use left outer join? ?
select * from student s
left join score c on s.studentno = c.studentno
where c.studentno is null
-- Query which students did not take the exam, use subquery? ?
-- The result of the subquery can only return one column of values. If there are multiple returned values, you can only use in and cannot use =
select * from student where studentno
not in( select studentno from score)

The above are some simple basic operations on tables in Mysql. I hope it will be helpful to everyone.

Related articles:

MySql basic syntax (study notes)_MySQL

Understand the basic syntax of mysql

The above is the detailed content of mysql basic syntax. For more information, please follow other related articles on the PHP Chinese website!