SQL高阶

Mysql进阶

约束

对表中的数据进行一定的限制 提高了数据的可靠性

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标 识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略 CHECK子句。

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

主键 常配搭自增长auto_increment来使用。

id INT PRIMARY KEY auto_increment 根据上一个来确定下一个 如果删除上一个仍然按照上一个来确定

如果要重新排列 1.删除重建表 2.truncate 表名 清空表

truncate 不用便利表 delete 要遍历表

外键：foreign key (字段名) references 主表(列)

CREATE TABLE classes (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 `desc` VARCHAR(100)
);
CREATE TABLE student (
   id INT PRIMARY KEY auto_increment,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20),
 classes_id int,
 FOREIGN KEY (classes_id) REFERENCES classes(id)
);

意味着一旦把外键约束建立好了，后续针对student表的数据还是class表的数据其实是都有限制的.~
student表中新增的数据,不能是class表之外的数据.
class表中删除的数据,也不能是student中已经被使用的数据.修改也是同理~~

check约束

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

mysql不生效作用

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SkYAfkBC-1629185688356)(C:\Users\23871\Desktop\typora\博客\面试重点\Mysql\check.png)]

数据库的设计

1.先找到需求中的实体 2. 理清向实体之间的关系

关系 ：一 一对多 多对多 没关系

新增操作 ：insert 是支持把查询的结果加入到新的表中

insert into student2 select * from student；

更加复杂的查询 聚合查询

聚合函数 show warnings 查看错误

COUNT([DISTINCT] expr) 返回查询到的数据的 数量

count（*）和count（列名）区别 指定列 列的数据为空不计数

