mysql的使用以及mybatis中的SQL语句

栖西

发布于 2023-10-17 08:19:22

3540

发布于 2023-10-17 08:19:22

文章被收录于专栏：栖西栖西

SQL具体用法

对应着最基本的增删改查

插入
insert into 表名(字段名...) values(值...);

删除
delete from 表名 where 条件字段 = 值;
例如：delete from student2 where id = 12;

清空整张表  效率高  也可以理解为删除所有行（delete也可以）
truncate table student2;

更新，切记更新的时候一定要加where条件，不然就是某个字段全表都一样，恐怖如斯！
update 表名 set 字段名 = 新的值 where 条件字段 = 要修改的数据值;
例如：update student2 set age = 20 where id = 15;

SQL查询语句基本格式
SELECT  查询内容
FROM    表名
WHERE   条件表达式
GROUP   BY  待分组的列名
HAVING  列名
ORDER   BY  待排序的列名

执行顺序为：
	先执行from语句
	在执行where条件
	在执行group by
	在执行having语句
	在执行select
	最后执行 order by
	
select			5	
from			1
where			2
group by		3
having			4
order by		6

查询语句详解

group by 	分组
分组就是把具有相同的数据值的行放在同一组中。
select age,count(name) as "个数" from student2 group by age;

分组函数会自动忽略null
分组函数不可直接使用在where子句当中
因为group by是在where执行之后才会执行的。

having
WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
select age,count(name) as "个数" from student2 group by age having age >20;

order by	排序（ASC 默认升序，DESC 降序）
根据某个字段进行排序
select * from student2 order by age;

distinct	去重
查询出student2表所有不同的年龄的值
select distinct age from student2;

limit		限制返回的行数，下面会详细介绍
常用于分页查询
1、有一个参数的时候，是查询前多少条数据
select * from student2 limit 5;
2、俩个参数时，第一个参数是偏移量（也就是跳过多少条数据），第二个是显示的条数（即每页的大小）
select * from student2 limit 20,5;

过滤条件
>		< 		>= 		<=		!=		=   
between and	在...之间	例如：select * from student2 where  age between 15 and 17;
is null  	某个字段为空  例如：select * from student2 where name  is null;
is not null 某个字段不为空
in () 		查询在这个范围之内的
not in()	不在这个范围之内，可以传多个值
and 		同时满足条件，且
or  		或的条件，只要有一个成立即可


like  模糊查询
查询名字里包含王的
select * from student where name like "%王%";
%和_的区别：
	% 匹配0或多个字符
	_ 匹配单个字符


子查询
select语句当中嵌套select语句，被嵌套的select语句是子查询。
	子查询可以出现在哪里？
		select
			..(select).
		from
			..(select).
		where
			..(select).
			
示例1：where后面嵌套子查询		
子查询的结果作为 WHRER 语句的过滤条件
select * 
		from 
	student2 
		where 
	id in 
		(select id from student2 where age > 18);

示例2：from后面嵌套子查询，查询结果当做临时表t，让t表和salgrade s表连接
select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

示例3：select后面嵌套子查询
select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

union连接查询，可以将查询结果集相加
使用or或者in 都可以替代它，效果是一样的

案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');
第三种：使用union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';



多表关联
多表连接可以替换子查询，并且比子查询的效率一般会更快。

自连接：一张表看做两张表。自己连接自己。

什么是外连接，和内连接有什么区别？
	内连接：
		假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
		AB两张表没有主副之分，两张表是平等的。
	外连接：
		假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
		的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
	外连接的分类？
		左外连接（左连接）：表示左边的这张表是主表。
		右外连接（右连接）：表示右边的这张表是主表。
		
外连接最重要的特点是：主表的数据无条件的全部查询出来。
		
多表连接可分为两种形式：
SQL92语法：比较旧一些,其实就是隐式内连接，通过where条件，去等值比较
SQL99语法：新的语法形式，建议使用，左连接和右连接等

内连接
select s.id,s.name,s.age,c.cno,c.cname
from student s ,class_student r, class c 
where s.id = r.sid and c.cno = r.cid;

内连接另一种写法：（inner join ... on）  inner可以省略
select 
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

左连接(left join ... on)   省略了outer  ( left outer join ... on)   
条件多的话，左连接后再写where条件

