mysql sqlyog(下)

Mysql下！




   一。约束 *****


       sql语句分类： 


        DDL 数据定义语言 
        DML 数据操作语言
        DQL 数据查询语言
        DCL 数据安全隔离级别设置




       DDL 
       DML  INSERT UPDATE DELETE
       DQL  区别：数据改变的角度


       DDL DML 会改变数据么？？？
       DDL DML会真实的改变数据库中的数据！


       DQL会改变数据么？


       DQL只是单纯的查询数据！并不会改变数据库中的数据！！！


       约束！！ --》 控制!限制！权限！


       控制和限制 控制数据和限制数据！！！




       约束：约束的是DDL DML这类能改变数据库数据的语言！


       约束：不是必须的但是是很有必要的！！！




       -实体约束


        约束行的数据！标识每一行数据不重复


        约束类型： 主键约束（primary key）  唯一约束(unique)  自动增长列(auto_increment)


         1.什么是主键！


           为了防止数据重复!我们通常在表中添加一个字段！我们只需要确保该字段不重复！那么正行的数据就不重复！我们通常称该字段为主键！命名通常为id
           (移动端命名通常为 _id )；


         2.主键的分类！
        
        自然主键


        自然主键指的就是单条实体本身的属性！但是当前情况下改属性又
        不重复！可以作为主键！例如：身份证号！QQ号！


        代理主键   


        自身不包含的属性！为了确保实体不重复！我们额外添加的一列！！


        推荐：使用代理主键！！！




        1. 主键约束（primary key）


        主键约束：约束的字段 不能重复 且不能为空！！


        语法： 


           1. 直接追加
              create table xx(
  id int primary key,
              )


           2.后添加
           
             create table xx（
             id int，
             primary key(id))  




           3.外部添加
    create table xx（
               id int）


              alter table xx add primary key(id)




           4.设置联合主键 
           
              create table xx(
              stuid int,
              classid int,
              primary key(stuid,classid)
              )   


            2.唯一约束(unique)


            注意：唯一约束跟主键约束很相似！不能重复！但是主键约束
            比唯一约束多了一个不能为null


            primary key == unique + not null




            语法：


                 列名 类型 unique
 


  3.自增长  auto_increment


  自动增长列
  mysql sqlite auto_increment 
  sqlserver数据库 identity
     oracle数据库sequence


     注意：自增长通常配合主键使用！但是要求主键的类型必须是
     int  double short !


     如果主键是字符串 varchar!通常使用UUID！


     语法： 


      id  int primary key auto_increment,


     注意：自增长的列！再插入的时候不需要插入数据！
     如果指定了自增长列！那么列对应的value填null即可！






       -域(列)约束


        约束列的输入内容


        1.类型约束 


         列名 varchar(20)   int 
       
       2.非空约束 


         not null 


         unique + not null = primary key


         语法:


           列名 类型 not null


            3.默认值约束 


              default '值'


              语法：


                列名 类型 default '值'




             注意： 默认值约束插入数据！！
             
             name  sex default '男'




             insert into xx (name) value('xxx');   ---> null 


             insert into xx value('xx',null);  --> null


             insert into xx value('xx',default); --> 男




       -参照/引用完整性约束


       外键 


       主表  和 子表


       外键约束：FOREIGN KEY 


       注意：主键和外键的名字可以不一样！但是类型必须相同！


       语法：
         1.内部添加

   CONSTRAINT 约束的名 foreign key (子表中的外键) references 主表(主表的主键)

 2.外部添加

ALTER TABLE 子表的表名 ADD CONSTRAINT 约束的名字 FOREIGN KEY(子表的外键) REFERENCES 主表(主表的主键);




