Team Project----Database SQL Statement Learning Summary and practice

　　In this team project programming process, I am mainly responsible for the database design and establishment, in the knowledge is insufficient, the ability is insufficient, without the database design experience, the SQL statement carried on the more thorough study, today will the result collation, wrote this blog, welcome everybody pointed out my insufficiency.

　　First, introduce use DOS command to enter MySQL database under Windows

1. The Windows icon in the lower left corner of the desktop--enter cmdin the search box, result, click cmd.exe, or use the shortcut key Windows key (the key that has a Windows flag on the keyboard) +r enter CMD and return.

2. Start MySQL database: Enter net start MySQLin the DOS command window, or use the shortcut key Windows key (keys with a Windows logo on the keyboard) + R Direct input net start MySQL back to enter. (also attached: closed command for net stop MySQL).

3. Enter mysql-hlocalhost-uroot-p in the DOS command window to enter the MySQL database, where- h indicates the server name, localhost indicates local;-u is the database user name , root is the MySQL default user name,-p is the password, if the password is set, can be directly after- p link input, such as:-p123456, the user does not set a password, display enter password, You can enter directly. Note that if your MySQL is not installed in the C drive, you will need to use the DOS command to go to the bin directory in the MySQL installation directory. Take My computer as an example, the method is as follows: Enter D: Enter the D drive and enter the bin directory of the MySQL in the input CD D:\Tools\MySQL5.5.25\bin to enter the Mysql-hlocalhost- Uroot-p.

4. Enter show databases; Show your database (the commands in the MySQL database must end with a semicolon ";").

5. Enter the use "database name"; In the list of displayed databases, select the database that you want to use.

6. After entering the database, enter show tables and display all the tables in the database.

7. Use the SQL statement to perform the operation on the table.

8. If you want to exit the MySQL database, enter exit; Enter.

The practice of SQL statements

As a total of four tables, the tables are now manipulated to deepen the understanding of the SQL statement:

Table 1: Class table, store all classes in the school

Table name: Class

Property name	Meaning	Type	Allow null	Category	Fields and Constraints
Classno	Class number	CHAR (6)	N	Pk	PRIMARY KEY constraint
ClassName	Class name	varchar (30)	N
Institute	Affiliated College	varchar (30)	N
Grade	Grade	smallint	N
Classnum	Class size	Int	Y	Derived	This property is a redundant property, and the value is calculated automatically by the table student.

Table 2: Curriculum, storage of all school courses

Table Name: Course

Property name	Meaning	Type	Allow null	Category	Fields and Constraints
Courseno	Course Number	CHAR (3)	N	Pk	PRIMARY KEY constraint
Coursename	Course Name	varchar (30)	N		Unique constraint: The course name cannot be duplicated.
Credithour	Credits	Numeric (1,0)	N
Coursehour	Number of lessons	tinyint	N
Priorcourse	First Course	CHAR (3)	Y	Fk	Foreign key constraints, refer to the table's primary key Courseno

Table 3: Student tables, storing all students in the school

Table Name: Student

Property name	Meaning	Type	Allow null	Category	Fields and Constraints
Studentno	School Number	CHAR (7)	N	Pk	Can only be a full number of 7-bit characters, primary key constraints.
Studentname	Name	varchar (20)	N
Sex	Gender	CHAR (2)	Y		Range of values: male, female, or null
Birthday	Date of birth	Datetime	Y
Native	Origin	varchar (20)	Y
Nation	National	varchar (30)	Y		The default is Han
Classno	Affiliated class	CHAR (6)	Y	Fk	FOREIGN KEY constraint, reference Class.classno

Table 4: The score table, which indicates which students have taken the exams and what their grades are.

Table Name: Score

Property name	Meaning	Type	Allow null	Category	Fields and Constraints
Studentno	School Number	CHAR (7)	N	Pk,fk	FOREIGN key constraint, refer to student. Studentno
Courseno	Course Number	CHAR (3)	N	Pk,fk	FOREIGN key constraint, refer to course. Courseno
Score	Results	Numeric (5,1)	N		The default is 0, which must be a number between 0-100

Note that the primary key for table score is the combined primary key for the two properties of Studentno and Courseno, indicating that there is a many-to-many relationship between the student and the course, that a student can take multiple courses, and a course can be chosen by multiple students.

