MySQL学习

操作数据库

Create database	xxx	创建数据库
Drop database xxx 删除数据库
Select xxx 选择表/数据库等
创建表格式，``非引号''
CREATE TABLE `student` (
  `id` int(10) NOT NULL COMMENT '学生id',
  `name` varchar(100) NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL COMMENT '学生年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
		

连接数据库的基本代码
package JDBC;

import java.sql.*;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//加载驱动

        //用户信息和url
        String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="root";
        //链接成功，数据库对象
        Connection connection= DriverManager.getConnection(url,username,password);
        //执行sql对象Syatement
        Statement statement=connection.createStatement();
        //
        String sql="SELECT * FROM student1";

        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("age="+resultSet.getObject("age"));

        }
        //释放链接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

数据库引擎

INNODB 默认使用

MYISAM 早年使用的引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大

常规使用操作：

• MYISAM 节约空间速度较快
  • 对应文件有：
  • .frm 表结构定义文件
  • .MYD 数据文件data
  • .MYI 索引文件index
• INNODB 安全性高，事务处理，多表多用户操作
  • 只有一个.frm文件，以及上级目录的ibdata文件

修改表操作

修改表名
ALTER TABLE teacher RENAME AS teacher1
增加表的字段
ALTER TABLE teacher1 ADD age INT(11)
修改表的字段
ALTER TABLE teacher1 MODIFY age VARCHAR(11) 
ALTER TABLE teacher1 CHANGE age age1 INT(1)   
删除表的字段
ALTER TABLE teacher1 DROP age1

删除表
DROP TABLE IF EXISTS teacher1
查询表的创建语句
SHOW CREATE TABLE student1
CREATE TABLE `student1` (
  `id` int(10) NOT NULL COMMENT '学生id',
  `name` varchar(100) NOT NULL COMMENT '学生姓名',
  `age` int(3) NOT NULL COMMENT '学生年龄',
  `gradeid` int(10) NOT NULL COMMENT '学生的年级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

数据管理

外键

增加外键
1.CREATE TABLE `student1` (
  `id` INT(10) NOT NULL COMMENT '学生id',
  `name` VARCHAR(100) NOT NULL COMMENT '学生姓名',
  `age` INT(3) NOT NULL COMMENT '学生年龄',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

2.ALTER TABLE student1
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY `gradeid` REFERENCES `grade` (`gradeid`)
gradeid 关联字段
fk_gradeid 外键名

删除外键
ALTER TABLE student1 DROP FOREIGN KEY FK_gradeid

DML语言

数据操作语言

添加
INSERT INTO `student1` (`字段名`,`字段名`) VALUES ('XX','XX'),('YY','YY')
字段名和插入数据一一对应

更新
UPDATE `grade` SET `gradename`='数学' 
WHERE gradeid BETWEEN 2 AND 4
AND OR BETWEEN >= <>!= <= 范围确定表达式

删除
DELETE FROM `grade` WHERE gradeid=4 

TRUNCATE `grade`

DQL查询数据

数据查询语言

查询全部信息
SELECT * FROM student

查询指定字段，并起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student 

函数 Concat(a,b) 
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student

去重
SELECT DISTINCT `studentno` FROM result 

对学生成绩加一分后查看
SELECT `studentno`,`studentresult`+1 AS '提分后' 
FROM result

where条件子句

作用: 检索数据中的符号条件的值

SELECT `studentno`,`studentresult` FROM result
WHERE studentresult >=60

模糊查询

SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '张_'

SELECT `studentno`,`studentname` FROM student
WHERE `address` LIKE '深圳%'

联表查询

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student  s
INNER JOIN result  r
ON s.studentno=r.studentno

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student  s
LEFT JOIN result  r
ON s.studentno=r.studentno

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student  s
RIGHT JOIN result  r
ON s.studentno=r.studentno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

on是建立连接，where是对结果进行筛选

自连接

自己的表和自己的表连接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vT0MX3qU-1621585884569)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1620905992424.png)]

父类

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

这里2.3.5的父类为1，可理解为二叉树的根为1

子类

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

