MySQL常见的增删改查命令，索引，事务的基本概念_mysql 事务和增删改查-优快云博客

基本知识：

基本知识：

注释：

单行注释：ctrl+/
多行注释：选中+ctrl+/

常用数据类型：

整数：int（可正可负）
int unsigned：只可以正数

小整数：tinyint（可正可负）范围比整数小（-128，127）
tinyint unsigned：只可以正数

小数：decimal，如decimal（5,2）共存5位数，小数占两位，不超过两位

字符串：varchar，如varchar（3）最多存3个字符

日期时间：datetime

表，字段，记录的概念：

表：table
字段：行field
记录：列record

别名：

可以给字段或表取别名，在名字后加上as，as也可以省略

字段的约束：

主键：primary key，主键表示的字段里面的值是不能重复的，auto_increment：自增长
非空：not null，值不能为空
唯一： unique，此字段的值不能重复
默认值：default 值，表示当不去写该值，他就会有一个默认值

创建带约束的字段语法：
create table 表名(
字段名数据类型约束，
字段名数据类型约束，
);

举例：

# 约束：主键，非空，唯一，默认值

# 主键
# 创建表D，id是主键，还有其他两个字段，id不能属重复的值，为了避免重复报错，选择自增长
# auto_increment表示自增长从1开始，在插入数据的时候就不要，自己输入id值
# 如果手动加一个值6，就会在6后面自增长
# 占位符：0或null表示，如果我不想指定字段，但在前面写字段名太麻烦，可以用字段符来代替主键自增长的值
create table d(
    id int signed primary key auto_increment,
    name varchar(10),
    age int
);
insert into d(name, age) values('张三',20);
insert into d(name, age) values('李四',30);
insert into d(name, age) values('王五',40);
insert into d values (0,'曹曹',70);
insert into d values (null,'曹值',80);
# delete删除所有数据，再插入数据时，他会从之前id的值往后增加，而不会从1开始增加
# 而truncate删除的数据，id值自增能够从1开始自增
delete from d;
insert into d values (0,'曹曹',70);
select *from d;

# 非空
# 创建表e，名字不能为空
create table e(
id int signed ,
name varchar(10) not null ,
age int
);
insert into e values(1,'张三',20);
# insert into e(id,age) values(1,30);会报错执行不了
select *from e;

# 唯一
# 创建表f，id为int型，name为varchar型，长度为10，unique唯一，age为int型
# name的值不允许重复的，主键就具备了unique的特性
create table f(
id int,
name varchar(10) unique,
age int
);
insert into f values(0,'张三',30);
select*from f;

# 默认值
# 创建表g，id为int，name为varchar，长度为10，age为int，默认值为30
# 插入数据时指定那个值，默认值就会失效，如果不指定，就是默认值
create table g(
id int,
name varchar(10),
age int default 30
);
insert into g(id, name) values(1,'张三');
select*from g;

创建表格：

create table 名字（
字段名 数据类型
字段名 数据类型
）；
多条字段中间用逗号隔开

举例：

create table a(
name varchar(10)
);
create table b(
name varchar(10),
height decimal(5,2)
);
create table c(
id int,
name varchar(10),
age tinyint unsigned
);
# 如果表不存在了，就创建
create table if not exists b(
    name varchar(10)
);

删除表格：

drop table 表名;
#如果表a存在就删除，否则就不干
drop table if exists a;

举例：

# 删除表
drop table a;
# 如果表a存在就删除，否则就不干
drop table if exists a;

插入记录：

insert into 表名  values（.......）；
单独设置某个字段的值
insert into 表名  （字段名）value（值）;

举例：

# 插入数据
# 往表c插入数据
insert into c values (0,'张三',30);
# 单独插入某个字段的值
insert into c (name) value ('曹操');
insert into c(id,age) values(3,20);
# 一条语句进行多条数据插入，也可以和上面单独插入相结合
insert into c values(4,'李四',30),(5,'王五',60),(6,'孙六',40);

查询记录：

*表示所有字段，也可以改成相应字段
select *from 表名；
select id from 表名;

举例：

# 查询数据
select*from c;
select name,id from c;

更改记录：

where条件如果没有代表改变这一字段所有的值
update 表名 set 字段1=值，字段2=值.....where条件；

举例：

# 改变数据
update c set age=50;
# 把表c中id为3的数据name改为狄仁杰，age改为20
update c set name='狄仁杰',age=20 where id=3;
# 修改name为曹操的记录为李白
update c set name='曹操'where name='李白';
# 让id大于3的长一岁
update c set age=age+1 where id>3;

删除记录：

