数据库总结

数据库总结

一.Centos安装Mysql

1.下载mysql包。地址：https://dev.mysql.com/downloads/mysql/

进去选择配置如下：

选择

 

 

2.在centos下随便一个目录下进行解压。

tar -vxf  MySQL-5.6.44-1.el7.x86_64.rpm-bundle.tar

执行命令

groupadd  mysql

useradd -g mysql -m mysql

rpm -e mariadb-libs-1:5.5.60-1.el7_5.x86_64

rpm -ivh MySQL-server-5.6.44-1.el7.x86_64.rpm

rpm -ivh MySQL-client-5.6.44-1.el7.x86_64.rpm

rpm -ivh MySQL-devel-5.6.44-1.el7.x86_64.rpm

rpm -ivh MySQL-shared-5.6.44-1.el7.x86_64.rpm

 chown -R mysql:mysql /var/lib/mysql

/etc/init.d/mysql start

3.上述安装过程中，有任何报错直接百度。接下来进行密码更改。

随机密码在/root/.mysql_secret  里，第一次登录需要用到里面的密码。

mysql -uroot -p

4.更改密码。

直接执行set PASSWORD=password('root');

设置远程连接 grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;

 FLUSH PRIVILEGES;

5.tip:安装报错小提示。

出现mariadb错误时，直接卸载mariadb版本，然后再装mysql。查询mariadb：

rpm -qa | grep mariadb   然后卸载 rpm -e maraidb*****

出现perl(Data::Dumper) 被 MySQL-server-5.6.44-1.el7.x86_64 需要  错误时，安装如下

yum install -y perl perl-devel

yum -y install autoconf

yum install -y perl-Data-Dumper

然后再重新安装Mysql即可

如果有类似‘依赖检测失败: mariadb-libs 被 mysql-community-libs-8.0.23-1.el7.x86_64 取代’错误

执行yum remove mysql-libs

二、Mysql语句练习

近期突然有事情要加强sql练习，本文是在转载的基础上自己进行尝试练习
原文地址：
https://blog.csdn.net/fashion2014/article/details/78826299

CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
JOIN: 如果表中有至少一个匹配，则返回行！！！！！
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行！！！！！
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行！！！！
FULL JOIN: 只要其中一个表中存在匹配，就返回行！！！！！！！

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
自己的答案
SELECT s.*,a.s_score as a,b.s_score as b from student s
JOIN score a on a.s_id=s.s_id and a.c_id='01'
JOIN score b on b.s_id=s.s_id and b.c_id='02'
where a.s_score > b.s_score
原文答案
select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a 
join score b on a.s_id=b.s_id and b.c_id='01'
left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
和第一题相差不多，就不写了
这是原文答案
select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a left join score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL 
join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score<c.s_score

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.*,ROUND(AVG(a.s_score),2) as avg_score from 
student s
join score a on s.s_id = a.s_id
GROUP BY s.s_id HAVING avg_score >=60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from 
student a 
left join score b on a.s_id=b.s_id
GROUP BY a.s_id,a.s_name;
这里说一下left join，此处即便该学生没有分数，也会返回学生信息。而join只会返回匹配的行，而right join只会返回有成绩的学生信息。

6、查询"李"姓老师的数量
SELECT t.*,COUNT(t.t_id) from teacher t
where t.t_name LIKE '李%';

7、查询学过"张三"老师授课的同学的信息
SELECT s.*,t.t_name from student s
LEFT JOIN score sc on sc.s_id=s.s_id
LEFT JOIN course c on sc.c_id=c.c_id
LEFT join teacher t on c.t_id=t.t_id
where t.t_name='张三';
第二种方式:
select t.t_name,s.* from student s,score sc,course c,teacher t where t.t_id=c.t_id and sc.c_id=c.c_id and t.t_name='张三' and sc.s_id=s.s_id;
原文答案：
select a.* from 
student a 
join score b on a.s_id=b.s_id where b.c_id in(
select c_id from course where t_id =(
select t_id from teacher where t_name = '张三'));

8、查询没学过"张三"老师授课的同学的信息
select s.* from student s where s.s_id not in(select sc.s_id from score sc,course c,teacher t where t.t_id=c.t_id and sc.c_id=c.c_id and t.t_name='张三');
思路为：找到所有学过张三老师的学生id，然后从所有学生里找不在这些id里的学生，即为没学过张三的学生。

三、有关Mybatis的知识点

MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的持久层框架。

1.Mybatis一级缓存：之所以有缓存，主要是为了减少数据库的访问，如果在缓存命中，直接返回即可。所以当即增删改操作时会清空缓存，避免脏读。

一级缓存是SqlSession级别的，在springboot框架中每次访问controller都会生成一个sqlsession，并且使用完后都会执行close方法。

