Scribd
Cargar
4 vistas5 páginas

Comandos básicos de MySQL en Ubuntu

El documento detalla una sesión de MySQL donde se crean y manipulan bases de datos y tablas. Se muestra la creación de una tabla 'proveedor' y operaciones como inserciones, actualizaciones y eliminaciones de registros. Además, se realizan consultas para visualizar y ordenar los datos almacenados en la tabla.

Cargado por

brisa olimar
Derechos de autor
© All Rights Reserved
Nos tomamos en serio los derechos de los contenidos. Si sospechas que se trata de tu contenido, reclámalo aquí.
Formatos disponibles
Descarga como TXT, PDF, TXT o lee en línea desde Scribd
4 vistas5 páginas

Comandos básicos de MySQL en Ubuntu

El documento detalla una sesión de MySQL donde se crean y manipulan bases de datos y tablas. Se muestra la creación de una tabla 'proveedor' y operaciones como inserciones, actualizaciones y eliminaciones de registros. Además, se realizan consultas para visualizar y ordenar los datos almacenados en la tabla.

Cargado por

brisa olimar
Derechos de autor
© All Rights Reserved
Nos tomamos en serio los derechos de los contenidos. Si sospechas que se trata de tu contenido, reclámalo aquí.
Formatos disponibles
Descarga como TXT, PDF, TXT o lee en línea desde Scribd

labso01@labso01-ROG-STRIX-G10CE-G10CE:~$ mysql -u root -p -h localhost

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu)

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its


affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;


+--------------------+
| Database |
+--------------------+
| MiBD |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

mysql> use MiBD;


Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_MiBD |
+----------------+
| MiTabla |
+----------------+
1 row in set (0.00 sec)

mysql> select * from MiTabla;


+------+--------+-------+
| idMT | nombre | valor |
+------+--------+-------+
| 1 | hola | 10 |
| 2 | olis | 10 |
+------+--------+-------+
2 rows in set (0.03 sec)

mysql> select nombre from MiTabla


-> ;
+--------+
| nombre |
+--------+
| hola |
| olis |
+--------+
2 rows in set (0.00 sec)

mysql> select nombre, valor*5 from MiTabla;


+--------+---------+
| nombre | valor*5 |
+--------+---------+
| hola | 50 |
| olis | 50 |
+--------+---------+
2 rows in set (0.00 sec)

mysql> select * from MiTabla;


+------+--------+-------+
| idMT | nombre | valor |
+------+--------+-------+
| 1 | hola | 10 |
| 2 | olis | 10 |
+------+--------+-------+
2 rows in set (0.01 sec)

