MySql

MySQL

• 启动,连接,断开和停止MySQL服务器
  1. 启动MySQL服务器
     1. 在window启动
        1. win+r --> services.msc(快速打开windows服务管理器)1
        2. 找到MySQL服务,双击打开,直接启动
     2. 在命令提示符下启动/停止
        • 此时注意,服务的名字不要写错,使用具有权限的用户去运行
        • win+r —> 已管理员形式运行dom窗口

​

3. 连接和断开MySQL服务

   1. 连接本地MySQL服务

   

   2. 连接远程的MySQL服务
      1. 对方开放了远程连接权限
      2. 通过inter网连接,对方的 IP
      3. 通过局域网连,对方的IP/同一个局域网下

4. mysql数据库中常用命令

   1. select now();现在的时间

   2. show databases; 查看数据库

   3. create database java220201; 创建数据库

   4. use java220201; 使用数据库

5. 数据库 的宏观了解

— 前期准备工作完成

SQL语言是用来操作数据库的数据的

mysql数据库里面可以创建很多数据库,众多数据库对象(表)的集合

MySQL数据库常用命令(SQL语言操作数据的常用命令,mysql数据库提供的命令)

• 创建数据库 : create database 数据库名;

• 删除数据库 : drop database 数据库名;

• 指定使用的数据库 : use 数据库名;

• 显示所有的数据库 : show databases;

• 创建表命令 : create table 表名(字段名 属性,字段名2 属性,…);

• 显示数据库中的所有表 : show tables;

• 查看表结构 : describe/desc 表名;

• 删除表 : drop table 表名;

• 表名重命名 : alter 表名 rename to 新表名;

• 增加列 : alter table 表名 add column 列名 列字段属性(长度);

• 添加表数据命令 : insert into 表名 (字段名1,字段名2,字段名3…) values(值1,值2,值3…);

PS : 在插入中文的时候,可能会报1366错误,目前数据库中的字符集不支持中文

解决步骤:

1. 查看目前数据库的字符集

• show variables like ‘character’;

2. 保证database和server的字符集支持中文;

• set 名字 = 字符集名字;

3. 如果上述不能成功,则单独修改表和表字段字符集

• alter table 表名 charset = utf8mb4;
• alter table 表名 modify 字段名 varchar(10) character set utf8 collate utf8_general_ci;

查询数据表命令

• 全字段查询+全表扫描 : select * from 表名;

  • ‘*’ 表示通配符,匹配所有字段

• 投影查询 : select 字段1,字段2,… from 表名;

• 如果字段名太长可以取别名: select 字段1 别名1 , 字段2 别名2… from 表名;

• 条件查询 : select 字段1,字段2,… from 表名 where 条件;

  select * from student where name = '李氏';
  

  • 查询条件连接符
    • 运算符 >,<,<=,>=,!=,<>,IS NULL.

• 插入NULL值 : insert into student(id,age,gender) values (4,‘55’,‘女’);

• 插入空值 : insert into student(id,name,age,gender) values (5,‘’,‘55’,‘女’);

• NULL不确定的,未知的值

• select * from student where name is NULL;

• select * from student where name is not NULL;
  

• 关键字

  • In, not,in,like,not like,between…and,not between…and, regexp;
  • select * from student where id [not] in (2,3);
    • 关键字in可以判断某个字段的值是否在指定的集合中.

• 模糊查询 : like

  • select * from student where name like ‘李%’;
  • % 表示0到多个任意字符
  • _ 表示一个任意字符
  • 也可以用正则表达式
  • 如果查询内容中含有特殊的字符,可以使用"\"对其进行转义

• between…and : 指定查询条件区间

• AND 可以用来联合多个条件进行查询(类似于&&)

  • 同时满足所有条件

• OR 可以用来联合多个条件进行查询(类似于 ||)

  • 只满足一个就可以

• 去除重复记录

  • distinct:可以去除查询结果中的重复记录.

• 使用order by对查询结果排序

  • asc按照升序进行排序
  • desc按照降序进行排序

  select * from student where gender = '男' order by id desc,name asc;
  

  先按照id进行降序,id相同的按照name升序排序

