SQL视图、优化方案、JDBC

视图
–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();
    }

}