MySQL Basic Operation

1. Connect to the database

To view available databases, use the show command:

SHOW DATABASES;

Connect to the database using the use command:

 use test;

Get all tables within the database:

SHOW TABLES;

To view the table structure:

 from Test;

2. Retrieving data

Retrieve a column or columns:

SELECT  from the products;

Retrieve all columns:

SELECT *  from the products;

If you want to display only the different values in a column, you can use the DISTINCT keyword:

SELECT DISTINCT  from the products;

Limit the number of results, with the Limit keyword:

SELECT DISTINCT  from 5 ; SELECT DISTINCT  from 0 5; 5 Starting from line 1th (counting from 0)

3. Sorting

Sort by using the ORDER BY clause:

SELECT  from ORDER  by name, id; SELECT  from ORDER  by DESC; The default is ascending, with DESC set to the descending order of IDs

4. Filtering

Filter data using the WHERE clause:

SELECT  from WHERE  2;

You can also filter a range:

SELECT  from WHERE between 5  and ten;

Look for a record that the value exists:

SELECT  from WHERE  is NULL;

You can also use multiple where clauses, with and or or in between.

If the record you want to query has an explicit scope, you can use the IN keyword or the NOT keyword.

SELECT  from WHERE inch (123); SELECT  from WHERE  not inch (123);

5. Wildcards regular with wildcard expression

Use the LIKE keyword to match wildcard characters. % represents more than one character, _ represents a single character.

SELECT  from WHERE  like ' s%e ' find name records with the beginning of S and e ending SELECT  from WHERE  like ' _s '; Find two words and the last letter is a record of s

Use the RegExp keyword to make regular matches.

SELECT  from WHERE ' . XX '; Output all records that contain 00

' 1|2 ' indicates a match of 1 or 2,[123] that matches one of 1, 2, 3, and [1-9] represents one of the matching 1-9,. Represents a match of one character, * represents 0 or more matches, + represents 1 or more matches,? Represents a 0 or 1 match.

{n} means a match of n characters, {n,} for more than n characters, and {n,m} to match n-m characters.

\ \ represents an escape character.

^ Represents the beginning of the text, and $ represents the end of the text. The [^1-3] represents a character other than 1-3.

Wildcards match the entire field, while regular expressions match part of the field.

6. Stitching

Stitching combines the contents of two fields with the Concat method:

SELECT ' (') '  as  from Product;

You can also calculate the values of two fields:

SELECT number, price* number as from products;

7. Functions

Common functions include date (), Time (), Length (), LTrim (), RTrim (), Upper (), Lower (), AVG (), SUM (), COUNT (), MIN (), MAX (), and so on.

SELECT COUNT (*asMINasMAXasAVG as from Products

8. Grouping

Grouping is implemented by the Group by method. Group by should be in the where, before order by.

SELECT COUNT (*as from theGROUP by ID;

For data rows, where to filter, and for groups to have a have to filter, that is, where the data is grouped before filtering, and having the data grouped after filtering:

SELECT COUNT (*as fromGROUPby haveCOUNT(*>  ten;

SELECT SUM (Num*as fromWHERE>tenGROUP by having SUM(num*>

9. Sub-query

Write each of the two queries together:

SELECT  from WHERE inch (SELECTfromWHERE=ten);

Or use a subquery as a new field:

Select name, ID, (selectCOUNT(*fromWHERE=   as from customers;

10. Junction table

The two tables will be connected to the query. The two tables of the junction query must have a junction relationship, otherwise the output is a Cartesian product of two tables:

SELECT  from WHERE = ORDER  by Vend_name;

You can also write:

SELECT  from INNER JOIN  on = products.vend_id;

The above connection method is called internal coupling. In addition, there are self-joins and external junctions.

A self-junction refers to itself more than once in a SELECT statement. As shown below:

SELECT  from WHERE = (SELECTfromWHERE=5);

The above is a subquery, find out the vendor ID of all prod_id 5 items, and then find the product ID and name of this supplier. Write a junction query as follows:

SELECT  from  as  as WHERE =  and = 5;

11. Combination Query

Use Union to Union two or more select query statements together. The two SELECT statement queries must have the same columns:

SELECT  from WHERE >= 5 UNION SELECT  from WHERE > 5;

12. Data insertion

Data is inserted using the INSERT INTO method. If the inserted data is arranged in the order of the columns, it can also be omitted (ID, name, price), but it is best to write:

INSERT  into VALUES (9'new');

To insert more than one, add a new entry directly after (9, ' new ', 300), separated by commas.

You can also insert data from a select query.

INSERT  into SELECT  from NewProducts;

13. Updates and deletions

Update using the Update method:

UPDATE SET =  - WHERE = 8;

You can also use update to delete a piece of data:

UPDATE SET = NULL WHERE = 6;

Delete a piece of data by using the Delete method:

DELETE  from WHERE = 7;

To delete the entire table, you can use the Trucate method:

TRUNCATE TABLE test;

14. Create a table

CREATE TABLE Products (ID  int.not  NULL  auto_increment,name  Char () not  NULL,  pricefloat  not  NULL,PRIMARYKEY= InnoDB;

The value of the primary key in the table must be unique, but the primary key can be determined by multiple columns together.

You can set a default value, such as defaults 0.

Engine mainly has InnoDB, MyISAM, memory three kinds. InnoDB supports transactions, does not support full-text search, MyISAM supports full-text search, does not support transactions, memory is the same as MyISAM, but data is stored in memory for temporary tables.

15. Updating and deleting tables

To update the table structure, use the Alter method:

ALTER TABLE ADD int ; ALTER TABLE DROP COLUMN vend_id;

You can also update foreign keys with the Alter method:

ALTER TABLE ADD CONSTRAINT FOREIGN KEY REFERENCES Vendors (prod_id);

To delete a table by using the Drop method:

DROP TABLE Products;

Rename with rename method:

TABLE  to NewProducts;

MySQL Basic Operation