Web阶段Day04

• 今日收获：

 

约束

    约束概述
      作用
         对表中的数据进行限定，保证数据的正确性、有效性和完整性。

      分类
        单表:
            1. 主键约束：primary key
            2. 非空约束：not null
            3. 唯一约束：unique
        多表:
            4. 外键约束：foreign key
        
    非空约束

        1. 创建表时添加约束
            CREATE TABLE stu(
                id INT,
                NAME VARCHAR(20) NOT NULL -- name为非空
            );
        2. 创建表完后，添加非空约束
            ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
            
        3. 删除name的非空约束
            ALTER TABLE stu MODIFY NAME VARCHAR(20);
        
    唯一约束

        1. 在创建表时，添加唯一约束
            CREATE TABLE stu(
                id INT,
                phone_number VARCHAR(20) UNIQUE -- 手机号
            );
        2. 删除唯一约束
            ALTER TABLE stu DROP INDEX phone_number;
            
        3. 在表创建完后，添加唯一约束
            ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
            
    主键约束

        1. 在创建表时，添加主键约束
            create table stu(
                id int primary key,-- 给id添加主键约束
                name varchar(20)
            );

        2. 删除主键
            -- 错误 alter table stu modify id int ;
            ALTER TABLE stu DROP PRIMARY KEY;

        3. 创建完表后，添加主键
            ALTER TABLE stu MODIFY id INT PRIMARY KEY;
            
    主键自动增长

        1. 在创建表时，添加主键约束，并且完成主键自增长
            create table stu(
                id int primary key auto_increment,-- 给id添加主键约束
                name varchar(20)
            );
        2. 删除自动增长
            ALTER TABLE stu MODIFY id INT;
            
        3. 添加自动增长
            ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

    外键约束
    
        数据准备
            -- 创建部门表(id,dep_name,dep_location)
            CREATE TABLE department(
            id INT PRIMARY KEY AUTO_INCREMENT,
            dep_name VARCHAR(20),
            dep_location VARCHAR(20)
            );

            -- 创建员工表(id,name,age,dep_id)
            CREATE TABLE employee(
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(20),
            age INT,
            dep_id INT
            );

            -- 添加 2 个部门
            INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');

            -- 添加员工,dep_id 表示员工所在的部门
            INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
            INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
            INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
            INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
            INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
            INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

        1. 在创建表时，可以添加外键
            create table 表名(
                ....
                外键列
                constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
            );
        2. 删除外键
            ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

        3. 创建表之后，添加外键
            ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
            
            
        为什么要跟主键关联
            主键是唯一并且非空的,根据主键就可以确定一条唯一的记录
        
        先写部门表,在写员工表
            主表:被引入外键的表 部门表
            从表:引入外键的表   员工表
    
    
事务

    概念
        多条sql语句完成一个业务需求,该业务包含的sql语句,要么同时成功，要么同时失败。
        
    操作：
        1. 开启事务：start transaction;
        2. 回滚：rollback;
        3. 提交：commit;
        
    案例：
        //创建账户表
        CREATE TABLE account (
            id INT PRIMARY KEY AUTO_INCREMENT,
            NAME VARCHAR(10),
            balance DOUBLE
        );
        //添加数据
        INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
        
        SELECT * FROM account;
        UPDATE account SET balance = 1000;
        
        //开启事务
        START TRANSACTION;
        //1. 张三账户 -500
        UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
        //2. 李四账户 +500
        UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
        
        //发现执行没有问题，提交事务
        COMMIT;
        
    应用
        图书管理系统
            1,系统读书,扫描书的二维码,并且录入用户的id
            2,系统把书的状态变成借出
            3,在图书-读者表中,增加该书和该读者的id
            4,读者的借书数量+1;
            
            执行了1,2之后,发生了异常,就会出现书被借出,但是没有人借走,用事务绑定这个几个动作,一旦出现异常,就直接回滚
    
   提交
        事务提交的两种方式：
            * 自动提交：
                * mysql就是自动提交的
                * 一条DML(增删改)语句会自动提交一次事务。
            * 手动提交：
                * Oracle 数据库默认是手动提交事务
                * 需要先开启事务，再提交
        修改事务的默认提交方式：
            * 查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
            * 修改默认提交方式： set @@autocommit = 0;

    特征
        1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
        2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
        3. 隔离性：多个事务之间。相互独立。
        4. 一致性：事务操作前后，数据总量不变

