mysql/mariadb learning record -- connection query

Join query: Design queries for two or more tables at the same time

Join condition or join predicate: general form of condition used to join two tables:

[< table name1 >] < column name1 > < comparison operator > [< table Name2 >] < column Name2 >

[< table name1 >] < column name1 > between [< table Name2 >] < column Name2 > and [< table Name2 >] < column name3 >

Equivalent connection:

The join operator is=

Inquire about each student and elective course

mysql> select student.*, sc.* from student,sc where student.sno=sc.sno;
+-------+--------+------+------+-------+-------+-----+-------+
| sno   | sname  | ssex | sage | sdept | sno   | cno | grade |
+-------+--------+------+------+-------+-------+-----+-------+
| 95001 | Li Yong    | male   |   20 | CS    | 95001 | 1   |    92 |
| 95001 | Li Yong    | male   |   20 | CS    | 95001 | 2   |    85 |
| 95001 | Li Yong    | male   |   20 | CS    | 95001 | 3   |    88 |
| 95002 | Liu Chen    | female   |   19 | IS    | 95002 | 2   |    90 |
| 95002 | Liu Chen    | female   |   19 | IS    | 95002 | 3   |    80 |
| 95004 | Zhang Li    | male   |   20 | IS    | 95004 | 2   |    65 |
| 95004 | Zhang Li    | male   |   20 | IS    | 95004 | 3   |  NULL |
| 95004 | Zhang Li    | male   |   20 | IS    | 95004 | 4   |  NULL |
| 95005 | Zhang San    | male   |   23 | CS    | 95005 | 2   |    84 |
| 95005 | Zhang San    | male   |   23 | CS    | 95005 | 4   |  NULL |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 1   |    87 |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 2   |    80 |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 3   |    90 |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 4   |    95 |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 5   |  NULL |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 6   |  NULL |
| 96001 | Liu Jun    | male   |   30 | IS    | 96001 | 7   |    86 |
| 97001 | Li Si    | male   |   26 | EN    | 97001 | 4   |  NULL |
| 97001 | Li Si    | male   |   26 | EN    | 97001 | 5   |  NULL |
+-------+--------+------+------+-------+-------+-----+-------+

Sort:

Select [] from < table name > order by < column name > [desc / ASC];

mysql> select sno,sname from student order by sno; //Default is ascending
+-------+--------+
| sno   | sname  |
+-------+--------+
| 12001 | bgg    |
| 94001 | Copycat   |
| 95001 | Li Yong   |
| 95002 | Liu Chen   |
| 95003 | Wang min.   |
| 95004 | Zhang Li   |
| 95005 | Zhang San   |
| 96001 | Liu Jun   |
| 96004 | Lotus   |
| 97001 | Li Si   |
+-------+--------+

mysql> select sno,sname from student order by sno asc; //asc Decorated in ascending order
+-------+--------+
| sno   | sname  |
+-------+--------+
| 12001 | bgg    |
| 94001 | Copycat   |
| 95001 | Li Yong   |
| 95002 | Liu Chen   |
| 95003 | Wang min.   |
| 95004 | Zhang Li   |
| 95005 | Zhang San   |
| 96001 | Liu Jun   |
| 96004 | Lotus   |
| 97001 | Li Si   |
+-------+--------+
10 rows in set (0.05 sec)

mysql> select sno,sname from student order by sno desc; //desc Decorated in descending order
+-------+--------+
| sno   | sname  |
+-------+--------+
| 97001 | Li Si   |
| 96004 | Lotus   |
| 96001 | Liu Jun   |
| 95005 | Zhang San   |
| 95004 | Zhang Li   |
| 95003 | Wang min.   |
| 95002 | Liu Chen   |
| 95001 | Li Yong   |
| 94001 | Copycat   |
| 12001 | bgg    |
+-------+--------+

mysql> select sno,sname from student order by sno desc,sname asc; //First in descending sno and then in ascending sname
+-------+--------+
| sno   | sname  |
+-------+--------+
| 97001 | Li Si    |
| 96004 | Lotus    |
| 96001 | Liu Jun    |
| 95005 | Zhang San    |
| 95004 | Zhang Li    |
| 95003 | Wang min.    |
| 95002 | Liu Chen    |
| 95001 | Li Yong    |
| 94001 | Copycat    |
| 12001 | bgg    |
+-------+--------+
10 rows in set (0.05 sec)

 

Selected between operation (closed interval in mysql/mariadb):

Select * from < table name > where < column name > between 'a' and 'B';

mysql> select * from student where sno between '94001' and '96001';
+-------+--------+------+------+-------+
| sno   | sname  | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 94001 | Copycat   | male   |   29 | CS    |
| 95001 | Li Yong   | male   |   20 | CS    |
| 95002 | Liu Chen   | female   |   19 | IS    |
| 95003 | Wang min.   | female   |   19 | MA    |
| 95004 | Zhang Li   | male   |   20 | IS    |
| 95005 | Zhang San   | male   |   23 | CS    |
| 96001 | Liu Jun   | male   |   30 | IS    |
+-------+--------+------+------+-------+

//not between and

mysql> select * from student where sno not between '94001' and '96001';
+-------+--------+------+------+-------+
| sno   | sname  | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 12001 | bgg    | M    | 26   | CS    |
| 96004 | Lotus    | female   | 32   | CH    |
| 97001 | Li Si    | male   | 26   | EN    |
+-------+--------+------+------+-------+

mysql fuzzy query:

%Replace one or more characters;

_Replace only one character;

Any single character in the [charlist] character column;

Any single character where [! Charlist] or [^ charlist] is not in the character column

//query sno Student information starting with 95
mysql> select * from student where sno like '95%';
+-------+--------+------+------+-------+
| sno   | sname  | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 95001 | Li Yong   | male   |   20 | CS    |
| 95002 | Liu Chen   | female   |   19 | IS    |
| 95003 | Wang min.   | female   |   19 | MA    |
| 95004 | Zhang Li   | male   |   20 | IS    |
| 95005 | Zhang San   | male   |   23 | CS    |
+-------+--------+------+------+-------+
5 rows in set (0.05 sec)

//query sno Student information ending with 01
mysql> select * from student where sno like '%01';
+-------+--------+------+------+-------+
| sno   | sname  | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 12001 | bgg    | M    |   26 | CS    |
| 94001 | Copycat   | male   |   29 | CS    |
| 95001 | Li Yong   | male   |   20 | CS    |
| 96001 | Liu Jun   | male   |   30 | IS    |
| 97001 | Li Si   | male   |   26 | EN    |
+-------+--------+------+------+-------+
5 rows in set (0.05 sec)

2018-05-01  20:24:33

Tags: MySQL MariaDB

Posted on Fri, 27 Mar 2020 08:13:11 -0700 by gerkintrigg