MySQL基础学习

MySQL5.7的下载与安装

下载

官网

安装

  1. 解压到自己的盘下

  2. 添加环境变量：我的电脑-->属性-->高级-->环境变量

    选择Path-->新建：添加mysql安装文件夹下的bin文件夹的路径 我的路径：D:\mysql-5.7.19\bin

    新建mysql配置文件：my.ini

    向配置文件添加内容：[mysqld] ​ basedir=D:\mysql-5.7.19\ ​ datadir=D:\mysql-5.7.19\data\ （没有data文件夹，自动生成) ​ port=3306 ​ skip-grant-tables

  3. 启动管理员模式下的CMD,输入cd /d D:\mysql-5.7.19\bin,然后输入mysqld -install （安装mysql服务）

  4. 再输入mysqld --initialize-insecure --user=mysql （初始化数据文件）

  5. 输入net start mysql （启动mysql)

  6. 输入mysql -u root -p（进入mysql通过命令行）

  7. 输入update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; （修改密码）

  8. 输入flush privileges;

  9. 修改my.ini文件最后一句跳过密码#skip-grant-tables

  10. 输入net stop mysql回车(关闭服务)，然后输入net start mysql (重启mysql)

  11. 输入mysql -u root -p回车，然后输入密码123456 （成功会出现：Welcome to the MySQL monitor:....）

数据库基本操作

mysql -u root -p  -- 注释连接数据库
​
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 修改密码
​
flush privileges; -- 刷新权限
​
-------------------------------------------------------------------------------------------
-- 所有的语句都使用;结尾
show databases; -- 查看所有的数据库
​
use school -- 切换数据库 use 数据库名
​
show tables; -- 查看数据库中所有的表
​
describle student; -- 显示数据库中所有的表的结构
​
create database westos; -- 创建一个数据库
​
exit; -- 退出连接
​
-- 单行注释（SQL的本来的注释）
/* (sql的多行注释)
hello
*/
​

操作数据库

  1. 创建数据库: create database if not EXISTS westos;

  2. 删除数据库：drop database if EXISTS westos;

  3. 使用数据库：-- tab键的上面，如果你的表明和字段名是一个特殊字符，就需要带``

    use school

  4. 查看所有的数据库：show databases;

数据库的列类型

数值：

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

  • smailint 较小的数据 2个字节

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

  • int 标准的整数 4个字节

  • bigint 较大的数据 8个字节

  • float 浮点数 4个字节

  • double 浮点数 8个字节

  • decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

 

字符串：

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

  • varchar 可变字符串 0-65535

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1 保存大文本

 

时间日期：

  • date YYYY-MM-DD，日期格式

  • time HH：mm：ss 时间格式

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

  • timestamp 时间戳，1970.1.1到现在的毫秒数！也较为常用。

 

null：

  • 没有值，未知

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

数据库的字段属性（重点）

Unsigned:

  • 无符号整数

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

zerofill：

  • 0填充的

  • 不足的位数，使用0来填充

自增：auto_increment

  • 通常理解为自动在上一条记录的基础上+1（默认）

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

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

非空 NULL not null

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

  • NULL，如果不填写值，默认就是null

默认：

  • 设置默认的值！

  • sex，默认值为男：如果不指定该列的值，就会有默认的值

sql创建数据库表

CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' 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;

注意点：

  • ``字段名，使用这个包裹

常用命令

show create database school; -- 查看创建数据库的语句
show create table student; -- 查看student数据表的定义语句
DESC student; -- 显示表的结构

数据库表的类型

-- 关于数据库引擎
/*
INNODB 默认使用~
MYISAM 早些年使用的
*/
 MYISAMINNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍

常规使用操作：

  • MYISAM 节约空间，速度较快

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

在物理空间存在的位置

所有的数据库文件都存在data目录，一个文件夹对应一个数据库，本质还是文件的存储

MySQL引擎在物理文件上的区别

  • innoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件

  • MYISAM对应文件：1.*.frm表结果的定义文件

    2.*MYD 数据文件（data）

    3.*.MYI 索引文件（index）

