MySQL Insights – Getting started

Unable to find a set of books suitable for beginners to study on the Internet, they bought a class, hurried to finish the class, only to find that all the books were returned to the teacher. This series of articles is here to document a journey to get started with MySQL, starting with downloading the software. Novices can also follow this road, one road to black. Fans can see, willing to take the bait. If you have any questions, please refer to the three lecture notes or write to me.

The article directories

• • • 1. My class notes
    • 2. Software download selection
    • 3. Sample database
    • 4. DOS interface user login mode
    • 5, cancel statement
    • 6. Database operation
    • 7. Data table operation
    • Insert data into table
    • Retrieve data from a table
    • 10. Sampdb database data
    • 11. Storage engines under the current server
    • 12. Storage engine features
    • 13. How to select an appropriate storage engine
    • 14. Storage engine Settings
    • • Warning
      • Create only tables that do not exist
    • Create temporary tables
    • 16. Create new tables from other tables
    • 17, Drop table
    • 18. Index a data table
    • Delete index
    • Alter table Alter table structure
    • 21. Obtain metadata from the database
    • 22. Use views
    • 23. Transaction processing
    • 24. Use transaction savepoints
    • 25, foreign key use
    • 26. Numeric types
    • • Numerical category
      • Special string handling
    • 27, How does MySQL handle illegal data
    • 28. Operators
    • • Arithmetic operator
      • Logical operator
      • Comparison operator
    • Compound statements and statement separators
    • 30. Trigger
    • 31. Use of indexes
    • Why can indexes improve query efficiency?
    • Select index
    • • Try to index columns that are used for searching, sorting, or grouping, not columns that are used for output.
      • Try to index small values, or try to index small values.
      • Sets the index for the prefix of the string
      • Use slow query logs to find SQL statements that are slowing you down
    • MySQL query optimizer
    • Use the Explain statement to validate optimizer actions
    • 34. Anything else
    • MySQL is used in C++
    • • 1. Build MySQL environment under Linux
      • 2. Header files
      • The true face of a woman
      • 4. Clear the fog and look at the next layer of fog
      • • ① Call mysql_real_connect to connect to Mysql database.
        • ② Call mysql_real_query function to query database.
        • ③ Obtain the query result data by calling the mysql_store_result or mysql_use_result variable.
        • ④ Call the mysql_fetch_row function to read the result set data.
        • ⑤ When the result set is used up, call mysql_free_result to release the result set to prevent memory leaks.
        • ⑥ If you do not need to query the Mysql database, call mysql_close to close the database connection.
      • Take a look at an example

1. My class notes

MySQL database from entry to actual application

2. Software download selection

MySQL8.0, download the debug version of 411M

3. Sample database

The sample database comes from someone else’s book, which is better than no book.

A link to the sample database is provided below.

Open the MySQL client (you can also directly open the DOS interface if the system path has been configured), log in and configure a new user:

create user 'sampadm'@'localhost' identified by 'secret';

grant all on sampdb.* to 'sampadm'@'localhost';
Copy the code

The new user sampadm can only log in to localhost.

After the configuration is complete, you can log in to the account with account: sampadm and password: secret.

4. DOS interface user login mode

5, cancel statement

Use \c when you cannot delete several statements that you have entered but do not want to execute

Let me know:

6. Database operation

7. Data table operation

Create tables: Method 1: code

create table president
(
	last_name varchar(15) not null,
	first_name varchar(15) not null,
	suffix varchar(5) null,
	city varchar(20) not null,
	state varchar(2) not null,
	birth date not null,
	death date null
);
Copy the code

Method 2: Import the. SQL file into the sampdb folder and run the source create_president.sql command.

SQL > select * from current database;

To view the columns of a table: desc tablename;

Desc president ‘%name’;

Run the show tables command to query all tables in the current database.

Run the show tables from sampdb command to query all tables in a specified database.

Insert data into table

Retrieve data from a table

A dry query statement I will not detail, this article has written the basic query statement: SQL statement learning

Use select * to find all the data in the table, and then do the actual operation design.

Delete and modify statements are also in there.

More practice is needed to master the basic operations. The operations and database in this chapter will continue to be used in subsequent chapters.

10. Sampdb database data

11. Storage engines under the current server

1. Useshow engines;To view all engines under the current system, as shown in the figure:

The circled line, yes means yes, no means no, default means the system default, usually on, disabled means yes but disabled.

