Just Enough (I hope) SQL

• SQL is the Structured Query Language
  • A special purpose language.
  • There is a standard
  • Unfortunately, it is not completely followed.
  • IE there are many dialects of SQL
• We will be interacting with MariaDB, which is a derivative of MySQL
  • A general google for mariadb (or mysql) and the construct will work.
  • Look for mariadb show
  • Ok, way too much, try mariadb show database
• Download Putty (I hope we can skip this step)
  • Go here.
  • Download putty
  • Double click and install.
• Start putty
  • Change configurations if you wish (larger fonts)
  • Connect to cslab100.cs.edinboro.edu
• Interacting with the database
  • There are many better clients for connecting with the database.
  • mysql is a simple text based one.
  • Others require more installation support.
  • We will use this to play.
• run mysql -u dsci -p -h mirkwood.cs.edinboro.edu
  • We are logging in as user dsci
  • We want to give the password (later)
  • We want to connect to the mysql server on mirkwood.cs.edinboro.edu
  • 
• This is all
  • text based.
  • SQL, in MySQL or MariaDB dialect.
• Everything ends with a semicolon;
  • If you forget, MySQL will not go on until you give one.
  • 
• SHOW DATABSES;
  • Like most languages SQL contains reserved words.
  • You normally see these in caps, but it is not required.
  • Try it.
  • By the way, depending on the user, you will see different databases.
• help SHOW
  • I suspect is a mysql interface help.
  • help show databases
• USE databaseName;
  • Let's use IrisDB
  • USE IrisDB;
• SHOW TABLES;
  • Notice, the result is returned in a box with a column label.
  • This is equivalent to a table.
• DESCRIBE tableName
  • DESCRIBE Iris;
  • DESCRIBE PokemonDB.CardTable;
• SHOW CREATE TABLE tableName
  • SHOW CREATE TABLE Iris;
  • Note, this is the SQL command to create this table.
  • It gives us some idea of the table structure.
• The big one select
  • SELECT * FROM table;
  • SELECT * FROM Iris;
  • help select;
  • [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    • SELECT * FROM Iris LIMIT 5;
    • SELECT * FROM Iris Limit 10,5
    • SELECT * FROM Iris LIMIT 10 Offset 12;
  • SELECT field_expr FROM table
    • See this page.
    • SELECT Species FROM Iris;
    • SELECT DISTINCT Species FROM Iris;
    • SELECT DISTINCT Species, PetalWidthCM from Iris;
  • Select field_expr FROM TABLE WHERE expression;
    • SELECT Species, SepalLengthCM from Iris;
    • SELECT Species, SepalLengthCM from Iris WHERE SepalLengthCM > 7;
    • SELECT Species, SepalLengthCM from Iris WHERE SepalLengthCM > 7 or SepalLengthCM < 5;
    • SELECT Species, SepalLengthCM from Iris WHERE (SepalLengthCM > 7 or SepalLengthCM < 5) and Species = "Iris-virginica";