mysql 部门统计 树结构

1,统计每个部门有多少人
 1,select dept_no ,count(*)as dept_count,d_name from em join dept on em.dept_no=
 dept.d_no group by dept_no;//第一种写法
 select dept.d_name,dept_no,count(*) as dept_count from dept,em where dept.d_no=
 em.dept_no group by dept_no;//第二种写法
 +---------+------------+----------+
 | dept_no | dept_count | d_name   |
 +---------+------------+----------+
 |      10 |          2 | kuaiji   |
 |      20 |          3 | yanjiu   |
 |      30 |          4 | xiaoshou |
 +---------+------------+----------+
 2,查询每个部门平均薪水是多少
 2,select dept_no ,avg(e_salary)as avg_salary,d_name from em join dept on em.dept_no=
 dept.d_no group by dept_no;
 select dept_no,avg(e_salary),d_name from em,dept where dept_no=d_no group by dept_no;
 | dept_no | avg_salary | d_name   |
 +---------+------------+----------+
 |      10 | 3725.0000  | kuaiji   |
 |      20 | 2358.3333  | yanjiu   |
 |      30 | 1637.5000  | xiaoshou |
 +---------+------------+----------+
 3,查询每个部们中薪水最高的员工的个人信息
 3,select * from em where e_salary in(select max(e_salary) from em,dept where dept_no = 
 d_no group by d_name);
 +------+--------+----------+---------+---------+----------+------------+
 | e_no | e_name | e_gender | dept_no | e_job   | e_salary | hireDate   |
 +------+--------+----------+---------+---------+----------+------------+
 | 1006 | blake  | f        |      30 | manager |     2850 | 1997-02-15 |
 | 1008 | scott  | m        |      20 | yanjiu  |     3000 | 2003-05-12 |
 | 1009 | king   | f        |      10 | zongcai |     5000 | 1995-01-01 |
 +------+--------+----------+---------+---------+----------+------------+
 4,查询在深圳工作的员工信息
 4,select em.*,dept.d_location from em join dept on em.dept_no=
 dept.d_no and dept.d_location='shenzhen';//第一种写法
 select * from em where dept_no=(select d_no from dept where d_location='shenzhen');//第二种写法
 结果1:
 +------+--------+----------+---------+----------+----------+------------+------------+
 | e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   | d_location |
 +------+--------+----------+---------+----------+----------+------------+------------+
 | 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 | shenzhen   |
 | 1006 | blake  | f        |      30 | manager  |     2850 | 1997-02-15 | shenzhen   |
 | 1010 | turner | f        |      30 | salesman |     1500 | 1997-10-12 | shenzhen   |
 +------+--------+----------+---------+----------+----------+------------+------------+
 结果2:
 +------+--------+----------+---------+----------+----------+------------+
 | e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
 +------+--------+----------+---------+----------+----------+------------+
 | 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
 | 1006 | blake  | f        |      30 | manager  |     2850 | 1997-02-15 |
 | 1010 | turner | f        |      30 | salesman |     1500 | 1997-10-12 |
 +------+--------+----------+---------+----------+----------+------------+


 5,查询jones所在的部门和部门的地址信息
 5,select dept.d_name,dept.d_location from dept join em on dept.d_no=
 em.dept_no and em.e_name='james';//第一种写法
 select dept.d_name,dept.d_location from dept left join em on dept.d_no=
 em.dept_no and em.e_name='james';//第二种写法
 select d_name,d_location from dept,em where d_no=dept_no and e_name='jones';//第三种方法
 +--------+------------+
 | d_name | d_location |
 +--------+------------+
 | yanjiu | beijing    |
 +--------+------------+




 命令记录:


 -链接数据库
 mysql -h localhost -u root -p
 或者(本机下的数据库)
 mysql -u root -p


 1.显示服务器上的所有数据库
 show databases;


 -创建数据库
 create database database_name;
 -查看数据库的定义信息
 show create database database_name;
 -删除数据库
 drop database database_name;


 ------------表的操作-----------


 -选中数据库
 use database_name;
 -创建数据表
 create table table_name(
 id int(11),
 name varchar(11)
 )
 -查看数据库中的所有表
 show tables;
 -查看表的定义信息
 show create table table_name\G;
 -查看表的基本信息结构
 describe table_name;
 -设置表的主键
 1. create table tb_emp2(id int(11)primary key,name varchar(25),deptld int(11),salary float);


 2. create table tb_emp3(id int(11),name varchar(25),deptld int(11),salary float,primary key(id));


 3.create table tb_emp4(name varchar(25),deptld int(11),salary float,primary key(name,deptld));


 -设置外键
 表一:
 create table tb_dept1(id int(11)primary key,name varchar(22) not null,location varchar(50));
 表二:
 create table tb_emp5(id int(11)primary key,name varchar(25),deptld int(11),salary float,constraint fk_emp_dept1 foreign key(deptid) references tb_dept1(id));


 -非空约束
 字段名 数据类型 NOT NULL,
 -唯一性约束
 (1)字段名 数据类型 UNIQUE,
 (2)[constraint <约束名>]UNIQUE(name)
 -默认约束
 字段名 数据类型 default 默认值
 deptid int(11)default 1111,
 -设置表的属性值自动增加
 字段名 数据类型 auto_increment
 id int(11) primary key auto_increment,
 -- -- -- -- -- -- --改变表-- -- --alter-- -- --
 -修改表名
 alter table <旧表名> rename [to] <新表名>;
 alter table tb_dept3 rename tb_deptment3;
 -修改字段的数据类型
 alter table <表名> modify <字段名> <数据类型>
 alter table tb_dept1 modify name varchar(30) --将name的varchar(22)改为(30)
 -修改字段名
 alter table <表名>change<旧字段名><新字段名><新数据类型>
 alter table tb_dept1 change location loc varchar(50),--将location改为loc,其他不变;也可以改变varchar(50)为其他的数字
 -添加字段
 alter table <表名>add<新字段名><数据类型>[约束条件][first|after 已存在的字段名];
 (1)alter table tb_dept1 add managerld int(10);
 (2)alter table tb_dept1 add columnl varchar(12) not null;
 (3)alter table tb_dept1 add column2 int(11) first;
 (4)alter table tb_dept1 add column3 int(11) after name;
 -删除字段
 alter table <表名>drop<字段名>
 alter table tb_dept1 drop column2;
 -修改字段排列位置
 alter table<表名>modify<字段1><数据类型>first|after<字段2>
 alter table tb_dept1 modify column3 varchar(12)first;
 alter table tb_dept1 modify columnl varchar(12) after location;
 -删除外键约束
 alter table <表名>drop foreign key <外键约束名>
 alter table tb_dept3 drop foreign key fk_emp_dept1;
 ----------------------数据处理-(student 为表名)---------------------
 -插入数据
 insert into 表名 values(内容);
 insert into student values(101,'张三', '男',1985,'计算机系', '北京市海淀区');
 - 查询表中所有数据
 select * from student; 
 select * from 表名;
 - 查询表中的2-4条记录
 select * from student limit 1,3;
 - 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息
 SELECT id,name,department FROM student;
 - 从student表中查询计算机系和英语系的学生的信息
 SELECT * FROM student WHERE department IN ('计算机系','英语系');
 - 从student表中查询年龄18~22岁的学生信息
 SELECT id,name,sex,2013-birth AS age,department,address FROM student WHERE 2013-birth BETWEEN 18 AND 22;
 - 从student表中查询每个院系有多少人 
 SELECT department, COUNT(id) FROM student GROUP BY department;
 - 从score表中查询每个科目的最高分
 SELECT c_name,MAX(grade) FROM score GROUP BY c_name;
 - 查询李四的考试科目（c_name）和考试成绩（grade）
 SELECT c_name, grade FROM score WHERE stu_id= (SELECT id FROM student WHERE name= '李四' );
 - 用连接的方式查询所有学生的信息和考试信息
 SELECT student.id,name,sex,birth,department,address,c_name,grade FROM student,score WHERE student.id=score.stu_id;
 - 计算每个学生的总成绩
 SELECT student.id,name,SUM(grade) FROM student,score WHERE student.id=score.stu_id GROUP BY id;
 - 计算每个考试科目的平均成绩
 SELECT c_name,AVG(grade) FROM score GROUP BY c_name;
 - 查询计算机成绩低于95的学生信息
 SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name="计算机" and grade<95);
 - 查询一组数据的平均值的最大值
 select s_id,avg(f_price) as a_price from fruits group by s_id order by a_price desc limit 1;
 - 查询同时参加计算机和英语考试的学生的信息
 SELECT * FROM student WHERE id =ANY ( SELECT stu_id FROM score WHERE stu_id IN (SELECT stu_id FROM score WHERE c_name= '计算机') AND c_name= '英语' );
 - 将计算机考试成绩按从高到低进行排序
 SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;
 - 从student表和score表中查询出学生的学号，然后合并查询结果
 SELECT id FROM student UNION SELECT stu_id FROM score;
 - 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
 SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score WHERE (name LIKE '张%' OR name LIKE '王%') AND student.id=score.stu_id ;
 - 查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
 SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score WHERE address LIKE '湖南%' AND student.id=score.stu_id;