Inicio > Base de datos I > Actividades > Comandos MySQL

Comandos MySQL

A continuación se muestran comandos MySQL utilizados durante la practica de Base de Datos 1 de la Corporación Universitaria Remington con el docente Javier Ospina.

Mostrar tablas según la Base de datos usada.

mysql> show tables;

+----------------------+

| Tables_in_biblioteca |

+----------------------+

| autor |

| lib_aut |

| libro |

+----------------------+

3 rows in set (0.03 sec)

Consultar estructura de una tabla

mysql> describe libro;

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

+---------+----------+------+-----+---------+-------+

2 rows in set (0.09 sec)

Actualizar estructura de una tabla (Adicionar campo)

mysql> alter table libro add valor_lib int(7) not null;

Query OK, 0 rows affected (0.50 sec)

Records: 0 Duplicates: 0 Warnings: 0

Actualizar estructura de una tabla (Insertar campo después de otro campo referencia)

mysql> alter table libro add editorial char(20) not null after nom_lib;

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe libro;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

| editorial | char(20) | NO | | NULL | |

| valor_lib | int(7) | NO | | NULL | |

+-----------+----------+------+-----+---------+-------+

4 rows in set (0.02 sec)

Actualizar estructura de una tabla (Inserta campo en la primera posición)

mysql> alter table libro add tomo_lib char(2) not null first;

Query OK, 0 rows affected (0.31 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe libro;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

| valor_lib | int(7) | NO | | NULL | |

+-----------+----------+------+-----+---------+-------+

5 rows in set (0.02 sec)

Eliminar campo de una tabla

mysql> alter table libro drop tomo_lib;

Query OK, 0 rows affected (0.36 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe libro;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

| valor_lib | int(7) | NO | | NULL | |

+-----------+----------+------+-----+---------+-------+

4 rows in set (0.01 sec)

Modificar tipo de dato a un campo de una tabla

mysql> alter table libro modify nom_lib char(50) not null;

Query OK, 0 rows affected (0.48 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe libro;

+-----------+----------+------+-----+---------+-------+

+-----------+----------+------+-----+---------+-------+

| valor_lib | int(7) | NO | | NULL | |

+-----------+----------+------+-----+---------+-------+

4 rows in set (0.06 sec)

Modificar nombre de un campo

mysql> alter table libro change valor_lib precio_lib int(7) not null;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe libro;

+------------+----------+------+-----+---------+-------+

+------------+----------+------+-----+---------+-------+

| precio_lib | int(7) | NO | | NULL | |

+------------+----------+------+-----+---------+-------+

4 rows in set (0.02 sec)

Crear tabla

mysql> create table editorial

-> (cod_editorial int(2) not null primary key,

-> nombre char(20) not null)engine=innodb;

Query OK, 0 rows affected (0.25 sec)

Eliminar clave primaria

mysql> alter table editorial drop primary key;

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

Modificar clave primaria de una tabla.

mysql> alter table editorial modify nombre char(20) not null primary key;

Query OK, 0 rows affected (0.45 sec)

Records: 0 Duplicates: 0 Warnings: 0

Renombrar tabla

mysql> alter table editorial rename to editorial_libro;

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;

+----------------------+

| Tables_in_biblioteca |

+----------------------+

| autor |

| editorial_libro |

| lib_aut |

| libro |

+----------------------+

4 rows in set (0.01 sec)

Eliminar tabla

mysql> drop table editorial_libro;

Query OK, 0 rows affected (0.09 sec)

mysql> show tables;

+----------------------+

| Tables_in_biblioteca |

+----------------------+

| autor |

| lib_aut |

| libro |

+----------------------+

3 rows in set (0.00 sec)

Crear Base de datos

mysql> create database prueba;

Query OK, 1 row affected (0.04 sec)

Mostrar base de datos

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| biblioteca |

| cdcol |

| data |

| matricula |

| mysql |

| performance_schema |

| phpmyadmin |

| prueba |

| test |

| webauth |

+--------------------+

11 rows in set (0.02 sec)

Eliminar Base de datos

mysql> drop database prueba;

Query OK, 0 rows affected (0.43 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| biblioteca |

| cdcol |

| data |

| matricula |

| mysql |

| performance_schema |

| phpmyadmin |

| test |

| webauth |

+--------------------+

10 rows in set (0.00 sec)

mysql> describe libro;

+------------+----------+------+-----+---------+-------+

+------------+----------+------+-----+---------+-------+

| precio_lib | int(7) | NO | | NULL | |

+------------+----------+------+-----+---------+-------+

4 rows in set (0.04 sec)

Insertar datos a una tabla.

mysql> insert into libro values ('00001','Lo que el viento se llevó','norma',500

00);

Query OK, 1 row affected (0.25 sec)

Consultar datos de una tabla

mysql> select * from libro;

+---------+---------------------------+-----------+------------+

+---------+---------------------------+-----------+------------+

| 00001 | Lo que el viento se llevó | norma | 50000 |

+---------+---------------------------+-----------+------------+

1 row in set (0.06 sec)

Seleccionar campo de una tabla

mysql> select nom_lib from libro;

+---------------------------+

| nom_lib |

+---------------------------+

| Lo que el viento se llevó |

+---------------------------+

1 row in set (0.00 sec)

Menú

Contacto

Comandos MySQL