mysql-Brief description

Classification

The installation development sequence is divided into:

• Web-based database
• Hierarchical database
• relational database
• Object-oriented database

Mainstream: relational database

relational database

Transaction transaction:

Multiple operations are treated as a whole

? ACID:
A: atomicity
C: Consistency
I: Isolation
D: Persistence

Entity entities:

? Objective things or abstract events that exist objectively and can be distinguished from each other are called entities.

Property:

? The characteristic or nature of an entity.

Contact:

? Association is a collection of associations between data, which is an objective application of semantic chain

Type of contact

? One-on-one contact (1:1)
? One-to-many links (1:n)
? Many-to-many links (m:n)

Three elements of data

? Data

? Operation of the data

? Constraints on data

Constraint: Constraint

Limitations of data to be observed in a table

? Primary key: The combination of one or more fields in which the data must be uniquely identified in this table; Data must be provided, that is, not NULL, a table can have only one
? Unique key: The combination of one or more fields, the data must be able to uniquely identify the bank in this table; Allow null, a table can exist multiple
? FOREIGN key: The data that a field in one table can fill in depends on the data that is already in the primary key or unique key of another table
? Check: Field values are within a certain range

Index:

? Copy data from one or more fields in a table to save one copy, and these need to be sorted in a specific order store

Common components of a relational database

? Databases: Database
? Tables: Table
Row: Row
Columns: Column
? Indexes: Index
? Views: view
? Users: User
? Permissions: Privilege
? Stored procedure: Procedure, no return value
? Stored functions: function, with return value
? Trigger: Trigger
? Events Scheduler: Event Scheduler, Task Scheduler

Database objects

