SQL Server Management Studio

SQL Server Management Studio 是一个集成环境，用于访问、配置、管理和开发 SQL Server 的所有组件。

连接到服务器

Navicat

通常用于MySql(以下基于5.7安装包)

---

DBMS - 数据库管理系统（Database Management System）

数据库管理系统是一种可以访问数据库中数据的计算机程序。
DBMS 使我们有能力在数据库中提取、修改或者存贮信息。

---

什么是SQL (Structured Query Language)

SQL(结构化查询语言)，是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。
SQL可作用于RDBMS(关系型数据库服务系统),如 My SQL, SQL Server等。不同的 SQL 数据库程序拥有SQL 标准之外的私有扩展！

---

表

表是相关数据条目的集合，它由许多列和行组成。其中列又称为字段，行又称为一条记录

---

常用数据类型

• 数字 int float
• 日期和时间 datetime date time
• 字符串
  char 定长 最长8000个字符
  varchar 不定长 最长8000个字符
  text 不定长 最长2,147,483,647个字符
• 二进制 binary varbinary image

---

SQL语法介绍

1. SQL 对大小写不敏感！
2. SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，则不要使用引号。

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

数据操作语言 (DML)

SELECT 查询

• 从表中找出指定列或所有列

SELECT 列名称1,列名称2 FROM 表名称
SELECT * FROM 表名称

• 去重 DISTINCT

SELECT DISTINCT 列名称 FROM 表名称

• 条件 WHERE (配合条件运算符使用)

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
SELECT * FROM 表名称 WHERE 列 LIKE '%lond%' //查询所有N开头的列
SELECT * FROM 表名称 WHERE 列 NOT IN ('Adams','Carter')
SELECT * FROM 表名称 WHERE 列 NOT BETWEEN 1 AND 5

条件运算符	功能
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
NOT	配合BETWEEN,IN,LIKE等使用
BETWEEN	在某个范围内
EXISTS	存在
UNIQUE	无重复项
IN	允许查询多个值
LIKE	配合通配符搜索某种模式(类似正则)
通配符 %	替代一个或多个字符
通配符 _	仅替代一个字符
通配符 [ABC]	字符列中的任何单一字符
通配符 [^ABC]	不在字符列中的任何单一字符

• AND 和 OR 运算符

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'

• ORDER BY 排序
  默认为升序排列,需要降序时使用DESC

SELECT * FROM Persons ORDER BY Age DESC, Name ASC

• TOP 数量

SELECT TOP number (percent) 列名称 FROM 表名称

• AS 别名

SELECT 列名称 AS 别名 FROM 表名称
SELECT 列名称 FROM 表名称 AS 别名 
SELECT po.OrderID, p.Name FROM Persons AS p, Product_Orders AS po WHERE p.Name=po.OwnerName
//会互相遍历拼接两张表的内容

• JOIN
  用于根据两个或多个表中的列之间的关系，从这些表中查询数据。

SELECT po.OrderID, p.Name FROM Persons AS p JOIN Product_Orders AS po ON p.Name = po.OwnerName

• INNER JOIN 等价于JOIN

• LEFT JOIN 左表的行即使未能匹配成功,也会全部显示

• RIGHT JOIN 右表的行即使未能匹配成功,也会全部显示

• FULL JOIN 两表内容不管能否匹配成功,都会全部展示。成功匹配的会出现在同一行内。

• UNION 和 UNION ALL
  将多个表的结果合并到同个列中显示(各语句的列数必须相同)
  UNION会自动去重,UNION ALL则显示所有

SELECT 列1(s) FROM 表1
UNION
SELECT 列2(s) FROM 表2

• SELECT INTO
  用于复制并创建新表。

SELECT 列名
INTO 新表名 (IN 其他数据库名) 
FROM 旧表名

• 内置函数表达式
  如：avg()，sum()，count()等

SELECT COUNT(*) AS "total_rows" FROM employees;
+------------+
| total_rows |
+------------+
|         40 |
+------------+
1 row in set

• 日期表达式
  返回当前系统日期和时间值

SELECT CURRENT_TIMESTAMP;
//或 SELECT  GETDATE();
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2019-01-15 09:11:05 |
+---------------------+

---

INSERT 新增

• 向表中(指定列)插入一条新数据
  未指定列时即为所有列依次填入数据(除id外)

INSERT INTO 表 VALUES (值1, 值2,....)
INSERT INTO 表 (列1, 列2,...) VALUES (值1, 值2,....)

Update 修改

• 更新数据

