一、约束条件
1、主键约束
主键约束 (primary key):用于标识表中的主键列的值,而且这个值是全表当中唯一的,而且值不能null,一个表只能有一个主键 。
在创建表的时候创建主键
#创建表和主键的方法一
CREATE table stu01(
id int(5),
name char(10),
score DECIMAL(5,2),
address VARCHAR(128),
PRIMARY KEY (id)#创建主键
);
#创建表和主键的方法二
CREATE table stu01(
id int(5) PRIMARY KEY,
name char(10),
score DECIMAL(5,2),
address VARCHAR(128)#创建主键
);
查看表结构
desc stu01;
向表中插入一条数据
#插入一条数据
INSERT into stu01 VALUES (1,'张三',89.9,'地址不详');
查看表中信息
SELECT * from stu01;
id为主键,插入一条id相同的数据,提示信息报错,不可插入。
INSERT into stu01 VALUES (1,'李四',89.9,'上海');
id为主键,插入一条id为空的数据,提示信息抱错,不可插入。
INSERT into stu01 VALUES (null,'李四',89.9,'上海');
删除主键
ALTER TABLE stu01 DROP PRIMARY key ;
以上展示了主键的约束效果。
2、外键约束
外键:用来建立表与表之间的关系,确保外键中的值与另一个表的主键值匹配,保证数据引用的完整性。
适应场景:多表联查,不要超过三张,三张以上会降低查询效率。
创建两个表,一个主表,一个从表
#创建学生表作为主表
CREATE TABLE if not EXISTS student(
#表示在创建表时自动检测表是否存在,如果不存在就创建。
crad_id int(5) PRIMARY KEY auto_increment,
#atuo_increment 自增长字段
#每条记录会自动递增1,自增长必须是主键,也不可以重复。如果添加失败下一跳记录也会自动增加
stu_name CHAR(10) not null,
stu_email VARCHAR(128) not null UNIQUE key
);
创建class表作为从表
CREATE TABLE class(
stu_id int(11) zerofill PRIMARY KEY auto_increment,
address VARCHAR(128) DEFAULT '地址不详',
crad_id int(5) not null,
#格式要与主表一样
foreign key (crad_id) references student (crad_id)
#声明外键
);
插入数据
先插入主表数据在插入从表数据,且从表的外键要与主表所关联是数据要一致,否则插入不成功
INSERT into student VALUES(320323,'张三','125426@qq,com');
INSERT into class VALUES(1,'南京',320323);
删除外键
先查询外键的索引名称之后才能删除外键
删除外键的索引名称
ALTER TABLE class DROP foreign key class_ibfk_1;
总结
外键就是和主表进行关联的列,不需要设置为从表的主键,但是不能为空,必须和主表的数据类型保持一致。
外键的值和主键的值要相同,先插入主表的数据,在插入从表的数据
删除表的外键不是直接删除外键的列名,而是删除外键的索引,show create table 表名:查看表的详细信息
删除主键不需要加上主键的列名,如果有extra的额外属性,比如自增长,要先移除属性,然后才能删除主键。
删除主键的方式:alter进行修改
3、非空约束
非空约束( not NULL):保证列中的值不含null值
创建表的时候,在字段类型后面设置not null 在插入数据时,不可为空,否则报错。
CREATE table stu02(
id int(5),
name char(10) not NULL ,#非空约束
score DECIMAL(5,2) not NULL ,#非空约束
address VARCHAR(128),#没有not null 可以为空
PRIMARY KEY (id)
);
4、唯一约束
唯一约束(unique key ):确保列中的所有值都是唯一的,类似主键,但是一个表可以有多个唯一约束 。
创建表的时候创建唯一约束
CREATE table stu02(
id int(5),
name char(10) not NULL UNIQUE key,#UNIQUE key唯一约束,前提条件不可为空
score DECIMAL(5,2) not NULL UNIQUE key,#UNIQUE key唯一约束,前提条件不可为空
address VARCHAR(128),
PRIMARY KEY (id)
);
name设置唯一约束,新增name一样的数据,提示错误,name被设置唯一,不可重复
score设置唯一约束,新增score一样的数据,成绩一样,提示错误score被设置唯一,不可重复。
5、自增约束
自增约束:在列生成的每一行都会自动生成一个唯一标识符,通常和主键一起使用,每次插入新行时,自增列的值会自动增加。
CREATE TABLE if not EXISTS test01(
#表示在创建表时自动检测表是否存在,如果不存在就创建。
id int(5) zerofill PRIMARY KEY auto_increment,
#zerofill 5位,不足5位会自动填0
#atuo_increment 自增长字段
#每条记录会自动递增1,自增长必须是主键,也不可以重复。如果添加失败下一跳记录也会自动增加
name CHAR(10),
score DECIMAL(5,2),
address VARCHAR(128)
);
插入数据
INSERT into test01 VALUES(1,'张三',90,'东京');
INSERT into test01 VALUES( 2,'李四',89,'东京');
INSERT into test01 VALUES( NULL,'王瑞',33,'东京');
由于数据库软件效果不明显,到Linux数据库查询id自增
删除id为3的数据,并继续插入一条新数据且id为null,不输入。
#删除id=3的
DELETE from test01 where id=3;
#新增
INSERT into test01 VALUES( NULL,'王为',99,'东京');
查询发现id在3的基础上自增到4
6、对表的其他操作
复制表
create table 新表名 like 旧表;
复制数据
insert into 新表名,select * from 旧表;
复制表和数据同时进行
create table 新表(select * from 旧表);
临时表: 临时表创建完成之后,在库里面是看不到的,但是依然可以增删改查,但是重新连接数据之后,临时表会消失不见
清空表
drop 表名 #删除表
delete from 表名 ;# 是一行一行的清空表数据,速度比较慢,如果有自增长字段,delete情况之后,会继续按照原来的序号,继续自增。
truncate table 表名;#清空表,保留表的结构,但是清空之后原有的记录全部抹去,自增长也将从头开始,速度比较快
二、数据库的用户管理
1、在数据库软件中查询用户
root都是相同的
Host:可以登录的主机,localhost指的是本地登录,%:任意主机(ip地址)
权限上localhost>%的权限。
2、创建用户
root@localhost 安装完mysql之后就有了,不需要额外设置,其他的需要人工创建,其他的用户也不设置成localhost,都是设置主机名。
创建用户,允许用户名可登录的主机是192.168.10.62,表示可以通过192.168.10.62上主机的用户访问mysql1:192.168.10.61上的数据库。
create user 'cq' @'192.168.10.62';
常见用户密码
CREATE USER 'cq' @'192.168.10.62' IDENTIFIED BY '123456';
用62的主机用户登录远程连接数据库
[root@localhost ~]# mysql -h 192.168.10.61 -u root -p #由于未设置密码因此不需要输入
删除用户
drop user 'cq'@'192.168.10.62';
3、用户权限
查看数据库
mysql> show databases;
因为没有权限因此只能看
information_schema :包含了mydql服务器中所有其他数据库,表,列,索引权限等详细的元数据的信息,查询数据库的结构和元信息
performance_schema 包含mysql的服务性能和资源利用情况。查询语句和执行时间和锁定等信息
在数据库61上设置用户的所有权限
grant all privileges on * . * to 'cq' @'192.168.10.62';
#grant 赋权语句
#all 给与所有权限,远程登录,数据库的操作权限(增删改查)
#* . * 左边* 表示库,右*:库里的表
#identified by '123456' 登录密码
刷新权限,让其刚刚的配置生效
flush privileges;
在61上查看该用户的权限配置成功,拥有所有权限
show grants for 'cq'@'192.168.10.62';
使用mysql2查看数据库,可以查询到数据库内容
在61主机对用户进行取消权限
revoke all privileges on * . * from 'cq'@'192.168.10.62';
#revoke 取消权限
刷新权限,让其刚刚的配置生效
flush privileges;
使用62查看数据库,不能查询到数据库内容,权限被取消
只能查看权限
grant select on xy102.* to 'zlm'@'192.168.10.62';
刷新权限,让其刚刚的配置生效
flush privileges;
使用62查看数据库,只能查询到xy102数据库,其他数据库不可见
给多个权限,权限之间使用逗号隔开
grant select,insert,update on xy102.* to 'zlm'@'192.168.10.62';