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;

# 增 => 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;
# 修改 => 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 表名
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 '%文%'
## 排序 select * from 表名 order by 字段 desc/asc  //desc:倒叙 asc:正序
select * from student order by clazzid asc
select * from student order by clazzid desc
## 多表联合查询 => 将两张表里面的数据综合起来查询

select * from clazz;
## 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