top of page

SQL简易入门Handbook

Updated: Apr 21, 2022

SQL是数据库的查询语言,也是BA/DA求职面试经常考查的内容。今天我们来学习一下,SQL基础语句。


什么是SQL?

  • SQL可以对数据库执行查询

  • SQL可以从数据库检索数据

  • SQL可以在数据库中插入记录

  • SQL可以更新数据库中的记录

  • SQL可以从数据库中删除记录

  • SQL可以创建新的数据库

  • SQL可以在数据库中创建新表

  • SQL可以在数据库中创建存储过程

  • SQL可以在数据库中创建视图

  • SQL可以对表,过程和视图设置权限


SQL常用语法

  • SELECT -从数据库中提取数据

  • WHERE - 用于过滤记录

  • UPDATE -更新数据库中的数据

  • DELETE -从数据库中删除数据

  • INSERT INTO -将新数据插入数据库

  • CREATE DATABASE -创建一个新的数据库

  • ALTER DATABASE -修改数据库

  • CREATE TABLE -创建一个新表

  • ALTER TABLE -修改表格

  • DROP TABLE -删除表格

  • CREATE INDEX -创建索引(搜索关键字)

  • DROP INDEX -删除索引


1. SELECT语句

从【数据表】中选择某些(个)【数列】:

SELECT column1, column2, ... FROM table_name;


从【数据表】中选择所有【数列】:

SELECT * FROM table_name;


从【数据表】中选择无重复值的某个【数列】:

* 在表内部,一列通常包含许多重复值;SELECT DISTINCT语句仅用于返回不同的值

SELECT DISTINCT column1, column2

FROM table_name;


2. WHERE & HAVING语句

从【数据表】中选择只满足指定条件(Condition)的【数列】:

SELECT column1, column2, ... FROM table_name WHERE condition;


HAVING:补上了WHERE无法同聚合函数一起使用:

SELECT COUNT(customerID), country FROM table_name GROUP BY country

HAVING COUNT(customerID) > 10


子语句:

  • ANY:满足任一条件,返回为TRUE

  • ALL:满足所有条件,返回为TRUE

# ALL - 查询出01班中,年龄大于 02班所有人的同学

SELECT * FROM student

WHERE class='01' AND age > ALL (

SELECT age FROM student class='02'

);

# 相当于

SELECT * FROM student

WHERE class='01' AND age > (

SELECT MAX(age) FROM student WHERE class='02'

);


# ANY - 查询出01班中,年龄大于 02班任意一个的同学

SELECT * FROM student

WHERE class='01' AND age > ANY (

SELECT age FROM student WHERE class='02'

);

# 相当于

SELECT * FROM student

WHERE class='01' AND age > (

SELECT MIN(age) FROM student WHERE class='02'

);



3. ORDER BY语句

默认升序排列,如需降序排列会用到DESC关键词

SELECT column1, column2, ...

FROM table_name

ORDER BY column1 DESC, column2 ASC;


4. INSERT INTO & INSERT INTO SELECT语句

# INSERT INFO

a.指定列名称和要插入的值:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

b. 如果要为表的所有列添加值,则无需在SQL查询中指定列名,但要确保值的顺序与表中各列的顺序相同。

INSERT INTO table_name VALUES (value1, value2, value3, ...);


# INSERT INFO SELECT

从一个表中复制数据并将其插入到另一个表中

INSERT INTO table2 (column1, column2, column3, ...)

SELECT column1, column2, column3, ...

FROM table1 WHERE condition;


5. UPDATE语句

UPDATE语句,用于修改表中的现有记录:

* 注意:WHERE语句中的子句UPDATE,该WHERE子句指定应更新的记录;如果省略该WHERE子句,表中的所有记录将被更新。

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;



6. DELETE语句

DELETE语句用于删除表中的现有记录:

* 注意 WHERE语句中的子 DELETE,该WHERE子句指定应删除的记录;如果省略该WHERE子句,表中的所有记录将被删除。

DELETE FROM table_name

WHERE condition;


7. LIMIT(SELECT TOP/FETCH)语句

从【数据表】中选择【指定行】数据:

SELECT TOP 3 FROM table_name;

SELECT * FROM table_name

LIMIT 3;


SELECT * FROM Customers FETCH FIRST 3 ROWS ONLY;


8. LIKE语句

用于WHERE子句中的筛选条件,通常配合以下符号出现:

%:用来代替零、一或多个字符

_: 用来代替一个单个字符


eg.从表中找到以a开头的客户名所在行数据

SELECT * FROM table_name WHERE CustomerName LIKE 'a%';


9. AS语句

用AS关键字创建别名,别名仅在该查询期间存在。

SELECT column_name AS alias_name FROM table_name;


别名在以下情况下很有用:

  • 查询涉及多个表

  • 在查询中使用函数

  • 列名很大或可读性很差

  • 两列或更多列合并在一起


10. JOIN语句

INNER JOIN:返回两个表中具有匹配值的记录

LEFT JOIN:返回与右表匹配的左表记录

RIGHT JOIN:返回与左表匹配的右表记录

FULL OUTER JOIN:返回与左表或右表中存在匹配项的所有记录


11. UNION语句

  • UNION中的每个SELECT语句必须具有相同的列数

  • 这些数列必须具有相似的数据类型

  • 每个SELECT语句中的数列也必须具有相同的顺序

UNION:仅显示无重复值数列;

UNION ALL:可显示所有数列

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;


12. GROUP BY语句

GROUP BY将具有相同值的行分组统计

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);


13. CASE…WHEN语句

SELECT column_name(s)

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END

FROM table_name;











4 Comments


Known for its expertise in software product development and technology services, Persistent Systems has a significant presence in Mumbai. It focuses on serving clients in areas like healthcare, software development company in mumbai finance, and retail through its innovative solutions.

Like

The production of the sp5der t-shirt aligns with ethical and responsible manufacturing practices. The brand collaborates with sp5der worldwide manufacturers who ensure safe and fair working conditions for their employees, creating a respectful work environment. This commitment goes beyond fashion it's about making a positive impact in the lives of those involved in the production process.

Like

Nivea Harini
Nivea Harini
Jul 26, 2022

Hand Mixers can have different designs, from traditional ones to a futuristic feel. Most of them have stainless steel blades because they are sturdy, efficient and easy to clean, but some models also come with three non-stick coatings, which is good for people who like to whip up desserts and need to crush ice. Hand Mixers can be a little inconvenient to use if they are not designed well. Some models are quieter than others, which is an important feature for people living in small apartments and Mixergrinderguide not disturbing their neighbors. Ideally, you should pick a unit that allows you to adjust the speed to get the consistency you want for your ingredients.

Like

lilly azar
lilly azar
Jul 07, 2022

The chances of walking away with that sweepstakes don't increment by playing every now and again, rather, you'd improve by buying more tickets for a similar drawing.In spite of the fact that there is no assurance in the securities exchange, the probability of getting a profit from your venture is obviously better New York Numbers Evening than your possibilities scoring that sweepstakes.Lottery champs have the choice to take their money in one singular amount or by spreading it out over various years through annuities.

Like
bottom of page