【SQL基础学习】----基础篇(2)

一、数据库的设计

多表间关系
    1. 一对一       
        如：人与身份证   分析：一个人对应一个身份证，一个身份证对应一个人     
    2. 一对多（多对一）        
        如：部门和员工   分析：一个部门有多个员工，一个员工对应一个部门    
    3. 多对多        
        如：学生和课程   分析：一个学生可以选择多门课程，一门课程可以被多个学生选择

实现一对多（多对一）  
    如：员工和部门。  
    实现方式：
        在多的一方建立外键，指向一的一方的主键

实现多对多   
    如：学生与选课。  
    实现方式：
        多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段

作为第三张表的外键，分别指向两张表的主键{中间表中的主键关系是成对出现且不重复的，这种形式称为联合主键}

实现一对一  
    如：人和身份证。
    实现方式：
        可以在任意一方添加唯一外键指向另一方的主键     
        {一对一用的不多，因为可以合并}

利用数据库，在后台实现多表、元组的修改

案例一：       
    案例场景：
        一个用户上旅游网页收藏旅游线路      
    案例中实体分析：    
        用户实体{主键列，名字列，密码列}；
        线路实体{主键列，名字列，价格列}；
        旅游线路分类实体{主键列，名字列} 

    实体间关系分析：
        一个旅游线路分类对应多个线路，一个线路对应一个旅游线路分类；
        一个用户可以收藏多个线路，一个线路可以被多个用户收藏

    实体间外键设置：
        一对多{在多的一方设置外键指向一的一方}，
        多对多{引入第三张表，实现两张主表中主键间的对应关系}

    SQL语句实现：       
        创建分类线路表：
            CREAT TABLE tab_category( 
                cid INT PRIMARY KEY AUTO_INCREMENT, 
                cname VARCHAR(100) NOT NULL UNIQUE
            ); 

        旅游线路表：
            CREAT TABLE tab_route( 
                rid INT PRIMARY KEY AUTO_INCREMENT, 
                rname VARCHAR(100) NOT NULL UNIQUE,
                price DOUBLE, rdate DATE, cid INT,
                CONSTRAINT  外键名称 FOREIGN KEY (cid) REFERENCES tab_category
            );

        用户表：
            CREATE TABLE tab_user( 
                uid INT PRIMARY KEY AUTO_INCREMENT, 
                username{用户昵称} VARCHAR(100) UNIQUE NOT NULL,
                upassword VARCHAR(30) NOT NULL, 
                uname{用户姓名} VARCHAR(100),
                birthday DATE,    
                sex CHAR(1) DEFAULT '男', 
                telephone VARCHAR(11), 
                email VARCHAR(100
            );    

        用户和线路的中间表：
            CREATE TABLE tab_favorite(
                rid INT, data DATETIME{收藏时间},
                uid INT {-- 创建复合主键}PRIMARY KEY(rid,uid),
                FOREIGN KEY(rid) REFERENCES tab_route(rid),
                FOREIGN KEY(uid) REFERENCES tab_user(uid)
        );

二、范式：

范式概念：
    设计数据库时需要遵循的规范以避免数据库中表的冗余过大，在添加，删除数据时产生异常，要遵循后边的范式，要求先遵循前边的所有范式要求

几个概念：     
    1. 函数依赖：A-->B，若通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A   
        如：
            学号-->姓名。   
            (学号，课程名称)-->分数    

    2. 完全函数依赖：A-->B 若A是一个属性组，则B属性值的确定要依赖于A属性组中所有的属性值  
        如：
            （学号，课程名称）-->分数

    3. 部分函数依赖：A-->B 若A是一个属性组，则B属性值的确定要依赖于A属性组中部分的属性值  
        如：
            （学号，课程名称）-->姓名

    4.传递函数依赖：A-->B，B-->C,若通过A属性（属性组）的值，可以确定唯一B属性的值,若通过B属性（属性组）的值，可以确定唯一C属性的值,则称C传递函数依赖于A         
        如：
            学号-->系名，系名-->系主任

    5.码：若在一张表中，一个属性或者属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码  
        主属性：
            码属性组中的所有属性            
        非主属性：
            将码属性排除后剩下的属性       
        如：
            选课表中的码：（学号，课程名称）  

范式分类：
    1. 第一范式（1NF）:
        每一列都是不可分割的原子数据项
            (满足二维表)

    2. 第二范式（2NF)：
        在1NF基础上非码属性必须完全依赖于候选码（在1NF的基础上消除非主属性对主码部分的函数依赖）
            （满足二维表，消除属性间的部分依赖)    

    3. 第三范式（3NF):
        在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）   
            （满足二维表，消除属性间的部分依赖，消除传递依赖)

