SQL快速入门

SQL快速入门

---

跟codecademy学sql。

创建数据表

语法：

CREATE TABLE db_name(
    param_name　param_type；
);

例

CREATE TABLE celebs (
    id INTEGER, 
    name TEXT, 
    age INTEGER
);

增

增加字段属性

语法：

ALTER TABLE db_name ADD COLNAME param_name param_type;

例：

ALTER TABLE celebs ADD COLNAME twitter_handle TEXT;

插入数据

语法：

INSERT INTO dbname(param1, param_2, ...) VALUES(value1, value2, ...);

例：

INSERT INTO celebs(id, name, age) VALUES(1, 'Justin Bieber', 21);

删

删除数据

语法：

DELETE FROM db_name WHERE condition[eg. param_name IS param_value];

例：

DELETE FROM celebs WHERE twitter_handle IS NULL;

查

查询所有数据

SELECT * FROM db;

查询特定属性的所有值

语法：

SELECT param1,,param2,... FROM db_name;

例：

SELECT name, age FROM celebs;

查询特定属性的不重复值（不显示重复出现的）

语法：

SELECT DISTINCT param_name FROM db_name;

例：

SELECT DISTINCT genre FROM movies;

按条件查询

语法：

 SELECT * FROM db_name WHERE [condition];

• 大小判断：

 = equals
 != not equals
 > greater than
 < less than
 >= greater than or equal to
 <= less than or equal to

例：

 SELECT * FROM movies WHERE imdb_rating > 8;

• 相似查询（LIKE）

SELECT * FROM db_name WHERE param_name LIKE pattern;

例1：

SELECT * FROM movies WHERE name LIKE 'Se_en';

Se_en represents a pattern with a wildcard character. The _ means you can substitute any individual character here without breaking the pattern. The names Seven and Se7en both match this pattern.

例2：

SELECT * FROM movies WHERE name LIKE 'a%';

% is a wildcard character that matches zero or more missing letters in the pattern.
A% matches all movies with names that begin with “A”
%a matches all movies that end with “a”

• 区域查询(BETWEEN）
  The BETWEEN operator is used to filter the result set within a certain range.
  语法：

SELECT * FROM db_name WHERE param BETWEEN value1 AND value2;

例1：

SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J';

例2：

SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000;

• 混合筛选查询（AND， OR）
  语法：

 SELECT * FROM db_name WHERE condition1 AND condition2;
SELECT * FROM db_name WHERE condition1 OR condition2;

例：

SELECT * FROM movies
WHERE year BETWEEN 1990 and 2000
AND genre = 'comedy';

SELECT * FROM movies
WHERE genre = 'comedy'
OR year < 1980;

对查询结果处理

排序

语法：

SELECT * FROM db_name ORDER BY param_name DESC;

DESC is a keyword in SQL that is used with ORDER BY to sort the results in descending order (high to low or Z-A).
ASC is a keyword in SQL that is used with ORDER BY to sort the results in ascending order (low to high or A-Z).

例：

SELECT * FROM movies ORDER BY imdb_rating DESC;

数量限制（LIMIT）

语法：

SELECT * FROM db_name
ORDER BY prama_name DESC
LIMIT num;

例：

SELECT * FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;

计数（COUNT），求和（SUM），最大值（MAX），最小值（MIN），平均值（AVG）

语法：

SELECT param*，COUNT(SUM/MAX/MIN/AVG(param_name), int) FROM db_name
WHERE [condition] 
GROUP BY param_name;

例：

SELECT category, SUM(downloads) FROM fake_apps
GROUP BY category;

SELECT name, category, MAX(downloads) FROM fake_apps
GROUP BY category;

SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps
GROUP BY price;

改（UPDATE）

语法：

UPDATE db_name
SET param1 = value1 #需要修改的字段
WHERE param2 = value2; #用来判断查找数据的字段

UPDATE celebs
SET age = 22
WHERE id = 1;

多表查询

SELECT
  table1.param1 [AS str]
  table2.param2
  ...
FROM
  table1
[LEFT/RIGHT] JOIN table2 ON
  [condition];

The left table is simply the first table that appears in the statement. Here, the left table is albums. Likewise, the right table is the second table that appears. Here, artists is the right table.
AS is a keyword in SQL that allows you to rename a column or table using an alias. The new name can be anything you want as long as you put it inside of single quotes.

常用数据类型

1. Integer, a positive or negative whole number
2. Text, a text string
3. Date, the date formatted as YYYY-MM-DD for the year, month, and day
4. Real, a decimal value