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: 73User: rootHost: localhostdb: student
Command: QueryTime: 0State: NULLInfo: 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: testNon_unique: 1Key_name: ind_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 2Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment:
2 rows in set (0.00 sec)(3) 查看是否调用索引
mysql> explain select * from test where name="oldboy"\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: testtype: ref
possible_keys: ind_name 可能调用的索引key: ind_name 实际调用的索引key_len: 20ref: constrows: 1Extra: 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 字段='条件'
