MySQL完整版详解

本文主要是介绍MySQL完整版详解，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、数据库的操作

1.创建数据库

若在可视化软件上创建数据库，参考如下图

如果要创建的数据库不存在，则创建成功

create database if not exists westos;

2.删除数据库

drop database if exists westos;

3.使用数据库

use tmalldemodb;
//tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``

4.查看数据库

show databases;

5.清空当前命令行界面命令

clear;

6.创建表

• auto_increment：自增

• 字符串使用单引号括起来

• 所有语句后面加逗号，英文的，最后一个不用加

• primary key主键，一般一个表只有一个唯一的主键，且必须要有

• engine=innodb：数据库表的引擎

mysql> create table if not exists student(
    -> id int(4) not null auto_increment comment '学号',
    -> name varchar(30) not null default '匿名' comment '姓名',
    -> pwd varchar(20) not null default '123456' comment '密码',
    -> sex varchar(2) not null default '女' comment '性别',
    -> birthday datetime default null comment '出生日期',
    -> address varchar(100) default null comment '家庭地址',
    -> email varchar(50) default null  comment '邮箱',
    -> primary key(id)
    -> )engine=innodb default charset=utf8;

创键成功之后的图

7.常用命令

(1)查看创建数据库的语句

show create database 数据库名;

运行结果图

(2)查看表的创建语句

show create table 表名;

(3)显示表的结构

desc 表名；

8.数据表的类型

(1)数据库的引擎

innodb //默认使用
myisam//早些年使用的

• 事务：假设有两个SQL都执行，要么都成功，要么都失败，如果有一个成功一个失败，它就提交不上去

• 数据行锁定：假设有两条SQL去查同一个表，他会把这个表先锁住，剩下的数据再查的时候就要排队等待

• 外键约束：一张表连接到另一张表

• 全文索引：比如在百度上根据你输入的关键词(在数据库叫字段)区搜索你想要的结果

	myisam	innodb
事务支持	不支持	支持
数据行锁定	不支持 （支持表锁）	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为myisam的2倍

常规使用操作：

• myisam 节约空间，速度较快

• innodb安全性高，事务的处理，多表多用户操作

(2)在物理空间存在的位置

所有的数据库文件都存在data目录下 本质还是文件的存储！

9.修改和删除表的字段

(1)修改表名

alter table 旧表名 rename as 新表名;

alter table teacher rename as teacher1;

(2)增加表的字段

alter table 表名 add 字段名 列属性;

alter table teacher1 add age int(11);

(3)修改表的字段（重命名，修改约束）

①修改约束modify(不能重命名)： alter table 表名 modify 字段名 新的列属性;

alter table teacher1 modify age varchar(11);

②字段重命名（既可以给字段重命名，又可以修改约束）
alter table 表名 change 旧字段名 新字段名 列属性;

alter table teacher1 change age age1 int(11);

(4)删除表的字段

alter table 表名 drop 字段名;

alter table teacher1 drop age1;

(5)删除表

如果要删除的表存在，则删除 drop table if exists 表名;

drop table if exists teacher1;

所有的创建和删除操作都尽量加上判断，以免报错

二、列的数据类型详解

1.数值

• tinyint    十分小的数据  1个字节

• smallint   较小的数据 2个字节

• int           标准的整数 4个字节      常用

• bigint      较大的数据  8个字节

• float         浮点数        4个字节

• double     浮点数         8个字节

• decimal      字符串形式的浮点数

2.字符串

• char       字符串固定的大小       0-255

• varchar       可变字符串      0-65535      常用

• tinytext        微型文本       2^8-1

• text        保存大文本       2^16-1

3.时间和日期

• date       日期格式：YYYY-MM-DD

• time       时间格式：HH:mm:ss

• datetime       日期格式：YYYY-MM-DD      HH:mm:ss      常用

• timestamp      时间戳，从1970.1.1至现在的毫秒数     常用

• year      年份表示

4.null

• 没有值，未知

• 注意，不要使用NULL进行运算，结果为NULL

三、数据库的字段属性（重点）

