MySQL管理和JDBC使用
mysql
select [all|distinct]
{|table.|table.field1[as alias1]]}
select [all|distinct]
{*|table.*|[table.field1[as alias1][,table.field2[as alias2]][.....]]}
from table_name[as table.alias]
[left|right|inner join table_name2] -- 联合查询
[where...] -- 指定结果需满足的条件
[group by ...] -- 指定结果按照那几个字段来分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by...] -- 指定查询记录按一个或更多条件排序
[limit {[offset,]row_count|row_countOFFSET offset}];--指定查询的记录从那条至那条
--[]代表可选,{}代表必选常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看数据表的定义语句
DESC student --显示表的结构--创建数据表的语句
CREATE TABLE `student` (
`id` int(10) NOT NULL COMMENT '学员id',
`name` varchar(100) NOT NULL COMMENT '姓名',
`age` int(3) NOT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8数据库在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储
MySQL引擎在物理文件上的区别
- InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET =utf-8不设置的话，mysql默认字符编码集编码会使用（不支持中文）
mysql的默认编码是latin1
在my.ini中配置默认的编码
character-set-server=utf-8定义外键key
就要给这个外键添加约束（执行引用） references 引用
方法一、在创建表的时候，增加约束。
删除有外键关系的表，必须先删除引用方的表，才能删除被引用的
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)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(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方法二、创建好表了，再add外键。
insert 操作
语法:insert into 表名([字段名1，字段2，字段3...]) values("值1")，("值2")，("值3")，...
字段和字段间用英文逗号隔开，字段可以省略，但值必须一一对应。
INSERT INTO student(name) VALUES ('张三')
update操作
语法为：
update 表名 set colnum_name =value,[...] where [条件]
UPDATE `student` SET `name`='芜湖' WHERE id=1;
UPDATE `student` SET `name`='汪汪队';
UPDATE `student` SET `name`='芜湖' WHERE `name`='wd' AND `sex`='男'delete操作和truncate操作
语法：delete from 表名 p [where 条件]
删除数据（避免这么用）
delete from `student`
DELETE FROM `student` WHERE `id`=1;
truncate `student`相同点：都能删除数据，不会删除表结构
不同：
- truncate 重新设置自增列计数器会归零（即如果表类有自增，自增会归零）；delete不会。
- truncate不会影响事务
select 操作
语法：select 字段，.... from 表
select * from student
select `studentNO` as id,`studentname` as name from tudent as s
去重 distinct
作用:去除select查询出来的结果中重复的数据，重复的数据只显示UC
SELECT * FROM result
SELECT `studentNO` FROM result
SELECT DISTINCT `studentNO` FROM result --发现重复数据，去重数据库的表达式
SELECT VERSION() --查询系统版本
SELECT 100*3-1 AS 计算结果
SELECT `studentNO`,`studentResult`+1 AS '提分后' FROM resultWHERE条件子句
SELECT studentNO,`studentResult` from result
where studentResult >= 95 AND sutdentresult <=100
-- and &&
SELECT studentNO,`studentResult` from result
where studentResult >= 95 && sutdentresult <=100
SElECT studentNo,`studentResult` FRom result
WHERE studentResult BETWEEN 95 AND 100
SELECT studentNO,`studentResult` FROM result
WHERE NOT studentNO = 1000联表查询
SELECT s.studentNO ,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNO=r.studentNO
--Right join
SELECT s.studentNO,studentName,subjectNO,studentResult
FROM student s
RIGHT JOIN result r
ON s.studentNO =r.studentNO
--Left join
SELECT s.studentNO,studentName,subjectNO,studentResult
FROM student s
LEFT JOIN result r
ON s.studentNO =r.studentNO| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| right join | 会返回右边值，即使左表中没有匹配 |
| left join | 会返回左边值，即使右表中没有匹配 |
自连接
自己的表和自己的表连接，核心就是一张表 拆分为两张表即可。
排序和分页(order by and limit)
order by 取值有desc和asc
limit: 查询的起始下标值，页面的大小 如：
limit 0,5 0~5
limit 1,5 ....
子查询
本质就是在where语句中嵌套一个查询语句，即：
where...（select ...）
select `StudentNo`,`SubjectNo`,`StudentResult`
FROm `result`
where SubjectNo=(select subjectno from `subject` where subjectname='数据库结构-1')
order by studentresult descMySQL常用函数
常用函数
-- 数字函数
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.1) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(-10) -- 判断一个数的符号，负数返回-1，正数返回1
-- 字符串函数
SELECT CHAR_LENGTH('人生得意须尽欢') -- 返回字符长度
SELECT CONCAT)('你','好'，'啊') -- 拼接字符串
SELECT INSERT('我爱你beliil',1,2,'超级热爱') -- 查询 ，替换
SELECT LOWER('wuhuKing') -- 转小写
SELECT UPPER('safdsfWhI') -- 转大写
SELECT INSTR('safafsf',f) -- 返回字母第一个出现的子串索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
-- 时间和日期函数
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT YEAR(NOW()) -- 获取年，其他的月日时...同理
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()聚合函数
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 求平均值 |
| MAX() | 求最大值 |
| MIN() | 求最小值 |
| .... | .... |
-- 聚合函数
SELECT COUNT(studentname) FROM student; -- count(指定列)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*)，不会忽略所有的null值，本质是计算行数
SELECT COUNT(1) FROM result; -- 不会忽略所有的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
-- 查询不同课程的平均分，最高分， 最低分，根据不同课程分组
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 --进行分组和过滤数据库级别的MD5加密
主要是增强算法复杂性和不可逆性
MD5不可逆，具体的值的md5是一样的
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,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部密码
-- 插入时就加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))
-- 校验方法: 将用户传递过来的密码，进行md5加密，比对加密过后的值。
事务
事务原则：acid原则 原子性，一致性，隔离性，持久性 (脏读，幻读....)
原子性(Atomicity)
要么都成功，要么都失败
一致性(Consistency)
事务前后的数据完整性要保持一致
持久性(Durability)--事务提交
事务一旦提交则不可逆，被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他的事务操作所干扰，事务之间要相互隔离
隔离所导致的问题
脏读：
指一个事务读取了另一个事务未提交的数据
不可重复读：
在一个事务内读取表中的某一行数据，多次读取的结果不同（得看具体情况）
幻读：
是指在一个事务内读取到了别的事务插入的数据导致前后读取不一致
执行事务
-- mysql是默认开启事务自动提交的
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启*/
-- 手动处理事务
SET autocommit=0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在一个事务中
INSERT xx
INSERT yy
-- 提交；持久化（成功后的操作）
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'
UPDATE `account` SET money=money+500 WHERE `name`='B'
COMMIT; -- 提交事务，用了之后操作就持久化了
ROLLBACK; -- 回滚
SET autocommit = 1索引
索引是帮助MySQL高效获取数据的数据结构。索引本质是数据结构
索引的分类
主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
常规索引（key/index）
- 默认的，index，key关键字来设置
全文索引（fulltext）
- 在特定的数据库引擎下才有，myisam
- 快速定位数据
索引原作
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
hash类型的索引
btree：innodb的默认数据结构
权限管理
sqlyog可视化管理
sqlyog可以通过鼠标选择用户管理按钮，然后创建用户，给它权限，完成创建。就可以连接上了。
mysql.user可以看所有用户的权限
sql命令
-- 创建用户
CREATE USER ww IDENTIFIED BY '123456'
-- 修改密码（修改当前用户密码）
SET PASSWORD =PASSWORD('123456')
-- 修改指定用户密码
SET PASSWORD FOR ww =PASSWORD('123456')
-- 重命名用户
RENAME USER ww TO www
-- 用户授权 ALL PRIVILEGES （全部的权限）
GRANT ALL PRIVILEGES ON *.* TO www
-- 查询权限
SHOW GRANTS FOR www
SHOW GRANTS FOR root@localhost
-- 撤销权限，那些权限，在哪个库，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM www
-- 删除用户
DROP USER www数据库备份
MySQL数据库备份的方法
- 直接拷贝物理文件
- 在sqlyog这类可视化工具中导出
- 使用命令行导出mysql
#mysql -h主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student>D:/a.sql
#mysql -h主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/b.sql
#导入
#登录的情况下，切换到指定的数据库
#source备份文件
source d:/a.sql
mysql -u用户名 -p密码 库名<备份文件规范数据库设计：
良好的数据库设计可以节省内存空间，保证数据库的完整性，方便开发系统。
设计数据库的步骤:（个人博客）
收集信息进行需求分析
- 用户表（用户登录注销，个人信息等等）
- 分类表（分类文章）
- 文章表（文章信息）
- 评论表（用户评论）
- 友链表（友情链接信息）
- 自定义的表
- 标识实体（把需求落地到每个字段）
标识实体之间的关系
- user->blog（写博客）
- user->category（分类）
- user->user（关注）
- links（友情链接）
- user-user-blog（评论）
*三大范式：
为什么需要数据规范化：
- 信息重复
- 更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失了有效信息
三大范式
第一范式（1NF）：
原子性：保证每一列不可再分
第二范式（2NF）：
在满足第一范式情况下
每张表只描述一件事情（确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关）
第三范式（3NF）：
在满足第二范式的情况下
确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标。（成本，用户体验）数据库的性能更重要
- 在规范性能的问题时，需要适当考虑规范性
- 故意给某些表增加一些冗余的字段（从多表查询变成单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
**JDBC
数据库驱动
程序是通过数据库驱动实现和数据库的交互
JDBC：是为了简化开发人员（对数据库的统一）操作，提供了一个（java操作数据库）规范
编写JDBC程序:
先创建数据库信息
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')在java项目中创建lib文件，再添加包mysql-connector-java-5.1.47.jar，最后完成包的效果。
$SBVJ6BT7Z8YNFV9CUB{MPX.png
编写测试代码
package com.wang.lesson1;
import com.mysql.jdbc.Driver;
import java.sql.*;
/**
* @author valive
* @create 2021-05-09 13:28
**/
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");//加载驱动的固定写法
//用户信息和url
//useUnicode=true&characterEncoding=utf8&useSSL=true 分别表示，支持中文编码，设定字符集为utf8，使用安全的连接
String url= "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&?serverTimezone=UTC ";
String username="root";
String password="123456";
//连接数据库对象成功 connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//执行SQL对象 statement 执行SQL对象
Statement statement = connection.createStatement();
//执行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("birth="+resultSet.getObject("birthday"));
}
//释放连接,必须做
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//加载驱动的固定写法
Connection connection = DriverManager.getConnection(url, username, password);
URL
String url= "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sidstatement对象
原文链接:https://blog.csdn.net/sinat_33940108/article/details/108792987
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
CRUD操作 – create
用executeUpdate（String sql）方法完成数据添加操作
Statement st = conn.createStatement();
String sql = "insert into user(...) values(....)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！");
}
CRUD操作 – delete
Statement st = conn.createStatement();
String sql = "delete from user where id = 1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功！");
}CRUD操作 – update
Statement st = conn.createStatement();
String sql = "update user set name = '' where name = '' ";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功！");
}CRUD操作 – read
Statement st = conn.createStatement();
String sql = " select * from user where id = 1 ";
int num = st.executeUpdate(sql);
while(rs.next()){
//根据获取列表的数据类型，分别调用rs的相应方法映射到JAVA对象中
}提取工具类（新建db.properties文件，输入
driver=com.mysql.jdbc.Driver url= jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true username=root password=123456
import com.mysql.cj.protocol.Resultset;
import javax.xml.transform.Result;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author valive
* @create 2021-05-09 15:41
**/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 resourceAsStream = 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");
//驱动只用加载一次
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 release(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();
}
}
}}
3. 编写增删改的方法，executeupdate
> SQL的注入问题
SQL存在漏洞，会被攻击导致数据泄露， SQL会被拼接or
> PrepareStatement对象
可以防止SQL注入，效率更好。
本质是把传递进来的参数当做字符，假设其中存在转义字符，会被直接转义
public void add(Config config){
String sql = "insert into category values(null,?,?)";
try {
Connection c = DBUtil.getConnection();
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, config.key);
ps.setString(2, config.value);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
while (rs.next()) {
int id = rs.getInt(1);
config.id = id;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
是初始化SQL，先把这个SQL提交到数据库中进行预处理，多次使用可提高效率。 在SQL中用?替换变量；通过set不同数据只需要生成一次执行计划，可以重用。
idea连接数据库
用dbnavigator连接数据库
原作者链接https://www.pianshen.com/article/43431132747/
双击就能打开数据库和数据表。
事务
开启事务conn.setAutocommit(false);
一组业务执行完成，提交事务
可以在catch语句中显示的定义回滚语句，但默认失败就会回滚。
[1]: http://blog.whxblog.club/usr/uploads/2021/05/2058445352.png