Detailed basic operations on data tables in MySQL database

Preface:

• The library name, table name, column name, etc. cannot be the same as a keyword. If you must use a keyword as a name, you can use backticks to quote the name.
• Before operating the table, you must first select the database

The following comments are supported in MySQL:

• -- Comment content : Single-line comment. Note that there should be a space between “–” and the comment content.
• #Comment content : single line comment
• /*Comment content*/ : multi-line comment

Note: Field or column comments can be added using the comment attribute, for example, in the table creation statement using

• In SQL, both single and double quotes can represent strings.

1. View the tables in the current database

grammar:

show tables;

Example:

2. Create a table

grammar:

create table [if not exists] table name (column name column type, column name column type, ..., column name column type);

Example:

3. View the specified table structure

grammar:

desc table name;

Notice:

desc is the abbreviation of describe. You can also use describe table name to view the table structure.

Example:

4. Delete table

grammar:

drop table [if exists] table name [, table name...];

Example:

Notice:

Deleting a table is also a very risky operation, even more dangerous than deleting a database. Because once it is deleted, the program will crash immediately and can be rescued in time. However, if you delete a table, the program may not display an abnormality immediately, so if it cannot be handled in time, there will be greater risks.

5. Add data to the table

grammar:

insert [into] table name [(column name 1, column name 2, ..., column name n)] values ​​(first row: value 1, value 2, ..., value n) [, ..., (nth row: value 1, value 2, ..., value n)]

Replenish:

• If the column name is not written in the above statement, it means that all columns need to add data
• The values ​​inserted in each row must correspond to the order of the column names to be inserted in each row.
• If a column is not inserted during insertion, the column will be set to the default value NULL

Example 1: Insert a row with all columns

The result is:

id	name
1	Zhang San

Example 2: Inserting multiple rows into a full column

The result is:

id	name
1	Zhang San
2	Li Si
3	Wang Wu

Example 3: Specify certain columns to insert

The result is:

id	name
1	Zhang San
2	Li Si
3	Wang Wu
4	NULL

Note : It is generally believed that inserting multiple records at once with one statement is much more efficient than inserting one record with multiple statements each.

We know that MySQL is a "client-server" structure program. The SQL entered by the user on the client will be transmitted to the server through the network, and then the server will perform specific operations. Let's analyze the difference between the two

One statement to insert multiple records at a time:

Multiple statements, each inserting one record:

Also, the computer's access speed to registers >>> the access speed to memory >>> the access speed to IO devices (data transmission on the network is called IO operation for computers). Therefore, it is generally believed that inserting multiple records at a time with one statement is much more efficient than inserting one record with multiple statements each.

6. Find data in the table

grammar:

