MySQL Basics 2

1. Curriculum Review
MySQL Basics
1) MySQL storage structure: Data SQL statements, tables, database
2) Management database:
Add: Create database default character UTF8;
Delete: Drop database;
Modified: ALTER DATABASE default character GBK;
Query: Show databases/show CREATE database;
3) Management table:
Select database: Use database;
Added: Create TABLE Table (Field Name 1 field type, Field Name 2 field type ...);
Delete: drop table;
Modify:
Add Field: ALTER TABLE table add [column] field name type;
Delete field: ALTER TABLE table drop [column] field name;
Modify field type: ALTER TABLE table modify field name new field type;
Modify field name: ALTER TABLE table change old field name new field name fields type;
Modify table name: Alter tables table rename [to] new table name;
Inquire:
Show Tables/desc student;
4) Management data:
Add: INSERT into Table (field 1, Field 2,。。。 Values (value 1, value 2 ...). );
Delete: Delete from table where condition;
Modified: Update table SET field 1= value 1, field 2= value 2 ... where condition;
Inquire:
4.1) All fields: SELECT * from table;
4.2) Specify the field: Select Field 1, Field 2 .... from table;
4.3) Specify alias: Select Field 1 as Alias from table;
4.4) Merge columns: Select (Field 1+ field 2) from table;
4.5) de-weight: SELECT distinct field from table;
4.6) Conditional query:
A) Logical condition: and (with) or (or)
SELECT * FROM table where condition 1 and/or condition 2
b) Comparison conditions: > < >= <= = <> between and (in ... Between
SELECT * FROM table where servlet>=90;
c) conditions for the award of Air:
Judge Null:is null/is NOT NULL
To judge an empty string: = '/<> '
d) Fuzzy condition: Like
%: Replace any character
_: Replace one character
4.7 Paged Query: Limit start Row, query row count
Start line starting from 0
4.8 reviews sorted by: Order By Field Asc/desc
ASC: Positive order, sequence
Desc: reverse order, reverse
4.9 Grouping queries: Group BY fields
4.10: Group after filter: having condition

Classification of SQL statements:
DDL: Data Definition language
Create/drop/alter
DML: Data manipulation statements
Insert/delete/update/truncate
DQL: Data Query Language:
Select/show

Today's goal:
Outline:
1) Data constraints
2) Database design (table design)
3) Stored Procedures
4) Trigger
5) MySQL Permissions issue
2 Data constraints
2.1 What data constraints
Constrain the data on the User action table
2.2 Default Value
Role: The default value is used when the user does not insert a value into a field that uses the default value.
Attention:
1) It is possible to insert NULL for the default value field.
2) The default Value field can be inserted non-null
--1.1 Default value
CREATE TABLE Student (
ID INT,
NAME VARCHAR (20),
Address VARCHAR Default ' Guangzhou Tianhe '--defaults
)

DROP TABLE student;
--MySQL automatically assigns a default value to the field when no value is inserted
INSERT into student (Id,name) VALUES (1, ' Zhang San ');

--NOTE: The default value of the field is allowed to be null
INSERT into student (id,name,address) VALUE (2, ' John Doe ', NULL);
INSERT into student (id,name,address) VALUE (3, ' Harry ', ' Guangzhou Panyu ');

2.3 Non-empty
Role: The limit field must be assigned a value
Attention:
1) non-null characters must be assigned
2) non-null characters cannot be assigned null
--1.2 Non-empty
--Requirements: Gender field must have a value (not NULL)
CREATE TABLE Student (
ID INT,
NAME VARCHAR (20),
Gender VARCHAR (2) NOT null--non-null
)

--Non-empty fields must be assigned values
INSERT into student (Id,name) VALUES (1, ' John Doe ');
--Non-null characters cannot insert null
INSERT into student (Id,name,gender) VALUES (1, ' John Doe ', NULL);

2.4 Unique
Function: The value of a field cannot be duplicated
Attention:
1) Unique field can be inserted null
2) Unique field can insert multiple null

--1.3 Unique
CREATE TABLE Student (
ID INT Unique,--unique
NAME VARCHAR (20)
)

INSERT into student (Id,name) VALUES (1, ' ZS ');
INSERT into student (Id,name) VALUES (1, ' Lisi '); --ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' ID '

INSERT into student (Id,name) VALUES (2, ' Lisi ');

2.5 PRIMARY Key
function: non-null + unique
Attention:
1) Typically, each table will have a primary key field set. Used to mark the uniqueness of each record in a table.
2) It is recommended that you do not select a field that contains business meaning for the table as the primary key, and it is recommended that you design a non-business-meaning ID field independently for each table.
--1.4 Primary KEY (non-null + unique)
DROP TABLE student;