where条件如果没有代表删除这一字段所有的值
delete from 表名 where 条件;
truncate没有条件直接全部删除
truncate table 表名
truncate和delete区别：
truncate速度比delete块
删除部分数据用delete，即使删除了，自增长字段是会从上一次数据的最大值开始增长
想保留表而删除所有数据，自增长字段从1开始，用truncate

举例：

delete from c where id=6;
truncate table c;

重点：

查询：

前置代码：

# 判断是否有这个表，有就删除，没有就创建
drop table if exists students;
/*创建学生表students*/
create table students(
studentNO varchar(10) primary key,/*学号*/
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint,
class varchar(10),
card varchar(18)
);
insert into students values
('001','王昭君','女','北京','30','1班','110214567897654367'),
('002','诸葛亮','男','上海','29','2班','310214567897654367'),
('003','张飞','男','南京','30','3班','320214567897654367'),
('004','白起','男','安徽','35','4班','340214567897654367'),
('005','大乔','女','天津','28','3班','120214567897654367'),
('006','孙尚香','女','河北','25','1班','130214567897654367'),
('007','百里玄策','男','山西','39','2班','140214567897654367'),
('008','小乔','女','河南','25','3班',null),
('009','百里守约','男','湖南','31','1班',''),
('010','妲己','女','广东','24','2班','440214567897654367'),
('011','李白','男','北京','30','3班','110214567897654367'),
('012','孙膑','男','新疆','36','4班','650214567897654367');

普通查询：

# 查询
# 显示所有字段
select*from students;
select name,sex from students;
# 查看的时候字段名是英文，我们可以给他起别名，as可以省略
select name as 名称,sex as 性别 from students;
# 给表取别名，结果上看不出差距，后面会用到
select*from students as stu;
# 查询后去掉重复项，distinct 字段名
select distinct sex from students;
# 只过滤查询后性别和班级都相同的结果
select distinct sex,class from students;

条件查询（where语句）：

比较查询：

# where语句用法：数据分组前对数据进行筛选
# where后支持多种运算符
# 比较运算
# =,>,<,!=
# 条件查询 where 子句，where可以用在select，delete，update后面
select *from students where studentNO='001';
# 查询age为30的name和class
select name,class from students where age=30;

逻辑查询：

# 逻辑运算
# and，or，not：与或非
# 查询年龄小于30且性别为女的记录
select *from students where age<30 and sex='女';
# 查询班级为1班或性别为女的记录
select *from students where class='1班' or sex='女';
# 查询老家非天津的同学记录
select *from students where not hometown='天津';
# 或
select *from students where hometown!='天津';

模糊查询：

# 模糊查询
# like
# %表示多个任意字符，可以放在名字的任意位置
# _表示一个任意字符
# 查询name以孙字开头的学生记录
select*from students where name like'孙%';
# 查询name以孙字开头的学生记录，后面只有一个字符
select*from students  where name like '孙_';
# 查询name中有白字的学生记录
select*from students where name like'%白%';

范围查询：

# 范围查询
# in表示在非连续范围
# between....and....表示在连续范围内
# 查询hometown范围在上海，北京，南京的字段记录
select *from students where hometown in('上海','北京','南京');
# 查询年龄在20-30岁的学生记录
select*from students where age between 20 and 30;

空查询：

# 空判断（is null）
# null代表什么都没有，''代表长度为0的字符串
# 查询card值为null的记录
select*from students where card is null;

排序：

# 排序
# 为了方便查看数据，可以对数据进行排序
# asc表示升序（默认，不写默认从小到大），desc表示降序
# order by 字段 1 asc/desc，字段 2 acs/desc.......
# 按age大小从小到大排序
# 当select中出现where和order by，先写where，再写order by语句
select *from students order by age asc;
# 先按照age从大到小排序，如果相同在按照学号从小到大排序
select *from students order by age desc,studentNO;

聚合函数：

# 聚合函数
# 聚合函数不能用到where语句中，只能在前面使用
# count（）计算总记录数
select count(*)from students;
# max（）计算字段最大值
select max(age)from students;
# min（）计算字段最小值
select min(age)from students;
# sum（）求和
select sum(age)from students;
# avg（）求平均值
# 如果计算平均值时有null，null不记作分母
select avg(age)from students;

分组：

