Linux下Mysql数据库浅析(二)

 The world's most popular open source database！

这是mysql官方主页的一个小标题-“世界上最流行的开源数据库软件”，既然mysql这么流行，那我们一定要学好它！现在，继续我们的航程...

 

一，mysql的数据类型：

1，数值型

2，字符型

3，日期时间型

 

下面我们来分析这几种数据类型:

1,数值型

它又可分为整型和近似值型

 1）整型

 有一下五种整型：

 数据类型  有符号表示范围  无符号表示范围  大小

 TINYINT   -128到127         0到255       1 byte

 SMALLINT  -32768到32767     0到65535     2 byte

 MEDIUMINT -8388608到8388607 0到16777215  3 byte

 INT  -2147483648到2147483647 0到4294967295 4 byte

 BIGINT     非常大            非常大      8byte

 

 DECIMAL 这个数值类型支持小数，但是表示的值是精确的    

 2)近似型

 FLOAT(单精度)   占用4 byte

 DOUBLE (双精度) 占用8 byte

 BIT(表示直接把数据存储为二进制位)   占用大小取决于bit大小

 

支持的修饰符： 

 UNSIGNED 修饰符规定字段只保存正值

 ZEROFILL 修饰符规定 0（不是空格）可以用来真补输出的值

 NOT NULL 这个字段的值不能为空

 NULL     这个字段的值可以为空，也是缺省值

 DEFAULT  定义默认的值

 AUTO_INCREMENT 这个字段中的值可以自动增长。用这个修饰的字段必须满足：唯一键或是主键；为整型；值不能为空；是无符号数；

 

2，字符型

数据类型      大小        描述 

CHAR       0-255字节   定长字符串 

VARCHAR    0-255字节   变长字符串

TINYBLOB   0-255字节   不超过 255 个字符的二进制字符串

TINYTEXT   0-255字节   短文本字符串

BLOB     0-65 535字节  二进制形式的长文本数据

TEXT      0-65 535字节  长文本数据

MEDIUMBLOB 0-16777215字节 二进制形式的中等长度文本数据

MEDIUMTEXT 0-16777215字节 中等长度文本数据

LOGNGBLOB  0-4294967295字节 二进制形式的极大文本数据

LONGTEXT 0-4294967295字节 极大文本数据

 

CHAR类型用于定长字符串，比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补。

VARCHAR类型是一种可变长度的字符串类型，VARCHAR类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值。所以短于指示器长度的VARCHAR类型不会被空格填补，但长于指示器的值仍然会被截短。

TEXT和BLOB都用于存储大容量的数据。而BLOB类型区分大小写，TEXT不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短

 

可用的修饰符：

NOT NULL

NULL

DEFAULT

如下这三个修饰符只能用于修饰CHAR,VARCHAR,TEXT,TINYTEXT,MEDIUMTEXT,LONGTEXT：

BINARY 默认字符存储的时候是不区分大小写的，而加上这个关键字后，存储和检索数据区分大小写

CHARACTER SET 定义字符集。不指的话为默认

COLLATION 定义排序规则

 

特殊的两种类型;

ENUM 枚举型

特点：列出的个数最多为65535，但是一次只能选取一个。

例：Weekday ENUM('Mon','Tue','Wed','Tus','Fri','Sat','Sun')

Weekday这个字段中的值只能是定义的列表中的一个

SET 集合型

特点：列出的个数最多为64个，一次可以选取多个

例：Weekday SET('Mon','Tue','Wed','Tus','Fri','Sat','Sun')

Weekday这个字段的值可以是定义的列表中的多个值

 

3，日期型

数据类型   格式      大小    表示范围

DATE     YYYY-MM-DD 3byte 1000-01-01到9999-12-31

TIME     hh:mm:ss   3byte 00:00:00到23:59:59

DATETIME YYYY-MM-DD hh:mm:ss 8byte 1000-01-01 00:00:00到9999-12-31 23:59:59

