Illustration of MySQL database installation and actual operations

This article describes how to install and operate a MySQL database. Do you have a headache in obtaining an illustration of MySQL database installation and actual operations? If this is the case, the following articles will provide you with corresponding solutions.

1. Diagram of MySQL database installation and operation: initial database

(Best combination with PHP) database installation and operations

Width = "505" height = "420"/>

B. Direct representation: select name 'name' from students order by age

2. Exact search:

A. Use in to specify the range: select * from students where native in ('hunan ', 'sichuan ')

B. between... and: select * from students where age between 20 and 30

C. "=": select * from students where name = 'hill'

D. like: select * from students where name like 'Li % '(Note that there is "%" in the query condition, it indicates that it is partially matched and there is information in it successively, that is, search for matching items starting with "Li. Therefore, to query all objects with "Li", run the following command: '% Li %'. If the second word is Li, it should be '_ Li %', '_ li', or '_ Li _'.)

E. [] match check character: select * from courses where cno like '[AC] %' (The Relationship Between or and "in (...) "similar, and" [] "can represent a range, such as: select * from courses where cno like '[A-C] % ')

3. Processing Time type variables

A. smalldatetime: Process strings directly, for example:

Select * from students where birth> = '2017-1-1 1' and birth <= '2017-12-31'

4. Diagram of MySQL database installation and operations. Set Functions

A. count () summation, for example: select count (*) from students (total number of students)

B. avg (column) calculates the mean, for example, select avg (mark) from grades where cno = 'b2'

C. max (column) and min (column), max and min

5. group

Used for statistics, such as the total number of group queries:

 
 

  
  
select gender,count(sno)   
  
  
from students  
  
  
group by gender 
 
 

(Check the number of male and female students)

Note: from which point of view the group is, from which column "group"

For multiple groups, you only need to list the grouping rules. For example, to query the number of male and female students in each major, the grouping rules are: grade, mno, and gender. Therefore, "group by grade, mno, gender"

 
 

  
  
select grade, mno, gender, count(*)  
  
  
from students  
  
  
group by grade, mno, gender 
 
 

Normally, group is also used with having. For example, if a student who fails to take more than one course is queried, the student IDs are classified by student ID (sno) as follows:

 
 

  
  
select sno,count(*) from grades   
  
  
where mark<60 
  
  
group by sno  
  
  
having count(*)>1  
 
 

6. UNION

Merge query results, such:

 
 

  
  
SELECT * FROM students
  
  
WHERE name like 'sheet %'
  
  
UNION [ALL]
  
  
SELECT * FROM students
  
  
WHERE name like 'Li %'
 
 

7. Diagram of MySQL database installation and operations. Multi-Table query

A. Internal Connection

 
 

  
  
select g.sno,s.name,c.coursename   
  
  
from grades g JOIN students s ON g.sno=s.sno  
  
  
JOIN courses c ON g.cno=c.cno 
 
 

(Note that aliases can be referenced)

B. External Connection

B1. left join

 
 

  
  
select courses.cno,max(coursename),count(sno)   
  
  
from courses LEFT JOIN grades ON courses.cno=grades.cno   
  
  
group by courses.cno 
 
 

Left join feature: displays all items in all left tables, even if some of the items are not completely filled in.

The left Outer Join returns the rows that exist in the left table but do not exist in the right table, plus the rows of the inner join.

B2. right join

Similar to left join

B3. full connection

 
 

  
  
select sno,name,major   
  
  
from students FULL JOIN majors ON students.mno=majors.mno 
 
 

Show all content in both tables

C. Self-connection

 
 

  
  
select c1.cno,c1.coursename,c1.pno,c2.coursename   
  
  
from courses c1,courses c2 where c1.pno=c2.cno 
 
 

Use aliases to solve the problem.

D. Cross join

 
 

  
  
select lastname+firstname from lastname CROSS JOIN firstanme 
 
 

Equivalent to playing the flute Product

8. nested Query

A. Use the keyword IN, for example, to query the hometown of Li Shan:

 
 

  
  
Select * from students
  
  
Where native in (select native from students where name = 'hill ')
 
 

B. Use the keyword EXIST. For example, the following two sentences are equivalent:

 
 

  
  
select * from students  
  
  
where sno in (select sno from grades where cno=’B2’)  
  
  
select * from students where exists   
  
  
(select * from grades where   
  
  
grades.sno=students.sno AND cno=’B2’)  
 
 

9. sorting order

A. There are two methods for sorting order: asc Ascending order and desc descending order.

B. sorting order can be arranged according to a certain item in the query condition, and this can be represented by numbers, such:

 
 

  
  
select sno,count(*) ,avg(mark) from grades   
  
  
group by sno  
  
  
having avg(mark)>85  
  
  
order by 3  
 
 

10. Diagram of MySQL database installation and operations. Miscellaneous

A. names with spaces should be enclosed.

B. You can use null to determine specific queries without data in a column, such as select sno and courseno from grades where mark IS NULL.

C. Differentiate the differences between any and all used in nested queries. any is equivalent to the logical operation "|", while all is equivalent to the logical operation "&".

D. Be careful when querying negative meanings:

For example, students who have not taken the 'b2' course:

 
 

  
  
select students.*  
  
  
from students, grades  
  
  
where students.sno=grades.sno  
  
  
AND grades.cno <> ’B2’  
 
 

The preceding query method is incorrect. For the correct method, see the following:

 
 

  
  
select * from students  
  
  
where not exists (select * from grades   
  
  
where grades.sno=students.sno AND cno='B2')  
 
 

11. Solution to multiple difficult nested queries:

For example, students who take all courses:

 
 

  
  
select *  
  
  
from students  
  
  
where not exists ( select *  
  
  
from courses  
  
  
where NOT EXISTS   
  
  
(select *  
  
  
from grades  
  
  
where sno=students.sno  
  
  
AND cno=courses.cno)) 
 
 

Exclusive: select from the student table to exclude those with no courses. Use not exist. Because the subject is a course, you can find the second query in the course table and exclude those that have selected the course.