Category Archives: MySQL

MySQL Basics

Showing the version number of MySql:

  • mysql> SHOW VARIABLES LIKE “%version%”;

The STATUS command displays the version as well as version comment information

·       mysql> STATUS;
 

Starting MySql  server from the command line:

  • C:\> “C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld”

If mysqld doesn’t start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the C:\Program Files\MySQL\MySQL Server 5.0\data directory. It is the file with a suffix of .err. You can also try to start the server as mysqld –console; in this case, you may get some useful information on the screen that may help solve the problem. The last option is to start mysqld with the --standalone and --debug options. In this case, mysqld writes a log file C:\mysqld.trace that should contain the reason why mysqld doesn’t start. Use mysqld –verbose –help to display all the options that mysqld understands.

To see the list of options provided by MySql, invoke it with the –help option

·       C:\> mysql –help

Connecting to the MySql server:
·       C:\> mysql -h host -u user -p
      Enter password: ********
If, when you attempt to log in, you get an error message such as ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), it means that that MySQL server daemon (Unix) or service (Windows) is not running.

Command to show version number and current Date:

·       mysql> SELECT VERSION(), CURRENT_DATE;
 

If you decide you do not want to execute a command that you are in the process of entering, cancel it by typing \c:

·       mysql> SELECT
          -> USER()
         -> \c
Use the SHOW statement to find out what databases currently exist on the server
·       mysql> SHOW DATABASES;

Database can be accessed using use command
·       mysql> use  test (‘test’ is the name of the database)

Granting permissions on the database to the user is granted by administrator using the grant command
·       mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
 
Database can be created using the following command:
·       mysql> CREATE DATABASE menagerie;
 
If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -pmypassword, not as -p mypassword). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

Show tables outputs the tables in the database
·       mysql> SHOW TABLES;
 
Use a CREATE TABLE statement to specify the layout of your table:
·       mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
 

To verify that your table was created the way you expected, use a DESCRIBE statement:

·       mysql> DESCRIBE pet;

MySQL expects dates in 'YYYY-MM-DD' format; this may be different from what you are used to.

In order to load data into the above ‘pet ’ table, You could create a text file pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For example, the record for Whistler the bird would look like this (where the whitespace between values is a single tab character):

To load the text file pet.txt into the pet table, use this command:

  • mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; Note that if you created the file on Windows with an editor that uses \r\n as a line terminator, you should use:
  • mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet -> LINES TERMINATED BY '\r\n'; If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default. If the statement fails, it is likely that your MySQL installation does not have local file capability enabled by default.
When you want to add new records one at a time, the INSERT statement is useful.
·       mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

To find out which database is currently selected, use the DATABASE() function:

·       mysql> SELECT DATABASE();

You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file:

·       shell> mysql < batch-file

If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this:                      C:\> mysql -e "source batch-file"

If you need to specify connection parameters on the command line, the command might look like this:

shell> mysql -h host -u user -p < batch-file
Enter password: ********
If you have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen. shell> mysql < batch-file | more
You can catch the output in a file for further processing.
shell> mysql < batch-file > mysql.out. If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv. 

To create and Grant permissions to users:
  • GRANT SELECT,INSERT,UPDATE, DELETE,CREATE,DROP
    ON [schema Name].*
    TO ‘[user Name]’@'[host Name]’
    IDENTIFIED BY ‘[Password]’;
Source: MySQL documentation