# 分组
# 比如我们分别要求男同学的记录数和女同学的记录数
# 正常的（太麻烦，代码量太大）
select count(*)from students where sex='男';
select count(*)from students where sex='女';
# 分组
# group by 字段名即根据什么分组，当然前面也可以加where条件
select count(*)from students group by sex;
# 例题：统计各个班级学生总数，平均年龄，最大年龄，最小年龄，不统计3班，结果按班级名称从打到小排序
select class,count(*),avg(age),max(age),min(age) from students where not class ='3班' group by class order by class desc;
#having 字段名：数据分组后对数据进行筛选
# 聚合函数不能用在where筛选中，但可以用在having筛选中
# 用where筛选男生总数
select count(*) from students where sex='男';
# 用having筛选男生总数
select count(*) from students group by sex having sex='男';
# 求班级人数大于3人的班级，这里只能用having，因为人数要聚合函数才能求，where中不能出现聚合函数
select class,count(*) from students group by class having count(*)>3;
# where和having的区别
# where是对from后面的表进行筛选，属于对原始数据筛选;having是对group by后面的数据进行修改
# where后面不能用聚合函数，having后面可以跟聚合函数

分页：

# 数据分页显示
# 数据过多时，在一页中不方便查看，所以分页显示
# limit start,count可以获取指定行数start表示第一行，可以省略，count表示到第几行
# 0代表第一行
# 查询前三行学生记录
select * from students limit 3;
select * from students limit 0,3;
# 查询从第四行开始的三条记录
select *from students limit 3,3;
# 查询年龄最大的学生记录(妙)，这里用不了聚合函数
select * from students order by age desc limit 1;
# 分页显示
# 每页显示m条数据，查询第n页的数据
#公式： select * from students limit (n-1)*m,m;
# 每页3条数据，查询第3页的数据
select * from students limit 6,3;

多表查询：

前置代码：

# 判断是否有这个表，有就删除，没有就创建
drop table if exists students;
/*创建学生表students*/
create table students(
studentNO varchar(10) primary key,/*学号*/
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint,
class varchar(10),
card varchar(18)
);
insert into students values
('001','王昭君','女','北京','30','1班','110214567897654367'),
('002','诸葛亮','男','上海','29','2班','310214567897654367'),
('003','张飞','男','南京','30','3班','320214567897654367'),
('004','白起','男','安徽','35','4班','340214567897654367'),
('005','大乔','女','天津','28','3班','120214567897654367'),
('006','孙尚香','女','河北','25','1班','130214567897654367'),
('007','百里玄策','男','山西','39','2班','140214567897654367'),
('008','小乔','女','河南','25','3班',null),
('009','百里守约','男','湖南','31','1班',''),
('010','妲己','女','广东','24','2班','440214567897654367'),
('011','李白','男','北京','30','3班','110214567897654367'),
('012','孙膑','男','新疆','36','4班','650214567897654367');
/*  课程表 */
create table courses (
courseNo int unsigned primary key auto_increment,
coursename varchar(10)
);
insert into courses (coursename) values ('数据库'), ('html'), ('linux'), ('系统测试'), ('单元测试'), ('测试过程'), ('python');
/*  成绩表 */
create table scores (
id int unsigned primary key auto_increment,
courseNo int unsigned,
studentNo varchar(10),
score tinyint
);
insert into scores (courseNo, studentNo, score) values
(1, '001', 90),
(1, '002', 75),
(2, '002', 98),
(3, '001', 86),
(3, '003', 80),
(4, '004', 79),
(5, '005', 96),
(6, '006', 80);
# 当地域表代表省
# id代表省id
# name代表省名
# pid代表无，因为省没有上级

# 当地域表代表市
# id代表市id
# name代表市2名
# pid代表市所属省id

# 当地域表代表区
# id代表区id
# name代表区名
# pid代表区所属市id
/*  地域表 */
create table areas (
id int primary key,
name varchar(20),
pid int
);
insert into areas values
('130000', '河北省', null),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('440000', '广东省', null),
('440300', '深圳市', '440000'),
('440100', '广州市', '440000'),
('440500', '汕头市', '440000'),
('440600', '佛山市', '440000'),
('440200', '韶光市', '440000');

连接查询：

