Creacion de Indices
Para la creacion del indice de una tabla se utiliza el siguiente comando en MySQL:
mysql> CREATE INDEX id_index USING BTREE ON cliente (nombre);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
Tamaño Tablas
Tamaño Bases de Datos
Asignación del tamaño máximo de la Base de Datos
Mediante el siguiente comando podemos asignar el tamaño maximo deseado para la tabla, sacando el calculo exacto de tamaño de la tabla por cada registro capturado se obtendria el tamaño maximo que ocuparian 100 registros con 6 visitas por registro de clientes:
shell> mysql --max_allowed_packet=16777216 shell> mysql --max_allowed_packet=16M
Diagrama de la Base de Datos
Creación y llenado de las tablas Visita y Cliente
Clientes:
mysql> DESCRIBE cliente;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| numcliente | int(11) | NO | PRI | NULL | |
| nombre | varchar(30) | YES | | NULL | |
| telefono | int(10) | YES | | NULL | |
| direccion | varchar(30) | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.04 sec)
mysql> insert into cliente (numcliente,nombre,telefono,direccion,email) values (
'20','Kevin','6566821515','bien lejos en las torres','kevinmalo@hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into cliente (numcliente,nombre,telefono,direccion,email) values (
'21','Janeth','6566821516','bien lejos en el centro','janethmala@hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into cliente (numcliente,nombre,telefono,direccion,email) values (
'22','Harry','6566823616','bien lejos en su casa','harrypotter@hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into cliente (numcliente,nombre,telefono,direccion,email) values (
'23','Erick','6566425616','por las torres','peñanieto@hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into cliente (numcliente,nombre,telefono,direccion,email) values (
'24','Cukita','6566285616','Av tegnoligico 2050','cukita@hotmail.com');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> Select * FROM cliente;
+------------+------------+------------+--------------------------+-------------
------------+
| numcliente | nombre | telefono | direccion | email
|
+------------+------------+------------+--------------------------+-------------
------------+
| 1 | Juan Perez | 2147483647 | CAmino nose | juanperez@gm
ail.com |
| 2 | Harry Mndz | 2147483647 | CAmino real | harrypotter@
gmail.com |
| 3 | Harry Pack | 2147483647 | CAminar | harrypack@gm
ail.com |
| 20 | Kevin | 2147483647 | bien lejos en las torres | kevinmalo@ho
tmail.com |
| 21 | Janeth | 2147483647 | bien lejos en el centro | janethmala@h
otmail.com |
| 22 | Harry | 2147483647 | bien lejos en su casa | harrypotter@
hotmail.com |
| 23 | Erick | 2147483647 | por las torres | peñanieto@ho
tmail.com |
| 24 | Cukita | 2147483647 | Av tegnoligico 2050 | cukita@hotma
il.com |
+------------+------------+------------+--------------------------+-------------
------------+
8 rows in set (0.00 sec)
Visitas:
mysql> CREATE TABLE visita (nombre CHAR(20) NOT NULL, razon CHAR(50), fecha VARC
HAR(10));
Query OK, 0 rows affected (0.10 sec)
mysql> show tables
-> ;
+-----------------------+
| Tables_in_veterinaria |
+-----------------------+
| cliente |
| general |
| general1 |
| mascota |
| visita |
+-----------------------+
5 rows in set (0.00 sec)
mysql> insert into visitas (nombre,razon,fecha) values ('Cukita','Garrapatas','1
0/03/2012');
ERROR 1146 (42S02): Table 'veterinaria.visitas' doesn't exist
mysql> insert into visita (nombre,razon,fecha) values ('Cukita','Garrapatas','10
/03/2012');
Query OK, 1 row affected (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Janeth','Baño','10/10/20
12');
Query OK, 1 row affected (0.03 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Cukita','Cortada en una
pata','10/12/2012');
Query OK, 1 row affected (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Janeth','Extraccion de d
iente','18/11/2012');
Query OK, 1 row affected (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Harry','Revision rutinar
ia','23/01/2013');
Query OK, 1 row affected (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Harry','Desparasitacion'
,'03/03/2013');
Query OK, 1 row affected (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Erick','Paty cure','08/0
10/2012');
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Erick','Corte de pelo','
08/05/2012');
Query OK, 1 row affected (0.03 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Kevin','Curacion menor',
'08/05/2012');
Query OK, 1 row affected (0.03 sec)
mysql> insert into visita (nombre,razon,fecha) values ('Kevin','Baño','08/09/201
2');
Query OK, 1 row affected (0.02 sec)
mysql>
Creación de usuarios y sus privilegios:
Setting environment for using XAMPP for Windows.
Administrador@Administrador-PC d:\xampp
# cd mysql
Administrador@Administrador-PC d:\xampp\mysql
# cd bin
Administrador@Administrador-PC d:\xampp\mysql\bin
# mysql -h localhost -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
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 database
-> ;
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 'datab
ase' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| mysql |
| performance_schema |
| phpmyadmin |
| Administrador |
| test |
| veterinaria |
| webauth |
+--------------------+
9 rows in set (0.01 sec)
mysql> use veterinaria
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_veterinaria |
+-----------------------+
| cliente |
| general |
| general1 |
| mascota |
+-----------------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'Administrador'@'localhost' IDENTIFIED BY 'admin_pass';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE USER 'Medico'@'localhost' IDENTIFIED BY 'medico_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'Asistente'@'localhost' IDENTIFIED BY 'asist_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, UPDATE ON veterinaria.* TO Harry IDENTIFIED BY 'medico_pass'
;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, DELETE ON veterinaria.* TO Harry IDENTIFIED BY 'medico_pass'
;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT ON veterinaria.* TO Harry IDENTIFIED BY 'medico_pass'
;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, UPDATE ON veterinaria.* TO Administrador IDENTIFIED BY 'admin_pas
s';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, CREATE ON veterinaria.* TO Administrador IDENTIFIED BY 'admin_pas
s';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, DELETE ON veterinaria.* TO Administrador IDENTIFIED BY 'admin_pas
s';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, DROP ON veterinaria.* TO Administrador IDENTIFIED BY 'admin_pass'
;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT ON veterinaria.* TO Administrador IDENTIFIED BY 'adminr_pas
s';
Query OK, 0 rows affected (0.00 sec)
mysql>
No hay comentarios:
Publicar un comentario