Every time you create a table, MySQL creates a hard disk file to hold the format (its definition) of the table. This format file has the same basic name as the name of the table, with a.frm extension.

For example, if the name of the data table is T, the name of the format file will be T.frm. The server will create the file in the database subdirectory of the database to which the table belongs.

The contents of.frm files remain the same, and there is only one corresponding. FRM file for each table, regardless of which storage engine is managing the table. If the table name character in the file name causes trouble, the table name used in the SQL statement may not correspond to the base name of the corresponding.FRM file, which is not specific to a particular storage engine, and may create several more specific files for the table to store its contents.

For a given table, all files associated with it are centralized in the database subdirectory of the database in which the table resides.

What is important about a storage engine? There was an introduction in the first three.

12. Storage engine features

13. How to select an appropriate storage engine

Selection criteria: Select an appropriate storage engine based on application characteristics. For complex application systems, you can combine multiple storage engines based on actual conditions.

The following are the applicable environments for common storage engines:

MyISAM: the default MySQL plug-in storage engine. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. InnoDB: For transaction processing applications, has many features, including ACID transaction support. Memory: Keeps all data in RAM, providing extremely fast access in environments where references and other similar data need to be found quickly. Merge: Allows a MySQL DBA or developer to combine a series of equivalent MyISAM tables together in a logical manner and act as1Object reference them. It is ideal for VLDB environments such as data warehousing.Copy the code

14. Storage engine Settings

Warning

Sometimes a warning is displayed even though the statement is executed without error, but the warning is not displayed actively. If we want to see it, we actively make it say: Show warnings;

Create only tables that do not exist

create table if not exit tablename;
Copy the code

Create temporary tables

Temporary data tables are automatically destroyed when disconnected from the server. Create method: create temporary table tablename;

The name of a temporary table can be the same as that of an existing table, but the permanent table is hidden for the duration of the temporary table. However, you cannot create two temporary tables with the same name.

When to use temporary tables: For example, if you want to do an experiment, you import an external file, but you don’t know if the data inside is secure.

16. Create new tables from other tables

17, Drop table

Drop table tablename; drop table tablename; Delete multiple tables: think for yourself

18. Index a data table

alter table tablename add index index_name(index_columns);
Copy the code

Delete index

drop index index_name on tablename;
Copy the code

Alter table Alter table structure

ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;

ALTER TABLB mytbl CHANGB i i MEDIUMINT UNSIGNED;
Copy the code

ALTER TABLE mytbl CHANGE i k MEDIUMINT UNSIGNED;
Copy the code

ALTER TABLE tbl name RENAME TO new_ tbl_ name;
Copy the code

RENAME TABLE old name TO new_ name;
Copy the code

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;
Copy the code

ALTER TABLE sampdb.t RENAME TO test. t;

RENAME TABLE sampdb.t TO test.t:
Copy the code

21. Obtain metadata from the database

In front of the disorderly, tidy up:

 show databases;
 show create database sampdb;	// create database statement query
 show tables;
 show create table score;	// create a table sentence query
 show columns from student;
 show index from student;
 show table status;		// View the descriptive information of the data table
Copy the code

22. Use views

create view view_name as
select column1,column2... from table_name;
Copy the code

23. Transaction processing

A transaction is a set of SQL statements that are executed as an indivisible logical unit and whose execution effect can be undone if necessary. Not all statements are executed successfully every time. Transactions are handled through commit and rollback functions. If all statements in a transaction are executed, committing the transaction permanently records those execution effects in the database. If an error occurs during a transaction, rolling back the transaction undoes all statements that were executed before the error occurred.

Another use of transactions is to ensure that rows designed by an operation cannot be modified by other customers while you are using them. MySQL automatically locks the resources designed for each SQL statement to prevent the statements from interfering with each other, but this is not enough to ensure that each database operation always produces the desired result. Remember, some database operations require more than one statement to complete, and during this time, different customers can interfere with each other. By combining multiple statements into a single execution unit, the transaction mechanism prevents concurrency problems that can occur in a multi-client environment.

Using transactions provides a strong guarantee for the database, but at the cost of increased CPU, memory, and disk space. To use transactions, you must choose a storage engine that supports transactions, such as innoDB.

Note that some statements cannot be part of a transaction, so the system will commit the transaction automatically if these statements appear in the transaction, such as:

ALTER TABLE

CREATEINDEX

DROP DATABASE

DROP INDEX

DROP TABLE

LOCK TABLES

