SQL 简易教程

说实话，平常我用不到 SQL，所以一直没有去学。因为要面试，在准备 SQL 方面的知识，就快速学习了一下。我的感觉是和 Pandas 没差多少。

我用的是苹果电脑，Windows 的会不太一样。

我选择用 Sqlite ，因为这个最简单，不需要服务器。

首先检查 sqlite3 是否安装：

sqlite3 --version

我假设你已经安装了 Anaconda。接着，我们建一个虚拟环境

conda create --name sql
conda activate sql
conda install pandas jupyter ipython-sql 
pip install SQLAlchemy==1.4.46 # 因为最新版本的 sqlalchemy 和 ipython-sql 有冲突

然后创建一个文件夹

mkdir sql-learn
cd sql-learn 

下载两个文档：

curl -o sqlite.txt https://www.sqltutorial.org/wp-content/uploads/2020/04/sqlite.txt
curl -o sqlite-data.txt https://www.sqltutorial.org/wp-content/uploads/2020/04/sqlite-data.txt

如果你怕安全性有问题，我建议你把两个网址打开，确认是单纯的 txt 文档。

然后：

sqlite3 tutorial.db

接着

.read sqlite.txt

继续

.read sqlite-data.txt

检查一下

.tables

结果应该是

sqlite> .tables
countries    dependents   jobs         regions    
departments  employees    locations  

SELECT * FROM countries LIMIT 5;

结果应该是

sqlite> SELECT * FROM countries LIMIT 5;
AR|Argentina|2
AU|Australia|3
BE|Belgium|1
BR|Brazil|2
CA|Canada|2

如果没问题，就可以继续开始了。

我在 VS Code 里用 Jupyter Notebook。建一个 ipynb 文档，然后下面的都是在那里面操作。

本文严重参考 https://www.sqltutorial.org/ 上的内容。

首先

%load_ext sql
%sql sqlite:///tutorial.db

但是我这个博客里因为数据在别的地方，所以第二行会不一样：

%load_ext sql
%sql sqlite:///../static/files/cnblog/sql-learn/tutorial.db

All tables #

%%sql
SELECT name FROM sqlite_master WHERE type='table';

%%sql
select * from locations limit 5

%%sql 
select first_name, last_name, salary, salary * 1.5 
from employees limit 10;

%%sql 
select 
    employee_id,
    first_name,
    last_name,
    hire_date,
    salary 
from 
    employees
limit 
    10;

%%sql 
select 
    salary 
from 
    employees 
order by 
    salary desc 
limit 10;

%%sql 
select distinct
    phone_number
from 
    employees 
order by 
    phone_number
limit 10;

LIMIT means to show many many rows. OFFSET means starting from which row. First row is 0. So OFFSET 3 means starting from the fourth row.

%%sql 
select 
    employee_id,
    first_name,
    last_name
from    
    employees
order by
    first_name
limit 10;

%%sql 
select  
    employee_id,
    first_name,
    last_name,
    salary
from 
    employees 
where
    salary > 14000 
order by 
    salary desc 

• =: equal
• <>: not equal
• >
• >=
• <
• <=

Logical operators #

• ALL
• AND
• ANY
• BETWEEN
• EXISTS
• IN
• LIKE
• NOT
• OR
• SOME
• IS NULL

AND #

%%sql 
select first_name, last_name, salary 
from employees 
where salary > 5000 AND salary < 7000 
order by salary desc;

%%sql 
select first_name, last_name, salary 
from employees 
where salary = 7000 OR salary = 8000; 

%%sql 
select first_name, last_name, phone_number 
from employees 
where phone_number IS NULL 
order by first_name, last_name;

%%sql 
select first_name, last_name, salary 
from employees 
where salary BETWEEN 9000 and 12000 
order by salary desc;

%%sql 
select first_name, last_name, department_id 
from employees 
where department_id IN (6, 8, 9)
order by department_id;

%%sql 
select employee_id, first_name, last_name 
from employees 
where first_name LIKE '_h%'
order by first_name;

% represents 0, 1, or multiple characters.

ALL, ANY #

SQLITE does not support ALL or ANY. But there are alternatives.

For example

SELECT * FROM employees
WHERE salary >= ALL (
    SELECT salary FROM employees WHERE department_id = 8
);

can be done via

SELECT * FROM employees
WHERE salary >= (
    SELECT MAX(salary) FROM employees WHERE department_id = 8
)

And

SELECT * FROM employees
WHERE salary >= ANY (
    SELECT salary FROM employees WHERE department_id = 8
);

can be achieved through

SELECT * FROM employees
WHERE salary >= (
    SELECT MIN(salary) FROM employees WHERE department_id = 8
);

or

SELECT * FROM employees
WHERE salary IN (
    SELECT salary FROM employees WHERE department_id = 8
);

EXISTS #

For example, show names of those who have dependents:

%%sql 
select first_name, last_name 
from employees e 
where EXISTS(
    select * from dependents d 
    where d.employee_id = e.employee_id
)
limit 10;

Conditional Expression #

%%sql 
SELECT first_name, last_name,
CASE 
    when salary < 3000 then 'low'
    when salary >= 3000 and salary <= 5000 then 'average'
    ELSE 'high'
