mysql常用sql语句

1. Mysql 管理命令 帮助？ HELP

1.1查看mysql状态信息

show processlist; 查询当前连接进程
show status  查看当前会话的数据库状态信息
show global status; 查看整个数据库运行状态信息，很重要的分析并做好监控
show processlist； 查看正在执行的sql语句，
show full processlist  查看正在执行的完整sql语句，
set global key_buffer_size=32777218   不重启数据库调整参数，临时生效
show variables;查看数据库的参数信息，如my.cnf中参数是否生效

1.2修改mysql字符集为UTF-8的方法：

show variables like "%char%"

在/etc/my.cnf对应如下配置段加入相应命令
[clinet]
字段加入default-character-set=utf8   客户端生效
[mysqld]
字段加入character-set-server=utf8
[mysql]
字段里加入default-character-set=utf8   本地生效
然后重启mysql服务


查询字符集文件
show character set;

1.3解决mysql乱码的问题？

方法1：
1. 检查建表语句，判断字符集类型show create table test_t0;
2. 设置字符集set names latinl;

方法2：
导入数据的时候指定符字符
mysql -uroot -p123456 --default-character-set=latin1 discuz</backup/discuz.sql

方法3：
在my.cnf文件中加入字符集
[mysqld]
default-character-set=latinl   适合5.5以前
character-set-server=latinl    适合5.5以后

不乱码的思想，linux ,客户端，服务器端，库，表，程序都统一字符集,中英文环境尽量使用UTF8

1.4 mysql的历史命令保护

vi /root/.mysql_bistory   # 创建mysql历史命令
ln -s dev/noull > mysql_history   # 保户命令历史，创建成空文件

系统历史命令清除  bashrc history -c

1.5 Mysql远程管理 mysql -uroot -p -h 192.168.1.100

mysql交互式   mysql -uroot -p123456 -e “show databases;”
Mysql安全优化
删除空用户和多余用户  drop user 'username'@'host'
删除空用户     drop user ' '@'host'   

1.6 mysql锁表

5.1版本： flush tables with read lock
5.5版本： flush table with read lock

解锁：
解锁跟系统的两个变量值有关，自动解锁时间
Wait_timeout = 28800
Interactive_timeout = 28800

1小时默认3600秒  8小时*3600秒=28800秒    24小时共86400秒

Show variables like “%timeout%”

1.7 数据库文件路径

mysql> show variables link '%datadir%';

2.Mysql的升级

官方升级网址
https://dev.mysql.com/doc/refman/5.5/en/upgrading.html

As part of the "Logical Upgrade", I had to add the --flush-privileges option when dumping because mysql didn't recognise the newly created users when restoring views. Additionally I added --triggers for completeness.
mysqldump --add-drop-table --routines --events --triggers --flush-privileges --all-databases --force -uroot -p > mysql_backup.sql

3. Mysql SQL语句（DDL DML DCL 语言）

**SQL语句常见的分类**

DDL   数据定义语言（CREATE ALTER DROP）
DML   数据操作语言（SELECT INSTER DELETE UPDATE）
DCL   数据控制语言（GRANT  REVOKE  COMMIT ROLLBACK）

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

一：数据查询语言（DQL:Data Query Language）： 其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。 二：数据操作语言（DML：Data Manipulation Language）： 其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。 三：事务处理语言（TPL）： 它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。 四：数据控制语言（DCL）： 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。 五：数据定义语言（DDL）： 其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。 六：指针控制语言（CCL）： 它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作

mysql 的system命令
在命令行下，可以通过执行system命令，调用bash环境，比如
>system ls /data/  查看data目录下的文件

3.1 Mysql 数据库管理

select查询命令  select *();
查看当前库
mysql> select database();

查看当前用户
mysql> select user();   

查看当前时间
mysql> select now();   

切换当前库  use databases;
mysql> use mysql;


创建数据库  Create Databas
create Database Discuz  character set utf8
create database if not exists db_name   
如果不存在就创建，不报错

# 查询数据库创建的历史命令
show create database discus;

查询字符集文件
show character set;

# 创建数据库并指定字符集
create database discuz default character set gbk collate gbk_chinese_ci;
create database discuz default character set utf8 collate utf8_general_ci;

数据库重命名
RENAME database olddbname TO newdbname

查看数据库  Show Databases
show databases;
show databases like ‘my%’;

查询数据库创建的历史命令
show create database discus;


删除数据库  Drop Database
drop datebase [if exists] db_name;  
注：删除无法恢复
Drop database bbs; # 删除bbs数据库

3.2 Mysql 数据表操作

