MySQL I : Encryption and Passwords

Encoding Passwords: PASSWORD() The PASSWORD() function creates a non-decryptable hash of the given password. INSERT INTO users VALUES (NULL, ‘user_name’, PASSWORD(‘user_pw’)); Resetting a password: mysqladmin -u user_name password #you will be prompted for the new password   One-Way Encryption: ENCRYPT() The ENCRYPT() function creates a non-decryptable encrypted string. INSERT INTO clients VALUES (NULL, ENCRYPT(‘client_name’)); Note …

MySQL I : Functions

Text Functions MySQL functions look similar to mathematical functions. The function itself has a name, and its transformation is performed on a target: FUNCTION(column). The basic syntax is: SELECT FUNCTION(column) FROM table_name You can select multiple columns, and perform the function on only  one: SELECT title, FUNCTION(artist) FROM albums   Text Functions Name Usage Purpose …

MySQL I : Update and Delete

1. The structure of and the information in tables changes, you need to be able to update them. 2. Select a record: SELECT client_street, client_city, client_state, client_zip FROM clients WHERE client_id = 3; 3. Perhaps the address has changed. To update this field: UPDATE clients SET client_street = ‘1313 Blackbird Lane’, client_city=’Castle Rock’, client_state=’ME’, client_zip …

MySQL I : Joins

1. You can retrieve related information that exists by using the JOIN and “dot” syntax. This is the MySQL equivalent of a primary key – foreign key relationship. As of MySQL 3.23 there are no permanently designated foreign keys (unlike Access or Oracle). This should change with MySQL 4. 2. For example, to retrieve (by …

MySQL I : Select and Insert

1. Start the MySQL Monitor client: mysql –u root –p Password: mypass 2. Create and load for use, a new database: CREATE DATABASE accounting; USE accounting; 3. Create an invoices table: CREATE TABLE invoices ( invoice_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, client_id SMALLINT(3) UNSIGNED, invoice_date DATE NOT NULL, invoice_amount DECIMAL(10,2) UNSIGNED NOT NULL, invoice_description TINYTEXT, …

MySQL I : Creating Databases and Tables

1. Start the MySQL Monitor client: mysql –u root –p Password: mypass 2. Create and load for use, a new database: CREATE DATABASE accounting; USE accounting; 3. Create an invoices table: CREATE TABLE invoices ( invoice_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, client_id SMALLINT(3) UNSIGNED, invoice_date DATE NOT NULL, invoice_amount DECIMAL(10,2) UNSIGNED NOT NULL, invoice_description TINYTEXT, …