Mysql学习-(4-创建数据表)

参考：http://blog.csdn.net/zjf280441589/article/details/18660509

MySQL创建数据表（上）

一、创建数据表的SQL语句模型【弱类型】

CREATETABLE [IF NOT EXISTS] 表名称（

字段名1列的类型[属性][索引],

字段名2 列的类型[属性][索引],

…

字段名n列的类型[属性][索引],

）[表属性][表字符集]；

【表名称，字段名需要自己来命名】

【SQL语句并不区分大小写，但是一个文件名：在Windows下不区分大小写，但是在 Linux/UNIX下是区分大小写的】

【命名规则： 1、有意义（英文，英文组合或英文缩写）

2、自己定的名称最好小写。

3、SQL语句最好大写。】

1、 CREATE TABLE users(

id INT,

name CHAR(32)

);

2、 SHOWTABLES;

3、 DESCusers;

4、 SELECT* FROM users;

二、数据值与列类型

都以空间大小来细分，能够存下即可！

1、数值型

整型（整数）：

非常小整数型 1字节 -128～127 0～255（无符号） TINYINIT

较小整数型 2字节 -32768～32767 SMALLINIT

中等大小整数型 3字节 0～16777215 MEDIUMIINT

标准整数型 4字节 -2147483648～2147483647 INT

大整数型 8字节 BIGINT

【可以添加属性：UNSIGNED】

浮点型（小数）：

FLOAT（M，N） 4字节 【长度M，保留小数点后N位】

DOUBLE（M，N） 8字节 【五舍六入】

DECIMAL（M，N） M+2字节 【定点数，以字符串形式存储数据， 更精确的保存数据，但是效率会有所折扣。】【四舍五入】

【浮点数会存在误差，因此在对数字敏感的时候，需要用定点数来存储！】

3、字符型

MySQL支持以单引号与双引号表示字符串类型。

如“MySQL”与 'MySQL'相同

char(M) 最大255 固定长度字符串

varchar(M) 最大255 可变长度字符串

char(4) varchar(4)// 永远比字符多一个字节

'' 4 '' 1字节

'a' 4 'a' 2字节

'abcd' 4 'abcd' 5字节

'abcdefg' 4 'abcdefgh' 5字节

char类型会截取尾部的空格；

示例： CREATE TABLE IF NOT EXISTS tab(v varchar(4),c char(4));

INSERTINTO tab(v,c) VALUES(“ab ”,”ab ”);

