mysql

1.什么是数据库

数据库：（DB，DataBase）

作用：用来存储数据，管理数据。Txt，Excel，word是在数据库出现之前存储数据的。

概念：数据仓库，安装在操作系统上的软件。

数据库是所有软件体系中最核心的存在

2.数据库分类

关系型数据库（SQL）

• Mysql，oracle，sqlServer，Sqlite
• 通过表和表之间，行与列之间的关系进行数据的存储……

非关系型数据库（NoSql）

• redis，MongDb
• 对象存储，通过对象的自身属性来决定。

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据。
• Mysql

3.数据库连接

命令行连接：

mysql -uroot -p123123 --数据库注释

数据库基本操作：

show databases; --显示所有数据库
 use mysql;     --切换数据库
 show tables;  --显示所有表
 describe user; --显示表的具体数据
 exit;  --退出

3.数据库的操作

数据库的字段属性：

Unsigned：

• 无符号整数
• 声明了该列值不能为负数

zeroFill:

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

自增:

• 自增
• 可以自定义增加的数量和步长

非空:

• NUll not null
• 设置为非空不给赋值就会报错

默认:

• 如果不赋值则为默认设置的值

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

操作数据库:

创建数据库

create database westos;

删除数据库

drop database westos;

使用数据库

use shop;

查看所有数据库

show databases;

操作数据库的表：

创建表

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment 'id',
 
`name` varchar(30) not null default '匿名' comment '姓名',
 
`pwd` varchar(20) not null default '123123' comment '密码',
 
`sex` varchar(2) not null default 'nv' 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;

格式：

create table if not exists `表名`(
    `字段名` 列类型 [属性][索引][注释],
    `字段名` 列类型 [属性][索引][注释],
        ......
    `字段名` 列类型 [属性][索引][注释]
);

删除表

drop table teacher if exists

修改表

修改表名: alter table 表名 as 新表名

alter table student as teacher

添加一个字段：alter table 表名 add 字段名 属性

alter table teacher add age int(11)

修改表的字段 类型：alter table 表名 modify 字段名 列属性

alter table teacher modify age varchar(10)

重名字：alter table 表名 change 旧名字 新名字

alter table teacher change age age1 int(1)

删除字段

alter table teacher drop age1

查看表

desc 表名

数据表类型

数据库引擎：

innodb：默认使用

myisam：早期使用

4.mysql数据管理

外键

create table `grade`(
 
 `gradeId` int(10) not null auto_increment comment '年级id',
 
`gradeName` varchar(20) not null comment '年级名称',
 
primary key(`gradeId`)
 
)engine=innodb default charset=utf8;

--学生表的gradeId引用年级表的gradeId
--定义外键key
--给外键添加约束reference引用

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment 'id',
 
`name` varchar(30) not null default '匿名' comment '姓名',
 
`pwd` varchar(20) not null default '123123' comment '密码',
 
`sex` varchar(2) not null default 'nv' comment '性别',
 
`birthday` datetime default null comment '出生日期',
 
`address` varchar(100) default null comment '住址',
 
`email` varchar(50) default null comment '邮箱',
 
primary key(`id`)
 key `fk_gradeId` (`gradeId`),
constraint `fk_gradeId` foreign key(`gradeId`)references`grade`(`gradeId`)        
)engine=innodb default charset=utf8;

外键在开发中不建议使用，因为会在删除更新的时候产生级联，造成错误！

DML语言

数据库管理语言：

插入: insert into 表名(字段名) values(属性)

insert into grade(gradeName) values('大四');

insert into grade (gradeName) values('大二'),('大三');

更新：update set 表名 修改的字段和值 where 条件，不指定条件会修改所有的列。

update grade set gradeName='大一' where gradeId=1;

删除：delete from 表名

delete from grade where gradeId=1;

清空：truncate 表名

truncate grade;

truncate和delete删除数据的区别：

• truncate会清空自增归零，从1开始，delete不会（除非断电重启，因为mysql在内存中）。
• 不会影响事务。

查询：select 字段 from 表名

-- 查询表的所有字段
select * from grade; 
-- 查询id字段
select gradeId from grade;
-- 起别名gradeid为id 
select gradeId as id from grade;
-- 在查出来的数据前面加上字符串 
select concat('加上字符串：',gradeName) as 名字 from grade;
-- 按照id条件查询
select gradeName as 名字 from grade where gradeId=2;
-- 按照name条件进行查询
select gradeName as 名字 from grade where gradeName='大二';
-- 去重
select distinct gradeId from grade;
-- 查询系统版本
select version();
-- 用来计算
select 100*3-1 as jisuanjieguo;
-- 查询自增的步长
select @@auto_increment_increment

模糊查询

like结合: %代表0到任意个字符，结合_代表一个字符

select gradeName from grade where gradeName like '大%';

select gradeName from grade where gradeName like '%er';

select gradeName from grade where gradeName like 'er_';

in：查询具体的一个或者多个值

select gradeName from grade where gradeName in('大er','大san');

联表查询

join（要连接的表）……on（等值判断）

select gradeName,name,sex from student inner join grade on where grade.gradeId=student.id;

自连接

自己的表和自己的表连接，一张表拆为两张表。

select a.categoryName as father ,b.categoryName as son from category as a,category as b where a.categoryid=b.pid

排序

order By通过哪个字段排序。desc降序，asc升序。

select email from student order by email asc;

select email from student order by email desc;

分页

limit 起始页，页面的大小。

将数据分页显示，使用户体验更好。

select email from student limit 1,1;

子查询

在where语句中嵌套一个子查询。

select gradeName from grade where gradeName=(select name from student where name='匿名');

group by

通过什么字段来分组。

having

分组之后的条件

5.mysql函数

