Database Application details 2

Database Application details 2

Databases help our friends who are currently working a lot. In Big Data Processing, We can query and manage data.

In particular, the current e-commerce industry is selling on the Internet. Therefore, databases are even more attractive.

For example, in the past few years, the company designed a database for us to use. Query and processing of customers, including management of customers under their jurisdiction. It is basically done through the database. Therefore, some basic query, update, delete, and other statements are often used.

I have also prepared relevant materials for you here, and hope you will like them.

 

1. Create two tables

 

2. manually delete a column

 

3. manually add a column

 

4. Add a non-empty column

 

5. Modify columns

Next we will look at the cases related to adding and deleting constraints in the database:

 

6. Add a primary key constraint

 

7. Add a non-empty Constraint

 

8. Add a unique constraint

 

9. Add a default constraint for Gender

 

10. Add a check constraint for Gender

 

11. Add an exam constraint for age

 

12. Add a foreign key constraint

 

13. Added cascade deletion and update

 

14. Constraints for deleting a name

 

15. Delete multiple constraints in one statement

 

Sixteen. add multiple constraints to the table with one statement

Let's take a look at the data retrieval:

 

17. query the student table

 

18. What does this column do?

What is the circle in the figure? For example, I have a mobile phone number in China Mobile and I have canceled it now. So, is my data actually deleted in the database of China Mobile? In many cases, it is not deleted. For users, it is deleted, but China Mobile makes a mark. That is, the user will not log in the next time, but it will not be deleted. 0 and 1 are used in the record. This is generally called "soft Delete ". Generally, 0 indicates that false is not deleted, and 1 indicates that true is deleted.

 

I just want to read three of the columns. For details, see:

 

19. Only view the information of the three columns

It is not very friendly for users. Now I need to display their Chinese information. That is, to give the column an alias. How can I create an alias for a column?

 

XX. Alias

 

21. method 2 and 3 for alias start

 

22. Display custom query results

 

23. Basic Search

 

Twenty-four, top

 

25. Check the top 10 rows with only names

 

26. 10 percent before return

Now there are 11 data records in the database, and 10 percent is returned. How many records are there?

The answer is: 2.

 

27. Sorting

 

Twenty-eight, distinct

If there are three columns, check whether the data in the three columns is repeated. If yes, the duplicates will be removed.

Does it affect table data? There is no impact at all.

 

Let me take a look at the five commonly used Aggregate functions in the database:

When there is something wrong with our work, you can open the help-index and input: aggregate function.

 

19th and five common Aggregate functions

Next we will look at the following conditional query:

 

Thirty. Conditional query 1

 

Use of conditional query 2 in

Next, let's take a look at the fuzzy search

 

, Like Query %

 

33. like query _

 

34. like Query []

^ Is not a regular expression.

 

35. like query ^

Next let's take a look at the processing of Null values in the database.

How can I check the age of all people whose age is not empty?

 

36. null indicates unknow.

The system cannot identify it, for example, 20 is not equal to unknown. Why? The returned result is also an unknow, which is followed by a where clause, so there is no query record.

We cannot use <> and = to compare them. How can we write them?

 

37. null judgment

 

Replacement of null values

If you want to insert a Null value in the designer, you have to write N and Null in upper case. If nothing is written, no, it is a string of 0. If it is written in lower case null, It is a string of null. This is something we need to pay attention.

Next let's look at the order by statement:

It is generally written at the end of the query statement.

 

. Sort by multi-Column Conditions

 

40. sort by expression

Once order by is available, this table is not a set. I will only mention this concept.

 

Next let's take a look at the data grouping:

First, let's raise several questions. When will this group happen? What is grouping?

For example, the Group is based on the nationality group and the group is based on men and women.

Group by is used in data summary and statistics. It is generally used together with Aggregate functions. If Aggregate functions are used without grouping, all the information in the group is regarded as a group for statistics.

If an aggregate function is used for grouping, the information of each group is counted.

 

Forty-one, grouped by class

 

. Query the number of people in each group after grouping

Now we want to see if the data in the group is greater than 3. If we want to filter the group, we use having.

 

Forty-three: use having to filter instances After grouping

 

Forty-four, group judgment should pay attention to the situation

 

Forty-five, situation 2 that needs to be paid attention to when grouping judgment

Which columns can be written in the Select statement? Which Columns cannot be written? Columns and Aggregate functions contained in group by can be written. Others cannot be written.

Why? Think about the problem. After dividing the group, all the information in the group will be involved. Use where to filter data before grouping. Both of them cannot be mixed.

46. Simple SQL statement execution sequence

Which alias can be used to understand the execution sequence of SQL statements? It is also good for understanding SQL statements. Of course, the execution sequence of simple SQL statements is listed here.

 

Forty-seven, Group By Exercises

In my opinion, the type conversion of the function is as follows:

 

Forty-eight, two methods of data type conversion

Under what circumstances will the data type be converted?

 

Forty-nine. Data type conversion I encountered in actual problems

If no conversion is performed, the data is sorted by characters. Instead of sorting two or more numbers.

 

50. Data type conversion encountered in actual problems 2

Use convert for date conversion. 120 is the time type in different databases.

Next let's take a look at the union result set union (set operator)

 

Fifty-one, union

Note that the corresponding data types of each column must be the same. You cannot adjust the position. To enable Union, you must ensure that the number of each column is the same and the data type is the same.

 

When the Union column is empty

The final query result set is named after the first column name.

However, from the perspective of name and gender, there are several identical data records. Union can remove duplicate data. If you do not want to remove the data, add all

 

Fifty-three, union all

So when will union all be used?

Common applications: summary at the bottom.

 

Fifty-four, age Summary

 

Fifty-five, score Summary

Let's take a look at the string functions:

The use of this learning area should be based on the help of Microsoft.

 

56. String Functions

You can see how to use the replace and stuff functions.

Next let's look at the date function:

The date function is still. You can enter the date and time in the help box of the database.

 

. Increase the time at the current time

 

Fifty-eight, calculate the difference between the two time

 

Fifty-nine, datediff Specific Application

 

60. Obtain a part of the time

The following case is very useful for the telemarketing job:

 

. The first talk

Train of Thought: If you see the longest call duration, first calculate the call duration; longest-descending order; First Five-top five

 

12th, the second question about the call duration

 

Session length: Part 3

 

6th, session duration 4

For the last question, I will not announce the answer for the moment, so I will leave it for consideration.

 

 

 

 

 

 

List of recent articles by the author:

C # basic tutorial (free of charge, the best gift for code lovers. Note: The authors share their carefully organized basic C # tutorials without any commercial purposes. I hope to share my experiences with more code lovers. Please give me more advice !!!)
SQL database ADO.net	Database Application diagram 1
Process-oriented, object-oriented, and advanced	Process-oriented, object-oriented in-depth understanding 1
	Process-oriented, object-oriented in-depth understanding 2
	Object-oriented deep understanding 3
Winform Basics	Winform Basics
	Commonly used controls in winform
Process-oriented	Comparison of three cycles
	Method (I) in C)
	Our common Array
Object-oriented	Thought Change
	C # super-class and easy-to-use classes
	The use of destructor and namespaces in C #
	C # super-class and easy-to-use strings
	How to quickly process strings in C #
	Value Type, reference type, and others
	ArrayList and HashTable
	ArrayList and HashTable
	File Management
	Polymorphism
	Section on other issues in C #
GDI +	The GDI + code I have collected over the years
	The GDI + Code 2 I collected over the years
HTML Overview	HTML language that you cannot ignore
	You can't ignore HTML 2.
	HTML language 3 that you cannot ignore