数据库

MySQL

表连接

全外连接:MySQL不支持,使用union来解决
union是MySQL中用来组合两个或多个select语句的结果集的一个操作符
基本语法:
```
SELECT COLUMN_NAME(S) FROM TABLE1
UNION
SELECT COLUMN_NAME(S) FROM TABLE2
```
- 使用规则:
  - union内的每个select语句必须拥有相同数量的列，并且对应列也拥有兼容的数据类型
- union会去掉重复行
- 如果不想去掉重复行：使用union all

SQL

DQL:数据查询语言:select
DML:数据操作语言,insert,update,delete
DDL:数据定义语言:create,alter,drop
DCL:数据控制语言:grant,remoke
TPL:事务控制语言,commit,rollback,savepoint

DML

插入语句

语法:

insert into 表名 (列名列表) values (对应值的列表)

列名列表可以省略，对应值输入的顺序与表的设计顺序一致，而且每列都要插入值
插入空值
- 列名列表中不写值为空的列
- 值设置为空(null)

一次插入多行记录

insert into 表名(列名列表) values (对应的列表),(对应的列表),(对应的列表)...

insert into 表名(列名列表) 子查询;

insert into emp_copy
select * from emp where DEPTNO = 10;

快速创建表(DDL)

create table 表名
as
子查询;

create table emp_copy
as
select * from emp where 1 <> 1;

更新语句

语法:

update 表名
set 列名 = 值,列名 = 值,列名 = 值,列名 = 值,列名 = 值,列名 = 值...
[where 条件]

例题:

-- 把员工的工资改为其部门的平均薪水
update emp e1,(select DEPTNO,avg(sal) avgsal from emp group by DEPTNO ) e2
set e1.SAL = e2.avgsal
where e1.DEPTNO = e2.deptno;

update  emp e1 join (select DEPTNO,avg(sal) avgsal from emp group by DEPTNO ) e2
on e1 .DEPTNO  = e2.DEPTNO
set e1.SAL = e2.avgsal;

删除语句

语法:

delete from 表名
[where 条件]

例题:

-- 3.删除工资大于所在部门平均工资的员工记录

delete  from emp 
where EMPNO  in (select EMPNO from
(select e2.EMPNO from emp e2
join (select DEPTNO,avg(sal) avgsal from emp e group by DEPTNO) a on e2.DEPTNO = a.deptno where e2.SAL > a.avgsal) b
)

delete emp
from emp join (select DEPTNO,avg(sal) avgsal from emp e group by DEPTNO) e
on emp.DEPTNO  = e.DEPTNO 
where emp.SAL > e.avgsal

截断表(DDL)

truncate table 表名

作用:删除表中所有数据

truncate与delete的区别

truncate属于DDL语句,它只能删除表中的所有记录，释放空间，使用rollback不可以回滚
delete属于DML语句，可以删除表中指定的记录，但不释放空间，使用rollback可以回滚

事务

事务也称作工作单元，是由一个或多个SQL语句组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败，目的是保证数据的一致性(与业务逻辑相一致)
事务控制语言:TCL

主要用来对组成事务的DML语句的操作结果进行确认或取消，确认也就是使DML操作生效，使用commit语句，取消也就是使DML操作失效，使用rollback语句来回滚
事物的特性:ACID
- 原子性:事务就像原子一样，不可分割
- 一致性:一旦事务完成，不管是成功(提交)还是失败(回滚)，整个系统处于一致的状态
- 隔离性:一个事务的执行不会被另一个事务所干扰
- 持久性:事务一旦提交，对事务的改变是永久的，不可以再回滚

MySQL是默认是执行事务自动提交

set autocommit = 0 禁止自动提交

set autocommit = 1 自动提交(系统默认)

隐式提交:
- 执行了DDL语句
- 执行了DCL语句
隐式回滚:
- 客户端强制退出
- 客户端连到服务器端异常
- 系统崩溃
保存点:如果在一个事务内，想要回滚到指定位置，不是回滚到事务的起始点，可以通过保存点来实现
- 设置保存点:savepoint 保存点名
- 回滚到保存点:rollback to 保存点名
- 注意:回滚到保存点，事务没有结束

