MySQL数据库基础篇

数据库分类

关系型数据库， Oracle、MySQL、SQLServer、Access
关系型数据库设计规则ORM思想（Object Relational Mapping）:
表对应类，一条记录row对应对象，一列对应字段、属性。
表与表有E-R(entity-relationship,实体-联系)模型中有三个主要概念是：实体集、属性、联系集。
表的关联关系：一对一、一对多、多对多、自我引用。
非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

MySQL安装

1 DBMS(Database Management System)删了DB还在，my.ini里是一些配置信息
1 运行regedit调取注册表
1 软件本身在自定义目录下，数据在C盘ProgramData
1 MySQL的bin路径放在环境变量Path下就使得cmd任意目录下都可以执行（任务管理器线程服务已开启）mysql指令了，因为mysql.exe就在bin目录下。
1 zip格式安装分两步，解压相当于服务器软件安装好了，还需要独自进行配置。
msi格式，安装和配置是一步下来的（推荐）。
2 命令行启动使用mysql -uroot -proot -hlocalhost -P13306（设置不同的端口号）来区分多个不同版本的mysql。
-u、-P、-h后面都可加空格
密码与用户名一起输入时-p后面不能加空格，如果加了会被默认为数据库的名会提示UnKnown database ’ ’
默认访问的是本机或端口号是3306可省略-h -P。
3 MySQL5.7需修改字符集为utf-8
MySQL8.0的字符集默认为utf-8且是utf8mb4
3 图形化界面工具连接mysql8出现错误：

出现这个原因是mysql8版本之前的加密规则是mysql_native_password，在mysql8之后的加密规则是caching_sha2_password。解决方法：升级图形化界面工具版本或把mysql8的用户登陆密码加密规则还原成mysql_native_password。
使用mysql数据库：
use mysql;
修改密码规则：
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;
刷新权限：
FLUSH PRIVILEGES;

MySQL数据库事务

1 基于注解@Transactional的属性(readOnly=true)表示只读，
(timeout = -1)表示永不超时，超时时间单位是秒。
(noRollbackFor = ArithmeticException.class)表示此异常不回滚
(propagation=Propagation.REQUIRED)没有就新建，有就加入
(propagation=Propagation.REQUIRES_NEW)直接新建事务
2 事务的隔离性是指事务之间不产生影响。如果不考虑隔离性会产生三个读的问题：脏读、不可重复读、虚读（幻读） 。
脏读指的是两个事务都没有提交但是它互相改数据都能看到。
不可重复读就是一个事务没有提交，另一个事务修改之后已经提交了，然后它能读到它修改之后的数据。
幻读表示一个事务没有提交，一个事务提交了但是它做了添加，把它添加的数据可以读到。

数据库常用操作

建库create database cgb2022 DEFAULT CHARACTER SET utf8;
删库drop database cgb2022;
查看所有数据库show databases;
表的常用操作：使用数据库：use cgb2022;
创建tb_door表，有id,door_name,tel字段
create table tb_door( id int primary key auto_increment, door_name varchar(100), tel varchar(50) );
修改表：添加列
alter table tb_door add column money NUMERIC(7,2)
删除表drop table tb_door;

查看所有表show tables;
查看表结构/设计表desc tb_door;

表记录的常用操作

插入记录
insert into tb_door values(null,'永和大王1店',666); insert into tb_door values(null,' 永和大王2店',888);
查询记录SELECT * FROM tb_door;
修改记录update tb_door set tel=555 where id=1;
删除记录Delete from tb_door where id=2;
排序Select * from tb_door order by tel desc;
记录总数Select count(*) from tb_door;
1.数据库字段以key命名时用在sql语句中时用` `引住。
2.数据库权限表path对应到前端请求

数据类型

命名规则：
字段名必须以字母开头，尽量不要使用拼音
长度不能超过30个字符（不同数据库，不同版本会有不同）
不能使用SQL的保留字，如where,order,group
只能使用如下字符az、AZ、0~9、$ 等
Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
多个单词用下划线隔开，而非java语言的驼峰规则
字符：
char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
大文本: 大量文字（不推荐使用，尽量使用varchar替代）
以utf8编码计算的话，一个汉字在u8下占3个字节
数字：
tinyint,int整数类型
float,double小数类型
numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
decimal和numeric表示精确的整数数字
日期：
date 包含年月日
time时分秒
datetime包含年月日和时分秒
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数
图片：
blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

