MySQL-based database practice (extended operation)

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)

whereClauses 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)