MySQL相关丶

名词概念

  • DDL数据库定义语言：CREATE、DROP
  • DML数据库操作语言：INSERT、UPDATE、DELETE
  • DQL数据库查询语言：SELECT
  • DCL数据库控制语言：GRANT、REVOKE访问权限

常用SQL

select version();
select user();
select system_user();
select 100*3-1 as jieguo;
select @@auto_increment_increment -- 查询数据库自增步长
show databases;
use school;
show create database school;
show create table student;
desc student;
update user set password=password('123') where user='root' and host='localhost';
flush privileges;
-- 修改表名
ALTER TABLE teacher RENAME AS teacher1;
-- 增加字段
ALTER TABLE teacher1 ADD age INT(11);
-- 修改字段约束MODIFY
ALTER TABLE teacher1 MODIFY age VARCHAR(11);
-- 修改字段名CHANGE
ALTER TABLE teacher1 CHANGE age age1 INT(11);
-- 删除字段
ALTER TABLE teacher1 DROP age1;
-- 删除表如果存在
DROP TABLE IF EXISTS teacher1;
-- `字段名`用这个符号更安全
-- 常用函数
-- 绝对值
select ABS(-8)
-- 向上取整
select ceiling(9.4)
-- 向下取整
select floor(9.4)
-- 返回随机数
select rand()
-- 判断一个数的符号
select sign(10)
-- 字符串函数
-- 字符串长度
select char_length('ssssssss')
-- 拼接字符串
select concat('i','o','u')
-- 查询 替换
select insert('woai',1,2,'xihuan')
-- 大小写替换
select lower('YeYuQiuChi');
select upper('YeYuQiuChi');
-- 返回第一次出现字串的索引
select instr('YeYuQiuChi','iu');
-- 替换字符串
select replace('YeYuQiuChi','iu','oo');
-- 截取字符串
select substr('YeYuQiuChi',3,4)
-- 反转字符串
select reverse('YeYuQiuChi');
-- 聚合函数 分组：符合分组条件的记录为同一组
-- MD5('123456');数据库级别MD5加密

数据类型

数值类型(宽度)

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes 此处略 此处略 极大整数值
FLOAT 4 bytes 此处略 此处略 单精度 浮点数值
DOUBLE 8 bytes 此处略 此处略 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

时间和日期

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型(字符长度)

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

引擎

建表的时候即可以选择（基于MySQL逻辑架构的可插拔特性）

  • INNODB：支持事务、行锁、安全等，
  • MYISAM：支持全文检索等

外键

方式一，创建表的时候，增加约束（麻烦，比较复杂）（导出的表结构是这种写法）

方式二，创建表完成之后添加外键关系

-- 创建表之后添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上两种操作都是物理外键，数据库级别的外键，容易造成困扰，外键概念必须在应用层解决（阿里开发手册）

联表查询

join on、自连接、mysql全外连接用union近接实现

img

七种Join

select * from A inner join B on A.key= B.key;
select * from A left join B on A.key=B.key;
select * from A right join B on A.key=B.key;
select * from A left join B on A.key=B.key where B.key is null;
select * from A right join B on A.key=B.key where A.key is null;
select * from A full outer join B on A.key=B.key;
select * from A full outer join B on A.key=B.key where A.key is null or B.key is null;
# mysql中不支持full outer join，用其他方式合并去重
# 全有
select * from A left join B on A.key=B.key
union
select * from A right join B on A.key=B.key;
# 左右各自独有
select * from A left join B on A.key=B.key where B.key is null
union
select * from A right join B on A.key=B.key where A.key is null;

准备数据

