A single table query is the scope of a query is just a table, we have one of the fields, or a number of fields to query back, the query can also meet certain conditions.
Below we will follow the actual problem, to learn the single-table query.
First, the original is a student table, which has several fields as follows:
School Number: Snum
Name: sname
Age: Sage
Gender: Ssex
Faculties: Department
Course: Course
Score: Score
CREATE TABLE student (Snum int (5), sname varchar, Sage Int (5), Ssex char (5), Department varchar (TEN), Course varchar (10) , score Int (ten), primary key (Snum));
As follows:
As you can see, the student table contains a total of 10 data, with 7 fields per data. We do the snum as the primary key, so even though my original import data is not sorted by the number of numbers, after I import the database, the database is sorted by the primary key field and is arranged from the 1~10.
All the topics behind us, unless specifically modified databases, are queried by default in this student table.
1. Check all student's number and name
Place the fields that need to be queried after the SELECT clause, which specifies the table scope of the query.
2. Add an arithmetic calculation to the query field
The above is the use of 2015-sage in the query field sage, which calculates the year of birth of all, that is, adding arithmetic formulas to the query field. Furthermore, we find that the corresponding field names have also become "2015-sage".
3. Change the display name of a field
As shown, we only need to add the name that needs to be displayed after the field of the query, Note: The middle is separated by a space.
4. Small Letter Display Data
Select Lower (department) from student;
Because this blog post is based on Chinese data, it is not actually displayed, but the command is not a problem. Note that MySQL is the lower function for lowercase letters conversion, some database system may not be this function, you need to pay attention to.
5. Eliminate duplicate values in a field
You need to use DISTINCT to add this constraint to the front of the field. That is, the front of all fields, the back of select. Because distinct is the function of all the fields behind it. Such as:
What needs to be unique is (snum, department) This pair, apparently none of the data has the same pair, so it returns all. remember: Distinct is scoped to all the fields that follow it.
6. Where to cause query conditions
The Where can be followed by query criteria, including:
7. Where to determine the range of values in the query
We can use sentence patterns such as between and to specify the range of values for comparisons. You can also use in and not to determine whether the collection is in the comparison.
Database Learning Note Series (4)--Single table query _1