数据库笔记

一、数据库基础知识

1、数据库：DataBase，简称DB，按照一定格式存储数据的一些文件组合

2、数据库管理系统：DataBase Management System，简称DBMS，专门用来管理数据库中的数据，可以对数据库中的数据进行增删改查

3、SQL：结构化查询语言，SQL是一套标准，程序员通过编写SQL语句，然后DBMS负责执行SQL

4、数据库中最基本的单位：表（table）

​ 任何一张表都有行和列：

​ 行（row）：称为数据/记录

​ 列（column）：称为字段，每一个字段都有字段名、数据类型、约束等属性

5、SQL语句分类：

​ DQL（Data Query Language）：数据查询语言（凡是带有select关键字的都是查询语句）

​ DML（Data Manipulation Language）：数据操纵语言（对表中的数据进行增删改）

​ insert 增

​ delete 删

​ update 改

​ DDL（Data Definition Language）：数据定义语言（对表的结构进行增删改）

​ create 新建

​ drop 删除

​ alter 修改

​ TCL（Transactional Control Language）：事务控制语言

​ commit 事务提交

​ rollback 事务回滚

​ DCL（Data Control Language）：数据控制语言

​ grant 授权

​ revoke 撤销权限

++++++

二、MySQL常用命令

1、show databases; //查看mysql中有哪些数据库

2、use 数据库名; //选择使用某个数据库

3、create database 数据库名; //创建数据库

4、show tables; //查看某个数据库下有哪些表

5、select version(); //查看mysql数据库的版本号

6、select database(); //查看当前使用的是哪个数据库

7、source sql文件的路径 //导入sql文件的数据，文件后缀是.sql，路径不能有中文，最后不用加分号

8、select * from 表名; //查看表中的所有数据，* 表示所有

9、desc 表名; //查看表的结构，desc是describe的缩写

+++++

三、SQL语句

SQL语言不区分大小写

1、DQL

注意：select 语句是永远不会进行修改操作的

select … from … where … group by … having … order by … limit …; //关键字的顺序不能颠倒

执行顺序：

1、from

2、where

3、group by

4、having

5、select

6、order by

7、limit

1.1、简单查询

1、select 字段名 from 表名; //查询一个字段

​ //查询多个字段则用 , 隔开；查询所有则用 * ；但使用 * 这种方式效率低，可读性差

2、select 字段名 as 别名 from 表名; //起别名，as关键字也可以省略

注意：只是查询时显示别名，不会改变原表字段名，select 语句是永远不会进行修改操作的

​ 如果名字中含有空格，则需要用单引号或双引号将整个名字括起来。在所有的数据库中，字符串统一使用单引号括起来，双引号在MySQL中可以使用，但在Oracle中用不了

3、字段可以参与数学运算

1.2、条件查询

select 字段名 from 表名 where 条件; //条件查询，查询出符合条件的数据

特殊条件符号：

（1）<> //不等于，与 != 一样

（2）between 数1 and 数2 //在数1和数2之间，必须遵循左小右大，两边都是闭区间

（3）is null //为null的，is not null 表示不为null的

（4）and并且 or或者 //and优先级比or高

（5）in(值1,值2,值3) //相当于多个or，not in表示不是这几个值的

（6）like //模糊查询，%表示任意多个字符，_表示任意一个字符

select name from emp where name like '%H%'	//找出name中含有H的
select name from emp where name like '%T'	//找出name中以T结尾的
select name from emp where name like '_a'	//找出name中第二个字符为a的

1.3、排序

1、select 字段名 from 表名 order by 字段名; //默认是升序，asc升序

2、select 字段名 from 表名 order by 字段名 desc; //降序

3、select 字段名 from 表名 order by 字段名1,字段名2; //按字段名1的升序排列，当字段名1相同时，按字段名2的升序排列

1.4、单行处理函数

单行处理行数又叫数据处理函数

特点：一个输入对应一个输出

1、select lower(字段名) from …; //转成小写，upper转成大写

2、select substr(字段名,起始下标,截取的长度) from …; //截取子串，注意：起始下标从1开始，没有0

3、select concat(字段名1,字段名2) from …; //字符串拼接

4、select length(字段名) from …; //取长度

5、trim() //去除空格

6、format() //设置千分位，对数字进行格式化

7、round(字段名,n) //四舍五入，如果n是0，保留到个位；n是-1保留到十位；n是1保留1位小数；以此类推

8、rand() //生成0到1之间的随机数

