MySQL

最新推荐文章于 2024-07-13 10:38:18 发布

夜空的猪

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量393

点赞数

分类专栏： mysql 文章标签： mysql java sql 数据库

本文链接：https://blog.csdn.net/qq_42231602/article/details/106378873

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.MySQL简介

MySQL是一个关系型数据库管理系统**，**原本是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

2.MySQL使用

1.在一个应用程序里，分成各个阶段去完成用户的请求。

在这里插入图片描述

2.数据库就是用来存储数据的仓库

数据库是如何被使用的
在这里插入图片描述

3. 连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; 
--修改密码

flush privileges --刷新权限
---------------------------------------------------
--所有的语句都使用；结尾
show databases;--展示数据库
use school;--切换数据库
show tables;--查看数据库中所有的表
describe student;--显示student表中所有信息
Ctrl+C强行终止命令(分号打错等)

----------------------------------------------------
create database【if not exists】 westos；--创建数据库
drop database westos--删除数据库
--如果表名或者字段名是一个特殊字符，比如说user，就需要带``
use `user`
exit;--退出连接

数据库xxx语言 CRUD增删改查
CV程序猿 API 程序猿 CRUD程序猿（业务）
DDL 数据库定义语言
DML 数据库操作管理语言
DQL 数据库查询语言
DCL 数据库控制语言

4.数据库的列类型

数值

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
时间日期
java.util.Date
date YYYY-MM-DD，日期格式
time HH：mm：ss 时间格式
datetime YYYY-MM-DD HH：mm:ss最常用的时间格式
timestamp时间戳 1970.1.1到现在的毫秒数
null
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200528101441265.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyMjMxNjAy,size_16,color_FFFFFF,t_70

5.CRUD

1.insert

在这里插入图片描述

2.update

在这里插入图片描述

有两张表，通过字段连接，给记录中某一列的值进行修改

有两个表

school表，存放id和学校id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xXuXReig-1591204010050)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529155411529.png)]

info表，存放schoolid和name

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-seJsHGHW-1591204010069)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529155742906.png)]

--通过两个表的字段对应，更改属性值
update school s,info i set s.school=i.name where s.id=i.id

之后school表就变成了，学校代码已经改变了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OlHrt7kd-1591204010072)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529160103026.png)]

change 和modify的区别
在这里插入图片描述

3.delete和truncate

在这里插入图片描述

4.select

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yoBIGex5-1591204010079)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529151421308.png)]

顺序很重要
select (distinct)--去重
from 表（表和字段可以取别名）
（inner（不写） 、left、right）join 表 on 等值判断
where (具体的值、子查询语句)
group by (用来分组的字段)
having 分组后的过滤 
--如果用来分组，那查询的字段只有分组中一致的字段和自定义的字段，比如总数，总分，平均值
order by （用来排序的字段）desc 倒序 asc 正序
limit 起始值，结束值（从0开始）


业务层面
查询：跨表  跨数据库

5.外键

6.去重

在这里插入图片描述

7.数据库的列（表达式）

在这里插入图片描述

8.逻辑运算符（与或非）

在这里插入图片描述

运算符	语法	描述
IS NULL	a is null	如果操作数为null，结果为真
IS NOT NULL	a is not null	如果操作数为null，结果为假
	a between b and c	若a在b和c之间，则结果为真
LIKE	a like b	如果a能够匹配到b，结果为真
IN	a in (a1,a2,a3…)	假设a为a1，a2…中的某一个，结果为真

上面的内容比较简单，下面的我建了3个表，用来测试

user表，只有两个属性就可以了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o5KKantc-1591204010081)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529152659473.png)]

student表，三个字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VGCFfOmP-1591204010083)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529152814618.png)]

score表，两个字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f4j03Ruf-1591204010085)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529152845055.png)]

9.模糊查询LIKE

==LIKE=

–查询姓刘的同学(%代替所有字符)

select studentNo ，StudentName from student 

where StudentName like ‘刘%’

–查询姓刘的同学，后面只有一个字

select studentNO，StudentName from student 

where StudentName like ‘刘_’

–查询姓刘的同学，后面有两个字（一个下划线表示一个字符）

select studentNO，StudentName from student 

where StudentName like ‘刘__’

10.集合元素匹配IN

这里不能使用模糊查询，只能使用已有集合或者查询满足条件的集合

–查找学号为（1,2,3）的学生

select studentNo,StudentName from student where StudentNo in (1,2,3)

–可以用or代替

select studentNo,StudentName from student where StudentNo=1 || StudentNo=2||StudentNo=3

–可以已查询的集合作为IN后面的集合

select studentNo,StudentName from student 

where StudentNo in (select studentNo from student)

11.判断是否控制NULL，NOT NULL

=NULL， NOT NULL==========

–查询地址为空的学生

select studentNo,studentName from student
where address='' or address is null

12.连表查询join

=连表查询Join===