? Component (object) of the database:
databases, tables, indexes, views, users, stored procedures, functions, triggers, event schedulers, and more
? Naming rules:
? Must start with a letter
? Can include a number and three special characters (# _ $)
? Do not use the reserved word for MySQL
? Objects under the same database (Schema) cannot have the same name

Data type

MySQL supports a variety of column types:

? Numeric type
? Date/Time Type
? String (character) type

Choosing the right data type is critical to getting high performance, three principles:

? Smaller is usually better, try to use the smallest data type that correctly stores the data
? Simple, simple data type operations typically require less CPU cycles
? Try to avoid NULL, including null columns, which is more difficult to optimize for MySQL

Modifier

All types:

? Null data columns can contain null values
? Not null data column is not allowed to contain null values
? Default Defaults
? PRIMARY Key Primary Key
? Unique key
? CHARACTER Set name specifies a character set

Numeric type:

? Auto_increment auto-increment for integer type
? UNSIGNED unsigned

SQL statement Classification

? Ddl:data defination Language Data Definition language
Create,drop,alter
? Dml:data manipulation Language Data Manipulation language
Insert,delete,update,select
? Dcl:data Control Language Data Controls language
Grant,revoke,commit,rollback
? Dql:data Query Language Data Queries language
SELECT

DML statements

Dml:insert, DELETE, UPDATE (add and revise)

INSERT:

? Insert one or more rows of data at a time

UPDATE:

? Note: Be sure to have a restriction, otherwise the specified field of all rows will be modified
? Restrictions:
WHERE
LIMIT

DELETE:

? Note: Be sure to have a restriction, otherwise all data in the table will be emptied
? Restrictions:
WHERE
LIMIT

Multi-Table Query

? Cross join: Cartesian product
? Internal connection:
Equivalent connections: The fields between tables are "equivalent" to establish a connection relationship;
No equivalent connection
Natural joins: Removing the equivalent of duplicate columns
Self-connect
? External connection:
Left Outer connection:
From TB1 left JOIN TB2 on Tb1.col=tb2.col
Right outer connection
From tb1 right JOIN TB2 on Tb1.col=tb2.col

Sub-query:

? Poor performance with query statements nested in query statements

? Query based on the query result of a statement

View

Views: view, virtual table, saving query results with real tables

To create a method:

CREATE VIEW view_name [(column_list)]
As Select_statement
[With [cascaded | LOCAL] CHECK OPTION]

To view the view definition:

SHOW CREATE VIEW view_name

To delete a view:

DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

The data in the view is in fact stored in the "base table", so its modification will also be implemented for the base table;
The modification operation is restricted by the base table

Function

system functions and custom functions

Custom Functions (user-defined function UDF)

Saved in the Mysql.proc table

To create a UDF:

CREATE [AGGREGATE] FUNCTION function_name (parameter_name
Type,[parameter_name type,...])
RETURNS {string| integer| REAL}
Runtime_body

Description

? Parameters can have multiple or no parameters
? Must have only one return value

To view a list of functions:

SHOW FUNCTION STATUS;

To view the function definition:

SHOW CREATE FUNCTION function_name

To delete a UDF:

DROP FUNCTION function_name

Call Custom Function Syntax:

SELECT function_name (Parameter_value,...)

Stored Procedures

Stored procedures are saved in the Mysql.proc table

To create a stored procedure:

CREATE PROCEDURE Sp_name ([Proc_parameter [, Proc_parameter ...])
Routime_body

? of which: Proc_parameter: [in| Out| INOUT] Parameter_name Type
? Where in represents the input parameters, out represents the output parameters, inout means that both can be input or output;
Param_name represents the parameter name; type

To view a list of stored procedures:

SHOW PROCEDURE STATUS

To view the stored procedure definition:

SHOW CREATE PROCEDURE Sp_name

To call a stored procedure:

Call Sp_name ([Proc_parameter [, Proc_parameter ...])
Call Sp_name

Note: When there is no parameter, "()" can be omitted and "()" should not be omitted when there are parameters.

Stored Procedure Modifications:

? The ALTER statement modifies the stored procedure to modify only the notes of the stored procedure, such as insignificant things, cannot be modified
? Stored procedure body, so you want to modify the stored procedure by removing the rebuild

To delete a stored procedure:

DROP PROCEDURE [IF EXISTS] Sp_name

Stored Procedure Benefits:

? Stored procedures encapsulate frequently used SQL statements or business logic, and precompilation is saved in the database.
? Called directly from the database when needed, eliminating the need to compile the process
? Increased speed of operation
? While reducing the amount of network data transfer

The difference between a stored procedure and a custom function:

? The process of stored procedure implementation is more complicated, but the function is more pertinence
? A stored procedure can have multiple return values, whereas a custom function has only one return value
? Stored procedures are typically performed independently, and functions are often used as part of other SQL statements

Process Control

Process control can be used in stored procedures and functions to control the execution of statements

Process Control:

? IF: Used to make conditional judgments. Executes different statements depending on whether the condition is met
? Case: Used for conditional judgment, can be more complex than if statement condition judgment
? Loop: Executes a specific statement repeatedly, implementing a simple loop
? LEAVE: For jumping out of loop control
? Iterate: Jump out of the loop and then go straight to the next loop
? REPEAT: A conditional-controlled loop statement. When a specific condition is met, the loop statement is jumped out
? While: Conditional control of a loop statement

Trigger

The execution of the trigger is not called by the program, nor is it started manually, but is triggered and activated by the event to implement

To create a trigger:

CREATE
[definer = {User | Current_User}]
TRIGGER trigger_name
Trigger_time trigger_event
On Tbl_name for each ROW
Trigger_body

Description
? Trigger_name: Name of the trigger
? trigger_time:{before | After}, which indicates a trigger before or after an event
? trigger_event::{INSERT | UPDATE | DELETE}, the specific event that is triggered
? Tbl_name: The trigger acts on the table name

To view a trigger:

SHOW TRIGGERS

To delete a trigger:

DROP TRIGGER trigger_name;

MySQL Client

MySQL Client options available:

-A,--no-auto-rehash no complement
-U,--user= user name, default to root
-H,--host= server host, default to localhost
-p,--passowrd= user password, recommended to use-p, default to blank password
-p,--port= server port
-S,--socket= specifies the path of the socket file connection
-D,--database= specify the default database
-c,–compress Enable compression
-E "SQL" Execute SQL command
-v,–version Display version
-v–verbose Show Details
--print-defaults get the configuration that the program uses by default

DML language (add and revise)

Dml:insert (add), delete (delete), UPDATE (Modify)

The default is to modify the physical data directly, cannot be recalled

To prevent changes to the table, we have to add a security mechanism

cd/etc/my.cnf.d/

Vim/mysql-clients.cnf

Add safe-updates to MySQL entry

Plus, we don't add the WHERE statement when we change it.

Also available mysql-u

Define aliases directly to

Cond...

mysql-Brief description