9、ifnull(数据,具体值) //空处理函数，将null转成一个具体值，如果“数据”为NULL，则把这个数据转为具体值

注意：在所有数据库中，只要有NULL参与的数学运算，最终结果一定是NULL

10、case … when … then … when … then … else … end

select
	name,(case job when 'A' then sal*1.5 when 'B' then sal*2 else sal end) as newsal
from 
	emp;	//当job为A是，其sal上调50%;当job为B时，其sal增加一倍；其他的不变

1.5、多行处理函数

多行处理函数又叫分组函数/聚合函数

特点：多个输入，一个输出

注意：分组函数在使用时必须先进行分组，然后才能用。如果没有对数据进行分组，默认整张表为一组

1、count() //计数

2、sum() //求和

3、avg() //平均值

4、max() //最大值

5、min() //最小值

注意：

（1）分组函数自动忽略NULL，所以不需要提前对NULL做处理

（2）count(字段名)：统计该字段下所有不为NULL的元素的总数（NULL不计入）

​ count(*)：统计表的总行数

（3）分组函数不能直接在where子句中使用,因为分组函数必须在分组后才能使用，where执行时，还未执行分组

6、分组查询

对数据先进行分组，再对每一组数据进行操作

（1）select … from … group by …;

在一条select语句中，如果有group by语句，那么select后面只能跟：参加分组的字段、分组函数。跟其他的显示无意义，且在Oracle中会报错

（2）select … from … gruop by … having …; //having可以对分组后的数据进一步过滤

注意：having不能单独使用，不能代替where，having只能和group by联合使用（优先选择where，where实在完成不了再使用having）

1.6、distinct

select distinct … from …; //把查询结果去除重复记录，如果是多个字段，则这几个字段联合去重

1.7、连接查询

多张表联合起来查询数据

两张表进行连接查询，如果没有任何限制，最终查询结果的条数是两张表条数的乘积，这种现象称为：笛卡尔积现象

按表连接的方式分类：

（1）内连接：等值连接、非等值连接、自连接

（2）外连接：左外连接（左连接）、右外连接（右连接）

（3）全连接

1、等值连接

select
	e.name,d.dname
from		
	emp e
inner join		//inner表示内部，可以省略，join用来连接表与表
	dept d
on		//on后面接连接条件
	e.deptno = d.deptno;

2、非等值连接

条件不是等量关系，可以是在某个范围

3、自连接

将一张表看成两张表

select ...
from 
	emp a
join 
	emp b
on 
	a.boss = b.name;

4、外连接

select ...
from		
	emp e
right outer join	//右链接，将join右边的表作为主表，查询出主表的全部数据，outer可以省略，left表示左连接
	dept d
on
	e.deptno = d.deptno;
	

注意：内连接没有主次之分

外连接的查询结果条数 >= 内连接的查询结果条数

5、多表连接

select ...
from
	a
join
	b
on
	...
join
	c
on
	...

1.8、子查询

select语句中嵌套select语句

select语句可以嵌套在where、from、select语句中，嵌套的select要用()括起来

1.9、union

合并查询结果集

union在进行结果集合并时，要求两个结果集的列数相同

Oracle要求两个结果集对应的列的数据类型要一致，MySQL则可以不一致

select name from emp where job = 'A'
union
select name from emp where job = 'B'

对于表连接，每连接一张新表，匹配次数满足笛卡尔积，但如果可以分成多部分，最终结果再使用union合并，效率会高一些

1.10、limit

将查询结果集的一部分取出，通常使用在分页查询中

limit startIndex, length //下标从0开始

limit n //取前n个

select ...
from ...
limit 0,5;	//也可以直接写成limit 5

注意：MySQL中，limit在order by之后执行，写也是在order by之后

+++++

2、DML

2.1、insert插入数据

insert into 表名(字段名1, 字段名2) values(值1, 值2);

1、str_to_date(‘字符串日期’, ‘日期格式’)：将varchar类型转成date类型

MySQL的日期格式：%Y 年，%m 月，%d 日，%h 时，%i 分，%s 秒

str_to_date('01-10-1999','%d-%m-%Y')	//如果字符串是按照'年-月-日'的格式，则可以不写str_to_date这个函数，因为系统会自动进行类型转换

2、date_format(‘date类型日期’, ‘日期格式’)：将date类型转成具有一定格式的varchar类型

如果不使用date_format函数，在显示时系统会自动将date转成varchar，且采用默认格式：%Y-%m-%d