设置数据库表的字符集编码

charset=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文）

MySQL默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

修改和删除数据表字段

-- 修改表名 alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1;
​
-- 增加表的字段 alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11);
​
-- 修改表的字段（修改约束,重命名）
-- alter table 表名 modify 字段名 列属性
alter table teacher1 modify age varchar(11); --修改字段属性
-- alter table 表名 chage 旧名字 新名字 列属性
alter table teacher1 change age age1 int(1); -- 重命名加修改约束
​
-- 删除表的字段
alter table 表名 drop 字段名
alter table teacher1 drop age1;
​
-- 删除表
drop table if exists teacher1;

MySQL数据管理

外键（了解即可）

方式一：在创建表时，增加约束（麻烦，比较复杂）

create table `grade`(
    `gradeid` int(10) not null auto_increment comment '年级',
        `gradename` varchar(50) not null comment '年级名称',
        primary key(`gradeid`)
)engine=innodb default charset=utf8;
​
-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
    `gradeid` int(10) not null comment '学生的年级',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
    key `FK_gradeid`(`gradeid`),
    constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），在删除被引用的表（主表）

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

-- 创建表的时候没有外键
-- alter table 表名 constraint 约束名 foreign key(作为外键约束的字段名) references 主表(相同名的字段)
alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);

以上的操作全是物理外键，数据库级别的外键，不建议使用

DML语言（全部记住）

DML语言：数据操作语言

  • insert

  • update

  • delete

添加

-- 插入语句（添加）
-- insert into 表名(字段名1，字段名2，字段名3)values('值1','值2','值3');
insert into grade(`gradename`)values('大四');
​
-- 由于主键自增我们可以省略（如果不写表的字段，它就会一一匹配）
insert into `grade` values(2,'大三');
​
-- 插入多个值
insert into `grade`(`gradename`)
values('大一'),('大二');

修改

-- 语法
-- update 表名 set 字段名1=值,字段名2=值 where 条件
​
-- 修改学员的名字
update student set `name`='gx' where id=1;
​
-- 不指定条件的状况下，会改动所有表
update `studnet` set `name`='jj';
​
-- 修改多个属性，逗号隔开
update `student` set `name`='gx',`email`='123444qq.com' where `id`=1;
操作符含义范围结果
between...and... 在某个范围内 [2,5]  
and && 5>1 and 1>2 false
or || 5>1 or 1>2 true

删除

delete命令：

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

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

-- 清空student表
truncate `student`;

delete和truncate的区别：

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

  • 不同点：

    • truncate:重新设置 自增列 计数器归零

    • truncate 不会影响事务

MySQL查询数据（最重点）

DQL（Data Query Language：数据查询语言）

 

-- 查询所有的学生
select * from `student`;

-- 查询指定字段

select StudentNo,StudentName from student;


-- 别名，给结果起一个名字 as  可以给字段起别名，也可以给表器别名

select StudentNo as 学号,StudentName as 学生姓名 from student as s;


-- 函数 Concat(a,b)

select concat('姓名：',StudentName) as 新名字 from student;

去重istinct:

作用：去除select查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下那些同学参加了考试，成绩
select * from result; -- 查询全部的考试成绩
select `StudentNo` from result; -- 查询有哪些同学参加了考试
-- 发现重复数据，去重
select distinct `StudentNo` from result;

数据库的列（表达式）

select version(); -- 查询系统版本（函数）
SELECT 100*3-1 as 计算结果; -- 用来计算（表达式）
select @@auto_increment_increment; -- 查询自增的步长 （变更）
​
-- 学员考试成绩 +1分查看
select `StudentNo`,`studentresult`+1 as '提了1分' from result;

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

select 表达式 from 表

where条件子句

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

