MySQL Common interview problem summary (ii)

031 Create a unique index on a table that already exists

1. CREATE UNIQUE INDEX index_name on table_name (

Field_name [(Index_length)] [asc| DESC])

2. ALTER TABLE table_name ADD UNIQUE index| KEY

Index_name (Field_name [(Index_length)] [asc| DESC])

032 Create a Full-text index when creating a table

TABLE_NAME (

column_name

Fulltext index| KEY [Index_name] (

Field_name [(Index_length)] [asc| DESC])

)

033 Create a Full-text index on a table that already exists

1. CREATE fulltext INDEX index_name on table_name (

Field_name [(Index_length)] [asc| DESC])

2. ALTER TABLE table_name ADD fulltext index| KEY

Index_name (Field_name [(Index_length)] [asc| DESC])

034 creating multiple-column indexes

Similar to the method above

Index_name (Field_name_1 [(Index_length)] [asc| DESC],

... ,

Field_name_n [(Index_length)] [asc| DESC])

035 Delete Index

DROP INDEX index_name on table_name

036 Creating a View

CREATE VIEW view_name as Select_query

037 View View information

Show TABLE STATUS [from database_name] [like ' pattern ']

038 View design Information

Desc[ribe] View_name

039 Delete view

DROP VIEW view_name[, view_name]

040 modifying views

1, CREATE OR REPLACE VIEW view_name as Select_query

2, ALTER VIEW view_name as Select_query

041 creating triggers

CREATE Tirgger trigger_name

before| After delete| Insert| UPDATE

On table_name for each ROW

Triggle_statement

Triggle_statement: Trigger is triggered to execute the statement (add, delete, change, check, etc.)

042 viewing triggers

Show triggers

043 Delete triggers

DROP TRIGGER trigger_name

044 inserting data

INSERT into table_name (field_1, field_2, ...) VALUES (Value_1, value_2, Vaule_3, ...)

045 View the MySQL table structure of the command, as follows:

DESC table name;

Show columns from table name;

describe table name;

Show create table name;

Use Information_schema

SELECT * from columns where table_name= ' table name ';

046 Database Paging Query

SELECT * from Userdetail where UserID limit 0,20

047 MySQL Database engine type

(mysql-5.5.5 begins, InnoDB as the default storage engine) before MyISAM, and even sooner ISAM the database engine you can use depends on how MySQL was compiled at installation time. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and heap. The other two types of InnoDB and Berkleydb (BDB) are also often available.

ISAM is a well-defined and time-tested method of data table management, designed to take into account that the number of times a database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two major drawbacks of ISAM are that it does not support transactions or fault tolerance: If your hard drive crashes, the data files cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and MySQL can support such a backup application by replicating the features.

MYISAM is MySQL's ISAM extended format and default database engine (prior to 5.5). In addition to providing a large number of functions for indexing and field management not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The price is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as MYISAMCHK tools for repairing database files and Myisampack tools to restore wasted space.

MyISAM highlights the fast read operation, which may be the main reason why MySQL is so favored by Web development: The large amount of data operations you do in Web development are read operations. Therefore, most virtual host providers and internet platform providers only allow the use of the MyISAM format.

HEAP allows temporary tables that reside only in memory. The presence of memory makes heap faster than ISAM and MyISAM, but the data it manages is unstable and if it is not saved before shutdown, all data will be lost. Heap does not waste a lot of space when data rows are deleted, heap tables are useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using it.

The INNODB and berkleydb (BDB) database engine is a direct product of the technology that creates MySQL flexibility, and this technology is the mysql++ API. When using MySQL, almost every challenge you face comes from ISAM and myiasm the database engine does not support transaction processing or foreign key support. Although it is much slower than the ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not in the top two engines. As mentioned earlier, if your design requires one or both of these features, you will be forced to use one of the last two engines.

048 MySQL Lock type

According to the type of lock , it can be divided into shared lock , exclusive lock , intent sharing lock and intent exclusive lock.

According to the size of the lock , and can be divided into row locks , table Locks .

For MySQL, the transaction mechanism is implemented more by the underlying storage engine. As a result, the MySQL level has only table locks, while the InnoDB storage engine that supports transactions implements row locks (record locks (locks on the corresponding index records)), Gap locks (locks that are on the interval of index Records), Next-key Lock (a combination of the lock on the gap before recording the lock and before this index record). The record lock of MySQL is the lock of the index record, because the INNODB is the Index organization table, the Gap lock is the lock of the index record gap, the lock is valid only under the RR isolation level, the Next-key lock is the combination of Gap lock before record lock plus record. MySQL implements RR isolation levels through gap locks and next-key locks.

Note: For the update operation (read not locked), only go index can uplink lock, otherwise the clustered index on each row of the lock, the actual equivalent to write a lock on the table.

If multiple physical records correspond to the same index, a lock conflict will also occur if access is simultaneous.

When a table has more than one index, different transactions can lock different rows with different indexes, and InnoDB locks the data record (clustered index) simultaneously with row locks.

MVCC (multiple version concurrency control) concurrency control mechanism, any operation will not block read operation, read operation will not block any operation, because read not locked.

shared lock : A lock that is added by a read table operation, and other users can only get shared locks on that table or row, and cannot get exclusive locks, which means they can only read and write

Exclusive lock : A lock that is added by a write table operation and other users cannot obtain any locks on the table or row after the lock is added, typically the update operation in the MySQL transaction

Intent Shared Lock (IS): A transaction intends to add rows to a data row to share a lock, which must be locked before a transaction can share a lock on a data row.

Intent Exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must first obtain an IX lock on the data row before it can be added to an exclusive lock.

049 MySQL support transaction?