数据库与数据表的管理

数据库的操作

创建数据库

create database [if not exists] 数据库名 [default charset uf8mb4];

删除数据库
```
drop database 数据库名;
```
查看有哪些数据库
```
show databases
```
显示创建数据库的语句
```
show create database 数据库名;
```
切换数据库
```
use 数据库名;
```

数据表的操作

创建数据表
```
create table 表名(
	字段名 字段类型 约束,
    字段名 字段类型 约束,
    字段名 字段类型 约束,
    ...
)engine = 数据库引擎名 default charset 编码名;
```
数据库引擎，也称为存储引擎，MySQL中数据用各种不同的技术储存在文件中，这些技术的每一种都使用不同的存储机制，索引技巧，锁定水平等。这些不同的技术以及配套的相关功能在MySQL中被称为存储引擎。

innodb存储引擎:支持外键、事务

查看数据库支持的引擎:
```
show engines
```

字段类型

整数类型
- int(integer):普通大小的整数，大约是±20多亿
- smallint(short):-32768到+32767
小数类型
- float:单精度浮点数
- double:双精度浮点数
- decimal(m,n)：定点小数,m为总长,n为小数位
字符类型
- char(n):定长字符类型，n为字符数，范围0~255
- varchar(n):变长字符类型,n为字符数，范围1~65535
- text:变长字符类型，64K
日期类型
- date:日期
- time:时间
- datetime:日期和时间
二进制数据类型
- blob:64KB

枚举类型

enum

create table stu(
	id int,
	sex enum('男','女')
);

insert into stu values(1,'男');
insert into stu values(2,'女');

集合类型

set

create  table stu2(
	id int,
	hobby set('java','玩游戏','看书')
);

insert into stu2 values(1,'java');
insert into stu2 values(2,'玩游戏');
insert into stu2 values(3,'java,看书');

select * from stu2;

约束

作用：限制每一列能写什么数据，不能写什么数据
类型
- 主键约束
- 非空约束
- 唯一约束
- 外键约束
- 检查约束
主键约束: primary key ,保证行的唯一性，要求唯一且非空
```
-- 列级定义
create table stu(
	id int primary key,
	name varchar(30)
);

-- 表级定义
create table stu(
	id int,
    name varchar(30)
    primary key(id)
)
```
- 注意:表中可以没有主键，但建议在非特殊情况下，都要设置表的主键
- 一个表中只能有一个主键
- 一个表中可以使用多个列联合作为主键，称为联合主键，但并不推荐使用
- 选择没有意义的列比较合适
非空约束：not null，该列不能为空
- 只能是列级约束，不能是表级约束
唯一约束：unique ，保证列的唯一性
- mysql中认为null是不确定值，任何两个null都是不相等的，所以在唯一约束的列上，可以有多个空值
- Oracle与MySQL相同，SqlServer唯一约束列只能有一个空
```
create table user(
	id int primary key,
	username varchar(30) unique not null,
	password varchar(30) not null
);
```

外键约束：foreign key,保证表数据引用的完整性

constraint 外键名 foreign key (列名) references 引用表名(列名)

create table class(
	id int primary key,
	name varchar(20) unique not null
);

insert into class values(1,'java7');

create table stu(

	id int,
	name varchar(30),
	classId int,
	primary key(id),
	foreign key(classId) references class(id)
);
insert into stu values(1,'Tom',1);
insert into class values(2,'java8');
insert into stu values(2,'Marry',2);
select * from stu;

注意:只有主键或唯一键列可以被其他表引用

check约束:对列的值进行限制

create table stu(
    id int primary key ,
    name varchar(30) not null,
    age int ,
    check(age >= 18)
);

create table test1(
            id int primary key,
            startDate date,
            endDate date,
            check(startDate <= endDate)
);

