MySQL学习笔记

一、数据库操作

命令行

1.数据库连接：mysql -u root -p密码
2.修改密码：update mysql_user set anthentication_string=password('密码') where user='root' and host='localhost';
3.刷新：flush privileges; --刷新权限
4.显示所有数据库：show databases;
5.切换数据库：use 数据库名;
6.创建数据库：create database [if not exists] 数据库名;
7.删除数据库：drop database [if exists] 数据库名;
8.退出连接: exit;

二、数据类型

1.数值类型

1.tinyint 1byte

2.smallint 2byte

3.mediumint 3byte

4.int 4byte

5.bigint 8byte

6.float 4byte

7.double 8byte （精度问题！！）

8.decimal 字符串形式的浮点数

2.字符类型

1.char 定长

2.varchar 变长

3.tinytext 微型文本

4.text 文本串

3.日期

1.date YYYY-MM-DD

2.time HH:mm:ss

3.datetime YYYY-MM-DD HH:mm:ss

4.timestamp 时间戳 1970.1.1到现在的毫秒数!

5.year 年份

三、表操作

命令行

1.查看所有的表：show tables;
2.显示表的详细结构：describe 表名;
3.创建表：create table 表名(
	列名  数据类型 [属性] [comment '列名的其他称呼'],
    ...      
    --最后一个不用加逗号
)engine=innodb default charset=utf8;
注：可加的属性：
	1）Unsigned       --无符号整数
	2）auto_increment  --自增
	3）zerofill      --零填充
	4）not null     --非空
	5）unique    --唯一
	6）default   --设置默认值
数据库引擎默认为innodb，除此之外主要还有myisam（以前使用的）
字符集默认不支持中文，所以要设置为utf8
4.删除表：drop table [if exists] 表名;
5.查看创建表的sql语句：show create table 表名;
6.修改表：alter table 表名  rename as 新表名;
         alter table 表名  add 列名  数据类型;
         alter table 表名  modify 列名 列属性; --修改约束
         alter table 表名  change 旧列名 新名 列属性;--修改字段名
         alter table 表名  drop 列名  ;--删除字段

四、外键

方式一

在建表的同时添加外键：

create table 表名(

   key '键名'('列名'),
   constraint '键名' foreign key('列名') references '表名'('列名')
);

方式二

在建表之后添加外键：

alter table 表名 
add constraint '外键名' foreign key ('列名') '表名'
('列名');

五、DML(数据库管理语言)

1.插入

insert into 表名 (字段1,字段2,...) values ('值1','值2');--单个记录
insert into 表名 (字段1) values('值1'),('值2');--多个数据

注：不写表的字段，会一一匹配（按表结构顺序），如下：

insert into 表名 values ('值1','值2',...);

插入语句要保证插入字段和值对应

2.修改

update 表名 set 字段= '' where 条件;--单个属性
update 表名 set 字段1= '', 字段2='',.... where 条件;--操作多个属性

注：不加where条件会更改所有的记录；

3.删除

delete from 表名 where 条件;

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

· InnoDB 自增量会从1 开始（数据存在内存中，断电即失）

· MyISAM 继续从上一个增量开始（存在文件中的，不会丢失）

TRUNCATE 表名;-- 完全清空一个数据库表，表的结构不变；

TRUNCATE 与 delete区别：truncate 会重置自增（自增回归零），delete不会影响自增（即自增不归零）

4.常见操作符

