mysql – Consulta SQL (aprendiendo) (group by)

que tal estoy aprendiendo bases de datos, estaba resolviendo unos ejercicos pero me atore con estas tres consultas me podrian ayudarme y decirme como serian y el por que?
Muchas gracias y saludos a todos.
introducir la descripción de la imagen aquí

  1. Consulta que obtenga el Nombre de cliente, Id de pedido, Fecha, Nombre productos, Cantidad, Precio y Tipo de Articulo ordenado por Id de cliente

  2. Consulta que obtenga el Total de pedidos agrupado por Id de Cliente hasta la fecha indicada

  3. Consulta que obtenga la Cantidad de Productos agrupado por Tipo y Id de Cliente

Queries of MySQL from Java are executed serially from multiple threads

I have REST API developed in spring + java and hosted on Tomcat server. I have a MySQL database where i store all my data. API makes just simple select from db as descripted below. I failed performance tests because of executing query one by one. The problem is: I run 100 threads to query my API (outside program) and my logs in API shows that each request stops on the execution of the query and waits for it’s turn to ask database. It seems like MySQL can do one query at a time. Is it true ? I’m setting connection.setReadOnly(true) – to the transaction should be marked as read only.

Body of a API request that is executed:

Long start = System.currentTimeMillis();
    System.out.println("Starting thread: "+idx);

 String sql = "SELECT * n" +
         "FROM EXERCISESn" +
         " WHERE 1 = 1 n" +
         " AND exercises.STARTTIME  n" +
         " BETWEEN '2020-08-03 00:00:00.000' n" +
         " AND '2020-10-10 23:59:59.999'n" +
         "and exercises.SALEVISIBILITY in ('E','B') n" +
         " AND exercises.STARTTIME > current_timestamp()n";

    MysqlDataSource dataSource = new MysqlDataSource();


    try {
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/sampleDB?useUnicode=yes&characterEncoding=UTF-8");
        dataSource.setUser("test");
        dataSource.setPassword("test");
        dataSource.setLoginTimeout(1000);
        dataSource.setAllowMultiQueries(true);
        System.out.println("Before connection "+(System.currentTimeMillis()-start));
        Connection connection = dataSource.getConnection();
        System.out.println("After connection "+(System.currentTimeMillis()-start));
        connection.setReadOnly(true);
        System.out.println("After readOnly "+(System.currentTimeMillis()-start));
        Statement stmt = connection.createStatement();
        System.out.println("After stmt "+(System.currentTimeMillis()-start));
        ResultSet rs = stmt.executeQuery(sql);
        System.out.println("After ResultSet "+(System.currentTimeMillis()-start));
        
        System.out.println("End task: "+idx+" time: "+(System.currentTimeMillis()-start));

        if(!rs.isClosed())
        {
            rs.close();
        }

        if(!stmt.isClosed()){
            stmt.close();
        }

        if(!connection.isClosed()){
            connection.close();
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }

I’ve put here the MysqlDataSource to make sure each request has it’s own connection and also to show my connection parameters (maybe here is something wrong).

And the result of the test is:

... many similar lines above
After connection 5457
After readOnly 5457
After stmt 5457
After connection 5509
After readOnly 5509
After stmt 5509
After connection 5509
After readOnly 5509
After stmt 5509
After connection 5555
After readOnly 5555
After connection 5553
After readOnly 5553
After stmt 5555
After stmt 5553
After connection 5504
After readOnly 5504
After stmt 5504
After connection 5607
After readOnly 5607
After stmt 5607
After ResultSet 7112
End task: 32 time: 7112
After ResultSet 7304
End task: 12 time: 7304
After ResultSet 6702
End task: 59 time: 6702
After ResultSet 6829
End task: 52 time: 6829
After ResultSet 7360
End task: 24 time: 7360
After ResultSet 6857
End task: 56 time: 6857
After ResultSet 7625
End task: 20 time: 7625
After ResultSet 7661
End task: 36 time: 7661
After ResultSet 7822
End task: 15 time: 7822
After ResultSet 7758
End task: 27 time: 7758
After ResultSet 7691
End task: 39 time: 7691
After ResultSet 7651
End task: 64 time: 7651
After ResultSet 8430
End task: 51 time: 8430
After ResultSet 8852
End task: 28 time: 8852
After ResultSet 8052
End task: 75 time: 8052
After ResultSet 8840
End task: 43 time: 8840
After ResultSet 7678
End task: 98 time: 7678
After ResultSet 8985
End task: 40 time: 8985
After ResultSet 8521
End task: 60 time: 8521
After ResultSet 9159
End task: 23 time: 9159
After ResultSet 8836
... and many the same lines below

All the request executes very fast to line: System.out.println(“After stmt “+(System.currentTimeMillis()-start)); and then all freeze to be executed one by one on line: ResultSet rs = stmt.executeQuery(sql);

My max connections is set to 3000. I’m using MySQL 5.7.25, single select executes in 300-350 ms. Max threads on tomcat is set to 600.

If anyone could help me – please! I would be very gratefull:)

