Practical mysql database command

By Mysql

Connect: mysql -h host address -u user name-p user password (Note: u and root need not add spaces, the other is the same)
disconnect: exit (Enter)

Create authorization: grant select on database.* to username@Login host identified by \”password\”
Modify password: mysqladmin -u username -p old password password new password
delete authorization: revoke select,insert,update,delete om *.* [email protected];

Display database: show databases;
Display data table: show tables;
Display table structure: describe table name ;

Create table: create table table name (field setting list);
Delete table: drop table table name;
Modify table: alter table t1 rename t2
Query table: select * from table name;
Empty table: delete from table name;
Backup table: mysqlbinmysqldump -h(ip) -uroot -p(password) databasenametablename> tablename.sql
Restore table: mysqlbinmysql -h (ip) -uroot -p(password) databasenametablename

Back up the database: mysql\bin\mysqldump -h(ip) -uroot -p(password ) databasename> database.sql
Restore the database: mysql\bin\mysql -h(ip) -uroot -p(password) databasename< database.sql
Copy the database: mysql\bin\mysqldump –all-databases >all-databases.sql
Repair the database: mysqlcheck -A -o -uroot -p54safer

Import text data: load data local infile \”file name\” into table table name;
Data import guide Out: mysql\bin\mysqlimport database tables.txt

Start and stop mysql service
net stop mysql
net start mysql

Login to mysql
The syntax is as follows: mysql -u username -p user password
Type the command mysql -uroot -p, press Enter and prompt you to enter the password, enter 12345, and then press Enter to enter mysql. The mysql prompt is:
mysql>
Note, if you are connecting to another machine, you need to add a parameter -h machine IP

Add a new user
Format: grant permission on database.* to username @Login host identified by “password”
For example, add a user user1 password to password1, so that it can log in on this machine, and have query, insert, modify, and delete permissions for all databases. First connect to mysql as the root user, and then type the following command:
grant select,insert,update,delete on *.* [emailprotected] Identified by “password1”;
If you want the user to be able to be on any machine Log on to mysql, then change localhost to “%”.
If you don’t want user1 to have a password, you can type another command to remove the password.
grant select,insert,update,delete on mydb.* [email protected] identified by””;

Example 2: Connect to MYSQL on the remote host
　　 Assuming 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 can be used without spaces, the other is the same) 　　

< p>　　3. Exit MYSQL command: exit

(一) Connect MYSQL:
Format: mysql -h host address-u user name-p user password

1, example 1 : Connect to MYSQL on this machine
First, open the DOS window, and then enter the bin directory under the mysql installation directory, for example: D:\mysql\bin, and then type the command mysql -uroot-p, and then press Enter to prompt You enter the password. If you just installed MYSQL, the super user root does not have a password, so you can enter MYSQL directly by pressing Enter. The prompt of MYSQL is: mysql>
2, Example 2: Connect to the remote host MYSQL on the
Assume that the IP of the remote host is 10.0.0.1, the user name is root, and the password is 123. Then type the following command:
mysql -h10.0.0.1 -uroot-p123
(Note: u and root can be without spaces, the other is the same)
3. Exit MYSQL command
exit ( Enter)

(2) Modify password:
Format: mysqladmin -u username -p old password password new password
1, Example 1: Add a password 123 to root. First enter the directory C:\mysql\bin under DOS, and then type the following command:
mysqladmin -uroot -password123
Note: Because root does not have a password at the beginning, the -p old password can be omitted.
2, Example 2: Then change the root password to 456
mysqladmin -uroot -pab12password 456
(3) Add a new user: (Note: Unlike the above, the following is because it is in the MYSQL environment Command, so there is a semicolon as the end of the command)
Format: grant select on database.* to username@Login host identified by “password”
Example 1. Add a user test1 password to abc, Let him log in on any host, and have query, insert, modify, and delete permissions for all databases. First connect to MYSQL as the root user, and then type the following command:
grantselect,insert,update,delete on *.* to [emailprotected]”%” Identified by”abc”;

But examples 1 The added user is very dangerous. If you want someone to know the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever you want with your data. See examples for solutions 2.
Example 2. Add a user test2 password to 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, where the MYSQL database is located Host), 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.
grantselect, insert, update, delete on mydb.* to [email protected] identifiedby “abc”;
If you don’t want test2 to have a password, you can type another command to delete the password.
grantselect,insert,update,delete on mydb.* to [email protected] identifiedby “”;

MySQL import and export commands
1. Export the entire database
　　mysqldump -u username -p database name> exported file name
　　mysqldump -u wcnc -p smgp_apps_wcnc >wcnc.sql
2. Export a table
　　mysqldump -u username-p database name table name> exported file name
br>　　mysqldump -u wcnc -p smgp_apps_wcnc users>wcnc_users.sql

3. Export a database structure
　　mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc>d:wcnc_db .sql
　　-d has no data–add-drop-table adds a drop table before each create statement

4. Import the database
　　 commonly used source commands
　　 enter the mysql database control Station,
　　 such as mysql -u root -p
　　mysql>use database
　　 Then use the source command, the following parameters are script files (such as the .sql used here)
　　mysql>source d:wcnc_db .sql

Create database:

　　create database test1;

Database query:

　　 show databases;

