SQL零基礎入門必知必會!

01 SQL 介紹

02 SQL 基礎語言學習

在瞭解 SQL 基礎語句使用之前,我們先講一下 表 是什麼?
一個數據庫通常包含一個或多個表。每個表由一個名字標識(例如“客戶”或者“訂單”)。表包含帶有資料的記錄(行)。

CREATE TABLE 表名稱
(
列名稱1 資料型別,
列名稱2 資料型別,
列名稱3 資料型別,
....
);
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
INSERT INTO 表名稱 VALUES (值1, 值2,....);
我們也可以指定所要插入資料的列:
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);
INSERT INTO Persons VALUES (1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');
SELECT * FROM 表名稱;
SELECT 列名稱 FROM 表名稱;
SELECT * FROM Persons;
SELECT LastName,FirstName FROM Persons;
SELECT DISTINCT 列名稱 FROM 表名稱;
SELECT LASTNAME FROM Persons;
SELECT DISTINCT LASTNAME FROM Persons;
SELECT 列名稱 FROM 表名稱 WHERE 列 運算子 值;
SELECT * FROM Persons WHERE City='Beijing';
SELECT * FROM 表名稱 WHERE 列 運算子 值 AND 列 運算子 值;
SELECT * FROM 表名稱 WHERE 列 運算子 值 OR 列 運算子 值;
INSERT INTO Persons VALUES (2, 'Adams', 'John', 'Oxford Street', 'London');
INSERT INTO Persons VALUES (3, 'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons VALUES (4, 'Carter', 'Thomas', 'Changan Street', 'Beijing');
INSERT INTO Persons VALUES (5, 'Carter', 'William', 'Xuanwumen 10', 'Beijing');
SELECT * FROM Persons;
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter';
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter';
SELECT * FROM 表名稱 ORDER BY 列1,列2 DESC;
SELECT * FROM Persons ORDER BY LASTNAME;
SELECT * FROM Persons ORDER BY ID_P,LASTNAME;
SELECT * FROM Persons ORDER BY ID_P DESC;
UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值;
UPDATE Persons SET FirstName = 'Fred' WHERE LastName = 'Wilson';
UPDATE Persons SET ID_P = 6,city= 'London' WHERE LastName = 'Wilson';
DELETE FROM 表名稱 WHERE 列名稱 = 值;
DELETE FROM Persons WHERE LastName = 'Wilson';
DELETE FROM table_name;
TRUNCATE TABLE 表名稱;
TRUNCATE TABLE persons;

11. DROP TABLE — 刪除表

DROP TABLE 語句用於刪除表(表的結構、屬性以及索引也會被刪除)。

DROP TABLE 表名稱;
drop table persons;

03 SQL 高階言語學習

SELECT 列名/(*) FROM 表名稱 WHERE 列名稱 LIKE 值;
INSERT INTO Persons VALUES (1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');
INSERT INTO Persons VALUES (2, 'Adams', 'John', 'Oxford Street', 'London');
INSERT INTO Persons VALUES (3, 'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons VALUES (4, 'Carter', 'Thomas', 'Changan Street', 'Beijing');
INSERT INTO Persons VALUES (5, 'Carter', 'William', 'Xuanwumen 10', 'Beijing');
select * from persons;
SELECT * FROM Persons WHERE City LIKE 'N%';
SELECT * FROM Persons WHERE City LIKE '%g';
SELECT * FROM Persons WHERE City LIKE '%on%';
SELECT * FROM Persons WHERE City NOT LIKE '%on%';
SELECT 列名/(*) FROM 表名稱 WHERE 列名稱 IN (值1,值2,值3);
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');
SELECT 列名/(*) FROM 表名稱 WHERE 列名稱 BETWEEN 值1 AND 值2;
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter';
SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';
SELECT 列名稱/(*) FROM 表名稱 AS 別名;
SELECT 列名稱 as 別名 FROM 表名稱;
SELECT LastName "Family", FirstName "Name" FROM Persons;
SELECT LastName "Family", FirstName "Name" FROM Persons;
create table orders (id_o number,orderno number,id_p number);
insert into orders values(1,11111,1);
insert into orders values(2,22222,2);
insert into orders values(3,33333,3);
insert into orders values(4,44444,4);
insert into orders values(6,66666,6);
select * from orders;
select * from persons p,orders o where p.id_p=o.id_p;
select 列名
from 表A
INNER|LEFT|RIGHT|FULL JOIN 表B
ON 表A主鍵列 = 表B外來鍵列;
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P = o.Id_P
ORDER BY p.LastName DESC;
SELECT 列名 FROM 表A
UNION
SELECT 列名 FROM 表B;
SELECT 列名 FROM 表A
UNION ALL
SELECT 列名 FROM 表B;
CREATE TABLE Persons_b
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
INSERT INTO Persons_b VALUES (1, 'Bill', 'Gates', 'Xuanwumen 10', 'Londo');
INSERT INTO Persons_b VALUES (2, 'John', 'Adams', 'Oxford Street', 'nBeijing');
INSERT INTO Persons_b VALUES (3, 'George', 'Bush', 'Fifth Avenue', 'Beijing');
INSERT INTO Persons_b VALUES (4, 'Thomas', 'Carter', 'Changan Street', 'New York');
INSERT INTO Persons_b VALUES (5, 'William', 'Carter', 'Xuanwumen 10', 'Beijing');
select * from persons_b;
select * from persons
UNION
select * from persons_b;
CREATE TABLE 表
(
列 int NOT NULL
);
create table lucifer (id number not null);
insert into lucifer values (NULL);
select * from persons where FirstName is not null;
select * from persons where FirstName is null;
CREATE VIEW 檢視名 AS
SELECT 列名
FROM 表名
WHERE 查詢條件;
create view persons_beijing as
select * from persons where city='Beijing';
CREATE OR REPLACE VIEW 檢視名 AS
SELECT 列名
FROM 表名
WHERE 查詢條件;
create or replace view persons_beijing as
select * from persons where lastname='Gates';
drop view persons_beijing;

04 SQL 常用函式學習

SQL 擁有很多可用於計數和計算的內建函式。

SELECT function(列) FROM 表;
SELECT AVG(列名) FROM 表名;
select avg(orderno) from orders;
select * from orders where orderno < (select avg(orderno) from orders);
SELECT COUNT(*) FROM 表名;
SELECT COUNT(DISTINCT 列名) FROM 表名;
SELECT COUNT(列名) FROM 表名;
select count(*) from persons;
select count(distinct city) from persons;
select count(city) from persons;
SELECT MAX(列名) FROM 表名;
select max(orderno) from orders;
SELECT MIN(列名) FROM 表名;
select min(orderno) from orders;
SELECT SUM(列名) FROM 表名;
select sum(orderno) from orders;
SELECT 列名A, 統計函式(列名B)
FROM 表名
WHERE 查詢條件
GROUP BY 列名A;
select lastname,count(city) from persons 
where city='Beijing'
group by lastname;
SELECT 列名A, 統計函式(列名B)
FROM table_name
WHERE 查詢條件
GROUP BY 列名A
HAVING 統計函式(列名B) 查詢條件;
select lastname,count(city) from persons 
where city='Beijing'
group by lastname
having count(city) > 1;
select upper(列名) from 表名;
select upper(lastname),firstname from persons;
select lower(列名) from 表名;
select lower(lastname),firstname from persons;
select length(列名) from 表名;
select length(lastname),lastname from persons;
select round(列名,精度) from 表名;
select round(1.1314,2) from dual;
select round(1.1351,2) from dual;
select round(1.1351,0) from dual;
select round(1.56,0) from dual;
select sysdate from 表名;
select sysdate from dual;

文章推薦

如何系統地學習資料探勘?

--

--

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
數據分析那些事

數據分析那些事

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/