mysql增加自增索引_mysql唯一索引、自增、增删改查

Day58

上节回顾：1. 以ATM引出DBMS2. MySQL-服务端-客户端3. 通信交流-授权-SQL语句-数据库create databasedb1; ?drop databasedb1;-数据表

先创建tb2部门表create tabletb1用户表(

idint not null auto_increment primary key,

namechar(10),

department_idint,

p_idint,constraint fk_1 foreign key (department_id,p_id) referencestb2(tid,xid)

)engine=innodb default charset=utf8;

补充：主键

一个表只能有一个主键

主键可以由多列组成

补充：外键 ?CREATE TABLEt5 (

nidint(11) NOT NULLAUTO_INCREMENT,

pidint(11) not NULL,

numint(11),primary key(nid,pid)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;create tablet6(

idint auto_increment primary key,

namechar(10),

id1int,

id2int,CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCESt1(nid,pid)

)engine=innodb default charset=utf8;-数据行insert into tb1(name,age) values('alex',18);delete fromtb1;truncate tabletb1;delete from tb1 where id > 10

update tb1 set name='root' where id > 10

select * fromtb;select id,name fromtb;4对于自增补充：desct10;

showcreate tablet10;

showcreate tablet10 \G;alter table t10 AUTO_INCREMENT=20;

MySQL: 自增步长

基于会话级别：

show session variableslike 'auto_inc%'; 查看全局变量set session auto_increment_increment=2; 设置会话步长

#set session auto_increment_offset=10;

基于全局级别：

show global variableslike 'auto_inc%'; 查看全局变量set global auto_increment_increment=2; 设置会话步长

#set global auto_increment_offset=10;

SqlServer：自增步长：

基础表级别：CREATE TABLE`t5` (

`nid`int(11) NOT NULLAUTO_INCREMENT,

`pid`int(11) NOT NULL,

`num`int(11) DEFAULT NULL,PRIMARY KEY(`nid`,`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8CREATE TABLE`t6` (

`nid`int(11) NOT NULLAUTO_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 tablet1(

idint....,

numint,

xxint,unique唯一索引名称 (列名,列名),constraint....

)

#1 1 1

2 1 2PS:

唯一：

约束不能重复(可以为空)

PS: 主键不能重复(不能为空)

加速查找1. 外键的变种

a. 用户表和部门表

用户：1 alex 1

2 root 1

3 egon 2

4 laoyao 3部门:1服务2保安3公关===》 一对多

b. 用户表和博客表

用户表：1alex2root3egon4laoyao

博客表：

FK()+唯一1 /yuanchenqi/ 4

2 /alex3714/ 1

3 /asdfasdf/ 3

4 /ffffffff/ 2

===>一对一create tableuserinfo1(

idint auto_increment primary key,

namechar(10),

genderchar(10),

emailvarchar(64)

)engine=innodb default charset=utf8;create tableadmin(

idint not null auto_increment primary key,

usernamevarchar(64) not null,

passwordVARCHAR(64) not null,user_id int not null,unique uq_u1 (user_id),CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCESuserinfo1(id)

)engine=innodb default charset=utf8;

c. 用户表(百合网) 相亲记录表

示例1：

用户表

相亲表

示例2：

用户表

主机表

用户主机关系表===》多对多create tableuserinfo2(

idint auto_increment primary key,

namechar(10),

genderchar(10),

emailvarchar(64)

)engine=innodb default charset=utf8;create tablehost(

idint auto_increment primary key,

hostnamechar(64)

)engine=innodb default charset=utf8;create tableuser2host(

idint auto_increment primary key,

useridint not null,

hostidint not null,uniqueuq_user_host (userid,hostid),CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCESuserinfo2(id),CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCEShost(id)

)engine=innodb default charset=utf8;2. SQL语句数据行操作补充create tabletb12(

idint auto_increment primary key,

namevarchar(32),

ageint)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 fromtb11;

删delete fromtb12;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 * fromtb12;select id,name fromtb12;select id,name from tb12 where id > 10 or name ='xxx';select id,name as cname from tb12 where id > 10 or name ='xxx';select name,age,11 fromtb12;

其他：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 fromtb11)select * from tb12 where id between 5 and 12;

通配符：select * from tb12 where name like "a%"select * from tb12 where name like"a_"

分页：select * from tb12 limit 10;select * from tb12 limit 0,10;select * from tb12 limit 10,10;select * from tb12 limit 20,10;select * from tb12 limit 10 offset 20;

# 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 bypart_id;count

max

min

sum

avg

**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****

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 * fromuserinfo5,department5select * from userinfo5,department5 where userinfo5.part_id =department5.idselect * from userinfo5 left join department5 on userinfo5.part_id =department5.idselect * from department5 left join userinfo5 on userinfo5.part_id =department5.id

# userinfo5左边全部显示

#select * from userinfo5 right join department5 on userinfo5.part_id =department5.id

# department5右边全部显示select * from userinfo5 innder join department5 on userinfo5.part_id =department5.id

将出现null时一行隐藏select * fromdepartment5left join userinfo5 on userinfo5.part_id =department5.idleft join userinfo6 on userinfo5.part_id =department5.idselectscore.sid,

student.sidfromscoreleft join student on score.student_id =student.sidleft join course on score.course_id =course.cidleft join class on student.class_id =class.cidleft join teacher on course.teacher_id=teacher.tidselect count(id) fromuserinfo5;

作业练习：

http://www.cnblogs.com/wupeiqi/articles/5729934.html10-15个完成