MySQL Learning Notes

--Using regular expressions
--^ Start,. Any character
SELECT name, continent, Population from country WHERE Name REGEXP ' ^. [a-e].* ' ORDER by Name;
--$ end
SELECT name, continent, Population from country WHERE name REGEXP '. *g$ ' ORDER by Name;
SELECT name, continent, Population from country WHERE name REGEXP ' o.o ' ORDER by Name;
--+ match one or more previous characters
SELECT name, continent, Population from country WHERE name REGEXP ' Oc+o ' ORDER by Name;
SELECT name, continent, Population from country WHERE name REGEXP ' Oc*o ' ORDER by Name;
-- ？ Match 0 or more of the previous characters
SELECT name, continent, Population from country WHERE name REGEXP ' Oc?o ' ORDER by Name;
SELECT name, continent, Population from country WHERE Name REGEXP ' ^. [aeiou].* ' ORDER by Name;
SELECT name, continent, Population from country WHERE Name REGEXP ' ^. [a-e].* ' ORDER by Name;
SELECT name, continent, Population from country WHERE Name REGEXP ' ^. [^a-e].* ' ORDER by Name;
--Middle [: space:] indicates matching spaces
SELECT name, continent, Population from country WHERE name REGEXP "[[: Space:]] ' ORDER by Name;
SELECT name, continent, Population from country WHERE Name Rlike "[[: Space:]] ' ORDER by Nam
--You can view the official website to help get more content http://dev.mysql.com/doc/refman/5.6/en/regexp.html

--string
SELECT ' Hello, world ';
SELECT ' Hello ', ' world ';
Select "Hello, World";
Select ' Hello, ' world ';
SELECT ' Hello ', ' world ';
SELECT ' Hello, \ ' world\ ';

--Create a table

DROP TABLE IF EXISTS test;
CREATE TABLE Test (
ID INTEGER NOT NULL default 47,
--ID INTEGER auto_increment primary key,
--Where the integer auto_increment primary key can be shortened to serial (not supported by versions prior to 4.1) the only difference is that the size of the integer changed from 11 to 20
A VARCHAR (255) Unique NOT NULL,
b VARCHAR (255) Unique,
Stamp TIMESTAMP,--Updated with record updates by default
INDEX (a),
FOREIGN KEY (a) REFERENCES client (ID)
);
INSERT into TEST (ID, a, b) VALUES (1, ' One ', ' both ');
INSERT into TEST (ID, a, b) VALUES (2, ' both ', ' three ');
INSERT into TEST (IDs, A, b) VALUES (3, ' Three ', ' four ');

DESCRIBE test; --View table structure
SHOW TABLE STATUS; --Displays the status of all tables in the database
SHOW TABLE STATUS like ' test '; --Show the status of a table
SHOW CREATE TABLE test; --Displays the SQL statement that created the test table
SHOW INDEXES from Test; --Displays the index of the test table
SELECT last_insert_id (); – Show the last inserted ID, note that this is global, so you need to look at the next call after inserting

--ENUM

DROP TABLE IF EXISTS test;
CREATE TABLE Test (
ID SERIAL,
A ENUM (' Pablo ', ' Henri ', ' Jackson ')
);
INSERT into Test (a) VALUES (' Pablo ');
INSERT into Test (a) VALUES (' Henri ');
INSERT into Test (a) VALUES (' Jackson ');
INSERT into Test (a) VALUES (1);
INSERT into Test (a) VALUES (2);
INSERT into Test (a) VALUES (3);

SELECT * from Test;

--SET

