Retrieve special character in MySQL

쓸만한게없네 2015. 10. 1. 23:30

2015. 10. 1. 23:30

https://dev.mysql.com/doc/refman/5.6/en/string-literals.html

Retrieve special character in MySQL.

ex.

DROP TEMPORARY TABLE IF EXISTS CharTest;

CREATE TEMPORARY TABLE CharTest

(value varchar(100));

INSERT INTO CharTest VALUES

('ABXX'), ('ACYY'),

('A%BXX'), ('A%CYY'),

('A\\BXX'), ('A\\CYY'),

('A''BXX'), ('A''CYY'),

('A/BXX'), ('A/CYY')

;

SELECT * FROM CharTest;

+-------+

| value |

+-------+

| ABXX |

| ACYY |

| A%BXX |

| A%CYY |

| A\BXX |

| A\CYY |

| A'BXX |

| A'CYY |

| A/BXX |

| A/CYY |

+-------+

10 rows in set (0.00 sec)

If you want to search data start with "A%".

It will not work properly as below.

SELECT * FROM CharTest where value like 'A%';

+-------+

| value |

+-------+

| ABXX |

| ACYY |

| A%BXX |

| A%CYY |

| A\BXX |

| A\CYY |

| A'BXX |

| A'CYY |

| A/BXX |

| A/CYY |

+-------+

10 rows in set (0.00 sec)

The result is exactly same data as start with "A".

So you need to use some escape sequence as below.

SELECT * FROM CharTest where value like 'A$%B%' ESCAPE '$';

+-------+

| value |

+-------+

| A%BXX |

+-------+

1 row in set (0.00 sec)

SELECT * FROM CharTest where value like 'A\\B%' ESCAPE '$';

+-------+

| value |

+-------+

| A\BXX |

+-------+

1 row in set (0.00 sec)

SELECT * FROM CharTest where value like 'A''B%';

+-------+

| value |

+-------+

| A'BXX |

+-------+

1 row in set (0.00 sec)

SELECT * FROM CharTest where value like 'A/B%';

+-------+

| value |

+-------+

| A/BXX |

+-------+

1 row in set (0.00 sec)

As you can see, "/" is not a special character.

저작자표시 비영리

Concat string, Split string

쓸만한게없네 2015. 9. 16. 21:41

2015. 9. 16. 21:41

##1. Concat String with delimiter

GROUP_CONCAT

https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html

ex.

DROP TABLE IF EXISTS group_concat_test;

CREATE TABLE group_concat_test

(

SEQ INT NOT NULL AUTO_INCREMENT,

CITY VARCHAR(20),

PRIMARY KEY `pk_group_concat_test` (SEQ)

) ENGINE = MEMORY;

INSERT INTO group_concat_test (CITY) VALUES ('Luxembourg') ,('London'), ('Seoul'), ('Paris');

SELECT GROUP_CONCAT(CITY) AS ALL_CITY_NAMES

FROM group_concat_test;

+-------------------------------+

| ALL_CITY_NAMES |

+-------------------------------+

| Luxembourg,London,Seoul,Paris |

+-------------------------------+

1 row in set (0.00 sec)

SELECT GROUP_CONCAT(CITY SEPARATOR '#') AS ALL_CITY_NAMES

FROM group_concat_test;

+-------------------------------+

| ALL_CITY_NAMES |

+-------------------------------+

| Luxembourg#London#Seoul#Paris |

+-------------------------------+

1 row in set (0.00 sec)

SELECT GROUP_CONCAT(CITY ORDER BY CITY SEPARATOR '$') AS ALL_CITY_NAMES

FROM group_concat_test;

+-------------------------------+

| ALL_CITY_NAMES |

+-------------------------------+

| London$Luxembourg$Paris$Seoul |

+-------------------------------+

1 row in set (0.00 sec)

DROP TABLE IF EXISTS group_concat_test;

##2. Split String Array using specific delimiter

#Create a procedure and call.

DELIMITER $$

DROP PROCEDURE IF EXISTS usp_split_array_test$$

CREATE PROCEDURE usp_split_array_test(

iValueArray VARCHAR(100) # Input array value ex) 'value1, value2, value3'

,iDelimiter CHAR(1) # Delimiter

)

BEGIN

DECLARE vValueArry VARCHAR(100);

DECLARE vValue VARCHAR(10);

DECLARE vDelimiter CHAR(1);

SET vDelimiter := iDelimiter; #Variable for delimiter

IF IFNULL(vDelimiter,'') = '' THEN

SET vDelimiter := ',';

END IF;

