MySQL学习笔记

Mysql

---

后端，前端代码网站：antdesign，element

1.1为什么学习数据库

1.岗位需求，现在社会中各个岗位都需要mysql数据库精通。

2.数据库是所有软件体系最核心的存在DBA

3.大数据时代

1.2什么是数据库

数据库DB（DataBase）

概念：数据仓库软件，安装在操作系统上（windows、linux、mac）

作用：存储数据 ，管理数据

1.3数据库分类

关系型数据库：（SQL）

• Mysql、Oracle、sql server，DB2，sqlLite

• 通过表与表之间的，行与列之间的关系进行数据存储

非关系型数据库：（NOSQL）

• Redis 、MongDB

• 非关系型数据库，对象存储，通过对象自身的属性来决定。

DBMS(数据库管理系统)

• 数据库的管理软件、科学有效的管理我们的数据，维护和获取数据

• Mysql，数据库管理系统

1.4Mysql简介

是一个关系型数据库管理系统，开源软件

体积小、速度快、总体拥有成本低

1.5连接数据库

命令行连接

mysql -uroot -p（密码）

show databases；--查看数据库

mysql>	use school  --切换数据库use数据库名

show tables；--查看数据库中所有的表
describe	student；--显示数据库中的所有表的信息

create databaes 名称;--创建一个数据库
 exit； --退出连接
 “--”--单行注释
 /**/多行注释

数据库的语言：

1. DDL定义语言

2. DML操作语言

3. DQL查询语言

4. DCL控制语言

2.操作数据库

操作数据库>操作数据库中的表>操作数据表中表的数据

2.1操作数据库

1. 创建数据库

   create database [if not exists] name

2. 删除数据库

   drop database[if exists] name

3. 使用数据库

   如果表名或者字段名是特殊字符就需要带‘’

   use 'name'

4. 查看数据库

   show database --查看所有的数据库

   5、显示表的结构

   DESC student

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• int 标准的整数 4个字节

• mediumint 中等大小的数据 3个字节

• bigint 较大的数据 8个字节

• float 浮点数 4个字节

• double 浮点数 8个字节

• decimal 字符串的浮点数

字符串

• char 字符串固定大小的 0-255

• varchar 可变字符串 0-65535 常用string

• tinytext 微型文本 2的8次方-1

• text 文本串 2的16次方-1 保存大文本

时间日期

• date YYYY-MM-DD，日期

• time HH:mm：ss 时间格式

• datetime YYYY-MM-DD HH：mm:ss 最常用的时间格式

• timestamp 时间戳 某某时间到现在的毫秒

• year年份

NULL

• 没有值，未知

• 注意，不要使用NULL运算，结果为NULL

2.3数据库的字段属性

Unsigned：

• 无符号的整数

• 声明了该列不能生明为负数

zerofill：

• 0填充的

• 不足的位数用0来填充，例：int（3），5--005

自增：

• 自动在上一条记录的基础上加1

• 通常用来设计唯一的主键-index，必须是整数类型

• 可以自定义设计主键自增的起始值和步长

非空：

• 假设设置为not null ，如果不给它赋值就会报错

默认：

• 设置默认的值

• 如果不指定该值，则会有默认的值

2.4创建数据库表

所有的符号都是英文字符

AUTO_INCREMENT 自增

字符串使用，单引号' '括起来

所有的语句后都要加入,(英文逗号)，最后一个语句不用加

primary key主键 一般一个表中只有一个主键。

CREATE TABLE [IF NOT EXISTS] '表名'(
      '字段名' 列类型 [属性] [索引] [注释 ],
    '字段名' 列类型 [属性] [索引] [注释 ],
    '字段名' 列类型 [属性] [索引] [注释 ],
    ……
    '字段名' 列类型 [属性] [索引] [注释 ]
)[表类型][字符集设置][注释]

2.5数据库的类型

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

MYISAM: 节约空间，速度较快

• *.frm 表结构定义文件

• *MYD 数据文件

• *.MYI 索引文件

INNODB： 安全性高，事务的处理，多表多用户操作

• 数据库表中只有一个*.frm文件

数据库的字符编码

CHARSET=utf8

不设置的话，会是默认的编码Latin1，不支持中文

2.6修改和删除表

修改