DROP TABLE IF EXISTS test;
CREATE TABLE Test (
  ID SERIAL,
  a SET (' Pablo ', ' Henri ', ' Jackson ')
);
INSERT into Test (a) VALUES (' Pablo ');
INSERT into Test (a) VALUES (' Henri ');
INSERT into Test (a) VALUES (' Jackson ');
INSERT into Test (a) VALUES (' Pablo,jackson,henri,henri,henri ');
INSERT into Test (a) VALUES (1);
INSERT into Test (a) VALUES (2);
INSERT into Test (a) VALUES (3);
INSERT into Test (a) VALUES (4);
INSERT into Test (a) VALUES (5);
INSERT into Test (a) VALUES (6);
INSERT into Test (a) VALUES (7);
SELECT COUNT (*) from test;
SELECT * from test;
DESCRIBE test;
SHOW CREATE TABLE test;

Function
Length ()--calculated length bytes
Char_length ()-Calculates the number of length characters, which is longer than length () because the Unicode is resolved
Mid (name,2,3)
Concat (Name, LocalName)--string concatenation
Concat_ws (', ', name,locaname)--You can set the delimiter

SELECT Group_concat (Name) from the country WHERE region = ' Western Europe ';
SELECT Group_concat (Name) from the country GROUP by region;
SELECT Group_concat (DISTINCT continent ORDER by continent SEPARATOR "/") from country;

SELECT SUBSTR (A, 1, 2) as State, SUBSTR (A, 3) as SCode, SUBSTR (b, 1, 2) as country,
SUBSTR (b, 3) as ccode from T;

Locate (' Bar ', ' Foobarbaz ') – shows the position of bar in Foobarbaz
Reverse (name)-Reverse case
Select 7 DIV 3--divisible

SELECT Current_timestamp ();
SELECT Unix_timestamp (); – Represents the number of seconds since 19700101
SELECT Utc_timestamp ();

SELECT Dayname (now ());
SELECT DayOfMonth (now ());
SELECT DAYOFWEEK (now ());
SELECT DayOfYear (now ());
SELECT MONTHNAME (now ());

SELECT time_to_sec (' 00:03:00 '); --Time becomes second
SELECT Sec_to_time (180); --Seconds to change time

SELECT addtime (' 1:00:00 ', ' 00:29:45 ');
SELECT subtime (' 1:30:00 ', ' 00:15:00 ');

SELECT adddate (' 2008-01-02 ', INTERVAL Day);
SELECT subdate (' 2008-01-02 ', INTERVAL Day);

--Time zone
--You can find all time zone listings in Https://en.wikipedia.org/wiki/List_of_tz_database_time_zones
SHOW VARIABLES like '%time_zone% ';
SELECT now ();
SET time_zone = ' Us/eastern ';
SHOW VARIABLES like '%time_zone% ';
SELECT now ();

--Time format
SELECT Date_format (now (), '%W,%d of%M,%Y ');
SELECT Date_format (now (), '%y-%m-%d%T ');

--Business
--using transactions can increase performance
START TRANSACTION;
INSERT into WidgetSales (inv_id, Quan, Price) VALUES (1, 5, 500);
UPDATE widgetinventory SET Onhand = (onhand-5) WHERE id = 1;
COMMIT;

START TRANSACTION;
INSERT into Widgetinventory (description, Onhand) VALUES (' toy ', 25);
ROLLBACK;

--Trigger
--Updating a table with a trigger

CREATE TABLE Widgetcustomer (id SERIAL, name VARCHAR (255), last_order_id BIGINT);
CREATE TABLE Widgetsale (id SERIAL, item_id BIGINT, customer_id BIGINT, Quan INT, Price DECIMAL (9,2));

INSERT into Widgetcustomer (name) VALUES (' Bob ');
INSERT into Widgetcustomer (name) VALUES (' Sally ');
INSERT into Widgetcustomer (name) VALUES (' Fred ');

SELECT * from Widgetcustomer;

CREATE TRIGGER Newwidgetsale after INSERT on Widgetsale
For each ROW
UPDATE widgetcustomer SET last_order_id = new.id WHERE id = new.customer_id
;

INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (1, 3, 5, 19.95);
INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (2, 2, 3, 14.95);
INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (3, 1, 1, 29.95);
SELECT * from Widgetsale;
SELECT * from Widgetcustomer;

