MySQL数据库的基本操作

连接MySQL数据库：
mysql -h hostname -u username -p，其中mysql为登录命令，-h后的参数代表服务器主机地址，-u后的参数代表登录数据库的用户名称，-p后的参数代表登录密码。

设置数据库密码：

set password for 用户名@localhost = password('新密码'); 

查看数据库：

SHOW DATABASES;

SQL语句不区分大小写，但最好大写。
创建数据库：

CREATE DATABASE database_name;

删除数据库：

DROP DATABASE database_name;

SHOW CREATE DATABASE声明查看数据库的定义，可以发现数据库test已经不存在了。
MySQL储存引擎：
数据库储存引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据引擎进行创建、更新和删除数据操作，MySQL的核心就是储存引擎。
MySQL提供了不同的储存引擎，包括处理事务安全表的引擎和储存非事务安全表的引擎。MySQL中不需要整个服务器中使用同一种储存引擎，可以根据情况对每一个表使用不同的存储引擎。使用SHOW ENGINES;可以查看MySQL支持的所有的引擎类型。想要格式化查看可使用SHOW ENGINES\G;

MySQL5.5.5版本之前默认的储存引擎是MyISAM，5.5.5版本之后使用的是InnoDB。Support表示该数据库是否可用，YES表示可以使用，NO表示不可用，DEFAULT表示默认存储引擎。
MySQL提供了那么多的储存引擎，那我们应该如何选择呢？如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，可以使用InnoDB；如果数据表主要用来插入和查询记录，则MyISAM可以提供较高的处理效率；如果只是临时存放数据，数据量不大，且不需要较高的数据安全性，可以选择Memory引擎，Memory引擎将数据存储在内存中，但mysqld守护进程崩溃时数据会丢失；如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive引擎支持高并发的插入操作，但本事并不是事务安全的。
查看数据表使用的存储引擎：SHOW CREATE TABLE table_name\G;

修改数据表的储存引擎：ALTER TABLE table_name ENGINE=储存引擎

不同的MySQL版本使用的默认储存引擎不同，可以使用SELECT @@default_storage_engine;查看数据库使用的默认储存引擎

创建数据表：

CREATE TABLE <表名>(
		字段名1 数据类型 [列级别约束条件][默认值],
		字段名2 数据类型 [列级别约束条件][默认值],
		......
		[表级别约束条件]
);

查看数据表基本结构：

DESCRIBE/DESC 表名;

修改表名：

ALTER TABLE <旧表名> RENAME <新表名>;

删除数据表：

DROP TABLE [IF EXISTS] 表1,表2,...;

加上IF EXISTS可用于删除表前判断表是否存在，不存在sql语句也可以继续执行，只是会发出警告。

主键约束：
主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键能够唯一的标识表中的一条记录，可以结合外键来定义数据表之间的关系，并且加快数据库查询的条件。主键分为单字段主键和多字段联合主键。
单字段主键有两种创建方式：
（1）、定义列的同时指定主键：字段名 数据类型 PRIMARY KEY [默认值]

（2）、定义完所有列后指定主键：CONSTRAINT <约束名> PRIMARY KEY [字段名]

为已存在的表添加主键约束：

ALTER TABLE <表名> ADD PRIMARY KEY(字段名);

多字段联合主键：
主键由多个字段联合组成，语法规则PRIMARY KEY [字段1，字段2,...];
外键约束：
外键用于在两个表之间建立连接，可以是一列或者多列，一个表可以有一个或多个外键，一个表的外键可以为空值，若不是空值，则每一个外键值必须等于另一个表中的主键的某一个值。外键的作用是保持数据的一致性、完整性，一个表设置了外键后，则另一个表中的相关联字段不可删除，想要删除必须先删除外键约束。
创建外键语法：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名1[字段名2,...] REFERENCES <主表名> 主键列1[,主键列2,...]

例如创建手机号码表关联用户表主键：

为已存在的表添加外键约束：

ALTER TABLE <外键所在的表名> ADD CONSTRAINT <外键名> FOREIGN KEY(外键所在的列名) REFERENCES <对应主键所在表名(主键所在列名)>;

注意：外键约束不能跨引擎使用！
非空约束：
非空约束（Not Null Constraint）指字段的值不能为空，语法如下：

字段名 数据类型 not null

为已存在的表添加非空约束：

ALTER TABLE <表名> MODIFY <字段名> <字段类型> NOT NULL;

唯一性约束：
唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一性约束可以确保一列或者几列不出现重复值。
唯一性约束同样有两种创建方式，可以定义完列后直接创建：字段名 数据类型 UNIQUE,也可以定义完列后指定唯一约束：[CONSTRAINT <约束名>] UNIQUE(<字段名>)。
为已存在的表添加唯一性约束：

ALTER TABLE <表名> ADD UNIQUE(字段1,字段2,...);

UNIQUE和PRIMARY KEY区别：一个表中可以有多个字段声明为UNIQUE，但只能有一个PRIMARY KEY声明；声明为PRIMARY KEY 的列不允许有空值，声明为UNIQUE的字段可以有空值。
默认约束：
默认约束（Default Constraint）指定某列的默认值，语法规则如下：

字段名 数据类型 DEFAULT 默认值

为已存在的表添加默认约束：

ALTER TABLE <表名> ALTER <字段名> SET DEFAULT <默认值>;

主键自增长：
设置主键后一般会希望更新数据时主键可以自动增加，可以通过为表主键添加AUTO_INCREMENT关键字来实现，MySQL中AUTO_INCREMENT默认初始值为1，每增加一条记录，字段自动加1，一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT只能约束整型类型（TINYINT、SMALLIN、INT、BIGINT）的字段，语法规则：

字段名 数据类型 AUTO_INCREMENT

为已存在的表添加主键自增长：

ALTER TABLE <表名> MODIFY <主键字段名> <字段类型> AUTO_INCREMENT;

修改字段数据类型：

ALTER TABLE <表名> MODIFY <字段名> < 数据类型>;

例如修改userinfo中gender字段的数据类型为CHAR：

修改字段名：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

添加字段：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在字段名];

FIRST为可选参数，表示将新创建的字段设置为表的第一个字段，AFTER表示将新创建的字段添加到指定字段后面。
删除字段：

ALTER TABLE <表名> DROP <字段名>;

修改字段的排列位置：

ALTER TABLE <表名> MODIFY <字段1><数据类型> FIRST|AFTER <字段2>;

删除被其他表关联的主表：
需要先删除关联子表的外键约束，再删除主表。
查看关联子表的外键约束：SHOW CREATE TABLE <表名>\G;

可以看到外键约束名为phone_ibfk_1，删除外键约束：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

现在就可以删除主表了：