MySQL Basics

MySQL Tutorial

When MySQL is first installed it has a pre-set root password, "sewanee"
This is obviously not good so each of you need to select a BETTER password that you can remember and change it on your selected server (ie, the machine where you are logged in)
```
                                      |--- tells mysql to prompt for password
                                      v 
                   mysqladmin -u root -p password new_password 
                                          ^ 
                                          |--- keyword ensures encyption!

                   
```
Ok, let's try it...
Pay Attention:
- After you hit return you'll be prompted for a password. THIS IS THE EXISTING PASSWORD (sewanee); not the new one.
- Why?
  You have to prove you know the current password ("sewanee") before the admin program will go ahead and make the change to your super secret new red password in the above.
- Also, the cursor won't move as you type in the passwords. Again, don't worry. It's working. Just type in what you need to and hit return.
Here's EXACTLY what it would look like if I wanted to change my password from "sewanee" to "i56890rgdhht2"
```
ldale@farheen:~$ mysqladmin -u root -p password i56890rgdhht2
Enter password: 
```
If all went well, you just get the terminal prompt back that you started with. That's a good thing.
And finally finally...what about that message you got back?
```
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
```
We definitely don't want ot be "insecure"!!!
history
history -c
history

AT LAST, Login with

                   mysql -u root -p

after you hit return you'll be prompted for the password. Use your NEW one, this time.

Again, here's what it looked like for me this morning using my spiffy NEW password.
Again, the cursor will not move as you type. Just keep typing and hit return!

ldale@farheen:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 5.6.19-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you get the mysql> prompt, all has gone well!

If changes don't seem to take effect, sometimes a reload command going thru mysqladmin is required. Type the following and hit return. Then try to login again as above.

                   mysqladmin -u root -p reload

How does mysql work? .... (Answer: always with tables!)

                   show databases;
                   use mysql
                   show tables;
                   select * from user;

```
CREATE DATABASE aliens;
            
```
```
USE aliens;
            
```
You can cut-n-paste or download the content of these files into your directory.

To "run" them, login into mysql, use the proper database. Then type "source create.sql" and hit return. Do the same with the others.

Let's look at

create.sql

pop.sql (annotated version)

drop.sql

Or delete a whole database with

            drop database aliens;

Some commands to see what's going on...
- SHOW DATABASES;
- SHOW TABLES;
- DESCRIBE tablename;
- SELECT * FROM abductee;
- SELECT * FROM abductee\G
- DELETE FROM abductee where lname='Dale';
Some important points to consider...
- MySQL is *not* case-sensitive with respect to commands
```
  SELECT, select, SeLeCt
```
  all mean the same thing!
- MySQL is OFTEN case-sensitive with respect to table & attribute names etc.
  (It depends on the Operating System....Linux & OSX usually yes, Windows no)
  So for us, in the Linux Lab, ASSUME CASE-SENSITIVE
```
  SELECT   *   FROM   abductee;
  SELECT   *   FROM   AbduCTEE;
```
  are NOT AT ALL the same thing!

more select commands...

Attributes (cols) can be listed in whatever order you like

SELECT lname,fname 
        FROM abductee;

SELECT fname,lname 
        FROM abductee;

with column headers you choose

SELECT lname AS 'Last Name',fname 
        FROM abductee;

limited to rows satisfying various criteria

SELECT fname, numTrips
        FROM abductee 
        WHERE numTrips > 30;

SELECT fname 
        FROM abductee 
        WHERE 20 < numTrips AND numTrips < 50;

Note. A select should have only one(uno,1) WHERE clause. If you have more than one condition you must build the appropriate BOOLEAN EXPRESSION.

              20 < numTrips    AND   numTrips < 50

Notice also the "wordiness" of boolean expressions in SQL. Most of the logical operators are "overloaded" with both the usual symbol(s) as well as the relevant English word(s).
```
              20 < numTrips    &&   numTrips < 50
                     
```

remove rows...

DELETE FROM abductee 
       WHERE lName="Dale";

Exit MySQL with "exit" and return

Pay Attention: