cgb2106-day05

客官°小女子只卖身不卖艺 2021-09-11 03:20 173阅读 0赞

### 文章目录 ###

*   *  一,视图
     *   *  \--1,概述
         *  \--2,测试
     *  二,SQL练习
     *   *  \--1,测试
     *  三,SQL优化的方案
     *   *  \--1,总结
     *  四,JDBC
     *   *  \--1,概述
         *  \--2,使用步骤
         *  \--3,导入jar包
         *  \--4,入门案例
         *  \--5,练习

## 一,视图 ##

### –1,概述 ###

可以把SQL的查询结果缓存起来,存入视图中.  
好处: 简化了SQL的编写  
坏处: 没法做SQL的优化,占用空间.

### –2,测试 ###

#1.创建视图:缓存了查询结果
    CREATE VIEW `cgb2106`.`emp_view` 
    AS
    #以下的SQL执行的结果会被缓存到视图中
    (SELECT * FROM emp WHERE ename LIKE '%a%');
    
    #2.使用视图
    #SELECT * FROM emp WHERE ename LIKE '%a%'
    SELECT * FROM emp_view 
    #直接查的视图,视图就是一张特殊的表,最好只做查询

## 二,SQL练习 ##

### –1,测试 ###

SELECT * FROM emp #低效
    SELECT empno,ename,job,sal FROM emp #高效
    SELECT * FROM emp WHERE empno=100
    SELECT * FROM emp WHERE job LIKE '总%'#高效
    SELECT * FROM emp WHERE job LIKE '_总'#_通配一个字符
    SELECT * FROM emp WHERE job LIKE '%总%'#%通配n个字符
    SELECT * FROM emp WHERE empno>100 AND empno<300
    SELECT * FROM emp WHERE empno BETWEEN 100 AND 300#包含
    SELECT * FROM emp 
    #where ename='rose' and job='副总' #并且关系
    #where ename='jack' or job='员工' #或者关系
    WHERE ename='jack' OR ename='rose' #或者关系
    #WHERE ename in('jack','rose')
    
    #查询>45岁的人的名字
    SELECT sname FROM students 
    #where里不能用聚合函数max min sum avg count
    WHERE YEAR(NOW())-YEAR(sbirthday) > 45
    
    #按照年龄排序
    SELECT *,YEAR(NOW())-YEAR(sbirthday) AS age 
    FROM students
    ORDER BY age
    #查询年龄最大的
    #desc limit 1
    SELECT sname,MIN(sbirthday) FROM students
    GROUP BY sname
    #查询高于平均工资的员工信息
    SELECT * FROM emp WHERE sal > (
      SELECT AVG(sal) FROM emp
    )
    #查询部门地址在二区的员工信息
    SELECT deptno FROM dept WHERE loc='二区'
    SELECT * FROM emp WHERE deptno IN (2,3)
    SELECT * FROM emp WHERE deptno IN (
      SELECT deptno FROM dept WHERE loc='二区'
    )
    
    #笛卡尔积/join/子查询
    #练习1: 查询李军的总得分
    SELECT SUM(degree) FROM scores WHERE sno=(
     SELECT sno FROM students WHERE sname='李军'
    )
    
    SELECT SUM(b.degree) FROM 
    students a,scores b
    WHERE a.sno=b.sno AND a.sname='李军'
    
    SELECT SUM(b.degree) FROM 
    scores b LEFT JOIN students a 
    ON a.sno=b.sno
    WHERE a.sname='李军'
    
    #练习2: 查询易天负责的课程名称
    SELECT cname FROM courses WHERE tno=(
     SELECT tno FROM teachers WHERE tname='易天'
    )
    SELECT b.cname FROM 
    courses b LEFT JOIN teachers a
    ON a.tno=b.tno 
    WHERE  a.tname='易天'
    
    SELECT b.cname FROM 
    teachers a,courses b
    WHERE a.tno=b.tno AND a.tname='易天'

## 三,SQL优化的方案 ##

### –1,总结 ###

1, 尽量使用字段名代替\*  
2, 做表设计时,字段的类型最好是varchar代替char  
3, 字段里的值,最好用数字代替字符串  
4, 尽量把过滤条件精细,能用and不用or  
5, 索引的设计,最多5个,不能太多  
6, 模糊查询,尽量要确定开始元素,让索引生效  
7, 数据库对数字没有严格的要求,name=123索引失效,name='123’索引生效  
8, 无用的索引最好及时删除

## 四,JDBC ##

### –1,概述 ###

是一种 专门用来 通过java程序操作数据库的标准

### –2,使用步骤 ###