RENAME TABLE

SET autocommit = 1 (if not already set to 1)
Copy the code

Each version is slightly different, depending on the official documentation.

24. Use transaction savepoints

25, foreign key use

Foreign key relationships allow you to declare an index in one table that is associated with an index in another table. You can also put the constraints you want to impose on the table into the foreign key relationship and have the system maintain referential integrity of the data according to the rules in the relationship. For example, the SCORE table in the SAMPDB database contains a student_ID column that associates test scores in the SCORE table with students in the Student table. When we created these tables in Chapter 1, we established some explicit relationships between them, one of which was a foreign key that defined the score. student_ id data column as the student. student_ id data column. This ensures that only rows with student_ id values in the studnt table are inserted into the Score table. In other words, this foreign key ensures that there is no error in entering a grade for a student who does not exist.

Foreign keys are useful not only for inserting rows, but also for deleting and updating. For example, we could create a constraint that when a student is removed from the Student table, all rows in the score table related to that student are automatically deleted. This is called cascaded delete, because the effect of a deletion flows like a cascade from one table to another. Cascading updates are also possible. For example, if a waterfall update changes a student’s student_ ID in the Student table, the value of all rows in the Score table that correspond to that student should also change accordingly.

Foreign keys help us maintain data consistency, and they are easy to use. If you don’t use foreign keys, you have to be responsible for maintaining the dependencies between tables and maintaining their compliance, which means adding a bit of code to your application. In some cases, this simply requires you to issue a few additional DELETE statements to ensure that when you DELETE rows in one table, the corresponding rows in the other tables will be deleted as well. But extra work is extra work, and if a database engine can do consistency checks for you, why not let it? If your data table has very complex relationships, checking these dependencies in your application code can be cumbersome, and the automatic checking capabilities provided by a database system are often more thorough, detailed, and practical than your own consideration.

Example:

26. Numeric types

Numerical category

The data values that MySQL recognizes and uses include numeric, string, date/time, coordinate, and NULL values.

Special string handling

27, How does MySQL handle illegal data

By default, MySQL handles “data out of bounds” and other abnormal data according to the following rules:

• For numeric data columns or TIME data columns, values outside the legal range are truncated to the nearest value range boundary, and the resulting value is stored in the database.
• For string columns (excluding enums and sets), strings that are too long will be truncated to the maximum length of the column.
• Assignment to ENUM and SET columns depends on the list of valid values given in the column definition. If the value you assign to an ENUM column is not a valid member, MySQL will assign the “error” member (that is, the empty string corresponding to the zero-valued member) to the column. If you assign a value to a SET column that contains invalid substrings, MySQL will remove those substrings and only assign the rest to the column.
• For date and time data columns, invalid values are converted to a “zero value” of that type.
• MySQL will generate a warning message if these conversions occur during INSERT, REPLACE, UPDATE, LOAD DATA, and ALTER TABLE statements. After these statements are executed, you can use the SHOW WARNINGS statement to view the contents of the warning message.
• If you need more stringent checks when inserting or updating data, you can enable either of the following SQL modes:

mysql> SET sql_ mode = ' STRICT_ALL_TABLES';
mysql> SET sql_ mode = ' STRICT_TRANS_TABLES';
Copy the code

28. Operators

Arithmetic operator

Logical operator

Comparison operator

Compound statements and statement separators

30. Trigger

A trigger is a stored procedure associated with a particular data table that executes automatically when the corresponding data table is modified by an INSERT, DELETE, or UPDATE statement. Triggers can be set to fire before or after each of these statements processes each row of data. The definition of a trigger includes a statement that will be executed when the trigger is fired.

Triggers are created using the CREATE TRIGGER statement. The definition of a trigger needs to indicate which statement (INSERT, UPDATE, or DELETE) it will fire before or after the row is modified. The basic syntax for trigger creation statements is as follows:

CREATE TRIGGER trigger_ name  		# the trigger name

{BEFORE | AFTER}  					# when the trigger activates

{INSERT | UPDATE | DELETE }  		# what statement activates it

ON tbl_ name  						# the associated table

FOR EACH ROW trigger_ stmt;  		# what the trigger does
Copy the code

Let’s have a look:

CREATE TABLE t (percent INT, dt DATETIME);
Copy the code

delimiter $

CREATE TRIGGER bi_tBEFORE INSERT ON t FOR EACH ROW BEGIN SET NEW.dt = CURRENT TIMESTAMP; IF NEW.percent <0 THEN

