mysql 常用命令（二)

1、创建空表

mysql> CREATE TABLE `test` (   `id` int(4) NOT NULL AUTO_INCREMENT,   `name` char(20) NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

AUTO_INCREMENT：自增，下次插入数据，会自动增加ID的值，必须配合主键来用

2、查看表结构

mysql> desc student;

3、查看所有的表

mysql> show tables;

mysql> show tables;

mysql> show create table student\G

5、删除表

mysql> drop table student;

5.1、删除表内所有内容

mysql> delete from test;

6、查看表内容

(1) 查看所有内容
mysql> select * from test;

(2) 查看指定字段内容
mysql> select id,name from test;

(3) 根据条件查看内容
mysql> select * from test where id=2 or name='bb'; 

7、查看是否调用索引

mysql> explain select * from test where name='oldboy'\G

8、创建索引

创建主键索引：
alter table student change id id int primary key auto_increment; 
删除主键索引（主键列不能自增）：
alter table student drop primary key;
创建普通索引：
alter table student add index index_dept(dept);
根据列的前n个字符创建普通索引
create index index_dept on student(dept(8));
根据多个列创建联合普通索引
create index ind_name_dept on student(name,dept);
根据多个列的前n个字符创建联合普通索引
create index ind_name_dept on student(name(8),dept(10));
创建唯一索引
create unique index uni_ind_name on student(name);
查看索引
desc student;
show index from student;
删除普通索引与唯一索引：
alter table student drop index index_dept;
drop index index_dept on student;
查看表记录唯一值的数量：
select count(distinct user) from mysql.user;
select count(distinct (user,host)) from mysql.user;

9、修改表数据（cc改oldboy）

mysql> update test set name='oldboy' where name='cc';  

10、对于不重要的慢查询语句可以直接杀死

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 73
   User: root
   Host: localhost
     db: student
Command: Query
   Time: 0
  State: NULL
   Info: show full processlist
1 row in set (0.00 sec)

mysql> kill 73;

11、查看错误日志

mysql> show variables like '%log_error%';

12、binlog日志

binlog用于记录（insert,update,delete,create,drop,alter）相关信息。用于主从复制，及增量恢复。

(1) 开启bin-log：
[root@Oldboy ~]# grep "log-bin" /etc/my.cnf 
log-bin=mysql-bin

(2) 查看bin-log日志
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON                   |                   记录binlog开关
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON                 |                   临时不记录binlog开关（增量恢复）
+---------------------------------+-------+
3 rows in set (0.00 sec)

(3) 查看binlog日志保存天数
mysql> show variables like "expire_logs_days";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7         |
+------------------+-------+
1 row in set (0.00 sec)

13、删除binlog

(1) 设置参数自动删除
expire_logs_days = 7  #<==删除7天前的日志

(2) 从'mysql-bin.000001'删到'mysql-bin.000004'
mysql> purge binary logs to 'mysql-bin.000004';
Query OK, 0 rows affected (0.03 sec)

#查看当前剩余binlog日志
mysql> system ls -l /data/3306/mysql-bin* 
-rw-rw---- 1 mysql mysql   126 8月  21 16:31 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql   126 8月  21 16:32 /data/3306/mysql-bin.000005
-rw-rw---- 1 mysql mysql 15881 8月  28 19:16 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql    84 8月  28 19:26 /data/3306/mysql-bin.index
(3) 按照时间删除
mysql> PURGE MASTER LOGS BEFORE '2016-08-28 13:00:00';
Query OK, 0 rows affected (0.02 sec)

14、binlog三种模式

binlog三种模式
(1) Statement Level（默认语句模式）
优点：数据库的所有重复操作类型语句，只会在binlog记录一次
缺点：导致主从不一致

(2) Row Level（行级模式）
优点：binlog记录数据很细（逐行），主从一致
缺点：binlog会记录每次一的操作记录，占用大量磁盘空间，降低磁盘性能

(3) Mixed Level（混合模式）
优点：记录binlog日志，使用语句模式
          推荐主从同步使用Row-level模式

15、修改表名（test改oldboy）

mysql> rename table test to oldboy;              （方法一）
mysql> alter table test rename to oldboy;      （方法二）

16、为新表添加字段
添加字段格式：alter table 表名 add字段 类型 其他;

(1) 按顺序添加字段
mysql> alter table test add age char(4);
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          
+-------+----------+------+-----+---------+----------------+
| id        | int(4)      | NO   | PRI  | NULL    | auto_increment 
| name  | char(20) | NO   |        | NULL     |                |
| age     | char(4)   | YES   |        | NULL     |                |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

(2) 添加字段在第一列
mysql> alter table test add class char(20) first;
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| class   | char(20) | YES   |        | NULL    |                |
| id        | int(4)      | NO   | PRI | NULL    | auto_increment 
| name  | char(20) | NO   |       | NULL    |                |
| age     | char(4)   | YES   |       | NULL    |                |
+-------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

(3) 添加字段在name后面
mysql> alter table test add sex char(10) after name;
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| class   | char(20) | YES   |       | NULL    |                |
| id        | int(4)      | NO   |PRI | NULL     | auto_increment 
| name  | char(20) | NO   |       | NULL     |                |
| sex      | char(10) | YES   |       | NULL     |                |
| age     | char(4)  | YES    |       | NULL     |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

(4) 删除字段
mysql> alter table test drop age;

17、插入内容

(1) 指定字段插入
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| class | char(20) | YES  |     | NULL    |                |
| id    | int(4)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20) | NO   |     | NULL    |                |
| sex   | char(10) | YES  |     | NULL    |                |
| age   | char(4)  | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into test(class,id,name,sex,age) values('yiban',1,'WangLi','gril',24);
mysql> select * from test;
+-------+----+--------+------+------+
| class   | id  | name   | sex  | age  |
+-------+----+--------+------+------+
| yiban  |  1 | WangLi | gril | 24   |
+-------+----+--------+------+------+
1 row in set (0.00 sec)

(2) 批量插入
mysql> insert into test values('erban',3,'WangHu','man',30),('sanban',4,'Lilin','girl',23);
mysql> select * from test;
+--------+----+--------+------+------+
| class  | id | name   | sex  | age  |
+--------+----+--------+------+------+
| yiban   |  1 | WangLi   | gril     | 24   |
| NULL    |  2 |                 | NULL | 25   |
| erban   |  3 | WangHu | man  | 30   |
| sanban |  4 | Lilin         | girl    | 23   |
+--------+----+--------+------+------+
4 rows in set (0.00 sec)

18、创建索引

(1) 为name创建索引
mysql> alter table test add index ind_name(name);
(2) 查看索引
mysql> show index from test\G
*************************** 2. row ***************************
        Table: test
   Non_unique: 1
     Key_name: ind_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 2
     Sub_part: NULL
     Packed: NULL
     Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

(3) 查看是否调用索引
mysql> explain select * from test where name="oldboy"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: ind_name             可能调用的索引
          key: ind_name                    实际调用的索引
      key_len: 20
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

19、修改表内容

mysql> update test set name='oldgirl' where name='oldboy' and id=2;
update 表名  set  字段='新内容'  where 字段='旧内容'  and   字段='条件'