• 分组查询

  • 通过通过关键字GROUP BY 可以将数据划分到不同的组中,实现对记录进行分组查询

  • 在查询时,所查询的列必须包含在分组的类中,目的是使查询的数据没有矛盾

  • 单独使用GROUP BY查询只显示每组的一条记录

  • GROUP BY和GROUP_CONCAT()函数查询可以将每个组中的所有字段全部显示

  • 使用分组函数时 select 后面只跟两种字段(1.分组字段,聚合函数,其他均不合理)

> 求部门平均工资大于10000的信息
>
> select avg(salary) from employess group by department_id having avg(salary) > 1000;

• 多个字段进行分组,按顺序依次分组

  PS: 分组函数一般和聚集函数配合使用

  1. count() 查询总记录数
  2. max() 最大值
  3. min() 最小值
  4. avg() 平均数
  5. sum() 求和

• 分页查询

  • 用关键字limit限制查询结果的数量
  • 查询数据时,可能会查询出很多的记录,而用户需要的记录可能只是很少的一部分,这样就需要限制查询结果的数量
  • 关键字limit 可以对查询结果的记录条数进行限制,控制它的输出数量
  • limit 开始的索引，每页查询条数；(索引从零开始)

• 多表查询

  • 连接是把不同的表记录连接到一起进行查询,这种连接语句可以以多种高级方法来组合表记录

  • 内连接查询

    • 内连接查询是最普遍的连接类型,它们要求构成连接的每一部分的每个表的匹配,不匹配的行将排除
    • 内连接是最常见的例子就是相等链接,也就是连接后的表中的某个字段与表中的都相同,这种情况下,最后的结果只包含参加连接的表中与指定字段相等的行

    显示所有员的姓名,部门号,部门名称

    select t1.name t2.department_id ,t2.department_name

    from employess t1,departments t2

    where t1.department_id = t2.department_id;

  • 外连接查询

    • 外连接是指使用OUTER JOIN关键字将两个表连接起来,外连接生成的结果集不仅包含符合连接条件的行数据,而且还包含左表或右表或者两个表中不符合连接条件的行数据
    • 左外连接
      • left join 是指将左边中的所有数据分别与右表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包含左边中不符合条件的数据,并在右边的相应列中添加NULL值
      • 多个连接条件不可使用where(其他数据库用法)

    以左外连接的形式查询员工名,部门名称

    select name ,department_name
    from employess left join departments
    on employess.department_id = departments.department_id;

    8.0不适用

    select name ,department_name
    from employess left join departments
    where employess.department_id = departments.department_id;

    –

    select name ,department_name
    from employess, departments
    where employess.department_id = departments.department_id(+);

    • 右外连接
      • right join 是指将右表中的所有数据分别与左表中的每条数据进行连接组合,返回的结果除内连接的数据外,还包含右边中不符合条件的数据,并在左边的相应列中添加NULL值

    以右外连接的形式查询员工名,部门名称

    select name ,department_name
    from employess right join departments
    on employess.department_id = departments.department_id;

    • 全外连接
      • 略 full outer join

  • 复合条件连接查询

    • 在连接查询时,也可以增加其他的限制条件

    select name ,department_name

    from employess right join departments

    on employess.department_id = departments.department_id and name like ‘%a%’;

  • 执行sql脚本文件

    • source 文件路径

    source d://employess.sql

  • 子查询(多层查询)

    • 子查询就是select 查询是另外一个查询的附属
    • 查询语句中可以嵌套多个查询语句,在外面一层的查询可以使用里面一层查询产生的结果集
    • 当遇到这样的多层查询时,MySQL从最内层的查询开始,然后从它开始向外向上移动到外层查询(主查询),这个过程每个查询产生的结果集都被赋给包围他的父查询,接着这个父查询被执行.
    • 内层子查询语句的执行结果位空值,那个外层的where就始终不会满足条件,这样的查询最后就必然是空值.

    查询名字中带有a字母的员工的信息

    1. select * from employess where name like ‘%a%’;

    2. 子查询方式

       先查询出名字带有a字母的员工名字

       根据这些名字查询员工的所有信息

       select * from employess where name in (select name from employess where name like ‘%a%’);

    查询员工信息,但是该员工所在部门号一定是存在于department是表中

    select * from employess where department_id in (select department_id from departments);

    显示所有员的姓名,部门号,部门名称

    select t1.name t2.department_id ,t2.department_name

    from employess t1,departments t2

    where t1.department_id = t2.department_id;

    PS : 从代码量,逻辑简单易读以及时间复杂度来看,子查询更加具备优势.

    带比较运算符的子查询

    查询工资大于8000的员工信息

    select * from employess where salary>=(select salary from employess where id = 8);

    查询技术支持部的员工的信息

    select * from employess where department_id = (select department_id from departments where department_name = ‘技术支持部’);

    查询employess表中工资既不是最低又不是最高的员工信息

    select * from employess t1,(select max(salary) max ,min(salary) min from employess) t2 where salary not in (t2.max,t2.min);

  • 单行操作符,<,>,>=,<=,!=,=

  • 子查询中使用(IN,ANY,ALL)关键字

    • ANY关键字

      • 必须与单行操作符结合使用,并且返回只要匹配自查的任何一个结果即可
      • 表示满足其中任意一个条件,只要满足内层查询语句返回的结果中任意一个,就可以通过该条件执行外层查询语句.

      在employess表中,查询工资大于1002号部门的任意一个员工工资的其他部门的员工信息.

      select * from employess where department_id != 1002 and salary > ANY(select salary from employess where department_id = 1002);

    • ALL关键字

      • 满足所有条件,使用ALL时,只有满足内层查询语句返回的所有结果,才可以执行外层循环

      在employess 表中,查询工资大于部门编号为1002的所有员工工资的员工信息

      select * from employess where salary > ALL(select salary from employess where department_id = 1002);

    • EXISTS 关键字

      • 内层查询语句不返回查询记录,而是返回一个真假值,如果内层查询语句查询到满足条件的记录,就返回一个真值(true),否则返回一个假值(false),当返回true时,外层查询语句将执行,否则外层不执行

      使用子查询,查询departments表中 是否存在2000号部门,如果存在则查询employess表中的记录

      select * from employess where exists(select * from departments where department_id = 2000);

      select * from employess where exists(select * from departments where department_id = 1002);

      使用子查询,查询departments表中 是否存在2000号部门,如果存在则查询employess表中id= 176员工的记录

      select * from employess where exists(select * from departments where department_id = 2000) and id = 176 ;

  • 合并查询结果

    • 是将多个select语句的查询结果合并在一起,使用UNION和UNION ALL
    • UNION : 是将所有查询结果合并在一起,然后去除相同的记录
    • UNION ALL : 简单的合并在一起

    查询employess 表中 name 字段和departments 表中 department_name字段,并使用UNION关键字合并查询结果

    select name from employess union all select department_name from departments;

    select name from employess union select department_name from departments;

  • 关联子查询

  • 在单行子查询和多行子查询中,内查询和外查询是分开执行的,也就是说,内查询的执行与外查询的执行是没有关系的,外查询仅仅是使用内查询的最终结果

  • 在一些特殊需求的子查询中,内查询的执行需要借助外查询,而外查询的执行又离不开内查询,这时,内查询和外查询是互相关联,这种子查询就称为关联子查询

  该岗位上的员工大于同职位的平均工资

  select id,name,salary from employess f where salary > (select avg(salary) from employess where job_id = f.job_id) order by job_id;

  查询所有管理者的下属员工信息

  select * from employess where id not in (select distinct manager_id from employess where manager_id is not null) and department_id is not null;

  • 自连接

    • 在应用系统开发中,用户可能会拥有"自引用式"外键,"自引用式"外键是指表中的一个列可以是该表主键的一个外键

    select emp2.name 上层管理者,emp2.name 下属员工 from employess emp1 left join employess emp2 on emp1.manager_id = emp2.id;

