1. How do you log mysql database
mysql -u username -p
2, how to turn on / off mysql service
service mysql start / stop
3, view the status of mysql
service mysql status
4, how to display the number of all databases
5, how to get the names and types of all the fields in the table objects
6, MYSQL support transactions do?
In the default mode, MYSQL is autocommit mode, all database updates will be immediately submitted, so by default, mysql does not support transactions.
But if your MYSQL table type is to use InnoDB Tables or BDB tables, then your MYSQL can use transaction processing, use SET AUTOCOMMIT = 0 can make MYSQL allow non-autocommit mode, in non-autocommit mode, you must use COMMIT to submit your changes, or use rOLLBACK to roll back your changes.
Examples are as follows:
SELECT @A: = SUM (salary) FROM table1 WHERE type = 1;
UPDATE table2 SET summmary = @ A WHERE type = 1;
7, MYSQL database in which features compared to the other?
MySQL is a small relational database management system, development of the Swedish company MySQL AB, now the company has been acquired by Sun, support FreeBSD, Linux, MAC, Windows and other operating systems and other large databases such as Oracle, DB2, SQL Server and other less powerful compared to some of the
1 can handle with tens of millions of records of large data
2, support for common SQL statements Specification
3, high rows portable, easy to install compact
4, good operating efficiency, there is a wealth of information for network support
5, commissioning, management, optimization simple (relative to other large databases)
8, the difference between varchar and char
Char is a type of fixed-length, varchar type is a variable-length
9, database things are there?
Isolation, persistence, consistency, atomicity
10, please concisely describe four kinds of transaction isolation level under the name of the MySQL InnoDB support, as well as the difference between progressive?
SQL standard defines four isolation levels as follows:
read uncommited: Read Uncommitted content
read committed: to read the submission
repeatable read: can reread
Explained in detail as follows:
Read Uncommitted (read uncommitted content)
In this isolation level, all other transaction can see the results of uncommitted transactions. The isolation level is rarely used in practical applications, because its performance is not much better than the other levels. Read uncommitted, also known as a dirty read (Dirty Read).
Read Committed (read submission)
This is the default isolation level for most database systems (MySQL, but not the default). It meets the simple definition of isolation: a transaction can only see the changes already made to submit firm. This isolation level also supports the so-called non-repeatable read (Nonrepeatable Read), because other instances of the same transaction in this instance during the process may be new commit, so select the same may return a different result.
Repeatable Read (can be re-read)
This is the MySQL default transaction isolation level, it ensures that multiple instances of the same transaction during concurrent reads data, you will see the same data row. In theory, however, this will lead to another thorny issue: phantom read (Phantom Read). Simply put, phantom read means that when a user reads a range of data rows and another transaction within the scope of inserting a new row, and then when the user reads the range of data rows, you will find a new " Phantom "line. InnoDB and Falcon storage engine through a multi-version concurrency control (MVCC, Multiversion Concurrency Control gap lock) mechanism to solve the problem. Note: In fact, only the problem of multiple versions of non-repeatable read problem, plus a gap lock (that is, it is here called concurrency control) to solve the problem of phantom read.
This is the highest level of isolation by enforcing transaction ordering, making it impossible to conflict with each other, so as to solve the problem phantom read. In short, it is to add a shared lock on each row of data read. At this level, it may lead to a lot of timeouts and lock contention.
For different transactions with different isolation levels each with a different result. Different isolation levels are different phenomena. There are now the following three kinds:
1, dirty read (dirty read): a transaction can read another transaction has not been submitted to modify the data.
2, non-repeatable read (nonrepeatable read): In the same transaction, the same query reads a row at time T1, T2 in time to re-read this line, the data in this line has been modified occurred, may be updated ( update), it may also be deleted (delete).
3, phantom read (phantom read): In the same transaction, the same query multiple times, because of the other insert (insert) transaction is committed, each leading to a different result set is returned.
Different isolation levels are different phenomena, and have different locking / concurrency mechanism, the higher the isolation level, database concurrency worse
11, the difference between mysql database engine is MyISAM and InnoDB
12, Table mysql related rights which has several
MySQL server to control user access authority table by the database, permissions table stored in the mysql database by the mysql_install_db script to initialize. These permissions tables are user, db, table_priv, columns_priv and host. The following are brief structure and contents of these tables:
user permissions table: Record allows the user account information to connect to the server, which is the global authority level.
db permissions table: each record on each account authority to operate the database.
table_priv permissions table: Record operating authority data table level.
columns_priv permissions table: record operation right data at the column level.
host permissions table: table with db permissions on a given host database level permissions for more granular control. This permission is not affected table GRANT and REVOKE statements.
13, mysql storage engine What? How to modify mysql storage engine?
MyISAM indexed sequential access method (indexed sequential access method)
Check and repair MyISAM tables have most of the tools. Tables can be compressed, and supports full-text search close
Not transaction-safe, and does not support foreign keys.
MEMORY is the former (HEAP) A table in memory, a hash index table is distributed.
merge these tables for the purpose of inquiry, the myisam table set as a single table, so you can avoid the maximum file size limit in some operating systems.
This type of table only archive support, insert, select does not support delete, update, replace, do not use indexes.
csv tables stored in a single file server, which contains data separated by commas.
innodb This table is transaction-safe. Providing commit (submit) rollback (rollback practice) support foreign keys, slower than myisam.
Modify storage engine mysql alter table tablename type = innodb;
14, MYSQL table data repair and data recovery face questions
MYSQL Data Sheet under what circumstances easily damaged?
A sudden power failure the server data file corruption.
Forced shutdown, without turning mysql services.
What are the main phenomena data table is damaged?
Choose from a table of data, get the following error: Incorrect key file for table: '...' Try to repair it.
Queries can not find the line or return incomplete data in the table.
Error: Table 'p' is marked as crashed and should be repaired.
Open the table failed: Can not open file: 'xxx .MYI' (errno: 145).
Datasheet damage repair options?
Use myisamchk to repair, concrete steps are:
1) before the repair will stop mysql service.
2) Open the command line, and then proceeds to the mysql / bin directory.
3) execute myisamchk -recover path to the database where /*.MYI
OPTIMIZE table or use the repair table command to repair, REPAIR TABLE table_name repair tables OPTIMIZE TABLE table_name optimization REPAIR TABLE table used to repair damaged tables.
OPTIMIZE TABLE to reclaim unused space in the database when the data row table is deleted, the disk space occupied and was not immediately recovered, use OPTIMIZE TABLE command the space will be reclaimed, and the rows of data on the disk rearrangement (Note: It is on the disk, instead of the database)
15. The method of MYSQL database server performance analysis of what order?
Some worth monitor variable values:
Bytes_received and Bytes_sent
And between servers and from traffic.
Com_ * server command being performed.
Created_ * temporary tables and files created between query execution deadline.
Handler_ * storage engine operations.
Select_ * Different types of coupling implementation plan.
Sort_ * Several sorting information.
Show session status like 'Select';
SET profiling = 1;
Show profiles \ G
16, mysql field in the record what type of currency is good
NUMERIC and DECIMAL types are implemented as the same type of MySQL, this allows SQL92 standards. They are used to hold values, the exact accuracy of the values is extremely important values, such as data related to money. When you declare a class is one of these types, the precision and scale can be (and usually is) specified; for example:
salary DECIMAL (9,2)
In this example, 9 (precision) representatives will be used to store the value of the total number of decimal places, and 2 (scale) will be used on behalf of digits stored after the decimal point. Therefore, in this case, can be stored in the salary column values range from -9999999.99 to 9,999,999.99. In ANSI / ISO SQL92, the syntax DECIMAL (p) is equivalent to DECIMAL (p, 0). Similarly, the syntax DECIMAL is equivalent to DECIMAL (p, 0), where implementation is allowed to decide the value of p. MySQL does not currently support these variant forms of the DECIMAL / NUMERIC data types either. This is generally not a serious problem, because the main benefits of these types of apparently derived from the ability to control the precision and scale.
DECIMAL and NUMERIC values are stored as strings, rather than as a binary floating-point number, in order to preserve the decimal precision of those values. A value for every character, the decimal point (if scale> 0) and the "-" sign (for negative values). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.
DECIMAL and NUMERIC worth maximum range with DOUBLE the same, but for a given DECIMAL or NUMERIC column, the actual range may be limited by the system precision or scale for a given column. When such a column is assigned to the decimal point position exceeds the value of bit scale allowed to specify, based on the value scale rounding. When a DECIMAL or NUMERIC column is assigned to its size exceeds the specified value (or default) precision and scale implied range, MySQL stores data representing the corresponding endpoint values that range.