MySQL Basics (ii)

Learning route: Data Constraints--database design process--related knowledge of stored procedures--trigger--rights management

(i) Data constraints

1.1, the default value of the settings

Create an employee table EMP sets the default address to ' China '
mysql> CREATE TABLE emp (    int,    name varchar),    - > Address varchar (default' China '    );
The default address can be null, and when no value is inserted into the field, MySQL automatically assigns the value to it using the default value

1.2, non-empty limit

Create student table student and set the student's name field cannot be empty, that is, the name must be assigned and cannot be assigned a value of NULL
Mysql> CREATE TABLE student (    int,    sname varchar (null     );

1.3. Unique value Limit

Create student table, set student ID is unique, unique field can insert one or more null, at this time MySQL does not error, but if there is duplicate number will not be inserted and error
Mysql> CREATE TABLE student (    int  unique,    name varchar )    ;

1.4. Primary KEY (non-null and unique)

Set Employee number SID field as primary key, non-empty and unique
mysql> CREATE TABLE emp (    int  primary key,    sname varchar)     );
Typically, the table will have a primary key that distinguishes the individual records and, in general, the ID

1.5, self-growth to achieve automatic increment (numbering class)

The student table is created, and the field SID is set as the primary key since the growth, when the record is inserted into the table, the SID can be unassigned and automatically incremented
Mysql> CREATE TABLE student (    int  primary key auto_increment,    sname varchar (    +);
Delete from does not affect the self-growth constraint at the time of deletion, TRUNCATE table will remove the self-growth constraint

1.6. Foreign key

Role: To constrain the data of two tables, solve the problem of data redundancy

For example: There are two tables, one is Student information table student (number, name, home), one is the College information table Coll (department number, department name)

Create a college table
mysql> CREATE Table coll (    int  primary key,    cname varchar)     );
Create Student Table section field Reference Main Table College table

Mysql> CREATE TABLE Student (
-Sid int,
sname varchar (20),
-Collid int,
-Constraint STU_COLL_FK foreign key (collid) references Coll (CID));

Foreign key name foreign key reference table and field

Note: The difference between the main table and the secondary table:

1, the constrained table is a secondary table, the constraint of others table is the main table, foreign keys are set on the secondary table

2, the Main Table reference field universal primary key

3. Add data Order: Master-"vice

4, modify the data order: Vice-"Main

5. Delete Data order: vice-"Main

Constraints on foreign keys

--Employee table

CREATE TABLE Employee (

ID INT PRIMARY KEY,

EmpName VARCHAR (20),

Deptname VARCHAR (20)-department name);

INSERT into employee VALUES (1, ' Zhang San ', ' Software Development Department ');

INSERT into Employee VALUES (2, ' John Doe ', ' Software Development Department ');

INSERT into Employee VALUES (3, ' Harry ', ' Application Maintenance Department ');

SELECT * from employee;

--Add employee, department name data redundancy is high

INSERT into Employee VALUES (4, ' Chen VI ', ' Software Development Department ');

--solving the problem of high data redundancy: placing redundant fields in a separate table

--independent design of a departmental table

CREATE TABLE Dept (

ID INT PRIMARY KEY,

Deptname VARCHAR (20)

)

DROP TABLE employee;

--Modify Employee table

CREATE TABLE Employee (

ID INT PRIMARY KEY,

EmpName VARCHAR (20),

DeptID INT,--Change Department name to Department ID

--Declaring a FOREIGN KEY constraint

CONSTRAINT emlyee_dept_fk FOREIGN KEY (deptid) REFERENCES Dept (ID) on UPDATE CASCADE on DELETE CASCADE--on CASCADE updat E: Cascading modifications

--FOREIGN Key name foreign key reference table (reference field)

)

INSERT into Dept (id,deptname) VALUES (1, ' Software Development Department ');

INSERT into Dept (Id,deptname) VALUES (2, ' Application Maintenance Department ');

INSERT into Dept (Id,deptname) VALUES (3, ' secretary ');

INSERT into employee VALUES (1, ' Zhang San ', 1);

INSERT into Employee VALUES (2, ' John Doe ', 1);

INSERT into Employee VALUES (3, ' Harry ', 2);

INSERT into Employee VALUES (4, ' Chen VI ', 3);

--Problem: The record is not legal in business, employees insert a non-existent department data

