Wildcard characters in a select
Asterisks *
can be used to select
represent all the attributes in a clause.
Mysql>SelectInstructor.* fromInstructor, teacheswhereInstructor.id = teaches.id;+-------+------------+------------+----------+|ID| name | Dept_name | Salary |+-------+------------+------------+----------+|10101| Srinivasan | Comp. Sci. |65000.00||10101| Srinivasan | Comp. Sci. |65000.00||10101| Srinivasan | Comp. Sci. |65000.00||12121| Wu | Finance |90000.00||15151| Mozart | Music |40000.00||22222| Einstein | Physics |95000.00||32343| El said | History |60000.00||45565| Katz | Comp. Sci. |75000.00||45565| Katz | Comp. Sci. |75000.00||76766| Crick | Biology |72000.00||76766| Crick | Biology |72000.00||83821| Brandt | Comp. Sci. |92000.00||83821| Brandt | Comp. Sci. |92000.00||83821| Brandt | Comp. Sci. |92000.00||98345| Kim | Elec Eng. |80000.00|+-------+------------+------------+----------+ the rows inch Set(0.01Sec
It returns all the attributes in the instructor.
Ordering of tuples
SQL provides the user with some control over the order in which the results are displayed, and the order by
clauses allow the tuples in the query results to be displayed ascending, considering that all teachers in the Physics system are serialized out by dictionary.
select name from instructor where‘Physics‘ orderby name;+----------+| name |+----------+| Einstein || Gold |+----------+2rowsinset (0.00 sec)
In order to use descending, we can express in descending order, desc
completely, can be used to asc
express ascending, in addition, the sort can be done on multiple attributes, for example, we want to salary the whole instructor relationship, if there are several teachers pay the same, then by name in ascending order.
Mysql>Select*- fromInstructorOrder bySalarydesc, nameASC;+-------+------------+------------+----------+|ID| name | Dept_name | Salary |+-------+------------+------------+----------+|22222| Einstein | Physics |95000.00||83821| Brandt | Comp. Sci. |92000.00||12121| Wu | Finance |90000.00||33456| Gold | Physics |87000.00||98345| Kim | Elec Eng. |80000.00||76543| Singh | Finance |80000.00||45565| Katz | Comp. Sci. |75000.00||76766| Crick | Biology |72000.00||10101| Srinivasan | Comp. Sci. |65000.00||58583| Califieri | History |62000.00||32343| El said | History |60000.00||15151| Mozart | Music |40000.00|+-------+------------+------------+----------+ A rows inch Set(0.00Sec
WHERE clause predicate
To simplify where
clauses, SQL provides between
operators to show that a value falls within a closed interval.
Consider the query to find out the names of teachers between $90000 and $100000, intuitively having
select name from instructor where100000and90000;+----------+| name |+----------+| Wu || Einstein || Brandt |+----------+3rowsinset (0.01 sec)
You can use between
the operator to override the
select name from instructor wherebetween90000and100000;+----------+| name |+----------+| Wu || Einstein || Brandt |+----------+3rowsinset (0.01 sec)
Similar to have not between
operators
select name from instructor wherenotbetween90000and100000;+------------+| name |+------------+| Srinivasan || Mozart || El Said || Gold || Katz || Califieri || Singh || Crick || Kim |+------------+9rowsinset (0.00 sec)
where
Clauses also support comparisons on tuples, consider queries, find biology the names of all the teachers who taught the course and the courses they taught
select name, course_id from instructor, teaches where‘Biology‘);+-------+-----------+| name | course_id |+-------+-----------+| Crick | BIO-101 || Crick | BIO-301 |+-------+-----------+2rowsinset (0.00 sec)
MySQL-based database practice (extended operation)