[database] must know at the end of the term ----- Chapter 6 experiment

Chapter six Experimental part

（ This part is the focus of the exam ）

1、SQL The composition of language 、 characteristic ？

form ：

1）DDL（ Database definition language ：CREAT、DROP、ALTER）

2）DML（ Database manipulation language ：INSERT、UPDATE、DELETE、SELECT）

3）DCL（ Database control language ：GRANT、REVOKE）

characteristic ：

Integrate and unify 、 Highly unprocessed 、 Face the set 、 The language is simple 、 Easy to learn and easy to learn

2、 Write SQL Data definition statement （ Create delete database 、 Data sheet 、 Will modify the table structure ）？

// Create database 
CREATE DATABASE D1;
// Delete database 
DROP DATABASE D1;
// Create table 
CREATE TABLE student(
  Student number  char(4) NOT NULL PRIMARY KEY,
  full name  char(8) NOT NULL,
  Gender  char(2),
  Date of birth  date,
  Home address  varchar(50)
);
// Delete table 
DROP TABLE student;

// Modify table structure 
ALTER TABLE grade 
ADD CONSTRAINT fk1 FOREIGN KEY( Student number ) REFERENCES student( Student number );

ALTER TABLE grade 
ADD CONSTRAINT fk2 FOREIGN KEY( Course number ) REFERENCES curriculum( Course number );

ALTER TABLE student
ADD  remarks  varchar(50);
.......

3、 Write SQL Inquire about （ The basic query 、 How to write the conditions ; nested queries 、 Link query 、 Set query ;order by、 group by, Aggregate functions should be able to ）？

// The basic query 
SELECT  Student number , full name , Date of birth  FROM student;
SELECT  full name , Home address  FROM student WHERE  Student number ='0002';
SELECT  full name , Date of birth  FROM student
WHERE  Date of birth >='2001-01-01' and  Gender =' Woman ';

// Conditions of the query 
SELECT * FROM grade WHERE  fraction  BETWEEN 70 AND 80;
SELECT AVG( fraction )  average  FROM grade WHERE  Course number ='0002';
SELECT COUNT(*)  The number of students who choose courses ,COUNT( fraction )  The number of people with achievements  FROM grade WHERE  Course number 
='0003';
SELECT  full name , Date of birth  FROM student WHERE  Home address  LIKE ' Hangzhou %'ORDER BY  born 
 date  DESC;
SELECT  Student number , full name  FROM student WHERE  full name  LIKE ' Zhang %'

// nested queries 
（
1）
SELECT  full name , Date of birth  FROM student
WHERE  Gender =(SELECT  Gender  FROM student WHERE  full name =' Liu Dongyang ');
（
2）
SELECT  Student number , full name , Gender  FROM student
WHERE  Student number  IN(SELECT  Student number  FROM grade
WHERE  Course number  IN ('0002','0005'));
（
3）
SELECT  Course number , fraction  FROM grade
WHERE  Student number ='0001' and  fraction >ANY(SELECT  fraction  FROM grade
WHERE  Student number ='0002');
（
4）
SELECT  Course number , fraction  FROM grade
WHERE  Student number ='0001' and  fraction >ALL(SELECT  fraction  FROM grade
WHERE  Student number ='0002');

// Link query 

（
1）SELECT s. Student number , full name , fraction  FROM student s,grade g
WHERE s. Student number =g. Student number  and  fraction  BETWEEN 80 AND 90;
（
2）
SELECT s. Student number , full name , fraction  FROM student s INNER JOIN grade g
ON s. Student number =g. Student number  INNER JOIN curriculum c ON g. Course number =c. Course number 
WHERE  Course name =' Database principle and Application ';
 or 
SELECT s. Student number , full name , fraction  FROM student s , grade g,curriculum c
WHERE s. Student number =g. Student number  and g. Course number =c. Course number 
and  Course name =' Database principle and Application ';
（
3）
SELECT s. Student number , full name ,MAX( fraction )  Top grade 
FROM student s,grade g
WHERE s. Student number =g. Student number 
GROUP BY s. Student number ;
（
4）
SELECT s. Student number , full name ,SUM( fraction )  Total score 
FROM student s LEFT OUTER JOIN grade g ON s. Student number =g. Student number 
GROUP BY s. Student number ;
 notes ：outer  Don't write .
（
5）
insert into curriculum values('0006', 'Python', 3);
INSERT INTO grade VALUES('0004','0006',76);
SELECT g. Course number , Course name ,count( Student number )  Number of electives 
FROM grade g RIGHT OUTER JOIN curriculum c ON g. Course number =c. Course number 
GROUP BY g. Course number ; 

4、 Be able to write SQL Data update statement （ Insert 、 modify 、 Delete ）？

// Insert 
INSERT INTO grade VALUES('0001','0001',80);
INSERT INTO grade VALUES('0001','0002',91);

// Delete 
DELETE FROM stu WHERE  Student number ='0004';

// modify 
UPDATE stu SET  Home address =' Xinjian Road, Binjiang city  96  Number ' WHERE  Student number ='0002';

5、 Concept of view , The difference between a table and a view ？

Concept ： Virtual table , Tables exported from one or more tables

difference ：

1） The view is compiled SQL sentence

2） The table has a physical structure , View none

3） Table belongs to internal mode , View belongs to external mode

4） The table is the content , Trying to be a window

5） Deleting and creating views does not affect the table

6、 establish 、 Delete the statement of the view ？

// establish 
CREATE VIEW < View name >;

// Delete 
DROP VIEW < View name >;

7、 The function of view ？

1) Improved reusability , View is like a function .
2) Refactoring the database , It doesn't affect the operation of the program .
3) Improved security performance . It can be applied to different users , Set different views .
4) Make the data clearer . What kind of data do you want , Just create what kind of view .

8、NULL The meaning of , usage ？

meaning ： I do not know! 、 non-existent 、 meaningless

usage ：

1） assignment , Assign a variable NULL value

2） Judge , Determine whether a variable is NULL

9、 The concept of index , type , effect , Create method ？

Concept ： Is a sorted list , Store the index value and the physical address corresponding to this value

type ： Aggregation class , Non aggregated classes

effect ： Speed up queries , Speed up the connection between tables

Create method ：

CREATE UNIQUE INDEX < Index name > ON grade( Student number );

CREATE INDEX < Index name > ON grade( Student number );