Download address

Dev.mysql.com/downloads/m…

Double click on the install

Foolproof installation

Modifying environment Variables

Create or open the. Bash_profile file

Open the.bash_profile file and enter the following environment configuration

export PATH=${PATH}:/usr/local/mysql/bin
Copy the code

Configure source ~/.bash_profile in the. ZSHRC file

The login

mysql -u root -p
Copy the code

Database A container that holds organized data (usually a file or group of files)

Table A structured list of data of a particular type

Creating a database

create database db_name;
Copy the code

Query an existing database

Returns a list of available databases

show databases;
Copy the code

Using a database

use db_name;
Copy the code

Deleting a Database

drop database testone;
Copy the code

Create a table

create table pirate(id int not null,name char(60) null,address varchar(60) null,sex char(20) null,age char(20) null.primary key(id));
Copy the code

AUTO_INCREMENT

Tell MySQL that this column is automatically incremented each time a row is added. Each time an INSERT is performed, MySQL automatically increments the column (hence the keyword AUTO_INCREMENT) to assign the next available value to the column

PRIMARY KEY

The PRIMARY KEY of a table can be specified when the table is created using the PRIMARY KEY keyword.

Update the list

alter table navy add intro varchar(600)
Copy the code

Add a primary key

Insert data into

INSERT is used to INSERT (or add) rows into a database table

Insert the full row

Requires the table name and the value to be inserted into the new row

 insert into pirate values(1.'kay more'.'Ghost Island in the Land of Peace'.'male'.30);
Copy the code

Insert multiple rows

insert into pirate(id,name,address,sex,age) values(2.'Charlotte Lingling'.totland.'woman'.28), (3.'Edward Newgate'.'The ability is known as the most powerful superhuman zhuzhen fruit.'.'male'.35);
Copy the code

Update the data

UPDATE (modify) data in a table, using the UPDATE statement

update pirate set address = 'An island of no name' where id = 3;
Copy the code

UPDATE statements always start with the name of the table to be updated. In this example, the name of the table to be updated is Pirate. The SET command is used to assign the new value to the updated column.

The UPDATE statement ends with the WHERE clause, which tells MySQL which row to UPDATE.

Delete the data

DELETE (DELETE) data from a table using the DELETE statement.

delete from pirate where id = 4;
Copy the code

If you want to DELETE all rows from a table, do not use DELETE. You can use the TRUNCATE TABLE statement, which does the same job but is faster (TRUNCATE actually deletes the original TABLE and creates a new one, rather than deleting the data in the TABLE row by row).

Sorting data

To unambiguously sort the data retrieved with the SELECT statement, use the ORDER BY clause. The ORDER BY clause takes the names of one or more columns and sorts the output accordingly.

A single order

select * from eagle_team order by num;
Copy the code

Sort by num

Sort by multiple columns

select name,num from eagle_team order by name,num;
Copy the code

Specify sort direction

select * from eagle_team order by num desc;
Copy the code

The DESC keyword applies only to column names directly preceding it. The opposite of DESC is ASC (ASCENDING), which you can specify when ASCENDING sort. In practice, however, ASC is of little use because the ascending order is default (if you specify neither ASC nor DESC, then ASC is assumed).

Using a combination of ORDER BY and LIMIT, you can find the highest or lowest value in a column.

select * from eagle_team order by num desc limit 1;
Copy the code

Filter the data

Use the WHERE clause of the SELECT statement to specify the search criteria.

Database tables typically contain a large amount of data, and it is rarely necessary to retrieve all rows in the table. Typically, only a subset of the table data is extracted based on the needs of a particular operation or report. Search criteria, also known as filter conditions, need to be specified to retrieve only the required data. In the SELECT statement, the data is filtered based on the search criteria specified in the WHERE clause. The WHERE clause is given after the table name (FROM clause)

select * from eagle_team where id = 1;
Copy the code

The WHERE clause operator

Data filtering

The AND operator

Update the table

Update the TABLE definition using the ALTER TABLE statement

Query all tables

Returns a list of available tables in the currently selected database.

show tables;
Copy the code

View basic information about the table

desc pirate;
Copy the code

Retrieve the data

SELECT retrieves table data and must give at least two pieces of information — what you want to SELECT, and from where.

Retrieving a single column

Retrieve a column named PROD_NAME from the Products table using the SELECT statement. The required column names are given after the SELECT keyword, and the FROM keyword indicates the name of the table FROM which the data is retrieved

select name from student;
Copy the code

Retrieve multiple columns

select name,age,home from student;
Copy the code

Retrieve all columns

Given a wildcard character (*), all columns in the table are returned.

select * from student;
Copy the code

Retrieves different rows

select distinct name from eagle_team;
Copy the code

Limit the results

The SELECT statement returns all matching rows, which may be each row in the specified table. To return the first or first few lines, use the LIMIT clause.

LIMIT with a value always starts on the first line and gives the number of rows returned. A LIMIT with two values can specify that the line number starts at the first value.

select * from eagle_team limit 3;
Copy the code

select * from eagle_team limit 2.3;
Copy the code

MySQL data type

String data type

No matter what form of string data type is used, string values must be enclosed in quotation marks (single quotation marks are usually better)

If the value is a value used in a calculation (sum, average, etc.), it should be stored in the value data type column. If used as a string (which may contain only numbers), it should be stored in a string data type column.

Numeric data type

Date and time data types

Binary data type

Binary data types can store any data (even binary information), such as images, multimedia, and word processing documents