MySQL增删改查

一：自增列起始值和步长
1. 自增列起始值查看和修改
show create table t1 \G;
alter table t1 set auto_increment=2;
2. 步长
会话
全局

二：索引和外键

1. 唯一索引

create table t1(
id in auto_increment primary key,
num int,
unique num_q (num)
)

2. 联合唯一

create table t1(
id in auto_increment primary key,
num1 int,
num2 int,
unique num_q (num1,num2)
)

唯一、联合唯一索引特点：不能重复，但是能为空

3. 一对一(外键+唯一索引)

    create table detail(id int auto_increment primary key,
                        iphone char(11),
                        address varchar(40),
                        price decimal(10,4)
                        )engine=innodb default charset=utf8;

    create table user(id int auto_increment primary key,
                      name varchar(30),
                      pwd varchar(30),
                      detail_id int,
                      unique user_detail_OTO (detail_id),
                      constraint fk_user_detail_OTO foreign key (detail_id) references detail(id)
                      )engine=innodb default charset=utf8;
                      

4. 多对多(联合索引+两个外键)

    create table userinfo(id int auto_increment primary key,
                      name varchar(30),
                      pwd varchar(30)
                      )engine=innodb default charset=utf8;

    create table host(id int auto_increment primary key,
                      ip varchar(30),
                      hostname varchar(30)
                      )engine=innodb default charset=utf8;
                      
    create table userinfo_host(id int auto_increment primary key,
                      userinfoid int not null,
                      hostsid int not null,
                      unique host_userinfo_q (userinfoid,hostsid),
                      constraint fk_us_userinfo foreign key (userinfoid) references userinfo(id),
                      constraint fk_us_host foreign key (hostsid) references host(id)
                      )engine=innodb default charset=utf8;
                      

三：增删改查

1. 增加
insert into t1(name, age) values('joe',19);
insert into t1(name, age) values('joe',19),('jojo',19),('ben',19); # 插入多条
insert into t1(name,age) select name,age from t2; # 复制表
2. 删除（where条件后面加and,or）
delete from t1 where id > 5 and name='joe';
delete from t1 where id != 5 or name='joe';
3. 改（where条件后面加and,or）
update t1 set name='joe',age=19 where id>9 and name="ben";
4. 查
（1）where条件后面加and，or
（2）in, not in , between and
（3）通配符 （like）
select * from t1 where name like 'a%' #%取多位任意字符串
select * from t1 where name like '_a%' # _取一位任意字符串
（4）limit
select *from t1 limit 10;
select * from t1 limit 10,20; #顾头不顾尾，从0开始
（5）order by (asc, desc) 排序
select * from t1 order by id asc; # 从小到大
select * from t1 order by id desc limit 10;

四：分组
1.group by
select department_id,count(id),max(id) from t1 group by department_id;
select department_id,avg(price) from t1 group by department_id;
count,max,min,avg,sum

2.having(分组之后，在进行条件过滤)
# 通过department_id进行分组，计算组内的平均工资，大于100才显示
select department_id,avg(price) from t1 group by department_id having avg(price) > 100;

五：连表查询
1. 基础连表
select * from user,detail where user.detail_id=detail.id

2. left join,right join,inner join(替换where)
（1）left join # 左边全部显示
select * from user left join detail on user.detail_id=detail.id
（2）right join # 右边全部显示,如果右边和左边有未匹配的，显示Null
（3）inner join # 将null 的行隐藏

3. 多表查询
select * from score
left join student on score.student_id=student.id,
inner join course on score.course_id=course.id,
left join class on student.class_id=class.id,
left join teacher on course.teacher_id=teacher.id;
六： 子查询 ， case when ， union