SUM([DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义 行和行之间的和与列无关 数学总成绩

AVG([DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

MAX([DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义

MIN([DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义

group by 分组操作

指定列进行分组查询

select role,max(salary),min(salary),avg(salary) from emp group by role;

HAVING

先进行筛选 在进行查询使用where

要对查询的结果进行分组 在进行筛选则使用Having

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select–from–where–group by–having–order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from–where–group by–having–select–order by,

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

需要注意having和where的用法区别：

1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

2.where肯定在group by 之前，即也在having之前。

3.where后的条件表达式里不允许使用聚合函数，而having可以。

多表查询 联合查询

笛卡尔积 查询的数据过多 数据库可能卡死

列数 A的列数+B的列数

行数 A的行数*B的函数

实际使用中只需要一部分数据

1.先找出要查找的信息都在哪些表中.⒉.针对这些表进行笛卡尔积.
3.找到连接条件,干掉不必要的记录4.加上其他的条件,最终结果符合要求

内连接

select student.id ,student.name ,score.student_id,score.score from student,score where student.id=score.studen_id and student.name='许仙 ’ ;

select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

select student.name,score.score from student join score on student.id=score.id and student.name=‘许仙’；

三个表联合查询

SELECT stu.id, stu.sn, stu.NAME, stu.qq_mail, sco.score, sco.course_id, cou.NAME

FROM student stu

JOIN score sco ON stu.id = sco.student_id

JOIN course cou ON sco.course_id = cou.id；

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

自连接

有些查询：希望行和行之间的数据进行比较

成绩表

张三 java 80

张三 c 70

李四 java 88

李四 c 80

想查询那些同学java成绩比 c的成绩高

SELECT s1.* FROM score s1 JOIN score s2 ON s1.student_id = s2.student_id

子查询

把多个select语句嵌套在一起

查询与“不想毕业” 同学的同班同学

select * from student where classes_id=(select classes_id from student where name=‘不想毕业’)；

多行查询 返回多行记录的子查询

子查询返回多行结果 in /not in

select * from score where course_id not in (select id from course where name=‘语文’ or name=‘英文’);

[not] exists

先执行父查询 针对父查询的每一条结果在执行一次子查询。执行速度很慢 但是解决了内存放不下的问题

合并查询

union union all

将两个查询的结果集合并 union会去掉重复行 union all不会去重

select * from course where id<3
union all
select * from course where name=‘java’;

索引 ：类似于书的目录 加快查找的速度 但是需要空间代价 额外的数据结构 插入 修改 删除慢了

二叉搜索树 查找O(n)

二叉平衡树 /红黑树 O(logN)

红黑树 和hash表不适合数据库的索引

红黑树 数据量很大的时候 书高度比较高 比较次数变多 每次的代价会很大

hash表 数据变大会发生冲突 也可能会转变成为红黑树 也不可以进行大小比较

B+树更好地适应。

事务

执行sql的时候经常一次性执行多个sql 典型的场景转账

事务的原子性 保证了sql都执行成功或者不执行 通过回滚机制 rollback来保证

Java的jdbc编程

mysql提供java驱动包mysql-connector-java 驱动Oracle 则使用Ojdbc

JDBC 执行SQL语句的java API

构造出相应的网络请求 操作的是mysql的客户端

可以再官网查找API 也可以在MAVEN中央仓库上面进行查找下载jar包

建立连接就是在打电话~
需要连接建立成功之后,才能进行后续的操作.
和数据库建立连接,也需要指定是和哪里的数据库进行连接~

//jdbc  进行修改   插入  和删除
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class jdbc {
    public static void main(String[] args)throws SQLException {//链接的时候受查异常
        //动态的插入数据
        Scanner scan=new Scanner(System.in);
        int id=scan.nextInt();
        String name=scan.next();
        double chinese=scan.nextDouble();
        double math=scan.nextDouble();
        double english=scan.nextDouble();
        //1.先建立连接
        //先创建数据源（Datasource）   MysqlDataSource类
        DataSource dataSource=new MysqlDataSource();
        //给数据源设置属性
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/rocket2021?characterEncoding=utf-8&&useSSL=false");
        //ssl是加密
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("wangxp184219");
        //也可以直接吧datasource写成mysqlDatasource   但是后期不易扩展
        //通过getconnection来建立连接。  connection  选择java.sql
        Connection connection=dataSource.getConnection();
        //拼装sql语句
        // String  sql="insert into exam_result values("+id+",'"+name+"'+english+","+chinese+","+math"")";
        //sql这样动态的 绑定sql语句必然会出错也比较麻烦  也容易sql注入
        //String  sql="insert into exam_result values(10,'hh',50,60,60)";
        //动态的拼装
        String sql="insert into  exam_result values (?,?,?,?,?)";

        //实际需要语句对象   preparedstatement(可以帮助我们动态的拼装sql)   statement
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.setString(2,name);
        preparedStatement.setDouble(3,chinese);
        preparedStatement.setDouble(4,math);
        preparedStatement.setDouble(5,english);
        System.out.println(preparedStatement.toString());

        //3.执行sql语句
        //delete  update   insert  都是用executeUpdate   返回影响的行数整数
        //selecte   使用的是 executeQuery  返回的
        int ret=preparedStatement.executeUpdate();
        System.out.println(ret);
        //收尾工作释放前边申请的资源    短时间反复建立连接不释放会出现问题
        preparedStatement.close();
        connection.close();
    }
}

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class jdbcselect {

    public static void main(String[] args)throws SQLException {//链接的时候受查异常
        //1.先建立连接
        //先创建数据源（Datasource）   MysqlDataSource类
        DataSource dataSource = new MysqlDataSource();
        //给数据源设置属性
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/rocket2021?characterEncoding=utf-8&&useSSL=false");
        //ssl是加密
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("wangxp184219");
        //也可以直接吧datasource写成mysqlDatasource   但是后期不易扩展
        //通过getconnection来建立连接。  connection  选择java.sql
        Connection connection = dataSource.getConnection();
        //拼装sql语句
        String sql = "select  * from exam_result where id=? ";
        int id=10;
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        //执行   sql语句   返回得是临时表
        ResultSet resultSet = statement.executeQuery();
        //通过resultset进行遍历   遍历结束  返回false
        while (resultSet.next()) {
            int id2 = resultSet.getInt("id");
            String name = resultSet.getString("name");
            double chinese = resultSet.getDouble("chinese");
            double math = resultSet.getDouble("math");
            double english = resultSet.getDouble("english");
            System.out.println(id2+","+name+","+chinese+","+math+","+english);
        }
        //收尾
        resultSet.close();
        statement.close();
        connection.close();
    }
}

服务器程序~
这个服务器可能就会收到很多的请求~
针对每一个请求,都可以创建[connection对象,创建PrepareStatement对象,创建ResultSet对象.
但是这些请求彼此之间都共用同一个DataSource对象即可~~
也不是不能创建多份 没有必要~
假设如果当前的服务器收到的请求数量很多，随之创建的Connection对象也很多~~
ResultSet ,PrepareStatement对象也会变多~ 创建数据库连接池

因此通过使用单例模式（棋谱）保证DataSource只有一个

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;


//保证DataSource唯一
public class DBUtil {
    //类成员
    private  static DataSource dataSource=null;
    //后面不用new  datasource 而是直接使用getDataSource（）方法
    public  static  DataSource getDatasSource(){
        if(dataSource==null){
            dataSource=new MysqlDataSource();
            ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/rocket2021?characterEncoding=utf-8&&useSSL=false");
            //ssl是加密
            ((MysqlDataSource) dataSource).setUser("root");
            ((MysqlDataSource) dataSource).setPassword("wangxp184219");
        }
        return dataSource;

    }

}