Ejemplos Select Funciones

FUNCIONES (CADENA, FECHAS) DE SQL SERVER Funciones Matemáticas select sqrt sqrt(9) (9) --3.00 – Raiz Cuadrada select round round(1234. (1234.56,0) 56,0) --1235.00 --1235.00 – Redondear Redondear sin decimales decimales select round round(1234. (1234.56,1) 56,1) --1234.60 --1234.60 – Redonde Redondear ar a un decimal decimal select round round($1234. ($1234.56,1) 56,1) --1234.6000 --1234.6000 select power power((2,8) –256 –256 -- Pot Potenc encia ia select floor floor(1332 (1332.39) .39) –1332 –1332 – el menor ente entero ro select floor floor(1332.59 (1332.59)) --1332 – el menor entero entero select ceiling ceiling(1332. (1332.39) 39) –1333 – el mayor entero entero select ceiling ceiling(1332. (1332.59) 59) --1333 – el mayor entero entero select square square(9) (9) --81.0 – cuadrado cuadrado de un numero numero Funciones de Cadena select ascii ascii('A') ('A') --65 select char char(6 (65) 5) --A --A select charindex charindex('bc', ('bc','abcd' 'abcd')) --2 patindex('%cd%','abcd') ('%cd%','abcd') --3 select patindex select replicate replicate('abc ('abc',2) ',2) --abcabc --abcabc select right right('abcd ('abcd',2) ',2) --cd select reverse reverse('abc ('abcd') d') --dcba --dcba select reverse reverse(12 (123) 3) --321 --321 select substring substring('inst ('instituto ituto Idat Idat',11,4) ',11,4) -- Idat Ejemplo: Ejemplo: Mostrar Mostrar solo el Apellido declare @nombre varchar varchar(30 (30)) set @nombre='Juan @nombre='Juan,, Perez Vargas' Vargas' select apellidos=ltrim apellidos=ltrim((substring substring(@ (@nombre, nombre, charindex(',',@nombre)+1, charindex (',',@nombre)+1, len len(@nombre)(@nombre)charindex(',',@nombre)+1)) charindex (',',@nombre)+1)) go -- Perez Vargas

PROFESOR: LIÑÁN RODRÍGUEZ, Julio César

Página 1

FUNCIONES (CADENA, FECHAS) DE SQL SERVER Funciones de Fecha Valores Datepart datepart day

Abreviación Valores dd 1-31

day of year dy

1-366

hour

0-23

hh

millisecond ms

0-999

minute

mi

0-60

month

mm

1-12

second

ss

0-59

week

wk

0-53

weekday

dw

1-7(dom-sab)

year

yy

1753-9999

1.- dateadd(datepart, numero, fecha) : devuelve un valor datetime que se basa en la suma de un intervalo a la fecha especificada. select dateadd(mm,6,'1/1/04') -- 2004-07-01 00:00:00.000 select dateadd(mm,-5,'1/1/04') -- 2003-08-01 00:00:00.000 2.- datediff (datepart,fecha1,fecha2) : devuelve el numero de dateparts entre 2 fechas.

select datediff (mm,'1/1/04',getdate()) -- 9 meses 3.- datename(datepart,fecha) : devuelve el nombre de un datepart especificado por la fecha mostrada.

select datename(month,getdate()) -- Octubre select datename(mm,getdate()) -- Octubre select datename(dw,getdate()) – Viernes


Página 2

FUNCIONES (CADENA, FECHAS) DE SQL SERVER 4.- datepart(datepart,fecha) : devuelve el valor entero de un datepart especificada por la fecha mostrada.

day(fecha), month(fecha), year(fecha), getdate() getdate() : devuelve la fecha y hora del sistema. select getdate() -- 2004-10-01 13:47:15.280 -- listar los empleados con el número de años trabajados use pubs Go select emp_id as idempleado, lname+', '+substring(fname,1,1)+'.' as nombre, 'ha trabajado durante ' ,datediff (year,hire_date,getdate()),' años ' from employee go idempleado nombre ---------- ------------------------- --------------------- --------- -----PMA42628M Accorti, ha trabajado 12 año PSA89086M Afonso,

ha trabajado

14

año

MFS52347M

ha trabajado

14

año

GHT50241M Thomas,

ha trabajado

16

año

DBT39435M Tonini,

ha trabajado

14

año

. .

(43 filas afectadas) select ascii('^') -- Alt + 94 use pubs go -- autores cuyo apellido no estén comprendidos entre c y h select au_lname from authors where au_lname like '[^c-h]%' ;


Página 3

FUNCIONES (CADENA, FECHAS) DE SQL SERVER

au_lname -------------------------------Bennet Blotchet-Halls . . Straight Stringer White Yokomoto (15 filas afectadas) -- autores cuyo apellido no sean m,c,d,h,g,y,s,k ó r select au_lname from authors where au_lname like '[^mcdhgyskr]%' ; au_lname -------------------------------Bennet Blotchet-Halls Locksley O'Leary Panteley White (6 filas afectadas)

-- lista los libros en donde en el campo notas estén configurados como nulos select title,notes from titles where notes is null ; title notes ------------------------------------------------------------------The Psychology of Computer Cooking NULL (1 filas afectadas) PROFESOR: LIÑÁN RODRÍGUEZ, Julio César

Página 4

FUNCIONES (CADENA, FECHAS) DE SQL SERVER

-- lista los libros donde tengan notas que NO estén configurados como nulos select title,notes from titles where notes is not null ; --lista los autores que tengan libros y que su apellido --terminen en una vocal select au_fname,au_lname from authors where exists(select au_id from titleauthor where titleauthor.au_id=authors.au_id) and right(au_lname,1) like '[a,e,i,o,u]' ; select au_fname,au_lname from authors where au_id in (select au_id from titleauthor where titleauthor.au_id=authors.au_id) and right(au_lname,1) like '[a,e,i,o,u]' ; au_fname au_lname ----------------- -----------------------------Michel DeFrance Innes del Castillo White Johnson Akiko Yokomoto (4 filas afectadas) -- lista los libros que no se hayan vendido select title_id,title from titles where ytd_sales is null ; title_id -------MC3026 PC9999

title -------------------------------------------------------------------The Psychology of Computer Cooking Net Etiquette

(2 filas afectadas) -- los grupos que no tienen filas que cumplen con la condición --contendrán NULL PROFESOR: LIÑÁN RODRÍGUEZ, Julio César

Página 5

FUNCIONES (CADENA, FECHAS) DE SQL SERVER select type,pub_id,avg(price) as prom_precio from titles where royalty=12 group by all type,pub_id ; type

pub_id prom_precio

------------

------

-------------------

psychology 0736

10.9500

mod_cook 0877

19.9900

psychology 0877

NULL

trad_cook 0877

NULL

UNDECIDE 0877

NULL

business

1389

NULL

popular_co 1389

NULL

(7 filas afectadas)

select type,pub_id,avg(price) as prom_precio from titles where royalty=12 group by type,pub_id ; type -----------psychology mod_cook

pub_id prom_precio ------ --------------0736 10.9500 0877 19.9900

(2 filas afectadas) select type,pub_id,avg(price) as prom_precio from titles group by type,pub_id ; type -----------psychology mod_cook psychology trad_cook UNDECIDE business popular_co

pub_id prom_precio -------- ------------------0736 11.4825 0877 11.4900 0877 21.5900 0877 15.9633 0877 NULL 1389 13.7300 1389 21.4750


Página 6

FUNCIONES (CADENA, FECHAS) DE SQL SERVER (7 filas afectadas) Advertencia: valor NULL eliminado por el agregado u otra operación SET. -- ahora del listado(agrupado) aquellos cuyo precio -- promedio sean mayores a 15 select type,pub_id,avg(price) as prom_precio from titles group by type,pub_id having avg(price)>15 ; type -----------psychology trad_cook popular_comp

pub_id -------0877 0877 1389

prom_precio -------------21.5900 15.9633 21.4750

Advertencia: valor NULL eliminado por el agregado u otra operación SET. -- para utilizar Having sin un group by, se debe tener una función de -- agregado en la lista de selección y en la clausula Having select sum(price) as suma from titles where type='mod_cook' having sum(price)>20 ; suma --------------------236.2600 -- lista los libros que tengan más de 1 autor y devuelve el -- numero de autores el title_id select title_id,count(title_id) as num_autores from titleauthor group by title_id having count(title_id)>1 ; title_id --------BU1032 BU1111 MC3021 PC8888 PS1372

num_auto -------------2 2 2 2 2


Página 7

FUNCIONES (CADENA, FECHAS) DE SQL SERVER PS2091 TC7777

2 3

(7 filas afectadas)

Convert y Cast Las funciones CAST y CONVERT convierten un valor (una variable local, una columna u otra expresión) de un tipo de datos a otro. convert(tipodato[(longitud)],expresion [,estilo])

sin siglo(aa) con siglo(aaaa) 101 1 103 3 105 5 108 8

Estandar USA Britanico/ Italiano -

formato fecha(Salida) mm dd dd/mm/yy dd-mm-yy hh:mm:ss

Compute y Compute By Sintaxis: select lista_columnas from lista_tablas where criterio [compute] expresion(es)_agregado(s) [by] lista_columnas go select type,sum(ytd_sales) from titles group by type ; select type,ytd_sales from titles order by type compute sum(ytd_sales) by type ; type -----------business business business business

ytd_sales -----4095 3876 18722 4095

sum =========== PROFESOR: LIÑÁN RODRÍGUEZ, Julio César

Página 8

FUNCIONES (CADENA, FECHAS) DE SQL SERVER 30788

select type,ytd_sales,sum(ytd_sales) as suma from titles group by type,ytd_sales order by type compute sum(ytd_sales) by type ; type -----------business business business

ytd_sales ----------3876 4095 18722

suma ---------3876 8190 18722

sum =========== 26693

Rollup y Cube Sintaxis: SELECT lista_columnas FROM lista_tablas WHERE criterio [GROUP BY [ALL] expresion(es)_noagregado(s) [WITH {ROLLUP|CUBE}]] GO USE pubs go select type,pub_id, Convert(Varchar(10),sum(Convert(Money,ytd_sales)),1) as suma from titles group by type,pub_id with rollup


Página 9

FUNCIONES (CADENA, FECHAS) DE SQL SERVER type pub_id ------------------business 1389 business NULL mod_cook 0877 mod_cook NULL popular_comp 1389 popular_comp NULL psychology 0736 psychology 0877 psychology NULL trad_cook 0877 trad_cook NULL UNDECIDED 0877 UNDECIDED NULL NULL NULL (14 filas afectadas)

suma ---------30,788.00 30,788.00 24,278.00 24,278.00 12,875.00 12,875.00 9,564.00 375.00 9,939.00 19,566.00 19,566.00 NULL NULL 97,446.00

Advertencia: valor NULL eliminado por el agregado u otra operación SET. -- ROLLUP produce una fila en la salida x cada fila de la tabla titles con un solo tipo(type) y pub_id select type,pub_id, Convert(Varchar(10),sum(Convert(Money,ytd_sales)),1) as suma from titles group by type,pub_id with cube ; type -----------business business mod_cook mod_cook popular_comp popular_comp psychology psychology psychology trad_cook trad_cook

pub_id -------1389 NULL 0877 NULL 1389 NULL 0736 0877 NULL 0877 NULL

suma ---------30,788.00 30,788.00 24,278.00 24,278.00 12,875.00 12,875.00 9,564.00 375.00 9,939.00 19,566.00 19,566.00


Página 10

FUNCIONES (CADENA, FECHAS) DE SQL SERVER UNDECIDED 0877 UNDECIDED NULL NULL NULL NULL 0736 NULL 0877 NULL 1389 (17 filas afectadas)

NULL NULL 97,446.00 9,564.00 44,219.00 43,663.00

Advertencia: valor NULL eliminado por el agregado u otra operación SET. -- aparte de la fila resumen de tipo ROLLUP, CUBE te muestra otra fila -- al final antes del total general con la suma x cada pub_id(o columna de -- grupo) Vistas CREATE VIEW nomb_vista(campo_1, …, campo_n) [WITH ENCRYPTION] AS instruccion_sql [WITH CHECK OPTION] GO No pueden incluirse Order by salvo que incluya la clausula TOP n No puede incluirse compute, ni compute by No se pueden utilizar tablas temporales, esto significa que no se puede utilizar select into -- creando una tabla con los registros de la tabla Authors a traves de SELECT … INTO select * into tblautores from authors ; -- creando la vista utilizando la tabla creada recientemente create view v_autores as select * from tblautores -- where au_lname like 'g%' go PROFESOR: LIÑÁN RODRÍGUEZ, Julio César

Página 11

FUNCIONES (CADENA, FECHAS) DE SQL SERVER -- listando el contenido de la vista, como si fuera una tabla select * from v_autores ; -- insertando una fila directamente a la vista insert v_autores(au_id,au_lname,au_fname, phone,contract) values('100-05-1000','perez', 'juan','12345',1) go -- si listamos la vista o la tabla el registro se muestra select * from v_autores ; select * from tblautores ; -- mostrando el codigo de la vista sp_helptext v_autores ; -- modificando la vista para incluir un filtro en el apellido y -- activando with check option alter view v_autores as select * from tblautores where au_lname like 'g%' with check option go with check option : obliga a que todas las instrucciones sql de modificación de datos utilicen los criterios establecidos en la clausula where. -- volviendo a insertar una nueva fila en la vista insert v_autores(au_id,au_lname,au_fname,phone,contract) values('200-10-2000','diaz','pedro','12345',1) ; -- no va a insertar porque la vista exige que los valores a ingresar en el -- campo au_lname empiezen con g


Página 12

FUNCIONES (CADENA, FECHAS) DE SQL SERVER insert v_autores(au_id,au_lname,au_fname,phone,contract) values('200-10-2000','gomez','pedro','12345',1) ; -- ahora si se pudo insertar y lo mostraremos select * from v_autores ; -- listando las tablas y/o vistas que dependan de una vista puede -- utilizarse tambien para stored procedure y triggers sp_depends v_autores ; -- eliminando la vista drop view v_autores ; -- creando la vista pero de manera encriptada create view v_autores with encryption as select * from authors where au_lname like 'g%' with check option go -- al tratar de ver nuevamente el codigo de la vista no muestra nada, -- ya que fueron cifrados sp_helptext v_autores go


Página 13

Ejemplos Select Funciones

Recommend Documents