1.unsigned

• 无符号的整数

• 不能声明为负数

2.zerofill

• 0填充的

• 假设你现在要写一个长度为10的int类型，但是你只写了个1，则他会用自动给你在1前面填充9个零

3.自增

• 通常理解为自增，自动在上一条记录的基础上+1（默认）

• 通常用来设计唯一的主键~index,必须是整数类型

• 可以自定义设置主键自增的起始值和步长

4.非空

• 假设设置为not null ，如果不给它赋值，就会报错！

• Null,如果不填写值，默认就是null！

5.默认

• 设置默认的值

• sex,默认值为男，如果不指定该列的值，则会有默认的值！

• 设置默认的值

四、MySQL数据管理

1.外键(了解即可)

删除有外键关系的表的时候，必须要先删除字表，才能删除父表

2.DML语言（全部背住）

• insert

• update

• delete

3.添加

insert into 表名(字段名1，字段名2，字段名3，....) value(值1，值2，值3，....) ==注意：一般写插入语句，我们一定要数据和字段一一对应！==

(1)插入一行数据

insert into grade(gradename) value('大三');

(2)插入多行数据

==注意：给一个字段添加多行值时，每个值都用括号括起来，且中间用逗号隔开。==

insert grade(gradename) value('大二'),('大一');

insert into student(name,pwd,sex)  values('张三','aaaa','男'),('李四','vvvv','女');

4.修改

(1)修改一条数据

格式：update 表名 set colnum_name = value where 条件 下面这行代码的意思是将student表中id=2的name值设置为TWQ

update student set name='TWQ' where id =2;

(2)修改多条数据

条件：where子句，运算符id等于某个值，大于某个值，或在某个区间内修改

操作符	含义	范围	结果
=	等于	5=6	false
<>或！=	不等于	5<>6	true
<	小于	5<6	true
>	大于	5>6	false
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
between a and b	在a到b这个闭包区间内	[a,b]	true
and	我和你 &&	5>1 and 1>2	false
or	我或你	5>1 or 1>2	true

5.删除

(1)delete命令

语法：delete from 表名 where 条件;

delete from student where id =1;

(2)truncate命令

作用：完全清空一个数据库表，表的结构和索引不会变

truncate table student;

(3)truncate和delete命令区别

• 相同点：都能删除数据，都不会删除表结构

• 不同：①truncate重新设置自增列，计数器会归零。②truncate不会影响事务
  使用delete删除表中所有数据，不会影响自增的值
  拓展（了解即可）关于delete删除的问题：删除后重启数据库

• 若该表使用的引擎为innodb，重启数据库后，自增列会从1开始（存在内存当中断电即失）

• 若采用的引擎是myisam 重启数据库后，会继续从上一个自增变量开始（存储在文件中，不会丢失）

delete from grade;

使用truncate删除表中所有数据，自增变量会归零

truncate table grade;

五、DQL查询数据（重点中的重点）

select语法

5.1指定查询字段

(1)查询某个表中全部的数据

如查询student表中所有的数据

select * from student;

(2)查询某个表中指定字段

①查询student表中name和pwd字段的值

select name,pwd from student;

②给字段或表名起别名(as)

select name as 姓名,pwd as 密码 from student;

(3)函数concat(a,b)

select concat('姓名:',name) as 新名字 from student;

(4)去重distinct

①查询有哪些同学参加了考试(可能有一个同学考了多个学科，造成的数据重复，需要去重)

select distinct Student_id from result;

(5)查询系统版本

select version();

(6)用来计算

select 100*3-1 as 计算结果;

(7)查看学生成绩+1分之后的结果

select Student_id,StudentResult+1 as 加一分后 from result;

5.2 where条件子句

作用：检索数据中符合条件的值 搜索的条件由一个或者多个表达式组成！结果为布尔值

(1)逻辑运算符

运算符	语法	描述
and	a and b	逻辑与
or	a or b	逻辑或
Not	not a	逻辑非

(2)where的运用

①查询23(学科编号)这个学科成绩在95到100之间的学生

