Database Key Concepts

Service mysqld Start: Start the database service

MySQL * mysql_init (MySQL *mysql); initializes the MySQL handle. If MySQL is null, one is assigned.

To connect to a database:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/8E/wKioL1daWLbDXXl_AAA1DUZ_Pmk532.png "style=" float: none; "title=" QQ picture 20160609103811.png "alt=" Wkiol1dawlbdxxl_aaa1duz_pmk532.png "/>

Close connection:void stdcall mysql_close (MySQL *sock);

Execute SQL statement:int mysql_query (MySQL *connection, const char *query); If the 0,query is successfully returned, it is an SQL statement.

Get Results: mysql_res * stdcall mysql_store_result (MYSQL *mysql);

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);

array Each structure provides a field definition for 1 columns in the result set. About the result column An array of Mysql_field structures for all columns.

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 determined byMysql_num_fields(result) given. If row holds a value returned from a pair with a mysql_fetch_row () call, point to the value of thePointersas row[0] to Row[mysql_num_fields (result)-1] to access. Null value in a rowindicated by a null pointer.

the length of a field value in a row can be called by calling the mysql_fetch_lengths () obtained. Empty fields and field lengths that contain null are all 0; you can differentiate them by checking the value's pointer. 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, using an index to quickly access specific information in a database table.

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

For example such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. Because the index is optimized by some algorithm, the number of lookups is much less. It is visible that the index is used for positioning.

A database index is like a directory in front of a book, speeding up the query speed of a database.

index is divided into two kinds of clustered index and non-clustered index .

Clustered index: The data is stored in the order of the physical location, but not the cluster index is not the same; the clustered index can improve the speed of multi-row retrieval, but the non-clustered index is very fast for the single-line retrieval.

Depending on the capabilities of your database, you can create three types of indexes in the Database Designer: Unique indexes, primary key indexes, and clustered indexes.

Note: Although a unique index helps locate information, for best performance results, it is recommended that you use a primary KEY or a unique constraint instead.

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

When duplicate key values exist in existing data, most databases do not allow a 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 Employees table, none of the two employees will have a namesake.

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 a table, and use an index to quickly access specific information in a database table.

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

The primary key column does not allow null values. A unique index allows null values.
Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index for the specified column. There is no obvious difference between creating a unique constraint and creating a unique index independent of the constraint. Data validation is the same way, and the query optimizer does not differentiate whether unique indexes are created by constraints or created manually. However, if your goal is to achieve data integrity, you should create a UNIQUE or PRIMARY KEY constraint for the column. This is done to make the index target 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. When a primary key index is used in a query, it also allows quick access to the data.

Clustered index

In a clustered index, the physical order of rows in a table is the same as the logical (indexed) 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 the rows in the table does not match the logical order of the key values. clustered indexes typically provide faster data access than nonclustered indexes.

Indexed columns

You can create indexes based on single or multiple columns in a database table. Multiple-column indexes can differentiate between rows in which one column might have the same value.

Indexes are also helpful if you frequently search for two or more columns or sort by two or more columns at the same time. For example, if you frequently set criteria for a first and last name in the same query, it would make sense to create a multicolumn index on those two columns.

To determine the validity of an index:

Check the WHERE and join clauses of the query. Each column included in either clause is an object that the index can select.

The index should be built with the following considerations:

Note: Experiment with the new index to check its impact on running query performance.

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

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

Checks the number of unique data values in a column and compares the number to the number of rows in the table. The result of the comparison is the selectivity of the column, which helps to determine if the column is suitable for indexing and, if appropriate, the type of the index.

trigger (trigger) is a method that SQL Server provides to programmers and data analysts to ensure data integrity, which is a special stored procedure related to table events, it is executed not by the program call, nor manually started, but rather is triggered by an event, such as when an operation is performed on a table (Insert,delete, update) and it is activated. Triggers are often used to enforce data integrity constraints and business rules.

(1) Triggers can query other tables, and can contain complex SQL statements. They are primarily used to enforce complex business rules or requirements. For example, you can control whether a new order is allowed to be inserted based on the current account status of the customer.

(2) Triggers can also be used to enforce referential integrity so that when rows are added, updated, or deleted in multiple tables, the relationships defined between the tables are preserved. However, the best way to enforce referential integrity is to define primary key 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 the trigger cannot execute the EXECUTE statement call, but instead automatically triggers execution when the user executes the Transact-SQL statement.

Triggers have the following effects:

