mysql基本增删改查

## 数据库更改编码utf8方式

ALTER DATABASE 数据库 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
## 表更改编码utf8方式

ALTER TABLE tb_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

## 查询 ## 基本查询 => 
公式：select * from 表名  
select * from student; 

## where条件查询 => 

公式：select * from 表名 where sid = ? 
select * from student where sid = 1; 
select * from student where gender = '男' 
select * from student where gender = '女' 
select * from student where clazzid = 3; 
## 模糊查询 => 
select * from where susername like '% 锦 %' 
select * from student where susername like '%锦%' 
select * from student where susername like '%文%' 

##增 =>
公式1：insert into 表名 (指定字段1，指定字段2，.....) values (指定字段1的值，指定字段2的值.....);

或者
公式2：insert into 表名 values(null,字段.....); 
//注意: 和数据库的表一一对应 
insert into student values(null,'文渊','1234567','男',3)； 
insert into student values(null,'文雯','123456','女',3); 

##删 => 
公式：delete from 表名 where id = ? //删除一般都是根据ID删除 
delete from student where sid = 10; 
delete from student where sid = 14; 
批量删除

公式：delete from 表名 where id in (?,?,?,?.....) 
delete from student where id in (1,4,9,19); 

##修 => 
公式：update 表名 set 字段1 = 值1,字段2 = 值2 ... where id = ? 
update student set susername = '锦jin',gender = '妖',clazzid = 1 where sid = 5; 
update student set susername = '陈锦',gender = '男' where sid = 6; 
##排序 => 
公式：select * from 表名 order by 字段 desc/asc //desc:倒叙 asc:正序 
select * from student order by clazzid asc 
select * from student order by clazzid desc 

##多表联合查询 => 将两张表里面的数据综合起来查询 
公式：select * from 表1,表2 where 表1.公共字段 = 表2.公共字段 and 字段 = ? 
select * from student,clazz where student.clazzid = clazz.cid and student.susername = '张三'

##Limit的作用是获取一表前几条或中间某几行数据，注意只在MySQL中起作用

select * from 表名 LIMIT 10;   //检索前10行数据，显示1-10条数据,默认从0开始；
select * from 表名 limit 5,10;//检索从第6行开始向前加10条数据，共显示id为6,7....15；

##查看当前是否为自动提交

show variables like 'autocommit'

##设置当前为手动动提交

set autocommit = FALSE