MySQL  
mysql
documentation
MySQL Documentation
Connect to
MySQL

from Linux logged into csci304zimmer:

$ mysql  -p
Enter Password: <mysql password>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 143287
Server version: 5.1.61 Source distribution
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement

mysql>

 

change your mysql
password
mysql> set password = password('somepassword'');
Query OK, 0 rows affected (0.04 sec)

mysql>

 

MySQL
commands
  • SQL command followed by a semi colon (semi colon terminates command)
  • not case sensitive (except user created identifiers - databases & tables)
  • can enter multiple stmts on a line separated by semi colons
  • single statement can span several lines

Examples:

mysql> select version( ); select current_date( );

mysql> select version( ); select now( );

note: to cancel a command  use <cntl> c

 

MySql prompts:

->  waiting for the rest
         (possibly missing a semi colon on previous command)
'>  waiting for the rest of a string that used single quotes
">  waiting for the rest of a string that used double quotes

 

Display
databases

mysql>show databases;
shows a list of database...

mysql>

connect to a
database

mysql>connect <database name>
connection id: ...
current database: ...

mysql>

display
tables

mysql>show tables;
shows a list of tables...

mysql>

create
table
mysql> create table <table> (<field1> <datatype>,
       -> <field2> <datatype> ...);

mysql>

 

Data Types:

int
real
char(len) - pads to length with spaces (best for index)
varchar(len) - truncates, reduces waste
date - YYYY-MM-DD
time
enum(' <value1>', '<value2> ...)

 

Display table organization:

mysql> describe <table name>;
displays the fields in the table

mysql>

 

 

fill the
table
manually:

mysql> insert into <table name>
       -> values ('<field1 value>', '<field2 value> ',
       ->  ...);

mysql>

from a file:

mysql> load data local infile "<fileName>" into table
       -><table Name>;

 

Try This:

Create the following tables:

table name: courses

num

sec

instr

credits

CSCI130

1

Zimmer

3

CSCI130

2

Bennett

3

MATH490

1

Marzano

1

MATH262

1

Sylvester

3

mysql> create table courses (num varchar(7),
       -> sec int, instr varchar(10), credit int);

mysql> describe courses;

 

 

table name: faculty

name

bdate

subj

gender

ext

Stein

1972-01-13

CS

F

1185

Zimmer

1976-10-18

CS

F

1179

Molnar

1960-04-29

CS

M

2553

Quinn

1978-02-15

MATH

F

2760

Sylvester

1982-11-30

MATH

M

1164

Marzano

1980-06-21

MATH

M

\n

Pineo

\n

CS

F

1161

Bennett

1965-08-29

CS

M

1126

mysq> create table faculty(name varchar(10),
       -> bdate date, subj enum('CS','MATH'),
       -> gender enum('M','F'), ext char(4) );

 

mysql> describe faculty;

 

Add the data to the courses table:

mysql> insert into courses values ('CSCI130',
       -> 1, 'Zimmer', 3);
mysql> insert into courses values ('CSCI130',
 
       -> 2, 'Bennett', 3);
mysql> insert into courses values ('MATH490',
       -> 1, 'Marzano', 1);
mysql> insert into courses values ('MATH262',
 
       -> 1, 'Sylvester',3);

 

 

Add the data from a file to the faculty table:

mysql> load data local infile
  -> "~zimmer/public_html/csci304/faculty.txt"
  -> into table faculty;

change data
 in a table

Update data:

mysql> update <tableName> set <colName> = " <value>
       -> where <cond>;

condition:
                    <field name> relational operator <value>
                    AND
                    OR

 

Delete data:

mysql> delete from <tableName> where <cond>;

Query the
table

mysql> select <colName>,<colName>... from <tableName>
       -> where <cond> ordered by <colName>;

 


join two or
tables

mysql> select <tableName> . <colName>, ... from
       -><tableName1>,<tableName2>,...where 
      
-><tableName1>.<colName> =
       -> <tableName2>.<colName>;

 

 

Try This:

Update the tables:

mysql> update faculty set subj="MATH" where
 
       -> name="Molnar";

mysql> update courses set instr="Stein" where
 
       -> num="CSCI130" and sec="2";

mysql> delete from faculty where name = "Pineo";

 

 

Query the tables:

mysql> select name, ext from faculty where
 
       ->  gender='m';
mysql> select name, gender from faculty where
 
       ->  gender='f' or ext='1181';
mysql> select name from faculty where gender='m'
 
       ->  and subj='CS';
mysql> select * from faculty;
mysql> select * from faculty where gender='m'
 
       -> order by name;

 

 

Join the tables:

mysql> select faculty.name, courses.num from
 
       -> faculty,courses where
 
       -> faculty.name = courses.instr;