MySQL
MySQL
前端 (页面:站视,数据)
后台 (连接点:连接数据库JDBC,连接前端(控制,控制试图跳转,给前端传递数据))
数据库
DDL : 定义
DML :操作
DQL :查询
DCL : 控制
1. 操作数据库
1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos
1.2 删除数据库
DROP DATABASE [IF EXISTS] westos
1.3 使用数据库
-- 表或字段名是一个特殊字符,需要带`` use 'student' ------------------------- SHOW CREATE DATABASE [表名] -- 查看创建数据库的语句 SHOW CREATE TABLE [表名] -- 查看数据表的 定义语句 DESC [表名] -- 显示表结构 -------------------------
1.4 数据库列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 (常用)
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 双精度浮点数 8个字节
- decimal 字符串形式的浮点数 金融计算
字符串
- char 字符串(0--255)
- varchar 可变字符串 (0-65535) 常用
- tinytext 微型文本 2^8 -1
- text 文本串 2^16 -1 保存大文本
时间日期
java.util.Date
- date YYYY-MM-DD 日期
- time HH: mm : ss 时间格式
- datatime YYYY-MM-DD mm : ss 最常用的时间格式
- timestamp 时间戳, 1970.1.1到现在的毫秒数 (常用)
null
- 没有值,位置
不能使用NULL进行计算,结果为NULL
1.5 数据库的字段类型
Unsigned :
- 无符号的整数
- 不能声明为负数
zerofill:
- 0填充
- 不足的位数,使用0来填充 int(3) , 5 --> 005
自增 AUTO_INCREMENT
- 上一条记录基础上+1
- 通常用来设置唯一主键 index 整数类型
- 可以自定义设计逐渐起始值根步长
非空 NOT NULL
- 必须有值,无则报错
默认 DEFAULT
- 设置默认值
主键 PRIMARY KEY
/* ‘version’ 乐观锁 is_delete 伪删除 gmt_create 创建时间 gmt_update 修改时间 */
1.6 数据表的类型
/* 数据库引擎: INNODB 默认使用~ MYISAM 早些年使用 */
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
物理空间位置
- 数据库文件都存在data目录下
- 本质上还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB在数据库表中止呕一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
-- 1. 数据表中设置字符集编码 CHARSET = UTF8 -- 2. my.ini中配置默认的编码 character-set-server=utf8
mysql默认字符集编码不支持中文
1.7 数据表的操作
-- 1. 表的创建: -- `字段名` : 表明并非关键字 CREATE Table [IF NOT EXISTS] '表名'( '字段名' 列类型 [属性] [索引] [注释], '字段名' 列类型 [属性] [索引] [注释], ...... '字段名' 列类型 [属性] [索引] [注释], )[表类型][字符集设置][注释] ------------------------------------ -- 2. 修改表名 ALTER TABLE [OldName] RENAME AS [NewName] -- 3. 增加表的字段 ALTER TABLE [TableName] ADD [字段名] [列属性] -- 4. 修改表的字段 -- change --> 字段重命名,不能修改字段类型和约束 -- modify --> 只能修改字段类型和约束,不用来字段重命名 ALTER TABLE [TableName] MODIFY [字段名] [新列属性] ALTER TABLE [TableName] CHANGE [OldName] [NewName] [列属性] -- 5. 删除表的字段 ALTER TABLE [TableName] DROP [字段名] -- 6. 删除表 DROP TABLE IF EXISTS [TableName] == 所有创建和删除尽量加上判断,以免报错 ==
2. MySQL数据表操作
2.1 外键
方法一:在创建表的时候增加外键
-- `字段名` : 表明并非关键字 CREATE Table [IF NOT EXISTS] '表名'( '字段名' 列类型 [属性] [索引] [注释], '字段名' 列类型 [属性] [索引] [注释], ...... '字段名' 列类型 [属性] [索引] [注释], PRIMARY KEY ('字段名') -- 主键 Key 'FK_...'('字段名') CONSTRAINT 'FK_...' FOREIGN KEY ('字段名') REFERENCES '外键表名' ('字段名') -- 外键 )[表类型][字符集设置][注释] ------------------------------------
方法二: 更改表字段增加外键
ALTER TABLE [TableName] ADD CONSTRAINT 'FK_...' FOREIGN KEY ('字段名') REFERENCES '外键表名'('字段名')
- 删除有外键关系的表的时候,必须要先删除从表,在删除主表
- 物理外键,数据库级别的外键不建议使用(避免数据库过多造成困扰)!!!
== 最佳实践 ==
- 数据库就是单纯的表,只用来存储数据
- 用程序实现外键的使用!!!
2.2 DML语言(数据操作)
2.2.1 插入
insert 插入
-- 插入单条记录: insert into [表名] ([字段名1,字段名2,...,]) values ('值1','值2',...) -- 插入多条记录: insert into [表名] (字段) values (值1),(值2),(...) --- 字段名根值--> 一一对应 ---
2.2.2 更新
update 修改
-- 带条件修改 update '表名' set 'column_name = value,[column_name = value,...]' where [条件] -- 不指定条件,会修改所有数据 update '表名' set 'column_name' = value
条件:
操作符 | 含义 |
---|---|
BETWEEN ... AND ... | [A,B] |
AND | && |
OR | || |
2.2.3 删除
delete 删除
delete FROM 'TableName' where [条件]
TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和约束不变
delete | TRUNCATE 的区别
相同点:都能删除数据,都不会删除表结构
不同点:
- TRUNCATE 重新设置自增列 计数器会归零
- TRUNCATE 不会影响事务
** delete删除的问题
,重启数据库 -->
- InnoDB 自增列会从1开始 (存在内存当中,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
2.3 DQL查询数据
select 查询
SELECT [ALL | DISTINCT] {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} FROM table_name [as table_alias] [left | right | inner join table_name2] --联合查询 [WHERE ...] -- 指定结果需要满足的条件 [GROUP BY ...] -- 按照哪几个字段来分组 [HAVING] -- 过滤分组的记录必须满足的次要条件 [ORDER BY ...] -- 指定查询的记录按一个或多个条件排序 [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条 []:可选 {}:必选
-- 查询全部 select * from [TableName] -- 查询指定字段 select '字段1','字段2' from [TableName] -- 别名,给结果起一个名字 select '字段1' as 别名1,'字段2' as 别名2 from [TableName] -- Concat函数 select CONCAT('字符串',字段) as 别名 from [TableName]
去重 distinct
作用:去除select查出来的结果中重复的数据(重复数据只显示一条)
select distinct '字段' from [TableName]
数据库的列(表达式)
select VERSON() -- 查询系统版本 (函数) select 100*3+1 -- 用于计算 (表达式) select @@auto_increment_increment --查询自增的步长 (变量) ------------------------------------
数据库中的表达式 : 文本值,列,Null,函数,计算表达式,系统变量...
select 表达式
from 表
2.3.1 where条件子句
作用:检索数据中符合条件
的值
逻辑运算符
与 : AND &&
或 : OR ||
非 : NOT !
模糊查询 :比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | A is null | 操作符为null,结果为真 |
IS NOT NULL | A is not null | 操作符不为null,结果为真 |
BETWEEN | A between B and C | a在b、c之间,结果为真 |
LIKE | A like B | SQL匹配,a匹配b,结果为真 |
IN | a in (a1,a2,a3,...) | a在a1,a2,a3... 某个值中,结果为真 |
-- 查询关键字A前后只有一个字 : _ select ... from [TableName] where [] LIKE '_A_' -- 查询关键字A前后有多个字 : % select ... from [TableName] where [] LIKE '%A%' -- 查询多个记录 select ... from [TableName] where [] in (a,b,c)
2.3.2 Join联表查询
Join 对比
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
select [col1,col2,...] from T1 as s[INNER JOIN | LEFT JOIN | RIGHT JOIN] T2 as r ON [conditions(r.a = s.b)]
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
2.3.3 联表查询
自连接
- 自己表和自己表连接
- 核心 :一张表拆为两张一样的表
select A.'cataName',B.'cataName' from [Table] as A,[Table] as B where A.Aid = B.Bid
2.3.4 分页和排序
LIMIT 分页
- 作用:缓解数据库压力 ( 图片 : 瀑布流)
- 语法 : limit 起始位置 , 页面的大小
ORDER BY 排序
- ASC : 升序
- DESC : 降序
-- 分页 -- limit 起始下标页 , 页面的大小 如: 0-5 -- n : 当前页 -- pageSize : 页面大小 -- 起始值 : (n-1)*pageSize LIMIT [currentPage] [PageSize]
2.3.5 子查询
本质 : where语句中嵌套一个子查询
2.3.6 分组和过滤
GROUP BY HAVING
-- 顺序 select 去重 需要查询的字段 from 表 (表、字段可取别名) xxx join 要链接的表 on 等职判断 where (具体的值,子查询语句) GROUP BY (通过哪个字段分组) HAVING (过滤分组后的信息,条件) ORDER BY (通过哪个字段排序) [ASC | DESC] LIMIT startIndex pagesize
3. MYSQL函数
3.1 常用函数
常用函数
-- 数字运算 SELECT ABS() : 绝对值 SELECT CELLING() : 向上取整 SELECT FLOOR() : 向下取整 SELECT RAND() : 返回0-1随机数 SELECT SING() : 判断一个数的符号 负数: -1 0 : 0 正数 : 1 -- 字符串函数 SELECT CHAR_LENGTH('STRING') : 返回字符串的长度 SELECT CONCAT('A','B') :字符串拼接 SELECT INSERE('oldString',l,r,'insertString') : 从某个位置开始替换某个长度 SELECT LOWER() : 转小写字母 SELECT UPPER() : 转大写字母 SELECT INSTR() : 返回第一次出现子串的索引 SELECT REPLACE('String','A','B') : 替换出现的指定字符串 将A替换为B SELECT SUBSTR('String',l,r) : 返回指定字符串l-r的子串 -- 时间和日期函数 !!! SELECT CURRENT_DATE() 获取当前时间 SELECT CURDATE() 获取当前时间 SELECT NOW() 获取当前的时间 SELECT LOCALTIME() 本地时间 SELECT SYSDATE() 系统时间 SELECT YEAR(NOW()) SELECT MONTH(NOW()) SELECT DAY(NOW()) SELECT HOUR(NOW()) SELECT MINUTE(NOW()) SELECT SECOND(NOW()) -- 系统 SELECT SYSTEM_USER() SELECT USER() SELECT VERSION()
3.2 聚合函数
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
.... | .... |
COUNT
- COUNT(field) : 会忽略所有的null
- COUNT(*) : 不会忽略null值 本质 计算行
- COUNT(1) : 不会忽略null值 本质 计算行
3.3 数据库级别的MD5加密
- 增强算法复杂性和不可逆性
- 不可逆
-- 函数 :MD5() insert into [Table] values(MD5(pwd))
4. 事务
6.1 什么是事务
要么都成功,要么都失败
一一一一一一一一一
将一组SQL放在一个批次中执行
一一一一一一一一一
事务原则:ACID
A : 原子性 (要么都完成,要么都不完成 )
C : 一致性 (数据的变化是一致的 )
I : 隔离性 (多用户同时操作,各个事务不相互影响)
D : 持久性
- 事务没有提交,恢复到原装 - 事务已经提交,持久化到数据库
原子性(Atomicity)
要么都完成,要么都不完成
一致性(Consistency)
事务前后的数据完整性要保证一致
持久性(Durability)
事务一旦提交则不可逆,被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每个用户开启的事务,不能被其他事务的操作数据干扰,事务之间要相互隔离
隔离导致的问题
事务的隔离级别
脏读
一个事务读取了另一个事务未提交的数据
不可重读
在一个事务内读取表中某一行数据,多次读取结果不同
虚度(幻读)
在一个事务内读取了别的事务插入的数据,导致前后数据不一致
手动处理事务
-- mySql 默认开启事务自动提交 SET autocommit = 0 /* 关闭 */ SET autocommit = 1 /* 开启 */ -- 手动处理事务 -- 事务开启 START TRANSACTION -- 标记一个事务的开始 -- 提交 : 持久化(成功) COMMIT -- 回滚 : 回到原来的样子(失败) ROLLBACK -- 事务结束 SET autocommit = 1 /* 开启自动提交 */ SAVAPOINT 保存点名 -- 设置事务的保存点 ROLLBACK TO SAVEPOINT 保存点名 -- 回滚事务的保存点 RELEASE SAVEPOINT 保存点名 -- 撤销保存点 ------------------------ -- 字符集、校对 CHARACTER SET UTF8 COLLATE utf8_general_ci ------------------------
5. 索引
索引是帮助MYSQL高效获取数据的数据结果
5.1 索引的分类
在一个表中,主键索只有一个,唯一索引可以有多个
- 主键索引(PRIMARY KEY)
- 唯一的标识,主键不可重复,只有一个列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引(KEY/INDEX)
- 默认的,index、key关键字设置
- 全文索引(FullText)
- 特定数据库引擎支持
- 快速定位数据
-- 增加全文索引 (索引名)列名 ALTER TABLE school.student ADD FULLTEXT INDEX 'studentName' ('studentName') CREATE INDEX <indexName> on <table('field')> -- EXPLAIN 分析sql执行的状况 -- 非全文索引 EXPLAIN SELECT * FROM student; -- 全文索引 SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
-- 插入100万数据 DELIMITER $$ -- 写函数之前必须要写,标志 CREATE FUNCTION mock_data() RETURN INF BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE i < NUM DO INSERT INTO app_user('name','email','') SET i = i+1; END WHILE RETURN i; END;
5.2 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用查询的字段上
索引的数据结构
BTree
6. 权限管理和备份
6.1 用户管理
记录生活