SQL注入问题

• 下列代码无论是使用statement 还是使用具备预编译的 prepareStatement都会引发SQL注入

    public void testFindAll(){
        //模拟用户名和密码数据
//        String name = "永志" ;
        String name = "1' or 1=1 -- '";
        String password = "123456";
        Statement stat = null;
        try {
            conn = JDBCUnit.getConnection();
            //2.获取连接
            String sql = "select * from user where name = '"+name+"' and password = '"+password+"';";
            //4.预编译语句(负责将sql语句通过通道conn放入数据库中执行.防止sql注入,预编译提升效率)
//            stat = conn.createStatement();
//            //5.执行sql语句
//            resultSet = stat.executeQuery(sql);
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            //遍历结果集
            if(resultSet.next()){
                System.out.println("登录成功");
            }else{
                System.out.println("用户名或密码错误,请重试");
            }

            System.out.println("over");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnit.closeConnection(conn);
            JDBCUnit.closePreparedStatement(ps);
            JDBCUnit.closeResultSet(resultSet);
        }
    }

public void testFindAll(){
        //模拟用户名和密码数据
//        String name = "永志" ;
        String name = "1' or 1=1 -- '";
        String password = "123456";
        Statement stat = null;
        try {
            conn = JDBCUnit.getConnection();
            //2.获取连接
//            String sql = "select * from user where name = '"+name+"' and password = '"+password+"';";
            String sql = "select * from user where name = ? and password = ?";
            //4.预编译语句(负责将sql语句通过通道conn放入数据库中执行.防止sql注入,预编译提升效率)
//            stat = conn.createStatement();
//            //5.执行sql语句
//            resultSet = stat.executeQuery(sql);
            ps = conn.prepareStatement(sql);

            //4.5 传入参数
            ps.setString(1,name );
            ps.setString(2,password );

            resultSet = ps.executeQuery();
            //遍历结果集
            if(resultSet.next()){
                System.out.println("登录成功");
            }else{
                System.out.println("用户名或密码错误,请重试");
            }

            System.out.println("over");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUnit.closeConnection(conn);
            JDBCUnit.closePreparedStatement(ps);
            JDBCUnit.closeResultSet(resultSet);
        }
    }

