1、初识mysql
前端(页面:展示,数据)
后台(连接点:JDBC,链接前端)
数据库(存数据,txt,excel,word)
1.2 什么是数据库
存数据,管理数据
概念:数据仓库,软件,安装在操作系统上的
1.3 数据库分类
关系型数据库SQL
- mysql oracle sqlServer sqllite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库 NoSQL(not sql)
- Redis,MongDB
- 对象存储,通过对象的自身属性来决定
DBMS 数据库管理系统
- 数据库的管理软件,维护和获取数据
1.4 mysql
Oracle旗下产品 使用SQL语言 体积小,速度快,开源
1.5 安装mysql
官网搜索 建议安装5.7版本的
安装建议:
1.尽量不要使用exe
2.尽可能使用压缩包安装
1.解压
2.把这个包放到自己的环境目录下
3.配置环境变量 我的电脑 高级 环境变量 把mysql bin目录的路径新增为环境变量
4.新建my.ini
[mysqld]
#目录是自己安装的
basedir=D:\\...\\bin\\
datadir=D:\\...\\data\\
port=3306
skip-grant-tables
5.启动管理员模式的CMD,运行所有的命令
6.切换到mysql bin目录下 输入mysqld -install
7.输入 初始化数据库文件
mysqld --initialize-insecure --user=mysql
没有报错,成功
8.启动mysql 设置命令
net start mysql
9.进入mysql -p后面不要有空格
mysql -u root -p
不用输密码,直接回车
10.更改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
输入flush privileges;刷新权限
11.删除my.ini文件最后一句 skip-grant-tables
12.重启mysql exit 退出
net stop mysql
net start mysql
1.6 安装sqlyog
kuangshen
8d8120df-a5c3-4989-8f47-5afc79c56e7c
1.7 创建数据库
基字符集 utf8
数据库排序规则 utf8_general_ci
每一个sqlyog操作其实都是mysql语句的操作,可以在历史记录中查看
表的默认
引擎为: InnoDB
字符集: utf8
核对:utf8_general_ci
1.8 链接数据库
管理员打开cmd
mysql -u root-p123456 --单行注释
-- 所有语句都是用;结尾
show databases; --查看所有数据库
use schooll --切换数据库
show tables; --查看数据库中的所有表
describe student; --显示数据库中所有的表的信息
create database test; --创建一个数据库
exit;退出连接
--单行注释(sql本质的注释)
/*
多
行
注
释
*/
2、操作数据库
操作数据库>操作数据库中的表>操纵数据库表的数据
mysql关键字不区别大小写
2.1 操作数据库
1.创建数据库
【】为可选
create database [if not exists] 数据库名
2.删除数据库
dorp database [if exists] 数据库名
3.使用数据库
use 数据库 --使用数据库
-- tab 键的上面 反引号
--如果表名或者字段名是一个特殊字符,需要带`符号
use `school`
4.查看数据库
show database; --查看所有的数据库
2.2 数据库的列类型
数值
- tinyint 十分小的数据,一个字节
- smallint 较小的数据,两个字节
- mediumint 中等的 三个字节
- int 标准的证书,四个字节 常用
- big 较大的数据 8个字节
- float 浮点数 4字节
- double 8字节
- 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 到现在的毫秒数
- year 年份表示
null
- 没有值,位置
- 注意,不要使用null类型进行计算
2.3 数据库的字段属性(重点)
Unsigned:
- 无符号整数
- 该列不能声明为负数
zerofill:
- 0填充的
- 不足的位数,用0来填充
自增:
- 通常理解为自增,自动在上一条记录加一
- 通常用来设计唯一的主键,必须是整数
- 可以自己设置起始值和步长
非空 null not null
- 设置为not null,不给他赋值,会报错
默认:
- 设置默认值
- sex,默认值为 男,如果不设置值,就用默认的值
扩展:听听就好
/*
每一个表,都必须存在以下五个字段!未来做项目用的,表示一个记录存在意义
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4 创建表
-- 注意点 ,使用英文() ,表的名称和字段 尽量用`扩起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来
-- 所有的语句后面加,(英文的),最后一个不用加
-- PRIMARY KEY 主键
CREATE TABLE IF NOT EXISTS `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
格式
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
常用命令
--查看创建数据库语句
show create database school
-- 查看创建表的语句
show create table student
--显示表的结构
desc student
2.5 数据表的类型
innodb 默认使用
myisam 早些年使用的
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
所有的数据库文件都存在data目录下
本质还是文件的存储
mysql引擎在物理文件上的区别
- innodb 在数据库表中只有一个*.from文件
- MYISAM对应文件 3个文件
设置数据库表的字符集
默认的编码格式不支持中文 Latin1
2.6 修改删除表
-- 修改表名
ALTER TABLE 旧的表名 RENAME AS 新的表名
-- 增加表的字段
ALTER TABLE 表名 ADD 字段名 列属性
-- 修改表的字段
-- 修改约束
ALTER TABLE 表名 MODIFY 字段名 类属性
-- 字段重命名
ALTER TABLE 表名 CHANGE 旧列名 新列名 列属性
-- 删除表的字段
ALTER TABLE 表名 DROP 列名
--删除表
DROP TABLE IF EXISTS 表名
所有的创建和删除尽量加上判断
注意点:
- 字段名用``包裹,安全
- 注释是 – /**/
- sql 关键字大小写不敏感,建议大家写小写
- 所有的符号用英文
3、mysql数据管理
3.1 外键(了解)
约束
方式一:在创建表的时候添加约束
-- 学生表的gradeid 字段要去引用年级表的gradeid
--定义外键key
--给这个外键添加约束(执行引用) references 引用
create table `grade`(
`gradeid` int(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` varchar(20) NOT NULL COMMENT '年级名称',
primary key( `gradeid`)
)engine=innodb default charset=utf8
create table `student`(
`id` int(4) not null auto_increment comment '学号',
`gradeid` int(10) not 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 `stduent`
add constraint `FK_gradeid` foreign key(`gradeid`)
references `grade`(`gradeid`);
-- alter table 表名 add constraint 约束名
-- foreige key(作为外键的列) references 哪个表(哪个字段)
以上为 物理外键,即数据库级别的外键,我们不建议使用
最佳实现
- 数据库就是单纯的表,只用来存数据,只有数据和字段
- 想使用多张表的数据,使用外键,使用程序实现
3.2 DML语言(记住)
DML 语言:数据操作语言
- insert
- update
- delete
3.3 添加
insert into 表名([字段名1,字段名2,...]) values ('值1','值2',...)[,(''),('')];
-- 如果不写表的字段,会一一匹配
-- 一般写插入语句,一定要数据和字段一一对应
注意:
1.字段与字段之间使用 英文逗号隔开
2.字段名可以省略,但是字段值要一一对应
3.可以同时插入多条数据
3.4 修改
-- 带条件的
update `表名` set `字段名`=`新值`,[...] where 字段名 = 字段值
条件 where 语句 运算符
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | true,false | |
<>,!= | 不等于 | ||
> | 大于 | ||
< | 小于 | ||
>= | 大于等于 | ||
<= | 小于等于 | ||
BETWEN …AND… | 闭合区间 | [2,5] | |
AND | 我和你&& | ||
or | 我或你|| |
update `student` set `birthday`=CURRENT_TIME where `name`='xxx'
字段值可以设置为变量
3.5 删除
delete from 表名[where 条件]
-- 避免这样写
delete from 表名
作用:完全清空一个数据库表名,表的结构和索引约束不会变
truncate table `表名`
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- truncate 重新设置 计数器会归零 不会影响事务
- delete 不会影响自增
4、DQL查询数据(最重点)
- 所有的查询操作都用它 select
- 简单的查询,复杂的查询他都能做
- 数据库中最核心的语言
- 使用频率高
4.1 指定查询字段
-- 查询全部的学生
select * from 表名
-- 查询指定字段
select 字段名[,字段名2] from 表名
--别名,给结果起别名 可以给字段和表起别名
select 字段名 as 别名[,字段 as 别名] from 表名 as 表的别名
-- 函数 concat(a,b)
select concat('姓名:',字段名) as 新名字 from
去重复 distinct
--
select distinct 字段名[,字段2] from 表名
作用:
去除select查询出来的结果中重复的数据
-- select 的其他使用
select version() --查询系统版本(函数)
select 100*9-10 AS 计算结果 --用来计算(表达式)
select @@auto_increment_increment --查询自增的步长(变量)
select 字段名+1 from 表名 --对查询后的数据处理
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量
4.2 where条件子句
作用:检索数据中符合条件的值
搜索的条件,由一个或者多个表达式组成
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&n | 逻辑与 |
or || | a or b a||b | 逻辑或 |
not ! | not a !a | 逻辑非 |
-- 模糊查询 【区间】
select 字段 from 表名
where 字段 between 90 and 100
模糊查询:本质是比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not null | a is nout null | 如果操作符为not 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 name,id from student
where name like '刘%'
-- 查询1001 1002 1003 学员信息
select * from student
where studentNo in (1001,1002,1003)
--查询在北京的学生
select * from student
where address in ('北京')
4.3 联表查询
JOIN 对比
百度7种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
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 即使右表中没有匹配,会返回所有的值 |
right join | 即使左表中没有匹配,会返回所有的值 |
-- join on 连接查询
-- where 等值查询
左连接以左表为基准
右连接以右表为基准
select s.studentNo,studentName,subjectName,studentResult
from student s --从studnet找
right join result r -- 右连接result表
on r.studentNo = s.studentNo --交叉点
inner join subject sub -- 上面连接查询的结果为左表,subject为右表
on r.subjectno = sub.subjectno -- 交叉点
自连接
核心:一张表拆分为两个一样的表
select a.name as '父',b.name as '子'
from category as a,category as b
where a.categoryid=b.pid
4.5 分页和排序
--order by
-- 升序ASC 降序DESC
select * from 表名
where 条件
order by 字段 asc|desc
-- 为什么要分页?
-- 缓解数据库压力
-- limit是在所有语句的最下面
--语法 : limit 起始位置,页面大小
-- limit 0,5 第一页 一页5个数据
-- limit 5,5 第二页
-- limit 10,5 第三页
--第n页 limit (n-1)*页面大小(pageSize)
--总页数=数据总数/页面大小(向上取整)
4.6 子查询
本质:在where语句中嵌套一个子查询语句
(由里及外)
4.7 分组
select 语法顺序
select
from
inner join --联合查询
where --指定结果需要满足的条件
group by --指定结果按照哪几个字段来分组
having --过滤分组的记录必须满足的次要条件
order by --指定查询结果排序
limit --指定结果从哪条到那条
5、Mysql函数
5.1 常用函数(并不常用)
--常用函数
select abs(-8) --绝对值
select ceiling(9.4) --向上取整
select floor(9.4) --向下取整
select rand() -- 返回一个0-1之间的随机数
select sign() --判断一个数的符号 0-0 负数- -1 正数-1
--字符串
select char_length('ewrwe') --字符串长度
select concat('1','s','3') --拼接字符串
select lower() --转小写
select upper() --转大写
select replace('aabbcc','a','d') --替换出现的指定字符串
select reverse() --反转
---时间日期函数(记住)
select current_date() --获取当前日期
select curdate() --获取当前日期
select now() --获取当前的时间
select localtime() --本地时间
select sysdate() --系统时间
select year(now()) --年
month,day,hour,minute,second
5.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均数 |
max() | 最大 |
min() | 最小 |
count(字段名) 会忽略所有的null值
count(*) 不会忽略null值
count(1) 不会忽略null值
5.3 扩展:数据库级别的MD5加密
什么是MD5
md5不可逆,具体的md5值是一样的
md5破解网站的原理,背后有一个字典
update 表名 set pwd=MD5(pwd)
--如何校验
--将用户传递进来的密码,进行md5加密,然后比对加密后的值
6、事务
6.1 什么是事务
要么都成功,要么都失败
将一组sql放在一个批次中去执行
事务原则:ACID原则: 原子性,一致性,隔离性,持久性
原子性:
要么都成功,要么都失败
一致性
事务前后的数据完整性要保证一致
持久性
事务一旦提交不可逆,被持久化到数据库中
隔离性
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不会被其他事务所干扰
隔离性出现的问题:
脏读:一个事务读取了另一个事务未提交的数据
不可重复读
在一个事务内读取表中的某一行数据,多次读取结果不同
虚读(幻读):在一个事务内读取到了别的事务插入的数据,导致前后读取不一致
执行事务:
mysql 是默认开启事务自动提交的
set autocommit = 0 --关闭
set autocommit = 1 – 开启,默认的
--关闭自动提交
set autocommit = 0
--事务开启
start transaction
--提交:持久化
commit
--回滚:回到最初
rollback
---事务结束
set autocommit=1 --开启自动提交
模拟场景
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'
update account set money=money+500 where `name`='B'
commit; --提交
rollback; -- 回滚
set autocommit=1; --回复默认
7、索引
索引的本质是:数据结构
通过索引可以高速获取数据
7.1 索引的分类
- 主键 primary key
- 唯一的标识,主键不可重复
- 唯一 unique key
- 避免重复的列出现,唯一索引可以重复
- 常规 key/index
- 默认的,可以用index,key关键字来设置
- 全文索引 fullText
-- 索引的使用
-- 在创建表的时候给字段增加索引
-- 创建完毕后,增加索引
-- 显示所有的索引信息
show index from student
-- 增加一个全文索引(索引名)(列名)
alter table 表名 add fulltext index 索引名(列名)
-- explain 分析sql执行的状况
explain select * from 表名
7.2 测试索引
7.3 原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加到常用来查询的字段上
8、权限管理和备份
8.1 用户管理
SQL yog 可视化管理
SQL 命令操做
用户表:mysql.user
本质:
-- 创建用户
create user xx identified by '123456'
-- 修改当前用户密码
set password =password('111111')
-- 修改指定用户密码
set password for xxx=password('123456')
--重命名
rename user xxx to xx2
-- 授权
-- 除了给别人授权,其他都能干
grant all privileges on *.* to xx2
-- 查看权限
show grants for xx2
show grants for root@localhost -- 查看管理员权限
-- 撤销所有权限
revoke all privileges on *.* from xx2
-- 删除用户
drop user xx
8.2 数据库备份
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 使用命令行到处 mysqldump
#mysqldump -h主机 -u 用户名 -p密码 数据库名 表1,表2,表3 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
导入:先登录mysql
source d:/a.sql
9、规范数据库设计
9.1 为什么需要设计
当数据库比较复杂时,需要设计
糟糕的:
- 数据冗余,浪费空间
- 数据插入和删除都会麻烦、异常
- 程序的性能差
良好的:
- 节约内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求
- 概要设计 E-R图
个人博客数据库设计
- 用户表
- 分类表
- 文章表
- 友链表
- 自定义表(系统信息,某个关键的字,或者一些主字段)
- 评论表
9.2 三大范式
为什么需要数据规范化
- 信息异常
- 插入异常
- 更新异常
- 删除异常
三大范式
第一范式:
原子性:保证每一列不可再分
第二范式:
前提:满足第一范式
每张表只描述一件事情
第三范式:
前提:满足第二范式
确保数据表中的每一列数据都和主键直接相关,而不能间接相关
规范化和性能的问题
关联查询的表不能超过三张表
10、JDBC(重点)
SUN公司为了简化开发人员的操作,提供了一个规范,就是JDBC
对于开发人员来说,只需要掌握JDBC接口即可
在maven中搜索下载
Class.forName(“com.mysql.jdbc.Driver”);
Connection connection = DriverManager.getConnection(url,username,password);
url
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
Statement 执行sql的对象
String sql =""; //编写sql
statement.executeQuery(); //查询操作 返回ResultSet
statement.execute()l//执行任何SQL
ResultSet 查询的结果集
释放资源(必须做)
例子:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcFirstDemo {
public static void main(String[] args) throws Exception {
//1. 加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2. 用户信息和url
//useUnicode=true&characterEncoding=utf8&&useSSL=true
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
String name = "root";
String password = "123456";
//3. 连接成功,返回数据库对象 connection代表数据库
Connection connection= DriverManager.getConnection(url,name,password);
//4. 执行SQL的对象 statement 执行SQL的对象
Statement statement = connection.createStatement();
//5. 执行SQL的对象 去执行SQL 可能存在结果,查看返回结果
String sql="SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
while(resultSet.next()){
System.out.println("id+"+resultSet.getObject("id"));
System.out.println("name+"+resultSet.getObject("NAME"));
System.out.println("password+"+resultSet.getObject("PASSWORD"));
System.out.println("email+"+resultSet.getObject("email"));
System.out.println("birthday+"+resultSet.getObject("birthday"));
}
//6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
提取工具类
package com.kuang.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
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.properties");
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();
}
}
//2.获取连接
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(url, username, password);
}
//3.释放资源
public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
if(rs!=null){
rs.close();
}
if (st!=null){
st.close();
}
if(conn!=null){
conn.close();
}
}
}
PreparedStatement对象
可以防止SQL注入,效率更高
案例
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection connection= null;
PreparedStatement pstm=null;
try {
connection = JdbcUtils.getConnection();
//区别
//使用问好占位符代替参数
String sql = "insert into users(id,`NAME`) values(?,?)";
pstm = connection.prepareStatement(sql);//预编译sql,先写sql然后不执行
//手动赋值
pstm.setInt(1,8);
pstm.setString(2,"SANJIN");
//执行
int i = pstm.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,pstm,null);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}