Roadrunner loginPersonalize this document by keying in your
database: LOGIN username: LOGIN password: LOGINThis database account is completely separate from your roadrunner user account even though the information has been made similar so as to avoid account information overload.
- Log in to roadrunner.cs.wcupa.edu account via a secure shell client.
- Invoke the client.
You get no feedback for the characters typed as your password!
$ mysql -p -u LOGIN LOGIN Enter password: LOGIN
- At this point, you have an SQL command-line interpreter with this prompt:
mysql>The first thing to know is how to get back out. Do so by doing:
Database Access MethodsSo far we have this way to access your database:
$ mysql -p -u LOGIN LOGINIt turns out that the "-u" parameter is unnecessary. By default, mysql will use your shell account user name as the database account user name. This means you can access your account simply by doing:
$ mysql -p LOGIN Password: LOGIN mysql>Although considered somewhat insecure in general, the password can be provided on the command line like this:
$ mysql -pLOGIN LOGIN mysql>Note that there can be no space between the "-p" and the password. One more way to access your account: access the MySQL system without specifying the database, and then specify the database through the use command:
$ mysql -pLOGIN mysql> use LOGIN
Password ChangeI do not recommend doing so initially, but here is how to change your database account password:
mysql> set password = password('some-new-password');
SQL command entryThe mysql client accepts SQL (Structured Query Language) commands terminated by a semicolon like this:
mysql> some sql command;The command can be continued across multiple lines. Whenever you type the terminating semicolon, the command is presumed to be complete:
mysql> some sql command -> across multiple lines;If you get stuck after mistyping something, just type the semicolon, generating an syntax error and try again.
Being a relational database means that the information is grouped into relations, or more simply tables. Each table row is an ordered collection of data values as you might think. The access of the data values in a single row is determined by the field name. For example, this might be considered a very simple table keeping track of thing, where each thing is some item and the quantity thereof:
item: varchar(20) (a variable number of characters, up to 20) qty: int (an integer, representing the quantity of the thing)We can be far more efficient about the size of the qty field, if we know that quantities are going to be small; nevertheless, we'll ignore that concern.
SQL Data Types
|CHAR(N)||N||fixed length string up to 255 bytes|
|VARCHAR(N)||string length + (1 or 2)||variable length string up to ≈ 65,000 bytes|
|TINYTEXT||string length + 1||string up to ≈ 255 bytes|
|TEXT||string length + 2||string up to 65,000 types|
|MEDIUMTEXT||string length + 3||string up to ≈ 16 million bytes|
|LONGTEXT||string length + 4||string up to ≈ 4.2 billion bytes|
|TINYINT||1||0 to 255 or -127 to 128|
|SMALLINT||2||0 to ≈ 65,000 (or neg/pos split)|
|MEDIUMINT||3||0 to ≈ 16 million (or neg/pos split)|
|INT||4||0 to ≈ 4 billion (or neg/pos split)|
|BIGINT||8||0 to ≈ 18*1018 (or neg/pos split)|
|REAL (DOUBLE)||8||16-digit precision|
|DECIMAL(N,D)||length + (1 or 2)||arbitrary precision|
|DATETIME||8||DATE and TIME combined|
|TIMESTAMP||4||# seconds since 1970|
|BINARY, VARBINARY||like "*CHAR" types, but binary|
|TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB||like "*TEXT" types, but binary|
|ENUM('value1','value2',...)||1 or 2||column holds one of the specific string values|
|SET('value1','value2',...)||1, 2, 3, 4 or 8||column holds one or more of the specific string values|
MySQL operationsThe operations we want to be able to do fall into two general categories:
- SQL operations that create/modify our tables in some way
- SQL operations that retrieve information from our tables without changing them
- MySQL-specific operations that manipulate information about the DBMS itself, rather than information within the databases.
create table ... create a table drop table ... remove a table insert into table ... add a new row replace into table ... replace a row by another update table ... change the contents of one or more rows delete from table ... remove one or more rowsThe last three of these are separated from insert by virtue of the fact that they modify existing rows within a table. When these are used, one typically qualifies the affected rows by adding a where clause:
replace/update/delete ... where ....On the right-hand side of the where keyword is a specification that identifies one or more rows which are to be affected by the operation. Regarding the operations of group II, there is effectively only one: the select operation. The syntax is like this:
select field-specification from table where where-specificationThe select operation retrieves information from the rows identified by the where-specification. The field-specification indicates which fields from those rows to select and/or how to modify them.
MySQL learning examplesIt is best just to get your feet wet and try out creating a table and performing various operations on it. Use the mysql command-line client as invoked above and enter these commands. You can simplify the command entry in many cases by using the command-repeat feature. Simply press the up-arrow key to access the history of previous commands and then use those directly or make modifications on them.
mysql> create table thing (item varchar(20), qty int); mysql> show tables; mysql> describe thing; mysql> insert into thing values ( 'book', 10 ); mysql> insert into thing values ( 'pencil', 4 ), ( 'book', 5 ); mysql> select * from thing; mysql> select count(*) from thing; mysql> select max(qty) from thing; mysql> delete from thing where item='book'; mysql> select * from thing; mysql> insert into thing values ('table', 2), ('chair',12); mysql> select * from thing; mysql> update thing set qty=qty-1 where qty>10; mysql> select * from thing; mysql> update thing set qty=qty+1 where item='table'; mysql> select * from thing; mysql> drop table thing; mysql> quit