1.环境搭建
创建数据库test1
create database if not exists test1;
选择test1数据库
创建两张表dept（部门表）和employee（员工表）
create table if not exists dept(
d_no int primary key auto_increment comment "部门编号（自增长主键）",
d_name varchar(10) comment "部门名称",
d_location varchar(20) comment "部门地址") comment "部门表";
create table if not exists employee(
e_no int primary key comment "员工编号",
e_name varchar(10) not null comment "员工姓名",
e_gender char(1) not null comment "员工性别",
dept_no int not null comment "部门编号",
e_job varchar(10) comment "职位",
e_salary double not null comment "薪水",
entrydate date comment "入职日期",
constraint dno_fk foreign key(dept_no) references dept(d_no)
) comment "员工表";
查看表结构
desc dept;
desc employee;
插入数据
insert into dept(d_no, d_name, d_location) values
(10, "财务部", "上海"),
(20, "研发部", "北京"),
(30, "销售部", "深圳"),
(40, "实践部", "福建");
insert into employee(e_no, e_name, e_gender, dept_no, e_job, e_salary, entrydate) values
(1, "杨逍", "男", 20, "普通职员", 800, "2005-11-12"),
(2, "杨不悔", "女", 30, "销售员", 1600, "2003-05-12"),
(3, "周芷若", "女", 30, "销售员", 1250, "2003-05-12"),
(4, "张翠山", "男", 20, "经理", 2975, "1998-05-18"),
(5, "胡青牛", "男", 30, "销售员", 1250, "2001-06-12"),
(6, "洪凌波", "女",30, "经理", 2850, "1997-02-15"),
(7, "宋远桥", "男",10, "经理", 2450, "2002-09-12"),
(8, "鹿杖客", "男",20, "分析师", 3000, "2003-05-12"),
(9, "灭绝", "女",10, "董事长", 5000, "1995-01-01"),
(10, "柳岩", "女",30, "销售员", 1500, "1997-10-12"),
(11, "宋青书", "男",20, "普通职员", 1100, "1999-10-15"),
(12, "小昭", "女",30, "普通职员", 950, "2008-06-15");
将employee表中的e_no字段值修改为5位数，如：第一位员工应为00001，不足的位数用“0”填充
update employee set e_no = lpad(e_no, 5, "0");
2.查询操作
2.1 在employee表中，查询所有记录的e_no, e_name和e_salary字段值
select e_no, e_name, e_salary from employee;
2.2 在employee表中，查询dept_no等于10和20的所有记录
方式一：select * from employee where dept_no in (10, 20);
方式二：select * from employee where dept_no = 10 or dept_no = 20;
2.3 在employee表中，查询工资范围在800~2500之间的员工信息
2.4 在employee表中，查询部门编号dept_no为20的部门中的员工信息
select * from employee where dept_no = 20;
2.5 在employee表中，查询每个部门最高工资的员工信息
分析：每个员工都可能处于不同的部门，要先找出每个部门中的所有员工，应该想到分组，将相同部门的员工分为一组，然后再使用max()函数比较最大的salary。注意不要max(group_concat(e_salary))，这样写就会报错，group_concat(e_salary)是将分组中所有的e_salary显示出来，我们直接max(e_salary)就会将该分组中所有的e_salary进行比较，拿到最大的一份。
select e_no, e_name, dept_no, max(e_salary) from employee group by dept_no;
2.6 查询员工洪凌波所在的部门和部门所在地
分析：看下题目就知道是两张表，也就应该使用连接查询，确定是用内连接还是外连接，然后找出连接条件
方式一：select e.e_name, d.d_no, d.d_name, d.d_location from employee as e, dept as d where e.e_name = "洪凌波" and e.dept_no = d.d_no;
方式二：select e.e_name, d.d_no, d.d_name, d.d_location from employee as e inner join dept as d on e.e_name = "洪凌波" and e.dept_no = d.d_no;
修改employee表中e_name列的数据为英文
update employee set e_name = "SMATH" where e_no = 1;
update employee set e_name = "ALLEN" where e_no = 2;
update employee set e_name = "WARD" where e_no = 3;
update employee set e_name = "JONES" where e_no = 4;
update employee set e_name = "MARTIN" where e_no = 5;
update employee set e_name = "BLAKE" where e_no = 6;
update employee set e_name = "CLARK" where e_no = 7;
update employee set e_name = "SCOTT" where e_no = 8;
update employee set e_name = "KING" where e_no = 9;
update employee set e_name = "TURNER" where e_no = 10;
update employee set e_name = "ANAMS" where e_no = 11;
update employee set e_name = "JAMES" where e_no = 12;
select * from employee;
2.7 使用连接查询，查询所有员工的部门和部门信息
分析：这个题跟上面那个差不多，使用内连接查询
select e.e_no, e.e_name, d.d_no, d.d_name, d.d_location from employee as e inner join dept as d on e.detp_no = d.d_no;
2.8 计算在employee表中，每个部门各有多少员工
分析：要查询每个部门用分组查询
select dept_no, count(e_name) from employee group by dept_no;
注意：sum()和count()要分清楚，SUM()是计算数值总和的，count()是计算总的记录行数的。
2.9 在employee表中，计算不同类型员工的总工资数
分析：对员工职位类型进行分组
select e_job, sum(e_salary) from employee group by e_job;
2.10 在employee表中，计算不同部门的平均工资
分析：对部门进行分组
select dept_no as "部门编号", avg(e_salary) as "平均工资" from employee group by dept_no;
2.11 在employee表中，查询工资低于1500的员工信息
select * from employee where e_salary < 1500;
2.12 在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列
select * from employee order by dept_no desc, e_salary desc;
2.13 在employee表中，查询员工姓名以字母A或S开头的员工信息
方式一：select * from employee where e_name like"A%" or e_name like"S%";
方式二：select * from employee where e_name regexp "^A" or e_name regexp "^S";
2.14 在employee表中，查询到目前为止，工龄大于等于20年的员工信息
方式一：select * from employee where (year(now()) - year(entrydate)) > 20;
方式二：select * from employee where (year(curdate()) - year(entrydate)) > 20;
解释：记得之前学过的数据类型吗，其中curdate()代表的是YYYY-MM-DD, now()代表的是YYYY-MM-DD HH:MM:SS，所以这里两个都能用，只要将其截取为year,然后相减，就能得到相差几年了。
3. 要已经创建好的employee表中进行如下操作
3.1 计算所有女员工的年龄（从入职到现在的时间）
select e_name, e_gender, (year(now()) - year(entrydate)) as e_age from employee where e_gender = "女";
select e_name, e_gender, (year(curdate()) - year(entrydate) as e_age from employee where e_gender = "女";
3.2 使用limit查询从第3条开始到第6条的记录
select * from employee limit 2,4;
3.3 查询销售员的最低工资
select min(e_salary) from employee where e_job = "销售员";
3.4 查询名字以字母N或者S结尾的记录
方式一：select * from employee where e_name like "%N" or e_name like "%S";
方式二：select * from employee where e_name regexp "N$" or e_name regexp "S$";
3.5 查询在北京工作的所有员工的姓名和职务
方式一：select e.e_name, e.e_job, d.d_location from employee as e inner join dept as d on e.dept_no = d.d_no where d_location = "北京";
select e.e_name, e.e_job, d.d_location from employee as e inner join dept as d on e.dept_no = d.d_no and d_location = "北京";
方式二：select e_name, e_job from employee where dept_no = (select d_no from dept where d_location = "北京");
方式三：select e.e_name, e.e_job, d.d_location from employee as e, dept as d where e.dept_no = d.d_no and d.d_location = "北京";
3.6 使用左连接方式查询employee和dept表
select * from employee left join dept on employee.dept_no = dept.d_no;
3.7 查询所有2001~2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用union合并两个查询结果
select * from employee where year(entrydate) between 2001 and 2005 union select * from employee where dept_no in (20, 30);
3.8 使用like查询员工姓名中包含字母a的记录
select * from employee where e_name like "%a" or e_name like "a%" or e_name like "%a%";
3.9 使用regexp查询员工姓名中包含T、C或者M 3个字母中任意1个的记录
select * from employee where e_name regexp "[t c m]";
标签：name,no,dept,查询,数据表,mysql,employee,where,select From： https://www.cnblogs.com/k0065/p/17618882.html