My Sql

Curso de Extensión Universitaria MySQL y JAVA

Curso 2003-04 Dep. Informática

Curso de MySQL y Java
Recuperación de información: consultas en MySQL
1.Algunos detalles sobre consultas
MySQL utiliza como lenguaje para la recuperación y la definición de datos el lenguaje SQL con alguna peculiaridad. • Literales o numéricos: 8, 0.0, 9.1 son números constantes o Cadenas de caracteres: ‘Hola mellamo Esther’ , “Hola me llamo Esther” o Tipo fecha: ‘2004- 07-12’-> ‘año-mes-dia’ Valor NULL: Tipos de datos para las columnas. Se pueden clasificar en tres tipos: numéricos, cadenas de caracteres y fecha/hora. o Tipos numéricos: tinyint, smallint, mediumint, int,
bigint, flota, double, decimal. tinyblob, tinytext,blob, text, mediumblob, mediumtext, longblob,longtext. o Tipo enumerado:enum(‘tipo1’,’tipo2’,’tipo3’…);

• •

o Tipo fecha: DATE, TIME, DATETIME, TIMESTAMP o Tipo cadena: char(n), varchar(n),

•

o Tipo set: set (‘uno’,’dos’, ‘tres’) (tipo conjunto) Operadores: a continuación se muestran los operadores que se pueden utilizar en MySQL en orden de precedencia:
:= ||, OR, XOR &&, AND BETWEEN, =, , >=, >, 50 CHAR_LENGTH(str)

Returns the numeric value of the leftmostcharacter of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

Curso de Extensión Universitaria MySQL y JAVA

Curso 2003-04 Dep. Informática

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containingfive two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have one or more arguments. A numeric argument is converted to its equivalent string form.
mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL'); ->NULL INSERT(str,pos,len,newstr) Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. mysql> SELECT INSERT('Quadratic', 3, 4, 'What'); -> 'QuWhattic'

This function is multi-byte safe.
INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument formof LOCATE(), except that the arguments are swapped.
mysql> SELECT INSTR('foobarbar', 'bar'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0 LOAD_FILE(file_name)

mysql> UPDATE tbl_name SET blob_column=LOAD_FILE('/tmp/picture') WHERE id=1; LOWER(str) Returns the string str with all characters changed to lowercase according to the

Reads the file and returns the file contents as a string. Thefile must be located on the server, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and be smaller than max_allowed_packet bytes. If the file doesn't exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

current character set mapping (the default is ISO-8859-1 Latin1).mysql> SELECT LOWER('QUADRATICALLY'); -> 'quadratically' LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.
mysql> SELECT LOCATE('bar', 'foobarbar'); -> 4 mysql>SELECT LOCATE('xbar', 'foobar'); -> 0 mysql> SELECT LOCATE('bar', 'foobarbar',5); -> 7

Curso de Extensión Universitaria MySQL y JAVA

Curso 2003-04 Dep. Informática

This function is multi-byte safe. In MySQL 3.23, this function is case sensitive. For 4.0 on, it is case sensitive only if either argument is a binary string.
REPLACE(str,from_str,to_str) Returns the string str with all...