-- ========================WHERE
-- and &&
select `studentno`,`studentresult` from result 
where `studentno`>=95 and `studentno`<=100;
​
-- 模糊查询（区间）
select `studentno`,`studentresult` from result
where studentresult between 95 and 100;
​
-- 除了1000号同学之间的同学的成绩
-- !=   not
select `studentno`,`studentresult` from result
where not studentno=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，a3....其中的某一个值，结果为真 
-- ====================模糊查询=================
-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符） _（一个字符）
select `studentno`,`studentname` from `student`
where `studentname` like '张%';
​
-- 查询姓张的同学，名字后面只有一个字的
select `studentno`,`studentname` from `student`
where `studentname` like '张_';
​
-- 查询姓张的同学，名字后面只有两个字
select `studentno`,`studentname` from `student`
where `studentname` like '张__';
​
-- 查询名字中间有嘉字的同学 %嘉%
select `studentno`,`studentname` from `student`
where `studentname` like '%嘉%';
​
-- ===============in（具体的一个或者多个值）===================
-- 查询1001，1002，1003号学员
select `studentno`,`studentname` from `student`
where `studentno` in(1001,1002,1003);
​
-- 查询在北京的学生
select `studentno`,`studentname` from `student`
where `address` in('江苏南京','北京朝阳');
​
-- ======null not null===========
-- 查询地址为空的学生 NULL
select `studentno`,`studentname` from `student`
where `address`='' or `address` is null;
​
-- 查询有出生日期的同学 不为空
select `studentno`,`studentname` from `student`
where `borndate` is not null;

联表查询

操作描述
inner join 两个表的交集的部分
left join 查出所有左边表的数据和与左边有交集部分的数据
right join 查出所有右边表的数据和与右边有交集部分的数据 
-- ===========联表查询 join==============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
select s.studentno,`studentname`,`subjectno`,studentresult
from student as s
inner join result as r
where s.studentno=r.studentno;
​
-- Right JOIN
select s.studentno,`studentname`,`subjectno`,studentresult
from student as s
right join result r
on s.studentno=r.studentno;
​
-- Left JOIN
select s.studentno,`studentname`,`subjectno`,studentresult
from student as s
left join result r
on s.studentno=r.studentno;
​
-- 查询缺考的同学
select s.studentno,`studentname`,`subjectno`,`studentresult`
from student as s
left join result r
on s.studentno=r.studentno
where studentresult is null;
​
-- 思考题（查询了参加考试的同学信息，学号，学生姓名，科目名称，分数）
select s.studentno,`studentname`,`subjectname`,`studentresult`
from student s
right join result r
on r.studentno=s.studentno
inner join `subject` sub
on r.subjectno=sub.subjectno;

自连接：

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

父类

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

子类

