CS & IT Solutions

all access to a system through a single account with all abilities is typically dangerous. Creating MySQL user accounts allows privileges to be granted as appropriate.

To create a user jsmith with password Secret15 and allow them to do anything with the database named accounts, connect to the database with mysql and issue the command:

grant all on accounts.* to jsmith@localhost identified by ‘Secret15′;

courtesy http://www.tech-recipes.com/rx/191/create_a_mysql_user_account/

Change the root user password for MySQL using mysqladmin

To change the MySQL root password to PaSsWoRd, use:

mysqladmin -u root password PaSsWoRd

courtesy http://www.tech-recipes.com/rx/19/change-the-mysql-root-user-password/

To remove a table from a MySQL database, and remove all of its data, use the following SQL command:

drop table if exists recipes;

The command will conditionally delete a table if it exists. The ‘if exists’ syntax is optional, but is useful when using SQL commands in a file, such as when importing data, as it will not display errors.

The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

courtesy http://www.tech-recipes.com/rx/277/drop-or-delete-a-table-in-mysql/

To list the databases that exist in a MySQL server, use the ’show databases’ SQL command:

show databases;
+—————–+
| Database |
+—————–+
| financial |
| mysql |
| test |
+—————–+

The mysql database holds user priviledge information and is required for operation of MySQL. The command can be issued from any SQL source including an application with database connectivity (i.e., PHP, Java, etc.) or directly from mysql command (see Connect to a MySQL server using the mysql command for more information).

courtesy http://www.tech-recipes.com/rx/274/display-a-list-of-databases-on-a-mysql-server/

A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person’s social security number). When more than one column is used, the combination of column values must be unique.

When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command:

CREATE TABLE `test1` (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);

Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).

courtesy http://www.tech-recipes.com/rx/377/create-a-mysql-table-with-a-primary-key/