Detailed introduction to common Mysql instructions and Chinese garbled problems under Ubuntu-Mysql Tutorial-php.cn

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

黄舟

Release： 2017-03-27 13:29:50

Original

1031 people have browsed it

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

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

Copy after login

mysql -u username -p passswd;

Copy after login

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

Copy after login

 mysql -h address -u username -p passswd;

Copy after login

Change new password

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

Copy after login

Add new user

mysql> grant select on database_name.* to user@address identified by ‘passwd&#39;

Copy after login

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 &#39;mysql&#39;;
or mysql> grant all privileges on . to root@localhost identified by &#39;mysql&#39;;
mysql> flush privileges;

Copy after login

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 &#39;&#39;;

Copy after login

Delete user

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

Copy after login

Database import and export (mysqldump)

Export all databases

mysqldump -u user -p -A > backup_file_path

Copy after login

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

Copy after login

Only export data but not export Data structure

mysqldump -u user -p -t database_name_need_to_backup > backup_file_path

Copy after login

Export Events in the database

mysqldump -u user -p -E database_name_need_to_backup > backup_file_path

Copy after login

Export the stored procedures and functions in the database

mysqldump -u user -p -R database_name_need_to_backup > backup_file_path

Copy after login

from the outside FileImport databaseIn

Use the "source" command

mysql > source path_of_backup_file

Copy after login

Use the "<" symbol

mysql -u root –p < path_of_backup_file

Copy after login

Mysql common instructions

View all databases

mysql> show databases;

Copy after login

Select the database to operate

mysql> use database_name;

Copy after login

View all tables under the current database

mysql> show tables;

Copy after login

Get the table structure

mysql> desc table_name;

Copy after login

 mysql> show columns from table_name;

Copy after login

Create a database

mysql> create database database_name;

Copy after login

Delete a database

mysql> drop database database_name;

Copy after login

Create a table

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

Copy after login

Delete a table

mysql> drop table table_name;

Copy after login

SQL insert statement

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

Copy after login

SQL update statement

mysql> update tablename set col1=&#39;value1&#39;, col2=&#39;value2&#39; where wheredefinition;

Copy after login

SQL query statement

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

Copy after login

SQL delete statement

mysql> delete from table_name where...

Copy after login

Add fields to the table structure

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

Copy after login

Delete the fields of the table structure

mysql> alert table table_name drop field1;

Copy after login

View the structure of the table

mysql> show columns from table_name;

Copy after login

Use of limit

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

Copy after login

Sort the query results

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

Copy after login

View character encoding

mysql> show variables like &#39;character%&#39; ;

Copy after login

Ubuntu mysql Chinese garbled solution

Openconfig file

sudo vim /etc/mysql/my.cnf

Copy after login

Find [mysqld] and add

character-set-server = utf8

Copy after login

Restart mysql

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

Copy after login

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!