Shulou(Shulou.com)06/01 Report--

Service mysqld start: start the database service

MYSQL * mysql_init (MYSQL * mysql); initializes the mysql handle. If mysql is NULL, assign one.

Connect to the database:

Close the connection: void STDCALL mysql_close (MYSQL * sock)

Execute the sql statement: int mysql_query (MYSQL * connection, const char * query); if it succeeds, it returns 0Query as the sql statement.

Get the result: MYSQL_RES * STDCALL mysql_store_result (MYSQL * mysql); sql:select* from stu must be called before executing this function

Returns the number of rows in the result set: int mysql_num_rows (MYSQL_RES * result)

Returns the number of l columns in the result set: int mysql_num_fields (MYSQL_RES * result)

Note: to get the number of rows affected by the INSERT,UPDATE or DELETE query, use mysql_affected_rows ().

MYSQL_FIELD * mysql_fetch_fields (MYSQL_RES * result)

For the result set, returns an array of all MYSQL_FIELD structures. Each structure provides a field definition for 1 column in the result set. An array of MYSQL_FIELD structures for all columns of the result column.

MYSQL_ROW mysql_fetch_row (MYSQL_RES * result)

Retrieves the next row of a result collection. When used after mysql_store_result (), mysql_fetch_row () returns NULL if there are no more rows to retrieve.

Note: the number of values in the row is given by mysql_num_fields (result). If row saves the value returned from a call to mysql_fetch_row (), the pointer to that value is accessed as row [0] to Rows [MySQL _ num_fields (result)-1]. The NULL value in the row is indicated by the NULL pointer.

The length of the field value in the row can be obtained by calling mysql_fetch_lengths (). Empty fields and fields containing NULL are both 0 in length; you can distinguish them by checking the pointer to the value. If the pointer is NULL, the field is NULL;, otherwise the field is empty.

Index: a structure that sorts the values of one or more columns in a database table and uses an index to quickly access specific information in a database table.

If you want to find a particular employee by his last name, the index helps you get information faster than searching all the rows in the table.

For example, a query like this: select * from table1 where id=10000. If there is no index, you must traverse the entire table until the row where ID equals 10000 is found; once you have an index (which must be built on the column ID), you can look it up in the index. Because the index is optimized by some algorithm, the number of searches is much less. It can be seen that the index is used to locate.

The database index is like the catalog in front of a book, which can speed up the query of the database.

Indexes are divided into clustered index and non-clustered index.

Clustered index: it is in order according to the physical location where the data is stored, but the non-clustered index is different; clustered index can improve the speed of multi-row retrieval, while non-clustered index can retrieve single row quickly.

Depending on the functionality of the database, three indexes can be created in the database designer: unique index, primary key index, and clustered index.

Note: although a unique index helps to locate information, it is recommended to use a primary key or unique constraint instead for best performance results.

Unique index unique index is an index that does not allow any two rows to have the same index value.

When there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees can have the same last name.

Primary key index

Note: a primary key is one or more fields in a table whose values are used to uniquely identify a record in the table and use the index to quickly access specific information in the database table.

Database tables often have a combination of one or more columns whose values uniquely identify each row in the table. This column is called the primary key of the table.

Null values are not allowed in primary key columns. A unique index allows null values.

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified column. There is no significant difference between creating a UNIQUE constraint and creating a unique index independent of the constraint. The data is validated in the same way, and the query optimizer does not distinguish between unique indexes created by constraints or manually. However, if your goal is to achieve data integrity, you should create UNIQUE or PRIMARY KEY constraints for the column. This is the only way to make the goal of the index clear.

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. It also allows quick access to data when a primary key index is used in a query.

Clustering index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. A table can contain only one clustered index.

If an index is not a clustered index, the physical order of rows in the table does not match the logical order of key values. Clustered indexes usually provide faster data access than nonclustered indexes.

Index column

You can create indexes based on single or multiple columns in a database table. A multi-column index can distinguish between rows in which one column may have the same value.

Indexes are also helpful if you often search for two or more columns at the same time or sort by two or more columns. For example, if you often set criteria for both last and first name columns in the same query, it makes sense to create multi-column indexes on both columns.

Determine the validity of the index:

Check the WHERE and JOIN clauses of the query. Each column included in any clause is an object that can be selected by the index.

Consider when building an index:

Note: experiment with the new index to check its impact on the performance of running queries.

Consider the number of indexes that have been created on the table. It is best to avoid having many indexes on a single table.

Check the definition of the index that has been created on the table. It is best to avoid overlapping indexes that contain shared columns.

Check the number of unique data values in a column and compare that number with the number of rows in the table. The result of the comparison is the selectivity of the column, which helps determine whether the column is suitable for indexing and, if so, the type of index.

Trigger is a method that SQL server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program, nor started manually, but triggered by events. For example, when an operation is performed on a table (insert,delete, update), it is activated. Triggers are often used to strengthen data integrity constraints and business rules.

(1) triggers can query other tables and can contain complex SQL statements. They are mainly used to enforce compliance with complex business rules or requirements. For example, you can control whether new orders are allowed to be inserted based on the customer's current account status.