数据库的备份和还原
    1: 命令行：     
        备份的语法：
            mysql -u用户名称 -p密码 数据库名称 > 保存的路径

        还原的语法：
            1.登录数据库（mysql -uroot -hlocalhost –P端口号 –p密码）；
            2.创建数据库；
            3.使用数据库；
            4.执行文件。 score 文件路径

    2.图形化界面：
        在图形化界面中随便点点

三、多表查询

含义：称连接查询，当查询的字段来自于多个表时，就会使用连接查询

笛卡尔积：
    A表中的元组N和B表中的元组M完全组合构成的新表,共有N*M个元组（表1有m行，表2有n行，结果m*n行）     
        语法：
            SELECT * FROM 表1,表2  
    发生原因：没有有效的连接条件
    避免：添加有效的连接条件
    要完成多表查询，需要消除笛卡尔积中无用的数据

单表查询语法：
    SELECT 列名 FROM 表名 WHERE...

多表查询分类：
        按年代分类
            sql92标准：仅仅支持内连接
            sql99标准：支持内连接+外连接(左外和右外)+交叉连接
        按功能分类：
            内连接：
                等值连接
                非等值连接
                自连接
            外连接
                左外连接
                右外连接
                全连接
            交叉连接和子查询

一、sql92标准
        1.等值特点{
            1.多表等值连接的结果为多表的交集部分
            2.n表连接至少需要n-1个连接条件
            3.多表的顺序没有要求
            4.一般需要为表起别名
            5.可以搭配其他查询条件比如排序，分组，筛选
        }

        1.等值连接

查询班级和班级对应的班主任名字

SELECT stname,teacher_name 
FROM teacher00,stclass00
WHERE stclass00.teacher_id=teacher00.teacher_id;

查询源名称和对应的部门名称

SELECT  name，department_name
FROM employees,departments
WHERE employee.`department_id`=dapartments.`department_id`;

        A.为表起别名(提高语句简洁度，区分多个重名字段，如果未表起了别名，则查询字段就不能使用原来的表名去限定)

查询员工名，工种号，工种名

SELECT e.name,e.job_id,j_title
FROM employees AS e,jobs j
WHERE e.`job_id`=j.`job_id`;
或
SELECT e.name,e.job_id,j_title
FROM jobs j,employees AS e
WHERE e.`job_id`=j.`job_id`;

        B.添加筛选

查询有奖金的员工名称、部门名称

SELECT name,department_name,commission_pct
EROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

        C.添加分组

查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM department d,locations l
WHERER d.`location_id`=l.`location_id`
GROUP BY city

查询有奖金的每个部门的部门名称和部门领导编号和该部门的最低工资

SELECT dapartment_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_id,manager_id

 查询每个工种、每个部门的部门名称，工种名称和最低工资

SELECT department_name,job_name,MIN(salary) 最低工资
FROM employees e,department d,hobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_name;

        D.加排序

查询每个工种的工种名称和员工个数，并且按员工个数降序

