1、MySql命令
1.1、创建数据库命令
create database if not exists `study`
1.2、创建表命令
CREATE TABLE `actor` (
`actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
1.3、修改表
alter table 旧表名 rename 新表名 -- 修改表名
alter table 表名 add 字段名称 字段属性 -- 新增表字段
alter table 表名 modify 表字段 表字段属性 -- 修改字段属性
alter table 表名 change 旧字段名 新字段名 新字段属性 -- 修改字段属性以及重命名
alter table 表名 drop 字段名 -- 删除表字段
drop table 表名 -- 删除表
1.4、操作表数据
新增
表名后面的字段,必须与values后面的值是一一对应的
字段省略不写则代表全部字段,values需要给所有字段一一对应上值
-- 插入单条数据
insert into student(id,name,age,grade) values(1,'a',40,90);
insert into student(name,age,grade) values('a',40,98);
insert into student values(1,'a',18,100);
-- 插入多条数据
insert into student values
(1,'a',18,100),
(2,'b',20,100);
修改
update actor set 字段名 = 新值 where 条件(id = 1) -- 修改一个字段值
update actor set 字段名 = 新值,字段名 = 新值 where 条件(id = 1) -- 修改多个字段值用,隔开
删除
delete from 表名 where actor_id = 1 -- 根据条件删除
delete from 表名 -- 所有数据一条一条删除
truncate table 表名 -- 记录原来建表语句,然后删除整个表,再重新创建一个表,效率较快
1.5、其他命令
drop database `数据库名` -- 删除数据库
use `数据库名` -- 使用某数据库
show databases -- 查看所有数据库信息
show tables -- 查看所有表
show create database `数据库名` -- 查看创建数据库语句
show create table `表名` -- 查看创建表语句
desc `表名` -- 显示表的结构
explain select * from agent where id = 1 -- 查看sql执行状况
1.6、select语法
2、操作符
操作符 | 含义 |
---|---|
= | 等于 |
<> 或者 != | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
between … and … | 在 … 和 … 范围内(包含两边…) where id between 1 and 5 |
and | 并且 |
or | 或者 |
in | 在一个数组内 where id in(‘a’,‘b’,‘c’) |
like | 模糊查询 where name like ‘%佳__’ (%代表0到任意数量字符,一个下划线代表一个字符,两个下划线代表两个字符) |
3、数据库表引擎
MyISAM | InnoDB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持(表锁) | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大(约为MyISAM2倍) |
对比: | 节约空间,速度较快 | 安全性高,事务处理,多表多用户操作 |
在物理空间存放的位置
所有数据库文件都存在data目录下,本质还是文件的存储
- InnoDB 在数据库中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MyISAM 对应的文件
- *.frm 表结构定义的文件
- *.MYD 数据文件
- *.MYI 索引文件
4、查询命令
查询表
-- 查询所有字段
select * from 表名
-- 查询指定字段
select id,name from 表名
别名 as
select user_name as name from 表名
拼接函数 concat
select concat(last_name,'-',last_update,first_name) from 表名 -- concat()中放置任意字符,字段名
去重
select distinct actor_id from 表名 -- 如果有多个一样的actor_id,那么将会把重复的去掉,只查出来一条
条件查询
select distinct actor_id from actor where actor_id = 1
模糊查询(通过like和%以及_配合查询)
-- 查询姓刘的,后面名字可以任意数量
select name from student where name like '刘%'
-- 查询姓刘的,后面名字是一个字的
select name from student where name like '刘_'
-- 查询名字中带刘的
select name from student where name like '%刘%'
-- %代表0到任意个字符
-- _代表一个字符
查询字段值不为空的
select * from actor where last_name is not null
查询字段值是空的
select * from actor where last_name is null
查询其他
select version() -- 查询mysql版本号
select 30*3 -- 查询数字计算
5、事务
要么都成功,要么都失败
5.1、事务原则
事务原则:ACID原则 原子性、一致性、隔离性、持久性
原子性:
表示sql要么都成功,要么都失败
一致性:
事务前后的数据完整性要保证一致
持久性:
持久性表示事务完成后,不会随着外界的原因导致数据丢失
事务一旦提交则不可逆,被持久化到数据库中
隔离型:
隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离
存在干扰就会出现事务隔离问题
5.2、事务隔离级别(事务隔离会导致的一些问题)
脏读:
一个事务读取到了另外一个事务还未提交的数据
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同(不一定是错误,只是某些场合不对)
虚读(幻读):
在一个事务内读取到了别人的事务插进来的一条数据,导致前后读取不一致(一般是行影响,多了一行)
6、模拟事务
CREATE TABLE `shop` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`money` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- mysql默认开启事务自动提交,需要先关闭(0:关闭,1:开启。默认是1开启状态)
set autocommit = 0;
-- 开启事务
start transaction;
update shop set money = money - 500 where id = 1; -- A减500
update shop set money = money + 500 where id = 2; -- B加500
commit; -- 提交事务,事务一旦被提交就会被持久化
rollback; -- 回滚事务
set autocommit = 1; -- 开启自动提交
7、索引
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
7.1、索引的分类
- 主键索引(PRIMARY KEY)
- 唯一索引(UNIQUE KEY)
- 常规索引(KEY)
- 全文索引(FullText)
7.2、索引的使用
-
显示所有的索引信息
show index from student
-
增加一个索引
- create创建
-- 语法 create [unique|primary|fulltext] index index_name on table_name(colums) -- 例子 create index index_name on user(name) -----普通索引 create index unique index_email_unique on user(email) -----唯一索引 create index primary index_id_primary on user(id) -----主键索引
- alert创建
-- 语法 alter table table_name add primary index_name(colums) -- 例子 alter table user add index index_name(name) ------普通索引 alter table user add unique index_email_unique(email) ------唯一索引 alter table user add primary index_id_primary(id) ------主键索引
-
删除索引
drop index index_name on table_name
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
8、三大范式
8.1、三大范式描述
第一范式
原子性:保证表中每一个字段都不可再分,否则会有歧义,不知道这个字段存什么值,也不知道怎么取
第二范式
前提:满足第一范式
每张表只描述一件事情
第三范式
前提:满足第一范式和第二范式
确保每一行中所有列的数据都要和主键直接相关,而不能间接相关
8.2、规范 和 性能 问题
鱼和熊掌不可兼得
ali规范:关联查询的表不能超过三张表,会影响查询速度
- 考虑商业化的需求和目标,成本、用户体验… 数据库的性能更加重要
- 考虑性能的时候,适当的考虑一下规范性
- 一般遵循第一第二范式即可,过于细分就会导致拆分很多表,查询速度慢
- 故意增加一些冗余字段,可以多表查询变为单表查询
- 故意增加一些计算列,大数据量可以降低为小数据量的查询
9、JDBC(Java DataBase Connectivity) -----Java数据库连接)
9.1、测试jdbc连接数据库
-
idea新建空项目
-
建表
CREATE TABLE `agent` ( `id` int NOT NULL AUTO_INCREMENT, `agent_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `agent_name` varchar(255) COLLATE utf8_bin DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`,`agent_num`) USING BTREE, FULLTEXT KEY `agent_name` (`agent_name`) ) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
-
导入
mysql-connector-java
jar包- 新建lib文件
- 拖入jar包
- 将lib文件 Add As Library
-
编写代码
import java.sql.*; public class JDBC { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.连接信息 String url = "jdbc:mysql://127.0.0.1:3306/study?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "***"; String pwd = "***"; //3.获取数据库对象 Connection connection = DriverManager.getConnection(url, username, pwd); //4.获取执行sql的对象 Statement statement = connection.createStatement(); //5.执行sql,获取结果 String sql = "select * from agent"; ResultSet resultSet = statement.executeQuery(sql);//查询用executeQuery,更新和删除使用executeUpdate,会返回一个受影响的行数,execute可以执行任意sql while (resultSet.next()){ System.out.println("id:"+resultSet.getObject("id")); System.out.println("agent_num:"+resultSet.getObject("agent_num")); System.out.println("agent_name:"+resultSet.getObject("agent_name")); System.out.println("create_time:"+resultSet.getObject("create_time")); System.out.println("---------------------------------------------------------"); } //6.关闭连接 resultSet.close(); statement.close(); connection.close(); } }
9.2、java连接数据库步骤总结
- 加载驱动
- 赋值连接信息
- 通过DriverManager获取数据库连接对象
- 通过数据库对象获取执行sql的对象
- 执行sql
- 关闭连接
9.3、抽取jdbc工具类
- 定义配置文件
db.properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/study?useUnicode=true&characterEncoding=utf8&useSSL=true
username = ***
pwd = ***
- 代码编写
package util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String pwd = null;
//static块(以及静态变量和静态方法)只会在类第一次被加载到JVM时执行或被访问。
//一旦类被加载,static块、静态变量和静态方法就会被保留在内存中,直到JVM关闭。
//再次引用或访问它们时,不会再次执行或加载。这是因为它们是类级别的,而不是每次创建对象时都需要的数据或代码。
static {
try {
//获取配置文件
InputStream resourceAsStream = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
//获取配置文件中参数值
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
pwd = properties.getProperty("pwd");
//驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 返回数据库连接对象
*
* @return
* @throws SQLException
*/
public static Connection connect() throws SQLException {
Connection connection = DriverManager.getConnection(url, username, pwd);
return connection;
}
/**
* 关闭资源
*/
public static void closeResource(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
- 调用工具类
package util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestJDBCUtil {
public static void main(String[] args) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
try {
connection = JDBCUtil.connect();
statement = connection.createStatement();
String sql = "select * from agent";
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id"));
System.out.println("agent_num:" + resultSet.getObject("agent_num"));
System.out.println("agent_name:" + resultSet.getObject("agent_name"));
System.out.println("create_time:" + resultSet.getObject("create_time"));
System.out.println("---------------------------------------------------------");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.closeResource(resultSet, statement, connection);
}
}
}
9.4、sql注入
sql存在漏洞会被攻击,数据泄露
自定义拼接一个百分百成立的条件,如 or 1 = 1,则sql百分百会被执行
package util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestSqlZhuRu {
/**
* 根据id查询代理协议
* @param id
*/
public static void selectAgentById(String id){
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
try {
connection = JDBCUtil.connect();
statement = connection.createStatement();
String sql = "select * from agent where id = " + id;
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id"));
System.out.println("agent_num:" + resultSet.getObject("agent_num"));
System.out.println("agent_name:" + resultSet.getObject("agent_name"));
System.out.println("create_time:" + resultSet.getObject("create_time"));
System.out.println("---------------------------------------------------------");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.closeResource(resultSet, statement, connection);
}
}
public static void main(String[] args) {
//正常调用
// TestSqlZhuRu.selectAgentById("1");
//不正常调用,自定义拼接sql,会将所有数据查出来,信息泄露
TestSqlZhuRu.selectAgentById("1 or 1 = 1");
}
}
9.5、如何防止sql注入
使用PreparedStatement预编译功能 预编译sql,先写sql,然后不执行,然后手动给参数赋值,再执行
它的本质就是将传递过来的参数当作字符,假设其中存在转义字符,比如说’ ',会被直接转义忽略
package util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
* 通过PreparedStatement占位符,防止sql注入
*/
public class GuardSql {
/**
* 根据id查询代理协议
* @param id
*/
public static void selectAgentById(String id){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Connection connection = null;
try {
//获取数据库连接对象
connection = JDBCUtil.connect();
//预编译sql,不执行,使用?作为占位符
String sql = "select * from agent where id = ? ";
preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值 第一个参数是?的位置,第二个参数是要给?赋的值
preparedStatement.setString(1,id);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println("id:" + resultSet.getObject("id"));
System.out.println("agent_num:" + resultSet.getObject("agent_num"));
System.out.println("agent_name:" + resultSet.getObject("agent_name"));
System.out.println("create_time:" + resultSet.getObject("create_time"));
System.out.println("---------------------------------------------------------");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.closeResource(resultSet, null, connection);
}
}
public static void main(String[] args) {
//不正常调用,自定义拼接sql,但是通过PreparedStatement的占位符还是只能查出来一条数据
GuardSql.selectAgentById("1 or 1 = 1");
}
}