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

In this article, the editor introduces in detail "what are the MySQL database query statements that are often asked in the interview?" the content is detailed, the steps are clear, and the details are handled properly. I hope that this article "what are the MySQL database query statements often asked in the interview" can help you solve your doubts.

What is a database?

A database is a warehouse that organizes, stores and manages data according to the data structure. Each database has one or more different API to create, access, manage, search and copy the saved data.

We can also store data in a file, but it is relatively slow to read and write data in the file.

So now we use a relational database management system to store and manage a large amount of data. The so-called relational database is a database based on the relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra.

The characteristics of relational database management system (RDBMS):

1. The data appears in tabular form.

two。 Various record names for each behavior

3. The data field corresponding to the record name of each column

4. Many rows and columns form a form

5. Several forms make up database

Nowadays, there are many commercial relational database management systems, such as Oracle,IBM DB2 and Microsoft SQL Server.

There are also many free and open source relational databases, such as MySQL,mSQL (mini-SQL) and embedded JavaDB (Apache Derby).

II. MySQL database

1. Positioning:

Open source, multi-platform, relational database

Currently the most widely used and most popular open source database.

2. Features:

Function: supports transactions, conforms to the principle of relational database, conforms to ACID, supports most SQL specifications, organizes data in a two-dimensional table, has plug-in storage engine, and supports multiple storage engine formats

Deployment: database deployment can be completed quickly by compiling and installing, or by binary package, according to "install software-create instance-database table user initialization".

Use: use standard SQL statements for database management, simple SQL statements have good concurrency and performance, and do not support views, stored procedures, functions, triggers, etc.

Monitoring: there are some commonly used command display status and performance in the command line interface. In the graphical interface, there are many open source monitoring tools to monitor and record the status of the database, such as zabbix,nagios,cacti,lepus, etc.

Backup: logical backup mysqldump/mysqldumper, physical backup using xtrabackup and other tools for backup

High availability: MySQL high availability has a variety of solutions, official basic master-slave master-slave replication, new version of innodb cluster, third-party high availability solutions such as MHA

Extension: MySQL horizontal split, which can be logically mapped and split in the middle of horizontal split proxy to expand the concurrency and throughput of MySQL database.

3. Applicable scenarios:

Default innodb storage engine that supports most OLTP scenarios with high concurrency and simplicity

Tokudb storage engine, scenarios that use highly concurrent insert

Inforbright storage engine, which can perform column compression and OLAP statistical query scenarios

4. Attention to selection:

When using MySQL for OLTP business, you need to pay attention to the data magnitude. If the data magnitude is too large, you need to split it horizontally.

If you have OLAP requirements, you can consider them in combination with other architectures.

Third, the tester interview: the required MySQL query sentence

1. Check which database is currently being used

Mysql > select database ()

In addition, which one is the current library can also be seen in the output of the following two statements

Mysql > show tables

Mysql > status

2. View MySQL version and status

Mysql > select VERSION ()

Mysql > status

3. View the current status of the MySQL instance (in the form of parameters)

Mysql > show status

4. View the parameters of the MySQL instance

Mysql > show variables

View maximum number of connections

Mysql > show variables like'% max_connections%'

5. View the current process of the MySQL instance

Mysql > show processlist

6. Query all data

Select * from Info check all data

Select Code,Name from Info Chart fixed column

7. Check according to the conditions

Select * from Info where Code='p001' a conditional query

Select * from Info where Code='p001' and Natio multi-condition union relation query

Select * from Info where Name=' Hu Jun'or Nation='n001' Multi-conditional or Relational query

Select * from Car where Price > = 50 and Price2 query the number of more than 2 in all series

12. Paging query

Select * from Car limit 05.Skip a few pieces of data to get a few pieces of data

13. Go to re-query

Select distinct Brand from Car

14. Query and build database and table statements

Mysql > show create database dbname

Query table statements only after the library is specified.

Mysql > show create table tablename

15. Query the field properties of the specified table

Mysql > show full columns from tablename

Or

Mysql > show full fields from tablename

After reading this, the article "what are the MySQL database query sentences often asked in the interview" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself to understand it. If you want to know more about related articles, welcome to follow the industry information channel.

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.