SELECTCONCAT(v,”#”),CONCAT(c,”#”) FROM tab;

最佳实践：

固定长度，推荐使用char类型（可能会浪费一定空间）。

可变长度，推荐使用varchar类型（可能会付出一些性能损耗）

text 文本数据：文章，日志 长度：2^16-1

MEDIUMTEXT

LONGTEXT

blob 二进制数据：照片，电影 长度：2^16-1

MEDIUMBLOB

LONGBLOB

【虽然没法通过SQL语句将照片，电影，压缩包等二进制文件插入到数据库中， 但是我们可以通过C++/Java等编程语言将二进制文件读成二进制数据流，然 后保存到数据库中】

ENUM 枚举 1～2字节

ENUM(“one”,”two”,”three”,”four”)~65535

一次只能有一个值

SET集合 1，2，3，4，8字节

SET(“one”,”two”,”three”,”four”)~64

一次可以有多个值，用“，”隔开。

4、日期型

DATE YYYY-MM-DD //如果位数充足，可以不用加‘-’

TIME hh:mm:ss //位数充足，可以忽略‘：’，位数不足，从后面截取

DATETIME YYYY-MM-DDhh:mm:ss

TIMESTAMP YYYYMMDDhhmmss //位数不足时，不可忽略前导0

YEAR YYYY

【创建数据表时，最好不要使用这些时间值，最好使用C++/Java等编程语言中的整数来保存 时间，这样更方便计算，如C++中的int，PHP中时间戳1970-1-10:0:0】

MySQL创建数据表（中）

三、数据字段属性

1、unsigned【无符号】

可以让空间增加一倍 比如可以让-128～127增加到0～255

注意：只能用在数值型字段

2、zerofill【前导零】

e.g. create table if not exists t2(num int(5) zerofill,price float(7,2)zerofill,name varchar(10));

注意：只能用在数值型字段，自动加上无符号属性

3、auto_increment【自增】 #auto自动；increment增量，增加

当插入值为：NULL，0，留空时，会自动+1；当插入已经存在的值时，则会报错

注意：只能用于整数，字段值不允许重复（需要结合其他属性实现，如：primarykey） #primary主要的；初级的，基本的。

e.g. createtable if not exists t3(id int auto_increment primary key,namechar(10));

insertinto t3(id,name) values(null,”xiaofang”); #可以连续插入n次,null可以换成0

insertinto t3(name) values(“xiaofang”);

插入时，会按照最大的数加1的顺序插入

e.g. deletefrom t3 where id >1 and id <9;

然后按照前面的语句插入

select* from t3 order by id;

deletefrom t3;

insertinto t3(name) values(“xiaofang”); # * 5

select* from t3;

insertinto t3(id,name) values(100,”ashun”);

insertinto t3(name) values(“jichang”) # * 5

select* from t3 order by id;

最佳实践：每个表最好都设置一个ID字段，设置为自增长属性，auto_increment

4、NULL和 NOTNULL

NULL：默认是空

建议：在创建表时，每个字段都不要插入空值，因为NULL值在转换为其他程序语言时存在很多不确定因素。

NOTNULL：非空

e.g. create table if not exists t4(id int not null,name varchar(10) notnull,price double(7,2) not null) ;

5、default【缺省值】

e.g. createtable if not exists t5(id int not null default 0,name varchar(10) notnull default “NULL”,price double(7,2) not null default 0.00);

6、综合

createtable users(

id  int unsigned not null auto_increment primary key,

name  varchar(30) not null default “”,

height double(10,2) not null default 1.00,

age int unsigned not null default 1,

sex varchar(5) not null default ”man”);

四、创建索引

1、主键索引【primarykey】 #duplicate复制，使加倍 entry进入，侵入

作用：确定数据库表里一条特定数据记录的位置，一个表只能有一个主键，并且，主键的值不能为空。

建议：最好为每一个数据表定义一个主键！

e.g. 1)create table t7(id int not null auto_incrementprimary key,namevarchar(10));

2) createtable t7(

idint not null auto_increment,

namevarchar(10) not null '',

primarykey(id)); #在最后指定主键索引

2、唯一索引【unique】 #unique唯一的，独一无二的

都可以防止创建重复的值，但是，每个表可以有多个唯一索引

createtable if not exists users(id int not null auto_increment,namevarchar(30) not null default ''unique,age int,primary key(id));

3、常规索引【index/key】

是最重要的技术，可以提升数据库的性能，是数据库优化最先考虑的方面。索引可以提高查找的速度，但是会减慢插入，删除，修改的速度

和表一样是独立的数据对象，可以在创建表时使用，也可单独使用

单独使用时：createindex ind1 on users(name,age);

dropindex ind1 on users; #删除索引

创建时使用：createtable carts(

idint not null auto_increment,

uidint not null,

sidint not null,

primarykey(id),

keycuid(uid),

indexcsid(sid));

4、全文索引

fulltext类型索引，只能MyISAM表类型上使用，只有在varchar,char,text上使用。也可以在多个数据列上使用。

createtable books(

idint not null auto_increment,

booknamevarchar(30) not null unique,

pricedouble,

detailtext not null,

fulltext(detail),

indexind(bookname),

primarykey(id));

原始查询：select* from books where bookname like '%C++%';

现在查询：selectbookname,price from books where match(detail)against('C++');

select match(detail) against('C++') from books; #match 匹配；against倚，靠；

可以明显的查询速度！

MySQL创建数据表（下）

五、数据表类型及存储位置

1、MySQL与大多数数据库不同，MySQL有一个存储引擎概念。MySQL可以针对不同的存储需求选择不同的存储引擎。

2、 showengines; #查看MySQL所支持的存储引擎storageengine

3、 showvariables like 'table_type'; #查看默认数据表类型