操作	描述
join（inner join）	如果两个表能够满足条件，就会把匹配的记录输出
left join	用on生成临时表的时候，不管on的条件是否为真，左表的记录都会显示
right join	用on生成临时表的时候，不管on的条件是否为真，右表的记录都会显示

注意点：

在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

3.如果是join条件下，on和where的条件的结果是一样的

4.join连接可以连接很多个表，但是一次性连接多个表会使记录膨胀特别快，应该在完成两个表的连接之后再继续连接其他的表，这样可以减少不必要的笛卡尔积消耗。

–查询左表和右表中学号相同的记录

select StudentName,score from student  join  score on student.StudentNo=score.StudentNo
or
select StudentName,score from student right join  score on student.StudentNo=score.StudentNo where StudentName is not null

–左连接查询(on条件单单只对左表的字段进行判断是没用的，)

select StudentName,score from student left join  score on student.StudentNo is Null

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GeETe4Mf-1591204010087)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529115000684.png)]

自连接查询=

categoryid（类别id）	pid（父id）	category（类别名称）
2	1	互联网
3	1	经济贸易
4	1	外语
5	2	计算机科学与技术
6	3	国际贸易
7	4	商务英语

select a.categoryid as '父栏目',a.category,b.categoryid as '子栏目',b.category
from category as a,category as b where a.categoryid=b.pid

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VKfEn6W4-1591204010090)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529143514436.png)]

13.分组和过滤

--group by
group by 字段
1. 我们可以使用group by 来对字段进行分组，
比如要计算一个学生的总分，我们需要按学号进行分组
2. 分组之后如果要对总分进行条件判断，比如说超过多少分才满足条件，
就要用having

14.分页排序

==分页排序=

分页的目的：

①缓解数据库压力

②给人体验更好

③瀑布流不一定适用（比如百度图片的加载，拉到底就继续加载新图片）

首先要按顺序排列

--order by
select a.categoryid as '父栏目',a.category,b.categoryid as '子栏目',b.category
from category as a,category as b where a.categoryid=b.pid
order by b.categoryid desc

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RlHUJlfb-1591204010092)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529152236065.png)]

