Use of command line

1. Find mysqlbin directory of phpstudy installation

2. mysql file location in phpstudy settings

3. Enter the bin directory of mysql, press the shift key + right key, and open the command line here.

mysql

#Enter mysql

mysql -u root -p

View all databases

show databases;

Create database

create database Database name;

After adding, refresh the class database in the visualization!

Modify the encoding format of the database

alter database Database name default character set 'utf8mb4';

In visualization, right create data and click database properties to view the utf8mb4 format

Select database

use Database name

Delete database

DROP database Database name

Create table

create table Table name(
	Field name type( int,varchar,date)[not null][comment 'Annotation information'],
)

Right click refresh on the table

View all tables

show tables;

View the field information of the table

desc Table name;

Modify table name

alter table news(Table name) rename new(New table name);

***

Delete table

drop table Table name;

Recorded operation

insert

Single addition

insert into Table name values(Value 1, value 2);

insert into Table name values(Value 1, value 2)；

Multiple additions

insert into Table name(Field 1, field 2...) values (Value 1, value 2);

insert into Table name values(Value 1, value 2),(Value 1, value 2)；

difference:

The first method is to write the fields that need to be assigned values

The second way is to assign values to all fields by default.

Delete

// Delete all records in the table
delete from Table name;
// Delete record with id 5
delete from Table name where Conditional judgment;
// Delete a record that contains a character in the field
delete from Table name where Field name like '%character%'

//Delete the record with ID 4 delete from table name where id = 5;

//Delete a record that contains a character in the field
delete from table name where field name like '% character%'

Update

update Table name set field = 'New value' where Conditional judgment;
// Modify the value of multiple fields
update classinfo set field = 'New value',field = 'New value'  where id = 3;

//Modify the value of the class field in the new table with id 5 to grade 6;
update new set class = "sixth grade" where id = 5;

//Modify the value of multiple fields
//Modify the value of the class field in the new table with id 5 to one, and the value of the name field to Wang Fugui
update new set name = 'Wang Fugui', class = 'one book', where id = 5;

Select

Single table query

// Query all fields, all records * means all fields, and query without conditions means query all records
select * from Table name;
// Query some fields
select Field 1, field 2 from Table name;

//Query all fields

//Query some fields

Condition query

where statement

//Equal to query

select*from new(Table name) where id=1;

//Greater than less than query

select*from new where id > 2;

//Fuzzy query (like)

 select*from new where name like '%king%';

And and

Records that meet the conditions on both sides will be matched.

// Records that meet the conditions on both sides will be matched.
 select * from new where name like '%king%' and class like '%book%';

Or or

As long as the conditions on the left and right meet one, they will be matched.

// As long as the conditions on the left and right meet one, they will be matched.
select * from classinfo where className like '%introduction%' or teacher like '%expensive%'

in

The value of the field before in will match as long as it is equal to any one in the parentheses.

// Multiple values can be specified in the where clause, and the values of the fields in the record will match as long as they are in multiple values.
 select * from classinfo where id in(1,2,3);

Advanced query

// Number of query records
select count(*) from Table name;
// Query the number of qualified records
select count(*) from Table name where Conditional judgment;

//Query the sum of qualified records
Select sum (field) from table name where condition judgment;

//Maximum

 select max(id) from new where id>2;

//Minimum value

 select min(id) from new where id>2;

//The first parameter of paging indicates where to start the query, which is a subscript. Starting from 0, the second parameter is the number of queries.

select * from new(Table name) limit 0,2;

multi-table query

// multi-table query  
select * from Table 1 as Alias 1,Table 2 as Alias 2 where Association condition
select * from student s,classinfo c where s.id = 1 and s.classId = c.id;

// Left connection
 select * from student s left join classinfo c on s.classId = c.id;
// Right connection
select * from student s right join classinfo c on s.classId = c.id;