查询表结构的sql语句

查询描述表：

DESC face;

查看生成表的DDL：

SHOW CREATE TABLE face;

查询表中列的详细信息：

select * from information_schema.columns where table_schema = 'bplus_test_01' and table_name = 'face';

查询列名和注释：

select column_name,column_comment from information_schema.columns where table_schema = 'bplus_test_01' and table_name = 'face';

查询表的名称和注释

select table_name,table_comment from information_schema.tables where table_schema = 'bplus_test_01' and table_name ='face'

复制表结构：

create table table1 like table;

修改表结构：

alter table t_face add eye bigint(20) unsigned DEFAULT NULL COMMENT '眼睛';

查询数据直接插入

insert into t_face(`user_id`,`domain`,`group`) select id,'www.baidu.com' as domain,`group` from t_visual_user;

部门表（dept_id，dept_name，descript） dept
员工表（id，name，age，salary，dept_id）employees
1.查询出平均薪资最高的部门
2.查询出各个部门中薪资最高的员工

第一个
select a.dept_id ,MAX(a.sal)
from
(select dept_id , AVG(salary) sal
from employees 
group by dept_id) a

第二个
SELECT e.ename,e.sal,e.dept_id
FROM employees e 
join (SELECT dept_id,MAX(salary) max_sal
       FROM employees
      GROUP BY dept_id) d
ON e.dept_id=d.dept_id
where e.salary=d.max_sal



********第二个查出group组里最高的一个******
Select * from (Select * from employees order by salary desc ) as tmp group by dept_id