MySQL:Basic commands

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL, Answers <

Introduction

Overview

The objective of this exercise is to make you familiar with how to executing some SQL commands. The following exercises assume you are in the SQL console environment.


Icon Objectives.png

Objectives

By the end of this page you will be able to:

  • execute some basic commands in MySQL

Executing SQL Statements

Exercise 1: SQL and the ;

To get started try the following two commands (press [Enter] at the end of each line).


mysql> SELECT NOW();
mysql> SELECT NOW(), USER(), VERSION();
  • Not working???
    • Make sure you do not have a space between the function and the brackets. (e.g. NOW[space]() doesn't work!)

Exercise 2: SQL and \g or \G

A ; is the usual way to terminate an sql statement, however you can use \g or \G. Try them out...

mysql> SELECT NOW(), USER(), VERSION()\g
mysql> SELECT NOW(), USER(), VERSION()\G

The \g is the same as a ; while the \G lists things in a "vertical" format

To speed up the typing see what happens when you use the right and left arrow keys!

Exercise 3: SQL over multiple lines

If you get an extra long line (which will happen as you get more complicated sql commands) you can press [Enter] mid line, for example

mysql>SELECT NOW(),
    -> USER(),
    -> VERSION()
    ->;

Notice that the prompt shows as "->" to show that you are continuing on the statement.

Exercise 4: Cancelling over multiple lines

Use \c to cancel the entering of the current statement, for example

mysql>SELECT NOW(),
    -> USER(),
    -> \c
mysql>

Reference: DuBois, P. (2009, pp. 30-33)[1]

Exercise 5: Running commands from an external file

Read this part first (exercise follows...)

To make things easier you can type your sql into an external text file (e.g. using notepad) and get mysql to load then run the file using the source command.

mysql>SOURCE C:\MVsql\mysqlA.sql

If you have shell access you can do this when you run mysql

%>mysql < C:\MVsql\mysqlA.sql
  • Notes
    • The % stands for the path
    • The extension sql is used but the file is actually a txt file. (You could use a .txt extension but it is more usual to use .sql)
    • I created a folder called MVsql for my files on drive C
    • If you need to edit the file, you can use Windows Explorer, find the file in the Folders, then right click and Open With > NotePad.

Exercise

Now try this...

1. Create a folder on your drive with your intials followed by sql (e.g. mine will be MVsql )

2. Create a text file in the folder (e.g. MVsql) called mysqlA.sql, and enter: (Notepad is an application that comes with Windows that allows you to create a text file (Start > Programs > Accessories > Notepad))

SELECT NOW(), USER(), VERSION();

3. Start mysql and enter (note I have my sql file on drive C, you may have yours somewhere else)

mysql>SOURCE C:\MVsql\mysqlA.sql


Icon Notes.png

Notes : Editing MySQL Commands

  1. Don't add a semi-colon to the end of the line when you use the SOURCE Command
  2. Use the arrow keys to recall earlier commands entered into MySQL
    ([Insert] and [Delete] keys also work).
  3. You can cut and paste from an external file into MySQL using:
    1. [Ctrl]+[c] top copy the text
    2. In MySQL use [Right click] + [Paste] to paste in the text
      ([Ctrl]+[v] doesn't work)

Icon References.png References

  1. DuBois, P. (2009) MySQL Developer's Library, 4th Ed. Addison-Wesley

MySQL:Basic commands. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 13, 2017, from http://www.virtualmv.com/wiki/index.php?title=MySQL%3ABasic_commands    (zotero)