MySQL result ORDER BY reverse order?

My query looks like ..

SELECT 
        uid
    FROM 
        user 
    
    WHERE 
        plz IN(24718,32326,32329,32328) 
    
    ORDER BY 
        FIELD(plz,24718,32326,32329,32328),
        uid DESC

Output:

97462
97407
97112
.
.
3037
891
15
91967
29360

I need to select / get first (97462) and last (29360) uid.

SELECT uid, MAX(uid), MIN(uid) does not work because of the ORDER BY.

mysql – me devuelve un valor sin decimales, pero tiene decimales

bien no se como preguntar realmente en esta ocasión, el problema que tengo es que estoy guardando datos numéricos, donde todos los campos son float, en la BDD me los guarda con los decimales, pero al querer mostrarlos no me muestra los decimales, solo si este es > 0.1

ejemplo:

BDD => retorna

20.00 => 20

20.40 => 20.4

20.44 => 20.44

Bien en el lado izquierdo es como se guarda y en el lado derecho es como me los muestra, lo raro es que en el gestor segun el tipo del campo es double pero los estoy “declarando” float

 Schema::create('tabla', function (Blueprint $table) {
     $table->bigIncrements('id');
     $table->float('valor1', 8,2);
     $table->float('valor2', 8,2);
     $table->float('valor3', 8,2);
 });

cuando tenia los campos tipo decimal si los mostraba bien, aún siendo 0.00, por que con float no?

el "cotejamiento" podría tener algo que ver con este problema ?

introducir la descripción de la imagen aquí

mysql – How secure is my PHP registration script?

I coded a registration form and was wondering if anyone could give me feedback on whether I’m lacking any major security mechanisms. I’ve done loads of reading around the topic and feel it should be secure but expert feedback would be great.

Registration script

<?php

// User/pass only used locally
$host = 'localhost';
$db   = 'new_db';
$user = 'root';
$pass = '';

$options = (
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
);
$dsn = "mysql:host=$host;dbname=$db";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
     throw new PDOException($e->getMessage(), (int)$e->getCode());
}


$errors = ();
if ( $_POST )
{

    // Get form field values
    $fname              = filter_input(INPUT_POST, 'fname', FILTER_SANITIZE_STRING);
    $email              = filter_input(INPUT_POST, 'email', FILTER_SANITIZE_EMAIL);
    $pwd                = filter_input(INPUT_POST, 'password', FILTER_SANITIZE_STRING);
    $pwd_confirm        = filter_input(INPUT_POST, 'confirm-password', FILTER_SANITIZE_STRING);

    // Generate activation key
    $activation_key     = md5(uniqid(mt_rand(), true));
    $activation_link    = 'https://www.example.com/activate?id='.$activation_key.'&name='.$fname;

    // Check if passwords match
    if ($pwd !== $pwd_confirm) {
        $errors() = "Passwords don't match";
    }

    // Check if password is secure
    if (strlen($pwd) < 8) {
        $errors() = "Password not long enough! Must be at least 8 characters long";
    }

    // Check if username equals password
    if ($fname === $pwd) {
        $errors() = "Your name cannot be your password!";
    }

    // Check if email address exists in database
    $email_query = $pdo->prepare("SELECT * FROM users WHERE email = :email");
    $email_query->bindParam(':email', $email);
    $email_query->execute();
    $email_found = $email_query->fetchColumn();
    if ($email_found) {
        $errors() = "Your email address is associated with another account.";
    }


    // If no errors, continue with user account creation
    if (!$errors)
    {   
        // Hash password
        $hashed_password = password_hash($pwd, PASSWORD_DEFAULT);

        // Create database entry
        $create_account = $pdo->prepare("INSERT INTO users (first_name,email,password, activation_key) VALUES (:fname, :email, :password, :activation_key)");
        $create_account->bindParam(':fname', $fname);
        $create_account->bindParam(':email', $email);
        $create_account->bindParam(':password', $hashed_password);
        $create_account->bindParam(':activation_key', $activation_key);
        $create_account->execute();

        // Send out activation email
        $message = "Your Activation Code is ".$code."";
        $to=$email;
        $subject="Activate your account";
        $from = 'no-reply@example.com';
        $body='Thank you for creating your account, '.$fname.'. Please click on the following link to activate your account: <a href="'.$activation_link.'">'.$activation_link.'</a>';
        $headers = "From:".$from;
        mail($to,$subject,$body,$headers);

        // Redirect user to the dashboard
        header("Location: /dashboard.php");
        exit;
    }



}