操作符	含义
=	等于
<>或!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN ... AND ...	在..和..之间
AND 或&&	与
OR或\|\|	或
LIKE	模糊查询
！	非
IS NULL	为空
IS NOT NULL	不为空
IN (a1,a2,...）	在....范围内

六、DQL（数据库查询语言）

1.select 基本语法

select * from 表名;--查询所有信息
select 字段1,... from 表名;--查询指定字段
select 字段1 as 别名 from 表名;--给字段取别名
select concat('','') from 表名;--concat函数，拼接字符串

2.去重 distinct

select distinct 字段 from 表名;--去除查询结果中重复的数据

3.数据库查询的列（表达式）

select version() --查版本号

select 100*3 --计算结果

select 字段 +1 --数据处理

select @@auto_increment_increment --自增步长

select count（ [distinct] 字段或者 *）from table; --统计记录数

4.where 条件之模糊查询

核心关键词 : LIKE

通配符：

% :代表0到任意个字符

下划线_：代表1个字符

5.联表查询

七种join理论，如下图：

left join , inner join , right join 表示三部分

select  a.x  from  alpha as  a   
left join  beta as b 
on a.y = b.y;    --join连接alpha和beta表，left表示以a表为主 
				--on 接判断条件
注：
where：等值查询
on：   联表查询

1.内连接(两表的共有部分)
SELECT * FROM tbl_dep d INNER JOIN tbl_emp e ON d.id=e.deptId;

2.左连接（左表的全部，右表不满足补NULL）
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId;

3.右连接（右表的全部，左表不满足的补NULL）
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;

4.特殊的左连接，（显示为左表的独有的数据）
说明：查询tbl_dep 表中跟tbl_emp 表无关联关系的数据，即tbl_dep 独占，且tbl_emp 表的显示列补NULL；
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;

5.特殊的右连接（显示为右表的独有的数据 ）
说明：查询tbl_emp 表中跟tbl_dep 表无关联关系的数据，即tbl_emp 独占，且tbl_dep 表的显示列补NULL；
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;

6.全连接（显示全部数据）（mysql 不支持 full outer join）
UNION ：有去重的功能。
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;

7.显示两表的独有的数据
SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL UNION
SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;

自连接

自己的表和自己的表连接，核心：一张表拆成两张一样的表

select a.x  from tab as a ,tab as b  
where a.y= b.z; 
--同一张表的y属性和z属性本质是相同的，比如都是科目编码

6.分页和排序

排序：升序 ASC 降序：DSC

select * from table 
order   by   字段    ASC
limit  起始下标    记录数;

分页：

limit 起始索引记录数

例：limit 0 5 对应 1 - 5

limit 1 5 对应 2 - 6

7.子查询(嵌套查询 )

select * from table 
where a=(select * from table) ;

8.分组group by 和分组过滤Having

select * from table
group by 字段  --按照该字段进行分组
having 条件;--相当于where，group by 只能和having搭配，不能用where

9.select 小结

select [ALL | DISTINCT] COLUMN_NAME
[WHERE  ..]
[GROUP BY COLUMN_NAME]
[HAVING  .. ]
[ORDER BY  COLUMN_NAME  ASC | DSC]
[LIMIT  start   size]

七、MySQL常用函数

官网：https://dev.mysql.com/doc/refman/8.0/en/functions.html

1.常用函数

数学运算：
1.ABS()--取绝对值
2.CEILING()--向上取整
3.FLOOR()--向下取整
4.RAND() --随机数
5.SIGN() --判断一个数的符号

字符串：
1.CHAR_LENGTH() --字符串长度
2.CONCAT() --拼接字符串
3.UPPER() --大写字母
4.LOWER() --小写字母

时间和日期函数：
1.CURRENT_DATE() --获取当前日期
2.CURDATE() --当前日期
3.NOW() --当前时间
4.LOCALTIME() --本地时间
5.SYSDATE -- 系统时间

2.聚合函数

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

COUNT(指定列) 会忽略null值

COUNT(*) 不会忽略null值

COUNT(1) 会忽略null值

3.数据库级别的MD5加密

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

用MD5()函数可进行MD5加密

再插入数据时进行加密

八、事务

1.事务的原则

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

一、原子性（atomicity)

一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性

二、一致性（consistency)

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态

三、隔离性（isolation）

事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。

在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化

1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取

2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A 操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C 是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。

3、可重复读（Repeatable Read)

就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；

4、串行化

是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。

四、持久性（durability）

一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。--即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

2.事务实现

mysql是默认开启事务自动提交的

手动处理事务

事务开始

START TRASACTION;

提交 ---一旦提交就不能回滚了，就被持久化了

COMMIT;

回滚

ROLLBACK;

事务结束

SET autocommit =1 ； --开启自动提交

--设置保存点

SAVEPOINT 保存点名;

-回滚到保存点

ROLLBACK TO SAVEPOINT 名字;

撤销事务的保存点

RELEASE SAVEPOINT 名字;

九、索引(Index)

1.分类

1.主键索引（PRIMARY KEY）

唯一的标识，主键不可重复，只有一个列可作为主键

2.唯一索引（UNIQUE KEY）

避免出现重复的列，唯一索引可重复，多个列可作为唯一索引

3.常规索引 ( KEY/INDEX)

默认的，key关键字来设置

4.全文索引（FullText INDEX）

在特定的数据库引擎下才有

显示所有的索引信息

show index from 表名；

