I met the MySQL
MySQL is an open source database management system (DBMS) developed, published, and supported by MySQL AB. MySQL is a cross-platform open source relational database management system, widely used in the development of small and medium-sized websites on the Internet. This chapter describes the basic knowledge of databases. Through this chapter, you can understand the basic concepts, composition of databases, and basic knowledge of MySQL.
First, database foundation
A database consists of an ordered collection of data stored in a structured table. Data tables are related to each other, reflecting the essential relationship between objective things. The database system provides security control and integrity control for data. This article will introduce some basic concepts of database, including: database definition, data table definition and data types.
1.1 What is a Database
The concept of database was born 60 years ago, with the rapid development of information technology and market, database technology emerges in an endless stream, with the application of the expansion and depth, the number and scale of database is growing, its birth and development to computer information management has brought a huge revolution.
The development of database can be roughly divided into the following stages: manual management stage, file system stage, database system stage, advanced database stage. There are about three types: hierarchical database, network database and relational database. Different kinds of databases are linked and organized according to different data structures.
For the concept of DataBase, not a completely fixed definition, along with the development of the DataBase history, define the content of also have very big difference, among them — a common view is that the DataBase (DataBase, DB) is a long-term storage in computer, organized, have Shared, unified management of data collection. It is a computer software system that stores and manages data according to data structure, that is, database contains two meanings: the “warehouse” that keeps data, and the method and technology of data management.
The characteristics of database include: realizing data sharing and reducing data redundancy; Using a specific data type; High data independence; Unified data control function.
Table 1.2
In a relational database, a database table is a collection of two-dimensional arrays used to store data and a logical structure for manipulating data. It consists of vertical columns and horizontal rows, which are called records and are the units that organize data. Columns are called fields. Each column represents an attribute of the record and has corresponding description information, such as data type and data width.
For example – in a table called authors for author information, each column contains a particular type of information for all authors, such as “name,” and each row contains all information for a particular author: number, name, gender, profession, as shown in the figure below.
Structure and recording of the authors table
1.3 Data Types
Data types determine how data is stored in a computer and represent different types of information. Common data types are: integer data type, floating point data type, exact decimal data type, binary data type, date/time data type, string data type.
Each segment in the table is a specified data type. For example, in the figure above, the “number” field is integer data and the “gender” field is character data.
1.4 the primary key
A Primary Key uniquely identifies each record in a table. A primary key can be defined in one or more columns of a table. A primary key column cannot have two rows of the same value, nor can it be null. Suppose you define the authors table, which assigns each author an author number as the primary key of the table. If the same value is present, an error message is displayed, and the system cannot determine which record is being queried. If the author’s name is used as the primary key, duplicate names are not allowed, which is not the case in real life, so the name field does not work as a primary key.
MySQL > alter table MySQL > alter table MySQL
MySQL triggers, like stored procedures, are programs embedded in MySQL. Triggers are events that trigger an action, including INSERT, UPDATAE, and DELETE statements. A trigger is a named database object associated with a table that is activated when a specific event occurs on the table. This chapter introduces the definition of a trigger, how to create a trigger, view a trigger, how to use a trigger, and how to delete a trigger through examples.
2.1 Creating a Trigger
Trigger is a special stored procedure, but the execution of the stored procedure is called by the CALL statement. The execution of the trigger does not need to be called by the CALL statement, and does not need to be manually started. As long as a predefined event occurs, it is automatically called by MySQL. For example, when operating on the FRUITS table (INSERT. DELETE or UPDATE), it is activated to execute.
Triggers can query other tables and can contain complex SQL statements. They are primarily used to satisfy complex business rules or requirements. For example, you can control whether new orders are allowed to be inserted based on a customer’s current account status. This section describes how to create triggers.
Create a trigger that has only one execution statement
The syntax for creating a trigger is as follows:
Trigger_ name specifies the trigger name. Trigger_ time specifies the trigger time, which can be before or after; Trigger_ event identifies trigger events including INSERT UPDATE and DELETE; Tbl_ name Specifies the name of the table on which the trigger is to be created. Trigger_ STMT is the trigger execution statement.
Create a trigger that executes a single statement as follows:
Create an account table with two columns: acct_ num (defined as int), amount (defined as float); Next, create a trigger named ins_ sum that sums the value of the newly inserted AMOUNT field before inserting data into the table account.
The code is executed as follows:
Create an account table and calculate the sum of the amount values of all newly inserted account tables before inserting data into the table. Trigger name ins_ sum if triggered before inserting data into the table.
Create a trigger that has multiple statements to execute
The syntax for creating multiple triggers that execute statements is as follows:
Trigger__name specifies the name of the trigger. Trigger_ time specifies the trigger time, which can be before or after; Trigger_ event Identifies trigger events, including INSERT, UPDATE, and DELETE. Tbl_ name Specifies the name of the table on which the trigger is to be created. Triggers can use BEGIN and
END is the beginning and END, and contains multiple statements.
Create a trigger that contains multiple execution statements as follows:
Create table testL (testL, testL, testL, testL, testL, testL, testL);
So the data in the four tables is as follows
The result shows that test2, test3, and test4 have changed while inserting records into table test1. From this example, you can see that the INSERT triggers the trigger that inserts the value from test1 into test2, deletes the same value from test3, and updates the b4 from test4 with the same number of inserted values.
2.2 Viewing Triggers
Viewing triggers is to view the definition, status, and syntax information of triggers that already exist in the database. You can view triggers that have been created by using commands. This section describes two ways to view TRIGGERS: SHOW TRIGGERS and view trigger information in the TRIGGERS table.
The SHOW TRIGGERS statement displays trigger information
The following statement is displayed with SHOW TRIGGERS:
SHOW TRIGGERS.Copy the codeView a trigger with the SHOW TRIGGERS command as follows:
SHOW TRIGGERS.Copy the codeCreate a simple trigger named TRIG_update that inserts a record into a table named myevent after each update to the account table:
The execution code to create the trigger is as follows:
Use the SHOW TRIGGERS command to view TRIGGERS:
As you can see, the information display is confusing. If you add ‘\G’ after the SHOW TRIGGERS command, the message will be more organized as follows:
Trigger indicates the name of the Trigger. The names of the two triggers are ins_ sum and trig_ update. Event indicates the Event that activates the trigger. The two trigger events are INSERT and UPDATE. Table indicates the operation object Table that activates the trigger, both of which are account tables. Timing Indicates the time when the trigger is triggered. The values are BEFORE and AFTER. Statement refers to the operation performed by the trigger. Other information, such as SQL schema, trigger definition account, and character set, is not covered here.
Tip: The SHOW TRIGGERS statement looks at information about all the TRIGGERS currently created, which is handy when there are fewer TRIGGERS. If you want to see information about a particular trigger, you can look directly from the Triggers table in the InformNation_ SCHEMA database. This approach is described in the next section.
View trigger information in the Triggers table
All TRIGGERS in MySQL exist in a table TRIGGERS from the INFORMATION_ SCHEMA database and can be viewed using the SELECT command. The syntax is as follows:
SELECT*EROM INFORMATION_SCHEMA. TRIGGERS WHERE condition;
Copy the codeUse the SELECT command to view the trigger as follows:
SELECT*FROM INFORMATION_SCHEMA. TRIGGERS WHERE TRIGGER_ NAME= 'trig_ update'\G
Copy the codeThe above command uses WHERE to specify which trigger to view with a specific name.
TRIGGER_ SCHEMA indicates the database where the trigger resides. TRIGGER_ NAME is followed by the NAME of the trigger; EVENT_ OBJECT _TABLE indicates which data table is fired ACTION_ STATEMENT indicates the specific action to be performed when the trigger is fired ACTION_ ORIENTATION is ROW, indicating that each record is fired. ACTION_ TIMING indicates that the trigger time is AFTER and the rest is system related information.
You can also specify no trigger name to view all triggers as follows:
SELECT*FROM INFORMATION_SCHEMA.TRIGGERS \G
Copy the codeThis command displays all the trigger information in the TRIGGERS table.
2.3 Use of triggers
A trigger is a named database object associated with a table that is activated when a specific event occurs on the table. In some use of triggers, it can be used to check values inserted into a table or to evaluate values involved in an update.
Triggers are table related and are activated when insert. DELETE or UPDATE statements are executed against a table. Sequence. Triggers can be set to be activated before or after statement execution. For example, the trigger can be activated before each — row is deleted from the table, or after each — row is updated.
Create a trigger that updates the myevent table after the record is inserted into the account table:
CREATE TRIGGER trig_ insert AFTER INSERT ON account
FOR EACH ROW INSERT INTO myevent VALUES (2, 'after insert') ;
Copy the codeCreate a trig_INSERT trigger that inserts data into the myevent table after inserting data into the account table.
As a result, a trigger named Trig Insert was created after the record was inserted into the Account to insert a record into the table myevent.
2.4 Deleting Triggers
DROP TRIGGER DROP TRIGGER DROP TRIGGER DROP TRIGGER DROP TRIGGER
DROP TRIGGER [schema_name. ]trigger_name
Copy the codeSchema_ name indicates the database name and is optional. If schema is omitted, the trigger is discarded from the current database. Trigger_name is the name of the trigger to drop.
Delete a trigger as follows:
DROP TRIGGER test. ins;
Copy the codeIn the code above, test is the database where the trigger resides, and INS is the name of a trigger. The code is executed as follows:
2.5 Comprehensive Case – Use of triggers
This section describes the definition and functions of MySQL database triggers, creating triggers, viewing triggers, using triggers, and deleting triggers. Creating and using triggers is the focus of this chapter. Understand the structure of triggers when you create them, and when you use them, know when they fire (BEFORE or AFTER) and the conditions under which they fire (insert.delete or UPDATE). After creating a trigger, know how to modify it.
1. Purpose of the case
Know how to create and invoke triggers.
Create trigger (num, sales); create trigger (num, sales);
Persons table structure
The sales table structure
Persons table of contents
2. Case operation process
Step 1: Create a business table persons
Create a table (persons) as follows:
CREATE TABLE persons (name VARCHAR(40)，num int) ;
Copy the codeStep 2: Create a sales table, Sales
Create a sales table as follows:
CREATE TABLE sales (name VARCHAR(40), sum int);Copy the codeStep 3: Create a trigger
Select * from persons where num = ‘sales’ and sum =’ sales’;
CREATE TRIGGER num_ sum AFTER INSERT ON persons
FOR EACH ROW INSERT INTO sales VALUES (NEW. name, 7*NEW. num) ;
Copy the codeStep 4: Insert into persons
Update the sales table after inserting a new record
INSERT INTO persons VALUES (' xiaoxiao',20) , ( 'xiaohua',69) ;
Copy the codeThe execution process is as follows:
As a result, the num_ sum trigger evaluates the data inserted into Persons and inserts the result into the sales table.
disambiguation
Question 1: Be careful when using triggers.
If you create a BEFOREINSERT trigger for account, MySQL will report an error if you create a BEFOREINSERT trigger for account. If you create a BEFOREINSERT trigger for account, MySQL will report an error if you create BEFOREINSERT trigger for account. Can only be in the table account. Create triggers of type AFTER INSERT or BEFOREUPDATE on. Flexible use of triggers will save a lot of trouble.
Question 2: Delete unnecessary triggers in a timely manner.
After a trigger is defined, each time a trigger event is executed, the trigger is activated and the statement within the trigger is executed. If the requirements change and the trigger does not change or delete accordingly, the trigger will still execute the old statement, affecting the integrity of the new data. Therefore, remove triggers that are no longer used in a timely manner.