|
|
Posted on 3/9/2019 7:46:46 PM
|
|
|
|
This post was last edited by Kongcida on 2019-3-10 00:12
preface
This article mainly talks in detail about the addition, deletion, checking and modification of table data -- insert, delete, select, update.
Get ready
Before we get into this article, let's create a database called DemoDb. Then execute the following code, which contains the table to be used for a while.
The above table has been created, let's enter the text.
Text
First, insert data into the database.
#Insert data
The query results are as follows:
#Modify data
The results are as follows:
The last column of quantity changed from 5 to 13
#Delete data
Knock on the key points, think twice before deleting data. Be sure to bring the conditional expression at the end, otherwise the data of the entire table will be deleted.
In this module, I'll differentiate between drop, truncate, and delete.
*drop: Deletes the entire table, including table data, table space, and table structure. You can't insert data into the table in the future unless you rebuild the table.
*delete: Delete data in a table, you can delete the data in the specified row, and the deleted data will be recorded in the log for later rollback. Therefore, when using this method to delete and then insert new data, the self-added column will continue and will not be reset.
*truncate: Deletes the data in the table to free up table space. You can continue to insert data into the table later. You cannot delete data for a row individually, only all data in the entire table and the count value used to identify the new row is reset to the seed of that column. That is, insert new data, and start over with the count of the columns that were previously added. truncate is equivalent to delete without where condition.
#Query data
In the front, we have been using "select * from table nameThis statement queries the entire table data. In actual projects, we will make more complex queries depending on the business needs. As follows:
Let's create a new table to perform some basic queries, the following is the SQL statement backed up from the database, first create a DemoDb database, and then execute the following statement.
Start with some simple queries:
The above queries can be pasted into the database for verification, and detailed comments are written on them, so I won't go into detail here. If you have any questions, you can leave a message in the comment area, and of course you are welcome to correct any mistakes!
>Result set:
The results of the implementation are as follows:
We can see that there are 4 data queries using union and 7 data queries using union all, three of which are duplicates. How we use these two methods to query depends on the specific business scenario.
>Copy Table:
>Session-level settings:
>dataPagination:
The results are as follows:
>Common Table Expressions:
>View:
>Inline table value function:
>Stored procedures:
The above has listed several ways to paginate.
>case expression:
>Logic Processing and Process Control:
>Common functions:
>Transactions and stored procedures:
The author passed away。。。。。。
|
Previous:Deep learning of dry goods dataNext:Share Python – Artificial Intelligence Topic
|
Posted on 3/10/2019 10:10:15 AM