数据库学习日记

MYSQL

命令行进入mysql

1. 配置环境变量C:\Program Files\MySQL\MySQL Server 5.7\bin
2. DOS界面输入 mysql -uroot -p 自己密码

sql常用语句以及注意事项

注意事项:

1. 每一句话都要加; 结尾才能执行
2. sql可以分行来写同一句话
3. – 表示注释
4. 关键字不区分大小写
5. 固定的语法和关键字!!! 不会就百度

常用语句:

show databases; -- 查看所有数据库
use [数据库名];
show tables; -- 查看数据库中所有的表
describe [表名]; --表的所有信息
exit; -- 退出链接
--单行注释 
/*
多行注释
*/

操作数据库

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

1. 创建数据库 create database [if not exists ] {表名};
2. 删除数据库 drop database ;
3. 使用数据库 use {库名};
4. 查看数据库 show databases; – 查看所有数据库

数据库的数据类型

数值:

• tinyint 1字节
• smallint 2字节
• mediumint 3字节
• int 4字节
• bigint 8字节
• float 单精度 4字节(精度问题)
• double 双精度 8字节(精度问题!)
• decimal 字符串形式的浮点数(金融计算的时候常用)

字符串:

• char 字符串(固定大小 0~255)
• varchar 可变长字符串 0~65535 对应String
• tinytext 微型文本 0~2^8-1
• text 文本串 0~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:

• 没有值,未知
• 注意不要用null进行运算,结果为null

字段属性(重点)

unsigned: 就是非负的意思…

zerofill: 不足的位数用0来填充 例如 int(3)的数据 复制为1 结果就是001

auto increment: 自增,默认加1,通常用来设置主键,必须是整型,可以自定义设计主键自增的起始值和步长

not null: 非空,一个简单的约束,就是字面意思

default: 默认值,仅此而已…

数据库XXX语言

DDL(定义) DML(操作) DQL(查询) DCL(控制)

表的创建

数据库引擎的区别

设置字符集

如果不设置字符集,MYSQL是默认不支持中文的

可以直接在建表的时候吧字符集修改了

CHARSET = utf8

也可以在MY.ini文件中修改默认的字符集

character-set-server=utf8

修改/删除表字段

被引用的字段和表示无法删除的!

所有的创建和删除操作都应该加上判断,以免出错

表的规范

1. id 主键
2. version 版本号,用于解决乐观锁的ABA问题
3. is_delete 标记是否被删除(伪删除,因为实际上没有删)
4. gmt_create 创建时间
5. gmt_update 修改时间

MySQL数据管理

外键

上面的操作是数据库级别的外键,不推荐使用(避免数据库过多造成困扰)

=== 最佳实践 ===

• 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
• 我们想用多张表的数据,想使用外键都尽可能的使用程序去实现

设置了外键的值必须是引用的表的对应字段的值的子集

其实外键的本质是一种约束,所以可以通过创建约束的形式来创建外键

DML语言(全部记住)

数据库的意义:数据存储,数据管理

DML语言: 数据操作语言

• insert
• update
• delete

增加

insert into 表名(`字段1名`,`字段2名`...) --这里字段名不写就默认是全体
values(第一组数据的字段1,第一组数据的字段2....),(第二组数据的字段1,第二组数据的字段2...) --这里一定要一一对应!!!!

SQL只要不加分号,就认为是一句话,所以一个语句可以通过分行来变美观一点

修改

update `表名` set `字段名1` = '要修改的值1', `字段名2` = '要修改的值2' where 判断条件-- 如果没有加判断条件,则默认把所有的值全部修改

常用的判断符 != <>, =, or, and,between … and …

没有实践就不会发现问题…

在安全模式下,如果要更新表 而 where 条件不是主键的话 是不允许更新的…

取消安全模式的代码是

SET SQL_SAFE_UPDATES = 0;

删除

delete from `表名` where 判断条件
-- delete会把数据删除(避免这样写)
truncate `表名` --完全清空一个数据库,标的结构和索引不会变

delete和truncate的区别

• 相同点:都删除数据,都不会删除表结构
• 不同点:
  • truncate 重新设置自增列,计数器会归零
  • truncate 不会影响事务

DQL查询数据(重中之重)

DQL(Data Query Language: 数据查询语言)

• 所有的查询操作都要用它 Select
• 简单的查询, 复杂的查询他都能做
• 数据中最核心的语言,最重要的语句
• 使用频率最高的语句

select基本用法:

联表查询

表连接有几种?

sql表连接分成外连接、内连接和交叉连接。

一.外连接

概述：

外连接包括三种,分别是左外连接、右外连接、全外连接。

对应的sql关键字:LEFT/RIGHT/FULL OUTER JOIN,通常我们都省略OUTER关键字,写成LEFT/RIGHT/FULL JOIN。

