mySQL part 3

Adding NOT NULL

ALTER TABLE vendors ADD COLUMN email VARCHAR(100) NOT NULL, ADD COLUMN hourly_rate decimal(10,2) NOT NULL; The email column is populated with blank values, not the NULL values. And the hourly_rate column is populated with 0.00 values.

Renaming tables using ALTER TABLE statement

ALTER TABLE old_table_name RENAME TO new_table_name; The ALTER TABLE statement can rename a temporary table while the RENAME TABLE statement cannot.

making column a foreign key

ALTER TABLE posts ADD CONSTRAINT fk_cat FOREIGN KEY (category_id) REFERENCES categories(id); If you remove the column that is a foreign key, MySQL will issue an error.

DROP COLUMN statement

ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name DROP column_name; Note that the keyword COLUMN keyword in the DROP COLUMN clause is optional so you can use the shorter statement as follows

Add columns to a table

ALTER TABLE vehicles ADD model VARCHAR(100) NOT NULL; --by default added in the end ALTER TABLE vehicles ADD color VARCHAR(50), ADD note VARCHAR(255);

Remove a column from table

ALTER TABLE vehicles DROP COLUMN vehicleCondition; This example shows how to remove the vehicleCondition column from the vehicles table

Rename a column in a table

ALTER TABLE vehicles CHANGE COLUMN note vehicleCondition VARCHAR(100) NOT NULL; The following example uses the ALTER TABLE CHANGE COLUMN statement to rename the column note to vehicleCondition:

Modify column position, remove constraint

ALTER TABLE vehicles MODIFY year SMALLINT NOT NULL, MODIFY color VARCHAR(20) NULL AFTER make; In this example: First, modify the data type of the year column from INT to SMALLINT Second, modify the color column by setting the maximum length to 20, removing the NOT NULL constraint, and changing its position to appear after the make column.

Rename table

ALTER TABLE vehicles RENAME TO cars; Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc.

Modify columns

It's a good practice to view the attributes of a column before modifying it. DESCRIBE vehicles; --note - VARCHAR(255), can be NULL ALTER TABLE vehicles MODIFY note VARCHAR(100) NOT NULL;

Dropping tables, privileges

Note that the DROP TABLE statement only drops tables. It doesn't remove specific user privileges associated with the tables. Therefore, if you create a table with the same name as the dropped one, MySQL will apply the existing privileges to the new table, which may pose a security risk.

How to release prepared statement

PREPARE stmt1 FROM 'SELECT productCode, productName FROM products WHERE productCode = ?'; DEALLOCATE PREPARE stmt1;

TRUNCATE TABLE vs DELETE FROM

To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get better performance.

How to use transactions

-- 1. start a new transaction START TRANSACTION; -- 2. Get the latest order number SELECT @orderNumber:=MAX(orderNUmber)+1 FROM orders; -- 3. insert a new order for customer 145 INSERT INTO orders(orderNumber, orderDate, requiredDate, shippedDate, status, customerNumber) VALUES(@orderNumber, '2005-05-31', '2005-06-10', '2005-06-11', 'In Process', 145); -- 4. Insert order line items INSERT INTO orderdetails(orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber) VALUES(@orderNumber,'S18_1749', 30, '136', 1), (@orderNumber,'S18_2248', 50, '55.09', 2); -- 5. commit changes COMMIT;

Selecting a database when you login

>mysql -u root -D classicmodels -p If you know which database you want to work with before you log in, you can use the -D flag to specify it as follows:

Selecting a MySQL database via command line

>mysql -u root -p In this command, we have specified the user root with -u flag and then used the -p flag. MySQL prompted for the password of the user. You need to type the password and press Enter to log in. mysql> use classicmodels;

To create a database in MySQL, you use the ... statement as follows:

CREATE DATABASE [IF NOT EXISTS] database_name;

MySQL CREATE TABLE syntax