小结 :

1. PreparedStatement 防止SQL的本质是改变注入参数的方式

2. PreparedStatement 能够做到参数和sql语句分离,如果sql语句不变则只会编译一次,而Statement 则每次都认为数据是全新的 .

  //设计程序验证PreparedStatement的效率更高
    @Test
    public void  test() throws SQLException {
        conn = JDBCUnit.getConnection();
        Statement statement = conn.createStatement();
//        String sql = "insert into user values (null,?,?); ";
//        ps = conn.prepareStatement(sql);
//        ps.setString(1,"二狗" );
//        ps.setString(2,"123456" );

        long time = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            String sql = "insert into user values (null,'二狗','123456'); ";
            statement.execute(sql);
//            ps.execute();
        }
        System.out.println(System.currentTimeMillis()-time);
    }

blob字段操作

• 了解下字段

  • tinyint 1 字节 类似于java中的byte 取值范围 -128~127
  • smailint 2 字节 类似于java中的short 取值范围 -30768~32767
  • mediumint 3字节
  • int 4字节
  • bigint 8字节 类似于java分钟long类型

• char

  • varchar 可变长字符串类型
  • char 固定长度字符串类型

• bolb

  • bolb 二进制对象

  • text 文本对象 大约64KB

  • longbolb 二进制大对象 4G 任何数据类型 视频,音频,图片

  • longtext 大文本对象 4G 字符串类型 char varchar

  • 使用bolb

    将图片存入数据库,然后再取出

    1. 增加longbolb类型的字段

       alter table student add column memo longtext;

       alter table student add column photo longblob;

    2. id设为主键,并自增

       alter table student modify column id int primary key auto_increment;

    3. 插入图片

    public void testBlob() throws Exception{
            //获取连接
            conn = JDBCUnit.getConnection();
            //创建sql语句
            String sql = "insert into student values (null,?,?,?,?,?,?)";
            //预编译sql语句
            ps = conn.prepareStatement(sql);
            ps.setString(1,"唐伯虎");
            ps.setInt(2,670);
            ps.setString(3,"男");
            ps.setInt(4,60000);
    
            FileInputStream fileInputStream = new FileInputStream("唐寅.jpg");
            ps.setBinaryStream(5,fileInputStream,fileInputStream.available());
            ps.setString(6,"我是唐伯虎,我要点秋香,我是四大才子之首,可是身上没有钱,借我300块,明天给你画一个小鸡啄米图");
            //执行
            ps.execute();
            fileInputStream.close();
            JDBCUnit.closePreparedStatement(ps);
            JDBCUnit.closeConnection(conn);
    
    
        }
    

    4. 读取图片

     public void findBlob() throws Exception{
            conn = JDBCUnit.getConnection();
            String sql = "select * from student where name = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"唐伯虎");
            resultSet = ps.executeQuery();
            if(resultSet.next()){
                InputStream binaryStream = resultSet.getBinaryStream("photo");
                FileOutputStream fileOutputStream = new FileOutputStream("唐伯虎.jpg");
                byte[] buffer = new byte[1024];
                int len = -1;
    //            int count = 1;
                while ((len = binaryStream.read(buffer)) != -1){
    //                if(count == 3){
    //                    break;
    //                }
                    fileOutputStream.write(buffer,0,len);
    //                count++;
                }
                fileOutputStream.close();
                binaryStream.close();
            }
            JDBCUnit.closeResultSet(resultSet);
            JDBCUnit.closePreparedStatement(ps);
            JDBCUnit.closeConnection(conn);
    
        }
    