在左、右外连接中都会以一种表为基表，基表的所有行、列都会显示，外表如果和条件不匹配则所有的外表列值都为NULL。

全外连接则所有表的行、列都会显示，条件不匹配的值皆为NULL。

1.左外连接示例:

sql语句: select * from TableA left join TableB on TableA.id=TableB.id

结果:

注释:TableA(基表)中所有的行列都显示了,第三行的条件不匹配所有TableB(外表)的值都为NULL。

2.右外连接示例：

sql语句： select * from TableA right join TableB on TableA.id=TableB.id

结果：

注释:TableB(基表)中所有的行列都显示了,第三行的条件不匹配所有TableA(外表)的值都为NULL。

3.全外连接示例:

sql语句：select * from TableA full join TableB on TableA.id=TableB.id

结果:

注释:TableA和TableB的所有行列都显示了，条件不匹配的行的值为NULL

二.内连接

概述：内连接是用比较运算符比较要连接的列的值的连接，不匹配的行不会被显示。sql关键字JOIN 或者INNER JOIN，通常我们写成JOIN

例子:

select * from TableA JOIN TableB on TableA.id=TableB.id

结果:

注释:只返回条件匹配的行

以上写法等效于:

select * from TableA,TableB where TableA.id=TableB.id

select * from TableA cross join TableB where TableA.id=TableB.id (cross join 后只能用where不能用on)

三.交叉连接
概念:没有where条件的交叉连接将产生连接表所涉及的笛卡尔积。即TableA的行数TableB的行数的结果集。（TableA 3行TableB 3行=9行）

sql语句:

select * from TableA cross join TableB

结果:

注释:返回3*3=9行数据，即笛卡尔积。

自连接

自连接就是自己和自己链接的操作,用法:

table: id,name,address

现在想所有找和name="张三"的人一样地址的名字,这里就需要自连接了

 select table1.name from `table` table1
 join `table` table2
 where table1.name="张三" and table1.address = table2.address

分页&排序

分页:imit

limit 0,5 
-- limit 从第几个开始,显示几个

排序: order by

order by `列名` ASC -- 升序
order by `列名` DESC --降序

常用函数

abs() -- 绝对值
ceiling() -- 向上取整
floor() --向下取整
rand() --返回一个0-1的随机数
sign() --返回一个数的符号 正的返回1 否则返回-1

char_length() -字符长度
concat('A','B','C') --返回'ABC'
upper() -- 转换为大写
lower() -- 转换为小写

聚合函数

having是分组条件,分组过后就无法使用where了

md5

一个加密函数,其实没啥好说的,知道有不可逆的属性就行

总结

事务

什么是事务?

要么都成功,要么都失败

---

将一组SQL放在一个批次里去执行

事务原则:ACID 原子性,一致性,隔离性,持久性 … (脏读,幻读)

Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

---

事务的使用

首先,我们要知道, mysql 是默认开始事务的,意思是,用户的每一条指令都会被认为是事务去执行可以通过

set autocommit =0/1 -- 0是关闭,1是开启

去关闭或者开启这个特性

create database if not exists `Kuangshen`;
use `Kuangshen`;
create table if not exists `account`(
	`id` int(3) primary key auto_increment not null,
    `name` varchar(30) not null,
    `money` decimal(9,2) not null
);
SET SQL_SAFE_UPDATES = 0; -- 取消数据库的安全模式,否则下面的更新将会报错
insert into `account`(`name`,`money`) values("张三",2000.00),("李四",1000.00);

-- 关闭自动提交
set autocommit = 0;
-- 开启事务
start transaction; 
-- 执行操作
update `account` set `money`=`money`-100 where `name`="张三";
update `account` set `money`=`money`+100 where `name`="李四";

commit; -- 提交事务
rollback; -- 回滚
-- 开启自动提交
set autocommit = 1;


select * from `account`

索引

索引是用来快速定位一个元素在表中的位置的数据结构,索引是一种数据结构

索引的分类

• 主键索引(Primary KEY)
  • 唯一的表示,主键不可重复,只能有一个列作为主键
• 唯一索引(UNIQUE KEY)
  • 避免重复列的出现,唯一索引可以重复,多个列都可以表示为唯一索引
• 常规索引(KEY/INDEX)
  • 默认的 ,index.KEY关键字来设置
• 全文索引(Fulltext)
  • 在特定的数据库才有,MYISAM
  • 快速定位数据

范式

JDBC(重点)

我们的程序会通过数据库驱动来和数据库打交道,数据库驱动由厂商制作

作为开发人员,我们只需要掌握JDBC接口的操作就行

代码样例

