PHP MySQL database common cmd command set
show databases; Display Database
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; Show the specific table structure
Add distinct in Select to remove duplicate fields
Mysqladmin drop DatabaseName You are prompted before you delete the database.
Show current MySQL version and current date
Select version (), current_date;
To modify the root password in MySQL:
Shell>mysql-h localhost-u root-p//Login
or mysql-uroot-p into MySQL.
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);
Grant
Create user firstdb (password firstdb) and database, and assign permissions to FIRSTDB database
mysql> CREATE DATABASE Firstdb;
Mysql> Grant all on firstdb.* to Firstdb identified by ' Firstdb '
User Firstdb is created automatically
MySQL default is localhost, the corresponding IP address is 127.0.0.1, so you log in with your IP address will be wrong, if you want to use your IP address to log in the first authorization with the grant command.
Mysql>grant all on * * to identified by "123456";
Description: Grant and on are various permissions, for example: Insert,select,update, etc.
On after is the database name and table name, the first * represents all databases, the second * represents all tables
Root can be changed to your user name, after the @ can be followed by the domain name or IP address, identified by behind the login password, can be omitted, that is, the default password or empty password.
Drop database Firstdb;
Create a full superuser who can connect to the server from anywhere, but must use a password something do this
Mysql> Grant all privileges on * * to identified by ' something ' with
Add new users
Format: Grant Select on database. * To User name @ login host identified by "password"
GRANT all privileges on *. * to 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 login on a specific client it363.com to access a specific database fangchandb
MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd
To rename a table:
mysql > ALTER table t1 rename T2;
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
If you only want to unload the build instruction, 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 you do not need to create a table command, the command is as follows:
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
Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as 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.
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 a file is stored in the My_file.sql
These queries can be executed as follows:
For example, if you want to write the statement in advance in Sql.txt,
MySQL > Mysql-h myhost-u root-p
Mysql5.0 Supported character sets
The character set control in MySQL is relatively thin and can be divided into database-level, table-level, field-level (this is different from Oracle). I changed the character set is a database-level, no effect on the table sysuser, so there is a change to the character set but the same cannot be inserted in Chinese.
Drop TABLE IF EXISTS ' firstdb '. ' Users ';
Create TABLE ' firstdb '. ' Users ' (
' id ' int (one) not NULL auto_increment,
' username ' varchar (+) default NULL,
' Birthday ' date default NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT charset=gb2312;
When compiling MySQL, a default character set is specified, and the character set is latin1;
When you install MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, this value is inherited from the compile-time specified;
When you start mysqld, you can specify a default character set in the command-line arguments, and if not specified, the value inherits from the configuration file;
At this point the character_set_server is set to the default character set;
When a new database is created, the character set of the database is set to Character_set_server by default unless explicitly specified;
When a database is selected, Character_set_database is set to the default character set for this database;
When creating a table in this database, the default character set of the table is set to Character_set_database, which is the default character set of the database;
When a column is set within a table, the default character set of the column is the table's character set, unless explicitly specified;
The character set is the character set used in the database to actually store the data, and the content of Mysqldump is the character set; Query Browser1.1 support for Chinese input is too bad, can be written in notebook, and then copy the past execution
Update firstdb.users set username= ' to ' where id=3;
MYSQL Common Commands
1. Export the entire database
Mysqldump-u user name-p--default-character-set=latin1 database name > exported
File name (database default encoding is latin1)
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql
3. Export a database structure
Mysqldump-u wcnc-p-d-add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql
-D No data-add-drop-table add a drop table before each CREATE statement
4. Import the database
A: Common Source command
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source Wcnc_db.sql
B: Using the mysqldump command
Mysqldump-u Username-p dbname < Filename.sql
C: Using the MySQL command
Mysql-u Username-p-D dbname < Filename.sql
First, start and exit
1. Enter MySQL: start MySQL Command line Client (MySQL dos interface), enter directly
The password at the time of installation. At this point the prompt is:mysql>
2. Exit Mysql:quit or exit
Second, the library operation
1. Create a database
Command: Create databases < database name >
For example, create a database named XHKDB
mysql> CREATE DATABASE xhkdb;
2. Show all databases
Command: Show databases (note: There is a last s)
mysql> show databases;
3. Delete Database
Command: Drop databases < database name >
Example: Delete a database named Xhkdb
mysql> drop Database xhkdb;
4. Connect to the database
Command: Use < database name >
For example: If the XHKDB database exists, try to access it:
mysql> use XHKDB;
Screen tip: Database changed
5. View the database currently in use
Mysql> Select Database ();
6. The current database contains table information:
Mysql> Show tables; (Note: There is a last s)
Third, table operation, should connect a database before operation
1. Build a table
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));
2. Get the table structure
Command: DESC table name, or Show columns from table name
Mysql>describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;
3. Delete a table
Command: DROP table < table name >
Example: Deleting a table with a table named MyClass
mysql> drop table MyClass;
4. Inserting data
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [,
(value N)]
For example, insert two records into the table MyClass, and these two records indicate that the result of a number 1 named Tom
Is 96.45, and the score of 2 named Joan is 82.99, and the result of the named Wang with the number 3 is
96.5.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99),
(2, ' Wang ', 96.59);
5. Querying the data in the table
1), Query all rows
Command: Select < Field 1, field 2,...> from < table name > where < expression >
Example: View all data in table MyClass
Mysql> select * from MyClass;
2), query the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Or:
Mysql> select * from MyClass limit 0, 2;
6. Delete data from the table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table MyClass
Mysql> Delete from MyClass where id=1;
7. Modify the data in the table: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;
7. Add a field to the table:
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '
8. Change the table name:
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass
Mysql> Rename table MyClass to Youclass;
Update Field Contents
Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
Add 4 spaces to the front of the article
Update article Set Content=concat (", content);
Field type
1.int[(M)] Type: normal size integer type
2.double[(m,d)] [Zerofill] Type: normal size (double precision) floating-point number type
3.DATE Date Type: The supported range is 1000-01-01 to 9999-12-31. MySQL with YYYY-MM-DD
Format to display the date value, but allows you to assign a value to the date column using a string or a number
4.CHAR (M) Type: Fixed length string type, when stored, always fills the right with a space to the specified length
Degree
5.BLOB text type with a maximum length of 65535 (2^16-1) characters.
Model 6.VARCHAR: Variable length string type
5. Importing database tables
(1) Create a. sql file
(2) First produce a library such as Auction.c:mysqlbin>mysqladmin-u root-p creat
Auction, you are prompted for a password and then created successfully.
(2) Import Auction.sql file
C:mysqlbin>mysql-u Root-p Auction < Auction.sql.
By doing this, you can create a database auction and one of the tables auction
。
6. Modify the Database
(1) Add a field to the MySQL table:
ALTER TABLE dbname add column userid int (one) NOT NULL primary key
Auto_increment;
In this way, a field userid, type int (11), is added to table dbname.
Authorization of the 7.mysql database
Mysql>grant Select,insert,delete,create,drop
On *. * (or test.*/user.*/.)
To User name @localhost
Identified by ' password ';
Such as: Create a new user account so that you can access the database, you need to do the following:
Mysql> Grant Usage
-On test.*
to;
Query OK, 0 rows affected (0.15 sec)
Since then, a new user has been created called: TestUser, the user can only connect from localhost to the number
You can connect to the test database. Next, we must specify TestUser this user can
What to do:
mysql> GRANT Select, INSERT, Delete,update
-On test.*
to;
Query OK, 0 rows Affected (0.00 sec)
This operation enables TestUser to perform select,insert and a table in each test database.
Delete and the update query operation. Now we end the operation and exit the MySQL client program:
Mysql> exit
bye9!
1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> Create DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed operation success!)
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> Create TABLE MYTABLE (name VARCHAR), sex CHAR (1));
6: Show the structure of the table:
Mysql> DESCRIBE MYTABLE;
7: Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");
8: Loading 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 TABLE MYTABLE;
11: Clear the table
Mysql>delete from MYTABLE;
12: Update data in table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';
Here are some of the management tips that you can inadvertently see on the web using MySQL,
Pick
From: Http://www1.xjtusky.com/article/htmldata/2004_12/3/57/article_1060_1.
Html
In Windows, MySQL exists as a service, and you should make sure that the service is started before you use it and does not start the available
net start MySQL command starts. While booting in Linux is available "/etc/rc.d/init.d/mysqld
Start command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security
Hidden dangers, for some important applications we should make the security as high as possible, where the anonymous account should be removed
, the root account setting password can be used as follows:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to restrict the logon terminal used by the user, you can update the host field for the user in the Users table.
You should restart the database service after you have made these changes, and you can log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is to log in
The name of the database.
In the development and actual application, the user should not only use the root user to connect the database, although using
The root user is very convenient to test, but will bring significant security risks to the system, but also not conducive to management technology
Improve. We give the most appropriate database permissions to the users used in an application. If a data is only
The inserted user should not be given permission to delete data. The user management of MySQL is implemented through the users table.
, there are two common ways to add new users, one is to insert the corresponding data row in the user table, and set the corresponding
, and the second is to create a user with some kind of permission through the grant command. The common usage of grant is as follows
：
Grant all on mydb.* to identified by "password";
Grant usage on *. Identified by "password";
Grant Select,insert,update on mydb.* to identified
By "Password";
Grant Update,delete on MyDB. TestTable to identified
By "Password";
To give this user the ability to manage his or her permissions on the object, you can add a with after grant
The GRANT option option. For users added with the Insert User table, the password field is applied password
function to update the encryption, in case the malicious person stole the password. For those who have not used the user should be given clear
, the user who has permission to pass through the bounds should reclaim the rights in time, and the Recycle permission can be updated by updating the user table corresponding fields or
To use the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
Insert: Adds a table record.
Select: Displays/searches the table's records.
Update: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Only allow login-nothing else is allowed.
---------------------
MySQL Common commands
There are a lot of friends who have installed MySQL but don't know how to use it. In this article we are going to connect from
MySQL, change the password, add users and so on to learn some common MySQL commands.
There are a lot of friends who have installed MySQL but don't know how to use it. In this article we will
Connect MySQL, change passwords, add users and more to learn some common commands for MySQL.
One, connect MySQL
Format: mysql-h host address-u user name-P user Password
1. Example 1: Connect to MySQL on this machine
First, open the DOS window, then go to directory Mysqlbin, and then type the command mysql-uroot-p
, enter after the prompt you lose password, if just installed MySQL, superuser root is no password, so straight
You can go to MySQL with a return, and the prompt for MySQL is:mysql>
2. Example 2: Connect to MySQL on a remote host
Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. The
Type the following command:
Mysql-h110.110.110.110-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter)
Second, change the password
Format: Mysqladmin-u username-P Old password password new password
1, Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type
Next 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
MySQL common commands (bottom)
First, the Operation skill
1, if you hit the command, the return after the discovery forgot to add a semicolon, you do not need to re-command, just hit
A semi-colon returns to the right. In other words, you can break a complete command into a few lines, and then use the points
The end sign is OK.
2. You can use the cursor up and down keys to recall the previous command. But one of the old MySQL versions I used before
Not supported. I'm using Mysql-3.23.27-beta-win now.
Second, show the command
1. Display the list of databases.
show databases;
Just started with two databases: MySQL and test. MySQL Library is important, it has a MySQL system inside it.
Information, we change the password and add the user, actually is to use this library to operate.
2. Display the data table in the library:
Use MySQL; Open the library, learned foxbase must not be unfamiliar.
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;
A city build and build tables and instances of inserting data
Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table
The following is the Insert field
Insert into teacher values (' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');
Note: In the Build table (1) Set the ID to a number field of length 3: Int (3) and let it each record automatically
Add one: auto_increment cannot be empty: NOT null and let him be the main field primary key
(2) Set name to a character field of length 10
(3) Set address to a character field of length 50, and the default value is Shenzhen. varchar and Char
What's the difference, just wait for the next article to say it again.
(4) Set year as the Date field.
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can put the above
The command is written to a text file that is assumed to be school.sql, and then copied to the C + + and in a DOS state
Enter the directory \mysql\bin, and then type the following command:
Mysql-uroot-p Password < C:\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged
, you can use it simply by removing the//comment).
Iv. transferring text data to the database
1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \ n.
Cases:
3 Rose Shenzhen II 1976-10-10
4 Mike Shenzhen one 1975-12-23
2, the data incoming command, load data local infile "file name" into table table name;
Note: You might want to copy the file to the \mysql\bin directory, and you must first use the using command to hit the table
The library.
V. BACKUP DATABASE: (command executed in DOS \mysql\bin directory)
Mysqldump--opt SCHOOL>SCHOOL.BBB
Note: Backing up the database school to a school.bbb file, school.bbb is a text file,
Take the filename, open it and see what you can find.
The complete syntax for the SELECT statement is:
select[all| Distinct| distinctrow| TOP]
{*|talbe.*| [table.] Field1[as alias1][,[table.] Field2[as alias2][,...]}
From tableexpression[,...] [In Externaldatabase]
[Where ...]
[GROUP by ...]
[Having ...]
[ORDER by ...]
[With Owneraccess OPTION]
Description
The part representation enclosed in brackets ([]) is optional, and the part enclosed in curly braces ({}) is a means to
Select one of them from.
1 FROM clause
The FROM clause specifies the source of the field in the SELECT statement. The FROM clause is followed by a table that contains one or more
Up (separated by commas), where the expression can be a single table name, a saved query, or a INNER-
Compound result from join, left JOIN, or right join. If a table or query is stored in an external data
Library, indicating its full path after the IN clause.
Example: The following SQL statement returns all customers with orders:
Select Orderid,customer.customerid
From Orders Customers
Where orders. Customerid=customers.customeersid
2 All, DISTINCT, Distinctrow, top predicates
(1) All returns all records that satisfy the conditions of the SQL statement. If this predicate is not specified, all is the default.
Example: Select all Firstname,lastname
From Employees
(2) DISTINCT if you have more than one record of the selected field, only the same data is returned.
(3) Distinctrow If there are duplicate records, return only one
(4) Top displays a number of records of the query. You can also return the percentage of records, which is to use TOP N
Percent clause (where n represents a percentage)
Example: Returns the order with the largest volume of 5% orders
Select TOP 5 percent*
from [Order Details]
OrDER by unitprice*quantity* (1-discount) DESC
3 Alias a field with an as clause
If you want to take a new title for the returned column, or after the calculation or summary of the field, a
A new value, which you want to put in a new joins display, is reserved with AS.
Example: return firstname field to alias nickname
Select FirstName as nickname, LastName, City
From Employees
Example: Return a new column to show inventory value
Select ProductName, UnitPrice, UnitsInStock, Unitprice*unitsinstock as
Valueinstock
From Products
Two. Where clause specifies query criteria
1 comparison operators
comparison operator meaning
= equals
> Greater than
< less than
>= greater than or equal to
<= less than or equal to
<> Not equal to
!> not greater than
!< not less than
Example: Return orders for January 96
Select OrderID, CustomerID, orderDate
From Orders
Where orderdate> #1/1/96# and OrderDate
Use MySQL full record under Cmd&linux
