[ 个人学习笔记 ] MySQL笔记

文末笔记pdf版下载地址

MySQL笔记

连接数据库

命令行连接

-- cmd窗口中 --
-- 数据库所有语句都以;结尾
mysql -u root -p123546  -- 连接数据库
show databases;  -- 查看所有的数据库
use sqlname  -- 切换数据库 (use 数据库名)
show tables;  -- 查看数据库中所有表
describe xxx  -- 查看数据库表中的内容 (desc xxx)
create datdabase abc;  --  创建一个数据库
exit;  --  退出连接

修改密码

(用户名: root 原密码: 123456 修改为: abcdef)

# 方法一:
mysql -uroot -p123456
set password for root@localhost = password('abcdef');

# 方法二: 
mysqladmin -uroot -p123456 password abcdef

数据库语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

基本操作

== mysql关键字不区分大小写 ==

-- 创建
create database [if not exists] ssql;
-- 删除
drop database [if exists] ssql;
-- 使用
use ssql;
-- 查看
show databases;

数据类型

数值

• tinyint
• smallint
• mediumint
• int 标准整型 (常用)
• bigint
• float
• double
• decimal

字符串

• char
• varchar 可变字符串 (常用)
• tinytext
• text 文本串

时间

• date YYYY-MM-DD，日期格式
• time hh:mm:ss，时间格式
• datetime YYYY-MM-DD hh:mm:ss 常用时间格式
• timestamp 时间戳，1970.1.1到现在的毫秒数
• year 年份

NULL

• 无值 NULL

数据库字段属性

1. Unsigned：无符号整数
2. zerofill：0填充，使用0填充整数不足的位数
3. 自增：整数型数据+1 (auto_increment)
4. 非空：内容为空则为错误 (not null)
5. 默认：默认值 (default ‘默认字符’)

创建数据表