约束的位置:
- 列级约束：把约束写道列的定义处，例如: id int primary key
- 表级约束：把约束写道列的定义外，例如：id int,primary key(id)

默认值：

当不设置某列值的时候，使用的值

create table stu(
    id int primary key ,
    name varchar(30) not null,
    age int default '18',
    check(age >= 18)
);

自动增长

create table stu(
    id int  auto_increment,
    name varchar(30) not null,
    age int default '18',
    check(age >= 18),
    primary key(id)
);

insert into stu(id,name) values (null,'tom')

只能在非空，主键或唯一键上添加自动增长
一个表中只能有一个自动增长列
查询最后自增长的值：
```
select last_insert_id()
```

练习：

角色表(role)：id 主键自增长，name 非空唯一
用户表(user)：id 主键自增长，username 非空唯一，密码默认值：888888 非空，角色编号外键，年龄 age 不能小于18岁

修改表

列的修改

-- 添加列
alter table 表名
	add 列名 类型 默认值;
	
-- 修改列
alter table 表名
	modify 列名 类型 默认值;
	
-- 删除列
alter table 表名
	drop 列名;

约束的添加和删除

-- 添加约束
alter table 表名
	add 约束;
	
-- 删除主键
alter table 表名
	drop primary key;
	
-- 删除外键
alter table 表名
	drop foreign key 外键名;
	
-- 删除唯一约束
alter table 表名
	drop index 唯一索引名(默认为列名)

删除表

drop table 表名

重命名表

rename table 原表名 to 新表名

复制表

-- 复制表结构和数据，但是没有复制约束

create table 表名
as
子查询;

-- 复制表结构，包括约束，不复制数据
create table 表名 like 原表名;

窗口函数

窗口函数:在每一行上执行一个计算，这个计算涉及到从同一结果集中的其他行获取数据。这个“窗口”是通过定义在一个或多个列上的某种关系来确定的

常用的函数

rank():计算每个窗口内的行的排名
dense_rank():功能与rank类似，但不会跳过排名
row_number():给窗口内的每一行分配一个唯一的数字

案例

-- 查询员工姓名,工资及工资排名
select ENAME ,SAL ,rank() over(order by sal desc) `rank`
from emp ;

-- 查询员工姓名，部门编号，部门内薪水排名
select e.ENAME ,d.DEPTNO ,
rank() over(partition by d.DEPTNO order by sal desc) `rank`,
dense_rank() over(partition by d.DEPTNO order by sal desc) `dense_rank`,
row_number() over(partition by d.DEPTNO order by sal desc) `row_number`
from emp e join dept d on e.DEPTNO = d.DEPTNO ;

limit

Top N：limit N

分页: limit starindex,N

starindex:从0开始的索引号

-- pageNum 第几页 pageSize:每页最多有多少行
select *
from emp 
order by sal desc
limit (pageNum-1)*pageSize,pageSize;

子句执行顺序

from、where、group by、having、select、order by、limit、union

视图

在简单视图（单表，不能有分组函数、不能有group by)上可以进行增删改查的操作在复杂视图上不可以

索引的优缺点：

优点：
- 提高查询速度
- 加速排序和分组操作
- 可以在查询过程中使用优化隐藏
缺点
- 占用存储空间
- 影响写操作的性能
- 索引的维护成本
- 可能会带来过度优化的问题

总结

DBMS:软件，一种操作和管理数据库的大型软件
SQL：结构化的查询语言
- 分类：
  - DQL:SELECT
  - DML:数据的操作语言，insert，delete，update
  - DDL:数据定义语言，create、drop、alter
  - TCL:事务控制语言，commit，rollback，savepoint
  - DCL:数据控制语言，grant，revoke
查询
- where：对数据行的筛选
- group by：分组
  - 根据group by后列出的列名列表的值相同的分作一组，每组显示一行结果
  - 分组后，select子句中只能出现分组列和分组函数
  - group by 后列名列表的顺序无关分组
