CREATE TABLE 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
)ENGINE=innodb DEFAULT CHARSET utf8;

create table student(
    id int not null auto_increment primary key,
    name varchar(50) not null,
    age int not null,
    sex enum(''男'',''女'') not null default ''男'',
    salary double(10,2) not null
)engine=innodb default charset=utf8;

not null :表示此列不能为空
auto_increment :表示自增长,默认每次增长+1
注意:自增长只能添加在主键或者唯一索引字段上

primary key :表示主键(唯一且不为空)
engine =innodb :表示指定当前表的存储引擎
default charset utf8 :设置表的默认编码集

create table tb1(
    nid int not null auto_increment primary key,
    num int null
)

create table tb1(
    nid int not null,
    num int not null,
    primary key(nid,num)
)

create table tb1(
    nid int not null auto_increment primary key,
    num int null
)
或
create table tb1(
    nid int not null auto_increment,
    num int null,
    index(nid)
)
注意：
     1、对于自增列，必须是索引（含主键）
     2、对于自增可以设置步长和起始值
    show session variables like ''auto_inc%'';
    set session auto_increment_increment=2;
    set session auto_increment_offset=10;

    show global variables like ''auto_inc%'';
    set global auto_increment_increment=2;
    set global auto_increment_offset=10;

create table tb2 select * from tb1; 
ps:主键自增、索引、触发器、外键，不会被复制

create table tb2 like tb1; 
ps: 数据、触发器、外键，不会被复制

drop table 表名;

truncate table 表名;

alter table 表名 add 字段名 类型 约束;

alter table student add age int not null default 0 after name;

方式一: 
alter table student modify 字段 varchar(100) null; 
方式二: 
alter table student change 旧字段 新字段 int not null default 0;

alter table student drop 字段名;

rename table 旧表名 to 新表名;

alter table student add primary key(字段,"多个","间隔");

alter table student drop primary key;

alter table student modify id int not null,drop primary key

alter table 从表 add CONSTRAINT fk_test foreign key 从表(字段) REFERENCES 主表(字段);

alter table 表 drop foreign key 外键名称;

alter table 表 alter 字段 set default 100;

alter table 表 alter 字段 drop default;

select 字段(多个以","间隔) from 表名;

select name,sex from student;

select * from student;

desc 表名;

desc student;

show create table student;

2、MyISAM   这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁。

3、MyISAM   类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

　　（1）静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样mysql就会自动使用静态myisam格式。

        　　使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是用空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

　　（2）动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低.

　　（3）压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。

　　　　压缩方式参考官方文档: https://dev.mysql.com/doc/refman/5.6/en/myisampack.html

4、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

InnoDB存储引擎

create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
 
#看一下三个存储引擎创建的 表文件
 t1.frm  t1.ibd 
 t2.MYD  t2.MYI  t2.frm 
 t3.frm
#细心的同学会发现最后的存储引擎只有表结构，无数据
#memory，在重启mysql或者重启机器后，表内数据清空　　

1.查看是否安装sql
    查询命令：rpm -qa|grep -i mysql
    删除命令：rpm -e RPM 软件包名（该名字是一上一个命令查出来的名字）
            rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
2.查看mysql是否安装成功
    ps -ef|grep mysql
3.查看mysql安装时创建的mysql用户和用户组
    cat /etc/passwd|grep mysql
    cat /etc/group|grep 
    也可以执行：mysqladmin --version
4.给mysql设置登录密码
    /usr/bin/mysqladmin -u root password hadoop
5.自启动mysql服务
    chkconfig mysql on 设置开机自启动
    chkconfig --list|grep mysql
    ntsysv
6.在linux下查看安装目录 
    ps -ef|grep mysql
7.修改配置文件位置
    当前5.5版本：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
    当前5.6版本：cp /usr/share/mysql/my-default.cnf /etc/my.cnf
    重新启动服务：service mysql start
8.修改字符集和数据存储路径
　　　　show variables like ''character%'';
　　　　show variables like ''%char%'';
　　　　注：默认的是客户端和服务器端都用了latin1 ，所以会乱码

