数据库基础之一篇文章学习数据库！

数据库介绍

DB
数据库（database）：存储数据库的“仓库”、
DB
数据库管理系统（Database Management System）数据库是通过DBMS创建和操作的容器
SQL
结构化查询语言（Structure Query Language）:专门用来与数据库通信的语言

数据库分类

1 关系型数据库：MySql、Orcle、SqlServer、DB2
2 非关系型数据库：Redis、Hadoop、MongoDB、NoSQL等

四大特性ACID

原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。失败回滚的操作事务，数据将保持与操作之前一致，对数据没有任何影响。
一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。
隔离性（Isolation）：隔离性是指当多个用户并发访问数据库时，比如同时访问一张表，数据库每一个用户开启的事务，不能被其他事务所做的操作干扰，多个并发事务之间，应当相互隔离。
持久性（Durability）：持久性是指事务的操作，一旦提交，对于数据库中数据的改变是永久性的，即使数据库发生故障也不能丢失已提交事务所完成的改变。

主键
每个表都应该有一个主键，并且 每个表的主键唯一性
主键不能为Null

外键
如果一个字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

索引
数据库索引为了使查询数据效率快。
聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。
非聚集索引：给普通字段加上索引。
联合索引：多个字段组成的索引，称为联合索引。

基础数据库操作

创建数据库/表

CREATE DATABASE 数据库名                         
CREATE TABLE 表名（
字段1，dataType，
字段2，dataType，
字段3，dataType，
...
）

删除数据库/表

DROP DATABASE 数据库名称
DROP TABLE 表名称
TRUNCATE TABLE 表名称  //清除表中数据

ALTER 添加删除字段

ALTER TABLE 表名 ADD 字段名 datatype
ALTER TABLE 表名 DROP COLUMN 字段名 
ALTER TABLE 表名 MODIFY 字段名  datatype                                       #修改字段类型
ALTER TABLE 表名 CHANGE 字段1 字段2  BIGINT                               #修改字段名
ALTER TABLE 表名 RENAME TO 新表名                                               #修改表名

SQL 约束

NOT NULL                                                    #约束强制列不接受 NULL 值。
UNIQUE                                                        #唯一标识数据库表中的每条记录 
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

PRIMARY KEY
主键

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

FOREIGN KEY
外键

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

CHECK

CHECK 约束用于限制字段中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

DEFAULT

DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。

常用查询关键字

SHOW TABLES                                                                查询所有的表
SHOW COLUMNS FROM 表名                                        查询该表所有字段
SELECT 列 FROM 表名                                                   查询
SELECT 列1，列2 	... FROM 表名                                 多列查询
SELECT * FROM 表名                                                     全部查询

INSERT

 insert into 表名 （字段1，字段2，字段3......）values(字段值1，字段值2，字段值3......)
 insert into 表名 values(字段值1，字段值2，字段值3.......)
 insert into 表名 values(字段值1，字段值2，字段值3.....)，(字段值1,字段值2，字段值3.....)，(字段值1,字段值2，字段值3.....)；（批量添加）

UPDATE

UPDATE 表明 set  列1=值1,列2=值2，...where 条件;（不加where条件会修改所有记录）

DELETE

 detele from 表名 where 条件
 truncate table 表名;
truncate语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比delete语句块

DISTINCT

DISTINCT 关键词去重，返回唯一且不同的值。
DISTINCT 关键字与SELECT结合使用，以去掉所有重复的记录 ，仅返回唯一的记录。
SELECT DISTINCT 列 FROM  表名；

WHERE条件

SELECT 字段名 FROM 表名称 WHERE 条件

SELECT 字段名 FROM 表名称 WHERE 字段名  LIKE ‘%字段值%’

//LIKE 关键字详解
'%字段值%'                                // 将查找与该字段名下同一字段值的所有数据  
'%a'                                      // 以a结尾的数据
'a%'                                      // 以a开头的数据
'%a%'                                     // 含有a的数据
'_a_'                                     // 三位且中间字母是a的
'_a'                                      // 两位且结尾字母是a的
'a_'                                      // 两位且开头字母是a的　 
‘%[0-9]%’                                 // 含有数字的。　 
‘%[a-z]%’ 　                              //  含有小写字母的。 
‘%[!0-9]%’                               //  不含有数字的。

//通过使用 NOT 关键字，可以从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人：
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
//从"Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
//从 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'

AND 和 OR 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

IN操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

BETWEEN 操作符

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

AS

为列名称和表名称指定别名（Alias）
SELECT t.name FROM table_name AS t      //查询table_name中的name字段
SELECT t.name as n FROM table_name AS t //查询table_name中的name字段查询结果字段名为n

LIMIT

LIMIT 关键词用来检索记录的一个子集
SELECT 列 FROM 表 LIMIT 3; 返回前3条记录。
SELECT 列 FROM 表 LIMIT 2,4; 
(偏移量选择从 0开始，也就是第二个位置为第三条数据)从第2个位置开始选取4条记录。

ORDER BY

SELECT 列名 FROM 表名 ORDER BY 列名; 根据列名排序。
SELECT 列名 FROM 表名 ORDER BY 列名1,列名2..; 多列排序
注意：ORDER BY 关键字默认按照升序 ASC 对记录进行排序。
 如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

