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

This article mainly introduces "MYSQL basic command summary". In daily operation, I believe many people have doubts about the summary of MYSQL basic commands. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MYSQL basic command summary". Next, please follow the editor to study!

Launch: net start mySql

Enter: mysql-u root-p/mysql-h localhost-u root-p databaseName

List: show databases

Select database: use databaseName

List table: show tables

Display the properties of the table column: show columns from tableName

Set up the database: source fileName.txt

Matching characters: you can use the wildcard character _ to represent any character and% to represent any string

Add a field: alter table tabelName add column fieldName dateType

Add multiple fields: alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType

Multi-line command input: note that words cannot be disconnected; when inserting or changing data, the string of the field cannot be expanded into multiple lines, otherwise hard enter will be stored in the data

Add an administrator account: grant all on *. * to identified by "password"

Add a semicolon at the end of each statement after you have finished typing; or you can add g

Query time: select now ()

Query the current user: select user ()

Query database version: select version ()

Query the currently used database: select database ()

1. Delete the students table in the student_course database:

Rm-f student_course/students.*

2. Back up the database: (back up the database test)

Dump-u root-p test > c: est.txt

Backup table: (backup mytable table under test database)

Mysqldump-u root-p test mytable > c: est.txt

Import backup data into the database: (import back to test database)

Mysql-u root-p test

3. Create a temporary table: (create a temporary table zengchao)

Create temporary table zengchao (name varchar (10))

4. To create a table is to determine whether the table exists or not.

Create table if not exists students (…)

5. Copy the structure of the table from the existing table

Create table table2 select * from table1 where 11

6. Copy the table

Create table table2 select * from table1

7. Rename the table

Alter table table1 rename as table2

8. Modify the type of column

Alter table table1 modify id int unsigned;// modifies the type of column id to int unsigned

Alter table table1 change id sid int unsigned;// changed the name of the column id to sid and changed the property to int unsigned

9. Create an index

Alter table table1 add index ind_id (id)

Create index ind_id on table1 (id)

Create unique index ind_id on table1 (id); / / build uniqueness index

10. Delete the index

Drop index idx_id on table1

Alter table table1 drop index ind_id

11. Join characters or multiple columns (connect column id with ":" and columns name and "=")

Select concat (id,:,name,=) from students

12. Limit (10 to 20 selected)

Select * from students order by id limit 9 and 10

13. Features not supported by MySQL

Transactions, views, foreign keys and referential integrity, stored procedures and triggers

14. MySQL will use the operation symbol of the index

, =, between,in, without like starting with% or _

15. Disadvantages of using indexes

1) slow down the speed of adding, deleting and correcting data

2) take up disk space

3) increase the burden of the query optimizer

When the query optimizer generates the execution plan, it will consider the index. Too many indexes will increase the workload of the query optimizer and make it impossible to choose the best query scheme.

16. Analyze the efficiency of index

Method: add explain before the general SQL statement

The meaning of the analysis results:

1) table: table name

2) type: type of connection, (ALL/Range/Ref). Among them, ref is the most ideal

3) possible_keys: the index name that can be used by the query

4) key: the index actually used

5) key_len: the length of the part used in the index (in bytes)

6) ref: displays the column name or "const" (I don't know what it means)

7) rows: displays the number of rows that MySQL believes must be scanned before finding the correct results

8) extra:MySQL 's suggestion

17. Use shorter fixed-length columns

1) use shorter data types as much as possible

2) use fixed-length data types whenever possible

A) using char instead of varchar, the data processing ratio of fixed length becomes longer.

Hurry up.

B) for tables that are frequently modified, the disk is easily fragmented, thus affecting the overall performance of the database

C) in the event of a data table crash, tables that use fixed-length data rows are easier to reconstruct. Using fixed-length data rows, the starting position of each record is a multiple of the fixed record length, which can be easily detected, but not necessarily using variable-length data rows.

D) for MyISAM-type data tables, although converting to fixed-length data columns can improve performance, it also takes up a lot of space.

18. Use not null and enum

Define the column as not null as far as possible, so that the data can come out faster and require less space, and when querying, MySQL does not need to check whether there is a special case, that is, a null value, thus optimizing the query

If a column contains only a limited number of specific values, such as gender, validity or school year, in this case, you should consider converting it to the value of the enum column. MySQL processes it faster, because all enum values are expressed as identification values in the system.

19. Use optimize table

For frequently modified tables, it is easy to produce fragments, so that more disk blocks must be read when querying the database, which reduces the query performance. All tables with variable length have a disk fragmentation problem, which is more prominent for blob data types because of their large size changes. You can use optimize table to defragment to ensure that database performance does not degrade and optimize data tables that are affected by fragmentation. Optimize table can be used for data tables of both MyISAM and BDB types. In fact, any defragmentation method is to use mysqldump to dump the data table, and then use the transferred file and recreate the data table.

20. Use procedure analyse ()

You can use procedure analyse () to display the best type of suggestion, which is as simple as adding procedure analyse () to the select statement; for example:

Select * from students procedure analyse ()

Select * from students procedure analyse (16256)

The second statement asks procedure analyse () not to recommend enum types with more than 16 values or more than 256bytes. If there is no * *, the output may be very long.

21. Use query caching

1) how the query cache works:

The first time a select statement is executed, the server remembers the text content and the query results of the query, stores them in the cache, and returns the results directly from the cache the next time the statement is encountered; when the data table is updated, any cached queries of the data table become invalid and will be discarded.

2) configure cache parameters:

Variable: query_cache _ type, the operation mode of the query cache. There are 3 modes, 0: do not cache; 1: cache queries unless they start with select sql_no_cache; 2: cache only those queries that start with select sql_cache as needed; query_cache_size: set the size of the maximum result set of the query cache, and those larger than this value will not be cached.

22. Adjust the hardware

1) install more memory on the machine

2) add a faster hard drive to reduce the waiting time of Iripple O

Seek time is the main factor that determines performance. Moving the head word by word is the slowest. Once the head is positioned, it will be read quickly from the track.

3) redistribute disk activity on different physical hard disk devices

If possible, the busiest databases should be stored on different physical devices, which is different from different partitions using the same physical device, because they will compete for the same physical resources (heads).

At this point, the study on the summary of MYSQL basic commands is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.