jdbc入门
    用java语言来操作数据库 
    
    
    jdbc
        sun公司提供给开发人员操作关系型数据库的API(一些类和接口)
        
        DriverManager类
        Connection
        Statement
        ResultSet
        
    驱动:两个设备之间通信的桥梁
    数据库驱动:jdbc的实现类,由数据库厂商提供
    
    面向接口编程    
        通过jdbc里面的接口,从驱动中取出实现类,调用实现类的重写方法,由实现类来操作数据库
        可以帮助我们屏蔽数据库之间的差异  Connection c = div.getConnection()
    
    快速入门
        1,注册驱动
        2,获得连接
        3,操作sql
            编写sql语句
            获取执行sql语句的对象
            执行sql
            处理结果
        4,释放资源
        
        
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获得连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
        //3,操作sql
            //编写sql语句
            String sql = "update account set balance = 2000 where id = 1";
             //获取执行sql语句的对象
            Statement statement = conn.createStatement();
            //执行sql
            int count = statement.executeUpdate(sql);
            // 处理结果
            System.out.println(count);
        //4,释放资源
        statement.close();
        conn.close();
        
    各个接口或类的作用
        DriverManager
            注册驱动
            获取连接
        Connection
            获取连接
            管理事务
        Statement    
            执行sql(executeUpdate 和 executeQuery)
    
多表关系
    
    实体与实体之间的关系
        一对多 
            建表原则:在多的一方建立外键指向一的一方的主键
        一对一 
            建表原则:在任意一方添加唯一的外键指向另一方的主键
        多对多 商品和订单  
            建表原则:要有中间表,至少有两个字段,分别作为外键指向两张表的主键
                
多表查询
    交叉查询
        
    内连接查询
    
    外连接查询
    
    子查询

    #交叉查询:查的是两张表的所有组成情况
    SELECT * FROM department,employee

    #内连接查询:查的是两张表的公共部分
    #1,隐式内连接(没有调用inner join关键字)
    SELECT * FROM department,employee WHERE department.id = employee.dep_id
    #2,显式内连接(调用inner join关键字)
    SELECT * FROM department INNER JOIN employee ON department.id = employee.dep_id

    #外连接查询
    #1,左外连接:左边表的全部和两张表的交集
    SELECT * FROM department LEFT JOIN employee ON department.id = employee.dep_id
    SELECT * FROM department LEFT OUTER JOIN employee ON department.id = employee.dep_id
    #2,右外连接:右边表的全部和两张表的交集
    SELECT * FROM department RIGHT JOIN employee ON department.id = employee.dep_id
    SELECT * FROM department RIGHT OUTER JOIN employee ON department.id = employee.dep_id

    #子查询:sql语句嵌套sql语句
    -- 结果是单行单列的:可以把子查询作为条件,使用运算符去判断(>,<,=)
     #年龄最大的员工的信息
     #先查最大的年龄
     SELECT MAX(age) FROM employee 
     #再查员工年龄等于最大年龄的员工
    SELECT *FROM employee WHERE age = ( SELECT MAX(age) FROM employee)

    -- 结果是单行多列的:可以把把子查询作为条件,使用in,any,all,exist
    #查询研发部和销售部的员工信息
    #先查研发部和销售部的id
    SELECT id FROM department WHERE dep_name='研发部' OR dep_name='销售部'
    #再在员工表中找到符合对应id的员工
    SELECT * FROM employee WHERE dep_id IN (SELECT id FROM department WHERE dep_name='研发部' OR dep_name='销售部')

    -- 结果是多行多列的:可以把子查询作为一张虚拟的表参与查询
    #查询员工年龄大于20岁的员工信息和部门信息
    #先查询员工年龄大于20岁的员工信息
    SELECT * FROM employee WHERE age > 20;
    #再将查到的员工信息作为一张表,和部门表进行查询
    SELECT * FROM department t1,(SELECT * FROM employee WHERE age > 20) t2 WHERE t1.id = t2.dep_id