SET vValueArry := iValueArray; #Variable for split work

IF RIGHT(vValueArry,1) != vDelimiter THEN

SET vValueArry := CONCAT(vValueArry,vDelimiter); # 'value1, value2, value3' --> 'value1, value2, value3,'

END IF;

DROP TEMPORARY TABLE IF EXISTS temp_split_array;

CREATE TEMPORARY TABLE temp_split_array

(

SEQ INT NOT NULL AUTO_INCREMENT,

VALUE VARCHAR(20),

PRIMARY KEY `PK_temp_split_array` (SEQ)

) ENGINE = MEMORY;

#SET @vValueArry := 'value1, value2, value3,'

WHILE (LOCATE(vDelimiter, vValueArry) > 0)

SET vValue := LEFT(vValueArry, LOCATE(vDelimiter,vValueArry) - 1);

SET vValueArry := SUBSTRING(vValueArry, LOCATE(vDelimiter,vValueArry) + 1);

INSERT INTO temp_split_array (SEQ, VALUE)

VALUES (null, vValue);

END WHILE;

SELECT * FROM temp_split_array;

DROP TEMPORARY TABLE IF EXISTS temp_split_array;

END$$

DELIMITER ;

CALL usp_split_array_test('Luxembourg,London,Seoul,Paris', ',');

+-----+------------+

| SEQ | VALUE |

+-----+------------+

| 1 | Luxembourg |

| 2 | London |

| 3 | Seoul |

| 4 | Paris |

+-----+------------+

4 rows in set (0.00 sec)

CALL usp_split_array_test('Luxembourg;London;Seoul;Paris', ';');

+-----+------------+

| SEQ | VALUE |

+-----+------------+

| 1 | Luxembourg |

| 2 | London |

| 3 | Seoul |

| 4 | Paris |

+-----+------------+

4 rows in set (0.01 sec)

저작자표시 비영리

(My)SQL Cheat Sheet

쓸만한게없네 2015. 9. 12. 14:17

2015. 9. 12. 14:17

(My)SQL Cheat Sheet

Here are the most commonly used SQL commands and the most commonly used options for each. There are many more commands and options than listed here. In other words, the syntaxes as I have listed them are far from complete. See the links at the bottom for more complete syntaxes and more commands.

MySQL Command-Line
What	How	Example(s)
Running MySQL	mysql -uusername -ppassword	mysql -ucusack2RO -pegbdf5s
Importing	mysql -uusername -ppassword < filename	mysql -usomeDB -pblah < myNewDB.sql
Dumping (Saving)	mysqldump -uusername -ppassword database [tables] > filename	mysqldump -ume -pblah myDB > My.sql mysqldump -ume -pblah myDB table1 table2 > my.sql

