Relational data management System--a SQL concept and operation

Directory

A few definitions of attention points

Second, SQL Operation statement Memo

III. Supplemental SQL constraints

(This is the Chinese Academy of Sciences Chen Shimin Teacher Course study notes)

-----------------------------------------

A few definitions of attention points

1.Scheme type declaration for each column of the table, simply define once

Eg: New table definition Scheme

CREATE TABLE Student (
ID Integer,
Name varchar (20),
GPA float
);

A 2.Primary Key can contain multiple attributes, which can be a combination of supplementary 1-sql constraints

eg. define Takecourse with (Course id,student ID) as the primary key:

Primary KEY (Courseid,studentid),

3.Foreign key refers to other tables, creates a contact, and acts like a pointer, so you cannot point to an empty

eg. Takecourse Reference CourseID

Foreign key (CourseID) reference Course (ID)

Second, SQL Operation statement Memo

Quick notes: Operation type + table + operation details

1. Insert Wangxiao Student Enrolment Information

INSERT INTO Student Values (12345, ' Wang Xiao Yi ', 1994/1/1,m, ' Computer science ');

2. Update Wangxiao when the student's name was wrongly called ' Wang Xiao Yi ', asked to change

Update Student set name= ' Wangxiao ' where ID = 12345;

3. Delete Wangxiao students drop out of business and Wangxiao students lead all computer Science department classmate start-up withdrawal

Delete from Student where id=12345;

Delete from Student where major= ' computer science ';

4. Relational operation one choice selection similar filter

SELECT * from Student where Major = ' computer science ';

Where later conditions can be combined with and OR

5. Relational operation two projections projection similar dimension slices

Select Colum1, colum2 from table;

Projection + Selection

Select Colum1, colum2 from table where Major = ' computer science ';

6. Relational operations three join joins

Equi-join equivalent connection, the simplest way to connect

Select Student.Name.Course.Name from Student to output properties

Multiple tables used from Student,course,takecourse

where Takecourse.courseid = course.id, connection condition 1

and Takecourse.studentid = student.id; Connection Conditions 2

7. Simple statistics GROUP BY

Select Major, COUNT (*), Output count (*) statistic function

From Student table

Where year >= and year <=-conditions

Group BY Major; ---first performs the grouping operation, the output time executes the count (*) within the group all counts in the statistic group

SQL-defined statistical functions Count.sum.avg.max.min

8. Simple statistics two having to select on group by

Select Major, Count (*) as CNT output

From Student table

Where year >= and year <=-conditions

Group by Major grouping and manipulating attributes

Having cnt>=2; ->count output named CNT

9. Simple statistics three order by sort

Select Major, Count (*) as CNT output

From Student table

Where year >= and year <=-conditions

Group by Major grouping and manipulating attributes

ORDER BY CNT Desc; ->desc (Descending reduction) ASC (ascending increase)

Supplemental Appendices:

1.SQL constraints

(1) Primary KEY constraint (PRIMARY KEY constraint) Unique, cannot be empty

(2) Uniqueness constraint (UNIQUE constraint cannot have duplicate values

(3) The FOREIGN KEY constraint (FOREIGN KEY constraint) is consistent with another table data type, and the primary table must be defined before it is defined from the table. That is, the person must exist before you can call him.

(4) Check constraint values need to meet a certain range or requirements.

(5) There are NOT NULL constraints, default constraints, to see the name of the role, all constraints are for the "specification" table.

Here are the explanations: http://www.runoob.com/sql/sql-constraints.html

Relational data management System--a SQL concept and operation