程序代写代做代考 database SQL Slide 1

Slide 1

XAMPP

Accessing MySQL

Get the command line

change drive (if needed), type

C: (where C is the drive letter)

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!!!