search record

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!