Accp8.0 conversion Materials Chapter 1 MySQL advanced query (1) Understanding and exercises, accp8.0 advanced Query

Accp8.0 conversion Materials Chapter 1 MySQL advanced query (1) Understanding and exercises, accp8.0 advanced Query

I. Word Section

① Constraint ② foreign key foreign ③ references reference

④ Subquery ⑤ inner internal 6 join connections

Ii. Preview

1. Modify the keywords of the table SQL statement

RENAME Modify Table Name CHANGE Modify Field

2. Which keyword can return the query result set based on the specified number of rows?

LIMIT

3. can table connections be replaced by subqueries?

Yes

Iii. Exercises

1. Create a data table and modify the table

# Machine 1
USE test;
Create table person (
Number INT (4) AUTO_INCREMENT primary key,
'Name' VARCHAR (50) not null,
Sex CHAR (2 ),
BornDate DATETIME
);
Alter table person RENAME tb_person;
Alter table tb_person DROP 'borndate ';
Alter table tb_person ADD bornDate DATETIME;
Alter table tb_person CHANGE number id BIGINT;

2. Use an SQL statement to add constraints to the result table in the myschool Database

# Machine 2
USE myschool;
Alter table result add constraint re primary key result ('studentno', 'subjectno', 'examedate ');
Alter table result add constraint fk_result_student foreign key (studentNo) REFERENCES student (studentNo );

3. Add data to the student table, student table, and student table

# Machine 3
USE myschool;
Insert into 'subobject' ('subobjectname', 'classhour ', 'gradeid') VALUES
('Html ', '123', '1 '),
('Java OOP ', '123', '2 ');

4. Modify the student table and student data

# Host 4
USE myschool;
Insert into 'myschool '. 'result' ('studentno', 'subjectno', 'examedate', 'studentresult') VALUES ('20170101', '1', '2017-8-8 ', '78 '),
('123', '2', '1970-7-7', '98 '),
('20140901', '1', '2017-8-8', '78 '),
('20140901', '2', '2017-8-8', '78 '),
('20140901', '1', '2017-8-8', '66 '),
('20140901', '1', '2017-8-8', '55 '),
('20140901', '1', '2017-8-8', '13 '),
('20140901', '1', '2017-8-8', '89 ');
UPDATE student SET eamil = 'stu200000 @ 163.com ', loginPwd = '000' WHERE studentNo = '2016 ';
UPDATE 'subobject' SET 'classhour '= 'classhour'-10 WHERE 'classhour '> 200 AND 'subjectno' = 1;
Drop table if exists student_grade1;
Create table student_grade1 (SELECT 'studentname', 'sex', 'borndate', 'phone' FROM student WHERE 'gradeid' = 1 );

5. query student information (query the student ID and score of the first five students in February 17, 2016)

# Machine 5
SELECT 'studentno', 'studentresult' FROM 'result' WHERE 'examedate' <'2017-2-17 'order by studentResult desc limit 5;

SELECT studentName, (YEAR (NOW ()-YEAR (bornDate) AS age, bornDate, phone FROM student
WHERE sex = 'female'
Order by bornDate ASC
LIMIT 1, 6;

Select year (bornDate) AS nian, COUNT (studentNo) AS num FROM student group by bornDate having count (studentNo)> = 2;

Select max ('studentresult'), MIN ('studentresult'), AVG ('studentresult ') FROM 'result' WHERE 'examedate' = '2017-02-17 'group BY 'studentno ';

6. query the test scores of a specified student

# Host 6
Select max ('studentresult'), MIN ('studentresult') FROM result
WHERE 'examedate' = (SELECT 'examedate' FROM 'result' order by exameDate desc limit 1) AND
'Subjectno' = (SELECT 'subjectno' FROM 'subobject' WHERE 'subjectname' = 'logic Java ');
# Select max (exameDate) from result

7. query the courses offered in a certain semester

# COMPUTER 7
SELECT subjectName FROM 'subobject'
WHERE subjectNo IN (SELECT subjectNo FROM 'subobject'
WHERE gradeId = (SELECT gradeId FROM grade WHERE gradeName = 's1 '));

8. query the list of students missing from the last exam of a course

# Host 8
SELECT 'studentname' FROM student WHERE 'studentno' IN (SELECT 'studentno' FROM student WHERE studentNo not in (SELECT 'studentno' FROM 'result'
WHERE 'subjectno' = (SELECT 'subjectno' FROM 'subobject' WHERE 'subobjectname' = 'html') AND
'Expamedate' = (SELECT 'expamedate' FROM 'result' WHERE subjectNo = (SELECT 'subobjectno' FROM 'subobject' WHERE 'subobjectname' = 'html ')
Order by exameDate desc limit 1 )));

V. Summary

Two methods of multi-table join query are available:

① Table join

② Subquery

You are welcome to ask questions. You are welcome to refer to the error. You can reply to the discussion and learning information in private chat and comments if necessary.

For the original article in the blog Park, contact us for deduction: 2265682997