Introduction
As already mentioned: We based this course on MySQL, but used no special MySQL features to keep the course compatible to SQL in general.First Steps
We assume that MySQL (or your SQL program) is installed and running. For the following commands, we use a command shell, like the GNOME Terminal under Linux.Logging in to MySQL
We can log in as root with the following command on the shell:mysql -u root -p
After you have typed in this command line, the shell will wait for you to type in the root password. The complete dialogue looks like this:
bernd@venus:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 50 Server version: 5.1.61-0ubuntu0.11.04.1 (Ubuntu) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Now, MySQL is waiting for commands with the prompt "mysql>".
Quitting MySQL / Logging out
Though we haven't really started, we want to tell you how to quit the mysql shell. There are two ways: Either you use "Ctrl + d" or you type in "quit" followed by the return key."Now" is the Time and the Date
Now, let's assume, that you have successfully logged in and you haven't quit in the previous subchapter. Where can we go from here? Like in real life, if you have nothing better to do or to ask for, query for the time. There is an easy way to ask for the time and the date in SQL. The command is now(). Let's test it:mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2012-04-07 18:51:58 | +---------------------+ 1 row in set (0.00 sec)
MySQL as a Calculator
SQL or MYSQL can be used for calculating mathematical expressions using functions like sin, cos, sqrt and so on:mysql> SELECT SIN(PI()*3), sqrt(2)*3; +----------------------+------------------+ | SIN(PI()*3) | sqrt(2)*3 | +----------------------+------------------+ | 3.67381906146713e-16 | 4.24264068711929 | +----------------------+------------------+ 1 row in set (0.00 sec) mysql>
Checking the Version
To see which version of SQL or MySQL is installed, you can use the VERSION() function in combination with the select statement:mysql> SELECT VERSION(); +-------------------------+ | VERSION() | +-------------------------+ | 5.1.63-0ubuntu0.11.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql>
\G with SELECT
Appending \G to a SELECT command instead of a semicolon changes the output behaviour:mysql> SELECT VERSION()\G *************************** 1. row *************************** VERSION(): 5.1.63-0ubuntu0.11.04.1 1 row in set (0.00 sec) mysql>
Getting help
Using the help command, may be a good start:mysql> help For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents' mysql>