SET NEW.percent= 0;

ELSEIF NEW.percent> 100 THEN

SET NEW.percent = 100;

END IF;

ENDS

mysql> delimiter ;
Copy the code

Let’s make a trigger for the stored program.

31. Use of indexes

Why can indexes improve query efficiency?

What do we know about search algorithms? Tree, B tree, red and black time, hash table? If you know it, you know it. If you don’t understand, go to understand it. Ha ha ha, spicy is the basis, don’t know how to line.

There are many techniques used to speed up queries, the most important of which is indexing. Often, the biggest difference in query speed is the proper use of indexes. Many times, when queries are slow, adding an index can quickly fix the problem. But that’s not always the case, because optimization isn’t always a simple matter. However, in many cases, if you don’t use indexes, trying to improve performance in other ways is a waste of time. You should use indexes first to maximize performance, and then see if there are other techniques you can use.

Select index

Try to index columns that are used for searching, sorting, or grouping, not columns that are used for output.

You know.

Try to index small values, or try to index small values.

Sets the index for the prefix of the string

Use slow query logs to find SQL statements that are slowing you down

MySQL query optimizer

When you issue a query that selects a row of data, MySQL analyzes it and considers whether it can be optimized to speed up the query.

So how do we match the work of this query optimizer?

Use the Explain statement to validate optimizer actions

34. Anything else

When comparing indexed data, query performance is higher if they are of the same data type and lower if they are of different data types.

Try not to use wildcards at the beginning of like mode to avoid too much use of MySQL’s automatic type conversion

Emmm, difficult…

MySQL is used in C++

1. Build MySQL environment under Linux

Mysql > install mysql  sudo apt-get install mysql-server sudo apt-get install mysql-client sudo apt-get install libmysqlclient-dev sudo apt-get install emmaCopy the code

This can’t be installed, Baidu is on the side.

2. Header files

/ / under Linux
//mysqltest. CPP

#include <stdio.h>

// Make sure there is a mysql.h file under /usr/include/mysql
#include "mysql/mysql.h"
Copy the code

/ / under Windows
#include <WinSock2.h> // Network programming requires winsock2.h
 
#include <mysql.h>
 
#pragmaComment (lib, "libmysql.lib")
Copy the code

The true face of a woman

/* Connect handles to the core of MYSQL development */
MYSQL * mysql;
 
/* Initialize */
MYSQL *mysql_init(MYSQL *mysql);
 
/* Sets connection options */
int mysql_options(MYSQL *mysql, enum mysql_option option, const char *arg);
 
/* Open the connection */
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db,
unsigned int port, const char *unix_socket, unsigned long client_flag);
 
/* Execute the SQL statement */
int mysql_real_query(MYSQL *mysql, const char *query, unsigned long length);
/* If the SQL statement is a C-style string, you can use the following function */
int mysql_query(MYSQL *mysql, const char *query);
 
If you want to execute more than one SQL statement in a function call, you need to start with; Separate, and set the property */ to be set when the connection is opened
CLIENT_MULTI_STATEMENTS
/* Or set the following function call on an already open connection, where mysql is the pointer to mysql */
mysql_set_server_option(mysql,MYSQL_OPTION_MULTI_STATEMENTS_ON);
 
/* If you execute a statement that returns a result, you can use the following function to get the result */
MYSQL_RES *mysql_use_result(MYSQL *mysql);
MYSQL_RES *mysql_store_result(MYSQL *mysql);
/* The former function just initializes the MYSQL_RES structure and does not actually get the result from the server, while the latter function directly reads all the data to the client */
 
/* the MYSQL_RES structure can obtain data from the following functions */
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);
/* The MYSQL_ROW type returned by this function is actually of type char**, and the value of each column can be obtained by subscript operation */
 
/* The number of columns in the result set can be obtained by using the following function */
unsigned int mysql_field_count(MYSQL *mysql);
unsigned int mysql_num_fields(MYSQL_RES *result);
/* Get the number of rows in the result set */
my_ulonglong mysql_num_rows(MYSQL_RES *result);
 
/* Remember to release */ after using the result set
void mysql_free_result(MYSQL_RES *result);
 
/* If the SQL statement executed does not return a result, such as DELETE INSERT, you can use the following function to get the number of affected rows */
my_ulonglong mysql_affected_rows(MYSQL *mysql);
 
