SQL学习一：ACID四个特性，CURD基本操作，常用关键字，常用聚合函数，五个约束，综合题

前言

• 数据库是开发中非常重要的模块，设计到SQL查询、连表查询、调优、分表、事务等。

• 在笔试中，必考，面试中，必问的一个知识点。整个的面试过程，大约会占百分之30的比例。

• 经历了大约N次的面试后，觉得需要系统学习一下数据库。

• 本篇博客就是一些简单的SQL语句、常用关键字、常用聚合函数、四个特性、五个约束等等的知识点。

• 在接下来以后，会慢慢的深入学习下去，也会深入学习一些ORM（对象关系映射）框架，比如mybatis、JPA等等。

• 这里以MySQL为主，数据库不同，聚合函数也不同。

一、SQL介绍

SQL（Structured Query Language）：结构化查询语言。SQL使我们有能力访问数据库，SQL是一种ANSI（美国国家标准化组织）的标准计算机语言。SQL对大小写不敏感，语句末可加分号。

二、SQL 分类

数据操作语言（DML）：SELECT、DELETE、INSERT INTO、UPDATE
数据定义语言（DDL）：CREATE、DROP、ALTER

三、数据库的四个特性ACID

ACID，是指在可靠数据库管理系统（DBMS）中，事务(transaction)所应该具有的四个特性：`原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）.这是可靠数据库所应具备的几个特性.下面针对这几个特性进行逐个讲解.

1. 事务的（ACID）特性是由关系数据库管理系统（RDBMS，数据库系统）来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。
   数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

2. 原子性是指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

3. 一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
   多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

4. 持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

四、DML(Data Manipulation Language)数据操纵语言：

1. SELECT 语句：

SELECT * FROM table；
SELECT LastName,FirstName FROM Persons;

2. INSERT 语句

a) 语法一：全字段添加，只加入一条语句

INSERT INTO student VALUE ('张三', 18)

b) 语法二：指定列添加（不允许为空的列必须给值，不然不能插入），可插入一条或多条。

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
INSERT INTO table_name ((列1, 列2) VALUES ("林冲","24"),("柴进","25"),("武松","26")

c) 语法三：通过查询语句进行数据源获取。

INSERT INTO table_name (列1, 列2,...) SELECT 列1, 列2,... FROM table_1WHERE ……
INSERT INTO table_name SELECT 列1, 列2,... FROM table_1WHERE ……（这样获取的数据插入到整个表中，也就是所有字段都必须有值切对应。）

3. UPDATE 语句

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

4. DELETE 语句

DELETE FROM 表名称 
WHERE 列名称 = 值

5. DISTINCT关键字：

关键词 DISTINCT 用于返回唯一不同的值。也就是去掉重复值。

SELECT DISTINCT LastName FROM Persons;

6. WHERE 关键字

操作符有（=、<>（!=）、>、<、>=、<=、AND、OR、BETWEEN、LIKE、IN、NULL、NOT IN）
SELECT * FROM Persons WHERE City =’Beijing’;
SELECT * FROM Persons WHERE Year > 1965;

i. BETWEEN 操作符
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
SELECT * FROM Persons WHERE LastName NOT BETWEEN ‘Adams’ AND ‘Carter’

7. 引号的使用

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。

8. SQL 通配符

1. 在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
2. SQL 通配符必须与 LIKE 运算符一起使用。
3. 在 SQL 中，可使用以下通配符：

9. AND & OR 运算符

SELECT * 
FROM Persons 
WHERE (FirstName=’Thomas’ OR FirstName=’William’) AND LastName=’Carter’

10. ORDER BY子句（升序ASC、倒序DESC）

SELECT Company, OrderNumber 
FROM Orders 
ORDER BY Company DESC, OrderNumber ASC。

11. GROUP BY 子句

合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

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

12. HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
根据上边的例题：查找订单总金额少于 2000 的客户。

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

13. LIMIT 关键字

LIMIT 关键字用于规定要返回的记录的数目。
语法：

SELECT column_name(s)
FROM table_name
LIMIT number

例子：

SELECT *
FROM Persons
LIMIT 5

14. IN 关键字

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

Mybatis 中 foreach标签。

<select id="findListByForEach"  parameterType="List" resultType="com.xiaobu.entity.Country">
     select * from country where id in
     <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
         #{item}
     </foreach>
</select>

foreach 说明

• item表示集合中每一个元素进行迭代时的别名，
• index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
• open表示该语句以什么开始，
• separator表示在每次进行迭代之间以什么符号作为分隔符，
• close表示以什么结束。
• collection指参数类型

15. limit

使用limt子句返回topN（对应这个问题返回的成绩前两名）如:[ limit 2 ==>从0索引开始读取2个]

四、SQL 内置函数

1. AVG()：

返回数值列的平均值，不包括null值；

2. COUNT()：

返回匹配指定条件的行数；
SELECT COUNT(Store_Name) FROM Store_Information WHERE Store_Name IS NOT NULL;
SELECT COUNT(DISTINCT Store_Name) FROM Store_Information;

3. MAX()：

返回一列中的最大值，null值不包括在计算中；
SELECT MAX(Salary) FROM Store_Information;

4. MIN()：

返回一列中的最小值，null值不包括在计算中；
SELECT MIN(Salary) FROM Store_Information;

5. SUM() ：

返回数值列的总数（总额）；
SELECT SUM(Sales) FROM Store_Information;

6. FIRST()：

返回指定的字段中第一个记录的值；
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders;

7. LAST()：

返回指定的字段中最后一个记录的值；
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders;

8. LEN()：

返回文本字段中值的长度；
SELECT LEN(City) AS LengthOfCity FROM Persons;

9. DATE时间

a) NOW() 函数

NOW 函数返回当前的日期和时间。
语法：SELECT NOW() FROM table_name
https:// www.w3school.com.cn/sql/sql_dates.asp 时间方法的案例

为了方便维护，不使用now（）函数，因为它准确到时间。

b) CURDATE()函数

返回当前的日期

c) CURTIME()

返回当前的时间

d) DATEDIFF()**

返回两个日期之间的天数

SELECT DATEDIFF(‘2008-12-30’,‘2008-12-29’) AS DiffDate
结果为 1
SELECT DATEDIFF(‘2008-12-29’,‘2008-12-30’) AS DiffDate
结果为 -1

10. FORMAT() 函数

FORMAT 函数用于对字段的显示进行格式化。
语法：SELECT FORMAT(column_name, format) FROM table_name
SELECT ProductName, UnitPrice, FORMAT(Now(),‘YYYY-MM-DD’) as PerDate
FROM Products

11. ABS()

求绝对值

五、五种约束

1. 主键约束（唯一约束）（Primay Key Coustraint）

特点：唯一性，非空性
第一种方式：先创建表后添加主键
alter table stuInfo
add constraint PK_stuNo primary key (stuNo)
第二种方式：创建表时，直接添加主键约束

2. 唯一约束（Unique Counstraint）

特点：唯一性，可以空，但只能有一个
alter table stuInfo
add constraint UQ_stuID unique(stuID)

3. 检查约束 (Check Counstraint)

alter table stuInfo
add constraint CK_stuAge check (stuAge between 15 and 40)
alter table stuInfo
add constraint CK_stuSex check (stuSex=’男’ or stuSex=’女′)

4. 默认约束 (Default Counstraint)

alter table stuInfo
add constraint DF_stuAddress default (‘地址不详’) for stuAddress

5. 外键约束 (Foreign Key Counstraint)

alter table stuInfo
add constraint FK_stuNo foreign key(stuNo) references stuinfo(stuNo)

6. 五种约束（建表创建）（加一个 NOT NULL 约束）

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE,
gender CHAR(1) CHECK(gender='男' OR gender ='女'),
seat INT UNIQUE,
age INT DEFAULT  18,
majorId INT REFERENCES major(id)
)

7. 索引创建与使用

CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL UNIQUE,
gender CHAR(1) CHECK(gender='男' OR gender ='女'),
seat INT UNIQUE,
age INT DEFAULT  18,
majorId INT REFERENCES major(id)
)

表（Student）

下面的案例都基于此表。

六、索引

索引定义：SQL Server允许用户在表中创建索引，指定按某列预先排序，从而大大提高查询速度（类似于汉语词典中按照拼音或者字画查找）。
索引作用：通过索引可以大大的提高数据库的检索速度，提高数据库的性能。

1. 索引的类型：

1. 唯一索引：唯一索引不允许两行有相同的索引值（其实也就是说唯一索引的这一列，每个值都是唯一的）；

   例如，如果在stuInfo表中的学员员身份证号(stuID) 列上创建了唯一索引，则所有学员的身份证号不能重复。

2. 主键索引：定义表主键的时候，会自动创建主键索引（其实主键索引就是唯一索引的特例），主键索引要求每一个值都是唯一的且非空。
   主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

3. 聚集索引：表中各行的物理顺序与键值的逻辑顺序相同，每个表只能有一个。
   在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，d……x，y，z就是索引的逻辑顺序，而页码1，2，3……就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。即拼音顺序较后的字（词）对应的页码也较大。如拼音“ha”对应的字(词)页码就比拼音“ba” 对应的字(词)页码靠后。

4. 非聚集索引：非聚集索引指定表的逻辑顺序，数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。
   如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。聚集索引比非聚集索引（nonclustered index）有更快的数据访问速度。例如，按笔画排序的索引就是非聚集索引，“1”画的字（词）对应的页码可能比“3”画的字（词）对应的页码大（靠后）。

提示：SQL Server中，一个表只能创建1个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引。

2. 索引的优缺点：

• 优点：加快访问速度；加强行的唯一性。
• 缺点：带索引的表在数据库中的存储需要更多的空间；
• 创建索引的原则：
  下列情况下可以使用索引：
  该列频繁用于搜索；
  该列用于对数据进行排序；
  下列情况下避免使用索引：
  列中仅仅包含几个不同的值；
  表中仅包含几行。为小型表创建索引可能不太划算

3. 报错

存在同名索引时会报错：Duplicate key name

4. 删除

ALTER TABLE student DROP INDEX index_name

5. 索引失效

索引什么时候不会生效，以下集中情况会导致索引失效：

1. 条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in吧）

2. 对于多列索引，不是使用的第一部分，则不会使用索引。

3. like的模糊查询以%开头，索引失效

4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引

5. 如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

6. Group by和having 子句的时候，也会失效

七、别名定义

有三种方式 定义别名

1. 使用 AS ，定义别名，也是推荐做法。格式：<表达式> AS <别名>
2. T-SQL 中还支持，<别名>=<表达式>(别名=表达式)。基本上我很少见到有采用这种方式为列定义别名的。
3. SQL Server 中还支持下面这样为列定义别名。
   格式：<表达式> <别名>(<表达式> 空格 <别名>）
   这种方式非常不明确，不建议使用。

八、综合题

1. 题一（单表）

现在有表reg_users（id, name, regdate）回答下面问题，

1、 统计user表中每个月注册的用户数。 不明白

SELECT create_month||'月' as month,(SELECT COUNT (*)  FROM reg_users  WHERE TO_CHAR (create_date, 'MM') = t1.create_month) total  FROM (SELECT DISTINCT TO_CHAR (create_date, 'MM') create_month FROM reg_users ORDER BY create_month) t1;

2、 统计user 表中有姓名相同的用户显示出来 并且 显示重复的个数。

SELECT name，COUNT（name） FROM reg_users GROUP BY name HAVING COUNT(name)>1

3、 列出用户名相同的记录

SELECT * FROM reg_users 
WHERE name IN 
(SELECT name FROM reg_users GROUP BY name HAVING COUNT (name) > 1);

4、 将用户名相同的记录插入samename_users表

INSERT INTO samename_users(id, name) 
VAULES(SELECT id, name FROM reg_users WHERE name IN 
(SELECT name FROM reg_users GROUP BY name HAVING COUNT (name) > 1))

2. 题二（三表）**

表一：student

1. 查询每个班的人数。
   SELECT c.name COUNT(s.s_id) FROM student s, class c, student_class sc WHERE s.s_id = sc.sc_id AND c.c_id = sc.sc_id GROUP BY c.name

2. 查询每个班级所有学生分数总和。
   SELECT c.name SUM(sc_s_code) FROM student s, class c, student_class sc WHERE s.s_id = sc.sc_id AND c.c_id = sc.sc_id GROUP BY c.name

3. 题三（单表）

表 equipments

1、 用一条SQL语句查找使用状态为“在用”和“检修”的所有设备。

SELECT * 
FROM equipments 
WHERE status IN (“在用”, “检修”)

2、 用一条SQL语句查找距离上次维护日期已经超出3个月的设备编号和设备名称。

SELECT eqid, eqname 
FROM equipments 
WHERE DATEDIFF(CURDATE(),curmtdate)>90

一般左为大，为正数，左小为负数
或者使用ABS() 为绝对值函数

3、 用一条SQL语句查找各使用部门分别拥有多少设备。

SELECT dept, COUNT(eqid) 
FROM equipments 
WHERE GROUP BY dept

4、 用一条SQL语句查找各使用部门分别拥有多少设备类型。

SELECT dept, COUNT(eqtype) 
FROM (SELECT dept, DISTINCT(eqtype) FROM equipments ) 
WHERE GROUP BY dept

5、 用一条SQL语句查找存在使用状态为“故障”设备的所有部门及其故障设备数量。

SELECT dept, COUNT(eqid) 
FROM equipments 
WHERE status=”故障”  
GROUP BY dept

4. 题四（两表）

1. 现有员工表emp，如下字段

1. 每个部门的奖金，根据奖金多少降序排序

2. 查询多于10个人的部门信息

3. 查询每个部门中奖金大于2000的人数

4. 查询员工信息和员工对应的部门信息