SQL Basics Review

创建表

create table EmployeeDemograpghics
( EmployeeID int,
FirstName varchar(50),
LastName varchar(50),
Age int,
Gender varchar(50))

create table EmployeeSalary
(EmployeeId int,
JobTitle varchar(50),
Salary int)

写一条注释一条即可：添加注释（ctrl+K then ctrl+C）,取消注释（ctrl+K then ctrl+U）;多摁一次就是多一层。

选择数据

SELECT

*：everything

SELECT *
FROM [dbo].[EmployeeSalary]

TOP+数字 ：返回前几条数据

返回前5条数据，包括所有字段

SELECT TOP 5 *
FROM [dbo].[EmployeeSalary]

DISTINCT（“字段名”）：返回列中出现过的值的种类

比如性别GENDER这一列为“FFFMMFMF”，DISTINCT(GENDER)返回的就是“FM”。

SELECT DISTINCT(Gender)
FROM [dbo].[EmployeeDemograpghics]

COUNT("字段名"）:返回该字段非空值的个数

SELECT COUNT(FirstName)
FROM [dbo].[EmployeeDemograpghics]

此时，查询结果列名为“（无列名）”。

问题不大，使用AS即可。

AS+“字段名”：给查询结果字段取名

SELECT COUNT(FirstName) AS FirstNameCount
FROM [dbo].[EmployeeDemograpghics]

MAX("字段名")/MIN("字段名")/AVG("字段名")：选取该列最大值/最小值/平均值

SELECT MAX(Salary)
FROM [dbo].[EmployeeSalary]

---

WHERE

=/<>:等于/不等于

SELECT *
FROM [EmployeeDemograpghics]
WHERE [FirstName] <> 'Jim'

>/</>=/<=:大于/小于/大于等于/小于等于

SELECT *
FROM [EmployeeDemograpghics]
WHERE [Age] >= 30

AND/OR:且/或

SELECT *
FROM [EmployeeDemograpghics]
WHERE [Age] >= 30 AND [Gender]='Male'

LIKE:模糊查询，与通配符“%”“_”一起使用

%：匹配包括0个字符在内的任意字符；

_：匹配单个字符

查询姓氏中含有S的数据

SELECT *
FROM [EmployeeDemograpghics]
WHERE [LastName] LIKE '%S%'

IS NULL / IS NOT NULL:空/非空

SELECT *
FROM [EmployeeDemograpghics]
WHERE [LastName] IS NOT NULL

IN:判断是否在集合中

SELECT *
FROM [EmployeeDemograpghics]
WHERE [LastName] IN ('Halpert','Bill','Scott')

---

GROUP BY:根据一个或多个列对结果集进行分组

与聚合函数（AVG()/SUM()/COUNT()等）一起应用。

SELECT Age,COUNT('Age')
FROM [EmployeeDemograpghics]
GROUP BY Age

---

ORDER BY: 排序（默认升序）

按个数从小到大排列

SELECT Age,COUNT('Age') AS CountGender
FROM [EmployeeDemograpghics]
GROUP BY Age
ORDER BY CountGender

ASC:升序（默认）

DESC:降序

按个数从大到小排列

SELECT Age,COUNT('Age') AS CountGender
FROM [EmployeeDemograpghics]
GROUP BY Age
ORDER BY CountGender DESC

ORDER BY后面可以跟多个字段：

先按年龄升序排列

SELECT *
FROM [EmployeeDemograpghics]
ORDER BY Age

可以观察到，AGE相同时先Male后Female。

改成升序。

预期结果为第三个与第四个数据互换。

SELECT *
FROM [EmployeeDemograpghics]
ORDER BY Age ASC,Gender ASC

 

可以用数字代替列名指代字段，即为“第几列”。

上表中Age是4，Gender是5。与上方同样的查询语句，结果相同。

SELECT *
FROM [EmployeeDemograpghics]
ORDER BY 4 ASC,5 ASC

插入数据

insert into 表 Values

(字段1取值，字段2取值……），

(字段1取值，字段2取值……）;

INSERT INTO EmployeeDemograpghics VALUES
(1001, 'Jim','Halpert',30,'Male'),
(1002, 'Tim','Beasley',30,'Female'),
(1003,'Sick','Martin',31,'Female'),
(1004,'Bili','Bili',20,'Male'),
(1005, 'Michael', 'Scott', 40, 'Male'),
(1006, 'Pam', 'Beesly', 28, 'Female'),
(1007, 'Dwight', 'Schrute', 35, 'Male'),
(1008, 'Angela', 'Martin', 32, 'Female'),
(1009, 'Kevin', 'Malone', 37, 'Male');

INSERT INTO EmployeeSalary VALUES
(1001,'Receotionist', 36000),
(1002,'Salesman',63000),
(1003, 'Manager', 80000),
(1004, 'Assistant', 40000),
(1005, 'Regional Manager', 90000),
(1006, 'Sales Representative', 55000),
(1007, 'Accountant', 50000),
(1008, 'HR Specialist', 45000),
(1009, 'IT Support', 48000);

 TIPS:

可以新建一个脚本（“新建查询”），写一个select语句实时观测表内数据情况。

/*Scipt for SelectTopNRows command from SSMS*/
SELECT top(1000) [EmployeeID],
[FirstName],
[LastName],
[Age],
[Gender]

From [SQLTutorial].[dbo].[EmployeeDemograpghics]

? SQL Server 中为了字段、表名等SQL Server Object名字的正确性，总是加上中括号是需要养成的习惯。

本人将所有中括号删除之后会报错：
对象名 'SQLTutorial.dbo.EmployeeDemographics' 无效。

原因请见What are the advantages always square brackets sql server objects inside procedure - Microsoft Q&A