In default mode, MySQL is autocommit mode, all database update operations are submitted immediately, so MySQL does not support transactions by default. But if your MySQL table type is using InnoDB tables or BDB tables, your MySQL can use transaction processing, and using Set autocommit=0 allows MySQL to be allowed in non-autocommit mode, In a autocommit mode, you must use a commit to submit your changes, or roll back your changes with rollback.

Examples are as follows:

START TRANSACTION;

SELECT @a:=sum (Salary) from table1 WHERE type=1;

UPDATE table2 SET summmary=@a WHERE type=1;

COMMIT;

What are the features of 050 MySQL compared to other databases?

1, can handle with the thousands record of large data

2. Support Common SQL statement specification

3, can be transplanted high, simple and compact installation

4, the good operation efficiency, has the rich information network support

5, debugging, management, optimization simple (relative to other large databases)

051 How to solve the MySQL database Chinese garbled problem?

1. Specify the character set when the database is installed

2. If you can change the configuration file after you have finished

3, the establishment of the database time: Specify the character set type

4, the time to build the table also specify the character set

052 How to improve the security of MySQL?

1. If the MySQL client and server-side connections need to cross over and pass through an untrusted network, you will need to use an SSH tunnel to encrypt the communication for that connection.

2. Use the Set Password statement to modify the user's password, first "Mysql-u root" login to the database system, and then mysql> update mysql.user set Password=password (' Newpwd '), The final execution of flush privileges is OK.

3.Mysql attacks that need to be guarded against eavesdropping, tampering, playback, denial of service, etc., do not involve usability and fault tolerance. All connections, queries, and other operations are performed using security methods based on ACLs, which are access control lists. There are also some support for SSL connections.

4. Set the user table in the MySQL master database is not allowed to be accessed by anyone other than the root user; Once the encrypted user password is encrypted and stored in the user table, the other person can use the corresponding database of the username/password as soon as it is compromised;

5. Use GRANT and REVOKE statements for user access control work;

6. Do not use plaintext password, but use MD5 () and SHA1 (), such as one-way HA functions to set the password;

7. Do not use the word in the dictionary to do the password;

8. The use of firewalls can remove 50% of the external risk, so that the database system behind the firewall to work, or placed in the DMZ area;

9. The use of Nmap to scan 3306 ports from the Internet can also be tested using Telnet server_host 3306, which does not allow access to the No. 3306 TCP port of the database server from the untrusted network and requires a firewall or router setting;

10. In order to prevent malicious entry of illegal parameters, such as where id=234, others enter where id=234 or 1=1 cause all to be displayed, use "or" in a Web form to use a string, add a%22 in a dynamic URL to represent double quotes,% 23 for well numbers,% 27 represents single quotes; passing unchecked values to the MySQL database is very dangerous;

11. Check the size when passing data to MySQL;

12. Applications need to connect to the database should use a general user account, open a few necessary permissions to the user;

13. Use a specific ' escape character ' function in each programming interface (c C + + PHP perl java JDBC, etc.); The use of MySQL database on the Internet must be used less data transmitted in plaintext, and SSL and SSH encryption method data transmission;

14. Learn to use the tcpdump and strings tools to view the security of transmitted data, such as Tcpdump-l-I eth0-w-src or

DST Port 3306 strings. To start MySQL database service with ordinary users;

15. Do not use the join symbol of the table, choose the parameter –skip-symbolic-links;

16. Convinced that only users starting the database service in the MySQL directory can have read and write permissions on the file;

17. Do not allow process or super permissions to the unmanaged user, the Mysqladmin processlist can enumerate the current execution of the query text; Super permission can be used to sever the client connection, change the server running parameter status, and control the copy database server;

18.file permissions are not paid to users other than administrators to prevent load data '/etc/passwd ' from appearing in the table and then using Select to display

of the problem;

19. If you do not trust the services of the DNS service company, you can set only the IP numeric address in the Host name allow table;

20. Use the max_user_connections variable to enable the Mysqld service process to limit the number of connections to a given account;

The 21.grant statement also supports resource control options;

22.–local-infile=0 or 1 if 0 the client program cannot use local load data, an example of empowering is grant insert (user) on Mysql.user to ' user_name ' @ ' host_name ' If using the –skip-grant-tables system will not make any access control to any user's access, but you can use mysqladmin flush-privileges or mysqladmin reload to open access control; By default, show The databases statement is open to all users and can be shut down with –skip-show-databases.

23. When you encounter error 1045 (28000) Access denied for user ' root ' @ ' localhost ' (using password:no) errors, you need to reset your password by using first- The Skip-grant-tables parameter starts Mysqld and then executes mysql-u root mysql,mysql>update user set Password=password (' NewPassword ') where user= ' root '; Mysql>flush privileges, and finally restart MySQL.

053 MySQL Gets the current time function is?, the function that formats the date is

Get the current time with now () on the line. Format the time in the database with Date_format (date, format). According to the format string

Format formats date or date time value date, returns the result string.

054 How do you determine if MySQL is running?

Answer: Debian runs the command service MySQL status, runs the command service mysqld status on the Redhat. Then look at the output.

055 How do I turn on or off MySQL service?

Answer: Run command service mysqld start services; Run command service mysqld stop service.

056 How do I log in to MySQL through the Shell?

Answer: Run command mysql-u root–p

057 How do I list all the databases?

Answer: Run the command show databases;

058 How do I switch to a database and work on it?

Answer: Run command use database_name; Enter the database named database_name.

059 How do I list all the tables in a database?

Answer: Run the command in the current database show tables;

060 How do I get the names and types of all the Field objects in the table?

Answer: Run command describe table_name;