数据库学习记录

1、创建数据库
create database
2、删除数据库
drop database
3、数字类型
常用:INT 、FLOAT、DOUBLE
4、日期和时间类型
常用：DATE
5、字符串类型
常用：CHAR、VARCHAR
6、创建数据表

CREATE TABLE table_name (column_name column_type);

7、删除数据表

DROP TABLE table_name ;

9、关系型数据库是以表格进行数据存储的，表格由行和列组成；执行查询语句返回的结果集是一张虚拟表。
select 列名 from 表名
select 指定查询的列，from 指定查询的表

查询部分的列：
select employee_id,first_name,email from t_employees;
在生产环境中，推荐使用列名查询，尽量少用*

队列中的数据进行运算：
select employee_id,first_name,salary*12 from t_employees;
能进行运算的一定是列全部是数值

在数据库中%是占位符，而非模运算。

10、列的别名
列 as ‘列名’

11、查询结果去重
distinct 列名

12、排序查询
依据单列进行排序：
select 列名 from 表名 order by 排序列 [排序规则]
ASC 对前面排序列做升序排序
DESC 对前面排序列做降序排序

依据多列进行排序：
//查询员工的编号、名字、薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）

13、条件查询
select 列名 from 表名 where 条件
where条件 在查询结果中，筛选符合条件的查询结果，条件为布尔表达式。

13.1等值判断（=）
查询薪资是11000的员工信息（编号，名字，薪资）

注意：与java不同（==），mysql中等值判断使用=

13.2逻辑判断（and，or，not）
查询薪资是11000并且提成是0.30的员工信息（编号，名字，薪资）

13.3不等值判断(>,<,>=,<=,!=,<>)
#查询员工的薪资在6000-10000之间员工的信息（编号，名字，薪资）

13.4区间判断（between and）
#查询员工的薪资在6000-10000之间员工的信息（编号，名字，薪资）
注：在区间判断语法中，小值在前面，大值在后面，反之，得不到正确的结果。

13.5 NULL值判断（IS NULL，IS NOT NULL）
IS NULL
列名 IS NULL
IS NOT NULL
列名 IS NOT NULL

13.6 枚举查询（IN (值1，值2，值3)）
注意：in枚举查询效率较低，可通过多条件拼接

13.7模糊查询
like_(单个任意字符)
列名 like ‘张_’

%（任意长度的任意字符）
列名 like ‘张%’

注意：模糊查询只能和like关键字结合使用

14、分支结构查询
例如：查询员工信息（编号，名字，薪资，薪资级别<对应条件表达式生成>）。case end会生成一个独立的列所以最好要给一个别名。
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
else 结果
end
注意：通过使用case end 进行条件判断，每条数据对应生成一个值。
经验：类似java中的switch

15、时间查询
select 【时间函数(参数列表)】
经验：执行时间查询函数，会自动生成一张虚拟表（一行一列）

16、字符串查询
select 字符串函数([参数列表])
concat（str1,str2）
insert(str,pos,len,newstr)

17、聚合函数
select 聚合函数（列名）from 表名。
经验：对多条数据的单列进行统计，返回统计后的一行结果。
sum()
avg()
max()
min()
count()求总行数，求你这个列一共有多少行数据
注意：聚合函数会自动忽略null值。

18、分组查询
select 列名 from 表名 where 条件 group by 分组依据（列）。
关键字 group by 分组依据，必须在where之后生校。

18.1查询各个部门的总人数
18.2查询各个部门的平均工资
18.3查询各个部门各个岗位的人数
常见的问题：
注意：分组查询中，select 显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

19、分组过滤查询
select 列名 from 表名 where 条件 group by 分组列 having 过滤规则。
having过滤规则 ，过滤定义对分组后的数据进行过滤。

20、限定查询
select 列名 from 表名 limit 起始行，查询行数。
起始行：0行开始往后查询行数。

21、查询范围记录
分页查询：一页显示10条，一共查询三页。

经验：应用在分页场景中，起始行是变化的，但是一页显示的条数是不变的。

22、查询总结

在SQL中语句实际查询执行顺序：

23、子查询（作为条件判断）
select 列名 from 表名 where条件**(子查询结果)**
23.1查询大于Bruce的员工信息
注意：将子查询“一行一列”的结果作为外部查询条件，做第二次查询
子查询的到的一行一列的结果才能作为外部查询的等值判断条件

24子查询（作为枚举查询条件）

select 列名 from 表名 in(**子查询结果**)