创建表：Create table tb_name (字段名 数据类型)
mysql> create table tutorials_tbl(
		->    tutorial_id INT NOT NULL AUTO_INCREMENT,
		->    tutorial_title VARCHAR(100) NOT NULL,
		->    tutorial_author VARCHAR(40) NOT NULL,
		->    submission_date DATE,
		->    PRIMARY KEY ( tutorial_id )
-> );
+-----------------+--------------+------+-------+------------+----------------+
| Field           | Type         | Null |   Key  | Default    | Extra         |
+-----------------+--------------+------+--------+------------+----------------+
| torial_id       | int(11)       | NO   |  PRI  | NULL    | auto_increment   |
| tutorial_title    | varchar(100)  | NO   |       | NULL    |                |
| tutorial_author  | varchar(40)   | NO   |       | NULL    |                |
| submission_date | date         | YES  |       | NULL    |                |



设置主键：
create table passwd2 ( 
id int(10) auto_increment PRIMARY KEY,
name char(20),
sex char(20)
);



例：create table students(name char(20) null,age char(10) null,sex char(10) null);
insert into students values('zhonglaing','23','man');

*************************
查询表的建表语句
show create table student\G;
*************************
send command to mysql server ,display result vertically 发送命令到mysql server，垂直显示结果


生产创建表语句
CREATE TABLE `subject_comment_manager` (
`subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键',
`subject_type` tinyint(2) NOT NULL COMMENT '素材类型',
`subject_primary_key` varchar(255) NOT NULL COMMENT '索材的主键',
`subject_title` varchar(255) NOT NULL COMMENT '索材的名称',
`edit_user_nick` varchar(64) default NULL COMMENT '修改人',
`edit_user_time` timestamp  NULL default NULL COMMENT '修改的时间',
`edit_comment` varchar(255) default NULL COMMENT '修改的理由',
`state` tinyint(1) NOT NULL  default '1' COMMENT '0代表关闭，1代表正常',
PRIMARY KEY (`subject_comment_manager_id`),
KEY `IDX_PRIMARYKEY`(`subject_primary_key`(32)),
KEY `IDX_SUBJECT_TITLE` (`subject_title`(32)),
KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)
);

查看表： show tables
查看库中的表：  show tables;
查年表中的结构： desc tabale_name;




删除表：	Drop table tb_name
drop table [if exists] tb_name;
drop table bbs; # 删除bbs表


修改表：Alter table tb_name [ add|change|modify ]
alter table tb_name;
修改 modify    改属性  改数据类型
改变 change    改值    改名称
增加 add
删除 drop
增加：add


语法格式： alter table students add 字段名varchar(100)  添加字段名 指定数据类型varchar

实例: alter talbe students add telphone int(20);  
# 增加telphone列，
alter talbe students add telphone int(20) first;  			 
# 在第1列增加telphone列，
alter talbe students add telphone int(20) after name ;  # 在name列后增加telphone列，	
  	

修改值：change
例：  alter table students change “原字段1“ ”现字段2“ varchar(100)  after name;	
修改”原字段1”的字段名和类型，添加到Nmae字符之后
要指定源字段，新字段，新类型
alter table user change age ages varchar(100);



修改属性：modify
例： alter table users modify tel text(10); 
修改tel字段的字段类型  

删除列：　drop
例：alter table user drop id;
#删除user表中的id列



 清空表：delete from tables | truncate table 
 delete 是逻辑删除，一行一行删
 truncate 物理删除，清空物理文件

清空表的内容
delect from jf_t1;

清空表的内容
truncate table user;

制一张表 create tables tb_name select * from tb_name
复制一张表和数据：
create table new_table select * from old_table;

复制一张表不复制数据
create table new_table select * from old_table where 0 ; 

 只复制表结构到新表
 CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
 或者
 CREATE TABLE 新表 LIKE 旧表  

 复制表结构及数据到新表
 CREATE TABLE 新表 SELECT * FROM 旧表 

 复制旧表的数据到新表(假设两个表结构一样) 
 INSERT INTO 新表 SELECT * FROM 旧表  

 复制旧表的数据到新表(假设两个表结构不一样)
 INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表


 重命名表  rename table 
 Rename TABLE tb_name TO new_tb_name
 ？ alter table rename tb_name to new_table   重命名表


 表结构 desc table
    mysql> desc mysql.user;
    -----------------------+-----------------------------------+------+-----+---------+-------+
    | Field                 | Type                              | Null | Key | Default | Extra |
    +-----------------------+-----------------------------------+------+-----+---------+-------+
    | Host                  | char(60)                          | NO   | PRI |         |       |
    | User                  | char(16)                          | NO   | PRI |         |       |
    | Password              | char(41)                          | NO   |     |         |       |
    | Select_priv           | enum('N','Y')                     | NO   |     | N       |       |

3.3 Mysql 数据管理

 插入数据：Insert into tb_name …  values …
	指定字段插入：
insert  into  tb_name （col1,col2,...）values  ('string',num，...);
插入数据 （字段名）  VALUES值 （‘字符串’， 数值1-9）
字符串要单引  数值不需要

单行插入
例： insert  into  tb_name （id,name）values  (‘1’,’zhongliang’)
可同时插入多行数据

多行插入
例： insert into student (name,sex) value ('zhongliang'.‘man’)(‘xiaoliang’,'woman'）;
插入前先查询表的结果DESC tb_name  看哪些是必须插入值的
			 # 未指定的字段代表是允许为null的，可以不在字段位写，按顺序写上不允许为空的字段


不指定字段插入

不指定字段插入，代表全部都插入值
name	age	sex	telnet

例：insert into students values ('zhongliang',20,'m',249765270);

语句插入1000条
create database hd;
create table techer (name char(20),tel char(20));

for i in `seq 10000`;do mysql -e "use hd;insert into techer (name,tel) values ('zhonglaing','123456789')";done




查询内容：select * from table; 


where条件：and   or  <   >
order排序：
    order by  id asc  # 按id正序排序
order by  id desc # 按id倒序排序



显示表的所有字段
describe user;

select 字段  frome tb_name where 条件1 [and | or] 条件2

select count(*) from user;  统计多少行

select * from user limit 10; 看10行  # 在生产环境中不要查所有，应为当上千条数据时会卡死

select * from user where 列名>30;   #  查询某列的值大于30的
select * from user where 列名 like ‘%abc%’;   # 查询某列的值包含匹配abc字符的所有内容


显示user表的第一行内容
select * from user limit 1;
select * from user limit 1,3;  # 查看第1行第3行内容
select * from user where name=""  limit 10\G;
select name from user where nam="";

内容匹配查询
select * from user name link "%zhong%";
select * from user name link "%zhong%" and name=xxx ;

排列显示，
select * from user where User='root' \G;  


多表查询

select 表1.列1 , 表2.列2 , 表3.列3 from 表1，表2，表3 where 条件1 and 条件2

select user.id,student.name,address.tel from user,student,address where


修改数据：Update tb_name Set 
 update tb_name set 列名=“新值”  where  列名=“value”;    # id
  
  例：update student set tel=249765270 where name=zhongliang;
  # 修改zhongliang用户的tel电话
  
  
  update mysql.user set host='localhost' where host='127.0.0.1' and user='root'
  
  删除数据：Delete From tb_name where
  delete from tb_name where id='value';
  
  
  例：delete form student where tel=249765270

3.4Mysql 数据库备份还原

Xtranbackup　　开源物理备份工具

备份分为逻辑备份和物理备份
逻辑备份：通过mysqldump备份过去
物理备份：通过将mysql目录的数据库文件拷贝过去


Mysqldump参数：
-A 备份所有库
-B 备份多个库 此参数使用后，自带create database语句，在恢复时，可以事先不建数据库就可忧复  如：mysqldump -uroot -p123456 </tmp/backup.sql
-x 锁表
-l  只读锁表
-C 压缩传输
--compact  去掉注释输出 用于调试
-d  备份表结构
-F  备份数据库时，切割blog日志，目的是防止备份数据与binlog重复，备份时记住binlog日志文件序号
--master-data=1    自动查找binlog日志位置节点 不需要手动入切割blog日志 与-F二选一    
=1 ;  在binlog中添加一条不带注释的change master的post节点和binlog文件名，用于在slave上导入时，能自动记录post点和binlog文件位置，用于同步之后的binlog文件

=2： 在binlog中添加一条带注释的change master的post节点和binlog文件名




 Mysql 全量备份与增量备份



全量备份：

全量部份就是将数据库完整备份，mysql -uroot -p -A -B >/backup/mysql.sql

innodb 引擎备份：
mysqldump -uroot -p --default-character-set=gbk --single-transaction -F -B discuz | gzip 	>/databackup/mysql.sql.gz

innodb 引擎备份：
mysqldump  -uroot -p --default-character-set=gbk --lock-all-tables -F -B discuz | gzip 		>/databackup/mysql.sql.gz






企业场景全量和增量的频率是怎么做的呢？
1. 中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时要锁表
2. 单台数据库，如何增量，用rsync（配合定时任务）把bin-log推送到远端备份服务器上
例：rsync -avz mysql-bin* rsybc_backup@10.204.1.100::backup --password-file=/etc/rsync.passwd
3. 大公司周备，每周六00点一次全备，下周日-下周六00点前都是增量
4. 一主多从，会有一个从库做备份，延迟同步





mysql的备份什么场景下使用？
1. 迁移或升级数据库的时候
2. 添加从库
3. 主库勿删除时，需要备份还原
4. 跨机房灾备，需要备份



增量恢复全过程（针对前端可以暂停写入）

1. 先对数据库进行锁表，防止新数据写入
2. 找到全备和增备文件
grep -i "change" /backup/mysql.sql  找到全备最后的binlog的pos点位置，根据位置，找到增量的binlog日志文件
3. 如果前端不能锁主库，这时要马上刷新binlog mysqladmin -uroot -p flush-logs 生成新的Binlog文件，这样可以判
断之后插入的数据会生成在新的binlog日志文件中
4. 将需要增量恢复的binlog日志文件移动到新的目录，以防二次损坏
5. 执行mysqlbinlog mysql-bin.0000014 >/databackup/binlog14.sql  将binlog日志生成sql语句
6. vi /databackup/binlog14.sql 将勿操作语句删除，比如drop database之类的


增量恢复全过程（针对前端不可以暂停写入） 不停库不推荐
1. 立即断开从的sql_threed线程，防止从读取relay-log日志，
2. 将主mysql的binlog进行分割 -F
3. 将主mysql的最新全部和出故障前的binlog日志进行恢复到从（前提要把日志中出问题的语句先删除）
4. 然后将从提升为主mysql
5. 将原主mysql被切割的后面用户写入的新数据的binlog日志恢复到现新的主mysql库
6.

缺点：可能导致后期写入的数据不能插入，因为在你恢复的时候，有人又写入数据，导致主键冲突
最好的方案就是，在恢复最后写入数据时，暂停最短时间导入新数据。


生产场景2：

	为了防止数据被勿删除，有些公司针对主从同步设置了某一台mysql从廷迟同步30-60分钟，这样做的目的是为了防止主数据库被执行勿删除时，从能保证数据完整并通过把主binlog增量部份的日志恢复到此从上，接替主的位置。



生产场影mysql数据恢复思路？

表重要的前题下：
1.	停服务，挂维护页面  防止前端写入数据，保证数据一致性。
2.	同时对故障的数据做一次备份，以防万一 
3.	恢复内存全量备份  根据post点位置，将相应的binlog日志恢复
4.	启动测试

Mysql备份数据库

	备份所有数据库

mysqldump -uroot -p  -B -A --events > all.sql    # 备份服务器中所有的库

	备份mysql 使用root用户，密码123456 备份到/tmp/
mysqldump -uroot -p123456  -B mysql  > /tmp/mysql2016.sql

	数据库压缩备份 
mysqldump  -uroot -p123456  discuz | gzip > backupfile.sql.gz
解压：gzip -d backupfile.sql.gz

	同时备份多个MySQL数据库 
mysqldump -uroot -p123456 -B databasename1 databasename2 databasename3 > multibackupfile.sql

	仅仅备份数据库结构 
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql


	跨主机备份
使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：

mysqldump -uroot -p sourceDb | mysql –uroot –p  --host=192.168.1.100 -C targetDb
-C指示主机间的数据传输使用数据压缩
目标主机必须gran授权允许源主机连接


mysqldump: Got error: 1016: Can't open file: './BBS/pre_home_feed.frm' (errno: 24) when using LOCK TABLES
mysqldump -uroot -p123456 BBS --lock-tables=false > /data/bbs.sql

mysqldump -uroot -p123456 -A -B --events --master-data=2 -x > /backup/all.mysql



企业常规备份 myisam
mysqldump -uroot -p123456 -A -B --master-data=2 -x dicuz|gzip >/backup/all.sql.gz

企业常规备份 inodb
mysqldump -uroot -p123456 -A -B --master-data=2 -x --single-transaction dicuz|gzip >/backup/all.sql.gz


			for myisam
			mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --envents --hex-blob >/data/mysqlall.sql

			for innodb
			mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --envents --hex-blob>/data/mysqlall.sql




 Mysql基于slave备份数据

		使用tar,copy,rsync来备份
		在slave的my.cnf增加log-slave-updates配置，使slave也产生binlog日志文件



  Mysql恢复数据库
			注意: mysql在恢复数据库的时候，最好像原数据库清除重新初始化，并重新change master，记得要重启数据库同步才会生效，


				恢复库
				mysql> mysql –uroot  zhongliang < /tmp/zhongliang.sql;

				恢复数据库 先创建数据库名，再恢复

				还原压缩的MySQL数据库 
				gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename





waiting to finalize termination
线程停止时发生的一个很简单的状态