mysql> create table proveedor(idprov varchar(4),);


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
mysql> create table proveedor(idprov varchar(4)
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> create table proveedor(
-> idprov varchar(4),
-> nombre varchar(20),
-> status integer,
-> ciudad varchar(20));
Query OK, 0 rows affected (1.36 sec)

mysql> select * from proveedor;


Empty set (0.00 sec)

mysql> describe proveedor;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| idprov | varchar(4) | YES | | NULL | |
| nombre | varchar(20) | YES | | NULL | |
| status | int | YES | | NULL | |
| ciudad | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table proveedor add primary key(idprov);


Query OK, 0 rows affected (2.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe proveedor;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| idprov | varchar(4) | NO | PRI | NULL | |
| nombre | varchar(20) | YES | | NULL | |
| status | int | YES | | NULL | |
| ciudad | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into proveedor values("V1","Smith",20,"Londres");


Query OK, 1 row affected (0.15 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 20 | Londres |
+--------+--------+--------+---------+
1 row in set (0.00 sec)

mysql> insert into proveedor values("V3","Blake",30,"París");


Query OK, 1 row affected (0.15 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 20 | Londres |
| V3 | Blake | 30 | París |
+--------+--------+--------+---------+
2 rows in set (0.00 sec)

mysql> insert into proveedor values("V","Blake",30,"París");


Query OK, 1 row affected (0.11 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V | Blake | 30 | París |
| V1 | Smith | 20 | Londres |
| V3 | Blake | 30 | París |
+--------+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> delete from proveedor where idprov="V";


Query OK, 1 row affected (0.10 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 20 | Londres |
| V3 | Blake | 30 | París |
+--------+--------+--------+---------+
2 rows in set (0.00 sec)

mysql> insert into proveedor values("V2","Jones",10,"París");


Query OK, 1 row affected (0.12 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 20 | Londres |
| V2 | Jones | 10 | París |
| V3 | Blake | 30 | París |
+--------+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from proveedor order by nombre;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V3 | Blake | 30 | París |
| V2 | Jones | 10 | París |
| V1 | Smith | 20 | Londres |
+--------+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> select * from proveedor order by status;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V2 | Jones | 10 | París |
| V1 | Smith | 20 | Londres |
| V3 | Blake | 30 | París |
+--------+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> insert into proveedor values("V2","Clark",20,"Londres");


ERROR 1062 (23000): Duplicate entry 'V2' for key '[Link]'
mysql> insert into proveedor values("V4","Clark",20,"Londres");
Query OK, 1 row affected (0.13 sec)

mysql> insert into proveedor values("V5","Adams",30,"Atenas");


Query OK, 1 row affected (0.11 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 20 | Londres |
| V2 | Jones | 10 | París |
| V3 | Blake | 30 | París |
| V4 | Clark | 20 | Londres |
| V5 | Adams | 30 | Atenas |
+--------+--------+--------+---------+
5 rows in set (0.00 sec)

mysql> update proveedor set status=50 where nombre="Smith";


Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 50 | Londres |
| V2 | Jones | 10 | París |
| V3 | Blake | 30 | París |
| V4 | Clark | 20 | Londres |
| V5 | Adams | 30 | Atenas |
+--------+--------+--------+---------+
5 rows in set (0.01 sec)

mysql> update proveedor set status=status*1.05;


Query OK, 5 rows affected (0.13 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 53 | Londres |
| V2 | Jones | 11 | París |
| V3 | Blake | 32 | París |
| V4 | Clark | 21 | Londres |
| V5 | Adams | 32 | Atenas |
+--------+--------+--------+---------+
5 rows in set (0.00 sec)

mysql> select idprov, nombre, status*100, ciudad from proveedor;


+--------+--------+------------+---------+
| idprov | nombre | status*100 | ciudad |
+--------+--------+------------+---------+
| V1 | Smith | 5300 | Londres |
| V2 | Jones | 1100 | París |
| V3 | Blake | 3200 | París |
| V4 | Clark | 2100 | Londres |
| V5 | Adams | 3200 | Atenas |
+--------+--------+------------+---------+
5 rows in set (0.00 sec)

mysql> select * from proveedor;


+--------+--------+--------+---------+
| idprov | nombre | status | ciudad |
+--------+--------+--------+---------+
| V1 | Smith | 53 | Londres |
| V2 | Jones | 11 | París |
| V3 | Blake | 32 | París |
| V4 | Clark | 21 | Londres |
| V5 | Adams | 32 | Atenas |
+--------+--------+--------+---------+
5 rows in set (0.00 sec)

mysql> exit
Bye

Common questions

Con tecnología de IA

Setting a primary key in a SQL table enforces uniqueness for the column designated as the primary key. This prevents any duplicate entries for that column across the table. An error such as 'Duplicate entry...' for the primary key occurs if an attempt is made to insert a row with a value that already exists in the primary key column, ensuring data integrity by not allowing duplicate keys .

Mathematical computation on SQL data types, such as multiplying 'status' by 5 or 1.05, allows for batch processing of records to track trends or adjust values en masse. These operations enable complex data analysis directly within SQL, like scaling all entries for a uniform change or calculating future projections based on current data, thus providing deeper insights during database transactions .

Inserting a row with a duplicate primary key value results in an error, specifically 'ERROR 1062 (23000): Duplicate entry... for key 'PRIMARY.'' This prevents duplicate primary keys, preserving data integrity. To prevent such occurrences, ensuring unique key values before an insert operation through either a check or by utilizing unique constraints or auto-inincrement mechanisms is crucial .

Command sequence in SQL directly influences database schema changes and data manipulation, with schema-altering commands like 'create table' or 'alter table' setting the structure. Subsequent commands manipulate data within that structure, such as 'insert,' 'update,' and 'delete,' executing in order to manage data application-wide. The sequence and timing of these commands ensure both structure and data integrity throughout operations .

The error in SQL syntax occurred due to incomplete statements while attempting to create a table, such as 'create table proveedor(idprov varchar(4),' leading to an error message indicating the syntax near ')' or ''. This can be resolved by providing a complete and correct SQL statement like 'create table proveedor (idprov varchar(4), nombre varchar(20), status integer, ciudad varchar(20));' which ensures the table is defined with all required fields adequately specified .

Techniques such as the 'UPDATE' SQL statement are used to modify existing data records. For example, setting 'status=50 where nombre="Smith"' changes the 'status' of 'Smith' to 50. Furthermore, updating with expressions like 'status=status*1.05' adjusts each status by multiplying it by 1.05, showcasing SQL's capability to apply arithmetic operations directly onto database records .

Ordering query results by different columns, such as names or status values, changes the sequence in which the data is presented. When ordered by 'nombre,' results are listed alphabetically, whereas ordering by 'status' arranges entries numerically by the status values. This affects not only readability but also the user’s ability to identify certain properties or trends within the dataset depending on the chosen order .

Ordering and filtering SQL query results significantly impact data visualization and decision-making. Ordered results, via 'order by', aid in highlighting specific trends or priorities, whereas 'where' filtering focuses on subsets of data relevant for particular analyses. These SQL features improve readability and interpretability, thus enhancing the decision-making process based on more organized and relevant data .

For complex dataset management, optimization strategies include batching operations with 'update' commands to minimize transaction costs and improve performance. Additionally, maintaining unique constraints through triggers to automatically check for duplicates or using 'ON DUPLICATE KEY UPDATE' clauses helps manage data integrity seamlessly. These approaches leverage SQL's powerful processing capabilities efficiently, ensuring smooth operation across large datasets .

Lack of error management in SQL scripting can lead to operational inefficiencies, such as halted execution or inaccurate data population, particularly evident when errors arise during table creation or row insertion. Adequate error handling, like condition checks or exception handling mechanisms, can mitigate such issues, ensuring database operations proceed smoothly without data or structural anomalies .

También podría gustarte