数据库使用

数据库创建

create Database test;

Query OK, 1 row affected (0.28 sec)；

创建数据表

CREATE TABLE pet(
				name VARCHAR(20),
				owner VARCHAR(20),
				species VARCHAR(20),
				sex VARCHAR(1),
				birth DATE,
				death DATE);
Query OK, 0 rows affected (0.27 sec)

查看表中的记录

select * from pet;

数据表中添加数据记录

INSERT INTO pet 
VALUES('puffball','diane','hamster','f','1999-03-30',NULL);

数据表删除数据

delete from pet where name = 'wang';

修改数据

update pet set name = 'ball' where owner = 'joke';

总结

数据库常见操作

–增加
INSERT
–删除
DELETE
–修改
UPDATE
–查询
SELECT

SHOW DATABASES；

USE world；

SHOW TABLES；

SHOW COLUMNS FROM city

Mysql 数据类型

https://www.runoob.com/mysql/mysql-data-types.html

mysql 建表约束

主键约束

能够唯一确定一张表中的一条记录，也就是通过某个字段添加约束，可以使得字段不重复且不为空。

create table user(
	id int primary key,
	name varchar(20)
);

--联合主键
--主键值都不重复
create table user(
	id int,
	name varchar(20),
	password varchar(20),
	primary key(id,name)
);

增加、删除主键约束

--使用add添加约束
alter table user add primary key(name);
--使用modify修改字段，添加约束
alter table user modify id int primary key;

alter table user drop primary key;

自增约束

管控ID的值自动增加

create table user3(
	id int primary key auto_increment,
	name varchar(20)
);
insert into user3 (name) values('zhangs');

唯一约束

• 约束修饰字段的值不可重复

alter table user add unique(name);

--删除约束
alter table user drop index name;

非空约束

• 约束修饰字段不能为空NULL

alter table user add not null(name);

默认约束

• 当插入字段的时候，如果没有传值，就会使用默认值

create table user(
	id int,
	name varchar(20),
	age int default 10 //默认约束
	);

外键约束

• 涉及到两个表：父表，子表
• 主表，副表

--班级
create table classes(
	id int primary key,
	name varchar(20)
	);

--学生表
create table students(
	id int primary key,
	name varchar(20),
	class_id int,
	foreign key(class_id) references classes(id)
	);

• 1、主表classes中没有的数据值，在副表中，是不可以使用的。
• 2、主表中的记录被副表引用，是不可以被删除的。

mysql 数据库的三大设计范式

第一范式（可不满足）

• 数据表中的字段要拆解到最小（不可分割）
• 如（地址可分解成国、省、市、街道、号）

第二范式

• 必须满足第一范式的前提下，第二范式要求，除主键外每列都必须完成依赖和主键。
• 如果要出现不完全依赖，只可能发生在联合主键的情况下。

第三范式

• 必须满足第二范式，除开主键列的其他列之间不能有依赖关系

mysql 查询练习

查询表中的几列

select 列名称，列名称 from 表名;

查询某列不重复的值

--排除重复
select distinct 列名称 from 表名;

查询数值表中的某范围之间的所有记录

select * from 表名 where 列名 between 60 and 90;
select * from 表名 where 列名 > 60 and 列名 < 90;

查询数值表中的确定所有记录

select * from 表名 where 列名 in(85, 86, 88);

查询数值表中的满足两个条件之一的记录

--例如查询student表中“95”班或者性别“女”的记录
select * from student where class = '95' or sex = '女';

以某列进行升降序进行查询

--例如查询student表中class 排序
--升序
select * from student order by class asc;
--降序
select * from student order by class desc;

以几列进行升降序进行查询

--例如查询student表中cno升序序、degree降序
select * from student order by cno asc,degree desc;

统计符合某一条件的记录数

--例如查询“95”班的学生人数
select count(*) from student where class = '95';

查询score表中的最高分学生学号和课程号

select sno,cno from score where degree = (select max(degree) from score);

查询每门课的平均成绩

-- avg()
-- 栗子🌰
select avg(degree) from socre where cno = '3-105';
select avg(degree) from socre group by cno; //group by 分组

查询score表中至少有2名学生选修的并以3开头的课程和平均分数

-- 栗子🌰
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

查询所有学生的sname、cno、degree列（从多表中查询.2张表）

-- 栗子🌰
select sname,cno,degree from student,score where student.sno = score.sno;

查询所有学生的sname、cname、degree列（从多表中查询.3张表）

-- 栗子🌰
select sname,cname,degree from student,score 
where student.sno = score.sno
and course.cno = score.cno;

查询"95031"班学生每门课的平均分

-- 栗子🌰
select cno,avg(degree) from score 
where sno in (select sno from student where class = '95301')
group by cno;

插入新的字段

1、指定列之后：

ALTER TABLE foo ADD id INT(4) AFTER column_i;

2、首列：

ALTER TABLE foo ADD dd INT(4) FIRST;

3、尾列：

ALTER TABLE foo ADD dd INT(4) ;

sql事务

事务的四大特性

• 原子性：事务是最小的单位，不可再分割。
• 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败。
• 隔离性：事务1 和 事务2 之间是具有隔离性的。
• 持久性：事务一旦结束（commit,rollback），就不可以返回。

事务开启

• 修改默认提交 set autocommit = 0；
• begin；
• start transaction；

事务手动提交：

commit;

事务手动回滚：

rollback;

事务的隔离性：

• read uncommitted; 读未提交的
  （如果有事务a 和事务b）
  a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果。 (出现脏读)
