Summary of MySQL basic statement operations

This article brings you a summary of basic MySQL statement operations. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Database operation statements

(Recommended course: MySQL tutorial)

• Create
  create database database name

• View all databases
  show databases.

• View the specified database table creation statement and character set
  show create database database name

• Delete database
  drop database database name

• Modify database character set - understand
  alter database database name character set 'Character set'

• Switch database
  use database Name

• View the current database name
  select database();

Check the addition, deletion and modification of the data table structure
After you have a database, if you want to save data, you must first have a data table in the database.

• Create data table:
  use database name

• View table:
  show tables; view all tables of the database
  desc table name; view table column information (table structure)

• Constraints when creating a single table
  In order to prevent duplicate names and ensure the integrity of the data stored in the data table and effectiveness.
  Common syntax for constraints: column name data type constraints
  There can only be one primary key in a table: id int primary key auto_increment

• Data table structure deletion: you can delete the table name , column names, class types, and class constraints are added, deleted, and modified.
  Add columns: alter table table name add/delete/modify column name type (length) constraints;
  Modify column type, length and constraints: alter table table name modify column name type (length) constraints;
  Modify existing column names: alter table table name change old column name new column name type (length) constraints;
  Modify existing columns: alter table table name drop column name;
  Modify table name: rename table old table Name to new table name;
  Modify the character set of the table: alter table table name character set encoding set;
  Delete data table: drop table table name;

• data table Summary
  Data table creation (important)
  create table table name (
  column name data type constraint,
  column name data type constraint,
  …………
  );
  View tables
  show tables: View all tables
  show create table Table name: View table creation statements and character sets
  desc Table name: View table structure.
  Statements to modify the table (understand)
  alter table table name (add|modify|drop|change) column name type (length) constraints.
  rename table old table name to new table name
  delete table
  drop table table name

Simple addition, deletion, modification and query of the contents of the data table (very important )

• insert statement - increase of data records
  CRUD: create, read/retrieve, update, delete
  The most frequent database operations in Java code is the CRUD operation on the data in the table.
  Data storage location: table.

Method 1: Write in full
Syntax: insert into table name (column name, column name, column name...) values ​​(value, value, value...);
Note:

1. Values ​​correspond to columns one-to-one. There are as many values ​​as there are columns. If a column has no value. You can use null. Indicates inserting empty space.
2. The data type of the value must match the defined data type of the column. And the length of the value cannot exceed the length of the defined column.
3. String: To insert character type data, single quotes must be written. In mysql, single quotes are used to represent strings.
4. Date time type data can also be expressed directly using single quotes: ‘yyyyMMdd’, ‘yyyy-MM-dd’, ‘yyyy/MM/dd’.
5. When inserting data, if some columns can be null, or are automatically growing columns, or have default values, they can be omitted when inserting. Or write null to achieve automatic growth.
6. If you insert data into all columns in the table, you can omit the column name after the table and write values ​​directly.

Use select*from table name - view all information of the table.

Method 2: Omit some columns
A column can be omitted only if it has a default value or is allowed to be empty.
The primary key is self-increasing and is considered to have a default value, which can also be omitted.

Method 3: Omit all columns
Syntax: insert into table name values ​​(value, value, value);

• update statement - modify table records
  Grammar: update table name set column name = value, column name = value... [where conditional statement];
  The square brackets are not grammatical content, here it means that the conditional statement can be added or not.
  Notes:
  1. If no conditions are added, all values ​​of a certain column will be modified.
  2. Generally, when modifying data, you need to add conditions.
  Use commas to separate multiple columns.

eg: Change the age of everyone to 20 years old
update user set age=20;
eg: Change the age of the person named Zhang San to 18 years old
update user set age=18 where name="Zhang San";

• delete statement - statement to delete data in the table
  Syntax: delete from table name [where conditional statement]
  If there is no where, delete all data in the table
  delete deletes rows.

• Truncate statement - delete data
  Syntax: truncate table table name;
  Deleting the table first and then creating the table is equivalent to deleting all the data.
  In terms of performance: truncate table has better performance.

Summary of data record additions, deletions and modifications:
Newly added:
insert into table name values(value, value, value...)
insert into table name (column name 1, column name 2, column name 3...) values ​​(value 1, value 2, value 3...)
insert into table name (column name 2, column name 4, column name 5….) values ​​(value 2, value 4, value 5…)

Modification:
update table name set column name = value, column name = value where condition

delete :
delete from table name where condition
If you do not add the where condition, all data will be deleted.

Delete: clear data
truncate table table name
The purpose of clearing data is achieved by deleting the entire table and then re-creating a table.

The difference between delete and truncate is that the data deleted by delete can be recovered under transaction management, while truncate cannot be recovered.

Aggregation/aggregation functions in SQL
Aggregation functions: perform operations on multiple data to produce a result.
For example: sum, average, maximum, minimum.
SQL language defines some functions to implement these operations.
count function - counting the number of records (number of rows)
Syntax: select count() | count(column name) from table name
select count () from table name: Number of rows in the statistics table.

sum summation function
Syntax: select sum(column name) from table name;
select sum(column name) from table name where conditions

avg function - average value
Syntax: select avg (column name) from table name;

##max/min maximum value/minimum valueselect max(column name),min(column name) from table name

group by group query****according to a certain column or several columns. Combine the same data and output it.
select … from … group by column name;

Description: In fact, it is classified by columns, and then the classified data can be operated using aggregate functions.

Notes:
1. Aggregation function: calculated after grouping;
2. Usually the content of select: a is the grouped column, b is the aggregate function.
3. If you encounter this situation, follow each, each. Grouping is usually used when making statements like these.
4. If you use group by to group the data, you still need to filter it. Where generally cannot be used at this time, because the where keyword cannot be followed by the functions explained above. If you need to add the above function to the filtering conditions, you can only use the having keyword.
5. Where cannot be followed by an aggregate function, but having can be followed by an aggregate function.

group by column name having conditions

Add filter conditions after grouping.

The difference between where and having. 1. Having is usually used in combination with group by.

2. Having can write aggregate functions (where aggregate functions appear: after select, group by...after having) where cannot.

That is to say, the condition after Where can be followed by having, and the condition followed by where may not necessarily be followed by

3. Where is filtered before grouping. having is filtered after grouping.

When querying, if it is not necessary, it is more efficient to use where, because the data is filtered first and then other conditions are judged.

Description: select … from … where Condition 1 … gropu by … having Condition 2 order by

Condition 1 will perform filtering first

For grouping
Condition 2 for filtering

Execution order of select statements and query summary: The order of appearance of query keywords is fixed

select...content to be displayed...from...table Name… where condition…. group by…grouped column…having…condition after grouping… order by…sort

select…5… from…1… where…2… group by…3…having…4… order by …6.

select product,sum(price) as总价 from orders
    where price>10
        group by product
            having 总价>30
                order by 总价 asc;

Query execution order

1. from : Table name

2. where: Conditional filtering

   (define alias)
   

3. group by : grouping

   (aggregation function execution)
   

4. having : performed after grouping filter.

5. select: After execution, query the content.

6. order by: Sort output display.

The above is the detailed content of Summary of MySQL basic statement operations. For more information, please follow other related articles on the PHP Chinese website!