Infomation System (lab1~lab3)

Lab1-Lab3

---

---

前言

SQL语言学习

---

一、SQL解题

得到什么，从哪里得到，再一步步细化…

SELECT: 最终要得到的output
（data是否要加上DISTINCT）
FROM: data 来源
（多表JOIN等）
GROUP BY: 要不要合并
ORDER BY: 要不要ASC/DESC排列OUTPUT
（有无LIMIT）

二、基本操作

1.CREATE

CREATE TABLE student(
id INTEGER NOT NULL PRIMARY KEY,/*不可以忘记主键*/
name VARCHAR(20) NOT NULL,
classId VARCHAR(10) NOT NULL,
FOREIGN KEY(classId) REFERENCES classes(id)
);

2.INSERT

INSERT INTO student VALUES(207, "Robin Walker", "CS03");

11对应

3. 加 field/key

ALTER TABLE student ADD courseId VARCHAR(10);

ALTER TABLE student ADD FOREIGN KEY(courseId) REFERENCES courses(id);

4. 更新/改 attribute

UPDATE student SET classId="CS01" WHERE name="Rboin Walker";

5. 删除

DELETE FROM student WHERE name = "Robin Walker";

6. 呈现TABLE的数据类型

DESCRIBE student;

7. 列出TABLE所有存储的数据

SELECT * FROM student;

8. WHERE

/*筛选条件*/
BETWEEN(A,B)/*[A,B]*/
IN(......)/*可以替换连续的 OR, 用于多项查找*/
LIKE:
LIKE('_ABCE')/*不知道开头字母*/
LIKE('%ABCE')
LIKE('[A,B]%')/*开头是A/B*/

9. 计算

常常与GROUP BY一起
AVG(...): !不可以写成AVG(SUM(...))!
SUM(...)
COUNT(...)/*计算个数，区分sum*/
MIN(...) MAX(...)

三、一些知识

1. Information Systems Definition:
   An organizational system designed to collect, process, store, and distribute information.

2. database:
   

3. People:
   End Users
   Application Developers
   Database Administrators
   DBMS Desingers

4. ‘information’ or ‘understanding’ is only acquired if we can usefully acess and interpret the data.

5. Requires:
   

6. database 应该处理的数据：
   Huge volumes of data
   Multiple simultaneous users and queries
   Very sensitive data
   Very complex data
   Dynamic data

四、两种database：

1.flat file database(word)

表格/文档之间没有关系建立。
独立的个体、重复的数据、文档格式不相容、固定的操作

2.relational database（1970年由Edgar Codd 发明）

structure:
Is based on the mathematical theory of set relations and predicate logic.

Integrity:

Keys:
Candidate Key
Primary Key
Composite Key
Foreign Key

Referential Integrity: Maintains relation between tables
Foreign Key

五、Terminology

relational model --> relational databases --> DBMS --> RDBMS is a DBMS that is based on the relational model.

---

总结

继续ing…
后续还有题目总结