Download - Taller de sql
DIPLOMADO DE INFORMATICA
Taller de SQL
1
Lupita Betancourt Instructor:MTI. Noè Càzarez Camargo [email protected] [email protected]
TALLER DE SQL LUNES 20/07/09
MySQL: instalación rápida. Interfaces en donde puede ejecutarse: MySQL Browser, MySQL Aminitrador, PHp Myamin Eidy. SQLServer: requiere requisitos específicos
Ejercicio: diseñar una b.d para llevar el control de las calificaciones y la asistencia de los estudiantes. Control Escolar (Ctrl_Esc)
Alumnos Materias Especialidad Grupo Plantel Gupo_alum
Calificaciones
*Matricula * Clave_mat(vc)**
*IdEsp (vc) *GrupoId *Clv_plantel GrupoId Matricula **(vc)
Apaterno Nombre_mat (vc)**
Nombre(vc) Nombre_gpo Nombre_plan Matricula Clave_mat (vc)**
Amaterno Semestre (vc)** Descripcion(vc)
Semestre Direcciòn_plan
FechaI IdMaestro (n)**
Nombres IdEsp (n)** Turno Telefono_plan FechaB (fecha de baja)
Cal1(n)def 0
Especialidad Creditos (n) def 0
Ciudad_plan Motivo Cal2(n)def 0
Plantel Status(b) def 0 Status Cal3(n)def 0
Calle Componentevc**
CalFin(n) def 0
Numero R(n)def0
Colonia Maestros Materia_Maes
Asistencia *GrupoId def 0
CiuadadR (donde vive)
*IdMaestro (n) autoinc
IdMaestro Matricula Status
CP Nombre_maes (vc)
Clave_mat Clave_mat FechaB vc
Telefono App (VC) Status (si/no) F1(faltas) (n) def 0
Email ApmVC) Justificada F2(n) def 0
Fnacimiento (Fn)
F3(n) def (0)
Sexo
Status
Fecharegistro (FechaR)
Foto
PadreTutor
DIPLOMADO DE INFORMATICA
Taller de SQL
2
Relaciones
Matricula de la tabla Alumnos (1) con Matricula de la tabla Grupo_Alum (V)
GrupoId de la tabla Grupo (1) con GrupoId de la tabla Grupo_Alum (V)
IdMaestro de la tabla Maestros (1) con IdMaestro de la tabla Maestros (V)
DIPLOMADO DE INFORMATICA
Taller de SQL
3
MySQL
Pasos para la instalación: 1. DBSM: mysql-essential 2. Interfaz: mysql-gui-tool 3. DB dessigner. 4. Driver para que se pueda conectar con mysql: mysql-connector.net
Pasos para ver la interfaz:
a) Inicio b) Todos los programas c) MySQL d) MySql Query Browser
Show databases: muéstrame las bd que tienes:
DIPLOMADO DE INFORMATICA
Taller de SQL
4
SQL SE DIVIDE EN 2 LENGUAJES:
DDL: lenguajes de definición de datos. Instrucciones para definir la estructura de la bd.
Show
Use
Create
Drop
Alter
DML: lenguajes de manipulación de datos. Es aquel con el cual se puede visualizar o alternar los datos contenidos
en una tabla.
Select
Update
Delete
Insert
Count
Sum
Avg
TRABAJAREMOS CON DLL
1ro. En línea de comandos.
1. Crear la BD
Create DataBase nombre_bd; Create DataBase Ctrl_Esc;
Nota: para ver la BD: show databases para ver la BD creada.
DIPLOMADO DE INFORMATICA
Taller de SQL
5
2. Eliminar BD
Drop DataBase nombreBD; Drop Databse Ctlr _ESc;
Usar la BD; marca la BD que se esta utilizando
Use nombreBD;
3. Crear Tabla
Engeni: define bajo que estructuras vamos a trabajar. Hay os formas la clásica (MYISAM, desventajas llega más
fuerte, no puedo hacer transacciones, relaciones etc) y la otra es INNODB (permite transacciones y relaciones en
la estructura)
DIPLOMADO DE INFORMATICA
Taller de SQL
6
Create Table nomtabla (nombrecampo1 tipo (longitud),nombre……) Engine __________;
Create table alumnos (Matricula integer , Nombre varchar(45) ) engine innodb;
Show tables: ver que la tabla se creo
2do. Con interfaz
TAREA/EJERCICIO DE REFORZAMIENTO:
Crear la tabla de alumnos con todos los campos.
Crear la tabla
MARTES 21/07/09
Borrar una tabla
Drop Table <nombre_tabla>
Not null: significa que no se puede dejar campos vacios.
Engine Type (es lo mismo)
create table Alumnos (Matricula varchar(15) not null, Apaterno varchar(45)not null, Amaterno varchar(45)not n
ull, Nombres varchar(45)not null, Estatus boolean not null default 0) Engine InnoDB;
DIPLOMADO DE INFORMATICA
Taller de SQL
7
Muestra la estructura de la tabla, cualquiera de las dos formas que se muestran a continuación:
Describe <nombre_tabla>;
Desc <nombre_tabla>;
PARA CREAR EL CAMPO LLAVE PRIMARIA
PRIMARY KEY
Create table Alumnos (Matricula varchar(15) not null, Apaterno varchar(45)not null, Amaterno varchar(45)not
null, Nombres varchar(45)not null, Estatus boolean not null default 0, Primary Key (Matricula)) Engine InnoDB;
CAMPO AUTOINCREMENTABLE
Auto_icrement: para que una columna en la tabla sea un dato que se esté generando solo, ejemplo 1..2..3 y se
vaya incrementando solo.
Unsigned: sin signo (no puede haber negativos)
Create table Alumnos (Id integer unsigned not null Auto_increment, Matricula varchar(15) not null, Apaterno
varchar(45)not null, Amaterno varchar(45)not null, Nombres varchar(45)not null, Estatus boolean not null
default 0, Primary Key (Matricula)) Engine InnoDB;
Esto genera un error porque el campo incrementable lo toma como llave primaria y ya tenemos un campo como
llave primaria que es la matricula, entonces hay que hacer lo siguiente.
Estructura de la tabla
Funciones para
mostrar la estructura
de la tabla
Clave o Llave
Primaria
DIPLOMADO DE INFORMATICA
Taller de SQL
8
Create table Alumnos (Id integer unsigned not null Auto_increment, Matricula varchar(15) not null, Apaterno
varchar(45)not null, Amaterno varchar(45)not null, Nombres varchar(45)not null, Estatus boolean not null default
0, primary key(Id))Engine InnoDB;
CREA LA TABLA SINO EXISTE (NO VER)
Create table if not exist <nombreDB><campo tipo (),….) engine;
Create table if not exists Alumnos (Id integer unsigned not null Auto_increment, Matricula varchar(15) not null,
Apaterno varchar(45)not null, Amaterno varchar(45)not null, Nombres varchar(45)not null, Estatus boolean not
null default 0, primary key(Id))Engine InnoDB;
Nota: para hacer referencia a una b.d y una tabla especifica: nombreBD.NombreTabla
CREA LA B.D SINO EXISTE
Create Database if not exist s <nombre_base_de_datos>
MODIFICAR LA ESTRUCTURA DE UNA TABLA SIN BORRAR LA TABLA
ALTER: modifica la tabla
Alter table <nombretabla>
Add column <nombre column> tipo (longitud);
Alter table Alumnos add column Especialidad varchar(45) not null;
Ya existe
la tabla
DIPLOMADO DE INFORMATICA
Taller de SQL
9
Alter table nombre tabla change nombre campo nombre del campo por el que se quiere cambiar: renombra un
campo.
Alter table Alumnos change ESPECIALIDAD Especialidad integer not null;
BORRAR UNA COLUMNA DE LA TABLA
Alter Table nombre_tabla drop <name>
Ejemplo:
Alter Table Alumnos drop Especialidad;
AGREGA UNA COLUMNA ANTES DE UN CAMPO
Alter table Alumnos add Column Demo varchar (45) not null after Nombres;
DIPLOMADO DE INFORMATICA
Taller de SQL
Ejercicio
Creación de las cuatro tablas:
Create table Alumnos (Matricula varchar(15) not null, Apaterno varchar(45)not null, Amaterno varchar(45)not null, Nombres varchar(45)not null,
Especialidad varchar (45), Plantel integer not null, Calle varchar (45), Numero varchar(5), Telefono varchar (20) not null, Primary Key (Matricula)) Engine
InnoDB;
Create table Especialidad (IdEsp integer unsigned not null Auto_increment, Nombre varchar (45) not null, Descripcion varchar (45), primary
key(IdEsp))Engine InnoDB;
Create table Grupo(GrupoId integer unsigned not null auto_increment, NombreGpo varchar(20) not null, Semestre varchar (20) not null, Turno varchar (30)
not null)) Engine InnoDB;
Create table Grupo_Alum(GrupoId integer not null, Matricula varchar(15)not null, FechaI varchar(40)not null, FechaB varchar(40), Motivo varchar(45)not
null, Estatus boolean not null default 0)Engine InnoDB;
Alumnos Tipo de Dato Especialidad Tipo de Dato Grupo Tipo de Dato Gupo_alum Tipo de Dato
*Matricula Vc not null *IdEsp (vc) N autoinc nnull
*GrupoId N autoinc not null
GrupoId Vn not null
Apaterno Vc not nul Nombre(vc) Vc not null NombreGpo Vc not null Matricula Vc not null
Amaterno Vc not nul Descripcion(vc) vc Semestre Vc not null FechaI Vc not null
Nombres Vc not nul Turno Vc not null FechaB (fecha de baja)
vc
Especialidad Vc Motivo Vc not null
Plantel N not nul Estatus Bool not null
Calle, Numero Vc, VC
Telefono Vc
DIPLOMADO DE INFORMATICA
Taller de SQL
11
UTLIZANDO LA INTERFAZ DE MySQL
Pasos para crear una base de datos utilizando la interfaz:
1. Clic derecho a la base de datos y selecciona Create New Schema:
2.
DIPLOMADO DE INFORMATICA
Taller de SQL
12
COMO HACER UN ENLACE ENTRE TABLAS (RELACIONES)
FOREIGN KEY (<NOMBRE_CAMPO>): de que campo. (tabla hija)
REFERENCES <NOMBRE_TABLA> (<NOMBRE>); a que tabla y en qué campo.
Ejemplo:
Create table Alumnos (Matricula varchar (15), primary key (Matricula)) Engine InnoDB;
Create table Grupo_Alum (Matricula varchar(15), FechaI varchar(45) not null, foreign key (Matricula) References
Alumnos (Matricula)) Engine InnoDB;
TAREA: Terminar la tabla con los campos y las validaciones.
MIERCOLES 22/07/09
Padre_alum hijo_esp, Padre hijo Padre Especialidad Alumnos Grupo_Alum Grupos IdEsp Matricula Matricula Idgrupo Nombre Apaterno Idgrupo Nombre Descripcion Amaterno Estatus Periodo Nombre FechaB Semestre Plantel Motivo IdEsp
Calificaciones Materias Maestros Matricula Clave_mat IdMaestro Clave_mat Nombre_mat IdMaestro GrupoId
Nota: Los de color azul son los padres.
Actualización en Cascada: si borras en el padre automáticamente se borra en el hijo. Es decir sin que lo ejecute
solito se borra.
Set Null: nada más borra lo que se le indica no borra todo el registro. Es inusual porque se pierde la integridad
referencial.
No Action: si tú borras aquí, marca un error porque en otra tabla se está utilizando, o funciona al revés tienes que
borrar en el hijo primero y después en el padre, OJO tienes que borrar todas las ligas existentes. Recomendada.
Restrict: similar a la anterior. Recomendada.
Set Defaull: es similar a actualización en cascada, borras en el padre y puedes poner el valor que tú quieras.
Ejemplo de relaciones:
Relaciones Entre tablas
DIPLOMADO DE INFORMATICA
Taller de SQL
13
RELACION TABLA GRUPO_ALUM CON ALUMNOS Y GRUPOS
Create table grupo_alum (Matricula varchar(45)not null, GrupoId integer unsigned not null, Estatus boolean
not null Default 0, FechaB varchar(45), FechaI varchar(45) not null, Motivo varchar(45), Index (Matricula),
foreign Key (Matricula) References Alumnos (Matricula) on Delete Restrict on update Restrict, Index (GrupoId),
foreign Key (GrupoId) References grupo (GrupoId) on delete Restrict on update Restrict) engine InnoDB;
1. Para crear la tabla anterior primero hay que borrar la tabla grupo_alum. 2. Posteriormente crear la tabla anterior. 3. Y ya está creada la referencia.
4. Borrar la tabla de alumnos, observa que pasa… 5. No se puede borrar por la integridad que existe.
Ahora crear la relación con la tabla especialidad
RELACION ESPECIALIDAD CON ALUMNOS
1. Modificar el campo especialidad de la tabla Alumnos:
Alter table alumnos change Especialidad IdEsp integer unsigned not null;
2. Crear la tabla alumnos sin borrar la tabla de alumnos anterior, para crear la relación:
Alter table alumnos Add index(IdEsp), Add constraint “nombre_relación “ foreign key (IdEsp) References especialidad(IdEsp) on Update restrict on delete restrict; Es lo mismo que escribirlo en una sola línea: Alter table alumnos Add index(IdEsp), Add constraint EspAlum foreign key (IdEsp)References especialidad(IdEsp)
on Update restrict on delete restrict;
RELACIÒN DE CALIFICACIONES CON ALUMNOS, CON MATERIA Y CON MAESTRO UTILIZANDO LA INTERFAZ
1. Clic derecho a la tabla que desea crear las relaciones y seleccionar Edit Table:
Borrar la tabla grupo_alum
Crear la tabla
grupo_alum
con las
relaciones Mostrar la
tabla
grupo_alum
DIPLOMADO DE INFORMATICA
Taller de SQL
14
2. Crear los índices:
a) Selecciona el campo al cual deseas crear el índice
b) Selecciona el signo màs para que lo agregue
c) Y ya agregado al índice se le pone la llavecita y listo, así a ser con cada uno de los índices que se
necesitaran para crear la relación.
3. Crear las relaciones:
a) Clic a la pestaña de Foreign Keys
b) Clic al signo de màs para agregar la relación
c) Aparece el siguiente cuadro en el cual se escribe el nombre de la relación
DIPLOMADO DE INFORMATICA
Taller de SQL
15
d) Después se selecciona con que tabla se crea la relación y aparecerá la llave principal y debe coincidir con
la de la tabla actual, tal como se muestra a continuación
ENTRAR A MYSQL COMO ADMINISTRADOR
Esto es con el objetivo de administrar nuestra base de datos para poder ponerle “ “ para que protegernos de
virus.
Vamos a crear un usuario para solamente nosotros tener los privilegios de la BD
Selecciona la tabla
Es la clave de la
tabla
seleccionada
Clave de la tabla actual
(en la que se está
trabajando)
DIPLOMADO DE INFORMATICA
Taller de SQL
16
En esta ventana establezco que es lo que puede hacer el usuario:
1. Selecciono ctrl_esc y le digo de las opciones del lado derecho que es lo que puede hacer
2. Clic en el botón Apply changes
3. Crear respaldos a la BD
a) Backup b) New proyect
DIPLOMADO DE INFORMATICA
Taller de SQL
17
c) Selecciona la BD que deseas agregar d) Clic a la flechita para pasarla al otro lado
(Puedo hacer respaldos de todas las que quiera) e) Execute
f) Se guarda seleccionando la ubicación deseada
g) Para abrirlo se debe de abrir con Wordpad
PARA RECUPERAR LA BASE DE DATOS
1. IR AL ADMINISTRADOR 2. SELECCIONAR 3. RESTORE 4. BUSCAR EL ARCHIVO 5. INICIAR RECUPERACIÓN 6. Y LISTO YA ESTA DE NUEVO LA B.D TAL COMO SE DEJO LA ULTIMA VEZ QUE SE GUARDO EL RESPALDO
DIPLOMADO DE INFORMATICA
Taller de SQL
18
MANIPULACIÓN DE DATOS (DML)
COMO GUARDAR REGISTROS Y SUS DIFERENTES USOS
Insert into <nombre_tabla> (campo1, campo2….) Values (valor1, valor2);
NOTAS:
* Ver copias * # Campos = # valores * Mismo orden en campos = orden de los valores Ejemplo: Insert into materias(Clave_mat, Nombre_mat, Semestre, IdEsp, Creditos, Componente) Values ('01', 'sql', '4to', 1, '10', 'Profesional'); VER CONTENIDO DE UNA TABLA Select * from nombre_tabla; Select * from materias;
JUEVES 23/07/09 El maestro nos dio la siguiente b.d. y nos la paso en archivo para su restauración y utilización el archivo se llama punto_venta y se reestructura en MYSQL administrador.
B.D. PUNTO DE VENTA
PRODUCTOS productoID proveedorID stock
ESTADO estadoID nombre
ALMACEN almacenID productoID
PROVEEDODORES proveedorID estadoID ciudadID
CIUDAD ciudadID estadoID
CLIENTES clientsID estadoID ciudadID
VENTA ventaID clientsID
DET_VENTA ventaID productoID
1. Registrar el estado (1)
insert into estados (NOMBRE) values ('Sinaloa'); 2. Registrar la ciudad (la ciudad depende del estado) (1)
insert into ciudades (NOMBRE, ESTADOID) values ('Escuinapa',1); 3. Registrar proveedor (1)
insert into proveedores (NOMBRE, CIUDADID, ESTADOID, TELEFONO) values ('Ley',1,1,6621-15-08-20); 4. Registrar productos (3)
DIPLOMADO DE INFORMATICA
Taller de SQL
19
insert into productos (NOMBRE, DESCRIPCION, PRECIO, COSTO, PROVEEDORID) values ('Jabòn','de tocador',10, 6,1); insert into productos (NOMBRE, DESCRIPCION, PRECIO, COSTO, PROVEEDORID) values ('Shampoo','kids',50, 35,1); insert into productos (NOMBRE, DESCRIPCION, PRECIO, COSTO, PROVEEDORID) values ('Crema','manos',45, 30,1);
PARA NO REPETIR LINEA POR LINEA: ESTA ES OTRA FORMA DE HACER LO MISMO Y NO REPETIR (INSERCIÓN MULTIPLE) INSERT INTO <NOM_TABLA> (CAMPO1, CAMPO2) VALUES (VALOR1, VALOR2..),(VALOR .1, VALOR2.2 …), (VALOR1.1.1, VALOR2.2.2 ….);
5. Registrar clientes (1) insert into clientes (NOMBRE, APATERNO, AMATERNO, RFC, CALLE, NUMERO, ESTADOID, CIUDADID) values ('Lupita','Sandoval','Ramos','SARL210881','Guamuchil','5a',1 ,1 );
6. Registrar la venta (1) insert into ventas (FECHA, MONTO, CLIENTSID) values ('23/07/09', 500.30, 1);
7. Registrar detalle de venta (1) insert into det_venta (VENTAID, PRODUCTOSID, CANTIDAD, PRECIO, DESCUENTO) values (1,1, 5,10, .25), (1,2, 2,50, 0), (1,3, 3,45, 0);
7.1. Registrar movimiento de almacén (1) insert into almacen (PRODUCTOSID, FECHAMOV, TIPOMOV) values ( 1, '23/07/09','SALIDA'), (2, '24/07/09','SALIDA'), (3, '24/07/09','SALIDA'); Nota: el paso 7 y el 7.1 se repite 3 veces porque agregamos tres productos. OTRA FORMA DE HACER LO ANTERIOR ES EN EL AMBIENTE VISUAL
1. SE LE DA DOBLE CLIC A LA TABLA 2. SE ABRE LOS CAMPOS PARA SER LLENADOS 3. CLC EN EDIT Y EMPEZAR A LLENAR LOS CAMPOS O COLUMNAS 4. UNA VEZ REGISTRADOS LOS DATOS, CLIC EN APPLY CHANGES
1
4 3
2
DIPLOMADO DE INFORMATICA
Taller de SQL
20
Falta
Select * from clientes order by Apaterno desc (los ordena descendentemente)
VIERNES 24/07/09
NOS PASO UNA CARPETA QUE SE LLAMA CECYTES_SQL
Nota es utilizando la estructura: INNODB
1. Transacción (transaction). Intercambio de datos o de objetos.
De la aplicación manda la orden al servidor y se ejecuta la b.d y regresa a la aplicación.
Instrucción y ejemplo:
Start Transaction;
Insert into productos (nombre, precio, costo, proveedorID) values (“XXXX”, 10,5, 1), (“yyy”, 15, 10, I), (“zzzz”, 20, 10, 1);
Delete from productos;
RollBack;
Lo correcto de la sentencia anterior seria poner la condición porque si no
estaríamos borrando todo el contenido de la tabla.
Es como ctrl+Z, y lo que hace esta instrucción es que no ejecutaría la
instrucción anterior pero tampoco el insertar. (No hagas nada de lo que te
dije antes). Si quisiéramos que si ejecutara la instrucción pondríamos la
instrucción comit QUE ES EJECUTAR LAS TRANSACCIONES.
1. STAR TRANSACTION
CTRL + ENTER 2. INSERTO INTRO, CTRL
+ENTER 3. ROLLBACK CTRL+ENTER 4. SELECTE, CTRL + ENTER
1. STAR TRANSACTION CTRL + ENTER
2. INSERTO INTRO, CTRL +ENTER
3. COMMIT CTRL+ENTER 4. SELECT, CTRL + ENTER
DIPLOMADO DE INFORMATICA
Taller de SQL
21
BORRADO
FORMATO:
Delete from <nom_tabla> where campo = valor;
NOTA: NO SE DEBEN DE DEFINIR COLUMNAS (no va el * antes del from).
Si se ejecuta el contenido de la tabla sin la condición borra todo el contenido de la tabla. Se recomiendo en vez
del delete un borrado lógico.
Limit: borra los primeros seis renglones que encuentre.
Delete from productos where nombre like ‘A%’ Limit 2
Delete from productos where productosid = 3 or productid = 8
Delete from Det_Venta, productos where Det_venta.productoID = productos.productosID
Delete from Det_venta, venta where det.ventaID = Det_venta.ventaID and Venta.Fecha = ‘ ‘
Abrimos la carpeta que nos pasó el maestro de CECYTES_SQL….
1. Abrimos el archivo CECYTES_SQL y se abre visual studio 5.0
2. Abrir frmBusqueda.vb
3. Se abre esta ventana
borra los primeros dos registros que empiecen con A
borra el producto que tenga el 3 o el 8
Borra todos los productos que se hayan vendido
Borra todas las ventas y su detalle de venta que se hayan vendido en la fecha establecida
DIPLOMADO DE INFORMATICA
Taller de SQL
22
4. Observar el código
El objetivo de esto es conectarnos a la base de datos punto_venta y que nos permita efectuar
búsquedas y consultas de selección (select), ejecutar sql (update, insert, delete)
5. Seleccionamos la primera pestaña de búsqueda para iniciar la programación.
a) Selccionamos la ventana
b) Nos vamos a las propiedades
c) Clic al icono del rayo
d) En el evento load dar doble clic y nos vamos a la ventana de código
DIPLOMADO DE INFORMATICA
Taller de SQL
23
6. Ligar la base de datos con el gridDatos
7. Repite los registros, para que no lo haga hay que cambiar la propiedad del GRID ___________
o poner las columnas
8. Resultado al momento de correr la aplicación
1. Clic y clic
en edit
colums
3. cambia su nombre como esta en la b.d. 2. selecciona
DIPLOMADO DE INFORMATICA
Taller de SQL
24
9. Programar el botón buscar, doble clic al botón, nos vamos a la ventana de código y tecleamos el
código. Buscara por el código del producto
SI QUEREMOS BUSCAR POR NOMBRE Y ORDENAR SERIA
Dim sql As String = "Select * from productos where NOMBRE='" & txtValor.Text &
"'order by NOMBRE"
AHORA SI QUEREMOS QUE EMPIECE CON LA LETRAS SERIA, ESTO SERIA LO IDEAL
Dim sql As String = "Select * from productos where NOMBRE LIKE'%" & txtValor.Text &
"%'"
INVESTIGAR SQL INYECTION
BOTON GUARDAR
1. DOBLE CLIC AL BOTON
2. CODIFICAR
3. Private Sub btnGuardarA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGuardarA.Click
Dim sql As String = "insert into productos (NOMBRE, DESCRIPCION, PRECIO,
COSTO, PROVEEDORID) values ('" & txtNombre.Text & " ','" &
txtDescripcion.Text & "'," & txtPrecio.Text & "," & txtCosto.Text & ",1)"
Dim obj As BaseDatos
obj = New BaseDatos
If obj.EjecutarSql(sql) Then
MessageBox.Show("Registro guardado")
Else
Me.txtMsgError.Text = obj.getMsgError
End If
End Sub