Common MySQL command Daquan One, the connection MySQL format: mysql-h host address-u user name-p user Password 1, example 1: Connect to the local MySQL. First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is: mysql>. 2. Example 2: Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: You and root can not add a space, the other is the same) 3, quit MySQL command: Exit (enter). Second, change the password format: mysqladmin-u user Name----old password password new password 1, example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command: Mysqladmin-uroot-password ab12 Note: Because Root does not have a password at the beginning, the-p old password can be omitted. 2, Example 2: Then change the root password to djg345. MYSQLADMIN-UROOT-PAB12 Password djg345 Third, add new users. (Note: Unlike above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as a command Terminator) format: Grant Select onDatabase. * To User name @ login host identified by \ "Password \" Example 1, add a userTest1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First use the root user to connect to MySQL, and then type the following command: Grant Select,insert,update,delete on * * to [email protected] identified by \ "Abc\"; If you do not want to test2 have a password, you can call another command to erase the password. Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by \ "\", which says login, add user, password change and so on. Let's look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon. 1, MySQL common command create database name; Create database use DatabaseName; Select database drop database name to delete the databases directly, do not remind show tables; Show table describe TableName; A detailed description of the table in select adds distinct to remove the repeating field mysqladmin drop database name is prompted before deleting the databases. Display current MySQL version and current date Select version (), current_date;2, modify the password for root in MySQL: shell>mysql-u root-pmysql> update user set Password=password ("Xueok654123″) where user= ' root ';mysql> flush privileges//Refresh database Mysql>use dbname; Open database: MySQL >show databases; Show all databases mysql>show tables; Displays all tables in the database MySQL: use MySQL first, then mysql>describe user; Show TableMySQL DatabaseThe column information of the user table); 3, Grant creates a full superuser who can connect to the server from anywhere, but must use a password something do this mysql> grant all privileges on the *. * to [email protected] identified by ' something ' with Add new user format: Grant select on database. * To User name @ login host identified by "password" grant all Privi Leges on * * to [email protected] identified by ' something ' with GRANT OPTION; Grant all privileges on * * to [email protected] "%" identified by ' something ' with GRANT OPTION; Remove authorization:mysql> Revoke All privileges on * * FROM [email protected] "%";mysql> delete from user where user= "root" and host= "%";mysql> Flush privileges; Create a user custom it363.com login on a specific client, access to a specific database fangchandbmysql >grant Select, INSERT, UPDATE, DELETE, Create,drop on fangchandb.* to [email protected] it363.com identified by ' passwd ' rename table: mysql > ALTER TABLE t1 RENA Me t2;4, mysqldump backup Database shell> mysqldump-h host-u root-p dbname >dbname_backup.sql Restore Database shell> mysqladmin-h myho St-u root-p Create dbnameshell> mysqldump-h host-u root-p dbname < DBname_backup.sql If you only want to unload the build table directive, the command is as follows:shell> mysqladmin-u root-p-D databasename > A.sql If you only want to unload the SQL command that inserts the data and do not need to build a table command, the command is:shell> mysqladmin-u root-p-t databasename > A.sql So what should I do if I only want the data and I don't want any SQL commands? mysqldump-t./phptest driver of which, only the-t parameter is specified to unload a plain text file that represents the directory where the data is unloaded./represents the current directory, that is, the same directory as the mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions. 5. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input. The shell can be used to type the redirection utility to complete this work. For example, if you have queries in file My_file.sql, you can execute these queries as follows: For example, if you want to write the statement in advance in sql.txt: mysql > mysql-h myhost-u root-p Database < Sql.txt 1, installation Environment: Windows Xpmysql 4.0.17 from the next need to use Mysql-uroot-proot to log in remote or local can use Mysql-h 172.5.1.183-uroot login, this is based on the policy of the second row to determine the permissions to modify the health Effect: 1) net stop mysqlnet start mysql2) c:\mysql\bin\mysqladmin Flush-privileges3) after logging in to MySQL, use flush privileges statement 6, Create databases Staffercreate database STAFFER;7, the following statement in the MySQL environment in the execution display user has permissions to the database show databases; switch to staffer database use staffer; Displays a table with permissions in the current database show tables; Display table staffer structure DESC staffer;8, creating a test environment 1) Creating a database staffermysql> create DaTabase staffer2) Creating Tables Staffer,department,position,depart_poscreate table s_position (id int NOT NULL auto_increment,name varchar () NOT NULL default ' manager ', #设定默认值description varchar, PRIMARY key Pk_positon (ID) #设定主键); CREATE TABLE Departme NT (ID int not NULL auto_increment,name varchar (a) NOT NULL default ' system department ', #设定默认值description varchar, primary key Pk_d Epartment (ID) #设定主键); Create table Depart_pos (department_id int not null,position_id int not null,primary key Pk_depart_po S (department_id,position_id) #设定复和主键); CREATE TABLE staffer (ID int not NULL auto_increment primary key, #设定主键name varchar ( Not null default ' anonymous ', #设定默认值department_id int not null,position_id int not null,unique (department_id,position_id) # Set unique values); 3) Delete mysql>drop table Depart_pos;drop table department;drop table s_position;drop table Staffer;drop Database Staffer;9, modify structure mysql> #表position增加列testalter table position Add (test char); #表position修改列testalter table Position Modify Test char (a) not null; #表position Modify the column test default value ALTER TABLE position ALTER TEST set default ' system '; #表position去掉test默认值alter table position alter test drop default; #表position去掉列testalter table position Drop column test; #表depart_pos删除主键alter table Depart_pos Drop primary key;# Table Depart_pos add primary key ALTER TABLE Depart_pos add primary key Pk_depart_pos (department_id,position_id); 10. Operation Data # Insert Table Departmentinsert into Department (name,description) VALUES (' System department ', ' System Department '); INSERT into department (name,description) VALUES (' PR ', ' PR '), insert into department (name,description) VALUES (' Customer service ', ' customer service '); INSERT into department (name, Description) VALUES (' Finance department ', ' finance Department '), insert into department (name,description) VALUES (' Test department ', ' Test Department '); #插入表s_positioninsert Into S_position (name,description) VALUES (' director ', ' director '), insert into s_position (name,description) VALUES (' manager ', ' manager '); Insert into s_position (name,description) VALUES (' Ordinary employees ', ' ordinary employees '), #插入表depart_posinsert into Depart_pos (department_id, position_id) Select a.ID department_id,b.id postion_idfrom Department a,s_position b; #插入表sTafferinsert into staffer (name,department_id,position_id) VALUES (' Chanda ', "n"); INSERT into staffer (name,department_id, position_id) VALUES (' Li Wenbin '), insert into staffer (name,department_id,position_id) VALUES (' Ma Jia ', 1,3); INSERT INTO Staffer (name,department_id,position_id) VALUES (' Zhiqiang ', 5,1); INSERT into staffer (NAME,DEPARTMENT_ID,POSITION_ID) VALUES (' Yang Yuju ', 4, 1); 11, Query and delete operations # Displays the personnel and positions of the system department select A.name,b.name Department_name,c.name Position_namefrom staffer A, Department b,s_position Cwhere A.department_id=b.id and A.position_id=c.id and B.name= ' System department '; #显示系统部的人数select Count (*) From staffer A,department bwhere a.department_id=b.id and b.name= ' System Department ' #显示各部门的人数select Count (*) Cou,b.namefrom staffer A, Department Bwhere A.department_id=b.idgroup by B.name, #删除客服部delete from department where name= ' customer service '; #将财务部修改为财务一部update Department set name= ' finance a ' where name= ' finance department; 12. Backing up and restoring a backup database Stafferc:\mysql\bin\mysqldump-uroot-proot staffer>e:\ Staffer.sql get the Staffer.sql is a SQL script, not including the building of the statement, so you need to manually create a database to import the recovery database staffer, you need to create an emptyLibrary Stafferc:\mysql\bin\mysql-uroot-proot Staffer<staffer.sql If you do not want to create staffer manually later, you can C:\mysql\bin\mysqldump-uroot- Proot--databases Staffer>e:\staffer.sqlmysql-uroot-proot >e:\ Staffer.sql But in this case the system can not exist staffer library, and can not import other names of the database, of course, you can manually modify Staffer.sql file 13, from the text to the database import data 1) using the tool c:\mysql\bin\ Mysqlimport The utility of this tool is to import files into and out of the table with the same filename extension name, such as Staffer.txt,staffer are commonly used in import into staffer table and functions as follows-D or--delete Remove all information from the data table before the new data is imported into the datasheet-F or--force whether or not an error is encountered, Mysqlimport forces the insertion of the data-I or--ignore mysqlimport to skip or ignore rows that have the same unique keyword. The data in the import file is ignored. -L or-lock-tables locks the table before it is inserted, which prevents the user's queries and updates from being affected when you update the database. -R or-replace This option is the opposite of the-I option, and this option will replace the record with the same unique keyword in the representative. --fields-enclosed-by= char Specifies, in many cases, the data in a text file is enclosed in double quotation marks. By default, the data is not surround by the word. --FIELDS-TERMINATED-BY=CHAR Specifies the delimiter between the values of each data, in a period-delimited file, where the delimiter is a period. You can use this option to specify a delimiter between data. The default delimiter is jump Geff (tab)--lines-terminated-by=str This option specifies the delimited string or character of data between rows and lines in a text file. By default, Mysqlimport with newline as the line delimiter. You can choose to replace a single character with a string: A new line or a carriage return. The common options for the Mysqlimport command are the-v display (version), the-P prompt for a password (password), and so on. There is a problem with this tool, and it is not possible to ignore some of the columns so that our data import hasA lot of trouble, although you can manually set this field, but there will be inexplicable results, we do a simple example we define the following depart_no.txt, saved in the E-disk, the interval is tab \T10 1011 1112 24 Execute the following command c:\mysql\bin\ Mysqlimport-uroot-proot staffer E:\depart_pos.txt here does not use the enclosing symbol of the column, the partition with the default \ t, because the use of other symbols will be problematic, do not know if it is not the cause of Windows 2) Load Data INFILE file_name into table_name (column1_name,column2_name) This command is used at the mysql> prompt, with the advantage that column import can be specified, such as under C:\mysql\bin\mysql -uroot-proot staffermysql>load Data infile "E:/depart_no.txt" into Depart_no (department_id,position_id); These two tools are used under Windows problems, do not know whether it is the cause of windows or Chinese problems, and do not specify the column it produced a null value, which is obviously not what we want, so use these tools carefully into the Mysql:mysql-uuser-ppassword- port=33071: Use the show statement to find out what database is currently present on the server:mysql> SHOW databases;2:2, create a database mysqldatamysql> MYSQLDATA;3: Select the database you created mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) ) 4: See what tables exist in the current database mysql> SHOW tables;5: Create a database table mysql> creation table MYTABLE (name VARCHAR, sex CHAR (1)); 6: Display the structure of a table:mysql> DESCRIBE mytable;7: Adding Records to a table mysql> insert into MYTABLE values ("HyQ", "M"); 8: Load data into a database table in text mode (for example, d:/ Mysql.txt) mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE mytable;9: import. sql File command (for example d:/mysql.sql) Mysql>use database;mysql> SOURCE d:/mysql.sql;10: Delete table mysql>drop tables mytable;11: Empty table Mysql>delete from mytable;12: Update data in table Mysql>update MYTABLE set sex= "F" where name= ' HyQ '; UPDATE [low_priority] [IGNORE] Tbl_nameset col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [ORDER by ...] [LIMIT rows]orupdate [low_priority] [IGNORE] tbl_name [, tbl_name ...] SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] Update updates the columns of rows in the existing table with a new value. The SET clause indicates which column to modify and what value they should give. If the WHERE clause is given, specify which row of records should be updated. Otherwise, all the record lines are updated. If the ORDER BY clause is specified, the record row is updated in the specified order. If you specify the keyword low_priority,update execution will be delayed until no other client is reading the table. If you specify the keyword IGNORE, the UPDATE statement will not abort abnormally, even if duplicate key errors occur during the update process. The row of records that caused the conflict will not be updated. If you access a column from tbl_name in an expression, UPDATE uses the current value of the column. For example, the following statement sets the Age column value to its current value plus 1:mysql> UPDATE persondata set age=age+1; The UPDATE assignment is calculated from left to right. For example, the following statement sets the age column to twice times, and then adds 1:mysql> update persondata set age=age*2, age=age+1; If you set the column as its current value, MySQL notices this and does not updateIt. UPDATE returns the number of record rows that were actually changed. In MySQL 3.22 or later, the C API function Mysql_info () returns the number of record rows that were matched and updated, as well as the number of warnings that occurred during the update. In MySQL 3.23, you can use LIMIT # to make sure that only a given number of record rows is changed. If an ORDER BY clause is used (supported from MySQL 4.0.0), the record row is updated in the specified order. This is actually only useful with limit. Starting with MySQL 4.0.4, you can also perform an update operation with multiple tables: Update items,month SET items.price=month.pricewhere items.id=month.id; Note: multiple tables UPDATE is not allowed to use ORDER by or LIMIT. Keyword: MySQL startup: net start MySQL; Enter: Mysql-u root-p/mysql-h localhost-u root-p databaseName; List databases: show databases; Select database: Use DatabaseName; List tables: show tables; Displays the properties of the table column: Show columns from TableName; 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 [email 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, backing up the database: (Backing up the database test) mysqldump-u root-p Test> ; C:\test.txt Backup table: (Back up the MyTable table under the test database) mysqldump-u root-p test Mytable>c:\test.txt Import the backup data into the database: (Back to test database) mysq L-u root-p Test 3, create a temporary table: (Create temporary table Zengchao) Create temporary table Zengchao (name varchar (10)); 4, create the table is the first to determine whether the table exists create table if not exists students (...); 5. Duplicate table structure from existing tables CREATE TABLE table2 SELECT * FROM table1 where 1<>1; 6. Copy table CREATE TABLE table2 select * FROM table1; 7, rename the table ALTER TABLE table1 rename as table2; 8. Modify the type of the column ALTER TABLE table1 modify ID int unsigned;//Modify the column ID of type int unsigned ALTER TABLE table1 change ID sid int Unsigned Change the name of the column ID to SID, and modify the property to int unsigned 9, create the index ALTER TABLE table1 ADD index ind_id (ID); CREATE INDEX ind_id on table1 (ID); Create unique index ind_id on table1 (ID);//Create a unique index 10, delete the index drop indexes idx_id on table1; AlterTable table1 DROP index ind_id; 11, union character or multiple columns (Connect column ID with ":" and column name and "=") select Concat (ID, ': ', name, ' = ') from students; 12, Limit (select 10 to 20) < The first recordset is numbered 0> SELECT * from students order by ID limit 9, 10; 13, MySQL does not support the functionality of transactions, views, foreign keys and referential integrity, stored procedures and triggers 14, MySQL will use the index of the operation symbol <,<=,>=,>,=,between,in, without% or _ the beginning of like 15, using Disadvantages of the index 1) slow deletion and change the speed of data, 2) take up disk space, 3) increase the burden of the query optimizer; When the query optimizer generates an execution plan, the index is taken into account, too many indexes increase the workload on the query optimizer, resulting in the inability to select the optimal query scheme; 16. Index efficiency method: In general The SQL statement is preceded by a explain; the meaning of the analysis result: 1) table name; 2) Type: type of connection, (ALL/RANGE/REF). Where ref is the ideal; 3) Possible_keys: The index name that the query can use, 4) key: The actual index used, 5) Key_len: The length of the part used in the index (bytes), 6) Ref: Display the column name or "Const" (do not understand what meaning); 7) Rows: Shows the number of rows that MySQL will have to scan before finding the correct results, 8) Extra:mysql recommendations, 17, using shorter fixed-length columns 1, using shorter data types whenever possible, 2) using fixed-length data types whenever possible, and a) substituting char for varchar, fixed length The degree of data processing is faster than the longer; b) for frequently modified tables, the disk is prone to fragmentation, which can affect the overall performance of the database; c) tables with fixed-length data rows are easier to reconstruct in case of a data table crash. With fixed-length data rows, each record's starting position is a multiple of a fixed record length, which can be easily detected, but it is not necessary to use a variable-length data row; 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 to define the column as NOT NULL, which makes the dataComes out faster, requires less space, and when queried, MySQL does not need to check for exceptions, or null values, to optimize queries, and if a column contains only a limited number of specific values, such as gender, whether it is valid, or the year of entry, you should consider converting it to the value of the enum column in this case. MySQL is processed faster because all enum values are represented in the system by identifying values; 19, using optimize table for frequently modified tables, it is easy to fragment, so that when querying the database must read more disk blocks, 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 using the dump file and re-create the data table, 20, using procedure analyse () can use procedure analyse () to display the best type of advice, the use is very simple, Add procedure analyse () to 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, if there is no limit, the output may be very long, 21, using the query cache 1) How the query cache works: When a SELECT statement is executed for the first time, The server remembers the text content and query results of the query, 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 cache Parameters: variable: Query_cache _type, query cache operation mode. 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) to install more memory on the machine;2) Increase the speed of the hard drive to reduce I/O wait time, the seek time is the main factor to determine performance, literally moving the head is the slowest, once the head is positioned, read from the track quickly, 3) on different physical hard disk devices to reallocate disk activity, if possible, the busiest database should be stored on different physical devices, This is different from different partitions that use the same physical device because they compete for the same physical resources (heads).
Common MySQL commands Daquan