Common MySQL Column Types
Purpose	Data Type	Example
Integers	int(M)	int(5)
Floating-point (real) numbers	float(M,D)	float(12,3)
Double-precision floating-point	double(M,D)	double(20,3)
Dates and times	timestamp(M)	timestamp(8) (for YYYYMMDD) timestamp(12) (for YYYYMMDDHHMMSS)
Fixed-length strings	char(M)	char(10)
Variable-length strings	varchar(M)	varchar(20)
A large amount of text	blob	blob
Values chosen from a list	enum('value1',value2',...)	enum('apples','oranges','bananas')

M is maximum to display, and D is precision to the right of the decimal.

MySQL Mathematical Functions
What	How
Count rows per group	COUNT(column \| *)
Average value of group	AVG(column)
Minumum value of group	MIN(column)
Maximum value of group	MAX(column)
Sum values in a group	SUM(column)
Absolute value	abs(number)
Rounding numbers	round(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)
nth power	pow(base,exponent)
random number n, 0<n < 1	rand()
sin (similar cos, etc.)	sin(number)

MySQL String Functions
What	How
Compare strings	strcmp(string1,string2)
Convert to lower case	lower(string)
Convert to upper case	upper(string)
Left-trim whitespace (similar right)	ltrim(string)
Substring of string	substring(string,index1,index2)
Encrypt password	password(string)
Encode string	encode(string,key)
Decode string	decode(string,key)
Get date	curdate()
Get time	curtime()
Extract day name from date string	dayname(string)
Extract day number from date string	dayofweek(string)
Extract month from date string	monthname(string)

Basic MySQL Commands
What	How	Example(s)
List all databases	SHOW DATABASES;	SHOW DATABASES;
Create database	CREATE DATABASE database;	CREATE DATABASE PhoneDB;
Use a database	USE database;	USE PhonDB;
List tables in the database	SHOW TABLES;	SHOW TABLES;
Show the structure of a table	DESCRIBE table; SHOW COLUMNS FROM table;	DESCRIBE Animals; SHOW COLUMNS FROM Animals;
Delete a database (Careful!)	DROP DATABASE database;	DROP DATABASE PhoneDB;

SQL Commands: Modifying
What	How	Example(s)
Create table	CREATE TABLE table ( column1 type [[NOT] NULL] [AUTO_INCREMENT], column2 type [[NOT] NULL] [AUTO_INCREMENT], ... other options, PRIMARY KEY (column(s)) );	CREATE TABLE Students ( LastName varchar(30) NOT NULL, FirstName varchar(30) NOT NULL, StudentID int NOT NULL, Major varchar(20), Dorm varchar(20), PRIMARY KEY (StudentID) );
Insert data	INSERT INTO table VALUES (list of values); INSERT INTO table SET column1=value1, column2=value2, ... columnk=valuek; INSERT INTO table (column1,column2,...) VALUES (value1,value2...);	INSERT INTO Students VALUES ('Smith','John',123456789,'Math','Selleck'); INSERT INTO Students SET FirstName='John', LastName='Smith', StudentID=123456789, Major='Math'; INSERT INTO Students (StudentID,FirstName,LastName) VALUES (123456789,'John','Smith');
Insert/Select	INSERT INTO table (column1,column2,...) SELECT statement; (See below)	INSERT INTO Students (StudentID,FirstName,LastName) SELECT StudentID,FirstName,LastName FROM OtherStudentTable; WHERE LastName like '%son';
Delete data	DELETE FROM table [WHERE condition(s)]; (Omit WHERE to delete all data)	DELETE FROM Students WHERE LastName='Smith'; DELETE FROM Students WHERE LastName like '%Smith%'; AND FirstName='John'; DELETE FROM Students;
Updating Data	UPDATE table SET column1=value1, column2=value2, ... columnk=valuek [WHERE condition(s)];	UPDATE Students SET LastName='Jones' WHERE StudentID=987654321; UPDATE Students SET LastName='Jones', Major='Theatre' WHERE StudentID=987654321 OR (MAJOR='Art' AND FirstName='Pete');
Insert column	ALTER TABLE table ADD COLUMN column type options;	ALTER TABLE Students ADD COLUMN Hometown varchar(20);
Delete column	ALTER TABLE table DROP COLUMN column;	ALTER TABLE Students DROP COLUMN Dorm;
Delete table (Careful!)	DROP TABLE [IF EXISTS] table;	DROP TABLE Animals;

SQL Commands: Querying
What	How	Example(s)
All columns	SELECT * FROM table;	SELECT * FROM Students;
Some columns	SELECT column1,column2,... FROM table;	SELECT LastName, FirstName FROM Students;
Some rows/ columns	SELECT column1,column2,... FROM table [WHERE condition(s)];	SELECT LastName,FirstName FROM Students WHERE StudentID LIKE '%123%';
No Repeats	SELECT [DISTINCT] column(s) FROM table;	SELECT DISTINCT LastName FROM Students;
Ordering	SELECT column1,column2,... FROM table [ORDER BY column(s) [DESC]];	SELECT LastName,FirstName FROM Students ORDER BY LastName, FirstName DESC;
Column Aliases	SELECT column1 [AS alias1], column2 [AS alias2], ... FROM table1;	SELECT LastName,FirstName AS First FROM Students;
Grouping	SELECT column1,column2,... FROM table [GROUP BY column(s)];	SELECT LastName,COUNT(*) FROM Students GROUP BY LastName;
Group Filtering	SELECT column1,column2,... FROM table [GROUP BY column(s)] [HAVING condition(s)];	SELECT LastName,COUNT(*) FROM Students GROUP BY LastName HAVING LastName like '%son';
Joins	SELECT column1,column2,... FROM table1,table2,... [WHERE condition(s)];	SELECT LastName,Points FROM Students,Assignments WHERE AssignmentID=12 AND Students.StudentID=Assignments.StudentID;
Table Aliases	SELECT column1,column2,... FROM table1 [alias1], table2 [alias2],... [WHERE condition(s)];	SELECT LastName,Points FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12;
Everything	SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2], ... FROM table1 [alias1], table2 [alias2],... [WHERE condition(s)] [GROUP BY column(s)] [HAVING condition(s)] [ORDER BY column(s) [DESC]];	SELECT Points, COUNT(*) AS Cnt FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12 GROUP BY Points HAVING Points > 10 ORDER BY Cnt, Points DESC;