selstc [distinct] {* | column name 1 [, ..., column name n]} from table name [where] [order by column name 1 [ASC | DESC] [, ..., [column name n [ASC | DESC]]] limit 

Replenish:

• The query result is a structure similar to a "table", but this table is a "temporary table" that is only stored in the memory for a while and disappears after printing. It will not be stored persistently.
• select will not modify disk data, especially will not affect the data on the server's original disk

To facilitate the following examples, the following data table has been created in the database:

6.1 Full column query

Example:

Notice:

In a production environment, use the above statement with caution because the returned data may be very large. If the data is very large, it may consume the server's network bandwidth, causing the server to fail to work properly.

6.2 Querying by Specifying Columns

Example:

Notice:

The order of the specified columns does not need to be the same as the order in which the table is defined.

6.3 Query fields as expressions

Example:

Notice:

• The above Chinese+math+english is an expression, and the result is the addition of their values. However, the added value is not restricted by the data type of the original column.
• The expression is an operation between columns and has nothing to do with rows.
• The expression calculation does not affect the original data, and the query result is a temporary table.

6.4 Assigning a constant to a column

When you specify a constant for a column during select , the constant will appear as an expression in each row.

Example 1:

Example 2:

6.5 Assigning an alias to a query expression (as)

Example:

6.6 Distinct

Use the distinct keyword to remove duplicate records with the same value in a column.

Example 1: Deduplication of a column

Example 2: Deduplication for multiple columns (requirement: distinct should be placed before all columns, and a record is considered a duplicate only when the value of each column in each row is the same as the value of each column in other rows)

6.7 Sorting query results (order by)

If our query operation does not include order by , the order of the query results is undetermined. You can add the column name/expression [asc | desc] to be sorted after the table name in the query statement to sort.

Replenish:

• asc : indicates ascending order (from small to large), without specifying the sorting method, the default is asc
• desc : indicates descending order (from large to small), which is the abbreviation of descending
• When sorting, if any value is NULL, it is considered the minimum value.
• Supports sorting of multiple columns, with priority sorting in the order of the columns to be sorted, and the columns to be sorted are separated by commas
• We know many sorting algorithms, such as bubble sort, selection sort, insertion sort, shell sort, heap sort, quick sort, merge sort, etc. Among them, merge sort is the most suitable for MySQL data sorting , because MySQL data is stored on the hard disk, and the read and write operations of the hard disk are very expensive. In order to reduce the number of read and write times, we need to find a sorting method with fewer traversals. In addition to the initial data volume, since the memory may not be able to store it all at once, each segment can be sorted and then stored. Therefore, merge sort is more suitable for MySQL to sort data.

Example 1: Query math in ascending order

Example 2 : Search math in descending order

Example 3: Sorting using an expression or alias

Example 4: Sort multiple columns comprehensively, requiring that if math is the highest, then chinese is the lowest

6.8 Conditional Query (where)

Conditional query has a wide range of applications and is not limited to the above usages. For example, statements such as update and delete can also be used. It performs a screening in the form of a where conditional expression, which is equivalent to traversing the query results, bringing each record into the condition, retaining the records that meet the conditions, and eliminating those that do not meet the conditions.

Conditional expressions can use: comparison operators or logical operators

Comparison operators:

Logical operators:

Like often matches wildcards:

Replenish:

• where conditions can use expressions, but cannot use aliases
• and has higher priority than or
• The and symbol can also realize the between function, but between is actually an optimization of SQL for this range matching.
• Wildcard: A character can be used to represent any other character or characters for fuzzy search
• Fuzzy matching involves string comparison, so when the string stored in the current column is long, the performance will be greatly reduced.
• Fuzzy matching does not necessarily require the column to be a string type. For example, a numeric type is also acceptable. However, the fuzzy search value after the like in the statement must be quoted.
• Conditions in SQL are equivalent to "filters" rather than "logical branches"

Example 1: Query students who failed English and their grades

Example 2: Query students whose total scores are less than 200 and their total scores (the following method does not include NULL values)

Example 3: Query the students whose total scores are less than 200 and their total scores, including students whose values ​​are NULL (for the convenience of display, I have proactively added a student Zhuge Liang whose value is null)

Example 4: Query students whose Chinese scores are between 80 and 90 and their scores

Example 5: Query the students and grades whose math scores are 98, 73, and 65

Example 6: Find all students with the last name Sun

Example 7: Find all students whose names end with "德"

Example 8: Query students whose names contain "悟"

Example 9: Find all students whose surname is Sun and whose names have only two characters

Example 10: Query the students whose math scores are above 90 and their math scores

6.9 Pagination query (limit)

When there are many query results, you can divide the query results into many pages.

Application scenario: When we search for something on Baidu, we find that there are many search results. In order not to get so much data at once, we use paging query.

Note: When the amount of data to be queried is large, the network overhead is actually very large, including:

• Database server disk IO
• Network IO from database client to database server

Therefore, paging queries can limit the number of results in a query to prevent large network overhead.

Example 1: limit n returns no more than n rows of results (n represents the maximum number of query results, starting from row 0 by default)

Example 2: limit m , n returns the results starting from row m and not more than n rows.

Example 3: limit n offset m returns the results starting from row m and not more than n rows.

Replenish:

The first row retrieved is row 0, not row 1. The m in Examples 2 and 3 above represents row m or row m+1.

7. Modify the data in the table

grammar:

update table name set column name 1 = value to be modified [, ..., column name n = value to be modified] [where condition] [order by ...] [limit...];
1

Replenish:

• update will actually modify the data on the database server hard disk
• = in update is equivalent to assignment
• If the data result is NULL, the result of the operation based on this is still NULL

8. Delete data from the table

grammar:

delete from table name [where ...] [order by ...] [limit ...]

Notice:

• Deletion needs to be done with caution
• Delete delete from . If the amount of data is large, the deletion speed will be very slow. In comparison, directly deleting the table is faster

This is the end of this article about the basic operations of data tables in MySQL database. For more basic operations of MySQL data tables, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!