mysql--常用基本语法

weisian151

已于 2024-06-18 11:18:44 修改

阅读量2.4k

点赞数 3

分类专栏： mysql 文章标签： mysql

于 2020-07-13 18:17:03 首次发布

本文链接：https://blog.csdn.net/qq_34207422/article/details/107322443

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

常用mysql用到语法积累

mybatis--mysql常用语法
1、包含指定sql，需要外部定义
使用
<include refid="Base_Column_List" /> 
定义：
<sql id="Base_Column_List">
	id, book_id,name,parent_id
</sql>

2、like用法
name like concat('%',concat(#{name},'%'))

3、判断字符串不为空，添加sql
<if test="bookId != null and bookId != ''">
	and book_id =#{bookId}
</if>

4、多条件，执行其中一个（如：parentId为空，执行when中的sql，否则，执行otherwise的sql）
<choose>
	<when test="parentId != null and parentId != '' ">
		and parent_id = #{parentId}  and id != '0'
	</when>
	<otherwise>
		and parent_id is null or parent_id = ''
	</otherwise>
</choose>

5、等于指定的值执行
<if test='period == "1"'>
	and grade &lt;= '6'
</if>

6、小于<，小于等于，大于，大于等于，&，单引号，双引号
&lt; &lt;= &gt; &gt;= &amp; &apos; &quot;
也可以
大于等于   <![CDATA[ >= ]]>
小于等于   <![CDATA[ <= ]]>

7、字符串转数字
CAST(t.CLASS_NO AS signed)

8、传入字符串的in语法
（1）、List<String> 用法，主要集合注意为空的情况，可在外面嵌套一层空判断（<if test="ids != null and ids.size() > 0">）
and t.period in 
<foreach collection="ids" index="index" item="id" open="(" separator="," close=")">
	#{id}
</foreach>
（2）List<Object>
<if test="orgSet != null and orgSet.size()>0">
	and t.belong_unit in
	<foreach collection="orgSet" index="index" item="org" open="(" separator="," close=")">
		#{org.id}
	</foreach>
</if>


9、获取最大排序值
MAX(cast(ec.CLASS_NO as SIGNED)) as classNo 

10、更新写法
<update id="backClass">
	update t_class t 
		set t.del_flag = #{delFlag}, 
		t.is_end = #{isEnd},
		t.is_his = #{isHis},
		t.MODIFY_TIME = #{modifyTime} 
	WHERE t.ID = #{id} 
</update>

11、删除写法
<delete id="deleteSubjectIsNullDataByTeacherId">
	delete from t_class_teacher_subject where teacher_id = #{teacherId} and (subject_id is null  or subject_id = '')
</delete>

12、时间类型或者时间类型字符串比较大小（between包含边界值）
（1）、直接日期比较
 and art.CREATE_TIME_ BETWEEN #{leaveMain.applyStartTimeForSearch} and #{leaveMain.applyEndTimeForSearch}
（2）、将日期转指定格式字符串比较
 AND DATE_FORMAT(t.begin_date, '%Y-%m-%d') &lt;= #{leaveDay}
AND DATE_FORMAT(t.end_date, '%Y-%m-%d') &gt;= #{leaveDay}
（3）、将字符串转成日期进行比较
 t.create_time > STR_TO_DATE('2020-08-09','%Y-%m-%d')
（4）、年月日时分秒格式化
 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')

13、获取时间类型的年，月，比较
and YEAR(y.change_date) = #{year}

14、按照分组连接，之后必须要和GROUP BY 连用
GROUP_CONCAT(eta.award_name  separator',') awardName,
。。。
GROUP BY t.id

15、判断为空，返回指定数据
IF(MAX(t.sn) IS NULL,1, MAX(t.sn)+1) 

16、返回查询数量
<select id="getMaxSnForInspectionItem" resultType="java.lang.Integer">

17、返回指定的内容（如果r.period为1，返回小学部，否则返回中学部给periodTxt）
(
	CASE r.period
	WHEN 1 THEN
			'小学部'
	ELSE
			'中学部'
	END
) periodTxt,

18、常用日期方法
-- 获取当前日期（yyyy-MM-dd HH:mm:ss），当前日期（yyyy-MM-dd），当前时间（HH:mm:ss）
select NOW(),CURDATE(),CURTIME(),DATE(NOW()),EXTRACT(YEAR FROM NOW());
-- 获取日期时间值输出日期
select DATE(NOW());
-- 获取日期的年，月，周，日（MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH）
select EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(WEEK FROM NOW()),EXTRACT(DAY FROM NOW());
-- 获取两天后的日期
select DATE_ADD(now(),INTERVAL 2 DAY);
-- 获取两天前的日志
select DATE_SUB(now(),INTERVAL 2 DAY); 
-- 获取两个日期之间的天数。
SELECT DATEDIFF(NOW(),'2008-08-08') AS 北京奥运会已经过去多少天;

19、date_format方法（第一个参数可以是日期，也可以是字符串）
select date_format('2018-06-26','%w')       获取日期是周几（0是星期日）
format如下：%a：缩写星期名
%b：缩写月名
%c：月，数值
%D：带有英文前缀的月中的天
%d：月的天，数值(00-31)
%e：月的天，数值(0-31)
%f：微秒
%H：小时 (00-23)
%h：小时 (01-12)
%I：小时 (01-12)
%i：分钟，数值(00-59)
%j：年的天 (001-366)
%k：小时 (0-23)
%l：小时 (1-12)
%M：月名
%m：月，数值(00-12)
%p：AM 或 PM
%r：时间，12-小时（hh:mm:ss AM 或 PM）
%S：秒(00-59)
%s：秒(00-59)
%T：时间, 24-小时 (hh:mm:ss)
%U：周 (00-53) 星期日是一周的第一天
%u：周 (00-53) 星期一是一周的第一天
%V：周 (01-53) 星期日是一周的第一天，与 %X 使用
%v：周 (01-53) 星期一是一周的第一天，与 %x 使用
%W：星期名
%w：周的天 （0=星期日, 6=星期六）
%X：年，其中的星期日是周的第一天，4 位，与 %V 使用
%x：年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y：年，4 位
%y：年，2 位


20、在字符串中查找指定的字符串（前为字符串，后为目标字符串，查不到返回0）
SELECT INSTR("abcd",'b');

21、常用字符串方法
-- 转小写字符串
select lower('SQL Course');
-- 转大写字符串
select upper('Use MYsql');
-- 字符串连接
select concat('My','S','QL');
-- 字符串连接特殊情况（包含null，则结果为null 和 包含数字，数字转字符串数字）
select  concat('My',null,'QL');
-- 用特定中间符号，连接字符串（用；连接字符串）
select concat_ws(';','First name','Second name','Last name');
-- 特定中间符号连接字符串特殊情况（包含null，去除null，继续连接）
select concat_ws(',','id',null,'name');
-- 字符串截取（正，从左截取；负，从右截取）  substring和substr一样
select substring('hello world',-5);
-- 字符串截取指定长度
select substr('hello world',5,3);
-- 返回字符串长度（字符占一个，中文字符，数据库utf8是3，gbk是2，本例结果4，6）
select length('text'),length('你好');
-- 返回字符串字符长度（中英文都只占一个，本例结果4，2）
select char_length('text'),char_length('你好');
-- 返回目标字符串在指定字符串中第一次出现的位置（没有返回0）
select instr('foobarbar','bar')
-- 源字符串的左边填充给定的字符??到指定的长度len，返回填充后的字符串
select lpad('hi',5,'??');
-- 在源字符串的右边填充给定的字符??到指定的长度len，返回填充后的字符串
select rpad('hi',6,'??');
-- 指定格式字符串（默认删除左右的空格）
select trim('  bar  ');
-- 删除左空格，删除右空格
SELECT  ltrim('   barbar   ') rs1, rtrim('   barbar   ') rs2;
-- 删除两边的x
select trim(both 'x' from 'xxxbarxxx');
-- 删除左边的x
select trim(leading 'x' from 'xxxbarxxx');
-- 删除右边的x
select trim(trailing 'x' from 'barxxyzxxx');
-- 替换自定字符串（以为将用Ww,替换字符串中的w）
select replace('www.mysql.com','w','Ww');
-- 将字符串重复三次输出（此例返回：MySQLMySQLMySQL）
select repeat('MySQL',3);
-- 字符串反转
select reverse('abcdef');
-- 数字输出指定的小数位
SELECT format(12332.123456, 4),format(12332.2,0);
-- 输出三个空字符，如‘   ’
select space(3);
-- 从左，返回5个字符
select left('chinaitsoft',5);
-- 从右，返回5个字符
select right('chinaitsoft',5);
-- 字符串比较大小  相等返回0，第一个大返回1，第一个小返回-1
SELECT strcmp('text', 'text2'),strcmp('text2', 'text');


22、自定义排序 （FIELD参数，指定name列，按照后面的先后顺序进行数据排序）
SELECT  *  FROM A  ORDER  BY  FIELD(name,  '王五',   '张三',   '李四')   ASC

23、返回数据带有集合字段的对象（定义map最为关键，本例createUser为List<String>字段，flowList为List<Object>字段）
定义Map如下：
<resultMap id="MatterListResultMap"
	type="com.dto.cloud.statistics.MatterPageResponseDto">
	<id column="THIRD_ORDER_NO" property="matterId" jdbcType="VARCHAR" />
	<result column="BUSINESS_SCENE" property="matterName" jdbcType="VARCHAR" />
	<result column="APP_NAME" property="appName" jdbcType="CHAR" />
	<result column="AREA_NAME" property="areaName" jdbcType="VARCHAR" />
	<result column="DEPT_NAME" property="deptName" jdbcType="VARCHAR" />
	<result column="SIGN_PROVIDER_CODE" property="signProviderCode" jdbcType="VARCHAR" />

	<!-- property表示集合类型属性名称，ofType表示集合中的对象是什么类型 -->
	<collection property="createUser" ofType="java.lang.String">
		<constructor>
			<arg column="createUser"/>
		</constructor>	
	</collection>
	
	<!-- property表示集合类型属性名称，ofType表示集合中的对象是什么类型 -->
	<collection property="flowList"
		ofType="com.MatterPageFlowResponseDto">
		<id column="F_ID" property="id" jdbcType="VARCHAR" />
		<result column="FLOW_ID" property="flowId" jdbcType="VARCHAR" />
		<result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
	</collection>
</resultMap>

定义查询列：
<sql id="matterPageCloumn">
	f.THIRD_ORDER_NO THIRD_ORDER_NO,
	f.BUSINESS_SCENE BUSINESS_SCENE,
	a.APP_NAME APP_NAME,
	a.AREA_NAME AREA_NAME,
	a.DEPT_NAME DEPT_NAME,
	f.SIGN_PROVIDER_CODE SIGN_PROVIDER_CODE,
	t.SIGN_USER_NAME createUser,
	f.ID F_ID,
	f.FLOW_ID FLOW_ID,
	f.CREATE_TIME CREATE_TIME
</sql>

定义sql
<!-- 根据分页的办件id获取办件统计列表数据 -->
<select id="getPageMatterList" resultMap="MatterListResultMap" > 
	select <include refid="matterPageCloumn" /> from t_flow f 
		left join t_app a on f.APP_ID = a.APP_ID 
		LEFT JOIN t_task t on f.FLOW_ID = t.FLOW_ID 
	where f.IS_DELETE = '0'  
	<if test="appId != null and appId != ''">
		and a.APP_ID = #{appId} 
	</if>
	<if test='cloudType == "1"'>
		and f.SIGN_PROVIDER_CODE = 'esign' 
	</if>
	<if test="endTime != null and endTime != ''">
		and t.CREATE_TIME &lt;= #{endTime} 
	</if>
	<foreach collection="list" item="item" index="id"
		open="and f.THIRD_ORDER_NO in (" close=")" separator=",">
		#{item.matterId}
	</foreach>
	order by f.CREATE_TIME desc 
</select>

24、按照中文首字母排序
ORDER BY CONVERT(表别名.字段名 USING gbk) COLLATE gbk_chinese_ci ASC;

25、null值排在最后
order by t.org_type is null, t.org_type asc
或者：
order by IF(ISNULL(t.org_type),1,0),t.org_type asc
或者：（不加ISNULL，则null排在前面）
select t.* from sys_account t order by ISNULL(t.MOBILE_PHONE);

26、计算两个日期之间的时间差
select TIMESTAMPDIFF(DAY, '2018-03-20 12:00:00', '2018-03-22 11:00:00');
select TIMESTAMPDIFF(HOUR, '2018-03-20 12:00:00', '2018-03-22 11:00:00');
select TIMESTAMPDIFF(MINUTE, '2018-03-20 12:00:00', '2018-03-22 11:00:00');
第一个按照天数：返回1
第二个按照小时数：返回47
第三个按照分钟数：返回2820

27、计算日期，如下为11-01，加一个月，在减去1秒，应该返回11月份的最后一秒
select DATE_ADD(
	DATE_ADD(
		CAST(CONCAT('2020-11', '-01') AS DATETIME),
		INTERVAL 1 MONTH
	),
	INTERVAL - 1 SECOND
)
返回：2020-11-30 23:59:59

28、基本表结构语句
（1）、建表语句
CREATE TABLE `edu_class` (
  `ID` varchar(32) DEFAULT NULL COMMENT '唯一标识',
  `SCHOOL_ID` varchar(32) DEFAULT NULL COMMENT '学校唯一标识',
  `CAMPUS_ID` varchar(32) DEFAULT NULL COMMENT '校区ID',
  `NAME` varchar(64) DEFAULT NULL COMMENT '班级名称',
  `BRIEF_NAME` varchar(32) DEFAULT NULL,
  `CLASS_NO` varchar(2) DEFAULT NULL COMMENT '班级号',
  `CLASS_TYPE` char(1) DEFAULT NULL COMMENT '0：普通班 1：实验班',
  `SCHOOL_SYSTEM` varchar(2) DEFAULT NULL COMMENT '1：六三制 2：五四制',
  `GRADE` varchar(2) DEFAULT NULL COMMENT '1：一年级2：二年级3：三年级4：四年级5：五年级6：六年级7：初一8：初二 9：初三 10：高一 11：高二 12：高三',
  `PERIOD` varchar(2) DEFAULT NULL COMMENT '1：小学 2：初中 3：高中',
  `START_YEAR` varchar(32) DEFAULT NULL COMMENT '入学年份',
  `END_YEAR` varchar(32) DEFAULT NULL COMMENT '毕业年份',
  `teacher_id` varchar(32) DEFAULT NULL,
  `IS_END` char(1) DEFAULT NULL COMMENT '是否毕业班',
  `IS_HIS` char(1) DEFAULT NULL COMMENT '是否历史班级',
  `CREATE_ACCOUNT` varchar(32) DEFAULT NULL COMMENT '创建人',
  `CREATE_ORG` varchar(32) DEFAULT NULL COMMENT '创建组织',
  `CREATE_ORG_PATH` varchar(255) DEFAULT NULL COMMENT '创建组织路径',
  `CREATE_UNIT` varchar(32) DEFAULT NULL COMMENT '创建单位',
  `CREATE_DEPT` varchar(255) DEFAULT NULL COMMENT '创建部门',
  `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  `MODIFY_ACCOUNT` varchar(32) DEFAULT NULL COMMENT '修改人',
  `MODIFY_ORG` varchar(32) DEFAULT NULL COMMENT '修改组织',
  `MODIFY_UNIT` varchar(32) DEFAULT NULL COMMENT '修改单位',
  `MODIFY_TIME` datetime DEFAULT NULL COMMENT '修改时间',
  `DEL_FLAG` char(1) DEFAULT NULL COMMENT '删除标识',
  `TENANT_ID` varchar(32) DEFAULT NULL COMMENT '租户id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）、新增属性字段
ALTER TABLE `edu_class`
ADD COLUMN `AAAA`  varchar(11) NULL AFTER `TENANT_ID`;

（3）、修改属性字段名称
ALTER TABLE `edu_class`
CHANGE COLUMN `AAAA` `AAAA1`  varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `TENANT_ID`;

（4）、修改属性字段属性值
ALTER TABLE `edu_class`
MODIFY COLUMN `AAAA1`  varchar(112) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `TENANT_ID`;

（5）、修改备注
ALTER TABLE `edu_class`
MODIFY COLUMN `AAAA1`  varchar(112) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT 'beizhu' AFTER `TENANT_ID`;

（6）、删除属性
ALTER TABLE `edu_class`
DROP COLUMN `AAAA1`;


29、求和四舍五入保留两位小数
SELECT
			s.user_id userId,
			s.class_name className,
			u.NAME studentName,
			#{month} month,
			count(DISTINCT t.id) leaveCount,
			TRUNCATE(SUM(t.total_day),2) leaveTotalDay         // 四舍五入保留两位小数，传0则没有小数
		FROM
			edu_leave_month_statistics t


30、新增索引
ALTER TABLE `attence_record`
ADD INDEX `attencedate_index` (`attence_date`) USING BTREE ;
修改索引
ALTER TABLE `attence_record`
DROP INDEX `attencedate_index` ,
ADD INDEX `attencedate_index1` (`attence_date`) USING BTREE ;


31、find_in_set方法，如字符串为1,2样（中间的连接符必须是英文逗号时才有效），可以通过方法识别字符串中是否包含1或者2
SELECT FIND_IN_SET('1', '1,2,3,4');      返回1
SELECT FIND_IN_SET('1', '11,2,3,4');     返回0，可见是精确包含
可以用于条件校验是否包含
<if test="accountType != null and accountType != ''">
    and FIND_IN_SET(#{accountType},ac.account_type)
</if>


32、mybatis会自动把time等关键词转大写，可以通过as "time"方式，转为小写time。

33、not exist用法（多关联，仅查询最小或者最大的）
SELECT
	*
FROM
	case_base_info T
LEFT JOIN (
	SELECT
		A1.*
	FROM
		BANK_RECEIVE_ACCOUNT A1
	WHERE
		A1.DEL_FLAG = '0'
	AND NOT EXISTS (
		SELECT
			1
		FROM
			BANK_RECEIVE_ACCOUNT A2
		WHERE
			A2.VIRTUAL_ACCOUNT = A1.VIRTUAL_ACCOUNT
		AND A2.CREATE_TIME < A1.CREATE_TIME
	)
) A ON T .PAYMENT_ACCOUNT = A .VIRTUAL_ACCOUNT
WHERE
	T .DEL_FLAG = '0'
AND T . ID = '15d3db9ec4dd4570b8c675f869168222';
或者
SELECT
			T . ID ID,
			T .CASE_NO caseNo,
			D .depute_no depNo,
			f.flow_name AS hjName,
			G .IDENTIFY_NAME idenName,
			A .timee endTime,
			T .create_time startTime,
			(A .timee - T .create_time) days
		FROM
			case_base_info T
		LEFT JOIN CASE_DEPUTE_COMMIT D ON T . ID = D .CASE_ID
		LEFT JOIN s_process_flow f ON T .case_state = f. ID
		LEFT JOIN GENERAL_IDENTIFY_TYPE G ON G . ID = T .IDENTIFY_TYPE
		AND G .del_flag = '0'
		LEFT JOIN (
			SELECT DISTINCT
				a1.VIRTUAL_ACCOUNT VIRTUALACCOUNT,
				"MIN" (A1.CREATE_TIME) timee
			FROM
				BANK_RECEIVE_ACCOUNT a1
			WHERE
				a1.del_flag = '0'
			GROUP BY
				A1.VIRTUAL_ACCOUNT
		) A ON T .PAYMENT_ACCOUNT = A .VIRTUALACCOUNT
		WHERE
			T .del_flag = '0'
		AND T .is_Payment = '1'
		AND D .DEL_FLAG = '0'
		AND A .timee IS NOT NULL
		AND A .timee > T .CREATE_time
		AND G . ID NOT IN ('8', '12')
		ORDER BY
			T .CREATE_TIME ASC


34、查询结果按照查询条件in中的顺序进行排序
mybatis写法：
select <include refid="cols2"/> from sys_org t
        where t.del_flag = '0'
        <if test="orgIdList != null and orgIdList.size()>0">
            and t.id in
            <foreach collection="orgIdList" index="index" item="orgId" open="(" separator="," close=")">
                #{orgId}
            </foreach>
            order by
            field(t.id,
            <foreach collection="orgIdList" index="index" item="orgId" separator="," close=")">
                #{orgId}
            </foreach>
        </if>
数据库写法：
select * from  sys_org t where t.del_flag = '0' and t.id in ('22','8ae9a314628a4977a29638b63d8cf3b4','77ba09f0a1d2478ba7ff219897fafd7e') 
order by field(t.id,'22','8ae9a314628a4977a29638b63d8cf3b4','77ba09f0a1d2478ba7ff219897fafd7e')

35、if常用语法
（1）、三个参数，第一个为条件，成立返回第二个参数，不成立返回第三个参数
SELECT if(t.MOBILE_PHONE like '%151%','1','0'),t.* FROM `sys_account` t ;
（2）、ifnull方法两个参数。如果第一个参数不是null则返回第一个参数，否则返回第二个参数
SELECT IFNULL(t.USERNAME,'00'),t.* FROM `sys_account` t ;

36、group by详细
-- having子句用于分组后筛选（可以添加聚合函数为条件），where子句用于行条件筛选（不可以添加聚合函数为条件）
-- 先where进行行筛选，在group by 进行分组，最后在having进行分组后筛选
select t.goods_id,COUNT(t.id) as c1 from goods_apply_detail t GROUP BY t.goods_id HAVING c1 > 1 ORDER BY null;
select SQL_BIG_RESULT t.goods_id,COUNT(t.id) as c1 from goods_apply_detail t GROUP BY t.goods_id HAVING c1 >= 1 order by c1 asc;
EXPLAIN select t.goods_id,COUNT(t.id) as c1 from goods_apply_detail t GROUP BY t.goods_id HAVING c1 > 1 ORDER BY null;
EXPLAIN select SQL_BIG_RESULT t.goods_id,COUNT(t.id) as c1 from goods_apply_detail t GROUP BY t.goods_id HAVING c1 > 1;

-- group by导致慢sql优化方法1、后面的字段加索引2、order by null 不用排序3、尽量只使用内存临时表4、使用SQL_BIG_RESULT


37、mysql获取行号
SELECT @index:=@index+1 myindex,t.id,t.name from sys_user t,(select @index:=0) c;

38、跨表进行值设置
UPDATE goods_apply_detail d1 
	LEFT JOIN goods_assets a on a.id = d1.FIXED_ASSETS_ID 
	LEFT JOIN goods_info g on g.id = a.goods_id
set d1.UNIT = g.UNIT ;


39、指定日期增加多少天
增加70天
select date_add(now(), INTERVAL 70 DAY );
增加3个月
select date_add(now(), INTERVAL 70 MONTH );
增加10年
select date_add(now(), INTERVAL 70 YEAR );

40、计算两个日期相差多少天（前大后小，不然会负数）
select datediff('2022-10-01', '2021-12-01');
距离当前日期多少天
select datediff(NOW(), '2021-12-01');


41、创建用户相关
A. 创建用户itcast, 只能够在当前主机localhost访问, 密码123456;
create user 'itcast'@'localhost' identified by '123456'; 
B. 创建用户heima, 可以在任意主机访问该数据库, 密码123456;
create user 'heima'@'%' identified by '123456';
C. 修改用户heima的访问密码为1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';
D. 删除 itcast@localhost 用户
drop user 'itcast'@'localhost';
E. 查询 'heima'@'%' 用户的权限
show grants for 'heima'@'%';
F. 授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限（授权时， 数据库名和表名可以使用 * 进行通配，代表所有）
grant all on itcast.* to 'heima'@'%';
G. 撤销 'heima'@'%' 用户的itcast数据库的所有权限
revoke all on itcast.* from 'heima'@'%';

39、数量百分比保留两位小数

select t.STATUS,(
   CASE t.STATUS
   WHEN '00' THEN
           '使用正常'
WHEN '02' THEN
           '使用待维修'
WHEN '10' THEN
           '闲置正常'
WHEN '11' THEN
           '闲置待领用'
WHEN '12' THEN
           '闲置待维修'
   ELSE
           '其他'
   END
) statusTxt,sum(t.SURPLUS_NUM) surplusNum,
CONCAT(ROUND(sum(t.SURPLUS_NUM)*100.0/(select sum(t1.SURPLUS_NUM) from goods_assets t1 where t1.DEL_FLAG = '0'),2),'%') surplusNumRate
from goods_assets t where t.DEL_FLAG = '0' group by t.STATUS ORDER BY surplusNum desc

40、获取一对多情况下，每个数据的第一条元素。

思路：先根据目标分组，获取仅一条数据，之后关联自身表通过ROW_NUMBER方法指定生成序号，条件中添加第一条即可

select * from (SELECT
t.* , ROW_NUMBER() OVER (PARTITION BY t.device_id ORDER BY t.start_time) AS sn
from device_running_record t
INNER JOIN (
SELECT
device_id,
MAX(start_time) AS start_time,
MAX(create_time) AS create_time
FROM
device_running_record
GROUP BY
device_id
) latest_records ON t.device_id = latest_records.device_id
AND t.start_time = latest_records.start_time and t.create_time = latest_records.create_time
where t.delete_flag = '0'
order by t.CREATE_TIME desc ,t.state_source desc ) temp where temp.sn =1;

mysql常见问题

1、text相关

text相当于varchar2（65535）

2、sql慢优化

（1）、数据量过大，使用group by进行分组或者去重，导致比较慢，根据相关条件查询导致比较慢。优化：where子句后条件添加索引，有索引的条件靠前，条件排除数据量越多越靠前。

3、不走索引或索引失效的情况

（1）、没有查询条件，或者查询条件没有创建索引。

（2）、查询结果集超过全表数据的25%，优化器不一定走索引

（3）、因为频繁的操作，造成索引失效，此时需要删除索引重新创建索引

（4）、索引条件上使用逻辑运算（+-*/）等，使用内置函数也会索引失效（如date_format）

（5）、隐式转换导致索引失效，字段定义为varchar，查询使用整数

（6）、!=,<>,not in等不走索引，索引是精确匹配，否定条件无校

（7）、like条件，且%_开头，导致索引失效，如（%a索引失效，a%走索引）

4、执行计划

在sql前面添加explain 即可查看目标sql的执行计划

5、查询mysql中每张表的数据量

SELECT TABLE_NAME AS '表名', TABLE_ROWS AS '数据量' FROM information_schema.TABLES WHERE TABLE_SCHEMA = '你的数据库名' ORDER BY TABLE_ROWS DESC;

6、快速删除表数据，不删除表结构

TRUNCATE TABLE my_table;

学海无涯苦作舟！

weisian151

关注

3
点赞
踩
9

收藏

觉得还不错? 一键收藏
1
评论
mysql--常用基本语法

常用mysql用到语法积累mybatis--mysql常用语法1、包含指定sql，需要外部定义使用<include refid="Base_Column_List" /> 定义：<sql id="Base_Column_List"> id, book_id,name,parent_id</sql>2、like用法name like concat('%',concat(#{name},'%'))3、判断字符串不为空，添加sql<if t
复制链接

扫一扫