MYSQL
mysql
What is the First Normal Form in SQL
For a database to satisfy the First Normal Form, it must fulfill three requirements:
1. There should be no repeating columns containing the same kind of data.
2. All columns should contain a single value.
3. There should be a primary key to uniquely identify each row.
Example of IN and NOT IN SQL Queries
SELECT id FROM stats WHERE POSITION IN ('Manager', 'Staff') SELECT ownerid, 'is in both orders & antiques' FROM orders, antiques WHERE ownerid = buyerid UNION SELECT buyerid, 'is in antiques only' FROM antiques WHERE buyerid NOT IN (SELECT ownerid FROM orders)
what are DDL, DML and DQL?
SQL string function REPLACE()
SQL string function REPLACE()
Syntax:
REPLACE(column_name, Find, Replace)
Description:
Used to replace an occurence of string/character in column data
Parameters:
Column_name – specifies the column to be manipulated
Find – string to be matched
Replace – string to replace the matched string
Example:
SELECT name, REPLACE (name, 'm', 'M') FROM product;
SQL string function SUBSTRING()
SQL string function SUBSTRING()
Syntax:
SUBSTRING(column_name, Start, Length).
Description:
Find the SUBSTRING from column data
Parameters:
Column_name – specifies the column to be manipulated
Start – Offset on the column
Length – Number of characters to be extracted
Example:
SELECT SUBSTRING(name,3,5) FROM product;
SQL string function REVERSE()
SQL string function REVERSE()
Syntax:
REVERSE(column_name)
Description:
Find the REVERSE of column data
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT REVERSE(name) FROM product;
SQL string function LENGTH()
SQL string function LENGTH()
Syntax:
LENGTH(column_name)
Description:
Find the length of column data
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT name, price FROM product WHERE LENGTH(name)> 10;
SQL string function RTRIM()
SQL string function RTRIM()
Syntax:
RTRIM(column_name)
Description:
Strips whitespace from ending of column data
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT RTRIM(name) FROM Product;
SQL string function LTRIM()
SQL string function LTRIM()
Syntax:
LTRIM(column_name)
Description:
Strips whitespace from beginning of column data
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT LTRIM(name) FROM Product;
SQL string function LOWER()
SQL string function LOWER()
Syntax:
LOWER(column_name)
Description:
Change the text to all lowercase.
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT LOWER(name) FROM Product;
SQL string function UPPER()
SQL string function UPPER()
Syntax:
UPPER (column_name)
Description:
Change the text to all uppercase.
Parameters:
Column_name – specifies the column to be manipulated
Example:
SELECT UPPER (name) FROM Product;
Join two MySQL tables together
SELECT a.column1, b.column1, b.column2 FROM tablename a JOIN tablename b ON a.column1 = b.column1 WHERE a.column1 LIKE '%something%'
Using MySQL SELECT to count rows in a table
MySQL count() Returns a count of the total number of rows in a table.
SELECT COUNT(*) from TableName
Using MySQL SELECT to return first 10 rows
To return the first 10 rows we should run a SQL Select statement and limit the number of rows returned. This is useful when we only want to return a few rows to do some initial analysis.
SELECT column1, column2, column3 FROM tblname LIMIT 10
MySQL String Functions
BINARY
string1=BINARY(string2)
Returns the binary representation of a string. This function can be used to force case-sensitive comparisons when they would otherwise not occur.
BIT_LENGTH
bits=BIT_LENGTH(string)
Returns the number of bits in a string.
CHAR_LENGTH
length=CHAR_LENGTH(string)
Returns the number of characters in a string. Like LENGTH, except that it returns the number of characters, rather than the number of bytes, for multibyte character sets.
CHARACTER_LENGTH
length=CHARACTER_LENGTH(string)
Alias for CHAR_LENGTH.
COMPRESS
string1=COMPRESS(string2)
Returns a compressed version of a string.
DECODE
string1=DECODE(string2,password)
Decrypts a string that has been encrypted with ENCRYPT .
ELT
string1=ELT(number,string2[,...])
Returns one of the elements in a list.
ENCODE
string1=ENCODE(string2,password)
Encrypts a string. The string can be decrypted with DECODE.
ENCRYPT
string1=ENCRYPT(string2,seed)
Encrypts a string. The string cannot be decrypted with DECODE.
EXPORT_SET
string=ENCODE_SET(number,on_string, off_string,seperator,no_of_bits)
Returns the binary representation of a number encoded with strings for on and off bits.
FIELD
number=FIELD(string1,string2[,...])
Searches for a string in a list of strings.
INET_ATON
number=INET_ATON(IPAddress)
Converts an IP address into a numeric representation.
INET_NTOA
IPAddress=INET_NTOA(number)
Converts a number into a corresponding IP address.
LOWER
string1=LOWER(string2)
Synonym for LCASE.
MID
string1=MID(string2,start [,length])
Returns a substring. Similar to SUBSTR.
OCTET_LENGTH
length=OCTET_LENGTH(string)
Alias for LENGTH.
ORD
position=ORD(string)
Returns the ordinal value of the character in the ASCII character set.
PASSWORD
string1=PASSWORD(string2)
Encrypts the given string as a MySQL password.
POSITION
position=POSITION(substring IN string)
Returns the position of the substring in the string. Similar to LOCATE.
QUOTE
string1=QUOTE(string2)
Returns a string with special characters preceded by an escape character.
REVERSE
string1=REVERSE(string2)
Reverses the order of characters in a string.
RIGHT
string1=RIGHT(string2,length)
Returns the rightmost portion of a string.
SHA
string1=SHA(string2)
Returns a 160-bit Secure Hash Algorithm (SHA) checksum for the string.
SHA1
string1=SHA1(string2)
Alias for SHA.
SOUNDEX
string1=SOUNDEX(string2)
Returns the SOUNDEX for a string. In theory, two strings that “sound alike” will have similar SOUNDEX values.
SPACE
spaces=SPACE(count)
Returns the specified number of space characters.
SUBSTRING_INDEX
string1=SUBSTRING_INDEX(string2, delimiter,count)
Returns a string from a character-delimited set of strings.
UNCOMPRESSED_LENGTH
length=UNCOMPRESSED_LENGTH( compressed_string)
Returns the length of a compressed string as if it were decompressed.
UNCOMPRESS
string1=UNCOMPRESS(string2)
Reverses the effect of COMPRESS.
UNHEX
character=UNHEX(HexNumber)
Converts a hexadecimal number to its ASCII equivalent.
UPPER
string1=UPPER(string2)
Converts a string to uppercase. Synonym for UCASE.
MySQL ASCII Function
The ASCII function in MySQL returns the ASCII character code corresponding to the first character in the provided input string.
Example for using the ASCII function
CREATE FUNCTION ascii_string (in_string VARCHAR(80) ) RETURNS VARCHAR(256) DETERMINISTIC BEGIN DECLARE i INT DEFAULT 1; DECLARE string_len INT; DECLARE out_string VARCHAR(256) DEFAULT ''; SET string_len=LENGTH(in_string); WHILE (i<string_len) DO SET out_string=CONCAT(out_string,ASCII(SUBSTR(in_string,i,1)),' '); SET i=i+1; END WHILE; RETURN (out_string); END
Executing the function
>SELECT ascii_string('MySQL Rocks!') +---------------------------------------+ | ascii_string('MySQL Rocks!') | +---------------------------------------+ | 77 121 83 81 76 32 82 111 99 107 115 | +---------------------------------------+ 1 row in set (0.00 sec)
Advantages of using stored procedures
The use of stored programs can lead to a more secure database.
Stored programs offer a mechanism to abstract data access routines, which can improve the maintainability of your code as underlying data structures evolve.
Stored programs can reduce network traffic, because the program can work on the data from within the server, rather than having to transfer the data across the network.
Stored programs can be used to implement common routines accessible from multiple applicationspossibly using otherwise incompatible frameworksexecuted either within or from outside the database server.
Database-centric logic can be isolated in stored programs and implemented by programmers with more specialized, database experience.
The use of stored programs can, under some circumstances, improve the portability of your application.
What are triggers?
Triggers are stored programs that are activated in response to, or are triggered by, an activity within the database. Typically, a trigger will be invoked in response to a DML operation (INSERT, UPDATE, DELETE) against a database table. Triggers can be used for data validation or for the automation of denormalization.
MySQL server security measures
Operating system security process can be improved with the following recommendations:
- Install software as ‘root’ OS user. The file permissions of all MySQL binary and support files are to be owned by ‘root’.
- Restrict access to the ‘root’ OS user via sudo privileges. Be diligent by only granting access in limited form.
- Configure an OS ‘mysql’ user, but do not allow direct login access to this user. The mysqld process does not run as the ‘root’ OS user.
- Set permissions of the MySQL data directory to OS ‘mysql’ user for example:
chmod 700/mysql/datadir
- Ensure the MySQL data directory only contains data, and InnoDB transactional logs only.
- The MySQL error, slow and general logs should be in a separate directory. This allows for permissions of the ‘mysql’ group to view logs. Grant group ‘mysql’ access when necessary.
- The MySQL socket file needs to be in a world readable directory. The pid file does not.
- You can provide additional constraints on the MySQL port, e.g. 3306 at a fire wall level. Ideally, your database should not be world accessible. Access should be restricted to the application or monitoring servers only.
