mysql2_mysql2_约束

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

"""mysql中""和''的意义相同,并且不区分字母的大小写"""

"""对数据库中的存在null的记录进行增删改查,where id=null并没有效果,得写成 id is null"""

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

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

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

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

"""重要的事情说三遍,这4个数据类型:都是字符串,都是字符串,都是字符串"""

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

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

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

# now() 插入记录时将当前时间datatime插入进去,数据类型自动筛选合适的数据插入

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

create table t6(dt datatime,ts timestamp);

insert into t6(dt,ts) values(null,null);

insert into t6 values(20191121093728,20380101050505)

#插入null就写入当前时间,插入具体时间就写具体时间

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

"""注:timestamp存进去的是时间字符串,要删除他要加引号'2019-11-21 19:00:14'"""

# mysql 内置函数

now() 当前时间(datetime时间)

concat(参数1,参数2,...) 拼接各个参数

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

# ### part2 约束

# 关于约束的创建,添加和删除

# 1 not null 不为空 约束 (定义一个字段时如果不说,一般默null(yes),也就是可以为null)

"""注:添加约束not null前,如果原本字段下有记录,并且记录中有null,

这个时候只能删除这个数据再添加约束not null"""

# alter table 表名 modify 字段名 类型 null(yes/no) key....

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

添加 alter table t6 modify dt datetime not null;

删除 alter table t6 modify dt datetime;

# 2 unsigned 无符号

创建 create table t7(id int unsigned)

insert into t7 values(5)

insert into t7 values(-1000) error

# 3 default 设置默认值

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

insert into t9 values(1,null)

insert into t9(id) values(2)

# 4 index 普通索引(MUL),建立辅助索引,加快查询速度(适当建立index,多了反而查询变慢)

#建表时创建

create table t1(id int primary key auto_increment,

name char(100),sex char(10),index index_name(name)) # ... 索引 索引名(name)

# 添加方法一

create index index_name on t1(name) #创建 索引 索引名 在 t1的name上

# 添加方法二 alter添加命令和create建表创建命令相似

alter t1 add index index_name(name) #更改 t1 添加 索引 索引名(name)

# 删除索引 drop删除和create添加的命令相似

drop index index_name on t1 #删除 索引 索引名 在t1上

# 5 unique 唯一索引(UNI) 数据唯一不能重复

""" 1 默认创建索引,通过索引可以加快查询速度,相当于字典的目录

2 默认允许插入多个null空值

3 同理.添加unique前如果这个字段下的记录中有两个相同的数据,

是不能添加unique成功的,因为unique是唯一的索引"""

# alter table 表名 add unique(dt)

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

添加 alter table t6 add unique(dt);

删除 alter table t1 drop index dt """注意写法不一样"""

# 6 primary key 主键(PRI) 数据唯一不为空

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

一个字段先被添加成unique ,显示为UNI,再加个not null约束,就会显示成PRI,

但他并不是真正的PRI,当设置另一个字段为primary key时,

之前那个PRI就会变回原来的UNI

"""

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

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

添加 alter table t1 add primary key(id);

删除 alter table t1 drop primary key; #删除 不带字段名,多此一举而且报错,因为primary key全表唯一

# 7 foreign key 外键 #关联双方的类型一定要相同

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

删除 alter table student1 drop foreign key student1_ibfk_1;

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

"""设置自增之前要把这个字段设置成主键或者unique,不然会报错"""

"""自增的那个字段下直接插null就会自增"""

创建 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 values();

添加 alter table t3 modify id int auto_increment; #建表时id的类型啊啥的,是啥跟啥,索引和主键不用跟

# 9 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,如果原来设置的自增id,现在还在原最大id的基础上自增+1)

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

# ### 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 外键,把多张表通过一个字段联合在一起 关键字key -> MUL

"""外键的要求: 主动关联的这张表设置外键,要求被关联的表字段必须具有唯一属性 (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 : 把数据存储在内存当中,也可以作为缓存

创建一个MyISAM存储引擎的表 create table t3(....) engine=myisam

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