CPSC340A - Day 1 Notes

Info About Database

Information About Databases and Tables
Get a list of the databases with SHOW DATABASES
Get the database currently being used with SELECT DATABASE()
Get tables in the current database with SHOW TABLES
Get description of database with DESCRIBE TableName
This is useful to get name and type of columns

Select

SQL queries are statements that read information from a database
We previously used Select * to get all of the data in a table
```
SELECT * FROM TableName;
```
If you are familiar with the Linux command line, the * asterisk should give you a hint that we can be more specific
Asterisk is wild card, which matches any text
Can specify certain columns instead of all columns
```
SELECT an_int FROM TableName;
```

Can specify multiple columns

SELECT an_int, some_text FROM TableName;

Whatever order you specify the column names in is the order they will appear in the result
```
SELECT some_text, an_int FROM TableName;
```

Distinct

By default SELECT retrieves all rows
It implicitly sets the all modifier

The following are equivalent

SELECT ALL an_int FROM TableName;
SELECT an_int FROM TableName;

Can remove duplicate rows from the retrieved rows
```
SELECT DISTINCT an_int FROM TableName;
```
Note this is distinct in the retrieved rows, not in the distinct in the table

For example, for the table

+--------+-----------+
| an_int | some_text |
+--------+-----------+
|      1 | one       |
|      1 | uno       |
|      1 | un        |
|      2 | two       |
|      2 | dos       |
|      2 | deux      |
+--------+-----------+

The query
```
SELECT DISTINCT an_int FROM TableName;
```

Will retrieve the rows

+--------+
| an_int |
+--------+
|      1 |
|      2 |
+--------+

Because there are only two distinct rows

While the query

SELECT DISTINCT an_int, some_text FROM TableName;

Will retrieve all of the rows

+--------+-----------+
| an_int | some_text |
+--------+-----------+
|      1 | one       |
|      1 | uno       |
|      1 | un        |
|      2 | two       |
|      2 | dos       |
|      2 | deux      |
+--------+-----------+

It’s also worth noting why this is useful
You could just do the query and write your own code that filters the result to what you want
Then you don’t have to know SQL, just do one query and transform the data in a language you know
What’s wrong with this approach

Order

Can also specify the order that the rows that are retrieved
```
SELECT an_int FROM TableName ORDER BY an_int;
```

This also works when there is more than one column

SELECT an_int, some_text FROM TableName ORDER BY an_int;

This even works when order column is not in the retrieved rows
```
SELECT some_text FROM TableName ORDER BY an_int;
```

All types are orderable

SELECT some_text FROM TableName ORDER BY some_text;

Can also specify how to resolve the order of rows with the same value in the ordering column by specify another column
```
SELECT some_text FROM TableName ORDER BY an_int, some_text;
```
By default it orders in ascending order

Can explicitly specify ascending

SELECT * FROM TableName ORDER BY an_int ASC;

Or can specify descending

SELECT * FROM TableName ORDER BY an_int DESC;

Note that you can specify the whether each of the columns used for sorting is ascending or descending
```
SELECT * FROM TableName ORDER BY an_int ASC, some_text DESC;
```

Comment

Can use #, --, or /* */ comments

# this line will be ignored
-- this line will also be ignored
/* all of
   these lines
   will be
   ignored */