一、二进制日志log-bin
　　作用：主从复制
二、错误日志 log-err
　　默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息
三、慢查询日志log
　　默认关闭，记录查询的sql语句，如果开启会降低sql的整体性能，因为记录日志也是需要消耗系统资源。
四、数据文件
　　frm文件：存放表结构
　　myd：存放表数据
　　myi：存放表索引
　　两个系统：
　　　　windows： D:MySQLServer5.5\data  目录下可以挑选很多库
　　　　linux： 查看当前系统中的全部库后再进去
　　　　　　　   默认路径：/var/lib/mysql
五、linux配置路径：/etc/my.cnf
　　widows配置路径： my.ini

看你的mysql现在已经提供什么存储引擎
　　show variables like ''%storage_engine%'';
　　show engines;
　　注：默认和当前都使用的    InnoDB
MyISAM和InnoDB的区别

 阿里巴巴 淘宝用那个

mysql> show engines\G;
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

# 创建指定引擎的表
mysql> create table t2(id int) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

# 查看创建的过程
mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

# 其他类型的引擎方式
mysql> create table t3(id int) engine=memory;  # 存在内存中，只有表结构，没有数据
Query OK, 0 rows affected (0.00 sec)

mysql> create table t4(id int) engine=blackhole; # 存入的数据就没啦
Query OK, 0 rows affected (0.00 sec)