# 连接查询，表的别名主要在这里起作用，当查询*时会自动给相同的字段起上别名来区分
# 当查询数据源于多张表时，要将多张表连成一个大的数据集，在查询合适的结果
# 内连接：只找表与表之间相同的地方，进行关联，可以和前面的聚合函数，排序等一起使用
# select * from 表1 inner join 表2 on 表1.字段=表2.字段:
select * from courses;
select * from scores;
select coursename 课程名,score 分数 from courses inner join scores on courses.courseNo=scores.courseNo;
# 隐式内连接，和上面一样，另一种语法，古老的语法
select * from scores,courses where scores.courseNo=courses.courseNo;
# 表的别名，简化代码
select coursename 课程名,score 分数 from courses co inner join scores sc on co.courseNo=sc.courseNo;
# 查询王昭君的信息，要求只显示姓名，课程号，成绩
select name,courseNo,score from students st inner join scores sc on st.studentNO=sc.studentNo where name='王昭君';
# 多表查询不止两张表，查询学生信息，成绩及对应的课程名称
select * from students st inner join scores sc on st.studentNO=sc.studentNo inner join courses co on sc.courseNo=co.courseNo;
# 左连接：left join在内连接的基础上，即先找表与表之间相同的地方，再把左表中特有的找出来
# 查询所有学生的信息和成绩，包括没有成绩的学生
select * from students st left join scores sc on st.studentNO=sc.studentNo;
# 右连接：right join在内连接的基础上，即先找表与表之间相同的地方，再把右表中特有的找出来
# 查询所有课程信息，包括没有成绩的课程
select * from scores sc right join courses co on sc.courseNo=co.courseNo;
# 多表查询，想查询同名字段时，可以在查的字段名前加上具体表名.字段名
select st.studentNo from students st left join scores sc on st.studentNO=sc.studentNo;

自关联：

# 自关联
# 表中的某一字段关注了这个表中另一字段，但是他们业务逻辑不同，
# 就比如地域表中的pid既可表示市所属省id也可表示区所属市id，
# 可以放在一张表中实现自关联，就会不会出现太多的表，下面表中又有省，又有市
# ('130000', '河北省', null),
# ('130100', '石家庄市', '130000'),
# ('130400', '邯郸市', '130000'),
# ('130600', '保定市', '130000'),
# ('130700', '张家口市', '130000'),
# ('130800', '承德市', '130000'),
# ('440000', '广东省', null),
# ('440300', '深圳市', '440000'),
# ('440100', '广州市', '440000'),
# ('440500', '汕头市', '440000'),
# ('440600', '佛山市', '440000'),
# ('440200', '韶光市', '440000');
# 设涉及自关联
# 查询广东省的所有城市，先用到别名，然后让id=pid
select * from areas a1 inner join areas a2 on a1.id=a2.pid where a1.name='广东省';

子查询：

# 子查询，是嵌套到主查询里面的
# 查大于平均年龄的学生记录，用括号括起来，子查询可以独立运行
# 子查询(select avg(age)from students)返回值一行一列就叫标量子查询
select * from students where age>(select avg(age)from students);
# 查询30岁学生的成绩
# 用内连接来实现
select score from students st inner join scores sc on st.studentNO=sc.studentNo where age=30;
# 用子查询来实现，这里要注意因为是多个值，所以用in而不用等号，范围查询
# 列子查询，一列多行
select score from scores where studentNo in(select studentNO from students where age=30);

# 用子查询查询所有女生的信息和成绩
# 表级子查询，先查询出一张只有女生的表，然后用内连接关联
select*from(select * from students where sex='女') se inner join scores sc on se.studentNO=sc.studentNo;

其他：

MySQL常用内置函数：

# MySQL常用内置函数，和聚合函数不同，聚合函数属于SQL语句中的，
# 几乎在所有的数据库中是统一的，而常用内置函数是MySQL中特有的
# 字符串拼接函数concat()，通过select把他查询出来
# 把12,34，ab拼接成一个字符串‘1234ab’
select concat(12,34,'ab');
# 子算字符串中字符的个数，一个汉字返回3
select length('abc');
# 查询表students表中name长度等于9的学生信息，可和where连用作为条件
select * from students where length(name)=9;
# 截取字符串
# 左截 left(字符串，截取个数)
# 截取左边3个字符串，这个英文和中文不区分
select left('我和你abc',3);
# 右截 right
select right('我和你abc',3);
# 指定截取字符串 substring(字符串，截取起始位置，截取个数)
select substring('我和你abc',2,3);
# 截取students表当中所有学生的姓
select left(name,1) from students;
# 去除空格
# 去除字符串左边的空格，l代表左边，trim代表去除
select ltrim('         n');
# 去除字符串右边的空格 rtrim
select ltrim('n          ');
select concat('abc        ','我和你');
select concat (rtrim('abc      '),'我和你');
# 去除两边空格 trim
select trim('       abc       ');
# 数字函数
# 四舍五入函数 round(数字，小数位数)，如果不写小数位数，默认整数即0位
select round(1.653);
select round(1.653,2);
# 查询学生的平均年龄并四舍五入，保留两位
select round(avg(age),2) from students;
# 随机数rand
select rand();
# 从学生表中随机抽取一个学生信息，放在order by后面表示随机排序，随机抽取一个
select * from students order by rand() limit 1;
# 日期和时间相关函数 current_date()
select current_date();
# 时间
select current_time();
# 如果都要返回
select now();
# 数据库中实时更新时间，电商项目中，要插入数据的时间，就会用到now函数
create table a(
id int,
indate datetime
);
insert into a values (1,now());
select *from a;