2.Mybatis二级缓存。二级缓存比一级缓存的作用范围大，即所有SqlSession都可以用，属于Mapper级别的(在同一个namespace下的mapper文件中，执行相同的查询SQL，第一次会去查询数据库，并写到缓存中；第二次直接从缓存中取。当执行SQL时两次查询中间发生了增删改操作，则二级缓存清空。)。二级缓存默认关闭，需要在Mybatis配置中设置。

二级缓存是建立在同一个namespace下的，每个mapper都有自己的二级缓存，如果对表的操作查询可能有多个namespace时，由于namespaceA执行了增删改，并不会通知namespaceB，数据就是错的。

3.Mybatis执行sql语句：映射的spacename + "." + 标签的id。

4.sqlsession，定义了数据库的操作方法，包括selectOne，selectList，commit等。并且非线程安全。

mybatis通过动态代理，获取dao层的全限定名、方法名(即id)，然后调用对应的mapper文件中的方法，对应SqlSession.getMapper(xxx.class)。

5.#和$都是传入参数的标识。#号对应占位符prepareStatement，执行效率更高，防止注入。是替换列值。

$是连接sql语句和传入参数，是连接语句，如"select * from xxx where id=" + "1111"，有注入风险，一般用于表名、列名的传入。

6.mybatis使用时需要提供实体类的无参构造方法，因为mybatis是执行sql语句查询时，通过无参构造函数创建对象，然后set同属性名的值。

 四、数据库小结

1.事务：是数据库操作的最小单元，是一个工作单元的一系列操作，这些操作作为一个整体，要么全执行，要么全不执行。

(1).A(原子性)：即本次执行的系列操作要么全部成功要么全部失败。

(2).C(一致性)：保证在一个事务中的多次操作的数据中间状态对其他事务不可见的，即只能看到结果值。还有就是执行前后数据要一致，比如A的100元转给B，其总额不变。

(3).I(隔离性)：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

(4).D(持久性)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即使数据库发生故障也不应该对其有任何影响。

2.索引

聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。

非聚集索引：就是给普通字段加上索引。

联合索引：就是好几个字段组成的索引，称为联合索引。联合索引遵从最左前缀原则，比如创建索引时顺序是'name','age',则查询时也需要从name开始查询，即查询的时候查询条件精确匹配索引的左边连续一列或几列。

普通索引：CREATE INDEX 索引名 ON 表名(列名);

唯一索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名) ;

不走索引的：

(1).Like这种就是%在前面的不走索引，在后面的走索引。

(2).用索引列进行计算的，不走索引。

(3).对索引列用函数了，不走索引。

(4).索引列用了!= 不走索引。

3.分库分表后ID的设置

分库分表就俩原因，要不就是单库并发太高，要不就是单库数据量太大。分完之后每个表都有自己的主键，这肯定不行，所以需要全局ID。

(1).可以设置不同的sequence，每个序列开始值不同(比如A服务器从1开始16位，B服务器从2开始16位...)，就是扩展服务器到一定数目就有点难搞了。

(2).UUID，这个不用说，就是有序性基本没有了，而且32位属实有点长。

(3).通过redis获取序列。依赖于redis，也蛮不错的。

(4).通过已知的各种算法，比如snowflake。是把一个 64 位的 long 型的 id，1个bit 是不用的(1负数0正数)，用其中的 41 bit 作为毫秒数，用10bit作为工作机器 id，12bit作为序列号。

五、三范式

一般都指的是关系型数据库的规范。

一范式：建表时保证列的原子性，即不可分割。比如有一列phone，既可以是座机，又可以是手机，应该分成两列。

二范式：在一范式的基础上，保证表的唯一性。即一张表只表示一种事物，每列和主键有依赖关系。

三范式：在二范式基础上，没有其他表中已有的除主键外的其他列，即没有传递性。

 六、MySql

1.常用的MySql数据类型有：

int或integer：大整数。

bigint：极大整数。

float：单精度浮点型。

double：双精度浮点型。

date/time/datetime：年月日/时分秒/年月日时分秒混合时间。

char：定长字符串。

varchar：变长字符串。

text：长文本数据。

char和varchar的n表示字符个数，char会进行超出截取、不满位数时补齐为空的特点，最大255。而varchar按照实际大小存储(不能超出指定n)，最大65532。

七、 一次sql优化的心酸

有一个业务需求，从excel导入数据，最多2000条，每条需要查询A表一次、B表一次、C表一次，共6000次。其中A表40万数据，B表20万数据，C表4万数据，因为业务需求没办法一次关联全部查出，必须一条一条的查。数据库是pg。

最初业务人员没说这么多数据，突然一天数据量增到这么多，导致查询非常非常慢，甚至到了7分钟处理完业务，我真的不知道为什么，pg查询一次数据这么慢。

没办法只好慢慢磨。我的方法是，先查出三个表条数，循环利用分页从A、B、C三表查出数据，然后封装到hashmap里，这样减少数据库查询并减少内存里匹配次数。并且将外层循环list转换为iterator，每匹配到一次，就remove，减少循环次数。利用这几步操作，目前时间控制在30秒内，目前满足需求。等后续再次优化。。