常用函数

--绝对值
select abs(-8);
--向上取整
select ceiling(9.4);
--向下取整
select floor(9.4);
--随机数
select rand();
--判断正负数
select sign(-1);
--字符串长度
select char_length('lllll');
--拼接字符串
select concat('w','s');
--字符串替换
select insert('woaibiancheng',1,4,'llll');
--转换大小写
select lower('SS');
select upper('hh');
--第一次出现的字串的索引
select replace('sss','ss','ww');
--返回指定的字符串 从第几个开始截取几个
select substr('ssssss',4,6);
--获取当前时间
select current_date();
select now();
select localtime();
select current();
select year(now());
select month(now());
       ......

聚合函数

select count(*) from student;
select count(1) from student;
select count(name) from student;
select sum(name) from student;
select avg(name) from student;
select min(name) from student;
select max(name) from student;

数据库级别的md5加密算法

--建表
mysql> create table testmd5(
 
`id` int(4) not null,
 
`name` varchar(20) not null,
 
`pwd` varchar(50) not null,
 
primary key(`id`)
 
)engine=innodb default charset=utf8;

--插入数据
insert into testmd5 values(1,'zhangsan','123123'),(2,'lisi','123123'),(3,'wangwu','123123');

--更新密码为MD5
update testmd5 set pwd=md5(pwd);

--插入密码为MD5
insert into testmd5 values(4,'xixi',md5('123123'));

--查询密码为MD5
select * from testmd5 where name='xixi' and pwd=md5('123123');
+----+------+----------------------------------+
| id | name | pwd                              |
+----+------+----------------------------------+
|  4 | xixi | 4297f44b13955235245b2497399d7a93 |
+----+------+----------------------------------+
1 row in set

6.事务

要么都成功，要么都失败。

1.sql执行 a给b转账 2.sql执行 b接受a的转账

同一事务一起执行。

事务的原则

• 原子性：要么都成功，要么就失败
• 一致性：事务前后数据保持一致
• 持久性：事务一旦提交不可逆
• 隔离性：数据库为每一个用户开启一个事务，不同事务之间要相互隔离。

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

虚读：一个事务读取到了另一个事务正在插入的事务。

事务执行流程

--关闭自动提交
set autocommit=0;
--事务开启
start transaction;
     --update
     --delete
--提交持久化 成功
commit;
--回滚 回到原来的样子 失败
rollback
--事务结束
set autocommit=1;

模拟转账

--建表
create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`money` decimal(9,2) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;
--插入数据
insert into account(`name`,`money`) values('A',2000),('B',10000);
--关闭自动提交
set autocommit=0;
--开启事务
start transaction;
--更新数据
update account set money=money-500 where `name`='A';
update account set money=money+500 where `name`='B';
--提交成功
commit;
--回滚失败
rollback;
--开启自动提交
set autocommit=1;

7.索引的分类

• 主键索引
  • 唯一的标识，主键不可重复，只有一个列作为主键
• 唯一索引
• 常规索引
• 全文索引

创建索引在查询数据较多的时候可以大大提升查询速度。

索引创建:create index 索引名 on 表(字段)

create index id_app_user_name on app_user(name)

索引原则

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

8.规范数据库设计

当数据库比较复杂的时候，就需要规范设计了。

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库的删除和插入都会麻烦，异常
• 程序性能差

良好的数据库

• 节省内存空间
• 保证数据库的完整性
• 方便我们开发系统

软件开发中关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图e-r图

三大范式

第一范式1NF

• 保证每一列不可再分

第二范式2NF

• 已达到第一范式
• 每张表的只描述一个事情

第三范式3NF

• 满足第二范式
• 数据库的每一列数据都和主键直接相关，而不能间接相关。

规范性和性能问题

在实际开发中也不能一味地追求满足三大范式

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

• 考虑商业化的需求和目标，数据库的性能更加重要
• 在规范性能问题的时候，需要适当的考虑一下规范性
• 故意给某些表增加一些冗余的字段。（使之从多表查询变为单表查询）

jdbc

数据库驱动

不同的数据库有不同的驱动，应用程序需要先连接到驱动，才能连接到数据库。

jdbc

为了简化开发人员对数据库的操作，提供了一个java操作数据库的规范，俗称jdbc。

第一个jdbc程序

1.创建一个数据库

create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`pwd` varchar(10) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;

2.添加数据库驱动

3.编写代码

package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author panglili
 * @create 2022-07-18-9:05
 */
//我的第一个jdbc程序
public class demo1 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false";
        String name="root";
        String pwd="123123";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.执行sql对象
        Statement statement = connection.createStatement();
        //5.查看结果
        String sql="select * from account";
        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("money"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }


}

分析jdbc对象

• Class：加载驱动，固定写法
• url：连接的统一资源定位符
• username：用户名
• password：密码
• connection：获取数据库对象，代表数据库
• statement：执行sql的对象
• resultset：sql对象执行方法获取返回的集合

sql注入：

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

java解决的方法：

• 使用prepareStatement

package com.sql;

import java.sql.*;

/**
 * @author panglili
 * @create 2022-07-18-9:57
 */
public class demo2 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false";
        String name="root";
        String pwd="123123";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.预编译sql先不赋值
        String sql="delete  from account where id=?";
        PreparedStatement st = connection.prepareStatement(sql);

        //5.手动给参数赋值
        st.setInt(1,2);
        //6.执行 删除语句会返回受影响的行数
        int i = st.executeUpdate();

        if(i>0){
            System.out.println(i+"行删除成功");
        }
        //7.释放连接
        st.close();
        connection.close();
    }
}

prepareStatement防止sql注入的本质就是，把传递进来的参数当做字符，如果存在转义字符会被直接转义！