MySQL is so simple. Part Two

1. Summary of the use of MySQL client tools

About executing SQL scripts In the MySQL client, you can Source/xxx/yyy/zzz.sql If the shell command line can be executed mysql-uxxx-pyyy-hzzz </.../.../test.sql Note The server-side command to end, the client command can be used without We can use delimiter to define You need to use delimiter in defining stored procedures \g \g \g means that the command before \g is forced to be sent directly to the server side, equivalent to the default; \g represents \g and displays the result set in a specific format, which is useful in real-world development when there are particularly many fields in a table. Mysql> SELECT * from users\g*************************** 1. Row *************************** id:001 Name:zhangsan sex:birthday:null*************************** 2. Row *************************** id:002 name:lisi Sex:birthday:NULL2 rows in Set (0.00 sec) System executes shell commands in the MySQL client mysql> system ls-ld/var/lib/mysqldrwxr-xr-x 7 mysql mysql 4096 Sep 20:34/var/lib/mysql Quit Quit client

2. Note Using the Help COMMAND

mysql> help create indexname:  ' Create index ' description:syntax:create [online | offline] [unique| Fulltext| Spatial] index index_name    [index_type]    on tbl_ name  (Index_col_name,...)     [index_option] ...index_col_name:    col_name [(length)]  [asc | desc]index_type:    using {btree | hash}index_option :    key_block_size [=] value  | index_type  |  With parser parser_namecreate index is mapped to an alter table  statement to create indexes. see [help alter table]. create index cannot be used to  create a primarykey; use alter table instead. for more  Information about indexes, seehttp://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html.url: http://dev.mysql.com/doc/ Refman/5.1/en/create-index.html

3.mysqladmin

Mysqladmin is a way to manipulate the MySQL database directly in the shell command line. Mysqladmin-uxxx-pyyy-hzzz Of course, if we have defined the User,host,password of the client connection in MY.CNF, then we can Direct Use mysqladmin COMMAND Commonly used as follows: Create, delete database   Mysqladmin Create Db_name Mysqladmin Drop Db_name View SERVER state variables Mysqladmin Extended-status Reread Authorization Form Mysqladmin flush-privileges Commands related to master and slave replication Mysqladmin Start-slave Mysqladmin Stop-slave To view brief server information [Email protected] ~]# mysqladmin status uptime:875 threads:1 questions:4 Slow queries:0 opens:0 Flush tables:1 Open tables:6 queries per second avg: 0.005 Shutting down the server Mysqladmin shutdown

4.MySQL system

When a user initiates a request to the MySQL Connection Manager , MySQL will determine whether the user has access to MySQL according to the User Rights Management module If the user has permission to connect to MySQL, then the thread manager of MySQL initiates a thread to process the user's request It is important to note that MySQL is a single-process multithreaded structure, and each user request creates a thread response. The thread that processes the user's request then orders the command to the Management module , and the command management module will parse the command, and if the command does not have a syntax error, it will pass the post analysis to the cache module be cached for the next use. At the same time, a log module is logged. After the command Manager is processed, commands are sent to the command parser, such as the SELECT statement, which is optimized within MySQL, such as a query table, and a table definition module to determine the table and field information. The access control module further checks to see if the user has permission to manipulate an object in the database and, if so, to the Table Manager , the table Manager notifies the storage engine to complete the physical operation. In fact, there are three levels of relational databases: Presentation Layer (Relationship)--"logical layer (storage engine)--" Physical layer (file) We see various database objects in the database, such as tables, views, and so on, to be reflected in the file. This process is done by the storage engine. For MySQL, it is support plug-in storage engine, more commonly used there are two, one is MyISAM, one is InnoDB. In simple terms, MyISAM performance is good, but does not support transactions, for more queries of the system is very suitable, such as Data Warehouse. And InnoDB is a support business, is a work very similar to Oracle, more suitable for online transaction systems.

This article is from the "I want to surpass myself" blog, please be sure to keep this source http://zhangfengzhe.blog.51cto.com/8855103/1552483

MySQL is so simple. Part Two