数据库的基本知识点

1、数据库常用命令

• 查看有哪些数据库

  show databases; //这个不是SQL语句，属于MySQL的命令
  

• 创建属于我们自己的数据库

  create database bjpowernode; //这个不是SQL语句，属于MySQL的命令
  

• 使用bjpowernode数据

  use bjpowernode; //这个不是SQL语句，属于MySQL的命令
  

• 查看当前使用的数据库中有哪些表？

  show tables; //这个不是SQL语句，属于MySQL的命令
  

• 初始化数据

  source 目录	//sql脚本的数据量太大是不能直接打开的，使用source命令导入
  

• 删除数据库

  drop database 库名;
  

2、DB DBMS SQL之间的关系

• DB：数据库
• DBMS：数据库管理系统
• SQL：结构化查询语句

DBMS -(执行)-> SQL -(操作)-> DB

3、SQL语句的分类

• DQL（数据查询语言）: 查询语句，凡是select语句都是DQL

• DML（数据操作语言）：insert delete update，对表当中的数据进行增删改

• DDL（数据定义语言）：create drop alter，对表结构的增删改

• TCL（事务控制语言）：commit提交事务，rollback回滚事务(TCL中的T是

  Transaction)

• DCL（数据控制语言）: grant授权、revoke撤销权限等

4、函数

4.1、分组函数（多行处理函数）

​ 分组函数对一组数据进行操作

​ 分组函数自动忽视null，分组函数需要分完组使用，所以可以用在having后面

max()
min()
avg()
sum()
count()

4.2、单行处理函数

单行处理函数：输入一行，输出一行，单行处理需要判断值是否为null

所有的数学表达式，但凡与null计算的结果都为null

• ifnull() 空处理函数

ifnull(sal,0)//如果sal的值未null，那么会替换成0

4.3、时间函数

timestampdiff(year,hiredate,now())

5、查询

• 内连接：两张表都是平等的没有主副之分

• 外连接：一张主表，一张副表，主要查询主表数据，顺带查询副表，当副表的数据没有和主表数据匹配上，副表自动模拟出null与之匹配

5.1、内连接

• 找出每个员工的部门名称以及工资等级

select
	e.ename,d.dname,s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal

5.2、外连接

• 找出没有员工的部门

select
	a.*
from
	dept a
left join 
	emp b
on 
	a.deptno = b.deptno
where
	b.job is null;

• 找出每个员工的部门名称、工资等级以及上级领导

select
	e.ename as '员工',d.dname,s.grade,m.ename as '领导'
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp m
on
	e.mgr = m.empno;

5.3、子查询

• 找出高于平均工资的员工信息

select
	*
from 
	emp
where
	sal > (select avg(sal) from emp);

• 找出每个部门平均薪水的薪资等级

select
	t.*,s.grade
from
(
	select
    	deptno,avg(sal) as avgsal
    from
    	emp
	group by
    	deptno
) t
join
	salgrade s
on 
	t.avgsal between s.losal and s.hisal;

• 找出每个部门薪水等级的平均值

select
	e.deptno,avg(s.grade)
from
	emp e
join
    salgrade s
on
    e.sal between s.losal and s.hisal
group by
	e.deptno;

• 找出每个员工所在的部门名称，要求显示员工名和部门名

select	e.ename,	(select d.dname from dept d where e.deptno = d.deptno) as dnamefrom	emp e;

5.4、拼接查询union

两张表拼接，两张表的查询结果列数必须一致

select ename from empunionselect dname from dept;

5.5、limit

mysql特有的，limit取出结果集中的部分数据，分页查询全靠limit

//starIndex表示起始位置，默认为0（第一条数据），length表示取几个
limit starIndex,length

select ename,sal from emp order by sal desc limit 0,5;

5.6、查询去重

distinct

select distinct job from emp;

6、创建表

表名在数据库中一般建议以：t_ 或者 tbl_开始

create table 表名(
	字段名1 数据类型，
    字段名2 数据类型，
    ...
)

6.1、创建学生表

学生信息：学号，姓名，性别，班级，生日

create table t_student(
	no bigint,	
    name varchar(255),
    sex char(1) default 1,
    classno varchar(255),
    birth char(10)
);

6.2、mysql中字段的数据类型

int		整数型
bigint	长整形
float	浮点型
char	定长字符串
varchar	可变成字符串
date	日期类型
BLOB	二进制大对象（存储图片、视频等流媒体信息）
CLOB	字符大对象（存储较大的文本，可以存储4G的字符串）
...

7、插入数据

//字段数量与值的数量要一致，且数据类型要相对应
insert into 表名（字段名1，字段名2，...） values（值1，值2，...）

