Common SQL commands (2)

Create Student Case table: Student

CREATE TABLE Student (St_class char (8), St_no char (TEN) not null,st_name char (8) Not null,st_sex char (2), St_age Smallint,pri MARY KEY (St_no))

Create a Course Setup table: Subject

CREATE TABLE subject (Su_no char (4) Not null,su_subject char (a) not null,su_credit integer,su_period Integer,su_preno CHA R (4), PRIMARY KEY (su_no))

Create student-selected timetable: Score

CREATE TABLE score (St_no char (Ten), St_nom char (4), St_score integerr null,foreign KEY (st_no) REFERENCE student)

View table structure: DESC Student

Delete tables: Drop table Student,score

Add field: ALTER TABLE student Add (st_born date not NULL)

Delete field: ALTER TABLE student Drop (st_age)

To modify the field type and length:

ALTER TABLE student ALTER COLUMN name varchar (all) null

View

Export A view from the student table that includes only the female student situation:

CREATE View Womanview as select St_class,st_no,st_name,st_age from student where

st_sex= "Female"

Create a female student view from the student and score tables with a score of more than 60 points:

CREATE View Woman_score as select Student.st_class student.st_no,student.st_name,

Student.st_age,score.sc_score from student score where student.st_sex= "woman" and

Score.sc_score >= and Student.st_no=score.st_no

View Woman_score View

SELECT * FROM Woman_score

Index

Create indexes for St_no and st_age in basic table student, ascending and descending, respectively

Create unique index Stindex on student (st_no asc,st_age DESC)

To delete an index:

Drop INDEX Stindex

Select query

Join ON statement joins

WHERE statement specifying query criteria

The ORDER BY statement specifies the sort field and sort method

GROUP BY and having statements grouped

DISTINCT Statements and UNIQUE statements specify whether to repeat records

Top statements are listed in the top

Operators and arithmetic functions in the WHERE clause

Arithmetic comparison operators: < <= > >= = <>

Logical operators: And,or,not

Set operator: UNION (and), INTERSECT (cross), EXCEPT (poor)

Collection Membership operator: In,not in

Predicate: EXISTS (present), all, SOME, UNIQUE

Aggregate function: Avg,min,max,sum,count (count)

Conditional query:

Select Unique Student.st_class,student.st_no,student.st_name,student.sex,

Student.st.age,score.su_no,score.score

Sort query:

Failing courses and arranging the course numbers from large to small

Select unique Su_no from score where score<60 order by Su_no DESC

Nested queries:

The number and name of the student whose course number is C03 and the course score is more than 80 points

Select St_no,st_name from student where St_no in (select St_no from score where su_no = ' c03 ' and score>80)

To calculate a query:

Check the total number of male students and the average age:

Select COUNT (*), AVG (st_age) from student where st_sex= ' man '

Number of elective courses in statistics:

Select count (DISTINCT st_no) from score

Data Update

INSERT statement: INSERT INTO score (St_no,su_no,score) value (' 10002 ', ' C02 ', 95)

Delete statement: Delete from student where st_no = ' 10002 '

UPDATE statement: Update subject Set su_subject = ' English ' where su_no = ' C02 '

Data Control

security control, integrity control, transaction control, and concurrency control

Authorization statement: Grant connect to SSE identified by password

Grant resource to SSE

Grant DBA to SSE

Power Recovery:

Revoke connect from SSE

Revoke resource from SSE

Grant the table student query rights to all users:

Grant SELECT on student to public

The right to insert and modify the table subject is granted to the user SSE and gives him the power to grant such power to others

Grant Insert,update (Su_subject) on subject-SSE with GRANT option

      

Common SQL commands (2)