24.1查询与名为‘king‘同一部门的员工信息
注意：将子查询“多行一列”的结果作为外部查询的枚举查询条件，做为第二次查询
24.2
查询工资高于60部门的所有人信息
大于所有就是ALL
高于部分的就是ANY

注意：当子查询结果集形式为多行单列时就可以使用ANY或者ALL关键字

25、子查询（作为一张表）

select  列名 from （**子查询的结果集**） where 条件；

将子查询“多行多列”的结果作为外部查询的第一张表，做第二次查询。
注意：**子查询作为临时表，为其赋予一个临时表名。**一定要赋予一个表名，不给的话就会报错的。

26、合并查询（了解）
select * from 表名1 union select * from 表名2
select * from 表名1 union all select * from 表名2

26.1合并两张表的结果（去除重复记录）
注意：合并结果的两张，列数必须相同，列的数据类型可以不同

26.2合并两张表的结果（保留重复记录）

27、表连接查询
select 列名 from 表1 连接方式 表2 on 连接条件

27.1内连接查询（INNER JOIN ON ）
查询所有部门员工的信息（SQL标准）

select * from t_employees 
inner join t_jobs
on t_employees.job_id=t_jobs.job_id;

MYSQL标准

select  * from t_employees where t_employees.job_id=t_jobs.job_id

经验:在mysql中，第二种方式也可以作为内连接查询，但是不符合SQL标准
而第一种属于SQL标准，与其他关系型数据库通用。

27.2三表连接查询
查询所有员工工号、名字、部门名称、部门所在国家ID

27.3、左外连接（LEFT JOIN ON）
查询所有员工信息，以及所对应的部门名称（美哟部门的员工，也在查询结果中，部门名称以NULL填充）；
注意：左外连接是以左表为主表，一次向右匹配，匹配到，返回结果。
匹配不到，则返回NULL值填充。

27.4右外连接（right join on ）
查询部门所有信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL填充）
注意：右外连接，是以右表为主表，一次向左匹配，匹配到，返回结果。
匹配不到，则返回NULL值填充。

28，DML操作【重点】
1、新增（insert）
insert into 表名（列1，列2，列3）values (值1，值2，值3)
1.1添加一条新的信息
注意：表名后的列名和values里面的值要一一对应（个数，顺序，类型）。

1、2修改（update）
update 表名 set 列1=新值，列2=新值,…where条件。
修改一条信息

注意:set后多个列名=值，绝大多数情况下都要加where条件，指定修改，否则为整表更新。

2、删除(delete)
delete from 表名 where 条件。

2.1删除一条信息
注意：删除时，如若不加where条件，删除的是整张表的数据。

3、清空整张表的数据（truncate）
turncate table 表名
注意：与delete不加where删除整张表数据不同，truncate是把表销毁，再按照原表的格式创建一张新表。

29、数据表的操作
1、数据类型
数值，日期，时间，字符串类型，对于我们约束数据类型有很大的帮助。

1、字符串类型
char
varchar

30、数据表的创建（create）
create table 表名（
列名 数据类型 【约束】
列名 数据类型 【约束】
…
+列名 数据类型【约束】//最后一列的末尾不加逗号
）[charset=utf8] //可根据需要只当表的字符编码集

31、创建表
32、数据表的修改(alter)
alter table 表名 操作；
32.1向现有表中添加一个列
#在课程基础上添加gradeId列
alter table subject add gradeId int;
32.2修改表中的列
#修改课程表中课程名称长度为10字符
alter table subject1 modify subjectName varchar(10);
注意：修改表中的某列时，也要写全列的名字，数据类型、约束。

32.3删除表中的列
alter table subject1 drop gradeId;
注意：删除列时，每次只能删除一列。

32.3修改列名
alter table subject1 change subjectHours classHours int;
注意：修改列名时，在给定列新名称时，要指定的类型和约束。

33、约束
问题:在往已创建表中新增数据时，可不可以新增两行相同列值数据？
如果可行，会有什么弊端？
33.1实体完整性约束
表中的一行数据代表一个实体(entiy)，实体完整性的作用即是标识每一行数据不重复，实体唯一。

33.1.1主键约束
primary key 唯一，标识表中的一行数据，此列的值不可重复，实体唯一。

33.1.2唯一约束
unique 唯一，标识表中的一行数据，不可重复，可以为NULL。

33.1.3自动增长列
auto_increment自动增长，给主键数值列添加自动增长，从1开始，每次加1。不能单独使用，和主键配合。

33.2域完整约束
限制列的单元格的数据正确性。

