#权限管理
#创建用户
create user 'zs' IDENTIFIED by '123';

#给用户分配权限
GRANT ALL ON mydb1.* to 'zs'; 

#撤销用户权限
REVOKE ALL ON mydb1.* from 'zs';

#删除用户 zs
drop user 'zs';

#视图操作：
#创建视图 创建 t_emp 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
create view t_emp as select employee_id,first_name,email,salary from t_employees;

#视图的操作，与原表一致的；且会联动改变原表
select * from t_emp;
select * from t_employees;

update t_emp set salary=30000 where employee_id=100;

#修改视图
#方式1：创建或替换视图   存在则替换，不存在则创建
create or REPLACE view t_emp as select employee_id,salary from t_employees;

#方式 2：直接对已存在的视图进行修改
alter view t_emp as select employee_id,first_name,email,salary from t_employees;

#删除t_emp视图
drop view t_emp;

#视图的注意事项：如果视图是聚合函数或分组形成的，则不能变更
create  view emp as select count(*) as count from t_employees;
select * from emp;
update emp set count=5;

#查询所有用户的订单
select * from user u INNER JOIN orders o ON u.userId=o.userId;

#查询用户id为 1 的所有订单详情
select * from user u INNER JOIN orders o ON u.userId=o.userId
inner JOIN orderitem oi ON o.oid=oi.oid and u.userId=1;

#查看用户为张三的订单
#先查用户张三的userid
select userId from user where username='张三';
#再匹配userID为1的订单
select * from orders where userId = (select userId from user where username='张三');


#查询出订单的价格大于800的所有用户信息。
#先查询订单大于800的userId
select DISTINCT userId from orders where totalprice>800;
#再使用枚举查询
select * from user where userId in (select DISTINCT userId from orders where totalprice>800);

//案例：增删改t_jobs数据
Connection conn = null;
Statement st = null;
try {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.通过驱动管理器获取连接对象
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC", "root", "123");
    //3.创建执行对象
    st = conn.createStatement();
    //添加
    //String sql = "insert into t_jobs values('MY_IT','IT_1',10000,25000)";
    //修改
    //String sql = "update t_jobs set min_salary=20000 where job_id='MY_IT'";
    //删除
    String sql = "delete from t_jobs where job_id='IT_PROG'";
    //4.执行SQL操作   5.返回影响条数
    int res = st.executeUpdate(sql);
    System.out.println("执行update："+res);
}catch (Exception e){
    e.printStackTrace();
}finally {
    //关闭数据库的接口为AutoCloseable
    DBUtils.closeAll(st,conn);  //统一资源关闭
}

public static void closeAll(AutoCloseable...ac){
    for(AutoCloseable a:ac){
        if(a!=null){
            try {
                a.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

//案例：查询t_jobs数据
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
    //1.加载驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.通过驱动管理器获取连接对象
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC", "root", "123");
    //3.创建执行对象
    st = conn.createStatement();
    String sql = "select * from t_jobs";
    rs = st.executeQuery(sql); //返回结果集--存储记录的集合
    while(rs.next()){ //结果集遍历--类似迭代器操作
        //参数为字段的字符串； 当获取聚合函数值，可使用整数标记
        String job_id = rs.getString("job_id");
        String job_title = rs.getString("job_title");
        String min_salary = rs.getString("min_salary"); //alt+enter
        String max_salary = rs.getString("max_salary");
        System.out.println(job_id+"--"+job_title+"--"+min_salary+"--"+max_salary);
    }
}catch (Exception e){
    e.printStackTrace();
}finally {
    //关闭数据库的接口为AutoCloseable
    DBUtils.closeAll(rs,st,conn);  //统一资源关闭
}

public static void main(String[] args) {
    Scanner sc = new Scanner(System.in);
    System.out.print("请输入用户名:");
    //next则字符串空格则结束  nextLine允许有空格，例如“zs f”是一个用户名
    String username = sc.nextLine();
    System.out.print("请输入密码:");
    String password = sc.nextLine();
    if(login(username,password)){ //调用登录功能
        System.out.println("登录成功~~！");
    }else{
        System.out.println("登录失败~~！");
    }
}
private static boolean login(String username, String password) {
    Connection conn = null;
    Statement  st   = null;
    ResultSet  rs   = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.通过驱动管理器获取连接对象
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC", "root", "123");
        //3.创建执行对象
        st = conn.createStatement();
        String sql = "select count(*) from user where username='"+username+"' and password='"+password+"'";
        rs = st.executeQuery(sql);
        if(rs.next()){
            //rs.getInt(1):获取第一个字段 count(*)字段   聚合函数：0或大于0
            return rs.getInt(1)>0;
        }
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        DBUtils.closeAll(rs,st,conn);
    }
    return false;
}

晨考：
1.什么是JDBC
Java连接数据库的标准
2.接口Statement中定义的executeQuery方法返回的类型是_集合_ResultSet____; executeUpdate返回的类型是__int____, 代表的含义是__受影响的条数____。
3.JDBC操作步骤：（只写步骤，无需写代码）
1.加载驱动
2.获取连接对象
3.创建执行对象
4.执行sql语句
5.返回结果 查询返回结果集 DML则返回影响条数
6.关闭数据库
4.有emp表,字段为 eno int,ename varchar,esex char,手写新增,修改,删除语句（仅编写sql语句）
insert into emp values(3,'ls','男');
insert into emp values(5,'wwu','男');
update emp set ename ='wu' where eno=5;
delete from emp where eno=3;