时间类型字段

使用程序将当前时间插入数据

  public void testDateType() throws Exception{
        conn = JDBCUnit.getConnection();
        String sql = "insert into employess (name,hire_date,salary) values(?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setString(1,"派大星");
        //获取当前时间
        java.util.Date date = new java.util.Date();
        //创建SimpleDateFormat
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //按照指定格式转换为字符串
        String time = simpleDateFormat.format(date);
        ps.setString(2,time);
        ps.setDouble(3,12000);
        ps.execute();
        JDBCUnit.closePreparedStatement(ps);
        JDBCUnit.closeConnection(conn);
        System.out.println("over");
    }

MySQL数据类型小结

• 在mysql数据库中每一条数据都有其数据类型

• 数字类型

数字类型	取值范围	说明	单位
tinyint	有符号-128~127 无符号0~255	存微小的整数	1字节
smallint	有符号-32768~32767 无符号0~65535	存小型整数	2字节
int		标准整数	4字节
bigint		大整数	8字节
mediuint		中型整数	3字节
…	…	…	…

默认情况下都是有符号的

指定为无符号的 : create table tt(id int,age int unsigned);

• 字符串类型
  • 普通字符串类

类型	取值范围
char(M)	0~255字符	固定长度为M的字符
varchar(M)	0~255字符	长度可变,其他等同于char

• 可变类型
  • text,blob, longtext,longblob
  • 大小可变,text适合存储长文本
  • bolb适合存储二进制数据,本质上支持任何类型数据,如 : 文本,音视频
  • text,bolb : 指定的最大长度(字节数) : 65535
  • longtext,longbolb : 大小 4G

• 日期和时间类型
  • date 日期 格式:yyyy-MM-dd
  • time 时间 格式:HH:mm:ss
  • datetime 日期和时间 格式 : yyyy-MM-dd HH:mm:ss

事务(Transaction)

• 一组不可分割的操作
• ACID
  • A : 原子性(atomicity),不可分割,作为一个整体一起成功或者一起失败
    • 事务本质是多条sql语句对数据库的操作,原子性将多个sql语句当做一个整体,要么一起成功,要么一起失败
  • C : 一致性(consistent),数据不被破环
    • 所有数据的流向是符合运算逻辑的
  • I : 隔离性(isolation),事务之间是独立的,不能干扰的
    • 多个客户端可以同时访问mysql服务器,多个客户端同时操作同一张表中的数据容易产生并发安全问题.
  • D : 永久性(durability),永久性,数据被永久保存起来
    • 数据发生改变之后将数据写到磁盘中保存起来.

mysql对于CRUD操作默认是自动提交的

• 修改自动提交
  • set autocommit = 0; – 关闭
  • set autocommit = 1; – 开启
• 手动提交操作: commit;

1. 提交操作时将数据的修改写入磁盘中,数据写入磁盘后,rollback失效
2. 开启事务前一定要先取消自动提交.

• 在java中设置自动提交和回滚

public void testTransaction() throws Exception{
        conn = JDBCUnit.getConnection();
        String sql = "insert into user(name,password) values(?,?)";
        ps = conn.prepareStatement(sql);
        //取消自动提交
        conn.setAutoCommit(false);
        try{
            for (int i = 0; i < 1000; i++) {
                ps.setString(1,"tom"+i);
                ps.setString(2,"123456");
                ps.executeUpdate();
//                if(i%30 == 29){
//                    int a = i/0;
//                }
            }
        }catch (Exception e){
            System.out.println("出现异常数据回滚");
            conn.rollback();
        }

        //手动提交
        conn.commit();

        JDBCUnit.closePreparedStatement(ps);
        JDBCUnit.closeConnection(conn);
    }