== SQL语句表名称与字段加``表示防止与关键字冲突 == (`:飘号)

-- auto_increment 自增
-- primary key(`主键`)  设置主键,一个表主键唯一
-- comment '注释内容'


create table if not exists `student`(
    `id` int(4) not null auto_increment comment '学号',
    `name` varchar(20) not null default '匿名' comment '姓名',
    `age` int(3) not null comment '年龄',
    `sex` varchar(2) not null default '男' comment '性别',
    primary key(`id`)
)engine=innodb default charset=utf8

格式

create table [if not exists] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型(长度) [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    [主键设置]
)[表类型][字符集设置][注释]

show create databases dbname;  -- 查看创建数据库dbname的语句
show create table tbname;  -- 查看创建表tbname的语句
desc tbname;  -- 查看表tbname的结构

数据库引擎

• INNODB 节约空间速度较快
• MYISAM 安全性高,事务处理,多表多用户操作

设置数据库的字符集编码

charset=utf8

表操作(删除、修改)

修改 (alter)

-- 改表名
alter table bdname as bdname1
-- 加字段
alter table bdname add age int(3)

-- 改字段
-- modify	(修改类型)
alter table bdname modify age varchar(3)	-- 修改字段类型
-- change	(重命名)
alter table bdname change age age1 int(1)	-- 修改字段名称

-- 删除字段
alter table bdname drop age

删除

-- 删除表
drop table if exists bdname

==加上判断[if not exists]以免报错==

MySQL数据管理

外键(了解)

数据表中的一个字段与另一个表中的字段相关联 (通过设置外键)

-- 在创建表时
key `FK_age` (`age`) ,
constraint `FK_age` foreign key (`age`) references `tbname2`(`age`)

-- `FK_字段名称` //固定格式
-- key `FK_本表字段` (`本表字段`),
-- constraint `FK_本表字段` foreign key (`本表字段`) references `外表名`(`外表字段`)

-- 创表后
alter table `tbname` add constraint `FK_age` foreign key (`age`) references `tbname2`(`age`)

*一般不使用，都在应用层实现

---

DML语言

==数据库操作语言==

• insert 插入
• update 修改
• delete 删除

插入 insert

语法：insert into表名(字段1,字段2,字段3) value ('值1'，'值2'，'值3'),('值4'，'值5'，'值6')

-- 字段与值一一对应
-- 插入的数据的值用括号括起,多组数据括号间隔逗号

insert into `student`(`name`,`age`,`sex`) values ('张三','18','男'),('李四','18','女')
-- 一般主键自增可不用插入

-- 不写插入的字段行,则默认插入所有字段数据(自增主键除外),需要在后续值中输入所有字段数据
insert into `student` values ('张三','18','男','2001-01-01','123456')

修改 update

语法：update [表名] set [字段1]='值1',[字段2]='值2' where [字段]='值'

update 表 set col_name = values where [条件]

-- set设置字段内容为相应值,where查找相应的修改字段
update `student` set `name`='张三' where `id`='1'

条件：where 子句 运算符

操作符： between x and y (between…and…) 范围x到y间的数;

​ and (x=1 and y=2) 多个条件

​ or (x=1 or y=2) 满足条件其一

删除 delete

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

delete from `student` where `id`='1'

清空 truncate

-- 清空所有数据，留下结构
truncate table `表名`
-- 与delete不同,truncate会归零自增

---

DQL查询数据

1.DQL

• 所有查询操作 select
• ==数据库核心语言==
• 使用频率最高

-- select 语句
select [all|distinct] table|table_field[as alias]
from table_name [as table_alias]
[left|right|inner join table_name2]
[where...]		-- 指定结果满足的条件
[group by...]
[having...]		-- 分组后满足的条件
[order by...]
[limit [offest,]row_count|row_countOFFEST offest]
-- select 语句需按照指定格式

2.查询指定字段

语法：select [字段] from [表]

-- 查询表中全部数据
select * from `tbname`;

-- 查询指定字段内容
select `name`,`age` from `tbname`;

-- 别名 as    查询字段名称以指定名称显示
select `name` as 姓名,`age` as 年龄 from `tbname` as 表1;

-- 函数	Concat(a,b)   拼接字符串a,b
select Concat('姓名:',`name`) as 名字 from `tbname`;	-- 显示结果将全部如“姓名：张三”格式显示

去重 distinct

*去除重复数据

select `age` from `tbname`;
-- 去除重复数据
select distinct `age` from `tbname`;

@@auto_increment_increment -- 自增的步长

数据库中的表达式

select `name`,`age`+1 as 年龄 from `tbname`;

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

select [表达式] from [表]

3.where条件子句

设置检索数据的条件

条件由一个或多个表达式组成

逻辑运算符

• and &&
• or ||
• not !

模糊查询*：比较运算符

• is null
• is not null
• between
• like a like b SQL匹配
• in a in (a1,a2,a3…) a在数据(a1,a2…)中

-- ==== like ====
-- like 结合 %(多个字符) _(单个字符)
-- 查询指定字符对应数据

-- 查询姓王的人名
select `name` from `tbname` where name like '王%';
-- 查询姓王的且名字是两个字的人名
select `name` from `tbname` where name like '王_';

-- 查询名字中含有'天'字的人的人名、
select `age`,`name` from `tbname` where name like '%天%';

-- ==== in ====
-- in (具体的一个或多个值) //无法用%_代替
select `age`,`name` from `tbname` where `id` in ('10','11','12');

-- ==== is null / is not null ====
-- 查询值为空/非空
select `age`,`name` from `tbname` where `sex` is null;
select `age`,`name` from `tbname` where `sex` is not null;

4.联表查询

JOIN 对比

-- 两张表关联(有相同字段且共享数据),使用join来对两张表的数据进行查询
-- 联表查询语句用on,条件语句用where
-- join on 连接查询,  where 等值查询
select s.`id`,`age`,`name` from `tbname` as s inner join `tbname1` as r on s.`id` = r.`id`;
select s.`id`,`age`,`name` from `tbname` as s left join `tbname1` as r on s.`id` = r.`id`;
select s.`id`,`age`,`name` from `tbname` as s right join `tbname1` as r on s.`id` = r.`id`;

-- 联表查询后条件查询
select s.`id`,`age`,`name` from `tbname` as s inner join `tbname1` as r on s.`id` = r.`id` where `soccer` is null;

• inner join 返回两表匹配的行
• left join 从左表中返回所有值
• right join 从右表中返回所有值

-- 多张连表查询,设置多个jion on语句判断
-- from 表xx jion 连接表 on 交叉条件
select t1.`id`,`name`,`age` frome `tbname` t1 right jion `tbname2` t2 on  t1.`id` = t2.`id` inner jion `tbname3` t3 on t2.`id` = t3.`id`;

自连接(了解)

自己的表与自己连接，(一张表拆为两张一样的表)

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

select a.`name` as '父栏',b.`name` as '子栏' from `tbname` as a,`tbname` as b where a.`id` = b.`number`;

5.分页和排序

排序 order by

• 升序 asc
• 降序 desc

order by [字段] as asc;	-- 根据字段对表进行升序排序
order by [字段] as desc;  -- ~~降序排序

分页 limit

语法：limit(起始值，分页数)

-- 从第一条数据开始显示，将所有数据分为五页
limit 0,5;

6.子查询

在where语句中嵌套一个子查询语句

*子查询顺序由里及外、

7.分组

分组 group by

过滤(分组后) having

-- 分组过滤数据
group by `字段名`   -- 通过什么字段来分组
having `字段名`>80  -- 设置分组后满足的条件

MySQL函数

1.常用函数

-- 运算函数
select ABS();	-- 绝对值
select CEILING();	-- 向上取整
select FLOOR();		-- 向下取整
select RAND();		-- 0~1的随机数
select SIGN();		-- 判断符号 负(-1) 正(1)

-- 字符串函数
select CHAR_LENGTH('abcde')	-- 字符串长度
select CONCAT('a','b','c')  -- 字符串拼接
select INSERT('nmcdef',1,2,'ab')	-- 字符串替换 insert(原字符串,位置,长度,新字符串)
select LOWER()	-- 转小写
select UPPER()	-- 转大写
select INSTR(str,subtsr)  -- 返回第一次出现的子串索引
select REPLACE(str,str1,nstr)	-- 把原字符串str中的str1内容替换为nstr
select SUBSTR(str,n,m)	-- 返回原字符串从n开始长度为m的子串
select REVERSE();  -- 反转字符串

-- 时间和日期函数
select CURRENT_DATE()  -- 获取当前日期
select CURDATE()  -- 获取当前日期
select NOW()  -- **获取当前日期时间
select LOCALTIME() -- 本地时间
select SYSDATE()  -- 系统时间
select YEAR(NOW())
select MONTH(NOW())
select DAY(NOW())
select HOUR(NOW())
select MINUTE(NOW())
select SECOND(NOW())

-- 系统
select SYSTEM_USER()
select USER()
select VERSION()

2.聚合函数

• count()

select count(`列名`) from `表名`  -- 忽略null值
select count(*) from `表名`  -- 不忽略null值
select count(1) from `表名`  -- 不忽略null值 

• sum()
• avg()
• max()
• min()

select sum(`mark`) from `student`
select avg(`mark`) from `student`
select max(`mark`) from `student`
select min(`mark`) from `student`

-- 分组过滤数据
group by `字段名`   -- 通过什么字段来分组
having `字段名`>80  -- 设置分组后满足的条件

3.数据库级MD5加密(扩展)

-- MD5加密 =》 使用MD5()函数
-- 可以进行多次加密
update `tbname` set `id`=MD5(`id`)  -- 更新表中的数据为MD5加密后的数据

-- 查找加密后数据
select * from `tbname` where `name`='张三' and `id`=MD5('123')

事务

1.事务的含义

多个需要同时完成的操作，使用事务避免出错;

事务中包含了基本操作；

*事务ACID特性：原子性，一致性，隔离性，持久性 (出现的问题：脏读，不可重复读，幻读)

mysql默认开启事务自动提交

set autocommit = 0  -- 关闭事务提交
set autocommit = 1  -- 开启事务提交

2.事务处理

start transaction  -- 标记一个事务的开启，这之后的sql都在同一个事务内
commit    -- 提交
rollback  -- 回滚
savepoint [保存点名]  -- 保存点
rollback to savepoint [保存点名] -- 回滚到保存点
release savepoint [保存点名] -- 撤销保存点

-- 例
set autocommit = 0;
start transaction 

update `tbname` set `soccer`=`soccer`+1 where `id`=1;
update `tbname` set `soccer`=`soccer`-1 where `id`=2;

commit;
rollback;
set autocommit = 1;

索引

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

1.索引的分类

• 主键索引 (primary key)
  • 唯一索引，主键不可重复，只能有一个列作为主键
• 唯一索引 (unique key)
  • 避免重复列值的出现，多个列都可设为唯一索引
• 常规索引 (key/index)
  • 默认的Index，通过key关键字来设置
• 全文索引 (fulltext)
  • 快速定位数据

基础语法

-- 使用索引

-- 创建表时设置
primary key(`列名`)
unique key `索引名`(`列名`)
key `索引名`(`列名`)			index 索引名(`列名`(索引长度))
key `索引名`(`列1`,`列2`,`列3`...(索引长度))			index 索引名(`列1`,`列2`,`列3`...(索引长度))

-- 显示所有的索引信息
show index from `tbname`

-- 增加一个全文索引
alter table `tbname`.`fname` add fulltext index `id`(`id`)

-- 用 explain 分析sql语句执行的情况
explain select * from `tbname`;		-- 非全文索引
explain select * from `tbname` where match(`soccer`) against(80); 

create index `索引名` on `表名`(`字段`)

索引通常在数据量大的时候使用

2.索引的原则

• 索引不是越多越好
• 不要对经常变动的数据加索引

索引的数据类型

Hash 类型的索引

BTree：InnoDB 的默认数据结构

权限管理和备份

1.用户管理

SQL 命令操作

用户表：mysql.user

-- 创建用户: create user `用户名` identified by `密码`
create user nanami identified by `123456`

-- 修改密码(修改当前用户密码)
set password = password(`123456`)

-- 修改指定用户密码
set password for nanami = password(`123456`)

-- 重命名 rename user `原名` to `新名`
rename user nanami to murasame

-- 用户授权 all privileges : 全部的权限(给他人授权除外)
-- *.* :所有库表
grant all privileges on *.* to nanami

-- 查询权限
show grant for nanami
show grant for root@localhost

-- root用户授权: grant all privileges on *.* to root@localhost with grant option

-- 撤销用户权限
revoke all privileges on *.* from nanami

-- 删除用户
drop user nanami

2.MySQL备份

原因：

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

方式：

• 物理拷贝文件
• sql可视化工具导出
• 命令行 mysqldump

-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置
mysqldump -hlocalhost -uroot -p123456 dbname tbname >D:/a.sql

-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘位置
mysqldump -hlocalhost -uroot -p123456 dbname tbname1 tbname2 tbname3 >D:/b.sql

-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置
mysqldump -hlocalhost -uroot -p123456 dbname >D:/c.sql

-- 导入(先登录)
-- source 备份文件
source D:/a.sql

规范数据库设计

1.优化数据库性能

​ 分表存放数据

2.三大范式

数据规范化的原因

• 信息重复
• 更新异常
• 插入异常
• 删除异常

三大范式

第一范式(1NF)

原子性：保证每一列不可再分

第二范式(2NF)

满足第一范式

每张表只描述一件事情

第三范式(3NF)

满足第一范式和第二范式

确保数据表中的每一列都和主键直接相关，而不能间接相关

规范性与性能问题

JDBC

1.数据库驱动

1.创建项目

2.导入数据库驱动

3.编写测试代码

2.JDBC测试代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//第一个jdbc程序
public class jdbcFirstDemo {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		// 1.加载驱动
		// Class.forName("com.mysql.jdbc.Driver") 已经被废弃了
		Class.forName("com.mysql.cj.jdbc.Driver");

		// 2.用户信息和url
		// useUnicode=true&characterEncoding=utf8&useSSL=true
		// 使用Unicode字符集  使用utf-8编码  使用SSL安全传输
		String url = "jdbc:mysql://localhost:3306/jdbct1?useSSL=true&allowPublicKeyRetrieval=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8";
		String userName = "root";
		String passWord = "123456";

		// String url ="jdbc:mysql://localhost:3306/jdbct1?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

		// 3.连接成功，数据库对象 connection 代表数据库对象
		Connection connection = DriverManager.getConnection(url, userName, passWord);

		// 4.执行SQL的对象 Statement 操作数据库的对象
		Statement statement = connection.createStatement();

		// 5.执行SQL的对象 去执行SQL 可能存在结果，查看返回结果
		String sql = "select * from user";

		ResultSet resultSet = statement.executeQuery(sql); // 返回结果集，结果集中封装了我们全部查询出来的结果

		System.out.println("user表内容:");
		System.out.println("-----------------------------------------------------");
		while (resultSet.next()) {
			System.out.println("序号 = " + resultSet.getObject("id"));
			System.out.println("姓名 = " + resultSet.getObject("name"));
			System.out.println("密码 = " + resultSet.getObject("password"));
			System.out.println("邮箱 = " + resultSet.getObject("email"));
			System.out.println("生日 = " + resultSet.getObject("birthday"));
			System.out.println("-----------------------------------------------------");
		}

		// 6.释放连接
		resultSet.close();
		statement.close();
		connection.close();

	}
}

1. 加载驱动
2. 连接数据库 DriverManager
3. 获得执行sql的对象 Statement
4. 获得返回结果集
5. 释放连接

DriverManager

// connection 代表数据库
//设置事务自动提交
//事务提交
//事务回滚

URL

String url = "jdbc:mysql://localhost:3306/jdbct1?useSSL=true&allowPublicKeyRetrieval=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8";

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

Statement 执行SQL的对象

PrepareStatement

statement.executeQuery();	//查询操作，返回 Resultset
statement.execute();        //执行所有 SQL
statement.executeUpdate();  //更新插入删除，返回受影响的行数

ResultSet 查询的结果集

获得指定的数据类型

ResultSet resultSet = statement.executeQuery(sql);

resultSet.getObject();	 //在不知道列类型的情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();

遍历，指针

resultSet.next();       //下一个数据
resultSet.previous();   //前一个数据
resultSet.beforeFirst();//最前行
resultSet.afterLast();  //最后行
resultSet.absolute(int row); //指定行

释放连接

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

3.Statement对象

ResultSet resultSet = statement.executeQuery();	    //查询操作
int n = statement.executeUptate();					//增删改操作
statement.execute();								//所有sql语句，但需判断返回值类型

SQL注入问题

用户非法输入导致数据库数据泄露

SQL拼接

4.PreparedStatement 对象

PreparedStatement 对象可以防止SQL注入，并且效率更高

//与statement的区别
statement = connection.getConnection();
prepareStatement = connection.prepareStatement(sql);	//预编译sql，不执行
//预编译的sql中的参数使用占位符?代替
//如：String sql = "insert into imm(`id`,`name`,`age`) values (?,?,?)";

//给参数赋值
prepareStatement.setInt(1,2);		//第一个数为赋值参数下标(从1开始)，第二个为具体的值
prepareStatement.setString(2,'zhangsan');
prepareStatement.setInt(3,18);

//执行操作，无需传参；可能会返回值
int n = prapareStatement.executeUpdate();	

prapareStatement.setInt(1,1);
//赋值方法
.setObject(m,n)
.setInt(m,n)
.setString(m,n)
.setFloat(m,n)

.execute()
.executeUpdate()
.executeQuery()

1.增加

2.修改

3.删除

4.查询

5.JDBC操作事务

ACID 原则

        Connection conn;
		PreparedStatement pst;
		Statement st = null;
		
		//连接数据库
		conn = CTsql.getConnection();
		try {
			//关闭 事务自动提交
			conn.setAutoCommit(false);
			
			String sql1 = "Update `table` set `count` = count-100 where `name`='A'";
			pst = conn.prepareStatement(sql1);
			pst.executeUpdate();
			pst.close();
			
			String sql2 = "Update `table` set `count` = count+100 where `name`='B'";
			pst = conn.prepareStatement(sql2);
			pst.executeUpdate();
		
			//提交事务
			conn.commit();
			System.out.println("事务提交成功");
			
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
                //conn.setAutoCommit(true);   事务结束后开启事务自动提交
				CTsql.result(conn,st);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

//开启事务
conn.setAutoCommit(false); 
//JDBC事务失败默认回滚

6.数据库连接池

数据库连接 — 执行完毕 — 释放

池化技术：预先准备资源，连接预先准备的服务

常用连接数–>最小连接数

最大连接数：业务最高承载上限

等待超时：n

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

开放数据源实现

DBCP

C3P0

Druild：阿里巴巴

private static DataSource dataSource = null;
/*
*预先设置驱动文件(.properties)并加载
*/

//创建数据源 工厂模式 --> 创建
dataSource = BasicDataSourceFactory.creatDataSource(properties);    //properties为加载的配置对象

//获取连接
public static Connection getConnection() throws SQLException{
    return dataSource.getConnection();//从数据源中获取连接
}

DBCP

导入的jar包： commons-dbcp-1.4.jar

​ commons-pool-1.6.jar

C3P0

导入的jar包： c3p0-0.9.5.5.jar

​ mchange-commons-java-0.2.1.9.jar

数据源dataSource 各项配置：

MySql主从复制

主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

作用与好处

​ 做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
​ 架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
​ 读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

主从复制和主主复制区别？

最大区别是 主从是对主操作数据，从会实时同步数据。反之对从操作，主不会同步数据，还有可能造成数据紊乱，导致主从失效。 主主则是无论对那一台操作，另一个都会同步数据。一般用作高容灾方案

笔记下载

下载地址:
https://download.csdn.net/download/conchino/16065397