My sql command line client

Basic MySQL managment

1. MySQL Command Line Client J M Githeko

2. Locating the Client Client is called “mysql.exe” Located in the “bin” directory inside the MySQL installation directory Called in a command console window Connect using:

3. Connecting to MySQL mysql -h host -u user -p Then supply password from prompt host is the IP number or domain name of computer hosting MySQL user is your user name

4. Connecting to a Database Use “ show databases ;” to list existing databasesat the command prompt (note semicolon) as shown: mysql > show databases; To use a specific database, at the command prompt, type “ use comp361 ” as shown: mysql > use comp361

5. To View Tables and Table Structure At command prompt, type “ show tables ;” You can view a table structure by typing “ describe table_name ;” table_name is the name of the table

6. To Create a Database create database comp361 ;

7. Create Table CREATE TABLE Students ( Name VARCHAR(40) NOT NULL, ID VARCHAR(40) NOT NULL, PRIMARY KEY (ID) );

8. Import Data into MySQL Table mysql > LOAD DATA LOCAL INFILE “test.txt" INTO TABLE pet FIELDS TERMINATED BY XXX FIELDS [optionally] ENCLOSED BY YYY LINES TERMINATED BY '\r\n' ; XXX are the characters separating fields (e.g. tab \t). YYY are characters enclosing fields e.g., "" This should be accompanied by a ESCAPED BY character.

9. Load Data from *.csv File (Comma delimited) mysql > LOAD DATA LOCAL INFILE “I:\\users.csv " INTO TABLE leo FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' ; [Windows text files need Carriage Return + Line Feed line termination (\r\n) Leave out “ LOCAL” to load from the server’s data directory ]

10. SQL Commands to Learn CREATE TABLE UPDATE abc SET xyz DROP TABLE abc SELECT * FROM efg WHERE ijk = “stu“ INSERT INTO shop VALUES (1,'A',3.45),…….; Learn common MySQL data types

11. Example CREATE TABLE course ( COMP0361 VARCHAR(8), Title VARCHAR(50), CF REAL UNSIGNED, Lecture TINYINT UNSIGNED, Tutorial TINYINT, Practicals TINYINT, Prerequisite1 VARCHAR(8), Prerequisite2 VARCHAR(8), Prerequisite3 VARCHAR(8), Prerequisite4 VARCHAR(8), Prerequisite5 VARCHAR(8), UNIQUE (COMP0361) );

12. Assignment: Install MySQL Install MySQL Administrator Install MySQL Query Browser

13. End Download and read Read the MySQL Manual