SELECT a.`categoryname` AS '父类', b.`categoryname` AS '子类'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid`= b.`pid`

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-85VOfoqS-1621585884577)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1620907071903.png)]

排序

升序asc(系统默认)

降序desc

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student  s
RIGHT JOIN result  r
ON s.studentno=r.studentno
ORDER BY studentresult ASC 
LIMIT 0,6
-- shengxu升序	

子查询

where这个值是计算出来的，嵌套一个子查询

WHERE (SELECT * FROM)

SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE subjectno=(
	SELECT subjectno FROM `subject`
	WHERE subjectname='高等数学-1'
)

MySQL函数

聚合函数

函数名称	描述
SUM()	求和
COUNT()	计数
AVG()	平均值
MAX()	最大值
MIN()	最小值

SELECT COUNT(studentname) FROM student

SELECT SUM(studentresult)AS 总和 FROM result 

SELECT MAX(studentresult)AS 最大值 FROM result 

SELECT MIN(studentresult)AS 最小值 FROM result 

SELECT subjectname ,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno 
GROUP BY r.subjectno  -- 通过字段来分组
HAVING AVG(studentresult)>80	

此处要注意where不能使用聚合函数，只能用having

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-65C9Zn4v-1621585884579)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621071305450.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hdE1RxRX-1621585884581)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621071327241.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wyp2qWEM-1621585884583)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621071337217.png)]

事务

原子性

两个步骤要一起成功或一起失败

一致性

针对一个事务操作前和操作后的状态一致

隔离性

针对多个用户同时操作，主要排除其他事务对本次事务的影响

持久性

–事务提交

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

事务结束后的数据不随着外界原因导致数据丢失

索引

• 主键索引（PRIMARY KEY）
  • 唯一的标识，逐渐不可重复，只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列出现，可以重复，多个列都可以标识为唯一索引
• 常规索引(KEY/INDEX )
  • 默认的
• 全文索引(FULLTEXT)
  • 在特定的数据库引擎下支持，MyISAM
  • 快速定位数据

索引原则

索引不是越多越好

不要对进程变动数据加索引

小数据量的表不需要加索引

索引一般加载常用来查询的字段上

三大范式

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YtsAARac-1621585884584)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621075634470.png)]

第二范式

• 满足第二范式（2NF）必须先满足第一范式（1NF）。
• 第二范式要求实体中没一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。
• 完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。
• 若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。

第三范式

• 满足第三范式必须先满足第二范式。
• 第三范式要求：实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。
• 如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

JDBC

固定写法

package JDBC;

import java.sql.*;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//加载驱动

        //用户信息和url
        String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="root";
        //链接成功，数据库对象
        Connection connection= DriverManager.getConnection(url,username,password);
        //执行sql对象Syatement
        Statement statement=connection.createStatement();
        //
        String sql="SELECT studentno,studentname,address FROM student";

        ResultSet resultSet=statement.executeQuery(sql);//返回结果
        while(resultSet.next())
        {
            System.out.print("studentno="+resultSet.getObject("studentno")+" ");
            System.out.print("studentname="+resultSet.getObject("studentname")+" ");
            System.out.print("address="+resultSet.getObject("address"));
            System.out.println();
        }
        //释放链接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

配置文件

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

测试类

package JDBC;

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

public class JDBCtest {

    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;
    static {
        try{
            InputStream in = JDBCtest.class.getClassLoader().getResourceAsStream("dlb.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            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 connection, Statement statement, ResultSet resultSet)
    {
        if (resultSet!=null)
        {
            try{
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement!=null)
        {
            try{
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(connection!=null)
        {
            try{
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

封装类

添加

package JDBC;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {

        Connection connection=null;
        PreparedStatement statement=null;
        //ResultSet resultSet=null;

        try{
            connection=JDBCtest.getConnection();//获取数据库连接

            //statement=connection.createStatement();

            //String sql="INSERT INTO grade (`gradeid`,`gradename`) VALUES('7','高二')";

            //区别，使用?占位符代表参数
            String sql="insert into grade (`gradeid`,`gradename`) values(?,?)";
            statement=connection.prepareStatement(sql);
            //手动参数赋值
            statement.setObject(1,8);
            statement.setObject(2,"初三");


//            int i=statement.executeUpdate(sql);
            int i=statement.executeUpdate(); //无sql参数
            if (i>0)
            {
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCtest.release(connection,statement,null);
        }

    }
}

筛查

package JDBC;

import java.sql.*;

public class TestSelect {
    public static void main(String[] args) {

        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try{
            connection=JDBCtest.getConnection();//获取数据库连接

            //区别，使用?占位符代表参数
            String sql="select * from grade where gradeid=?";
            statement=connection.prepareStatement(sql);
            //手动参数赋值
            statement.setObject(1,2);

            resultSet=statement.executeQuery();
            if (resultSet.next())
            {
                System.out.println(resultSet.getString("gradename"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            JDBCtest.release(connection,statement,resultSet);
        }

    }
}

事务

package JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection connection=null;//此处连接需要修改dlb.properties的数据库
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try{
            connection =JDBCtest.getConnection();
            connection.setAutoCommit(false);

            String sql1="update account set money=money-100 where name='a'";
            preparedStatement=connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            String sql2="update account set money=money+100 where name='b'";
            preparedStatement=connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            connection.commit();
            System.out.println("事务成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCtest.release(connection,preparedStatement,resultSet);
        }

    }
}

使用idea连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RPjM3guP-1621585884585)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621129726250.png)]

区别RANK，DENSE_RANK和ROW_NUMBER

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CCdhKfpj-1621585884585)(C:\Users\32537\AppData\Roaming\Typora\typora-user-images\1621558392720.png)]

    preparedStatement.executeUpdate();

        String sql2="update account set money=money+100 where name='b'";
        preparedStatement=connection.prepareStatement(sql2);
        preparedStatement.executeUpdate();

        connection.commit();
        System.out.println("事务成功");

    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        JDBCtest.release(connection,preparedStatement,resultSet);
    }

}

}

# 使用idea连接数据库

[外链图片转存中...(img-RPjM3guP-1621585884585)]

# 区别RANK，DENSE_RANK和ROW_NUMBER

[外链图片转存中...(img-CCdhKfpj-1621585884585)]