SQL Examples

CREATE USER 'portal'@'localhost' IDENTIFIED BY '[password]';
GRANT ALL PRIVILEGES ON *.* TO 'portal'@'localhost' WITH GRANT OPTION;
CREATE DATABASE Portal;

CREATE USER 'portal'@'%' IDENTIFIED BY '[password]';

CREATE  TABLE IF NOT EXISTS `identifier`
(
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT UNSIGNED NOT NULL ,
  `identifier` VARCHAR(450) NOT NULL ,
  `identifier_type` VARCHAR(45) NULL ,
  `name` VARCHAR(100) NULL ,
  `email` VARCHAR(200) NULL ,
  `created_application_id` INT NULL ,
  `created_at` DATETIME NULL ,
  `modified_application_id` INT NULL ,
  `modified_at` DATETIME NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_identifier_user1` (`user_id` ASC) ,
  UNIQUE INDEX `identifier_UNIQUE` (`identifier` ASC) ,
  CONSTRAINT `fk_identifier_user1`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci;

SELECT *
 FROM table_name
 LIMIT 5
 OFFSET 5

SELECT TOP 5 *
 FROM table_name

SELECT *
 FROM table_name
 FETCH FIRST 10 ROWS ONLY

SELECT * FROM (
   SELECT row_number() OVER (ORDER BY id_col ) as row_number, id_col, other_col FROM table_name 
 ) as t
 WHERE row_number BETWEEN 100 AND 120

SELECT team
 FROM results 
 GROUP BY team
 ORDER BY SUM( CASE WHEN points IS NULL THEN 0 ELSE points END ) DESC

INSERT INTO table_name ( DateField ) VALUES ( CURDATE() )

INSERT INTO table_name ( DateField ) VALUES ( getDate() )

truncate table_name

alter table schema.table_name activate not logged initially with empty table

GRANT FILE ON 'database'.* to 'user'@'host'

SELECT id INTO OUTFILE '/temp/uid.csv' 
FIELDS TERMINATED BY ','
FROM user
order by rand()
limit 10;

SELECT @@IDENTITIY

SELECT LAST_INSERT_ID();

SET @lastId=LAST_INSERT_ID();
 INSERT INTO myTable ( 'ref' ) VALUES ( @lastId )

SELECT column, RAND() as IDX 
FROM table 
ORDER BY IDX

Category: SQL