TIMESTAMP  YYYY-MM-DD hh:mm:ss 4byte 1970年1月1日午夜到2037年12月31日午夜

YEAR(2)  YY         1byte     00到99

YEAR(4)  YYYY       1byte     1901到2155

 

支持的修饰符：

NOT NULL

NULL

DEFAULT

 

以上是mysql数据库中的数据类型的分析，当我们创建表结构的时候，字段要选择合适的数据类型，这样不仅能节省存储空间，也能提升其性能。

例：

mysql> CREATE DATABASE Student;

mysql> USE Student

mysql> CREATE TABLE TB  (

    -> SID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY,

    -> Name CHAR( 40) BINARY NOT NULL,

    -> Age TINYINT UNSIGNED NOT NULL,

    -> Gender CHAR(1) NOT NULL DEFAULT 'M' 

    -> );

 

mysql> CREATE TABLE tb3 (

    -> Weekday ENUM('Mon','Tue','Wed','Tus','Fri','Sat','Sun') NOT NULL,

    -> Week SET('Mon','Tue','Wed','Tus','Fri')

    -> );

    

二，mysql的环境变量sql_mode模式：

1，全局变量：设定之后对后来新建的会话有效，但重启mysqld服务器将失效。更改全局变量只有root才有权限

查看所有全局变量：

mysql> SHOW GLOBAL VARIABLES;

查看特定全局变量的值：

mysql> select @@global.variablename;  

查看的是特定用户自定义的全局变量值：

mysql> select @global.variablename;

 

2，会话变量：只对当前的会话有效。普通用户也有权限

查看所有会话变量：

mysql> SHOW SESSION VARIABLES;

查看特定会话变量的值：

mysql> select @@session.variablename; 

查看特定用户自定义的会话变量值：

mysql> select @session.variablename;

 

sql_mode常见的模型：

定义了mysql应该支持的sql语法，数据校验等！当违反了一些数据库中的表中字段定义的策略时，执行什么样操作等。

支持的sql_mode的模型有：

1，ANSI_QUOTES

使用ANSI标准的引号，即“的作用和‘相同，字符串只能用’‘引起来。允许插入非法数据，但会有警告。

2，IGNORE_SPACE

忽略mysql内建函数和’()‘中的空格。允许插入非法数据，但会有警告。

3，STRICT_ALL_TABLES

最严格模式，不允许网表中插入非法数据等。

4，STRICT_TRANS_TABLES

无效数据不允许插入事物中，允许插入到普通表中，但有警告。

5，TRADITIONAL

传统方式，等同也设置了STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER

 

查看sql_mode的值，如果为空使用mysql的默认值（like即相似匹配，支持%，_等通配符）：

mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';

mysql> select @@global.sql_mode;

mysql> select @global.sql_mode;

mysql> select @@session.sql_mode;

mysql> select @session.sql_mode;

设置sql_mode的值：

mysql> SET GLOBAL sql_mode=‘modename’;  （全局设置）

mysql> SET SESSION sql_mode=‘modename’; （会话设置）

 

三，mysql中的相关命令用法

1，创建数据库相关命令：

简单创建数据库：

mysql> create database dbname;

加上if not exists关键字表示不存在就创建：

mysql> create database if not exists dbname;

创建数据时设定默认字符集和排序规则：

mysql> create database if not exists dbname character set gbk collate gbk_chinese_ci;

设定默认数据库，在脚本中一般不这样用：

mysql> use dbname

查看当前默认数据库：

mysql> select database();

修改数据库的排序规则：

mysql> alter database db_name collate collate_name;

修改数据库的字符集：

mysql> alter database db_name character set charset_name;

删除数据库：

mysql> drop database if exists db_name;

 

注意：

1)，数据库不能进行重命名

2)，详细了解命令的使用方法：mysql> help command

 

