Slide 1
XAMPP
Accessing MySQL
Get the command line
change drive (if needed), type
C:
change to the xampp installation folder, type
cd c:\xampp\mysql\bin
XAMPP
Accessing MySQL
Win7/10, simpler way.
Explorer > navigate to xampp\mysql\bin folder
Shift + RightClick
Select:
Open command window here
XAMPP
Accessing MySQL
Login using root and password (if you set one)
Type the following:
mysql –u root –p
And then your password
-u: username
-p: password
XAMPP
Basic MySQL command
xampp comes with cdcol database installed
cdcol contains cds table.
To use/change database (note the semi-colon):
use
e.g. use cdcol;
To show available tables:
show tables;
XAMPP
Basic MySQL command
Show schema/constraints of table, type:
describe
e.g. describe cds;
XAMPP
Import an existing database
You are provided with example script which
construct tables used in lecture: cs250.sql
Download cs250.sql from blackboard
Save it in xampp/mysql/bin folder.
There are two ways to rebuild/import database
from a .sql file.
1. Use MySQL under window prompt
2. Use MySQL’s source command within MySQL
XAMPP
1. Use MySQL under window prompt
Type in command prompt:
mysql –u root –p < cs250.sql
Extra: what is the meaning of < under prompt:
redirect standard input from a file
What is standard input? The cursor where you can type!
XAMPP
1. Use MySQL under window prompt
Now, GO into MySQL, check if the database is created.
Database CS250 is created and available.
Before
import
After
import
XAMPP
2. Use MySQL’s source command
Inside MySQL, type: source
e.g. source c:\xampp\mysql\bin\cs250.sql
note: NO quotes, NO semi-colon at the end
note: if drive\path is omitted, assume same folder as mysql
Database CS250 is created and available.
Before
import
If Method 1 works, you need not run this.
After
import
XAMPP
Basic MySQL command
What is in cs250.sql?
SQL DDL
statements
To be covered
at week4
Lecture 8
XAMPP
Basic MySQL command
To test SQL statements taught in lecture
Use cs250 database.
Type SQL in MySQL,
semi-colon
XAMPP
Copy data from command prompt
+—–+—–+——+
| pid | cid | year |
+—–+—–+——+
| p1 | c1 | 2011 |
| p1 | c2 | 2012 |
| p2 | c2 | 2012 |
| p1 | c1 | 2011 |
| p1 | c2 | 2012 |
| p2 | c2 | 2012 |
+—–+—–+——+
right click
select Mark
click and drag
your mouse
right click again
to copy
paste anywhere
you like
XAMPP
Accessing MySQL
phpMyAdmin:
http://localhost/phpmyadmin
Click
cdcol database
SQL tab
http://localhost/phpmyadmin
XAMPP
phpMyAdmin
Useful, but you are warned:
Need to deal with collation and language settings
UI is messy and not intuitive
Command line is much faster
Importantly, for your career & job search:
On linux (OpenSource,Free) / unix
phpMyAdmin very likely NOT available!
Often ONLY text prompt is available
And, know the SQL command is a MUST!!!