总结mysql和mybatis的常用语句

前言

总结工作中使用的一些mysql常用语句

mysql常用语句

查询表的数据结构

describe table;

查询表的多个字段

WHERE ID='741'or ID='2088'or ID='2087';

省略查询哪个表

IN和OR的区别

id IN (1, 2, 3) 和 id = 1 OR id = 2 OR id = 3 这两个条件在SQL查询中实现的功能是相同的，它们都用于在查询时筛选满足指定条件的数据。然而，它们在语法和性能上有一些区别。

1. 语法区别：
   • id IN (1, 2, 3): 这是一个简化的语法，用于表示id字段在给定的一组值中。它的语法更加简洁，适用于传递一个固定的列表或数组值。
   • id = 1 OR id = 2 OR id = 3: 这是使用多个OR条件连接的语法，每个OR条件代表一个具体的值。它的语法更加冗长，适用于需要动态构建查询条件时使用。
2. 性能区别：
   • 通常情况下，对于较小的数据集，IN和OR语法的性能差异不大，数据库优化器会对它们进行优化。
   • 但是对于较大的数据集，IN通常比多个OR条件更高效，因为IN语句会被数据库优化器转换为更有效的查询计划，而多个OR条件可能导致数据库执行多次索引扫描，影响性能。

多表联查

SELECT *
FROM bookrack t1
INNER JOIN fiction t2 ON t1.fiction_id = t2.fiction_id

内连接（INNER JOIN）是SQL中最常见的连接类型之一，它只返回两个表中在连接条件上匹配的行，即两个表中连接字段的值相等的行。在你的例子中，t1.fiction_id和t2.fiction_id是连接条件，连接后只返回两个表中fiction_id相等的行。

左连接（LEFT JOIN）和右连接（RIGHT JOIN）是另外两种连接类型：

• 左连接（LEFT JOIN）：返回左表中所有的行，以及右表中在连接条件上匹配的行。如果右表没有匹配的行，返回的结果中右表的字段值为NULL。
• 右连接（RIGHT JOIN）：返回右表中所有的行，以及左表中在连接条件上匹配的行。如果左表没有匹配的行，返回的结果中左表的字段值为NULL。

要使用左连接或右连接，语句的写法类似于内连接，只需要将INNER JOIN替换为LEFT JOIN或RIGHT JOIN。

例如，如果你想使用左连接，可以这样写：

SELECT *
FROM table1 t1
LEFT JOIN fiction t2 ON t1.fiction_id = t2.fiction_id;

如果你想使用右连接，可以这样写：

SELECT *
FROM table1 t1
RIGHT JOIN fiction t2 ON t1.fiction_id = t2.fiction_id;

总结：

• INNER JOIN：只返回连接字段匹配的行。
• LEFT JOIN：返回左表所有行和右表中连接字段匹配的行。
• RIGHT JOIN：返回右表所有行和左表中连接字段匹配的行。

mybatis动态sql

foreach的用法

MyBatis是一个Java持久层框架，用于将SQL语句映射到Java方法。其中，<foreach>标签用于在MyBatis中进行循环迭代，常用于在SQL语句中构建IN条件。下面是一个使用<foreach>标签的示例：

假设我们有一个数据库表users，包含以下字段：id, name, age。

我们想要查询一组指定id的用户记录，可以通过<foreach>标签构建IN条件。以下是示例代码：

<!-- userMapper.xml -->

<!-- 查询指定多个id的用户 -->
<select id="getUsersByIds" resultType="User">
    SELECT * FROM users
    WHERE id IN
    <foreach item="id" collection="ids" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

在上面的示例中：

• <select>标签用于定义一个查询语句，其中id属性是方法名，在Java代码中会调用该方法名进行数据库查询。
• resultType属性指定查询结果返回的类型，这里假设我们有一个User类作为查询结果对象。
• <foreach>标签用于构建IN条件，其中item属性指定在循环中每次迭代的集合元素变量名，collection属性指定要迭代的集合，这里假设我们传入一个List<Integer>类型的ids参数。
• open属性定义在整个IN条件的开头，这里是左括号(。
• separator属性定义在每个集合元素之间的分隔符，这里是逗号,。
• close属性定义在整个IN条件的结尾，这里是右括号)。
• #{id}表示在每次循环迭代中，将使用集合中的一个元素来替换这个位置，从而构建成类似于id IN (1, 2, 3)这样的SQL查询条件。

除了ids，在MyBatis的<foreach>标签中，还可以使用其他名称作为collection属性的值。collection属性用于指定要迭代的集合或数组，在每次迭代中，会将集合中的一个元素赋值给<foreach>标签中定义的item属性指定的变量名。

除了ids，其他常见的collection属性值有：

1. List： 使用Java的List集合类型。

<foreach item="item" collection="list">
    #{item}
</foreach>

2. Set： 使用Java的Set集合类型。

<foreach item="item" collection="set">
    #{item}
</foreach>

3. Array： 使用Java的数组类型。

<foreach item="item" collection="array">
    #{item}
</foreach>

4. map.keySet()： 使用Java的Map类型，迭代Map的键集合。

<foreach item="key" collection="map.keySet()">
    #{key}
</foreach>

5. map.values()： 使用Java的Map类型，迭代Map的值集合。

<foreach item="value" collection="map.values()">
    #{value}
</foreach>

