MySQL Learning Notes

Source: Internet
Author: User

--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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.