1、前言
1.1、什么是数据库?
- Database,简称DB
- 按照一定格式存储一些数据的组合
1.2、数据库管理系统?
- DatabaseManagements,简称DBMS
- 顾名思义,是用来管理数据库的
- 可以对数据库进行增删改查之类的操作
- 常见DBMS
- mysql
- oracle
- sql server
- DB2
- sybase等
1.3、SQL
简介
- 结构化查询语言
- DBMS通过执行SQL,对DB进行一些操作
- 它是一套标准,可以运行于多种数据库
种类
- DQL:数据查询语言(select)(操作数据)
- DML:数据操作语言(insert,delete,update)(操作数据)
- DCL:数据控制语言(grant,revoke)(操作权限)
- DDL:数据定义语言(drop,create,alter)(操作表结构)
- TCL:事务控制语言(commit,rollback)(操作事务)
1.4、MySQL的安装与卸载
1.5、MySQL的服务
2、MySQL基本使用
2.1、数据库
创建和销毁
创建
CREATE DATABASE IF NOT EXISTS `数据库名` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
删除
DELETE DATABASE IF EXISTS `数据库名`;
2.2、数据类型
数值型
整数型
int
- 整数型,相当于Java中的int
bigint
- 整数型,相当于Java中的long
浮点型
float
- 单精度浮点型
double
- 双精度浮点型
字符型
varchar
- 可变长字符串
- 会根据实际传入字符串的长度动态分配空间
- 优点:节省空间
- 缺点:需要动态分配空间,速度慢
char
- 定长字符串
- 会根据建表时设置的长度分配空间
- 优点:不需要动态分配空间,速度快
- 缺点:使用不当会造成空间的浪费
clob
- Character Large OBject
- 字符大对象
- 最多存储4GB
- 适合存储文章,说明之类
- 超过255字符的数据可采用此类型
进制型
blob
- Binary Large OBject
- 二进制大对象
- 适合存储图片,音频,视频等流媒体数据
日期型
data
- 短日期类型
- 年月日
datatime
- 长日期类型
- 年月日时分秒
- now() 返回当前时间,datetime类型
2.3、表
简介
-
数据库中最基本的单元;
-
用表来展示数据较为直观
-
每张表都由行和列
- 行:row,记录
- 列:column:字段
-
性别 年龄 男 18 男 18 男 18
-
每个字段都有字段名,字段类型,约束等属性
查看表结构
desc(describe缩写) 表名;
创建和销毁
创建
CREATE TABLE IF NOT EXISTS `表名`(
字段1 数据类型 [PRIMARY KEY],
...
字段n 数据类型 [DEFAULT 默认值]
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
删除
DELETE TABLE IF EXISTS `表名`;
复制(了解)
CREATE TABLE 表名1 AS SELECT 字段1, ..., 字段n FROM 表名2;
# 原理
将查询到的 数据与结构 一起构建新表
2.4、常用命令
命令 | 解释 |
---|---|
select version() | 查看mysql版本 |
select database() | 查看当前使用数据库 |
show databases | 查看所有数据库 |
show tables | 查看所有表 |
show engines \g | 查看所有支持引擎 |
2.5、字符集
注意:如果不是通过my.ini配置文件设置的内容,只在当前状态下有效,当重启数据库服务后失效。所以如果想要不出现乱码只有修改my.ini文件,数据库编码可以在创建数据库时候指定UTF8,如下:
|character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8
注:
该配置character_set_server
无法设置成UTF8
交互时候仍然会出现乱码。
通过命令设置编码:
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
SET collation_connection = utf8_bin;
SET collation_database = utf8_bin;
SET collation_server = utf8_bin;
my.ini中配置默认编码
default-character-set=utf8
连接数据库设置编码
jdbc:mysql://192.168.18.1:3306/test?characterEncoding=utf8
常用编码:
Java | MySQL |
---|---|
UTF-8 | utf8 |
GBK | gbk |
GB2312 | gb2312 |
ISO-8859-1 | latin1 |
3、增删改查
- 增删改查,作用于
数据
3.1、SELECT
语法
SELECT 字段1, ..., 字段n FROM 表名, ..., 表名
[WHERE 条件1 [AND 条件2] ]
3.2、INSERT
语法:
# 插入单条数据
INSERT INTO 表名(字段1, ..., 字段n) VALUES(值1, ..., 值n);
# 插入多条数据
INSERT INTO 表名(字段1, ..., 字段n) VALUES(值1, ..., 值n), ..., (值1, ..., 值n);
# 与select连用 (少用)
INSERT INTO 表名1(字段1, ..., 字段n) SELECT 字段1, ..., 字段n FROM 表名2
3.3、DELETE
语法:
DELETE FROM 表名
[WHERE 条件1[ AND 条件2]]
优点:
可以回滚
缺点:
按照字段逐个删除,且空间不释放,效率较低
TRUNCATE
属于DDL操作
语法:
TRUNCATE TABLE 表名;
优点:
截断表,物理删除,效率高
缺点:
无法恢复
3.4、UPDATE
语法:
UPDATE 表名
SET 字段1 = 值, ..., 字段n = 值
[WHERE 条件1 [ AND 条件2]]
3.5、ALTER(待补充)
- 操作表结构,DDL
- 修改表结构成本较高
- 这个责任应该由设计人员来承担
4、MySQL进阶语法
4.1、ORDER BY
- 排序语法
- 升序asc(默认),降序desc
- 若排序遇到字段值相同,可使用多个字段
案例:单个字段
select * from 表名 order by 字段 desc;
案例:多个字段
# 当字段值相同,再根据第二个字段排序
select * from 表名1 order by 字段1 desc,字段2 asc;
可以根据字段位置排序(了解)
# 根据第二列排序
select 字段1, 字段2 from 表名 order by 2;
# 不推荐,因为不健壮
# 因为列的顺序和数量很容易发生变化
4.2、数据处理函数
- 又被称为单行处理函数
- 一个输入对应一个输出,记录之间没有关系
- 比如sum求和是个多行处理函数,输入可以任意个,输出是一个
函数名 | 作用 | 案例 | 输出 |
---|---|---|---|
lower(字符串) | 转小写 | select lower(’'XXX) from 表名 | xxx |
upper(字符串) | 转大写 | select upper(‘xxx’) from 表名 | XXX |
substr(起始位置, 截取长度) | 截取子串 | select substr(‘abcd’, 1, 1) from 表名 | a |
concat(串1, 串2) | 字符串拼接 | select concat(‘x’, ‘x’) from 表名 | xx |
length(参数) | 取得长度 | select length(‘abc’) from 表名 | 3 |
l/r/trim(串) | 去空格,trim去两边,l/r 去 左/右 | select trim(’ x ') from 表名 | x |
str_to_date(字符串, 自定义格式) | 转字符串为日期 | select str_to_date(‘1999-04-07 1:00:00’, ‘%Y-%m-%d %h:%i:%s’) | 1999-04-07 01:00:00 |
data_format(日期, 自定义格式) | 转日期为具有一定格式的字符串 | select date_format(date, ‘%Y/%m/%d’) | 年/月/日 |
round(数) | 四舍五入,参数1为字段,2为保留的小数位 | select round(4.5, 0) from 表名 | 5 |
rand() | 生成[0, 1)范围的随机数 | select rand() | 0.9057697559760601 |
ifnull(值, 值) | 可将null转为具体值 | select ifnull(null, 0) from 表名 | 0 |
format(值,自定义格式) | 格式化 | select format(123456, ¥999, 999) from 表名 | 123,456 |
4.3、分组函数
- 多行处理函数
- 输入多行,返回单行
- 一般先分组再使用
- 若不分组,则计算所有
- 自动忽略null
- 不能直接使用在where中
函数名 | 作用 | 测试 | 输出 |
---|---|---|---|
max | 求得最大值 | select max(字段) from 表名 | 最大值 |
min | 求得最小值 | select min(字段) from 表名 | 最小值 |
avg | 求得平均值 | select avg(字段) from 表名 | 平均值 |
count | 求得总数量 | select count(字段) from 表名 | 总数量 |
sum | 求得总值 | select sum(字段) from 表名 | 总值 |
count(*)和count(字段)的区别?
前者
返回所有
记录的数量后者
返回该字段不为null
记录的数量
4.4、GROUP BY
分组
- 分组函数必须分组才能使用
- 若不分组默认一组
思考:为什么这条会报错?
select * from 表名 where 字段1 > min(字段1);
语句执行顺序:
from > where > group by > select > order by
因为where执行在分组之前,因此分组函数失败!
4.5、DISTINCT
去除重复记录
错误示范:
select ename, distinct job from emp
#error:ename与去重之后的job记录数量不匹配
4.6、LIMIT
可以控制要显示数据的起始下标
和数量
,多用于分页
- 一个参数
# 取前五条数据
select * from 表 limit 5;
- 两个参数
# 取下标1开始的五条数据
select * from 表 limit 1, 5;
5、连接查询
- 一张表中取数据称为单表查询
- 多张表中取数据称为连接查询
连接分为:
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左(外)连接
- 右(外)连接
5.1、内连接:等值连接
条件是等量,因此被称为等值连接
SQL92语法:
select * from 表1, 表2 where 表1.字段1 = 表2.字段1
缺点:连接条件和进一步筛选条件放到了一起,结构不清晰。
解决方案:SQL99语法
select * from 表1
inner(可省略) join 表2
on 表1.字段1 = 表2.字段1
where 条件...
5.2、内连接:非等值连接
条件是一个范围,不是等量关系,称为非等值连接
select * from 表1
inner(可省略) join 表2
on 表1.字段1 between 表2.字段2 and 表2.字段3
where 条件...
5.3、内连接:自连接
自连接,自己连接自己
假如一张员工表emp有两个字段,no工号,name姓名和mgr对应领导工号,mgr对应的是,比如
no | name | mgr |
---|---|---|
1 | 员工1 | 2 |
2 | 员工2 | 3 |
3 | 员工3 | null |
求出员工姓名和对应领导姓名
select a.name '员工姓名', b.name '领导姓名' from emp a
join emp b
on a.mgr = b.no
5.4、外连接:左(右)外连接
以左(右)边的表为主表,只要有数据就显示,不管右(左)边是否为null
select * from 表1
left(right) outer(可省略)join 表2
on 表1.字段1 = 表2.字段1
5.5、子查询
select中嵌套select,被嵌套的称为子查询,比如:
#子查询可能出现的位置
select
(select...)
from
(select...)
where
(select...)
5.5、UNION
- union要比表连接高一些
- 表连接每连接一次新表则匹配的次数满足笛卡尔积,成倍的翻
- 但是union能减少匹配的次数,还能完成两个结果集的拼接
- union的两张表字段必须匹配,包括数量和类型
语法:
select * from 表1
union
select * from 表2
6、约束
建表时可以给一些字段添加约束,增加数据的完整性,有效性
约束分为:
主键约束:primary key
外键约束:foreign key
非空约束:not null
唯一约束:unique
检查约束:check(mysql不支持,oracle可以)
6.1、PRIMARY KEY
- 主键约束
- 一张表只能有一个
- 被修饰字段不能重复
- 唯一 + 非空 = 主键
- 相当于人的身份证
单字段主键:建表时语法:
字段名 数据类型 PRIMARY KEY
多字段(复合)主键(不建议):建表时语法:
PRIMARY KEY(字段1, ..., 字段n)```
6.2、NOT NULL
- 非空约束
- 被修饰字段不能为NULL
- 建表时语法:
字段名 数据类型 NOT NULL
- 主键类型通常为int、bigint、char
- 不建议使用varchar
- 主键一般为定长
注
主键除了单一主键和复合主键,还分为自然主键和业务主键
**自然主键:**主键值是一个自然数,跟业务没关系;
**业务主键:**主键值和业务紧密相连,例如银行卡号;
6.3、UNIQUE
- 唯一约束
- 正如其名,被修饰字段不能重复
- 但可为NULL
单字段唯一:建表时语法
字段名 数据类型 UNIQUE
多字段唯一:建表时语法:
UNIQUE(字段1, ..., 字段n)
6.4、FOREIGN KEY
- 外键约束
- 表与表之间建立联系
语法:
FOREIGN KEY(字段1) REFERENCES 表名(字段)
7、存储引擎
- MySQL特有的术语
- 是一个表存储/组织数据的方式
查看MySQL支持的引擎
SHOW ENGINES \G
7.1、MyISAM
将一张表分成三份进行存储
- 格式文件 - 存储表结构的定义(mytable.frm)
- 数据文件 - 存储表行的内容(mytable.MYD)
- 索引文件 - 存储表上的索引(mytable.MYI)
**优点:**可被转化为压缩,只读表来节省空间
**缺点:**不支持事务,不安全
7.2、InnoDB
- MySQL默认存储引擎
- 支持事务,以保证数据安全,所以效率不高
- 不支持压缩,只读表
- 支持数据库崩溃后的自动回复
- 表在数据库目录中以.frm格式表示
- 数据和索引被存放在表空间 tablespace
- 提供一组用来记录事务性活动的日志文件
- 多版本MVCC和行级锁定
- 支持外键及引用的完整性,包括级联删除和更新
**优点:**支持事务,安全
**缺点:**慢,不能被压缩,没有只读表
7.3、MEMORY
- 数据和索引被存在内存中
- 表在数据库目录中以.frm格式表示
- 表级锁机制
- 没有TEXT和BLOB字段
- 之前被称为堆引擎
**优点:**快
**缺点:**不安全,数据断电即失
8、事务
8.1、什么是事务?
- 一个事务就是一个完整的业务逻辑。
比如A向B转账10000
A扣10000
B加10000
- 以上是最小的单元,同成共败,不可再分。
- 只有操作数据的DML具有事务
- 当开启了事务,所有DML操作会被记录到事务性活动日志文件中
- 提交:COMMIT,清空日志,将数据持久化,标志着事务成功的结束;
- 回滚:ROLLBACK,撤销操作,清空日志,标志着事务失败的结束;
- MySQL默认当执行一条DML语句会自动提交
8.2、特性(ACID)
原子性
- Atomicity
- 事务是
最小的单元
,全部
成功或全部失败,没有第二种可能
一致性
- Consistency
- 在事务前后,数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性
- Isolation
- 数据库
允许
多个事务同时
对其数据进行读写
,隔离性可以防止
由于交叉执行而导致的数据不一致
。
持久性
- Durability
- 事务
结束
后,对数据的修改是永久
的,即便系统故障也不会丢失
。
8.3、隔离级别
读未提交(最低)
- READ UNCOMMITTED
- 未提交的事务可以被其他事务读到
- 存在
脏读
现象
脏读: 读到的数据不是最终数据,可能会被修改或回滚,没有意义。
读已提交
- READ COMMITTED
- 只能读取其他事务已提交的数据
- 解决了脏读
- 存在
不可重复读
问题
不可重复读: 事务开启过程中,读取到的数据随时可能被其他事务修改,事务期间读取数据不一致
称为不可重复读
可重复读
- REPEATABLE READ
- 事务开启之后,读取到的数据一致,不会因为被其他事务而改变
- 解决了不可重复读
- 存在
幻读
现象
幻读: 读到的数据是幻象,不够真实。
比如昨天开启事务,a为100,到了第二天结束事务,在此过程中a被改变了n次,但此事务中a还是100。
串行化(最高)
- SERIALIZABLE
- 最高级别,解决了上述所有问题
- 最低效率
- 给被操作的数据加锁,阻止并发
9、索引
9.1、简介
-
索引就是事先排序,分块存储,
结构
是二叉树,查找时可以应用二分
找到其所在块,相当于一本书的目录,缩小扫描范围
-
加于字段
-
主键和唯一约束
自动添加索引
-
不管是否有索引,首先会在SGA的数据缓冲区中查找所需要的数据,没有时服务器进程才去读磁盘。
无索引
,直接去读表数据存放的磁盘块,读到数据缓冲区中再查找需要的数据。有索引
,先读入索引表,通过索引表直接找到所需数据的物理地址,并把数据读入数据缓冲区中。
什么时候适合用索引呢?
- 数据量庞大
- 经常被作为筛选条件的字段
- 很少被DML修改
优点: 提高了查询速度
缺点:
- 索引底层是用B-Tree来存储的,是一颗平衡二叉树,如果我们对这棵树增删改的话,那肯定会破坏它的原有结构。
- 要维持平衡树,就必须做额外的工作。正因为这些额外的工作开销,导致索引会降低增删改的速度
- 任何一张表的任何一张记录在硬盘上都有一个物理存储编号
9.2、索引分类
- 单一索引:一个字段添加索引
- 复合索引:多个字段联合索引
- 主键索引:主键上添加索引
- 唯一索引:UNIQUE约束字段添加索引
注意: 唯一性较弱的字段添加索引意义不大
9.3、索引的使用
创建语法
CREATE INDEX 索引名 ON 表名(字段名)
删除语法
DROP INDEX 索引名 ON 表名
查看语句是否使用了索引?
EXPLAIN + SQL语句
# type为ref表示使用了索引
9.4 索引失效
- 当使用模糊查询以
%
开头,索引失效 - 当使用
or
时,两边字段都有索引才会生效 - 当使用复合索引当作条件,若没有使用左侧字段则失效
- where条件中索引列参加了运算,失效
- where条件中索引列使用了函数
10、视图
10.1、什么是视图?
将查询结果保存起来,以不同的角度看待数据
10.2、视图的使用
创建语法:
CREATE VIEW 视图名 AS SELECT 字段... FROM 表名
只有DQL语句才能以视图形式创建
删除语法:
DROP VIEW 视图名
更新视图会导致原表发生变动
11、DBA常用命令
11.1、用户创建
CREATE USER 用户名 IDENTIFIED BY 用户密码
11.2、授权
CREATE USER 用户名 IDENTIFIED BY 用户密码
11.3、导入导出
导出
# 导出数据库
mysqldump 数据库名>绝对路径 -r用户名 -p密码
mysqldump 数据库名 表名>绝对路径 -r用户名 -p密码
导入
- 新建一个数据库;
- 进入数据库;
- 执行source 路径```sql
CREATE DATABASE IF NOT EXISTS `数据库名` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE 数据库名;
# 进入命令提示符页面
source 路径
12、三大范式(空)
- 第一范式(确保每
列
保持原子
性) - 第二范式(确保表中的每
列
都和主键
相关) - 第三范式(确保每
列
都和主键
列直接
相关,而不是间接相关)
参考:https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html 博客园博主:Ruthless
13、存储过程
13.1、简介
SQL 语句需要
先编译
后执行
。
而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库
中,用户通过指定存储过程的名字
并给定参数
(如果该存储过程带有参数)来调用执行
它。
创建的存储过程保存在数据库的数据字典中。
13.2、 优点
(1)增强SQL语言的功能和灵活性:存储过程可以用
控制语句
编写,有很强的灵活性,可以完成复杂的判断
和较复杂的运算
。
(2)标准组件式编程·:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句
。
(3)较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被执行多次,那么存储过程要比批处理的执行速度快很多
。这是因为存储过程是预编译的。
(4)减少网络流量:针对同一个数据库对象(如查询、修改),如果这一操作所设计的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句
,从而大大减少网络流量并降低了网络负载。
(5)作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应数据访问权限的限制
,避免了非授权用户对数据的访问,保证了数据的安全。
13.3、 基本语法
CREATE PROCEDURE 过程名([ [IN | OUT | INOUT] 参数名 数据类型[,[IN | OUT | INOUT] 参数名 数据类型...]])
[特性...] 过程体
存储过程根据需要可能会有输入、输出、输入输出参数,类型分别为:IN,OUT,INOUT: 如果有多个参数用“,”分隔开。
MySQL存储过程的参数类型在存储过程定义时指定
特性:
- CONTAINS SQL 表示子程序包含SQL语句,但不包含读写数据的语句(默认)
- NO SQL 表示子程序不包含SQL语句。
- REDS SQL DATA 表示子程序包含读数据的语句。
- MODIFIES SQL DATA 表示子程序包含写数据的语句。
过程体用BEGIN、END用来标识
例:
DELIMITER //
CREATE PROCEDURE myproc(OUT s INT)
BEGIN
SELECT COUNT(*) INTO S FROM students;
END
//
DELIMITER ;
DELIMITER 分隔符
MySQL默认以“;”为分隔符,所以需事先用”DELITITER //“声明当前段分隔符,让编译器把两个”//“之间的内容当作存储过程的代码,不会执行这些代码;换句话说将语句的结束符号从分号;临时改为//(可自定义)
默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀;比如 select host, user from mysql.user; 其中mysql.user
为数据库名加表名
13.4、 存储过程实例
- 编写addresult存储过程,计算n+…+m的结果
DELIMITER //
CREATE PROCEDURE addresult1(IN n INT, IN m INT, OUT sum INT)
BEGIN
SET sum = 0;
WHILE(n <= m) DO
SET sum = sum + n;
SET n = n + 1;
END WHILE;
END
//
DELIMITER ;
//调用addresult1
SET @sum = 0;
CALL addresult1(n, m, @sum);
SELECT @sum;
全局变量需要以@开头
用set @变量名 = value
赋值
用select @变量名
显示值
- 创建存储过程sp_student_info, 用于显示输入学号的学生基本信息(包括学号、姓名、性别和系)
DELIMITER //
CREATE PROCEDURE sp_student_info(IN s_sno char(9))
BEGIN
SELECT SNO, SNAME, SSEX, SDEPT FROM STUDENT WHERE SNO = S_SNO;
END
//
DELIMITER ;
//调用sp_student_info
CALL sp_student_info('201215122');
13.5、 查看存储过程
show procedure status[\g | \G];
\g : 作用通;
\G:作用把显示结果旋转90°
例:
show procedure status like "%sp%" \G;
// 获取数据库所有名称包含sp的存储过程信息
查看存储过程或函数的创建代码
show create procedure <sp_name>;
13.6、 删除存储过程
DROP PROCEDURE [IF EXISTS] SP_NAME
IF EXISTS 子句时MYSQL的一个扩展
如:
DROP PROCEDURE IF EXISTS SP_AGE_PLUS;