MySQL数据库基础
MySQL数据库基础
本文的所有操作是基于CMD环境，MySQL通过在命令行中输入SQL语句对数据库进行操作。配置问题可参考《打通MySQL的操作权限》中的内容，该文算是针对前期的环境配置问题提供了一个较为全面的解决方案，与本文是一个相(yao)辅(xiang)相(hu)成(ying)的关系。MySQL官方文档访问链接 https://dev.mysql.com/doc 。
一、连接相关
1. 启动MySQL服务器：net start mysql
2. 连接MySQL数据库：mysql -uroot -h127.0.0.1 -ppassword
3. 断开MySQL数据库：连接到MySQL服务器后，可通过在MySQL提示符下输入exit;或是quit;断开MySQL连接。
4. 停止MySQL服务器，一共有三种基本方式。
（1）如果将MySQL设置为Windows服务，可以通过控制面板，系统安全，管理工具，服务 列表中右击mysql服务，在弹出的快捷菜单中选择“停止”即可。
（2）net stop mysql 回车
（3）mysqladmin -uroot shutdown -ppassword
二、数据库操作
1. 创建数据库：CREATE DATABASE 如：create database db_admin;
数据库命名的注意事项：
（1）不能与其他数据库重名，否则将发生错误；
（2）名称可以由任意字母、数字、下划线_和$组成，可以使用上述的任意字符开头，但不能单独使用数字，否则会造成它与数值的混淆；
（3）名称最长可为64个字符，而别名最多可多达256个字符；
（4）不能使用MySQL关键字作为数据库名、表名。
（5）Windows下数据库名和表名大小写不敏感，而在Linux环境下大小写是敏感的。为了便于数据库在不同平台的移植，建议采用小写定义数据库名和表名。
2. 查看数据库：SHOW DATABASES 如：mysql> show databases;
3. 选择数据库：USE DATABASE(数据库名) 如：mysql> use db_admin;
4. 删除数据库：DROP DATABASE 数据库名; 如：mysql> drop database db_admin;
三、MySQL数据表操作
在对MySQL数据表进行操作之前，必须首先使用USE语句选择数据库，才可在指定的数据库中对数据表进行操作。
1. 创建数据表CREATE TABLE 详见 官方参考手册 语法格式如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
参数说明：
| 参 数 | 说 明 |
| TEMPORARY | 如果使用该关键字，表示创建一个临时表 |
| IF NOT EXISTS | 该关键字用于避免表不存在时MySQL报告的错误 |
| create_definition | 表的列属性部分。MySQL要求在创建表时，表至少包含一列 |
| table_options | 表的一些特性参数 |
| partition_options |
其中，属性create_definition，每一列定义的具体格式如下：
create_definition:
col_name column_definitioncolumn_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
| 参 数 | 说 明 |
| col_name | 字段名 |
| data_type | 字段类型 |
| NOT NULL | NULL | 指出该列是否允许是空值，系统一般默认允许为空值，所以当不允许为空值时，必须使用NOT NULL |
| DEFAULT default_value | 表示默认值 |
| AUTO_INCREMENT | 表示是否是自动编号，每个表只能有一个AUTO_INCREMENT列，并且必须被索引 |
| PRIMARY KEY |
表示是否为主键。一个表只能有一个PRIMARY KEY。如表中没有一个PRIMARY KEY，而某些应用需要PRIMARY KEY，MySQL将返回第一个没有没有任何NULL列的UNIQUE键，作为PRIMARY KEY |
| reference_definition | 为字段添加注释 |
以上是创建数据表的一些基础知识，看起来十分复杂，但在实际应用中使用最基本的格式创建数据表即可，具体格式如下：
create table table_name(列名1 属性, 列名2 属性...);
create table tb_admin(
id int auto_increment primary key,
user varchar(30) not null,
password varchar(30) not null,
createtime datetime);
2. 查看表结构SHOW COLUMNS或DESCRIBE
对于创建成功的数据表，可以使用 SHOW COLUMNS或DESCRIBE语句查看指定数据表的表结构。下面分别对这两个语句进行介绍。
（1）SHOW [FULL]COLUMNS FROM 数据表名 [FROM 数据库名]; 或 SHOW [FULL]COLUMNS FROM 数据库名.数据表名;
上图是 show columns from tb_admin from db_admin; 的输出结果。
（2）DESCRIBE 数据表名; 其中 DESCRIBE 可以简写成DESC。在查看表结构时，也可以只列出某一列的信息。如：DESC 数据表名 列名;
综上， show columns from tb_admin from db_admin; 等价于 show columns from db_admin.tb_admin; 等价于 describe tb_admin; 等价于 desc tb_admin;
3. 修改表结构ALTER TABLE
修改表结构使用ALTER TABLE语句。修改表结构指增加或者删除字段、修改字段名或者字段类型、设置或取消主键外键、设置或者取消索引以及修改表的注释等。
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
当指定IGNORE时，如果出现重复关键的行，则只执行一行，其他重复的行被删除。
其中，alter_spec子句定义要修改的内容，其部分语法如下，详见参考手册。
alter_specification:
table_options| ADD [COLUMN] col_name column_definition //添加新字段
[FIRST | AFTER col_name ]| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name] //添加索引名称
[index_type] (index_col_name,...) [index_option] ...| ADD [CONSTRAINT [symbol]] PRIMARY KEY //添加主键名称
[index_type] (index_col_name,...) [index_option] ...| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] //添加唯一索引
[index_type] (index_col_name,...) [index_option] ...| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} //修改字段名称
| CHANGE [COLUMN] old_col_name new_col_name column_definition //修改字段类型
[FIRST|AFTER col_name]| MODIFY [COLUMN] col_name column_definition //修改子句定义字段
[FIRST | AFTER col_name]| DROP [COLUMN] col_name //删除字段名称
| DROP PRIMARY KEY //删除主键名称
| DROP {INDEX|KEY} index_name //删除索引名称| RENAME [TO|AS] new_tbl_name //更改表名
ALTER TABLE语句允许指定多个alter_spec子句，每个子句间使用逗号分隔，每个子句表示对表的一个修改。例如：
alter table tb_admin add email varchar(50) not null ,modify user varchar(40);
修改表的编码方式：ALTER TABLE `test` DEFAULT CHARACTER SET utf8; 该命令用于将表test的编码方式改为utf8；
修改字段的编码方式：ALTER TABLE `test` CHANGE `name` `name` VARCHAR(36) CHARACTER SET utf8 NOT NULL; 该命令用于将表test中name字段的编码方式改为utf8
注意：通过alter修改表列的前提是必须将表中数据全部删除，然后才能修改。
4. 重命名表RENAME TABLE
重命名数据表使用RENAME TABLE语句，语法如下：
RENAME TABLE 数据表名1 To 数据表名2;
该语句可同时对多个数据表进行重命名，多个表之间以逗号分隔。
rename table tb_admin to tb_user;
5. 删除表DROP TABLE
语法格式为：DROP TABLE 数据表名;
删除数据表的操作应该谨慎使用。一旦删除了数据表，那么表中的数据将会全部清除，没有备份则无法恢复。
四、MySQL语句操作
在数据表中插入、浏览、修改和删除记录可以在MySQL命令行中使用SQL语句完成，下面介绍如何在MySQL命令行中执行基本的SQL语句。
1. 插入记录insert
在建立一个空的数据库和数据表时，首先需要考虑的是如何向数据表中添加数据，该操作可以使用insert语句来完成。
语法格式如下：
insert into 数据表名(column_name,column_name2,...) values (values, value2, ...)
在MySQL中，一次可以同时插入多行记录，各行记录的值清单在VALUES关键字后以逗号分隔，而标准的SQL语句一次只能插入一行记录。
2. 查询数据库记录select
要从数据库中把数据查询出来，就要用到数据查询语句select。select语句是最常用的查询语句，它的使用方式有些复杂，但功能强大。
select语句的语法格式如下：
select selection_list //要查询的内容，选择哪些列
from 数据表名 //指定数据表
where primary_constraint //查询时需要满足的条件，行必须满足的条件
group by grouping_columns //如何对结果进行分组
order by sorting_columns //如何对结果进行排序
having secondary_constraint //查询时满足的第二个条件
limit count //限定输出的查询结果
（1）查询数据表：select * from 数据表名;
（2）查询表中一列或多列
针对表中的多列进行查询，只要在select后面指定要查询的列名即可，多列之间用逗号分隔。例如：
select id,user,password,email from tb_admin where id=1;
（3）多表查询
针对多个数据表进行查询，关键是where子句中查询条件的设置，要查找的字段名最好用“表名.字段名”表示，这样可以防止因表之间字段重名而无法获知该字段属于哪个表，在where子句中多个表之间所形成的联动关系应按照如下形式书写：表1.字段=表2.字段 and 其他查询条件
多表查询的SQL语句格式如下：
select 字段名 from 表1,表2...... where 表1.字段=表2.字段 and 其他查询条件
例如：select * from tb_student,tb_sscore where tb_student.userid=tb_sscore.sid and tb_student.userid=001
对于SQL语句的使用，是一个很丰富的知识点，合理地运用SQL语句，能够提高程序查询、输出数据的速度。后续将继续对这一块知识进行深入研究。
3. 修改记录update
要执行修改的操作可以使用update语句，该语句的格式如下：
update 数据表名 set column_name = new_value1,column_name2 = new_value2, ...[where condition]
其中，set子句指出要修改的列和它们给定的值，where子句是可选的，如果给出它将指定记录中哪行应该被更新，否则，所有的记录行都将被更新。
update tb_admin set password = '123' where user = '乐乐';
注意：更新时一定要保证where子句的正确性，一旦where子句出错，将会破坏所有改变的数据。
4. 删除记录delete
在数据库中，有些数据已经失去意义或者错误时就需要将它们删除，此时可以使用delete语句，该语句的格式如下：
delete from 数据表名 where condition
delete from tb_admin where user = '乐乐';
注意：该语句在执行过程中，如果没有指定where条件，将删除所有的记录；如果指定了where条件，将按照指定的条件进行删除。在实际的应用中，执行删除操作时，执行删除的条件一般应该为数据的id，而不是具体的某个字段值，这样可以避免一些不必要的错误发生。
五、MySQL数据库备份和恢复
1.数据的备份
在命令行模式下（必要时对电脑的环境变量进行设置）完成对数据的备份，使用的是MYSQLDUMP命令。通过该命令可以将数据以文本文件的形式存储到指定的文件夹下。
语法格式：
mysqldump -uroot -ppassword db_database>F:\db_database.txt
在该命令的结尾处没有任何的结束符，只要按Enter键即可。
可以看到db_admin.txt的内容：
-- MySQL dump 10.13 Distrib 5.6.11, for Win32 (x86)
--
-- Host: localhost Database: db_admin
-- ------------------------------------------------------
-- Server version 5.6.11/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Table structure for table `tb_admin`
--DROP TABLE IF EXISTS `tb_admin`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb_admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(40) CHARACTER SET gbk NOT NULL,
`password` varchar(30) NOT NULL,
`createtime` datetime NOT NULL,
`email` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `tb_admin`
--LOCK TABLES `tb_admin` WRITE;
/*!40000 ALTER TABLE `tb_admin` DISABLE KEYS */;
INSERT INTO `tb_admin` VALUES (1,'乐乐','123','2017-03-01 09:11:59','lele@fintech.com'),(2,'天天','112','2017-03-01 09:12:59','tiantian@fintech.com'),(3,'圆圆','112','2017-03-01 09:13:59','yuanyuan@fintech.com');
/*!40000 ALTER TABLE `tb_admin` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2017-03-02 19:52:57
2.数据的恢复
命令格式：
mysql -uroot -ppassword db_admin <F:\db_amin.txt
究其原因，从txt内容可以看出在执行备份时并未生成创建数据库语句，仅仅只是把数据库中的数据表给备份了。解决方法是先创建数据库db_admin之后再导入db_admin.txt。
从上述的操作可以看到，并不能真正的备份数据库，而只能对数据表进行备份，因此删除数据库意义并不是很大，如果你是为了备份的话。
