Detailed introduction to common Mysql instructions and Chinese garbled problems under Ubuntu

This article mainly introduces the common commands of Mysql under Ubuntu and the solutions to the Chinese garbled problem. Friends who need it can refer to it.

The following will introduce it to you. Mysql common commands and solving problems with Chinese garbled characters

Mysql system management commands

Log in to local Mysql

mysql -u username -p # 回车输入密码

or

mysql -u username -p passswd;

Log in to remote Mysql

mysql -h address -u username -p # 回车输入密码

or

 mysql -h address -u username -p passswd;

Change new password

mysql> use mysql;
mysql> update user set password=PASSWORD(‘newpasswd') where user='username';
mysql> flush privileges; #更新权限
mysql> quit; #退出

Add new user

mysql> grant select on database_name.* to user@address identified by ‘passwd'

ex: Add a user test1 with the password abc, allowing him to log in on any host and have query, insert, modify, and delete permissions on all databases.

mysql> grant select,insert,update,delete on . to root@localhost identified by 'mysql';
or mysql> grant all privileges on . to root@localhost identified by 'mysql';
mysql> flush privileges;

ex: If you do not want root to have a password to operate the data table in the database "mydb", you can issue another command to eliminate the password

mysql> grant select,insert,update,delete on mydb.* to root@localhost identified by '';

Delete user

mysql> delete from user where user='user_name' and host='address';
mysql> flush privileges;
mysql> drop database dbname; #删除用户的数据库

Database import and export (mysqldump)

Export all databases

mysqldump -u user -p -A > backup_file_path

Export data and data structure

mysqldump -u user -p database_name_need_to_backup > backup_file_path
ex00: export database mydb
mysqldump -h localhost -u root -p mydb > ./mydb.sql
ex01: export database mydb mytable
mysqldump -h localhost -u root -p mydb mytable > ./mytable.sql
ex02: export database mydb framework
mysqldump -h localhost -u root -p mydb –add-drop-table > ./mydb_stru.sql

Only export data but not export Data structure

mysqldump -u user -p -t database_name_need_to_backup > backup_file_path

Export Events in the database

mysqldump -u user -p -E database_name_need_to_backup > backup_file_path

Export the stored procedures and functions in the database

mysqldump -u user -p -R database_name_need_to_backup > backup_file_path

from the outside FileImport databaseIn

Use the "source" command

mysql > source path_of_backup_file

Use the "<" symbol

mysql -u root –p < path_of_backup_file

Mysql common instructions

View all databases

mysql> show databases;

Select the database to operate

mysql> use database_name;

View all tables under the current database

mysql> show tables;

Get the table structure

mysql> desc table_name;

or

 mysql> show columns from table_name;

Create a database

mysql> create database database_name;

Delete a database

mysql> drop database database_name;

Create a table

mysql> create table table_name( uid bigint(20) not null, uname varchar(20) not null);

Delete a table

mysql> drop table table_name;

SQL insert statement

mysql> insert into table_name(col1, col2) values(value1, value2);

SQL update statement

mysql> update tablename set col1='value1', col2='value2' where wheredefinition;

SQL query statement

mysql> select * from table_name where....... #(最复杂的语句)

SQL delete statement

mysql> delete from table_name where...

Add fields to the table structure

mysql> alert table table_name add column field1 date, add column field2 time ...

Delete the fields of the table structure

mysql> alert table table_name drop field1;

View the structure of the table

mysql> show columns from table_name;

Use of limit

mysql> select * from table_name limit 3; #每页只显示3行
mysql> select * from table_name limit 3,4; #从查询结果的第三个开始，显示四项结果。 此处可很好的用来作分页处理。

Sort the query results

mysql> select * from table_name order by field1, orderby field2; #多重排序

View character encoding

mysql> show variables like 'character%' ;

Ubuntu mysql Chinese garbled solution

Openconfig file

sudo vim /etc/mysql/my.cnf

Find [mysqld] and add

character-set-server = utf8

Restart mysql

/etc/init.d/mysql restart 或者 service mysql restart

The above is the detailed content of Detailed introduction to common Mysql instructions and Chinese garbled problems under Ubuntu. For more information, please follow other related articles on the PHP Chinese website!