mysql的中的常用对象 mysql的数据库对象有

Java学习05–MySQL

1、数据库简介

• 概念：数据仓库，软件，安装在操作系统之上
• 作用：存储、管理数据
• 分类

• 关系型数据库（SQL）： MySQL，Oracle，Sql server 通过表和表之间行和列之间关系进行数据的存储
• 非关系型数据库（NoSQL）： Redis，MongDB 对象存储，通过对象的自身的属性来决定

• DBMS（数据库管理系统）： 管理和操作数据，会有各种软件链接在上面。MySQL本质是一个数据库管理系统

2、连接创建数据库

• 命令行连接

mysql -u root -p               -- 后面可以接密码
-- 单行注释
/*
多行注释
*/ 
-- 所有语句都以;结尾

3、数据库语言

• DDL 定义
• DML 操作
• DQL 查询
• DCL 控制

4、操作数据库

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

• mysql中的关键字不区分大小写

create database school;-- 创建数据库库
drop database school; -- 删除数据库
use school; -- 切换数据库
show databases; -- 查看所有数据库
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有表的信息
SHOW CREATE DATABASE school; -- 查看创建数据库的语句
show CREATE TABLE students; -- 查看创建表的语句
desc students; -- 查看表的结构

• 学习思路，对照历史记录看命令

4.1、数据库的列类型

• 数值

tinyint -- 很小的数据 1字节
smallint -- 较小的数据 2字节
mediumint -- 中等大小数据 3字节
int -- 正常整数 4字节
bigint -- 较大数据 8字节
float -- 浮点数 4字节
double -- 浮点数 8字节（精度问题）
decimal -- 字符串形式的浮点数 （金融计算时用）

• 字符串

char -- 字符串固定大小的 0~255
varchar -- 可变字符串 0~65535
tinytext -- 微型文本 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

4.2、数据库的字段属性

• unsigned：无符号的整数，声明该列不能为负数
• zerofill：零填充的，不足的位数用0来填充
• auto_increment:自增，自动在上一条的基础上+1，通常用来设置唯一主键index，必须是整数类型
• not Null：非空
• default:设置默认的值