--修改表名：ALTER TABLE 旧表名 RANAME AS 新表名
--增加表的字段 ： ALTER TABLE 表名 ADD 字段名 列属性[]
--修改表的字段：1、修改约束  ALTER TABLE 表名 MODIFY 字段名 列属性[]
               2、字段重命名 ALTER TABLE 表名 CHANGE 旧名 新名 列属性[] 

删除

DROP TABLE IF EXISTS 表名--删除表
ALTER TABLE 表名 DROP 字段名--删除表的字段

​

所有的创建和判断都要加上判断，一免报错

3. MySQL数据管理

外键：

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY（作为外键的列） REFERENCES 哪一个表（那一个字段）

3.1DML（数据操作）语言

数据库意义：数据存储，数据管理

• Insert

• update

• delete

3.2插入（添加）

insert into 表名([字段名1,字段名2,字段名3]) values('值1'),('值2'),('值3'……)
--由于主键自增，可以省略（不写表的字段，他就会一一匹配）   
--如果有多个不同的数据插入,需要将各个数据对应输入

3.3 修改

--修改有条件的
UPDATE '表名' SET '字段名'='要输入的数值' WHERE 要条件字段名='指定的数值'
--不指定条件的情况下，会改动所有表
UPDATE '表名' SET '字段名'='要输入的数值'
--修改多个属性用逗号隔开
UPDATE '表名' SET '字段名1'='要输入的数值','字段名2'='要输入的数值' ,……WHERE 要条件字段名='指定的数值'
--通过多个条件来修改
UPDATE UPDATE '表名' SET '字段名1'='要输入的数值','字段名2'='要输入的数值' ,……WHERE 要条件字段名='指定的数值'AND 要条件字段名='指定的数值'

条件：where后的条件如果是其他运算符的话，就在范围内修改

操作符	含义	范围	结果
=	等于	1=2	false
<>或！=	不等于	4<>5	true
>
<
<=
>=
BETWEEN...AND...	在某个范围内	[2,5]
AND	多个条件都成立
OR	成立一个就可

3.4删除

DELETE

不会影响自增

语法：

delete  from '表名'  where [条件]--指定删除
delete  from '表名'--全部删除

TRUNCATE

自增会清零

作用：完全清空一个数据表，表的结构和索引不会变

truncate '表'--清空表

区别

• 相同点：都会删除数据，都不会破坏表的结构

• 不同点：truncate 重新设置自增列，计数器回归零；会不影响事务

4.DQL查询数据

4.1DQL

(date Query language:数据库查询语言)

• 所有的查询语句都要使用select

• 数据库中最核心的语言，最重要的语句

• 使用频率最高的语句

select的语法

SELECT [ALL|DISTINCT]
{*| TABLE.*|[TABLE.FIELD1[AS ALIAS1][,TABLE.FIELD2[AS ALIAS2]][...] ]}
FROM TABLE_NAME[AS TABLE_SLIAS]
   [LEFT |RIGHT|INNER JOIN TABLE_NAME2]--联合查询
   [WHERE...]--指定结果需满足的条件
   [GROUP BY ...]--指定结果按照哪几个字段来分组
   [HAVING]--过滤分组的记录必须满足次要条件
   [ORDER BY ...]--指定查询记录按照一个或者多个条件排序
   [LIMIT {[OFFSET,]ROW_COUNT|ROW_COUNTOFFSET OFFSET}];--指定查询的记录从那条到那条
   --[]代表可选。{}代表必选。

4.2查询所有字段

select * from 表--查询所有的数据
select '指定的字段1','指定的字段2' from 表--查询指定的字段
select '指定的字段1'AS  新名字,'指定的字段2' AS 新名字 from 表 AS 新表名--别名，给结果起一个名字
select concat('加入的字符',列名) AS 新名字 FROM 表--函数concat（a，b）

去重distinct

select ditinct '列名' from 表--发现重复的数据，去除 

数据库的列

select version()--查询系统版本    
select 100*3-1 AS 计算结果--用于计算
select @@auto_increment_increment --查询自增的步长

4.3where条件子句

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

搜索的条件由一个或者多个表达式组成！结果 布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，同时为真，才为真
or ||	a or b a||b	逻辑或，一方为真即为真
Not	not a ! a	逻辑非，真为假，假为真

查询成绩在95-100之间
SELECT studentno,'studentResult' from result  where studentresult>=95 and studentresult<=100
模糊查询（区间）
select studentno,'studentresult' from result where studedntresult between 95 and 100
除了1000号的学生之外的的成绩
select studentno,'studentresult' from result where NOT=1000

