mysql中继_mysql操作篇续

# ### part1. 数据类型 - 时间

date YYYY-MM-DD 年月日 (纪念日)

time HH:MM:SS 时分秒 (体育竞赛)

year YYYY 年份值 (酒的年份,82年拉菲)

datetime YYYY-MM-DD HH:MM:SS 年月日 时分秒 (登录时间,下单时间)

create table t5(d date,t time , y year , dt datetime);

insert into t5 values("2019-11-21","09:30:30","2019","2019-11-21 09:30:30")

insert into t5 values(now(),now(),now(),now())

timestamp YYYYMMDDHHMMSS 自动更新时间(不需要手动写入,修改数据时候,自动更新,记录最后一次修改时间)

create table t6(dt datetime , ts timestamp);

insert into t6 values(null,null)

insert into t6 values(20191121093728,20380101050505)

insert into t6 values(20191121093728,20390101050505) error # 时间戳最多到2038年的某一天

# mysql 内置函数

now 获取当前时间

concat 拼接各个参数

user() 获取当前登录的用户

# ### part2 约束

# 关于约束的添加和删除

# 1 添加/删除 约束 not null

#alter table 表名 modify 字段名 类型

alter table t1 modify id int not null

alter table t1 modify id int

# 2 添加/删除 unique 唯一索引

# alter table 表名 add unique(id)

alter table t1 add unique(id)

alter table t1 drop index id

# 3 添加/删除 primary key

# alter table 表名 add primary key(id);

alter table t1 add primary key(id);

alter table t1 drop primary key;

# 4 添加/删除 foreign key 外键

alter table student1 drop foreign key student1_ibfk_1; #删除

alter table student1 add foreign key(classid) references class1(id) #添加

# 约束 : 对插入的数据进行限制,不满足约束的条件就直接报错

unsigned 无符号

not null 不为空

default 设置默认值

unique 唯一约束,数据唯一不能重复

primary key 主键,唯一不为空的值,表达这条数据的唯一性

auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)

zerofill 零填充 , int(6) , 位数不够6为,拿0来填充

foreign key 外键,把多张表通过一个字段联合在一起

# unsigned 无符号

create table t7(id int unsigned)

insert into t7 values(5)

insert into t7 values(-1000) error

# not null 不为空

create table t8(id int not null , name varchar(255));

insert into t8 values(1,"tianqi")

insert into t8 values(null,"tianqi") error

# default 设置默认值

create table t9(id int not null , name varchar(255) default "常远" );

insert into t9 values(1,null)

insert into t9(id) values(2)

# unique 唯一约束,数据唯一不能重复 [默认创建索引,通过索引可以加快查询速度,相当于字典的目录]

"""默认允许插入多个null空值 UNI """

create table t10(id int unique , name char(10) default "张龙");

insert into t10 values(1,null)

insert into t10(id) values(1) error

insert into t10(id) values(2)

insert into t10(id) values(null)

insert into t10(id) values(null)

# primary key 主键,唯一不为空的值,表达这条数据的唯一性

"""在一个表中,只能有一个字段标记成主键,一般标记id"""

# 原型 PRI

create table t11(id int not null unique ,name char(15) default "周永玲")

insert into t11 values(1,"你好")

insert into t11 values(null,"你好")

# primary key 创建主键

create table t12(id int primary key , name char(15) default "周永玲")

insert into t12 values(1,"你好")

# 两者同时存在 , 优先显示primary key 作为主键,

create table t12_2(id int primary key , name char(15) not null unique);

# 一个表只能让一个字段变成主键

create table t12_3(id int primary key , name char(15) primary key); error

# auto_increment 自增加一,(一般针对于主键 或者 unique 进行自增)

create table t13(id int primary key auto_increment , name char(15) default "尹家平");

insert into t13 values(null,"李四")

insert into t13 values(100,"张三")

insert into t13(id) values(null)

insert into t13(name) values("王二麻子")

# 使用默认值进行插入;

insert into t13 values();

# zerofill 零填充 , int(6) , 位数不够6为,拿0来填充

create table ceshi111(id int(6) zerofill );

insert into ceshi111 values(2)

insert into ceshi111 values(222222222)

# 删除:

# (1) delete from 表 where 条件 (删除数据,保留id)

delete from t13 where id = 1

delete from t13 ;

insert into t13(id,name) values(null,"王文")

# (2) truncate table 表名 (删除数据,重置id ,重置表)

truncate table t13

insert into t13(id,name) values(null,"王文")

# ### part3

# 1.联合唯一约束 : unique(字段1,字段2,..... ) 把多个字段拼在一起表达一个唯一的数据

# (1) 联合唯一索引 (在非空的情况,显示为主键 PRI)

create table t1_server(id int , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port));

insert into t1_server values(1,"aa","192.168.111.15",3306);

insert into t1_server values(1,"aa","192.168.111.15",3307);

insert into t1_server values(1,"aa","192.168.111.16",3306);

insert into t1_server values(1,"aa",null,null); error

# (2) 联合唯一索引 (在为空的情况,显示索引 MUL 代表普通索引)

create table t2_server(id int , server_name char(10) not null,ip char(15) , port int , unique(ip,port));

insert into t2_server values(1,"aa","192.168.111.15",3306);

insert into t2_server values(1,"aa","192.168.111.15",3306); error

insert into t2_server values(1,"aa","192.168.111.17",3306);

