MySQL

User
- INSERT INTO user (host, user, password, select_priv) VALUES('%', 'lin', PASSWORD('password'), 'Y'), add a user into mysql.user table, % means all host, PASSWORD to encrypt the password, create user and grant global privileges
- CREATE USER 'lin'@'%' IDENTIFIED BY 'password')
- DROP USER 'lin'@'%', remove a user
- FLUSH PRIVILEGES, make the privileges work
- GRANT ALL PRIVILEGES ON databaseName.tableName to 'lin'@'%'; setup privileges to a user
- REVOKE ALL PRIVILEGES ON *.* FROM 'lin'@'localhost'; revoke privileges from a user
- SHOW GRANTS FOR 'lin'@'%', show the privileges of a user
Database
- /Applications/AMPPS/mysql/bin/mysql -u root -p, login mysql system
- show databases;, show all available databases
- use databaseName, switch database
- select database(), show the current database
- show tables;, list all tables in a database
- database mysql contains all database information
- create database databaseName, create a database
- drop database databaseName, remove a database
Data type
- Numeric
- Data and Time
- VARCHAR(M)
- BLOB or TEXT, save images or other types of files
  - BLOB: up to 64KBytes
  - MEDIUMBLOB: up to 16MBytes
  - LONGBOLB: up to 4GBytes
- ENUM
  - associated with an index, beginning with 1 for the first allowable value
  - the empty string has index of 0
- SET
  - could choose zero (empty string) or more values from the allowable values

Table

describe tableName, show table structure
show columns from tableName, show the definition of columns
show index from tableName, show the index of a table
select * from databae.table, select all column from a table
explain select * from database.table, explain records
drop table, remove table
insert into tableName (...) values (...), insert record into table
update tableName set columnName = value where, update record
delete from tableName where, remove a record
LIKE, case insensitve
- %, zero, one or multiple character
- _, one character
REGEXP, regular expression
select (...) from tableName limit number offset n, limit the number of display records and figure out the start record
order by [ASC|DESC], sort records
group by columnName, group records
having, set up conditions
distinct, eliminate all duplicate records
create table, create database table

USE University;

