MySQL学习

1.初始MySQL

---

2.操作数据库

---

3.MySQL数据管理

3.1 外键(了解)

方式1：在创建表的时候，增加约束(较为麻烦)

CREATE TABLE IF NOT EXISTS `xxx`(
...
...
...
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREING KEY(`gradeid`) REFERENCES grade(`gradeid`)
)ENGINE=INNODB DEFAULT=utf8

• 删除有外键关系的表的时候，必须先删除引用别人的表(从表)，再删除被引用的表(主表)

方式二：创建表成功后，添加外键约束

ALERT TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

• 以上操作都是物理外键，数据库级别的外键，不建议使用！(避免数据库过多造成困扰)
• 数据库是单纯的表，仅用来存数据，只有行(数据)和列(字段)

---

3.2 DML语言(全部记住)

数据库的意义：数据存储与管理

DML 语言：即数据库操作语言

• insert
• update
• delete

3.3 添加

insert

insert into `student` (`name`,`pwd`,`gender`,gradeid) VALUES('张三','123456','男','1')

• 语法：insert inro 表名([字段1,字段2,字段3,...]) values ('值1'),('值2'),(‘值3’,......)
  注意事项：
  1.字段与字段之间用英文逗号隔开
  2.字段可以省略，但后面的值必须要一一对应，不可少
  3.可以同时插入多条数据，VALUES后面的值，需要使用,隔开即可，(),(),(),()

3.4 修改

update 修改谁 （条件） set原来的值=新值

--修改单个  UPDATE `student` set name='czx' WHERE id=1;
--修改多个  UPDATE `student` set name='czx',email='xxxxxxxx' WHERE id=1;

• 语法：update 表名 set column_name =value,... where 条件
• 条件：where 字句 运算符 id等于某个值,大于某个值,在某个区间内修改…
• 操作符会返回boolean值
  注意事项：
• column_name为数据库的列，尽量带上``
• 条件，筛选的条件，没有指定则会修改所有的列
• value是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

3.5 删除

delete 命令

语法：delete from 表名 [where 条件]

--删除数据(避免这样写)
DELETE FROM `student`;
--删除指定数据
DELETE FROM `student` where id=1;

TRUNCATE 命令(完全清空一个数据库表，表的结构和索引约束不会变！)

TRUNCATE `student`

二者区别：

• 相同点:都可以删除数据，且不会删除表结构
• 不同：1.TRUNCATE重新设置自增列，计数器归零
  2.TRUNCATE 不会影响事务

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

• 1.innoDB 自增会从1开始（存在内存中的，断电即失）
• 2.MyISAM 继续从上一个自增开始（存在文件中的，不会丢失）

---

4.DQL查询数据(最重要)

4.1 指定查询字段

--查询全部学生
select * from student
--查询指定字段
select `studentName`,studentNo from student
-- 别名，给结果起个名字 AS 可以给字段起别名，也可以给表起别名
select `studentName` AS 姓名,`studentNo` AS 学号 from student
--函数 Concat(a,b)
select concat('姓名： ',studentName) AS 姓名 from student

语法：select 字段 ...... from 表
列名不是那么见名知义时，可以起别名AS ，字段名 AS 别名

去重 distinct
select distinctstudentidfrom result

数据库的列

select verson() //查询数据库版本
select 100*3-1 AS 计算结果  --用来计算，表达式
select @@auto_increment_increment --查询自增的步长（变量）
---学员考试成绩+1分查看
select `studentNo`,studentResult+1 AS '提分后' from result

数据库中表达式：文本值、列、null、函数、计算表达式、系统变量

4.3 where条件子句

作用：检索数据中符合条件的值

逻辑运算符
and && -----a and b a&&b 与
or || -----a or b a||b 或
Not ! ----- not a !a 非

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	操作符为null，结果为真
IS NOT NULL	a is not null	操作符为not 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，其中某个中，结果为真

Like:
--查询刘姓的同学
select `studentid`,`studentName` from `student` where studentName like '刘%'
--查询刘姓的同学，后面只有一个字
select `studentid`,`studentName` from `student` where studentName like '刘_'
--查询刘姓的同学，后面只有两个字
select `studentid`,`studentName` from `student` where studentName like '刘__'
--查询名字中有嘉字的同学
select `studentid`,`studentName` from `student` where studentName like '%嘉%'

in(具体的一个或多个值):
--查询1001，1002，1003的学生
select `studentid`,`studentName` from `student` where studentid IN (1001,1002,1003);
--查询在北京的学生
select `studentid`,`studentName` from `student` where studentid IN (1001,1002,1003);
select `studentid`,`studentName` from `student` where address IN ('北京');

--null/not null:
--查询地址为空的学生 null ''
select `studentid`,`studentName` from `student` where address='' or IS NULL
--查询有出生日期的学生 not null 
select `studentid`,`studentName` from `student` where BornDate IS NOT NULL

4.4 联表查询