3、now()：获取系统的当前时间，并且有时分秒，是datetime类型

insert into 表名(字段名1, 字段名2) values(…), (…), (…); //一次插入多条记录

2.2、update更新数据

update 表名 set 字段名1=值1，字段名2=值2 where 条件;

注意：如果没有条件限制，会导致所有数据都更新

2.3、delete删除数据

delete from 表名 where 条件;

注意：如果没有条件，整张表的数据会全部删除

delete语句的原理：表中的数据被删除，但这些数据在硬盘上的真实存储空间不会被释放

缺点：删除效率比较低

优点：支持回滚，可以恢复数据

truncate table 表名; //删除表中数据，truncate属于DDL操作

缺点：不支持回滚

优点：速度快，适合用于删除数据非常多的大表

++++++

3、DDL

3.1、create建表

create table 表名(字段名1, 数据类型, 字段名2, 数据类型);

MySQL中的数据类型：

数据类型
varchar	可变长度的字符串，最长225个字符，动态分配空间，速度较慢
char	定长字符串，最长225个字符，速度较快，可能会导致空间浪费
int	整型，最长11位
bigint	长整型
float	单精度浮点型
double	双精度浮点型
date	短日期类型，只包括年月日
datetime	长日期类型，包括年月日时分秒
clob	字符大对象，最多4G的字符串，可用于存储文章
blob	二进制大对象，专门用于存储图片、声音、视频等流媒体数据

create table 表名2 as select * from 表名1; //快速创建表，复制表1。也可以复制表的部分查询结果

3.2、drop删除表

drop table if exists 表名;

+++++

四、约束constraint

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性

约束的分类

非空约束：not null

唯一性约束：unique

主键约束：primary key （简称PK）

外键约束：foreign key （简称FK）

检查约束：check （MySQL不支持，Oracle支持）

1、非空约束 not null

create table stu{
   id int not null,
   name varchar(255)
};

2、唯一性约束 unique

唯一性约束的字段不能重复，但可以为NULL

create table stu{
	name varchar(255),
	email varchar(255),
	unique(name,email)	//联合唯一性，表级约束，name和email两个字段只要有一个字段不一样就可以，例如name一样，email不一样
};

注意：在MySQL中，如果一个字段同时被not null 和unique约束，则该字段自动变成主键字段；但在Oracle中不一样

3、主键约束 primary key

主键的特征：主键值不能为NULL，也不能重复

两个字段联合起来作主键也称为复合主键（在实际开发中不建议使用复合主键，建议使用单一主键）

注意：一张表的主键约束只能有一个，主键值一般是定长的，使用一般不用varchar作主键

自然主键：主键值是一个自然数

业务主键：主键值和业务紧密关联，例如手机号

在实际开发中，自然主键比较多，因为主键只要做到不重复就行，一般不需要有意义

在MySQL中，有一种机制可以帮助我们自动维护一个主键值

create table stu{
	id int primary key auto_increment,	//auto_increment表示从1开始自增，这样在添加数据时，可以省略id这个主键，系统会自动添加
	name varchar(255)
};

4、外键约束 foreign key

create table t_class{
	classno int primary key,
	classname varchar(255)
};
create table t_stu{
	id int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)	//t_stu表的外键cno，引用t_class表的classno
};

如果t_stu表的cno字段没有任何约束，则可能会导致数据无效（在t_class表中没有与之对应的班级号），所以为了保证cno字段中的值是t_class表中的班级号，需要给cno字段加外键约束

注意：这样使用外键后，t_class相当于父类，t_stu相当于子类。创建表先创父，再创子；删表先删子，再删父

外键值可以为NULL

++++++

五、事务transaction

一个事务就是一个完整的业务逻辑

只有DML语句（insert、update、delete）与事务有关，其他语句与事务无关

当做某件事时，需要多条DML语句都执行成功才算完成，那么就需要事务来保证数据安全。所以本质上，一个事务就是多条DML语句同时成功或同时失败

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中

1、在事务的执行过程中，我们可以提交事务，也可以回滚事务

提交事务commit：清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。提交事务标志着事务的结束，并且是一种全部成功的结束

回滚事务rollback：将之前的所有DML操作全部撤销，并且清空事务性活动的日志文件。回滚事务标志着事务的结束，并且是一种全部失败的结束（注意：回滚只能回滚到上一次的提交点）

MySQL默认情况下是自动提交事务，即每执行一条DML语句就提交一次

start transaction; //关闭自动提交事务