//值的顺序必须和表中字段一致，且数量相同
insert into t_student values(值1...)

//一次插入多行
insert into 表名(字段名1，字段名2，...) 
values(值1，值2，...),(值1，值2，...)，(值1，值2，...)
//如：
insert into t_student(no,name,sex,classno,birth) 
values(15666,'wangwu','1','gaoSan1Ban','1999-01-01'),(15556,'wangwu','0','gaoSan1Ban','1999-02-02'),(1562,'wangwu','1','gaoSan1Ban','1999-03-03');

7.1插入学生信息

学生信息：学号，姓名，性别，班级，生日

insert into t_student(no,name,sex,classno,birth) values(15666,'wangwu','0','gaoSan1Ban','1999-01-01');

8、删除表

//当这个表存在，删除该表
drop table if exists t_student;

9、修改数据

update t_student set name = 'zhangsan',classno = 'dayi2ban' where no = 1562;

10、删除数据

delete from t_student where no = 15666;

10.1、删除大表（数据量很大）

表被截断，不可回滚，永久丢失，慎重使用

truncate table 表名

11、约束（constraint）

• 非空约束（not null）

  约束的字段不能为NULL

• 唯一约束（unique）

  约束的字段不能重复

  1. 列级：

  ​ name varchar(255) unique,

  2. 表级：

     可以多列字段具有唯一性

     在这些字段后面添加：

     unique（字段名1，字段名2…）,

• 主键约束（primary key）

  1. 自然主键：主键值最好与业务没有任何光系的自然数
  2. 业务主键：主键值和系统的业务挂钩，最好不要拿业务相关的字段作为主键。因为以后的业务一旦发生了变化，主键值可能也需要随着发生变化，但有的时候无法改变，因为变化可能导致主键重复

  //id字段自动增加，从1开始
  create table t_user(
  	id int primary key auto_increment
  );
  

• 外键约束（foreign key）

  create table t_class(
  	cno int,
      cname varchar(255),
      primary key(cno)
  );
  
  create table t_student(
  	sno int,
      sname varchar(255),
      classno in,
      primary key(sno),
      foreign key(classno) references t_class(cno)
  );
  

12、事务

提交事务commit，回滚事务rollback

四大特性：ACID

• A：原子性：事务是最小的工作单元，不可再分

• C：一致性：事务必须保证多条DML语句同时成功或同时失败

• I：隔离性：事务A与事务B之间具有隔离

• D：持久性：最终数据必须持久化到硬盘文件中，事务才算成功结束

隔离性：

1. 第一级别：读未提交（read uncommitted）

   事务A可以读取事务B未提交的数据

2. 第二级别：读已提交（read committed）

   事务读取的数据是实时更新的数据

3. 第三级别：可重复读（repeatable read）

   事务所读取的数据一直都是事务开启时的数据，即使数据被修改了，事务所读取的数据不会发生改变。事务开启读取的数据是备份数据，不能实时读取数据

4. 第四级别：序列化读/串行读

   效率低，事务需要排队

13、索引

主键和具有unique约束的字段自动会添加索引

根据主键查询效率较高，尽量根据主键检索

创建索引：

create index 索引名 on 表名(字段名);

删除索引：

drop index 索引名 on 表名(字段名);

需要加索引的条件：

• 数据量庞大
• 该字段很少进行DML操作（字段进行修改操作，索引也需要维护）
• 该字段经常出现在where语句中（经常根据哪个字段查询）

索引底层采用的数据结构是：B + Tree

​ 通过B Tree 缩小扫描范围，底层索引进行了排序、分区，索引会携带数据在表中的“物理地址，最终通过索引检索到数据之后，获取数据的物理地址，通过物理地址定位到表中的数据

索引什么时候失效：

​ 模糊查询的时候，第一通配符使用的是%，这个时候索引会失效

14、视图

作用：

视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，只对视图进行CRUD

15、三范式

• 第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分

• 第二范式：建立在第一范式基础之上，所有非主键的字段完全依赖主键，不产生部分依赖

  多对多采用三张表，关系表存储另外两张表的主键，即关系表两个外键

• 第三范式：建立在第二范式基础之上，所有非主键的字段直接依赖主键，不能产生传递依赖

  多对一采用两张表，多的加外键

  一对一设计有两种方案：

  1. 主键共享：既是主键也是外键
  2. 外键唯一：一张表加外键，并且外键需要添加唯一约束

  在实际开发中，以满足客户的需求为主，有时候会拿冗余换执行速度

16、sql语句执行顺序

select		5
    ...
from		1
    ...		
where		2
    ...	
group by	3
    ...
having		4
    ...
order by	6
    ...
limit		7
    ...;