导入jar包(存放了大量java工具类的压缩包)  
使用工具类,连接数据库  
通过程序发起SQL,操作数据库  
数据库把结果返回给java程序  
java程序解析结果

### –3,导入jar包 ###

创建lib文件夹: 选中project-右键-new-directory  
导入jar包: 找到你磁盘里的mysql的jar包,复制,直接粘贴到lib里  
编译jar包: 选中jar包-右键-add as library  
检查: jar包前面的箭头可以展开

### –4,入门案例 ###

package cn.tedu.test;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    
    //测试 jdbc的入门案例
    //需求:
    public class Test1 { 
        public static void main(String[] args) throws Exception { 
           //1,注册驱动 Driver
           Class.forName("com.mysql.jdbc.Driver");
           //2,获取连接(用户名 密码 端口号 库名)
                    // 协议 IP地址:端口号 库名
        String url = "jdbc:mysql://localhost:3306/cgb2106";
           Connection c = DriverManager.getConnection(url,"root","root");
           //3,获取传输器
           Statement s = c.createStatement();
           //4,执行SQL --executeQuery执行查询的SQL
           ResultSet r = s.executeQuery("SELECT * FROM dept");
           //5,处理结果集
           while( r.next() ){ //next()判断有没有数据,有数据就返回true
               String a=r.getString(1);//获取第1列的数据
    // String b=r.getString("deptno");//获取指定字段名的值
               String b=r.getString(2);//获取第2列的数据
               String d=r.getString(3);//获取第3列的数据
               System.out.println(a+b+d);
           }
           //6,释放资源
           r.close();//结果集
           s.close();//传输器
           c.close();//连接
        }
    }

### –5,练习 ###

需求2: 查询用户表的所有数据  
思路: 先建user表(id,name,password) , 添加数据  
再用jdbc连接数据库 , 发起select语句  
创建表

CREATE TABLE USER(
     id INT PRIMARY KEY AUTO_INCREMENT,
     NAME VARCHAR(20),
     PASSWORD VARCHAR(10)
    )
    INSERT INTO USER VALUES(NULL,'jack','123456')
    INSERT INTO USER VALUES(NULL,'rose','123')
    SELECT * FROM USER

package cn.tedu.test;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.Scanner;
    
    //测试用户表
    public class Test2 { 
        public static void main(String[] args) throws Exception { 
    // method();//查询所有用户信息
            method2();//根据用户名和密码查询,用户信息,查到了才能登录
        }
        //模拟用户登录:利用JDBC,根据用户名和密码查询用户信息
        private static void method2() throws Exception{ 
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库的连接
            String url="jdbc:mysql://localhost:3306/cgb2106?characterEncoding=utf8" ;
            Connection c = DriverManager.getConnection(url,"root","root");
            //获取传输器
            Statement s = c.createStatement();
            //执行SQL
    //String sql= "SELECT * FROM USER WHERE NAME='jack'#' AND PASSWORD='123'";
        String a = new Scanner(System.in).nextLine();
        String b = new Scanner(System.in).nextLine();
    
    //问题::::当用户输入了特殊数据时,发生的现象叫SQL注入攻击
    //当用户名输入了jack'# 时候,SQL中出现了特殊符号#,在SQL里表示注释
    //只需要输入用户名正确,不需要密码也能登录
    //SQL语义变了:SELECT * FROM USER WHERE NAME='jack'#' AND PASSWORD='123'
    String sql= "SELECT * FROM USER WHERE NAME='"+a+"' AND PASSWORD='"+b+"'";
            ResultSet r = s.executeQuery(sql);
            //处理结果集
            if( r.next() ){ //next()用来判断有没有数据,查到了返回true
                System.out.println("恭喜您,登录成功!");
            }else{ 
                System.out.println("用户名或者密码输入错误!");
            }
            //释放资源
            r.close();
            s.close();
            c.close();
        }
        //利用JDBC,查询所有用户信息
        private static void method() throws Exception { 
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库的连接
    String url="jdbc:mysql://localhost:3306/cgb2106?characterEncoding=utf8" ;
            Connection c = DriverManager.getConnection(url,"root","root");
            //获取传输器
            Statement s = c.createStatement();
            //执行SQL
            ResultSet r = s.executeQuery("select * from user");
            //处理结果集
            while( r.next() ){ //如果有数据就返回true
                String id = r.getString(1);//获取数据
                String name = r.getString(2);//获取数据
                String pwd = r.getString(3);//获取数据
                System.out.println(id+name+pwd);
            }
            //释放资源
            r.close();
            s.close();
            c.close();
        }
    
    }