MySQL Tutorial: Database specific operations

1. Connecting to the database server

$./mysql-h Host_name-u User_name-p

• -H HOST_NAME (--host=host_name), the connected database hostname, can be omitted if it is on the local host.

• -U user_name (--user=user_name), database user name, on UNIX systems, if the login name of the system is the same as the data user name, it can be omitted. In a Windows system, you can give the database user name, such as set User=username, by setting the environment variable user.

• -P (--password), provide the database user password, with this option MySQL will prompt you to enter the password. The password entered is displayed as an asterisk to ensure security. You can also write the password directly after-p (no spaces between-p and password), but this is unsafe and not recommended.

After the connection is successful, the MySQL database server displays some welcome information. You can then open the specified database by using the mysql>use database_name command.

	$./mysql-h host_name-u user_name-p database_name command to open the specified database directly.

2. Update user password

Mysql>update user Set PASSWORD=PASSOWRD (' Your passowrd ') where host= '% ';

Refresh permission settings: Mysql>flush privileges;

3. mysql read the configuration file order

MY.CNF is a MySQL database configuration file, it exists in several places, in/etc directory, data directory and user home directory have. In different locations, the options inside have different scopes, the following is the order and function of MySQL read configuration file.

MySQL read config file in order:/etc/my.cnf Global options. DATADIR/MY.CNF server-specific Options. Defaults-extra-file the file specified with the--defaults-extra-file option. ~/.MY.CNF user-specific Options.

4. How to reset the MySQL administrator password

Sometimes we forget the password of the database administrator because we set the reason or the time, so that we are locked out of the MySQL server. The MySQL server provides a way for us to reset the password on the server. The operations on the windows and Linux/unix platforms are slightly different, as described below:

• linux/unix platform:

  1. use% kill-term pid to shut down the server, Using-term information allows the server to write the data in memory to disk before shutting down. If the server is not responding, we can use the% kill-9 PID to force the removal process, but this is not recommended. The data in memory is not written to disk, resulting in incomplete data. If you are using the Mysql_safe script to start the MySQL server, this script will monitor the server's operation and restart the server when it is terminated, so if you need to shut down the server, terminate the process before you really terminate the mysqld process.

  2. % mysqld_safe--skip-grant-tables & 

  3. % mysql-u
       Root #不用密码就可连接到服务器 mysql> use MySQL mysql> set password for ' root ' @ ' localhost ' = password (' password '); 

  4. shut down the server, and then start the server in the normal way.

• Under Windows platform:

  1. Log on to the server with the Administrator account and close the MySQL database server.

  2. Start the server with the--skip-grant-tables parameter:

     C:\mysql\bin>mysqld-nt--skip-grant-tables

  3. Reopen a console window and log in to the server with the MySQL command to set a new password for root:

     C:\mysql\bin>mysql mysql> use MySQL mysql> set password for ' root ' @ ' localhost ' = password (' password ');

  4. Shut down the server, and then start the server in the normal way.

5. Null value

NULL is a null value, which means nothing. It cannot compare operations and arithmetic operations with values, nor can it be compared to NULL because two null comparisons are meaningless. We can use "is NULL" or "is not NULL" to determine whether it is a null value. Such as:

Mysql> SELECT * FROM Test where mytest is NULL; Mysql> SELECT * FROM test where mytest are not NULL;

Later versions of MySQL3.23 have a new comparison operator, "<=>", which allows for equality comparisons of NULL values. Such as:

Mysql> SELECT * FROM Test where mytest <=> UNLL; Mysql> SELECT * FROM Test where not (MyTest <=>);

If the data in the sort after query contains null, then starting with MySQL4.0.2, the data row with the null value always appears at the beginning of the query result, even if the desc parameter is set. 4.0.2 previous version, if ASC is set, it appears at the beginning of the query result, and if Desc is set, it will appear at the end of the query result.

6. Using SQL variables

MySQL3.23.6 later versions allow variables to be set by query results, and the set variables can be used later. Variables are defined by @name, and are assigned in the form of @name:=value. Here is an example of assigning and using variables in a query statement:

Mysql> Select @name: =id from Test where mytest= "test"; Mysql> SELECT * from test where [email protected]

7. Change the default prompt

After logging into the database with MySQL, the default prompt for MySQL data is "MySQL" and we can set it to vary depending on the name of the database that the user opens, such as:

Mysql>prompt \d>\_ none>use test test>use MySQL mysql>

Prompt for the set command, \d represents the current database, and \_ represents a space.

8. Non-optimized full data table delete operation

In order to clear the data table, you need to know the number of deleted rows and maintain the value of the auto_increment sequence, the following DELETE statement is required:

# DELETE from table_name where 1;

9. mysql Transaction example

mysql advanced Features-transaction processing the following is an example of a transfer between two bank accounts. To use transaction processing in MySQL, you first need to create a table that uses a transaction table type (such as BDB = Berkeley db or InnoDB).

CREATE TABLE Account (account_id BIGINT UNSIGNED not NULL PRIMARY KEY auto_increment, balance DOUBLE) TYPE = InnoDB; To use transaction processing on a transaction table, you must first turn off autocommit: SET autocommit = 0; The transaction begins with the BEGIN command: begin; Now the MySQL client is in the context of the server-related things. Any changes made to the transaction table will not be permanently changed until it is committed. UPDATE account SET balance = 50.25 WHERE account_id = 1; UPDATE account SET balance = 100.25 WHERE account_id = 2; After making all changes, use the commit command to complete the transaction: commit; Of course, the real advantage of a transaction is that it manifests itself in the execution of an error in the second statement, and can be rolled back if the entire transaction is terminated before committing: ROLLBACK; Here is another example of a direct math operation with MySQL: SELECT @first: = Balance from account WHERE account_id = 1; SELECT @second: = Balance from account WHERE account_id = 2; UPDATE account SET balance = @first -25.00 WHERE account_id = 1; UPDATE account SET balance = @second + 25.00 WHERE account_id = 2; In addition to the commit command, the following command automatically ends the current transaction: ALTER table BEGIN CREATE INDEX drop DATABASE drop table LOCK TABLES RENAME table TRUNCATE Unlo CK TABLES


This article by the Web Teaching Network (http://www.webjx.com) organizes publishes! Reprint please indicate the source, thank you!

MySQL Tutorial: Database specific operations