MySQL的详细操作

MySQL的详细操作

存储引擎
   不同的数据应该有不同的处理机制
   
   mysql存储引擎
      Innodb:默认的存储引擎  查询速度较myisam慢  但是更安全,支持事务,行锁,外键
由于以上的支持,数据更安全,建表时产生两个文件1.表结构文件,2.存储数据文件
      myisam:mysql老版本用的存储引擎,查询速度较与Innodb快
建表时有三个文件,1.表结构文件,2.存储数据文件,3.索引文件(能更快的查找数据,就像书的目录)
      memory:内存引擎(数据全部存在内存中)一个表结构文件(数据放在内存)
      blackhole:无论存什么 都立马消失(黑洞)一个表结构文件(不保存数据)

创建表的完整语法

create table 表名（

字段名1 类型[（宽度） 约束条件]，

字段名2 类型[（宽度） 约束条件]）；

注意：

1.在同一张表中，字段名不能相同

2.宽度和约束条件可选，字段名和类型是必须的

3.最后一个字段后不能加逗号

4.宽度指的是对存储数据的限制

数据类型

建表的时候,字段都会有对应的数据类型

整形

分类：TINYINT　　SMALLINT　　MEDIUMINT　　INT　　BIGINT

作用：存储年龄，等级，id，各种号码等

类型存储范围：

整型字段在整型类型后不加unsigned就有正负符号之分。

create table t1(x tinyint);

insert into t1 values(120),(-120);

create table t2(x tinyinr unsigned);

insert into t1 values(220);

强调：对于整型来说，数据类型后面的宽度并不是存储限制，

而是显示限制，所以在创建表时，如果字段采用的是整型类型，

完全无需指定显示宽度，默认的显示宽度足够显示完整当初存放的数据

严格模式补充

create table t1(x tinyint,name char);

存储数据时超过它们的最大存储长度，
发现数据也能正常存储进去，只是mysql帮我们自动截取了最大长度。但在实际情况下，
我们应该尽量减少数据库的操作，缓解数据库的压力，让它仅仅只管理数据即可
，这样的情况下就需要设置安全模式

 show variables like "%mode%";  # 查看数据库配置中变量名包含mode的配置参数

安全模式

修改安全模式
set session   # 只在当前操作界面有效
set global     # 全局有效

set global sql_mode ='STRICT_TRANS_TABLES'
修改完之后退出当前客户端重新登陆即可

浮点型

创建方法：create table t1（x float（255,30））；

分类：FLOAT　　DOUBLE　　DECIMAL

应用场景：身高，体重，薪资

字段限制特点（5,3）前一位表示所有的位数，后一位表示小数个数

三者最大整数位和小数位对比：

float（255,30）（精度最差）

double（255,30）（精度比float）

decimal（65,30）（精度最好）

字符类型(char与varchar)

create table t1（name char（4））；

分类：char（定长）varchar（变长）

char（4）：存储超出四个字报错，不够四个就用空格补充

varchar（4）：存储超出四个就报错，不够四个有几个就存几个

针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。
但是会在读出结果的时候自动取掉末尾的空格

如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

用select char_length(name) from t1;查询字符长度

char与varchar的使用区别

name char(5)
# 缺点：浪费空间
# 优点：存取速度都快

name varchar(5)
# 缺点：存取速度慢
# 优点：节省空间

日期类型

分类

date：2019-05-01
time：11:11:11
Datetime: 2019-01-02 11:11:11
Year:2019

测试

create table student(
id int,
name char(16),
born_year year,
birth date,
study_time time,
reg_time datetime
);
insert into student values(1,'egon','2019','2019-05-09','11:11:00',
'2019-11-11 11:11:11');

 

枚举与集合

分类
枚举enum 多选一
集合set 多选多

测试

create table user(
id int,
name char(16),
gender enum('male','female','others')
);
insert into user values(1,'jason','xxx') # 报错
insert into user values(2,'egon','female') # 正确！


create table teacher(
id int,
name char(16),
gender enum('male','female','others'),
hobby set('read','sleep','sanna','dbj')
);
insert into teacher values(1,'egon','male','read,sleep,dbj') # 集合也可以只存一个

约束条件

NOT NULL --标识该字段不能为空

约束条件初试>>>null与not null

create table t1（id int,name char not null）;

insert into t1 values(1,'j');  # 正常存储

insert into t1 values(2,null);  # 报错

总结 类型与约束条件区别

类型：限制字段必须以什么样的数据类型存储

约束条件：限制字段是在类型之外添加一种额外的限制

PRIMARY KEY (PK) --标识该字段为该表的主键，可以唯一的标识记录

单从约束角度来说primary key就等价于not null unique
create table t11(id int primary key);
desc t11;
insert into t11 values(1),(1); # 报错
insert into t11 values(1),(2);

除了约束之外，它还是innodb引擎组织数据的依据，提升查询效率

强调:
1.一张表中必须有且只有一个主键，如果你没有设置主键，
那么会从上到下搜索直到遇到一个非空且唯一的字段自动将其设置为主键

create table t12(
id int,
name char(16),
age int not null unique,
addr char(16) not null unique
)engine=innodb;
desc t12;

2.如果表里面没有指定任何的可以设置为主键的字段，
那么innodb会采用自己默认的一个隐藏字段作为主键，
隐藏意味着你在查询的时候无法根据这个主键字段加速查询了
索引：类似于书的目录，没有主键就相当于一页一页翻着查
3.一张表中通常都应该有一个id字段，并且通常将改id字段作成主键

联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键
create table t18(
ip char(16),
port int,
primary key(ip,port)
);
desc t18;

AUTO_INCREMENT --标识该字段的值自动增长（整数类型，而且为主键）

primary key + auto_increment

主键id作为数据的编号，每次最好能自动递增
create table t13(
id int primary key auto_increment,
name char(16)
);
insert into t13('jason'),('jason'),('jason'); # id字段自动从1开始递增
# 注意：auto_increment通常都是加在主键上，并且只能给设置为key的字段加

delete from tb1;
强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，
所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录
delete from tb1 where id > 10;

如果要清空表，使用truncate tb1；
作用：将整张表重置,id重新从0开始记录

FOREIGN KEY (FK) --标识该字段为该表的外键
pass

UNIQUE KEY (UK) --标识该字段的值是唯一的

单列唯一
create table user1(
id int unique,
name char(16)
);
insert into user1 values(1,'jason'),(1,'egon') # 报错
insert into user1 values(1,'jason'),(2,'egon') # 成功

联合唯一
create table server(
id int,
ip char(16),
port int,
unique(ip,port)
)
insert into server values(1,'127.0.0.1',8080);
insert into server values(2,'127.0.0.1',8080); # 报错
insert into server values(1,'127.0.0.1',8081);

DEFAULT --为该字段设置默认值

not null+default

create table user(
id int,
name char(16)
);
insert into user values(1,null) # 可以修改

alter table user modify name char(16) not null;
insert into user(name,id) values(null,2); # 报错 插入数据可以在表名后面指定插入数据对应的字段

create table student(
id int,
name char(16) not null,
gender enum('male','female','others') default 'male'
)
insert into student(id,name) values(1,'jason') # 成功

UNSIGNED --无符号，ZEROFILL --使用0填充

# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);