MyISAM和InnoDB最常用的存储引擎【表类型】

4、指定表类型【使用哪一个存储引擎】：

createtable ...() engine = InnoDB;

注意：在一个数据库中可以在创建数据表时指定不同的表类型，即同一个库中可以有多个不同表类型的数据表存在

5、不同引擎的特点：

1）MyISAM表类型：

成熟稳定，易于管理，使用一种表格锁定的机制，需要经常使用“optimizetable 表名”来恢复机制所浪费的空间。

强调可以快速读取操作。但是也有一些功能不支持。

2）InnoDB表类型：

支持一些MyISAM所不支持的功能

缺点：占用空间大，不支持全文索引

对比：

功能

MyISAM

InnoDB

事务处理功能

No

Yes

数据行锁定

No

Yes

外键约束

No

Yes

表空间占用

0

相对大（最大2倍）

全文索引

0

0

存储(/var/lib/mysql/)

3个文件（.frm存储表结构

1个文件（.frm）

.MYD保存数据

.MYI 保存索引）

六、MySQL默认字符集

1、MySQL支持的数据编码

ASCII码 #7个字符存储

ISO-8859-1/latin1字符集 #西欧字符集，经常被程序员转码用，8位编码

gb2312-80 #不推荐

...

GBK【95年】 #2字节，可以用，但是不推荐，双字节编码

GB18030【2000年发布】#数据库支持比较少见

UTF-32 #4字节，不常用

USC-2 #2字节，Windows2000内部使用

UTF-16 #2/4字节编码，JAVA，WindowsXP，WindowsNT内部使用

UTF-8 #1～4字节编码，Unicode是互联网与UNIX/Linux与MySQL服务器广泛支持的字符集，强烈推荐

e.g. GBK2字节：namevarchar(12) 6个汉字

UTF-83字节：namevarchar(12) 4个汉字

2、MySQL服务器，数据库，数据表，字段都可以指定不同的字符集，用“showcharacter set；”可用于查看MySQL支持的所有字符集

注意：数据库中UTF-8在使用时为utf8

3、MySQL的字符集包括 #character字符

字符集【charset】：用来定义MySQL字符串的存储方式

校对规则【collation】：用来定义MySQL字符串的比较方式

是一对多的关系：1个字符集可以对应多个校对规则

showcollation like 'gbk%'; #可用来查看gbk所对应的校对规则

showcollation; #可用来查看所有的校对规则，以ci结尾，表示大小写不敏感，以cs结尾，大小写敏感，以bin结尾表示以二进制比较

showvariables like 'character_set_server'; #查看服务器端字符集

showvariables like 'collation_server'; #查看服务器段校对规则

4、指定默认字符集与校对规则

createdatabase xsdemo default character gbk collategbk_chinese_ci; #指定数据库默认字符集

create table t1(id int not null auto_increment primary key)engine=myisam default character set gbk collategbk_chinese_ci; #制定数据表的字符集

5、客户端与服务器交互时

character_set_client #客户端字符集

character_set_connection #连接字符集

character_set_result #返回结果字符集

通常情况下这三个字符集应该是相同的，才能使得数据传输是相同的，使用“setnames 字符集”可以同时修改这三个的值。

alterdatabase character set utf8; #修改数据库的字符集，alter修改，更改

altertable t1 character set utf8; #修改数据表的字符集

服务器的字符集就只能改配置文件了

6、备份数据库

mysqldump-u root -p --default-character-set=gbk -d xsdemo >/home/xiaofang/backup.sql #dump 倾倒；倾卸

7、还原数据库

mysql-u root -p xsdemo < /home/xiaofang/backup.sql

七、修改表

altertable... #更多内容参见?Alter table;

e.g. altertable t1 add price double not null default 0.00;

altertable t1 add sex varchar(5) after name; #在姓名之后添加性别

altertable t1 add height double first; #在第一位置添加身高

altertable t1 modify sex char(3); #modify适用于改类型

altertable t1 change name username varchar(5);#change 既可以更改字段名，又可以更改类型

altertable t1 rename users; #直接修改表名

altertable t1 drop age; #删除字段

droptable if exists users; #删除数据表