pidcategoryidcategoryname
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`;

分页和排序

-- 分页每页只显示1条数据
-- 语法：limit 起始值((n-1)*pageSize)，页面的大小(pageSize)
select s.`studentno`,`studentname`,`subjectname`,`studentresult`
from student s
inner join result r
on s.studentno=r.studentno
inner join `subject` s2
on r.subjectno=s2.subjectno
where subjectname='高等数学-1'
order by studentresult desc -- 降序排序
limit 0,1; -- 分页
​
-- 查询 Java第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
where studentresult>80 and subjectname='高等数学-1'
order by studentresult DESC
limit 0,10;

子查询

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

-- ==================where=====================
-- 1.查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
select `studentno`,r.`subjectno`,`studentresult`
from `result` r
inner join `subject` sub
on r.subjectno=sub.subjectno
where subjectname='数据库结构-1'
order by studentresult desc;
​
-- 方式二：使用子查询（）
select `studentno`,`subjectno`,`studentresult`
from `result`
where `subjectno`=(
  select `subjectno` from `subject`
  where subjectname='数据库结构-1'
)
order by studentresult desc;
​
-- 2.分数不小于80分的学生的学号和姓名,科目为高等数学-1
-- 方式一：子查询
select s.`studentno`,`studentname`
from student s
inner join result r
on s.studentno=r.studentno
where `studentresult`>=80 and `subjectno`=(
   select subjectno from `subject`
     where subjectname='高等数学-1'
);
​
-- 方式二：IN(由里及外）
select studentno,studentname from student where studentno in(
   select studentno from result where studentresult>80 and subjectno=(
        select subjectno from `subject` where subjectname='高等数学-1'
     )
);

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，
-- 核心：(根据不同的课程分组)
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result r
inner join `subject` sub
on r.subjectno=sub.subjectno
group by r.subjectno; -- 通过字段来分组
having 平均分>80

 

MySQL函数

常用函数

-- ===================常用函数===================
-- 数学运算
select ABS(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); --向下取整
select rand(); -- 返回0~1之间的随机数
select sign(0); -- 判断一个数的符号 0返回0 负数返回-1 正数返回 1
​
-- 字符串函数
select char_length('gxyyds'); -- 字符串长度
select concat('wo ','ai'); -- 拼接字符串
select insert('我爱编程',1,2,'超级热爱'); -- 查询，替换
select lower('GX'); -- 小写字母
select upper('gx'); -- 大写字母
select instr('gx','x'); -- 返回第一次出现的子串的索引
select replace('gxyydn','n','s'); -- 替换出现的指定字符串
select substr('gxyyds',1,3); -- 返回指定的子字符串 (源字符串，截取的位置，截取的长度）
select reverse('sdyyxg'); -- 反转字符串
​
-- 查询姓张的同学，名字 章
select replace(studentname,'张','章') from student
where studentname like '张%';
​
-- 时间和日期函数（记住）
select current_date(); -- 获取当前日期
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 version();

聚合函数（常用）

函数名称描述
COUNT() 计数
SUM() 求和
AVG() 平均值
MAX() 最大值
MIN() 最小值 
-- =============聚合函数=============
-- 都能够统计 表中的数据
select count(borndate) from student; -- count(指定字段)，会忽略所有的null值
select count(*) from student; -- count(*),不会忽略所有的null值，走所有列
select count(1) from student; -- count(1),不会忽略所有的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;

where和having区别

子名作用
where 子句 1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2) where 后面不可以使用聚合函数。3）过滤行。
having 子句 1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2）having后面可以使用聚合函数。3）过滤组。4）支持所有where操作符。

MD5加密

-- =========测试MD5 加密============
create table `testmd5`(
   `id` int(4) not null,
     `name` varchar(20) not null,
     `pwd` varchar(50) not null,
     primary key(`id`)
)engine=innodb default charset=utf8;
​
​
-- 明文密码
insert into testmd5
values(1,'zhangshan','123456'),
(2,'lisi','123456'),
(4,'wangwu','123456');
​
-- 加密
update testmd5 set pwd=md5(pwd);
​
-- 插入的时候加密
insert into testmd5
values(3,'xiaoming',md5('123456'));
​
-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
select * from testmd5 where `name`='xiaoming' and pwd=md5('123456');

事务

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

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

  • 一致性：事务操作前与操作后的数据完整性要保持一致

  • 持久性：事务一旦提交则不可逆，被持久化到数据库中

  • 隔离性：针对多个用户操作，主要是排除其他事务对本次事务的影响

    隔离所导致的一些问题：

  • 脏读：一个事务读取了另一个事务未提交的数据

  • 不可重复读：一个事务读取表中某一行数据，多次读取数据结果不同、

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

-- ================事务========================
-- mysql是默认开启事务自动提交的
set autocommit=0;/*关闭*/
set autocommit=1;/*开启(开启的)*/
​
-- 手动处理事务
set autocommit=0; -- 关闭自动条件
​
-- 事务开启
start transcation -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
​
insert xx
insert xx
​
-- 提交：持久化(成功)
commit
​
-- 回滚：回到的原来的样子(失败)
rollback
​
-- 事务结束
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(3) 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'; -- A减500
update account set money=money+500 where `name`='B'; -- A减500
​
commit; -- 提交事务
rollback; -- 回滚
​
set autocommit=1; -- 恢复默认值

索引

索引的分类

  • 主键索引 （primary key）

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

  • 唯一索引 (unique key)

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

  • 常规索引 (key/index)

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

  • 全文索引 （FullText）

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

    • 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息

use school;

show index from student;


-- 增加一个索引

alter table student add fulltext index studentname(studentname);


-- explain分析sql执行的状况

explain select * from student; -- 非全文索引

explain select * from student where match(studentname) against('张');

测试索引

create table `app_user`(
   `id` bigint(20) unsigned not null auto_increment,
     `name` varchar(50) default '' comment '用户昵称',
     `email` varchar(50) not null comment '用户邮箱',
     `phone` varchar(20) default '' comment '手机号',
     `gender` tinyint(4) unsigned default '0' comment '性别(0：男;1:女)',
     `password` varchar(100) not null comment '密码',
     `age` tinyint(4) default '0' comment '年龄',
     `create_time` datetime default current_timestamp,
     `update_time` timestamp null default current_timestamp on update current_timestamp,
     primary key(`id`)
)engine=innodb default charset=utf8mb4 comment='app用户表';
​
-- 插入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),'22222@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;
​
insert into app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
values(concat('用户',i),'22222@qq.com',concat('18',floor(rand()*((999999999-100000000)+100000000))),
floor(rand()*2),uuid(),floor(rand()*100));
​
select mock_data();
select * from app_user where `name`='用户9999';
​
-- id_表名_字段名
-- create index 索引名 on 表(字段)
create index id_app_user_name on app_user(`name`);

索引在小数据量的时候，用户不大，但是在大数据的时候，区别十分明显

索引原则

  • 索引不是越多越好

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

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

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

权限管理和备份

用户管理

用户表：mysql.user

本质：对这张表进行增删改查

-- 创建用户
create user gx identified by '123456';
​
-- 修改密码（修改当前用户密码）
set password=password('123456');
​
-- 修改密码（修改指定用户密码）
set password for gx=password('123456');
​
-- 重命名 rename user 原来名字 to 新的名字
rename user gx to gx2;
​
-- 用户授权 all privileges 全部的权限，库，表
grant all privileges on *.* to gx2;
​
-- 查询权限
show grants for gx2; -- 查看指定用户的权限
show grants for root@localhost;
​
-- root用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
​
-- 撤销权限 revoke 哪些权限，在哪个库销，给谁撤销
revoke all privileges on *.* from gx2;
​
-- 删除用户
drop user gx2;

MySQL备份

作用：1.保证重要的数据不丢失 2.数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件

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

  • 使用命令行 mysaldump 命令行使用

-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
​
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
​
-- mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
​
-- 导入
-- 登录的情况下，切换到指定的数据库
source 备份文件
source d:/a.sql
​
mysql -u用户名 -p密码 库名<备份文件

规范数据库设计

三大范式

第一范式：

要求数据库表的每一列都是不可分割的原子数据项。

第二范式：

前提：满足第一范式，满足完全依赖（数据库中的每一列都和主键相关，而不能只与主键的某一部分相关）

第三范式：

前提：满足第二范式，消除传递依赖（数据库中表的每一列数据和主键直接相关，而不能间接相关）

JDBC（重点）

1.创建一个普通的项目

2.idea导入数据库驱动-->创建lib文件夹-->把mysql-connector-java复制进来-->右击lib文件夹-->add as library

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="123456";
        //3.连接成功，数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.执行sql的对象 Statement执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行sql的对象去执行sql，可能存在结果，查看返回结果
        String sql="select * from student";
        ResultSet resultSet = statement.executeQuery(sql);//返回结果集,结果集中封装了我们全部的查询出来的结果
        while(resultSet.next()){
            System.out.println("studentno="+resultSet.getObject("studentno"));
            System.out.println("studentname="+resultSet.getObject("studentname"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

  1. 加载驱动

  2. 连接数据库DriverManager

  3. 获得执行sql的对象Statement

  4. 获得返回的结果集

  5. 释放连接

Driver Manager：

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
​
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();

URL:

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

//mysql -- 3306

//协议://主机地址:端口号/数据库名?参数1&参数2&参数3


//oracle -- 1521

//jdbc:oracle:thin@localhost:1521:sid

Statement执行sql的对象，PrepareStatement执行sql的对象

statement.executeQuery();//查询操作返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//更新、插入、删除都是用这个，返回一个受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

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

遍历，指针

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

释放资源

//6.释放连接
resultSet.close();
statement.close();
connection.close();//耗资源，用完关掉

statement对象

  1. 提取工具类

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

     

  2. 编写增删改的方法，executeUpdate

    public class TestInsert {
        public static void main(String[] args) {
            Connection conn=null;
            Statement st=null;
            ResultSet rs=null;
            try {
                conn = JdbcUtils.getConnection();//获取数据库连接
                st = conn.createStatement();//获得sql的执行对象
                String sql="insert into student values (1005,'123456','张伟',0,2,'13801001234','北京朝阳','1980-1-1','text123@qq.com','123456197001011234')";
                int i=st.executeUpdate(sql);
                if(i>0){
                    System.out.println("插入成功");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.relase(conn,st,rs);
            }
        }
    }
public class TestDelete {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得sql的执行对象
            String sql="delete from student where studentno=1005";
            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.relase(conn,st,rs);
        }
    }
}
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得sql的执行对象
            String sql="update student set studentname='gx1' where studentno=1003";
            int i=st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.relase(conn,st,rs);
        }
    }
}

  1. 查询

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn=JdbcUtils.getConnection();
            st=conn.createStatement();
            String sql="select * from student";
            rs=st.executeQuery(sql);//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getInt("studentno"));
                System.out.println(rs.getString("loginpwd"));
                System.out.println(rs.getString("studentname"));
                System.out.println(rs.getInt("sex"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.relase(conn,st,rs);
        }
    }
}

SQL注入问题

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

public class SqlInSert {
    public static void main(String[] args) {
        login("'or'1=1","'or'1=1");
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn=JdbcUtils.getConnection();
            st=conn.createStatement();
            String sql="select * from student where studentno='"+username+"' and loginpwd='"+password+"'";
            rs=st.executeQuery(sql);//查询完毕会返回一个结果集
            while(rs.next()){
                System.out.println(rs.getInt("studentno"));
                System.out.println(rs.getString("loginpwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.relase(conn,st,rs);
        }
    }
}

PrepareStatement对象

PrepareStatement可以防止SQL注入，效率更高

  1. 新增

  2. public class TestInsert {
        public static void main(String[] args) {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                conn= JdbcUtils.getConnection();
                //区别
                //使用？占位符代替参数
                String sql="insert into student values (?,?,?,?,?,?,?,?,?,?)";
                ps=conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
                //手动给参数赋值
                ps.setInt(1,1006);
                ps.setString(2,"123456");
                ps.setString(3,"张大卫");
                ps.setInt(4,0);
                ps.setInt(5,2);
                ps.setString(6,"1234567123");
                ps.setString(7,"江苏常州");
                ps.setString(8,"1998-12-12");
                ps.setString(9,"text123@qq.com");
                ps.setString(10,"123456197101011234");
                //执行
                int i=ps.executeUpdate();
                if(i>0){
                    System.out.println("插入成功");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.relase(conn,ps,null);
            }
        }
    }

  3. 删除

  4. public class TestDelete {
        public static void main(String[] args) {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                conn= JdbcUtils.getConnection();
                //区别
                //使用？占位符代替参数
                String sql="delete from student where studentno=?";
                ps=conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
                //手动给参数赋值
                ps.setInt(1,1006);
                //执行
                int i=ps.executeUpdate();
                if(i>0){
                    System.out.println("删除成功");
                }
            } catch (
                    SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.relase(conn,ps,null);
            }
        }
    }

  5. 更新

  6. public class TestUpdate {
        public static void main(String[] args) {
            Connection conn=null;
            PreparedStatement ps=null;
            try {
                conn= JdbcUtils.getConnection();
                //区别
                //使用？占位符代替参数
                String sql="update student set studentname=? where studentno=?";
                ps=conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
                //手动给参数赋值
                ps.setString(1,"gx");
                ps.setInt(2,1003);
                //执行
                int i=ps.executeUpdate();
                if(i>0){
                    System.out.println("更新成功");
                }
            } catch (
                    SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.relase(conn,ps,null);
            }
        }
    }

  7. 查询

  8. public class TestSelect {
        public static void main(String[] args) {
            Connection conn=null;
            PreparedStatement ps=null;
            ResultSet rs=null;
            try {
                conn=JdbcUtils.getConnection();
                String sql="select * from student where studentno=?";
                ps=conn.prepareStatement(sql);
                ps.setInt(1,1003);//传递参数
                //执行
                rs=ps.executeQuery();//执行
                while(rs.next()){
                    System.out.println(rs.getString("studentname"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JdbcUtils.relase(conn,ps,rs);
            }
        }
    }

JDBC事务实现

  1. 开启事务conn.setAutoCommit(false);

  2. 一组业务执行完毕，提交事务

  3. 可以在catch语句中显示的定义回滚语句，但默认失败就会回滚

public class TestTransaction {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false);//开启事务
            String sql1="update account set money=money-100 where name='A'";
            ps=conn.prepareStatement(sql1);
            ps.executeUpdate();
            String sql2="update account set money=money+100 where name='B'";
            ps=conn.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕提交事务
            conn.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            try {
                conn.rollback();//如果失败则回滚事务
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.relase(conn,ps,rs);
        }
    }
}

数据库连接池

DBCP：commons-dbcp2-2.9.0.jar,commons-logging-1.2.jar,commons-pool2-2.11.1.jar

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
​
#
initialSize=10
​
#最大连接数量
maxActive=50
​
#
maxIdle=20
​
#
minIdle=5
​
#
maxWait=60000
​
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意：“user” 与 “password” 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
​
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
​
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
public class JdbcUtils_DBCP {
    private static DataSource dataSource=null;
    static {
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properities");
            Properties properties=new Properties();
            properties.load(in);
            //创建数据源 工厂模式-->创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源获取连接
    }
    //释放连接资源
    public static void relase(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

C3P0：c3p0-0.9.5.5.jar,mchange-commons-java-0.2.20 .jar

<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
    <!-- 命名的配置 -->
    <default-config>
        <!-- 连接数据库的4项基本参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school?useUnicode=true&amp;characterEncoding=&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化连接数 -->
        <property name="initialPoolSize">5</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">40</property>
        <!-- JDBC的标准参数，用以控制数据源内加载的PrepareStatements数量 -->
        <property name="maxStatements">200</property>
        <!-- 连接池内单个连接所拥有的最大缓存statements数 -->
        <property name="maxStatementsPerConnection">5</property>
    </default-config>
​
    <named-config name="MySQL">
        <!-- 连接数据库的4项基本参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化连接数 -->
        <property name="initialPoolSize">5</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">40</property>
        <!-- JDBC的标准参数，用以控制数据源内加载的PrepareStatements数量 -->
        <property name="maxStatements">200</property>
        <!-- 连接池内单个连接所拥有的最大缓存statements数 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>
public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource=null;
    static {
        try{
            //代码版配置
//            dataSource=new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();
            //创建数据源 工厂模式-->创建
            //dataSource=nwe ComboPooledDataSource("MySQL");配置文件写法
            dataSource =new ComboPooledDataSource("MySQL");//配置文件
        }  catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源获取连接
    }
    //释放连接资源
    public static void relase(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Druid:阿里巴巴

使用数据库连接池后，我们在项目开发中就不需要编写连接数据库的代码了。

Apache官网

posted @ 2022-07-09 21:57  你是魔鬼哦  阅读(50)  评论(0)    收藏  举报