mysql一些常用操作_MYSQL常用操作

此篇是为数据库小白入门而写的，介绍使用数据库的最基本也是最常用的一些操作。我也是在日常工作中东一点西一点学到的，难免不成体系，理解也有偏颇，欢迎批评指正。目录：

一、创建数据库

二、创建表

三、数据导入导出

四、单表查询

五、多表查询

六、查询优化

默认使用NAVICAT FOR MYSQL，安装教程见MYSQL软件安装及配置。

一、创建数据库

右键单击连接的MYSQL服务名称，新建数据库，弹出以下界面，填写数据库名(中英文都可以，建议英文)，字符集选gbk，排序规则选gbk_chinese_ci，因为我们处理的数据大多是中文，这样不会乱码。

新建数据库界面

二、创建表

MYSQL导入数据前必须先创建好一个表格，告诉数据库这个表有哪些字段名称、数据是什么类型等，创建的表的样式必须与要导入的数据样式一样。

1、菜单操作创建表

双击创建的数据库，点击下面的【表】，点击【新建表】，填写字段名称、数据类型、长度。

创建表界面1

创建表界面2

(1) 数据类型及长度填写说明

字段为字符串的，选择varchar，如身份证号码、手机号码、姓名等，varchar需给定一个长度，这个根据字段信息大概估一个值，注意如果过小的话，可能有些数据会读取不到；

字段为数字的，选择int或者float，int为整数，float为浮点数。

(2)增加一些限制条件

创建表的时候，有时会增加一些限制条件，如是否允许空值啊，是否添加主键啊。讲一下主键吧，比较常用。主键(primary key)是表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录，相当于一个ID，它对应的值必须是唯一的而且是非空的。

一般会选择唯一且非空、有ID性质的字段添加主键，如用户号码。在导入数据的时候增加这个限制，可以保证我们导入的数据是唯一的而且是非空的。如果导入的数据不是唯一的，则导入时会报错。

主键除了作为限制外，还有别的用途，主键可以作索引用，增加查询速度。

2、使用SQL语句创建表

双击创建的数据库，点击下面的【查询】，点击【新建查询】，进入查询编辑器，对应以上菜单操作，其SQL语句如下：CREATE TABLE my_table (

`身份证号码`  varchar(18)  ,

`姓名`  varchar(10)  ,

`年龄`  int  ,

`身高`  float ,

PRIMARY KEY (`身份证号码`));

三、数据导入导出

1、数据导入load data local infile 'F:/XX.txt' into table XXX

fields terminated by '|'

lines terminated by '\r\n';

注1：数据路径不能含有中文，必须是英文；

注2：换行符有'\r\n'、'\r'、'\n'三种，不同系统的换行符不同，'\r\n'是Windows系统的换行符，'\n'是Linux系统的换行符，'\r'是Unix系统的换行符，一般先试'\r\n'，如果发现只能导入一行数据，一般是换行符不正确，则再试试其他两个换行符。

2、数据导出select  xxx  from table_name

into outfile "F:/xx.txt"

fields terminated by '|'

lines terminated by '\r\n';

四、单表查询

1、简单数据查询

(1)查询所有字段select * from table_name;

(2)查询指定字段select field1, field2, ..., fieldn  from  table_name;

(3)使用关键字DISTINCT避免重复数据查询select  DISTINCT field1, field2, ..., fieldn  from  table_name;

2、条件数据查询

使用关键字WHERE对查询的记录进行过滤，查询结果只输出满足条件的数据。select field1, field2, ..., fieldn  from  table_name

where 条件;

3、排序数据记录查询

查询到的数据记录在默认情况下是按照数据记录最初添加到表中的顺序显示，我们可以使用关键字ORDER BY 来设置查询结果的顺序。select field1, field2, ..., fieldn  from  table_name

order by fieldm1 ASC;

在上述语句中，查询结果按照字段fieldm1升序排序，参数ASC为升序，也可换成DESC降序排序。

4、限制数据记录查询

使用关键字LIMIT可限制查询结果数量：select field1, field2, ..., fieldn  from  table_name

limit 初始位置, 行数;

5、统计函数和分组数据记录查询

(1)常用的统计函数有count()，avg()，sum()，max()，min()select count(*)  from  table_name;

select avg(fieldm) from table_name;

(2)使用关键字GROUP BY进行分组查询select filedm, count(fieldn) from table_name

group by fieldm;

6、举个例子

表3月长市漫号码有字段主套餐 | 品牌 | 用户号码 3个字段，查询神州行品牌用户数前10的主套餐及其对应的用户数，SQL语句如下：select 主套餐,count(distinct 用户号码) from 3月长市漫号码

where 品牌='神州行'

group by 主套餐

order by count(用户号码) desc

limit 1,10;

五、多表查询

多表查询涉及多个表的连接，两个表的连接可使用内连接(inner join)、左外连接(left join)、右外连接(right join)，按照跑数逻辑选取。select  ....

from table_name a

left join another_table_name b

on a.id = b.id;

注1：上述语句中a和b分别是table_name和another_table_name的别名，因为表的名字一般比较长，为了简化(偷懒)，给它们各自取个简单的别名，可避免重复输入长长的表名。

注2：on后面是连接条件a.id = b.id。

注3：如果两个连接的表不在同一个数据库下，则使用该表需加它的数据库名作为前缀。比如表1在database1数据库中，表2在database2数据库中，查询在database1中进行，则使用表2的方式如下：database2.表2。

六、查询优化

多表查询时，当数据量上千万级别，跑数通常长达几个小时，这时对查询语句进行优化显得非常重要。最常用的查询优化方法就是加索引：索引类似于书的目录，主要是为了提高从表中检索数据的速度。索引的创建有利有弊，创建索引可以提升查询速度，但过多的索引则会占据许多磁盘空间。

1、 什么情况下适合创建索引呢？

(1)用于两表连接的字段；

(2)经常被查询的字段，即在WHERE子句中出现的字段；

(3)在分组的字段，即在GROUP BY子句中出现的字段；

(4)设置唯一完整性约束的字段，主键就是这种。

2、怎样创建索引呢？

(1)创建表时创建普通索引create table 2月长市漫号码 (

用户号码 varchar(11),

品牌 varchar(50),

主套餐 varchar(50),

index index_用户号码(用户号码));

(2)在已经存在的表上创建普通索引create index index_name on table_name(字段名);