MySQL基础学习笔记（2）

关于查询结果集的去重
    select distinct job from emp;
    distinct出现在所有字段最前方，表示联合去重
    统计岗位的数量：select count(distinct job) from emp;

连接查询
    实际开发中，单表查询数据使用场景有限，一般是多张表联合查询
    一个业务对应多张表，如，学生和班级

连接查询的分类
    SQL92(DBA 数据库管理员)
    SQL99(比较新的)
    表的连接方式包括：
        内连接
            等值连接
            非等值连接
            自连接
        外链接
            左外连接
            右外连接
        全连接(很少用)
    --------
    笛卡尔积现象：
        两张表进行连接查询，没有进行任何限制时，最终查询结果是两张表记录条数的乘积。
    找出每一个员工的部门名称，要求显示员工名和部门名。
    select ename,dname from emp,dept;(出现迪卡尔积现象)
    --------
    
关于表的别名：
    select e.ename,d.dname from emp e dept d;
    表的别名有什么好处？
        1.执行效率高
        2.可读性好
        
避免笛卡尔积现象，加条件进行过滤，但不会记录减少匹配次数
    找出每一个员工的部门名称，要求显示员工名和部门名。
    select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;//SQL92,以后不用
    
内连接之等值连接：连接条件是等量关系
    找出每一个员工的部门名称，要求显示员工名和部门名。
    select e.ename,d.dname from emp e (inner) join dept d on e.deptno=d.deptno;//SQL99
    inner可以省略，带着inner可读性好
    99语法：(优点：表连接条件与数据过滤条件where分离，结构清晰)
        ...
            A
        join
            B
        on
            连接条件
        where
            ...
            
内连接之非等值连接：连接条件是非等量关系
    找出每个员工的工资等级，要求显示员工名、工资、工资等级。
    select 
        e.ename,e.sal,s.grade
    from
        emp e
    join
        salgrade s
    on
        e.sal between s.losal and hisal;
        
内连接之自连接：一张表看作两张表
    找出每个员工的上级领导，要求显示员工名和对应的领导名
    select empno,ename,mgr from emp;
    select
        a.ename as '员工名',b.ename as '领导名'
    from
        emp a
    inner join
        emp b
    on
        a.mgr=b.empno;  //SQL中=表示等于逻辑运算，不是赋值运算
    
外连接
    内连接，凡是两张表能够匹配上的记录查询出来，两张表没有主副之分，是平等的。
    外连接，两张表有主副之分，主要查询主表的数据，当副表中没有与主表相匹配的数据时，副表会自动模拟出NULL与之匹配。
    左外连接(左连接)：表示左边的是主表
    右外连接(右连接)：表示右边的是主表
    左右连接 可以互换写法。
    查询每个员工的领导：select a.ename,b.ename from emp a left join emp b on a.mgr=b.empno;
    外连接最大的特点，主表的数据无条件全部查询出来。
    --------
    找出哪个部门没有员工？
    select
        d.* 
    from
        emp e 
    right join 
        dept d 
    on 
        e.deptno=d.deptno 
    where 
        e.empno is null; //副表会自动模拟出NULL
    --------
    
3张表怎么连接查询？
    找出每一个员工的名称以及工资等级
        ...
            A
        join
            B
        join
            C
        on
            ...
        表示：A表先和B表进行连接，之后A表继续和C表进行连接。
        select 
            e.ename,d.dname, s.grade
        from
            emp e
        join
            dept d
        on
            e.dept=d.dept
        join
            salgrade s
        on
            e.sal between s.losal and s.hisal;
    找出每一个员工的部门名称、工资等级和上级领导
        select 
            e.ename,g.ename,d.dname, s.grade
        from
            emp e
        left join  // 左外连接
            emp g
        on 
            e.mgr=g.empno
        join
            dept d
        on
            e.dept=d.dept
        join
            salgrade s
        on
            e.sal between s.losal and s.hisal;

