MySQL Command Summary: mysql Command

MySQL Command Summary: mysql Command

=== 0-MySQL Password =
0.1-log on to and access the MySQL database:

0.1.1-log on to the MySQL database: C: \ Users \ Administrator> mysql-u username-hMySQL server address-p
Enter password: User password // The initial mysql does not have a password by default. You can press Enter to Enter the password. If you have a password, Enter the password.
For example, mysql-uroot-hlocalhost-p
Enter password: ******** // when my password is 11111111
0.1.2-enter the MySQL database: mysql> use mysql;

0.2-set the password for the root user:Mysql> update user set password = password ("new password") where user = "root ";
For example, update user set password = password ("11111111") where user = "root ";
0.3-refresh the database (Be sure to remember ):Mysql> flush privileges;
0.4-exit mysql:Mysql> quit
===1-mysql server commands ===
1.1-connect to the MySQL server:\> Mysql-uroot-h127.0.0.1-ppassword
\> Mysql-u username-hMySQL server address-p User Password
Example: \> mysql-uroot-hlocalhost-p11111111
1.2-Disconnect the MySQL server:Mysql> quit;
1.3-stop the MySQL server:
1) \> net stop mysql
Or 2) \> mysqladmin-uroot shutdown-ppassword
Example: \> mysqladmin-uroot shutdown-p11111111
1.4-start the MySQL server:\> Net start mysql
====2-mysql Database Operations ===
2.1-create a database:Create database name;
Example: create database db_czy;
2.2-view database:Show databases;
2.3-select database:Use db_czy;
2.4-delete a database:Drop database name;
Example: drop database db_czy;
=== 3-MySQL data table operations ===
3.1-create a data table in the selected database:
Create table data table name (field name 1 attribute, field name 2 attribute ,...);
Example: create table tb_people (
Id int auto_increment primary key,
User varchar (30) not null,
Password varchar (30) not null,
Email varchar (50) not null,
Createtime datetime
);
3.2-view the data table in the selected database:Show tables;
3.3-view the table structure:
1.1) show columns from data table name from database name;
Example: show columns from tb_people from db_czy;
Or 1.2) show columns from database name. Data Table Name;
Example: show columns from db_czy.tb_people;
Or 2.1) describe data table name;
Example: describe tb_people;
Or 2.1.1) Name of the desc data table name column;
For example, describe tb_people password; // only one column of information can be listed.
3.4-Modify Table Structure:Alter table data table name related operations;
Example: alter table tb_people add email varchar (50) not null, modify user varchar (40 );
// Add a new field email with the type varchar (50) and not null. Change the field user type to varchar (40 ).
3.5-rename a table:Rename table data table name 1 to data table name 2;
Example: rename table tb_people to tb_czy;
Rename table tb_czy to tb_people;
3.6-delete a table:Drop table data table name;
Example: drop table tb_people;
=== 4-MySQL data addition, deletion, query, and modification operations ===
4.1-insert record:Insert into data table name (field name 1, field name 2 ,..) values (value 1, value 2 ,..); // multiple rows of records can be inserted at a time. Only one row can be inserted at a time in standard SQL statements.
Example: insert into tb_people (user, password, email, createtime)
Values ('czy', '000000', '2017 @ qq.com ', '2017-10-10 02:36:50 ');
Insert into tb_people (user, password, email, createtime)
Values ('q6', '123', '1970 @ qq.com ', '1970-10-10 02:40:36'); // values ('wha', '123 ', '2017 @ qq.com ', '2017-10-10 02:45:25 ');

4.2-delete record:Delete from data table name where Condition
Example: delete from tb_people where user = 'wha'; // The general condition is that the id is specified to delete the table. If the where condition is not specified, the entire table is deleted.
4.3-query database records:
1) select * from data table; // query all data in the specified table
Example: select * from tb_people;
2) select * from database name. Data Table Name; // query data in the specified table of the specified database
Example: select * from db_czy.tb_people;
3) select part of the field name from the data table where query related conditions; // query the data of the specified field and a field value in the specified table
Example: select id, user, password, email from tb_people where id = 1;
4.4-Modify record:Update data table name set column_name1 = new_value1, column_name2 = new_value2,... [where condition];
Update data table name set field name 1 = new value 1, field name 2 = new value 2,... [where condition];
Example: update tb_people set password = '000000', email = '2017 @ 163.com 'where user = 'czy ';
=== 5-MySQL data backup and recovery ===
5.1-data backup:C: \ Users \ Administrator> mysqldump-u user name-p User Password Database Name> Backup file storage path and file name
Example: mysqldump-uroot-p11111111 db_czy> D: \ File \ phpEnv \ MySQL \ data_backup.txt
5.2-data recovery:C: \ Users \ Administrator> mysql-u user name-p User Password Database Name <file storage path and file name to be restored
For example, mysql-uroot-p11111111 db_czy <D: \ File \ phpEnv \ MySQL \ data_backup.txt

Started with: 2016.9.30 ---- Zhiyin