常用sql语句

1.增加数据

 

<insert id="方法名" parameterType="实体类路径"> 
    insert into 表名(属性名,属性名······) values(值,值·····) 
</insert>

2.删除数据

 

<delete id="方法名" parameterType="int"> 
     delete from 数据库表名 where 属性名=值 
</delete>

3.修改数据

<update id="方法名" parameterType="实体类路径"> 
    update 数据库表名 set 属性名=值,属性名=值······where 属性名=值 
</update>

4.全查数据

<select id="方法名" resultType="实体路径"> 
    select * from 表名 
</select>

5.依据属性字段查询数据

<select id="方法名" resultType="实体路径"> 
    select * from 表名 where 属性名=值 
</select>

6.查询数量

<select id="方法名" resultType="java.lang.Integer"> 
    select count(*) from 表名 
</select>

7.多表查询

以学生表student(id,code,name,age,telephone,teacher_id,create_time)与教师表 teacher(id,name,class_name,telephone)为例

<select id="方法名" resultType="实体路径"> 
    SELECT a.id,a.name,a.age,a.telephone,a.teacher_id, b.id, b.name,
    b.class_name,b.telephone FROM student a INNER JOIN teacher b ON a.teacher_id=b.id
    WHERE b.id is not null 
</select>

8.分页

//取10条数据 0到10

<select id="方法名" resultType="实体路径"> 
    select * from 表名 limit 0,10; 
</select>

9.分组查询

<select id="方法名" resultType="实体路径"> 
    select * from 表名 group by name 
</select>

10.分组查询后对条数求和

<select id="方法名" resultType="java.lang.Integer"> 
    SELECT COUNT(*) FROM  (SELECT 字段... FROM 表 GROUP BY 分组字段) 别名 
</select>

11.逆序排列

//asc  升序排列 (默认) desc 降序排列  

<select id="方法名" resultType="实体路径"> 
    select * from 表名 ORDER BY name desc 
</select>

12.GROUP_CONCAT  用于拼接同一属性的字段

例如  数据库中学生表，学生名有张三与李四。

<select id="方法名" resultType="实体路径"> 
    select GROUP_CONCAT(name separator '、')  from student 
</select>

结果: 张三、李四。

附: GROUP_CONCAT 通常与group by使用，用于合并数据后去重。  多表查询时，可使用left join 避免为空数据不显示

13.拼接字符

select concat(“123”, “456”)

结果: 123456

注:使用中，若出现中文乱码，添加concat(convert(整形, char),"中文")

14.查询一个字段中不同状态的数量

SELECT sum(CASE STATE WHEN '0' THEN 1 ELSE 0 END) AS 'state0Count', 
 sum(CASE STATE WHEN '1' THEN 1 ELSE 0 END) AS 'state1Count', 
 sum(CASE STATE WHEN '0' THEN moeny ELSE 0.0 END) AS 'state1MoenySum', 
 sum(CASE STATE WHEN '1' THEN moeny ELSE 0.0 END) AS 'state2MoenySum' FROM table

15.依据时间查询，如果没有符合数据就为0

SELECT b.id, b.branch_name,COUNT(a.branch_id) branchCount 
FROM sc_worksheet a RIGHT JOIN sc_branch b ON b.id=a.branch_id 
 AND a.create_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
 GROUP BY b.id ORDER BY branchCount DESC LIMIT 8;

16.截取字符 substr

select substr(“123456”, 1,4) 

结果：1234

17.时间戳与字符串的相互转换 

//时间戳转换成字符串 select from_unixtime(1234567892)   

//字符串转换成时间戳 select unix_timestamp('2019-01-01'); 

//时间转字符串 select date_format(now(), '%Y-%m-%d'); 

//字符串转时间 select str_to_date('2019-01-01', '%Y-%m-%d %H'); 

//时间转时间戳 select unix_timestamp(now()); 

//时间戳转时间 select from_unixtime(1451997924);

18.依据时间取最新的一条数据

 

<select id="selectOneStudent" resultType="com.modules.student"> 
    select * from student where name=#{name} ORDER BY create_time DESC limit 1 
</select>

19.获取某一属性最大值(可用于自动生成学生编码)

<select id="getMaxCode" resultType="java.lang.String"> 
    select max(code) from student 
</select>

20.foreach用法

附:foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有 item，index，collection，open，separator，close。

item集合中每一个元素进行迭代时的别名，

index表示在迭代过程中，每次迭代到的位置，

open该语句以什么开始，

separator在每次进行迭代之间以什么符号作为分隔 符，

close以什么结束，

使用时应注意以下情况：

(1). 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

(2). 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

(3). 如果传入的参数是多个，通常封装成Map，collection的属性值为map里面的key

//循环添加 
<insert id="addAmount"> 
  insert into tb_student(id,name,age) value <foreach collection="lstStudent" item="student" index="index" separator=";" open="" close=""> <trim prefix=" (" suffix=")" suffixOverrides=","> #{student.id}, #{student.name}, #{student.age}, </trim> </foreach> </insert> 

//循环修改 <update id="updateAmount"> <foreach collection="lstStudent" item="student" index="index" separator=";" open="" close=""> update tb_student set age=#{student.age} </foreach> </update> 

//获取导出列(可用于导出excel) <select id="getStudent" resultType="com.modules.student"> select id,name,age,telephone,teacher_id from student where 1=1 <if test="list!=null and list.size()>0"> and id in <foreach item="ids" index="index" collection="list" open="(" separator="," close=")"> #{ids} </foreach> </if> </select>

21.字段模糊查询

 

/*学生名称 模糊查询*/ 
<if test="name!= null and name.trim() != ''"> name like concat(concat('%', #{name}), '%') </if>

22.去重用法

//依据学生名称对学生表进行去重后，取列表字段 
<select id="getSelectList" resultType="java.util.Map"> 
    select DISTINCT name as id,age as text from student where 1=1 
    <if test='queryString!=null and queryString!=""'> and name like 
    concat(concat('%', #{queryString}), '%') </if> 
</select> 

//依据学生名称对学生表进行去重后，取列表总条数 
<select id="getSelectMaterielTotal" resultType="java.lang.Integer"> 
    select count(DISTINCT name) from student where 1=1 <if test='queryString!=null and 
    queryString!=""'> and name like concat(concat('%', #{queryString}), '%') </if> 
</select>

23.//判断是否存在重复数据

<select id="getStudentCount" resultType="java.lang.Integer"> 
    select count(1) from student where id = #{id} and name in <foreach 
collection="lstNames" item="name" index="index" open="(" separator="," close=")"> #{name} 
</foreach> 
</select>

附:resultType与parameterType简介

select 查询时 resultType返回的几种方式:

resultType="java.lang.Integer"    返回int 
resultType="java.lang.String"     返回String 
resultType="com.User"             返回User实体对象(com.User为实体路径) 
resultType="java.util.List"       返回list 
resultType="java.util.Map"        返回map 

parameterType:参数类型

24.增加索引

1.普通索引 添加INDEX ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
2.主键索引 添加PRIMARY KEY ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
3.唯一索引 添加UNIQUE ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
4.全文索引 添加FULLTEXT ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.如何添加多列索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

25.查看索引

1、查看数据库所有索引 SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据库名'; 

2、查看某一表索引 SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = '数据
库名' and a.table_name like '%表名%';