CREATE TABLE IF NOT EXISTS tasks ( task_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, start_date DATE, due_date DATE, status TINYINT NOT NULL, priority TINYINT NOT NULL, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=INNODB;

declaring foreign key with ON DELETE CASCADE

CREATE TABLE rooms ( room_no INT PRIMARY KEY AUTO_INCREMENT, room_name VARCHAR(255) NOT NULL, building_no INT NOT NULL, FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON DELETE CASCADE );

DELETE and LIMIT clause

DELETE FROM customers WHERE country = 'France' ORDER BY creditLimit LIMIT 5; Similarly, the following DELETE statement selects customers in France, sorts them by credit limit in from low to high, and deletes the first 5 customers:

DELETE JOIN with LEFT JOIN

DELETE customers FROM customers LEFT JOIN orders ON customers.customerNumber = orders.customerNumber WHERE orderNumber IS NULL; The following syntax illustrates how to use DELETE statement with LEFT JOIN clause to delete rows from T1 table that does not have corresponding rows in the T2 table Note that we only put T1 table after the DELETE keyword, not both T1 and T2 tables like we did with the INNER JOIN clause. We can use DELETE statement with LEFT JOIN clause to clean up our customers master data. The following statement removes customers who have not placed any order:

deleting database

DROP DATABASE [IF EXISTS] database_name;

ROLLBACK example

First, log in to the MySQL database server and delete data from the orders table: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM orders; Query OK, 327 rows affected (0.03 sec) As you can see from the output, MySQL confirmed that all the rows from the orders table were deleted. Second, log in to the MySQL database server in a separate session and query data from the orders table: In this second session, we still can see the data from the orders table. We have made the changes in the first session. However, the changes are not permanent. In the first session, we can either commit or roll back the changes. For the demonstration purpose, we will roll back the changes in the first session. mysql> ROLLBACK; Query OK, 0 rows affected (0.04 sec)

Using MySQL REPLACE to insert a new row

INSERT INTO cities(name,population) VALUES('New York',8008278), ('Los Angeles',3694825), ('San Diego',1223405); REPLACE INTO cities(id,population) VALUES(2,3696820); First, REPLACE statement attempted to insert a new row into cities the table. The insertion failed because the id 2 already exists in the cities table. Then, REPLACE statement deleted the row with id 2 and inserted a new row with the same id 2 and population 3696820. Because no value is specified for the name column, it was set to NULL.

using lookup table in mySQL

INSERT INTO merits(performance,percentage) VALUES(1,0), (2,0.01), (3,0.03), (4,0.05), (5,0.08); -- insert data for employees table INSERT INTO employees(emp_name,performance,salary) VALUES('Mary Doe', 1, 50000), ('Cindy Smith', 3, 65000), ('Sue Greenspan', 4, 75000), ('Grace Dell', 5, 125000), ('Nancy Johnson', 3, 85000), ('John Doe', 2, 45000), ('Lily Bush', 3, 55000); UPDATE employees INNER JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * percentage;

DELETE JOIN with INNER JOIN example

INSERT INTO t1 VALUES (1),(2),(3); INSERT INTO t2(id,ref) VALUES('A',1),('B',2),('C',3); --assume table are empty, "ref" references t1 table DELETE t1,t2 FROM t1 INNER JOIN t2 ON t2.ref = t1.id WHERE t1.id = 1; Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.

MySQL prepared statement example

PREPARE stmt1 FROM 'SELECT productCode, productName FROM products WHERE productCode = ?'; First, prepare a statement that returns the product code and name of a product specified by product code SET @pc = 'S10_1678'; Second, declare a variable named pc, stands for product code, and set its value to 'S10_1678' EXECUTE stmt1 USING @pc; Third, execute the prepared statement: SET @pc = 'S12_1099'; EXECUTE stmt1 USING @pc; Fifth, execute the prepared statement with the new product code

Why do you need Prepared Statements

Prior MySQL version 4.1, a query is sent to the MySQL server in the textual format. In turn, MySQL returns the data to the client using textual protocol. MySQL has to fully parse the query and transforms the result set into a string before returning it to the client. The textual protocol has serious performance implication. To address this issue, MySQL added a new feature called prepared statement since version 4.1. The prepared statement takes advantage of client/server binary protocol. It passes the query that contains placeholders (?) to the MySQL Server as the following example: SELECT * FROM products WHERE productCode = ?; When MySQL executes this query with different productcode values, it does not have to fully parse the query. As a result, this helps MySQL execute the query faster, especially when MySQL executes the same query multiple times. Since the prepared statement uses placeholders (?), this helps avoid many variants of SQL injection hence make your application more secure.

Renaming multiple tables

RENAME TABLE depts TO departments, people TO employees;

Using MySQL REPLACE to insert data from a SELECT statement

REPLACE INTO cities(name,population) SELECT name, population FROM cities WHERE id = 1;

Using MySQL REPLACE statement to update a row

REPLACE INTO cities SET id = 4, name = 'Phoenix', population = 1768980; This example uses the REPLACE statement to update the population of the Phoenix city to 1768980: Unlike the UPDATE statement, if you don't specify the value for the column in the SET clause, the REPLACE statement will use the default value of that column.

Read vs. Write locks

Read locks are "shared" locks which prevent a write lock is being acquired but not other read locks. Write locks are "exclusive " locks that prevent any other lock of any kind.

How to get connectionID

SELECT CONNECTION_ID();

shows information about database

SHOW CREATE DATABASE testdb; MySQL returns the database name and the character set and collation of the database. Note that the CREATE SCHEMA statement command has the same effect as the CREATE DATABASE statement.

list all databases

SHOW DATABASES;

How to check if commands from some other sessions are waiting in the queue

SHOW PROCESSLIST;

MySQL supports two modifiers in the UPDATE statement.

The LOW_PRIORITY modifier instructs the UPDATE statement to delay the update until there is no connection reading data from the table. The LOW_PRIORITY takes effect for the storage engines that use table-level locking only such as MyISAM, MERGE, and MEMORY. The IGNORE modifier enables the UPDATE statement to continue updating rows even if errors occurred. The rows that cause errors such as duplicate-key conflicts are not updated. UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name1 = expr1, column_name2 = expr2, ... [WHERE condition];

A WRITE lock has the following features:

The only session that holds the lock of a table can read and write data from the table. Other sessions cannot read data from and write data to the table until the WRITE lock is released. LOCK TABLE messages WRITE; UNLOCK TABLES;

updating two tables in one query

UPDATE T1,T2 INNER JOIN T2 ON T1.C1 = T2.C1 SET T1.C2 = T2.C2, T2.C3 = expr WHERE condition OR UPDATE T1, T2 SET T1.c2 = T2.c2, T2.c3 = expr WHERE T1.c1 = T2.c1 AND condition

UPDATE to update rows returned by a SELECT statement

UPDATE customers SET salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' ORDER BY RAND() LIMIT 1) WHERE salesRepEmployeeNumber IS NULL; You can supply the values for the SET clause from a SELECT statement that queries data from other tables. For example, in the customers table, some customers do not have any sale representative. The value of the column saleRepEmployeeNumber is NULL We can take a sale representative and update for those customers.

UPDATE JOIN example with LEFT JOIN

UPDATE employees LEFT JOIN merits ON employees.performance = merits.performance SET salary = salary + salary * 0.015 WHERE merits.percentage IS NULL; Suppose the company hires two more employees Because these employees are new hires so their performance data is not available or NULL The UPDATE LEFT JOIN statement basically updates a row in a table when it does not have a corresponding row in another table. For example, you can increase the salary for a new hire by 1.5% using this statement

UPDATE to replace string

UPDATE employees SET email = REPLACE(email,'@classicmodelcars.com','@mysqltutorial.org') WHERE jobTitle = 'Sales Rep' AND officeCode = 6; In this example, the REPLACE() function replaces @classicmodelcars.com in the email column with @mysqltutorial.org.

Tips to find tables affected by MySQL ON DELETE CASCADE action

USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'classicmodels' --database name AND referenced_table_name = 'buildings' --table name AND delete_rule = 'CASCADE' Sometimes, it is useful to know which table is affected by the ON DELETE CASCADE referential action when you delete data from a table. You can query this data from the referential_constraints in the information_schema database as follows:

the name of the currently connected database

mysql> select database();

What happens when you read-lock certain table How to unlock table

session which locked it cannot write to the table until the session is over the other session which tries to edit table will be waiting in queue until the lock is off LOCK TABLE messages READ; UNLOCK TABLES; A READ lock for a table can be acquired by multiple sessions at the same time. In addition, other sessions can read data from the table without acquiring the lock.