不会JAVA可以学MySQL吗_java学习---mysql

1.初识

数据库分类

关系型

MySQL、Oracle、Sql Server

通过表与表之间，行和列之间的关系进行数据的存储

非关系型

Redis、MongoDB

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

命令行语句

show database;

use database;

show tables;

desc tables;

create database xxx;

exit

-- 单行注释

/*多行注释*/

2.操作数据库

mysql数据库不区分大小写

操作数据库

1、创建数据库

create database if not exists xxx;

2、删除数据库

drop database if exists xxx;

3、使用数据库

use `xxx`

4、查看数据库

show databases;

数据类型

数值

tinyInt十分小的数据1个字节

smallint 较小的数据 2个字节

mediumint 中等大小的字节 3个字节

int 标准的整数 4个字节 常用

bigint 较大的数据 8个字节

float 浮点数 4个字节

double 浮点数 8个字节

decimal 字符串形式的浮点数 金融计算

字符串

char 字符串固定大小的 0~255

varchar 可变字符串 0~65535 常用 对应string

tinytext 微型文本 2^8-1

text 文本串 2^16-1

时间日期

date YYYY-MM-DD 日期格式

time HH:mm:ss 时间格式

datetime YYYY-MM-DD HH:mm:ss 最常用

timestamp 时间戳 较为常用

year 年份表示

null

没有值，未知

注意 不要使用null进行运算 结果为NULL

数据库的字段属性(重点)

Unsigned:

无符号的整数

声明了该列不能声明为负数

zerefill：

0填充的

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

自增：

自动在上一条记录的基础上+1(默认)

通常用来设计唯一的逐渐 ~index，必须是整数类型

可以自定义设计主键自增的起始值和步长

默认：

设置默认的值

sex，默认值为男，如果不指定该列的值，则会有默认的值

拓展：

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

id 主键

`version` 乐观锁

is_delete伪删除

gmt_create创建时间

gmt_update修改时间

*/

创建数据库表

格式

CREATE TABLE[IF NOT EXISTS] `表名`(

`字段名` 列类型[属性][索引][注释],

`字段名` 列类型[属性][索引][注释],

.......

`字段名` 列类型[属性][索引][注释]

)[表类型][字符集设置][注释]

/*目标 ：创建一个school数据库

创建学生表(列，字段) 使用SQL创建

学号int 登录密码varchar(20) 姓名，性别varchar(2)，出生日期(datatime)

家庭住址，email

*/

