mysql使用
...大约 14 分钟
Java
Oracle
Mysql
1. 基本的select语句
DDL: 数据定义语言
1. create
2. drop
3. alter
DML: 数据操作语言
1. insert
2. delete
3. update
DCL: 数据控制语言
1. grant
2. revoke
3. commit
4.rollback
5. savepoint
DQL: 数据查询语言
select
select...from...join...on....where....and...or...group by...having....order by...
#方式1:
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2:
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中:
#(1)from:从哪些表中筛选
#(2)on:关联多表查询时,去除笛卡尔积
#(3)where:从表中筛选的条件
#(4)group by:分组依据
#(5)having:在统计结果中再次筛选
#(6)order by:排序
#(7)limit:分页
执行顺序:
from -> where -> group by -> having -> select -> distinct -> order by -> limit
SQL执行原理:
- 先执行from,若存在多表,则:
- 求笛卡尔积,相当于得到虚拟表(virtual table) vt1-1
- on筛选,在vt1-1基础上筛选,得到vt1-2
- 添加外部行(外连接的外部行),在vt1-2的基础上添加外部行,得到vt1-3
- 若操作两张以上的表,会重复以上步骤,知道所有表处理完。得到原始数据,最终表vt1
- where阶段,vt1筛选过滤,得到vt2
- gruop by 和 having ,vt2进行分组和分组过滤 ,分别得到vt3、vt4
- select distinct,筛选表中字段,distinct去掉重复行,得到vt5-1 vt5-2
- order by ,提取想要的字段后,根据字段排序,得到vt6
- limit,在vt6基础上,取出指定行的记录,得到最终结果,vt7
2. 数据类型
内置函数,单行函数,聚合函数
子查询,单行子查询,多行子查询,不相关子查询,相关子查询
优先使用自然连接,而不是子查询
JDBC
1. 使用
@Test
public void testConnection5() throws Exception {
//1.加载配置文件
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3.加载驱动
Class.forName(driverClass);
//4.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
user=root
password=root
url=jdbc:mysql://127.0.0.1:3306/yygh_hosp?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
driverClass=com.mysql.cj.jdbc.Driver
2. 数据库连接池:
public class TestDruid {
public static void main(String[] args) throws Exception {
Properties pro = new Properties(); pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall
- 详细配置参数:
配置 | 缺省 | 说明 |
---|---|---|
name | 配置这个属性的意义在于,如果存在多个数据源,监控的时候可以通过名字来区分开来。 如果没有配置,将会生成一个名字,格式是:”DataSource-” + System.identityHashCode(this) | |
url | 连接数据库的url,不同数据库不一样。例如:mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto | |
username | 连接数据库的用户名 | |
password | 连接数据库的密码。如果你不希望密码直接写在配置文件中,可以使用ConfigFilter。详细看这里:https://github.com/alibaba/druid/wiki/使用ConfigFilter | |
driverClassName | 根据url自动识别 这一项可配可不配,如果不配置druid会根据url自动识别dbType,然后选择相应的driverClassName(建议配置下) | |
initialSize | 0 | 初始化时建立物理连接的个数。初始化发生在显示调用init方法,或者第一次getConnection时 |
maxActive | 8 | 最大连接池数量 |
maxIdle | 8 | 已经不再使用,配置了也没效果 |
minIdle | 最小连接池数量 | |
maxWait | 获取连接时最大等待时间,单位毫秒。配置了maxWait之后,缺省启用公平锁,并发效率会有所下降,如果需要可以通过配置useUnfairLock属性为true使用非公平锁。 | |
poolPreparedStatements | false | 是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。 |
maxOpenPreparedStatements | -1 | 要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100 |
validationQuery | 用来检测连接是否有效的sql,要求是一个查询语句。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会其作用。 | |
testOnBorrow | true | 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 |
testOnReturn | false | 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能 |
testWhileIdle | false | 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 |
timeBetweenEvictionRunsMillis | 有两个含义: 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据,详细看testWhileIdle属性的说明 | |
numTestsPerEvictionRun | 不再使用,一个DruidDataSource只支持一个EvictionRun | |
minEvictableIdleTimeMillis | ||
connectionInitSqls | 物理连接初始化的时候执行的sql | |
exceptionSorter | 根据dbType自动识别 当数据库抛出一些不可恢复的异常时,抛弃连接 | |
filters | 属性类型是字符串,通过别名的方式配置扩展插件,常用的插件有: 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall | |
proxyFilters | 类型是List,如果同时配置了filters和proxyFilters,是组合关系,并非替换关系 |
3. Apache-DBUtils工具类:
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。
MyBatis
1. 引入
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
spring:
datasource:
url: jdbc:mysql://localhost:3306/atguigudb?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
mybatis:
mapper-locations: classpath:mapper/*.xml
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 打印执行sql
lazy-loading-enabled: true #开启延迟加载
type-aliases-package: com.apple.demo.bean
2. Mybatis的增删改查:
注意
:
1、查询的标签select必须设置属性resultType或resultMap,用于设置实体类和数据库表的映射关系resultType:自动映射,用于属性名和表中字段名一致的情况
resultMap:自定义映射,用于一对多或多对一或字段名和属性名不一致的情况
2、当查询的数据为多条时,不能使用实体类作为返回值,只能使用集合,否则会抛出异常TooManyResultsException;但是若查询的数据只有一条,可以使用实体类或集合作为返回值
3. Mybatis获取参数值的两种方式
建议参数的设置以 两种方式为主,一是实体类对象,二是使用注解命名参数
/**
* mapper接口的不同参数类型,xml获取参数值:
* 1.单个参数
* xml 以${} #{} 访问
* 2.多个参数
* mybatis自动封装多个参数到map {arg0: v1,arg1:v2}或{param0:v1,param1:v2}
* xml以mybatis自动设置的键值获取参数
*
* 3.参数类型为map
* xml以自己设置的键值获取参数
*
* 4.参数是实体类类型
* 实体类是属性和属性值
* xml以实体类的属性名获取属性值
*
* 5.使用@Param命名参数
* 使用@Param("value")设置键值 mybatis设置键值的方式有两种,一是以value为键,二是以param0,param1,。。。
* xml以value为键获取参数值
*
*
* 建议参数的设置以 两种方式为主,一是实体类对象,二是使用注解命名参数
*/
4. MyBatis的各种查询功能
/**
* mybaits的各种查询功能
*
* 1. 若查出的数据只一条,
* a.实体类接收
* b.集合接收
* c.map集合
* 2. 若查出的数据有多条,
* a.list集合接收
* b.map类型的list集合接收
* c.添加@Mapkey使用某个字段作为键,使用map接收
*
* MyBatis中设置了默认的类型别名
*
*/
5. 特殊sql的执行
/**
* 特殊的sql查询
* 1.模糊查询
* "%"#{}"%"
* 2.批量删除
* int deleteMore(@Param("ids") String ids);
* delete from t_user where id in (${ids})
* 3.动态表名
* 表名作为参数 #{tableName}
* 4.获取自增的主键
* 插入语句的返回值是固定的,不能更改,所以主键要另寻存储的地方
* useGeneratedKeys="true" keyProperty="id"
* useGeneratedKeys: 设置当前标签中的sql使用了自增的主键
* keyProperty: 将自增的主键的值赋值给传输到映射文件中的参数的某个属性
*/
6. 自定义映射ResultMap
/**
* 解决字段名和属性名不一致的情况:
* 1. 为字段名起别名,保持和属性名的一致
* select id,emp_name empName,age,sex,email,did from t_emp
* 2. 设置全局并配置,将_自动映射为驼峰
* map-underscore-to-camel-case: true
* 3.通过resultMap 设置自定义的映射关系
* <resultMap id="empResultMap" type="com.apple.demo.bean.Emp">
* <id property="eid" column="eid"></id>
* <result property="empName" column="emp_name"></result>
* <result property="age" column="age"></result>
* <result property="sex" column="sex"></result>
* <result property="email" column="email"></result>
* </resultMap>
*
* 处理多对一的映射关系:
* 1. 级联属性
* <result property="dept.did" column="did"></result>
* <result property="dept.deptName" column="dept_name"></result>
* 2. association
* <association property="dept" javaType="com.apple.demo.bean.Dept">
* <id property="did" column="did"></id>
* <result property="deptName" column="dept_name"></result>
* </association>
* 3. 分步查询
* <association property="dept"
* select="com.apple.demo.dao.DeptMapper.getEmpAndDeptByStepTwo"
* column="did">
* </association>
*
* 分步查询的优点是可以实现延迟加载:
* lazy-loading-enabled: true
* emp.getEmpName() 只是用第一步的sql,不会加载其他的sql
*
* fetchType="lazy | eager" 延迟加载 开启 | 关闭
*
*
* 处理多对一的映射关系:
* 1. collection
* <collection property="emps" ofType="com.apple.demo.bean.Emp">
* <id property="eid" column="eid"></id>
* <result property="empName" column="emp_name"></result>
* <result property="age" column="age"></result>
* <result property="sex" column="sex"></result>
* <result property="email" column="email"></result>
* </collection>
* 2. 分布查询
* <collection property="emps"
* select="com.apple.demo.dao.EmpMapper.getDeptAndEmpByStepTwo"
* column="did">
* <id property="eid" column="eid"></id>
* <result property="empName" column="emp_name"></result>
* <result property="age" column="age"></result>
* <result property="sex" column="sex"></result>
* <result property="email" column="email"></result>
* </collection>
*/
7. 动态sql
/**
* 动态sql:
* 1. if: 根据标签中test属性所对应的表达式决定标签中的内容是否需要拼接到sql中
* <select id="getEmpByCondition" resultType="com.apple.demo.bean.Emp">
* select * from t_emp where 1=1
* <if test="empName != null and empName != ''">
* and emp_name = @{empName}
* </if>
* <if test="age != null and age != ''">
* and age = @{age}
* </if>
* <if test="sex != null and sex != ''">
* and sex = @{sex}
* </if>
* <if test="email != null and email != ''">
* and email = @{email}
* </if>
* </select>
* 2. where: 当where标签中有内容时,会自动生成where关键字,并且将内容前多余的and或or去掉,
* 当where标签中没有内容时,此时的where标签没有任何效果
* 注意:where标签不能去掉内容后面多余的and 或 or
* <where>
* <if test="empName != null and empName != ''">
* and emp_name = @{empName}
* </if>
* <if test="age != null and age != ''">
* and age = @{age}
* </if>
* </where>
*
* 3. trim:
* 若标签中有内容时:
* prefix | suffix : 将trim标签中内容前面或后面添加指定内容
* prefixOverrides | suffixOverrides : 将trim标签中内容前面或后面去掉指定内容
* 若没有内容,没有任何效果
* 4. choose,when,otherwise 相当于 if。。。else if.... else...
* <choose>
* <when test="">
* case1
* </when>
* <when test="">
* case2
* </when>
* <when test="">
* case3
* </when>
* <otherwise>
* else case
* </otherwise>
* </choose>
*
* 5. foreach:
* 批量删除:
* <delete id="deleteMoreByArray">
* delete from t_emp where eid in
*
* <foreach collection="eids" item="eid" separator="," open="(" close=")">
* #{eid}
* </foreach>
*
* </delete>
*
* <delete id="deleteMoreByArray">
* delete from t_emp where
*
* <foreach collection="eids" item="eid" separator="or">
* eid = #{eid}
* </foreach>
*
* </delete>
*
* 批量添加:
* <insert id="insertMoreByList">
* insert into t_emp values
* <foreach collection="emps" item="emp" separator=",">
* (null,#{emp.empName},#{emp.age},#{amp.sex},#{emp.email},null)
* </foreach>
* </insert>
*
* 6. sql标签:
* <sql id="empColumns">eid,ename,age,sex,email</sql>
* <include refid="empColumns"></include>
*
*
*/
8.Mybatis的缓存
一级缓存
一级缓存是SqlSession级别
的,通过同一个SqlSession查询的数据会被缓存,下次查询相同的数据,就会从缓存中直接获取,不会从数据库重新访问
二级缓存
二级缓存是SqlSessionFactory级别
,通过同一个SqlSessionFactory创建的SqlSession查询的结果会被
缓存;此后若再次执行相同的查询语句,结果就会从缓存中获取
缓存查询顺序
- 先查询二级缓存,因为二级缓存中可能会有其他程序已经查出来的数据,可以拿来直接使用。
- 如果二级缓存没有命中,再查询一级缓存
- 如果一级缓存也没有命中,则查询数据库
- SqlSession关闭之后,一级缓存中的数据会写入二级缓存
MyBatis-Plus
1. 引入
<properties>
<java.version>1.8</java.version>
<mp.version>3.5.1</mp.version>
<freemarker.version>2.3.31</freemarker.version>
</properties>
<dependencies>
<!--mybatis-plus启动器-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mp.version}</version>
</dependency>
<!--lombok用于简化实体类开发-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--自动生成代码-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mp.version}</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>${freemarker.version}</version>
</dependency>
</dependencies>
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&userSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
table-prefix: t_
id-type: assign_id
type-enums-package: com.atguigu.mybatisplus.enums # 枚举类
2. 基本CRUD
BaseMapepr
@Mapper
public interface UserMapper extends BaseMapper<User> {
Page<User> selectPageVo(@Param("page") Page<User> page,@Param("age") Integer age);
}
IService
public interface UserService extends IService<User> {
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
3. 常用注解
1. @TableName
2. @TableId
3. @TableField
4. @TableLogic
4. 条件构造器和常用接口
- Wrapper : 条件构造抽象类,最顶端父类
- AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
- QueryWrapper : 查询条件封装
- UpdateWrapper : Update 条件封装
- AbstractLambdaWrapper : 使用Lambda 语法
- LambdaQueryWrapper :用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper : Lambda 更新封装Wrapper
5. 插件
1. 分页插件
1. 添加配置类
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 分页插件
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
// 乐观锁插件
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
}
2. 使用
public void testPageVo() {
Page<User> page = new Page<>(2,3);
userMapper.selectPageVo(page,24);
System.out.println(page.getRecords());
System.out.println(page.getPages());
System.out.println(page.getTotal());
System.out.println(page.getCurrent());
System.out.println(page.hasNext());
System.out.println(page.hasPrevious());
}
2. 乐观锁插件
1. 配置类同上
2. 使用
public void testProduct02() {
//1、小李
Product p1 = productMapper.selectById(1L);
System.out.println("小李取出的价格:" + p1.getPrice());
//2、小王
Product p2 = productMapper.selectById(1L);
System.out.println("小王取出的价格:" + p2.getPrice());
//3、小李将价格加了50元,存入了数据库
p1.setPrice(p1.getPrice() + 50);
int result1 = productMapper.updateById(p1);
System.out.println("小李修改结果:" + result1);
//4、小王将商品减了30元,存入了数据库
p2.setPrice(p2.getPrice() - 30);
int result2 = productMapper.updateById(p2);
if (result2 == 0) {
p2 = productMapper.selectById(1L);
p2.setPrice(p2.getPrice() - 30);
result2 = productMapper.updateById(p2);
}
System.out.println("小王修改结果:" + result2);
//最后的结果
Product p3 = productMapper.selectById(1L);
//价格覆盖,最后的结果:70
System.out.println("最后的结果:" + p3.getPrice());
}
6. 代码生成器
1. 引入依赖
<!--自动生成代码-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.31</version>
</dependency>
2. 配置生成器类的代码
import com.baomidou.mybatisplus.generator.FastAutoGenerator;
import com.baomidou.mybatisplus.generator.config.OutputFile;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;
import java.util.Collections;
/**
* @author dongzhaohe
* @version V1.0
* @ClassName: FastAutoGeneratorTest
* @Description:
* @date 2022/4/22 12:09
*/
@SpringBootTest
public class FastAutoGeneratorTest {
@Test
public void test() {
FastAutoGenerator.create("jdbc:mysql://127.0.0.1:3306/mybatis_plus?characterEncoding=utf-8&userSSL=false&serverTimezone=UTC", "root", "root")
.globalConfig(builder -> {
builder.author("atguigu") // 设置作者
// .enableSwagger() // 开启 swagger 模式
.fileOverride() // 覆盖已生成文件
.outputDir("G://mybatis_plus_g"); // 指定输出目录
})
.packageConfig(builder -> {
builder.parent("com.atguigu") // 设置父包名
.moduleName("mybatisplus") // 设置父包模块名
.pathInfo(Collections.singletonMap(OutputFile.mapperXml, "G://mybatis_plus_g"));// 设置mapperXml生成路径
})
.strategyConfig(builder -> {
builder.addInclude("t_user") // 设置需要生成的表名
.addTablePrefix("t_", "c_"); // 设置过滤表前缀
})
.templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker 引擎模板,默认的是Velocity引擎模板
.execute();
}
}
7. 多数据源
1. 引入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.0</version>
</dependency>
2. 配置多数据源
spring:
# 配置数据源信息
datasource:
dynamic:
# 设置默认的数据源或者数据源组,默认值即为master
primary: master
# 严格匹配数据源,默认false.true未匹配到指定数据源时抛异常,false使用默认数据源
strict: false
datasource:
master:
url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&userSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
slave_1:
url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf-8&userSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
3. 使用
@DS("slave_1")
@Service
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements ProductService {
}
Servlet
Spring
ioc
aop
SpringMVC
使用spring核心整合mvc
SpringBoot
简化springmvc的配置
强大的自动配置功能
ES6
Vue
Powered by Waline v2.15.5