Frequent SQL commands DML and DDL

Frequent SQL commands part 2

Data manipulatiuon language DML

-- Insert new rows
 INSERT INTO table1
VALUES(1, 2, 'test')
 
 
 -- Insert new rows with columns
 INSERT INTO table1 (key, parent, column1)
VALUES(1, 2, 'test')
 
 
-- Update one row
 UPDATE table1 SET parent = 0 WHERE key = 1
 
 -- Update all
 UPDATE table1 SET parent = 0
 
-- Delete rows in a table.
 DELETE FROM table1 WHERE id = 1
 
 
-- Deletes all rows from table.
 TRUNCATE TABLE table1

Embeded updates

-- standard sql one table
UPDATE table1 t1,  
(  
    SELECT 'key', MAX(parent) as maxParent  
    FROM table1  
    GROUP BY 'key'  
) t  
SET t1.parent = t.maxParent  
WHERE t1.key = t.key


-- one table with more constraints
UPDATE mytable c1, 
(
SELECT distinct name as name, salary AS maxsalary
FROM mytable
WHERE salary > '0'
) t
SET c1.salary = t.maxsalary
WHERE c1.salary = '0'
AND c1.name = 'Jack' 
AND t.name = c1.name


-- standard sql two tables
UPDATE table2 t2,
(   SELECT columt1, key 
    FROM table1 
) t1
SET t2.columt1 = t1.columt1
WHERE t1.key = t2.key

-- mysql two tables
UPDATE table2 t2, (
SELECT 'key', parent
FROM table1) t1 
SET t2.name = t1.name
WHERE t1.id = t2.id

-- mysql two tables
UPDATE table2 t2
JOIN table1 t1 ON t1.key = t2.key 
SET t2.parent = t1.parent;

Database modification

--List all databases
SHOW DATABASES;
--Create database
CREATE DATABASE mydb;
--Use a database
USE mydb;
--List tables in the database
SHOW TABLES;
--Show table structure
DESCRIBE table1;
--Show table columns
SHOW COLUMNS FROM table1;
--Delete a database
DROP DATABASE mydb;

Table modification Data definition language DDL

--create table in DB - last two rows are optional or could depend on the db
CREATE TABLE `table1` (
	`key` INT(11) NULL DEFAULT NULL,
	`parent` INT(11) NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

-- Add columns in an existing table.
 ALTER TABLE table1 ADD column1 char(10)
 
-- Delete columns in an existing table.
 ALTER TABLE table1 DROP column1 char(10)
 
-- Delete a table.
 DROP TABLE table1
 

Index manipulation

-- Create a simple index.
 CREATE INDEX tableIndex ON table1 (key, parent)
 
 
-- Create a unique index.
 CREATE UNIQUE INDEX uniqueIndex ON table1 (LastName DESC)
 
 
-- Delete a index.
 DROP INDEX table1.uniqueIndex 

Related Article