模糊查询：比较运算符

运算符	语法	描述
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……之间的某一个值则为真

--查询姓刘的同学，名字后面只有一个字,如果是两个字就是'刘__'
select 'studentno','studentname' from 'student' where studentname like '刘_'
--查询名字带有“家”子的信息
select 'studentno','studentname' from 'student' where studentname like '%家%'
--查询1001，1002，1003号同学的信息
select 'studentno','studentname' from 'student' where studentno In (1001,1002,1003)
--查询在北京的学生
select 'studentno','studentname' from 'student' where 'Address' In (1001,1002,1003)
--查询地址为空的的学生
select 'studentno','studentname' from 'student' where Address='' or Address is null

4.4联表查询

多个表查询

7种方式查询

INNER JOIN：（只返回两个表中联结字段相等的行）

select s.student.no,studentname,subjectno,studentruselt
from student as s
inner join result as r
on s.studentno=r.studentno

RIGHT JOIN:（返回包括左表中的所有记录和右表中联结字段相等的记录）

select s.student.no,syudentname,subjectno,studentruselt
from student as s
right join result as r
on s.studentno=r.studentno
--以右表为基准

LEFT JOIN:（返回包括右表中的所有记录和左表中联结字段相等的记录）

select s.student.no,syudentname,subjectno,studentruselt
from student as s
left join result as r
on s.studentno=r.studentno
--以左边的表为基准
--on是连接查询
--where是等值查询

多表查询，先查询两张表然后再+1表查询

我要查询哪些数据 则：select... from 表 XXX(right\left\inner...) JOIN 连接的表 on 交叉条件

select s.studentno , studentname,subjetname,'studentresult'
from student as s
right join result as r
on r.studentno =s.studentno
inner join 'subject' sub
on s.studentno=sub.subjectno

自链接

自己和自己的表连接

核心：一张表拆为两张一样的表

原表：

拆分成:

父类表：

categoriyid	categoryname
3	软件开发
5	美术设计
2	信息技术

子类表：

categoryid	pid	categoryname
4	3	数据库
8	2	办公信息
6	3	web技术
7	5	ps技术

categoryid是自身的id，pid是父id，例：数据库和web技术pid=3，所以它属于categoryid=3的内容。

实际表：

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

--查询父子信息
select a.categoryname , b.categoryname
from category as a, category as b
where a.'categoryid'=b.'pid'

4.5分页和排序

分页 limit ；排序 order by

排序

升序：ASC 降序：DESC

order by 要排序的列名 ASC/DESC

分页

为什么分页：缓解数据库压力

--每页显示五条数据
--语法：limit 起始页 ，页面的大小
limit 0，5--第一页（1-1）*5
limit 5，5--第二页（2-1）*5
limit 10，5--第三页（3-1）*5
--第n页 （n-1）*5

4.6子查询

where（该值为计算出来的)

本质：where（select语句）

inner join写法

嵌套（子查询）

5.MySQL函数

5.1常用函数

数学运算

SELECT ABS(-8) --绝对值
select ceiling（9，4）--向上取整
select floor（9，4）--向下取整
select RAND()--返回一个0-1之间的随机数
select sign()--判断一个数的符号 即负数为-1，正数为1，0为0

字符串函数

select char_length('你好！')--字符串长度
select concat('你'，'好')--拼接字符串
select insert('我爱编程'，1,2,'超级热爱')--插入，替换。结果为：超级热爱编程。1：第一个字符，2：替换指定字符后的两个字符
select lower--转小写
select upper--转大写
select instr('nihao','h')--结果为3。返回第一次出现该字符串的位置
select substr('你好呀Mysql'，2，4)--结果为"好呀My"从第2个开始截取四个
select reverse('你好啊')--反转结果为“啊好你”

查询 时间和日期

select current_date()--获取当前日期
select now()--获取当前时间，精确到秒
select localtime()--本地时间

5.2聚合函数

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

SELECT COUNT(`STUDENTNAME`)FROM STUDENT;--COUNT(字段)，会忽略所有的null值。没有主键的话速度最快
SELECT COUNT(*)FROM STUDENT;--计算行数
SELECT COUNT(1)FROM STUDENT;--计算行数
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加密