2,数据库存储引擎相关命令

mysql的存储引擎是插件式的，常用的有MyISAM和InnoDB。

mysql-5.5版本之后，默认的存储因为为InnoDB。

数据库中的每个表可以独立使用一个存储引擎，但是不建议同一个数据库中所使用不同的存储引擎，会带来很多麻烦。

 

查看当前数据库支持存储引擎：

mysql> show engines;（其中带DEFAULT的为默认存储引擎）

各字段的意思：

Engine：存储引擎名字

Support：当前mysql是否支持这个引擎

Comment：描述信息

Transactions：是否支持事物机制

XA：是否支持分布式事物机制

Savepoints：是否支持存储点的保存

 

查看默认存储引擎：

mysql> show global variables like '%engine%';

查看某个表的属性信息：

mysql> show table status like 'table_name'\G

 

修改默认存储引擎：

#vim /etc/my.cnf

在[mysqld]下面添加：

default_storage_engine = name （name为引擎名）

 

使修改生效要重启mysqld服务：

#service mysqld restart

 

3，数据表的相关命令

1）create table命令用法

直接创建：

mysql> create table dbname.tb_name ( 

mysql> name char(20)

mysql> );

 

通过从其它表中检索数据后创建表（复制过来的有数据但不能附带原表中关于字段属性的定义）

mysql> create table test1 select * from test2;

查看表结构：

mysql> desc test1;

 

如下方法可以把表结构复杂过来，但是表中没有数据：

mysql> create table test3 like test2;

mysql> desc test3;

 

2）alter命令用法

主要用于修改表结构;

新增字段：

mysql> alter table test1 add (CID1 SMALLINT UNSIGNED,CID2 INT NOT NULL);

 

修改字段：

添加字段并修改位置：

mysql> alter table test1 modify Name CHAR(10) AFTER CID2;

修改属性：

mysql> alter table test1 modify Name CHAR(10) NOT NULL;

修改字段名称：

mysql> alter table test1 change Name SName CHAR(10) NOT NULL;

删除字段：

mysql> alter table test1 drop SName;

更改表名：

mysql> alter table test1 rename to new_test1;

mysql> rename table new_test1 to test1;

  

3)insert命令的用法

作用是向表中插入数据

方法1：

mysql> insert into test1 (CID1,CID2) VALUES ('1','2');

mysql> insert into test1 (CID1,CID2) VALUES ('1','2'),('10','11');

mysql> insert into test1 values ('20','21'),('30','31');

方法2：

mysql> insert into test1 set CID1='01',CID2='02';

方法3：

以一个表为模板，生成另一个与之相同的表,各字段定义也相同：

mysql> create table test like test1;

mysql> insert into test select * from test1;

 

4）update命令的用法

修改表的数据：

mysql> update test1 set CID1='100' where CID2='2';

以CID1字段的数据进行排序，把排好序的CID2的前2行内容都改为200：(默认为升序)

mysql> update test1 set CID2=200 order by CID1 LIMIT 2;

 

5)delete命令的用法

用于删除表中的记录

删除test1标准中CID1的值小于5的记录：

mysql> delete from test1 where CID1 < 5; 

以CID1字段进行降序排列，把排好序的前2行删除：

mysql> delete from test1 order by CID1 DESC LIMIT 2;

 

6）replace命令的用法

和insert命令的用法相同，只是当往字段中插入数据时，如果本身有数据，replace会覆盖其中的内容，而insert会执行不成功

 

7）truncate命令的用法

用于清空一个表中的数据，并且会重置计数器。

mysql> truncate test1;

应用举例：

如果test1表的CID1设置了auto_increment属性，现在表中只有一条记录且CID1为1；如果我们把这条记录删掉，再插入一条记录，CID1的值为2。如果我们用truncate这个命令清空表，再插入一条新纪录，CID1的值为1。

 

下一篇我们将继续深入的探讨mysql，继续....