Operation problems with table in MySQL

Take advantage of a MySQL problem today to learn a small test of MySQL operations in the early days!! Now summarize the operation of MySQL in the creation of tables, inserting specific data, and querying data that satisfies a certain condition.

First, you need to set up the following three tables (table);

Client Table Customer :

CNO Customer number

CNAME Customer Name

Product Table Goods:

Gno Product number

Gname Product Name

Order Form orders :

Data date (8-bit integer representation, e.g. 20160823)

Ono Order Number

CNO Customer number

Gno Product number

Number of Amount

Mysql build code for the table:

1 Create Database if  not existsTest//first create a database2  3  UseTest//Enter the database4 5 Create TableCustomer (IDintAuto_incrementPrimary Key);//CREATE table ID column automatically+16 7 Alter TableCustomerAdd column(CNOvarchar( -), CNAMEvarchar( -));//add CNO columns and CNAME columns8 9 Insert  intoCustomerTen (CNO, CNAME) One Values A("001", "001"), -("002", "002");//This creates a good customer table

Customer table:

This operation gets the goods table and the Orders table:

1. Check the product number and product name that have not been purchased for 2015 years:

1  Select  from Goods 2 where  not inch (Selectfromwherebetween20150101 and 20151231);

Results shown:

2. Check the 2016-year order, show the order date, order number, customer Name:

1 Select  from orders 2 where between 20160101  and 20161231;

Results shown:

3. Delete Orders prior to 2015:

1 Delete  from orders 2 where < 20150101 ; 3 4 5 Select *  from // Query orders data

Results shown:

4. Query the number of orders over 4 commodity numbers and total quantity and sort in descending order of total quantity:

1 Select  from orders 2 where > 4 Order  by desc;

Results shown:

Basic operation is like this, these days have been studying MySQL grammar, the brain feel in a paste feeling, or need a little time to absorb these things slowly!

One of the most obvious ways to create an appropriate index for an order is to delete the ID primary key, add a date or other column as the new index, and do so because I add the ID as an auto-increment and a primary key.

Take a look at some of the syntax used:

1 Alter TableTableNameDropId//Delete ID column2 3 Alter TableTableNameAddIdintAuto_incrementPrimary KeyFirst//Add the ID column to the first column and give the primary key index4 5 6 Delete  fromTableNamewhereId=1;//Delete ID=1 This line7 8 UpdateTableNameSet column = Values whereId= 1;//In the ID=1 Assign values to column columns in this line9 Ten Select *  fromTableNameOrder  by cast(column  asSigned/Unsignedinteger);//Change column properties to change varchar to int

1 Alter Table Add Index (column); // Create an index 2 3 Alter Table Add Primary Key (column); // Create PRIMARY key index

Operation problems with table in MySQL