mysql-basic Commands

Start: net start mySql;
Enter: Mysql-u root-p/mysql-h localhost-u root-p databaseName;
List databases: show databases;
Select database: Use DatabaseName;
List table: Show tables;
Show table Column properties: Show columns from TableName;
Build database: Source FileName.txt;
Match characters: You can use wildcard characters _ to represent any one 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 you cannot break the word, and when inserting or changing data, you cannot expand the string of the field into multiple lines, or the hard return will be stored in the data;
Add an Administrator account: Grant all on * * to [e-mail protected] identified by "password";
Fill in the end of each statement with a semicolon '; ', or add ' \g ' to it;
Query time: Select Now ();
Query Current User: Select User ();
Querying database version: Select version ();
Query the database currently in use: select databases ();
  
1. Delete the students data sheet from the Student_course database:
Rm-f student_course/students.*
  
2. Back up the database: (Backup DATABASE Test)
Mysqldump-u root-p Test>c:\test.txt
Backup table: (Back up the MyTable table under test database)
Mysqldump-u root-p Test Mytable>c:\test.txt
Import the backup data into the database: (Back to test database)
Mysql-u root-p Test
  
3. Create temporary table: (Create temp table Zengchao)
Create temporary table Zengchao (name varchar (10));
  
4. Create a table to determine if the table exists first
CREATE table if not exists students (...);
  
5. Copying the structure of tables from existing tables
CREATE TABLE table2 SELECT * FROM table1 where 1<>1;
  
6. Copy the table
CREATE TABLE table2 SELECT * FROM table1;
  
7. Renaming the table
ALTER TABLE table1 Rename as table2;
  
8. Modify the type of the column
ALTER TABLE table1 Modify ID int unsigned;//The type of the modified column ID is int unsigned
ALTER TABLE table1 change ID sid int unsigned;//the name of the modified column ID is SID, and the property is modified 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);//establishing a unique indexing
  
10. Delete Index
Drop index idx_id on table1;
ALTER TABLE table1 DROP INDEX ind_id;
  
11, union character or multiple columns (Connect column ID to ":" and column name and "=")
Select Concat (ID, ': ', name, ' = ') from students;
  
12, limit (choose 10 to 20) < The first Recordset is numbered 0>
SELECT * FROM students order by ID limit 9, 10;
  
13, MySQL does not support the function
Transactions, views, foreign key and referential integrity, stored procedures, and triggers

14, MySQL will use the index operation symbol
<,<=,>=,>,=,between,in, like with no% or _
  
15. Disadvantages of using indexes
1) Slow down or change the speed of data;
2) occupy disk space;
3) Increase the burden of the query optimizer;
When the query optimizer generates an execution plan, it takes into account the index, too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query scheme;
  
16. Analyze Index efficiency
Method: Add explain before the general SQL statement;
Meaning of the analysis result:
1) Table: list name;
2) Type: Types of connections, (ALL/RANGE/REF). Where ref is the most desirable;
3) Possible_keys: The index name that the query can use;
4) Key: The actual use of the index;
5) Key_len: The length of the part used in the index (bytes);
6) Ref: Displays the column name or "Const" (does not understand what meaning);
7) Rows: Shows the number of rows that MySQL has to scan before finding the correct results;
8) Extra:mysql's recommendations;
  
17. Use shorter fixed-length columns
1) Use a shorter data type whenever possible;
2) Use fixed length data type whenever possible;
a) using char instead of varchar, fixed-length data processing is faster than the longer;
b) for frequently modified tables, the disk is prone to fragmentation, thus affecting the overall performance of the database;
c) In the event of a data table crash, a table with fixed-length data rows is easier to reconstruct. With fixed-length data rows, each record's starting position is a multiple of a fixed record length and can be easily detected, but using variable-length data rows is not necessarily the same;
d) for MyISAM types of data tables, although conversion to fixed-length data columns can improve performance, but occupy a large space;
  
18. Use NOT NULL and enum
Try to define the column as NOT NULL, which makes the data come out faster, requires less space, and when queried, MySQL does not need to check for a special case, or null value, to optimize the query;
If a column contains only a limited number of specific values, such as gender, whether it is valid or the year of entry, etc., it should be considered in this case to convert it to the value of the enum column, MySQL processing faster, because all enum values in the system are represented by the identification value;
  
19. Using Optimize table
For frequently modified tables, it is easy to fragment, making it necessary to read more disk blocks when querying the database and to reduce query performance. A disk fragmentation problem exists with variable-length tables, which are more prominent for BLOB data types because their size varies greatly. You can defragment the data by using optimize table to ensure that database performance is not degraded and that the tables that are affected by fragmentation are optimized. Optimize table can be used for data tables of type MyISAM and BDB. In fact, any defragmentation method is to use mysqldump to dump the data table, and then use the dump file and re-create the data table;
  
20. Using procedure analyse ()
You can use procedure analyse () to display the best type of recommendation, which is simple, with the addition of Procedure analyse () after the SELECT statement, for example:
SELECT * From students procedure analyse ();
SELECT * From students procedure analyse (16,256);
The second statement requires procedure analyse () not to suggest that there are more than 16 values, or an enum type with more than 256 bytes, and that if there is no limit, the output may be very long;
  
21. Using Query Caching
1) How the query cache works:
When a SELECT statement is executed for the first time, the server remembers the query's text content and query results, stores it in the cache, and returns the result directly from the cache the next time it encounters the statement, and when the data table is updated, any cached queries for that data table become invalid and discarded.
2) Configure the cache parameters:
Variables: Query_cache _type, querying the operating mode of the cache. There are 3 modes, 0: Not cached, 1: Cache queries, unless you start with select Sql_no_cache; 2: Only those queries that start with select Sql_cache are cached as needed; Query_cache_size: Sets the maximum result set size of the query cache, Larger than this value will not be cached.
  
22. Adjust the hardware
1) Install more memory on the machine;
2) Increase the speed of the hard drive to reduce I/O wait time;
Seek time is the main factor to determine the performance, literally moving the head is the slowest, once the head positioning, from the track to read quickly;
3) Re-allocate disk activity on different physical hard disk devices;
If possible, you should store the busiest databases on different physical devices, which are different from the different partitions that use the same physical device, because they will compete for the same physical resources (heads).

mysql-basic Commands