mysql语言定义_MySQL定义语言[DDL]

创建表tablecreate table table_name (

列名 属性,

age int,

...

name varchar(10)

);

整型列tinyint：1字节，取值范围[-128 ~ 127]，非负取值范围[0 ~ 255]

smallint：2字节，取值范围[-32768 ~ 32767]，非负取值范围[0 ~ 65535]

mediumint：3字节，取值范围[-8388608 ~ 8388607]，非负取值范围[0 ~ 16777215]

int：4字节，取值范围[-21亿 ~ 21亿]，非负取值范围[0 ~ 42亿]

bigint：8字节

整型例的可选参数【属性】各种整型数据类型默认为带符号的取值范围

unsigned：无符号，列的值从0开始，不为负。

[M] zerofill：适合用于学号，编码等固定宽度的数字，可以用0填充至固定宽度，并且默认决定列为unsigned属性。

如果数字位数超过了[M],并不影响，MySQL会字节存取。alert table table_name add 列名 tinyint(5) zerofill;    添加一个固定宽度为5的列

浮点列与定点列float[M,D]：浮点型，M表示精度，总位数；D表示标度，小数位数；这两个参数可以省略不写，默认为普通浮点数。

double[M,D]：与上相同，只是默认精度更高。

decimal[M,D]：定点型，float和double有精度损失，而decimal没有，更精确。

字符型列

类型宽度可存字符实存字符[i<=M]实占空间利用率

charMMiM<=100%

varcharMMii字符(+1-2)字节<100%char型如果不够M个字符，内部活用空格补齐，取出时再把右侧空格删掉。[这意味着，如果右侧本身有空格，将会丢失]，但是char速度更快。

M是字符，是几就表示可以存几个汉字。

text：文本型，可存储约6万字。

blob：用来存储二进制文件，如图片，音频等，由于采用的是二进制形式，不用考虑字符集。

enum：枚举型，是定义好值就在某几个枚举范围内。alert table table_name add 列名 enum("男","女");    //只能选址一个值进行插入操作

alert table table_name add 列名 set("男","女");    //可以选择一个或多个值进行插入操作

日期时间型列类型样式范围

year19951901~2155

date1998-12-311000/01/01~9999/12/31

time12:32:45-838:59:59 ~ 838:59:59

datetime1998-12-31 12:32:45-

timestamp1512394064可不写，系统自动获取

列的默认值因为null查询不便且索引效率不高，所以在实际应用中，应尽量避免列的值为null，如果想避免，声明列not null default "默认值"alert table table_name add 列名 int not null default 1;

主键与自增主键：次列不重复，能够区分每一行。//写法一

create table table_name(

id int primary key,

name char(10)

);

//写法二

create table table_name(

id int,

name char(10),

primary key(id)

);自增：一张表，只能有一个自增列，且此列必须加索引[index/key id]create table table_name(

id int,

name char(10),

index id(id)

);主键且自增create table table_name(

id int primary key auto_increment,

name char(10),

);

列的增删改alert table 表名 add 列名 列类型 列属性；               //添加列，默认在表最后

alert table 表名 add 列名 列类型 列属性 after 列名；     //添加一个新列并放在某列之后

alert table 表名 drop column 列名；                   //删除列

alert table 表名 change 列名 新列名 新列类型；          //修改列名和列类型

alert table 表名 modify 列名 新列类型；                //只能修改列类型

视图create view  视图名 as select 列1, 列2, 列3 from table_name;如果一个查询结果集需要经常被用到，就需要创建视图。

又被称为虚拟表，是sql的查询结果。

作用：权限控制。

比如说某几个列允许用户查询，其他列不允许，可以通过视图开放其中一列或几列起到权限控制的作用。

简化复杂的查询

如果视图的所有列都来自物理表，那么视图中的数据就可以修改；如果视图中存在某一列或几列是经过物理表中的列进行运算得到的，那么视图中这样的列中的数据就不能被修改。

视图的algorithm[视图放在哪？]//指定使用merge算法

create algorithm=merge view  视图名 as select 列1, 列2, 列3 from table_name;

指定使用temptable算法

create algorithm=temptable view  视图名 as select 列1, 列2, 列3 from table_name;

//由MySQL自己决定使用何种算法

create view  视图名 as select 列1, 列2, 列3 from table_name;对于简单查询形成的view，再对view进行查询时，如where，order等等，可以把建视图语句+查视图语句合并成 --> 查物理的语句。这种视图的算法叫merge [合并]

也有可能创建视图的语句本身就比较复杂，很难在和查询视图的语句进行合并，MySQL可以先执行视图的创建语句，把结果集形成内存中的临时表，然后去查询临时表。这种视图的算法叫temptable [临时表]

