All about Mysql

MYSQL

• mysql -u root -p
• Show database
• Use database

What determines the sequence of operations?

• Order of precedence
• Parentheses

Terms to know

• function
• parameter
• argument
• concatenate

Dont’t equal zero

• where credit_total <> 0
• where credit_total != 0

The order of precedence for compound conditions

• NOT
• AND
• OR

The default sequence for an ascending sort

• null values
• special characters
• numbers
• letters

note

• null values appear first in the sort sequence, even if you are using desc

Don’t count first two

why do we use join?

• same columns in two different tables

To use another database(scheme)

Using self join to get common

We can join N-1 tables to one table

Also we can implicitly join tables not using join

Terms to know about inner join

1. join
2. join condition
3. inner join
4. ad hoc relationship
5. qualified column name
6. table alias
7. schema
8. self-join
9. explicit syntax
10. implicit syntax

Outer join retrieve unmatched data as well as matched data from left or right

Combine join

Simpler join using

What is the meaning of natural here?

• Based on common field for example vendor ID

Cross join

• join two table with all possible combinations
• If we have 5 rows in A, 9 rows in B then we will get 45 rows
• It is basically same as select * from A, B

Terms to know about other types of join

1. Outer join
2. Left outer join
3. Right outer join
4. Equijoin
5. Natural join
6. Cross join
7. Cartesian product

Rules for a union

1. each result set must return the same number of columns
2. the corresponding columns in each result set must have compatible data types
3. the column names in the final result set are taken from the first select clause

If you want to use full outer join, you have to use union