MY-SQL(Basic)

Posted on

我想分享一些基本的DB(MYSQL)語法。之前面試有考不管是在Amazon，或Microsoft 都有考過我DB。我那時有準備筆記，但後來就不知跑哪。今天想在這篇寫關於SQL基本指令，有機會再寫高階的語法。

我住前是用MYSQL的DB軟體，你們可以選用其他的如MogoDB, Microsoft SQL server，orlacle DB等等。基本上語法都差不多，我猜的

  • Table: employee:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +----+-------+--------+--------------+
    | id | Name  | Salary | DepartmentID |
    +----+-------+--------+--------------+
    |  1 | Joe   | 85000  | 1            |
    |  2 | Henry | 80000  | 2            |
    |  3 | Sam   | 60000  | 1            |
    |  4 | Max   | 90000  | 1            |
    |  5 | Janet | 69000  | 1            |
    |  6 | Randy | 85000  | 1            |
    +----+-------+--------+--------------+

SELECT & FROM

select * from

select 可以選擇要印那一欄，如果加*可以把所有內如印出來，以下是指令:

select * from employee;

1
2
3
4
5
6
7
8
9
10
11
Table: employee;
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  1 | Joe   | 85000  | 1            |
|  2 | Henry | 80000  | 2            |
|  3 | Sam   | 60000  | 1            |
|  4 | Max   | 90000  | 1            |
|  5 | Janet | 69000  | 1            |
|  6 | Randy | 85000  | 1            |
+----+-------+--------+--------------+

select Name 指定欄位

SELECT Name, Salary From Employee;

output:

1
2
3
4
5
6
7
8
9
10
+-------+--------+
| Name  | Salary |
+-------+--------+
| Joe   | 85000  |
| Henry | 80000  |
| Sam   | 60000  |
| Max   | 90000  |
| Janet | 69000  |
| Randy | 85000  |
+-------+--------+

DISTINCT

SELECT DISTINCT DepartmentID From Employee;

1
2
3
4
5
6
+--------------+
| DepartmentID |
+--------------+
| 1            |
| 2            |
+--------------+

WHERE

Where & And

1
2
SELECT * From Employee
where DepartmentID =2 AND Salary > 50000;

output:

1
2
3
4
5
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  2 | Henry | 80000  | 2            |
+----+-------+--------+--------------+

Where & or

1
2
SELECT * From Employee
where Salary < 70000 OR Salary >80000;

output:

1
2
3
4
5
6
7
8
9
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  1 | Joe   | 85000  | 1            |
|  3 | Sam   | 60000  | 1            |
|  4 | Max   | 90000  | 1            |
|  5 | Janet | 69000  | 1            |
|  6 | Randy | 85000  | 1            |
+----+-------+--------+--------------+

Where & IN

1
2
SELECT * From Employee
where Salary < 70000 and Salary < 85000;

output

1
2
3
4
5
6
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  3 | Sam   | 60000  | 1            |
|  5 | Janet | 69000  | 1            |
+----+-------+--------+--------------+

也可以這樣用

1
2
SELECT * From Employee
where Salary IN (70000 , 85000);

output

1
2
3
4
5
6
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  1 | Joe   | 85000  | 1            |
|  6 | Randy | 85000  | 1            |
+----+-------+--------+--------------+

Where & like

1
2
SELECT * From Employee
where Name Like "%an%;

output

1
2
3
4
5
6
+----+-------+--------+--------------+
| id | Name  | Salary | DepartmentID |
+----+-------+--------+--------------+
|  5 | Janet | 69000  | 1            |
|  6 | Randy | 85000  | 1            |
+----+-------+--------+--------------+

GROUP BY

Group BY

1
2
SELECT DepartmentID, Count(Name)
from Employee Group By DepartmentID;

output

1
2
3
4
5
6
+--------------+-------------+
| DepartmentID | Count(Name) |
+--------------+-------------+
| 1            |           5 |
| 2            |           1 |
+--------------+-------------+

Group BY & alias 另取新名

1
2
3
SELECT DepartmentID, Count(Name) As Number_Employee
from Employee
Group By DepartmentID;

output

1
2
3
4
5
6
+--------------+-------------+
| DepartmentID | Count(Name) |
+--------------+-------------+
| 1            |           5 |
| 2            |           1 |
+--------------+-------------+

Group BY & Aggression function

1
2
3
4
5
6
7
SELECT DepartmentID, Count(Salary) As Total,
Min(Salary) AS MinSalary,
Max(Salary) As MaxSalary,
Avg(Salary) as AvgSalary,
Sum(Salary) as SumSalary
from Employee
Group By DepartmentID;

output

1
2
3
4
5
6
+--------------+-------+-----------+-----------+-----------+-----------+
| DepartmentID | Total | MinSalary | MaxSalary | AvgSalary | SumSalary |
+--------------+-------+-----------+-----------+-----------+-----------+
| 1            |     5 | 60000     | 90000     |     77800 |    389000 |
| 2            |     1 | 80000     | 80000     |     80000 |     80000 |
+--------------+-------+-----------+-----------+-----------+-----------+

HAVING & ORDER BY

Having