?>

I do want to use it on a live site at one point, so the end result should not just be a registration form that’s somewhat secure but could actually be used in production.

¿Cómo puedo actualizar varias columnas o líneas a la vez en Mysql?

Ando actualizando los datos de una columna en una tabla de mi base de datos(MariaDB), pero son muchos datos y tardaré demasiado haciéndolo manualmente 1×1. ¿Habría una forma de crear una consulta para actualizar todo de una sola vez?
introducir la descripción de la imagen aquí

Intento hacer una consulta UPDATE común con WHERE, pero no encuentro como editar todo sin dañar el resto de datos, el WHERE sería el item_name bc_cone porque se repite en todas las líneas pero no sé como hacerlo.

mysql – recuperar coordenadas e exibir no mapa leaflet com uma polyline

Bom dia, gostaria de saber como recupero as coordenadas que estão no banco adiciono em um array para exibir no mapa o marker e uma linha? segue meu código.

0) && ($_POST(‘imei’) == “”)) { ?>

swal({
title: “Selecione o BEM!”,
icon: “warning”,
buttons: “Fechar”,
dangerMode: true,

})
.then((willDelete) => {
if (willDelete) {
window.location = ‘principal.php?link=49’;
} else {
window.location = ‘principal.php?link=49’;
}
});

#map {
width: 100%;
height: 450px;
}

  • Home
  • Histórico
  • Rota Percorrida
  • while ($linha_usuario = mysqli_fetch_assoc($resultado_busca)) { ?>

    <?php $linha_usuario('imei'); ?>
    <?php } ?>
    <div class="box box-primary">
    <div class="box-header">
    <h3 class="box-title text-red">Traçar Rota </h3>
    <div class="box-tools pull-right">
    <button type="button" class="btn btn-box-tool" data-widget="collapse"><i class="fa fa-minus"></i>
    </button>

    </div>

    </div>

    <div class="box-body">

    <div class="col-md-3 xs-12">
    <!-- DIV PARA SELECIONAR O BEM-->
    <div class="form-group">
    <label for="exampleInputEmail1" class="text-primary">INFORME O BEM</label>
    <select class="form-control" name="imei" id="imei">
    <option value="">SELECIONE</option>
    <?php
    if ($_SESSION('usuarioLogin') == "adm") { //se usuario adm busca todos os BENS
    $busca_usuario = "SELECT * FROM bem WHERE ativo = 'S'";
    $resultado_busca = mysqli_query($conn, $busca_usuario);
    } else {
    $busca_usuario = "SELECT * FROM bem WHERE id_usuario='$id_usuario' AND ativo = 'S'"; // busca o bem de acordo com o usuario
    $resultado_busca = mysqli_query($conn, $busca_usuario);
    }

    while ($linha_usuario = mysqli_fetch_assoc($resultado_busca)) { ?>

    <option value="<?php echo $linha_usuario('imei'); ?>"><?php echo $linha_usuario('identificacao'); ?>

    </option>
    <?php } ?>
    </select>
    </div>
    </div>

    <div class="col-md-2 xs-12">
    <!-- Date -->
    <div class="form-group">
    <label class="text-primary">Data Inicial</label>

    <div class="input-group date">
    <div class="input-group-addon">
    <i class="fa fa-calendar"></i>
    </div>
    <input type="date" class="form-control pull-right" id="datepicker" name="data_inicial">
    </div>

    <!-- /.input group -->
    </div>
    </div>

    <div class="col-md-2 xs-12">
    <div class="form-group">
    <label class="text-primary">Hora Inicial</label>

    <div class="input-group date">
    <div class="input-group-addon">
    <i class="fa fa-clock-o"></i>
    </div>
    <input type="time" class="form-control pull-right" id="datepicker" name="hora_inicial">

    </div>
    <!-- /.input group -->
    </div>
    <!-- /.form group -->
    </div>
    <!-- /.form group -->
    <div class="col-md-2 xs-12">
    <div class="form-group">
    <label class="text-primary">Data Final</label>

    <div class="input-group date">
    <div class="input-group-addon">
    <i class="fa fa-calendar"></i>
    </div>
    <input type="date" class="form-control pull-right" id="datepicker" name="data_final">

    </div>
    <!-- /.input group -->
    </div>
    <!-- /.form group -->
    </div>
    <div class="col-md-2 xs-12">
    <div class="form-group">
    <label class="text-primary">Hora Final</label>

    <div class="input-group date">
    <div class="input-group-addon">
    <i class="fa fa-clock-o"></i>
    </div>
    <input type="time" class="form-control pull-right" id="datepicker" name="hora_final">

    </div>
    <!-- /.input group -->
    </div>
    <!-- /.form group -->
    </div>
    <div class="col-md-12">
    <button type="submit" class="btn btn-success"><i class="fa fa-map-marker"></i> Traçar Rota</button>
    </div>
    </div>
    <div class="datepicker datepicker-dropdown dropdown-menu datepicker-orient-left datepicker-orient-top"></div>

    NOME: ‘ + name + ‘
    IMEI: ‘ + imei + ‘‘, {
    maxWidth: ‘300’”);
    }
    }
    ?>

    var mymap = L.map(‘mapid’);
    L.tileLayer(‘http://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}.png’, {
    attribution: ‘© OpenStreetMap © CartoDB’,
    subdomains: ‘abcd’
    }).addTo(mymap);
    mymap.scrollWheelZoom.disable();

    var lat = “”;

    var lon = “”;

    var rota = ( (40.702244436175114,-74.01660919189453), (40.70562793820589, -74.01695251464844 ), ( 40.71369559554873, -74.01420593261717 ) );

    //=================================ESSAS COORDENADAS TEM QUE VIR DO BANCO DE DADOS ===========================================================

    rota.forEach(function (rotas){
    new L.Marker(rotas).addTo(mymap);
    });

    var polyline = L.polyline(rota, {color: ‘red’, className: ‘west_side_drive’}).addTo(mymap);

    mymap.fitBounds(polyline.getBounds());
    // Plot through the trip time.

    mysql – Is it a good practice to store auth session in the database?

    I created a login system that in addition to being used on a website, will also be used in mobile applications.

    As on cell phones I want to keep the user logged in until he chooses to log out, I did not use the authentication for sessions in PHP.

    So I thought it would be better to store the login sessions in the database, for each user request, to verify if the authentication token is still valid.

    But I don’t know if this is a good practice. Since every time the user updates the screen in the browser, or sends any application request to the system, he will make a query to verify that the login is still active and then make another query to search for what the user requested.

    My concern is whether this will become too slow, for a system that could have between 900 million and 1,5 billion users, since the database will have many more requests and verification queries in addition to the normal query requested by the user.

    Below is the current structure of my database. I would also like tips if my structure is very wrong.

    MysqlDatabase

    sql server 2008 – MYSQL Error: Incorrect usage of UNION and ORDER BY?

    Am trying to randomize my first query but ran into an error “Incorrect usage of UNION and ORDER BY” Kindly find below my queries:

    SELECT *
    FROM(
            SELECT hobber_deal_page.id,
                hobber_deal_page.merchant_id,
                hobber_deal_page.category_id,
                hobber_deal_page.emirate_id,
                hobber_deal_page.title,
                hobber_deal_page.description,
                hobber_deal_page.emirate,
                hobber_deal_page.lat,
                hobber_deal_page.lng,
                hobber_deal_page.address,
                hobber_deal_page.thumbnail_name,
                hobber_deal_page.price,
                hobber_deal_page.created_at,
                AVG(hobber_ratings.ratings) AS ratings
            FROM hobber_deal_page
                LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
            GROUP BY hobber_deal_page.id,
                hobber_deal_page.merchant_id,
                hobber_deal_page.category_id,
                hobber_deal_page.emirate_id,
                hobber_deal_page.title,
                hobber_deal_page.description,
                hobber_deal_page.emirate,
                hobber_deal_page.lat,
                hobber_deal_page.lng,
                hobber_deal_page.address,
                hobber_deal_page.thumbnail_name,
                hobber_deal_page.price,
                hobber_deal_page.created_at
            ORDER BY hobber_deal_page.lat ASC
            LIMIT 2
        ) t1 ORDER BY RAND()
    UNION DISTINCT
    SELECT *
    FROM(
            SELECT hobber_deal_page.id,
                hobber_deal_page.merchant_id,
                hobber_deal_page.category_id,
                hobber_deal_page.emirate_id,
                hobber_deal_page.title,
                hobber_deal_page.description,
                hobber_deal_page.emirate,
                hobber_deal_page.lat,
                hobber_deal_page.lng,
                hobber_deal_page.address,
                hobber_deal_page.thumbnail_name,
                hobber_deal_page.price,
                hobber_deal_page.created_at,
                AVG(hobber_ratings.ratings) AS ratings
            FROM hobber_deal_page
                LEFT JOIN hobber_ratings ON hobber_deal_page.id = hobber_ratings.deal_id
            GROUP BY hobber_deal_page.id,
                hobber_deal_page.merchant_id,
                hobber_deal_page.category_id,
                hobber_deal_page.emirate_id,
                hobber_deal_page.title,
                hobber_deal_page.description,
                hobber_deal_page.emirate,
                hobber_deal_page.lat,
                hobber_deal_page.lng,
                hobber_deal_page.address,
                hobber_deal_page.thumbnail_name,
                hobber_deal_page.price,
                hobber_deal_page.created_at
            ORDER BY hobber_deal_page.lat ASC
        ) t2
    
    

    Am not sure where I got it wrong. Without adding “ORDER BY RAND()” my query runs well but I need the first query to randomize. I need a guide on how to come about this.

    mysql – How to do left join , same table with different conditions per column

    You can move them together like here

    Schema (MySQL v8.0)

    CREATE TABLE polizamovtos  (
      `idlote` VARCHAR(10),
      `cargo` VARCHAR(10),
      `abono` INTEGER,
      `fechamovto` VARCHAR(10)
    );
    
    INSERT INTO polizamovtos 
      (`idlote`, `cargo`, `abono`, `fechamovto`)
    VALUES
      ('2', '0', '5000', '2019-11-01'),
      ('1', '1000', '0', '2019-12-01'),
      ('2', '4000', '0', '2019-11-01'),
      ('1', '2000', '0', '2020-01-01'),
      ('2', '0', '500', '2020-10-02'),
      ('3', '4000', '0', '2020-01-01'),
      ('4', '5000', '0', '2020-01-01'),
      ('1', '2000', '0', '2020-01-01'),
      ('2',  '0',   '2000', '2020-04-02');
    

    Query #1

    SELECT 
    IDLOTE,
    SUM(cargo) as saldo 
    FROM polizamovtos 
    WHERE FECHAMOVTO='2020-01-01' 
    GROUP BY IDLOTE
    having sum(cargo)-sum(abono) > 0;
    
    | IDLOTE | saldo |
    | ------ | ----- |
    | 1      | 4000  |
    | 3      | 4000  |
    | 4      | 5000  |
    

    View on DB Fiddle

    Query #2

    SELECT 
    IDLOTE,
    SUM(CASE WHEN FECHAMOVTO='2020-01-01' 
        THEN cargo
       ELSE 0
       END) as saldo 
    FROM polizamovtos 
    GROUP BY IDLOTE
    having sum(cargo)-sum(abono) > 0;
    
    | IDLOTE | saldo |
    | ------ | ----- |
    | 1      | 4000  |
    | 3      | 4000  |
    | 4      | 5000  |
    

    View on DB Fiddle