Day06 homework -- skill question 2

1. establish test database

 create database test character set utf8;

2. stay test Create in the database emp surface (id Set as primary key self growth )

create table emp(
empno int not null primary key auto_increment,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
COMM decimal(7,2),
deptno int
)
desc emp;

3. towards emp Add records to the table

insert into emp value (1001,' Gan Ning ',' Clerk ',1013,'2000-12-17',8000.00,null,20),
(1002,' Daisy ',' Salesperson ',1006,'2001-02-20',16000.00,3000.00,30),
(1003,' Yin Zhengtian ',' Salesperson ',1006,'2001-02-22',12500.00,5000.00,30),
(1004,' Liu bei ',' The manager ',1009,'2001-04-02',29750.00,null,20),
(1005,' Thank sun ',' Salesperson ',1006,'2001-09-28',12500.00,14000.00,30),
(1006,' Guan yu ',' The manager ',1009,'2001-05-01',28500.00,null,30),
(1007,' Zhang Fei ',' The manager ',1009,'2001-09-01',24500.00,null,10),
(1008,' Zhugeliang ',' analysts ',1004,'2007-04-19',30000.00,null,20),
(1009,' Zeng a Niu ',' Chairman of the board of directors ',null,'2001-11-17',50000.00,null,10),
(1010," Xiangr "," Salesperson ",1006,"2001-09-08",15000.00,0.00,30),
(1011," Zhou Tai "," Clerk ",1008,"2007-05-23",11000.00,null,20),
(1012," Cheng pu "," Clerk ",1006,"2001-12-03",9500.00,null,30),
(1013," Pang Tong "," analysts ",1004,"2001-12-03",30000.00,null,20),
(1014," Huang Gai "," Clerk ",1007,"2002-01-23",13000.00,null,10),
(1015," Zhang San "," Clerk ",1007,"2002-01-23",53000.00,null,50)

Use sql Statement completes the following functions ;

(1) Look up everything in the table

select * from emp;

(2) The name in the query table is all the message records of Zhang San

select * from emp where ename=' Zhang San ';

(3) The name in the query table is the name of all employees consisting of three words ename,job,sal The corresponding information of the field

select ename,job,sal from emp where ename like '___';

 (4) In the query table empno Fields from 1004 to 1008 Records of all employees

select * from emp where empno between 1004 and 1008;

(5) Query all in the table job All the information of the employee whose field is clerk and whose name is Huang Gai

select * from emp where job=' Clerk ' and ename=' Huang Gai ';

 (6) Query table in 2001 Information of employees who have been employed since

select * from emp where hiredate >='2002-01-01';

 (7) The bonus in the query table （COMM） yes NULL Employee information

select * from emp where  COMM is null;