MySQL command line mode management MySQL a little experience _mysql

MySQL database is the first choice for the backend database of small and medium sized web sites, because it is free for non-commercial applications. Web developers can build a "linux+apache+php+mysql" platform, which is one of the most cost-effective and efficient platforms. When developing with MYSQL, MySQL's own documentation is a good reference for beginners. This article is my little experience in using MySQL.
The current general user's development environment is mostly Windows or Linux, users can go to the http://www.codepub.com/software/ index.html download the relevant version of the installation, in Windows MySQL in the form of a service, you should ensure that the service has been started, the free net start MySQL command started. Linux can be started with the "/etc/rc.d/init.d/mysqld start" command, and notice 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 risk, for some important applications we should improve security as far as possible, where the anonymous account deletion, the root account set the password, you can use the following command:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to limit the logon terminals used by the user, you can update the host field for the corresponding user in the users table, restart the database service after making the above changes, and at this point you may be able to log on to the following similar commands:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The command parameters above are part of the common parameters, and the details refer to the documentation. The mydb here is the name of the database to log on to.
In the development and practical applications, users should not only use root to connect the database, although the use of root users to test is very convenient, but it will bring significant security risks to the system, but also not conducive to the improvement of management technology. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete data. MySQL user management is implemented through the user table, there are two ways to add new users, one is to insert the corresponding data rows in the user table, set the appropriate permissions, and the second is to create a user with some kind of permission through the grant command. One of the common uses of grant is as follows:
Grant all on mydb.* to newusername@hostname identified by "password";
Grant usage on *.* to newusername@hostname identified by "password";
Grant Select,insert,update on mydb.* to newusername@hostname identified by "password";
Grant Update,delete on MyDB. TestTable to Newusername@hostname identified by "password";
To give this user the ability to manage his or her permissions on the object, add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption, in case the malicious person steals the password. For those who have not used the user should be given clearance, permissions of the user should be timely recall permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.
The following gives an explanation of the common rights that I get from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills a service thread belonging to another user.
RELOAD: Overload access Control table, refresh log, etc.
SHUTDOWN: Turn off MySQL service.
Database/data Table/Data column permissions:
Alter: Modify existing data tables (for example, add/Remove Columns) and indexes.
Create: Create a new database or datasheet.
Delete: Deletes a table record.
Drop: Deletes a datasheet or database.
Index: Create or delete indexes.
Insert: Adds a record of the table.
Select: Displays/searches the records of the table.
Update: Modifies records that already exist in the table.
Special permissions:
All: Allow to do anything (like root).
USAGE: Only allow login-nothing else allowed.
At last, I give the MySQL operation demo under RedHat9.0:
Select the root user login for the database
[Weiwen@weiwenlinux] $mysql-uroot-p
Enter Password:mypassword
Mysql>create database mydb;
Query OK, 1 row affected (0.02 sec)
Mysql>use MyDB;
Database changed
Mysql>create table testtable (Id int aut_increment primary KEY,
UserName varchar () NOT NULL,
Address varchar (255));
Query OK, 0 rows affected (0.02 sec)
Mysql>grant all on mydb.* to Test@localhost identified by "test";
Query OK, 0 rows affected (0.01 sec)
Mysql>quit
Bye
[Weiwen@weiwenlinux] $mysql mydb-utest-ptest

The Test.sql is a SQL script that is edited with VI, and its contents are:
Insert into TestTable (username,address) VALUES (' Tom ', ' Shanghai ');
Insert into TestTable (username,address) VALUES (' John ', ' Beijing ');
SELECT * from TestTable;
Running an edited SQL script can be done with source filename or. \ filename.
The above is just a simple exercise for beginners, to become a good database, when the tireless pursuit of knowledge, and constantly think, try, think again.

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
MySQL Common command Summary
This two days to get a Web site, and use MySQL, but the command is not a thought, so, take this opportunity to tidy up, the right as a note, so that their later lookup!
1: Use the show statement to find out what database is currently 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 when the database changed the 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: Load data into a database table (for example, d:/mysql.txt) in text mode
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: Delete Table
Mysql>drop TABLE MYTABLE;
11: Empty the table
Mysql>delete from MYTABLE;
12: Update the data in the table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';