博客

上接上一篇

MySQL数据库

/* 列属性相关

数据类型 对数据进行统一的分类，从系统而言，可以统一的进行管理
数据类型包括：数值类型，字符串类型，时间日期类型。
*/

-- 1， 数值型 
-- 数值型包括：整数型，小数型
-- a 整数型  
-- Tinyint 迷你整型  1个字节 256     
-- Smallint 小整型   2个字节 65535
-- Mediumint 中      3个字节
-- int               4个字节
-- Bigint            8个字节

-- 创建整型表

create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;
-- 限定数据类型和数据的插入范围插入的数据都默认有符号,可以通过unsigned限定无符号数据

insert into my_int values(100,100,100,100);
-- 增加无符号数据列
alter table my_int
add int_5 tinyint unsigned
after int_4;

insert table my_int values(100,100,-100,-100,200);

-- 在查看数据表时，显示的类型后面都有数字，表示数据最终显示的位数包含符号。
-- 指定数据显示的宽度,默认告诉用户可以显示的宽度，用户可以控制数据显示的宽度，这种控制不会改变数据本身的大小。
alter table my_int
add int_6 tinyint(1) unsigned
;
insert into my_int values(123,0,0,0,255,255);
-- 超出数据显示的宽度，依然可以正常显示，通常用于当数据不够显示宽度的时候，用0进行填充，通过zerofill.通过0填充，保证数据显示格式

alter table my_int
add int_7 tinyint(3) unsigned zerofill;
insert into my_int values(123,0,0,0,255,255,2);


-- b.小数型 分为浮点型和定点型。
--浮点型 小数点浮动，超出指定范围丢失精度，进行四舍五入
---float 单精度  占用四个字节 精度范围7位左右。
-- double 双精度 占用八个字节 精度范围15位左右

-- 创建浮点数表float(M,D) M总长度  D小数点长度   整数长度M-D
create table my_float(
f1 float,
f2 float(3,2),
f3 float(6,2)
)charset utf8;
--插入数据
--整数部分不可以超出长度，小数部分可以超出长度，但是会进行四舍五入，如果整数部分由于进位而超出长度，系统可以允许。
insert into my_float values(1000.10,9.10,1000.10);
insert into my_float values(999999999,9.99,9999.99);
insert into my_float values(3e38,9.99,9999.2);

-- 小数型定点型
-- 绝对保证整数部分不会被四舍五入丢失精度，小数部分有可能（理论上小数部分也不会丢失精度）
-- 变长，大致是每9个数字，采用4个字节存储，整数和小数分开计算 
-- M 最大是65
-- D 最大是30
-- 默认是10,2，65个9
create table my_decimal(
f1 float(10,2),
d1 decimal(10,2)
)charset utf8;
-- 插入数据，整数部分不能超出长度，小数部分可以随意超出长度
insert into my_decimal values(12345678.99,12345678.90);
insert into my_decimal values(123.123456,1234.123456);
-- 浮点数如果进位导致长度溢出没有问题，但是定点数不行
insert into my_decimal values(99999999.99,99999999.99); -- 没有问题
insert into my_decimal values(99999999.99,99999999.999); --进位超出范围，小数部分超出范围

-- 时间日期类型。
--Datetime时间日期，格式是YYYY-MM-dd HH:ii:ss,表示的范围是从1000到9999，有0值，
-- 0000-00-00 00:00:00
-- Date:日期就是datetime中的date部分 Time:时间(段) 指定的某个区间之间，时间到时间
-- 时间戳，并不是时间戳只是从1970年开始的时间表示，
-- YEAR 有两种表示方式，year(2)和year(4)