CREATE TABLE Student (
ID INT PRIMARY Key,--primary key
NAME VARCHAR (20)
)

INSERT into student (Id,name) VALUES (1, ' Zhang San ');
INSERT into student (Id,name) VALUES (2, ' Zhang San ');
--INSERT into student (Id,name) VALUES (1, ' John Doe '); --Violation of the UNIQUE constraint: Duplicate entry ' 1 ' for key ' PRIMARY '

--INSERT into student (name) value (' John Doe '); --violation of non-null constraint: ERROR 1048 (23000): Column ' id ' cannot be null

2.6 Self-growth
Function: Auto Increment

--1.5 self-growth
CREATE TABLE Student (
ID INT (4) Zerofill PRIMARY KEY auto_increment,--self-growing, starting from 0 Zerofill 0 padding
NAME VARCHAR (20)
)

--Self-growth fields can be unassigned, automatically incremented
INSERT into student (NAME) VALUES (' Zhang San ');
INSERT into student (NAME) VALUES (' John Doe ');
INSERT into student (NAME) VALUES (' Harry ');

SELECT * from student;
--cannot affect self-growth constraints
DELETE from student;
--can affect self-growth constraints
TRUNCATE TABLE student;

2.7 Foreign key
Role: Constrain data for both tables

There are two types of tables:
Solving high data redundancy problems: separate out a table
Example: Employee tables and Departmental tables
Problem: When inserting employee table data, the Department ID field of the employee table can be inserted arbitrarily!!!!!

Use FOREIGN KEY constraint: Constrain Insert Department ID field value for employee table

Workaround: Add a FOREIGN KEY constraint in the Department ID field of the employee table

--Department table (main table)
CREATE TABLE Dept (
ID INT PRIMARY KEY,
Deptname VARCHAR (20)
)

--Modify the Employee table (sub-table/from 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)
--FOREIGN Key name foreign key reference table (reference field)
)

Attention:
1) The constrained table is called the secondary table, the table that constrains others is called the main table, and the foreign key is set on the secondary table!!!
2) Main Table Reference field universal primary key!
3) Add data: First add the Main table, then add the secondary table
4) Modify the data: Modify the secondary table first, then modify the main table
5) Delete the data: Delete the secondary table before deleting the main table

--1.6 FOREIGN KEY constraints
--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 UPDATE : Cascade Modification
--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;

2.8 Cascade Operations
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!!!

Cascading modifications: on UPDATE CASCADE
Cascade Delete: ON delete CASCADE

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 UPDATE : Cascade Modification
--FOREIGN Key name foreign key reference table (reference field)
)
Note: Cascading operations must be used on a foreign key basis

--Cascade Modification (Modification)
--Direct modification of the department
UPDATE Dept SET id=5 WHERE id=4;

--Cascade Delete
--Delete the department directly
DELETE from dept WHERE Id=1;

3 Database Design
3.1 Introduction
Demand analysis-Demand analyst-"raw demand-> Extract business Model"
Book Model: Book name, version number, author
Student Model: Student number, students name, mobile number
......
Role: Student teacher, librarian
"Requirement Specification"
Demand Design-
Overview design:
Extract entities: Entity model, business model (Java Class C + + Class) memory
Class book{name, Bookno,author}
Database design:
Business model/entity model-> Data Model (hard disk)

database table Design
Question: How to design?
Detailed design
Class details, properties and methods

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

First paradigm: Requires that each field of a table must be an indivisible independent unit
Student:name--violation of the first paradigm
A Nickname | Dog doll
Sutdent:name Old_name--conforming to the first paradigm
A nickname Dog doll

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.

Employee (Employee): Worker number Employee Name Department name Order name-violation of second normal form

Employee Table: Employee Number Employee Name 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: Employee number (primary key) Employee Name Department Number department name--conforms to the second paradigm, violates 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
4 Correlation query (multi-table query)
--************** second, related query (multi-table query) ****************----
--Demand: query employees and their departments (Show employee name, department name)
--2.1 Cross-connect queries (not recommended.) Produce a Cartesian product: 4 * 4=16, some repeating records)
SELECT empname,deptname from Employee,dept;

--Demand: query employees and their departments (Show employee name, department name)
--Multi-table query rule: 1) determine which table (s) of the Query 2) determine which fields 3) The connection condition between table and table (rule: Number of join conditions is number of tables-1)
--2.2 Internal connection query: Only the results that meet the criteria are displayed (most frequently used)
SELECT Empname,deptname--2) determine which fields
From Employee,dept--1) determining which tables to query
WHERE Employee.deptid=dept.id--3) Join condition between table and table

