mysql 命令备忘

一.常用基本命令

1.去重查询

select count(*) from tablename;

select count(distinct(pid)) from tablename;  //distinct 去重，不同的

2.查询A表中的字段，然后插入到B表中

Mysql备份。insert into table(c_serialid,c_password,c_value,c_gentime,c_usepid,c_usetime,c_useip) (select c_serialid,c_password,c_value,c_gentime,c_usepid,c_usetime,c_useip from tb_card_temp);

3.存储过程格式

DELIMITER $$

DROP PROCEDURE IF EXISTS `A` $$

CREATE DEFINER=`root`@`%` PROCEDURE `A`()

BEGIN

mysql导入sql文件命令。  SQL语句

END $$

DELIMITER ;

A为存储过程名字；$$ 也可换为;;

4.改表名

rename table old表名 to new表名;

mysql数据库备份、5.FROM_UNIXTIME(a_regtime)

 FROM_UNIXTIME 将查询出来的unix时间字段转换为常见的格式

6.MySQL字符集

MySQL字符集多种多样，下面为您列举了其中三种最常见的MySQL字符集查看方法，该方法供您参考，希望对您学习MySQL数据库能有所启迪。

1)查看MySQL数据库服务器和数据库MySQL字符集。

退出mysql命令。

mysql> show variables like '%char%';  

+--------------------------+-------------------------------------+------  

| Variable_name            | Value                               |......  

+--------------------------+-------------------------------------+------  

| character_set_client     | utf8                                |......   -- 客户端字符集  

MySQL binlog，| character_set_connection | utf8                                |......  

| character_set_database   | utf8                                |......   -- 数据库字符集  

| character_set_filesystem | binary                              |......  

| character_set_results    | utf8                                |......  

| character_set_server     | utf8                                |......   -- 服务器字符集  

| character_set_system     | utf8                                |......  

MySQL日志，| character_sets_dir       | D:\MySQL Server 5.0\share\charsets\ |......  

+--------------------------+-------------------------------------+------ 

2)查看MySQL数据表（table）的MySQL字符集。 

desc tablename或者

mysql> show table status from sqlstudy_db like '%countries%';  

mysql删除数据库命令。+-----------+--------+---------+------------+------+-----------------+------  

| Name      | Engine | Version | Row_format | Rows | Collation       |......  

+-----------+--------+---------+------------+------+-----------------+------  

| countries | InnoDB |      10 | Compact    |   11 | utf8_general_ci |......  

+-----------+--------+---------+------------+------+-----------------+------ 

3）、查看MySQL数据列（column）的MySQL字符集。

mysql登录数据库命令，

mysql> show full columns from countries;  

+----------------------+-------------+-----------------+--------  

| Field                | Type        | Collation       | .......  

+----------------------+-------------+-----------------+--------  

| countries_id         | int(11)     | NULL            | .......  

mysqldump命令、| countries_name       | varchar(64) | utf8_general_ci | .......  

| countries_iso_code_2 | char(2)     | utf8_general_ci | .......  

| countries_iso_code_3 | char(3)     | utf8_general_ci | .......  

| address_format_id    | int(11)     | NULL            | .......  

+----------------------+-------------+-----------------+--------  

二.mysqldump 格式命令

mysql备份数据库命令。1、导出数据库

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

mysqldump -uroot -p abc > abc.sql

2.导出表

mysqldump -u用户名 -p密码 数据库名 表名 > 名字.sql （导出表）

mysql数据库命令大全，mysqldump -u用户名 -p密码 数据库名 < 名字.sql （导入表到数据库中） 名字.sql表中的表名要是自己的.

3.只导出表结构

mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql

mysqldump -uroot -p -d abc > abc.sql

MySQL update语句。

4.导入数据库

1、首先建空数据库

mysql>create database abc;

2、导入数据库

MySQL查询语句、方法一：

（1）选择数据库

mysql>use abc;

（2）设置数据库编码

mysql>set names utf8;

（3）导入数据（注意sql文件的路径）

MySQL having？mysql>source /home/abc/abc.sql;

方法二：

mysql -u用户名 -p密码 数据库名 < 数据库名.sql

#mysql -uabc_f -p abc < abc.sql

建议使用第二种方法导入。

常见选项： 

docker日志收集方案。   shell> mysqldump [options] db_name [tbl_name ...]

           shell> mysqldump [options] --databases db_name ...

           shell> mysqldump [options] --all-databases

--all-databases, -A： 备份所有数据库

--databases, -B： 可以备份一个或者多个数据库。如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名

 导出数据库的时候加上这个选项，就有了创建数据库的语句。