33.2.1非空约束
唯一约束的弊端允许为NULL值。但是如果此列中不允许出现NULL值。
那么就要进行非空约束；
NOT NULL非空，此列必须有值。

33.2.2默认值的约束
default 值，为列赋予默认值，当新增数据不指定值时，书写DEFAULT,以指定默认值进行填充。

33.2.3引用完整性约束
语法：CONSTRAINT 引用名 FOREIGN KRY （列名）REFERENCES 被引用表名（列名）。
详解：foreign key 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。
在删除表的时候，主表是被引用表，
从表是引用表。
注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表）,再删除主表（被引用表）。
创建的时候需要先创建主表，再创建从表。

34、约束创建整合
创建带有约束表。

35、权限管理
1、创建用户
create user 用户名 identified 密码

1.1、创建一个用户

CREATE USER 'zhanglu' IDENTIFIED BY '123';

2、授权
grant all on 数据库.表 to 用户名;

GRANT ALL ON companydb.* TO 'zhanglu';

3、撤销权限
revoke all on 数据库.表名 from 用户名；
注意：撤销权限后，账户要重新连接客户端才会生效。

REVOKE ALL ON companydb.* FROM 'zhanglu';

4、删除用户
drop user 用户名;

drop user 'zhanglu';

36、视图
视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用select语句查询数据，也可以使用insert,update,delete修改记录，视图可以使用户操作方便，并保障数据库系统安全。
37、范式
第一范式：每个属性都不可再分。
建表的时候如果不满足一范式是创建不成功的。
第二范式：

我们分析一下这张表，发现数据冗余很高。
第二范式问题背景：
1、数据插入异常：
设想一个场景，在一个学校我们开设了一个新的系，有系主任。但是还没有开始招收学生，这个时候发现插入数据的时候，由于没有学生的信息，主键是不能为空的，会产生数据插入异常。
2、数据删除异常：
设想一个场景，加入某个学生毕业了，我们需要将学生的信息删除掉，此时学校的系名和系主任也会被删除掉。
3、数据修改异常：
设想一个场景，假如某个同学转专业了，那么对应的系名，系主任都应该更换，这样更换会很繁琐。
在这样的情况下，我们需要第二范式：
2NF：在第一范式的基础上，消除了非主属性对于码的部分函数依赖。
这个概念是有点绕口
**码：**的意思是，一个表中，可以唯一决定一个元组的属性‘集合’。
比如在这张表中，通过id我们只能确定学生的姓名，系名和系主任，但是一个学生对应有多个课程，和课程对应的分数，这个时候我们必须知道学生的id和课程名称，才能确定一个元组的所有信息。
非主属性： 上面说到的码里面的属性就是主属性，包括id,课程名称。不是主属性的就是非主属性，包括姓名，系名，系主任，分数。
函数依赖： 函数y=f(x)代表给定一个x的值，y的值也是确定的。在数据表中，在属性x确定的情况下，必定能确定y的值，那就是说y函数依赖于x，写作x–y。比如：在表中，给定一个学号。必定能得到唯一的一个姓名。那就说姓名函数依赖于学号。写作学号—姓名。
完全函数依赖： 在一张表中，若x–>y,且对于x的任何一个真子集（假如属性组X包含超过一个属性的话），x->y不成立，那么我们称y对于x完全函数依赖，记作xF->y。
比如学号和课程名称-----成绩。
部分函数依赖： 如果Y函数依赖于x，但是y不完全函数依赖于X，那就叫做部分函数依赖。
比如（学号，姓名）-----姓名。
由于学号可以得到唯一的姓名，但是由于克明不能得到唯一的姓名。所以称y部分函数依赖x。

判断是否符合2NF，就是看数据表中是否存在非主属性对于码的部分函数依赖。若存在，则数据表最高只符合1NF的要求，若不存在，则符合2NF的要求。
步骤：
1、找出数据表中所有的码。（id,课程名）
2、根据第一步所得到的码，找出所有的主属性。（id和课程名）
3、数据表中，出去所有的主属性，剩下的就都是非主属性了。（姓名，系名，系主任，分数。）
4、查看是否存在非主属性对码的部分函数依赖。对于（学号，课名）->姓名，有学号->姓名，存在非主属性对码的部分依赖。

第二范式还是解决了一点问题： 数据冗余减少了，修改异常（只需要修改一个字段就可以了）。
没有解决的问题：
插入异常：如果需要新建一个系，有系主任。但是因为还没有学生，所以主键为空的，肯定是不能插入的。
删除异常：如果删除某个学生信息，这个系也就不存在了。

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。也就是说，如果存在非主属性对于码的传递函数依赖，则不符合3NF的要求。