start transaction;
...
...
rollback;	//撤销全部DML操作

start transaction;
...
...
commit;	//提交事务，这时再回滚也无法撤销之前的DML操作，因为新的提交点在这

2、事务的4个特性ACID：

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

C：一致性，在同一个事务中，所有操作必须同时成功，或者同时失败，以保证数据的一致性

I：隔离性：事务和事务之间具有一定的隔离

D：持久性，事务最终结束的一个保障，事务提交就相当于将数据保存到硬盘上

3、事务和事务之间的4种隔离级别：

（1）读未提交read uncommitted（最低隔离级别）：事务A可以读取到事务B未提交的数据（脏读现象），大多数数据库的隔离级别都是二档起步

（2）读已提交read committed：事务A只能读到事务B提交后的数据，但不可重复读取数据（例如在事务开启后，第一次读到3条数据，之后事务B继续提交，第二次读到5条数据），这种隔离级别每次读到的数据是绝对真实的。Oracle数据库默认是这个隔离级别

（3）可重复读repeatable read：事务A开启后，只要事务不结束，不管过多久，每一次在事务A读到的数据都是一致的，都是开始时的数据。MySQL数据库默认是这个隔离级别

（4）序列化/串行化serializable（最高隔离级别）：事务排队，不能并发，效率最低

4、事务语句

1、select @@tx_isolation; //查看隔离级别

2、***set global transaction isolation level 隔离级别; //设置隔离级别

set global transaction isolation level read committed;

++++++

六、索引index

索引是在数据库表的字段上添加的，是为了提高查询效率，相当于一本书的目录。一张表的一个字段可以添加索引，也可以多个字段联合起来添加索引。在MySQL中，索引的底层是B-Tree

在MySQL种，主键和unique字段都会自动添加索引

1、考虑添加索引的情况：

条件1：数据量庞大

条件2：该字段经常出现在where后面，即该字段经常被扫描

条件3：该字段很少进行DML操作，因为DML操作后，索引需要重新排序

2、索引的语法

创建索引：create index emp_name_i1 on emp(name); //给emp表的name字段添加索引，起名为emp_name_i1

删除索引：drop index emp_age_i2 on emp; //将emp表上的emp_age_i2索引对象删除

explain select * from emp where name = 'Tom';	//查看一个SQL语句是否使用了索引进行检索

3、索引失效是情况：

（1）在模糊查询中，如果查询是以%开头，则索引失效，使用全表扫描

（2）使用or时，两边都有索引才会使用索引检索

（3）使用复合索引时，没有使用最左侧的字段查找，索引失效

（4）在where中索引字段参与了运算或使用了函数，索引失效

+++++

七、视图view

视图，站在不同的角度去看同一份数据

1、视图的特点：对视图进行增删改查，会导致原表被操作

2、视图的语法

创建视图对象：create view emp_v1 as select * from emp; //注意：只有DQL语句才能以view的形式创建，即create view后面必须是DQL语句

删除视图对象：drop view emp_v1;

3、视图对象在实际开发的作用：

假设有一条非常复杂的SQL语句，且这条SQL语句在不同位置上反复使用，这就可以把这条复杂的SQL语句以视图对象的形式新建，在需要编写这条SQL语句的位置直接使用视图对象，可以简化开发，利于后期维护。

面向视图开发的时候，使用视图可以像使用table一样。视图对象存储在硬盘上

八、DBA常用命令

1、数据导出

在windows的dos命令窗口中输入：

mysqldump 数据库名>导出路径 -管理员名称 -密码

2、数据导入

需要先登陆到MySQL数据库服务器上

（1）创建数据库：create database 数据库名;

（2）使用数据库：use 数据库名;

（3）导入初始化数据库：sourse 数据库路径

+++++

九、数据库设计三范式

数据库设计范式是数据库表的设计依据，按照三范式进行设计，可以避免表中数据的冗余、空间的浪费

第一范式：要求任何一张表必须有主键，每一个字段都是原子性，不可再分

第二范式：要求所有非主键字段完全依赖主键，不要产生部分依赖

第三范式：要求所有非主键字段直接依赖主键，不要产生传递依赖

多对多设计口诀：多对多，三张表，关系表两个外键

一对多设计口诀：一对多，两张表，多的表加外键

注意：三范式是理论上的，在实际开发中，有时会拿冗余换速度，因为在sql中，表和表之间的连接次数越多，效率越低（笛卡尔积），并且有时虽然冗余但sql语句的编写难度比较低