数据库入门到精通(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>2	false
OR	||	5>1AND>2	true

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	操作符为空,返回true	a is NUll
IS NOT NULL	操作符不为空,返回true	a is Not NULL
BETWEEN	若a在b和c之间,结果返回真	a between b and c
like	SQL匹配,如果a匹配b，返回true	a like b
in	a 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.自连接

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

父类

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

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

子类的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)

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

举例说明：

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

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式(2NF)

前提:满足第一范式

每张表只做一件事

举例说明：

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

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

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

第三范式(3NF)

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

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

举例说明：

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

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

(规范数据库的设计)

规范性和性能的问题

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

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

十.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