//方式一：
select student_id  as 学生编号,studentresult as 分数 from result
where studentresult>=95 and studentresult<=100 and subject_id=23;

//方式二：
select student_id  as 学生编号,studentresult as 分数 from result
where studentresult between  95 and 100 and subject_id=23;

(3)模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not nul	如果操作符为不为null,结果为真
between	a between b and c	若a在b和c之间，则结果为真
like	a like b	sql匹配，如果匹配b,则结果为真
in	a in(a1,a2,a3,.....)	假设a在a1,或者a2....其中的一个值中，结果为真

①查询姓唐的同学
like 结合 %(代表0到任意个字符)    _（一个字符）

select name,birthday from student
 where name like '唐%';

②查询姓唐的同学,并且名字后面仅有一个字的

select name,birthday from student
 where name like '唐_';

③查询姓唐的同学,并且名字后面仅有个字的

select name,birthday from student
 where name like '唐__';

④查询名字中含有嘉字的同学 %嘉%

select name,birthday from student
 where name like '%嘉%';

⑤查询id为3,4,5号的学生

select name,birthday from student
 where id in (3,4,5);

⑥查询家住在宁波和上海的学生

select name,address from student
 where address in ('宁波','上海');

⑦查询邮箱为空的学生

select name,email from student
 where email is null;

⑧查询邮箱不为空的同学

select name,email from student
 where email is not null;

5.3联表查询

(1)join对比

①查询参加了考试的同学（学号，姓名，科目编号，分数）使用inner join来查询
思路：

• 分析需求，分析查询的字段来自哪些表，（连接查询）

• 确定使用哪种连接查询？ 7种

• 确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id

--  这里给student表和result表起别名，就是因为这两张表中都有student_id这个属性，为了不扯皮，所以起别名并写明用哪个表的id
select s.student_id,name,subject_id,studentresult
from student as s
inner join result as r
where s.student_id=r.student_id;

②使用right join来实现上述操作
join（连接的表） on(判断的条件) 连接查询
where 等值查询

select s.student_id,name,subject_id,studentresult
from student  s
right join result  r
on s.student_id=r.student_id;

③使用left join操作上述问题

select s.student_id,name,subject_id,studentresult
from student  s
left join result  r
on s.student_id=r.student_id;

由结果可知left join会将没有参与考试的学生也查了出来

操作	描述
inner join	如果表中至少有一个匹配，就返回行（hang）
left join	会从左表中返回所有的值 ，即使右表中没有匹配
right join	会从右表中返回所有的值 ，即使左表中没有匹配
④查询缺考的同学

select s.student_id,name,subject_id,studentresult
--  起别名的时候as也可以省略
from student  s
left join result  r
on s.student_id=r.student_id
where studentresult is null;

⑤查询参加了考试的同学的信息：学号，姓名，科目名，分数
思路：

• 分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）

• 确定使用哪种连接查询？ 7种

• 确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id ，成绩表中的subject_id =科目表中的subject_id;

select s.student_id,name,subject_name,studetresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id;

⑥查询参加了高等数学考试的同学的信息：学号，姓名，科目名，分数

select s.student_id,name,subject_name,studentresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name ='高等数学';

总结：

• 我要查询哪些数据 select.....

• 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件

• 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

(2)自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可 对于下面这张表进行拆分

父类（找pid等于1的为一棵树的最顶端）

categoryid	categoryname
2	信息技术
3	软件开发
5	美术技术

子类

pid	categoryid	categoryname
3	4	数据库
3	6	web
5	7	ps技术
2	8	办公信息

①操作：查询父类对应的子类关系

父栏目	子栏目
信息技术	办公信息
软件开发	数据库
软件开发	web
美术技术	ps技术

采用联表查询得到上表只需要让父类的categoryid等于子类的pid即可

//将一张表看成一模一样的表
select a.categoryname as 父栏目,b.categoryname as 子栏目
from category as a,category as b
where a.categoryid = b.pid;

5.4分页(limit)和排序(order by)

(1)排序

