MySQL Commands

MySQL is an open-source relational database management system (RDBMS) widely used in web applications. The SQL part of mySQL stands for "Structured Query Language."

Creating a MySQL Connection
$con=mysqli_connect(website_name, username, password, database);

Creating a Database
$sql="CREATE DATABASE my_db";

Initializing a Database
shell> mysql_install_db [options] Initializes the MySQL Database

Administering a MySQL Server
mysqladmin can be used to check server configuration and current status as well as drop and create databases: shell> mysqladmin [options] command [command-arg] [command [command-arg]]

Switches
-p Enter password -u Changes user, MySQL keeps current user if user does not enter -u switch -h Selects host; by default, MySQL connects to the server on local host if -h switch is not entered

Main Data Types
Exact Numerical Data Types DECIMAL, INTEGER, SMALLINT, NUMERIC Approximate Numerical Data Types FLOAT, REAL, DOUBLE PRECISION Character/Other CHAR, VARCHAR, BINARY

Identifier Qualifiers
col_name The column from whichever table in the statement contains a column of that name tbl_name.col_name The column name from the table name of the default database db_name.tbl_name.col_name The column name from the table name of the database db_name

Creating a Table
CREATE TABLE TableName( name(number), ... ); Use NOT NULL to specify that the data type cannot be null

Displaying Table/Database Information
shell> mysqlshow [options] [db_name [tbl_name [col_name]]] Can be used to quickly see existing databases, tables, or a table's columns or indexes shell> myisamchk [options] tbl_name ... Can be used on MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes)

Upgrading a Table
shell> mysql_upgrade [options] Upgrades the mysql tables to the newest version and examines them for incompatibilities with the newest version

Testing a Table
mysqlcheck checks tables for errors shell> mysqlcheck test t

Saving a File/Loading Data to a File
tee Saves input and output from a session and appends it to a specified file shell> mysqlimport [options] db_name textfile1 [textfile2 ...] mysqlimport takes the filename, removes the extension, and uses the filename as the name of the table in which to store the data

Other
-f or --force Can prevent mySQL from terminating a session after it encounters an error (use carefully)

Error functions
shell> perror [options] errorcode ... perror prints descriptions for system error codes shell> comp_err [options] comp_err creates the errmsg.sys file that mysqld uses to determine the error messages associated with different error codes

Replace
shell> replace from to [from to] ... -- file_name [file_name] ... shell> replace from to [from to] ... < file_name replace can change strings in places or in standard input

Resolveip
shell> resolveip [options] {host_name|ip-addr} ... resolveip resolves host names to IP addresses and vice versa

Connecting to MySQL Using JDBC
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; //do not import com.mysql.jdbc.* if you don't want problems public class LoadDriver { public static void main(String[] args) { try { // newInstance call is works around some broken Java implementations Class.forName("com.mysql.jdbc.Driver").newInstance; } catch (Exception e) { // error handling }    } }