MySQL Common commands

Tag:ext    include     about     window     assign    ABC     Data Sheet    group    common    

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 the command Terminator) format: Grant Select on database. * To username @ Login host identified by \ "Password \" Example 1, Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. 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 [email protected] identified by \ "\", which says login, add user, password change and so on. Let's get down here.Check out MySQL for database-related operations. 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; Display column information for the user table in the MySQL database); 3. Grant creates a full superuser who can connect to the server from anywhere, but must use a password something to do this mysql> grant all privileges on *. * to [email protected] identified by ' something ' with Add new user format: Grant select on database. * To User name @ login host identified by "password" GRAN T all privileges 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> del Ete from user where user= "root" and host= "%";mysql> flush privileges; Create a user custom login on a specific client it363.com to access 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 rename t2;4, Mysqldu MP Backup Database shell> mysqldump-h host-u root-p dbname >dbname_backup.sql Restore Database shell> mysqladmin-h myhost-u root-p C reate dbnameshell> mysqldump-h host-u root-p dbname < Dbname_backup.sql If you just want to unload the build instruction, the command is as follows:shell> Mysqladmin-u R Oot-p-D databasename > A.sql If you only want to unload the SQL command that inserts the data, and you don't need to create a table command, the command is:shell> mysqladmin-u root-p-t databasename > a.sq　　 L How do I do this if I only want the data and 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; Show table staffer structure DESC staffer;8, create a test environment 1) Create a database staffermysql> creating databases Staffer2) Creating Tables Staffer,department,position,depart_poscreate table s_position (id int NOT NULL auto_increment,name varchar (a) not Null default ' manager ', #设定默认值description varchar, PRIMARY key Pk_positon (ID) #设定主键); CREATE TABLE department (ID int not nu ll auto_increment,name varchar (+) NOT NULL default ' system department ', #设定默认值description varchar, PRIMARY key pk_department (ID) # Set primary key); CreaTe table Depart_pos (department_id int not null,position_id int not null,primary key Pk_depart_pos (department_id,position _id) #设定复和主键); CREATE TABLE staffer (ID int not NULL auto_increment primary key, #设定主键name varchar (a) NOT null default ' anonymous ', #设定默认值department_id int not null,position_id int not null,unique (department_id,position_id) #设定唯一值); 3) Delete mysql> drop table Depart_pos;drop table Department;drop table s_position;drop table Staffer;drop Database staffer;9, modify structure mysql& gt; #表position增加列testalter table position Add (test char); #表position修改列testalter table position Modify test char (20) Not null; #表position修改列test默认值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 Depar T_pos drop primary key; #表depart_pos增加主键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 ', up); 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 # Display the personnel and positions of the system department select A.NAme,b.name Department_name,c.name Position_name

 

From staffer A,department b,s_position Cwhere a.department_id=b.id and A.position_id=c.id and B.name= ' System department '; Show number of Systems 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 Department '; #将财务部修改为财务一部update department set name= ' finance a ' where name= ' finance department '; 12, backup and restore 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, Need to create an empty library 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 The new data is imported into the datasheet before you delete all the information in the data table-F or--force regardless of whether you encounter an error, MYsqlimport will force continued insertion of data-I or--ignore mysqlimport skips or ignores rows with 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. This tool has a problem, can not ignore some columns, so that our data import has a 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 a 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, Don't know if it's not Windows cause 2) Load Data INFILE file_name into table_name (column1_name,column2_name) This command is used at the mysql> prompt, The advantage is that you can specify a column import, such as the following c:\mysql\bin\mysql-uroot-proot staffermysql>load data infile "E:/depart_no.txt" into Depart_no ( department_id, position_id);  the two tools used under Windows have problems, do not know whether it is windows or Chinese, and do not specify the column it produced a null value, which is obviously not what we want, so use these tools carefully into 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 > CREATE DATABASE mysqldata;3: Select the databases 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 (e.g. D:/mysql.sql) mysql>use Database;mysql>source d:/mysql.sql;10: Remove table Mysql>drop table mytable;11: Clear table Mysql>delete from MYTABLE;12: Update table Data 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 update it. 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; Access: 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&gt ; 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 a 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;　　ALTER TABLE 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 functional transactions, views, foreign keys and referential integrity, stored procedures and triggers note: With regard to transactions, MySQL uses different storage engines and has different support for transactional functions, such as InnoDB and BDB support transactions and MyISAM, memory, etc. do not support transactions. 14, MySQL will use the index of the operation symbol <,<=,>=,>,=,between,in, without% or _ the beginning of like 15, the disadvantage of using the index 1) to slow down the deletion and 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, the index is taken into account, too many indexes will increase the workload for the query optimizer, resulting in the inability to select the optimal query scheme; 16. Index Efficiency method: Add explain to the general SQL statement; Meaning of the analysis result: 1) t Able: Table name; 2) Type: The 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. Using NOT null and enum as far as possible to define the column as NOT NULL, so that the data out faster, less space required, and in the query, MySQL does not need to check whether there is a special case, that is, null value, so as to optimize the query, if a column contains only a limited number of specific values, such as gender, is valid or the year of entry, etc., in this case should consider converting it to the value of the enum column, MySQL processing faster, because all the enum values in the system are represented by the identification value, 19, using optimize table for frequently modified tables, easy to produce fragments, Make it necessary to read more disk blocks when querying the database, reducing 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 using MYsqldump to dump the data table, then use the dumped 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) to increase the speed of the hard disk to reduce I/O wait time, 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; The busiest databases should be stored on different physical devices, which are different from different partitions that use the same physical device because they will compete for the same physical resources (heads).

Reprinted from: https://www.cnblogs.com/123456789qq/p/5891289.html slightly changed

MySQL Common commands