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

mailto:[email protected]

mailto:[email protected]


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)


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:


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.


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)


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;


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


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


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;


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


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.


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


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


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


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)


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


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


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)


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


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


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


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


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


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

Taller de sql

Education

Transcript of Taller de sql