Select the database to be operated;

　　use test1

View all the data tables created in the test1 database:

　　show tables;

Create a table:

　　create table emp(ename varchar(10),hiredate date,sal decimal(10,2) ,deptno int(2));

View table:

　　desc emp;

Delete table:

　　drop table emp

Modify table type:

　　alter table emp modify ename varchar(20);

Add table fields

　　alter table emp add column age int(3);

Delete table fields

< p>　　alter table emp drop column age;

Rename the field

　　alter table emp change age agel int(4);

　　 Note: change< Both /code> and modify can modify the definition of the table. The difference is that the column name needs to be written twice after change, which is inconvenient. But the advantage of change is that you can modify the column name, but modify cannot.

Modify the field arrangement order

  1. alter table emp add birth date after ename;

  2. alter table emp modify age int(3) first;

table rename

　　alter table emp rename emp1;

Insert record

　　insert into emp(ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1) ;

Modify the order of the fields

  1. alter table emp add birth date after ename;
  2. alter table emp modify age int(3) first;

Insert multiple records at once

  1. INSERT INTO tablename ( field1, field2,……fieldn)

  2. VALUES(record1_value1, record1_value2,……record1_valuesn),
  3. (Record2_value1, record2_value2,……record2_valuesn),

Update record: update emp set sal=4000 where ename='lisa';

    < li>

Delete records: delete from emp where ename='dony';

  1. delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

Query records: select * from emp;

where "*" means to select all records, or all fields separated by commas can be used instead, for example

Query unique records : Select distinct deptno from emp;

Conditional query: select * from emp where deptno=1;

Sorting and restriction: select * from emp order by sal;

< p>Aggregation

  1. Fun_name indicates the aggregation operation to be done, that is, the aggregation function. Commonly used are sum (sum), count(*) (number of records), max (maximum), min (minimum).

  2. The GROUP BY keyword indicates the field to be classified and aggregated. For example, if you want to count the number of employees by department, the department should be written after group by.
  3. 　WITH ROLLUP is an optional syntax, indicating whether to re-aggregate the results after classification and aggregation.
  4. The HAVING keyword means to filter the classified results again.
  5. #The statistics of the company in the emp table Total number of people
  6. Select count(1) from emp;
  7. #On this basis, count the number of people in each department
  8. select deptno,count(1) from emp group by deptno;
  9. #It is necessary to count the number of people in each department and To count the total number of people
  10. Select deptno,count(1) from emp group by deptno with rollup;
  11. #Count the number of people Department with more than 1 person
  12. select deptno,count(1) from emp group by deptno having count(1)>1;
  13. #Statistics of the total salary, maximum and minimum salary of all employees in the company
  14. SELECT sum(sal),max(sal),min(sal) from emp;

Table join

  1. select ename,deptname from emp,dept where emp.deptno=dept.deptno;

    < /li>

  2. # Can list names that do not exist in another column
  3. Select ename, deptname from emp left join dept on emp.deptno=dept. deptno;

Sub query

  1. select * from emp where deptno in(select deptno from dept);
  2. #If the number of records in the subquery is unique, you can also use = instead of in
  3. select * from emp where deptno = (select deptno from dept limit 1);

alter table emp add birth date after ename;

alter table emp add birth date after ename;

alter table emp modify age int(3) first;

alter table emp modify age int(3) first;

alter table emp add birth date after ename;

alter table emp modify age int(3) first;

INSERT INTO tablename (field1, field2, ……Fieldn)

INSERT INTO tablename (field1, field2,……fieldn)

VALUES(record1_value1, record1_value2,……record1_valuesn),

< p>(record2_value1, record2_value2,……record2_valuesn),

delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

delete a,b from emp a,dept b where a .deptno=b.deptno and a.deptno=3;

Fun_name indicates the aggregation operation to be done, that is, the aggregation function. Commonly used are sum (sum), count(*) ( Number of records), max (maximum), min (minimum).

fun_name represents the aggregation operation to be done, that is, the aggregation function. Commonly used are sum (sum), count(*) (number of records), max (maximum), min ( Minimum).

The GROUP BY keyword indicates the field to be classified and aggregated. For example, if you want to classify and count the number of employees by department, the department should be written after group by.

　WITH ROLLUP is an optional syntax, indicating whether to re-aggregate the results after classification and aggregation.

The HAVING keyword means to filter the results after classification.

#To count the total number of companies in the emp table

select count(1) from emp;

#On this basis, count the number of people in each department

select deptno,count(1) from emp group by deptno;

# It is necessary to count the number of people in each department as well as the total number of people

select deptno,count(1) from emp group by deptno with rollup;

#Departments with more than 1 person

/p>

select deptno,count(1) from emp group by deptno having count(1)>1;

#Statistics of the total salary, maximum and minimum salary of all employees in the company

select sum(sal),max(sal),min(sal) from emp;

select ename,deptname from emp,dept where emp.deptno=dept.deptno;

< p># Can list names that do not exist in another column

select ename, deptname from emp left join dept on emp.deptno=dept.deptno;

select * from emp where deptno in(select deptno from dept);

#If the number of subquery records is unique, you can also use = instead of in

select * from emp where deptno = (select deptno from dept limit 1);

, , ,

Leave a Comment

Your email address will not be published.