MySQL frequently uses command lists

MySQL frequently uses command lists 1. System Management Mysql-h Host Address-uusername-p Connect MySQL (in mysql/bin) Exit Quit MySQL Command mysqladmin-uusername-p Old password Password new password Change password (in Mysql/bin) Grantselect[insert][,update][,delete]on database. *[email protected] ("%", which means no matter what the host) identifiedby "password" Add user Mysqldump–u root–p opt database name > backup file name Backing up the database (in Mysql/bin) Mysql–u root–p < batch file (e.g. backup file name) Using batch Processing Mysql.server start Start the server Mysql.server stop Stopping the server Msql.server Log 2. Query command Select Version () Query version Select Current_date Query the current date 3. Show commands Show databases Show Database list Show tables display the data table in the library Describe table name shows the structure of the data table SELECT * from table name shows records in table Select What_to_select from which table [Whereconditions_to_satisfy and (or) where CONDITIONS_TO_SATISFY] Retrieve data from a table "Meet condition" Select field 1, Field 2,... the From table name displays the value of a specific column SELECT * FROM table name order By field name sort rows Select field 1, which includes field 2 's expression as new field from table name segment value operation operation Select field 1 is null (was NOT NULL) NULL operation Select*from table name where field name like (not) "character" Note: Agree to use "_" to match no matter what single character, while "%" matches the random number of character pattern matches SELECT * from table name where field name RegExp (not regexp) or rlike (not rlike) "." Match no matter what the single character of a character class [...] Matches whatever characters are in the box. For example [A],[asd],[az] matches no matter what lowercase letters, [09] matches no matter what number Word. "*" matches 0 or more things in front of it. The normal form distinguishes between uppercase and lowercase [AA]. Let's say the pattern matches whatever place it is now measured. Positioning, using "^" at the beginning of the pattern and "$" at the end, such as "^b" Extended regular Form Select Count (*) from table name Select field Name, COUNT (*) from table name Group By field name row count 4. Edit command Use database Database name The database used Create DATABASE library Name Create a database CREATE TABLE Table name Create a table in the database Insert into table name values ("Data", "data") Add a record to a table Load data infile "/path/filename" intotable table name Data is added to the table from the file, each row contains a record, and the value is separated by a locator (tab). Drop DATABASE Database name Deleting a database DROP table Name To delete a table in a database Delete from table name where Delete records in a database table Update table name Set field = "Value" wherewhereconditions_to_satisfy Updating the values of records in a database table MySQL database is a multi-user, multi-threaded relational database, is a client/server structure of the application. It is free for both individual users and business users. The MySQL database has the following advantages: 1. Unlimited number of users visiting the database at the same time 2. Ability to save more than 50 million records 3. The fastest performing database system in existing database products on the market today 4. User rights setting is simple and effective. MySQL database frequently uses commands: Start the MySQL database C:/>CD Mysql5.0/bin C:/mysql5.0/bin>mysqld–install Installing the MySQL service C:/mysql5.0/bin>net start MySQL startup MySQL service The requested service has been started. Connect to MySQL Users need to provide MySQL username and password to connect to the server, assuming that the server is not native, then a host name or IP is required to specify the location of the server. C:/mysql5.0/bin>mysql-h Localhost-u Root-p Enter Password: * * * Welcome to the MySQL Monitor. Commands End With; Or/g. Your MySQL Connection ID is 6 to server version:5.0.18-nt Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer. Mysql> Use a simple query statement Mysql> Select Version (), current_date; Mysql> select version (); select Now (); Create or delete a database Mysql>create database mydb; mysql> drop Database mydb; command to open a database mysql> use MySQL Database changed Commands to view the database mysql> show databases; View the specific structure of a data table Mysql> desc func; New Database Mysql> CREATE DATABASE School; Query OK, 1 row Affected (0.00 sec) New Table Mysql> CREATE TABLE User01 ( ID varchar () not NULL, -UserName varchar (+) not NULL, -Age int (one) default ' 0 ', , Sex char (2) Not NULL default ' m ', -PRIMARY KEY (ID) ) Type=innodb; Query OK, 0 rows affected, 1 Warning (0.02 sec) Mysql>desc student; Inserting and deleting data from a table Create table Student (stuname varchar (), age varchar (1), id varchar (+), Set0 char ()); Insert mysql> INSERT into student (Id,stuname) VALUES (' 1 ', ' Tomcat '); Query OK, 1 row Affected (0.00 sec) Delete mysql> Delete from student where id= ' 1 '; Query OK, 1 row affected (0.01 sec) Delete all data in a table mysql> TRUNCATE TABLE student; Query OK, 1 row affected (0.01 sec) Delete a table Mysql> CREATE TABLE Temp (t varchar (1)); Query OK, 0 rows Affected (0.00 sec) mysql> drop Table temp; Query OK, 0 rows Affected (0.00 sec) Create a new user and give permission Mysql> Grant all privileges on * * to [e-mail protected] identified by ' 1234 ' with GRANT option; Change MySQL user password C:/mysql5.0/bin>mysqladmin-u root-p Password 1234 Enter Password: * * * Backing up databases and tables We use the mysqldump command to back up the database. C:/mysql/bin/>mysqldump–u root–p 3306 Mysql>d:/backup.sql Running this statement will back up the mydb to the D-disk Backup.sql file. Backing up multiple database tables C:/mysql/bin/>mysqldump–u root–p 3306 School User01 user >d:/backup.sql The meaning of this sentence is to back up the contents of the User01 table and the user table in the school library to the D disk Backup.sql file. Back up all of your databases C:/myql/bin>mysqldump–u root–p 3306–all–database>d:backup.sql Restore MySQL Database C:/mysql/bin/mysql–u Root–p 3306 School Restore one of the tables Mysql> source D:/books.sql; ERROR: Unknown command '/b '. Query OK, 0 rows Affected (0.00 sec) Query OK, 1 row Affected (0.00 sec) Exit MySQL Connection Mysql>quit (Exit) Turn off MySQL service C:/mysql/bin>net MySQL 1. Start MySQLServer In fact, the previous article has talked about how to start MySQL. Two methods: One is to use the winmysqladmin, assuming that the machine started on its own initiative, you can go directly to the next step of operation. The second is to execute D:MYSQLBINMYSQLD in DOS mode 2. Enter MySQL Interactive interface In DOS mode, execute: d:mysqlbinmysql Appears: MySQL prompt, this time has entered the MySQL interactive operation mode. Assume that "error 2003:can′t connect to MySQL server on′localhost′ (10061)" appears, Indicates that your MySQL has not started yet. 3. Exit the MySQL operator interface Enter quit at the mysql> prompt to exit the interactive interface at any time: Mysql> quit Bye You can also use control-d to quit. 4, the first command Mysql> Select Version (), current_date (); +----------------+-----------------+ | Version () | Current_date () | +----------------+-----------------+ | 3.23.25a-debug | 2001-05-17 | +----------------+-----------------+ 1 row in Set (0.01 sec) Mysql> This command requires MySQLServer to tell you its version and current date. Try using different uppercase and lowercase commands to see what the result is. The results show that the uppercase and lowercase results of the MySQL command are consistent. Exercises such as the following actions: Mysql>select (20+5); Mysql>select (20+5) *4,sin (Pi ()/3); Mysql>select (20+5) as Result,sin (Pi ()/3); (as: Specify Kana as result) 5, multi-line statements A command can be divided into multiple lines of input until a semicolon ";" is present: Bordercolorlight = "BLACK" Bordercolordark = "#FFFFFF" align= "Center" > Mysql> Select -USER () , Now () ->; +--------------------+---------------------+ | USER () | Now () | +--------------------+---------------------+ | [email protected] | 2001-05-17 22:59:15 | +--------------------+---------------------+ 1 row in Set (0.06 sec) Mysql> Notice the middle of the comma and the use of the last semicolon. 6, one line multi-command Enter for example the following command: Mysql> SELECT USER (); SELECT now (); +------------------+ | USER () | +------------------+ | [email protected] | +------------------+ 1 row in Set (0.00 sec) +---------------------+ | Now () | +---------------------+ | 2001-05-17 23:06:15 | +---------------------+ 1 row in Set (0.00 sec) Mysql> Notice the middle semicolon, separating the commands with semicolons. 7. Display the database that currently exists mysql> show databases; +----------+ | Database | +----------+ | MySQL | | Test | +----------+ 2 row in Set (0.06 sec) Mysql> 8. Select the database and display the currently selected database mysql> use MySQL Database changed Mysql> (Use and QUIT commands do not require a semicolon to end.) ） Mysql> Select Database (); +---------------+ | Database () | +---------------+ | MySQL | +---------------+ 1 row in Set (0.00 sec) 9. Displays the tables that exist in the current database Mysql> SHOW TABLES; 10, display the contents of the table (db) Mysql>select * from DB; 11. Cancellation of the Order When a command is entered incorrectly and cannot be changed (multi-line statement scenario), you can use C to cancel the command only before the semicolon appears Mysql> Select -User () C Mysql 1. MySQL often uses commands Create database name; Create a database Use DatabaseName; Select Database Drop database name deletes databases directly and does not alert Show tables; Show Table Describe TableName; A detailed description of the table Add distinct in Select to remove repeating fields Mysqladmin drop DatabaseName You are prompted before you delete the database. Displays the current MySQL version number and the current date Select version (), current_date; 2. Change the root password in MySQL: Shell>mysql-u root-p mysql> Update user Set Password=password ("Xueok654123″) where user= ' root '; Mysql> flush Privileges//Refresh Database Mysql>use dbname; Open the database: Mysql>show databases; Show all databases Mysql>show tables; Displays all tables in the database MySQL: use MySQL first; Mysql>describe user; Displays column information for the user table in the MySQL database); 3. Grant Create a fully superuser who can connect to the server from anywhere, but must use a password something do this mysql> Grant all privileges on * * to [e-mail protected] identified by ' something ' with Add New User Format: Grant Select on database. * to [email protected] Login host identified by "password" GRANT All privileges on * * to [email protected] identified by ' something ' with GRANT OPTION; GRANT all privileges on * * to [e-mail 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 login in a specific clientit363.com to access a specific database fangchandb mysql >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [email protected] it363.com identified By ' passwd ' To rename a table: mysql > ALTER table t1 rename T2;4, Mysqldump Backing Up the database shell> mysqldump-h host-u root-p dbname >dbname_backup.sql Recovering a Database shell> mysqladmin-h myhost-u root-p Create dbname shell> mysqldump-h host-u root-p dbname < Dbname_backup.sql Assuming that you just want to unload the build table, the command is as follows: shell> mysqladmin-u root-p-D databasename > A.sql Assuming that you just want to unload the SQL command that inserts the data without having to build a table command, the command is like this: shell> mysqladmin-u root-p-t databasename > A.sql So what should I do if I just want the data and I don't want any SQL commands? mysqldump-t./phptest Driver Among them, only the-t parameter is specified to unload the plain text file, which represents the folder where the data is unloaded./Represents the current folder, that is, the same folder as Mysqldump. Assuming 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, including the build table run. There is also a. txt file that includes 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. You can use the shell to type a redirect utility to complete the work. For example, suppose a file is stored in the My_file.sql For example, you can run these queries as follows: For example, suppose you want to write the statement in advance in Sql.txt: mysql > mysql-h myhost-u root-p Database < Sql.txt

Transferred from: http://hi.baidu.com/ashinehan/blog/item/0ed8b2173cb94657f2de326b.html

MySQL frequently uses command lists