MySql数据库的操作(别名,组建数据等)

按最新时间显示数据
SELECT score_current AS score FROM zyjx_record_score WHERE user_id = 1 ORDER BY operate_time DESC

基本的增删改查

	##查询所有
	 select * from ss_company
	 ## 删除一条
	  delete from ss_company where id = #{id}
	  ## 添加数据
 insert into
        ss_company
        (id,name,expiration_date)
        values
        (#{id},#{name},#{expirationDate})

## 修改数据
update ss_company set
            name=#{name},
            expiration_date=#{expirationDate},
            address=#{address},
            license_id=#{licenseId},
            representative=#{representative},
            phone=#{phone},
            company_size=#{companySize},
            industry=#{industry},
            remarks=#{remarks},
            state=#{state},
            balance=#{balance},
            city=#{city}
        where id = #{id}

分组 计算总值 排序

## 厂家销量统计
	SELECT factory_name , SUM(amount)
		FROM co_contract_product 
		WHERE company_id = 1 GROUP BY factory_name ;
		##  销售数量 前15名 
 SELECT * FROM (
        SELECT product_no AS NAME ,SUM(cnumber) AS VALUE
        FROM co_contract_product WHERE company_id = 1
        GROUP BY product_no
        ORDER BY VALUE DESC ) a
        LIMIT 15

字符串 时间转换 根据时间查询

date_format(zyjx_read_record.read_time,’%Y-%m-%d’) = date_format(#{readTime},’%Y-%m-%d’)
// zyjx_read_record.read_time 比较得日期 (#{readTime} 传入得日期

date_format(cc.ship_time,’%Y-%m’) = “2015-05”;

SELECT DATE_FORMAT(TIME,"%H") FROM st_sys_log ; ## 获取时间部分的小时部分  05  00  23

  date_format(cc.ship_time,'%Y-%m') = "2015-05";  ## 把时间转换成字符串  
  select
            cc.custom_name customName,
            cc.contract_no contractNo,
            cp.product_no productNo,
            cp.cnumber cnumber,
            cp.factory_name factoryName,
            cc.delivery_period deliveryPeriod,
            cc.ship_time shipTime,
            cc.trade_terms tradeTerms
        from
            co_contract  cc , co_contract_product cp
        where
            cc.company_id = '1' and
            cc.id = cp.contract_id and
            date_format(cc.ship_time,'%Y-%m') = "2015-05";

别名

## 首先查询处 所有的模块 信息   (只要名字 id , 和 parent_id)
SELECT module_id AS 'id',
parent_id AS 'pId',
parent_name AS 'name' 
FROM ss_module ;
## 查询出 4028a1c34ec2e5c8014ec2ebf8430001 role_id 所包含的模块信息 
SELECT module_id FROM pe_role_module WHERE role_id = '4028a1c34ec2e5c8014ec2ebf8430001' ;

## 查询当前角色 所拥有的模块权限
SELECT module_id AS 'id',
parent_id AS 'pId',
parent_name AS 'name'
 FROM ss_module WHERE module_id IN (
SELECT module_id FROM pe_role_module WHERE role_id = '4028a1c34ec2e5c8014ec2ebf8430001'
) ;
## 查询当前角色 所拥有的模块权限  添加选中属性 状态
SELECT
	module_id AS 'id',  ##起别名
	parent_id AS 'pId',
	parent_name AS 'name',
	CASE WHEN module_id IN   ## 当 (if module_id in ) module_id 存在以下 (in) 结果中
	( SELECT module_id FROM pe_role_module WHERE role_id = '4028a1c34ec2e5c8014ec2ebf8430001' )
	THEN 'true'  ## 存在 输出true'
	ELSE 'false' ## 不存在 输出false'
	END          ## 结束判断语句
	AS 'checked' ## 对输出的结果内容 起的别名  (不起名字就是条件查询全字段)
 FROM ss_module ;

连表查询

子查询

## 用户登陆 获取到的id  '002108e2-9a10-4510-9683-8d8fd1d374ef'
## 查询当前用户 所拥有的角色
SELECT role_id FROM pe_role_user WHERE user_id = '002108e2-9a10-4510-9683-8d8fd1d374ef'
## 根据 所持有的 角色id 查找到 对应的角色信息
SELECT * FROM pe_role WHERE role_id IN ( 
	SELECT role_id FROM pe_role_user WHERE user_id = '002108e2-9a10-4510-9683-8d8fd1d374ef'
 )
 
## 根据 所持有的 角色id 找到到 对用的模块信息
SELECT * FROM pe_role_module WHERE role_id IN (
	SELECT role_id FROM pe_role_user WHERE user_id = '002108e2-9a10-4510-9683-8d8fd1d374ef'
)
## 根据 所持有的 模块id  查找 模块信息
SELECT *FROM ss_module WHERE module_id IN (
	SELECT module_id FROM pe_role_module WHERE role_id IN (
	SELECT role_id FROM pe_role_user WHERE user_id = '002108e2-9a10-4510-9683-8d8fd1d374ef'
)
)

子查询 联表查询 这个查询速度快

  select * from ss_module where module_id in (
        select module_id from pe_role_module as  rm, pe_role_user as ru
        where
        rm.role_id = ru.role_id and
        ru.user_id = #{userId}
        )

连接数据库配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://114.55.165.72:3306/zyjx?characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
##jdbc.url=jdbc:mysql:///zyjx
jdbc.username=root
jdbc.password=Guoll@123

3表联查 动态sql

 <select id="selectMapAll"  resultType="java.util.Map" parameterType="com.zyjx.entity.MapRequestVO" >
	 SELECT
	zyjx_read_record.id
	,zyjx_read_record.entity_type AS entityType
	,zyjx_read_record.read_user_id AS readUserId
	,zyjx_read_record.entity_id AS entityId
	,zyjx_read_record.read_time AS readTime
	,zyjx_knowledge_library.title AS title
	,zyjx_user.user_name AS userName
	,zyjx_user.phone_number AS phoneNumber
	,zyjx_user.address AS address
	FROM zyjx_read_record   LEFT  JOIN zyjx_user
	ON zyjx_read_record.read_user_id  = zyjx_user.id
    LEFT  JOIN zyjx_knowledge_library
	ON zyjx_knowledge_library.id = zyjx_read_record.entity_id
    and zyjx_knowledge_library.knowledge_type = zyjx_read_record.entity_type
    <where>
      <if test="entityType != null " >
          zyjx_read_record.entity_type = #{entityType}
      </if>
      <if test="readTime != null and readTime != '' " >
        AND zyjx_read_record.read_time = #{readTime}
      </if>
      <if test="title != null and title != ''"   >
        AND zyjx_knowledge_library.title LIKE CONCAT('%', #{title} ,'%')
      </if>
      <if test="userName != null and userName != '' " >
        AND zyjx_user.user_name LIKE CONCAT('%', #{userName} ,'%')
      </if>
      <if test="phoneNumber != null and phoneNumber != '' " >
        AND zyjx_user.phone_number LIKE CONCAT('%', #{phoneNumber} ,'%')
      </if>
      <if test="address != null and address != '' " >
        AND zyjx_user.address LIKE CONCAT('%', #{address} ,'%')
      </if>
    </where>


  </select>