How to use mysql database?

Usage of mysql database: 1. Connect to mysql; 2. Start the mysql service; 3. Stop the mysql service; 4. Check whether the port conflicts; 5. Exit mysql; 6. Change the password.

1. Basic concepts of database
1. Two commonly used engines:

(1) InnoDB engine:
1) Support ACID, which simply means supporting transaction integrity and consistency;
2) Supporting row locks, ORACLE-like consistent reading, and multi-user concurrency;
3) Unique clustered index primary key design method, which can greatly improve concurrent read and write performance;
4) Support foreign keys;
5) Support self-repair of crashed data;
6) InnoDB is designed to handle large-capacity database systems, and its CPU utilization It is unmatched by other disk-based relational database engines.
  7) It is a reliable transaction processing engine and does not support full-text search
(2) MyISAM engine:
  1) Does not support atomicity of each query
  2) Only supports table locations
3) The emphasis is on performance, its execution speed is faster than the InnoDB type, but it does not provide transaction support
4) If you perform a large number of SELECT (delete tasks), MyISAM is a better choice

2. Introduction to database:
1) Database-database: A container that saves organized data (usually a file or a set of files)
2) Table-table: A structured structure of a specific type of data List
3) Schema-schema: Information about the layout and characteristics of the database and tables

4) Column-column: A field in the table, all tables are composed of one or more columns
5) Row-row: A record in the table

6) Data type-datatype: The type of data allowed. Each table column has a corresponding data type, which limits (or allows ) The data stored in this column

2. Mysql command line (as shown below)

1. Connect mysql - format:

 mysql -h主机地址 -u用户名 －p用户密码

1) Connect to the local Mysql, first open the DOS window (shortcut key window + R together to bring up the command instruction box and then enter cmd), then enter the directory mysql\bin, then type the command mysql -u root -p, return You will be prompted to enter your password after driving. The MYSQL prompt is: mysql> (the default root user has no password)
2) Connect to Mysql on the remote host: Assume that the IP of the remote host is: 218.105.110.116, and the user name is root, the password is abcd123456, then type the following command: mysql -h218.105.110.116 -u root -p abcd123456; (Note: There is no need to add a space between u and root, and the same is true for others)

2. Start the mysql service:

net start mysql

3. Stop the mysql service:

net stop mysql

4. Check whether the port conflicts:

netstat –na | findstr 8080 View The monitored port, findstr is used to find whether the following port exists.

5. Exit mysql:

quit or exit

6. Change password: Format:

mysqladmin -u username -p old password password New password;

For example, change the password for root:

mysqladmin -u root -p ab12 password djg345

3. Display command

1. Display the database list in the current database server:

mysql> SHOW DATABASES；

2. Display the data table in a database:

mysql> USE 库名；//使用某个库；
mysql> SHOW TABLES；//列出库中所有的表

3. Display the structure of the data table:

mysql> DESCRIBE 表名；

4. Create the database:

mysql> CREATE DATABASE 库名；

5. Create a data table:

mysql> USE 库名。mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1)；

6. Delete the database:

mysql> DROP DATABASE 库名；

7. Delete the data table:

mysql> DROP TABLE 表名；

8. Clear the records in the table:

mysql> DELETE FROM 表名；

9. Display records in the table:

mysql> SELECT  *  FROM 表名；

10. Insert records into the table:

mysql> INSERT INTO 表名 VALUES (”hyq”,”M”)；

11. Update data in the table:

mysql-> UPDATE 表名 SET 字段名1=’a',字段名2=’b’ WHERE 字段名3=’c'；

12. Use text mode Load the data into the data table:

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名；

13. Import .sql file command:

mysql> USE 数据库名； mysql> SOURCE d:/mysql.sql；

14. Change the root password on the command line:

mysql>UPDATEmysql.userSETpassword=PASSWORD(’新密码’) WHERE User=’root’；
mysql> FLUSH PRIVILEGES；

15. Display use Database name:

mysql> SELECT DATABASE()；

16. Display current user:

mysql> SELECT USER()；

The above is the detailed content of How to use mysql database?. For more information, please follow other related articles on the PHP Chinese website!