--Another syntax for inner joins
SELECT Empname,deptname
From employee
INNER JOIN Dept
On Employee.deptid=dept.id;

--Using aliases
SELECT E.empname,d.deptname
From Employee E
INNER JOIN Dept D
On E.deptid=d.id;

--Requirements: Query employees in each department
-Expected results:
--Software development Department Zhang San
--Software Development Department John Doe
--Application and maintenance department Harry
--Chen Liu, Secretary department
--Total handling null
--2.2 left [outer] connection query: Use data from the table on the left to match the data on the right side of the table, if the results of matching join conditions are displayed, NULL if the connection condition is not met
--(Note: Left outer connection: The data of the left table is sure to complete the display!) ）
SELECT D.deptname,e.empname
From Dept D
Left OUTER JOIN employee E
On D.id=e.deptid;

--2.3 Right [outer] connection query: Use the data on the right table to match the data on the left table, if the results of matching join conditions are displayed, NULL is displayed if the join condition is not met
--(Note: Right outer connection: the data of the right table must be displayed!) ）
SELECT D.deptname,e.empname
From Employee E
Right OUTER JOIN Dept D
On D.id=e.deptid;

--2.4 self-connected queries
--Demand: query employees and their superiors
-Expected results:
--Zhang San null
--Li Shizhang
--Harry John Doe
--Chen Liu Harry
SELECT E.empname,b.empname
From Employee E
Left OUTER JOIN Employee b
On E.bossid=b.id;

5 Stored Procedures
5.1 What is a stored procedure
stored procedures, SQL statements with logic
The previous SQL has no condition to judge, no loops
Stored procedures with Process Control statements (if while)
5.2 Stored Procedure Features
1) execution efficiency very fast! The stored procedure is executed on the server side of the database!!!
2) Very poor transplant! Stored procedures for different databases are not portable.

5.3 Stored Procedure syntax
--Create a stored procedure
DELIMITER $--Terminator of declaring stored procedures
CREATE PROCEDURE pro_test ()--Stored Procedure name (parameter list)
Begin--Start
--can write multiple SQL statements; --SQL statement + Process Control
SELECT * from employee;
End $--end Terminator

--Execute Stored procedure
Call Pro_test (); --Call stored procedure name (parameter);

Parameters:
In: Indicates input parameters, can carry data with stored procedure
Out: Represents an output parameter that can be returned from a stored procedure
INOUT: Indicates input and output parameters, can either input function or output function

--************** Three, stored procedure *******************-
--declaration Terminator
--Create a stored procedure
DELIMITER $
CREATE PROCEDURE pro_test ()
BEGIN
--can write multiple SQL statements;
SELECT * from employee;
END $

--Execute Stored procedure
Call Pro_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;

6 triggers
6.1 Trigger Action
When you manipulate a table, you want to trigger some action/behavior at the same time, you can use the trigger to complete!!

For example, when you insert a record into an employee table, you want to insert the data in the log table at the same time

--Requirements: when inserting a record into an employee table, I want MySQL to automatically insert data into the log table at the same time
--Create a trigger (add)
CREATE TRIGGER Tri_empadd After insert on employee for each ROW--when a record is inserted into the employee table
Insert into Test_log (content) VALUES (' Employee table insert a record ');

--Inserting data
INSERT into employee (Id,empname,deptid) VALUES (7, ' Zangus ', 1);
INSERT into employee (Id,empname,deptid) VALUES (8, ' Staples 2 ', 1);

--Create a trigger (modify)
CREATE TRIGGER tri_empupd after UPDATE on employee for each ROW--when a record is modified to the employee table
INSERT into Test_log (content) VALUES (' Employee table modified a record ');

--Modification
UPDATE employee SET empname= ' Eric ' WHERE id=7;

--Create TRIGGER (delete)
CREATE TRIGGER Tri_empdel After delete on employee for each ROW--when a record is deleted to the employee table
INSERT into Test_log (content) VALUES (' Employee table delete a record ');

--Delete
DELETE from employee WHERE id=7;

7 MySQL Permissions issues
--***********, MySQL permissions problem ****************
--MySQL Database permissions problem: Root: Have all permissions (can do anything)
--rights account, only partial permissions (curd) For example, you can only manipulate a table of a database
--How do I change the MySQL user password?
--PASSWORD:MD5 encryption function (one-way encryption)
SELECT PASSWORD (' root '); --*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

--MySQL database, User configuration: Users table
Use MySQL;

SELECT * from USER;

--Change Password
UPDATE USER SET password=password (' 123456 ') WHERE user= ' root ';

--Assigning rights to accounts
GRANT SELECT on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';
GRANT DELETE on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';

MySQL Basics 2