Ejemplos funciones MySql

Funciones de control de flujo:

case when amount<4 then 'bajo' when amount <7 then 'medio' else 'alto' end as tipo ,
if (amount<6,'barato','caro') precio,
if (amount between 4 and 6,'medio','') precio2,
if(amount<4,'bajo',if(amount<7,'medio','alto')) tipo2
FROM sakila.payment;

case dayofweek(payment_date)
 when 1 then 'Domingo'
 when 2 then 'Lunes' 
 when 3 then 'Martes' 
 when 4 then 'Miércoles' 
 when 5 then 'Jueves' 
 when 6 then 'Viernes' 
 when 7 then 'Sábado' 
 end dias,count(payment_id) cuenta,sum(amount) total,avg(amount) media
from payment
group by dias;

select *,
case staff_id when 1 then 'Ana' when 2 then 'Juan' end as empleado
FROM sakila.payment;

select title, if(replacement_cost>20,concat('Descuento: ',replacement_cost*.9) ,replacement_cost) coste from film;

select ifnull(8,0), ifnull(null,0),0+null,concat('Ana',null)

Funciones matemáticas:

select round(4.9), floor(4.9), ceil(4.9),truncate(4.9,0), round(-4.9), floor(-4.9), ceil(-4.9),truncate(-4.9,0), sign(4.9),sign(-4.9),abs(-4.9);

Funciones de cadena:

SELECT * FROM sakila.film where length(title)>20; select length(first_name), concat(first_name,' ',last_name), left(first_name,3), right(last_name,3) nombre, lcase(first_name), ucase(last_name) , concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), reverse(first_name), replace(first_name,'P','##') from actor where first_name=reverse(first_name) update actor set first_name=concat(ucase(left(first_name,1)),lcase(substr(first_name,2))), last_name=concat(ucase(left(last_name,1)),lcase(substr(last_name,2))) where actor_id>=1

Funciones de fecha:

select curtime(),curdate(),now(),week(curdate()),year(curdate()),month(curdate()),
dayofyear(now()),last_day(now()),date_format(now(),'%e-%m-%y %H:%i'),utc_date()

SELECT * FROM sakila.payment
where dayofweek(payment_date)=3;

SELECT * FROM sakila.payment
where date_format(payment_date,'%w')=2;

SELECT * FROM sakila.payment
where day(payment_date)=day(curdate()) ;

Funciones de conversión:

select '8'>'100',cast('8' as signed)>cast('100' as signed);

Funciones de encriptación:

select aes_decrypt(aes_encrypt('Hola que tal','123'),'123');

select md5('hola que tal'),md5('tomateofñlkfñdfñlghfñgsipshdfisu'),sha1('hola que tal'),sha2('hola que tal', 256)

Funciones de información:

select database(), current_user(), last_insert_id(), row_count(), version();

insert into country(country) values('Utopía');
set @a=last_insert_id();
insert into city(city,country_id) values ('Nowhere',@a);
insert into city(city,country_id) values ('Somewhere',@a);

Chat socket io



var app = require('express')();
var http = require('http').Server(app);
var io = require('socket.io')(http);

app.get('/', function(req, res){
users = [];
io.on('connection', function(socket){
  console.log('A user connected');
  socket.on('setUsername', function(data){
    if(users.indexOf(data) > -1){
      socket.emit('userExists', data + ' username is taken! Try some other username.');
      socket.emit('userSet', {username: data});
  socket.on('msg', function(data){
      //Send message to everyone
      io.sockets.emit('newmsg', data);
http.listen(3000, function(){
  console.log('listening on localhost:3000');


 <!DOCTYPE html>
 <head><title>Hello world</title></head>
 <script src="/socket.io/socket.io.js"></script>
 var socket = io();
 function setUsername(){
 socket.emit('setUsername', document.getElementById('name').value);
 var user;
 socket.on('userExists', function(data){
 document.getElementById('error-container').innerHTML = data;
 socket.on('userSet', function(data){
 user = data.username;
 document.body.innerHTML = '<input type="text" id="message">\
 <button type="button" name="button" onclick="sendMessage()">Send</button>\
 <div id="message-container"></div>';
 function sendMessage(){
 var msg = document.getElementById('message').value;
 socket.emit('msg', {message: msg, user: user});
 socket.on('newmsg', function(data){
 document.getElementById('message-container').innerHTML += '<div><b>' + data.user + '</b>: ' + data.message + '</div>'
 <div id="error-container"></div>
 <input id="name" type="text" name="name" value="" placeholder="Enter your name!">
 <button type="button" name="button" onclick="setUsername()">Let me chat!</button>

Ejemplo promesa

function promiseSqrt(value){
return new Promise(function (fulfill, reject){
console.log(‘START execution with value =’, value);
setTimeout(function() {
fulfill({ value: value, result: value * value });
}, 0 | Math.random() * 1000);

var p = [0,1,2,3,4,5,6,7,8,9];
function (sequence, value) {
return sequence.then(function() {
return promiseSqrt(value);
}).then(function(obj) {
console.log(‘END execution with value =’, obj.value,
‘and result =’, obj.result);
).then(function() {

Ejemplo de multihilo con node

function longRunningOperation(callback) {
// simulate a 3 second operation
setTimeout(callback, 3000);

function userClicked() {
console.log(‘starting a long operation’);
longRunningOperation(function () {
console.log(‘ending a long operation’);
// simulate a user action


Cada uno con su closure:

function longRunningOperation(callback) {
// simulate a 3 second operation
setTimeout(callback, 3000);

function webRequest(request) {
console.log(‘starting a long operation for request:’, request.id);
longRunningOperation(function () {
console.log(‘ending a long operation for request:’, request.id);
// simulate a web request
webRequest({ id: 1 });
// simulate a second web request
webRequest({ id: 2 });

IDEs para node