CREATE TABLE IF NOT EXISTS `student`(

`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',

`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',

`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',

`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',

`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',

`address` VARCHAR(100) DEFAULT NULL COMMENT '住址',

`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',

PRIMARY KEY(`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

常用命令

SHOW CREATE DATABASE school --查看创建数据库的语句

SHOW CREATE TABLE student --查看student数据表的定义语句

DESC student --显示表的结构

数据表的类型

-- 关于数据表引擎

/*

INNODB默认使用

MYISAM早些年使用的

*/

MYISAM

INNODB

事务支持

不支持

支持

数据行锁定

不支持

支持

外键约束

不支持

支持

全文索引

支持

不支持

表空间的大小

较小

较大

常规使用操作：

MYISAM 节约空间，速度较快

INNODB 安全度高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质还是文件的存储

MYSQL引擎在物理文件上的区别

INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

MYISAM对应文件

*.frm 表结构的定义文件

*.MYD 数据文件

*.MYI 索引文件

字符集编码

CHARSET=utf8

在my.ini中配置默认的编码

character-set-server=utf8

修改删除

修改

ALTER TABLE student RENAME AS teacher

-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性

ALTER TABLE teacher ADD age INT(11)

-- 修改表的字段(重命名。修改约束)

ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束

ALTER TABLE teacher CHANGE age age1 INT(1)-- 重命名

-- 删除字段

ALTER TABLE teacher DROP age1

删除

-- 删除表

DROP TABLE IF EXISTS teacher

所有的创建和删除操作尽量加上判断

注意点：

`` 字段名 用这个符号包裹

sql关键字大小写不敏感 建议小写

符号用英文

3.mysql数据管理

外键(了解)

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

最佳实践：

数据库就是单纯的表，只用来存数据，只有行和列

用程序来实现外键

DML语言(重点)

添加

-- insert into 表名([字段名1，字段名2，字段名3]) values('值1')，('值2'),('值3')

-- 如果不写表的字段，会一一匹配

-- 一般一定要数据和字段一一对应

修改

--UPDATE 表名 set column_name1 = value1,coluumn_name2 = value2 where[条件]

where

where子句，运算符，id等于某个值，大于某个值，在某个区间内修改

between and 在某个范围内

AND

OR

删除

-- Delete from 表名 where 条件

-- TRUNCATE 表名

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

-- 会重新设置自增列

-- 不会影响事务

4.DQL(最重点)

-- select 字段 from 表

-- 别名 as

-- 函数 Concat(a，b)

-- 去重 distinct

模糊查询

运算符

语法

描述

IS NOT NULL

a is not null

如果操作符不为null，结果为真

is null

a is null

如果为null，结果为真

between

a between b and c

like

a like b

%代表0到任意个字符、_代表一个字符

in

a in (a1，a2，a3)

联表查询

操作

描述

inner join

如果表中至少有一个匹配，就返回行

left join

会从左表中返回所有的值，即使右表中没有匹配

right join

会从右表中返回所有的值，即使左表中没有匹配

例：学生表、考试考勤表

​左连接会包含 缺考的学生 结果显示其他列为null

​查询所有参加考试的学生 应该用右连接

自链接

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

分页和排序

-- limit

语法： limit 起始下标，页面的大小

--第一页 limit 0,5 (1-1)*5

--第二页 limit 5,5 (2-1)*5

--第三页 limit 10,5 (3-1)*5

--第N页 limit 0,5

-- (n-1)*pageSize,pageSize

--【pageSize：页面大小】

--【(n-1)*pageSize：起始值】

--【n：当前值】

--【数据总数/页面大小 = 总页数】

-- order by

asc 升序

desc降序

子查询

本质：在where语句中嵌套一个查询语句

GROUP BY

分组

HAVING

过滤分组后的记录必须满足的条件

5.函数

常用函数

-- 数学运算

-- 绝对值 ABS()

-- 向上取整 ceiling()

-- 向下取整 FLOOR()

-- 随机数 RAND()

-- 返回符号 Sign()

--字符串函数

-- 字符串长度 CHAR_LENGTH()

-- 拼接 concat('','','')

-- 插入替换 INSERT('',1,2,'')

-- 小写LOWER()

-- 大写UPPER()

-- 返回第一次出现的索引 INSTR()

-- 替换出现的指定字符串 REPLACE()

-- 截取 SUBSTR('',截取位置，长度)

-- 反转 REVERSE()

--时间和日期函数

-- 获取当前日期 CURRENT_DATE()

-- 获取当前时间 NOW()

-- 获取本地时间 LOCALTIME()

聚合函数

-- COUNT() 计数

COUNT(字段名) 会忽略所有NULL

COUNT(*)不会忽略NULL 本质 计算行数

COUNT(1)不会忽略NULL 本质 计算行数

-- SUM() 求和

-- AVG() 平均值

-- MAX()

-- MIN()

6.事务

要么都成功，要么都失败

事务原则 ACID

原子性(atomicity)

要么都成功，要么都失败

一致性(Consistency)

事务前后的数据完整性要保持一致

持久性(Durability)

事务一旦提交则不可逆，被持久化到数据库中

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务之间要相互隔离

隔离所导致的一些问题

脏读

指一个事务读取了另外一个事务未提交的数据

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。(这个不一定是错误，只是某些场合不对)

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

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

SET autocommit = 0 /*关闭*/

SET autocommit = 1 /*开启*/

-----------------------------------------

-- 手动开启事务

SET autocommit = 0 /*关闭*/

-- 事务开启

START TRANSACTION

INSERT XXX

INSERT XXX

-- 提交：持久化

COMMIT

-- 回滚：失败 回到原来的样子

ROLLBACK

-- 事务结束

SET autocommit = 1 /*恢复默认值*/

7.索引

索引是帮助MYSQL高效获取数据的数据结构

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引 (PRIMARY KEY)

唯一的标识，主键不可重复，只能有一个列作为主键

唯一索引 (UNIQUE KEY)

避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

常规索引(KEY/INDEX)

默认的，index 。key关键字来设置

全文索引(FULLTEXT)

在特定的数据库引擎下才有，MYISAM

快速定位数据

--1.在创建表的时候给字段增加索引

--2.创建完毕后，增加索引

--显示所有的索引信息

SHOW INDEX FROM XXX

--创建索引 CREATE INDEX 索引名 on 表(字段)

CREATE INDEX id_user_name ON user('name')

--增加一个全文索引

ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`)

--EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM XXX; --非全文索引

索引原则

索引不是越多越好

不要对经常变动数据加索引

小数据量的表不需要加索引

索引一般加在常用的字段上

索引的数据结构

hash类型的索引

Btree： INNODB的默认数据结构

8.权限管理和备份

用户管理

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'

-- 修改密码(修改当前用户)

-- SET PASSWORD = PASSWORD(' ')

-- 修改指定用户

-- SET PASSWORD FOR XXX = PASSWORD('')

-- 重命名

-- RENAME USER XXX TO XXX2

-- 用户授权 全部的权限 库.表

-- GRANT ALL PRIVILEGES ON *.*

-- 查询权限

SHOW GRANTS FOR xxx

SHOW GRANTS FOR root@localhost

-- 撤销权限

REVOKE ALL PRIVILEGES ON *.* FROM XXX

-- 删除用户

DROP USER XXX

MySQL备份

直接拷贝物理文件

在可视化工具中手动导出

使用命令行导出 mysqldump

mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql

# 导入

# 登录的情况下，切换到指定的数据库

# source d:/a.sql

mysql -u用户名 -p密码 库名< 备份文件

9.规范数据库设计

分析需求：分析业务和需要处理的数据库的需求

概要设计：E-R图

三范式

第一范式

原子性：保证每一列不可再分

第二范式

前提：满足第一范式。

每张表只描述一件事情

第三范式

前提：满足第二范式。

需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

规范性和性能

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

考虑商业化的需求和目标，数据库的性能更加重要

在规范性能的问题的时候，需要适当考虑规范性

故意给某些表增加一些冗余的字段(从多表到单表查询)

故意增加一些计算列

10.JDBC(重点)

数据库驱动

应用程序通过数据库驱动与数据库打交道

JDBC

java操作数据库的规范，具体实现由具体厂商去做

只需要掌握jdbc的接口即可

java.sql

javax.sql

第一个jdbc程序

package com.bbbsun.lesson01;

import java.sql.*;

public class JdbcFirstDemp {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

// 1.加载驱动

Class.forName("com.mysql.jdbc.Driver");

// 2.用户信息

String url = "jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Aisa/ShangHai";

String username = "root";

String password = "1234";

// 3.连接成功

Connection connection = DriverManager.getConnection(url, username, password);

// 4.执行SQL的对象

Statement statement = connection.createStatement();

// 5.执行Sql的对象去执行sql

String sql = "SELECT * FROM users";

ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()){

System.out.println("id"+resultSet.getObject("id"));

}

// 6.释放连接

resultSet.close();

statement.close();

connection.close();

}

}

