MYSQL/MARIADB Learning Record--Connection query

Connection queries: Design query join conditions or join predicates for two or more tables: The conditional general format used to connect two tables:

[< table name 1>]< column name 1> < comparison operator > [< table name 2>]< column name 2>

[< table name 1>]< column name 1> between [< table name 2>]< column name 2> and [< table name 2>]< column name 3>

Equivalent connections:

The join operator is =

Find out 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 |   Women | 19 | is | 95002 |    2 | 90 | | 95002 | Liu Chen |   Women | 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 June |   Male | 30 | is | 96001 |    1 | 87 | | 96001 | Liu June |   Male | 30 | is | 96001 |    2 | 80 | | 96001 | Liu June | Man  | 30 | is | 96001 |    3 | 90 | | 96001 | Liu June |   Male | 30 | is | 96001 |    4 | 95 | | 96001 | Liu June |   Male | 30 | is | 96001 |  5 | NULL | | 96001 | Liu June |   Male | 30 | is | 96001 |  6 | NULL | | 96001 | Liu June |   Male | 30 | is | 96001 |    7 | 86 | | 97001 | John Doe |   Male | 26 | EN | 97001 |  4 | NULL | | 97001 | John Doe |   Male | 26 | EN | 97001 |  5 | NULL |+-------+--------+------+------+-------+-------+-----+-------+

Sort:

SELECT [] From < table name > ORDER by < column name > [DESC/ASC];

Mysql>SelectSno,sname fromStudent order by Sno;//Default to Ascending+-------+--------+| Sno | Sname |+-------+--------+|12001| BGG | |94001| Cottage | |95001| Li Yong | |95002| Liu Chen | |95003| Wang min | |95004| Zhang Li | |95005| Zhang San | |96001| Liu June | |96004| Furong | |97001| John Doe |+-------+--------+MySQL>SelectSno,sname fromStudent ORDER by SNO ASC;//ASC modifier to ascending+-------+--------+| Sno | Sname |+-------+--------+|12001| BGG | |94001| Cottage | |95001| Li Yong | |95002| Liu Chen | |95003| Wang min | |95004| Zhang Li | |95005| Zhang San | |96001| Liu June | |96004| Furong | |97001| John Doe |+-------+--------+TenRowsinch Set(0.05sec) MySQL>SelectSno,sname fromStudent ORDER BY Sno Desc;//desc modifier to descending order+-------+--------+| Sno | Sname |+-------+--------+|97001| John Doe | |96004| Furong | |96001| Liu June | |95005| Zhang San | |95004| Zhang Li | |95003| Wang min | |95002| Liu Chen | |95001| Li Yong | |94001| Cottage | |12001| BGG |+-------+--------+

Mysql> Select Sno,sname from Student ORDER by Sno Desc,sname ASC; First press sno Descending and then sname ascending
+-------+--------+
| Sno | sname |
+-------+--------+
| 97001 | John Doe |
| 96004 | Hibiscus |
| 96001 | Liu June |
| 95005 | Zhang San |
| 95004 | Zhang Li |
| 95003 | Wang Min |
| 95002 | Liu Chen |
| 95001 | Li Yong |
| 94001 | Cottage |
| 12001 | BGG |
+-------+--------+
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* fromStudentwhereSno between'94001'and'96001';+-------+--------+------+------+-------+| Sno | sname | Ssex | Sage | Sdept |+-------+--------+------+------+-------+|94001| Cottage | Male | in| CS | |95001| Li Yong | Male | -| CS | |95002| Liu Chen | Women | +| is | |95003| Wang Min | Women | +| MA | |95004| Zhang Li | Male | -| is | |95005| Zhang San | Male | at| CS | |96001| Liu June | Male | -| 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 | Hibiscus | Women | 32 | CH |
| 97001 | John Doe | Male | 26 | EN |
+-------+--------+------+------+-------+

MySQL Fuzzy query:

% replaces one or more characters;

_ replaces only one character;

[charlist] Any single character of the word columns;

[!charlist] or [^charlist] any single character not in Word columns;

//Query Sno Student Information starting with 95Mysql>Select* fromStudentwhereSno Like'95%';+-------+--------+------+------+-------+| Sno | sname | Ssex | Sage | Sdept |+-------+--------+------+------+-------+|95001| Li Yong | Male | -| CS | |95002| Liu Chen | Women | +| is | |95003| Wang Min | Women | +| MA | |95004| Zhang Li | Male | -| is | |95005| Zhang San | Male | at| CS |+-------+--------+------+------+-------+5Rowsinch Set(0.05sec)//Query Sno Student information ending with 01Mysql>Select* fromStudentwhereSno Like'%01';+-------+--------+------+------+-------+| Sno | sname | Ssex | Sage | Sdept |+-------+--------+------+------+-------+|12001| BGG | M | -| CS | |94001| Cottage | Male | in| CS | |95001| Li Yong | Male | -| CS | |96001| Liu June | Male | -| is | |97001| John Doe | Male | -| EN |+-------+--------+------+------+-------+5Rowsinch Set(0.05Sec

2018-05-01 20:24:33

MYSQL/MARIADB Learning Record--Connection query