insert into t2_server values(1,"aa",null,null); # 允许插入多个控制,推荐使用第一种

| id | server_name | ip | port |

+------+-------------+----------------+------+

| 1 | aa | 192.168.111.15 | 3306 |

| 1 | aa | 192.168.111.17 | 3306 |

| 1 | aa | NULL | NULL |

| 1 | aa | NULL | NULL |

# (3) 联合唯一索引 和 主键 是否可以同时存在呢?可以同时存在 primary key 是真正的主键,联合唯一索引恢复成MUL索引状态

# 方法一

create table t3_server(id int , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port))

alter table t3_server add primary key(id);

# 方法二

create table t4_server(id int primary key , server_name char(10) not null,ip char(15) not null , port int not null , unique(ip,port))

# (了解) unique(ip,port) 联合唯一索引 , primary key(ip,port) 联合唯一主键 用法一样,区别在于后者不能在继续添加主键了

# foreign key 外键,把多张表通过一个字段联合在一起

"""外键的要求: 主动关联的这张表设置外键,要求被关联的表字段必须具有唯一属性 (unique 或者 primary key)"""

student:

id name age classname .... address

1 changyuan 81 python8 世外桃源 ...

2 zhouyongling 7 python8 世外桃源 ...

3 wangwen 18 python9 富丽华冠冕堂皇大酒店 ...

# 为了避免出现过多的字段,可以采取分表的形式,来减少冗余数据,提升查询的效率;

student1:

id name age classid

1 changyuan 81 1

2 zhouyongling 7 1

3 wangwen 18 2

class1:

id classname

1 python8

2 python9

# 创建class1表

create table class1(id int , classname varchar(255));

# 设置classid 为主键或者唯一索引

alter table class1 add unique(id);

# 创建student1表

create table student1(

id int primary key auto_increment,

name varchar(255) not null,

age int not null,

classid int,

foreign key(classid) references class1(id)

);

# 插入数据

insert into class1 values(1,"python8")

insert into class1 values(2,"python9")

insert into student1 values(null,"changyuan",81,2);

insert into student1 values(null,"zhouyongling",7,1);

insert into student1 values(null,"wangwen",7,2);

# 删除class1 如果这条数据在多张表中被使用,直接删除会报错,因为有外键关联

delete from class1 where id = 1

# 把关联的数据删掉之后,才可以

delete from student1 where id = 2;

delete from class1 where id = 1;

# 联级删除 联级更新 (谨慎操作)

"""

联级删除 on delete cascade

联级更新 on update cascade

"""

# 创建class2

create table class2(id int unique , classname varchar(255));

# 创建student2

create table student2(

id int primary key auto_increment,

name varchar(255) not null,

age int not null,

classid int,

foreign key(classid) references class2(id) on delete cascade on update cascade

);

# 插入数据

insert into class2 values(1,"python8");

insert into class2 values(2,"python9");

insert into student2 values(null,"changyuan",81,2);

insert into student2 values(null,"zhouyongling",7,1);

insert into student2 values(null,"wangwen",18,2);

# 联级删除class2数据

delete from class2 where id = 2

# 联级更新

update class2 set id = 100 where classname = "python8"

# 表和表之间的关系

(1) 一对一 表1:id m1 m2 m3 m4(表2的id数据) 表2: id m5 m6 m7

(2) 一对多 或多对一 : 1个班级 可以对应多个学生 把学生作为主动关联的表,其中设置一个外键,去关联那个唯一的数据

(3) 多对多 : 一个学生可以对应多个学科,一个学科可以多个学生学习,

一本书可以对应多个作者,一个作者可以写多本书

xueke (表1)

id name

1 math

2 english

3 huaxue

student (表2)

id name

1 wangwen

2 changyuan

3 zhouyongling

relation (表3) 把 xid 和 sid 设置成外键 关联xueke 的 id 和 student 的 id

xid sid

1 1

1 2

1 3

2 1

2 2

2 3

3 1

3 2

3 3

# 存储引擎 : 存储数据的方法

"""

show engines

"""

# 概念理解:

表级锁: 如果有人修改这张表,就直接上锁,其他人无法修改,速度慢,不能并发 (MyISAM)

行级锁: 如果有人修改这个表中的一个记录,当前这条记录会上锁,其他数据可以进行修改,允许更大的并发和更快的速度 (InnoDB)

事务处理 : 如果执行sql语句,在全部执行成功之后,在选择提交,如果操作时,有一条失败,直接回滚,恢复成初始状态

begin : 开启事务

commit: 提交数据

rollback:回滚数据

# 存储引擎:

MyISAM : 是5.6版本之前,默认的存储引擎,支持表级锁

InnoDB : 是5.6版本之后,默认的存储引擎,支持行级锁,能够抗住更大的并发

BLACKHOLE : 黑洞,用来同步数据的,场景发生在服务器集群,用在:主从数据库 [主:查询 ,从:增删改]

MEMORY : 把数据存储在内存当中,也可以作为缓存

create table myisam1(id int , name char(10)) engine = myisam;

myisam1.frm 表结构

myisam1.MYD 表数据

myisam1.MYI 表索引

create table innodb1(id int , name char(10)) engine = innodb;

innodb1.frm 表结构

innodb1.ibd 表数据 表索引

create table memory1(id int , name char(10)) engine = memory;

memory1.frm 只有一个表结构,数据在内存中

create table blackhole1(id int , name char(10)) engine = blackhole;

blackhole1.frm