Some useful commands to deal with MySQL…
Assuming, the mysql package was installed and the service is up and running, and you are logged at the same server.
For the first time, just paste the mysql command, you’ll see a response from the database, and the MySQL command-line prompt:
mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 17 to server version: 4.1.10a-nt Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql>
When not facing an untrusted environment, you may not want to setup a password. However, it is higly recommended to set up a password for the user root. Once logged in the database server, apply the following command:
SET PASSWORD = PASSWORD('a_new_password_for_root');
Now, you can log out using:
When you want to log in as root, you will be promped for the above defined password. Just paste the following command and insert the root password once promped:
mysql -u root -p
Once logged, enter the following command to create a database (e.g., testdb):
CREATE DATABASE testdb;
To verify, the database was created, use:
If you want to delete the created database, use:
DROP DATABASE testdb;
The data types are not discussed here, you can always find an up to date documentation on MySQL Reference Manual pages.
To start working with a database, it needs to be selected first:
To create a table, use:
CREATE TABLE table_name (column_name column_data_type, ...);
CREATE TABLE data (id INT(11) NOT NULL auto_increment, name VARCHAR(20) NOT NULL, release_year YEAR, release_date DATETIME, PRIMARY KEY (id));
To delete a table, use:
DROP TABLE table_name;
To alter the table data, column names, etc., check the Reference Manual
Restrict Access to the Database
In MySQL environment, create a new user:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'p455w0rd';
Grant privileges while assigning the password:
GRANT ALL ON db_name.* TO 'username'@'localhost';
The primary key feature can be found in the Reference manual.
The join command feature can be found in the Reference manual.
The views feature can be found in the Reference manual.
And many others…