INSERT into employee VALUES (5, ' Chen VI ', 4); --Violation of FOREIGN KEY constraint: Cannot add or update a child row:a FOREIGN KEY constraint fails (' DAY16 '. ' Employee ', constraint ' Emlyee_dept_f K ' FOREIGN KEY (' deptid ') REFERENCES ' dept ' (' ID '))

--1) When you have a FOREIGN key constraint, add the order of the data: Add the primary table first, and then add the secondary table data

--2) When you have a FOREIGN key constraint, modify the order of the data: Modify the secondary table first, and then modify the main table data

--3) When you have a foreign key constraint, delete the order of the data: delete the secondary table before deleting the main table data

--Modify the department (cannot directly modify the main table)

UPDATE Dept SET id=4 WHERE id=3;

--Modify the employee table first

UPDATE employee SET deptid=2 WHERE id=4;

--Delete Department

DELETE from dept WHERE id=2;

--Delete the employee table first

DELETE from employee WHERE deptid=2;

SELECT * FROM Dept;

SELECT * from employee;

1.7. Cascade operation

problem: When you have a foreign key constraint, you must modify or delete all associated data in the secondary table before you can modify or delete the main table! However, we want to directly modify or delete the main table data, thereby affecting the secondary table data.        Can be implemented with cascading operations!!!    Cascade modification: On UPDATE CASCADE cascade Delete: On delete CASCADE CREATE TABLE employee (ID INT PRIMARY KEY, EmpName VARCHAR ( -), DeptID INT,--Change Department name to department ID--declares a foreign key constraint CONSTRAINT EMLYEE_DEPT_FK FOREIGN key (DeptID) REFERENCES Dept (ID) on UPDATE CASCADE on DELETE CASCADE 
    --On CASCADE UPDATE: Cascade modification--Foreign key name foreign key reference table (reference field) Note: Cascading operations must be used on the external key basis--Cascade Modification (Modification)--Modify Department Update Dept SET ID directly=5WHERE id=4;--Cascade Delete--directly Delete Department Delete from Dept WHERE ID=1;

(ii) design of the database

Design principles: The proposed design of the table as far as possible to abide by the three paradigms.

First paradigm: each field of the requirement table must be an indivisible independent unit, i.e. each field is the smallest non-divided

The second paradigm: on the basis of the first paradigm, each table is required to express only one meaning. Each field of the table is dependent on the primary key of the table.

EMP (Employee): Number Name Department order --violation of the second paradigm

Employee Table: Number Employee Department name

Order Form: Order number Order name-- conform to the second paradigm

The third paradigm: on the basis of the second paradigm, all fields other than the primary key of each table are required to directly determine the dependency relationship with the primary key.

Employee table: Number (primary key) name Department numberDepartment Name --conforming to the second paradigm, violating the third paradigm                                                                                                                                                                      (High data redundancy)

Employee Table: Employee number (primary key) Employee Name department number--conforms to the third paradigm (reduces data redundancy)

Department Table: Department number Department name

(iii) Knowledge of stored procedures

First, the stored procedure is similar to a function (method), except that it contains the SQL statement we want to implement.

The characteristics of a stored procedure: High execution efficiency, which is performed on the server side. The disadvantage is poor portability.

Grammar:

Mysql> delimiter $        --declaring stored procedure terminator MySQL> CREATE PROCEDURE Test ()--the name of the stored procedure is set    to test-><　　c4> Begin--Start     Select * from student;  --SQL statement, can write multiple SQL statements put here     --0 rows affected (0.24 sec)

--Call the stored procedure

Mysql> call Test ();

--3.1 stored procedure with input parameters
--Requirements: Pass in an employee ID, query employee information
DELIMITER $
CREATE PROCEDURE Pro_findbyid (in Eid Int.)--In: Input parameters
BEGIN
SELECT * FROM employee WHERE Id=eid;
END $

--Call
Call Pro_findbyid (4);

--3.2 stored procedure with output parameters
DELIMITER $
CREATE PROCEDURE pro_testout (out str VARCHAR) – Out: Output parameters
BEGIN
--Assigning values to parameters
SET str= ' Helljava ';
END $

--Delete stored procedures
DROP PROCEDURE pro_testout;
--Call
--How to accept the value of the return parameter??
--***mysql variable ******
--Global variables (built-in variables): MySQL database built-in variables (all connections work)
--View All global variables: show variables
--View a global variable: select @@ 变量 Name
--Modify global variables: Set Variable name = new value
--Encoding of the received data of the CHARACTER_SET_CLIENT:MYSQL server
--Encoding of CHARACTER_SET_RESULTS:MYSQL server output data

--session variable: exists only in one connection between the current client and the database server side. If the connection is broken, then all session variables are lost!
--Define Session variables: SET @ variable = value
--View Session variables: SELECT @ variable

--Local variables: variables used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!!

--1) Define a Session variable name, 2) use the name session variable to receive the return value of the stored procedure
Call Pro_testout (@NAME);
--View variable values
SELECT @NAME;