--join (连接的表) on（判断的条件）连接查询
--where 等值查询
--查询参加考试的同学
select s.studentNo,s.studentName,r.StudentResult from student AS s inner join result AS r where s.studentNo=r.studentNo;
--Right JOIN
select s.studentNo,s.studentName,r.StudentResult from student AS s RIGHT join result AS r ON s.studentNo=r.studentNo;
--Left JOIN
select s.studentNo,s.studentName,r.StudentResult from student AS s LEFT join result AS r ON s.studentNo=r.studentNo;

操作	描述
Inner join	如果表中至少有个匹配，则返回行
left join	即使右表中没有匹配，也会返回左表中所有的值
right join	会返回右表中所有的值，即使左表中没有匹配

--我要查询哪些数据select ...
--从那几个表中查FROM表XXX join连接的表 ON 交叉
--假设存在多张表查询，慢慢来，先查询两张表在慢慢增加
--查询学生id，姓名，考试名称和成绩，涉及三张表t_stu、t_fenshu、t_shiti
select s.stu_id,stu_realname,shiti_name,fenshu from
 t_stu AS s RIGHT JOIN t_fenshu AS r ON s.stu_id=r.stu_id 
 INNER JOIN t_shiti AS t ON r.shiti_id=t.shiti_id

自连接（了解）

自己的表和自己的表连接，即一张表拆为两张一样的表即可

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps设计

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps设计

--查询父子信息：把一张表看成两个一摸一样的
select a.categoryName AS '父栏目',b.categoryName AS '子栏目'
from category AS a,category AS b
where a.categoryid=b.pid

4.5 分页和排序

排序

--排序  升序:ASC 降序:DESC
select * from student order by DESC/ASC

分页

语法:limit 起始值，页面的大小
limit(查询的起始值下标，pageSize)

4.5 子查询

where(这个值是计算出来的)

select studentNo,subjectNo,studentResult from result where subjectNo=(
select subjectNo from subject where subjectName='xxx'
)

5. MySQL函数

5.1常用函数

--数学函数：
select ABS() //绝对值
select CEILING() //向上取整
select FLOOR() //向下取整
select RAND() //返回一个0-1之间的随机数
select SIGN() //判断一个数的符号 ，负数返回-1，正数返回1
--字符串函数
select CHAR_LENGTH //字符串长度
select CONCAT() //拼接字符串
select INSERT('',1,2,'') //某个位置开始替换某个长度
select LOWER,UPPER //转化大小写
select INSTR('asfghd','h') //返回第一次出现的子串的索引
select REPLACE('','','') //替换原来的字符串
select substr('',4,6) //返回指定的字符串 
select REVERSE() //反转
--时间和日期函数
select CURRENT_DATE() //获取当前日期
select CURDATE() --获取当前日期
select NOW() //获取当地时间
select LOCALTIME() //本地时间
select SYSDATE() //系统时间
--系统
select SYSTEM_USER()
select USER()
select VERSION()

5.2 聚合函数(常用)

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

--统计表中数据
select COUNT(studentName) from student; //会忽视所有的null值
select COUNT(*) from student; //不会忽视所有的null值
select COUNT(1) from student; //不会忽视所有的null值 本质，计算行数
--总和、平均分、最高最低分
select SUM(`studentResult`) AS 总和 from result
select AVG(`studentResult`) AS 平均分 from result
select MAX(`studentResult`) AS 最高分 from result
select MIN(`studentResult`) AS 最低分 from result

5.3 数据库级别的MD5加密（扩展）

MD5不可逆，具体的MD5的值是一样的

---

6.事务

例如：
1.SQL执行,A给B转账 A:1000 —>200 B:200
2.SQL执行，B收到A的钱 A:800 —> B:400
核心：将一组SQL放在一次批次中执行

事务原则：ACID原则 原子性、一致性、隔离性、持久性

• 原子性
  要么都成功，要么都失败
• 一致性
  事务前后的数据完整性要保持一致
• 持久性
  事务一旦提交则不可逆，被持久化到数据库中
• 隔离性
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰

隔离所导致的一些问题

脏读：指一个事务读取了另一个事务未提交的数据
虚读(幻读)：指一个事务内读取了别的事务插入的数据，导致前后读取不一致

执行事务，MySQL是默认开启事务自动提交的

set autocommit=0 //关闭
set autocommit=1 //开启

--手动处理事务
set autocommit=0
--事务开启
start TRANSACTION //标记一个事务的开始，从这个之后的sql都在同一个事务内
insert xx
insert xx
--提交：持久化（成功）
COMMIT
--回滚：回到原来的样子
ROLLBACK
--事务结束
set autocommit=1//开启自动提交

---

7.索引

索引(index)是帮助MySQL高效获取数据的数据结构

7.1索引分类

• 主键索引 （PRIMARY KEY）
  唯一标识，主键不可重复
• 唯一索引（UNIQUE KEY）
  避免重复的列出现，唯一索引可以重复，多个列都可标注为唯一索引
• 常规索引（KEY/INDEX）
  默认的，index或key关键字来设置
• 全文索引（FullText）
  特定的数据库引擎下才有，MyISAM，用来快速定位数据