子查询
    什么是子查询，子查询可以出现在哪里？(p37 where后面嵌套子查询)
    select语句嵌套select语句，被嵌套的select语句是子查询。
    select
        ...(select)
    from
        ...(select)
    where
        ...(select)
    
    --------
    where字句中使用子查询
    找出高于平均薪资的员工信息
    select * from emp where sal > avg(sal); //错
    select * from emp where sal > (select avg(sal) from emp);
    --------
    from字句中使用子查询(常用)********
    找出每个部门平均薪水的薪资等级
        第一步 每个部门的平均薪资：
        select deptno,avg(sal) as avgsal  from emp group by deptno;
        第二步 每个部门平均薪资对应的等级：
        select
            t.*,s.grade 
        from 
            (select deptno,avg(sal) as avgsal  from emp group by deptno) t 
        join
            salgrade s  
        on
            t.avgsal between s.losal and s.hisal;
    找出每个岗位平均薪资的薪资等级
        select
            t.*,s.grade 
        from 
            (select job,avg(sal) as avgsal  from emp group by job) t 
        join
            salgrade s  
        on
            t.avgsal between s.losal and s.hisal;
    找出每个部门平均的薪资等级
    找到每个人对应的薪资等级：
        select
            e.deptno,avg(s.grade)
        from 
            emp e 
        join 
            salgrade s 
        on 
            e.sal between s.losal and s.hisal  
        group by                               
            e.deptno;
        

        select
            t.deptno,avg(t.grade)
        from 
            (select e.ename,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t 
        group by
            t.deptno;
    --------
    select字句中使用子查询
    找出每个员工所在部门名称，显示员工名和部门名。
        select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
        select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
    ---------

union(可以将查询结果集相加)
    找出工作岗位是salesman和manager的员工？
    第一种：select ename,job from emp where job='salesman' or job='manager';
    第二种：select ename,job from emp where job in ('salesman','manager');
    第三种：select ename,job from emp where job='salesman'
            union
            select ename,job from emp where job='manager';
    两张不相干的表查询结果的合并就只能用 union.
    
limit的使用(重点中的重点，分页查询)
    limit是MySQL特有的，其它数据库没有
    limit取结果集中的部分数据。
    语法机制: limit startIndex,length
    --------
    取出工资前5名：(薪资降序取前5个)
    select ename,sal from emp order by sal desc;
    select ename,sal from emp order by sal desc limit 0,5;
    limit是SQL语句最后执行的一个环节。
        select    5
            xxx
        from    1
            xxx 
        where    2
            xxx
        group by 3
            xxx
        having    4
            xxx
        order by    6
            xxx
        limit
            xxx  7
    找出工资排名在第4到第9名的员工
        select ename,sal from emp order by sal desc limit 3,6;
    --------
    
通用的标准分页sql？
    每页显示3条记录：
    第一页：0,3
    第二页：3,3
    第三页：6,3
    ...
    每页显示pageSize条记录：
    第pageSize页：3*(pageSize-1),3
    java代码{
        int pageNo=2;
        int pageSize10;
        
        limit pageSize*(pageNo-1),10;
    }
DQL语句结束    
    
    
表的创建
    建表语句：create table 表名(
        字段名1  数据类型
        字段名2     数据类型
        ...
    );
    --------
    关于MySQL中常见字段的数据类型？
        int  整数
        bigint    长整型(java中的long)
        float    (String)
        fouble    (StringBuffer或StringBuilder)
        char    定长字符串
        varchar 变长字符串(最多255的字符)
        date    日期类型(对应java.sql.Date类型)
        BLOB(二进制大对象，存储图片、视频等流媒体信息)
        CLOB(字符大对象，存储较大文本，比如可以存储4G的字符串)
    --------
    char和varchar怎么选择？
        
    --------
    表名在数据库中一般建议以：t_或者tbl_开始
    创建学生表
        信息包括：
            学号、姓名、性别、班级编号、生日
            学号：bigint
            姓名：varchar
            性别：char
            班级编号：int
            生日：char
    create table t_student(
        no bigint,
        name varchar(255),
        sex char(1),
        classno varchar(255),
        birth char(10)
    );
    
insert语句插入数据
    语法格式：
        insert into 表名(字段1，字段2，字段3，...) values(值1, 值2, 值3,...);
        要求 字段的数量和值的数量相同，并且数据类型对应相同，顺序不必相同，前后对应就行，
        insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1980-10-12');
        insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban','1980-10-12',2);
        insert into t_student(name) values("wanghwu"); //除name字段，剩下自动插入NULL
        insert into t_student()
    --------
    drop table if exists t_student;//表若存在删除
        create table t_student(
        no bigint,
        name varchar(255),
        sex char(1) default 1,
        classno varchar(255),
        birth char(10)
    );
    insert执行成功，表格中间会增加一条记录。
    --------
    insert into t_student values(3,'huao','0','gaosan2ban','1981-12-10');
    此时，顺序与数量必须与建表时匹配
    一次插入多行数据
    insert into t_student
        (no,name,sex,classno,birth) 
    values
        (4,'rose','1','gaosan2ban','1980-02-03'),(5,'laotie','1','gaosan2ban','1981-02-03');
    
表的复制
    语法：create table 表名 as select语句;
    将查询结果当作表创建出来。
    create table dept1 as select * from dept;
    
将查询结果插入到一张表中
    语法：insert into dept1 select * from dept;
    
修改数据库
    语法格式：update 表名 set 字段名1=值1，字段名2=值2... where 条件;
    注意：没有条件整张表数据全部更新。
    --------
    将部门10的loc修改为sahnghai，部门名修改为renshibu
    update dept1 set loc='shanghai',dname='renshibu' where deptno=10;
    update语句中的','不要写成and.
    更新所有记录 update dept1 set loc='x',dname='y';
    --------

删除数据
    语法：delete from 表名 where 条件;
    注意：没有调价全部删除
    删除10部门数据 delete from dept1 where deptno=10;
    删除所有数据：delete from dept1;
    --------
    delete语句删除数据慢，没有释放数据的真实存储空间，删完可以后悔回滚rollback！
    
    怎么删除大表中的数据？(重点)  删除之后无法恢复(删之前确认两遍)
    truncate table emp1;//表被截断，不可回滚。永久丢失。
    --------
    删除表？
        drop table 表名; // 通用
        drop table if exists 表名; //oracle不支持

DQL(select) DML(insrt delete update) DDl(create drop alter)数据定义语言    
关于表结构的修改(alter)
    使用工具，修改表结构的代码不会出现在java代码中。出现在java代码中的sql包括：
    insert delete update select(这些都是表中的数据操作)
    增删该查一个术语 CRUD
    Create(增)  Retrieve(检索)  Update(修改)  Delete(删除)
    
    
约束作用及常见约束
    Constraint
    什么是约束？常见的约束有哪些？
        表格
        id       username(唯一性约束)       password(非空约束)
        ------------------------------------------------------
    添加约束目的是为了保证表中数据的合法性、有效性、完整性。
    非空约束(not null)          字段不能为空
    唯一约束(unique)          字段不能重复
    主键约束(primary key)      既不能空，也不能重复 pk
    外键约束(foreign key)      ...                 fk
    检查约束(check) ORACLE数据库有，mysql不支持该约束
    
非空约束 not null
    drop table if exists t_user;
    create table t_user(
        id int,
        username varchar(255) not null, //注意：not null只有【列级约束】
        password varchar(255)
    ); 
    insert into t_user(id,password) values(1,'123'); //error 1366 非空约束
    会导致java程序异常，异常处理
    insert into t_user(id,username,password) values(1,'rose','123'); //ok
    
唯一性约束unique
    唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL,NULL为空，不是具体的值
    drop table if exists t_user;
    create table t_user(
        id int,
        username varchar(255) unique
    ); 
    insert into t_user values(1,'zhangsan');
    insert into t_user values(2,'zhangsan');
    --------
    多个字段联合唯一，联合不能重复
    drop table if exists t_user;
    create table t_user(
        id int,
        usercode varchar(255),
        username varchar(255)
        unique(usercode,username) //多个字段联合添加约束【表级约束】
    ); 
    --------
    drop table if exists t_user;
    create table t_user(
        id int,
        usercode varchar(255) unique, //【列级约束】
        username varchar(255) unique
    ); 
    insert into t_user values(1,111,'zhangsan');
    insert into t_user values(2,111,'zhangsan');
    
主键约束
    怎么给一张表添加主键约束？
    drop table if exists t_user;
    create table t_user(
        id int primary key,    //【列级约束】
        username varchar(255), //注意：not null只有【列级约束】
        email varchar(255)
    ); 
    insert into t_user(id,username,email) values(1,'zs','zs@123.com'); 
    insert into t_user(id,username,email) values(2,'ls','ls@123.com'); 
    insert into t_user(id,username,email) values(3,'ww','ww@123.com'); 
    --------
    主键约束：主键中约束既不能空，也不能重复。
    主键相关的术语：
        主键约束、主键字段、主键值
    主键约束有什么用？
        表的设计三范式，第一范式要求任何一张表中要有主键。
        作用：主键值是这行记录在这张表中的唯一标识(身份证号一样)
    一张表的主键约束只能由一个.
    --------
    主键的分类
        根据主键字段的数量来划分：
            单一主键
            复合主键(多个字段联合起来添加一个主键约束，但不建议用).
        根据主键的性质来划分：
            自然主键：主键值最好就是一个和业务没有任何关系的自然数。
            业务主键：主键和系统的业务挂钩，如用银行卡的卡号做主键，身份证号码做主键。
            
外键约束
        外键约束(foreign key)
        外键字段:添加外键约束的字段
        外键值：外键字段中的每一个值
        --------
        设计数据库表，用来维护学生和班级的信息？
            第一种方案：一张表存储所有的数据
                no(pk)   name   classno     classname
                --------------------------------------
                  1       zs1     101        北京市大兴区经济技术开发区亦庄二中高三1班
                  2       zs2     102        北京市大兴区经济技术开发区亦庄二中高三2班
            缺点：冗余
            第二种方案：两张表
                t_class  班级表
                cno(pk)    cname
                -----------------------
                101        北京市大兴区经济技术开发区亦庄二中高三1班
                102        北京市大兴区经济技术开发区亦庄二中高三2班
                
                t_student 学生表
                sno(pk)    sname     classno(该字段添加外键约束后,数值只能是班级表中的cno中存在的数值)
                ------------------------
                  1         zs1      10x
                  2         zs2      10y
            t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做字表，t_class表叫作父表.
            
            删除表的时候，先删除字表，再删除父表
            创建表的时候，先创建父表，再创建字表
            create table t_class(
                cno int,
                cname varchar(255),
                primary key(cno)
            );
            create table t_student(
                sno int,
                sname varchar(255),
                classno int,
                primary key(sno),
                foreign key(classno) reference t_class(cno)
            );      
        --------
        外键值可以为NULL
        外键字段，被引用字段必须是主键吗？不一定是主键，但至少具有unique约束
        
存储引擎(了解)
    即数据在MySQL中的存储方式