• You can force the validation or conversion of data before writing to the data table.

• When a trigger error occurs, the result of the action is revoked.

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

• The instructions for the replacement of the action (INSTEAD of) can be substituted according to specific circumstances.

• SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

• DML triggers

• When the data in a table in a database changes, including insert,update,delete arbitrary action, the trigger executes automatically if we write a corresponding DML trigger on the table. the primary role of DML triggers is to enforce business rules, as well as extend SQL Server constraints, default values, and so on. because we know that a constraint can only constrain data in the same table, and the trigger executes arbitrary SQL commands.

• DDL triggers

• It is a new trigger for SQL Server2005, which is mainly used to audit and standardize operations on the structure of tables, triggers, views, etc. in the database . For example, in modifying tables, modifying columns, adding tables, adding columns, and so on. It executes when the database structure changes, we mainly use it to record the database modification process, as well as restrict the programmer to modify the database, such as not allowed to delete some of the specified table.

• Logon triggers

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

• NOTE: Constraints are restrictions on your table, or the columns in the table, and so on. The focus is on "limits."
  A trigger is a number of other actions that arise when you do something, such as a delete update, which is a reaction that occurs after you have touched an action.

• Transactions (Transaction) is a program execution unit (unit) that accesses and possibly updates various data items in a database. Transactions are typically caused by the execution of user programs written by advanced database manipulation languages or programming languages such as sql,c++ or Java, and are used in the form of BEGIN TRANSACTION and end transaction Statement (or function call) to define. A transaction consists of all operations performed between the start of a transaction (BEGIN TRANSACTION) and the end of the transaction (end transaction).

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

• 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/82/90/wKiom1daV63Bfz4fAABMpHeFK3U710.png "title=" QQ picture 20160610135303.png "style=" Float:none; "alt=" Wkiom1dav63bfz4faabmphefk3u710.png "/>
  
• Characteristics

• Transactions are the basic unit of recovery and concurrency control.

• A transaction should have 4 properties: atomicity, consistency, isolation, persistence. These four properties are often called acid properties .

• Atomicity (atomicity): A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.

• Consistency (consistency): A transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.

• Isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.

• Persistence (Durability): Persistence, also known as permanence (permanence), refers to the fact that once a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on it.

• transaction type :
  

• (1) Manual transactions

• Manual transactions allow you to explicitly process several procedures, such as starting a transaction, controlling each connection and resource enlistment within the transaction boundary, determining the transaction result (commit or abort), 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 reconciliation between data stores in a manual transaction. In addition, unlike automatic transactions, transactions in a manual transaction do not flow between objects.

• If you choose to manually control distributed transactions, you must manage recovery, concurrency, security, and integrity. In other words, you must apply all the programming methods required to maintain the ACID properties associated with the transaction.

• (2) Automatic transaction

• Once a. NET page, XML Web services method, or. NET Framework class is marked as participating in a transaction, they are automatically executed within the scope of the transaction. You can control the transactional behavior of an object by setting a transaction property value in a page, XML Web services method, or class. The attribute value in turn determines the transactional behavior of the instantiated object. Therefore, depending on the value of the declared attribute, the object will automatically participate in an existing transaction or an ongoing transaction, become the root of the new transaction, or 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 databases are warehouses that organize, store, and manage data. So, the way the database stores data is the storage engine.

In MySQL, the storage engine is loaded in the form of a plug-in. MySQL has a wide variety of storage engines, for us to be familiar with two kinds of storage engines, MyISAM and Inonodb.　MyISAM does not support transactions. InnoDB support transactions.

Outreach: 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 the tabular data in memory. Alternatively, you might need a database that supports transactional processing (to ensure that the data is backed up when transaction processing is unsuccessful).

these different technologies and associated functions are called storage engines (also known as table types) in MySQL. MySQL has a number of different storage engines configured by default and can be pre-set or enabled in MySQL server. You can choose the 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 features you need to combine with your data. This flexibility in choosing how to store and retrieve your data is the main reason why MySQL is so popular.

Features of various storage fuses :

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/82/8E/wKioL1daWLqBmCVJAAEj6sadGVM828.png "title=" QQ picture 20160610135707.png "style=" Float:none; "alt=" Wkiol1dawlqbmcvjaaej6sadgvm828.png "/>

This article is from the "Small Stop" blog, please be sure to keep this source http://10541556.blog.51cto.com/10531556/1787761

Database Key Concepts