mysql> create table t5(id int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;  # 存于内存，重启mysql就啦
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t4;  # 数据没有啦，黑洞
Empty set (0.00 sec)

mysql> select * from t5;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

# 重启mysql 查看t3的数据
Usage: /etc/init.d/mysql start|stop|restart|reload|force-reload|status
leco@leco:/etc/mysql/mysql.conf.d$ /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
leco@leco:/etc/mysql/mysql.conf.d$ mysql -uroot -pleco
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ''help;'' or ''\h'' for help. Type ''\c'' to clear the current input statement.

mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
| t4            |
| t5            |
+---------------+
5 rows in set (0.00 sec)

mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from t3;  # 发现数据么有啦
Empty set (0.00 sec)

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
4. []数据中是可有可无的
5. 每行有逗号，除了最后一条数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database db2 charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use db2;
Database changed
mysql> create table t1(
    -> id int,
    -> name varchar(50),
    -> sex enum(''male'',''female''),
    -> age int(3)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into t1 values(1,''cmz'',''male'',18);  # 单条数据插入
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2,''leco'',''female'',10),(3,''loocha'',''male'',8);  # 多条数据插入
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+--------+--------+------+
| id   | name   | sex    | age  |
+------+--------+--------+------+
|    1 | cmz    | male   |   18 |
|    2 | leco   | female |   10 |
|    3 | loocha | male   |    8 |
+------+--------+--------+------+
3 rows in set (0.00 sec)

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;  # 字段放在开头
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名; # 字段放在 after字段，该字段后

3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from t1;
+------+--------+--------+------+
| id   | name   | sex    | age  |
+------+--------+--------+------+
|    1 | cmz    | male   |   18 |
|    2 | leco   | female |   10 |
|    3 | loocha | male   |    8 |
+------+--------+--------+------+
3 rows in set (0.00 sec)

#1. 修改存储引擎
mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum(''male'',''female'') DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

#2. 添加字段
mysql> create table student(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> desc student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table student add name varchar(20) not null,  # 默认不能为空
    -> add age int(10) not null default 22;                # 默认不能为空，默认值是22
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(10)     | NO   |     | 22      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 添加到最开始
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> alter table student add sex enum(''male'',''female'') default ''male'' first;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(20)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
# 插入××之后
mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(20)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table student add stu_num varchar(10) not null after id;  #插入在ｉｄ之后
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-----------------------+------+-----+---------+-------+
| Field   | Type                  | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| sex     | enum(''male'',''female'') | YES  |     | male    |       |
| id      | int(11)               | YES  |     | NULL    |       |
| stu_num | varchar(10)           | NO   |     | NULL    |       |
| name    | varchar(20)           | NO   |     | NULL    |       |
+---------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


#3. 删除字段
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(10)     | NO   |     | 22      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table student drop age;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#4. 修改字段类型modify
mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| age   | int(3)                | NO   |     | 22      |       |
| name  | varchar(20)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student modify name varchar(10) not null; 
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| age   | int(3)                | NO   |     | 22      |       |
| name  | varchar(10)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student  modify id int(11) not null primary key auto_increment; # 修改id为主键
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;  +-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| sex   | enum(''male'',''female'') | YES  |     | male    |                |
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| age   | int(3)                | NO   |     | 22      |                |
| name  | varchar(10)           | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student  modify id int(11) not null primary key auto_increment;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;  
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| sex   | enum(''male'',''female'') | YES  |     | male    |                |
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| age   | int(3)                | NO   |     | 22      |                |
| name  | varchar(10)           | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> alter table student modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined
mysql> alter table student modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| sex   | enum(''male'',''female'') | YES  |     | male    |                |
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| age   | int(3)                | NO   |     | 22      |                |
| name  | varchar(10)           | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#6. 对于存在的表增加复合主键
mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | NO   |     | NULL    |       |
| age   | int(3)                | NO   |     | 22      |       |
| name  | varchar(10)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student add primary key(id,age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | NO   | PRI | NULL    |       |
| age   | int(3)                | NO   | PRI | 22      |       |
| name  | varchar(10)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#7.　增加主键
mysql> alter table student  modify id int(11) not null primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#8. 增加主键，自动增长
mysql> alter table student  modify id int(11) not null primary key auto_increment;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#9. 删除主键
 # 1. 先删除子增约束
　# 2. 删除主键
mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| sex   | enum(''male'',''female'') | YES  |     | male    |                |
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| age   | int(3)                | NO   |     | 22      |                |
| name  | varchar(10)           | NO   |     | NULL    |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student modify id int(11) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student drop primary key;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| sex   | enum(''male'',''female'') | YES  |     | male    |       |
| id    | int(11)               | NO   |     | NULL    |       |
| age   | int(3)                | NO   |     | 22      |       |
| name  | varchar(10)           | NO   |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


 

1. 复制表结构 + 记录（数据）
2. 只复制表结构，不复制记录
   1. 复制部分表结构
   2. 复制全部表结构

复制表结构＋记录 （key不会复制: 主键、外键和索引）
# 复制表结构哦和数据
root@leco:~# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ''help;'' or ''\h'' for help. Type ''\c'' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select × from t1;
ERROR 1054 (42S22): Unknown column ''×'' in ''field list''
mysql> select * from t1;
+------+--------+--------+------+
| id   | name   | sex    | age  |
+------+--------+--------+------+
|    1 | cmz    | male   |   18 |
|    2 | leco   | female |   10 |
|    3 | loocha | male   |    8 |
+------+--------+--------+------+
3 rows in set (0.00 sec)

mysql> create table new_t1 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| new_t1        |
| t1            |
+---------------+
2 rows in set (0.00 sec)

mysql> desc new_t1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from new_t1;
+------+--------+--------+------+
| id   | name   | sex    | age  |
+------+--------+--------+------+
|    1 | cmz    | male   |   18 |
|    2 | leco   | female |   10 |
|    3 | loocha | male   |    8 |
+------+--------+--------+------+
3 rows in set (0.00 sec)

# 只复制表结构，不复制数据 
mysql> create table t2 select * from t1 where 1=2;  # 条件为假，查不到任何记录，此时就只会复制表结构，不复制表数据
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| new_t1        |
| t1            |
| t2            |
+---------------+
3 rows in set (0.00 sec)

mysql> desc t2;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

mysql> create table t3 like t1;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| new_t1        |
| t1            |
| t2            |
| t3            |
+---------------+
4 rows in set (0.00 sec)

mysql> desc t3;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum(''male'',''female'') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from t3;
Empty set (0.00 sec)

create table t2 select * from t1 where 1=2; 和 create table t3 like t1;都是创建表结构有神码区别？
前者可以选择性的复制，比如只复制其中部分字段的表结构，而后者是全部复制表结构字段。

DROP TABLE 表名;

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| new_t1        |
| t1            |
| t2            |
| t3            |
+---------------+
4 rows in set (0.00 sec)

mysql> drop table t3;  # 指定表名
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| new_t1        |
| t1            |
| t2            |
+---------------+
3 rows in set (0.01 sec)

    创建数据表:
    create table 表名(
    字段名  datatype  约束,
    字段名  datatype  约束,
    ......
    )

rename table 表名 新表名;

alter table 表名 add 字段名 数据类型;

alter table 表名 drop 字段名;

alter table 表名  modify 字段 数据类型 属性[after/first 字段名] ;

alter table 表名  change 旧字段名 新字段名  数据类型属性 [after/first 字段名];

drop table 表名;

show tables ;

desc 表名;

show columns from 表名;

show create table  表名;