/* The connection needs to be released when the connection is finally used
void mysql_close(MYSQL *mysql);
 
/* Error handling */
/*MYSQL functions basically follow the programming conventions of C language. When the return value is an integer, 0 indicates success, non-0 indicates failure, and when the return pointer, NULL indicates failure */
/* If the function fails, you can use the following functions to get information */
unsigned int mysql_errno(MYSQL *mysql);// Error code
const char *mysql_error(MYSQL *mysql);// Error message in English
Copy the code

4. Clear the fog and look at the next layer of fog

① Call mysql_real_connect to connect to Mysql database.

The mysql_real_connect function is modeled as follows:

MYSQL * STDCALL mysql_real_connect (MYSQL * MYSQL,const char* the host,const char* the user,const char* the passwd,const char* db,unsigned intThe port,const char* unix_socket,unsigned longClientflag);Copy the code

Parameter Description:

Mysql: the mysql variable defined earlier; Host: indicates the IP address of the MYSQL server. User: indicates the login user name. Passwd: indicates the login password. Db: database to connect to. Port: TCP service port of the MYSQL server. Unix_socket: Indicates the Unix connection mode. If the value is NULL, the socket or pipe mechanism is not used. Clientflag: indicates the flag of the ODBC database run by Mysql. The value is usually 0.

This function returns 0 on connection failure.

② Call mysql_real_query function to query database.

intSTDCALL mysql_real_query (MYSQL * MYSQL,const char* q,unsigned longLength);Copy the code

③ Obtain the query result data by calling the mysql_store_result or mysql_use_result variable.

The prototypes of the two functions are:

MYSQL_RES * STDCALL mysql_store_result (MYSQL * MYSQL); MYSQL_RES * STDCALL mysql_use_result (MYSQL * MYSQL);Copy the code

These two functions represent two ways to obtain query results. First, the mysql_store_result function is called to store all data queried from the Mysql server to the client and then read. The second method, which calls mysql_use_result to initialize the retrieval so that the result set can be read line by line, does not itself read any data from the server. This method is faster and requires less memory than the first method, but it binds the server and prevents other threads from updating any tables. And mysQL_FETch_ROW must be repeated to read the data until NULL is returned, otherwise unread rows will be returned as part of the result on the next query. So often we use mysql_store_result.

④ Call the mysql_fetch_row function to read the result set data.

MYSQL_ROW STDCALL mysql_fetch_row (MYSQL_RES *result);Copy the code

⑤ When the result set is used up, call mysql_free_result to release the result set to prevent memory leaks.

The mysql_free_result function is modeled as follows:

voidSTDCALL mysql_free_result (MYSQL_RES *result);Copy the code

⑥ If you do not need to query the Mysql database, call mysql_close to close the database connection.

voidSTDCALL mysql_close (MYSQL *sock);Copy the code

Take a look at an example

It’s an example. I didn’t write it. I’m just getting started.

    #include <WinSock2.h>
    #include <mysql.h>
 	#include <iostream>
 	
    #pragmaComment (lib, "libmysql.lib")
    using namespace std;

  intMain () {MYSQL MYSQL; MYSQL_RES *res; MYSQL_ROW row;// Initialize MYSQL variablesMysql_init (&mysql);// Connect to the Mysql server, which is used as the server in this example. The name of the database to be accessed is MSYQL. User indicates your login user name, and *** indicates your login password. Set this parameter based on actual users
 
    if(! Mysql_real_connect (&mysql,"127.0. 01.", "User","123", "mysql",3306.0.0) {cout << "mysql_real_connect failure!" << endl;return 0;
    }
 
    Mysql > select * from user
 
    if(mysql_real_query (&mysql, "select * from user", (unsigned long) strlen (" select * from user ")) {cout << "mysql_real_query failure!" << endl;return 0;
    }
 
    // Store the result setRes = mysql_store_result (&mysql);if （NULL== res) {cout << "mysql_store_result failure!" << endl;return 0;
    }
 
    // Read the row repeatedly and print the value of the first field until row is NULL
 
    while(row = mysql_fetch_row (res)) {cout << row [0] < < endl; }// Release the result setMysql_free_result (res);// Close the Mysql connectionMysql_close (&mysql);return 0; 
}
Copy the code

This is the last part of this introductory tour, and more practice

Code word is not easy, sure you don’t want a triple plus attention? A week later, you’ll be surprised to find that you’re a fan viewable…

Conveniently collect a wave of good habits, paddling, can not find.