存储过程：

# 创建存储过程stu()，用于查询学生信息,写存储过程调用能变得简单
# sql语句只能写在begin和end中间
create procedure stu()
begin
select * from students;
end;
# 执行
call stu();
# 删除存储过程 drop procedure 名字
drop procedure stu;
drop procedure if exists stu ;

视图：

# 创建视图,本来要查询男生的,改需求后就要查询女生,
# 如果不用视图就会要去原查询语句中修改很麻烦,视图就相当于对查询语句进行封装
# 不能对原表进行修改操作,他是只读的,只能查询
# 语法 create view 视图名称 as select 语句;
# 创建一个视图查询所有男生信息,相当于是一张筛选过的表
create view stu_nan as select * from students where sex='男';
# 查询男同学的成绩
select score from stu_nan inner join scores s on stu_nan.studentNO = s.studentNo;
# 删除视图
drop view stu_nan;
drop view if exists stu_nan;

事务：

# 事务：数据的更改需要事务
# 事务广泛应用于订单系统，银行系统等
# A给B转账500块，需要做这几件事
# 检查A的账户余额是否>500，不该数据
# A账户中扣除500，updateA的数据
# B账户增加500，updateB的数据
# 假设A转账500给B，A中update已经执行，此时网络故障，手机故障，那A就损失500，B也没有收到转账的500，这就会有风险，所以引入了事务
# 这里面有多条修改数据库的语句，事务要求他们要么就一起失败，要么就一起生效
# 事务是维护数据一致性的单位，这些操作要么成功，要么失败
# 开启事务begin，执行所有的删除或修改操作，变更不会立马生效，会写到缓存中
# 回滚事务rollback，放弃所有修改
# 提交事务commit，将修改的数据写入实际的表中
# 开启事务，删除students表中studentNo中为001的记录，同时删除scores表中studentNo为001的记录
# 如果你没写begin一旦更改了就会马上失效
begin;
# sql语句1
delete from students where studentNO='001';
# sql语句2
delete from scores where studentNo='001';
# 事务回滚
rollback;
# 在这里我们开启事务，再执行sql语句1，这时候把mysql关了，理解为系统故障，再打开执行查询会发现之前删除的数据还在，
# 这表明事务开启之后，你后面没做rollback或commit操作，他就会默认执行rollback，这就是事务回滚
# 只有运行了commit，提交更改后才会执行生效
select * from students;
select * from scores;

事务四大特性：

原子性: 指整个事务中的所有操作要么全部成功, 要么全部失败回滚, 不允许出现部分成功或部分失败的情况.
一致性: 指事务开始之前和结束之后, 数据库的状态必须是一致的, 即满足所有的约束条件和完整性规则.
隔离性: 指事务的执行不受其他事务的干扰, 一个事务执行过程中的中间状态对其他事务是不可见的.
持久性: 指事务一旦提交, 对数据库的修改将永久保存到数据库中, 即使出现宕机等故障, 也不会丢失数据.

索引：

# 索引：加快select查询的速度
# 当表数据量很大时，查找数据就会变得很慢，就可以给表加一个类似书籍中目录，从而加快数据的查询效率，这就是索引
# 语法
# create index 索引名 on 表名(字段名称（长度）);
# 如果字段为字符串，要指定长度，如果不是字符串类型，就不用填
# 为表students的age字段创建索引名为age_index
create index age_index on students(age);
# 为表students的name字段创建索引name_index
create index name_index on students(name(10));
# 索引不需要调用，select时，数据库系统会自动查找是否有索引
# 查看索引
show index from students;
# 查看索引时会发现有三个索引，但是我们只建立了两个，
# 这表明创建主键时，系统就会给主键自动建立一个索引，所以查询主键效率就会很高
# 删除索引
# 语法：drop index 索引名 on 表名:
drop index age_index on students;
# 索引的优点：查询效率大大提高
# 索引的缺点：进行删除，更改，增加等操作时不仅更改数据，还会更改索引，影响速度
# 所以在大量插入或更改数据的时候可以先删除索引，然后更改，最后在建立索引，加快效率
# 主键很重要
# 首先我们建立一个没有主键的表
create table b(
name varchar(10),
age int
);
# 插入一条数据，在插入一条一样的
insert into b values ('张三',30);
# 查询表中记录
select * from b;
# 此时，表中有两条一样的数据，我想根据名字去更改张三的年龄，改起来就会有问题
# 所以我们需要建立主键，有利于表的更改和维护