三大类  
七小类 


 实体约束
  primary key 
  unique
  auto_increment
 域约束
  类型约束
  not null
  default
 参照约束
    foreign key 


   二。表关系处理 *****（两个表）


      一对多（多对一） ********


      1.两个表有主次之分！
      2.需要建立主外键关联！（参照引用完整性！）


      多对多


           1.创建中间表
           2.中间表要包含两个表的主键(中间表中是外键)
           3.多对多的表没有主次之分！！


       一对一


            没必要存在！！


            1.一对一有一个主外键关联！但是子表的外键又是主键！
            2.完全可以把两个表的数据放到一起！没必要存在！




      为什么拆表！！


      一对多 




      student - score


      name  age  info height   score  




      student - score


      name  age  info height   -   score  


      -   score 




      拆表就是防止数据冗余！！！！！！！！！(冗余就是防止重复,多用于计算机数据重复)


     


   三。合并查询 **


      多表查询！！！！！


         1.合并查询 
   
  把两个查询结果合并到一起！ 


  UNION 


     会将两次的查询结果合并到一个表中！并去掉重复数据！


  UNION ALL 


  会将两次的查询结果合并到一个表中！不去掉重复数据！


  例：
 
   select * from a 
         union all
         select * from b


        注意：合并结果集的两个表！列数必须相同！列的类型必须相同！
        列名可以不同！但是会以第一个表为基准！
         




         2.99查询法


            查询学生姓名和学生分数？？？？


            99查询法是方言！sql中对应 内连接！




            -.导入查询的数据表！


               学员表和分数表
               员工表和部分表




           1.问题 
           
             99查询法！会把两个表的数据完全组装到一起！查询的结果是
             两个表数据的乘积！！！有很多数据是假数据！
             SELECT *FROM 表1,表2 where 表1.主=表2.外;
             问题：笛卡尔积    




           2.解决方案：
           
                主外键相等即可！！！！！  




           SELECT s.stuid,s.stuname,c.score FROM student s,score c WHERE s.stuid = c.stuid;




           给表起别名：




              from 表名 别名！ 




           99查询：会严格准守主外键相等！！！！如果一放在另一放中没有对应的数据！那么最后查询结果没有他的数据！！！


         3.内连接


             内连接和99查询法的效果一样！可以内连接是99查询法的官方版！


             表 [INNER] JOIN  表   ON  主 = 外


             SELECT s.stuid,c.score FROM student s 
                     INNER JOIN score c ON s.stuid = c.stuid;




              内连接： inner 可以省略！内连接也会严格准守主外键相等！如果一放在另一放中没有对应的数据！那么最后查询结果没有他的数据！！！


         4.外连接


            99查询法和内连接中！内连接也会严格准守主外键相等！
            但是在外连接中 表示有主次之分的！！！！


            LEFT 和 RIGHT 指明主表的方法！


             [OUTER] JOIN ON


              左外
                表   LEFT [OUTER]  JOIN   表  ON  主 = 外
              右外  
          表   RIGHT [OUTER]  JOIN 表 ON 主 = 外




            外连接：只有在一个表中有数据！但是还想通过连接查询查出数据！
            需要使用外连接！






        总结： 


        多表查询！先查询两个表！在依次追加！ 条件数=表数-1！


        作业： 查询老师的家乡！查询家乡的特产！特产的价格！！ *****


          5.自然连接


            NATURAL JOIN 


            去掉on主动找到主外键！


            要求：主外键类型相同!命名相同！


            SELECT * FROM  student t NATURAL JOIN score c;




         6.子查询  *****  
   
    SELECT * FROM XX WHERE  XX >(SELECT * FROM XX)




  子查询结果集： 


  单行单列
  简单的条件查询 


      > < <> != = >= <=


  单行多列


    跟job和工资一样的


    select * from xx where (job,sal) in (job,sal);


    前后列要一一对应！


  多行单列


    比30号部门工资都高或者都低


    any  高于最低工资
    all  最高工资
  1.
  form xx where sal > all(select sal from  emp where  =30)   form xx where sal > any(select sal from  emp where  =30)  
     2.
     form xx where sal > (select MAX(sal) from  emp where  =30) 


  多行多列


  多行多列可以作为表！ from 的后面！但是此表必须起别名


  from (select * from xx) 别名！！！！




  7.自连接 


  同一个表查询多次！！


  SELECT e1.ename,e1.empno,e1.mgr,e2.ename,e2.empno FROM emp e1,emp e2
            WHERE e1.empno = 7369 AND e1.mgr = e2.empno;


  作业：


      有2个以上直接下属的员工信息！


      查询老师的家乡！查询家乡的特产！特产的价格！！
   


   六。数据库备份 *




    1.库保存到磁盘中 


      mysqldump -uroot -p111 要备份的数据库名>c:\文件名字.sql




    2.磁盘中的备份导入到数据库中！
   
      需要创建一个新的数据库！！


      不需要登录： 


       mysql -uroot -p111 需要导入的库<c:\备份的文件.sql   


   回顾：


       1.DDL 


         数据定义语言 




            create  drop  alter 


     操作库 创建库  删除库 修改库
     操作表 创建表  删除表 修改表
     操作列 添加列  删除列 修改列




     create database 


     show databases 


     use 数据库名 


     select database();




     drop database kuming




     create table 表名（


         列名 类型 修饰符 


         类型：  
 
             int       4
             tinyint   1
             samllint  2
             bigint    8


             double(5,3) 99.999


             varchar (10)  0-65535
             char (10) 0-255


             数字类型全要正数 ： UNSIGNED


        约束： 
        
            约束DDL DML 




            三大类：


               实体约束


                   primary key 


                    如何添加主键：


                       1. 类型后面


                       2. 内部修改


                       primary key(列名，列名) 联合主键


                       3.外部修改 


                       alter table xx add primary key(列名);
                   unique


                   auto_increment
               域约束


                   类型 


                   not null 


                   default 


                   insert into xx  values (xx,default) 


                   not null + unique = primary key 


               参照引用约束


                   主外键 


                   foreign key 




                   外键约束 --》  一对多（多对一） 或者 一对一




                   1.


                    CONSTRAINT 约束名字 foreign key (子表的外键) references 主表（主键）


                   2.


                    alter table 子表名字 add CONSTRAINT 约束名字 foreign key (子表的外键) references 主表（主键）


                    外键： 主表和子表 主外键命名一定相同么？？？ 可以不相同


                    但是类型必须相同！！ 






       2.DML


           数据操作语言 


           INSERT UPDATE DELETE 


           INSERT INTO xx () value/values


           UPDATE 表名 set  列名=值, 列名=列名（数学逻辑运算符）值 where 


           DELETE FROM 表名 where 


           //删除 


           TRUNCATE  删除表再创建新表


           DELETE  删除数据


       3.DQL




          数据查询语言 


            SELECT *(思考  你真的需要所有么？？？) ，列名 AS  别名 ，列名 别名
            from 表名 where 列 group by having order by 列名 desc/asc limit 


            order by 列名1 desc，黎明2 asc;


            当列名1相同 按列名2 排序！！！






        表关系：




         一对多  **** 
         多对一


             两个表有主次之分！
             两个表有主外键关系




         一对一


               两个表有主次之分！
               两个表有主外键关系
               子表的外键是子表本身的主键！


               因为两个数据可以写到一个表中所有没有必要存在


         多对多




            多对多关系的两个表！应该存在一个中间表！
            并且中间表应该包含量个外键！分别关联两个表的主键！
            多对多关系的表没有主次之分！！


            开发中多对多通常拆分成一对多！




        多表查询： 


            合并结果


            union
            union all 


            union 去重复
            all 不会去重的


            unIon 要求两个表的 列数两同 并且类型相同！
            名字可以不同！但是如果不同会以第一个为准！！


            99查询法


             select * from 表1，表2，表3...表n
             where　表１.主键 = 表2.外键 and 表2。主 = ......


             别名   表的别名 


              from 表名 空格 别名！


            内连接




            表1 inner join  表2  on  主 = 外 inner join 表3 on 。。。




            内连接和99查询法！  


            严格遵循主外键相等! (王老五查不出来！) 




            外连接


            left outer join on 


            right outer join on 


            根据方向分主次表！


            自然连接


            表1  natural  表2 join 


            主动找到主外键相等  
            但是要求： 列名 和类型相同！！ 




            子查询   


            子查询： 


            select 中包含select 作为条件的select应该包含在扩号中！


            select * from where xx > (子查询的条件 select)


            子查询的结果集：
 
  单行单列   正常作为条件 >< 


    170 


  单行多列  in  ()  in  () 前后要一一对应 
 
    170    张三


  多行单列  any- min   all -max
 
    100
    110
    120


  多行多列  from 作为表！ 记住要起别名！！
 
    100  1


    110  2