聚合函数

SELECT COUNT(*) from 表名;                                                                 # 计数, ps：为null的数据不会被统计进去
SELECT MAX(字段名) FROM 表名                                                          # 最大值
SELECT MIN(字段名) FROM 表名                                                           # 最小值
SELECT AVG(字段名) FROM 表名                                                          # 平均值
SELECT SUM(字段名) FROM 表名                                                         # 求和
SELECT FIRST(字段名) FROM 表名                                                      # 返回指定字段第一个记录的值
SELECT LAST（字段名）FROM 表名                                                    # 返回最后一个记录的值 
SELECT UCASE(字段名) FROM  表名                                                    # 字段值转换为大写
SELECT LCASE(字段名) FROM  表名                                                    # 字段值转换为小写
SELECT LEN(字段名) FROM  表名                                                        # 返回文本字段中值长度
SELECT ROUND(字段名,decimals) FROM 表名                                    #把数值字段舍入为指定的小数位数
SELECT NOW() FROM 表名                                                                  #返回当前的日期和时间
SELECT FORMAT(字段名,format) FROM 表名                                      #用于对字段的显示进行格式化。

分组GROUP BY

group by 字段名                           
按照某字段进行分组，有去重的作用，一般与聚合函数搭配使用
所有select的字段，除聚合函数中的字段，都必须在group by中出现。只要满足这个规则就可以 

分组后过滤HAVING

select count(*) from 表名 where sex = '男';                  
查询所有男生的人数（方法1---先过滤，在聚合）

select count(*) from 表名 group by sex having sex = '男';                                      
查询所有男生的人数（方法2---先分组聚合再过滤),HAVING 做分组时按什么条件过滤

联表查询JOIN

JOIN（即INNER JOIN）: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

分页

select * from 表名 limit 2；                                                              # 查询前2条数据，相当于limit  0, 7
select * from 表名 limit 2, 3;                                                            # 跳过前2条，查询其后的3条
select * from 表名 limit (页数-1) * 每页显示的条数, 每页显示的条数;          # 分页功能

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
同时，每条 SELECT 语句中的列的顺序必须相同。
SELECT column_name(s) FROM table_name1
UNION (ALL)
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 选取不同的值。如果允许重复的值，请使用 UNION ALL。
另外，UNION 结果集中的字段名总是等于 UNION 中第一个 SELECT 语句中的字段名。

列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

锁

概述
数据库为了保证数据的唯一性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制。MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

        **乐观锁**
             乐观锁一般是指用户自己实现的一种锁机制，假设认为数据一般情况下不会造成冲突，所以在数据进行提交
             更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决
             定如何去做。乐观锁的实现方式一般包括使用版本号和时间戳。

         **悲观锁**
             悲观锁一般就是我们通常说的数据库锁机制，以下讨论都是基于悲观锁。
             悲观锁主要表锁、行锁、页锁。在MyISAM中只用到表锁，不会有死锁的问题，锁的开销也很小，但是相应
             的并发能力很差。innodb实现了行级锁和表锁，锁的粒度变小了，并发能力变强，但是相应的锁的开销变
             大，很有可能出现死锁。同时inodb需要协调这两种锁，算法也变得复杂。InnoDB行锁是通过给索引上的
             索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
             表锁和行锁都分为共享锁和排他锁（独占锁），而更新锁是为了解决行锁升级（共享锁升级为独占锁）的死
             锁问题。

             innodb中表锁和行锁一起用，所以为了提高效率才会有意向锁（意向共享锁和意向排他锁）。

表级锁
开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页级锁
开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

并发事物带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况。

更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其
他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电
子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副
本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文
件，则可避免此问题

脏读（Dirty Reads）：
一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同
一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
不可重复读（Non-Repeatable Reads）：
一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读（Phantom Reads）：
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为
“幻读”。

SQL面试题：

第一题：

用一条SQL 语句 查询出每门课都大于80 分的学生姓名
student表:
name   kecheng   fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

解题思路：
DISTINCT 去重，数据唯一性
按名字分组：GROUP BY name
每门课都大于80分,则为最小分数大于80即可：min(fenshu)>80
第一种：
SELECT DISTINCT name FROM student WHERE name NOT IN （SELECT name FROM student WHERE min（fenshu）<=80）
第二种：
SELECT name FROM student GROUP BY NAME HAVING MIN(fenshu)>80

第二题：

data表：
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
如何把data表中的数据查成这样一个结果
year m1   m2   m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

解题思路：
根据年份进行分组：GROUP BY year
起别名：as m1，as m2,as m3,as m4 来区分同一类型数据
答案：
SELECT year,
(SELECT amount FROM data as d WHERE d.month=1 AND d.year=data.year LIMIT 1) as m1,
(SELECT amount FROM data as d WHERE d.month=2 AND d.year=data.year LIMIT 1) as m2,
(SELECT amount FROM data as d WHERE d.month=3 AND d.year=data.year LIMIT 1) as m3,
(SELECT amount FROM data as d WHERE d.month=4 AND d.year=data.year LIMIT 1) as m4
FROM data GROUP BY year