数据库模块(2天)(sql基础主键，外键，唯一索引)最重要连表查询

节回顾：
    1. 以ATM引出DBMS
    2. MySQL
        - 服务端
        - 客户端
    3. 通信交流
        - 授权
        - SQL语句
            - 数据库
                create database db1; ?
                drop database db1;
                
            - 数据表
                先创建tb2部门表
                    
                create table tb1用户表(
                    id int not null auto_increment primary key,
                    name char(10),
                    department_id int,
                    p_id int,
                    constraint fk_1 foreign key (department_id,p_id) references tb2(tid,xid)
                )engine=innodb default charset=utf8;
                创建外键可以()写多个，注意里面的多列是一个主键
                
                补充：主键
                    一个表只能有一个主键
                    主键可以由多列组成
                    
                    
                补充：外键 ?
                    CREATE TABLE t5 (
                      nid int(11) NOT NULL AUTO_INCREMENT,
                      pid int(11) not NULL,
                      num int(11),
                      primary key(nid,pid)这两列组成一个主键，多列组成的主键不常用，但是要知道
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



                    create table t6(
                        id int auto_increment primary key,
                        name char(10),
                        id1 int,
                        id2 int,
                        CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
                    )engine=innodb default charset=utf8;
                                    
                
                
            - 数据行
            
                insert into tb1(name,age) values('alex',18);
                
                delete from tb1;自增列计数不会清空
                truncate table tb1;自增列计数清空
                delete from tb1 where id > 10
                
                
                update tb1 set name='root' where id > 10
                
                select * from tb;
                select id,name from tb;
            
            
    4 对于自增补充：
        desc t10;
        
        show create table t10;
        
        show create table t10 \G;
        
        alter table t10 AUTO_INCREMENT=20;修改表的自增来
            
            
        MySQL: 自增步长
            基于会话级别：
                show session variables like 'auto_inc%';    查看全局变量
                set session auto_increment_increment=2;     设置会话步长
                # set session auto_increment_offset=10;
            基于全局级别：
                show global variables like 'auto_inc%';        查看全局变量
                set global auto_increment_increment=2;         设置会话步长
                # set global auto_increment_offset=10;
                
                
        SqlServer：自增步长：
            基础表级别：
                CREATE TABLE `t5` (
                  `nid` int(11) NOT NULL AUTO_INCREMENT,
                  `pid` int(11) NOT NULL,
                  `num` int(11) DEFAULT NULL,
                  PRIMARY KEY (`nid`,`pid`)
                ) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
                
                CREATE TABLE `t6` (
                  `nid` int(11) NOT NULL AUTO_INCREMENT,
                  `pid` int(11) NOT NULL,
                  `num` int(11) DEFAULT NULL,
                  PRIMARY KEY (`nid`,`pid`)
                ) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=20 DEFAULT CHARSET=utf8
            
            
今日内容：
    0. 唯一索引(可以联合唯一索引)
        
        语法
        create table t1(
            id int ....,
            num int,
            xx int,
            unique 唯一索引名称 (列名,列名),
            constraint ....
        )
        # 
        1   1   1
        2   1   2
        唯一索引的主要作用PS: 
            唯一：
                约束不能重复（可以为空）
                PS: 主键不能重复（不能为空）
            作用是加速查找，如果以后有用到唯一但是为不为空不限制 就可以考虑主键和约束
            
            
    1. 外键的变种
        
        a. 用户表和部门表
        
            用户：
                1 alex     1
                2 root       1
                3 egon       2
                4 laoyao   3
                
            部门:
                1 服务
                2 保安
                3 公关
            ===》 一对多
        b. 用户表和博客表
            用户表：
                1 alex    
                2 root       
                3 egon       
                4 laoyao   
            博客表：
                                  FK() + 唯一
                1   /yuanchenqi/   4
                2    /alex3714/    1
                3    /asdfasdf/    3
                4    /ffffffff/    2
                
            ===> 一对一
            
                create table userinfo1(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar(64)
                )engine=innodb default charset=utf8;

                create table admin(
                    id int not null auto_increment primary key,
                    username varchar(64) not null,
                    password VARCHAR(64) not null,
                    user_id int not null,
                    unique uq_u1 (user_id),
                    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
                )engine=innodb default charset=utf8;

            
            
            
        c. 用户表（百合网） 相亲记录表
        
            示例1：
                用户表
                相亲表
                
            示例2：
                用户表
                主机表
                用户主机关系表
            ===》多对多
    
                create table userinfo2(
                    id int auto_increment primary key,
                    name char(10),
                    gender char(10),
                    email varchar(64)
                )engine=innodb default charset=utf8;

                create table host(
                    id int auto_increment primary key,
                    hostname char(64)
                )engine=innodb default charset=utf8;


                create table user2host(
                    id int auto_increment primary key,
                    userid int not null,
                    hostid int not null,
                    unique uq_user_host (userid,hostid),
                    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
                    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
                )engine=innodb default charset=utf8;
                    
                    
    2. SQL语句数据行操作补充
            create table tb12(
                id int auto_increment primary key,
                name varchar(32),
                age int
            )engine=innodb default charset=utf8;
    
        增
            insert into tb11(name,age) values('alex',12); 单条
            
            insert into tb11(name,age) values('alex',12),('root',18);多条
            
            insert into tb12(name,age) select name,age from tb11;把某个表，查出来放到另一个表
        删
            delete from tb12;
            delete from tb12 where id !=2 
            delete from tb12 where id =2 
            delete from tb12 where id > 2 
            delete from tb12 where id >=2 
            delete from tb12 where id >=2 or name='alex'
        
        改
            update tb12 set name='alex' where id>12 and name='xx'
            update tb12 set name='alex',age=19 where id>12 and name='xx'
            增删改就那几种最要还是查的多
        查
            
            select * from tb12;
            
            select id,name from tb12;
            
            select id,name from tb12 where id > 10 or name ='xxx';
            
            select id,name as cname from tb12 where id > 10 or name ='xxx'; as起别名
            
            select name,age,11 from tb12;额外的常量列11
            
            其他：
                select * from tb12 where id != 1
                select * from tb12 where id in (1,5,12);
                select * from tb12 where id not in (1,5,12);
                select * from tb12 where id in (select id from tb11) where后面的select只能写一列
                select * from tb12 where id between 5 and 12;
    
            
                通配符：
                
                select * from tb12 where name like "a%"  %表示任务多个字符(0个或多个)
                select * from tb12 where name like "a_"   _代指一个字符
    
            
                分页：
                
                    select * from tb12 limit 10;
                    
                    select * from tb12 limit 0,10;    从0开始后面取10条
                    select * from tb12 limit 10,10;    前面的是起始位置
                    select * from tb12 limit 20,10;
                    
                    select * from tb12 limit 10 offset 20;  从20开始取10条
        
                    结合python分页
                    # page = input('请输入要查看的页码')
                    # page = int(page)
                    # (page-1) * 10
                    # select * from tb12 limit 0,10; 1 
                    # select * from tb12 limit 10,10;2
                
                
                排序：
                    select * from tb12 order by id desc; 大到小
                    select * from tb12 order by id asc;  小到大
                     select * from tb12 order by age desc,id desc;
                     
                    取后10条数据
                    select * from tb12 order by id desc limit 10;

 

分组：
                
                    select count(id),max(id),part_id from userinfo5 group by part_id;
                    
                    聚合函数
                    count
                    max
                    min
                    sum
                    avg
                    
                    **** 如果对于聚合函数结果进行二次筛选时？group by排序必须使用having ****
                    count就是聚合函数
                    select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
                    
                    select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
            
                    
                连表操作：
                
                    select * from userinfo5,department5
                    
                    select * from userinfo5,department5 where userinfo5.part_id = department5.id
                    

                    select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
                    select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
                    # userinfo5左边全部显示   left join是左边表全显示，
                    
                    
                    # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
                    # department5右边全部显示  righe join是右边表全显示 
                
                
                
                    select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
                    将出现null时一行隐藏
                    
                    
                
                
                
                
                    select * from 
                        department5 
                    left join userinfo5 on userinfo5.part_id = department5.id
                    left join userinfo6 on userinfo5.part_id = department5.id
                
                
                    select 
                        score.sid,
                        student.sid 
                        from 
                    score

                        left join student on score.student_id = student.sid

                        left join course on score.course_id = course.cid

                        left join class on student.class_id = class.cid

                        left join teacher on course.teacher_id=teacher.tid
                    
            
            
            
            select count(id) from userinfo5;
                
                
                
作业练习：
    http://www.cnblogs.com/wupeiqi/articles/5729934.html
    10-15个完成

 

主键
语法：primary key(nid,pid)
一个表只能有一个主键
主键可以由多列组成
单独写一行

外键：
语法：
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
创建外键可以()写多个，注意里面的多列是一个主键

唯一索引：
语法 ：unique 唯一索引名称 (列名,列名)；unique uq_u1 (user_id),
约束不能重复（可以为空）
PS: 主键不能重复（不能为空）
作用是加速查找，如果以后有用到唯一但是为不为空不限制 就可以考虑主键和约束

外键的变种
有几种形式： FK() + 唯一；多对多(主机表)

sql语句
增 多列增，查出表进行增
删 delete
改 update
查 where后面加in，notin，betwe and ，like,通配符；分页limit跟where没关系；order by 排序；
group by分组，如果再有条件不能用where只能用having；
left join左边的表全部显示，建议使用table.sid的方式表示列

inner join 碰到NULL的(有表有有表没有隐藏)