• read committed; 读已经提交的
  会出现不可重复读现象。
• repeatable read; 串行化
  会出现幻读（事务a和事务b同时操作一张表，事务a提交的数据，不能被事务b读到，就造成幻读）。
• serializable; 串行化
  当user表被其外一个事务操作的时候，其他事务里面的写操作，是不可以进行的。（会进入等待状态，等待另一方commit）

修改隔离级别？

select global transaction isolation level read uncommitted;

数据库常见问题

数据库事务

https://www.cnblogs.com/takumicx/p/9998844.html（完整的数据库事务）
定义:数据库事务是构成单一逻辑工作单元的操作集合

四个特性

• 原子性
• 一致性
• 隔离性
• 持久性

数据库索引

索引是提高数据检索速度最有效的手段

不同的数据库索引

1.单列索引
（1）普通索引,这个是最基本的索引
（2）唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值
（3）主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的
2.组合索引
即一个索引包含多个列。（满足最左前缀）
3.全文索引
文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

聚簇索引

聚簇索引。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

非聚簇索引

非聚簇索引。表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

数据库的存储引擎

常用的存储引擎InnoDB、MyISAM
1、事务安全：
InnoDB支持事务安全，MyISAM不支持。
2、存储限制：
InnoDB有64TB的存储限制，MyISAM示具体情况而定。
3、 空间使用：
InnoDB对空间使用程度较高，MyISAM对空间使用程度较低。
4、内存使用：
InnoDB对内存使用程度较高，MyISAM对内存使用程度较低。
5、插入数据的速度：
InnoDB插入数据的速度较低，MyISAM插入数据的速度较高。
6、对外键的支持：
InnoDB对外键支持情况较好，MyISAM不支持外键。
InnoDB存储特点：
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），其它存储引擎都是非事务安全表，支持行锁定和外键，MySQL5.5以后默认使用InnoDB存储引擎。
InnoDB特点： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。
如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
MyISAM存储特点：
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务，不支持外键。
MyISAM特点： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。
https://blog.csdn.net/qq_35642036/article/details/82820178（完整介绍）

关系和非关系数据库的区别

关系型数据库

• 定义：由二维表及其之间的联系所组成的一个数据组织。
  举例：mysql/oracle/sql server/sqlite
  优点：
  易于维护：表结构
  使用方便：SQL语言通用
  复杂操作：支持SQL，支持复杂查询
  缺点：
  读写性能比较差
  固定的表结构，灵活度稍欠
  硬盘I/O是一个很大的瓶颈

非关系型数据库

• 定义：一种数据结构化存储方法的集合
  举例：redis/hbase/mongoDB/CouchDB/Neo4J
  优点：
  格式灵活：存储格式可以是key,value、文档、图片形式等。
  速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘。
  高扩展性
  成本低：nosql数据库部署简单，基本都是开源软件。
  缺点：
  不提供sql支持
  无事务处理
  数据结构相对复杂，复杂查询方面稍欠。

索引的结构，为什么会用B+树,为什么不用B树

• 数据结构：
  B+树只有叶子节点存放数据，其余节点存放索引（能够存放的索引量大能够到达亿级别）
  B树每个节点都会存放索引和数据（容易增加磁盘的IO次数、并且读出的数据量大）

B树相对于红黑树的区别

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

什么时候联合索引失效

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
• is null,is not null也无法使用索引
• like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

sql如何进行优化

https://blog.csdn.net/qq_38789941/article/details/83744271

索引的数据结构分类

B+TREE

• 普通索引
• 唯一性索引：普通索引+字段取值必须唯一
• 主键自带索引
• 联合索引（多列）

HASH索引

很快、占内存、不合适做范围查找

操作知识点

排序

select …order by（可实现多列排序）
排序方向 asc升序 desc降序

过滤数据

select … where…条件（AND和OR以及IN范围查找、NOT）
where和order by混合使用，将order by放在where之后
可使用通配符过滤LIKE 配合%,_使用
% 代表一个或多个字符
_ 代表一个字符

使用正则表达式

REGEXP
'.'用来匹配一个字符
OR的匹配
REGEXP ‘1000|2000’
匹配几个字符之一
‘[123]TON’
范围匹配
‘[0-9]TON’
匹配特殊字符
\r 换页 \n 换行 \r 回车 \t 制表 \v 纵向制表

拼接字段

concat(字段，字段) AS 新的字段名

数据处理函数

LEFT() 返回串左边的字符
LENGTH() 返回串的长度
LOCATE() 找出串的一个子串
LOWER() 将串转换为小写
LTRIM() 去掉左边的空格
RIGHT() 返回串右边的字符
RTRIM() 去掉串右边的空格
SUBSTRING() 返回子串字符
UPPER() 将串转换为大写

聚集函数

AVG（） 返回某列的平均值
COUNT（） 返回某列的行数
MAX（） 返回某列的最大值
MIN（） 返回某列的最下值
SUM（） 返回某列之和
组合聚集函数即多个聚集函数一起使用

分组数据

创建分组

分组是在select 语句的GROUP BY中创建的

过滤分组

通过在GROUP BY 后加上 HAVING子句（对一些条件进行过滤）

分组和排序

GROUP BY 后可加上ORDER BY 分组的基础上进行排序显示

select子句顺序

SELECT 返回列或者表达式
FROM 从中检索数据的表
WHERE 行级过滤
GROUP BY 分组说明
HAVING 组级过滤
ORDER BY 输出排序顺序
LIMIT 要检索的行数

子查询

使用子查询进行过滤
作为计算字段使用子查询

链结表