查询当前会话的隔离级别 : select @@transaction;

设置当前会话的隔离级别 :

1. 设置当前会话的隔离级别 : set session transaction isolation level XXXX;
2. 设置全局会话的隔离级别 : set global transaction isolation level XXXX;

使用事务的步骤

1. 取消自动提交 set autocommit = 0;
2. 开启事务 start transaction
3. 一系列sql语句
4. 提交

事务是否开启不影响隔离级别

事务的隔离级别

概念:多个事务之间隔离的,互相独立的.但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题.

存在问题:

1. 脏读:一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样.
3. 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一条事务查询不到自己的修改.

隔离级别:

1. read uncommitted:读未提交 1

​ 产生问题:脏读,不可重复读,幻读

​ 2. read committed:读已提交(Oracle) 2

​ 防止其他用户更新未提交的操作

​ 3.repeatable read:可重复读(MySQL默认) 4

​ 保证用户读取的数据一致

​ 4.serializable:串行化 8

​ 级别最高,会引发阻塞,类似于同步锁

​ 可以解决所有问题

• 注意:隔离级别从小到大安全性越来越高,但是效率越来越低

• 数据库查询隔离级别:

  • select @@tx_isolation;

• 数据库设置隔离级别:

  • set global transaction isolation level 级别字符串;

mysql新版本除读未提交全部可以避免幻读

事务是否开启不影响隔离级别,开启事务之后可以保证原子性

//模拟脏读
    @Test
    public void testDRA() throws Exception{
        conn = JDBCUnit.getConnection();
        //关闭自动提交
        conn.setAutoCommit(false);
        ps = conn.prepareStatement("update user set name = '李白' where id = '2'");
        ps.execute();
        System.out.println("A还未提交");

//        conn.rollback();
        conn.commit();
    }

    @Test
    public void testDRB() throws Exception{
        conn = JDBCUnit.getConnection();
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
        ps = conn.prepareStatement("select * from user where id = 2");
        resultSet = ps.executeQuery();
        if(resultSet.next()){
            String name = resultSet.getString("name");
            System.out.println(name);
        }

    }

约束

• 主键约束 : 非空且唯一,能够确定表中的记录
• 非空约束
• 唯一约束

create table tt(
	id int primary key auto_increment,
	name varchar(20) not null,
	phoneNum int unique
);

• 外键约束

  • 添加外键约束后,外键所在表为子表,外键关联表为父表,父表中外键关联字段不能随意删除,修改

  • 外键的作用

    1. 子表 : 向子表更新,插入数据时,应保证其外键字段的值能在父表中找到,删除没有限制.
    2. 父表 : 父表中的记录如果已经被子表中的记录关联,则不能删除,关联字段不能更新,插入新数据不受影响

    数据 : 参照外键完整性

  • 查看表的外键约束:

    • show create table 表名;

PS : mysql支持多种存储引擎,默认使用的存储引擎innoDB,其是众多存储引擎之中唯一支持事务的.

show engines;

索引

• 什么是索引:索引是一种将数据库中单列或多列的值进行排序的结构

• 将某个字段设置为索引或者将多个字段设置为索引(联合索引)

• 将索引列的值取出重新进行排序,生成的新结构即为B树

• 索引的作用:应用索引,可以大幅度提高查询的速度

  • 为什么?类似于书本中的目录,能够将某列或某几列数据的特征记录
  • 怎么用?查询的时候,查询条件中带有索引

• 索引分类

  • 普通索引

    • 对一个普通列(即不是主键,也不是外键并且没有任何约束)定位索引列

  • 唯一性索引

    • 对一个有唯一性约束的列定义为索引
    • 对一个普通列定位为索引时添加唯一性约束

    create table t_index(
    	id int primary key auto_increment,
        name varchar(20),
        adress varchar(20), 
        unique index (name)
    );
    

  • 单列索引 : 普通索引和唯一索引都可以认为是单列索引

  • 多列索引(联合索引)

  • 全文/空间 : innoDB 不支持.myISAM 支持

  • 即单列和联合

每个表都应该建立主键,并且设置自动增长

索引分类
普通索引
唯一性索引
-主键会自动成为索引列,并且属于唯一性索引
单列索引:普通索引和唯一性索引都可以认为是单列索引
多列索引(联合索引)
全文/空间:innoDB不支持,MyISAM支持.
||
单列和联合
Ps:每个表都应该建立主键,并且设置自动增长.