7.2测试索引

--插入100万条数据
DELIMITER $$ --写函数之前要写，标志
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),'1586935242@qq.com',concat('18',
      FLOOR(RAND()*(999999999-100000000)+100000000)),
      FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100))
      SET i=i+1;
    END WHILE;
    RETURN i;
END;

索引在小数据时用处不大，但在大数据时用处明显

7.3 索引原则

• 索引不是越多越好
• 不要对进程变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在用来查询的字段上

8.权限管理和备份

8.1 用户管理

SQL命令操作

用户表：mysql.user
本质：对表进行增删改查

--创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
select user czx IDENTIFIED BY '123456'
--修改当前用户密码
set PASSWORD=PASSWORD(' ')
--修改指定用户密码
set PASSWORD FOR czx=PASSWORD(' ')
--重命名 RENAME USER 原名 TO 新名
RENAME USER czx TO czx1
--用户授权 ALL PRIVILEGES全部的权限,库.表
GRANT ALL PRIVILEGES ON *.* TO 
--查询权限
SHOW GRANTS FOR czx //查看指定用户
SHOW GRANTS FOR root@localhost
--撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM czx
--删除用户
DROP USER czx

---

8.2 MySQL备份

为什么备份：

• 保证重要的数据不丢失
• 数据转移

备份方式：

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 使用命令行到处，在mysqldump命令行使用
  mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3... > 物理磁盘位置/文件名

9.规范数据库设计

9.1 为什么设计？

数据库叫复杂时就需要设计了
糟糕的数据库：

• 数据冗余，浪费空间
• 数据库插入和删除麻烦且可能产生异常
• 程序性能差

良好的数据库：

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

软件开发中，关于数据库设计

• 分析需求：分析业务和需要处理的数据库需求
• 概要设计：设计关系图E-R图

设计数据库的步骤

• 收集信息，分析需求
• 标识实体（需求落地到每个字段）
• 标识实体之间的关系

9.2三大范式（记）

是什么要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  无法正常显示信息
• 删除异常
  丢失有效信息

三大范式（规范性和性能问题）

第一范式(1NF)
要求数据库表每一列都是不可分割的原子数据项，即原子性
第二范式(2NF)
前提：满足第一范式
非码属性必须完全依赖于候选码，确保数据库表中每一列和主键相关，而不能只与主键某一部分相关，即每张表只描述一件事
第三范式(3NF)
前提：满足第一范式和第二范式
在第二范式基础上，任何非主属性不能依赖于其他非主属性，要求确保数据表中每一列数据都和主键直接相关，而不能间接相关
规范性与性能问题（了解）
关联查询的表不能超过三张

• 考虑商业化的需求和目标,(成本，用户体验)数据库性能更加重要
• 在规范性能的问题时，需要适当考虑规范性
• 故意给某些表增加冗余字段（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降为小数据量的查询：索引）

10.JDBC(重点)

10.1数据库驱动

驱动：声卡、显卡、数据库
JDBC是用来Java操作数据库规范
应用程序–>JDBC–>数据库驱动–>数据库

10.2第一个JDBC程序

步骤：

//1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcStudy?
        useUnicode=true&&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String password="123";
        //3.连接成功数据库对象
        Connection connection= DriverManager.getConnection(url,username,password);
        //4.执行SQL读写
        Statement statement=connection.createStatement();
        //5.执行sql
        String sql="select * from users";
        ResultSet resultSet= statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();

10.3 statement对象

作用：用于向数据库发送SQL语句
executeUpdate：返回一个整数
executeQuery：用于向数据库发送查询信息

SQL注入问题

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

10.4 PreparedStatement对象

可以防止SQL注入，效果更好
Preparement防止SQL注入的本质，把传递进来的参数当作字符
假设其中存在转义字符，则会被直接转移

Connection connection=null;
        PreparedStatement pstm=null;
        ResultSet resultSet=null;
        try {
            connection=JdbcUtils.getConnection();
            String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
            pstm= connection.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动赋值
            pstm.setInt(1,4);
            pstm.setString(2,"czx");
            pstm.setString(3,"158631");
            pstm.setString(4,"555896@qq.com");
            pstm.setDate(5,new java.sql.Date(new Date().getTime()));

            int i= pstm.executeUpdate();;
            if (i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,pstm,null);
        }

10.5事务

要么成功，要么失败

ACID原则

• 原子性
  要么都成功，要么都失败
• 一致性
  事务前后的数据完整性要保持一致
• 持久性
  事务一旦提交则不可逆，被持久化到数据库中
• 隔离性
  事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰

10.6数据库连接池

数据库连接—执行完毕—释放 连接–释放 浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
最小连接数 10
最大连接数 15 业务最高承载上限
编写连接池，实现一个接口DataSource

开源数据源实现

DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池后，在编写项目时就无需写连接数据库代码

DBCP

commons-dbcp-1.4 commons-pool-1.6

C3P0

c3p0-0.9.5.5 mchange-commons-java-0.2.19

结论

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