SELECT job_title count(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

        E.三表连接

查询员工名、部门名和所在的城市

SELECT name,department_name,city
FROM employees e, departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

2.非等值连接

查询员工的工资和级别

SELECT salary，grade_level
FROM employees e，job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

3.自连接(自己和自己连接)：

在一张表上查询员工名字和其上级的名字

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

内连接查询：      
    1.隐式内连接：
        使用where条件消除无用数据

        如在员工表1和部门表2中查询员工对应的部门信息：
            写法1：
                SELECT * FROM 表1,表2 WHERE 表1.'部门id'=表2.'部门id';          
            写法2：
                SELECT  
                    t1.'属性1', -- 表1的1属性
                    t2.'属性2'  -- 表2的2属性
                FROM   
                    表1 t1, -- 表1重新命名为t1
                    表2 t2  -- 表2重新命名为t2
                WHERE    
                    t1.'属性x'=t2.'属性y' ；

    2.显式内连接：   
        语法：
            SELECT 属性 FROM  表1 INNER JOIN 表2 ON 条件    

        如：
            SELECT * FROM 表1 INNER JOIN 表2 ON 条件   
            或者：SELECT * FROM 表1 JOIN 表2 ON 条件

    内连接查询注意：   
        1.从哪些表中查询数据    
        2.条件什么        
        3. 查询哪些字段

外连接查询:         
    1.左外连接：
        查询左边所有数据及其交集部分{交集部分查询由内连接体现}，

        常用语法：
            SELECT 属性 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件  

        如：在员工表1，部门表2中查询所有员工信息，员工如果有部门就显示部门，没有部门就不显示部门
            错误{SELECT t1.*,t2.'name' FROM  表1 t1,表2 t2 WHERE t1.'属性X'=t2.'属性Y'}   
            正确{SELECT t1.*,t2.'name' FROM  表1 t1 LEFT JOIN 表2 t2 ON t1.'属性X'=t2.'属性Y'}

    2.右外连接：查询右边所有数据及其交集部分{交集部分查询由内连接体现}     
        语法：
            SELECT 属性 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件       

子查询：       
    概念：
        查询中嵌套查询，称嵌套查询为子查询

    如：有一张员工表1，现要查询工资最高的员工信息：        
        1.查询最高工资是多少（9000）：
            SELECT MAX(salary) FROM 表1；

        2.查询员工信息，并且工资是最高9000：
            SELECT * FROM 表1 WHERE 表1.'salary'=9000；

        3.现要查询工资最高的员工信息:
            SELECT * FROM 表1 WHERE 表1.'salary'=(SELECT MAX(salary) FROM 表1)

    子查询的不同情况：        
        子查询结果是单行单列：
            子查询可以作为条件，使用运算符去判断    
            运算符：>,<,=,>=,<=  

    查询员工工资小于平均工资的人：
        SELECT * FROM 表1 WHERE 员工表.'salary'<(SELECT AVG(salary) FROM 员工表)  

    子查询结果是多行单列：
        子查询可以作为条件，使用运算符IN判断

    查询'财务部'和'市场部'所有员工的信息：     
        1. SELECT id FROM 部门表 WHERE NAME='财务部' OR NAME='市场部'
        2. SELECT * FROM 员工表 WHERE 部门属性='财务部' OR 部门属性='市场部'
        3. SELECT * FROM 员工表 WHERE 部门属性 IN (SELECT id FROM 部门表 WHERE NAME='财务部' OR NAME='市场部')

    子查询结果是多行多列：
        子查询可以作为一张虚拟表，参与查询

    查询员工入职日期是2011-11-11日之后的一员工信息和部门信息：       
        1.SELECT * FROM 员工表 WHERE 员工表.'join_date' > '2011-11-11'   
        2.SELECT * FROM 部门表 t1,(
            SELECT * FROM 员工表
            WHERE 员工表.'join_date' > '2011-11-11'
        ) t2 WHERE t1.'属性X'=t2.'属性Y'

多表查询步骤：
    1.查的内容来自那几张表；
    2.查询的条件（主外键，运算符）

四、事务

    1.事务的基本介绍   
    2.事务的四大特征      
    3.事务的隔离级别

事务的基本介绍：        
    1.概念：
        若一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时进行，要么同时失败  

    2.操作：  
        a:开启事务：
            start transaction = START TRANSACTION；

        b:回滚：
            rollback = ROLLBACK；

        c:提交：
            commit = COMMIT

    如：在数据库中要体现张三给赵六转500：     
        CREATE TABLE account(
            id INT PRIMARY KEY AUTO_INCREMENT,
            pname VARCHAR(10),balance DOUBLE
        ); -- 建表

        INSERT INTO account(
            pname,balance
        ) VALUES (
            'zhangsan',1000
        ),(
            'zhaoliu',1000
        )  -- 插入数据

        SELECT * FROM  account;    
        UPDATE account SET balance=1000;   
            -- 张三给赵六转500         
            -- 0.开启事务       
        START TRANSACTION;   
            -- 1.张三账户-500     
        UPDATE account SET balance=balance-500 WHERE pname ='zhangsan'   
            -- 2. 赵六账户+500   
        UPDATE account SET balance=balance-500 WHERE pname ='zhaoliu'   
            测试，如果出错：ROLLBACK；        
            测试，如果正确：COMMIT；                

事务提交方式：    
    自动提交：
        mysql中自动提交：一条DML(增删改)语句会自动提交一次事务

    手动提交：
        oracle数据库默认是手动提交事务，需要先开启事务，再提交

修改事务的提交方式：     
    查看事务的默认提交方式：
        SELECT @@autocommit； -- 1{自动提交}   ；0{手动提交}

    修改默认提交方式：SET @@autocommit=0;

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

事务的隔离级别：       
    概念：
        多个事务之间相互隔离，相互独立。但若多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
    存在问题:         
        1.脏读:
            一个事务读取到另外一个事务没有提交的数据

        2.不可重复读(虚读):
            在同一事务中，两次读取到的数据不一样

        3.幻读:
            一个事务操作(DML)表中所有记录，另外一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别(级别字符串)：
    1.read uncommitted:读未提交
        产生的问题:脏读，不可重复读，幻读

    2.read committed:读已提交(oracle默认)
        产生的问题:不可重复读，幻读

    3.repeatable read:可重复读(MySQL默认)
        产生的问题:幻读

    4.serializable:串行化

    可以解决所有问题      
        注意:
            隔离级别从小到大安全性越来越高，但效率越来越低

        数据库查询级别:
            SELECT @@tx_isolation;

        数据库设置隔离级别:
            SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串；

    演示
        SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串；
        START TRANSACTION;
            -- 转账操作
        UPDATE account SET balance=balance-500 WHERE id=1；
        UPDATE account SET balance=balance+500 WHERE id=2；

五、DCL

DBA:数据库管理员
DCL(管理用户，并对用户进行授权)
    1.管理用户
    A.增加用户{1.语法：CREATE USER '用户名'@'主机名'IDENTIFIED BY'密码'；}
    B.删除用户{1.语法：DROP USER '用户名'@'主机名';}
    C.查询用户{                    
        -- 1. 切换到MySQL数据库          
            USE mysql;

        -- 2.查询user表                       
            SELECT * FROM USER;

        通配符：%可以表示在任意主机访问使用用户登录数据库
            }
    D.修改用户密码:{
        1.语法：
            UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER ='用户名

        2.语法：
            SET PASSWORD FOR '用户名'@'主机名' =PASSWORD('新密码')
    }

mysql中忘记了root用户密码：
    1. cmd--(以管理员身份运行）-->net stop mysql --停止mysql服务   
    2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
    3. 打开新的cmd窗口，直接输入mysql命令，回车，就可以登录成功  
    4. use mysql;      
    5. UPDATE user set password = password('新密码') where user='root';  
    6. 关闭两个窗口   
    7. 打开任务管理器，手动结束mysqld.exe的进程  
    8. 启动mysql服务器    
    9. 使用新密码登录

权限管理：       
    1.查询权限：{
        -- 查询权限：
            SHOW GRANTS FOR '用户名'@'主机名(可以用%号替代）';
    }

    2.授予权限：{
        -- 授予权限：
            GRANT 权限列表 ON 数据库名 TO '用户名'@'主机名';    
    }

    给张三用户授予所有权限，在任一数据库的任一表上：
        GRANT ALL ON *.* TO 'zhangsan'@'localhost';

    3.撤销权限：{
        --权限撤销 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
        例如：REVOKE UPDATE ON db3.'account' FROM '用户名'@'主机名';
    }