It is recommended to use the installation package. After installation, modify the mysql configuration file my.ini to change the character set to utf8

show database

show databases;

If the database mysql9_17 delete mysql9 if it already exists_ seventeen

drop database if exists mysql9_17;

Create database mysql9_17 and set the character set to utf8

create database if not exists mysql9_17 character set utf8mb4;

View mysql9_17 what tables are there in the database

show tables;

If the table student already exists, delete the student

drop table if exists student;

Create a department table with a department field_ ID (self increment of primary key), name

create table depart(depart_id int primary key auto_increment,name varchar(20));

View the Department table structure

Create employee table staff with staff field_ id,name,age,depart_ id

create table staff(staff_id int primary key auto_increment,name varchar(20),age int,depart_id int);

View the structure of staff table

Create salary table with salary field_ id,staff_ id,salary,month

create table salary(salary_id int primary key auto_increment,staff_id int,salary decimal(11,2),month datetime);

View the structure of the salary table

After the table is created, insert data into the three tables. In the process of inserting data into the table, you can specify the inserted columns, which are all columns by default; At the same time, you can also insert multiple pieces of data at a time, separated by commas between parentheses. It is generally considered that inserting multiple pieces of data at a time is more efficient than inserting one piece of data at a time

insert into depart values(null,'Marketing Department');
insert into depart values(null,'Technology Department');

insert into staff values(null,'Lu Shuo',23,1),(null,'Yu Neng',23,1),(null,'Sun Yigui',23,2),(null,'Zhu Jialong',23,2);

insert into salary values(null,1,10000,'2021-9-18 10:00:00'),(null,2,10000,'2020-9-18 10:00:00'),(null,3,10000,'2019-9-18 10:00:00'),(null,3,10000,'2018-9-18 10:00:00');

View all data in three tables

Query salary table to increase everyone's salary by 10000 (the query result is only a temporary table and will not affect the data on disk)

select salary+10000 from salary; 

De duplication: remove the data with the same salary in the salary table according to the query results

select distinct salary from salary;

According to Sally_ ID is displayed in ascending or descending order

Change staff in salary table_ Salary with ID 2 has a value of 20000
Note that if the update statement does not specify where, it will modify all data, which is prone to errors.

update salary set salary=20000 where staff_id=2;
update salary set salary=30000 where staff_id=3;
update salary set salary=40000 where staff_id=4;

Condition query: query the data information of salary greater than 25000 in salary table

select * from salary where salary>25000;

Pay attention to < = > < >

update salary set salary=null where salary_id=4;
select * from salary where 

Logical and (and) or (or) can also be used in conditional queries
There is also the keyword in

Paging query (also to prevent problems caused by excessive transmission): two methods of paging query

select * from salary limit 1;
select * from salary limit 1 offset 1;
--Start with the second piece of data and take one piece of data later
select * from salary limit 1 offset 2;
select * from salary limit 1 offset 3;

Start with the third piece of data and take one piece of data later
select * from salary limit 3,1;

Delete salary table
Delete all data in the salary table
Delete some data in the salary table

drop table salary;
delete from salary;
delete from salary where salary_id=1;