Mysql-related applications

Mysql-related applications
Commonly used MySQL commands are full. Connection MySQL format: mysql-h host address-u username-p User Password 1. Example 1: connect to MYSQL on the local machine. First, open the DOS window, enter the directory mysqlbin, then type the command mysql-uroot-p, and press enter to prompt you to enter the password. If you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql>. 2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is abcd123. Run the following command: mysql-h110.110.110.110-uroot-pabcd123 (Note: you do not need to add spaces to the u and root nodes.) 3. exit MYSQL: exit (Press ENTER ). Ii. Change the password format: mysqladmin-u username-p old password new password 1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command: mysqladmin-uroot-password ab12 Note: because the root has no password at the beginning, the old-p password can be omitted. 2. Example 2: Change the root password to djg345. Mysqladmin-uroot-pab12 password djg345 3. Add new users. (Note: Unlike the above, the following commands are in the MySQL environment, so a semicolon is followed as the command Terminator) Format: grant select on database. * to username @ login host identified by \ "password \" Example 1. Add a user test1 whose password is abc so that he can log on to any host, all databases are permitted to query, insert, modify, and delete databases. First, use the root user to connect to MySQL, and then type the following command: grant select, insert, update, delete on *. * to test2 @ localhost identified by \ "abc \"; if you do not want test2 to have a password, run another command to remove the password. Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by \ "\"; the logon, adding users, and changing passwords are discussed above. Next, let's take a look at the database operations in MySQL. Note: you must first log on to MySQL. The following operations are performed at the MySQL prompt 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 database directly without prompting show tables; display table describe tablename; in the detailed description of the table, add distinct in the select statement to remove the duplicate field mysqladmin drop database name. A prompt is displayed before deleting the database. Display the current mysql version and current date select version (), current_date; 2. Modify the root password 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 the database: mysql> show databases; display all databases mysql> show tables; display all tables in the database mysql: use mysql first; then mysql> describe user; displays the column information of the user table in the mysql database. 3. grant to create a full super user who can connect to the server from any place, but must use a password something for this mysql> gr Ant all privileges on *. * to user @ localhost identified by 'something' with adds the new user format: grant select on database. * to username @ login host identified by "password" grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option; grant all privileges on *. * TO monty @ "%" identified by 'something 'with grant option; Delete authorization: mysql> revoke all privileges on *. * from root @ "%"; mysql> delete from user where user = "Root" and host = "%"; mysql> flush privileges; create a User custom to log on to the specific client it363.com, and access the specific database fangchandbmysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ it363.com identified by 'passwd' rename the table: mysql> alter table t1 rename t2; 4. mysqldump backup database shell> mysqldump-h host-u root-p dbname> dbname_backup. SQL Restore database shell> mysqladmin-h myhost-u root-p create dbnameshell> mysqldump-h Host-u root-p dbname <dbname_backup. SQL if you only want to unload the table creation command, the command is as follows: shell> mysqladmin-u root-p-d databasename>. if you only want to unload the SQL command for inserting data without creating a table, the command is as follows: shell> mysqladmin-u root-p-t databasename>. if I only want data, but do not want any SQL commands, what should I do? Mysqldump-T. in/phptest driver, only the-T parameter can be specified to unload plain text files, indicating the directory for unloading data ,. /indicates the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands. 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. You can use the shell to type the redirection utility to do this. For example, if a query is stored in the my_file. SQL file, you can execute the following queries: for example, if you want to write the table creation sentence in SQL .txt: mysql> mysql-h myhost-u root-p database <SQL .txt 1. installation environment: for Windows XP mysql 4.0.17, you need to use mysql-uroot-proot to log on remotely or locally. You can use mysql-h 172.5.1.183-uroot to log on, this is based on the policy of the second row to determine the permission change effective: 1) net stop mysqlnet start mysql2) c: \ mysql \ bin \ mysqladmin flush-privileges3) after logging on to mysql, use the flush privileges Statement 6. Create the database staffercreate database staffer. 7. The following statement displays the user in the mysql environment. The show databases database with permissions is switched to the staffer database use staffer. The show tables Table with permissions in the current database is displayed. the desc staffer structure of the staffer table is displayed. 8. Create a test environment. 1) create database staffermysql> create database staffer2) create tables staffer, department, position, depart_poscreate table s_position (id int not null auto_increment, name varchar (20) not null default 'manager ', # Set the default value description varchar (100), primary key PK_positon (id) # Set the primary key); create table department (id int not null Uto_increment, name varchar (20) not null default 'System amount', # Set the default value description varchar (100), primary key PK_department (id) # Set the 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) # Set compound and primary keys); create table staffer (id int not null auto_increment primary key, # Set the primary key name varchar (20) not null default 'Anonymous ', # Set the default value department_id int no T null, position_id int not null, unique (department_id, position_id) # sets the unique value); 3) Delete mysql> drop table depart_pos; drop table department; drop table s_position; drop table staffer; drop database staffer; 9. modify the structure of mysql> # add testalter table position add (test char (10) to table position )); # modify the table position column testalter table position modify test char (20) not null; # modify the table position column test default value alter table position alter test set default 'System '; # Alter table position alter test drop default; # Remove testalter table position drop column test from table position; # Delete table depart_pos Delete primary key 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 part ', 'System amount'); insert into department (name, description) val Ues ('Public relations ', 'public relations'); insert into department (name, description) values ('Customer Service department ', 'Customer Service department'); insert into department (name, description) values ('finance department ', 'Finance department'); insert into department (name, description) values ('test part', 'test part '); # insert TABLE s_positioninsert into s_position (name, description) values ('authorization', 'authorization'); insert into s_position (name, description) values ('manager ', 'manager'); insert into s_position (name, description) values ('ordinary employees', 'ordinary employees'); # insert Table d Epart_posinsert into depart_pos (department_id, position_id) select. id department_id, B. id postion_idfrom department a, s_position B; # insert the table stafferinsert into staffer (name, department_id, position_id) values ('chen dazhi ', 1, 1); insert into staffer (name, department_id, position_id) values ('livembin', 1, 2); insert into staffer (name, department_id, position_id) values ('majia', 1, 3); insert into staffer (name, department_id, position_id) Values ('Kang Zhiqiang ',); insert into staffer (name, department_id, position_id) values ('yang Yuru ); 11. query and delete operations # select. name, B. name department_name, c. name position_namefrom staffer a, department B, s_position cwhere. department_id = B. id and. position_id = c. id and B. name = 'System amount'; # displays the number of people in the system. select count (*) from staffer a, department bwhere. department_id = B. id and B. name = 'System amount' # show the number of people in each department select count (*) cou, B. namefrom staffer a, department bwhere. department_id = B. idgroup by B. name; # delete the customer service department delete from department where name = 'Customer Service department '; # change the finance department to the finance department update department set name = 'finance department 'where name = 'finance department '; 12. Back up and recover the backup database stafferc: \ mysql \ bin \ mysqldump-uroot-proot staffer> e: \ staffer. staffer obtained by SQL. SQL is an SQL script that does not contain database creation statements. Therefore, you need to manually create a database to import and restore the database staffer. You need to create an empty database stafferc: \ mysql \ bin \ mysql-uroot-proot staffer <staffer. if you do not want SQL Create a staffer. You can use c: \ mysql \ bin \ mysqldump-uroot-proot -- databases staffer> e: \ staffer. sqlmysql-uroot-proot> e: \ staffer. but in this case, the system cannot have the staffer library and cannot import databases with other names. Of course, you can manually modify staffer. SQL file 13. import data from text to database 1) the tool c: \ mysql \ bin \ mysqlimportis used to import files to tables with the same name as the extension name of the drop file, such as staffer.txt, staffer is used to import data to staffer tables. Common options and functions are as follows:-d or -- delete all information in the data table before the new data is imported into the data table-f or -- force no matter whether an error occurs, mysqlimport will force the insertion of data-I or -- ignore mysqlimpo Rt skips or ignores rows with the same unique keyword. Data in the imported file is ignored. -L or-lock-tables: The table is locked before data is inserted. This prevents you from affecting your queries and updates when updating the database. -R or-replace is opposite to-I. This option replaces records with the same unique keywords in the table. -- Fields-enclosed-by = char specifies the data record in a text file. In many cases, the data is enclosed by double quotation marks. By default, data is not enclosed by characters. -- Fields-terminated-by = char specifies the delimiter between values of each data. In a file separated by periods, the Delimiter is a period. You can use this option to specify the delimiter between data. The default Delimiter is the Tab character -- lines-terminated-by = str. This option specifies the delimiter string or character used to separate the data between rows and rows in a text file. By default, mysqlimport uses newline as the line separator. You can choose to use a string to replace a single character: a new line or a carriage return. Common options of the mysqlimport command include-v display version and-p prompt for password. Saved, saved on the e disk. The interval is tab \ t10 1011 24. Run the following command c: \ mysql \ bin \ mysqlimport-uroot-proot staffer e: \ depart_pos.txt does not use the column surround symbol here. The default \ t is used for column separation. It may be caused by other symbols. I don't know if it is the reason for windows. 2) the Load Data INFILE file_name into table_name (column1_name, column2_name) command is used at the mysql> prompt. The advantage is that you can specify the column import. example c: \ mysql \ bin \ mysql-uroot-proot staffermysql> load data Infile "e:/depart_no.txt" into depart_no (department_id, position_id, in addition, unspecified columns produce null values, which is obviously not what we want. Therefore, use these tools with caution to access MySQL: mysql-uuser-ppassword -- port = 33071: use the SHOW statement to find out the current DATABASE on the server: mysql> show databases;. Create a DATABASE MYSQLDATAmysql> Create database mysqldata; 3: select the mysql> use mysqldata Database you created. (If you press the Enter key to change the Database, the operation is successful !) 4: Check what TABLES exist in the current database mysql> show tables; 5: Create a database TABLE mysql> Create table mytable (name VARCHAR (20), sex CHAR (1 )); 6: display the table structure: mysql> describe mytable; 7: add the record mysql> insert into MYTABLE values ("hyq", "M") to the table; 8: load data into a database TABLE (for example, D:/mysql.txt) using text. mysql> load data local infile "D:/mysql.txt" into table mytable; 9: import. SQL file commands (such as D:/mysql. SQL) mysql> use database; mysql> source d:/mysql. SQL; 10: Delete TABLE mysql> drop TABLE MYTABLE; 11: Clear TABLE m Ysql> 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 the columns of the row in the existing table with a new value. The SET clause specifies the column to be modified and the value they should be given. If the WHERE clause is given, specify the record row to be updated. Otherwise, all record rows 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, the 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, even if a duplicate key error occurs during the update process. The conflicting records 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 add 1 to its current value: mysql> UPDATE persondata SET age = age + 1; the UPDATE value is calculated from left to right. For example, the following statement sets the age column to twice, 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 actually changed record rows. In MySQL 3.22 or an updated version, the c api function mysql_info () returns the number of matched and updated record rows and the number of warnings during UPDATE. In MySQL 3.23, you can use LIMIT # to ensure that only the specified number of record rows is changed. If an order by clause is used (supported since MySQL 4.0.0), the record row is updated in the specified ORDER. This is actually useful only with LIMIT. From MySQL 4.0.4, you can also execute an UPDATE operation that contains multiple tables: UPDATE items, month SET items. price = month. priceWHERE items. id = month. id. Note: order by or LIMIT cannot be used for multi-Table UPDATE. Keyword: mysql start: net start mySql; enter: mysql-u root-p/mysql-h localhost-u root-p databaseName; List database: show databases; select database: use databaseName; List tables: show tables; show table column attributes: show columns from tableName; Create Database: source fileName.txt; matching character: wildcard _ can be used to represent any character, % represents 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 date Type; multi-line command input: note that words cannot be broken; when data is inserted or changed, the field string cannot be expanded into multiple rows; otherwise, the hard press enter will be stored in the data; add an Administrator Account: grant all on *. * to user @ localhost identified by "password"; after each statement is entered, enter the plus sign ';' At the end, or add '\ G'. query time: select now (); query the current user: select user (); query the database version: select version (); query the currently used database: select database (); 1. Delete the students data table in the student_course Database: rm-f student_course/students. * 2. Back up the database: (back up the database test) mysqldump-u root-p test> c: \ test.txt backup table: (Backup Mytable table in the test Database) mysqldump-u root-p test mytable> c: \ test.txt import the backup data to the database: (export back to the 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, first determine whether the table has the create table if not exists students (......); 5. Copy the table structure from an existing table: create table table2 select * from table1 where 1 <> 1; 6. Copy the table create table table2 select * from table1; 7. rename alter table table1 rename as table2; 8. modify the column type alter table table1 modify id int unsigned; // modify the column id type to int unsigned alter table table1 change id sid int unsigned; // modify the column id name to sid, modify the attribute to int unsigned 9 and 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 index idx_id on table1; alter table table1 drop index ind_id; 11. Union character or multiple columns (concatenating the column id with ":" And column name and "=") select concat (id, ':', name, '= ') from students; 12. limit (10 to 20 records selected) <the number of the first record set is 0> select * from students order by id limit 9, 10; 13. MySQL does not support function transactions, views, foreign keys, and reference integrity. Stored Procedures and triggers 14. MySQL uses the index operator symbol <, <=, >=,>, =, between, in, like 15 without the start of % or _. disadvantages of indexing 1) Slow down the speed of adding, deleting, and modifying data; 2) occupying disk space; 3) increasing the burden on the query optimizer; when the query optimizer generates an execution plan, indexes are considered, too many indexes will increase the workload of the query optimizer, resulting in the inability to select the optimal query solution. 16. Index Efficiency Analysis Method: add the explain statement before a general SQL statement. Meaning of the analysis result: 1) table: table Name; 2) type: Connection type (ALL/Range/Ref ). Among them, ref is the most ideal; 3) possible_keys: query the available index name; 4) key: actually used index; 5) key_len: length of the part used in the index (in bytes); 6) ref: displays the column name or "const" (What do you mean?); 7) rows: display the number of rows that MySQL considers to be scanned before finding the correct results; 8) extra: MySQL advice; 17. Use a short fixed-length column; 1) use a shorter data type as much as possible; 2) use the fixed-length data type as much as possible. a) replace varchar with char, and process data with a fixed length faster than that with a longer length. B) for frequently modified tables, disks are prone to fragmentation, this affects the overall performance of the database. c) in case of a data table crash, it is easier to reconstruct a table with a fixed length of data rows. When a data row of a fixed length is used, the start position of each record is a multiple of the fixed record length, which can be easily detected, but the use of a variable length data row is not necessarily; d) for MyISAM data tables, although converting to a fixed-length data column can improve performance, it occupies a large space. 18. Use not null and enum to define the column as not null, in this way, data can be generated faster and less space is required. During query, MySQL does not need to check whether there is a special case, that is, the null value, to optimize the query; if a column contains only a limited number of specific values, such as gender, validity, or enrollment year, you should consider converting it to the value of the enum column, mySQL processes faster, because all enum values are expressed in the system as identifiers. 19. Using optimize table, fragments are easily generated for tables that are frequently modified, this allows you to read more disk blocks when querying the database to reduce query performance. All tables with variable lengths have disk fragmentation issues. This problem is more prominent FOR blob data types because of their large size changes. You can use optimize table to organize fragments to ensure that the database performance is not degraded and optimize the data tables affected by fragments. Optimize table can be used for MyISAM and BDB data tables. In fact, any fragment method uses mysqldump to store data tables, and then creates a new data table using the converted files. 20. procedure analyze () can be used to perform procedure analyze () it is recommended to display the optimal type. It is easy to use. You can add procedure analyze () after the select statement. For example: select * from students procedure analyze (); select * from students procedure analyze (16,256); the second statement requires procedure analyze () not to be recommended to contain more than 16 values, or enum type containing more than 256 bytes. If there is no limit, the output may be long. 21. query cache is used. 1) query cache method: When a select statement is executed for the first time, the server remembers the text content and query results of the query, stored in the cache. The next time you encounter this statement Results are returned from the cache. After a data table is updated, any cached query of the data table becomes invalid and is discarded. 2) configure the cache parameter: variable: query_cache _ type, and query the cache operation mode. There are 3 moderate modes, 0: no cache; 1: cache query, unless it starts with select SQL _no_cache; 2: only queries starting with select SQL _cache are cached as needed; query_cache_size: set the maximum result set size of the query cache, which is greater than this value and will not be cached. 22. Adjust hardware 1) add more memory to the machine; 2) increase the speed of the hard disk to reduce the I/O wait time; Seek time is the main factor that determines the performance, the speed of moving the head is the slowest. Once the head is located, it is fast to read from the track; 3) disk activity is reassigned on different physical hard disk devices; if possible, the busiest data inventory should be placed on different physical devices, which is different from using different partitions of the same physical device because they will compete for the same physical resources (head ).