1. Install MySQL

I used MySQL5.7.34 and Navicat Premium 12.024 to install MySQL and Navicat. Specific installation can refer to here.

2. Basic concepts of database

1. The database

Database software is called DBMS(database management system), database is created and manipulated by DBMS container.

2. The table

A table is a structured file that can be used to store a specific type of data, but the data stored in a table is either a type of data or a list. You should not store different listings in the same database table.

You cannot use the same table name twice in a database but you can use the same table name in different databases.

You can think of a table as consisting of columns, each of which has a corresponding data type, rather than each row having a corresponding data type. The data in the table is composed of rows, each record stored in its own row, such as a row can store a customer’s information, customer number, address, telephone, birthday, age… And so on. A primary key identifies a row and must have a primary key column

3. MySQL

1. Log in to mysql

mysql -uroot -p
Enter password: ********
show databases;
Copy the code

Enter show Databases to see some of the default databases:

Then use the following statement to select the corresponding databasemysqlAnd display the tables inside.

use mysql;
show tables;
Copy the code

4. Basic operations of MySQL

Upper case is better than lower case so we’re going to use upper case. But it’s best to use uppercase for keywords and lowercase for custom column and table names to make it easy to distinguish and debug.

1. Create database Customer

 CREATE DATABASE customer;
Copy the code

After creation, you can see in the installation directory:

USE CUSTOMER;

2. Create a table in the database

Create table LOW from database

Then check the created table show create table low;

To display the table as a table LOW: show columns from LOW

3. Insert a row of data into the created table LOW

Simply insert a row of data (unsafe) :

INSERT INTO LOW
    -> VALUES(1- >'LISI');
Copy the code

View after insertion:

select * from LOW;
Copy the code

The above method is not safe to insert a row of data, depending on the order in which the columns in our table are defined, so use the following method:

mysql> INSERT INTO LOW(customer_id,
    -> customer_name)
    -> VALUES('2'.'XLL');
mysql> SELECT * FROM LOW;
Copy the code

4. Add a column to the created table LOW

Add a column to a table (but, ideally, the table stores data that is never updated)

ALTER TABLE LOW ADD CUSTOMER_AGE CHAR(20);
Copy the code

Insert data into the newly created column:


mysql> INSERT INTO LOW(CUSTOMER_AGE)
    -> VALUES(23);
Copy the code

Get:

5. Update the third column below the third row to update a specific value

mysql> UPDATE LOW
    -> SET CUSTOMER_AGE = '12'
    -> WHERE customer_id = 1;
Copy the code

The updated result is:

Update value syntax for multiple columns:

mysql> update low
    -> set customer_name = 'xiaohua',
    -> CUSTOMER_AGE = '11'
    -> WHERE customer_id = 3;
Copy the code

The result is:

6. Delete a row of data

mysql> DELETE FROM LOW
    -> WHERE customer_id = 2;
Copy the code

Delete the second row:

7. Query the data in a column

mysql> SELECT CUSTOMER_AGE FROM LOW;
Copy the code

Results:

Query multiple columns of data:

mysql> SELECT customer_id, customer_name FROM LOW;
Copy the code

Delete table LOW

DROP TABLE LOW;
Copy the code