①升序asc

查询参加了考试同学的信息（姓名，考试科目，成绩）并将成绩按从小到大排序

select  name,subject_name,studentresult
from student s
inner join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id = sub.subject_id
order by studentresult asc;

②降序desc

只需在上述例子中将asc改为desc即可

(2)分页

语法：==limit(查询起始下标，pagesize)==

第一页： limit 0,5     起始查询下标:(1-1)5
第二页： limit 5,5     起始查询下标:(2-1)5
第三页： limit 10,5     起始查询下标:(3-1)5
第N页： limit (n-1)5,5     起始查询下标:(n-1)*pagesize

pagesize:页面大小
（n-1)*pagesize:起始查询下标
n:当前页
数据总数/页面大小 = 总页数

5.5子查询

①查询高等数学 的所有考试结果（学生id,科目编号，成绩）降序排列 方式一：使用连接查询

select student_id,r.subject_id,studentresult
from result r
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name='高等数学'
order by studentresult desc;

方式二：使用子查询

select student_id,subject_id,studentresult
from result
where subject_id =(
  select subject_id 
  from subject
  where subject_name='高等数学'
);

六、MySQL函数

1.常用函数

①数学运算

--  绝对值
select abs(-8)

-- 向上取整
select ceiling(9.4)

-- 向下取整
select floor(9.4)

--  返回一个0-1之间的随机数
select rand()

-- 判断一个数的符号 0-0 返回-1，正数返回1
select sign（10）

②字符串函数

-- 字符串长度
select char length('发来的法计算')

-- 拼接字符串
select concat('我','adf','asd')

-- 查询，从某个位置开始替换某个长度
select insert('我爱变成helloworld',1,2,'超级热爱')

-- 小写字母
select lower('KjfdsSJK')

-- 大写字母
select upper('KjfdsSJK')

-- 返回第一次出现的子串的索引
select instr('twq123','3')

-- 替换出现的指定字符串
select replace('坚持就是胜利','坚持','努力')

-- 返回指定的字符串(源字符串，截取的位置，截取的长度)
select substr('坚持就是胜利',3,2)

-- 反转
select reverse('清晨我上马')

运用
将姓唐的同学姓式改为姓汤

select replace(name,'唐','汤') from student
where name like '唐%';

③时间和日期函数

-- 获取当前的日期
select current date()

-- 获取当前日期
select curdate()

-- 获取当前的时间
select now()

-- 获取本地时间
select localtime()

-- 获取系统时间
select system()

select year(now())
select month(now())
select day(now())
select hour(now())
select minute(now())
select second(now())

--系统
select system_usr()
select user()
select version()

2.聚合函数(常用)

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
....

查询student表中一共有多少人

-- 都能够统计，表中的数据(想查询一个表中有多少个记录，就是用这个count())
select count(name) from student -- count(字段)，会忽略所有的null值
select count(*) from student; -- count(*) 不会忽略null值 ,本质在计算行数
select count(1) from result; -- count(1) 不会忽略null值 ,本质在计算行数

select sum(studentresult) as 总和 from result
select avg(studentresult) as 平均分 from result
select max(studentresult) as  最高分 from result
select min(studentresult) as  最低分 from result

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
select subject_name, avg(studentresult),max(studentresult),min(studentresult)
from result r
inner join subject sub
on r.subject_id =sub.subject_id
group by r.subject_id -- 通过什么字段来分组
having 平均分>80; -- 分组之后再添加条件必须使用having，详见select语法的图

注意：执行以上命令可能会如果没有修改MySQL的mode将会报以下错误

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.subject_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式：
在Navicat或者sqlyog的命令中执行以下命令即可

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

5.3数据库级别的MD5加密(扩展)

-- 明文密码
insert into testmd5 values(1,'zhanshan','123456'),(2,'lisi','abcdefg'),(3,'wangwu','asdfgh');

-- 加密
update testmd5 set pwd=MD5(pwd) where id =1; -- 对单个数据进行加密

update testmd5 set pwd=MD5(pwd); -- 加密全部数据