END AS evaluation 
FROM employees
LIMIT 10;

Alias #

%%sql 

SELECT first_name, last_name, salary * 1.1 AS new_salary 
FROM employees 
ORDER BY new_salary DESC
LIMIT 10;

SELECT e.first_name, e.last_name FROM employees AS e LIMIT 10;

<table>
    <thead>
        <tr>
            <th>first_name</th>
            <th>last_name</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Steven</td>
            <td>King</td>
        </tr>
        <tr>
            <td>Neena</td>
            <td>Kochhar</td>
        </tr>
        <tr>
            <td>Lex</td>
            <td>De Haan</td>
        </tr>
        <tr>
            <td>Alexander</td>
            <td>Hunold</td>
        </tr>
        <tr>
            <td>Bruce</td>
            <td>Ernst</td>
        </tr>
        <tr>
            <td>David</td>
            <td>Austin</td>
        </tr>
        <tr>
            <td>Valli</td>
            <td>Pataballa</td>
        </tr>
        <tr>
            <td>Diana</td>
            <td>Lorentz</td>
        </tr>
        <tr>
            <td>Nancy</td>
            <td>Greenberg</td>
        </tr>
        <tr>
            <td>Daniel</td>
            <td>Faviet</td>
        </tr>
    </tbody>
</table>

    
    Done.











### Inner Join

取共有的。

![](https://www.sqltutorial.org/wp-content/uploads/2016/03/SQL-INNER-JOIN.png)

图片来源: https://www.sqltutorial.org/wp-content/uploads/2016/03/SQL-INNER-JOIN.png

我们来看一个例子：

![](https://www.sqltutorial.org/wp-content/uploads/2016/03/emp_dept_tables.png)

图片来源：https://www.sqltutorial.org/wp-content/uploads/2016/03/emp_dept_tables.png


```sql
%%sql 

SELECT department_id, department_name 
FROM departments 
WHERE department_id IN (1, 2, 3)

SELECT e.first_name, e.last_name, e.department_id, d.department_id, d.department_name FROM employees AS e INNER JOIN departments AS d ON d.department_id = e.department_id WHERE e.department_id IN (1, 2, 3)

<table>
    <thead>
        <tr>
            <th>first_name</th>
            <th>last_name</th>
            <th>department_id</th>
            <th>department_id_1</th>
            <th>department_name</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>Jennifer</td>
            <td>Whalen</td>
            <td>1</td>
            <td>1</td>
            <td>Administration</td>
        </tr>
        <tr>
            <td>Michael</td>
            <td>Hartstein</td>
            <td>2</td>
            <td>2</td>
            <td>Marketing</td>
        </tr>
        <tr>
            <td>Pat</td>
            <td>Fay</td>
            <td>2</td>
            <td>2</td>
            <td>Marketing</td>
        </tr>
        <tr>
            <td>Den</td>
            <td>Raphaely</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
        <tr>
            <td>Alexander</td>
            <td>Khoo</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
        <tr>
            <td>Shelli</td>
            <td>Baida</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
        <tr>
            <td>Sigal</td>
            <td>Tobias</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
        <tr>
            <td>Guy</td>
            <td>Himuro</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
        <tr>
            <td>Karen</td>
            <td>Colmenares</td>
            <td>3</td>
            <td>3</td>
            <td>Purchasing</td>
        </tr>
    </tbody>
</table>

    
    Done.











### LEFT JOIN 

![](https://www.sqltutorial.org/wp-content/uploads/2016/03/SQL-LEFT-JOIN.png)

图片来源：https://www.sqltutorial.org/wp-content/uploads/2016/03/SQL-LEFT-JOIN.png


```sql
%%sql 

SELECT country_id, country_name 
FROM countries 
WHERE country_id IN ('US', 'UK', 'CN')

SELECT country_id, street_address, city FROM locations WHERE country_id IN (‘US’, ‘UK’, ‘CN’)

<table>
    <thead>
        <tr>
            <th>country_id</th>
            <th>street_address</th>
            <th>city</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>US</td>
            <td>2014 Jabberwocky Rd</td>
            <td>Southlake</td>
        </tr>
        <tr>
            <td>US</td>
            <td>2011 Interiors Blvd</td>
            <td>South San Francisco</td>
        </tr>
        <tr>
            <td>US</td>
            <td>2004 Charade Rd</td>
            <td>Seattle</td>
        </tr>
        <tr>
            <td>UK</td>
            <td>8204 Arthur St</td>
            <td>London</td>
        </tr>
        <tr>
            <td>UK</td>
            <td>Magdalen Centre, The Oxford Science Park</td>
            <td>Oxford</td>
        </tr>
    </tbody>
</table>

    
    Done.












```sql
%%sql 
SELECT 
    c.country_name,
    c.country_id,
    l.country_id,
    l.street_address,
    l.city 
FROM 
    countries AS c 
LEFT JOIN locations AS l on l.country_id = c.country_id 
WHERE 
    c.country_id IN ('US', 'UK', 'CN')

待续

最后一次修改于 2024-12-07