数据库入门到精通(MySql)

数据库入门到精通(MySql)

一.概述

1.1 什么是数据库

数据库(DB ,DateBase)

概念:数据仓库、软件、安装在操作系统(windows,linux,mac…)之上!

作用:存数据、管理数据库

1.2数据库分类

关系型数据库: 行 列

  • MySql,Oracle,SQL Server ,DB2
  • 通过表与表,行与列的关系进行数据存储

非关系数据库

  • Redis,MongDB
  • 对象存储

二.常用命令

1.创建数据库表

2.修改表
--修改表的名字  
ALERT TABLE teacher RENAME AS teacher1
--增加表的字段
ALERT TABLE teacher ADD `sex` varchar(1) 
--修改表的字段   modify与change的区别
--modify 用于修改字段的约束以及类型
--change 用于字段的重命名
ALERT TABLE teacher modify age VARCHAR(1)
ALERT TABLE teacher change age ages
--删除表的字段
ALERT TABLE teacher drop ages
--删除表
drop TABLE IF EXISTS aaa

三.MySQL数据管理

1.外键(了解即可)
  • 声明外键的表就是子表
  • 被关联的表就是父表
  • 一般在子表中重新定义一个索引字段
  • 建立索引的字段就是外键
  • 外键然后和主表的主键相连

子表/外键表 约束

ON DELETE CASCADE --删除父表字段子表也会删除

ON DELETE SET NULL --删除父表字段子表会设置NULL

ON DELETE NO ACTION --子表有数据,主表就不能动

ON UPDATE CASCADE --父表字段更新子表也会更新

ON UPDATE SET NULL —父表字段更新子表也会变成NULL

ON UPDATE NO ACTION --子表正在用的时候,父表不能进行修改

2.插入
intset into `grade`(`name`)values('张三')
3.Update

UPDATE [表名] set [字段名] =[具体值],[字段名] =[具体值],… where [条件]

update 修改谁  (条件) set 原来值=现在值
update `Student` set 'name' =`代码狂人` where id=1;
操作符含义范围返回值
=等于\true/false
<>或!=不等于\true/false
<或者<=
>或者>=
Between…and…闭区间×到×[2,5]true/false
AND&&5>1AND>2false
OR||5>1AND>2true
UPDATE `Student` set `datetime`=current_time where id='1' and `name`='张三'
4.删除数据(delete)

delete与truncate区别

  • 相同点
    • 都可以删除一个表,同时保留表的结构和索引
  • 不同点
    • truncate 会将重置设置,自增列计数器归零
    • truncate 不影响事务
delete from `student` where [条件]

delete删除问题,重启数据库

  • innoDB :自增列会从一开始(存储在内存中,断电即失)
  • MyISAM:继续从上一增量开始(存储在文件中)

四.DQL查询数据(重)

1.DQL(DATA QUERY Language)
2.select基本语法
SELECT [字段] from `student`
  • 起别名(字段或者表)

    • select `name` AS 学号,`StudentName` AS 学生姓名  from `student` AS s
      
  • 函数Concat

    • Select Concat("姓名",StudentName) As 学生姓名  from `student`
      

去重 distinct

作用:去除SELECT查询出来的重复的数据,相同的数据只显示一条