UPDATE 表 SET 列 = 新值 WHERE 列 = 某值
UPDATE Person SET Address = 'Zhongshan', City = 'Nanjing' WHERE LastName = 'Wilson'

Delete 删除

• 删除语句
  不写where则删除表中所有行

DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM table_name

---

数据定义语言 (DDL)

CREATE DATABASE 创建数据库。

CREATE DATABASE 新数据库名

CREATE TABLE 创建数据库中的表。

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE trainings (
    employee_id INT,
    course_id INT,
    taken_date DATE,
    PRIMARY KEY (employee_id , course_id)//联合主键
);

ALTER TABLE

在已有的表中添加、修改或删除列。

ALTER TABLE 表名 ADD COLUMN 列名 数据类型
ALTER TABLE 表名 DROP COLUMN 列名
ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型

常用数据类型

• char(n) 固定长度字符串
• varchar(size) 可变长度字符串
• date 日期 精确到天
• datetime2 日期 精确到100纳秒
• bit 相当于布尔值(0 1 null)
• int 整数

SQL约束

用于限制加入表的数据的类型。可以在CREATE TABLE或ALERT TABLE时进行设置,拼接在数据类型之后。

• NOT NULL 禁止为null
• UNIQUE 每条记录必须唯一
• CHECK 用于限制列中的值的范围。
• DEFfULT 用于向列中插入默认值。
• PRIMARY KEY 主键,每个表有且仅有一个主键,且主键值不能包含NULL
• FOREIGN KEY 外键,指向另一个表中的主键

CREATE TABLE Orders
(
  Id_O int NOT NULL PRIMARY KEY,
  OrderNo int NOT NULL CHECK (OrderNo>0),
  City varchar(255) DEFAULT 'Sandnes',
  Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

• CREATE INDEX 或 CREATE UNIQUE INDEX
  用于在表内创建(唯一的)索引
  用户无法看到索引，它们只能被用来加速搜索/查询。
  更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。

CREATE INDEX 索引名 ON 表名 (列名(s)) 

DROP

删除索引/表/库

DROP TABLE 表名称
DROP DATABASE 数据库名称
TRUNCATE TABLE 表名称//只删内容不删表本身

IDENTITY / AUTO_INCREMENT

每条新增记录会自动获得递增的该值。通常为主键设置该值。
默认地，IDENTITY 的开始值是 1，每条新记录递增 1。
要规定 "P_Id" 列以 20 起始且递增 10，可以把 IDENTITY 改为 IDENTITY(20,10)

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL
)

---

其他

• VIEW 视图是可视化的表
• Date 具有一些内置的函数,不过一般用类似 >'2019-09-06 00:00:00' 的语法即可
• NULL
  未赋值的可选列中的记录会以NULL值保存。
  无法使用比较运算符来测试 NULL 值，必须使用 IS NULL 和 IS NOT NULL 操作符。
• ISNULL函数
  为了在计算中防止null值影响计算,通过该函数可以在值为null时用指定值进行替换计算

SELECT UnitPrice*ISNULL(UnitsNumber,0) FROM Products

---

SQL函数

SQL 拥有很多可用于计数和计算的内建函数。内建 SQL 函数的语法是：

SELECT 函数名(列) FROM 表

• AVG
  返回数值列的平均值。NULL 值不包括在计算中。
• COUNT
  返回匹配指定条件的行数。
• FIRST
  返回指定的字段中第一个记录的值。
• LAST
  返回指定的字段中最后一个记录的值。
• MAX
  返回一列中的最大值。NULL 值不包括在计算中。
• MIN
  返回一列中的最小值。NULL 值不包括在计算中。
• SUM
  返回数值列的总数。
• GROUP BY
  结合SUM函数，根据一个或多个列对结果集进行分组。
  例:分别输出每个Customer的OrderPrice之和

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

• HAVING
  WHERE 关键字后无法跟SUM函数,因此需要用 HAVING 代替 WHERE。
  例:找出OrderPrice之和少于2000的Customer

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

• UCASE
  把字段的值转换为大写。
• LCASE
  把字段的值转换为小写。
• MID
  用于从文本字段中提取字符。

SELECT MID(列名,开始位置[,截取长度,不填则到底]) FROM table_name

• LEN
  返回文本值的长度。
• ROUND
  用于把数值字段舍入为指定的小数位数。

SELECT ROUND(列名,小数位数) FROM table_name

• NOW / GETDATE()
  返回当前的日期和时间。
• FORMAT
  对字段的显示进行格式化。

SELECT FORMAT(column_name,format) FROM table_name
SELECT ProductName, UnitPrice, FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss') as PerDate
FROM Products

---