CPSC340A
Database Systems

Log Into MySQL

• Connecting to and Disconnecting from the Server

• First ssh to cs

  $ ssh username@cs.roanoke.edu

• Don’t need to specify the host because of ssh to cs

• Don’t specify the password after the p option or it will be in plain text

  $ mysql -u username -p
  Enter password: default_password

• Change password

  > SET PASSWORD = PASSWORD('newpasswordgoeshere');

• To exit

  > QUIT

• Note the capitalization is style convention
• Text that is not capitalized is often case senstive
• Most of the time you need a semicolon at the end of a statement (QUIT does not)
• If you add it to a statement that doesn’t need it, will work fine
• If you forget the semicolon, enter will produce a line break
• Just add the semicolon after the line break
• This allows you to write statements that are multiple lines long

• The statement isn’t executed until it encounters the semicolon

Select Databases

• Creating and Selecting a Database
• You can have multiple databases
• You do not have permission to create or destroy databases

• To see your databases use SHOW command

  > SHOW DATABASES;

• I created one for you that is username_db

• Before interacting with a database must inform mysql which database

  > USE username_db

• It’s easy to forget this step on subsiquent logins.

• If you get the error

  ERROR 1046 (3D000): No database selected

• It’s because you forgot to specify the database before issuing commands

Create Table

• Creating a Table

• Get a list of the existing tables

  > SHOW TABLES;

• Need to add some tables
• A table is a grid of information, think spreadsheet

• Each column in the table has a name and a type

  > CREATE TABLE TableName (an_int INT, some_text TEXT);

• By convention table name uses title case and columns use lower case

• To verify it was created Use show tables

  > SHOW TABLES;

• To get a description of a table use DESCRIBE

  > DESCRIBE TableName

Add Data

• Loading Data into a Table

• Use INSERT to add a row of data

  > INSERT INTO TableName VALUES (1, 'one');

• All entries in a row must be specified

• If you want to specify just some entries can specify column names

  > INSERT INTO TableName (an_int, some_text) VALUES (2, 'two');

• If you don’t specify a value, a NULL is added

• Can also explicity add NULL

  > INSERT INTO TableName (an_int, some_text) values (0, NULL);

• [Data Types](https://dev.mysql.com/doc/refman/5.7/en/data-types.html](Data Types)

View Table

• Selecting All Data

• To see what is in the table use the SELECT statement

  > SELECT * FROM TableName;

Delete Table

• DROP TABLE Syntax

• Use the DROP TABLE command to remove a table and all the data in it

  > DROP TABLE TableName;

• Be careful, this can’t be undone

• Exploits of a Mom

Create Script

• Using mysql in Batch Mode

• You can put multiple statements in a text file and execute the file

  > source script.sql;

• Can be any text file, the .sql extension is convention

• Can also run a script from the shell

  $ msql -u username -p < script.sql
  Enter password: ********

• This is nice if you want to redirect to a file or a viewing program

  $ msql -u username -p < script.sql > output.txt
  $ msql -u username -p < script.sql | less

• Re-entering the password can get annoying, look into SSH keys