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(...)
三、一些知识
- Information Systems Definition:
An organizational system designed to collect, process, store, and distribute information.
- database:
- People:
End Users
Application Developers
Database Administrators
DBMS Desingers
- ‘information’ or ‘understanding’ is only acquired if we can usefully acess and interpret the data.
- Requires:
- 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…
后续还有题目总结