这里的item属性用于指定每次迭代中集合元素的变量名，你可以根据实际情况自由命名。在每次迭代中，#{item}会被替换为集合中的一个元素。

请注意，collection属性的值可以是任意表达式，只要能够返回一个Iterable类型的对象即可。例如，你也可以使用一个方法调用或者一个返回集合的复杂表达式作为collection属性的值。

Java代码：

// UserMapper.java

public interface UserMapper {
    List<User> getUsersByIds(List<Integer> ids);
}

调用方法：

List<Integer> ids = Arrays.asList(1, 2, 3);
List<User> users = userMapper.getUsersByIds(ids);

set用法

通过使用 <foreach> 元素来批量更新 bs_broadband_preorder_10086_info 表中的多个行。在这个更新语句中，您使用了一个列表 updateList，其中包含要更新的不同行的数据。

<update id="batchUpdatePreorderInfo">
  UPDATE bs_broadband_preorder_10086_info
  <set>
    EXTEND_1 =
    <foreach collection="updateList" item="order" open=" CASE ID " close=" END " separator=" " >
      WHEN #{order.id} THEN #{order.extend1, jdbcType=VARCHAR}
    </foreach>,
    EXTEND_1_UPDATE_TIME =
    <foreach collection="updateList" item="order" open=" CASE ID " close=" END " separator=" " >
      WHEN #{order.id} THEN #{order.extend1UpdateTime, jdbcType=TIMESTAMP}
    </foreach>
  </set>
  WHERE ID IN
  <foreach collection="updateList" item="order" open=" (" close=" ) " separator=" , ">
    #{order.id}
  </foreach>
</update>

上述代码中，我们使用了两个 <foreach> 元素，一个用于更新 EXTEND_1 列，另一个用于更新 EXTEND_1_UPDATE_TIME 列。这两个 <foreach> 元素都将 updateList 中的数据进行循环遍历，根据每个对象的属性值更新相应的列。

请确保您在调用这个更新语句之前正确设置了参数 updateList，并且您的数据对象中包含了 id、extend1 和 extend1UpdateTime 这些属性。同时，也要确保您的数据库表结构与这些列和数据类型匹配。

以下是相应的 SQL 更新语句：

UPDATE bs_broadband_preorder_10086_info
SET EXTEND_1 = 
    CASE
        WHEN ID = #{updateList[0].id} THEN #{updateList[0].extend1, jdbcType=VARCHAR}
        WHEN ID = #{updateList[1].id} THEN #{updateList[1].extend1, jdbcType=VARCHAR}
        -- Repeat this pattern for each item in updateList...
        ELSE EXTEND_1
    END,
    EXTEND_1_UPDATE_TIME =
    CASE
        WHEN ID = #{updateList[0].id} THEN #{updateList[0].extend1UpdateTime, jdbcType=TIMESTAMP}
        WHEN ID = #{updateList[1].id} THEN #{updateList[1].extend1UpdateTime, jdbcType=TIMESTAMP}
        -- Repeat this pattern for each item in updateList...
        ELSE EXTEND_1_UPDATE_TIME
    END
WHERE ID IN (#{updateList[0].id}, #{updateList[1].id}, ...) -- List all IDs from updateList

在上述 SQL 语句中，我们使用了多个 WHEN 子句来处理每个需要更新的行。您需要为 updateList 中的每个对象分别提供其相应的 id、extend1 和 extend1UpdateTime 值。

传入包含参数的 Map，MyBatis 会根据参数名自动映射

当使用 MyBatis 时，你可以传入一个包含参数的 Map，并且 MyBatis 会根据参数名自动映射值到 SQL 查询中的对应参数。

假设你有一个数据库表格 students，包含 id、name 和 age 字段，你想使用 MyBatis 查询学生信息。以下是一个简单的示例：

1. 首先，定义一个包含参数的 Map，其中键是参数名，值是参数值：

   Map<String, Object> paramMap = new HashMap<>();
   paramMap.put("name", "Alice");
   paramMap.put("age", 25);

2. 接下来，在你的 MyBatis 映射文件（Mapper XML）中，编写 SQL 查询语句，并使用参数名作为占位符：

   <select id="getStudentsByNameAndAge" resultType="Student">
       SELECT * FROM students
       WHERE name = #{name} AND age = #{age}
   </select>

3. 在你的 Java 代码中，调用 MyBatis 的查询方法，并传入之前定义的参数 Map：

   List<Student> students = sqlSession.selectList("yourNamespace.getStudentsByNameAndAge", paramMap);

在上面的例子中，MyBatis 会根据参数名自动将 Map 中的键值赋给 SQL 查询中的参数，实现了按照姓名和年龄查询学生信息的功能。

需要注意的是，这个例子中的命名空间（namespace）、实体类（Student）、SQL 查询语句等具体细节需要根据你的项目情况进行调整。同时，确保 Map 中的键名与 SQL 查询中的参数名保持一致，这样 MyBatis 才能正确地映射参数值。

MYSQL设置远程访问

使用改表法

可能是我们的的帐号不允许从远程登陆，只能在localhost（127.0.0.1）登录。这个时候只要在localhost的那台电脑，登入mysql后，更改 “mysql” 数据库里的 “user” 表里的 “host” 项，从”localhost”改称”%”，具体命令如下：

mysql -u root -pvmware

use mysql;

update user set host = '%' where user = 'root';

flush privileges;

select host, user from user;