--Preventing Automatic updates with a trigger
DROP TABLE IF EXISTS Widgetsale;

CREATE TABLE Widgetsale (id SERIAL, item_id BIGINT, customer_id BIGINT, Quan int, price DECIMAL (9,2), reconciled int);
INSERT into Widgetsale (item_id, customer_id, Quan, price, reconciled) VALUES (1, 3, 5, 19.95, 0);
INSERT into Widgetsale (item_id, customer_id, Quan, price, reconciled) VALUES (2, 2, 3, 14.95, 1);
INSERT into Widgetsale (item_id, customer_id, Quan, price, reconciled) VALUES (3, 1, 1, 29.95, 0);
SELECT * from Widgetsale;

--The following delimiter allows a semicolon to appear in the statement and not be executed as the end of the statement.
DELIMITER//
CREATE TRIGGER Updatewidgetsale before UPDATE on Widgetsale
For each ROW
BEGIN
IF (SELECT reconciled from Widgetsale WHERE id = new.id) > 0 Then
--here signal xxxx is throwing an exception
SIGNAL SQLSTATE ' 45000 ' SET message_text = ' error:cannot update reconciled row in Widgetsale ';
END IF;
END
//
DELIMITER;

START TRANSACTION;
UPDATE widgetsale SET Quan = Quan + 9 WHERE id = 2;
COMMIT;

SELECT * from Widgetsale;

--Alternate error code for MySQL versions prior to 5.5
IF (SELECT reconciled from Widgetsale WHERE id = new.id) > 0 Then
Update ' Error:cannot update reconciled row in Widgetsale ' SET x=1;
END IF;

--Logging transactions with a trigger

DROP TABLE IF EXISTS Widgetsale;

CREATE TABLE Widgetsale (id SERIAL, item_id BIGINT, customer_id BIGINT, Quan INT, Price DECIMAL (9,2));
CREATE TABLE widgetlog (id SERIAL, stamp TIMESTAMP, event VARCHAR (255), username VARCHAR (255),
TableName VARCHAR (255), table_id BIGINT);

DELIMITER//
CREATE TRIGGER Stampsale after INSERT on Widgetsale
For each ROW
BEGIN
UPDATE widgetcustomer SET last_order_id = new.id
WHERE widgetcustomer.id = new.customer_id;
INSERT into Widgetlog (event, username, TableName, table_id)
VALUES (' INSERT ', ' TRIGGER ', ' Widgetsale ', new.id);
END
//
DELIMITER;

INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (1, 3, 5, 19.95);
INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (2, 2, 3, 14.95);
INSERT into Widgetsale (item_id, customer_id, Quan, Price) VALUES (3, 1, 1, 29.95);

Stored Routines
--Stored functions

DROP FUNCTION IF EXISTS Track_len;
CREATE FUNCTION Track_len (seconds INT)
RETURNS VARCHAR (deterministic)
RETURN concat_ws (': ', seconds DIV., Lpad (seconds MOD 60, 2, ' 0 '));

SELECT title, Track_len (duration) from track;

--Stored procedures

--With parameter
DROP PROCEDURE IF EXISTS list_albums;

DELIMITER//
CREATE PROCEDURE List_albums (a VARCHAR (255))
BEGIN
SELECT a.artist as artist,
A.title as album,
T.title as track,
T.track_number as Trackno,
Track_len (t.duration) as length
From track as T
JOIN album as a
On a.id = t.album_id
WHERE a.artist like a
ORDER by artist, album, Trackno
;
END//

DELIMITER;
Call List_albums ('%hendrix% ');

--With output parameter
DROP PROCEDURE IF EXISTS total_duration;

DELIMITER//
CREATE PROCEDURE total_duration (a varchar (255), out D varchar (255))
BEGIN
SELECT Track_len (SUM (duration)) into D
From track
where album_id in (SELECT ID from album WHERE artist a)
;
END//

DELIMITER;
Call Total_duration ('%hendrix% ', @dur);
SELECT @dur;

MySQL Learning Notes