这是我学习B站UP主“遇见狂神说”的Mysql教程的学习笔记
关于此笔记请参考：【狂神说Java】MySQL最新教程通俗易懂
文章目录
基础sql
更新mysql的登陆密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
查看所有的数据库
show databases;
切换数据库
use [dbName];
查看选择的库中所有的表
show tables;
显示数据库中所有的表的信息
describe [tableName];
创建数据库
-- 最简单的创建库
create database [dbName];
-- 设置字符集
create database [dbName] character set utf8 collate utf8_general_ci;
退出连接
exit;
数据库的几种语言
DDL(Database Define Language) 数据库定义语言
DML(Database Manager Language) 数据库管理语言
DQL(Database Query Language) 数据库查询语言
DCL(Database Control Language) 数据库控制语言
操作数据库
创建数据库
create database [if not exists] [dbName];
-- 设置字符集
create database `dbname` character set utf8 collate utf8_general_ci;
移除数据库
drop database [if exists] [dbName];
拓展
/*
阿里巴巴规范—— 每一个表都必须存在以下5个字段：
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
操作表
创建表
create table if not exists `student` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(30) not null comment '密码',
`sex` varchar(2) not null comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key (`id`)
)engine=innodb default charset=utf8;
修改表
-- 修改表名：
alter table `originalName` renames as `newName`;
-- 增加表的字段：
alter table `tableName` add age int(11);
-- 修改表的字段（重命名，修改约束）：
alter table `student` modify `password` varchar(30); -- 修改约束
alter table `student` change `password` `password` varchar(50); -- 字段重命名(也可以修改约束)
-- 删除表的字段：
alter table `student` drop `address`;
删除表
drop table [if exists] student;
mysql 数据管理
外键（了解，一般不使用）
1.创建表的时候增加外键
-- 主表
CREATE table `grade`
(
`grade_id` int(10) NOT NULL auto_increment comment '年级id',
`grade_name` varchar(50) not null comment '年级名称',
primary key (`grade_id`)
) engine = innodb
default charset = utf8;
-- 从表
create table if not exists `student`
(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(30) not null comment '密码',
`sex` varchar(2) not null comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
`grade_id` int(10) NOT NULL comment '学生的年级',
primary key (`id`),
key `FK_grade_id` (`grade_id`), -- 外键
constraint `FK_grade_id` foreign key (`grade_id`) references `grade` (`grade_id`)
-- 约束
) engine = innodb
default charset = utf8;
2.在已有表上添加约束
alter table `student` add constraint `FK_grade_id` foreign key (`grade_id`) references `grade` (`grade_id`)
操作字段
插入字段
insert into `表名` (`字段名`) values(`值`)
修改字段
设置单个属性
update `表名` set `字段名`='新值' [where 条件表达式];
设置多个属性
update `表名` set `字段名1`='新值1',`字段名2`='新值2'... [where 条件表达式];
条件表达式
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| < > 或 != | 不等于 | 5<>6 | true |
| > | 大于 | ||
| < | 小于 | ||
| >= | 大于等于 | ||
| <= | 小于等于 | ||
| between … and … | 在某个范围内 | [2, 5] | |
| and | 只有我和你 && | 5>1 and 1<2 | false |
| or | 我或者你 || | 5>1 or 1>2 | true |
删除字段
delete from `表名` [where ...] -- 物理删除数据
truncate `表名` -- 完全清空整个表(包括重置auto_increament)
DQL 查询数据（最重要）
DQL
(Data Query Language：数据库查询语言)
select语法
-- select语法（注意：顺序很重要）
select [all | distinct] -- 是否去重
{* | table.* | [table.field1[as alias1][, table.field2[as alias2]][,...]]} -- 选择字段
from table_name [as table_alias] -- 选择表
[left | right | inner join table_name2 on ...] -- 联合查询
[where ...] -- 指定结果需要满足的条件
[group by ...] -- 指定结果按照哪几个字段来分组
[having ...] -- 过滤分组的记录的必须满足的次要条件
[order by ...] -- 指定查询记录按一个或多个条件排序
[limit {[offset, ] row_count | row_countOFFSET offset}]; -- 指定查询记录的开始位置与条数
查询
-- 查询全部字段
select * from `表名`
-- 查询指定字段
select `字段名` from `表名`
起别名
-- 给字段起别名
select `字段名1` as '别名1', `字段名2` as '别名2' from `表名`
-- 给表起别名
select * from `表名` as '别名'
函数
-- 拼接字符串 concat(a, b)
select concat('student_name:', `列名`) as 'new_name' from `表名`
去重
select distinct `字段名` from `表名`
查询系统版本
select version()
计算表达式
select 100*3-1 as '列名'
查询自增步长
select @@auto_increment_increment
查询结果更改
select `列名`+1 as 'changed' from `表名`
-- 数据库中的表达式：文本值、列、NULL、函数、计算表达式、系统变量
select 表达式 from `表`
where 条件子句
作用：检索数据中符合条件的值，搜索的条件由一个或多个表达式组成
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 与 |
| or || | a or b a||b | 或 |
| not ! | not a !a | 非 |
语法上尽量使用英文字母
-- 例子
select * from `表名` where `列A` >= 95 and `列A` <= 100
-- 模糊查询（区间）
select * from `表名` where `列名` between 95 and 100
-- 搜索除了1000号学生之外的同学
select `student_id` from `result` where not `student_id` = 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…其中的某一个值中，结果为真 |
字符串通配符
%：代表任意数量的字符
_：代表一个字符
例子
like
like可以使用字符串通配符
-- 查询所有姓刘的同学
select `id`, `name` from `student`
where `name` like '刘%'
-- 查询周姓但是后面只有一个字的同学
select `id`, `name` from `student`
where `name` like '周_'
-- 查询周姓但是后面只有两个字的同学
select `id`, `name` from `student`
where `name` like '周__'
in
in中使用通配符无效，只能使用具体的一个或多个值
-- 查询1，2，3号学员
select `id`, `name` from `student`
where `id` in (1,2,3)
null \ not null
-- 查询地址为空的学生
select * from `student`
where address = '' or address is null
-- 查询有出生日期的同学
select * from `student`
where birthday is not null
连表查询
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少一个匹配，就返回行 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
例子
-- inner join 查询on后表达式的两个表的并集
select student.`id`, `name`, `grade`
from `student`
inner join `grade`
on student.id = grade.id
-- left join 并集+左表独有的数据
select student.`id`, `name`, `grade`
from `student`
left join `grade`
on student.id = grade.id
-- right join 并集+右表独有的数据
select student.`id`, `name`, `grade`
from `student`
right join `grade`
on student.id = grade.id
-----------------------------------
-- 查询没有成绩的同学（缺考的同学）
select student.`id`, `name`, `grade`
from `student`
left join `grade`
on student.id = grade.id
where grade is null
----------------------------------------------------
-- join on 可以连接更多的表（表结构在下面）
select table_A.name, table_B.address, table_C.grade
from table_A
left join table_B on table_A.id = table_B.id
left join table_C on table_C.id = table_A.id
-- 上面所需要的表的表结构
create table `table_A`
(
`id` int not null auto_increment,
`name` varchar(5) not null,
primary key (`id`)
);
create table `table_B`
(
`id` int not null auto_increment,
`address` varchar(20) not null,
primary key (`id`)
);
create table `table_C`
(
`id` int not null auto_increment,
`grade` int not null,
primary key (`id`)
);
注意：
join (连接的表) on (判断的条件) 连接查询
where 等值查询
自连接（了解）
一张表切分成两张表
create table `table_a`
(
`id` int not null auto_increment,
`category_id` int null,
`name` varchar(20),
primary key (`id`)
);
insert into `table_a` (`name`)
values ('语文');
insert into `table_a` (`name`)
values ('数学');
insert into `table_a` (`name`)
values ('英语');
insert into `table_a` (`category_id`, `name`)
values (3, '英语阅读');
insert into `table_a` (`category_id`, `name`)
values (3, '英语单词');
insert into `table_a` (`category_id`, `name`)
values (2, '线性代数');
insert into `table_a` (`category_id`, `name`)
values (2, '概率论');
insert into `table_a` (`category_id`, `name`)
values (1, '文言文');
insert into `table_a` (`category_id`, `name`)
values (1, '语文作文');
-- 自连接
select a.`name` as 'category_name', b.`name` as `name`
from table_a as a, `table_a` as b
where a.id = b.category_id;
结果：
| category_name | name | |
|---|---|---|
| 1 | 语文 | 文言文 |
| 2 | 语文 | 语文作文 |
| 3 | 英语 | 英语阅读 |
| 4 | 英语 | 英语单词 |
| 5 | 数学 | 线性代数 |
| 6 | 数学 | 概率论 |
排序与分页
排序
-- 降序
select * from grade
order by grade.`grade` DESC;
-- 升序
select * from grade
order by grade.`grade`;
分页
select * from `表名`
limit 1,3; -- 分页，第一页，共显示3条数据
子查询
select `grade` from grade
where `id` = (
select `id` from student
where name = '晓明'
)
MySql函数
常用函数
数学运算
-- 绝对值
select abs(-8) -- 8
-- 向上取整
select ceiling(9.4) -- 10
-- 向下取整
select floor(9.4) -- 9
-- 随机数
select rand() -- 0到1之间
-- 判断一个数字的符号
select sign(1) -- 0返回0， 负数返回-1， 正数返回1
字符串函数
-- 返回字符串的长度
select char_length('abc') -- 3
-- 拼接字符串
select concat('hello','world') -- helloworld
-- 插入替换字符串
select insert('hi',2,1,'ello') -- hello, 从第2个位置替换1个字符
-- 大小写转换
select lower('Hello') -- 转成小写 hello
select upper('Hello') -- 转成大写 HELLO
-- 查找字符串
select instr('xiaoming','m') -- 5
-- 替换字符串
select replace('hello, world!', 'hello', 'hi') -- hi, world!
-- 截取字符串
select substring('hello,world!', 7, 5) -- world (从第7个字符开始，往后5个字符)
-- 反转字符
select reverse('abcdefg') -- gfedcba
时间和日期函数
-- 获取当前日期
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() -- system_user()的简写
-- 获取当前版本号
select version() -- 版本号
聚合函数
count() -- 技术
sum() -- 求和
avg() -- 平均值
max() -- 最大值
min() -- 最小值
...
-- Example
-- count
select count(*) from student -- 返回student中有多少条数据
select count(1) from student -- 同上
select count(name) from student -- 返回student中不为空的name的数量（忽略null）
select count(distinct name) from student -- 返回student中不重复且不为空的name的数量
-- 关于效率的注意事项：
-- 当count查询主键列时，count(主键列) 比 count(1) 效率更高
-- 如果count查询的列名不为主键，count(1)效率更高
-- 如果表多个列没有主键，则count(1)比count(*)效率更高
-- 如果表中只有一个字段，则 count(*) 效率最高
-- sum 求和
select sum(`grade`) from student
-- avg 平均值
select avg(`grade`) from studnet
-- max 最大值
select max(`grade`) from studnet
-- min 最小值
select min(`grade`) from studnet
分组和过滤
分组（group by）
-- group by
select `subject`.`name` as `科目`, sum(`subject_data`.`grade`) as `分数`
from subject_data
inner join subject on subject.id = subject_data.subject_id
group by `subject`.`name`
order by 分数;
过滤（having）
-- having
select `subject`.`name` as `科目`, sum(`subject_data`.`grade`) as `分数`
from subject_data
inner join subject on subject.id = subject_data.subject_id
group by `subject`.`name`
having 分数 > 300 -- 与where作用类似，但用于group by
order by 分数;
MD5加密
MD5() -- 加密
事务(transaction)
要么都成功，要么都失败
原则
事务原则：ACID
原子性（atomicity）：多个步骤要么一起成功，要么一起失败
一致性（consistency）：事务前后的数据要保证一致
隔离性（isolation）：多个用户同时操作，互相不受影响
持久性（durability）：事务一旦提交则不可逆，被持久化
事务的隔离级别
脏读：一个事务读取了另外一个事务未提交的数据导致的结果不一致
不可重复读：在一个事务内多次读取结果不同
幻读（虚读）：有人插入新的数据导致的数据量不同
mysql开关事务
-- 事务的开启与关闭（mysql是默认开启事务自动提交的）
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启（默认）
手动处理事务
-- 1.关闭自动提交
set auto commit = 0
-- 2.标记一个事务的开始
start transaction;
-- 3.做一些事情
insert into xxx ...;
insert into xxx ...;
-- 4.提交事务或回滚事务
commit; | rollback; -- 提交|回滚
-- 5.重新开启自动提交
set autocommit = 1; -- 开启自动提交
-- 事务结束
-- 了解，一般不用
savepoint 保存点名; -- 设置一个事务的保存点
rollback to savepoint 保存点名; -- 回滚到保存点
release savepoint 保存点名; -- 撤销保存点
例子
create database shop character set utf8 collate utf8_general_ci;
use shop;
create table `account` (
`id` int 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; -- 恢复默认值
索引
-
在大量数据时，帮助数据库高效获取数据
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
定义
Mysql 官方定义：索引（index）是帮助Mysql高效获取数据的数据结构。（索引是数据结构）
索引的分类
- 主键索引（primary key）
- 唯一标识，不可重复，只能由一个列作为主键
- 唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
- 常规索引（key / index）
- 默认的，使用 index 或 key 关键字设置
- 全文索引（FulText）
- 需要引擎支持
- 快速定位数据
- 需要在大数据量下才有作用
索引的使用
添加索引
创建时添加
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
表创建完毕后添加
-- 创建索引
CREATE INDEX indexName ON table_name (column_name)
-- 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
删除索引
DROP INDEX [indexName] ON mytable;
显示所有的索引信息
show index from `表名`;
分析工具（explain：分析sql执行的状况）
explain ...
权限管理
创建用户
create user `用户名` identified by '密码';
修改密码
-- 修改当前用户密码
set password = password('新密码')
-- 修改指定用户密码
set password for '用户名' = password('新密码')
重命名
rename user `旧用户名` to `新用户名`;
用户授权
grant all privileges on *.* to `用户` -- 授予全部权限
查询权限
show grants for `用户`
撤销权限
revoke all privileges on *.* from `用户` -- 撤销全部权限
删除用户
drop user `用户`
Mysql 备份
mysql的备份方式
-
直接拷贝物理文件
-
在可视化工具中导出sql
-
使用命令行 mysqldump
使用命令行备份
mysqldump -h地址 -u用户 -p密码 数据库名 表名 > 导出的物理磁盘位置/文件名
导入
source 磁盘物理位置/x.sql
规范数据库设计
当数据库比较复杂的时候，我们需要设计
糟糕的数据库设计
- 数据冗余，浪费空间
- 数据库插入和删除都很麻烦、异常（未屏蔽使用物理外键）
- 程序的性能差
- 使用驼峰命名
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
- 使用下划线命名
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图（E-R图）
三大范式
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式：
第一范式（1NF）：要求数据库的每一列都是不可分割的原子数据项。
第二范式（2NF）：（前提：满足第一范式）确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（每张表只描述一件事情）。
第三范式（3NF）：（前提：满足第一范式和第二范式）确保数据表中每一列数据都和主键直接相关，而不能间接相关。
规范性 和 性能的问题 的取舍：
-
不一定必须按照三大范式进行设计。
-
关联查询的表不得超过三张表（阿里巴巴规范）
-
考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
-
在规范性能问题的时候，需要适当考虑一下规范性
-
故意给某些表增加一些冗余字段
-
估计增加一些计算列
JDBC（重点）
SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC。
这些规范的实现由具体的厂商去做。
对开发人员来说，我们只需要掌握JDBC的接口操作即可。
JDBC的使用样例(mysql 8.0.22)
// mysql 8.0.22
// （注意，这个例子中的statement应该使用preparedStatement替换以防止sql注入）
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.用户信息和url(后面是设置时区/是否使用Unicode字符/设置字符集/设置安全连接)
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "xxxxxxxx";
// 3.连接成功，数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行sql的对象 Statement（请使用preparedStatement替代Statement，详情查看下一章节：sql注入）
Statement statement = connection.createStatement();
// 5.执行sql的对象 去 执行sql，可能存在结果，查看返回的结果
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("name"));
}
// 6.释放资源
resultSet.close();
statement.close();
connection.close();
JDBC Connection
connection 代表数据库级别的操作
connection.commit(); // 提交事务
setAutoCommit(bool); // 自动提交事务
connection.rollback(); // 回滚
...
JDBC Statement
注意：statement有sql注入问题，请参阅后面相关的内容
statement.execute(); // 执行任何sql
statement.executeQuery(); // 查询操作，返回ResultSet
statement.executeUpdate(); // 更新、插入、删除都用这个，返回受影响的行数
...
JDBC ResultSet
// 在不知道列类型的情况下使用
result.getObject();
// 如果知道类型可以使用指定类型
result.getString();
result.getInt();
result.getFloat();
result.getDate();
...
遍历结果
result.next(); // 指针移动到下一个位置
result.previous(); // 前一行
result.absolute(row); // 移动到指定行
result.beforeFirst(); // 指针移动到最前
result.afterLast(); // 指针移动到最后
释放资源
// 6.释放资源
resultSet.close();
statement.close();
connection.close();
sql注入问题
statement有sql注入的安全问题，而PreparedStatement将有含义的符号都转义成了普通的字符，所以程序中尽量使用PreparedStatement。
例子
// 当出现这种情况时，statement将会返回所有内容
String username = "'' or 1=1";
String password = "'' or 1=1";
String sql = "select * from users where username = " + username + " and password = " + password;
/* 最终的sql等于：
select * from users where username = '' or 1=1 and password = '' or 1=1;
*/
所以，应该使用 preparedStatement 替代 statement ：
// 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 用户信息和url(后面是设置时区/是否使用Unicode字符/设置字符集/设置安全连接)
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "xxxxxxxx";
// 连接成功，数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// ?代表占位符可通过preparedStatement.set函数进行赋值
String sql = "insert into `user` (`name`) values (?)";
// 预编译sql，但是不执行
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置占位符的内容
preparedStatement.setString(1, "晓明");
// 执行sql
preparedStatement.executeUpdate();
// 释放资源
preparedStatement.close();
connection.close();
JDBC操作事务
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
try {
// 连接成功，数据库对象
connection = DriverManager.getConnection(url, username, password);
// 关闭数据库的自动提交，同时开启事务
connection.setAutoCommit(false);
String sql1 = "insert into `user` (`name`) values (?)";
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement1.setString(1, "晓明111");
preparedStatement1.executeUpdate();
String sql2 = "insert into `user` (`name`) values (?)";
preparedStatement2 = connection.prepareStatement(sql2);
preparedStatement2.setString(1, "晓明222");
preparedStatement2.executeUpdate();
//异常，导致回滚
int i = 1/0;
// 提交事务
connection.commit();
System.out.println("提交成功");
} catch (Exception e) {
// 回滚（可以不用写，因为默认失败即回滚）
connection.rollback();
System.out.println("提交失败，回滚成功");
} finally {
if (connection != null) connection.close();
if (preparedStatement1 != null) preparedStatement1.close();
if (preparedStatement2 != null) preparedStatement2.close();
}
数据库连接池
常用连接池：
-
dbcp
-
c3p0
-
druid (alibaba)
扫一扫
1157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
