1.
Verifica que el equipo de cómputo se encuentre
conectado.
2.
Enciende el equipo de cómputo.
3.
Abre sesión de ambiente grafico
4.
Ingresa al Sistema Gestor de Bases de Datos
5.
Transcribe el código con las sentencias
generadas en el editor SQL.
6.
Guarda el archivo que contiene las sentencias
SQL elaboradas
7.
Ejecuta el archivo que contiene las sentencias
SQL elaboradas
8.
Imprime las sentencias elaboradas.
Enter
password: ****
Welcome to
the MySQL monitor. Commands end with; or
\g.
Your MySQL
connection id is 1
Server
version: 5.0.51b-community-nt-log MySQL Community Edition (GPL) Type 'help;' or
'\h' for help. Type '\c' to clear the buffer.
mysql>
create database datos;
Query OK, 1
row affected (0.06 sec)
mysql> use datos;
Database changed
mysql> create table alumnos(cvealumno varchar(30) primary
key,nombre varchar(50),a_Paterno varchar(50),a_Materno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),email varchar(100));
Query OK, 0
rows affected (0.03 sec)
mysql> create table profesores(cveprofesor varchar(50)
primary key,nombre varchar(50),aPaterno varchar(50),aMaterno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),especialidad varchar(150),email varchar(100));
Query OK, 0
rows affected (0.09 sec)
mysql>
create table materias(cvemateria varchar(50) primary key,nombre varchar(50),semestre
char);
Query OK, 0
rows affected (0.08 sec)
mysql>
create table calificaciones(cvealumno varchar(30) primary key,cvemateria
varchar(50),par1 double,par2 double,par3 double,par4 double,par5 double,par6
double,par7 double,par8 double,par9 double,calificacionfinal double,periodo
varchar(20));
Query OK, 0
rows affected (0.05 sec)
mysql> create table carreras(cvecarrera varchar(20)
primary key,nombre varchar(50));
Query OK, 0
rows affected (0.08 sec)
mysql> create table grupos(cvegrupo varchar(50) primary
key,nombre varchar(50),cveprofesor varchar(50),cvealumno varchar(50),cvemateria
varchar(50),cvecarrera varchar(50),periodo varchar(20));
Practica de procedimientos
|
Query OK, 0 rows
affected (0.05 sec)
mysql> delimiter //
mysql> create procedure agregar_alumno(incveAlumno
varchar(30),nombre varchar(50),aPaterno varchar(50),aMaterno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),email varchar(100))
-> begin
-> insert into
alumnos values(cveAlumno,nombre,aPaterno,aMaterno,calle,numero,colonia,municipio,estado,telefono,email);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql>
delimiter //
mysql>
call agregar_alumno('002','Luis','Perez','Gonzalez','1_mayo',7,'Consorcio','Cuautitlan_Izcalli','Edo.Mex','58892572','Luis.perez@hotmail.com’);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from alumnos;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| cveAlumno | nombre | a_Paterno | a_Materno | calle | numero | colonia | municipio | estado | telefono | email |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| 002 | Luis
| Perez | Gonzalez | 1_mayo | 7 | Consorcio | Cuautitlan_Izcalli |
Edo.Mex | 58892572 | Luis.perez@hotmail.com |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> delimiter //
mysql> create procedure agregar_profesor(incveProfesor
varchar(50),nombre varchar(50),aPaterno varchar(50),aMaterno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),especialidad varchar(150),email varchar(100))
-> begin
-> insert into
profesores values(cveProfesor,nombre,aPaterno,aMaterno,calle,numero,colonia,municipio,estado,telefono,especialidad,email);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql>
delimiter //
mysql>
call agregar_profesor('008',’Dolores','Hernandez','Rivera',’Jacarandas',15,'Lomas','Tultitlan','Edo.Mex','56-32-47-80',’docente’,'dolores_hndez@outlook.com’);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from profesores;
+-------------+--------+-----------+----------+------------+--------+---------+-----------+---------+----------+--------------+--------------------------+
| cveProfesor | nombre | aPaterno | aMaterno | calle | numero | colonia | municipio |
estado | telefono | especialidad |
email |
+-------------+--------+-----------+----------+------------+--------+---------+-----------+---------+----------+--------------+--------------------------+
| | Dolores | Hernandez | Rivera | Jacarandas | 15 | Lomas | Tultitlan | Edo.Mex | 56-32-47-80 |
docente | dolores_hndez@outlook.com
|
+-------------+--------+-----------+----------+------------+--------+---------+-----------+---------+----------+--------------+--------------------------+
1 row in
set (0.06 sec)
mysql>
delimiter //
mysql>
create procedure agregar_materia(incveMateria varchar(50),nombre
varchar(50),semestre char)
-> begin
-> insert into materias values(cveMateria,nombre,semestre);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql>
delimiter //
mysql>
call agregar_materia('648','Quimica',cuarto);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql>
select * from materias;
+-----------+--------+-----------+-----------+
|
cvemateria | nombre | semestre |
+-----------+--------+-----------+-----------+
| 648|
Quimica |
cuarto|
+-----------+--------+-----------+-----------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql>
delimiter //
mysql>
create procedure agregar_calificacion(incveAlumno varchar(30),cveMateria
varchar(50),par1 double,par2 double,par3 double,par4 double,par5 double,par6
double,par7 double,par8 double,par9 double,calificacionfinal double,periodo
varchar(20))
-> begin
-> insert into
calificaciones values(cveAlumno,cveMateria,par1,par2,par3,par4,par5,par6,par7,par8,par9,calificacionfinal,periodo);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql> delimiter //
mysql> call agregar_calificacion('012','670'8,8,8,8,8,9,7,10,9’,’8.9’,'segundo’);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from calificaciones;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+------+
| cveAlumno | cveCalificacion | par1 | par2 |par3 | par4 |
par5 | par6| par7 | par8 | par9| calificacionfinal|periodo| |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+-------+
| 012 | 670 |
8| 8 |
8 | 8 | 8 |
9 | 7 | 10| 9|
8.9| segundo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+--------+
1 row in set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> delimiter //
mysql> create procedure agregar_carrera(incveCarrera
varchar(20),nombre varchar(50))
-> begin
-> insert into
carreras values(cveCarrera,nombre);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql>
delimiter //
mysql>
call agregar_carrera('001','Informatica');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from carreras;
+-----------+--------+-----------+
| cveCarrera | nombre |
+-----------+--------+-----------+
| 001|
Informatica |
+-----------+--------+-----------+
mysql> delimiter //
mysql> create procedure agregar_grupo(cveGrupo
varchar(50),nombre varchar(50),cveProfesor varchar(50),cveAlumno
varchar(50),cveMateria varchar(50),cveCarrera varchar(50),periodo varchar(20))
-> begin
-> insert into
grupos values(cveGrupo,nombre,cveProfesor,cveAlumno,cveMateria,cveCarrera,periodo);
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql> delimiter //
mysql> call agregar_grupo('301','Los Genios','005','012',’520',’670’,'segundo');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from grupos;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| cveGrupo | nombre | cveProfesor | cvealumno | cveMateria | cveCarrera | periodo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| 301
| Los Genios | 005 |012
| 520 | 670 |
segundo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
Practica de procedimientos para editar
|
Enter
password: ****
Welcome to
the MySQL monitor. Commands end with ;
or \g.
Your MySQL
connection id is 9
Server
version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)Type 'help;' or
'\h' for help. Type '\c' to clear the buffer.
mysql> delimiter//
mysql> create procedure editar_alumno(incveAlumno
varchar(30),nombre varchar(50),aPaterno varchar(50),aMaterno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),email varchar(100))
-> begin
-> update alumnos set cvealumno=cveAlumno,nombre=nombre,aPaterno=aPaterno,aMaterno=aMaterno,calle=calle,numero=numero,colonia=colonia,municipio=municipio,estado=estado,telefono=telefono,email=email;
-> end;
-> //;
Query OK, 0
rows affected (0.06 sec)
mysql>
delimiter//
->call editar_alumno('006','Carlos','Perez','Hernandez','Novillos',46,'Lomas','Tultitlan','Edo.Mex','55786321','Carlos_perez@gmail.com’);
-> //;
Query OK, 0
rows affected (0.06 sec)
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| cvealumno | nombre | a_Paterno | a_Materno | calle | numero | colonia | municipio | estado | telefono | email |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| 006 | Carlos
| Perez | Hernadez | Novillos | 46 | Lomas | Tultitlan | Edo.Mex |
55786321 | Carlos_perez@gmail.com |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> delimiter//
mysql> create procedure editar_profesor(incveProfesor
varchar(50),nombre varchar(50),aPaterno varchar(50),aMaterno varchar(50),calle
varchar(150),numero int,colonia varchar(50),municipio varchar(50),estado
varchar(50),telefono varchar(20),especialidad varchar(150),email varchar(100))
-> begin
-> update
profesores set cveProfesor=cveProfesor,nombre=nombre,aPaterno=aPaterno,aMaterno=aMaterno,calle=calle,numero=numero,colonia=colonia,municipio=municipio,estado=estado,telefono=teléfono,especialidad=especialidad,email=email;
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql>
delimiter //
mysql>
call editar_profesor('045','Maria','Monter','Rodriguez','Amaranto',14,'valle de
tules','Tultitlan','edo_mex','55221034','Maestra primaria','marihermosa54@outlook.com');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from profesores;
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
| cveprofesor | nombre | aPaterno | aMaterno
| calle | numero | colonia |
municipio | estado | telefono |
especialidad | email |
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
| 045 | Maria
| Monter | Rodriguez | Amaranto |
14 | valle de tules | Tultitlan |
edo_mex | 55221034 | Maestra primaria | marihermosa54@outlook.com |
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
1 row in
set (0.00 sec)
mysql>
delimiter //
mysql> create procedure editar_materia(incveMateria
varchar(50),nombre varchar(50),semestre char)
-> begin
-> update materias set cveMateria=cveMateria,nombre=nombre,semestre=semestre;
-> end;
-> //;
Query OK, 0
rows affected (0.08 sec)
mysql>
delimiter //
mysql> call
editar_materia('052','Fisica','Tercero');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from materias;
+-----------+--------+-----------+-----------+
| cvemateria | nombre | semestre |
+-----------+--------+-----------+-----------+
| 052| Fisica |
Tercero |
+-----------+--------+-----------+-----------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql>
delimiter //
mysql>
create procedure editar_calificacion(incveAlumno varchar(30),cveMateria
varchar(50),par1 double,par2 double,par3 double,par4 double,par5 double,par6
double,par7 double,par8 double,par9 double,calificacionfinal double,periodo
varchar(20))
-> begin
-> update
calificaciones set cveAlumno=cveAlumno,cvMeateria=cveMateria,par1=par1,par2=par2,par3=par3,par4=par4,par5=par5,par6=par6,par7=par7,par8=par8,par9=par9,calificacionfinal=calificacionfinal,periodo=periodo;
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql> delimiter //
mysql> call editar_calificacion('026','002',4,7,7,7,6,10,9,6,6,6.8,'cuarto’);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from calificaciones;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+------+
| cveAlumno | cvecalificacion | par1 | par2 |par3 | par4 |
par5 | par6| par7 | par8 | par9| calificacionfinal|periodo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+-------+
| 026 | 002 |
4| 7 | 7 |
7 | 6 | 10 |
9 | 6| 6|
6.8| cuarto|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+--------+
1 row in set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> delimiter //
mysql> create procedure editar_carrera(incveCarrera
varchar(20),nombre varchar(50))
-> begin
-> update
carreras set cveCarrera=cveCarrera,nombre=nombre;
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql> delimiter //
mysql> call editar_carrera('014','Conservacion Ambiental');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from carreras;
+-----------+--------+-----------+--------+
| cveCarrera | nombre |
+-----------+--------+-----------+---------+
|014| Conservacion Ambiental|
+-----------+--------+-----------+----------+
mysql> delimiter //
mysql> create procedure editar_grupo(incvegrupo
varchar(50),nombre varchar(50),cveprofesor varchar(50),cvealumno
varchar(50),cvemateria varchar(50),cvecarrera varchar(50),periodo varchar(50))
-> begin
-> update
grupos set cveGrupo=cveGrupo,nombre=nombre,cveProfesor=cveProfesor,cveAlumno=cveAlumno,cveMateria=cveMateria,cveCarrera=cveCarrera,periodo=periodo;
-> end;
-> //;
Query OK, 0
rows affected (0.00 sec)
mysql> delimiter //
mysql> call editar_grupo('304','Los Imaginarios
','095','006',’067',’018’,'cuarto');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from grupos;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| cveGrupo | nombre | cveProfesor | cvealumno | cveMateria | cveCarrera | periodo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| 304
| Los Imaginarios | 095 |006
| 067 | 018 |
cuarto|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
Procedimientos para eliminar registros
|
mysql> create procedure eliminar_alumno(incvealumno
varchar(30))
-> begin
-> delete from
alumnos where cvealumno=cvealumno;
-> end;
-> //;
Query OK, 1
row affected (0.06 sec)
mysql>
delimiter //
mysql>
call eliminar_alumno('006','Carlos','Perez','Hernandez','Novillos',46,'Lomas','Tultitlan','Edo.Mex','55786321','Carlos_perez@gmail.com’);
-> //;
Query OK, 0
rows affected (0.06 sec)
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| nombre | a_Paterno | a_Materno | calle | numero | colonia | municipio | estado | telefono | email |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
| Carlos |
Perez | Hernadez | Novillos | 46 | Lomas | Tultitlan | Edo.Mex |
55786321 | Carlos_perez@gmail.com |
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> create procedure eliminar_profesor(incveprofesor
varchar(50))
-> begin
-> delete from profesores where
cveprofesor=cveprofesor;
-> end;
-> //;
Query OK, 1
row affected (0.06 sec)
mysql>
delimiter //
mysql>
call eliminar_profesor('045','Maria','Montesinos','Rodriguez','Amaranto',12,'Paraje','Tultitlan','edo_mex','55221034','Maestra_primaria','marihermosa54@outlook.com');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from profesores;
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
| nombre | aPaterno
| aMaterno | calle | numero | colonia | municipio |
estado | telefono | especialidad | email |
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
| Maria | Montesinos
| Rodriguez | Amaranto | 12 |
Paraje | Tultitlan | edo_mex | 55221034
| Maestra_primaria | marihermosa54@outlook.com |
+-------------+--------+------------+-----------+----------+--------+---------+-----------+---------+----------+------------------+---------------------------+
1 row in
set (0.00 sec)
mysql> create procedure eliminar_materia(incvemateria
varchar(50))
-> begin
-> delete from materias where
cvemateria=cvemateria;
-> end;
-> //;
Query OK, 1
row affected (0.06 sec)
mysql>
delimiter //
mysql> call eliminar_materia('067','Tecnologia','Tercero');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from materias;
+-----------+--------+-----------+-----------+
| nombre | semestre |
+-----------+--------+-----------+-----------+
| Tecnologia | Tercero
|
+-----------+--------+-----------+-----------+
1 row in
set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> create procedure eliminar_calificacion(incvealumno
varchar(30))
-> begin
-> delete from
calificaciones where cvealumno=cvealumno;
-> end;
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> delimiter //
mysql> call eliminar_calificacion('006','009',4,7,7,7,6,10,9,6,6,6.8,'cuarto’);
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from calificaciones;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+------+
| cvecalificacion | par1 | par2 |par3 | par4 | par5 | par6| par7
| par8 | par9| calificacionfinal|periodo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+-------+
| 009 | 4| 7 |
7 | 7 | 6 |
10 | 9 | 6|
6| 6.8|
cuarto|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+----------+------------------------+--------+
1 row in set (0.01 sec)
Query OK, 0
rows affected (0.09 sec)
mysql> create procedure eliminar_grupo(incveGrupo
varchar(50))
-> begin
-> delete from
grupos where cveGrupo=cveGrupo;
-> end;
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> delimiter //
mysql> call eliminar_grupo('406','Los_ingeniers','045','006',’067',’008’,'cuarto');
-> //;
Query OK, 1
row affected (0.06 sec)
mysql> select * from grupos;
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| nombre | cveProfesor | cvealumno | cveMateria | cveCarrera | periodo|
+-----------+--------+-----------+-----------+--------+--------+-----------+--------------------+---------+
| Los_ingeniers | 045
|006 | 067 |
008 | cuarto|
