MySQL

2022-02-24 11:10:20

MySQL

数据表的类型

关于数据库引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MYISM2倍

MySQL引擎在物理文件上的区别

InnoDB在数据库表中只有一个*.frm文件（表结构定义文件），以及上级目录下的ibdata1文件
MYISM对应文件
- *.frm文件（表结构定义文件）
- *.MYD文件（数据文件）data
- *.MYI文件（索引文件）index

设置数据库表的字符集

不设置的话，会是mysql默认的字符集编码~（不支持中文）

MySQL的默认编码是Lantin1，不支持中文

在my.ini中配置默认的编码(不推荐)

character-set-server=utf8

在建表的时候设置CHARSET=utf8

CREATE TABLE `student` (
    `id` int(10) NOT NULL COMMENT ‘学员id‘,
    `name` varchar(100) NOT NULL COMMENT ‘学员姓名‘,
    `age` int(3) NOT NULL COMMENT ‘学员年龄‘,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

快捷

SHOW CREATE DATABASE `school`;  --查看创建数据库的语句
SHOW CREATE TABLE `student`;  --查看创建表的语句
DESC student;  --显示表的结构

表的相关操作

建表

CREATE TABLE `student` (
    `id` int(10) NOT NULL COMMENT ‘学员id‘,
    `name` varchar(100) NOT NULL COMMENT ‘学员姓名‘,
    `age` int(3) NOT NULL COMMENT ‘学员年龄‘,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改表

ALTER TABLE student1 RENAME AS student;--修改表名
ALTER TABLE student ADD hobby VARCHAR(10);--增加字段
ALTER TABLE student MODIFY hobby INT(10);--修改约束
ALTER TABLE student CHANGE hobby hobby1 INT(11);--修改字段名及约束

UPDATE `student` SET `name` = ‘niyun‘,hobby1=‘2‘ WHERE `id`=1011;

面试题：change和modify的区别？

同：均可用于alter语句中，修改字段属性；

异：change可用于字段重命名，句式与modify也不同（多输一个字段名）：

alter table 表名 change 旧字段名 新字段名 字段属性；
alter table 表名 modify 字段名 字段属性；

modify和change都可以修改表的定义，但是change却需要写两次列名，不是很方便。但是change的优点是可以修改列名称，modify则不能。

插入数据

INSERT INTO `student`(`id`,`name`,`age`,`hobby1`) VALUES (1005,‘zhangsan‘,13,1),(1006,‘lisi‘,13,2);

操作符返回布尔值

between ... and ...	在某个边缘	[2,5]

删除

CREATE TABLE `test` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学员id‘,
    `coll` VARCHAR(20) NOT NULL COMMENT ‘学员姓名‘,
    PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
INSERT INTO test(coll) VALUES (‘1‘),(‘2‘),(‘3‘);
DELETE FROM test;
TRUNCATE test;

TRUNCATE和DELETE区别：

相同点：都能删除数据，都不会删除表结构（drop语句删除表结构及所有数据，并将表所占用的空间全部释放）
不同：
- TRUNCATE 重新设置自增列（建表时定义的AUTO_INCREMENT=1001失效，从1开始）
- TRUNCATE 不会影响事务

了解即可：DELETE删除的问题，重启数据库现象

InnoDB 自增列会重新从1开始（存在内存当中的，断电即失）
MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）

DQL查询数据（最重点）

DQL（Data Query Language）：

SELECT VERSION();  -- 查询版本
SELECT 100*3-1 AS 计算结果;  -- 用来计算（表达式）
SELECT @@auto_increment_increment;  --查询自增的步长（变量）

-- 学员成绩+1分查看
SELECT age+1 FROM student;

联表查询

SELECT age+1 FROM student;
SELECT studentNo,studentName,gradeName
FROM student s
INNER JOIN grade g -- LEFT JOIN/RIGHT JOIN
ON s.gradeId = g.gradeId;

排序

select * from tableName limit i,n
# tableName：表名
# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
# n：为查询结果返回的数量
# i与n之间使用英文逗号","隔开
#limit n 等同于 limit 0,n
# 查询10条数据，索引从0到9，第1条记录到第10条记录
select * from t_user limit 10;
select * from t_user limit 0,10;

# 查询8条数据，索引从5到12，第6条记录到第13条记录
select * from t_user limit 5,8;

ORDER BY关键字用于对结果集进行排序。但是ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，需用 DESC 关键字

常用函数

ceiling(9.4) -- 向上取整（9.4结果为10）
floor() -- 向上取整（9.4结果为9）
sign(10)  -- 判断一个数的符号  0返回0，负数返回-1，正数返回1
//字符串函数
SELECT CHAR_LENGTH(‘哈哈哈哈‘)  -- 返回字符串长度
SELECT CONCAT(‘w‘,‘e‘,‘lcome‘)  --拼接字符

SELECT INSERT(‘我爱编程‘,1,2,‘我们超级爱‘)  -- 从第1个字开始，代替长度2
输出：我们超级爱编程

SELECT INSTR(‘kuangshen‘,‘u‘); -- 返回第一次出现的子串的索引
SELECT SUBSTR(‘狂神说坚持就能成功‘,4,6); -- 从第4个开始截取，截取长度为6,不写长度默认到最后

-- 获取当前日期 2021-03-27
SELECT CURRENT_DATE();
SELECT CURDATE();  
-- 当前时间 2021-03-27 15:01:34 
SELECT NOW();
-- 本地时间 2021-03-27 15:02:28
SELECT LOCALTIME();
-- 系统时间 2021-03-27 15:03:48
SELECT SYSDATE();
-- 年 月 日 时 分 秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统用户  root@localhost
SELECT SYSTEM_USER();
SELECT USER();
-- 系统版本 5.7.19
SELECT VERSION();

聚合函数

SELECT COUNT(`id`) FROM student; -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
性能：count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)

count(primary key)：遍历整个表，把主键值拿出来，累加；
count(1)：遍历整个表，但是不取值，累加；
count(非空字段)：遍历整个表，读出这个字段，累加；
count(可以为空的字段)：遍历整个表，读出这个字段，判断不为null累加；
count(*)：遍历整个表，做了优化，不取值，累加。

ACID

Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别	脏读	丢失更新	不可重复读	幻读	并发模型	更新冲突检测
未提交读：Read Uncommited	是	是	是	是	悲观	否
已提交读：Read commited	否	是	是	是	悲观	否
可重复读：Repeatable Read	否	否	否	是	悲观	否
可串行读：Serializable	否	否	否	否	悲观	否

事务

模拟事务提交（银行）

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO ACCOUNT(`name`,`money`) VALUES(‘A‘,2000.00),(‘B‘,10000.00);

-- 模拟转账事务
SET  autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开启一个事务（一组事务）

UPDATE `account` SET money=money-500 WHERE `name`=‘A‘; 
UPDATE `account` SET money=money+500 WHERE `name`=‘B‘; 

COMMIT;
ROLLBACK;
SET  autocommit = 1; -- 开启自动提交

索引

索引的分类

主键索引
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识位
常规索引
全文索引

基础语法

SHOW INDEX FROM STUDENT;
-- 增加一个全文索引
ALTER TABLE 表名 ADD 索引类型 index 索引名（列名）;
ALTER TABLE school.student ADD FULLTEXT index `studentName`(`studentName`);
-- explain 分析sql执行的情况
EXPLAIN SELECT * FROM STUDENT; -- 非全文索引
EXPLAIN SELECT * FROM STUDENT WHERE MATCH(studentName) AGAINST(‘刘‘);

插入100万条数据

tinyint一个字节 smallint 两个字节 MEDIUMINT三个字节

DROP TABLE IF EXISTS app_user;
CREATE TABLE `app_user` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT ‘‘,
    `email` VARCHAR(50) NOT NULL,
    `phone` VARCHAR(20) DEFAULT ‘‘,
    `gender` TINYINT(4) UNSIGNED DEFAULT ‘0‘,
    `password` VARCHAR(100) NOT NULL DEFAULT ‘‘,
    `age` TINYINT(4) DEFAULT NULL,
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

DELIMITER $$  -- 修改结束标识符
DROP FUNCTION IF EXISTS `mock_data`$$
CREATE FUNCTION mock_data()
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),‘22222222@qq.com‘,CONCAT(‘18‘,FLOOR(RAND()*(1000000000-9999999999)+1000000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
        SET i = i+1;
    END WHILE;
    RETURN i;
END;$$
DELIMITER ;

SELECT mock_data();

SELECT COUNT(*) FROM app_user;
SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- 0.467 sec
SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- 0.473 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- rows=993330
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- 0.001 sec
SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- 0.001 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = ‘用户9999‘; -- rows=1

MySQL索引背后的数据结构及算法原理

JDBC

jdbc.jpg:

四大对象：

DriverManager：驱动管理对象

Class.forName(“com.mysql.jdbc.Driver”)底层实际通过静态代码块java.sql.DriverManager.registerDriver(new Driver())；
Connection：数据库连接对象

语法：jdbc:mysql://ip地址(域名):端口号/数据库名称

例如：jdbc:mysql://localhost:3306/school
Statement：执行sql的对象

int executeUpdate(String sql) ：执行DML（insert、update\delete）语句、DDL(create，alter、drop)语句，
返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功，返回值>0的则执行成功，反之，则失败。

ResultSet executeQuery(String sql) ：执行DQL（select)语句，返回值：结果集（ResultSet 对象）

boolean execute(String sql) ：可以执行任意的sql 。

ResultSet：结果集对象，封装查询结果。

boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回false，如果不是则返回true
getXxx(参数):获取数据
Xxx：代表数据类型如： int getInt() , String getString()

事务案例：简单银行事务.jpg

//银行事务操作简单案例
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库自动提交
            conn.setAutoCommit(false);//开启事务
            st = conn.createStatement();
            String sql1 = "update account set money = money-100 where NAME = ‘A‘";
            st.executeUpdate(sql1);
   
            String sql2 = "update account set money = money+100 where NAME = ‘B‘";
            st.executeUpdate(sql2);
            
            conn.commit();
            System.out.println("更新成功");
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入

sql存在漏洞，会被共计==攻击导致数据泄露，SQL会被拼接 or

PreparedStatement

PreparedStatement继承Statement，PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符，假设其中存在转义字符，比如说 ‘ 会被直接转义
IDEA连接数据库
- Data Source Properties -->Schemas -->选择数据库

连接池

编写连接池，实现一个接口 DataSource

开源数据源实现（拿来即用）

DBCP

C3P0

Druid（阿里巴巴）

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

C3P0

c3p0-0.9.5.5、mchange-commons-java-0.2.19

结论

无论使用什么数据源，本质还是一样的，DataSource接口不会变

MySQL

码农公寓

MySQL

数据表的类型

表的相关操作

DQL查询数据（最重点）

ACID

事务

索引

JDBC

SQL注入

连接池

相关文章