又存在一个比较绕口的概念，
传递函数依赖： 如果Y依赖于X，Z又依赖于Y，那就说Z依赖于X。
比如：系名依赖于学号，系主任依赖于系名，那么系主任传递函数依赖于学号。不满足3NF。

看一看三范式有没有解决前面二范式留下的问题：
1、当我们想新建一个系插入，系名，系主任，这个时候在新建的系表中插入数据是没有问题的。
2、当某个学生毕业了，我们删除学生的信息，只需会删除学生的课程及分数的表和学生的信息表，对系表没有影响，系依然存在，这个时候不会出现异常。

特点是1NF，2NF，3NF特点是什么？怎么区分？
三个范式的总结：1NF就是看它是不是原子性，2NF就看它存不存在部分函数依赖，3NF就看它存不存在传递函数依赖。

38、事务
1、模拟转账
生活当中转账是转账方账户扣钱，收帐方加钱。我们用数据库操作来模拟现实转账。

CREATE TABLE account(
	id INT,
	money INT
)CHARSET=utf8;

INSERT INTO account (id,money)
VALUES(1,10000);
INSERT INTO account (id,money)
VALUES(2,1000);

SELECT * FROM account;

#模拟转账，账户1给账户2转1000，
UPDATE account SET money=money-1000  WHERE id=1;
UPDATE account SET money=money+1000 WHERE id=2;

2、模拟转账错误
上述代码在减操作后过程中出现了异常或加钱语句，会发现，减钱仍旧是成功的，而加钱失败了！
注意：每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

事务的概念
事务就是一个原子操作，是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都是成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

事务的边界
1、开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即执行事务的开始
2、结束：
1）提交：
a：显示提交：commit;
b：隐示提交：一条创建，删除的语句，正常退出（客户端退出连接）。
2）回滚：
a：显示回滚：rollback;
b：隐式回滚：非正常退出（断电，宕机），执行了创建，删除的语句，但是失败了，会为这个无效的语句执行回滚 。

事务原理
数据库会为每个客户端都维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束(commit)，才会将回滚段中的书同步到数据库。否则无论因为哪种原因失败，整个事务将回滚(rollback)。

事务的特性（ACID）
1、原子性
表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。
2、一致性
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态。
3、隔离性
事务查看数据操作时数据所处的状态，要么是另一个事务修改它之前的状态，要么是另外一事务修改它之后的状态，事务不会查看中间状态的数据。
4、持久性
持久性事务完成之后，它对于系统的影响是永久的。

事务应用
应用环境：基于增删改语句的操作结果（军返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚。
1、事务完成转账

#A账户给B账户转账
#1、开启事务
START	TRANSACTION ;|setAutoCommit=0;#禁止自动提交，setAutocommit=1;开启自动提交
#2、事务内数据操作查询语句
UPDATE account SET money=money-1000 WHERE ID=1;
UPDATE account SET money=money+1000 WHERE ID=2;
SELECT * FROM account;
#3、事务内语句都成功了，执行COMMIT,
COMMIT;
#4、事务内如果出现错误，执行ROLLBACK
ROLLBACK;

事务的隔离级别（有四个隔离级别）

1、并发情况下事务引发的问题
一般情况下，多个单元操作并发执行，会出现这么几个问题
1、脏读： A事务还没有提交，B事务就读到了A事务的结果。（破坏了隔离性）
2、不可重复读： A事务在本次事务中，对自己未操作过的数据，进行了多次读取，结果出现了不一致或者记录不存在的情况。（破坏了一致性，update和delete）
3、幻读： A事务在本次事务中，对自己未操作过的数据，进行了多次读取，第一读取时，记录不存在，第二次读取时，记录出现了。（破坏了一致性，insert）。

2、为了解决问题，我们制定了标准
为了权衡隔离和并发的矛盾，ISO定义了四个事务的隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同。

• 未提交读：最低一级别，只能保证持久性
• 已提交读：语句级别的
• 可重复读：事务级别的
• 串行化：最高级别，事务与事务完全串行化执行，毫无并发可言，性能极低。
  
  注意：这四个级别只是一个标准，各个数据库厂商，并不是完全按照这个标准来做的。
  3、实现(innoDB)
  锁机制：当一个事务在执行的时候，阻止其他事务进行操作，各个隔离级别主要表现在读取数据时加的锁和释放时机。
  MVCC机制：
  44、数据库索引