如何查看索引
show index from 表名;

创建索引:
-创建单列索引
–主键自动成为索引
–单列中(包含普通索引和唯一性索引)
—普通索引:
-对一个普通列(既不是主键也不是外键,并且没有任何约束)定义为索引列.
—唯一性索引(主键是特殊的唯一性索引):
create table t_index(
id int primary key auto_increment,
name varchar(20),
adress varchar(20),
unique index(name)); – 定义唯一索引
-对一个有唯一性约束的列定义为索引
-对一个普通列定义为索引时添加唯一性约束
-创建多列索引
create table t_index2(
id int primary key auto_increment,
a int,
b int,
c int,
e varchar(20)
); – 创建表

create index idx inx_t_idx on t_index2(a,b,c); – 创建多列索引
Ps:数据在插入时就会进行排序,优先按照主键排序,联合索引排序(a,b,c).
1.主键索引会生成主键B树
2.联合索引会生成联合索引B树
3.索引本身是将索引列的特征值取出并排列生成的,联合索引B树有多个列,优先按照第一个值进行排序,然后按照第二个…,如果在查询时,联合索引的第一个值没有,则联合索引树的顺序将失去意义,所以此时不会走索引.该现象称为”最左前缀原则”.

函数

• datediff(d1,d2)

  • 用于计算日期d1和d2之间相隔

   select datediff('2011-07-05','2011-06-01');
  

• adddate(d,n)

  • 用于返回起始日期d加上n天后的日期

  select adddate('2011-07-01',5);
  

• subdate(d,n)

  • 用于返回起始日期d减去n天后的日期

查询员工姓名，hrie_date, department_id 满足以下条件，雇用时间在2022年之后，department_id为88或90或110，commission_pct不为空

select name,hire_date,department_id

from employess

– hire_date >= ‘2022-01-01’

– where hire_date >= str_to_date(‘2022-01-01’,‘%Y-%m-%d’)

where date_format(hire_date,‘%y’) >= ‘2022’

and department_id in (20,50,1001)

and commission is not null;

str_to_date 字符串转时间

date_format 时间转字符串

• to_days(d)
  • 计算时间d~0000年1月1日的天数
• concat
  • 连接字段在同一列显示

批量处理

mysql本质是socket编程,使用批处理提升效率的本质是提升了输入输出流,每次读取数据的数量,减少了和服务器连接的次数

public void testBatchProcessing() throws Exception{
    conn = JDBCUnit.getConnection();
    String sql = "insert into user(name,password) values(?,?)";
    ps = conn.prepareStatement(sql);
    conn.setAutoCommit(false);
    try{
        for (int i = 0; i < 1000; i++) {
            ps.setString(1,"tom"+i);
            ps.setString(2,"123456");
            ps.addBatch();
            if(i%1000==0){
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        ps.executeBatch();
    }catch (Exception e){
        System.out.println("出现异常数据回滚");
        conn.rollback();
    }

    //手动提交
    conn.commit();

    JDBCUnit.closePreparedStatement(ps);
    JDBCUnit.closeConnection(conn);
}

行转列

将同一userid的不同科目转化成列

使用case when 条件 then 为真时 (else 为假时) end

select userid,
case when subject = '数学' then score end as '数学',
case when subject = '语文' then score end as '语文',
case when subject = '英语' then score end as '英语',
case when subject = '政治' then score end as '政治'
from tb_score;

问题:同一userid 的不同科目成绩,会产生不同的行,这不符合我们的需求

方案通过聚合函数解决

 select userid,
 sum(case when subject = '数学' then score end)as '数学',
 sum(case when subject = '语文' then score end)as '语文',
 sum(case when subject = '英语' then score end)as '英语',
 sum(case when subject = '政治' then score end)as '政治'
 from tb_score
 group by userid;

使用 if(条件,为真时,为假时)

select userid,
 sum(if(subject = '数学',score ,0))as '数学',
 sum(if(subject = '语文',score ,0))as '语文',
 sum(if(subject = '英语',score ,0))as '英语',
 sum(if(subject = '政治',score ,0))as '政治'
 from tb_score
 group by userid;

列转行

SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid

分别查询然后合并表,然后排序