--查询有哪些同学参加了考试、成绩
SELECT * from result --查询全部的考试成绩
SELECT `StudentNo` from result --查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` from result --发现重复,去重
SELECT VERSION()  --查询系统版本
SELECT `StudentGrade`+1 from result--让所有的成绩+1

3.模糊查询(重)

比较运算符

运算符描述语法
IS NULL操作符为空,返回truea is NUll
IS NOT NULL操作符不为空,返回truea is Not NULL
BETWEEN若a在b和c之间,结果返回真a between b and c
likeSQL匹配,如果a匹配b,返回truea like b
ina in (a1,a2,a3…)假设a在a1,a2,a3…其中的一个值,返回true
--like结合  %(代表0个以上的字符) _(代表一个字符)
SELECT Student ,StudentName From student
where StudentName like '刘%'
--查询姓刘的同学,名字后面带有一个字符的
SELECT  Student ,StudentName From student
where StudentName like '刘_'
--查询包含霖字的同学
SELECT  Student ,StudentName From student
where StudentName like '%霖%'

注意

% _ 虽然好用,可不要贪杯啊

  • % _ 仅用于like 语句中
4.联表查询join On(重)
操作描述
inner join表中有一个匹配,就返回行(交集)
left join会从左表中返回所有的值,即失右表没有匹配
right join会从右表中返回所有的值,即失左表没有匹配

where 是等值连接

join on 是连接查询

left join 以左表为基表,返回左表的信息,然后和右表进行匹配

在这里插入图片描述

无论on上的条件是否为真都会返回左表或右表的结果

查询了参加考试的同学信息:

--①现在成绩表中查询出每个学生的学号
--②然后在学生表中查询相关的学生的信息
SELECT  studentNo,studentName,SubjectNO,`StudentResult` from student s Right JOIn result r ON r.studentNo=s.studentNo INNER JOIN `subject` sub 
ON r.SubjectNo=sub.SubjectNo
5.自连接

核心思想一张表拆成两张表

在这里插入图片描述

父类

categoryidcategoryName
2信息技术
3软件开发
5美术设计

子类

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps设计

子类的pid=父类的categoryid id即可得到

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps设计
SELECT a.'categoryName' AS '父类',b.'categoryName' AS '子类' from 'category' AS a,'category' AS b where a.`categoryid` =b.`categoryid`

6.分页和排序
1.排序

ORDER BY [字段名] ASC/DESC

2.LIMIT—分页

第一页 LIMIT 0,5 1~5条

第二页 LIMIT 5,5 6~10条

第N页 LIMIT (N-1)*5,5 (N-1)*5+1~pagesize条

n–当前页

总页数=[数据总数/页面大小]

7.子查询和嵌套查询
1.高等数学分数不小于80分的学生的学号和姓名
SELECT s.`StudentNo`,`StudentName` from student s
INNER JOIN result r ON r.StudentNo=s.StudentNo
where `StudentNo`>=80 AND `SubjectNo`=(SELECT SubjectNo from `subject` Where `SubjectName` =`高等数学`) 

注意 此时的内查询的返回值只能是一个,也就是变量的值确定时只能唯一

2.第二种解法
select StudentNo,StudentName from student WHERE StudentNo IN(
 SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo=(
 Select SubjectNo FROM subject WHERE `SubjectName`='高等数学'
 )
)
8.分组和过滤(GroupBy / Having)
 -- 查询不同课程的平均分,最高分,最低分
 -- 前提:根据不同的课程进行分组
 
 SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
 FROM result AS r
 INNER JOIN `subject` AS s
 ON r.subjectno = s.subjectno
 GROUP BY r.subjectno
 HAVING 平均分>80;
 
 /*
 where写在group by前面.
 要是放在分组后面的筛选
 要使用HAVING..
 因为having是从前面筛选的字段再筛选,而where是从数据表中的>字段直接进行的筛选的,那时的平均分还未生成
 */
9.常用函数

数据函数

 SELECT ABS(-8);  /*绝对值*/
 SELECT CEILING(9.4); /*向上取整*/
 SELECT FLOOR(9.4);   /*向下取整*/
 SELECT RAND();  /*随机数,返回一个0-1之间的随机数*/
 SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

字符串函数

 SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
 SELECT CONCAT('我','爱','程序');  /*合并字符串,参数可以有多个*/
 SELECT INSERT('我爱编程helloworld',1,2,'超级热爱');  /*替换字符串,从某个位置开始替换某个长度*/
 SELECT LOWER('KuangShen'); /*小写*/
 SELECT UPPER('KuangShen'); /*大写*/
 SELECT LEFT('hello,world',5);   /*从左边截取*/
 SELECT RIGHT('hello,world',5);  /*从右边截取*/
 SELECT REPLACE('狂神说坚持就能成功','坚持','努力');  /*替换字符串*/
 SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
 SELECT REVERSE('狂神说坚持就能成功'); /*反转
 
 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 FROM student WHERE studentname LIKE '周%';

日期和时间函数

 SELECT CURRENT_DATE();   /*获取当前日期*/
 SELECT CURDATE();   /*获取当前日期*/
 SELECT NOW();   /*获取当前日期和时间*/
 SELECT LOCALTIME();   /*获取当前日期和时间*/
 SELECT SYSDATE();   /*获取当前日期和时间*/
 
 -- 获取年月日,时分秒
 SELECT YEAR(NOW());
 SELECT MONTH(NOW());
 SELECT DAY(NOW());
 SELECT HOUR(NOW());
 SELECT MINUTE(NOW());
 SELECT SECOND(NOW());

系统信息函数

 SELECT VERSION();  /*版本*/
 SELECT USER();     /*用户*/
 

聚合函数

 -- 聚合函数
 /*COUNT:非空的*/
 SELECT COUNT(studentname) FROM student;
 SELECT COUNT(*) FROM student;
 SELECT COUNT(1) FROM student;  /*推荐*/
 
 -- 从含义上讲,count(1) 与 count(*) 都表示对全部数据行的查询。
 -- count(字段) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
 -- count(*) 包括了所有的列,相当于行数,在统计结果的时候,包含字段为null 的记录;
 -- count(1) 用1代表代码行,在统计结果的时候,包含字段为null 的记录 。
 /*
 很多人认为count(1)执行的效率会比count(*)高,原因是count(*)会存在全表扫描,而count(1)可以针对一个字段进行查询。其实不然,count(1)和count(*)都会对全表进行扫描,统计所有记录的条数,包括那些为null的记录,因此,它们的效率可以说是相差无几。而count(字段)则与前两者不同,它会统计该字段不为null的记录条数。
 
 下面它们之间的一些对比:
 
 1)在表没有主键时,count(1)比count(*)快
 2)有主键时,主键作为计算条件,count(主键)效率最高;
 3)若表格只有一个字段,则count(*)效率较高。
 */
 
 SELECT SUM(StudentResult) AS 总和 FROM result;
 SELECT AVG(StudentResult) AS 平均分 FROM result;
 SELECT MAX(StudentResult) AS 最高分 FROM result;
 SELECT MIN(StudentResult) AS 最低分 FROM result;
10.select 总结

五.MD5加密

1.概念

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

MD5不可逆,具体的值MD5是一样的

MD5破解网站的原理,把大量的数据封装成字典,里面对应着MD5加密后的值和加密前的值

CREATE TABLE test(
	`Nid` int NOT NULL PRIMARY KEY auto_increment,
	`Nname` VARCHAR(10) NOT NULL COMMENT '名字',
	`Nsex` VARCHAR(2) DEFAULT '男' COMMENT '性别'
	)ENGINE=INNODB DEFAULT CHARSET=utf8
	alter TABLE  test add `Npass` varchar(50) NOT NULL
	insert test(Nname,Npass) values('李四',MD5('123456'))
	select Nname from test
	where Npass=MD5('123456')

六.事务(ACID)

原子性(Atomicity)

要么成功,要么失败

一致性(Consistency)

事务前后数据的完整性必须保持一致。 ¥1000

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

持久性(Durability)

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

事务的隔离级别
脏读

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

不可重复读

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

页面统计查询值
这里写图片描述
点击生成报表的时候,B有人转账进来300(事务已经提交)
这里写图片描述

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)

这里写图片描述

CREATE TABLE bank(
	`Nid` int NOT NULL PRIMARY KEY auto_increment,
	`Nname` VARCHAR(10) NOT NULL COMMENT '名字',
	`money` DECIMAL(7,2)  COMMENT '余额'
	)ENGINE=INNODB DEFAULT CHARSET=utf8
	
	insert into bank(Nname,money) values("张三",2000.00),("李四",10000.00)
	--执行事务之前要把关闭事务
	set autocommit=0;--关闭自动提交
	START TRANSACTION -- 开启一个事务
	
	update bank set money=money-500 where Nname='张三'
	update bank set money=money+500 where Nid=6
	
	COMMIT;
	ROLLBACK;
		set autocommit=1;--开启自动提交

七.索引

索引可以提高获取数据的效率

1.索引的分类
  • 主键索引 (primary key)
    • 唯一的标识,主键不可重复
  • 唯一索引(unique key)
    • 索引可以重复
  • 常规索引(key/index)
    • 默认的索引
  • 全文索引(FullText)
    • 快速定位数据

基础语法

--索引的使用
--1.在创建的时候给字段增加索引
--2.创建完毕,增加索引

--显示所有的索引
show index from student

--增加一个全文索引
alter table student add FULLTEXT `studentName`(`studentName`);

--explain 分析sql执行情况
explain select * from student;-- 非全文索引

explain select * from studetn where match(studentName) against('刘');
2.索引的原则
  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据量的表不加索引
  • 索引一般加载查询的字段上

索引的数据结构

Hash 类型索引

Btree :innoDB 的默认数据结构

参考:索引数据结构

八.权限管理

1.用户管理

在这里插入图片描述

2.数据备份

命令行导出

#mysqldump  -h主机  -u 用户名 -p 密码  数据库 表名 > 物理磁盘位置/文件名 
mysqldump -hlocalhost -uroot -proot school student > D:/a.sql
#mysqldump  -h主机  -u 用户名 -p 密码  数据库 表名1 表名2 表名3 ... > 物理磁盘位置/文件名 
mysqldump -hlocalhost -uroot -proot school student teacher > D:/a.sql
#导入
#登录状态下,切换到指定的数据库
#source  备份文件的地址
source d:/a.sql

九.数据库的设计规范

三大范式

第一范式(1NF)

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

举例说明:

img

在上面的表中,“家庭信息”和“学校信息”列均不满足原子性的要求,故不满足第一范式,调整如下:

img

可见,调整后的每一列都是不可再分的,因此满足第一范式(1NF);

第二范式(2NF)

前提:满足第一范式

每张表只做一件事

举例说明:

img

在上图所示的情况中,同一个订单中可能包含不同的产品,因此主键必须是“订单号”和“产品号”联合组成,

但可以发现,产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关,但是订单金额和订单时间仅与“订单号”相关,与“产品号”无关,

这样就不满足第二范式的要求,调整如下,需分成两个表:

img img

第三范式(3NF)

前提:满足第一范式和第二范式

第三范式保证每一列数据都和主键相关,而不能间接相关

举例说明:

img

上表中,所有属性都完全依赖于学号,所以满足第二范式,但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”,

而不是主键“学号”,所以需做如下调整:

img img

(规范数据库的设计)

规范性和性能的问题

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

  • 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当的考虑一下规范性!
  • 故意给某些表增加一些冗余字段.
  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

十.JDBC(重点)

1.数据库驱动

在这里插入图片描述

程序通过数据库驱动和数据库操作

2.JDBC

SUN 为了简化开发人员的对数据库的统一操作,提供了一个操作数据库的规范

在这里插入图片描述

3.IDEA连接数据库

①在项目的根目录下创建一个lib文件夹

在这里插入图片描述

②将mysqlconnection驱动架包导进去

在这里插入图片描述

在这里插入图片描述

③使架包和项目融合

④编写java代码进行连接

import java.sql.*;

public class jdbc_connection {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");  //固定写法 加载驱动
        //2.用户信息和url
        String url="jdbc:mysql://localhost/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username="root";
        String pwd="root";
        //3.驱动进行连接,返回数据库connection
        Connection connection = DriverManager.getConnection(url, username, pwd);
        //4.connction创建对象进行返回sql对象
        Statement statement = connection.createStatement();
        //5.sql对象执行语句
        String sql="select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id"+resultSet.getObject("id"));
            System.out.println("id"+resultSet.getObject("name"));
            System.out.println("id"+resultSet.getObject("pwd"));
        }
        //6.释放链接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

在这里插入图片描述

statement对象

封装一个工具类专门连接mysql

①先在src目录下新建一个db.properties文件代码如下:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
pwd=root

②新建工具类

package demo1.utility;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class Jdbcutility {
    private static String driver =null;
    private static String url=null;
    private static String username=null;
    private static String pwd=null;
    static{
            try {
                InputStream in = Jdbcutility.class.getClass().getResourceAsStream("src/db.properties");
                Properties properties = new Properties();
                properties.load(in);
                driver=properties.getProperty("driver");
                url=properties.getProperty("url");
                username=properties.getProperty("username");
                pwd=properties.getProperty("pwd");
                //1.加载驱动
                Class.forName(driver);

            } catch (Exception e) {
                e.printStackTrace();
            }

    }
    //获取数据库连接对象
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,pwd);
    }
    //释放资源
    public static void relese(Connection conn, Statement st, ResultSet rs)  {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

③测试插入数据库

package demo1.connection;
import demo1.utility.Jdbcutility;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Insert {
    public static void main(String[] args)  {
          Connection connection=null;
          Statement statement=null;
          ResultSet resultSet=null;
        try{
            //获取数据库连接
             connection = Jdbcutility.getConnection();
             //创建数据库对象
             statement = connection.createStatement();
            String sql="insert into user VALUES(4,'赵刘','34567')";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("数据插入成功");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        finally {
       Jdbcutility.relese(connection,statement,null);
        }

    }
}

SQL注入问题

sql常见漏洞,输入一些合法的字符串既可以攻破数据库

本质 拼接字符串

PreparedStatement对象

可以有效解决sql注入

十一.idea连接mysql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rF3cFJv5-1610808459734)(https://img.rruu.net/image/6002fb69b34e5)]

连接成功之后在选择自己需要的数据库

更新数据完之后必须点击上交

默认写SQL地方

在这里插入图片描述

连接失败解决办法

十二.数据库连接池

数据库连接–执行完毕–释放

连接–释放 浪费系统的资源

池化技术:准备一些预先的资源,过来就连接预先准备好的

------开门------业务员; 等待 — 服务 — 关门

常用连接数:10个

最小连接数:10个

最大连接数:15个

等待超时:100ms

不需要在自己创建数据库连接

**实现方法 ** 实现一个接口 DataSource

开源数据源实现

DBCP

导入所需要的

C3P0
在这里插入图片描述

Druid–阿里巴巴数据源–spring–boot

   }

}

}


> SQL注入问题

sql常见漏洞,输入一些合法的字符串既可以攻破数据库

**本质** 拼接字符串

![](https://i-blog.csdnimg.cn/blog_migrate/465606d26cbd4301ba95d3a7b9d2b207.png)

**PreparedStatement对象**

可以有效解决sql注入

![](https://i-blog.csdnimg.cn/blog_migrate/e37ccfc1a1fc1001f7b47c6726760a01.png)

### 十一.idea连接mysql

![](https://img-blog.csdnimg.cn/img_convert/4a184c16329fa19b832f55a03c955daa.png)

连接成功之后在选择自己需要的数据库

![](https://i-blog.csdnimg.cn/blog_migrate/640effdee6baf7c741c095ae89b34823.png)

更新数据完之后必须点击上交

![](https://i-blog.csdnimg.cn/blog_migrate/2389457a3eed7d35667a61554edfda6a.png)

默认写SQL地方

![](https://img-blog.csdnimg.cn/img_convert/951a9986a3f3c9052f8448c6b9a449b3.png)

连接失败解决办法

![](https://i-blog.csdnimg.cn/blog_migrate/7c092d919fae7543ce725c42943a6e5a.png)
### 十二.数据库连接池

数据库连接--执行完毕--释放

连接--释放   浪费系统的资源

**池化技术:准备一些预先的资源,过来就连接预先准备好的**

------开门------业务员;  等待  ---  服务  --- 关门

常用连接数:10个

最小连接数:10个

最大连接数:15个

等待超时:100ms



不需要在自己创建==数据库连接==

**实现方法 ** 实现一个接口  DataSource

> 开源数据源实现

DBCP

导入所需要的

C3P0

![](https://img-blog.csdnimg.cn/img_convert/ac2243d3207d0bd614a0de129898206c.png)

Druid--阿里巴巴数据源--spring--boot

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值