主要增强算法复杂度和不可逆性

update  test set pwd=md5(pwd) where id =1;--指定密码进行加密
update  test set pwd=md5(pwd) --全部加密
insert into student vlaues(4,'xiaoming',md5('123456'))--插入的时候加密
select * from student where 'name'='yao' AND pwd=md5('123456')--将用户传递的密码进行加密，然后对比加密后的值

6.事务

6.1什么是事务

要么都成功要么都失败

事务原则

ACID原则：原子性，一致性，隔离性，持久性

原子性：要么都成功，要么都失败

一致性：事务前后整体数据不变

持久性:事务一旦提交则不可逆 永久保存在数据库中，没有提交的话数据就一直会存在原先的位置

隔离性：并发访问，互不干扰

隔离会导致的问题

脏读：指一个事务读取了另一个事务没有提交的数据

不可重复读：在读取数据时，多次读取数据都不同

虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

start transaction--事务开启
commit--提交（成功的话）
rollback--回滚 ,撤回（失败的话）
set autocommit=1--开启自动提交
set autocommit=0--关闭自动提交

默认为自动提交，首先要关闭自动提交，然后开启一个事物，然后提交，提交成功就开启自动提交，如果失败就回滚到原先的数据。

7.索引

Mysql高效获取数据的数据结构

7.1索引的分类

• 主键索引（primary key）

  • 唯一的标识，不可重复，只有一个主键

• 唯一索引（unique key）

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引

• 常规索引（key）

  • 默认的，index，key关键字来设置

• 全文索引（fulltext）

  • 在特定的引擎中才有，Myisam

  • 快速定位数据

索引的使用

1、在创建表的时候给字段增加索引

2、在创建完毕后，增加索引

显示所有的索引信息

show index from student--显示所有的索引信息
alter table `student` ADD FULLTEXT INDEX `STUDENTNAME`(`STUDENTNAME`);--增加一个全文索引（索引名） 列名
explain select * from student--explain分析sql的执行的状况   

7.2测试索引

--id_表名_字段名 
create index 索引名 on 表（'字段'）--创建常规索引 

索引在小数据的时候用处不大，但是在大数据查询的时候用处很大。

7.3索引原则

• 索引不是越多越好

• 不要对进程变动数据加索引

• 小数据量的表不需要加索引

• 索引一般加在常用来查询的字段上

  索引的数据结构

Btree：INNODB默认的数据结构

8.权限管理和备份

8.1用户管理

create user yaolong identified by '123456'--创建用户
set password =password('123456')--修改当前用户密码
set password for 用户 =   password('123456')--修改指定的用户密码
rename user 旧用户名 to 新名--重命名
grant all privileges on *.* to 用户名--授予全部的权限   库.表
--all privileges 除了能给别人授权，其他的都能做
show grants for 用户名--查看指定用户的权限
revoke all privileges on  *.* from 用户名--撤销权限，revoke哪些权限，在那个库撤销，
drop 用户名--删除用户

8.2数据库备份

为什么要备份？

• 保证数据不丢失

• 数据转移

Mysql数据库备份的方式

• 直接拷贝物理文件（data）

• 在可视化工具中手动导出

• 使用命令行导出mysqldump命令行使用

9.规范设计数据库

9.1为什么要设计数据库

糟糕的数据库设计：

• 数据库冗余，浪费空间

• 数据库插入和删除都会很麻烦

• 程序性能差

良好的数据库的设计：

• 节省存储空间

• 保证数据库的完整性

• 方便我们开发系统

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

• 分析需求：分析业务和要处理的数据库需求

• 概要设计：设计关系图E-R图

设计数据库的步骤：

• 收集信息，分析需求

• 标识实体（把需求落实到每一个字段）

• 标识实体之间的关系（实体与实体有联系）

9.2三大范式

为什么数据规范化？

• 信息重复

• 更新异常

• 插入异常

• 删除异常

三大范式

第一范式（1NF）

要求数据库中的每一列都不可再分

第二范式（2NF）

前提满足第一范式。属性完全依赖于主键，一行中的所有数据，都只能使用一次，不可多次使用

第三范式（3NF）

前提满足前两个范式。

数据中不存在传递关系，每一个属性都跟主键有着直接的关系，而不是间接关系。（即属性不能通过中间属性进行与主键关联）

10.JDBC

10.1数据库驱动

