skip navigation

www.Hilands.com


Content:: MySQL

MySQL "Basic" Command Line Reference
Last Modified: 2012-06-05


Table of Contents
Using the Command Line Interface
To access the command line interface you must find the mysql binary. If you are unsure where it is you can use the find command. # find -name mysql | grep bin
The pipe "|" appends another command in this case grep. The grep command will parse the output looking for any line that has "bin" in it. I install my mysql files in /usr/local/mysql so the the command above returns /usr/local/mysql/bin/mysql.
Now that you have found the mysql binary file you can start the mysql cli. Replace the path with the one appropriate for your installation. # /usr/local/mysql/bin/mysql
Navigating the MySQL CLI
# mysql> show databases;
show databases will return a list of databases in the system.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

# mysql> use <database name>;
The use command will select a database for use.
ERROR 1046 (3D000): No database selected
An error like the one above will appear if you attempt to run a database command without the database selected. The use command will solve this issue.
# mysql> show tables;
show tables will print a list of databases to the screen.
+----------------------+
| Tables_in_<database> |
+----------------------+
| <tables>             |
+----------------------+
# rows in set (0.00 sec)
Creating Database(s)
# mysql> create database database_name;
# mysql> use database_name;
Access Rights
If you are using a scripting language like PHP to read data to display to a website its pretty stupid to use "grant all"
# mysql> grant privilege on database_name.table(s) to 'user'@'host' identified by 'password';
# mysql> grant privilege on database_name.* to 'user'@'localhost' identified by 'password';
I forget what the % does, I'll note it sometime
# mysql> grant privilege on *.* to 'user'@'%' identified by 'password';
ok now some "working" examples.
# mysql> grant select on database_name.* to 'user'@'localhost' identified by 'password';
# mysql> grant insert on database_name.comment_table to 'user'@'localhost' identified by 'password';
View Access Table Reference
Security Recommendations can be found here
Creating Tables
# mysql> create table table_name(
`fid` int unsigned not null Primary Key auto_increment,
`fstring` varchar(255),
`ftext` longtext,
`fdate` date NOT NULL,
`ftime` time default NULL,
`fint` int(3),
);
Change (alter) Table structure
# mysql> alter table table_name add column;
Reset auto increment
Setting the auto increment to 0 will make the next entered ID the hightest ID plus one
# mysql> alter table table_name AUTO_INCREMENT = 0;
Insert Commands
# mysql> insert into table_name (column) values("column data");
Multiple Inserts
# mysql> insert into table_name (column) values
("column data"),
("column data"),
("column data");
Edit/Modify table data
# mysql> update table_name set column='column data' where id=unique id;
# mysql> update table_name set column='column data' where id=1;
# mysql> update data set date='yyyy-mm-dd' where id=1;
# mysql> update data set time='hh:mm:ss' where id=1;
It is possible to change the column data in every row # mysql> update table_name set column='column data';
Lock Tables
# mysql> LOCK TABLES `table_name` WRITE;
//run insert, alter or other table command here
# mysql> UNLOCK TABLES;
Select
Column(s) can be individual Columns like "column_one, column_two, column_three" or wild card characters like "*" for all columns.
Using a select statement alone like "select *" will return an error as it does not know which table to pull the data from.
ERROR 1096 (HY000): No tables used

You can add from to the select statement followed by the table name to display the data from a table.
# mysql> SELECT column(s)
FROM table_name;
There are other conditions you can add to your select statement. "Where" and "Order By" are common conditions to add to your statement.
# mysql> SELECT column(s)
FROM table_name
WHERE column = equals variable &&
	column != not equal to variable
ORDER BY column;
The where command will only choose tables that meet the condition. Your condition can be equal "=", not equal "!=", or "||", and "&&". The order by command will change the order the data is displayed. Most tables have an ID to identify a row. The order can be Ascending ASC or Descending DESC. If you select all the rows and order them by the IDit will display the rows in numerical order by ID.
Insert into Based on Select
The Insert .. Select SQL allows you to take chunks from one table and insert it into another. To test the script you can leave out the INSERT INTO and view the column names that appear. The Insert variables between the parentheses "(xxx)" will be the columns populated from the select.
Insert .. Select Syntax
# mysql> INSERT INTO table_name_insert (column_one, column_two)
	SELECT table_name_select.column_one, table_name_select.column_two
	FROM table_name_select;
Inner Join
join
# mysql> SELECT *
FROM table_name_one, table_name_two
WHERE table_name_one.column_id = table_name_two.column_id;
Left Join
# mysql> SELECT *
FROM table_name_one
LEFT JOIN table_name_two
ON table_name_one.column_id = table_name_two.column_id;
Backing up and Restoring
Backing up database on systems that are functional can easily be done with the mysqldump command. This will export a specificed database into an SQL file. An SQL file is a basic text file that contains the content and queries of the database that can be imported into a database. # mysqldump -a -u <user> -p<password> <database> > <file>.sql
An import can easily be ran by importing the SQL file as a command. # mysql -u <user> -p<password> <database> < <file>.sql
Simple shell script to backup databases to SQL files
This script will add a simple date stamp to the SQL file name.
Repeat the mysqldump line for every database you wish to backup.
#!/bin/sh
DATE=`date +%Y%m%d` ;
mysqldump -a -u <user> -p<password> <database> > <file>.$DATE.sql