(2) triggers can also be used to enforce referential integrity so that the relationships defined between tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to enforce referential integrity is to define primary and foreign key constraints in related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints.

The only difference between a trigger and a stored procedure is that a trigger cannot execute an EXECUTE statement call, but instead automatically triggers execution when the user executes a Transact-SQL statement.

Triggers have the following functions:

Data can be forcibly verified or converted before being written to the data table.

When an error occurs in the trigger, the result of the change is undone.

Some database management systems can use triggers for data definition language (DDL), called DDL triggers.

The abnormal instruction (INSTEAD OF) can be replaced according to the specific situation.

SQL Server includes three general types of triggers: DML trigger, DDL trigger, and login trigger.

DML trigger

When the data in the table in the database changes, including any insert,update,delete operation, if we write a corresponding DML trigger to the table, the trigger executes automatically. The main role of DML triggers is to enforce industry rules, and to extend Sql Server constraints, default values, and so on. Because we know that constraints can only constrain data in the same table, while triggers can execute arbitrary Sql commands.

DDL trigger

It is a new trigger added by Sql Server2005, which is mainly used to audit and standardize the operation of tables, triggers, views and other structures in the database. For example, in modifying tables, modifying columns, adding tables, adding columns, and so on. It is executed when the database structure changes, and we mainly use it to record the modification process of the database and to restrict programmers' changes to the database, such as not allowing certain specified tables to be deleted.

Login trigger

The login trigger fires the stored procedure in response to the LOGIN event. This event is raised when a user session is established with the SQL Server instance. The login trigger fires after the authentication phase of the login is complete and before the user session is actually established. Therefore, all messages from within the trigger that typically reach the user, such as error messages and messages from PRINT statements, are delivered to the SQL Server error log. If authentication fails, the login trigger is not fired.

Note: constraints refer to certain restrictions on your table, or columns in your table, and so on. The key point is "restriction".

A trigger refers to some other actions caused by you when you perform some operations, such as DELETE UPDATE, etc.

A Transaction is a program execution unit (unit) that accesses and possibly updates various data items in a database. Transactions are usually caused by the execution of user programs written in high-level database manipulation languages or programming languages such as SQL,C++ or Java, and are defined by statements such as begin transaction and end transaction (or function calls). A transaction consists of all operations performed between the start of the transaction (begin transaction) and the end of the transaction (end transaction).

For example, in a relational database, a transaction can be a SQL statement, a set of SQL statements, or an entire program.

Characteristics

Transaction is the basic unit of recovery and concurrency control.

Transactions should have four attributes: atomicity, consistency, isolation, and persistence. These four attributes are often referred to as ACID attributes.

Atomicity: a transaction is an indivisible unit of work, and all the operations included in the transaction are either done or not done.

Consistency: the transaction must be to change the database from one consistency state to another. Consistency is closely related to atomicity.

Isolation: the execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions can not interfere with each other.

Durability: persistence, also known as permanence, means that once a transaction is committed, its changes to the data in the database should be permanent. Other operations or failures that follow should not affect it in any way.

Transaction type:

(1) Manual transactions

Manual transactions allow you to explicitly handle several processes, including starting a transaction, controlling each connection and resource enlistment within the transaction boundary, determining the transaction outcome (commit or aborting), and ending the transaction. Although this model provides standard control over transactions, it lacks some simplified operations built into the automatic transaction model. For example, there is no automatic enlistment and coordination between datastores in manual transactions. In addition, unlike automatic transactions, transactions do not flow between objects in manual transactions.

If you choose to control distributed transactions manually, you must manage recovery, concurrency, security, and integrity. That is, all the programming methods required to maintain the ACID properties associated with the transaction must be applied.

(2) automatic transaction

Once .NET pages, XML Web services methods, or .NET Framework classes are marked to participate in a transaction, they are automatically executed within the transaction scope. You can control the transaction behavior of an object by setting a transaction property value in a page, XML Web services method, or class. Property values in turn determine the transactional behavior of the instantiated object. Therefore, depending on the declared property value, the object automatically participates in an existing or ongoing transaction, becomes the root of the new transaction, or does not participate in the transaction at all. The syntax for declaring transaction properties is slightly different in .NET Framework classes, .NET pages, and XML Web services methods.

Note: in general, transactions are recommended for businesses with high security requirements.

Storage engine

We say that a database is a warehouse for organizing, storing and managing data. Then, the way the database stores data is the storage engine.

In mysql, the storage engine is loaded as a plug-in. Mysql has a wide variety of storage engines, and for us, we should be familiar with the two storage engines, MyISAM and inonoDB. Myisam does not support transactions. Innodb supports transactions.

Extension: for example, if you are working on a large amount of temporary data, you may need to use a memory storage engine. The memory storage engine can store all table data in memory. Or, you may need a database that supports transactions (to ensure the fallback ability of the data if the transaction is not successful).

These different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL is configured with many different storage engines by default, which can be pre-set or enabled in the MySQL server. You can choose a storage engine for servers, databases, and tables to provide you with maximum flexibility when choosing how to store your information, how to retrieve it, and what performance and functionality you need your data to combine. This flexibility in choosing how to store and retrieve your data is the main reason why MySQL is so popular.

Various storage engine features:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.