- having：对分组后的结果进行筛选
```
select * from emp having sal > 2000; -- mysql支持，相当于where 但不推荐使用
```
- order by：排序
  - order by 列名|表达式|列号|列别名
  - desc降序，asc升序
  - 多列排序：先按照前面的列排序，前面的列值相同的情况下，按照后面的列排序
- limit：求topN的问题以及分页
  - topN:limit N
  - 分页:limit startindex,N
- 表连接
  - 等值连接和非等值连接
    - 等值连接：表连接中使用等号
    - 非等值连接：表连接中不使用等号
  - 内连接和外连接
    - 内连接：只有匹配的记录会显示出来
    - 外连接：没有匹配的记录也会显示出来
      - 左外连接：left join，左边表中的所有记录至少显示一次
      - 右外连接：right join，右边表中的所有记录至少显示一次
      - 全外连接：full join(MySQL不支持，可以使用union连接)，两个表中没有匹配的记录都会至少显示一次
  - 自连接
  - 子查询
    - 非相关子查询：可以独立执行，不依赖于父查询
```
select *
from emp e
where sal > (select sal from emp where ename = 'smith')

-- 查询各个岗位中最低薪水人的姓名，薪水(多列子查询)
select ename,sal
from emp e 
where (sal,job) in (select min(sal),job from emp group by job);
```
    - 相关子查询：子查询中涉及到了父查询中的某些列，不能独立执行
      - 在select使用:
        
        -- 不使用表连接，查询出员工的姓名和部门名称 select ename,(select dname from dept where dept.deptno = e.deptno) from emp e;
      - 在where使用
        
        -- 查询比部门平均工资高的员工编号、姓名、薪水、部门平均薪水 select e.EMPNO ,e.ENAME ,SAL ,(select avg(sal) from emp where e.DEPTNO = emp.DEPTNO) from emp e where e.sal > (select avg(sal) from emp where e.DEPTNO = emp.DEPTNO);

窗口函数

-- 查询员工姓名、工资和其部门平均工资，使用窗口函数不使用分组函数
select ename,sal,DEPTNO ,avg(sal) over(partition by deptno) `平均工资`
from emp;

DML

insert

insert into 表名(列名列表) values(对应的值);

insert into 表名(列名列表) values(对应的值),(对应的值),(对应的值)...;

insert into 表名(列名列表)
子查询;

-- 创建一个新的表，结构与emp相同，表中没有数据
create table emp1 like emp;
-- 把查询到的部门编号为10的所有员工插入到上述表中
insert into emp1
(select * from emp where deptno = 10);
select * from emp1;

update

update 表名
set 列名 = 列值,列名 = 列值,列名 = 列值...
[where 条件]

delete

delete from 表名
[where 条件]
-- 删除高于本岗位平均工资的员工
delete emp
from emp join (select job,avg(sal) avgsal from emp e group by job) a
on emp.JOB = a.job
where emp.SAL > a.avgsal;

TCL：事务的控制语言
- 事务:把一个或多个DML语句组成一个整体，不可分割，要么全部都执行成功，要么全部都执行失败
- 提交:commit
- 回滚:rollback
- 设置保存点:savepoint 保存点名
- 回滚到保存点:rollback to 保存点名
- 特性：原子性，一致性，隔离性，持久性

DDL

创建数据库

create database [if not exists] 数据库名;

删除数据库
```
drop database 数据库名
```

创建表

create table 表名(
	列名 数据类型 约束 默认值 自动增长,
    列名 数据类型 约束 默认值 自动增长,
    列名 数据类型 约束 默认值 自动增长,
    ...
);

删除表
```
drop table 表名
```

修改表

alter table 表名
	add 列名 数据类型 约束 默认值 自动增长,
	modify 列名 数据类型 约束 默认值 自动增长,
	drop 列名,
	add 约束,
	drop primary key,
	drop foreign key 外键名,
	drop index 唯一索引名;

重命名表
```
rename table 原表名 新表名
```