数据库select作业

作业：

1. 查看岗位是teacher员工姓名、年龄

　　SELECT emp_name,age FROM youku.employee where post = 'teacher';

 

 

 

2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄

SELECT emp_name,age FROM youku.employee where post = 'teacher' and age >30;

 

 

 

3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

SELECT emp_name,age,salary FROM youku.employee where salary>=1000 and salary <=9000;

 

 

 

4. 查看岗位描述不为NULL的员工信息

SELECT * FROM youku.employee where post_comment is not null;

 

 

 

5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

SELECT emp_name,age,salary FROM youku.employee where post='teacher' and salary in (1000,9000,30000);

6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

SELECT emp_name,age,salary FROM youku.employee where post='teacher' and salary <>1000 and salary <>9000 and salary <> 30000  ;

7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

SELECT emp_name,age,salary FROM youku.employee where post='teacher' and emp_name like 'ja%'

 

 

创建的表:

create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', # 大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, 
depart_id int
) ENGINE=InnoDB DEFAULT CHARSET=utf8