修改指定用户密码增加一个全文索引

alter table 表名 add fulltext index 索引名（字段名）；

3.添加索引

1.创建表时添加
2.修改表以创建	增加一个全文索引
	alter  table  表名   add  fulltext index  索引名（字	段名）；
3.直接创建索引 
	create index  索引名  on 表名(字段);
测试索引：
explain  select语句;

4.索引原则

1.索引不是越多越好；

2.不要对经常变动的数据加索引

3.小数据量的不需要加索引

4.索引一般加在常用来查询的字段

5.索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构

十、数据库备份

备份的原因：

1.保证重要的数据不丢失

2.数据转移

备份的方式：

1.直接拷贝物理文件

2.可视化工具手动导出

3.命令行导出

mysqldump -h主机 -u 用户 - p密码表名 > 物理磁盘地址

十一、权限管理

1.用户管理

本质：对mysql.user这张表进行操作

create user identified by '密码' ;

修改当前用户密码

set PASSWORD =PASSWORD();

修改指定用户密码

set PASSWORD FOR user_name = PASSWORD('pwd');

重命名

RENAME USER user_name TO new_name;

用户授权

GRANT ALL PRIVILEGES ON *.* TO user_name [with grant option];--能操作所有的库和表

查看权限

show grant for user_name;--普通用户
show grant for root@localhost;--

撤销权限

revoke all privileges on *.* from user_name;

删除用户

drop user_name;

十二、数据库的设计

1. 目的

节省内存空间

保证数据库的完整性

方便开发系统

2.步骤

收集信息，分析需求

标识实体

标识实体之间的关系

3.三大范式

需要数据规范化的原因：

信息重复（冗余）

更新异常

插入异常

删除异常

第一范式：1NF

要求：数据库表的每一列都不可再分。

例：家庭信息可拆分为家庭地址和家庭电话

第二范式 2NF

前提：满足1NF

要求：表中每一列都和主键相关，而不能和逐渐的部分相关（针对联合主键）

第三范式3NF

前提：满足2NF

要求：确保每一列和主键直接相关，而不能间接相关（消除依赖传递性）

十三、JDBC

1.创建项目

2.新建lib目录，导入数据库驱动

3.编写测试代码

//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和URL
String url = "jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding= utf8&useSSL=true";
String username ="root";
String password = "123456";
//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(resultSet.getObject("column_name"));
    
}
//6.释放连接 先建立后释放，后建立先释放
resultSet.close();
statement.close();
connection.close();

步骤总结

1.加载驱动

2.连接数据库DriverManager

3.获得执行sql的对象Statement

4.获得返回的结果集

5.释放连接

PreparedStatement（预编译SQL）详解

String sql = "select * from table where name =?";
PreparedStatement st = connection.prepaerdStatement(sql);
st.setInt(1,'nick');
st.executeQuery(sql);

这种方式可避免sql注入，原因是参数会被引号包裹，遇到参数中含引号会直接忽略掉

十四、数据库连接池

连接----释放十分浪费资源，所以使用池化技术

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

常见的

1.DBCP 需要用到的jar包

commons -dbcp.jar

commons -pool.jar

2.C3P0

posted @ 2021-03-12 23:27 进击的小吴同学阅读(107) 评论(0) 收藏举报

刷新页面返回顶部

Loading

WU'S Blog

MySQL学习笔记

MySQL学习笔记

一、数据库操作

命令行

二、数据类型

1.数值类型

2.字符类型

3.日期

三、表操作

命令行

四、外键

方式一

方式二

五、DML(数据库管理语言)

1.插入

2.修改

3.删除

4.常见操作符

六、DQL（数据库查询语言）

1.select 基本语法

2.去重 distinct

3.数据库查询的列（表达式）

4.where 条件之模糊查询

5.联表查询

6.分页和排序

7.子查询(嵌套查询 )

8.分组group by 和分组过滤Having

9.select 小结

七、MySQL常用函数

1.常用函数

2.聚合函数

3.数据库级别的MD5加密

八、事务

1.事务的原则

2.事务实现

九、索引(Index)

1.分类

3.添加索引

4.索引原则

5.索引的数据结构

十、数据库备份

十一、权限管理

1.用户管理

十二、数据库的设计

1. 目的

2.步骤

3.三大范式

十三、JDBC

1.创建项目

2.新建lib目录，导入数据库驱动

3.编写测试代码

十四、数据库连接池

公告