MySQL的基础操作（二）（补充）

上节简单提及的自增、外键、索引、增删改查，由于实际应用中会有更多更复杂的需求，在本节将有更具体的阐述（复习自用）

注：为了更便携书写sql语句，任意表名用t1表示，任意数字用2表示，任意列名用num、id、name等表示

一、自增补充

自增一般默认从1开始，以1为步长递增，但实际应用中我们有时要求起始值为特定值或者需更改步长

1、起始值更改

alter table  t1 auto_increment=2;

2、步长更改

（1）在一次会话中更改

登录一次表示一次会话，另一次登录不受影响

set session auto_increment_increment=2;

（2）在全局中更改

谨慎使用，更改后每次登录都以这个步长为准

set global auto_increment_increment=2;

二、索引

指不能重复的列（可以为空）

作用：约束，加速查找

1、唯一索引

某列的内容不能重复

create unique index 索引名称 on 表名(列名)

2、联合索引

某两列或以上的内容不能完全一样

create unique index 索引名称 on 表名(列名,列名)

删除索引：drop unique index 索引名称 on 表名

三、外键的变种

1、一对多

普通用法，例子如第一节中 员工的部门名称用数字代替，只需再引入一个部门表作为外键即可

2、一对一

例如每个用户都有自己的博客，需要构建用户表，博客表，并加入唯一索引

create table userinfol(
    -> 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 usernfol(id)
    -> )engine=innodb default charset=utf8;

3、多对多

例如用户和主机，某些用户可以使用某几个主机，不存在一一对应，需构建用户表，主机表，用户主机关系表

 

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 (hostiq) REFERENCES host(id)
)engine=innodb default charset=utf8;

四、增删改查的补充

1、增

增多条数据

insert into t1(name,age) values('alex',12),('root',18);

另一个表的部分数据插入

insert into t1(name,age) select name,age from t2;

2、删

加要求

delete from t1 where id !=5 and id>2;

3、改

加要求

update t1 set name='alex' where id >2 and name='xx'

4、查

一般形式:

select * from t1;#查全部

select id,name from t1;#查特定列

select id,name from t1 where id>10 or name='xxx'; #查特定要求

select id,name as cname from  t1 where id>10 or name='xxx'; #取别名

select id,name,1 from t1;#查询的同时增加全为1的一列

其他：

条件

select * from t1 where id>1 and name !='alex' and num=12;

select * from t1 where id bewteen 5 and 15;

select * from t1 where id in(1,5,12);

select * from t1 where id not in(1,5,12);

select * from t1 where id in (select nid from t2);

通配符

select * from t1 where name like 'a%';

select * from t1 where name like 'a_';

注：%表示任意的长度和内容，_表示一个长度，任意内容

去重（消除查询结果的重复值）

select distinct 列名 from 表名；

限制（分页）

select * from t1 limit 5; #前5行

select * from t1 limit 2，5；#从第2行开始的5行

select * from t1 limit 5 offset 2; #从第2行开始的5行

排序

select * from t1 order by id asc； #按id从小到大排序

select * from t1 order by id desc； #按id从大到小排序

select * from t1 order by id desc，name asc；#按id从大到小排序，如果相同则按name从小到大排序

分组

聚和函数：max、min、count（计数）、sum、avg（求平均数）

select num from t1 group by num;

select num,id from t1 group by num,id;

select num,id from t1 where id>10 group by num,id order id desc;

select num,id,count(*),sum(score),max(score),min(score) from t1 group by num,id;

如果对于聚合函数结果进行二次筛选时？必须使用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 * from t1,t2 where t1.id=t2.nid;

left join#左边全部显示

right join#右边全部显示

innor join#有null值的整行隐藏

例：select * from t1 left join t2 on t1.id=t2.nid;