MySQL Common operations Summary

MySQL Common operations

Prerequisites: MySQL is installed.

Learning Goal: Write out the remainder of a and B with an SQL statement

AA table

BB table

The above is an interview question, followed by the question to review some of the basic operation of the database.

Log in to MySQL

sudo mysql-p, enter the password, and then press CTRL + C to exit the login:

Display Database

show databases;

Create a database

Create DATABASE xxx;

Select Database

Use database xxx;

Show Database Tables

Show tables;

Create a database table

Create TABLE XX (fieldName type,fieldname type,...);

Inserting data (and querying)

Insert INTO XX (fieldName type,fieldname type,...) VALUES (value1,value2,...);

Now that the table has been built, the data is inserted and completed. The next thing to do is to do some subtraction, to count A, B, and C to buy and sell the amount, and then subtract.

Querying database tables

You can see that the ID in the AA table is not duplicated, and there are duplicate data in the BB table, so the Sell field values for the same ID data in the BB table need to be counted plus:

Select Id,sum (Sell) as Sumsell from the BB group by ID;

This completes the sell and statistics for the same ID field.

Multi-table Joint search

Use the above query results as a new table CC, and a known table AA to do a subtraction to get the remaining amount of a, B product (Buy-sumsell):

Select Aa.id,buy-sumsell from AA, (select Id,sum (Sell) as Sumsell from BB Group by ID) as CC where aa.id=cc.id;

MySQL Common operations Summary