MySQL Database Management: Users, Permissions, Tables

Users

• Creation

From the Partner's Computer

• Create user `Professor` @ '192.168.5.25' identified by 'Professor';

Local Access

• Administrative user `Create` @ 'localhost' identified by 'Administrative';

Access from Any Computer

• Create user `` @ '%' student identified by 'student';

• Deletion

• DROP

• Permissions

To Grant Permissions

• GRANT SELECT ON clase.cuidadores TO 'student'@'localhost';

SELECT * FROM carers;

• GRANT SELECT (name, currency) ON clase.cuidadores TO 'student'@'localhost';

SELECT name FROM currency carers;

• GRANT INSERT, SELECT, UPDATE ON IES.Curso TO 'Administrative'@'localhost';

• GRANT INSERT (IdCurso, DNI), SELECT (IdCurso, DNI), UPDATE (IdCurso, ID) ON IES.Matrícula TO 'Administrative'@'localhost';

With Update

• GRANT SELECT (name, currency), UPDATE (currency) ON clase.cuidadores TO 'student'@'localhost';

• UPDATE September caregivers SET currency = 11 WHERE name LIKE 'Louis%';

To Remove Permissions

• REVOKE ALL ON clase.cuidadores FROM 'student'@'localhost';

• REVOKE UPDATE ON centroestudios.aprobadosmenosocho

FROM 'usu1'@'localhost';

• Tables

Create Table

CREATE DATABASE ClínicaSC;
USE ClínicaSC;
CREATE TABLE table (
  `id` INTEGER patient,
  patient_name VARCHAR(15),
  `date` date of birth,
  PRIMARY KEY (`code` patient)
);
CONSTRAINT Fk1 FOREIGN KEY (`id` Patient) REFERENCES patient (Patient `id`) ON UPDATE CASCADE ON DELETE CASCADE;

Field of the table that I am referring to the other table with the field of it.

• Integer: numbers
• Varchar: characters (save only those put)
• Char: save 45 although only type 4
• ON DELETE CASCADE ON UPDATE CASCADE: When you delete something, anything related to it is also removed.

Modify Table

Insert

• INSERT INTO test.tabla (field1, field2, field3) VALUES (1, 'something', 'something');

Fields contained in the table. Data to be added (order)

Add Column

use clínicasc;

• clínicasc.medicos ALTER TABLE ADD COLUMN address VARCHAR(45);

Delete Column

• clínicasc.medicos ALTER TABLE DROP COLUMN address;

Add PK

• clínicasc.ingresos ALTER TABLE ADD PRIMARY KEY (`codigo_ingresos`);

Remove PK

• clínicasc.ingresos ALTER TABLE DROP PRIMARY KEY;

Add FK

• clínicasc.ingresos ALTER TABLE ADD CONSTRAINT FK1 FOREIGN KEY (`id` Patient) REFERENCES patient (patient `code`) ON DELETE CASCADE ON UPDATE CASCADE;

Delete FK

• clínicasc.ingresos ALTER TABLE DROP FOREIGN KEY fk1;

Rename Table

• clínicasc.medicos ALTER TABLE RENAME clínicasc.medicuchos;

Clear Table

• DROP TABLE otratabla;

• Views

Create Views

USE centoestudios;

• CREATE VIEW v1 AS

  SELECT courses.*, Ponente.Nombre
  FROM courses
  INNER JOIN Ponente ON Cursillos.Ponente = Ponente.IdPonente;
  WHERE Matrícula.Nota >= 5
  WITH LOCAL CHECK OPTION;

WITH CASCADED CHECK OPTION;

Clear View

• DROP VIEW v1;

CASCADED WITH CHECK OPTION checks the condition that created it and its own. IF YOU HAVE TO CHECK

WITH LOCAL CHECK OPTION checks the condition only his. NOT HAVE TO CHECK