MySQL select、  CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db_battlezone` /*!40100 DEFAULT CHARACTER SET utf8 */;

--force, -f：即使发现sql错误，仍然继续备份

--host=host_name, -h host_name：备份主机名，默认为localhost

--no-data, -d：只导出表结构，不含数据 

--password[=password], -p[password]：密码

--port=port_num, -P port_num：制定TCP/IP连接时的端口号

MySQL数据库启动命令，--quick, -q：快速导出

--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名

--user=user_name, -u user_name：用户名

--xml, -X：导出为xml文件

5.关于阿里云数据库导入导出

进入mysql的命令？    5.1存储过程

mysqldump -h地址   -u用户名 -p密码 --opt --default-character-set=utf8 --hex-blob 数据库名字 -ntd -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > 数据库名字_gc.sql

    5.2数据

mysqldump -h地址   -u用户名 -p密码  --opt --default-character-set=utf8 --hex-blob   --single-transaction 数据库名  表名 --skip-triggers > 表名.sql

注：-single-transaction 此选项在所有表名是innodb引擎的情况下可以使用，不会导致锁表的。

四.导出数据库的时候导出存储过程

mysqldump -uroot  -R dbname> dbname.sql

mysql -uroot -ppassword dbname< dbname.sql （先创建空库，后倒入）

3.授权

grant all privileges on *.* to 'haha'@'%' identified by '111111';   

flush privileges;

4.删除表中内容，不删除表  delete from joininfo where pid= ; 

5.仅仅导出表中的数据，不要表结构，例如tb_directpay.sql        

mysqldump -u root -t db_paycenter tb_directpay > tb_directpay.sql 

五.mysqldump 参数

1.查看全部存储过程

show procedure status;

2.Mysql查看存储过程和函数的具体内容

SHOW CREATE PROCEDURE EdwardCMS.checkAdminUser;

SHOW CREATE FUNCTION EdwardCMS.checkAdminUser;

今天使用mysqldump导出数据库，导入到新库的时候，发现没有存储过程。

默认居然不是完整的啊。

加上“-R”，就好了。

-R, --routines      Dump stored routines (functions and procedures).

procedures 程序  routines 日常活动

现在用：

mysqldump -uroot -ppassword -R dbname> dbname.sql

导入更方便：

mysql -uroot -ppassword dbname< dbname.sql

记得导入之前先建库。

CREATE DATABASE dbname DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

在部署某学院的网站时，发现一个错误:

1 FUNCTION config_get does not exist. 

找到代码一看，是conn.prepareCall("{call config_get(?,?,?)}");这一句出了错。上网查了查，这是在调用存储过程, 那么config_get就是存储过程名。到数据库里用show procedure status;看了看，一个存储过程也没有。也就是导数据库时没有导存储过程。

只导出存储过程的代码

1 # mysqldump -u 数据库用户名 -p -n -t -d -R 数据库名 > 文件名 

--no-create-db, -n

           This option suppresses the CREATE DATABASE statements that are otherwise included in the output if the --databases or

           --all-databases option is given.

       ·   --no-create-info, -t

           Do not write CREATE TABLE statements that re-create each dumped table.

               Note

               This option does not not exclude statements creating log file groups or tablespaces from mysqldump output; in

               MySQL 5.1.14 and later, you can use the --no-tablespaces option for this purpose.

       ·   --no-data, -d

           Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only

           the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump

           file).

所以上述代码表示仅仅导出函数和存储过程，不导出表结构和数据。但是，这样导出的内容里，包含了trigger。再往mysql中导入时就会出问题，错误如下：

ERROR 1235 (42000) at line **: This version of MySQL doesn't yet support ‘multiple triggers with the same action time and event for one table’

所以在导出时需要把trigger关闭。代码为

1 # mysqldump -u 数据库用户名 -p -n -t -d -R --triggers=false 数据库名 > 文件名 

这样导入时，会出现新的问题：

ErrorCode:1418

This function has none of DETERMINISTIC, NOSQL, or READS SQL DATA inits declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

解决方法是，在/etc/my.cnf中找到[mysqld],在它下面添加这样一行：

1 log-bin-trust-function-creators=1

六.导查询结果

1.mysql -hxx -uxx -pxx -e "query statement" db > file 

例如： 

mysql -h127.0.0.1 -uroot -p000000 -e"select * from a" test > 1.txt 

        host ip     user   password   query statement  database  filename 

这样会输出列名信息，如果不想输出列名信息： 

mysql -h127.0.0.1 -uroot -p000000 -N -e"select * from a" test > 1.txt 

        host ip     user   password   query statement  database  filename 

或 

mysql -hxxx -uxx -pxx 

select * from table into outfile 'xxx.txt'; 

例如： 

mysql -h127.0.0.1 -uroot -p000000 

select * from a into outfile '1.txt'; 

两种方法效果一样的 

第二种方式的mysql文档： 

SELECT [select options go here] INTO {OUTFILE | DUMPFILE} filename 

EXPORT_OPTIONS 

FROM table_references [additional select options go here] 

例如： 

mysql -h127.0.0.1 -uroot -p000000 

select * from a into outfile "1.txt" fields terminated by '\t' lines terminated by '\r\n' 

第一种方法和第二种方法的结合：使用 mysql -e执行导出到文件的sql语句 

mysql -hxx -uxx -pxx -e "query statement" db 

例如: 

mysql -h127.0.0.1 -uroot -p000000 -e"select * from a into outfile '1.txt' fields terminated by ',' lines terminated by '\r\n'" test 

如果不想输出列名信息： 

mysql -h127.0.0.1 -uroot -p000000 -N -e"select * from a into outfile '1.txt' fields terminated by ',' lines terminated by '\r\n'" test 

默认情况下， mysql -e导出的文件，列是用"\t"分隔，行是用"\r\n"分隔(dos)，行是用"\n"分隔(unix 

追加一种方式： 

select col002,col005,col004,col008 into outfile 'e:/mysql/i0812.txt' fields terminated by '|' lines terminated by '\r\n' from a where col003 in (select col001 from qdbm) order by col005;

2.用mysqldump把数据成导成sql格式的：

mysqldump  -uroot  -t db_webpaycenter_cq   tb_payrecords --where="money=870" >870.sql  (--where/-w)

1.用mysql执行

格式: mysql -uusername -ppassword -e "SQL语句" >/tmp/a.txt  注：sql语句用单引号或者双引号括起来；多个语句用分号隔开。//输出重定向

mysql -u root -p -e 'select g_gameid from tb_gamedetail' db_lobbyv2 >1.txt

mysql -uroot -ppassword -e "select id from databases.table;">test     

mysql -u root -e ' SELECT a_pid,DATE(FROM_UNIXTIME(a_regtime))FROM db_lobbyv2.tb_account WHERE DATE(FROM_UNIXTIME(a_regtime))>=20140901 AND DATE(FROM_UNIXTIME(a_regtime))<=20140930;' >a.txt

show variables like 'max_connections';

show global status like 'Max_used_connections';

2.用mysqldump 

七.load

从客户端使用绝对路径load数据

LOAD DATA LOCAL INFILE '/import/data.txt' INTO TABLE db2.my_table;

八.数据库密码

1.改密码

wolfplus@ubuntu:/usr/share/mysql$ mysqladmin -u root -p password  '123'

Enter password: 

5.开始没有密码，设置密码

mysqladmin  -u root password "111111"  

修改mysql密码

mysqladmin -u用户名 -p旧密码 password 新密码   mysqladmin -u root -p111111 password 123456

mysqladmin  -u root -p  password 111111 回车后输入旧密码

或进入mysql命令行SET PASSWORD FOR root=PASSWORD("root");

mysql>use mysql;

mysql>update user set password=password('123456') where user='root'; (注:'new pw' 就是你的新密码，其它照抄;

另一种写法:update mysql.user set password=password('new pw') where user='root';

2. Linux下MYSQL 清除密码，重设密码方法

1.先以root用户登录系统;

2.关闭MYSQL服务或进程：service mysqld stop / killall mysqld;

3.特权启动MYSQL: /usr/local/mysql/bin/mysqld_safe --skip-grant-tables & or mysqld_safe --skip-grant-tables --skip-networking & (注：参数--skip-grant-tables为跳过授权表；--skip-networking为不监听TCP/IP连接);

4.然后可以不需要密码进入MySQL:mysql -u root -p（要求输入密码时直接回车即可）;

mysql>use mysql;

mysql>update user set password=password('new pw') where user='root'; (注:'new pw' 就是你的新密码，其它照抄;

另一种写法:update mysql.user set password=password('new pw') where user='root';

5.更新下:

mysql>flush privileges;

mysql>quit;

6.结束刚启动过的MYSQL进程：killall mysqld or killall -9 mysqld.

7.正常启动MYSQL: service mysql start

8.用'new pw'登入：mysql -u root -p.

OK......

>>同时也可以这样做：

首先，修改MySQL的登录设置：

# vi /etc/my.cnf

在[mysqld]的段中加上一句：skip-grant-tables

例如：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-grant-tables

保存并且退出vi。

然后，重新启动mysqld，进去修改密码，同上述方法；

最后，将MySQL的登录设置修改回来:

# vi /etc/my.cnf

将刚才在[mysqld]的段中加上的skip-grant-tables删除

保存并且退出vi。

再次登入MYSQL,一样OK。