Connect to MySQL
Format: mysql -h host address -u username -p user password
1. Example 1: Connect to MYSQL on this machine.
First open the DOS window, then enter the directory mysqlbin, and then type the command mysql -uroot -p. After pressing Enter, you will be prompted to enter your password. If MYSQL has just been installed, the super user root does not have a password, so directly Press Enter to enter MySQL. The MySQL prompt is: mysql>
2. Example 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 -uroot -pabcd123
(Note: u and root do not need to add spaces, and the same applies to others)
3 . Exit MySQL command: exit (Enter)
Note: If you want to successfully connect to the remote host, you need to open MySQL remote access permission on the remote host
The method is as follows:
In Enter the remote host as an administrator
Enter the following command
mysql>GRANT ALL PRIVILEGES ON *.* TO 'agui'@%'IDENTIFIEDBY '123' WITH GRANT OPTION;
FLUSH PRIVILEGES;
//Give any host permission to access data
mysql>FLUSH PRIVILEGES
//Modification takes effect
agui for us The username
password used is 123
that is: after setting the settings on the remote host, we can connect to the remote host through mysql -h110.110.110.110 -uagui -p123
Change password
Format: mysqladmin -u username -p old password password new password
1. Example 1: Add a password ab12 to root. First, enter the directory mysqlbin under DOS, and then type the following command
mysqladmin -uroot -password ab12
Note: Because root does not have a password at the beginning, the -p old password item can be omitted. .
2. Example 2: Change the root password to djg345.
mysqladmin -uroot -pab12 password djg345
Add new user
(Note: Different from the above, the following is a command in the MySQL environment , so there is a semicolon after it as the command terminator)
Format: grant select on database.* to username@login host identified by “password”
Example 1. Add a user The password of test1 is abc, which allows him to log in on any host and has query, insert, modify, and delete permissions on all databases. First connect to MySQL as the root user, and then type the following command:
grant select,insert,update,delete on *.* to test1@“%” Identified by “abc”;
But the user added in Example 1 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 example 2.
Example 2, add a user test2 with the password abc, so that he can only log in on localhost, and can query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the MYSQL database The host where it is located), so that 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 test2@localhost identified by “abc”;
If you don’t want test2 to have a password, you can type another command to eliminate the password .
grant select, insert, update, delete on mydb.* to test2@localhost identified by “”;
Let’s take a look at the database operations in MySQL. Note: You must first log in to MySQL. The following operations are performed at the MySQL prompt, and each command ends with a semicolon.
Operation skills
1. If you find that you forgot to add a semicolon when typing a command and press Enter, you do not need to type the command again. You only need to add a semicolon and press Enter. That is to say, you can divide a complete command into several lines and use a semicolon as the end mark.
2. You can use the cursor up and down keys to recall previous commands. But an old version of MySQL I used before does not support it. I am using mysql-3.23.27-beta-win.
Display command
1. Display the database list.
show databases;
There were only two databases at the beginning: mysql and test. The mysql library is very important. It contains MySQL system information. When we change passwords and add new users, we actually use this library for operations.
2. Display the data tables in the library:
use mysql; //Open the library, it will be familiar to those who have learned FOXBASE
show tables;
3. Display the structure of the data table:
describe table name;
4. Create database:
create database library name;
5. Create table:
use library name;
create table table name (field setting list);
6. Delete database and table:
drop database database name;
drop table table name;
7. Clear the records in the table:
delete from table name;
8. Display records in the table:
select * from table name;
9. Display errors, warnings and notifications generated by the last executed statement:
show warnings;
10. Only the errors generated by the last executed statement are displayed:
show errors;
Instance
drop database if exists school; //If SCHOOL exists Then delete
create database school; //Create library SCHOOL
use school; //Open library SCHOOL
create table teacher //Create table TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default 'Shenzhen',
year date
); //End of table creation
//The following are the inserted fields
insert into teacher values('','glchengang','XX company', '1976-10-10');
insert into teacher values('','jack','XX company','1975-12-23');
Note: in Creating table:
1. Set the ID to a numeric field of length 3: int(3) and let each record automatically increase by one: auto_increment cannot be empty: not null and make it the primary 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. What is the difference between varchar and char? I will have to wait for a future article to talk about it.
4. Set YEAR as the date field.
If you type the above command at the mysql prompt, it is OK, but it is not convenient for debugging. You can write the above command as it is into a text file, assuming it is school.sql, then copy it to c:\, enter the directory \mysql\bin in DOS state, and then type the following command:
mysql - uroot -p password
If successful, a blank line will be left blank; if there is an error, there will be a prompt. (The above command has been debugged, you only need to remove the // comment to use it).
Text to database
1. The text data should conform to the format: field data are separated by tab keys, and null values are replaced by \n.
Example:
3. rose Shenzhen No. 2 Middle School 1976-10-10
4. mike Shenzhen No. 1 Middle School 1975-12-23
2. Data input command load data local infile "file name" into table table name;
Note: You'd better copy the file to the \mysql\bin directory, and first use the use command to open the database where the table is located
Or transfer (cd) the path to the path where the text is to be imported before logging in to the database.
Backup database
(The command is executed in the \mysql\bin directory of DOS)
mysqldump --opt school>school.bbb
Note: Back up the school database to the school.bbb file. School.bbb is a text file. You can choose any file name. Open it and see if you will find new things.
Method to automatically back up mysql database
1. First create a batch file and save the following code as a .bat file. The file name is preferably in English. Pay attention to the path below. Taking the author's own database as an example, the database is installed under mysql\mysql under the D drive. The backup location is F:\beifen. The following code is the date [8].
@echo off[9]
color 0D
MODE con: COLS=71 LINES=25
title mysql database automatic backup script (task plan )--Script author: http://www. ***. com
set sou_dir="D:\mysql\Mysql\data"
set obj_dir=F:\beifen\%date:~0,10%
net stop mysql
md %obj_dir%
xcopy /e /y %sou_dir% %obj_dir%
net start mysql
@echo off&setlocal enabledelayeexpansion
call:D,30
echo. The date 30 days ago is: %D%
echo. Delete the backup 30 days ago...
if exist F:\beifen\%D% rd /s /q F:\beifen\%D%
echo The automatic backup is completed and the program will automatically exit...
Restore/Import
Restore/Import database [10] During the import process, enter the mysql database console, such as mysql -u root -p
mysql>use database
Then use the source command, and the following parameters are script files (such as .sql used here)
mysql>source d:\dbname.sql
If the prompt cannot be found file, after inputting the source, you can use the mouse to drag the file directly into the command line window
Start the tracking file
mysqld --debug
Shut down the server
mysqladmin -u root shutdown
Start the MySQL service
mysqld --console
The above is the basic content of Mysql series (6) for beginners. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Atom editor mac version download
The most popular open source editor

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 Mac version
God-level code editing software (SublimeText3)