CREATE TABLE `tbl_emp` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`name` VARCHAR ( 20 ) DEFAULT NULL,
`deptId` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `fk_dept_id` ( `deptId` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER 
SET = utf8;

CREATE TABLE `tbl_dept` (
`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR ( 30 ) DEFAULT NULL,
`locAdd` VARCHAR ( 40 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER 
SET = utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD', 11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR', 12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK', 13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS', 14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD', 15);
INSERT INTO tbl_emp(NAME, deptId) VALUES('z3', 1);
INSERT INTO tbl_emp(NAME, deptId) VALUES('z4', 1);
INSERT INTO tbl_emp(NAME, deptId) VALUES('z5', 1);
INSERT INTO tbl_emp(NAME, deptId) VALUES('w5', 2);
INSERT INTO tbl_emp(NAME, deptId) VALUES('w6', 2);
INSERT INTO tbl_emp(NAME, deptId) VALUES('s7', 3);
INSERT INTO tbl_emp(NAME, deptId) VALUES('s8', 4);
INSERT INTO tbl_emp(NAME, deptId) VALUES('s9', 51);

测试7种join

select * from tbl_emp a,tbl_dept b;# 机读从from开始做笛卡尔积
select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;
# 全连接
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;
# 全连接不要交集
select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null
union
select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;

事务

ACID原则：atomicity原子性、consistency一致性、isolation隔离性、durability持久性

关于隔离性的四个隔离级别：未授权读取（读未提交），授权读取（读已提交），可重复读取和串行化

隔离级别 脏读 非重复读 Phantom read
READ_UNCOMMITTED allowed allowed allowed
READ_COMMITTED prevented allowed allowed
REPETABLE_READ prevented prevented allowed
SERIALIZABLE prevented prevented prevented

（Phantom幻读，prevented阻止，allowed这里指代可能出现）

set autocommit = 0;-- 关闭自动提交事务
start transaction;
-- 一顿操作
commit;
ROLLBACK;
set autocommit = 1;--  默认是开启事务自动提交的
SAVEPOINT 保存点名;-- 设置一个事务的保存点
ROLLBACK to SAVEPOINT 保存点;-- 回滚到保存点
RELEASE SAVEPOINT 保存点;-- 撤销保存点

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，只能有一个列作为主键
  • 常规索引（KEY/INDEX）
    • 默认的，可通过key或index关键字设置
  • 唯一索引（UNIQUE KEY）
    • 避免重复的行记录
  • 全文索引（FullText）
    • 在特定的数据库引擎下才有
  • 联合索引
show index from student;-- 显示索引信息
alter table school.`student` add fulltext index `fullindex_studentName`(`studentName`);
-- EXPLAIN分析sql执行状况
EXPLAIN select * from student where match(studentName) against('赵')

加索引原则

  • 索引不是越多越好
  • 经常变动的字段不宜加索引
  • 小数据量表不加索引
  • 索引通常加在常用来查询的字段上

索引的数据结构

  • Hash类型的索引
  • Btree，Innodb默认的

用户管理

CREATE USER xiaoming IDENTIFIED BY '123456';-- 创建用户
set password = password('123456');-- 改当前用户密码
set password for xiaoming = password('123456');-- 改指定用户密码
rename user xiaoming to xiaoming2;-- 重命名
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';-- 授权
GRANT ALL ON *.* TO 'pig'@'%';
show grant for xiaoming;-- 查询权限
revoke all privileges on *.* from xiaoming2;-- 撤销权限
drop user xiaoming;--删除用户

备份

  • 拷贝物理文件
  • 可视化工具中手动导出
  • 使用命令行mysqldump导出
    • mysqldump -hlocalhost -uroot -proot school student >D:/a.sql
    • source d:/a.sql

规范数据库设计

糟糕的数据库设计

  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦、异常
  • 程序的性能差

良好的数据库设计

  • 节省内存空间
  • 保证数据库的完整性
  • 方便开发系统

设计过程

  • 需求分析：业务、表
  • 概要设计：设计关系ER图

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。而通常用的最多的就是第一范式（1NF，列不可再分）、第二范式（2NF，在1nf基础上，一张表只描述一件事情）、第三范式（3NF，在2nf的基础上，每个字段必须与主键相关，不相关的拆开）

但是阿里要求关联查询的表不得超过三张，意味着表不能分得太细（综合考虑）

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

举例说明：

img

在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

img

可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例说明：

img

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

img img

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：

img

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

img img

这样以来，就满足了第三范式的要求。

ps:如果把上表中的班主任姓名改成班主任教工号可能更确切，更符合实际情况，不过只要能理解就行。

JDBC

while(resultSet.next()){}//这种数据结构都是链表形式

SQL注入问题：JDBC执行sql语句传参时，用Java拼接sql时，不讲武德，本来正规是传参数，结果不按规矩，传了一段拼接得sql进去，以图谋不轨，这个问题是跟网站得安全相关的。PreparedStatement对象就是用来防止sql注入。

数据库连接池

项目中一定需要连接池，自己搞网站，断开了数据库连接再重新建立连接巨慢

  • DBCP
  • C3P0
  • 阿里Druid
posted @ 2020-11-15 17:38  夜雨秋池  阅读(65)  评论(0)  编辑  收藏  举报