常用sql

1. insert ... select

-- directory和parent_directory，目录和父目录
INSERT INTO DIRECTORY(`directoryName`, `directoryCode`, `directoryPriority`, `directoryType`, `directoryIcon`, `parentNavigator`, `consoleUrl`) 
select '苹果', 'apple', 1, 1, NULL, parentDirectoryCode, 'apple.action'
from parent_directory where parentNavigatorName='水果';

 2. 使用变量

select @num:=parentDirectoryCode from parent_directory where parentNavigatorName='水果';
INSERT INTO DIRECTORY(`directoryName`, `directoryCode`, `directoryPriority`, `directoryType`, `directoryIcon`, `parentNavigator`, `consoleUrl`) 
VALUES ('苹果', 'apple', 1, 1, NULL, @num, 'apple.action');

 3. update ... join

update voucher_user vu
join vouchermodel vm on vu.modelId=vm.modelId
set vu.realDenomination=vm.denomination
where vu.realDenomination is null;

 4. delete from 和 truncate 的区别

对于MySQL，truncate一张表，表中AUTO_INCREMENT的列序号会清零。

5. having 和 where 的用法一样，where 在 group by 前面，having在group by后面。

select s.sid, count(1) as num, sum(g.score) as total
FROM student s 
join grade g on s.sid=g.sid
where g.score>60
group by s.sid
HAVING num>3

 6. 按IP地址统计MySQL连接数

select SUBSTRING_INDEX(host, ':', 1) as ip , count(1) 
from information_schema.processlist group by ip;

7. 根据字符串查找mysql表名

select table_name from information_schema.tables 
where table_schema='db_zhang' and table_name like '%table_xxx%';

8. 根据字符串查找列:

select * from information_schema.columns
where TABLE_SCHEMA='db_zhang' and COLUMN_COMMENT like '%紧急联系人';

9. 不存在才插入

CREATE TABLE `books` (
  `id` smallint(6) NOT NULL,
  `bookno` smallint(6) DEFAULT NULL,
  `bookname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into books 
select 3, 15, 'haha' from dual where not EXISTS 
(select bookno from books WHERE bookno=15);

exists 判断子查询返回的集合是否为空

10. 给定一个字符串，如果该字符串包含一行指定列的值， 则返回改行

select * from employee e
where INSTR('zhang 10086', e.emp_no)>=1 

其中 emp_no  为 10086。

11. 查看 MySQL 配置的最大连接数

select @@max_connections;