mysql often uses command line operation statements
- 2021-01-18 06:42:49
- OfStack
Step 1: mysql service start and stop
net stop mysql
net start mysql
Step 2: Log in to mysql
The syntax is: mysql -u user name -p user password
mysql-uroot-p. Enter the mysql-uroot-p command, and you will be prompted for your password. Enter 12345.
mysql
>
Note that if you are connecting to another machine, you need to add 1 parameter -h machine IP
Third, increase new users
* to username @ login host identified by "password"
For example, add a user user1 password as password1, so that it can log in on the machine, and have the right to query, insert, modify and delete all databases. First of all,
Connect to mysql for root users, then type the following command:
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
If you want the user to be able to log in to mysql on any machine, change localhost to "%".
If you do not want user1 to have a password, you can type another command to remove the password.
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
Step 4: Manage the database
Log in to ES73en and run the following commands at the ES74en prompt, each ending with a semicolon.
1. Display database list.
show databases;
There are two databases by default: mysql and test. The mysql repository holds the mysql system and user permission information. By changing passwords and adding new users, we are actually operating on this repository.
2. Data table in the display library:
use mysql;
show tables;
3. Structure of display data table:
describe table name;
4. Build and delete libraries:
create database library name;
database library name;
5. Build a table:
use library name;
create table table name (list of fields);
drop; table;
ALTER TABLE TABLE ALTER TABLE TABLE TABLE
delete; from;
7. Show the records in the table:
select * from;
Step 5: Export and import data
1. Export data:
mysqldump --opt test
>
mysql.test
Export the database test database to the file mysql.test, which is a text file
For example: mysqldump-u root-p123456 --databases dbname
>
mysql.dbname
The database dbname is exported to the file mysql.dbname.
2. Import data:
mysqlimport -u root -p123456
<
mysql dbname.
I don't need to explain.
3. Import text data into database:
The field data of text data is separated by the tab key.
use test;
load data local infile "File name" into table
Use SHOW statements to find out what database currently exists on the server:
mysql
>
SHOW DATABASES;
2: create a database MYSQLDATA
mysql
>
CREATE DATABASE MYSQLDATA;
3: Select the database you created
mysql
>
USE MYSQLDATA; Is required (press the return key appear Database changed operation success!)
What tables exist in the current database
mysql
>
SHOW TABLES;
Create a database table
mysql
>
CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
Display table structure:
mysql
>
DESCRIBE MYTABLE;
7: Add a record to the table
mysql
>
insert into MYTABLE values ("hyq","M");
D:/mysql.txt :/mysql.txt :/mysql.txt :/mysql.
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: clear the table
mysql
>
delete from MYTABLE;
12: Update data in the table
mysql > update MYTABLE set sex="f" where name='hyq';