-- 插入的数据的时候加密
insert into testmd5 values(4,'xiaming',MD5('567890'));

七、事务

7.1什么是事务

要么都成功，要么都失败就是下面这个转账的事务，必须要子啊两条SQL执行完成之后该事务才算结束，体现事物的原子性

①、SQL执行     A给B转账    A最开始有1000   B最开始有200   A给B转200
②、SQL执行    B收到A的钱   此时A有800    B有400

7.2事务原则

ACID 原则：原子性，一致性，隔离性，持久性

(1)一致性

还是A给B转账这个事，事务完成后，符合逻辑运算也就是总钱数始终是1200

(2)隔离性

所谓的独立性是指并发（多个用户同时操作）的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响 隔离所导致的一些问题：

①脏读：

指一个事务读取另外一个事务未提交的数据.

②不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同.（这个不一定是错误，只是某些场合不对）.

③虚读（幻读）：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致.

(3)持久性

事务因提交不可逆转，被持久化到数据库中

（4）事务的实际操作

-- MySQL是默认开启事务自动提交的
set autocommit =0  -- 关闭
set autocommit =1; --开启（默认的）

-- 手动处理事务
set autocommit =0 -- 关闭自动提交

-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

-- 提交：持久化（成功）
commit

-- 回滚：回到原来的样子（失败）
rollback

-- 事务结束
set autocommit=1 -- 开启自动提交

（5）模拟事务场景

-- 创建一个转账的数据库
create database shop character set utf8 collate utf8_general_ci;

-- 在此数据库中创建表
create table account(
    id int(3) not null auto_increment,
    name varchar(20) not null,
    money decimal(9,2) not null,
    primary key(id)
    )engine=innodb default charset=utf8;

--往表里添加数据
insert into account(name,money)
values('A',2000.00),('B',10000);

--模拟转账，事务
set autocommit =0; -- 关闭自动提交
start transaction; --开启一个事务
update account set money=money-500 where name='A'; -- A减500
update account set money=money+500 where name='B'; -- B加500

commit; -- 提交事务
rollback; -- 回滚

set autocommit =1; -- 开启自动提交

八、索引

索引的定义：索引是帮助MySQL高效获取数据的数据结构，
提取句子主干，就可以得到索引的本质：索引就是数据结构

8.1索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引(primary key)
  • 唯一的标识，主键不可重复，只能有一个列作为主键

*唯一索引 (unique key)

• 避免重复列出现，唯一索引可以重复，多个列都可以标识唯一索引

• 常规索引（key / index）

  • 默认的，index，key关键字来设置

• 全文索引（fulltext）

  • 快速定位数据

(1)索引的使用

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
show index from student; -- 显示所有的索引信息
-- 增加一个全文索引(索引名) 列名
alter table school.student add fulltext index name (name);

-- explain 分析sql执行的状况
explain select * from student;-- 非全文索引
explain select * from student where match(name) against('唐');

给student表中的name添加字段之后结果图

8.2测试索引

~~~sql -- 插入100万条数据 -- 写函数之前必须要写，标志 delimiter $$ create function mock_data() returns int begin declare num int default 1000000; declare i int default 0;

while i <num do
insert into app_user(name,email,phone,gender,password,age)
values(concat('用户',i),'1430953131@qq.com',concat('18',floor(rand()((999999999-100000000)+100000000))),floor(rand()2),uuid(),floor(rand()*100));
set i =i+1;
end while;
return i;
end;

执行上述函数可能会报以下错误
1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
解决方式：
临时解决方法（重启MySQL后失效）：
在命令行界面执行以下命令

~~~sql
set global log_bin_trust_function_creators=TRUE;

永久解决方法：
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

8.3索引的原则

• 索引不是越多越好

• 不要对进程变动数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用来查询的字段上

(1)索引的数据结构

Hash类型的索引 Btree：innodb的默认数据结构

九、数据库备份

十、权限管理

1.用户管理

十一、数据库的规约，三大范式

十二、JDBC

这篇关于MySQL完整版详解的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！