/*每一个表，都必须存在以下五个字段！

id 主键
'version'  乐观锁
is_delete  伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

4.3、创建表

CREATE DATABASE school;
USE school;
CREATE TABLE IF NOT EXISTS `students`(
`id` INT(4) NOT NULL auto_increment COMMENT '学号', 
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`name` VARCHAR(2) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`birthdate` datetime DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

4.4、数据库表的类型

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持（现在支持英文）
表空间的大小	较小	较大约为二倍
物理文件	*.frm表结构定义, *.MYD数据文件, *.MYI索引文件	数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

• MYISAM：节约空间速度快
• INNODB：安全性高，事务处理，多表多用户
• 设置字符集：CHARSET=utf8 MySQL默认的不支持中文

4.5、修改删除表

• 修改表

alter table teacher rename as teacher1; -- 改表的名字
alter table teacher1 add age int(11); -- 添加字段
alter table teacher1 modify age varchar(11); -- 修改约束和类型
alter table teacher1 change age age1 int(3); -- 字段重命名
alter table teacher1 drop age1; -- 删除字段
drop table if exists teacher1; -- 删除表（所有创建和删除操作尽量加上判断以免报错）

-- 注意点
所有的字段名都用`字段名`包裹

5、MySQL数据管理

5.1、外键

• 外键约束

-- 直接在建表时添加外键约束
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(20) NOT NULL auto_increment COMMENT '年级号', 
`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `students`(
`id` INT(4) NOT NULL auto_increment COMMENT '学号', 
`gradeid` INT(20) NOT NULL COMMENT '学生年级号',
`name` VARCHAR(2) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`birthdate` datetime DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 后添加外键
alter table `student`
add constraint `FK_grade` foreign key(`gradeid`) references `grade`(`gradeid`)

• 删除有外键关系的表的时候必须先删除引用外键的表，再删除引用别人的表
• 以上都是物理外键（数据库级别的外键）约束，现在并不建议使用（避免数据过多造成困扰）
• 最佳实践：

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

5.2、DML语言（数据操作语言、重点）

5.2.1、插入语言（添加）

insert into `grade`(`grade`,`gradename`) 
values('4','大四'),('5','研一');
-- 可以不写字段名，但是values必须一一对应

5.2.2、修改

update `students` set `name`='李四'(可以是变量),`age`=22 where id = 1;
-- =符号还可以换成!=,>,<,>=,<=,between...and...,and(&&),or(||)

5.2.3、删除

delete from `students` where `id`=1; -- 删除指定数据
delete from `students` -- 完全删除一个表中的数据，表的结构和索引约束不会变
truncate table `students` -- 完全删除一个表中的数据，表的结构和索引约束不变

• delete 和 truncate 的区别：truncate不会删除和事务且自增归零，而delete保留自增
• delete删除后重启数据库时：innodb 自增列会从1开始（存在内存中断电即失），myisam继续从上一个自增量开始（存在文件中不会丢失）

6、DQL（数据查询语言、最重点）

6.1、简单查询

select 表达式 from 表;
select * from student; -- 查询student表中全部信息
select `StudentNo` as 学号,`StudentName` as 学生姓名 from student as 学生; -- 查询指定字段，as可以设置别名
select concat('姓名：',StudentName) as 名字 from student; -- 函数使用
select distinct `StudentNo` from result; -- distinct去掉重复

6.2、where条件子句

• 作用：检索数据中符合条件的值
• 逻辑运算符：and,or,not

select `StudentNO`,`StudentResult` from result where `StudentResult` between 95 and 100; -- 使用where查询

• 模糊查询：比较运算符

运算符	语法	描述
is null	a is null	操作符为null则为真
is not null	a is not null	操作符不为null则为真
between	a between b and c	a在b和c之间为真
like	a like b	SQL匹配，若a匹配b则为真
in	a in (…)	a在…的某个值中则为真

select `StudentNo` as 学号,`StudentName` as 学生姓名 from student as 学生
where `StudentName` like '刘%'; -- ‘%’是通配符代表任意个任意字符‘_’也是通配符代表一个任意字符
select `StudentNo` as 学号,`StudentName` as 学生姓名 from student as 学生
where `StudentNo` in (1000,1001,1002); -- 使用in，in中可以嵌套select

6.3、联表查询

select s.StudentNO,StudentName,SubjectNo,StudentResult 
from student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo; -- on操作在生成表时，where则是生成临时表后

-- join （连接的表） on （条件判断） 
-- inner join  如果表中至少有一个匹配，就返回
-- left join   会从左表中返回所有的值，即使右表中没有匹配
-- right join  会从右表中返回所有的值，即使左表中没有匹配

-- 自连接将一张表视作两张一模一样的表
select a.name as 'dad', b.name as 'son'
from family as a,family as b
where a.nameid = b.pid;

6.4、分页limit和排序order by和分组group by

• 排序order by： 升序ASC 、降序DESC

-- select语句顺序
select ...
from ...
(join)...on...
where ...  -- 只能有一个where，并且表达式中不能使用聚合函数
group by ...  -- 指定结果按什么分组
having ...  -- 分组后过滤
order by ...
limit 起始下标,页面大小  -- limit是select语句中的最后一个

6.5、子查询

• 本质：在where语句中嵌套一个select语句

6.6、MySQL常用函数

-- 聚合函数
count（列名）,count（*）,count（1） -- 计数
sum（列名）
avg（列名）
max（列名）
min（列名）

6.7、数据库级别的MD5加密

-- 加密
update testmd5 set pwd=MD5(pwd)
(where) ... ;

-- 插入时加密
insert into testmd5 values(4,'zhangsan',MD5('123456'));

7、事务

7.1、什么是事务

• 事务：将一组SQL放在一个批次去执行。
• ACID原则：原子性，一致性，隔离性，持久性

• 原子性：要么都成功，要么都失败
• 一致性：一个事物操作前和操作后的状态一致
• 持久性：事物结束后的数据不会因为外界影响而丢失（未提交则恢复原状，提交了就不会变）事务一旦提交就不可逆了
• 隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

• 脏读：指一个事务读取了另外一个事务未提交的数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
• 幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。（一般是行影响）
• 四种隔离级别:

级别设置	描述
Serializable	可避免脏读、不可重复读、虚读情况的发生。（串行化）
Repeatable read	可避免脏读、不可重复读情况的发生。（可重复读）
Read committed	可避免脏读情况发生（读已提交）。
Read uncommitted	最低级别，以上情况均无法保证。(读未提交)

7.2、事务执行

-- MySQL是默认开启事务自动提交的

-- 手动处理事务
set autocommit = 0
-- 事务开启
start transaction -- 标记一个事物的开始，从此之后的sql都在同一事务内
-- 提交：持久化（成功）
commit
-- 回滚：回到原样（失败）
rollback
-- 事务结束
set autocommit = 1
-- 设置保存点
savepoint 保存点名
-- 回滚到保存点
rollback to 保存点名

8、索引

• 简介：索引是为了帮助sql高效获取数据的数据结构

8.1、索引分类

• 主键索引（PRIMARY KEY) 唯一索引一个表只能有一个
• 唯一索引 (UNIQUE KEY) 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引 (KEY/INDEX) 默认的索引
• 全文索引 (FullText) 在特定的数据库引擎下有

-- 查帮助文档
help create
help create index
==================
1.创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int , #可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

8.2、索引原则

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

9、权限管理和备份

9.1、权限管理

CREATE USER 'jzf'@'localhost'; -- 创建用户

-- 当前用户设置密码
SET PASSWORD = PASSWORD('mypass');

-- 给指定账户设置密码
SET PASSWORD FOR 'jzf'@'localhost' = PASSWORD('mypass');

-- 赋权限
GRANT ALL ON db1.* TO 'jzf'@'localhost';

-- 刷新权限
flush privileges;

9.2、备份

mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql  -- 使用命令行导出sql
source D:/a.sql -- 导入

10、规范数据库设计

三大范式

• 第一范式： 第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
• 第二范式（必须满足第一范式）： 第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• 第三范式（必须满足第一、二范式）： 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
• 规范性和性能会有冲突，为了照顾实际需求我们有时会故意添加一些冗余字段

11、JDBC

• JDBC是Java操作数据库的规范

//java连接数据库
package com.jzf.demo01;

import java.sql.*;

public class firstjdbc {
    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/jdbcstudy?useUnicode=true&serverTimezone=UTC&characterEncoding=utf8&&useSSL=false";
        String username = "root";
        String password = "123456";
        //3、连接成功，数据库对象 Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4、执行sql的对象Statement
        Statement statement = connection.createStatement();
        //5、执行sql对象
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));           			System.out.println("====================================================");
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

/*
=====Drivermanager =======
加载驱动，固定写法用Class.forname("com.mysql.cj.jdbc.Driver")
======URL========
URL:"jdbc:mysql://主机地址:端口号（3306）/数据库名?参数1&参数2&参数3"
=====connection=========
Connection connection = DriverManager.getConnection(url, username, password);
connection.rollback(); 事务回滚
connection.commit();   事务提交
connection.setAutoCommit(); 数据库设置自动提交
======statement执行sql的对象，是最重要的对象=========
String sql = "select * from users";
Statement statement = connection.createStatement();
statement.executeQuery(); 查询操作返回resultset
statement.execute();  执行任何sql
statement.executeUpdate(); 更新插入删除，返回受影响的行数
=======resultset查询的结果集=========
ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
resultSet.getObject(); 不知道类型使用，若知道类型则使用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getString();
resultSet.beforeFirst(); 移动到最前
resultSet.afterLast();   移动到最后
resultSet.next();        移动到下一个数据
resultSet.previous();	移动到前一行
resultSet.absolute(row);	移动到指定行
*/

• SQL注入： SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
• PreparedStatement对象： 防止SQL注入

12、数据库连接池

• 池化技术：预先准备一些资源，过来就可以直接连接
• 开源数据实现：

• DBCP
• C3P0