外键的变种 数据行的操作

外键的变种　　数据行的操作（增删改查　　条件查询|通配符|限制取|排序|分组|连表操作）

一. 外键的变种:
1、唯一索引
　　create table t5(
　　　　id int,
　　　　num int,
　　　　unique(num)
　　　　)engine=Innodb charset=utf8;
　　作用:    
　　　　1）num列的值不能重复
　　　　2）加速查找
　　　　create table t6(
　　　　　　id int,
　　　　　　num int,
　　　　　　unique(id, num)
　　　　)engine=Innodb charset=utf8;

　　联合唯一索引作用:    
　　　　1）num列和id列的值不能重复
　　　　2）加速查找
　　　　create table t6(
　　　　　　num int,
　　　　　　unique(id, num......)
　　　　)engine=Innodb charset=utf8;
2、一对多
　　部门表:
　　　　id       depart_name
　　　　1          公关部
　　　　2          公共部
　　　　3          保安部        
　　员工表:
　　　　id    name  age   depart_id(外键)
　　　　1     lxxx  12      2
　　　　2     xxxx  13      1
　　　　3     xxxx  13      2        
3、一对一
　　用户表:
　　　　id    name     age  
　　　　1      zekai    23  
　　　　2      eagon    34
　　　　3      lxxx     45
　　　　4      owen     83
　　博客表:
　　　　id         url        user_id  (外键 + 唯一约束)
　　　　1       /linhaifeng       2
　　　　2       /zekai          1
　　　　3       /lxxx          3
　　　　4       /lxxx          4
4、多对多
　　用户表:
　　　　id    name    phone
　　　　1    root1    1234
　　　　2    root2    1235
　　　　3    root3    1236
　　　　4    root4    1237
  　　 5    root5    1238
 　　　6    root6    1239
     7    root7    1240
　　　　8    root8    1241
　　主机表:
　　　　id    hostname    
　　　　1    c1.com    
　　　　2    c2.com    
　　　　3    c3.com    
　　　　4    c4.com    
　　　　5    c5.com    
       为了方便查询, 用户下面有多少台主机以及某一个主机上有多少个用户, 我们需要新建第三张表:
　　user2host:
　　　　id      userid    hostid
　　　　1        1      1
　　　　2        1      2
　　　　3        1      3
　　　　4        2      4
　　　　5        2      5
　　　　6        3      2
　　　　7        3      4    
        创建的时候, userid 和 hostid 必须是外键, 然后联合唯一索引 unique(userid, hostid)
    Django orm 也会设计

二、数据行的操作
1、增
　　insert into 表名 (列名1, 列名2,) values(值1, 值2);
　　insert into 表名 (列名1, 列名2,) values(值1, 值2),(值1,值2),(值n,值n);    
　　insert into 表名 (列名1, 列名2,) select 列名1, 列名2 from 表名;        
2、删除
　　delete from 表名;
　　delete from 表名 where id > 10
　　delete from 表名 where id < 10
　　delete from 表名 where id <= 10
　　delete from 表名 where id >= 10
　　delete from 表名 where id != 10
　　delete from 表名 where id = 10 and name='xxx';  and : 并且 两个条件都必须要成立
　　delete from 表名 where id = 10 or name='xxx';   or :  或者 只要满足一个条件成立
3、修改
　　update 表名  set name='zekai', age=23 where id > 10;    
4、查询　　　　——重点

　　　　　　条件查询|通配符|限制取|排序|分组|连表操作
　　基本:
　　　　select * from 表名;
　　　　select name , age from 表名;
　　高级:
　　　　1）条件查询：　　where
　　　　　　select * from 表名 where  id=10;
　　　　　　select * from 表名 where  id >10 and id<15;
　　　　　　select * from 表名 where  id > 10;
       　　!= : 不等与
       　　>= <=
       　　between and: 闭区间
　　　　　　select * from t4 where id between 9 and 12;    
       　　in: 在某一个集合中
　　　　　　select * from t4 where id in (9,10,11....);        
　　　　　　select * from t4 where id in (select id from t3 where id between 2 and 4)
       　　是可以这样使用的, 但是不建议大家使用;
　　　　2）通配符：　　like
　　　　　　alex
　　　　　　　　select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
　　　　　　　　select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
　　　　3）限制取几条：　　limit 0, 10        
　　　　　　select * from 表名 limit 索引偏移量, 取出多少条数据;
       select * from t3 limit 0, 10;  第一页
       select * from t3 limit 10, 10;  第二页
         page = input('page:')
       page    索引偏移量      数据量(offset)
       1         0              10
       2         10             10
       3         20             10
       4         30             10
         page   (page-1)*offset   offset
       分页核心SQL语句:　　select * from t3 limit (page-1)*offset, offset;
　　　　4）排序：　　order by
　　　　　　降序:
       　　select * from t4 order by 列名 desc; descending
　　　　　　升序:
       　　select * from t4 order by 列名 asc; ascending    
　　　　　　多列:
       　　create table t7(
         　　id int auto_increment primary key,
         　　num int not null default 0,
         　　age int not null default 0
　　　　　　　　)engine=Innodb charset=utf8;
　　　　　　　　insert into t7 (num, age) values (2, 12),(3,13),(4, 12);
　　　　　　　　elect * from t4 order by num desc, name asc;
         　　如果前一列的值相等的话, 会按照后一列的值进行进一步的排序.
　　　　5）分组　　group by
　　　　　　select age, 聚合函数(count(num)/sum(num)/max(num)/min(num)/avg(num)) from 表名 group by 列名;
　　　　　　select age, avg(num) from t7 group by age;
  　　　　 select age, count(num) from t7 group by age;
　　　　　　select age, count(num) as cnt from t7 group by age;  显示别名 as
       having的二次删选:
         select age, count(num) as cnt from t7 group by age  having cnt>1;
       where 和 having的区别:
         （1) having与where类似,可筛选数据
         （2) where针对表中的列发挥作用,查询数据
         （3) having针对查询结果中的列发挥作用,二次筛选数据, 和group by配合使用
　　　　6）连表操作
　　　　　　　　select * from userinfo, department; (笛卡尔积)
　　　　　　　　select * from userinfo, department where userinfo.depart_id=department.id;
　　　　　　左连接:
　　　　　　　　select * from userinfo left join department on userinfo.depart_id=department.id;
　　　　　　　　　　左边的表全部显示, 右边没有用到不显示
　　　　　　右连接:
　　　　　　　　select * from userinfo right join department on userinfo.depart_id=department.id;
　　　　　　　　　　右边的表全部显示, 左边没关联的用null表示
　　　　　　内连接:
　　　　　　　　左右两边的数据都会显示
　　　　　　ps:
　　　　　　　　a.只需要记住左连接 left join
　　　　　　　　b.可以连接多张表 通过某一个特定的条件        
　　　　　　注意查询的顺序:
　　　　　　　　select name,sum(score) from 表 where id>10 | group by score having age>12 | order by age desc | limit 2, 10

 

补充：去重distinct 只能放在第一位

　　tee 后接一个就对路径 会把在cmd中运行的程序记录到文件中