DROP TABLE IF EXISTS Department;
CREATE TABLE Department (
	Dname VARCHAR(15) NOT NULL,
	Dnumber INT NOT NULL,
	Mgr_ssn CHAR(9) NOT NULL,
	Mgr_start_date DATE,
	PRIMARY KEY(Dnumber),
	UNIQUE(Dname),
	INDEX (Mgr_ssn)
	-- FOREIGN Key(Mgr_ssn) REFERENCES Employee(Ssn) ON DELETE RESTRICT ON UPDATE CASCADE, not able to add foreign due to that Employee table is not created yet
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Department table';

DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee(
	Fname VARCHAR(15) NOT NULL,
	Minit CHAR(1),
	Lname VARCHAR(15) NOT NULL,
	Ssn CHAR(9) NOT NULL,
	Bdate DATE,
	Address VARCHAR(30),
	Sex CHAR(1),
	Salary DECIMAL(10, 2),
	Super_ssn CHAR(9),
	Dno INT NOT NULL,
	PRIMARY KEY(Ssn),
	FOREIGN KEY(Super_ssn) REFERENCES Employee(Ssn) ON DELETE SET NULL ON UPDATE CASCADE,
	FOREIGN KEY(Dno) REFERENCES Department(Dnumber) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Employee table';

ALTER TABLE Department
    ADD CONSTRAINT mgrEmp
    FOREIGN KEY(Mgr_ssn)
    REFERENCES Employee(Ssn) ON DELETE RESTRICT ON UPDATE CASCADE

USE University;

DROP TABLE IF EXISTS Dept_locations;
CREATE TABLE Dept_locations (
	Dnumber INT NOT NULL,
	Dlocation VARCHAR(15) NOT NULL,
	PRIMARY KEY(Dnumber, Dlocation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Department locations';

ALTER TABLE Dept_locations
    ADD CONSTRAINT DepLoc
    FOREIGN KEY(Dnumber)
    REFERENCES Department(Dnumber) ON DELETE RESTRICT ON UPDATE CASCADE

USE University;

DROP TABLE IF EXISTS Project;
CREATE TABLE Project (
	Pname VARCHAR(15) NOT NULL,
	Pnumber INT NOT NULL,
	Plocation VARCHAR(15),
	Dnum INT NOT NULL,
	PRIMARY KEY(Pnumber),
	UNIQUE(Pname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Project table';

ALTER TABLE Project
    ADD CONSTRAINT ProjDept
    FOREIGN KEY(Dnum)
    REFERENCES Department(Dnumber) ON DELETE RESTRICT ON UPDATE CASCADE

USE University;

DROP TABLE IF EXISTS Works_on;
CREATE TABLE Works_on (
	Essn CHAR(9) NOT NULL,
	Pno INT NOT NULL,
	Hours DECIMAL(3,1) NOT NULL,
	PRIMARY KEY(Essn, Pno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Works_on table';

ALTER TABLE Works_on
    ADD CONSTRAINT EmpWorks
    FOREIGN KEY(Essn)
    REFERENCES Employee(Ssn) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE Works_on
    ADD CONSTRAINT WorkProj
    FOREIGN KEY(Pno)
    REFERENCES Project(Pnumber) ON DELETE RESTRICT ON UPDATE CASCADE;

USE University;

DROP TABLE IF EXISTS Dependent;
CREATE TABLE Dependent (
	Essn CHAR(9) NOT NULL,
	Name VARCHAR(15) NOT NULL,
	Sex CHAR,
	Bdate DATE,
	Relationship VARCHAR(8),
	PRIMARY KEY(Essn, Name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Works_on table';

ALTER TABLE Dependent
    ADD CONSTRAINT EmpDep
    FOREIGN KEY(Essn)
    REFERENCES Employee(Ssn) ON DELETE RESTRICT ON UPDATE CASCADE;

FOREIGN KEY (foreignKey) REFERENCES tableName(primaryKey) ON DELETE deleteOption ON UPDATE options
- SET NULL, set as NULL
- RESTRICT, rejects the delete or update operation for the parent table
- NO ACTION, equivalent to RESTRICT
- CASCADE, delete or update the row from the parent table, and automatically delete or update the matching rows in the child table
- InnoDB permits a foreign key to reference any index column or group of columns
Constraints
- Not Null
- Default
- Unique
- Primary Key
- Foreign Key
- Check

CHECK, are not supported by MySQL, use trigger instead
Assertion, MySQL doesn't support assertions, use trigger instead

Input and output

Create tables, insert records, then put constraints to avoid the reference key contains of ON UPDATE from loop contraints

# insert Employee
use University;

LOCK TABLES Employee WRITE;
insert into Employee values
('John','B','Smith','123456789','1965-01-09',"731 Fondren, Houston, TX",'M',30000,333445555,5);
('Franklin','T','Wong','333445555','1955-12-08',"638 Voss, Houston, TX",'M',40000,888665555,5),
('Alicia','J','Zelaya','999887777','1968-01-19',"3321 Castle, Spring, TX",'F',25000,987654321,4),
('Jennifer','S','Wallace','987654321',1941-06-20,"291 Berry, Bellaire, TX",'F',43000,888665555,4),
('Ramesh','K','Narayan','666884444','1962-09-15',"975 Fire Oak, Humble, TX",'M',38000,333445555,5),
('Joyce','A','English','453453453','1972-07-31',"5631 Rice, Houston, TX",'F',25000,333445555,5),
('Ahmad','V','Jabbar','987987987','1969-03-29',"980 Dallas, Houston, TX",'M',25000,987654321,4),
('James','E','Borg','888665555','1937-11-10',"450 Stone, Houston, TX",'M',55000,NULL,1);
UNLOCK TABLES;

Backup and recovery

mysqldump -u root -p databaseName > backup.sql #backup a database

mysqldump -u root -p databaseName tableName > backupTable.sql #backup a table

create database databaseName
mysql -u root -p databaseName < backup.sql # read the backup to the database

Script

Write sql script in a .sql file
mysql -u userName -p -t -vvv < fileName.sql

use mysql;
select user from user;

mysql -u myuser -p -vvv -e "Use mysql; SELECT user, host FROM user; SHOW databases", implement scripts on the command line
mysql> SOURCE d:/myproject/sqlscripts/mytestscript.sql, implement *.sql file in the mysql environment
Comments
- /* ... */, multiple line comment
- #, single line comment
Case sensitive
- MySQL keywords are not case-sensitive
- identifiers are case-sensitive

Variables

User-defined variables

# s2.sql
SET @a := 1;
select @a; # print out the value @a

select @b := user from mysql.user; # fetch user from table mysql.user
select @b; # b contains the last value of user

System variables

global variables, affect the overall operation of the server
session variables, affect individual client connections

SET GLOBAL sort_buffer_size = 1000000;

show GLOBAL VARIABLES; # show all global variables
show session variables; # show all session variables

select @@global.sort_buffer_size;
select @@session.sort_buffer_size;

Local variables

Character sets and collations

character set, defines a set of character and maps each character to a unique numeric code
- SHOW CHARACTER SET;
collating Sequences, refers to the orders in which individual characters should be ranked in comparing or sorting the strings
- SHOW COLLATION;

CREATE DATABASE databaseName
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS StringArena (
   cString1 VARCHAR(50) CHARACTER SET latin1 COLLATE latin1_general_cs,  -- case-sensitive 'A' -> 'a' -> 'B' -> 'b'
   cString2 CHAR(10) CHARACTER SET latin1,                               -- default collation of latin1_swedish_ci
   cString3 TEXT
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;                    -- case-insensitive 'A' and 'a' are the same

Procedure

SHOW PROCEDURE, show procedures

# a simple procedure
use mysql;

drop procedure if exists getUser;

DELIMITER //

create procedure getUser()
begin
	select host from user;
end//

DELIMITER ;

call getUser();

# s5.sql
use mysql;

#show procedure status;
drop procedure if exists getCurrentHost;

DELIMITER //

create procedure getCurrentHost(IN userName varchar(15), OUT hostName varchar(15))
begin
	select @hostName = host as host from user where user = userName;
end//

DELIMITER ;

call getCurrentHost('root', @hostName);
select @hostName;

source s5.sql; # import s5.sql
call getCurrentHost('lin', @hostName);
select @hostName;

Function

a function returns a scalar value, via the statement RETURN. All parameters are IN parameters

use mysql;

#show function status;
drop function if exists getCurrentHost;

DELIMITER //
CREATE FUNCTION getCurrentHost (name VARCHAR(20)) RETURNS VARCHAR(30)
BEGIN
	declare test varchar(20);
	select host into test from user where user = name;
	return test;
END//
DELIMITER ;

select getCurrentHost('lin');

Views

a view is a preset query stored in a database

use mysql;

drop view if exists users;

create view users
as
select * from user;

select host from users;

Trigger

a event handler that executes in response to an event

use Temp;

drop trigger if exists employeeID;

DELIMITER //
CREATE TRIGGER employeeID
BEFORE INSERT ON Temp.employee FOR EACH ROW
BEGIN
  if new.ID > 10 then
	set new.ID := 100;
   end if;
END//

DELIMITER ;

insert into Temp.employee values(20, 'test', 19, 970124678);

Events

a set of stored SQL statements that get executed at the scheduled date and time

use Temp;
SET @@global.event_scheduler = ON;
CREATE EVENT updateEmployeeID
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
   DO
      UPDATE employee SET ID = id * 10;

Download University Database SQL

Create table

Create foreign key