标签：name -- 数据库持续性索引 Day17 MySQL table null

1、初识MySQL

JavaEE:企业级Java开发 Web

前段（页面展示，数据）

后端（连接点：连接数据库JDBC；链接前端：控制，控制反转，给前台传数据）

数据库（存数据）

1.1 数据库分类

关系型数据库(SQL)：

MySQL、Oracle、Sql Server、DB2、SQLlite
通过表和表、行和列之间的关系进行数据的存储

非关系型数据库(NoSQL：Not Only SQL)：

redis、MongDB
非关系型数据库，对象存储，通过对象自身的属性来决定

1.2 DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理数据。维护和获取数据
MySQL：关系型数据库管理系统

基本操作：

update mysql.user set authentication_string = password('') where user = '' and Host = 'localhost'; /修改用户密码/

show databases; /查看所有数据库/

use school; /使用数据库/

show tables; /查看表/

describe student; /查看表信息/

create database school; /创建一个数据库/

2、操作数据库

操作数据库>操作数据库中的表>操作表中的数据

2.1 操作数据库

创建数据库

create database [if not exist] dataBaseName

删除数据库

drop database [if exist] dataBaseName

使用数据库

use `dataBaseName`  --关键词加`

查看数据库

show databases;

2.2 数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准的整数 4个字节
bigint 较大的数据 8个字节
float 单精度浮点数 4个字节
double 双精度浮点数 8个字节
decimal 字符串形式的浮点数，金融计算的时候一般使用decimal

字符串

char 固定大小的字符串 0-255
varchar 可变长的字符串 0-65535
tingtext 微型文本 2^8-1
text 文本串 2^16-1

时间日期

date YYYY-MM-DD 日期
time HH:mm:ss 时间
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳，1970.1.1到现在的毫秒数！也较为常用
year 年份表示

null

没有值，未知
注意：不要使用NULL进行运算，结果为NULL

2.3 数据库的字段属性

Unsigned:

无符号的整数
声明了该列不能为负数

zerofill：

0填充的
不足的位数使用0来填充

自增：

通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键~index，必须是整数类型
可以自定义设计主键的起始值和步长

非空：

假设设置为not null，如果不给它赋值，就会报错
null，如果不填写值，默认就是null

默认：

设置默认的值！
sex，默认值是男，如果不指定该列的值，则会有默认的值

show create database school; /*查看创建数据库的语句*/
show create table student; /*查看创建表的语句*/
desc student; /*显示表的结构*/

2.4 数据表的类型

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM两倍

常规使用操作：

MYISAM：节约空间，速度较快
INNODB：安全性高，支持事务的处理，多表多用户操作

2.5 修改删除表

alter table name1 rename as name2;修改表明
alter table name add ziduan shuxing;新增字段
alter table name modify age varchar(11);修改约束
alter table name change age age1 int(1);修改字段名
alter table name drop age1;删除表的字段
drop table if exists name;如果表存在，则删除

3、MySQL数据管理

3.1 外键

物理外键，数据库级别的外键，不建议使用(避免数据库过多，造成困扰)

key `FK_name` (`name`),
constraint `FK_name` FOREIGN KEY (`name`) references `table`(`name`)    //外键

alter table `tablename` add constraint `FK_name` foreign key(`name`) references 'tablename2'(`name`)

最佳实践

数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)
我们想要使用多张表的数据，想使用外键(程序来实现)

3.2 DML语言

3.3 添加

--插入语句

insert into table () values(),()

3.4 修改

--更新语句

update table set column1 = value,column2 = value where [条件]
update school.student set birthday = current_time() where id = 1;

3.5 删除

--删除语句

delete from table where [条件]

--完全清空一个数据库表，表的结构和索引约束不会变

truncate table table; --自增会归零

4、DQL查询数据

4.1 DQL

(Date Query Language：数据查询语言)

所有的查询都用它 Select
简单的查询，复杂的查询都能做
数据库中最核心语言，最重要的语句
使用频率最高的语句

4.2 指定查询字段

select * from table where [条件]

SELECT version(); --查看系统版本

--联表查询

leftjoin; --左联
innerjoin; --内联
rightjoin; --右联

--自连接：自己的表和自己的表连接，核心：一张表拆成两张一样的表

select a.name,b.name from table1 a,table1 b where a.param1 = b.param2

--分页

limit 0,5; --起始值，页面的大小
--【pageSize:页面大小;(n-1)*pageSize:起始值;n:当前页】
--总页数:数据总量/页面大小

--子查询

--本质：在where语句中嵌套一个子查询语句
where (select from)

--常用函数

ABS();--绝对值
CEILing();--向上取整
FLOOR();--向下取整
RAND();--返回0~1之间的随机数
SIGN();--判断一个数的符号,负数返回-1，整数返回1

--字符串函数

CHAR_LENGTH();--字符串长度
CONCAT();--拼接字符串
INSERT("啊啊"，1，2,"熬熬");--查询，从某个位置开始替换某个长度
INSTR('aaabbbccd','d');--返回第一次出现子串的位置
REPLACE('aabbcc','bb','ww');--替换出现的指定字符串
SUBSTR('aabbcc',2,3);--返回指定的子字符串,(源字符串，截取的位置，截取的个数)
REVERSE();--反转

--时间和日期函数

CURRENT_DATE();--获取当前日期

--系统

SYSTEM_USER();
USER();
VERSION();

--聚合函数

COUNT();
AVG();
MAX();
MIN();

5、事务

5.1 什么是事务

要么都成功，要么都失败

事务原则：ACID（原子性，一致性，隔离性，持久性）

set autocommit = 0;--关闭自动提交
start transaction;--标记一个事务的开始，从这之后的SQL都在同一个事务内
commit;--提交，持久化(成功！)
rollback;--回滚，回到原来的样子(失败！)
set autocommit = 1;--开启自动提交
savepoint 保存点名;--设置一个事务的保存点
rollback to savepoint;--回滚到保存点
release savepoint 保存点名;--撤销保存点

5.2 事务流程举例

create database shop character set utf8 collate utf8_general_ci;
use shop;
create table `account`(
    `id` int(3) not null auto_increment,
    `name` varchar(30) not null,
    `money` decimal(9,2) not null,
    primary key (`id`)
)engine=innodb default charset=utf8

set autocommit=0;
start transaction
update account set money=money-500 where name = 'A';
update account set money=money+500 where name = 'B';
commit;
rollback;
set autocommit=1;

6、索引

MySQL官方定义：索引(Index)是帮助MySQL高效获取数据的数据结构。

6.1 索引的分类

主键索引(primary key)
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引(unique key)
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引(key)
- 默认的，index、key关键字来设置
全文索引(full text)
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
-- 显示所有索引信息
show index from `table`;
--增加一个索引
alter table `tableName` add fulltext index `索引名`(`列名`);
--explain 分析SQL执行的状况
explain select * from tablename;--非全文索引
explian select * from tablename where MATCH('字段名') against('内容');

6.2 创建索引

-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_student_name on student(`name`); /*创建索引*/

6.3 索引原则

索引不是越多越好
不要对经常变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段

--索引的数据结构
-- Hash 类型的索引
-- Btree InnoDB默认的数据结构

7、权限管理和备份

7.1 用户管理

-- 可视化管理
-- SQL命令操作
-- 用户表:mysql.user
-- 本质:对用户表的
create user testa identified by '123456';
rename testa to testb;
grant all privileges on *.* to testa; --授予全部的权限
show grants for testa; -- 查询指定用户的权限
show grants for root@localhost; -- 查看root管理员权限
revoke all privileges on *.* from testa; -- 撤销权限 revoke哪些权限，在哪个库撤销，给谁撤销
drop user testa; -- 删除用户

7.2 MySQL备份

为什么要备份：

保证重要数据不丢失
数据转移

MySQL数据库备份方式：

直接拷贝物理文件
在可视化工具中手动导出
使用命令行导出 mysqldump 命令行使用

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql -- 导出
source D:/a.sql -- 导入 登录的情况下 切到对应数据库

8、规范数据库设计

三大范式

1NF：要求数据库的每一列都是不可分割的原子数据项
2NF：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF的基础上消除非主属性对主码的部分函数依赖）
3NF：在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF的基础上消除传递依赖）

规范和性能问题：

关联查询的表不得超过三张

考虑商业化的需求和目标，（成本、用户体验）数据库的性能更加重要
在规范性能问题的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询）

9、JDBC

9.1 JDBC

SUN公司为了简化开发人员的（对数据库的统计）操作，提供了一个（Java操作数据库的）规范，俗称 JDBC

这些规范的实现由具体的厂商去做

对于开发人员来说，只需要掌握JDBC接口的操作即可

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String userName = "root";
        String passWord = "stujc0828";
        //3.连接成功，返回数据库对象
        Connection con = DriverManager.getConnection(url,userName,passWord);
        //4.执行SQL的对象
        Statement statement = con.createStatement();
        //5.执行SQL的对象去执行SQL，可能存在结果，查看返回结果
        String sql = "select * from users";
        ResultSet ret = statement.executeQuery(sql);

        while (ret.next()){
            System.out.println("id="+ret.getObject("id"));
            System.out.println("name="+ret.getObject("name"));
            System.out.println("password="+ret.getObject("password"));
            System.out.println("email="+ret.getObject("email"));
            System.out.println("birthday="+ret.getObject("birthday"));
        }
        //6.释放连接
        ret.close();
        statement.close();
        con.close();

    }
}

9.1 工具类

public class JdbcUtil {
    private static String driver = null;
    private static String url = null;
    private static String userName = null;
    private static String passWord = null;

    static {
        try{
            InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            userName = properties.getProperty("userName");
            passWord = properties.getProperty("passWord");
            //加载驱动只需要执行一次
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url,userName,passWord);
    }
    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Test {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet ret = null;
        try {
            con = JdbcUtil.getConnection();
            st = con.createStatement();
            String sql = "select * from users";
            ret = st.executeQuery(sql);
            while (ret.next()){
                for(int i=1;i<6;i++){
                    System.out.println(ret.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.release(con,st,ret);
        }
    }
}

9.2 PrepareStatemen对象

可以防止SQL注入
效率更高

public class TestPrepareStatement {
    public static void main(String[] args) {
        Connection  con = null;
        PreparedStatement pst = null;
        ResultSet ret = null;
        try {
            con = JdbcUtil.getConnection();
            String sql = "select * from studeng where id = ?";
            pst = con.prepareStatement(sql);    //预编译SQL，先写SQL，然后不执行
            pst.setInt(1,1);
            ret = pst.executeQuery();
            while (ret.next()){
                for(int i=1;i<6;i++){
                    System.out.println(ret.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.release(con,pst,ret);
        }
    }
}

9.3 事务

隔离性问题：

脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
幻读：在一个事务内，读到了别人插入的数据，导致前后读出来的结果不一致

public class TestTransaction {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtil.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            con.setAutoCommit(false);
            String sql1 = "update account set money = money-100 where name = 'A';";
            st = con.prepareStatement(sql1);
            st.executeUpdate();
//            int x = 1/0;
            String sql2 = "update account set money = money+100 where name = 'B';";
            st = con.prepareStatement(sql2);
            st.executeUpdate();

            con.commit();
            System.out.println("成功");
        } catch (SQLException e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtil.release(con,st,rs);
        }
    }
}

10、数据库连接池

拓展

/*每一个表，都必须存在以下5个字段！用来做项目用的，表示一个记录存在意义

id 主键

`version`  乐观锁

is_delete  伪删除

gmt_create  创建时间

gmt_update  修改时间

*/

--数据库级别的MD5加密

主要增强算法复杂度和不可逆性

insert into table (pwd) values (MD5(''));
update table set pwd=MD5(pwd);

标签：name,--,数据库,持续性,索引,Day17,MySQL,table,null
From： https://www.cnblogs.com/-Gin/p/18229449

持续性学习-Day17（MySQL）