connection 代表数据库,事务在其中

Statement、PrepareStatement执行SQL的对象

statement.execute();//执行任何sql

statement.executeQuery();//查询操作返回ResultSet

statement.executeUpdate();//更新、插入、删除,返回一个受影响的行数

ResultSet 查询的结果集

抽取工具类

driver = "com.mysql.jdbc.Driver"

username = "root"

password = "1234"

url = "jdbc:mysql:///useUnicode=true&useCharacterEncoding=utf8&useSSL=true&serverTimezone=Aisa/ShangHai"

package com.bbbsun.lesson01;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

public class JdbcUtil {

private static String driver = null;

private static String username = null;

private static String password = null;

private static String url = 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 (Exception e) {

e.printStackTrace();

}

}

public static Connection getConnection() throws SQLException {

return DriverManager.getConnection(url, username, password);

}

public static void release(Connection connection, Statement statement, ResultSet resultSet) {

if (connection != null) {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (statement != null) {

try {

statement.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (resultSet != null) {

try {

resultSet.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

SQL注入问题

对用户输入数据的合法性过滤不严

SQL会被拼接 Or

PreparedStatement 对象

预注入，使用？占位符代替参数

Connection conn = null;

PreparedStatement st = null;

try {

conn = JdbcUtil.getConnection();

//区别

//使用？占位符代替参数

String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`)";

st = conn.prepareStatement(sql); //预编译SQL，先写sql，不执行

st.setInt(1,4);

st.setString(2,"zhangsan");

st.setString(3,"xxx");

st.setString(4,"xxx@xx.com");

int i = st.executeUpdate();

if (i>0){

System.out.println("插入成功");

}

PreparedStatement 防止SQL注入的本质，把传递进来的参数当作字符，假设其中存在转义字符，比如说 ’ ，会被直接转义