public class Application {
    public static void main(String[] args) throws SQLException {
        //1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动,实际上驱动代码是在静态方法区中的,所以这么写就可以了,不用实例出一个对象
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2.用户信息和URL
        String url= "jdbc:mysql://localhost:3306/Kuangshen?useUnicode=true&characterEncoding=utf8&useSSL=true";//也是固定的,指问号以前.Mysql默认的端口号就是3306
        String username="root";
        String password="zbw537680";

        //3.链接成功,数据库对象,Connection 代表数据库
        Connection connection= DriverManager.getConnection(url,username,password);

        //4.创建SQL对象 Statement 代表sql对象
        Statement statement=connection.createStatement();

        //5.执行sql对象,可能存在结果,查看返回结果
        String sql="select * from account";
        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("money="+resultSet.getObject("money"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

类的理解和常用函数

1. Connection 代指数据库,链接数据库的方法也很简单就是使用DriverManager的静态方法getConnection 里面有三个参数,要连接的数据库,用户名,和密码

   connection.commit();//提交
   connection.rollback();//回滚
   connection.setAutoCommit();//设置自动提交
   

2. Statement 执行类,通过connect对象的createstatement方法赋值获得(逻辑也就应该这样)

   statement.executeQuery("");//执行查询 返回resulset类,但是ResultSet不是个接口吗?为什么可以直接返回个接口而不是像List那样返回的是个实现类?不懂
   statement.executeUpdate("");//执行更新,删除,插入,返回一个影响的行数
   statement.execute("");// 执行任何sql语句 返回一个布尔值
   

3. ResultSet 结果类(其实是个接口),一般是查询的结果,封装了所有的查询结果,底层逻辑没看懂(百度了一下,这个接口java是没有实现的,而是交给JDBC实现了,而且采用的包装器模式,导致我们只看这部分什么都看不出来)

   //默认是第一条数据
   resultSet.next();// 下一条数据
   resultSet.previous();//上一条数据
   resultSet.beforeFirst();//第一条数据
   resultSet.afterLast();//最后一条数据
   resultSet.absolute(row);//移动到指定行
   

释放资源必须做!!!

尤其是connection,必须释放

数据库综合练习

public class DBTool {

    private static String username = null;
    private static String driver = null;
    private static String url = null;
    private static String password = null;
    static {
        try {
            InputStream in = DBTool.class.getClassLoader().getResourceAsStream("db.properties");//新建的配置文件
            Properties properties =new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            username=properties.getProperty("username");
            url=properties.getProperty("url");
            password=properties.getProperty("password");
            Class.forName(driver);

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

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    public  static void release(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();
            }
        }
    }

}

public class InsertSQL {
    /**
     *
     * @param list_name
     * @param context
     * 为了方便输入,请严格以 "内容1.1,内容1.2,内容1.3...,内容2.1 ..."的形式输入(格式只能是全字符串QWQ)
     * @return
     */
    public static String getSQL(String table_name,List<String> list_name,String context){
        List text= changeToList(context);
        String res="insert into "+table_name+"(";
        for(int i=0;i< list_name.size();i++){
            res+='`';
            res+=list_name.get(i);
            res+='`';
            if(i!= list_name.size()-1)
            res+=',';
        }
        res+=") values ";
        for(int i=0;i<text.size()/ list_name.size();i++){
            res+="(";
            for(int j=0;j< list_name.size();j++){
                res+='\'';
                res+=text.get(i* list_name.size()+j);
                res+='\'';
                if(j!= list_name.size()-1)
                res+=',';
            }
            res+=')';
            if(i!=text.size()/ list_name.size()-1)
            res+=",";
        }
        return res+';';
    }

    private static List changeToList(String name){
        return Arrays.asList(name.split("\\,"));
    }
}

public class Application1 {
    private static Connection conn=null;
    private static Statement st=null;
    private static ResultSet rs=null;
    public static void main(String[] args) {
        try {
            conn=DBTool.getConnection();
            st=conn.createStatement();
            String sql=InsertSQL.getSQL("account", Arrays.asList(new String[]{"name","money"}),"A,100,B,200");
            st.executeUpdate(sql);
            System.out.println(sql);
            
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            DBTool.release(conn,st,rs);
        }

    }
}

PreparedStatement

可以防止SQL注入的执行类

String sql ="insert into "表名"("..","..") values(?,?)// 使用?占位符
PreparedStatement st=connection.preparedStatement(sql);//创建"有备而来"执行类,参数是预编译sql语句
st.setInt(1,123);// 给第一个占位符赋值
st.setString(2,"sad");// 给第二个占位符赋值

st.executeUpdate();// 执行命令

注意事项

``里填的是表名,数据库名之类的东西,另外表里面名字的东西尽可能的用它包裹

''里面填的是字符串,性质是不一样的