Getting started with MySQL

I. Definition

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications. belongs to a small database

Two. Basic operation

2.1 Library-level operations

Create database database name [character set character set][collate proofing rules

Character Set (CharSet): is a set of symbols and encodings.

Proofing Rules (Collation): A set of rules for comparing characters within a character set

View all databases Show databases

View Character set (view database definition) show CREATE database name

Delete Database CREATE database name

Modify database character set ALTER DATABASE name character Set character set

Switch database use database name

View the database you are using select Databases ();

2.2 Table-level operations

• Create a table　　

CREATE TABLE table name (        column name type constraint,        column name type constraint,        column name type constraint    );

• View Table

View all tables show tables;

View the table's definition structure (view the table's fields) DESC table name;

View the Build Table statement (not important) show create TABLE table name

• Modify Table

Add a column; ALTER TABLE name add field name type constraint;

Modify the column type constraint; ALTER TABLE name modify field type constraint;

Modify column name, type, constraint; ALTER TABLE name change old column new column type constraint; New column must specify type

Delete a column; ALTER TABLE name drop column name;

Modify the table name; Rename table name to new table name;

• Delete a table

drop table name;

2.3 Data-level operations

　　

Inserting Data

Insert into table name (column, column:) VALUES (value, value ...); Insert into table name values (value, value ...) How many values will be written in the following columns, even if you want to be empty you also have to write null.

　　

Update records (update all row data if not conditionally)

    Update table name Set field = value, field = value [Where Condition]

Delete record (table still in)

Delete from table name [where condition], if no condition is equal to delete all data in the table. TRUNCATE table name; Delete all data from the table

Query records (key points, difficulties)

Select [Column name, column name] [*] [aggregate function][distinct field] FROM table name [WHERE--Group by-->having--> ORDER by]

* aggregate functions cannot be used in the Where condition

? Count (*| field); counts the number of record rows that specify columns that are NOT NULL, and is typically used for the total number of data bars in the statistics table. For the accuracy of the statistics, the column names of columns that cannot be empty in parentheses are passed in or *

? If you want the total number of data bars in the statistics, then count (*| cannot be an empty field name)

? SUM (), calculates the value of the specified column, and evaluates to 0 if the specified column type is not a numeric type

? Max (); Calculates the maximum value of the specified column, and if the specified column is a string type, use the string sort operation

? Min (); Calculates the minimum value of the specified column, and if the specified column is a string type, use the string sort operation

? AVG (); Calculates the average of the specified column, if the specified column type is not a numeric type, the result is 0

*where and having the difference

• 1.having Filters the data after grouping . Where is the filtering of data before grouping

• 2.having you can use aggregate functions later, where you cannot use aggregate functions

• Only grouped field names or aggregate functions that you use can appear in the conditions after 3.having

• 4.WHERE is the condition that is recorded before grouping, and if a row record does not meet the conditions of the WHERE clause, the row record does not participate in grouping, and having is a constraint on the data after grouping.

Order: Select...from...where...group By...having...order By...limit A, B (explanation: Limit A, a paging query);

Getting started with MySQL