查询数据练习（源码

查询数据练习（源码

#创建数据表
USE tx;
create table dept
(d_no int(11) not null primary key auto_increment,
d_name varchar(50),
d_location varchar(100)
);
create table employee
(
e_no int not null primary key,
e_name varchar(50) not null,
e_gender char(2) not null,
dept_no int(11) not null,
e_job varchar(50) not null,
e_salary int(11) not null,
hiredate date,
CONSTRAINT dno_fk foreign key(dept_no)
references dept(d_no)
);
#插入数据
INSERT INTO dept 
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

INSERT INTO employee 
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'), 
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

#查询数据

select e_no,e_name,e_salary from employee;

select * from employee where dept_no = 10 or dept_no = 20;
select * from employee where dept_no in (10,20);

select * from employee where e_salary BETWEEN 800 AND 2500;

select * from employee where dept_no = 20;

select * from employee where max(select e_salary from 

select e_salary from employee where max(e_salary)

select dept_no,max(e_salary) 
from employee group by dept_no;

select max(e_salary) from employee;
select dept_no from employee GROUP BY dept_no;
select max(e_salary) from employee group by dept_no;

select d_name,d_location from dept 
where d_no = 
(select dept_no from employee where e_name = 'BLAKE');

SELECT e_no,e_name,dept_no,d_name,d_location 
from dept d,employee e 
where d.d_no = e.dept_no;

select dept_no,count(e_name) 
from employee group by dept_no;

select sum(e_salary) from employee group by e_job;

select dept_no,avg(e_salary) 
from employee GROUP BY dept_no;

select * from employee where e_salary < 1500;

select * from employee 
order by dept_no desc,e_salary desc;

select * from employee 
where e_name LIKE ('A%','S%');like错误用法

select * from employee where e_name regexp'^[AS]';

select * from employee 
where (year(curdate())-year(hireDate)) >= 18;

#合并查询结果
use test_db;
select s_id,f_name,f_price
from fruits
where f_price<9.0
union select s_id,f_name,f_price
from fruits
where s_id in(101,103);

select s_id,f_name,f_price
from fruits
where f_price<9.0
union all
select s_id,f_name,f_price
from fruits
where s_id in(101,103);

# 正则表达式
use test_db;
select * from fruits where f_name REGEXP '^b';
select * from fruits where f_name regexp '^be';
select * from fruits where f_name regexp 'y$';
select * from fruits where f_name regexp 'rry$';
select * from fruits where f_name regexp 'a.g';
select * from fruits where f_name regexp'^ba*';
select * from fruits where f_name regexp'^ba+';
select * from fruits where f_name regexp'a+';
select * from fruits where f_name regexp'on';
select * from fruits where f_name regexp 'on|ap';
select * from fruits where f_name like 'on';#like错误用法
select * from fruits where f_name regexp'[ot]';
select * from fruits where s_id regexp'[456]';
select * from fruits where f_id regexp'[^a-e1-2]';
select * from fruits where f_name regexp'x{2,}';
select * from fruits where f_name regexp'ba{1,3}';

第七章 课后习题

use tx;
select year(curdate())-year(hireDate) as gongling 
from employee where e_gender ='f';
select * from employee limit 2,4;
select min(e_salary) from employee 
where e_job = 'salesman';

select * from employee where e_name regexp'[ns]$';

select e_name,e_job from dept d,employee e 
where d.d_no = e.dept_no and d_location = 'BeiJing';

select * from employee e 
left outer join dept d on d.d_no = e.dept_no;

select * from employee 
where year(hireDate) in 
(2001,2002,2003,2004,2005) a
nd dept_no = 20 
union 
select * from employee 
where year(hireDate) in (2001,2002,2003,2004,2005) 
and dept_no = 30;

select * from employee where e_name like '%a%';
select * from employee where e_name regexp'[TCM]';