mysql command exercise

Task:
Table name: worker -- all fields in the table are in Chinese, such as department number, salary, employee number, work participation, etc
CREATE TABLE worker (
Department number int(11) NOT NULL,
Employee id int(11) NOT NULL,
Working time date NOT NULL,
Salary float(8,2) NOT NULL,
Political outlook varchar(10) NOT NULL DEFAULT 'masses',
Name varchar(20) NOT NULL,
Date of birth not null,
PRIMARY KEY
)

INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (101, 1001, '2015-5-4', 3500.00, 'Masses', 'Zhang San', '1990-7-1');
INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (101, 1002, '2017-2-6', 3200.00, 'League member', 'Li Si', '1997-2-8');
INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (102, 1003, '2011-1-4', 8500.00, 'party member', 'Liang Wang', '1983-6-8');
INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (102, 1004, '2016-10-10', 5500.00, 'Masses', 'Zhao Liu', '1994-9-5');
INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (102, 1005, '2014-4-1', 4800.00, 'party member', 'Qian Qi', '1992-12-30');
INSERT INTO `worker` (`Department number`, `Employee number`, `working hours`, `wages`, `Political outlook`, `full name`, `birth
 date`) VALUES (102, 1006, '2017-5-5', 4500.00, 'party member', 'Sun Ba', '1996-9-2');

1,Display the basic information of all employees.   
2,Query the department number of the Department to which all employees belong. Duplicate Department numbers are not displayed.  
3,Find out the number of all employees.  
4,List the maximum and minimum wages.   
5,List the average salary and total salary of employees.   
6,Create a new table with only employee number, name and work participation, called work date table. 
7,Displays the age of all female employees. 
8,List the employee number, name and date of birth of all employees surnamed Liu.
9,List the names and working dates of employees born before 1960.
10,List salary in 1000－2000 Names of all employees between. 
11,List the names of all employees surnamed Chen and Li.
12,List all employee numbers, names and Party members with department numbers 2 and 3.  
13,Employee list worker The employees in the are sorted according to the order of birth.
14,Display the employee number and name of the top 3 employees with the highest salary. 
15,Find out the number of Party members in each department. 
16,Statistics of wages and average wages of various departments
17,List the department number and total number of people whose total number is greater than 4.

practice:
Create a database + database name first

show databases can see all databases;

use + database name can enter the database

After entering the library, we can practice
create table + table name () can generate a table, separated by commas and ending with semicolons.

After creation, desc + table name can view the created table

insert into + table name command is used to insert data into the table

After filling in the command, select * from + table name to view the modified content

1. Display the basic information of all employees.

2. Query the department number of the Department to which all employees belong. Duplicate Department numbers are not displayed.
You can use the select department number from worker group by department number command to view

3. Find out the number of all employees.
You can use Select count as' total number of people 'from worker; Command view

4. List the maximum and minimum wages.
Select max as' maximum wage 'and min as' minimum wage' from worker; see
5. List the average salary and total salary of employees.
Select AVG (salary) as' average salary ', sum (salary) as' total salary' from worker; see

6. Create a new table with only employee number, name and work participation, called work date table.
You can use create table work date table (work content varchar (255)) to select employee number and name from worker;
Then select * from work date table to view

7. Displays the age of all female employees.
Because there are no gender and age items in the table, we need to add them ourselves

Available:
alter table worker add gender varchar(20) not null;
alter table worker add age int not null;
Add table entries
You can see that it has been added. Next, modify the content

update + table name + set + (constraint modification)

After the revision, look at the table and list only those whose gender is female and their age
Available select name, gender, age from worker where gender = 'female';
8. List the employee number, name and date of birth of all employees surnamed Li
You can select employee number, name, date of birth from worker where name like 'Li%'
The percent sign indicates that any word can be followed by Lee

9. List the names and working dates of employees born before 1960.

Because no, in order to verify its feasibility, modify the conditions and be born after 1990

10. List the names of all employees whose wages are between 1000 and 2000.

Because it is 0, in order to verify that the code is feasible, modify the conditions 4000-5000

11. List the names of all employees surnamed Chen and Li

12. List all employee numbers, names and Party members with department numbers 2 and 3.

13. The employees in the employee table are listed in the order of birth.

14. Display the employee number and name of the top 3 employees with the highest salary.

15. Find out the number of Party members in each department.

16. Statistics of wages and average wages of various departments

17. List the department number and total number of people whose total number is greater than 4.