驱动：数据库，声卡，显卡等等

程序必须通过驱动才能和数据库进行连接。

10.2JDBC

SUN公司为啦简化开发人员的（对数据库的统一）操作，提供一个（java操作数据库）的规范，叫做JDBC。

我们只需要完成JDBC的操作即可，mysql驱动都由厂商提供。

数据库驱动包：mysql-connector-java-版本号

10.3第一个JDBC程序

public class JDBC {
    public static void main(String[] args) {
       // DriverManager.registerDriver(new com.mysql.jdbc.Driver());如果是低版本的话就需要注册驱动
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
        //?设置参数 useUnicode=true：支持中文编码。characterEncoding=utf8：设置字符集为utf8
         String username="root";
         String password="yaolong";
         //3.连接成功，数据库对象
        Connection connection= DriverManager.getConnection(url,username,password);
        //4.执行sql对象   statement 执行sql的对象
        Statement statement=connection.createStatement();
        //5.执行sql，可能存在结果，查看结果
        String sql="select * from class";
        ResultSet resultSet=statement.executeQuery(sql);//返回的结果集
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

​

步骤：

1.加载驱动

2.连接数据库DriverManager

3.获取数据库对象Statement

4.获得返回结果集

5.释放连接

DriverManager

 // DriverManager.registerDriver(new com.mysql.jdbc.Driver());如果是低版本的话就需要注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
​
  Connection connection= DriverManager.getConnection(url,username,password);
//connection代表数据库

url

String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";

jdbc：mysql://主机地址：端口号/数据库名?参数1&参数2&参数3

mysql默认3306端口号

时区：TimeZone=UTC

Statement

statement.excuteQuery();//查询操作，并返回结果

statement.excute();//增删改

statement.excuteUpdate();//更新、插入、删除

RusultSet查询的结果集：封装所有的查询结果

获得指定的数据类型

释放资源

就是将所有的链接都关闭

 resultSet.close();
        statement.close();
        connection.close();

10.4Statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想要完成数据库的增删改查，通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CREATE操作

Statement st=conn.createStatement();
String sql="insert into user(...) values(...)";
int num=st.exvuteUpdate(sql);
if(num>0){
   System.out.println("插入成功");
}

DELETE操作

Statement st=conn.createStatement();
String sql="delete from uesr where id=1";
int num=st.exvuteUpdate(sql);
if(num>0){
   System.out.println("删除成功");
}

update操作

Statement st=conn.createStatement();
String sql="update user set name='' where name=''";
int num=st.exvuteUpdate(sql);
if(num>0){
   System.out.println("修改成功");
}

read操作

Statement st=conn.createStatement();
String sql="select * from user where id =1";
ResultSet rs=st.exvuteQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

• 通用代码

public static void main(String arg[]){
Connection conn =null;
Statement st=null;
ResultSet rs=null;
try{
conn =Jdbcunitls.getconnection();//获取数据库连接
st=conn.createStatement();//获取sql执行对象
String sql="sql的操作语句"；
int i =st.excuteUpdate(sql);
if(i>0){
System.out.println("删除成功！")
}
}catch(SQLException e){
e.printStackTrace();
}finally{//释放资源
JdbcUntils.release(conn,st,rs);
}
}

• 工具类（JdbcUnitls）

private static String driver = null;
private static String url = nu1l;
private static String username = nu1l;
private static String password = null;
static {
    try{
        
        InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties=new Properties();
        properties.loaad(in);
        properties.getProperty("driver");
        properties.getProperty("url");
         properties.getProperty("username");
         properties.getProperty("password");
        //1.驱动只加载一次
        Class.forName(driver);
    }catch(IOException e){
        e.printStackTrace();
    }catch(ClassNotFoundException e){
        e.printStackTrace();
    }
}
Public static void getConncetion(){
    Connection conn=DriverManager.getConnection(url,username,password);//获取链接
    //释放资源
    Public static void release (Connection conn,Statement st,ResultSet rs){
        if(rs!=null){
            try{
                rs.close;
            }catch(SQLExcetion e){
                e.printStackTrace();
            }
        }
        if(st!=null){
            try{
                st.close;
            }catch(SQLExcetion e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try{
                conn.close;
            }catch(SQLExcetion e){
                e.printStackTrace();
            }
        } 
    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露。

10.5 perparedStatement对象

防止sql注入，效率更高。