select s.id,s.name,s.age,c.cno,c.cname
from student s 
left join  class_student r on r.sid = s.id
left join class c on c.cno = r.cid;

右连接 right join

SQL99标准格式：
	select ...
		A
		join
			B
		on
			连接条件
		where
			...
	
SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。


concat	连接字段
select concat(trim(id),'(',trim(name),')') as id_name from student2 limit 5;
trim 用于去除空格

分页查询

无论使用什么数据库，分页都是重头戏，必须要掌握的知识点。

关于Oracle的使用可以看我的这一篇文章 oracle的使用

limit分页查询，是mysql特有的，Oracle使用的rownum

limit offset ,size
参数解释：
	offset  偏移量，也可以理解为越过多少条
	size  	每页显示的条数

案例：
查询前5条数据
select * from student limit 0,5;

查询第15到20条数据
select * from student limit 15,5;

查询第25到第35条数据
select * from student limit 25,10;

索引

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式：
			第一种方式：全表扫描
			第二种方式：根据索引检索（效率很高）
			
索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中
的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改
这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

创建索引对象：
			create index 索引名称 on 表名(字段名);
删除索引对象：
			drop index 索引名称 on 表名;

什么情况下考虑给字段添加索引：
1、数据量大
2、该字段很少进行修改，因为索引页需要维护
3、该字段经常出现在where条件中

注意：主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。

索引底层采用的数据结构是：B + Tree

索引的实现原理？
通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，
最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

索引的分类？
	单一索引：给单个字段添加索引
	复合索引: 给多个字段联合起来添加1个索引
	主键索引：主键上会自动添加索引
	唯一索引：有unique约束的字段上会自动添加索引
	....

索引失效的条件：
1、使用模糊查询的时候 like '%A%';

视图

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

对视图的操作，会影响到原表数据（通过视图影响原表数据的，不是直接操作的原表）

视图：站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

-- 创建视图
create view t_student_view as select * from t_student

-- 查询数据
select * from t_student_view

-- 修改视图
update t_student_view set sex = '女' ,address = '杭州西湖' where sid = 2

-- 删除视图
drop view t_student_view

函数

AVG()		 返回某列的平均值
COUNT() 	 返回某列的行数
MAX()		 返回某列的最大值
MIN() 		 返回某列的最小值
SUM() 		 返回某列值之和

mysql时间函数  时间类型     使用datetime   yyyy-MM-dd HH:mm:ss
DATE_FORMAT(entrytime, '%Y-%m-%d')='2019-02-22'
DATE_FORMAT('2021-12-16 22:23:00','%Y-%m-%d %H:%i:%s')

case when 的使用
select id,(case role_id when 1 then '管理员' when 2 then '普通用户' else 'VIP会员' end) role_id, 
username from tbl_user
 
插入时间  sysdate()
insert into teacher(id,entrytime) values(99,sysdate())

concat()	 字符串拼接，可以传多个参数
trim()		 去除空格

CurDate() 	 返回当前日期
CurTime() 	 返回当前时间
Now() 		 返回当前日期和时间
DateDiff() 	 计算两个日期之差		select datediff("1999-12-12","1999-11-30");  返回12
DayOfWeek()  对于一个日期，返回对应的星期几  select DayOfWeek("2021-08-15");  返回1   1代表星期日

ifnull() 	 空处理函数
案例：
	计算每个员工的年薪？
		select ename,(sal+comm)*12 as yearsal from emp;
			重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
		使用ifnull函数：
		select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

对表的操作

我认为这个很不常用，表一旦确定了，实际开发中表结构基本是不会动的，运用最多的还是查询语句。

新增一列
alter table student add address varchar(64);

删除一列
alter table student drop column address;

修改一列，修改字段名（也可以顺便修改字段类型   新字段名后面是字段类型）
alter table 表名 change 原字段名 新字段名 char(4);

删除表
drop table table_name;

建表语句：  注解（primary key）  自动递增（auto_increment）
示例1：
create table 表名(
id int primary key,
age int,
sex char(2)
);

示例:2：
create table 表名(
id int primary key auto_increment,
age int,
sex char(2)
);

插入语句

普通插入
insert into student2(id,age) value(4,15);

插入检索出来的数据  将student表查出来的数据插入到student2里面
insert into student2(id,name,age) select id,name,age from student where id >4;