常用表管理语句show tables;                                //查看所有表,视图也会包含在内。

desc 表名;                                   //查看表详细信息

drop table 表名;                             //删除表

drop view 视图名;                            //删除视图

show create table 表名/视图名;                //查看建表/视图过程

show table status \G;                       //查看所有表详细信息，\G表示竖排显示

show table status where name = '表名' \G;    //查看指定表详细信息

rename table 旧表名 to 新表名;                //改表名

truncate 表名;                               //想当于删除后有重建了这个表

delete from 表名;    //删除表中所有数据，但如果继续插入数据，自增列会在原有基础上继续自增

存储引擎的概念create table table_name(

id int,

name char(10)

)engine innodb charset utf8;数据库对同样的数据有着不同的存储方式和管理方式，在MySQL中称之为数据引擎

在MySQL_5.5.5以前默认引擎是Myisam，从MySQL_5.5.5开始，默认引擎改为Innodb

Myisam数据组织方式：每一个数据库形成一个文件夹，每张表包含3个文件放在该文件夹下table_name.frm        //表结构文件

table_name.MYD      //表数据文件

table_name.MYI        //表索引文件

可以直接把文件夹拷走，就是一个完整的数据库

Myisam不支持事务

Innodb数据组织方式：所有数据放在一个文件内

字符集与乱码问题create table table_name(

id int,

name char(10)

)engine innodb charset utf8;乱码是因为文字本来的的字符集与展示的字符集不一致导致

校对集：就是排序规则

一套字符集可以有几套校对集

character_set_client = 'utf8'           //设置客户端发送与可接收字符集

character_set_connection = 'utf8'       //设置解释字符集

character_set_results = 'utf8'          //设置数据库返回与可接收字符集

set names utf8;                         //就是将以上三个变量同时设为utf8字符集解释器可以将不同编码进行翻译

如果客户端和MySQL使用的是不同的字符集，就会出现乱码

连接器使用GBK或者UTF8都可以，但是不可以使用编码范围比较小的字符集，比如ASCII，那样会造成数据丢失

谁能连接数据库谁就是客户端，如PHP和MySQL的控制窗口就是两个不同的客户端

保证HTML文件，数据库，处理脚本(如PHP)，查询语句前的声明保持一致，就可以避免乱码

索引的概念key                //普通索引，纯粹为了加快查询速度

unique key         //唯一所以，一是为了加快查询速度，二是为了约束数据 [不能重复]

primary key        //主键索引，不需要起名，因为一张表就一个

fulltext           //全文索引，在中文环境下，几乎无效create table table_name(

id int,

name char(10),

key name(name),

unique id(id)

);索引是数据的目录，能快速定位行数据的位置，索引提高了查询速度，但是影响了增删改的速度，并非加的越多越好，一般在查询频率比较高的列上加，而且在重复度的列上加效果更好

索引长度：建索引时，可以只索引列的前一部分内容，比如前10个字符create table table_name(

id int,

name char(10),

key name(name(5))

);多列索引：就是把2列或多列的值，看成一个整体然后索引。create table table_name(

xing char(5),

ming char(10),

key xm(xing,ming)

);

insert into table_name values ('朱','元璋');                     //插入一条数据

select * from table_name where xing = '朱' and ming = '元璋';    //索引生效

select * from table_name where xing = '朱';                     //索引生效

select * from table_name where ming = '元璋';                    //索引失效索引是左前缀发挥作用

冗余索引：就是在某个列上，可能存在多个索引create table table_name(

xing char(5),

ming char(10),

key xm(xing,ming),

key ming(ming)

);

索引的操作语法show index from 表名; || show create table 表名;                     //查看索引

alter table 表名 drop index 索引名;  ||  drop index 索引名 on 表名;    //删除索引

alter table 表名 add 索引名(列名);                                    //添加索引

alter table 表名 add primary key (列名);                             //添加主键索引

alter table 表名 drop primary key;                                   //删除主键所以

事务的概念//事务执行成功

start transaction

update 表名 set money+500 where id=2;

update 表名 set money-500 where id=1;

commit;

//事务执行失败，进行回滚

start transaction

update 表名 set money+500 where id=2;

update 表名 set money-500 where id=1;

rollback;比如转账就可以看作一个事务，包含A扣款，B收款两个部分，只有两部分都成功，才能说一个事务完成

事务具有以下四个特点隔离性：用户看不到事务的中间态

原子性：不论事务中间过程过么复杂，最后结果要么是事务执行之前的状态，要么是事务执行成功的状态

一致性：事务前后的数据保持守恒

持久性：事务一旦执行成功，就不能rollback