SQL

Basic Concepts

1. what is database: a collection of related information

2. relational DB vs non-relational DB:

   • 关系数据库需要SQL来进行操作，SQL是统一的标准
   • 非关系型数据库需要特定的语言来操作，没有统一的标准

3. surrogate key: primary key does not have mapping in real world natural key: primary key does have mapping in real world

SQL Basics

1. SQl is actually a hybrid language, 4 tpes in 1:

   • Data Query Language
   • Data Defintion Language
   • Data Control Language
   • Data Manipulation Language

2. 常用的数据类型

   • INT – whole numbers
   • DECIMAL(M,N) – decimal number-exact value
   • VARCHAR(1) – string
   • BLOB – binary large object
   • DATE – ‘YYYY-MM-DD’
   • TIMESTAMP – ‘YYYY-MM-DD HH:MM:SS’

3. CRUD table

   CREATE TABLE student(
       student_id INT PRIMARY KEY,
       name VARCHAR(20),
       major VARCHAR(20)
   );
   
   DESCRIBE student;
   
   DROP TABLE student;
   
   ALTER TABLE student ADD gpa DECIMAL(3,2);
   
   ALTER TABLE student DROP gpa;
   

4. CRUD row

   INSERT INTO student(student_id, name) VALUES(3, 'Kai');
   
   UPDATE student SET major = 'biochemistry' WHERE major = 'biology' OR major = 'chemistry';
   
   DELETE FROM student WHERE name = 'tom' AND major = 'biology';
   
   SELECT student.name, student.major FROM student ORDER BY name DESC;
   
   SELECT * FROM student WHERE name IN ('Mike','Kobe','Ray');