-- 创建时间日期表
create table my_data(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;

-- 插入数据，时间time可以是负数，而且可以是很大的负数。 year可以用两位和四位数表示

insert into my_data values('2018-4-20 16:17:49','2018-4-20','-16:50:54','2018-4-20 16:17:49',2018);
insert into my_data values('2018-4-20 16:17:49','2018-4-20','-111:50:54','2018-4-20 16:17:49',2018);
insert into my_data values('2018-4-20 16:17:49','2018-4-20','-1 6:50:54','2018-4-20 16:17:49',2018);-- -1表示过去一天加上24小时

insert into my_data values('2018-4-20 16:17:49','2018-4-20','-16:50:54','2018-4-20 16:17:49',66);
insert into my_data values('2018-4-20 16:17:49','2018-4-20','-16:50:54','2018-4-20 16:17:49',70);

--timestamp:修改记录时自动进行更新，修改时间戳
update my_data set d1='2018-4-21 16:15:06' where d5=2069;


/*
字符串类型
在SQL中将字符串类型分为六类：char,varchar,text,blob,enum,set
*/

-- 定字符串
--定字符串：char 二维表在定义结构时候就已经确定了最终数据的存储长度
-- char(L) L为可以存储的长度，单位为字符，最大长度值可以为255.
-- char(4) 在utf8中需要4*3=12字节
-- 变长字符串
-- 变长字符串：varchar,在分配空间的时候，按照最大的空间分配，如果实际上最终用了多少，是根据具体的数据来确定的
-- varchar(L) L表示字符长度，理论长度是65536个字符，但是会多出1到2个字节来确定存储的实际长度，
-- varchar(10) 的确存储了10个汉字，utf8环境，10*3+1=31 bytes

/*
定长与变长的存储实际空间（UTF8）
实际存储数据  Char(4)     Varchar(4)    Char占用字节      Varchar(占用字节) 
ABCD          ABCD         ABCD           4*3=12           4*3+1=13    
A             A            A              4*3=12           1*3+1=4     
ABCDE                                     数据超过长度     数据超过长度                          

如何选择定长或者变长字符串，定长的磁盘比较浪费空间，但是效率高。
变长的磁盘空间节省但是效率低；如果数据不能确定长度（不同数据间有变化）

*/

--文本字符串
-- 如果数据量非常大，通常说超过255个字符就会使用文本字符串，文本字符串根据数据的格式进行分类
-- 文本字符串根据存储数据的格式进行分类，text 和 blob
-- text :存储文字
-- blob 存储二进制数据（通常不用）

/*
枚举字符串
枚举：enum事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个

枚举的使用方式：
 定义：enmu(可能出现的元素列表)： 例如：enum('','','');
 使用：存储数据，只能存储上面定义好的数据。
 作用：节省空间，在mysql中系统自动转换格式。
 枚举原理：枚举在进行数据规范的时候，系统会自动创建一个数字与枚举元素的对应关系（关系位于日志中），然后在进行数据插入的时候，系统自动将字符串转换成对应的数据存储，然后在进行数据提取的时候
 系统自动将数值转换成对应的字符串显示。
 */

-- 创建枚举表
create table my_enum(
gender enum('男','女','空')
)charset utf8;

-- 加入数据
insert into my_enum values('男'),('空')；
-- 按照枚举类型出现的顺序进行编号，从1开始（65535个）
select gender+0,gender from my_enum;

-- 集合字符串
/* 
集合与枚举类型，实际存储的是数值，而不是字符串，（集合是多选）

集合使用方式：

 定义：set(元素列表)
 使用：可以使用元素列表中的元素，使用逗号分隔
 插入数据：可以是字符串组合，也可以是直接插入数值。
 
 集合中每个数据对应一个二进制位，被选中为1，没有则为0，最后反过来将二进制转换为十进制。
 
 集合的优点在于能够规范数据和节省空间
*/
create table my_set(
car set('BMW','Audi','QAR','大众')
)charset utf8;
-- 插入数据
insert into my_set values('BMW,Audi,QAR');-- 0111   -> 7 
insert into my_set values(3);

-- 查看集合数据
select car+0,car from my_set;
insert into my_set values(15)-- 选中集合中的所有元素

/* mysql中规定，任何一条记录最长不超过65535个字节

varchar实际存储长度能达到多少？看字符集编码


*/
create table myutf8(
name varchar(21844)-- 最长长度 21844*3+2=65534
)charset utf8;

create table mygbk(
-- 最长长度 32766*2+2=65534
)charset gbk;

-- 如果想用完整个65535个字节，增加一个字节,
-- 但是在mysql中如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL，如果想释放NULL所占用的字节，必须保证所有的字段都不允许为空。
create table mygbk1(
tinyintt tinyint not null,
name varchar(32766) not null
)charset gbk;

-- 文本字符串不占用记录长度，额外存储，但是text文本字符串也是属于记录的一部分，一定需要占据记录中的部分长度，10个字节。与编码方式无关。（主要用于保存数据的地址和长度）

create table myutf8(
name varchar(21841) not null, -- 最长长度 21841*3+2=65525
content text not null    -- 占用十个字节
)charset utf8;

/* 
列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性。

列属性有很多：NULL,NOT NULL ,default,Primary Key, unique Key,  auto_increment, comment

*/

/* 
空属性：
NULL  
NOT NULL

虽然默认的，数据库基本都是字段为空，但是实际上在真实开发的时候，尽可能的要保证所有数据都不能为空，空数据没有意义。空数据没有办法参与运算。
null 默认为空，不写则为默认值
*/
-- 创建一个班级表

create table my_class(
name varchar(20) not null,
room varchar(20) null 
)charset utf8;

/*
列属性：comment,描述，没有实际含义，是专门用来描述字段，会根据表创建语句保存
为了数据库管理员管理数据库进行了解。位于字段之后

*/

create table my_teacher(
name varchar(20) not null comment '姓名',
money decimal(10,2) not null comment '工资'
)charset utf8;

/*
默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
default

*/
create table my_default(
name varchar(20) not null,
age tinyint unsigned default 0,
gender enum('男','女','保密') default '男'
)charset utf8;

-- 要使默认值生效，在赋值时不给默认数据赋值，想要使用默认值，可以不一定去指定列表，故意不使用字段列表，可以使用默认值default
insert into my_default (name) values('高强');
insert into my_default values('高零',18,default);

/* 字段属性
主键，唯一键  自增长

主键 primary Key
一张表只能有一个字段可以使用相应的键，用来唯一的约束该字段里面的数据，不能重复，
一张表中只能有最多一个主键

*/
-- 增加主键
-- 直接在字段后进行定义，主键本身不允许为空
--非常直接，缺点，只能使用一个字段作为主键
create table my_key(
name varchar(20) not null comment '姓名',
number1 char(10) primary key comment '学号:itcast+0000,不能重复'
)charset utf8;

-- 在创建表的时候，在所有的字段之后，使用primary key(主键字段列表)来创建，如果有多个字段作为主键，则为复合主键。

create table my_key1(
name varchar(20) not null comment '姓名',
number1 char(10) comment '学号:itcast+0000',
course char(10) comment '课程代码:333+0000',
score tinyint unsigned default 60 comment '成绩',
primary key(number1,course)
)charset utf8;
-- 表创建好以后直接额外追加主键，可以通过修改字段属性，也可以直接追加。(创建的表不包含主键的情况下追加主键)
create table my_key3(
name varchar(20) not null comment '666'
)charset utf8;

alter table  my_key3 add primary key(name);

/*
主键约束：
主键对应的数据应具有唯一性，不能重复
复合主键中只要两个主键都相同才算冲突。
*/

--主键的更新与删除
alter table my_key3 drop primary key;
/*
自动增长；
当对应的字段，不给值或者说给默认值，或者给NULL的时候会自动
被系统触发，系统会从当前字段中已有的最大值在进行+1操作，得到下一个新的在不同的字段。
自增长通常跟主键搭配，
自增长特点：auto_increment
1.任何一个字段要做自增长必须前提是本身是一个索引（key一栏有值）
2.自增长字段必须是数字整型
3.每个表只能有一个自增长字段
*/

-- 自增长

create table my_auto(
id int primary key auto_increment comment '自动增长',
name varchar(10) not null
)charset utf8;

--自增长的使用,自增长的第一个元素是1，自增长每次都是自增1，当对应字段赋值时，自增长失效，下一次从最大值开始自增长。

insert into my_auto(name) values('gbk'),('yeyeye');
insert into my_auto values(null,'yryryry');
insert into my_auto values(default,'ggg');

-- 通过表创建语句查看下一次增长的数是多少。
show create table my_auto;

--修改自增长，修改自增长，必须先删除后增加。一张表只能有一个自增长
-- 修改当前表选项自增长的值只能向上修改不能向下修改，向下修改不生效。

alter table my_auto auto_increment=10;

-- 查看自增长变量
show variables like 'auto_increment%';-- 修改自增长变量是对整个数据库进行修改

-- 修改是会话级，即当次修改有效。
-- auto_increment_increment  
-- auto_increment_offset
-- set 变量名=2;

-- 删除自增长
-- 自增长是字段的属性，可以通过modify来进行修改，保证字段没有auto_increment即可）
alter table my_auto modify id int primary key;-- 主键已经存在会出现主键重复定义。
alter table my_auto modify id int;-- 有主键时不可以加主键。

-- 增加唯一键
-- 一张表往往有很多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键解决表中有多个字段需要唯一性约束的问题。
-- 唯一键的本质与主键差不多，唯一键默认的允许自动为空，而且可以有多个为空，空字段不参与唯一性的比较。

-- 增加唯一键
/*
1，在创建表的时候，字段之后直接跟unique或者 unique key
2. 在所有字段之后增加unique key(字段列表)，复合唯一键，
3. 在创建表之后增加唯一键。
*/
-- 1
create table my_unique(
number1 char(10) unique comment '学号：唯一，允许为空',
name varchar(20) not null
)charset utf8;
-- 2
create table my_unique2(
number2 char(10) not null comment '学号',
name varchar(20) not null,
unique key(number2)
)charset utf8;

-- 3

create table my_unique3(
id int primary key auto_increment,
number char(10) not null,
name varchar(20) not null
)charset utf8;