Shulou(Shulou.com)05/31 Report--

What is the basic knowledge of MySQL? in view of this question, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the products of Oracle. MySQL is one of the most popular relational database management systems. Many large-scale and fast-growing organizations in the world, such as Facebook, Google, Adobe, Alcatel Lucent and Zappos, all rely on MySQL to manage data and provide support for their high-capacity websites, key business systems and software packages.

I. Overview of Relational Database MySQL

MySQL has the following characteristics, which make it widely used.

1. Simple and easy to use

MySQL is a high-performance and relatively simple database system, which is less complex than the setup and management of some large-scale systems.

two。 The price is low

MySQL is free for most individual users.

3. Small

Only 21MB is available for the 4.1.1 release of the MySQL database, and only 51MB is installed.

4. Support query language

MySQL supports SQL (structured query language). SQL is a language of choice for all modern database systems. SQL supports ODBC (Open Database Connectivity) applications. ODBC is a database communication protocol developed by Microsoft.

5. Performance

There is no limit to the number of users in the MySQL database, and multiple clients can use the same database at the same time. MySQL can be accessed interactively using several interfaces that enter the query and view the results. These interfaces are command-line client programs, Web browsers, or X Window System client programs. In addition, there are interfaces written in various languages such as C, C++, Eiffel, Java, Perl, PHP, Python, Ruby and Tcl. Therefore, you can choose to use a written client program or write your own client application.

6. Connectivity and security

MySQL is fully networked and its database can be accessed anywhere on the Internet, so it can be shared with anyone anywhere. And MySQL supports access control, which controls who can't see the data.

7. Portability

MySQL can run on various versions of UNIX and other non-UNIX systems, such as Windows and OS/2. MySQL can run on servers ranging from home PC to advanced.

8. Open distribution

MySQL is easy to get: you can download it from the official website by using a Web browser. If you can't understand how one of these algorithms works, or if you're curious about an algorithm, you can take its source code and analyze it. If you don't like some algorithms, you can also change them.

9. High speed

MySQL runs very fast. Some developers have claimed that MySQL is probably the fastest database available so far.

II. Overview of SQL

The earliest version of SQL, originally called Sequel, was developed by IBM and was part of the System R project in the early 1970s. With the development of Sequel language, its name has become SQL (Structured Query Language, structured query language). It is a purpose-specific programming language used to manage relational database management systems or stream processing in relational flow data management systems.

SQL is based on relational algebra and tuple relational calculus, including a data definition language and a data manipulation language. The scope of SQL includes insertion / query / update and deletion of data, creation and modification of database schemas, and data access control. Although SQL is often described as a declarative programming language (4GL, the fourth generation language), it also contains procedural elements.

SQL is the first commercial language implementation of the Edgar Cod relational model, which was described in an influential paper "A Relational Model for large shared databases" in 1970. Although SQL is not designed entirely according to Cod's relational model, it is still the most widely used database language.

SQL became a standard of the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987. Since then, the standard has undergone a series of updates and added a large number of new features. Although this standard exists, most SQL code is not completely cross-platform in different database systems.

MySQL supports the SQL language very amicably, and this article gives only a simple example.

The official website is divided into 8 parts according to the grammatical function of SQL, which are:

Data definition statement (Data Definition Statement)

Data processing statement (Data Manipulation Statement)

Transaction and lock declaration (Transactional and Locking Statement)

Copy statement (Replication Statement)

Preprocessing SQL statement syntax (Prepared SQL Statement Syntax)

Compound statement syntax (Compound-Statement Syntax)

Database Management statement (Database Administration Statement)

Utility statement (Utility Statement)

Examples of the first two parts will be given below.

III. Data definition statement

The relational set in the database must be assigned to the system by the data definition statement, which mainly provides table-related commands to define the relational schema, delete the relationship, and modify the relational schema. For example: database, table, tablespace, trigger, event, index and function definition, change operation, etc.

[example 1] specify the tablespace to create a library table, and add columns to the existing table to view the table structure.

1. Create a tablespace named ts_1

Mysql > CREATE TABLESPACE ts_1 ADD DATAFILE 'ts_1.ibd' Engine=InnoDB

Query OK, 0 rows affected (0.11 sec)

two。 Set up a database named testq

Mysql > CREATE DATABASE testq

Query OK, 1 row affected (0.00 sec)

3. Switch to a database named testq

Mysql > use testq

Database changed

4. Create a table and specify the tablespace and engine that the table uses

Mysql > CREATE TABLE T1 (

-> C1 INT STORAGE DISK,# data is stored on disk

-> c2 INT STORAGE MEMORY# data based on memory

->) TABLESPACE ts_1 ENGINE INNODB;# specifies tablespace ts_1 and engine INNODB

Query OK, 0 rows affected (0.34 sec)

5. Add column c3 to table T1 with type VARCHAR and length 10

Mysql > ALTER TABLE T1 ADD c3 VARCHAR (10)

Query OK, 0 rows affected (0.52sec)

Records: 0 Duplicates: 0 Warnings: 0

6. View the structure of table T1

Mysql > DESC T1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | C1 | int (11) | YES | | NULL |

| | c2 | int (11) | YES | | NULL | |

| | c3 | varchar (10) | YES | | NULL | |

+-+ +

3 rows in set (0.00 sec)

Be careful

Set up in the my.ini file:

# The default storage engine that will be used when create new tables when

Default-storage-engine=INNODB

Otherwise, a "1286 Unknown storage engine 'InnoDB'" error will occur.

After a successful startup, check the SHOW ENGINES to see if the engine is running.

IV. Data processing statements

Data processing statements mainly provide operations such as insertion, deletion, modification and query of related data tuples in database tables.

[example 2] insert, query, change and delete data into the existing table T1.

1. Insert 2 pieces of data into table T1, where the second column data is twice as large as the first column data

Mysql > INSERT INTO T1 (c1rect c2meme c3) VALUES (1recorder c1pl 2mema`), (2meme c1p2meme b')

Query OK, 2 rows affected (0.10 sec)

Records: 2 Duplicates: 0 Warnings: 0

two。 Query all the data in table T1

Mysql > SELECT * FROM T1

+-+

| | C1 | c2 | c3 |

+-+

| | 1 | 2 | a |

| | 2 | 4 | b | |

+-+

2 rows in set (0.00 sec)

3. Update the data in table T1, where when C1 is equal to 2, the data of the corresponding c2 column is multiplied by 3, and the value of the corresponding c3 column is changed to updateb

Mysql > UPDATE T1 SET c2 / c1 / 3 / 3 / 3 / 3 / 3 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3

-> WHERE c1room2

Query OK, 1 row affected (0.11 sec)

Rows matched: 1 Changed: 1 Warnings: 0

4. When C1 equals 2, query the data of the corresponding tuple in table T1

Mysql > SELECT * FROM T1 WHERE c1x2

+-+

| | C1 | c2 | c3 |

+-+

| | 2 | 6 | updateb |

+-+

1 row in set (0.05sec)

5. When C1 equals 2, delete the data of the corresponding tuple in table T1

Mysql > DELETE FROM T1 WHERE c1x2

Query OK, 1 row affected (0.10 sec)

Using the select statement to query table T1, it is found that the data of the tuples corresponding to c1room2 have been deleted.

Mysql > SELECT * FROM T1

+-+

| | C1 | c2 | c3 |

+-+

| | 1 | 2 | a |

+-+

1 row in set (0.00 sec)

What are the answers to the questions about the basic knowledge of MySQL? I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.