字段约束

主键约束：
如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。
非空约束：
非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。
唯一约束：
如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

基础函数

Lower SELECT ‘ABC’,LOWER(‘ABC’) from dept; --数据转小写
upper select upper(dname) from dept --数据转大写
length select length(dname) from dept --数据的长度
substr SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
concat select dname,concat(dname,‘123’) X from dept --拼接数据
replace
infull
round & ceil & floor
uuid
now
year & month & day
转义字符

条件查询

distinct
where 注意where中不能使用列别名！！（是因为where的执行顺序在select之前，同理order by后的语句执行是在select之后所以是可以使用别名的）
like
null
between and
limit
order by 默认升序
强调格式：where需要声明在from后，order by之前。

select employee_id,salary 
from  employees
where department_id in (50,60,70)
order by department_id desc;

聚合 aggregation

count
max/min
sum/avg

分组 group

group by
having

扩展

char和varchar的区别如下：
char为定长字符串，char(n)，n最大为255
varchar为不定长字符串，varchar(n)，n最大长度为65535
char(10)和varchar(10)存储abc的差别是
char保存10个字符，abc三个，其它会用空格补齐；而varchar只用abc三个位置。
datetime和timestamp的区别？
datetime年月日时分秒，存储和显示是一样的
timestamp时间戳存储的不是个日期，而是从1970年1月1日到指定日期的毫秒数
中文乱码：
如果在dos命令下执行insert插入中文数据，数据又乱码，那现在sqlYog客户端执行下面命令：set names gbk;
只要保证创建数据库时用utf8，使用可视化工具一般就基本正确。
注释
/* 很多注释内容 /
#行注释内容 这个使用较多
主键外键唯一索引的区别是
Primary Key 主键约束，自动创建唯一索引
Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不能瞎写
Unique Index 唯一索引，唯一值但不是主键
对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。
drop、delete和truncate之间的区别？
drop删除库或者表，数据和结构定义
delete和truncate只是删除表的数据
delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录
对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数。
select count(1) from Owner效率高于select count() from Owner
在MySQL 8.0以后默认连接字符集从latin1改成了utf8mb4

JDBC

sun公司设定的一套数据库标准JDBC（Java Database Connectivity）。
JDBC数据库接口：
Connection链接、Statement语句、PreparedStatement预处理语句、CallableStatement存储过程、ResultSet结果集。
调用方式有三种：Statement语句、PreparedStatement预处理语句、CallableStatement存储过程，推荐使用第二种PreparedStatement，防止SQL注入，其也是预编译性能高。
1 INSERT INTO t_emp VALUES(NULL,?,?,?)";//JDBC的预编译方式
2

JDBC常见的问题

Class.foName用了静态代码块创建对象

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }

Java提供了SPI机制，用户可以自行配置类，JDBC高版本驱动就都引入了这个支持。如果用户使用了Class.forName方式就自己指定了驱动，如果未写这句话，则Java自动去META-INF/services/java.sql.Driver文件中找启动类。
版本驱动

Mysql5.0x mysql-connector-java-5.1.32.jar
Mysql8.0x mysql-connector-java-8.0.21.jar

Driver变成了： com.mysql.cj.jdbc.Driver，中间多了cj
url必须加时区参数： serverTimezone=Asia/Shanghai
中文乱码 url增加参数：characterEncoding=utf8防止中文乱码
SQL注入
利用转义字符，屏蔽了SQL中的恶意字符。
不仅解决了sql注入问题，使系统变的安全，
PreparedStatement还有个极大的好处，它是预编译的语句，
其主干部分mysql进行预编译后缓存，下次这部分就无需在解析，
只把条件拼入，这样执行效率远高于statement每次都要编译sql语句。

（SQL 攻击发生的现象是：用户输入了一些SQL中的特殊字符，#表示注释
Statement工具：无法避免SQL注入问题，而且SQL复杂需要自己拼接参数，低效
PreparedStatement工具：避免了SQL攻击的问题，SQL简单，高效，先把SQL骨架发给数据库，再把参数发给数据库。用?代替参数的位置叫占位符）

SQLServer

MongoDB

数据库连接池

-阿里巴巴Druid.pool