Today, let's learn the record search function using my_smart_devices created last time.
The select statement we used so far is to retrieve records.
There are four fields in my_smart_devices: num, name, thenumberofcpu, and company.
Type select * from my_smart_devices to display all fields selected.
To select only certain fields, enter:
For example, if you select only the name field
SELECT name FROM my_smart_devices;
You can enter
If you want to search the whole record like that, select * from table name to search;
Select field name from table name;
You can do this.
If you want to skip duplicate values and search for them, use DISTINCT as follows:
SELECT DISTINCT field name FROM table name;
You can type this
First of all, there is no data in the my_smart_devices table, so let's enter it.
Run the following three statements.
INSERT INTO my_smart_devices(name, thenumberofcpu, company) VALUES('galaxy nexus','2','samsung');
INSERT INTO my_smart_devices(name, thenumberofcpu, company) VALUES('galaxy tab','1','samsung');
INSERT INTO my_smart_devices(name, thenumberofcpu, company) VALUES('iPad2','2','apple');
Now let's check the data you entered.
SELECT * FROM my_smart_devices;
I have a table as above. So let's just load the name field.
Enter the following statement
SELECT name FROM my_smart_devices;
Then only the name field and its records are displayed as shown below.
So if you want to get rid of the duplicated values and search for them, the duplicated values are in the number of you.
Once again, if you want to search for duplicate values, use distinct as follows:
SELECT DISTINCT field name FROM table name;
SELECT DISTINCT thenumberofcpu FROM my_smart_devices;
Then let's search only the values that meet the criteria. The statement is as follows:
SELECT field name1, field name2, field name3... FROM table name where condition;
The where statement is used to grant conditions. So let's look for a two core product in the table my_smart_devices.
SELECT * FROM my_smart_devices where thenumberofcpu = '2';
Just type the statement above. = Means find the same thing.
If you only want to see specific fields for a product with two cores, for example name and company, you can enter:
SELECT name company FROM my_smart_devices where thenumberofcpu = '2';
You can also sort the searched fields in ascending and descending order.
Ascending order is ASC descending order is DESC.
To sort them, type the following:
SELECT field name1, field name2, field name3... FROM table name order by ield name ascending order ( ASC ) or descending order ( DESC );
And ascending order can be omitted. The default is ASC. Then let's experience the descending order in ascending order directly with num !!
SELECT * FROM my_smart_devices ORDER BY num DESC;
Next, let's search for products with two cores in descending num order.
SELECT * FROM my_smart_devices WHERE thenumberofcpu = '2' ORDER BY num DESC;
You may not know well because there are few records, but it is useful when you have a large database.
Now let's do this. Let's search for records with the numberofcpu descending in ascending order..
SELECT * FROM my_smart_devices WHERE thenumberofcpu > 1 ORDER BY num DESC;
You can give conditions as shown in the command. thenumberofcpu > 1
You can give a condition that is greater than 1.
So let's try to satisfy two conditions. Let's look for Samsung products with more than one core.
SELECT * FROM my_smart_devices WHERE thenumberofcpu >1 AND company LIKE '%samsung%';
When searching for a string, write the field name, like, and set the value of the field to '% string%'.
company = samsung
Then let's see only a few people from above. How do we do this?
Use LIMIT.
For example, if you want to print only 50 people with high grades.
SELECT * FROM table name ORDER BY field name ASC or DESC LIMIT numeric;
So let's do it! In ascending order of num to show only the second in the table.
SELECT * FROM my_smart_devices ORDER BY num ASC LIMIT 2;
Next time, let's learn about the aggregation function!