1. Connect to Mysql
Format: mysql -h host address -u username -p user password
1. Connect to MYSQL on this machine.
First open a DOS window, then enter the directory mysqlbin, and then type the command mysql -u root -p. After pressing Enter, you will be prompted to enter your password. Note that the user name can have spaces or no spaces in front of it, but there must be no spaces in front of the password, otherwise you will be Please re-enter password.
If MYSQL has just been installed, the super user root does not have a password, so just press Enter to enter MYSQL. The MYSQL prompt is: mysql>
2. Connect to MYSQL on the remote host. Assume that the IP of the remote host is: 110.110.110.110, the user name is root, and the password is abcd123. Then type the following command:
mysql -h110.110.110.110 -u root -p 123; (Note: There is no need to add a space between u and root, and the same is true for others)
3. Exit the MYSQL command: exit (Enter)
2. Change the password
Format: mysqladmin -u username -p old password password new password
1. Add a password ab12 to root.
First enter the directory mysqlbin under DOS, and then type the following command
Mysqladmin -u root -password ab12
Note: Because root does not have a password at the beginning, the -p old password item can be omitted.
2. Change the root password to djg345.
Mysqladmin -u root -p ab12 password djg345
3. Add a new user
Note: Different from the above, the following is a command in the MYSQL environment, so it is followed by a semicolon as the command terminator
Format: grant select on database.* to username@login host identified by "password"
1. Add a user test1 with the password abc, so that he can log in on any host and query, insert, modify, and delete all databases permission. First connect to MYSQL as the root user, and then type the following command:
grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”;
But adding more users is very dangerous. If someone knows the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. Solution See 2.
2. Add a user test2 with the password abc so that he can only log in on localhost and perform query, insert, modify and delete operations on the database mydb (localhost refers to the local host, where the MYSQL database is located) that host), so even if the user knows the password of test2, he cannot directly access the database from the Internet, and can only access it through the web page on the MYSQL host
Grant select, insert, update, delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”;
If you don’t want test2 to have a password, you can type another command to remove the password
grant select,insert,update,delete on. mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”;
4.1 Create database
Note: You must connect to the Mysql server before creating the database
Command: create database
Example 1: Create a database named xhkdb
mysql> create database xhkdb;
Example 2: Create a database and assign users
①CREATE DATABASE Database name;
②GRANT SELECT,INSERT,UPDATE,DELETE ,CREATE,DROP,ALTER ON database name.* TO database name@localhost IDENTIFIED BY 'password';
③SET PASSWORD FOR 'database name'@'localhost' = OLD_PASSWORD('password');
Execute 3 commands in sequence Complete the database creation. Note: The Chinese "password" and "database" need to be set by the user.
4.2 Show databases
Command: show databases (note: there is an s at the end)
mysql> show databases;
Note: In order to no longer display garbled characters, the default encoding of the database must be modified. The following takes the GBK encoding page as an example:
1. Modify the MYSQL configuration file: modify default-character-set=gbk in my.ini
2. Modify when the code is running:
①Java code: jdbc:mysql:// localhost:3306/test?useUnicode=true&characterEncoding=gbk
②PHP code: header("Content-Type:text/html;charset=gb2312");
③C language code: int mysql_set_character_set(MYSQL * mysql, char * csname);
This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. The concatenated collation becomes the default collation for the character set. This function works similarly to the SET NAMES statement, but it also sets the value of mysql- > charset, thereby affecting the character set set by mysql_real_escape_string().
4.3 Delete database
Command: drop database
For example: delete a database named 0 rows affected (0.00 sec)
Example 2: Delete a database that does not exist
mysql> drop database drop_database;
ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
// // An error occurred. The 'drop_database' database cannot be dropped. The database does not exist.
mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 sec)//Generate a warning indicating that this database does not exist
mysql> create database drop_database;
Query OK, 1 row affected (0.00 sec) )
mysql> drop database if exists drop_database;//if exists Determine whether the database exists, no error will be generated if it does not exist
Query OK, 0 rows affected (0.00 sec)
4.4 Connect to the database
Command: use
mysql> use xhkdb;
Screen prompt: Database changed
The use statement can notify MySQL to use the db_name database as the default (current) database for subsequent statements. . This database remains the default database until the end of the segment, or until a different USE statement is issued:
mysql> USE db1;
mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
mysql> USE db2 ;
mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable
Use the USE statement to mark a specific current database, which will not prevent you from accessing tables in other databases. The following example can access the author table from the db1 database and the edit table from the db2 database:
mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
-> WHERE author.editor_id = db2.editor .editor_id;
USE statement is set up for compatibility with Sybase.
Some netizens asked how to exit after connecting. In fact, you don’t need to exit. After using the database, you can use show databases to query all databases. If you want to jump to other databases, just use
use other database names
.
4.5 Currently selected database
Command: mysql> select database();
The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, etc. How to use the special features of the SELECT command in MySQL?
1. Display the version of MYSQL
mysql> select version();
+-----------------------+
| version() -----------------------+
| 6.0.4-alpha-community |
+-------------- ---------+
1 row in set (0.02 sec)
2. Display the current time
mysql> select now();
+-------------- -------+
| now() |
+---------------------+
| 2009-09-15 22:35:32 |
+---------------------+
1 row in set (0.04 sec)
3. Display year, month and day
SELECT DAYOFMONTH(CURRENT_DATE);
+--------------------------+
| DAYOFMONTH(CURRENT_DATE) |
+------------- -------------+
| 15 |
+--------------------------+
1 row in set (0.01 sec)
SELECT MONTH(CURRENT_DATE);
+---------------------+
| MONTH(CURRENT_DATE) |
+--- ------------------+
| 9 |
+--------------------------+
1 row in set (0.00 sec)
SELECT YEAR(CURRENT_DATE);
+--------------------------+
| YEAR(CURRENT_DATE) |
+---- ----------------+
| 2009 |
+--------------------+
1 row in set ( 0.00 sec)
4. Display string
mysql> SELECT "welecome to my blog!";
+----------------------+
| welcome to my blog! |
+----------------------+
| welcome to my blog! |
+---------- ------------+
1 row in set (0.00 sec)
5. When used as a calculator
select ((4 * 4) / 10 ) + 25;
+----- ------------------+
| ((4 * 4) / 10 ) + 25 |
+---------------- ------+
| 26.60 |
+----------------------+
1 row in set (0.00 sec)
6. String Connect string
select CONCAT(f_name, " ", l_name)
AS Name
from employee_data
where title = 'Marketing Executive';
+---------------+
| Name |
+-------------+
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+------------- -+
3 rows in set (0.00 sec)
Note: The CONCAT() function is used here to concatenate strings. In addition, we also used the AS we learned before to give the result column 'CONCAT(f_name, " ", l_name)' a pseudonym.