接着我们要规定每一次的查询限制
```
--asc排序，结果如图
select a.categoryid as '父栏目',a.category,b.categoryid as '子栏目',b.category
from category as a,category as b where a.categoryid=b.pid
order by b.categoryid asc
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MEORMeO2-1591204010094)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529160937646.png)]

--limit 起始值,终止值（记录从0开始）
--如图可知，为上图第一条记录
select a.categoryid as '父栏目',a.category,b.categoryid as '子栏目',b.category
from category as a,category as b where a.categoryid=b.pid
order by b.categoryid asc
limit 0, 1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CspTLSgl-1591204010095)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200529161059297.png)]

这个时候我们会有两个已知值 ----总记录数sum，页面记录数pagesize
然后我们要求的是 -------------页面数pages,起始记录索引start,和终止记录索引end
pages=((sum/pagesize)*pagesize<sum)?sum/pagesize+1:sum/pagesize
for(int i=0;i<pages;i++){
	start = pages*pagesize;
	end = (pages*(pagesize+1))>sum?sum-1:pages*(pagesize+1)
}
然后limit start，end就可以了。

15.子查询

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

16.常用函数

select ABS(-8) --绝对值
select CEILING(9.4) --向上取整
select Floor(9.4) --向下取整
select RAND() ---返回一个0~1之间的随机数
select SIGN(10) ---判断正负 0-0，负数为-1，正数为1
包括一些最大值、最小值、平均值avg、计数count

但是一般这种函数直接在java中使用就可以了，单一职责

3.事务

3.1什么是事务？

要么都成功，要么都失败

把一组sql放在一个批次中去执行

事务原则：ACID原则

原子性，一致性，隔离性、持久性（脏读、幻读）

原子性：同一批次的事务命运共存

同一批次中的事务要么一起成功，要么一起失败

一致性：针对一个事务操作前与操作后的状态一致

比如a给b转钱，事务前后的金额总数一定是相同的

隔离性：针对多个用户同时操作，主要是排除其他事务对本次事务的影响

比如说a给b转钱，c给d转钱，两者互不干扰

持久性：事务结束后不会随着外界原因导致数据丢失

事务没有提交，恢复原状

事务提交，持久化到数据库

3.2事务的隔离级别

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

a读，b读，a改，b改

不可重复读：在一个事务中读取某一行数据，多次读取结果不同（可能是别的事务正在改变这一数据）

a读，b改，a读

幻读：指事务在读取的时候读到别的事务新插入的数据，导致前后读取不一致

a读，b插入，a读

执行事务

关闭自动提交
开启一个事务
sql语句
提交或回滚
开启自动提交

4.索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引是数据结构

4.1索引的分类

主键索引 primary key
- 主键是唯一的，不能有两个主键
唯一索引 unique key
- 避免重复列出现
常规索引 key
- 默认的，index，key关键字来设置
全文索引 fullText
- 在特点的数据库引擎才有 MyISAM

--创建索引
create index 索引名 on 表（字段）
--查看查询语句的信息，有查询所用时间
explain 查询语句
--想插入大量数据时，可以使用concat('用户',i)来设置一个主键值

4.2索引原则

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

索引的数据结构

Btree:InnoDB的默认数据结构

5.权限管理和备份

--创建用户
create user zhangsan identified by '123456'
--修改指定用户密码
set password for zhangsan =password('123456')
--修改当前用户密码
set password = password('123456')
--重命名   
--张三变李四
rename user zhangsan to lisi
--用户授权(除了给别人授权，其他权限都可以)
grant all privileges on *.* to lisi
--查看指定用户被赋予的权限
show grants for lisi
--撤销权限
revoke all privileges on *.* from li
--删除用户
drop user li

为什么要备份：

保证重要的数据不丢失
数据转移A—>B

MySQL数据库备份的方式

拷贝物理文件----------mysql中的data文件
sql yog可视化界面右键导出

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QDQbK5Bb-1591204010097)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200530154149715.png)]

使用命令行导出mysqldump

--导出数据库文件中的数据和结构
mysqldump -hlocalhost -uroot -p123456 test school
>D:/a.sql
--导入数据库文件，在登录了mysql的情况下
source d:/a.sql

6.数据库三大范式

第一范式：要求数据表的每一列都是不可再分的原子数据项，

比如家庭信息里面写着（3口人，上海）就不对

第二范式

在第一范式的基础上，所有的属性必须完全依赖主键

每张表只描述一件事情

第三范式

在第一第二范式的基础上

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

规范性和性能的问题

规范要求：关联查询的表不得超过三张表

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

7.JDBC

jdbc简介

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zMiAOyKI-1591204010098)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200531135500905.png)]

所以我们要学习jdbc

1.jdbc的使用

准备工作：

1.有一个数据库表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VO9Gqneh-1591204010101)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200530212614650.png)]

2.开启数据库连接

3.java下载mysql-connector-java-5.1.47.jar连接包,并将lib设为library

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kJvlHjNJ-1591204010103)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200530212801873.png)]

java业务逻辑连接如下：
1. 加载驱动
2. 通过管理驱动获得数据库连接对象 ----url，username，password
3. 通过数据库对象创建执行SQL对象
4. 执行SQL得到结果集
5. 关闭连接

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="";

        //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"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }


        //6.断掉连接
        resultSet.close();
        statement.close();
        connection.close();

2.SQL注入

sql存在漏洞，会被攻击导致数据泄露，=SQL会被拼接or字段=

解决方法：

使用preparedStatement，预编译SQL代码，先用？占位符代替参数，然后用set给参数赋值（本质：会把传递进来的参数当作字符，假设其中存在转义字符，如‘会被直接转义，让传进来的只能是一个参数，而不能改变SQL语句本身结构）

3.在idea中连接数据库

1.创建连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dz4HYM1C-1591204010105)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200531113415538.png)]

2.下载连接驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AhC39EzM-1591204010106)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200531113524861.png)]

3.选择要的数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CjSMlLix-1591204010107)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200531113623591.png)]

4.c3p0连接池

不用连接池我们每次都需要重新创建一个连接，这样效率不高，我们可以设置一个连接池，采用的是工厂模式。

导入依赖包

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hhaRkJwm-1591204010110)(C:\Users\57765\AppData\Roaming\Typora\typora-user-images\image-20200531133231370.png)]

创建一个c3p0-config.xml的配置问价，注意，名字一定要写对

xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="user">root</property>
        <property name="password"></property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">3</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">2</property>
        <property name="maxStatements">200</property>
    </default-config>
    <!-- 命名的配置,可以通过方法调用实现 -->
    <named-config name="MySQL">
        <property name="user">root</property>
        <property name="password"></property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">20</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">25</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

再写一个工具类

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;
    private static Statement st = null;
    static {
        dataSource = new ComboPooledDataSource("MySQL");
    }

    //1.获取连接
    public static Connection getConnection()throws SQLException{
        return dataSource.getConnection();
    }

    //2.释放连接资源
    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 TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn= JdbcUtils_C3P0.getConnection();
            System.out.println("连接成功");
            String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
            st=conn.prepareStatement(sql);
            st.setInt(1,6);
            st.setString(2,"qinjiang");
            st.setString(3,"123456");
            st.setString(4,"sdfs@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));

            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            JdbcUtils_C3P0.release(conn,st,rs);
        }
    }
}

夜空的猪

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL

在一个应用程序里，分成各个阶段去完成用户的请求。数据库就是用来存储数据的仓库数据库是如何被使用的连接数据库命令行连接mysql -uroot -p123456 --连接数据库update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改密码flush privileges --刷新权限----------------------.
复制链接

扫一扫