--3.3 stored procedure with input and output parameters
DELIMITER $
CREATE PROCEDURE pro_testinout (INOUT n INT)--INOUT: input and output parameters
BEGIN
--View variables
SELECT N;
SET n = 500;
END $

--Call
SET @n=10;

Call Pro_testinout (@n);

SELECT @n;

--3.4 stored procedures with conditional judgment
--Demand: Enter an integer if 1, then return "Monday" if 2, return "Tuesday", if 3, return "Wednesday". Other numbers, return "error input";
DELIMITER $
CREATE PROCEDURE pro_testif (in num int,out str VARCHAR (20))
BEGIN
IF Num=1 Then
SET str= ' Monday ';
ELSEIF num=2 Then
SET str= ' Tuesday ';
ELSEIF Num=3 Then
SET str= ' Wednesday ';
ELSE
SET str= ' input error ';
END IF;
END $

Call Pro_testif (4, @str);

SELECT @str;

--3.5 stored procedures with loop function
--Requirements: Enter an integer and sum. For example, enter 100, statistic 1-100, and
DELIMITER $
CREATE PROCEDURE pro_testwhile (in num int,out result INT)
BEGIN
--Define a local variable
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
While I<=num do
SET vsum = vsum+i;
SET i=i+1;
END while;
SET result=vsum;
END $

DROP PROCEDURE Pro_testwhile;

Call Pro_testwhile (@result);

SELECT @result;

Use DAY16;

--3.6 Assigning a value to a variable using the query's result (into)
DELIMITER $
CREATE PROCEDURE Pro_findbyid2 (in Eid int,out vname VARCHAR (20))
BEGIN
SELECT EmpName to VName from employee WHERE Id=eid;
END $

Call Pro_findbyid2 (1, @NAME);

SELECT @NAME;

(d) Trigger

The role of triggers: When you manipulate a table, you want to trigger some actions/behaviors at the same time, you can use the trigger to complete!!

--requirement: When inserting a record into the employee table, I want MySQL to automatically insert data into the log table at the same time--Creating a trigger (add) Create TRIGGER Tri_empadd after INSERT on employee for each ROW--Insert into Test_log (content) VALUES when inserting a record into the employee table'the Employee table inserts a record'); --inserting data INSERT into employee (Id,empname,deptid) VALUES (7,'Zangus',1) INSERT into employee (Id,empname,deptid) VALUES (8,'Zangus 2',1);--Creating a trigger (Modify) Create TRIGGER tri_empupd after UPDATE on the employee for each ROW--INSERT into Test_log (content) VALUES when a record is modified to the employee table ('The employee table modifies a record'); --Modify UPDATE employee SET EmpName='Eric'WHERE id=7; --Creating a trigger (delete) Create TRIGGER Tri_empdel after delete on the employee for each ROW--INSERT into Test_log (content) VALUES when deleting a record to the employee table'Employee table Deletes a record'); --remove DELETE from employee WHERE ID=7;

(v) Rights Management

--MySQL Database permissions issue: Root: Have all permissions (can do anything)--rights account, with only partial permissions (curd) For example, you can only manipulate a table of a database--How do i modify the MySQL user password? --PASSWORD:MD5 Cryptographic functions (one-way encryption) SELECT password ('Root'); -- *81f5e21e35407d884a6cd4a731aebfb6af209e1b--MySQL database, User configuration: Users table use MySQL; SELECT*From USER;--Modify password update USER SET PASSWORD=password ('123456') WHERE user='Root';--Assign rights account grant SELECT on Isole.emp to' Isole'@'localhost'Identified by'123456'; GRANT DELETE onIsole.emp to'Isole'@'localhost'Identified by'123456';

MySQL Basics (ii)