Connection query:
based on the Score,student,course three table, it is important to note that there may be some student numbers that are not in the score table (that is, some students have not consulted on any of the courses), and there may be some course numbers that are not in the Socre table (that is, some courses do not have any students).
Natural Connection: According to the Score,student,course three table shows the student's test results, showing the school number, name, course number, course name, results, query results by the number of ascending order. Only check the results of the number and course number (students and courses that took the exam) that appeared in the score table:
Select A.studentno,b.studentname,a.courseno,c.coursename,a.score
From score A,student B,course C
where A.studentno=b.studentno and A.courseno=c.courseno
ORDER BY A.studentno
outside connection: According to the Score,student,course display student's examination result situation, displays the school number, the name, the course number, the course name, the result, the query results by the study number ascending order. In addition to the students who have taken the exam, the students who have not taken the exam are also queried, and the courses not selected by the students are not shown:
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a joins Course C on A.courseno=c.courseno
Right join Student B on A.studentno=b.studentno
ORDER BY B.studentno
or write
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From Student B left joins score a on A.studentno=b.studentno
Left join Course C on A.courseno=c.courseno
ORDER BY B.studentno
outside connection: According to the Score,student,course display student's examination result situation, displays the school number, the name, the course number, the course name, the result, the query results by the study number ascending order. In addition to inquiring about the courses selected by the students, the students who have not been selected by the students are not shown:
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a joins Student B on A.studentno=b.studentno
Right join Course C on A.courseno=c.courseno
ORDER BY B.studentno
or write
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From Student B joins score a on A.studentno=b.studentno
Right join Course C on A.courseno=c.courseno
ORDER BY B.studentno
Full connection: According to Score,student,course shows the student's test results, showing the number, name, course number, course name, results, query results by the number of ascending order. In addition to the students who have taken the examination, they have to inquire about the students who have not taken the exam, and to inquire about the courses that have not been selected by the students except for the courses they have chosen.
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a right joins Student B on A.studentno=b.studentno
Full outer join Course C on A.courseno=c.courseno
ORDER BY B.studentno
or write
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From Student B left joins score a on A.studentno=b.studentno
Full outer join Course C on A.courseno=c.courseno
ORDER BY B.studentno
Self-Connection: According to the course table, the information of all courses is queried: Course number, course name, credits, number of lessons, first course Name:
Select A.courseno,a.coursename,a.credithour,a.coursehour,b.coursename as First course
From Course as a left join Course as B
On B.courseno=a.priorcourse

Aggregate query:
Enquiry number is 0800005 students selected modified course total credits
Select SUM (credithour) Total Credits
From score A, Course b
where studentno= ' 0800005 ' and A.courseno=b.courseno
Check the number of selected classes, average points, the highest score of each student
Select Studentno,count (*) Gate number, AVG (score) average, Max (score) Highest score
From score
GROUP BY Studentno
nested subqueries
query for students with scores greater than average in the score table
SELECT * FROM score where score>= (select AVG (score) from score)
The average score and the highest score of each class are queried, the properties shown are: Class number, class name, average score, highest score, and the final result is displayed in reverse order of average.
Select C.classno,d.classname,c. Average score, c. Highest score
From (select A.classno,avg (score) average, Max (score) highest score from Student A,score b
where A.studentno=b.studentno GROUP by A.classno) C, Class D
where C.classno=d.classno
ORDER BY average DESC
View Design
Create a view that contains the number, name, and age of the Studentageview
CREATE View Studentageview as
Select Studentno,studentname,year (getdate ())-year (birthday) age from Student
Go
SELECT * FROM Studentageview

Can the order of connections be exchanged in the case of three-table or multiple-table connections or full connections? Why?
Like what:
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a
Right join Student B on A.studentno=b.studentno
Full outer join Course C on A.courseno=c.courseno
ORDER BY B.studentno
Can you change it to:
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a
Full outer join Course C on A.courseno=c.courseno
Right join Student B on A.studentno=b.studentno
ORDER BY B.studentno

No, but it can be changed to
Select B.studentno,b.studentname,c.courseno,c.coursename,a.score
From score a
Right join Course C on A.courseno=c.courseno
Full outer join Student B on A.studentno=b.studentno
ORDER BY B.studentno

Team Project----Database SQL Statement Learning Summary and practice