mysql基本操作系列之（一）DML增删改查

一、数据库管理和存储引擎
MySQL是一个关系型数据库管理系统,存储引擎是数据库管理系统用来从数据库创建、读取和更新数据的软件模块。
常用的存储引擎：
1）INNODB
存储格式：.frm(表结构),
数据和索引存储在表空间里，
支持事务和回滚，读写效率低，占用空间大
2）MYISAM
存储格式: .frm（表结构）
.myd- 数据.myi-索引，
占用空间小，读写效率高， 不支持事务
3）MEMORY
存储格式：.frm（表结构）
数据存储在内存 ，效率高 ，异常无法恢复
二、基本数据库操作
1、修改表的相关操作
1》修改表名
alter table users rename [to|as] newname
–把表testuser改成testuser1
alter table testuser rename to testuser1;
–把表testuser1重新修改成testuser
rename testuser to testuser ;

2》修改数据表的存储引擎
alter table testuser engine=MYISAM;
–但是原则上不采取这种方式修改,一般都是把原表的数据导出,之后修改再重新导入。
或者是创建一个指定存储引擎的表，把之前的数据导入到新建的表中
create table testusernew like testuser;
alter table testusernew engine=MYISAM;
insert into testusernew select * from testuser;
3》修改自增长的值
alter table users AUTO_INCREMENT=100;

2、添加记录

insert [into] tablename [字段…] values [值…]

insert into user (id,name,age) values(1,‘A’,12);
insert into user (id,name,age) values(NULL,‘b’,12);
insert into user (id,name,age) values(DEFAULT,‘b’,12);
insert into user values(‘C1’,12),(‘C2’,34);

通过set 方式插入数据
insert into user set name=‘C4’,age=30;

3、更新记录
update 表名 字段名=[值，DEFAULT…] WHERE [条件]

update users name=‘lily’ where id=‘1’;

4、删除记录
创建一个users表
create table users(
id INT unsigned auto_increment key,
name VARCHAR(20),
age int unsigned
)
alter users auto_increment=10; --设置自增长从10开始
insert users values(NULL,‘A’,31)
delete from users; --这个是删除表的记录
alter users auto_increment=1; --设置自增长从10开始
insert users values(NULL,‘A’,31)

注意：delete只是删除表记录，原表的自增长的不会收影响；
truncate的话是数据删除并把自增长设置从1开始。
truncate table users;–彻底删除表

5、查询记录
5.1查询条件
in、in not in
between and not between and
and or
like not like

5.2分组查询
group_concat()配合group by 使用对分组之后的字段进行集合展示
count(*) 和count(字段名称) 在于：count(字段)的时候对NULL不做计数统计。

5.3特殊子查询
除了where 后面连接比较运算符之外还有any,some,all等用法。

1》>或者>= ANY|SOME（子查询） 是大于最小值
select from users where id>ANY(SELECT id FROM department); --找出id大于部门表id最小值的人。

2》>或者>= ALL（子查询） 是大于最大值
select from users where id>ALL(SELECT id FROM department); --找出id大于部门表id最大值的人。

3》<或者<= ANY|SOME（子查询） 是小于最大值
select from users where deptid<ANY(SELECT deptid FROM department); --找出id大于部门表id最小值的人。

4》<或者<= ALL（子查询） 是大于最小值
select from users where deptid<ALL(SELECT deptid FROM department); --找出id大于部门表id最大值的人。

5》 = ANY(子查询) 相当于是in的效果

等于的时候只能写ANY,SOME（子查询）
不等于的时候只能写ALL(子查询)

5.4 多表联合查询
1》内外连接
内连接： (inner| cross)join
外连接：left outer join
right outer join
2》联合查询： union | union all
3》多表更新和删除：
–根据关联关系，把子表中的多条记录addr值转换成父表的字段pid值。
update users u join provinces p on u.pid=p.id set u.addr=p.id;
–把符合条件的users表的记录删除。
delete u from users u join provinces p on u.pid=p.id;

5.5外键:
目前的话只是在INNODB中使用，MYIASM存储引擎支持外键的创建，但是是无效的。
目的是为了保证数据一致性和完整性，在2个不同的表之间创建关联关系。
语法：
constraint 外键名称 foreign key(外键字段) references 父表(父表主键字段)

5.5.1、创建表的时候增加外键
create table provinces(
id int unsigned auto_increment primary key,
pname varchar(20) comment ‘省份’
);

create table user1(
id int unsigned auto_increment key,
name varchar(20) unique,
pid int unsigned,
constraint user1_fk_province foreign key(pid) references provinces(id)
);

5.5.2、在原来表的基础上alter操作
create table user1(
id int unsigned auto_increment key,
name varchar(20) unique,
pid int unsigned
)

alter table user1 add constraint user1_fk_province foreign key(pid) references provinces(id)

结论：
在子表user1插入数据的时候收到的父表provinces的id的外键的限制，不能插入出了provinces里面的id之外的pid的数据到子表user1。

5.5.3、在父表provinces进行更新和删除操作的时候，子表对应的一些处理。
实际开发的时候不会进行物理上的外键更新删除操作，而是借助于外键的思想，在删除父表的记录的前提是要把子表的对应的记录给删除。
父表和子表之间存在外键之间的约束。

1）. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
–删除父表provinces的记录的同时删除子表user1的相关记录

alter table user1 add constraint user1_fk_province foreign key(pid) references provinces(id) on delete cascade;

2）. set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null
alter table user1 add constraint user1_fk_province foreign key(pid) references provinces(id) on delete set null;

3). No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
alter table user1 add constraint user1_fk_province foreign key(pid) references provinces(id) on delete no action;

4). Restrict方式
同no action, 都是立即检查外键约束
alter table user1 add constraint user1_fk_province foreign key(pid) references provinces(id) on delete restrict;