MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一
由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站开发都选择 MySQL
SQL语句分类
- DDL 数据定义语言,用于定义数据库队形:库,表,列等。
- DML 数据操作语言,用于对数据进行
增删改
操作。 - DCL 数据控制语言,用于定义访问权限和安全级别。
DQL
数据查询语言,用于查询数据。重点
引擎类型
功能 | MyISAM | InnoDB |
---|---|---|
事务支持 |
不支持 | 支持 |
行级锁 | 不支持 表级锁 |
支持 |
外键约束 |
不支持 | 支持 |
表空间的大小 | 较小 | 较大 |
索引类型 | 非聚集索引 | 聚集索引 |
SQL字段的数据类型
类型 | 大小 | 与Java对应数据类型 | 用途 |
---|---|---|---|
TINYINT |
1 byte | byte/Byte | 小整数值 |
SMALLINT | 2 bytes | int/Integer | 大整数值 |
MEDIUMINT | 3 bytes | int/Integer | 大整数值 |
INT |
4 bytes | long/Long | 大整数值 |
BIGINT | 8 bytes | BigInteger | 极大整数值 |
FLOAT | 4 bytes | float/Float | 单精度浮点值 |
DOUBLE |
8 bytes | double/Double | 双精度浮点值 |
DECIMAL | 对DECIMAL(M,D) ,若M>D,为M+2否则为D+2 | BigDecimal | 精确小数值 |
BIT | 1 bit | boolean/Boolean | 0/1值 |
DATE |
3 bytes | java.sql.Date | 日期值 |
TIME | 3 bytes | java.sql.Time | 时间值或持续时间 |
DATETIME | 8 bytes | java.sql.Timestamp | 混合日期和时间值 |
TIMESTAMP |
4 bytes | java.sql.Timestamp | 时间戳 |
CHAR |
0~255 bytes | String | 定长字符串 |
VARCHAR |
0~65535 bytes | String | 变长字符串 |
BLOB | 0~65535 bytes | byte[] | 二进制字节数据 |
约束类型
约束名称 | 描述 |
---|---|
NOT NULL |
非空 |
UNIQUE | 取值不允许重复,唯一 |
PRIMARY KEY |
主键 自带索引 |
FOREIGN KEY |
外键 |
CHECK | 逻辑表达式约束 |
DEFAULT | 默认值(缺省值) |
AUTO_INCREMENT | 主键自增,非主键无法自增 |
primary key主键约束
1 | -- 方式一,在定义字段旁边定义主键 |
default 默认值约束
1 | create table `student`( |
unique 唯一约束
1 | -- 方式一,在定义字段旁边定义主键 |
check 检查约束
1 | create table `student`( |
foreign key外键约束
1 | -- foreign key (当前表字段) references 引用外键的表名 (引用的字段) |
DDL 数据定义语言
查询所有数据库
1 | show databases; |
创建数据库
1 | create database test default character set utf8; |
创建test数据库并指定其默认是字符集为UTF-8,避免存中文时出现乱码情况。
进入数据库
1 | use test; |
进入test数据库
删除数据库
1 | drop database test; |
删除名称为test的数据库。
显示表
1 | show tables; |
创建表
1 | create table student( |
添加表的字段
1 | alter table student add (hobby varchar(20)); |
追加表的列属性,若追加多个属性
可使用逗号
分隔开。
修改表的字段的类型
1 | alter table student modify hobby int; |
将student表中的hobby属性的类型由varchar修改为int 类型。
修改表字段名及字段的类型
1 | alter table student change hobby newHobby varchar(15); |
将student表中int类型的hobby列数据变为varchar类型的newHobby。
删除表的字段
1 | alter table student drop newHobby; |
修改表字段的约束
1 | alter table student MODIFY username varchar(20) unique; |
查看表的结构
1 | desc 表名 |
修改表名
1 | alter table student rename to stu; |
删除表
1 | drop table student; |
DML 数据操作语言
insert 新增记录
1 | insert into 表名(字段名1,字段名2) values(字段值1,字段值2); |
update 更新记录
1 | update 表名 set 字段名1=字段值1,字段名2=字段值2 where XXXX; |
delete 删除记录
1 | delete from 表名 where XXXX; |
DCL 数据控制语言
创建用户
1 | create user 用户名@'%' identified by '密码'; |
创建一个指定的用户名和密码的用户,并允许该账户在任何ip地址访问该数据库。
给用户授权
1 | grant all on 数据库名.* to 用户名; |
给指定用户授权在指定数据库下的所有表的所有的操作权限,包括create,alter,drop,insert,uodate,delete,select等。
DQL 数据查询语言
单表查询
基本查询
查询所有行,列数据
1
select * from 表名;
查询指定列数据
1
select 字段名1,字段名2 from 表名;
去重
操作,用于除去完全重复的查询结果。1
select distinct 字段名 from 表名;
列运算
数字类型
的字段可以进行加减乘除操作1
select age+1 from student;
字符串类型
的字段可以使用concat(str1,str2)函数将str2拼接到str1后面1
select concat(name,'你好') from student;
给字段
取别名
1
select stu_name as name from student;
条件控制,进一步筛选除我们想要的数据
1
2
3
4
5
6
7
8
9
10select * from student where 条件;
-- 举个例子
-- 查询id为3的学生信息
select * from student where id=3;
-- 查询年龄在20岁以上的学生信息
select * from student where age>20;
-- 查询分数在80-90之间(包含80与90)的学生信息
select * from student where score between 80 and 90;
-- 模糊查询,查询姓张的学生
select * from student where name like'张%';
排序
升序 ASC
默认
1
2
3
4select * from 表名 order by 字段名 ASC;
-- 举个例子
-- 按照学生的分数升序排列
select * from student order by score asc;
降序 DESC
1
2
3
4
5
6select * from 表名 order by 字段名 DESC;
-- 举个例子
-- 按照学生的分数降序排列
select * from student order by score desc;
-- 按照多个字段进行排序
select * from student order by id asc,score desc;
分组查询
分组后不能查到个人的信息,只能查到组的信息
,即查询的列字段必须是分组的字段。
1 | -- 语法如下 |
还可以在分组前使用条件控制语句where
先把满足条件的选出来,再进行分组。
1 | -- 语法如下 |
分组查询后,可以使用having
语句把满足条件的组再筛选出来。
1 | -- 语法如下 |
limit子句
sql中独有的语法
用来限定查询结果的起始行、以及总行数。涉及分页查询
时可用此语法。
1 | -- 限定查询结果的起始行、以及总行数 |
1 | -- 如果limit后面只跟一个参数n,表示起始行为第0行,一共查询n条数据 |
如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
初始记录行的偏移量是 0(而不是 1)
in ,null,<>
in(值1,值2,值2)
用于指定数据的取值范围在指定的集合内
not in(值1,值2,值2)
用于指定数据的取值范围不在指定的集合内
1 | -- 查询学生学号为1,2,3的学生信息 |
is null
用于指定该字段值为null
is not null
用于指定该字段值不为null
1 | -- 用于查询name字段是null的学生信息 |
<>
与 !=
在sql中都是表示不等于的意思。
1 | -- 查询性别不为男的学生信息 |
多表查询
笛卡尔积
笛卡尔积在SQL中的实现方式即是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表,笛卡尔积是关系代数里的一个概念,表示
两个表中的每一行数据任意组合
,上图中两个表连接即为笛卡尔积(交叉连接)
内连接
满足条件的记录才会显示出来。
1 | -- 三种内连接查询方式 |

外连接 ☆常用
外连接不但返回符合连接和查询条件的数据行,
还返回一些不符合条件的行。
左外连接
1 | select * from course s left join teacher t on s.t_id = t.id; |

右外连接
1 | select * from course s right join teacher t on s.t_id = t.id; |

子查询
where型子查询
若where 字段
=
(子查询),则子查询的查询结果必须是单行单列
,只能有一个值。若where 字段
in
(子查询),则子查询的查询结果必须是单列
,可以有多个值(行)。
1 | -- 查询有分数大于80分的学生信息 |
from型子查询
可以理解为在查询的结果集上在结构上可以看做一个表,再次对他进行查询。
1 | -- 取分数前5名,然后按分数升序排列学生信息 |
视图
视图和表的区别
1.
表是将数据存在磁盘上,视图是将sql语句存在磁盘上。
2.表是静态的,视图是动态的。表中的数据发生了变化,其建立在表基础上的视图跟着改变。
3.通过视图不能改变表中的数据(一般情况下)
4.删除视图,表不受影响;删除表,视图将不再起作用
5.视图本身没有数据,其
本质是保存了SQL语句
。
创建视图
1 | -- 创建视图语法 |
显示视图
1 | -- 语法 |
删除视图
1 | drop view 视图名; |
函数
完整的函数请查阅该网站 https://www.runoob.com/sql/sql-functions.html
聚合函数
count(字段名) 用于查询行数
1
select count(*) from student;
max (字段名) 用于查询整张表中指定字段的最大值
1
2-- 查询学生的最高分
select max(score) from student;min (字段名) 用于查询整张表中指定字段的最小值
1
2-- 查询学生的最低分
select min(score) from student;sum (字段名) 用于查询整张表中指定字段的值之和
1
2-- 查询全部学生成绩的总和
select sum(score) from student;avg (字段名) 用于查询整张表中指定字段值的平均值
1
2-- 查询全部学生成绩的平均分
select avg(score) from student;
字符函数
函数名 | 功能 | 示例 |
---|---|---|
concat(s1,s2...sn) |
字符串 s1,s2 等多个字符串合并为一个字符串 | |
length(s) | 返回字符串字节长度,一个汉字占3个字节 |
select length('你好') -- 6 |
char_length(s) | 返回字符串 s 的字符数 | select char_length('你好') --2 |
substr(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | select substr("RUNOOB", 2, 3); -- UNO |
lower(s) | 将字符串s转换为小写 | select upper("RUNOOB"); --runoob |
upper(s) | 将字符串s转换为大写 | select upper("runoob"); -- RUNOOB |
数学函数
函数名 | 功能 | 示例 |
---|---|---|
abs(x) | 返回x的绝对值 | select abs(-1) -- 返回1 |
sin(x) | 返回x的正弦值(参数是弧度) | SELECT SIN(2); -- 0.909297 |
cos(x) | 返回x的余弦值(参数是弧度) | SELECT COS(2); -- -0.41614 |
floor(x) | 向下取整,返回小于或等于 x 的最大整数 | SELECT FLOOR(1.5) -- 返回1 |
count(expression) | 返回查询的记录总数,参数是一个字段或者 * 号 | |
PI() | 返回圆周率(3.141593) | SELECT PI() -- 3.141593 |
mod(x,y) | 返回 x 除以 y 以后的余数 | SELECT MOD(5,2) -- 1 |
rand() | 返回 0 到 1 的随机数 | |
ROUND(x) | 返回离 x 最近的整数 | SELECT ROUND(1.23456) -- 1 |
时间函数
函数名 | 功能 | 示例 |
---|---|---|
curdate() | 与current_date作用相同,返回当前日期 | select current_date; -- 2020-06-03 |
curtime() | 与current_time作用相同,返回当前时间 | select curtime(); -- 22:45:10 |
now() | 与sysdate()作用相同,返回当前日期和时间 | select now(); --2020-06-03 22:47:18 |
datediff(d1,d2) | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF('2001-02-02','2001-01-01') -- 32 |
unix_timestamp | 获取Unix时间戳 | SELECT UNIX_TIMESTAMP() |
from_unixtime(stamp,format) | 将时间戳转化为指定格式的日期格式 | SELECT FROM_UNIXTIME(unix_timestamp,format) |
date_format(date,format) |
指定date日期对象的显示格式 |
流程控制函数
if(test,t,f)
若test表达式为真,则返回t,否则返回f。1
2
3
4-- 输出学生的姓名及是否成年
select name,
if(age>18,'成年','未成年') as 是否成年
from student;ifnull(arg1,arg2)
若arg1非空,返回arg1;反之,返回arg2。1
2-- 输入课程名称及所属教师的id,若存在课程无所属教师,则输出无
select name,ifnull(t_id,'无') as 教师ID from course ;nullif(arg1,arg2)
若arg1=arg2则返回null,否则返回arg1。case 字段名
when condition then result
when condition then result
....
else 默认值
end
1 | -- 举个栗子 |

加密函数
MD5加密函数
1 | -- 将字符串str进行MD5进行简单加密操作,生成一个定长的16进制字符串 |
SHA加密函数
1 | -- 举个栗子 |
索引
索引的数据结构
mysql索引的数据结构就是用到的
B+树
。B+树只会在叶子节点上面挂载数据,非叶子节点不会存放数据,非叶子节点只存放索引列的数据。
`如上图所示, sql的两种查找数据方法分别为:
第一种:若用户表存在主键
,或唯一约束的字段
,若前两者都不存在则使用_rowid
作为索引,使用前三者作为条件来查询数据,这样的查询效率高
很多。
第二种:若使用非主键、非唯一约束,非rownum作为条件来查询数据,那么将挨个遍历B+数的所有叶子结点连接在一起的结构,直至查找到数据,这样的方式表的记录过多时查询效率低
。
普通索引
创建索引后,需要确保该索引是应用在SQL查询语句的条件(一般作为where子句的条件)
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
优点:
建立
合理的设计且使用
索引可以大大提高sql的检索速度。缺点:
虽然查询速度提高了,却会降低更新表的的速度。如对表进行增、删、改操作。因为更新表时,sql不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
基本索引
创建最基本的索引,它没有任何限制。
1 | create index 索引名 on 表名(字段名(length)) |
若字段是char、varchar类型,length可以小于字段实际长度。
若字段是blod、text类型,必须指定length。
修改表结构添加索引
1 | -- 语法 |
创建表时指定索引
1 | -- 语法 |
删除索引
1 | -- 语法 |
唯一索引
仅由一个字段组成的索引。
组合索引
即由多个字段共同组成索引。
全文索引
索引优化
like语句操作 ☆重点
一般情况下不鼓励使用like操作,若非要使用,如何使用也是个问题。
like "%aaa%"
会造成索引失效;而like "aaa%"
不会造成索引失效。
即%
在前面会造成索引失效,%
在后面就不会造成索引失效。
索引不会包含有null值的列
只要列中包含有null值都不会被包含在索引中。符合索引中只要有一列含有null值,那么这一列对于此符合索引就是无效的,就不会走索引结构进行查找,而是选择全文检索进行查询。所以我们在数据库设计时尽量不要让字段的默认值为null
。
联合索引,如何选择索引的顺序
- 经常会被使用到的字段优先
- 选择性搞的字段优先
- 宽度小的字段优先
事务
使用InnoDB数据库引擎的数据库或表才能支持事务,MyISAM数据库引擎不支持事务。
事务能够保证
成批的SQL语句要么全部执行成功,要不全部不执行
。
四大特性 ACID
Atomicity原子性
一个事务中的操作要么全部完成,要么全部不完成。执行SQL语句中途发生错误
,则会被回滚
(Rollback)至事务开始之前的状态。
Consistency一致性
在事务开始之前和事务结束之后,数据库的完整性没有被破坏。例:(A向B转钱,不能出现A扣了钱,B却没收到钱)
Isolation隔离性
数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行而导致数据的不一致问题。事务隔离分为不同级别,包括读未提交、读提交、可重复读,可串行化。
Durability持久性
事务结束后,对数据的修改是永久的,即便系统故障也不会丢失。
开启事务
1 | -- 开始事务语法1 |
1 | -- 开始事务语法2 |
1 | -- 举个例子 A向B转账业务 |
事务的隔离级别
读未提交:read_uncommited 可读到其他事务未提交的数据
读已提交:read_commited 只能读取到其他事务已经提交的数据。Oracle数据库默认该级别
可重复读:repeatable_read 重复读。就是在事务开启时,不再允许修改操作。
InnoDB默认隔离级别
串行化:serializable 完全串行化的读,效率低下,比较耗数据库性能,一般不使用。
四个级别逐渐增强,每个级别解决一个问题。
事务级别越高,性能越差
。大多数环境read committed 可以用。
隔离级别 | 是否存在脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | √ | √ | √ |
读已提交 | × | √ | √ |
可重复读 | × | × | √ |
串行化 | × | × | × |
脏读: 脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
不可重复读:是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内多次读到的数据是不一样的,因此称为是不可重复读。
幻读:第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。
sql通过多版本并发控制(MVCC)(快照读/一致性读)解决了幻读问题。
查看事务隔离级别
1 | select @@global.tx_isolation as 全局事务隔离级别,@@session.tx_isolation as 会话事务隔离级别,@@tx_isolation as 下一个数据库事务隔离级别; |

注意:默认的行为(不带session和global)是为下一个(未开始)事务设置隔离级别。
如果你使用GLOBAL关键字,语句在全局对从那点开始创建的所有新连接(除了不存在的连接)设置默认事务级别。你需要SUPER权限来做这个。
使用SESSION 关键字为将来在当前窗口上执行的事务设置默认事务级别,窗口关闭则隔离级别恢复至默认级别。 任何客户端都能自由改变会话隔离级别(甚至在事务的中间),或者为下一个事务设置隔离级别。
设置事务隔离级别
1 | -- 设置当前session的下一个未开始的事务的隔离级别 |
1 | -- 设置当前会话的事务隔离级别 |
1 | -- 设置全局的事务隔离级别 |
三范式
第一范式:要求
有主键
,并且每一个字段原子性不可再分
。第二范式:要求所有非主键字段完全依赖主键,不能产生
部分依赖
。第三范式:所有非主键子弹和主键字段之间不能产生
传递依赖
。
注: 设计只是一种思想理念,我们按照规范的设计方式设计数据库对我们来说有好处,但绝对不是说一定要严格遵守,三范式能极大的减少数据冗余
,但是相对编写SQL而言是增加了难度的,所以所有好的设计都是要权衡利弊的,要对编码难度,存储大小,执行效率等多方面进行综合考量,但是在学习初期最好紧紧的遵循三范式,在后续的编码中体会和总结自己的经验。
第一范式
数据库表中不能出现重复记录,每个字段的原子性是不可再分的。
不符合第一范式的实例。
学生编号 主键 |
学生姓名 | 联系方式 |
---|---|---|
1001 | 小明 | 123@qq.com,15823492837 |
1002 | 小军 | abc@qq.com,17488293998 |
1003 | 小红 | hmj@qq.com.13512873684 |
原因是联系方式可再分为邮箱和电话。
第二范式
第二范式是建立在第一范式的基础上的,另外要求非主键子弹完全依赖主键,不能产生部分依赖。
不符合第二范式的案例:
其中学生编号和课程编号为联合主键。
学生编号 | 性别 | 姓名 | 课程编号 | 课程名称 | 教室 | 成绩 |
---|---|---|---|---|---|---|
1001 | 男 | 白杰 | 2001 | Java | 3004 | 89 |
1002 | 男 | 杨春旺 | 2002 | sql | 3003 | 88 |
1003 | 女 | 刘慧慧 | 2003 | HTML | 3005 | 90 |
1001 | 男 | 白杰 | 2002 | sql | 3003 | 77 |
1001 | 男 | 白杰 | 2003 | HTML | 3005 | 89 |
1001 | 女 | 刘慧慧 | 2001 | Java | 3004 | 90 |
上表虽然确定了主键,但此表会出现大量的数据冗余。原因在于学生信息依赖的主键是学生编号字段,和课程编号没有任何关系;而课程信息依赖于课程编号,与学生编号没有任何关系。只有成绩字段完全依赖于主键的两个部分。这就是部分依赖。
解决方案:
学生表(学生编号,学生姓名,性别) 其中学生编号为主键
课程表(课程编号,课程名称,教室) 其中课程编号为主键
成绩表(学生编号,课程编号,成绩) 其中学生编号与课程编号为联合主键
第三范式
第三范式建立在第二范式的基础上,非主键字段不能传递依赖与主键字段(不能产生传递依赖)
学生编号 | 姓名 | 专业编号 | 专业名称 |
---|---|---|---|
1001 | 白杰 | 2001 | 计算机 |
1002 | 杨春旺 | 2002 | 自动化 |
1003 | 张志伟 | 2001 | 计算机 |
专业编号依赖于学生编号,但专业清楚与学生没有多大关系。专业名称依赖于专业编号。这就是传递依赖。
即某一个字段不直接依赖主键,而是依赖于依赖主键的另一个字段。
解决办法:
学生表(学生编号,姓名,专业编号)
专业表(专业编号,专业名称)
常见表关系
一对一
1 | 第一种方案,分两张表存储,共享主键。 |
一对多
1 | 分两张表存储,在多的一方添加外键。 |
多对多
1 | 分三张表存储。例如在学生表中存储学生信息,在课程表中存储课程信息,在成绩表中存储学生和课程的关系信息。 |