mysql语句1

分组查询

1.SELECT * FROM sys_org_element WHERE fd_org_type=‘8’ GROUP BY fd_name HAVING COUNT(*)>2 ORDER BY fd_name DESC

判断字段字符与字节数

1、SELECT * FROM sys_org_element WHERE LENGTH(fd_name)=8
2、SELECT * FROM sys_org_element WHERE CHAR_LENGTH (fd_name)=8

外键约束取消和恢复

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

连级删除和连级修改

创建表时添加 ON DELETE CASCADE
ON UPDATE CASCADE

根据外键关联更新表字段

update person p nner join element e
set p.fd_name=e.fd_name where p.fd_id=e.fd_id

将表中的数据插入另一张表

insert into wh_pmc_month (fd_wl_id,fd_num,fd_price,doc_dept_id,fd_date)
select fd_id,IFNULL(fd_quantity,0)+IFNULL(fd_djnum,0),
fd_unit_price,doc_dept_id,NOW() FROM wh_pmc_quantity where fd_is_available=1

mysql向表中某字段后追加一段字符串

UPDATE sys_org_element SET fd_name=CONCAT(fd_name,’’,‘aa’) WHERE fd_name =‘bb’;

mysql向表中某字段前加一段字符串

UPDATE sys_org_element SET fd_name=CONCAT(‘aa’,fd_name) WHERE fd_name =‘bb’;

mysql: 不加order by InnoDB 表，会按主键的顺序排序

mysql: INSTR函数字符串包含和被包含

SELECT * FROM sys_org_element WHERE INSTR(fd_name,‘杰’)>0
SELECT * FROM sys_org_element WHERE INSTR(‘杰’,fd_name)>0

字符串拼接

SELECT CONCAT(fd_name,fd_name_pinyin) FROM sys_org_element WHERE fd_name=‘aa’

字段计算排除null

SELECT (SUM(IFNULL(p.fd_num,0)) FROM cash_manage_product p