2016-06-09

SQL基础

创建删除DB

• 创建:

  CREATE DATABASE dbName;

• 删除:

  DROP DATABASE dbName;

创建删除表

• 创建:

  CREATE TABLE xxx (colum1 dataType,colum2 dataType);

• 删除:

  DROP TABLE tableName;

• 修改表结构:

  ALTER TABLE XXX ADD COLUMN columName type;
  ALTER TABLE XXX DROP COLUMN columnName;

增删改查

• 查询

  SELECT * | xx,xx FROM xxxTable WHERE XXX AND XXX;

• 增加

  INSERT INTO xxName (column,column) VALUES (xx,xx);

• 更新

  UPDATE xxName SET colum=xxx, colum=xxx WHERE XXX;

• 删除

  DELETE FROM xxName WHERE XXX;

主键和外键

• 非空和唯一性

  CREATE TABLE xxName(id int, name varchar(50) NOT NULL UNIQUE);
  CREATE TABLE xxname(id int, name varchar(50) NOT NULL,CONSTRAINT unique_name UNIQUE(name));
  CONSTRAINT unique_name UNIQUE (name, category);

• 主键

  id int PRIMARY KEY,

• 外键

  movie_id int REFERENCES movies(id), | movie_id int REFERENCES movies,
  FOREIGN KEY (movie_id) REFERENCES movies

聚合函数

• count(column_name):not null

• sum(column_name)

• avg(column_name)

• max(column_name)

• min(column_name)

数据库查询

• 比较运算符

  等于(=),小于(<),大于(>),小于或等于(<=),大于或等于(>=),不等于(<>)

• 排序

  SELECT * FROM xxx ORDER BY XX DESC(递减);默认递增(ASC)

• 分组

  GROUP BY columnName
  HAVING COUNT(*) > 1 分组的搜索数量大于1

• CHECK & IN

  duration int CHECK (duration>0)
  WHERE id in (2,3)

• Aliases

  column and table - SELECT m.title as tit FROM Movies m

多表查询

• Inner Joins

  SELECT * FROM Movies INNER JOIN Reviews ON Movies.id=Reviews.movie_id

• Outer Joins

  SELECT * FROM Movies LEFT OUTER JOIN Reviews ON Movies.id=Reviews.movie_id;会去除所有Movies(左)的数据
  SELECT * FROM Movies RIGHT OUTER JOIN Reviews ON Movies.id=Reviews.movie_id;会去除所有Reviews(右)的数据

• Subqueries

  SELECT SUM(sales) FROM Movies WHERE id IN (SELECT movie_id from Promotions where category='Non-cash')
  SELECT SUM(m.sales) FROM Movies m INNER JOIN Promotions p ON m.id=p.movie_id WHERE p.category='Non-cash'

Database