Java基础之MySQL数据库

2020-04-07  本文已影响0人  在error边缘疯狂试探

数据库的操作命令：

数据库建表操作：

--auto_increment 表示自增
--comment 表会备注，注意，这里的 ` 符合不是单引号！
--engine是引擎，默认是innodb 
create table [if not exites] `student` {
  `id` int(4) not null auto_increment comment '学号',
  `name` varchar(20) not null default '匿名' comment '姓名',
  `birthday` datetime default null comment '出生日期',
  primary key (`id`)
}engine=innodb default charset=utf8

查看创建hello数据库的语句：show create database hello --创建表的类似。

数据表存在的位置

物理位置：在mysql的data目录下。

数据库表的操作:

--修改表名：alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1
--增加表的字段 alter table 表名 add 字段名 属性
alter table teacher1 add age int(2)
--修改表的字段（重命名，修改约束）两种用不同的关键字！
alter table teacher1 change age age1 int（5） --重命名，modify:修改

alter table teacher1 modify age varchar（5） --修改约束 

--删除表的字段
alert table teacher1 drop age1
--删除表
drop table [if exists] teacher1
--表添加外键
--constraint：约束，references：引用
alter table `student` add
constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
--`FK_`这是固定的格式，后面接外键的名称

Tips：上诉的外键操作都是物理外键，不建议这样操作，存在外键和级联会导致delete或update表时考虑外键约束，令人头疼，利用程序来实现表的外键，表只用来存数据。

DML语言（常用）

--对表字段插入具体值的操作
insert into `teacher` (`id`,`name`,`age`) values('001','张三','18');--如果表的字段不写则会一一对应
--对表字段的内容更新的操作
update `teacher` set name ='cwx' where id=2;-- 别忘记加条件！没加则所有的name都为cwx，直接爆炸。
--删除数据
delete form `teacher` where id=1;
delete from `teacher`; --删除所有表内容
truncate table `teacher`;--清空表

Tips：delete 和 truncate的区别，truncate会清空自增的数据且不会影响事务，delete就是纯删除。

数据库引擎delete删除的问题，重启数据库发生的现象：

DQL查询数据（重点）

模糊查询（比较运算符）

Tips：like 一般可以结合 %（代表0个或多个字符） 或 _（代表一个字符）字符使用。如：select studentName from student where name like '陈%';(查找姓陈的名字)

联表查询Join

--先实现前两个表的查询，在利用学科的subjectNo进行三表查询
select s.studentNo,studentName,subjectName,studentResult from student as s
left join result on s.studentNo = r.studentNo
inner join subject as sub where r.subjectNo = sub.subjectNo

分页和排序

--查询数据库这门课的成绩，升序，desc（降序），且从第0个数据开始，到第5个，5代表输出的个数
select `source` from result
where subjectName = '数据库'
order by `source` asc
limit 0,5;  --想要看下一页，则改为limit 5,5 第N页为limit (N-1)*5,5

Tips：order by 必须在where 后面，limit 必须在order by 后面！

子查询（在where 条件里面加计算得到的结果，如select ...from...）

聚合函数

select count(studentName) from student --查询学生的人数
select count(*) from student   --查询学生的人数
select count(1) from student   --查询学生的人数

Tips：三者的区别，count(指定列) 会忽略null值，count(*)或count(1)不会忽略null，其中count(*)是查询所有列，本质是计算行数，count(1)是包括了忽略所有列，将所有的字段变成1，按行查。

--查询不同课程的平均分，且平均分要大于80
select subjectName,AVG(studentResult) from result r 
inner join subject sub
group by r.subjectNo  --分组展示，如果没有加group by 则只会展示一列
having AVG(studentResult)>80;

select完整语法

select [all | distinct] xxx from table_name
[left | right | inner join table_name]    --联合查询
[where xxx]      --指定结果满足的条件
[group by xxx]   --进行分组
[having xxx]     --分组后在进行条件判断
[order by xxx]    --排序
[limit a,b]      --进行分页处理

Tips：这里的顺序是规定死的，各个关键字必须按顺序要求排好。

事务(Transaction)

Tips：MySQL默认开启事务。

索引

索引是帮助MySQL高效的获取数据的数据结构，它的本质是数据结构。在数据量庞大的时候，查询效率明显提高。

索引原则

三大范式（规范数据库的设计）

Tips：考虑到性能的问题，一般在真实的项目中并不会这样做，例如在阿里公司就规定了关联查询表不得超过三张，也有可能故意给某些表增加一些冗余的字段（从多表查询到单表查询）。

JDBC

public class JdbcDemo{
  public static void main(String[] args )throw ClassNotFoundException{
    //1、加载数据库驱动
    //  DriverMannger.registerDriver(new Driver());原始的方法，先注册一个驱动，不建议使用
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2、用户信息和URL
    String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&&characterEncoding=utf-8&&serverTimezone=UTC";
    String username = "root";
    String password = "123456";
    //3、连接数据库
    Connection connection = DriverMannger.getConnection(url,username,password);
    //4、创建执行SQL的对象
    Statement statement = connection.createStatement();
    //5、执行SQL语句
    String sql = "select * from 表名";
    ResultSet resultSet = statement.executeQury(sql);
    //6、返回结果集并输出
    while(resultSet.next()){
    System.out.println("id="+resultSet.getObject("id"));
    System.out.println("name="+resultSet.getObject("name"));
    System.out.println("age="+resultSet.getObject("age"));
}

    //7、释放连接
    resultSet.close();
    statement.close();
    connection.close();

}
}

connection就是相当于数据库的对象，可以实现事务的提交和回滚，如，connection.commit(),connection.rollback()，但前提是要开启事务connection.setAutoCommit(false)。

package 数据库相关.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
       private static String driver=null;
       private static String url=null;
       private static String username=null;
       private static String password =null;
   static {
       //获得properties文件资源
       InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("JdbcConnection.properties");
       Properties properties = new Properties();
       try {
           properties.load(in);

           driver=properties.getProperty("driver");
           url= properties.getProperty("url");
           username=properties.getProperty("username");
           password=properties.getProperty("password");
           //驱动只需要注册一次就可以
           Class.forName(driver);
       } catch (IOException e) {
           e.printStackTrace();
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }

   }
   //获取连接的方法
   public static Connection getConnection() throws SQLException {
       return   DriverManager.getConnection(url, username, password);

   }
   //释放connection资源
   public static void close(Connection con, Statement st, ResultSet rs){
       if(con!=null){
       try {
       con.close();
       } catch (SQLException e) {
           e.printStackTrace();
       }
       }
       if(st!=null){
           try {
               st.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }

}


-->这个是JdbcConnection.properties文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/students?useSSL=false&serverTimezone=UTC
username=root
password=123456

package 数据库相关;
import 数据库相关.utils.JdbcUtils;
import java.sql.*;
//主函数，实现连接数据库，这里是将连接和创建驱动抽取出来了，直接利用utils包中的方法
public class ContentMysql {
    public static void main(String args[]) throws SQLException {
        Connection conn=null;
        Statement sql = null;
        ResultSet rs =null;
        try {
            conn=  JdbcUtils.getConnection();
            sql=conn.createStatement();
            rs=sql.executeQuery("SELECT * FROM mess"); //查询mess表
            while(rs.next()) {
                String number=rs.getString(1);
                String name=rs.getString(2);
                Date date=rs.getDate(3);
                float height=rs.getFloat(4);
                System.out.printf("%s\t",number);
                System.out.printf("%s\t",name);
                System.out.printf("%s\t",date);
                System.out.printf("%.2f\n",height);
            }


        }
        catch(SQLException e) {
            System.out.println(e);
        }finally {
            JdbcUtils.close(conn,sql,rs);
        }
    }

}

Tips：记住啊，properties文件应该放在src目录文件下，别乱放！否则读取不到。

SQL注入的问题（本质是SQL存在拼接）

在登录场景下，用户输入用户名为'or '1=1 ,密码也为'or '1=1，这就导致后台判断为密码是'' or '1=1'判断为真，从而出现问题。

数据库连接池

由于我们原先是使用数据库连接--->执行完毕--->释放，每次如果都是这样操作将会大大浪费系统资源，于是使用连接池技术。

上一篇 下一篇

猜你喜欢

热点阅读