MySQL > select * from ‘MySQL’;

This is the 22nd day of my participation in the August Wen Challenge.More challenges in August

Takeaway:

If you have any errors or questions, please kindly point them out.

Private communicate version 15, mysql version 5.7.31

If there is a group by problem, we recommend to check the MySQL version:

If the mysql5.7.x version is used, the only_full_group_by mode is enabled by default, which results in code errors.

Solutions:

1, check sql_mode:

select @@global.sql_mode;
Copy the code

The queried value is:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN GINE_SUBSTITUTIONCopy the code

2. Delete ONLY_FULL_GROUP_BY and set the value again.

 set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Copy the code

Change global SQL_mode for new database. For an existing database, run the following command under the corresponding data:

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Copy the code

Resource issues:

Put the student table and the study table into the Baidu network disk and take them by yourself;

Link: pan.baidu.com/s/1CxZA_pb9… Extraction code: 1234

I. Purpose of the experiment

1. Master the basic composition and usage of query statements.

2. Master common query skills.

Two, experimental preview

1, SQL query statement format:

Select attribute name from table name;Copy the code

SQL > create table SQL > create table SQL > create table

Create table name (Field Name Field type......
);
Copy the code

Iii. Experimental content and requirements

Select * from ****db_student**** where the basic table is located.

Student: Student

Sno Sname Ssex Sage Sdept
9512101 Li yong male 19 Computer science department
9512103 Ma y female 20 Computer science department
9521101 lily female 22 Information system
9521102 Was wubin male 21 Information system
9521103 Zhang hai male 20 Information system
9531101 Xiao-ping qian female 18 Department of mathematics
9531102 Da-li wang male 19 Department of mathematics

A: Course

Cno Cname Ccredit Semster Period
C01 Introduction to Computer 3 1 3
C02 VB 4 3 4
C03 Computer network 4 7 4
C04 Database Basics 6 6 4
C05 Higher mathematics 8 1 8

Course Selection: SC

Sno Cno Grade
9512101 C03 95
9512103 C03 51
9512101 C05 80
9512103 C05 NULL
9521101 C05 NULL
9521102 C05 80
9521103 C05 45
9531101 C05 81
9531101 C01 67
9531102 C05 94
9521103 C01 80
9512101 C01 NULL
9531102 C01 NULL
9512101 C02 87
9512101 C04 76

Select * from student where username = ‘db_student’;

(1) Query the information of all students.

Select * from student;
Copy the code

(2) Select student ID, name and year of birth from information department.

Select sno,sname,YEAR(NOW())-sage from student 

WHERE sdept='Information system';
Copy the code

(3) select student id from student who failed the exam.

Select distinct sno from sc where grade<60;
Copy the code

(4) select student ID and course ID from student where there is no test result.

Select sno,cno from sc where grade is null;
Copy the code

(5) Rank the students in ascending order by age.

Select * from student order by sage;
Copy the code

(6) select student id and name from course.

(Requirement: use join query and nested subquery respectively)

Connection query:

Select distinct student.sname,sc.sno from student,sc where student.sno=sc.sno ;
Copy the code

Nested subquery:

select sno,sname from student where sno in (
select distinct sno from sc);
Copy the code

Supplement:

=any- > Specifies a value in the subquery result.

Select sno,sname from student where sno=any (select distinct sno from sc);
Copy the code

Select * from student where age between 20 and 23; select * from student where age between 20 and 23; select * from student where age between 20 and 23;

Select sname,sage,sdept from student where sage between 20 and 23 order by sdept;
Copy the code

Supplement:

Note: utF-8 default school team set is UTF-8-general-ci, which is not based on Chinese characters, so you need to force mysql to sort according to Chinese characters.

Select sname,sage,sdept from student where sage between 20 and 23 order by convert(sdept using gbk);
Copy the code

Select student id, name from computer network or basic database course.

(Requirement: use join query and nested subquery respectively)

Connection query:

select distinct student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and (cname='Computer Network' or cname='Database Basics');
Copy the code

Nested query:

select student.sno,sname 

from student

where sno in 

(

	select sno From sc

	where cno in (select cno from course where cname = 'Computer Network' or cname = 'Database Basics' ));
Copy the code

Supplement:

Joint query:

Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='Computer Network')
Union
Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='Database Basics');
Copy the code

Select * from student whose surname is “Zhang”;

select * from student where sname like'a %';
Copy the code

Student id, name, number of courses, course name list (separated by commas), in ascending order according to student ID.

 SELECT student.sno,sname,COUNT(*),
 ​
 GROUP_CONCAT(cname ORDER BY cname SEPARATOR ', ')'Course List'
 ​
 FROM student,sc,course
 ​
 WHERE student.sno=sc.sno AND sc.cno=course.cno
 ​
 GROUP BY student.sno
 ​
 ORDER BY student.sno;
Copy the code

The end:

If you see this or happen to help you, please click 👍 or ⭐ thank you;

There are mistakes, welcome to point out in the comments, the author will see the modification.