建立一张与查询的表结构相同的表，把查询到的所有数据写入到这张表里
insert into student2 select * from student

将一张表的内容插入到新表，想当于复制表
create table student3 as select * from student2;

mybatis中基本的增删改查

parameterType	参数类型：可以传基本数据类型，其他类型，对象，以及Map（java.util.Map）
resultType		返回的类型：可以是基本数据类型，其他类型，对象，以及Map（java.util.Map）
/* SQL中的注释：采用SQL多行注释 */

<select id="loadAllUser" resultType="tbuser">
    select *  from tb_user;
</select>

<insert id="addOneUser" parameterType="TbUser">
    insert into tb_user(username,password) values(#{username},#{password,jdbcType=VARCHAR})
</insert>

<update id="updateUser" parameterType="tbuser">
    update tb_user set username = #{username},password= #{password}
    where uid = #{uid}
</update>

<delete id="deleteUser" parameterType="int">
    delete from tb_user where uid = #{uid}
</delete>

动态SQL

这里写的所有SQL仅为了测试使用，无关任何业务逻辑，只是提供一个解决问题的思路。

元素					   作用					备注
if						 判断语句				单条件分支，大部分都会用到
choose(when、otherwise)	相当于 Java 中的 if else	多条件分支
trim(where、set)			辅助元素			用于处理 SQL 拼接问题
foreach					 循环语句	批量插入， 更新， 查询时经常用到
bind					 创建一个变量， 并绑定到上下文中	用于兼容不同的数据库， 防止 SQL 注入等

choose标签

<!--返回值为map和TblStudent 同样的结果 -->
<!--    <select id="findByCondition" parameterType="java.util.Map" resultType="java.util.Map">-->
<select id="findByCondition" parameterType="java.util.Map" resultType="com.test.oracle.pojo.TblStudent">
    select <include refid="BaseColumn" /> from tbl_student
    <where>
        <choose>
            <when test="name != null and name != ''">
                and name like '%${name}%'
                and status = 'N'
            </when>
            <when test="sex != null and sex != ''">
               and sex = #{sex}
               and status = 'N'
            </when>
            <when test="age != null and age != ''">
                and age = #{age}
            </when>
            <when test="address != null and address != '' ">
              and  address like '%${address}%'
                and status = 'N'
            </when>
            <otherwise>
                and status = 'N'
            </otherwise>
        </choose>
    </where>
</select>

choose的另一个测试，是不是觉得发现新大陆了？这样你以后可以减少很多java的业务代码。直接丢到mapper里去进行判别。

<!--测试加一个不存在数据库的字段  来判别一些信息  测试通过  -->
<select id="selectS1" parameterType="java.util.Map" resultType="java.util.Map">
    select <include refid="BaseColumn" /> from tbl_student
    <where>
        <choose>
            <when test="s1=='1'.toString()">
               and sex = '男' and status = 'N'
            </when>
            <when test="s1=='2'.toString()">
               and sex = '女' and status = 'N'
            </when>
            <otherwise>
                and status = 'D'
            </otherwise>
        </choose>
    </where>
</select>

trim标签

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，
或者给sql语句前拼接 where、set以及values ( 等前缀，或者添加 ) 等后缀，
可用于选择性插入、更新、删除或者条件查询等操作。

属性			   描述
prefix			给sql语句拼接的前缀
suffix			给sql语句拼接的后缀
prefixOverrides	去除sql语句前面的关键字或者字符，比如：and  ,  逗号等
suffixOverrides	去除sql语句后面的关键字或者字符，我案例去除的是逗号

<insert id="insertStudent" parameterType="java.util.Map">
        insert into tbl_student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                id,
            </if>
            <if test="name != null and name != ''">
                name,
            </if>
            <if test="sex != null and sex != ''">
                sex,
            </if>
            <if test="age != null and age != ''">
                age,
            </if>
            <if test="address != null and address != ''">
                address,
            </if>
            <if test="phone != null and phone != ''">
                phone,
            </if>
            <if test="classid != null and classid != ''">
                classid,
            </if>
            <if test="status != null and status != ''">
                status,
            </if>
        </trim>

        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                #{id},
            </if>
            <if test="name != null and name != ''">
                #{name},
            </if>
            <if test="sex != null and sex != ''">
                #{sex},
            </if>
            <if test="age != null and age != ''">
                #{age},
            </if>
            <if test="address != null and address != ''">
                #{address},
            </if>
            <if test="phone != null and phone != ''">
                #{phone},
            </if>
            <if test="classid != null and classid != ''">
                #{classid},
            </if>
            <if test="status != null and status != ''">
                #{status},
            </if>
        </trim>
</insert>

where标签会自动的屏蔽掉第一个连接符and 或 or，必须搭配里面的if使用

<select id="select17" parameterType="user" resultMap="userMap">
    select * from student
    <where>
        <if test="userName!=null and userName!=''">
            username  like '%' #{userName} '%'
        </if>
        <if test="userAddress !=null and userAddress !=''">
            and address  like '%' #{userAddress} '%'
        </if>
    </where>
</select>

另一个测试

<sql id="BaseColumn">
       id,name,sex,age,birthday,address,phone,classId
</sql>

<select id="findByManyParams" parameterType="java.util.Map" resultType="java.util.Map">
        select <include refid="BaseColumn" /> from tbl_student
        <where>
            <if test="name != null and name != ''">
               and name like '%${name}%'
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="address != null and address != ''">
                and address like '%${address}%'
            </if>
            and status ='N'
        </where>
 </select>

set标签

<update id="updateStudent" parameterType="java.util.Map">
        update tbl_student
        <set>
            <if test="name != '' and name != null">
                name = #{name},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="age != null and age != ''">
                age =#{age},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
            <if test="phone != null and phone != ''">
                address = #{phone},
            </if>
            <if test="classid != null and classid != ''">
                address = #{classid},
            </if>
            <if test="status != null and status != ''">
                address = #{status},
            </if>
        </set>
        where id = #{id}
</update>

foreach标签

 foreach标签：用来遍历传递参数的数组参数
            collection 标识传递参数的类型
                        array 数组
                        list 集合
             item：每一次遍历出来的元素，在使用该元素的时候，需要套用#{}
             open：拼接循环的开始符号
             close：拼接循环的结束符号
             separator：元素与元素之间的分割符
<foreach collection="array" item="id" open="(" close=")" separator=",">
		#{id}
</foreach>

用法
 <select id="select18" resultMap="userMap">
        select * from eesy_mybatis
        where id in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
 </select>

bind标签

bind 实质：建立新的映射关系

bind元素标签可以从 OGNL 表达式中创建一个变量井将其绑定到上下文中，
MyBatis中使用mysql的模糊查询字符串拼接（like） 中也可以使用bind来完成。

创建一个 bind 元素标签的变量后 ，就可以在下面直接使用，
使用 bind 拼接字符串不仅可以避免因更换数据库而修改 SQL，且预防 SQL 注入。

bind，也可以用于替换concat拼接函数

案例：

<select id="findByFuzzyAddress" parameterType="String" resultType="java.util.Map">
        <bind name="realName" value="'%'+address+'%'"/>
        select <include refid="BaseColumn" /> from tbl_student
       where address like #{realName}
</select>

mybatis中的大于小于等于

在有些项目里面会遇到这样的写法，不按照这样的格式写会报错

第一种写法

原符号       <        <=      >       >=       &        '        "
替换符号    &lt;    &lt;=   &gt;    &gt;=   &amp;   &apos;  &quot;

示例：查询年龄小于18岁的全部信息
<select id="findByAge" parameterType="String" resultType="java.util.Map">
        select <include refid="BaseColumn" /> from tbl_student
        where age  &lt;  #{age}
</select>

第二种写法

大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>

示例：查询年龄大于等于18岁的全部信息
<select id="findByAge" parameterType="String" resultType="java.util.Map">
        select <include refid="BaseColumn" /> from tbl_student
        where age <![CDATA[ >= ]]> #{age}
</select>

mybatis模糊查询

%和_的区别：

% 匹配0或多个字符

_ 匹配单个字符

使用mybatis模糊查询有三种方式：
 like '%${name}%'
 like concat('%',#{typeName},'%')   推荐使用
 bind标签

<select id="fuzzyLike" parameterType="String" resultType="com.myfdc.domain.TbUser">
    select * from tb_user where username like '%${name}%'
</select>

我建议使用 like concat(’%’,#{typeName},’%’) 当然了也可以使用1上面说的bind标签，一切写法都随个人爱好。

推荐使用的模糊查询

<select id="findByType" parameterType="String" resultType="Shop">
    select id,type_name from shop where type_name like concat('%',#{typeName},'%')
</select>

mybatis的limit实现分页查询

使用limit的时候，参数无需和controller 一一对应，只能写param1和param2，否则会报下面的错误

初学的时候，这里仅仅做一个记录，实际用法在下面，当然了，市面上很多插件都执行分页的效果，但原理都是limit实现分页。

<select id="limitQuery" parameterType="int" resultType="TbUser">
    select * from tb_user limit #{param1},#{param2}
</select>

controller层：

// limit 查询
@RequestMapping("/limit")
public String limitQuery(int offset,int size,Model model){
    List<TbUser> tbUsers = tbUserService.limitQuery(offset, size);
    model.addAttribute("tbUsers",tbUsers);
    return "all";
}

推荐使用这个分页，使用concat避免SQL注入问题

分页查询+模糊查询

dao层

// 模糊查询
List<Shop> findByType(@Param("typeName") String typeName,@Param("offset") int offset,@Param("size") int size);

参数类型parameterType

<select id="findByType" parameterType="String" resultType="Shop">
    select id,type_name from shop where type_name like '%${typeName}%' limit #{offset},#{size}
</select>

使用concat同样的效果

<select id="findByType"  resultType="Shop">
select id,type_name from shop where type_name like concat('%',#{typeName},'%') limit #{offset},#{size}
</select>

注解

dao层使用@Param()注解，和mapper.xml文件的参数相对应
	@Param("size") int size
我建议在dao层的时候每个方法中的参数都加上@Param注解，可映射文件中的参数一一对应

主键id自动生成

<!-- 主键id 自动生成-->
<insert id="save" parameterType="User" useGeneratedKeys="true" keyProperty="id">
    insert into t_users values (#{id},#{name},#{bir},#{sex},#{address})
</insert>

测试

@Test
public void save(){
    User user = new User();
    user.setName("小张").setSex("男").setBir(new Date()).setAddress("上海市浦东区");
    userService.save(user);
    userService.findAll().forEach(u1 -> System.out.println("user = "+u1));
}

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
@Accessors(chain = true)  // 链式调用
public class User implements Serializable {
    private String id;
    private String name;
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date bir;
    private String sex;
    private String address;
}

多表关联结果集映射

也可以不这样去写，返回Map，或者vo类均可。

<resultMap id="empAndDept" type="emp">
        <id column="id" property="id"></id>
        <result column="ename" property="ename"></result>
        <result column="job_id" property="jobId"></result>
        <result column="mgr" property="mgr"></result>
        <result column="joindate" property="joindate"></result>
        <result column="salary" property="salary"></result>
        <result column="bonus" property="bonus"></result>
        <result column="dept_id" property="deptId"></result>
        <association property="dept" column="dept" javaType="dept">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
        </association>
</resultMap>

附录

set names 'utf8';
短暂解决xshell中文问号的问题

mysql数据类型：
date  只包含年月日，不包含时间
datetime  年月日 时分秒
timestamp 日期类型，时间戳

decimal(m,n)  指定整数位与小数位长度的小数类型

命令行
查看支持的存储引擎：
show engines;

导出整个数据库
用法： mysqldump 数据库名 >导出的路径名和导出的文件名 -u用户名 -p密码
例如：
mysqldump xyxy>D:\谷歌浏览器\xyxy.sql -uroot -p123456

导出指定的表，一般情况下直接点击转储数据文件和结构就可以了，不要命令行
用法： mysqldump 数据库名 表 >导出的路径名和导出的文件名 -u用户名 -p密码
例如：
mysqldump xyxy student2>D:\谷歌浏览器\xyxy2.sql -uroot -p123456

导入数据：登录之后在进行创建数据库，然后使用该数据库，执行source命令
create database xyxy ;
use xyxy ;
source D:\谷歌浏览器\xyxy.sql


mysql赋予远程登录权限（以mysql5.7为例）
先在命令行登录mysql或者在SQL查询界面登录进去，只需以下四步即可。
1、使用mysql数据库
2、查询用户
3、授权
4、刷新权限

use mysql;

select  User,authentication_string,Host from user

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '145263'  

flush privileges;

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2022-02-11，如有侵权请联系 cloudcommunity@tencent.com 删除

sql