MySQL
1、什么是数据库
数据库(DB,DataBase)
- 概念：数据仓库，软件，安装在操作系统之上，500万以下的数据都可处理，以上需要做优化(索引)
- 作用：存储数据，管理数据
2、操作数据库
2.1、简单操作
MySQL数据库不区分大小写
创建数据库
CREATE DATABASE [IF NOT EXISTS] school CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
-
utf8_general_ci;\此编码格式对字母大小写不敏感，不严格区分大小
-
utf8_bin:严格区分大小写
注意： 如果表名或字段名是特殊字符，需要加``
drop `school` #删除表
2.2、数据库的字段类型
数值
- int:int(n)中n是指显示数据的长度，和数据值大小无关(int(1)也能插入200)
- decimal 字符串的浮点数 进行金融计算，float和double有精度问题
- decimal(10,2):10是总长度(不包括小数点),2表示小数部分
字符串
- char 固定大小字符串 0~255
- varchar 可变字符串 0~65535
- varchar(20)就必须是20个字符内的字符串，可以中英文
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本(书)
时间
- date: YYYY-MM-DD,日期
- time: HH:mm:ss,时间
- datetime: YYYY-MM-DD HH:mm:ss
- timestamp：时间戳 1970.1.1到现在的毫秒数
null
- 无值
- 不要用null进行计算，结果为null
2.3、数据库字段
项目规范
#每张表必须有的五个字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 更新时间
#新建数据库
create table if not EXISTS `student`(
`id` INT(4) not null auto_increment,
`name` VARCHAR(30) not null,
`age` VARCHAR(3) not null,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
#查看创建表的语句
show create table student;
2.4、数据表的类型
'' | MYISAM | INNODB
—|--- | —
事务支持 | 不支持 | 支持
数据行锁定 | 不支持(表锁定) | 支持
外键 | 不支持 | 支持
全文索引 | 支持 | 不支持
表空间大小 | 较小 | 较大，约2倍
使用操作
- MYISAM:节约空间，速度较快
- INNODB：安全性高，事务处理，多表多用户操作
MySQL默认编码Latin1，不支持中文，创表设置charset=utf8
2.4、修改表的约束
- modify:修改约束和字段类型，不能重命名
- change:字段重命名，不能修改约束和字段类型
alter table teacher modify age varchar(10);
alter table teacher change age age1 int(1);#可执行成功，但一般不用
2.5、外键
constraint `FK_1` foreign key (`stu_id`) references student(`id`)
- 删除表的时候需要先删除引用表(从表)，再删除被引用表(主表)
- 以上操作是物理外键，是数据库级别的外键，不建议使用（避免数据库过多造成困扰）
最佳实践
- 数据库就是单纯的表，只存数据，只有行(数据)和列(字段)
- 需要用到外键时，使用程序实现
阿里巴巴规范
【强制】：不得使用外键和级联，一切外键在应用层解决
3、数据管理
3.1、DML(数据库管理语言)
- 添加：
insert into table (字段1,字段2) values (?,?);- 插入多行数据：
values(),();
- 插入多行数据：
- 修改：
update table set 字段1=v1,字段2=v2 where id=?;如果不加where子句，默认修改整张表 - 删除：
delete from table where id=?;不加where子句，全部删除
truncate
truncate student;完全清空一个数据表，表的结构和索引约束不会改变
truncate和delete的区别
- 不同点：truncate重新设置自增列，计数器归零且不会影响事务，delete不会
了解：delete删除问题，删除所有数据之后，重启数据库
- InnoDB:自增列从1开始，数据存在内存中，断电即失
- MyISAM:会继续从上一个增量开始，存在文件中
3.2、DQL(查询)
- 查询：select distinct * from where , group by , having ,order by ,limit
concat(“name:”,studentname)
- 拼接字符串
distinct
- 去除重复数据
select distinct name from student
where子句
- and和&&效果一样，or和||,not和!
模糊查询
- is null,is not null:不能用=判断
- like:%(0到任意个字符) _(一个字符)
联表查询
- A inner join B on A.id=B.id:查左右表都有的
- left join:左表所有值，右表未匹配就是null
- right join:和left相反
- 全外连接=左外连接 UNION 右外连接
- 多次连接
select * from stu1 left join stu2
on stu1.id=stu2=id
right join stu3
stu3.uid=stu2.uid;
自连接
select a.categoryName `上级`,b.categoryName `下级` from
(select * from category where pid=1) a
inner join
(select * from category where pid!=1) b
on a.categoryid=b.pid;
# 也可以:select * from stu s1,stu s2 where s1.id=s2.pid;
排序和分页(order by,limit)
- order by:asc升序,desc降序
- limit n:表示前n条数据
- 分页：limit 起始值(从零开始),页面大小
# 第一页 limit 0,5
# 第二页 limit 5,5
# 第三页 limit 10,5
# 第n页 limit (n-1)*pagesize,pagesize
# 总页数=数据总数/页面数 +1
select * from stu order by id limit 0,5;
子查询：比连接查询要快
函数
- now():当前时间
select now();#2020-12-22 00:27:46
3.3、DDL(定义)
- create table/view/…
3.4、DCL(控制)
- grant、rollback、commit
3.5、聚合函数
-
count、sum、avg、max、min
-
count
- count(column):会忽略列的null值，*和1不会忽略
- count(*):
- count(1):*和1本质都是按行计算，差不多
- 列名为主键count(主键)最快，不为主键，count(1)最快，如果只有一个字段，count(*)最优
-
MD5加密:把密码加密之后，用户传进来密码加密后验证
select * from student weher pwd=MD5(pwd);
3.6、视图
- 视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的,可以展现基表的部分数据。
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
4、事务
4.1、什么是事务
要么都成功，要么都失败
- 例子：银行转账，一个事务两个动作(将一组SQL放在同一个批次里面)
事务原则：ACID
- 原子性：事务的操作要么都发生，要么都不发生
- 转账一个动作减钱一个加钱，必须都发生或者都不发生
- 一致性：事务前后数据完整性保持一致
- 转账前后两个账户钱的总和不变
- 隔离性：多个并发事务相互隔离，互不影响
- 针对多个用户之间的操作隔离，如果没有的话会出现以下情况
- 脏读：一个事务读取了另一个事务未提交的数据
- 不可重复读：一个事务读取另一个修改的数据
- 幻读：一个事务读取另一个插入的数据
- 持久性：数据库中的数据持久，数据库发生故障也可找回
- 事务一旦提交就不可逆
- 在断电前事务还没提交，数据恢复到原状
- 事务提交，数据就持久化到数据库
SQL执行事务
set autocommit=0;#自动提交关闭
set autocommit=1;#自动提交开启(mysql默认开启)
start transaction;#标记事务，这之后的sql都在同一个事务内，要么都成功，要么都失败
commit;#提交事务
rollback;#回滚事务
5、索引
索引(index)是帮助mysql高效获取数据的数据结构
5.1、索引
- 主键索引(primary key)
- 列值唯一(不可null)，只能有一列
- 唯一索引(unique key)
- 列值唯一(可为null)，可以有多列
- 普通索引(index)
- 仅加速查询
- 全文索引(fulltext)
- 对文本的内容进行分词，进行搜索，在大量数据下才起作用
#直接创建索引，还可创建表时创建索引
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
#通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
#查看:
show index from `表名`;
5.2、测试索引
explain关键字
- 查看查询的细节，类型和查询行数
explain select * from table;
- id：查询序列号
- select_type：查询类型
- table：表名
- type：查询使用的类型
- possible_keys 和 key：显示可能应用在这张表的索引
- key_len：表示索引中使用的字节数
- ref：显示索引的那一列被使用
- rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
- Extra：包含不适合在其他列中显式但十分重要的额外信息
插入100万条数据
#设置数据库可添加函数
set global log_bin_trust_function_creators=1;
#定义函数
DELIMITER $$
CREATE FUNCTION mdata()
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),'19224305@qq.com','123456789',FLOOR(RAND()*2),'12345',20);
SET i=i+1;
END WHILE;
RETURN i;
END;
#执行函数
SELECT mdata();
查询时间对比
#未创建索引
select * from app_user where name='用户9999';#查了九十多万条数据,1秒多
#创建索引
create index index_name on app_user(name);
select * from app_user where name='用户9999';#查了一条数据，0.001秒
- 总结：索引在数据量小的时候用处不大，在大数据的时候，区别十分明显
5.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加
- 索引一般加在常用来查询的字段上
数据结构Btree:innodb默认
6、权限管理和备份
6.1、权限管理
权限
- grant:授权
- revoke:撤销权限
6.2、数据库备份
为什么要备份
- 保证重要数据不丢失
- 数据转移
mysql数据备份的方式
- 直接拷贝物理文件
- 在可视化文件手动导出
- 命令行(cmd)下
#一张表，多张表school 表1 表2 表3
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
#下面表示导出成功
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导入需要先登录
mysql>source d:/a.sql
7、规范数据库设计
7.1、设计数据库
当数据库比较复杂的时候，就需要数据库设计
糟糕的数据库设计：
- 数据冗余，浪费空间
- 有外键，数据的插入和删除都很麻烦【不需要使用物理外键】
- 程序性能差
良好的数据库设计：
- 节省内存空间
- 保证数据的完整性
- 方便开发系统
设计数据库的步骤：(个人博客)
- 收集信息，分析需求
- 用户表（用户登录注销、个人信息、写博客、创建分类）
- 分类表（文章分类，谁创建的）
- 文章表
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息：key:value）
- 标识实体（把需求落地到每个字段）
- 标识实体之间的关系
- user ->blog
- user ->category
7.2、三大范式
为什么需要
- 信息重复
- 更新异常
- 插入异常：无法正常显示信息
- 删除异常：丢失有效的信息
三大范式
-
第一范式(1NF)
- 原子性：保证每一列不可再分
-
第二范式(2NF):前提 满足第一范式
- 每张表只描述一件事情
- 就是确保每一列都与主键相关，而不能与主键的一部分相关
-
第三范式(3NF):前提 满足第一和第二范式
- 确保每一列都与主键直接相关，而不能间接相关(任何非主属性不依赖其他非主属性)
规范性和性能的问题
- 阿里规范：关联查询不能超过三张表
- 考虑商业化的需求和目标(成本，用户体验)，数据库的性能更加重要
- 在规范性能的时候适当考虑规范
- 故意给表增加冗余的字段，将多表查询变成单表查询
- 故意增加计算列(从大数据量降低为小数据量的查询，索引也行)
8、JDBC
框架思想：没有什么是加一层解决不了的。
8.1、第一个JDBC程序
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接数据库对象
Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT","root","123456");
//3.获取执行SQL对象
Statement statement=connection.createStatement();
//4.执行SQL，返回结果
ResultSet rs=statement.executeQuery("select * from users");
//5.处理结果
while(rs.next()){
System.out.println("name=>"+rs.getObject("name"));
}
//6、释放连接
rs.close();
statement.close();
connection.close();
可提取成工具类(获取和关闭连接数据库对象)
8.2、SQL注入
SQL注入是程序中事先定义好的查询语句的结尾上添加额外的SQL语句，来获取数据。
rs = st.executeQuery("select * from users where name='"+name+"' and password='"+password+"'");
login(" 'or '1=1"," 'or '1=1");
//mysql中and的优先级高于or,可把and看出* or看成+
8.3、PreparedStatement
有效防止SQL注入，效率更好
- 本质是把传入的参数包一层’’，当做字符串，有转义字符直接转义
pst = conn.prepareStatement("select * from users where id=?");
pst.setInt(1,1);
rs = pst.executeQuery();
8.4、使用Java实现事务
- 开启事务：conn.setAutoCommit(false);
- 一组事务执行完毕，提交事务
- 如果有异常：在catch语句中可显示定义回滚事务，但默认会回滚
9、数据库连接池
问题
数据库连接–执行完毕–释放
连接–释放
… 十分浪费资源
池化技术：准备一些预先的资源，需要使用直接拿，不需要自己获取
- 最小连接数
- 最大连接数
- 等待超时
- 编写连接池，实现接口DataSource
开源数据源实现(拿来即用)
- DBCP：jar包：commons-dbcp-1.4;commons-pool-1.6
- C3P0：jar包：c3p0-0.9.5.5;mchange-commons-java-0.2.19
- Druid:阿里巴巴
结论
- 无论使用什么数据源，本质不变，实现DataSource接口，方法不变
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
