MySQL深入浅出（一）：SQL基础、数据类型、运算符

一、SQL基础

    安装MySQL时系统自动创建的数据库，其各自功能如下：

• information_schemm：主要存储了系统中的一些数库对象信息，比如用户表信息列信息。权限信息，字符集信息、分区信息等
• cluster存储了系统的集群信息
• mysql存储了系统的用户权限信息
• test系统自动创建的测试数摇库，任何用户都可以使用

1.1 SQL分类

1. DDL（ Data Definition Langnages）语句：数据定义语言，这些语句定义了不同的数据段、数据库，表、列、索引等数据库对象。常用的语句关键字主要包括 create、drop、alter等
2. DML（ Data Manipulation Language）语句：数据操纵语句，用于添加，删除，更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和 select等
3. DCL（ Data Control Languane）语句：数据控制语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等

1.2 DDL语句

1.2.1 数据库操作

mysql -uroot -p

create database dbname; 
use dbname; //选择数据库
show tables; //查看数据库中创建的所有表
drop database dbname;

1.2.2 表操作

CREATE  TABLE `user`(
  `id` tinyint(1) unsigned NOT NULL auto_increment comment '主键',
  `name` VARCHAR(20) NOT NULL DEFAULT '',
  `image` VARCHAR(20) NOT NULL DEFAULT '',
  `type` tinyint(1) unsigned NOT NULL DEFAULT 0,
  `create_time` int(10) unsigned NOT NULL DEFAULT 0,
  `update_time` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT charset=utf8 comment '用户信息表';

desc table_name;//查看表定义
show full columns from user;//查询一张表的详细信息(包括字段注释,字段名称,类型等)
drop table table_name;//删除表

alter table table_name modify col_definition; // 修改表类型
alter table table_name change old_col_name col_definition; // 字段改名
alter table table_name add col_definition; // 增加表字段
alter table table_name add/modify/change ... [first|after col_name];//修改字段排列顺序
alter table table_name drop col_name; //删除表字段
alter table table_name rename new_table_name; //更改表名

1.3 DML语句

insert into table_name(field1,field2,...,fieldn) values(value1,value2,...,valuen)[,(value1,value2,...,valuen)]; // (批量)插入记录

insert into table_name set col1_name=value1,...;

update table_name set field1=value1,field2=value2....[where condition]; // 更新记录

delete from table_name [where condition]; // 删除记录

select ... [limit offset_start,row_count]; 
//offset_start:起始偏移量（默认为0），row_count:显示的行数。分页显示：limit + order by 

    聚合函数：

select [field1,field2...] fun_name 
from table_name 
[where where_condition] 
[group by field1,...] 
[with rollup] 
[having where_condition]

• fun_name表示要做的聚合操作，常用的有sum（求和）、count(*)（记录数）、max(field)、
• min(field)
• group by关键字表示要进行分类聚合的字段
• with rollup是可选语法，表明是否对分类聚合后的结果再汇总
• having关键字表示对分类后的结果再进行条件的过滤

    注意：having是对聚合后的结果进行条件过滤，where是在聚合前就对记录进行过滤

    

    

    

    Mysql Join语法解析与性能分析

1.4 查询元数据信息

    MySQL5.0之后，提供了一个新的数据库information_schema,用来记录MySQL中的元数据信息。元数据指的是比如表名、列名、列类型、索引名等表的各种属性名称。它是一个虚拟数据库，库里show tables显示的各种‘表’也不是实际存在的物理表，而全部是视图。
    下面列出一些比较常用的视图：

• SCHEMATA：该表提供了当前 mysql实例中所有数据库的信息， show databases的结果取之此表
• TABLES：该表提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema、表类型、表引擎、创建时间等信息， show tables from schemaname的结果取之此表
• COLUMNS：该表提供了表中的列信息，详细表述了某张表的所有列以及每个列的信息， show columns from schemaname.tablename的结果取之此表
• STATISTICS：该表提供了关于表索引的信息， show index from schemaname.tablename的结果取之此表

    场景示例：

• 1、删除数据库下所有前缀为tmp的表

select concat('drop table 数据库名.', table_name,';') from information_schema.tables where table_schema='数据库名' and table_name like 'tmp%';

• 2、将数据库下所有存储引擎为myisam的表改为innodb

select concat('alter table 数据库名.', table_name, ' engine=innodb;') from information_schema.tables where table_schema='数据库名' and engine='MyISAM';

• 3、批量清空数据表

select concat('truncate table ',table_name,';') from information_schema.tables where table_schema='数据库名';

二、数据类型

2.1 数值类型

    数值数据类型可以大致划分为两个类别，一个是整数，另一个是小数
  

2.1.1 整数

    MySQL允许我们指定数值字段中的值是否有正负之分(unsigned)或者用零填补(zerofill)和auto_increment属性。

    MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展(如 INT(6),6即是其宽度指示器,该宽度指示器并不会影响int列存储字段的大小,也就是说,超过6位它不会自动截取,依然会存储,只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有zerofill,如果有未满6位的部分就会用0来填充)。
    Tips：如果一个列指定为zerofill，则MySQL自动为该列添加unsigned属性。

    auto_increment属性：

• 只用于整数类型
• 一个表最多只能有一个auto_increment列
• 该列应该定义为not null，并且定义为primary key或unique键

2.1.2 小数

    小数类型可以大致划分为两个类别，一个是浮点数，另一个是定点数

1. 浮点数：float（单精度），double（双精度）
2. 定点数：decimal，在MySQL内部以字符串形式存放，比浮点数更精确，适合表示货币等高精度的数据

    float/double/decimal(M,D)：M标度（整数位+小数位），D精度

    Tips：小数会自动四舍五入

2.2 日期时间类型

    

    TIMESTAMP的一个重要特点就是和时区相关，一般业务也可以用int比较好，节约空间，存时间戳即可。

2.3 字符串类型

一个英文字符，占用一个字节

汉字：

字符类型若为gbk，每个字符最多占2个字节;
字符类型若为utf8，每个字符最多占3个字节。

• CHAR 和 VARCHAR 类型

    CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从 0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

    CHAR 类型的一个变体是 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围指示器。

    CHAR 和 VARCHGAR 不同之处在于 MYSQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍然会被截短。

三、运算符与常用函数

3.1 运算符

    

3.2 （流程）函数

    

    

select database()/version()/user(); // 查看当前数据库/版本/用户

下一篇：MySQL深入浅出（二）：索引的设计原则、SQL优化、MySQL日志、备份与恢复