1
2
3
4
SELECT DepartmentID
from Employee
Group By DepartmentID
HAVING COUNT(*) > 3;

output:

1
2
3
4
5
+--------------+
| DepartmentID |
+--------------+
| 1            |
+--------------+

ORDER BY ASC

1
2
3
SELECT Name,Salary,DepartmentID
from Employee
order by salary;

output:

1
2
3
4
5
6
7
8
9
10
+-------+--------+--------------+
| Name  | Salary | DepartmentID |
+-------+--------+--------------+
| Sam   | 60000  | 1            |
| Janet | 69000  | 1            |
| Henry | 80000  | 2            |
| Joe   | 85000  | 1            |
| Randy | 85000  | 1            |
| Max   | 90000  | 1            |
+-------+--------+--------------+

ORDER BY ASC Multiply column

1
2
3
4
SELECT Name,Salary,DepartmentID
from Employee
order by salary, DepartmentID;

output

1
2
3
4
5
6
7
8
9
10
+-------+--------+--------------+
| Name  | Salary | DepartmentID |
+-------+--------+--------------+
| Sam   | 60000  | 1            |
| Janet | 69000  | 1            |
| Henry | 80000  | 2            |
| Joe   | 85000  | 1            |
| Randy | 85000  | 1            |
| Max   | 90000  | 1            |
+-------+--------+--------------+

ORDER BY Desc

1
2
3
SELECT Name,Salary,DepartmentID
from Employee
order by salary DESC;

output

1
2
3
4
5
6
7
8
9
10
+-------+--------+--------------+
| Name  | Salary | DepartmentID |
+-------+--------+--------------+
| Max   | 90000  | 1            |
| Joe   | 85000  | 1            |
| Randy | 85000  | 1            |
| Henry | 80000  | 2            |
| Janet | 69000  | 1            |
| Sam   | 60000  | 1            |
+-------+--------+--------------+

Join

  • Table: employee:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    +----+-------+--------+--------------+
    | id | Name  | Salary | DepartmentID |
    +----+-------+--------+--------------+
    |  1 | Joe   | 85000  | 1            |
    |  2 | Henry | 80000  | 2            |
    |  3 | Sam   | 60000  | 1            |
    |  4 | Max   | 90000  | 1            |
    |  5 | Janet | 69000  | 1            |
    |  6 | Randy | 85000  | 1            |
    +----+-------+--------+--------------+

  • Table: department:

    1
    2
    3
    4
    5
    +----+------+
    | ID | name |
    +----+------+
    |  1 | IT   |
    +----+------+

Inner Join

INNER JOIN: returns rows when there is a match in both tables.

1
2
3
4
5
6
7
SELECT Employee.Id as ID,Employee.Name as Name,
Employee.salary as Salary, Employee.DepartmentID as DepartmentID,
Department.Name as DepartmentName
From Employee
INNER JOIN Department
on Employee.DepartmentId=Department.Id;

output:

1
2
3
4
5
6
7
8
9
+----+-------+--------+--------------+----------------+
| ID | Name  | Salary | DepartmentID | DepartmentName |
+----+-------+--------+--------------+----------------+
|  1 | Joe   | 85000  | 1            | IT             |
|  3 | Sam   | 60000  | 1            | IT             |
|  4 | Max   | 90000  | 1            | IT             |
|  5 | Janet | 69000  | 1            | IT             |
|  6 | Randy | 85000  | 1            | IT             |
+----+-------+--------+--------------+----------------+

Left Join

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

1
2
3
4
5
6
SELECT Employee.Id as ID,Employee.Name as Name,
Employee.salary as Salary, Employee.DepartmentID as DepartmentID,
Department.Name as DepartmentName
From Employee
LEFT JOIN Department
on Employee.DepartmentId=Department.Id;

output:

1
2
3
4
5
6
7
8
9
10
+----+-------+--------+--------------+----------------+
| ID | Name  | Salary | DepartmentID | DepartmentName |
+----+-------+--------+--------------+----------------+
|  1 | Joe   | 85000  | 1            | IT             |
|  2 | Henry | 80000  | 2            | NULL           |
|  3 | Sam   | 60000  | 1            | IT             |
|  4 | Max   | 90000  | 1            | IT             |
|  5 | Janet | 69000  | 1            | IT             |
|  6 | Randy | 85000  | 1            | IT             |
+----+-------+--------+--------------+----------------+

Right Join

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

1
2
3
4
5
6
7
SELECT Employee.Id as ID,Employee.Name as Name,
Employee.salary as Salary, Employee.DepartmentID as DepartmentID,
Department.Name as DepartmentName
From Employee
RIGHT JOIN Department
on Employee.DepartmentId=Department.Id;

output:

1
2
3
4
5
6
7
8
9
+------+-------+--------+--------------+----------------+
| ID   | Name  | Salary | DepartmentID | DepartmentName |
+------+-------+--------+--------------+----------------+
|    1 | Joe   | 85000  | 1            | IT             |
|    3 | Sam   | 60000  | 1            | IT             |
|    4 | Max   | 90000  | 1            | IT             |
|    5 | Janet | 69000  | 1            | IT             |
|    6 | Randy | 85000  | 1            | IT             |
+------+-------+--------+--------------+----------------+