数据库第二次作业

最新推荐文章于 2023-04-19 19:45:43 发布

张高波

最新推荐文章于 2023-04-19 19:45:43 发布

阅读量985

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_52674640/article/details/124347623

版权

这篇博客主要涵盖了MySQL数据库的查询操作，包括选择特定字段、按条件过滤记录、范围查询、聚合函数应用、排序和连接查询等。通过一系列实例，如查询特定部门员工、工资范围、部门信息及员工统计，深入探讨了SQL查询的多种技巧和方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

查询语句

select e_no,e_name,e_salary from employee;
select *from employee where dept_no = 10 or  dept_no = 20;
select * ,(select d_name from dept d where d.d_no = e.dept_no ) 部门 from employee e where e_salary>800 and e_salary<2500;
select * ,(select d_name from dept d where d.d_no = e.dept_no ) 部门 from employee e where dept_no=20;
select * ,(select d_name from dept d where d.d_no = e.dept_no ) 部门 from employee e where e_salary=any(select max(e_salary) from employee e  group by e.dept_no);
select d_name ,d_location ,(SELECT e_name from employee where e_name= 'BLAKE') 姓名 from dept d where d.d_no=(select dept_no from employee where e_name = 'BLAKE');
select e.e_name,d.d_name,d.d_location from employee e,dept d where e.dept_no = d.d_no;
select count(1) ,d.d_name from employee e ,dept d  where e.dept_no = d.d_no group by  e.dept_no;
select sum(e_salary),d.d_name from employee e ,dept d  where e.dept_no = d.d_no group by  e.dept_no;
select avg(e_salary),d.d_name from employee e ,dept d  where e.dept_no = d.d_no group by  e.dept_no;
select *from employee where e_salary<1500;
select *from employee order by  e_no desc;
select *from emplyee where e_name regexp '^S'OR e_name regexp'^A';
/*14、不会
15、不会*/
select * from (select * ,row_number() over (order by e_salary) rownum from employee) g where g.rownum between 3 and 6;
select min(g.e_salary) from(select e_salary from employee where e_job = 'SALESMAN') g;
select e_name from employee  where e_name regexp 'N$' OR  e_name regexp 'S$';
select e_name,e_job from employee e where e.dept_no = (select d_no from dept where d_location='BeiJing');
/*20、不会*/
select * from employee   where hireDate > 2004-12-31 and  hireDate < 20016-01-01 and dept_no=20   union all select * from employee   where hireDate > 2004-12-31 and  hireDate < 20016-01-01 and dept_no=30 ;
select * from employee where e_name like '%A%'
select e_name from employee where e_name regexp '^.*T*C*M*.*$' ;

创建表以及表内容1111

create table employee(
		e_no  int(11) primary key not null unique,
		e_name  varchar(50)  not null,
		e_gender char(2),
		dept_no  int(11) not null,
		e_job  varchar(50)  not null,
		e_salary int(11) not null,
		hireDate date  not null
	);
create table dept(
		d_no int(11)  primary key auto_increment not null  unique,
		d_name varchar(50) not null,
		d_location  varchar(50)
);
	alter table employee add constraint pk_emp foreign key(dept_no) references dept(d_no);

1. 查询所有记录的e_no、e_name和e_salary字段值